-- ============================================================================
-- FablePool: Open Source Kite Flying Map — Database Schema v1 (baseline)
-- Target: PostgreSQL 15+, PostGIS 3.3+
-- Companion design doc: docs/06-database-schema.md
-- Apply with:  psql -d kitemap -f db/schema.sql
-- ============================================================================

BEGIN;

-- ---------------------------------------------------------------------------
-- Extensions
-- ---------------------------------------------------------------------------
CREATE EXTENSION IF NOT EXISTS postgis;      -- geography types & spatial indexes
CREATE EXTENSION IF NOT EXISTS pgcrypto;     -- gen_random_uuid()
CREATE EXTENSION IF NOT EXISTS citext;       -- case-insensitive email
CREATE EXTENSION IF NOT EXISTS pg_trgm;      -- trigram name search

-- ---------------------------------------------------------------------------
-- Enumerated types
-- ---------------------------------------------------------------------------
CREATE TYPE user_role           AS ENUM ('user', 'moderator', 'admin');
CREATE TYPE unit_system         AS ENUM ('metric', 'imperial', 'nautical');
CREATE TYPE spot_type           AS ENUM ('park', 'beach', 'hill', 'field', 'water', 'other');
CREATE TYPE spot_status         AS ENUM ('pending', 'published', 'flagged', 'archived');
CREATE TYPE content_status      AS ENUM ('pending', 'published', 'removed');
CREATE TYPE data_source         AS ENUM ('osm', 'community', 'curated');
CREATE TYPE data_source_weather AS ENUM ('open_meteo', 'noaa');
CREATE TYPE obstacle_tag        AS ENUM (
    'power_lines_within_100m',
    'airport_within_5km',
    'no_kite_bylaw',
    'tree_line_upwind',
    'buildings_adjacent',
    'crowded_area',
    'uneven_ground',
    'shallow_water_hazard',
    'rocks'
);
CREATE TYPE report_target       AS ENUM ('spot', 'review', 'photo');
CREATE TYPE report_reason       AS ENUM ('inaccurate', 'unsafe', 'spam', 'inappropriate', 'duplicate', 'other');
CREATE TYPE report_status       AS ENUM ('open', 'resolved', 'dismissed');
CREATE TYPE token_purpose       AS ENUM ('magic_link', 'refresh');

-- ---------------------------------------------------------------------------
-- Housekeeping: updated_at trigger function
-- ---------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    NEW.updated_at := now();
    RETURN NEW;
END;
$$;

-- ---------------------------------------------------------------------------
-- Grid snap for forecast cache keys (0.05 degree ~ 5 km).
-- Single source of truth so every consumer computes identical cache keys.
-- ---------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION snap_grid(coord double precision)
RETURNS numeric
LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $$
    SELECT round((round(coord / 0.05) * 0.05)::numeric, 4);
$$;

-- ===========================================================================
-- scoring_profiles  (rubric constants as data — docs/05 §3, §10)
-- ===========================================================================
CREATE TABLE scoring_profiles (
    key             text PRIMARY KEY,
    display_name    text        NOT NULL,
    w_min_ms        numeric(4,1) NOT NULL,
    w_ideal_lo_ms   numeric(4,1) NOT NULL,
    w_ideal_hi_ms   numeric(4,1) NOT NULL,
    w_max_ms        numeric(4,1) NOT NULL,
    gust_tolerant   boolean     NOT NULL DEFAULT false,
    precip_floor    numeric(3,2) NOT NULL DEFAULT 0.00,
    is_water_sport  boolean     NOT NULL DEFAULT false,
    rubric_version  text        NOT NULL,
    sort_order      smallint    NOT NULL,
    CONSTRAINT trapezoid_ordered CHECK (
        w_min_ms < w_ideal_lo_ms
        AND w_ideal_lo_ms <= w_ideal_hi_ms
        AND w_ideal_hi_ms < w_max_ms
    ),
    CONSTRAINT precip_floor_range CHECK (precip_floor >= 0 AND precip_floor <= 1)
);

INSERT INTO scoring_profiles
    (key, display_name, w_min_ms, w_ideal_lo_ms, w_ideal_hi_ms, w_max_ms,
     gust_tolerant, precip_floor, is_water_sport, rubric_version, sort_order)
VALUES
    ('single_line',    'Single-line / Casual',   1.7, 2.8,  5.5,  8.3, false, 0.00, false, '1.0.0', 1),
    ('sport_dual',     'Sport / Stunt kite',     2.8, 4.2,  8.3, 11.1, true,  0.00, false, '1.0.0', 2),
    ('power_traction', 'Power / Traction',       3.3, 5.0,  9.7, 12.5, true,  0.00, false, '1.0.0', 3),
    ('kitesurf',       'Kitesurfing',            5.1, 6.2, 12.9, 15.4, false, 0.60, true,  '1.0.0', 4);

-- ===========================================================================
-- users
-- ===========================================================================
CREATE TABLE users (
    id               uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    display_name     text        NOT NULL,
    email            citext      UNIQUE,
    oauth_provider   text,
    oauth_subject    text,
    role             user_role   NOT NULL DEFAULT 'user',
    preferred_units  unit_system NOT NULL DEFAULT 'metric',
    default_profile  text        REFERENCES scoring_profiles(key) ON DELETE SET NULL,
    created_at       timestamptz NOT NULL DEFAULT now(),
    updated_at       timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT display_name_len CHECK (char_length(display_name) BETWEEN 3 AND 40),
    CONSTRAINT oauth_pair CHECK (
        (oauth_provider IS NULL AND oauth_subject IS NULL)
        OR (oauth_provider IS NOT NULL AND oauth_subject IS NOT NULL)
    ),
    CONSTRAINT some_identity CHECK (email IS NOT NULL OR oauth_provider IS NOT NULL)
);

CREATE UNIQUE INDEX users_oauth_identity_uq
    ON users (oauth_provider, oauth_subject)
    WHERE oauth_provider IS NOT NULL;

CREATE TRIGGER users_set_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- ===========================================================================
-- spots
-- ===========================================================================
CREATE TABLE spots (
    id                 uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    name               text        NOT NULL,
    description        text,
    location           geography(Point, 4326)   NOT NULL,
    area               geography(Polygon, 4326),
    spot_type          spot_type   NOT NULL DEFAULT 'other',
    is_water_launch    boolean     NOT NULL DEFAULT false,
    shore_bearing_deg  smallint,
    suitable_profiles  text[]      NOT NULL DEFAULT '{}',
    surface            text,
    access_notes       text,
    hazards_notes      text,
    osm_id             bigint,
    osm_type           text,
    country_code       char(2),
    status             spot_status NOT NULL DEFAULT 'pending',
    rating_avg         numeric(3,2),
    rating_count       integer     NOT NULL DEFAULT 0,
    created_by         uuid        REFERENCES users(id) ON DELETE SET NULL,
    created_at         timestamptz NOT NULL DEFAULT now(),
    updated_at         timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT name_len            CHECK (char_length(name) BETWEEN 3 AND 120),
    CONSTRAINT description_len     CHECK (description IS NULL OR char_length(description) <= 5000),
    CONSTRAINT shore_bearing_range CHECK (shore_bearing_deg IS NULL OR shore_bearing_deg BETWEEN 0 AND 359),
    CONSTRAINT water_needs_bearing CHECK (NOT is_water_launch OR shore_bearing_deg IS NOT NULL),
    CONSTRAINT osm_type_valid      CHECK (osm_type IS NULL OR osm_type IN ('node', 'way', 'relation')),
    CONSTRAINT osm_pair            CHECK ((osm_id IS NULL) = (osm_type IS NULL)),
    CONSTRAINT rating_avg_range    CHECK (rating_avg IS NULL OR (rating_avg >= 1 AND rating_avg <= 5))
);

CREATE INDEX spots_location_gix       ON spots USING gist (location);
CREATE INDEX spots_area_gix           ON spots USING gist (area);
CREATE INDEX spots_status_country_idx ON spots (status, country_code);
CREATE INDEX spots_profiles_gin       ON spots USING gin (suitable_profiles);
CREATE INDEX spots_name_trgm          ON spots USING gin (name gin_trgm_ops);

CREATE TRIGGER spots_set_updated_at
    BEFORE UPDATE ON spots
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- ===========================================================================
-- spot_wind_sectors
-- ===========================================================================
CREATE TABLE spot_wind_sectors (
    id        uuid     PRIMARY KEY DEFAULT gen_random_uuid(),
    spot_id   uuid     NOT NULL REFERENCES spots(id) ON DELETE CASCADE,
    from_deg  smallint NOT NULL,
    to_deg    smallint NOT NULL,
    note      text,
    CONSTRAINT from_range CHECK (from_deg BETWEEN 0 AND 359),
    CONSTRAINT to_range   CHECK (to_deg   BETWEEN 0 AND 359),
    CONSTRAINT note_len   CHECK (note IS NULL OR char_length(note) <= 280)
);

CREATE INDEX spot_wind_sectors_spot_idx ON spot_wind_sectors (spot_id);

-- ===========================================================================
-- spot_obstacles
-- ===========================================================================
CREATE TABLE spot_obstacles (
    id           uuid         PRIMARY KEY DEFAULT gen_random_uuid(),
    spot_id      uuid         NOT NULL REFERENCES spots(id) ON DELETE CASCADE,
    tag          obstacle_tag NOT NULL,
    bearing_deg  smallint,
    source       data_source  NOT NULL DEFAULT 'community',
    verified_by  uuid         REFERENCES users(id) ON DELETE SET NULL,
    created_at   timestamptz  NOT NULL DEFAULT now(),
    CONSTRAINT bearing_range CHECK (bearing_deg IS NULL OR bearing_deg BETWEEN 0 AND 359)
);

-- NULLs are distinct in unique indexes, so coalesce bearing for dedupe.
CREATE UNIQUE INDEX spot_obstacles_dedupe_uq
    ON spot_obstacles (spot_id, tag, COALESCE(bearing_deg, -1));
CREATE INDEX spot_obstacles_spot_idx ON spot_obstacles (spot_id);

-- ===========================================================================
-- spot_photos
-- ===========================================================================
CREATE TABLE spot_photos (
    id           uuid           PRIMARY KEY DEFAULT gen_random_uuid(),
    spot_id      uuid           NOT NULL REFERENCES spots(id) ON DELETE CASCADE,
    uploaded_by  uuid           REFERENCES users(id) ON DELETE SET NULL,
    storage_key  text           NOT NULL,
    caption      text,
    width_px     integer,
    height_px    integer,
    license      text           NOT NULL DEFAULT 'CC-BY-SA-4.0',
    status       content_status NOT NULL DEFAULT 'pending',
    created_at   timestamptz    NOT NULL DEFAULT now(),
    CONSTRAINT caption_len     CHECK (caption IS NULL OR char_length(caption) <= 280),
    CONSTRAINT dimensions_sane CHECK (
        (width_px IS NULL AND height_px IS NULL)
        OR (width_px > 0 AND height_px > 0)
    )
);

CREATE INDEX spot_photos_spot_idx ON spot_photos (spot_id, status);

-- ===========================================================================
-- spot_reviews  (+ denormalized rating maintenance on spots)
-- ===========================================================================
CREATE TABLE spot_reviews (
    id          uuid           PRIMARY KEY DEFAULT gen_random_uuid(),
    spot_id     uuid           NOT NULL REFERENCES spots(id) ON DELETE CASCADE,
    user_id     uuid           NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    rating      smallint       NOT NULL,
    body        text,
    status      content_status NOT NULL DEFAULT 'published',
    created_at  timestamptz    NOT NULL DEFAULT now(),
    updated_at  timestamptz    NOT NULL DEFAULT now(),
    CONSTRAINT rating_range CHECK (rating BETWEEN 1 AND 5),
    CONSTRAINT body_len     CHECK (body IS NULL OR char_length(body) <= 2000),
    CONSTRAINT one_review_per_user UNIQUE (spot_id, user_id)
);

CREATE INDEX spot_reviews_spot_idx ON spot_reviews (spot_id, status);

CREATE TRIGGER spot_reviews_set_updated_at
    BEFORE UPDATE ON spot_reviews
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE OR REPLACE FUNCTION refresh_spot_rating()
RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
    target uuid;
BEGIN
    target := COALESCE(NEW.spot_id, OLD.spot_id);
    UPDATE spots s
       SET rating_avg = sub.avg_rating,
           rating_count = sub.cnt
      FROM (
            SELECT round(avg(rating)::numeric, 2) AS avg_rating,
                   count(*)::integer              AS cnt
              FROM spot_reviews
             WHERE spot_id = target
               AND status = 'published'
           ) AS sub
     WHERE s.id = target;
    -- When no published reviews remain, the subquery returns (NULL, 0): correct.
    RETURN NULL;
END;
$$;

CREATE TRIGGER spot_reviews_refresh_rating
    AFTER INSERT OR UPDATE OR DELETE ON spot_reviews
    FOR EACH ROW EXECUTE FUNCTION refresh_spot_rating();

-- ===========================================================================
-- session_reports
-- ===========================================================================
CREATE TABLE session_reports (
    id                 uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    spot_id            uuid        NOT NULL REFERENCES spots(id) ON DELETE CASCADE,
    user_id            uuid        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    flown_at           timestamptz NOT NULL,
    profile_key        text        NOT NULL REFERENCES scoring_profiles(key),
    experienced_rating smallint,
    predicted_score    smallint,
    observed_wind_ms   numeric(4,1),
    notes              text,
    created_at         timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT experienced_range CHECK (experienced_rating IS NULL OR experienced_rating BETWEEN 1 AND 5),
    CONSTRAINT predicted_range   CHECK (predicted_score IS NULL OR predicted_score BETWEEN 0 AND 100),
    CONSTRAINT observed_sane     CHECK (observed_wind_ms IS NULL OR observed_wind_ms BETWEEN 0 AND 80),
    CONSTRAINT notes_len         CHECK (notes IS NULL OR char_length(notes) <= 1000)
);

CREATE INDEX session_reports_spot_time_idx   ON session_reports (spot_id, flown_at DESC);
CREATE INDEX session_reports_calibration_idx ON session_reports (profile_key, experienced_rating);

-- ===========================================================================
-- favorites
-- ===========================================================================
CREATE TABLE favorites (
    user_id    uuid        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    spot_id    uuid        NOT NULL REFERENCES spots(id) ON DELETE CASCADE,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (user_id, spot_id)
);

CREATE INDEX favorites_spot_idx ON favorites (spot_id);

-- ===========================================================================
-- forecast_cache  (grid-snapped; see docs/06 §5)
-- ===========================================================================
CREATE TABLE forecast_cache (
    id          uuid                PRIMARY KEY DEFAULT gen_random_uuid(),
    grid_lat    numeric(7,4)        NOT NULL,
    grid_lon    numeric(7,4)        NOT NULL,
    provider    data_source_weather NOT NULL,
    payload     jsonb               NOT NULL,
    fetched_at  timestamptz         NOT NULL DEFAULT now(),
    expires_at  timestamptz         NOT NULL,
    CONSTRAINT grid_lat_range CHECK (grid_lat BETWEEN -90 AND 90),
    CONSTRAINT grid_lon_range CHECK (grid_lon BETWEEN -180 AND 180),
    CONSTRAINT cache_key UNIQUE (grid_lat, grid_lon, provider)
);

CREATE INDEX forecast_cache_expiry_idx ON forecast_cache (expires_at);

-- ===========================================================================
-- moderation_reports
-- ===========================================================================
CREATE TABLE moderation_reports (
    id           uuid          PRIMARY KEY DEFAULT gen_random_uuid(),
    reporter_id  uuid          REFERENCES users(id) ON DELETE SET NULL,
    target_type  report_target NOT NULL,
    target_id    uuid          NOT NULL,
    reason       report_reason NOT NULL,
    details      text,
    status       report_status NOT NULL DEFAULT 'open',
    resolved_by  uuid          REFERENCES users(id) ON DELETE SET NULL,
    created_at   timestamptz   NOT NULL DEFAULT now(),
    resolved_at  timestamptz,
    CONSTRAINT details_len       CHECK (details IS NULL OR char_length(details) <= 1000),
    CONSTRAINT resolution_paired CHECK (
        (status = 'open' AND resolved_at IS NULL)
        OR (status <> 'open' AND resolved_at IS NOT NULL)
    )
);

CREATE INDEX moderation_reports_open_idx
    ON moderation_reports (status, created_at)
    WHERE status = 'open';
CREATE INDEX moderation_reports_target_idx
    ON moderation_reports (target_type, target_id);

-- ===========================================================================
-- auth_tokens
-- ===========================================================================
CREATE TABLE auth_tokens (
    id          uuid          PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     uuid          NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash  text          NOT NULL,
    purpose     token_purpose NOT NULL,
    expires_at  timestamptz   NOT NULL,
    used_at     timestamptz,
    created_at  timestamptz   NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX auth_tokens_hash_uq   ON auth_tokens (token_hash);
CREATE INDEX        auth_tokens_user_idx  ON auth_tokens (user_id);
CREATE INDEX        auth_tokens_sweep_idx ON auth_tokens (expires_at);

-- ===========================================================================
-- schema_migrations  (baseline bookkeeping for future migrations)
-- ===========================================================================
CREATE TABLE schema_migrations (
    version     text        PRIMARY KEY,
    applied_at  timestamptz NOT NULL DEFAULT now()
);

INSERT INTO schema_migrations (version) VALUES ('0001_baseline');

COMMIT;