113 lines
3.5 KiB
SQL
113 lines
3.5 KiB
SQL
-- Enable UUID extension
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
-- Species table
|
|
CREATE TABLE species (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
scientific_name TEXT NOT NULL,
|
|
common_name TEXT NOT NULL,
|
|
kingdom TEXT NOT NULL,
|
|
phylum TEXT NOT NULL,
|
|
class TEXT NOT NULL,
|
|
order_name TEXT NOT NULL,
|
|
family TEXT NOT NULL,
|
|
genus TEXT NOT NULL,
|
|
species_name TEXT NOT NULL,
|
|
authority TEXT,
|
|
sis_id INTEGER,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Species common names
|
|
CREATE TABLE common_names (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
species_id UUID NOT NULL REFERENCES species(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
language TEXT NOT NULL,
|
|
is_main BOOLEAN DEFAULT FALSE
|
|
);
|
|
|
|
-- Subpopulations
|
|
CREATE TABLE subpopulations (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
species_id UUID NOT NULL REFERENCES species(id) ON DELETE CASCADE,
|
|
scientific_name TEXT NOT NULL,
|
|
subpopulation_name TEXT NOT NULL,
|
|
sis_id INTEGER,
|
|
authority TEXT
|
|
);
|
|
|
|
-- IUCN assessments
|
|
CREATE TABLE iucn_assessments (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
species_id UUID NOT NULL REFERENCES species(id) ON DELETE CASCADE,
|
|
year_published INTEGER NOT NULL,
|
|
is_latest BOOLEAN DEFAULT FALSE,
|
|
possibly_extinct BOOLEAN DEFAULT FALSE,
|
|
possibly_extinct_in_wild BOOLEAN DEFAULT FALSE,
|
|
status TEXT,
|
|
url TEXT,
|
|
assessment_id INTEGER,
|
|
scope_code TEXT,
|
|
scope_description TEXT
|
|
);
|
|
|
|
-- CITES listings
|
|
CREATE TABLE cites_listings (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
species_id UUID NOT NULL REFERENCES species(id) ON DELETE CASCADE,
|
|
appendix TEXT NOT NULL,
|
|
listing_date DATE NOT NULL,
|
|
notes TEXT,
|
|
is_current BOOLEAN DEFAULT FALSE
|
|
);
|
|
|
|
-- CITES trade records
|
|
CREATE TABLE cites_trade_records (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
species_id UUID NOT NULL REFERENCES species(id) ON DELETE CASCADE,
|
|
record_id TEXT,
|
|
year INTEGER NOT NULL,
|
|
appendix TEXT NOT NULL,
|
|
taxon TEXT NOT NULL,
|
|
class TEXT,
|
|
order_name TEXT,
|
|
family TEXT,
|
|
genus TEXT,
|
|
term TEXT NOT NULL,
|
|
quantity DECIMAL(10,2),
|
|
unit TEXT,
|
|
importer TEXT,
|
|
exporter TEXT,
|
|
origin TEXT,
|
|
purpose TEXT,
|
|
source TEXT,
|
|
reporter_type TEXT,
|
|
import_permit TEXT,
|
|
export_permit TEXT,
|
|
origin_permit TEXT
|
|
);
|
|
|
|
-- Timeline events - combined view of significant events
|
|
CREATE TABLE timeline_events (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
species_id UUID NOT NULL REFERENCES species(id) ON DELETE CASCADE,
|
|
event_date DATE NOT NULL,
|
|
year INTEGER NOT NULL,
|
|
event_type TEXT NOT NULL, -- 'iucn_assessment', 'cites_listing', 'cites_trade'
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
status TEXT, -- IUCN status code or CITES appendix
|
|
source_type TEXT NOT NULL, -- Which table this came from
|
|
source_id UUID -- Reference to the original record
|
|
);
|
|
|
|
-- Create indexes for efficient querying
|
|
CREATE INDEX idx_species_scientific_name ON species(scientific_name);
|
|
CREATE INDEX idx_timeline_events_species ON timeline_events(species_id);
|
|
CREATE INDEX idx_timeline_events_date ON timeline_events(event_date);
|
|
CREATE INDEX idx_timeline_events_type ON timeline_events(event_type);
|
|
CREATE INDEX idx_cites_trade_species ON cites_trade_records(species_id);
|
|
CREATE INDEX idx_cites_trade_year ON cites_trade_records(year);
|
|
CREATE INDEX idx_iucn_assessments_species ON iucn_assessments(species_id);
|
|
CREATE INDEX idx_iucn_assessments_year ON iucn_assessments(year_published); |