-- ============================================================================
-- 0022 — Automated Meteorological Manifest Framework
--
-- Spec reference: CLAUDE.md "Automated Meteorological Manifest rules"
-- + docs/SkyLara_Automated_Meteorological_Manifest_Framework_Master_Spec.md
--
-- Additive only. Creates 4 new tables and extends waitlist_entries
-- with 3 nullable columns for meteo-blocking context.
--
-- New tables:
--   • meteo_tier_policies          — per-DZ per-tier threshold overrides
--   • meteo_operating_configs      — autonomous vs S&TA approval mode
--   • special_load_policies        — beach/demo load requirements
--   • meteo_eligibility_decisions  — audit log of eligibility evaluations
-- ============================================================================

-- ── MeteoTierPolicy ────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "meteo_tier_policies" (
    "id"                  SERIAL PRIMARY KEY,
    "dropzoneId"          INTEGER NOT NULL,
    "tier"                VARCHAR(16) NOT NULL,
    "steadyWindHold"      INTEGER,
    "steadyWindCaution"   INTEGER,
    "gustPeakHold"        INTEGER,
    "gustPeakCaution"     INTEGER,
    "gustSpreadHold"      INTEGER,
    "gustSpreadCaution"   INTEGER,
    "headwindHold"        INTEGER,
    "headwindCaution"     INTEGER,
    "crosswindHold"       INTEGER,
    "crosswindCaution"    INTEGER,
    "updatedAt"           TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedByUserId"     INTEGER,

    CONSTRAINT "meteo_tier_policies_dropzoneId_fkey"
        FOREIGN KEY ("dropzoneId") REFERENCES "dropzones"("id") ON DELETE CASCADE,
    CONSTRAINT "meteo_tier_policies_updatedByUserId_fkey"
        FOREIGN KEY ("updatedByUserId") REFERENCES "users"("id") ON DELETE SET NULL
);

CREATE UNIQUE INDEX "meteo_tier_policies_dropzoneId_tier_key"
    ON "meteo_tier_policies"("dropzoneId", "tier");

CREATE INDEX "meteo_tier_policies_dropzoneId_idx"
    ON "meteo_tier_policies"("dropzoneId");


-- ── MeteoOperatingConfig ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "meteo_operating_configs" (
    "id"                  SERIAL PRIMARY KEY,
    "dropzoneId"          INTEGER NOT NULL,
    "operatingMode"       VARCHAR(20) NOT NULL DEFAULT 'AUTONOMOUS',
    "autoHoldEnabled"     BOOLEAN NOT NULL DEFAULT TRUE,
    "autoReleaseEnabled"  BOOLEAN NOT NULL DEFAULT TRUE,
    "staApprovalRoles"    VARCHAR(255),
    "updatedAt"           TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedByUserId"     INTEGER,

    CONSTRAINT "meteo_operating_configs_dropzoneId_fkey"
        FOREIGN KEY ("dropzoneId") REFERENCES "dropzones"("id") ON DELETE CASCADE,
    CONSTRAINT "meteo_operating_configs_updatedByUserId_fkey"
        FOREIGN KEY ("updatedByUserId") REFERENCES "users"("id") ON DELETE SET NULL
);

CREATE UNIQUE INDEX "meteo_operating_configs_dropzoneId_key"
    ON "meteo_operating_configs"("dropzoneId");


-- ── SpecialLoadPolicy ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "special_load_policies" (
    "id"                        SERIAL PRIMARY KEY,
    "dropzoneId"                INTEGER NOT NULL,
    "loadType"                  VARCHAR(24) NOT NULL,
    "minLicenseLevel"           VARCHAR(8) NOT NULL DEFAULT 'C',
    "waterTrainingRequired"     BOOLEAN NOT NULL DEFAULT TRUE,
    "recentCurrencyDays"        INTEGER NOT NULL DEFAULT 30,
    "sameCanopyModelRequired"   BOOLEAN NOT NULL DEFAULT FALSE,
    "customRules"               JSONB,
    "isActive"                  BOOLEAN NOT NULL DEFAULT TRUE,
    "updatedAt"                 TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedByUserId"           INTEGER,

    CONSTRAINT "special_load_policies_dropzoneId_fkey"
        FOREIGN KEY ("dropzoneId") REFERENCES "dropzones"("id") ON DELETE CASCADE,
    CONSTRAINT "special_load_policies_updatedByUserId_fkey"
        FOREIGN KEY ("updatedByUserId") REFERENCES "users"("id") ON DELETE SET NULL
);

CREATE UNIQUE INDEX "special_load_policies_dropzoneId_loadType_key"
    ON "special_load_policies"("dropzoneId", "loadType");

CREATE INDEX "special_load_policies_dropzoneId_idx"
    ON "special_load_policies"("dropzoneId");


-- ── MeteoEligibilityDecision ───────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "meteo_eligibility_decisions" (
    "id"                  SERIAL PRIMARY KEY,
    "dropzoneId"          INTEGER NOT NULL,
    "userId"              INTEGER NOT NULL,
    "loadId"              INTEGER,
    "tier"                VARCHAR(16) NOT NULL,
    "decision"            VARCHAR(8) NOT NULL,
    "gates"               JSONB NOT NULL,
    "windSnapshot"        JSONB NOT NULL,
    "policySnapshot"      JSONB NOT NULL,
    "specialLoadType"     VARCHAR(24),
    "operatingMode"       VARCHAR(20) NOT NULL DEFAULT 'AUTONOMOUS',
    "overriddenById"      INTEGER,
    "overrideReason"      VARCHAR(255),
    "createdAt"           TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "meteo_eligibility_decisions_dropzoneId_fkey"
        FOREIGN KEY ("dropzoneId") REFERENCES "dropzones"("id") ON DELETE CASCADE,
    CONSTRAINT "meteo_eligibility_decisions_userId_fkey"
        FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE,
    CONSTRAINT "meteo_eligibility_decisions_overriddenById_fkey"
        FOREIGN KEY ("overriddenById") REFERENCES "users"("id") ON DELETE SET NULL
);

CREATE INDEX "meteo_eligibility_decisions_dropzoneId_idx"
    ON "meteo_eligibility_decisions"("dropzoneId");

CREATE INDEX "meteo_eligibility_decisions_userId_idx"
    ON "meteo_eligibility_decisions"("userId");

CREATE INDEX "meteo_eligibility_decisions_dropzoneId_createdAt_idx"
    ON "meteo_eligibility_decisions"("dropzoneId", "createdAt");

CREATE INDEX "meteo_eligibility_decisions_dropzoneId_userId_createdAt_idx"
    ON "meteo_eligibility_decisions"("dropzoneId", "userId", "createdAt");


-- ── WaitlistEntry extensions ───────────────────────────────────────────────
-- 3 nullable columns for meteo-blocking context on waitlist entries.
ALTER TABLE "waitlist_entries"
    ADD COLUMN IF NOT EXISTS "meteoBlockedAt"  TIMESTAMP(3),
    ADD COLUMN IF NOT EXISTS "meteoTier"       VARCHAR(16),
    ADD COLUMN IF NOT EXISTS "meteoReasonCode" VARCHAR(64);