-- Main animals table
CREATE TABLE animals (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    vivarium_id UUID NOT NULL,
    name VARCHAR(255) NOT NULL,
    assigned_id VARCHAR(50),
    species_id UUID REFERENCES species(id),
    morph_combination JSONB DEFAULT '[]',
    sex VARCHAR(10) CHECK (sex IN ('male', 'female', 'unknown')),
    birth_date DATE,
    age_classification VARCHAR(20), -- hatchling, juvenile, adult
    acquisition_date DATE,
    acquisition_method VARCHAR(50),
    acquisition_cost DECIMAL(10,2),
    source VARCHAR(255),
    owner_id UUID, -- References users or organizations
    status VARCHAR(20) DEFAULT 'active',
    weight_grams INTEGER,
    length_mm INTEGER,
    notes TEXT,
    visibility VARCHAR(20) DEFAULT 'private',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Animal health records
CREATE TABLE animal_health_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    animal_id UUID REFERENCES animals(id) ON DELETE CASCADE,
    record_type VARCHAR(50) NOT NULL,
    date_recorded DATE NOT NULL,
    reporter VARCHAR(255),
    notes TEXT,
    attachments JSONB DEFAULT '[]',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Animal media associations
CREATE TABLE animal_media (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    animal_id UUID REFERENCES animals(id) ON DELETE CASCADE,
    media_id UUID NOT NULL, -- References repti-media service
    media_type VARCHAR(20),
    is_primary BOOLEAN DEFAULT FALSE,
    caption TEXT,
    display_order INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Lineage relationships
CREATE TABLE lineage (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    offspring_id UUID REFERENCES animals(id) ON DELETE CASCADE,
    parent_id UUID REFERENCES animals(id) ON DELETE CASCADE,
    parent_type VARCHAR(10) CHECK (parent_type IN ('sire', 'dam')),
    confidence_level VARCHAR(20) DEFAULT 'confirmed',
    verified_by UUID, -- References users
    verification_date TIMESTAMP WITH TIME ZONE,
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(offspring_id, parent_id, parent_type)
);

-- Lineage verification logs
CREATE TABLE lineage_verifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    lineage_id UUID REFERENCES lineage(id) ON DELETE CASCADE,
    verified_by UUID NOT NULL,
    verification_method VARCHAR(50),
    confidence_score DECIMAL(3,2),
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Breeding pairs
CREATE TABLE breeding_pairs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL,
    sire_id UUID REFERENCES animals(id),
    dam_id UUID REFERENCES animals(id),
    pairing_date DATE,
    status VARCHAR(20) DEFAULT 'active',
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(sire_id, dam_id, pairing_date)
);

-- Breeding attempts
CREATE TABLE breeding_attempts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    pair_id UUID REFERENCES breeding_pairs(id) ON DELETE CASCADE,
    attempt_date DATE NOT NULL,
    successful BOOLEAN,
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Clutches and offspring
CREATE TABLE clutches (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    pair_id UUID REFERENCES breeding_pairs(id),
    laid_date DATE,
    incubation_start DATE,
    expected_hatch_date DATE,
    actual_hatch_date DATE,
    total_eggs INTEGER,
    viable_eggs INTEGER,
    hatched_count INTEGER,
    temperature_scale VARCHAR(20) DEFAULT 'celsius',
    incubation_temp DECIMAL(4,2),
    incubation_humidity_percent INTEGER,
    substrate VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Species taxonomy
CREATE TABLE species (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    scientific_name VARCHAR(255) UNIQUE NOT NULL,
    common_name VARCHAR(255),
    family VARCHAR(100),
    genus VARCHAR(100),
    species VARCHAR(100),
    subspecies VARCHAR(100),
    description TEXT,
    care_requirements JSONB DEFAULT '{}', -- break into separate table
    breeding_info JSONB DEFAULT '{}', -- break into separate table
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Morphs and genetic traits
CREATE TABLE morphs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    species_id UUID REFERENCES species(id),
    name VARCHAR(255) NOT NULL,
    genetic_type VARCHAR(50), -- dominant, recessive, co-dominant, etc.
    allele_symbol VARCHAR(10),
    description TEXT,
    inheritance_pattern JSONB DEFAULT '{}',
    visual_traits JSONB DEFAULT '[]',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Animal genetic profiles
CREATE TABLE animal_genetics (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    animal_id UUID REFERENCES animals(id) ON DELETE CASCADE,
    morph_id UUID REFERENCES morphs(id),
    expression_level VARCHAR(20), -- homozygous, heterozygous, visual, etc.
    confirmed BOOLEAN DEFAULT FALSE,
    confidence_score DECIMAL(3,2),
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);