-- 0003 — claim-first data layer, quality flags, campus containment, capacity ontology, run tracking, snapshots.
-- Idempotent (IF NOT EXISTS everywhere) so it can be re-applied on a database created before this file existed.

-- ─── claims ────────────────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "claims" (
  "id" text PRIMARY KEY,
  "subject_type" text NOT NULL,            -- facility | campus | project | operator | market | country
  "subject_id" text NOT NULL,
  "predicate" text NOT NULL,               -- it_capacity_mw | planned_power_mw | project_investment_usd | status | …
  "value" double precision,
  "value_text" text,
  "unit" text,                             -- MW | USD | …
  "scope" text NOT NULL DEFAULT 'unknown', -- building | facility | campus | metro | country | portfolio | company | unknown
  "scope_reason" text,
  "source_id" text NOT NULL,
  "connector_id" text NOT NULL,
  "document_id" text,
  "url" text NOT NULL,
  "published_at" text,                     -- partial date as published
  "retrieved_at" timestamp with time zone NOT NULL DEFAULT now(),
  "confidence" text NOT NULL DEFAULT 'moderate',
  "is_estimate" boolean NOT NULL DEFAULT false,
  "authority_tier" text NOT NULL DEFAULT 'D',
  "evidence_text" text,
  "evidence_start" integer,
  "evidence_end" integer,
  "parser_name" text,
  "parser_version" text,
  "run_id" text,
  "status" text NOT NULL DEFAULT 'current', -- current | superseded | rejected | review | unscoped
  "rejection_reason" text,
  "first_observed" timestamp with time zone NOT NULL DEFAULT now(),
  "last_observed" timestamp with time zone NOT NULL DEFAULT now(),
  "created_at" timestamp with time zone NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS "claims_uq" ON "claims" ("subject_type", "subject_id", "predicate", "source_id", "url", COALESCE("value", 0), COALESCE("value_text", ''));
CREATE INDEX IF NOT EXISTS "claims_subject_idx" ON "claims" ("subject_type", "subject_id", "predicate");
CREATE INDEX IF NOT EXISTS "claims_status_idx" ON "claims" ("status");
CREATE INDEX IF NOT EXISTS "claims_run_idx" ON "claims" ("run_id");
CREATE INDEX IF NOT EXISTS "claims_document_idx" ON "claims" ("document_id");

-- ─── quality flags ─────────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "quality_flags" (
  "id" text PRIMARY KEY,
  "entity_type" text NOT NULL,
  "entity_id" text NOT NULL,
  "claim_id" text,
  "code" text NOT NULL,                    -- mw_single_site_gt_1000 | scope_company | inv_single_site_gt_50b | project_false_positive | duplicate | …
  "severity" text NOT NULL DEFAULT 'warn', -- info | warn | critical
  "field" text,
  "message" text NOT NULL,
  "details" jsonb,
  "priority" integer NOT NULL DEFAULT 0,   -- review priority (impact-weighted)
  "status" text NOT NULL DEFAULT 'open',   -- open | resolved | dismissed
  "resolution" text,
  "resolved_by" text,
  "resolved_at" timestamp with time zone,
  "run_id" text,
  "dedupe_key" text NOT NULL,
  "created_at" timestamp with time zone NOT NULL DEFAULT now(),
  "updated_at" timestamp with time zone NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS "quality_flags_dedupe_uq" ON "quality_flags" ("dedupe_key");
CREATE INDEX IF NOT EXISTS "quality_flags_entity_idx" ON "quality_flags" ("entity_type", "entity_id");
CREATE INDEX IF NOT EXISTS "quality_flags_open_idx" ON "quality_flags" ("status", "priority" DESC) WHERE "status" = 'open';
CREATE INDEX IF NOT EXISTS "quality_flags_code_idx" ON "quality_flags" ("code");

-- ─── facilities: containment, capacity ontology, AI evidence ───────────────────────────────────────
ALTER TABLE "facilities" ADD COLUMN IF NOT EXISTS "parent_facility_id" text REFERENCES "facilities"("id");
ALTER TABLE "facilities" ADD COLUMN IF NOT EXISTS "record_scope" text NOT NULL DEFAULT 'facility'; -- building | facility | campus
ALTER TABLE "facilities" ADD COLUMN IF NOT EXISTS "ai_evidence" text NOT NULL DEFAULT 'unknown';   -- confirmed | likely | associated | unknown
ALTER TABLE "facilities" ADD COLUMN IF NOT EXISTS "utility_capacity_mw" double precision;
ALTER TABLE "facilities" ADD COLUMN IF NOT EXISTS "grid_connection_mw" double precision;
ALTER TABLE "facilities" ADD COLUMN IF NOT EXISTS "ultimate_campus_mw" double precision;
ALTER TABLE "facilities" ADD COLUMN IF NOT EXISTS "capacity_scope" text;                            -- scope of the displayed MW figure
ALTER TABLE "facilities" ADD COLUMN IF NOT EXISTS "capacity_semantics" text;                        -- predicate behind the displayed MW figure
ALTER TABLE "facilities" ADD COLUMN IF NOT EXISTS "developer_id" text REFERENCES "operators"("id");
ALTER TABLE "facilities" ADD COLUMN IF NOT EXISTS "landowner_id" text REFERENCES "operators"("id");
ALTER TABLE "facilities" ADD COLUMN IF NOT EXISTS "review_priority" integer NOT NULL DEFAULT 0;
CREATE INDEX IF NOT EXISTS "facilities_parent_idx" ON "facilities" ("parent_facility_id");
CREATE INDEX IF NOT EXISTS "facilities_campus_idx" ON "facilities" ("campus_id");
CREATE INDEX IF NOT EXISTS "facilities_merged_idx" ON "facilities" ("merged_into") WHERE "merged_into" IS NOT NULL;
CREATE INDEX IF NOT EXISTS "facilities_owner_idx" ON "facilities" ("owner_id");
CREATE INDEX IF NOT EXISTS "facilities_ai_idx" ON "facilities" ("ai_evidence") WHERE "ai_evidence" <> 'unknown';
CREATE INDEX IF NOT EXISTS "facilities_opened_idx" ON "facilities" ("opened_on") WHERE "opened_on" IS NOT NULL;

-- ─── projects: classification, evidence, lifecycle, geocoding, scopes ─────────────────────────────
ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "project_class" text;                -- NEW_BUILD | EXPANSION | … | UNKNOWN
ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "evidence_level" text;               -- strong | weak | none
ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "merged_into" text;
ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "ai_evidence" text NOT NULL DEFAULT 'unknown';
ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "capacity_scope" text;
ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "capacity_semantics" text;
ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "investment_scope" text;
ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "investment_semantics" text;
ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "developer_id" text REFERENCES "operators"("id");
ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "tenant_id" text REFERENCES "operators"("id");
ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "campus_id" text REFERENCES "campuses"("id");
ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "construction_started_on" text;
ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "approved_on" text;
ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "permit_filed_on" text;
ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "opened_on" text;
ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "review_priority" integer NOT NULL DEFAULT 0;
ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "hidden" boolean NOT NULL DEFAULT false;          -- false positives kept for audit, never listed
CREATE INDEX IF NOT EXISTS "projects_class_idx" ON "projects" ("project_class");
CREATE INDEX IF NOT EXISTS "projects_merged_idx" ON "projects" ("merged_into") WHERE "merged_into" IS NOT NULL;
CREATE INDEX IF NOT EXISTS "projects_live_idx" ON "projects" ("status", "country_iso2") WHERE "merged_into" IS NULL AND NOT "hidden";
CREATE INDEX IF NOT EXISTS "projects_latlng_idx" ON "projects" ("lat", "lng") WHERE "lat" IS NOT NULL;
CREATE INDEX IF NOT EXISTS "projects_metro_idx" ON "projects" ("metro_id");

-- ─── provenance / versions: run tracking + scope ──────────────────────────────────────────────────
ALTER TABLE "provenance" ADD COLUMN IF NOT EXISTS "run_id" text;
ALTER TABLE "provenance" ADD COLUMN IF NOT EXISTS "scope" text;
ALTER TABLE "provenance" ADD COLUMN IF NOT EXISTS "is_winner" boolean NOT NULL DEFAULT false; -- the observation backing the displayed column value
CREATE INDEX IF NOT EXISTS "provenance_current_idx" ON "provenance" ("entity_type", "entity_id", "field") WHERE "is_current";
CREATE INDEX IF NOT EXISTS "provenance_run_idx" ON "provenance" ("run_id");
ALTER TABLE "document_versions" ADD COLUMN IF NOT EXISTS "run_id" text;
ALTER TABLE "document_versions" ADD COLUMN IF NOT EXISTS "extractor_version" text;
CREATE INDEX IF NOT EXISTS "document_versions_run_idx" ON "document_versions" ("run_id");
ALTER TABLE "events" ADD COLUMN IF NOT EXISTS "run_id" text;
ALTER TABLE "events" ADD COLUMN IF NOT EXISTS "cluster_id" text;          -- same underlying announcement across outlets
ALTER TABLE "events" ADD COLUMN IF NOT EXISTS "evidence_count" integer NOT NULL DEFAULT 1;
ALTER TABLE "events" ADD COLUMN IF NOT EXISTS "is_ai" boolean NOT NULL DEFAULT false;
ALTER TABLE "events" ADD COLUMN IF NOT EXISTS "source_kind" text;
CREATE INDEX IF NOT EXISTS "events_significance_idx" ON "events" ("significance" DESC, "detected_at" DESC);
CREATE INDEX IF NOT EXISTS "events_project_idx" ON "events" ("project_id");
CREATE INDEX IF NOT EXISTS "events_cluster_idx" ON "events" ("cluster_id");
CREATE INDEX IF NOT EXISTS "events_run_idx" ON "events" ("run_id");
CREATE INDEX IF NOT EXISTS "events_metro_idx" ON "events" ("metro_id");

-- ─── news items ────────────────────────────────────────────────────────────────────────────────────
ALTER TABLE "news_items" ADD COLUMN IF NOT EXISTS "project_class" text;
ALTER TABLE "news_items" ADD COLUMN IF NOT EXISTS "cluster_id" text;
ALTER TABLE "news_items" ADD COLUMN IF NOT EXISTS "metro_id" text;
CREATE INDEX IF NOT EXISTS "news_items_operator_gin" ON "news_items" USING gin ("operator_ids");
CREATE INDEX IF NOT EXISTS "news_items_cluster_idx" ON "news_items" ("cluster_id");

-- ─── connectors: quarantine + health ───────────────────────────────────────────────────────────────
ALTER TABLE "connectors" ADD COLUMN IF NOT EXISTS "quarantine" boolean NOT NULL DEFAULT false;
ALTER TABLE "connectors" ADD COLUMN IF NOT EXISTS "consecutive_failures" integer NOT NULL DEFAULT 0;
ALTER TABLE "connectors" ADD COLUMN IF NOT EXISTS "blocked_since" timestamp with time zone;
ALTER TABLE "connectors" ADD COLUMN IF NOT EXISTS "last_discovered" integer;
ALTER TABLE "connectors" ADD COLUMN IF NOT EXISTS "priority_score" real;
ALTER TABLE "connector_runs" ADD COLUMN IF NOT EXISTS "quarantined" boolean NOT NULL DEFAULT false;

-- ─── sources: licensing ────────────────────────────────────────────────────────────────────────────
ALTER TABLE "sources" ADD COLUMN IF NOT EXISTS "redistribution" text;      -- allowed | attribution | restricted | unknown
ALTER TABLE "sources" ADD COLUMN IF NOT EXISTS "attribution_required" boolean;

-- ─── entity keys: scope the primary key by entity type ─────────────────────────────────────────────
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'entity_keys_pkey' AND conrelid = 'entity_keys'::regclass
             AND array_length(conkey, 1) = 1) THEN
    ALTER TABLE "entity_keys" DROP CONSTRAINT "entity_keys_pkey";
    ALTER TABLE "entity_keys" ADD PRIMARY KEY ("key", "entity_type");
  END IF;
END $$;

-- ─── daily snapshots for "as of" views and regression checks ───────────────────────────────────────
CREATE TABLE IF NOT EXISTS "entity_snapshots" (
  "day" date NOT NULL,
  "kind" text NOT NULL,        -- global_totals | ranking | facility_status | project_stage | operator_totals | country_totals
  "key" text NOT NULL,
  "payload" jsonb NOT NULL,
  "created_at" timestamp with time zone NOT NULL DEFAULT now(),
  CONSTRAINT "entity_snapshots_pk" PRIMARY KEY ("day", "kind", "key")
);
CREATE INDEX IF NOT EXISTS "entity_snapshots_kind_idx" ON "entity_snapshots" ("kind", "key", "day");

-- ─── watchlists (private, cookie-scoped) ───────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "watchlists" (
  "id" text PRIMARY KEY,
  "owner_token" text NOT NULL,
  "entity_type" text NOT NULL,
  "entity_id" text NOT NULL,
  "created_at" timestamp with time zone NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS "watchlists_uq" ON "watchlists" ("owner_token", "entity_type", "entity_id");

-- ─── grid / power context (events already carry the type; this keeps market-level constraint notes) ─
CREATE TABLE IF NOT EXISTS "grid_constraints" (
  "id" text PRIMARY KEY,
  "metro_id" text REFERENCES "metros"("id"),
  "country_iso2" text REFERENCES "countries"("iso2"),
  "kind" text NOT NULL,         -- moratorium | grid_delay | capacity_restriction | load_cap | new_transmission | new_substation | regulation | large_load_queue
  "title" text NOT NULL,
  "summary" text,
  "effective_date" text,
  "source_id" text,
  "document_id" text,
  "url" text NOT NULL,
  "event_id" text,
  "confidence" text NOT NULL DEFAULT 'moderate',
  "created_at" timestamp with time zone NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS "grid_constraints_metro_idx" ON "grid_constraints" ("metro_id");
CREATE INDEX IF NOT EXISTS "grid_constraints_country_idx" ON "grid_constraints" ("country_iso2");

-- ─── daily metrics time-series lookups ─────────────────────────────────────────────────────────────
CREATE INDEX IF NOT EXISTS "daily_metrics_series_idx" ON "daily_metrics" ("metric", "dim", "day");
CREATE INDEX IF NOT EXISTS "entity_matches_created_fac_idx" ON "entity_matches" ((candidate->>'createdFacilityId')) WHERE "status" = 'pending';