CREATE TABLE geological_periods (
  period_id INTEGER PRIMARY KEY,
  period_name TEXT NOT NULL,
  start_mya REAL NOT NULL,
  end_mya REAL NOT NULL,  
  description TEXT
);

CREATE TABLE fossil_localities (
  locality_id INTEGER PRIMARY KEY, 
  locality_name TEXT NOT NULL,
  country TEXT NOT NULL,
  state_province TEXT,
  latitude REAL,
  longitude REAL,
  formation TEXT,
  period_id INTEGER NOT NULL,
  FOREIGN KEY (period_id) REFERENCES geological_periods(period_id)
);

CREATE TABLE fossil_taxa (
  taxon_id INTEGER PRIMARY KEY,
  taxon_name TEXT NOT NULL,
  taxon_rank TEXT NOT NULL,
  parent_taxon_id INTEGER,  
  first_appearance_mya REAL,
  last_appearance_mya REAL,
  FOREIGN KEY (parent_taxon_id) REFERENCES fossil_taxa(taxon_id)
);

CREATE TABLE fossil_specimens (
  specimen_id INTEGER PRIMARY KEY,
  locality_id INTEGER NOT NULL,
  taxon_id INTEGER NOT NULL,
  specimen_number TEXT NOT NULL,
  description TEXT,
  image_url TEXT,
  collector TEXT,
  collection_date TEXT,
  repository TEXT,
  FOREIGN KEY (locality_id) REFERENCES fossil_localities(locality_id),
  FOREIGN KEY (taxon_id) REFERENCES fossil_taxa(taxon_id)  
);

CREATE TABLE specimentaxon (
  specimen_id INTEGER NOT NULL,
  taxon_id INTEGER NOT NULL,
  PRIMARY KEY (specimen_id, taxon_id),
  FOREIGN KEY (specimen_id) REFERENCES fossil_specimens(specimen_id),
  FOREIGN KEY (taxon_id) REFERENCES fossil_taxa(taxon_id)
);

CREATE TABLE localityspecimen (
  locality_id INTEGER NOT NULL,
  specimen_id INTEGER NOT NULL, 
  PRIMARY KEY (locality_id, specimen_id),
  FOREIGN KEY (locality_id) REFERENCES fossil_localities(locality_id),
  FOREIGN KEY (specimen_id) REFERENCES fossil_specimens(specimen_id)
);

CREATE TABLE fossil_references (
  reference_id INTEGER PRIMARY KEY,
  authors TEXT NOT NULL,
  year INTEGER NOT NULL,
  title TEXT NOT NULL,
  publication TEXT NOT NULL,
  doi TEXT
);

CREATE TABLE specimenreference (
  specimen_id INTEGER NOT NULL,
  reference_id INTEGER NOT NULL,
  PRIMARY KEY (specimen_id, reference_id),
  FOREIGN KEY (specimen_id) REFERENCES fossil_specimens(specimen_id),
  FOREIGN KEY (reference_id) REFERENCES fossil_references(reference_id)
);

INSERT INTO geological_periods (period_id, period_name, start_mya, end_mya, description)
VALUES
    (1, 'Eocene', -56, -33.9, 'Second epoch of the Paleogene period, witnessing the diversification of modern mammals and the emergence of early primates.'),
    (2, 'Oligocene', -33.9, -23.03, 'Third and final epoch of the Paleogene period, characterized by global cooling and the evolution of modern bird and mammal groups.'),
    (3, 'Miocene', -23.03, -5.332, 'First epoch of the Neogene period, known for the expansion of grasslands and the evolution of large mammalian herbivores and predators.'),
    (4, 'Pliocene', -5.332, -2.588, 'Second epoch of the Neogene period, witnessing the emergence of hominins and the evolution of modern mammal communities.'),
    (5, 'Pleistocene', -2.588, -0.0117, 'First epoch of the Quaternary period, marked by glacial cycles, the evolution of modern humans, and the extinction of megafauna.'),
    (6, 'Holocene', -0.0117, 0, 'Current epoch, extending from the end of the Pleistocene to the present day. It is marked by the development of human civilizations.'),
    (7, 'Early Cambrian', -541, -514, 'First part of the Cambrian period, marked by the emergence of the first complex ecosystems and the "Cambrian explosion" of life.'),
    (8, 'Middle Cambrian', -514, -497, 'Second stage of the Cambrian period, witnessing the diversification of trilobites and the evolution of early arthropods.'),
    (9, 'Late Cambrian', -497, -485.4, 'Final stage of the Cambrian period, leading up to the Cambrian-Ordovician extinction event.'),
    (10, 'Early Ordovician', -485.4, -470, 'First part of the Ordovician period, marked by the recovery of life after the Cambrian-Ordovician extinction.'),
    (11, 'Middle Ordovician', -470, -458.4, 'Second stage of the Ordovician period, witnessing the diversification of marine invertebrates and the expansion of coral reefs.'),
    (12, 'Late Ordovician', -458.4, -443.8, 'Final stage of the Ordovician period, ending with the Ordovician-Silurian extinction event.'),
    (13, 'Early Triassic', -251.902, -247, 'First part of the Triassic period, marked by the recovery of life after the Permian-Triassic extinction event.'),
    (14, 'Middle Triassic', -247, -235, 'Second stage of the Triassic period, witnessing the diversification of dinosaurs and the evolution of early crocodilians.'),
    (15, 'Late Triassic', -235, -201.36, 'Final stage of the Triassic period, leading up to the Triassic-Jurassic extinction event.');

INSERT INTO fossil_localities (locality_id, locality_name, country, state_province, latitude, longitude, formation, period_id)
VALUES
    (1, 'Burgess Shale', 'Canada', 'British Columbia', 50.8667, -116.6833, 'Stephen Formation', 1),
    (2, 'Fossil Creek', 'United States', 'Texas', 33.45, -97.3667, 'Fossil Creek Formation', 4), 
    (3, 'Mazon Creek', 'United States', 'Illinois', 41.25, -88.25, 'Francis Creek Shale', 6), 
    (4, 'Solnhofen Limestone', 'Germany', 'Bavaria', 48.9, 11.2, 'Solnhofen Limestone', 8), 
    (5, 'Yixian Formation', 'China', 'Liaoning', 41.3, 120.5, 'Yixian Formation', 9), 
    (6, 'Florissant Fossil Beds', 'United States', 'Colorado', 38.9333, -105.2833, 'Florissant Formation', 15), 
    (7, 'La Brea Tar Pits', 'United States', 'California', 34.0611, -118.3569, 'Rancho La Brea', 16), 
    (8, 'Maotianshan Shales', 'China', 'Yunnan', 25.3, 101.8, 'Maotianshan Shales', 1), 
    (9, 'Emu Bay Shale', 'Australia', 'Tasmania', -40.7833, 147.6333, 'Emu Bay Shale', 1), 
    (10, 'Rhynie Chert', 'United Kingdom', 'Scotland', 57.2833, -2.75, 'Rhynie Chert', 2), 
    (11, 'Soom Shale', 'South Africa', 'Western Cape', -33.9167, 18.85, 'Soom Shale', 2), 
    (12, 'Red Hill', 'United States', 'Virginia', 37.5333, -76.3, 'Hampden-Sydney Shale', 3), 
    (13, 'Frasassi', 'Italy', 'Marche', 43.4167, 12.9, 'Frasassi Formation', 5), 
    (14, 'Joggins Fossil Cliffs', 'Canada', 'Nova Scotia', 45.7167, -64.4, 'Joggins Formation', 6), 
    (15, 'Ghost Ranch', 'United States', 'New Mexico', 36.3167, -106.5, 'Chinle Formation', 7), 
    (16, 'Morrison Formation', 'United States', 'Colorado', 39.35, -108.2167, 'Morrison Formation', 8); 

INSERT INTO fossil_taxa (taxon_id, taxon_name, taxon_rank, parent_taxon_id, first_appearance_mya, last_appearance_mya)
VALUES
    (1, 'Animalia', 'Kingdom', NULL, -635, NULL),
    (2, 'Arthropoda', 'Phylum', 1, -541, NULL),
    (3, 'Trilobita', 'Class', 2, -541, -251.902),
    (4, 'Chelicerata', 'Class', 2, -541, NULL),
    (5, 'Mollusca', 'Phylum', 1, -541, NULL),
    (6, 'Chordata', 'Phylum', 1, -510, NULL),
    (7, 'Arachnida', 'Class', 4, -485.4, NULL),
    (8, 'Insecta', 'Class', 2, -412, NULL),
    (9, 'Crustacea', 'Class', 2, -400, NULL),
    (10, 'Brachiopoda', 'Phylum', 1, -541, NULL),
    (11, 'Echinodermata', 'Phylum', 1, -515, NULL),
    (12, 'Conodonta', 'Class', 11, -541, -201.36),
    (13, 'Agnatha', 'Class', 6, -510, -358.9),
    (14, 'Placodermi', 'Class', 6, -430, -358.9),
    (15, 'Elasmobranchii', 'Subclass', 16, -420, NULL),
    (16, 'Chondrichthyes', 'Class', 6, -420, NULL),
    (17, 'Actinopterygii', 'Class', 6, -416, NULL),
    (18, 'Tetrapoda', 'Class', 6, -395, NULL),
    (19, 'Amphibia', 'Class', 18, -395, NULL),
    (20, 'Reptilia', 'Class', 18, -312, NULL),
    (21, 'Synapsida', 'Class', 18, -305, NULL),
    (22, 'Diapsida', 'Class', 18, -270, NULL),
    (23, 'Therapsida', 'Infraclass', 21, -270, NULL),
    (24, 'Mammalia', 'Class', 18, -225, NULL),
    (25, 'Dinosauria', 'Class', 22, -235, -66),
    (26, 'Aves', 'Class', 22, -150, NULL),
    (27, 'Homo', 'Genus', 24, -2.588, NULL);

INSERT INTO fossil_specimens (specimen_id, locality_id, taxon_id, specimen_number, description, image_url, collector, collection_date, repository)
VALUES
    (1, 1, 3, 'SMF 8571', 'Anomalocaris canadensis', 'https://[Log in to view URL]', 'Charles Doolittle Walcott', '1911-07-30', 'Smithsonian Institution'),
    (2, 2, 17, 'MCZ 4225', 'Eusthenopteron foordi', 'https://[Log in to view URL]', 'John William Dawson', '1881-05-15', 'Museum of Comparative Zoology'),
    (3, 3, 10, 'FMNH PE 10278', 'Athyris spiriferoides', 'https://[Log in to view URL]', 'Robert M. Fields', '1967-09-20', 'Field Museum of Natural History'),
    (4, 4, 20, 'BMNH 37001', 'Archaeopteryx lithographica', 'https://[Log in to view URL]', 'Hermann von Meyer', '1861-08-06', 'Natural History Museum, London'),
    (5, 5, 25, 'IVPP V12564', 'Sinosauropteryx prima', 'https://[Log in to view URL]', 'Ji Qiang', '1996-10-10', 'Institute of Vertebrate Paleontology and Paleoanthropology'),
    (6, 6, 24, 'F:AM 49899', 'Columbian mammoth skeleton', 'https://[Log in to view URL]', 'Harold Cook', '1955-08-27', 'Florida Museum of Natural History'),
    (7, 7, 27, 'La Brea Woman', 'Partial human skeleton', 'https://[Log in to view URL]', 'Loyal Chapman', '1975-06-12', 'Los Angeles County Museum of Natural History'),
    (8, 8, 3, 'NIGPAS 112530', 'Anomalocaris saron', 'https://[Log in to view URL]', 'Hou Xianguang', '1995-03-15', 'Nanjing Institute of Geology and Palaeontology'),
    (9, 9, 3, 'NMV F188699', 'Amplectobelua symbrachiata', 'https://[Log in to view URL]', 'John Jago', '1971-11-30', 'Museum Victoria'),
    (10, 10, 13, 'Rhynie chert fish', 'Partial fish fossil', 'https://[Log in to view URL]', 'Derek Briggs', '1978-09-05', 'Natural History Museum, London'),
    (11, 11, 13, 'Soom Shale fish', 'Primitive jawless fish', 'https://[Log in to view URL]', 'Robert W. Gess', '2006-02-21', 'Iziko South African Museum'),
    (12, 12, 19, 'Red Hill salamander', 'Primitive salamander-like amphibian', 'https://[Log in to view URL]', 'Robert L. Carroll', '1966-07-18', 'Virginia Museum of Natural History'),
    (13, 13, 14, 'Frasassi fossil fish', 'Primitive armored fish', 'https://[Log in to view URL]', 'Stefano Dominici', '1980-04-10', 'Museo Geopaleontologico di Rocca di Papa'),
    (14, 14, 21, 'Holotype of Dendromaia unamakiensis', 'Earliest known synapsid', 'https://[Log in to view URL]', 'Robert R. Reisz', '2001-07-03', 'Joggins Fossil Centre'),
    (15, 15, 23, 'Ghost Ranch dinosaur', 'Partial dinosaur skeleton', 'https://[Log in to view URL]', 'Edwin H. Colbert', '1947-06-25', 'American Museum of Natural History');

INSERT INTO specimentaxon (specimen_id, taxon_id)
VALUES
    (1, 2), 
    (2, 2), 
    (3, 25), 
    (4, 25), 
    (5, 25), 
    (6, 24), 
    (7, 24), 
    (8, 27), 
    (9, 25), 
    (10, 22); 

INSERT INTO localityspecimen (locality_id, specimen_id)
VALUES
    (1, 1), 
    (1, 2), 
    (3, 3), 
    (4, 4), 
    (4, 5), 
    (6, 6), 
    (7, 7), 
    (7, 8), 
    (15, 9), 
    (15, 10); 

INSERT INTO fossil_references (reference_id, authors, year, title, publication, doi)
VALUES
    (1, 'Smith, J. and Johnson, K.', 2022, 'Anomalocaris canadensis: A New Species of Arthropod from the Cambrian', 'Journal of Paleontology', '10.1093/jpaleo/ptoy067'),
    (2, 'Williams, R. and Lee, M.', 2021, 'The Evolution of Eurypterids during the Ordovician', 'Paleobiology', '10.1017/pab.2021.30'),
    (3, 'Wright, A.', 2020, 'Archaeopteryx: A Transitional Fossil between Dinosaurs and Birds', 'Nature', '10.1038/s41586-020-2387-6'),
    (4, 'Xu, X. et al.', 2019, 'A New Tyrannosaurid from the Yixian Formation of China', 'Proceedings of the Royal Society B', '10.1098/rspb.2019.0720'),
    (5, 'Brown, B. and Miller, S.', 2018, 'The Horned Dinosaur Triceratops horridus: New Insights from a Complete Skeleton', 'Paleontological Society Memoir', '10.1017/9781108626461'),

    (6, 'Chang, Y. and Liu, J.', 2022, 'Late Pleistocene Mammoths in North America: A Review', 'Quaternary International', '10.1016/j.quaint.2022.01.012'),
    (7, 'Johnson, K. and Wilson, R.', 2021, 'The La Brea Tar Pits: A Window into Pleistocene Megafauna', 'Journal of Vertebrate Paleontology', '10.1080/02724634.2021.1904710'),
    (8, 'White, T. et al.', 2020, 'Homo sapiens: The First Modern Humans', 'Science', '10.1126/science.abc123'),
    (9, 'Hatcher, J. et al.', 2019, 'A New Specimen of Diplodocus from the Morrison Formation', 'PeerJ', '10.7717/peerj.7432'),
    (10, 'Wang, Y. and Zhang, F.', 2018, 'Pterodactyls of the Late Jurassic: A Review', 'Geological Bulletin of China', '10.11867/j.issn.1009-5187.2018.03.02.01');

INSERT INTO specimenreference (specimen_id, reference_id)
VALUES
    (1, 1), 
    (2, 2), 
    (3, 3), 
    (4, 4), 
    (5, 5), 
    (6, 6), 
    (7, 7), 
    (8, 8), 
    (9, 9), 
    (10, 10);

-- WITH RECURSIVE TaxonomyPath (specimen_id, taxon_id, taxon_name, taxon_rank, chain) AS (
--     SELECT
--         fs.specimen_id,
--         ft.taxon_id,
--         ft.taxon_name,
--         ft.taxon_rank,
--         ft.taxon_name AS chain
--     FROM fossil_specimens fs
--     JOIN fossil_taxa ft ON fs.taxon_id = ft.taxon_id
--     WHERE ft.parent_taxon_id IS NULL

--     UNION ALL

--     SELECT
--         tp.specimen_id,
--         ft.taxon_id,
--         ft.taxon_name,
--         ft.taxon_rank,
--         tp.chain || ' -> ' || ft.taxon_name
--     FROM TaxonomyPath tp
--     JOIN fossil_taxa ft ON tp.taxon_id = ft.parent_taxon_id
-- )

-- SELECT
--     specimen_id,
--     taxon_name,
--     taxon_rank,
--     chain
-- FROM TaxonomyPath
-- WHERE taxon_rank = 'Kingdom'
-- ORDER BY specimen_id;

-- WITH RECURSIVE Lineage AS (
--     SELECT
--         taxon_id,
--         taxon_name,
--         parent_taxon_id
--     FROM fossil_taxa
--     WHERE taxon_name = 'Homo' -- Starting point
--     UNION ALL
--     SELECT
--         ft.taxon_id,
--         ft.taxon_name,
--         ft.parent_taxon_id
--     FROM fossil_taxa ft
--     JOIN Lineage l ON ft.taxon_id = l.parent_taxon_id
-- )
-- SELECT
--     taxon_name
-- FROM Lineage;

WITH MultiPeriodSpecimens AS (
    SELECT
        fs.specimen_id,
        fs.specimen_number,
        COUNT(DISTINCT fl.period_id) AS period_count
    FROM fossil_specimens fs
    JOIN fossil_localities fl ON fs.locality_id = fl.locality_id
    GROUP BY fs.specimen_id
    HAVING period_count > 1
)
SELECT
    mps.specimen_number,
    fr.title,
    fr.publication,
    fr.year
FROM MultiPeriodSpecimens mps
JOIN specimenreference sr ON mps.specimen_id = sr.specimen_id
JOIN fossil_references fr ON sr.reference_id = fr.reference_id;