CREATE TYPE invasiveness_level AS ENUM ('none', 'watch_list', 'invasive', 'banned');
CREATE TYPE plant_layer AS ENUM ('canopy', 'understory', 'shrub', 'herbaceous', 'ground_cover', 'vine', 'root');
CREATE TYPE succession_stage AS ENUM ('pioneer', 'early', 'mid', 'climax');
CREATE TYPE drought_tolerance AS ENUM ('none', 'low', 'moderate', 'high');
CREATE TYPE salt_tolerance AS ENUM ('none', 'low', 'moderate', 'high');

CREATE TABLE species (
    id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    slug              TEXT NOT NULL UNIQUE,
    family_id         UUID NOT NULL REFERENCES families(id) ON DELETE RESTRICT,
    name_scientific   TEXT NOT NULL,
    name_en           TEXT,
    name_de           TEXT,
    description       TEXT,
    soil_moisture     TEXT,
    drainage_requirement TEXT,
    organic_matter_pct DOUBLE PRECISION,
    nitrogen_ppm      INTEGER,
    phosphorus_ppm    INTEGER,
    potassium_ppm     INTEGER,
    boron_ppm         DOUBLE PRECISION,
    calcium_ppm       INTEGER,
    copper_ppm        DOUBLE PRECISION,
    iron_ppm          DOUBLE PRECISION,
    magnesium_ppm     INTEGER,
    manganese_ppm     DOUBLE PRECISION,
    molybdenum_ppm    DOUBLE PRECISION,
    sulfur_ppm        INTEGER,
    zinc_ppm          DOUBLE PRECISION,
    ph_min            DOUBLE PRECISION,
    ph_max            DOUBLE PRECISION,
    soil_texture_preference TEXT[],
    hardiness_zone_usda TEXT,
    hardiness_zone_at   TEXT,
    min_temp          DOUBLE PRECISION,
    max_temp          DOUBLE PRECISION,
    drought_tolerance drought_tolerance,
    water_requirement_mm_week DOUBLE PRECISION,
    waterlogging_tolerance BOOLEAN,
    salt_tolerance    salt_tolerance,
    edibility_rating  SMALLINT,
    food_uses         TEXT,
    medicinal_uses    TEXT,
    other_uses        TEXT,
    native_range      TEXT,
    invasiveness      invasiveness_level DEFAULT 'none',
    pollination_type  TEXT,
    plant_layer       plant_layer,
    nitrogen_fixer    BOOLEAN,
    dynamic_accumulator BOOLEAN,
    dynamic_accumulator_nutrients TEXT[],
    attracts_pollinators BOOLEAN,
    attracts_beneficial_insects BOOLEAN,
    wildlife_value    TEXT,
    mulch_plant       BOOLEAN,
    ground_cover_quality TEXT,
    allelopathic      BOOLEAN,
    guild_role        TEXT[],
    succession_stage  succession_stage,
    heavy_metal_tolerance BOOLEAN,
    wikidata_qid      TEXT,
    gbif_id           TEXT,
    eppo_code         TEXT,
    pfaf_url          TEXT,
    primary_image_key TEXT,
    source_urls       TEXT[],
    created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_species_family ON species(family_id);
CREATE INDEX idx_species_search ON species
    USING GIN (to_tsvector('english',
        coalesce(name_scientific,'') || ' ' ||
        coalesce(name_en,'') || ' ' ||
        coalesce(name_de,'') || ' ' ||
        coalesce(description,'')));