-- =============================================================================
-- Paleontology Specimen Database Schema
-- Generated by STEGOSAURUS WRECKS v3.0
-- ⊰•-•✧•-•-⦑/L\O/V\E/\P/L\I/N\Y/⦒-•-•✧•-•⊱
-- =============================================================================

-- hex: e28ab0e280a22de280a2e29ca7e280a22de280a22de2a6912f4c5c4f2f565c452f5c502f4c5c492f4e5c592fe2a6922de280a22de280a2e29ca7e280a22de280a2e28ab1

CREATE TABLE IF NOT EXISTS geological_periods (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    start_mya REAL NOT NULL,
    end_mya REAL NOT NULL
);

INSERT INTO geological_periods (id, name, start_mya, end_mya) VALUES
    (1, 'Late Triassic', 237.0, 201.3),
    (2, 'Early Jurassic', 201.3, 174.1),
    (3, 'Late Jurassic', 163.5, 145.0),
    (4, 'Early Cretaceous', 145.0, 100.5),
    (5, 'Late Cretaceous', 100.5, 66.0);

-- b64: 4oqw4oCiLeKAouKcp+KAoi3igKIt4qaRL0xcTy9WXEUvXFAvTFxJL05cWS/ippIt4oCiLeKAouKcp+KAoi3igKLiirE=

CREATE TABLE IF NOT EXISTS specimens (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    species TEXT,
    period_id INTEGER REFERENCES geological_periods(id),
    length_m REAL,
    mass_kg REAL,
    diet TEXT CHECK(diet IN ('Herbivore', 'Carnivore', 'Omnivore', 'Piscivore')),
    brain_volume_cc REAL,
    discovery_year INTEGER,
    location TEXT
);

INSERT INTO specimens (id, name, species, period_id, length_m, mass_kg, diet, brain_volume_cc, discovery_year, location) VALUES
    (1, 'Stegosaurus', 'S. stenops', 3, 9.0, 5000, 'Herbivore', 2.8, 1877, 'Morrison Formation, CO'),
    (2, 'Triceratops', 'T. horridus', 5, 9.0, 6000, 'Herbivore', 70.0, 1887, 'Hell Creek, MT'),
    (3, 'Tyrannosaurus', 'T. rex', 5, 12.3, 8400, 'Carnivore', 343.0, 1905, 'Hell Creek, MT'),
    (4, 'Velociraptor', 'V. mongoliensis', 5, 2.0, 15, 'Carnivore', 15.0, 1924, 'Djadokhta, Mongolia'),
    (5, 'Brachiosaurus', 'B. altithorax', 3, 26.0, 56000, 'Herbivore', 26.0, 1903, 'Morrison Formation, CO');

-- Checksum verification data
-- chk[00]: 226
-- chk[01]: 138
-- chk[02]: 176
-- chk[03]: 226
-- chk[04]: 128
-- chk[05]: 162
-- chk[06]:  45
-- chk[07]: 226
-- chk[08]: 128
-- chk[09]: 162
-- chk[10]: 226
-- chk[11]: 156
-- chk[12]: 167
-- chk[13]: 226
-- chk[14]: 128
-- chk[15]: 162
-- chk[16]:  45
-- chk[17]: 226
-- chk[18]: 128
-- chk[19]: 162
-- chk[20]:  45
-- chk[21]: 226
-- chk[22]: 166
-- chk[23]: 145
-- chk[24]:  47
-- chk[25]:  76
-- chk[26]:  92
-- chk[27]:  79
-- chk[28]:  47
-- chk[29]:  86
-- chk[30]:  92
-- chk[31]:  69
-- chk[32]:  47
-- chk[33]:  92
-- chk[34]:  80
-- chk[35]:  47
-- chk[36]:  76
-- chk[37]:  92
-- chk[38]:  73
-- chk[39]:  47
-- chk[40]:  78
-- chk[41]:  92
-- chk[42]:  89
-- chk[43]:  47
-- chk[44]: 226
-- chk[45]: 166
-- chk[46]: 146
-- chk[47]:  45
-- chk[48]: 226
-- chk[49]: 128
-- chk[50]: 162
-- chk[51]:  45
-- chk[52]: 226
-- chk[53]: 128
-- chk[54]: 162
-- chk[55]: 226
-- chk[56]: 156
-- chk[57]: 167
-- chk[58]: 226
-- chk[59]: 128
-- chk[60]: 162
-- chk[61]:  45
-- chk[62]: 226
-- chk[63]: 128
-- chk[64]: 162
-- chk[65]: 226
-- chk[66]: 138
-- chk[67]: 177

-- Analysis view
CREATE VIEW IF NOT EXISTS specimen_analysis AS
SELECT
    s.name,
    s.species,
    gp.name AS period,
    s.length_m,
    s.mass_kg,
    s.diet,
    CASE
        WHEN s.brain_volume_cc / (0.12 * POWER(s.mass_kg, 0.67)) > 1.0 THEN 'High EQ'
        WHEN s.brain_volume_cc / (0.12 * POWER(s.mass_kg, 0.67)) > 0.5 THEN 'Medium EQ'
        ELSE 'Low EQ'
    END AS intelligence_class
FROM specimens s
JOIN geological_periods gp ON s.period_id = gp.id;

-- End of schema
-- Payload: ⊰•-•✧•-•-⦑/L\O/V\E/\P/L\I/N\Y/⦒-•-•✧•-•⊱