/**
 * PostgreSQL schema (source of truth for entities, provenance, events, connector state).
 * ClickHouse (see clickhouse.ts) holds crawl history, observations, page changes and time series.
 * Column naming: snake_case via drizzle `casing`.
 */
import { boolean, doublePrecision, index, integer, jsonb, pgTable, primaryKey, real, text, timestamp, uniqueIndex, date, bigint } from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";

const ts = (name: string) => timestamp(name, { withTimezone: true, mode: "string" });
const createdAt = () => ts("created_at").notNull().defaultNow();
const updatedAt = () => ts("updated_at").notNull().defaultNow();

export const countries = pgTable("countries", {
  iso2: text("iso2").primaryKey(),
  iso3: text("iso3"),
  slug: text("slug").notNull().unique(),
  name: text("name").notNull(),
  region: text("region"),
  subregion: text("subregion"),
  population: bigint("population", { mode: "number" }),
  gdpUsd: doublePrecision("gdp_usd"),
  electricityTwh: doublePrecision("electricity_twh"),
  renewableShare: doublePrecision("renewable_share"),
  statsYear: integer("stats_year"),
  lat: doublePrecision("lat"),
  lng: doublePrecision("lng"),
  stats: jsonb("stats").$type<Record<string, unknown>>().notNull().default(sql`'{}'::jsonb`),
  updatedAt: updatedAt(),
});

export const metros = pgTable(
  "metros",
  {
    id: text("id").primaryKey(),
    slug: text("slug").notNull().unique(),
    name: text("name").notNull(),
    countryIso2: text("country_iso2").notNull().references(() => countries.iso2),
    regionName: text("region_name"),
    lat: doublePrecision("lat").notNull(),
    lng: doublePrecision("lng").notNull(),
    radiusKm: real("radius_km").notNull().default(60),
    aliases: text("aliases").array().notNull().default(sql`'{}'::text[]`),
    description: text("description"),
    stats: jsonb("stats").$type<Record<string, unknown>>().notNull().default(sql`'{}'::jsonb`),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [index("metros_country_idx").on(t.countryIso2)],
);

export const operators = pgTable(
  "operators",
  {
    id: text("id").primaryKey(),
    slug: text("slug").notNull().unique(),
    name: text("name").notNull(),
    normalizedName: text("normalized_name").notNull(),
    kind: text("kind"), // OperatorKind
    website: text("website"),
    hqCountryIso2: text("hq_country_iso2"),
    hqCity: text("hq_city"),
    parentId: text("parent_id"),
    description: text("description"),
    aliases: text("aliases").array().notNull().default(sql`'{}'::text[]`),
    externalIds: jsonb("external_ids").$type<Record<string, string | number>>().notNull().default(sql`'{}'::jsonb`),
    isCloudProvider: boolean("is_cloud_provider").notNull().default(false),
    isCarrier: boolean("is_carrier").notNull().default(false),
    stats: jsonb("stats").$type<Record<string, unknown>>().notNull().default(sql`'{}'::jsonb`),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [index("operators_norm_idx").on(t.normalizedName), index("operators_kind_idx").on(t.kind)],
);

export const campuses = pgTable("campuses", {
  id: text("id").primaryKey(),
  slug: text("slug").notNull().unique(),
  name: text("name").notNull(),
  operatorId: text("operator_id").references(() => operators.id),
  metroId: text("metro_id").references(() => metros.id),
  countryIso2: text("country_iso2"),
  city: text("city"),
  lat: doublePrecision("lat"),
  lng: doublePrecision("lng"),
  externalIds: jsonb("external_ids").$type<Record<string, string | number>>().notNull().default(sql`'{}'::jsonb`),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});

export const facilities = pgTable(
  "facilities",
  {
    id: text("id").primaryKey(),
    slug: text("slug").notNull().unique(),
    name: text("name").notNull(),
    normalizedName: text("normalized_name").notNull(),
    operatorId: text("operator_id").references(() => operators.id),
    ownerId: text("owner_id").references(() => operators.id),
    campusId: text("campus_id").references(() => campuses.id),
    metroId: text("metro_id").references(() => metros.id),
    countryIso2: text("country_iso2").references(() => countries.iso2),
    city: text("city"),
    regionName: text("region_name"),
    address: text("address"),
    postalCode: text("postal_code"),
    lat: doublePrecision("lat"),
    lng: doublePrecision("lng"),
    geoPrecision: text("geo_precision").notNull().default("unknown"),
    geoSource: text("geo_source"),
    geohash: text("geohash"),
    status: text("status").notNull().default("unknown"),
    facilityType: text("facility_type").notNull().default("unknown"),
    tier: text("tier"),
    buildingSqm: doublePrecision("building_sqm"),
    siteAreaHa: doublePrecision("site_area_ha"),
    itCapacityMw: doublePrecision("it_capacity_mw"),
    totalPowerMw: doublePrecision("total_power_mw"),
    plannedPowerMw: doublePrecision("planned_power_mw"),
    mwIsEstimate: boolean("mw_is_estimate").notNull().default(false),
    rackCount: integer("rack_count"),
    pue: real("pue"),
    coolingType: text("cooling_type"),
    renewableClaim: text("renewable_claim"),
    openedOn: text("opened_on"), // partial date
    constructionStartedOn: text("construction_started_on"),
    announcedOn: text("announced_on"),
    website: text("website"),
    description: text("description"),
    isAi: boolean("is_ai").notNull().default(false),
    isHyperscale: boolean("is_hyperscale").notNull().default(false),
    certifications: text("certifications").array().notNull().default(sql`'{}'::text[]`),
    carriersCount: integer("carriers_count"),
    ixpCount: integer("ixp_count"),
    networksCount: integer("networks_count"),
    confidence: text("confidence").notNull().default("unverified"),
    completeness: integer("completeness").notNull().default(0),
    externalIds: jsonb("external_ids").$type<Record<string, string | number>>().notNull().default(sql`'{}'::jsonb`),
    sourceCount: integer("source_count").notNull().default(0),
    firstSeen: ts("first_seen").notNull().defaultNow(),
    lastVerified: ts("last_verified"),
    mergedInto: text("merged_into"),
    /** containment: a building row points at its campus row (aggregation never counts both) */
    parentFacilityId: text("parent_facility_id"),
    recordScope: text("record_scope").notNull().default("facility"), // building | facility | campus
    aiEvidence: text("ai_evidence").notNull().default("unknown"), // confirmed | likely | associated | unknown
    utilityCapacityMw: doublePrecision("utility_capacity_mw"),
    gridConnectionMw: doublePrecision("grid_connection_mw"),
    ultimateCampusMw: doublePrecision("ultimate_campus_mw"),
    capacityScope: text("capacity_scope"),
    capacitySemantics: text("capacity_semantics"),
    developerId: text("developer_id"),
    landownerId: text("landowner_id"),
    reviewPriority: integer("review_priority").notNull().default(0),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [
    index("facilities_country_idx").on(t.countryIso2),
    index("facilities_parent_idx").on(t.parentFacilityId),
    index("facilities_operator_idx").on(t.operatorId),
    index("facilities_metro_idx").on(t.metroId),
    index("facilities_status_idx").on(t.status),
    index("facilities_type_idx").on(t.facilityType),
    index("facilities_geohash_idx").on(t.geohash),
    index("facilities_norm_idx").on(t.normalizedName),
    index("facilities_updated_idx").on(t.updatedAt),
  ],
);

export const facilityAliases = pgTable(
  "facility_aliases",
  {
    facilityId: text("facility_id").notNull().references(() => facilities.id, { onDelete: "cascade" }),
    alias: text("alias").notNull(),
    normalized: text("normalized").notNull(),
    sourceId: text("source_id"),
  },
  (t) => [primaryKey({ columns: [t.facilityId, t.alias] }), index("facility_aliases_norm_idx").on(t.normalized)],
);

/** connector-scoped key → facility id (idempotent re-ingestion, e.g. "peeringdb:fac:1"). */
export const entityKeys = pgTable(
  "entity_keys",
  {
    key: text("key").notNull(),
    entityType: text("entity_type").notNull(),
    entityId: text("entity_id").notNull(),
    connectorId: text("connector_id").notNull(),
    createdAt: createdAt(),
  },
  (t) => [primaryKey({ columns: [t.key, t.entityType] }), index("entity_keys_entity_idx").on(t.entityType, t.entityId)],
);

export const cloudRegions = pgTable(
  "cloud_regions",
  {
    id: text("id").primaryKey(),
    slug: text("slug").notNull().unique(),
    providerId: text("provider_id").notNull().references(() => operators.id),
    code: text("code").notNull(),
    name: text("name").notNull(),
    city: text("city"),
    regionName: text("region_name"),
    countryIso2: text("country_iso2").references(() => countries.iso2),
    metroId: text("metro_id").references(() => metros.id),
    lat: doublePrecision("lat"),
    lng: doublePrecision("lng"),
    geoPrecision: text("geo_precision").notNull().default("city"),
    availabilityZones: integer("availability_zones"),
    launchedOn: text("launched_on"),
    status: text("status").notNull().default("operational"),
    isSovereign: boolean("is_sovereign").notNull().default(false),
    sourceUrl: text("source_url"),
    externalIds: jsonb("external_ids").$type<Record<string, string | number>>().notNull().default(sql`'{}'::jsonb`),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex("cloud_regions_provider_code_uq").on(t.providerId, t.code), index("cloud_regions_country_idx").on(t.countryIso2)],
);

export const ixps = pgTable("ixps", {
  id: text("id").primaryKey(),
  slug: text("slug").notNull().unique(),
  name: text("name").notNull(),
  nameLong: text("name_long"),
  city: text("city"),
  countryIso2: text("country_iso2").references(() => countries.iso2),
  metroId: text("metro_id").references(() => metros.id),
  regionContinent: text("region_continent"),
  website: text("website"),
  networkCount: integer("network_count"),
  externalIds: jsonb("external_ids").$type<Record<string, string | number>>().notNull().default(sql`'{}'::jsonb`),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});

export const facilityIxps = pgTable(
  "facility_ixps",
  {
    facilityId: text("facility_id").notNull().references(() => facilities.id, { onDelete: "cascade" }),
    ixpId: text("ixp_id").notNull().references(() => ixps.id, { onDelete: "cascade" }),
    sourceId: text("source_id"),
  },
  (t) => [primaryKey({ columns: [t.facilityId, t.ixpId] })],
);

/** Carriers / networks / cloud providers present at a facility (operators with kind carrier|cloud|hyperscaler). */
export const facilityTenants = pgTable(
  "facility_tenants",
  {
    facilityId: text("facility_id").notNull().references(() => facilities.id, { onDelete: "cascade" }),
    operatorId: text("operator_id").notNull().references(() => operators.id, { onDelete: "cascade" }),
    role: text("role").notNull().default("carrier"), // carrier | cloud | network | tenant
    asn: integer("asn"),
    sourceId: text("source_id"),
  },
  (t) => [primaryKey({ columns: [t.facilityId, t.operatorId, t.role] }), index("facility_tenants_operator_idx").on(t.operatorId)],
);

export const projects = pgTable(
  "projects",
  {
    id: text("id").primaryKey(),
    slug: text("slug").notNull().unique(),
    name: text("name").notNull(),
    normalizedName: text("normalized_name").notNull(),
    operatorId: text("operator_id").references(() => operators.id),
    facilityId: text("facility_id").references(() => facilities.id),
    metroId: text("metro_id").references(() => metros.id),
    countryIso2: text("country_iso2").references(() => countries.iso2),
    city: text("city"),
    regionName: text("region_name"),
    lat: doublePrecision("lat"),
    lng: doublePrecision("lng"),
    geoPrecision: text("geo_precision").notNull().default("unknown"),
    status: text("status").notNull().default("announced"),
    announcedOn: text("announced_on"),
    expectedOpening: text("expected_opening"),
    plannedMw: doublePrecision("planned_mw"),
    investmentUsd: doublePrecision("investment_usd"),
    investmentCurrency: text("investment_currency"),
    investmentOriginal: doublePrecision("investment_original"),
    acreage: doublePrecision("acreage"),
    phaseCount: integer("phase_count"),
    isAi: boolean("is_ai").notNull().default(false),
    description: text("description"),
    sourceUrl: text("source_url"),
    confidence: text("confidence").notNull().default("moderate"),
    externalIds: jsonb("external_ids").$type<Record<string, string | number>>().notNull().default(sql`'{}'::jsonb`),
    lastUpdate: ts("last_update").notNull().defaultNow(),
    projectClass: text("project_class"), // NEW_BUILD | EXPANSION | CONSTRUCTION_START | PERMIT | … | UNKNOWN
    evidenceLevel: text("evidence_level"), // strong | weak | none
    mergedInto: text("merged_into"),
    aiEvidence: text("ai_evidence").notNull().default("unknown"),
    capacityScope: text("capacity_scope"),
    capacitySemantics: text("capacity_semantics"),
    investmentScope: text("investment_scope"),
    investmentSemantics: text("investment_semantics"),
    developerId: text("developer_id"),
    tenantId: text("tenant_id"),
    campusId: text("campus_id"),
    constructionStartedOn: text("construction_started_on"),
    approvedOn: text("approved_on"),
    permitFiledOn: text("permit_filed_on"),
    openedOn: text("opened_on"),
    reviewPriority: integer("review_priority").notNull().default(0),
    /** false positives are hidden, never deleted (audit trail) */
    hidden: boolean("hidden").notNull().default(false),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [index("projects_country_idx").on(t.countryIso2), index("projects_operator_idx").on(t.operatorId), index("projects_status_idx").on(t.status), index("projects_norm_idx").on(t.normalizedName), index("projects_class_idx").on(t.projectClass), index("projects_metro_idx").on(t.metroId)],
);

export const projectTimeline = pgTable(
  "project_timeline",
  {
    id: text("id").primaryKey(),
    projectId: text("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }),
    eventDate: text("event_date").notNull(), // partial date
    eventType: text("event_type").notNull(),
    description: text("description").notNull(),
    sourceId: text("source_id"),
    documentId: text("document_id"),
    url: text("url"),
    createdAt: createdAt(),
  },
  (t) => [index("project_timeline_project_idx").on(t.projectId)],
);

/** Sources = websites / datasets a connector reads from. */
export const sources = pgTable("sources", {
  id: text("id").primaryKey(),
  connectorId: text("connector_id").notNull(),
  name: text("name").notNull(),
  domain: text("domain").notNull(),
  kind: text("kind").notNull(), // SourceKind
  priority: integer("priority").notNull().default(3),
  url: text("url"),
  license: text("license"),
  attribution: text("attribution"),
  robotsAllowed: boolean("robots_allowed"),
  notes: text("notes"),
  redistribution: text("redistribution"), // allowed | attribution | restricted | unknown
  attributionRequired: boolean("attribution_required"),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});

/** Connector runtime state (config is loaded from YAML and mirrored here for the admin UI). */
export const connectors = pgTable("connectors", {
  id: text("id").primaryKey(),
  sourceName: text("source_name").notNull(),
  domain: text("domain").notNull(),
  kind: text("kind").notNull(),
  mode: text("mode").notNull(),
  enabled: boolean("enabled").notNull().default(true),
  paused: boolean("paused").notNull().default(false),
  config: jsonb("config").$type<Record<string, unknown>>().notNull().default(sql`'{}'::jsonb`),
  parserVersion: text("parser_version").notNull().default("v1"),
  schedule: jsonb("schedule").$type<Record<string, string>>().notNull().default(sql`'{}'::jsonb`),
  health: text("health").notNull().default("never_run"),
  lastRunAt: ts("last_run_at"),
  lastStatus: text("last_status"),
  lastError: text("last_error"),
  nextRunAt: ts("next_run_at"),
  stats: jsonb("stats").$type<Record<string, number>>().notNull().default(sql`'{}'::jsonb`),
  /** quarantine: runs extract and preview but publish nothing (after parser changes, HTML changes, suspicious spikes) */
  quarantine: boolean("quarantine").notNull().default(false),
  consecutiveFailures: integer("consecutive_failures").notNull().default(0),
  blockedSince: ts("blocked_since"),
  lastDiscovered: integer("last_discovered"),
  priorityScore: real("priority_score"),
  createdAt: createdAt(),
  updatedAt: updatedAt(),
});

export const connectorRuns = pgTable(
  "connector_runs",
  {
    id: text("id").primaryKey(),
    connectorId: text("connector_id").notNull(),
    task: text("task").notNull().default("crawl"), // crawl | discover | reprocess | manual
    startedAt: ts("started_at").notNull().defaultNow(),
    finishedAt: ts("finished_at"),
    status: text("status").notNull().default("running"), // running | ok | partial | failed | aborted
    stats: jsonb("stats").$type<Record<string, number>>().notNull().default(sql`'{}'::jsonb`),
    error: text("error"),
    log: jsonb("log").$type<Array<{ t: string; level: string; msg: string }>>().notNull().default(sql`'[]'::jsonb`),
    quarantined: boolean("quarantined").notNull().default(false),
  },
  (t) => [index("connector_runs_connector_idx").on(t.connectorId, t.startedAt)],
);

/** Crawled documents (URL registry with scheduling + change-tracking state). Raw bodies live in object storage. */
export const documents = pgTable(
  "documents",
  {
    id: text("id").primaryKey(), // stableId("document", canonicalUrl)
    connectorId: text("connector_id").notNull(),
    sourceId: text("source_id").notNull(),
    url: text("url").notNull(),
    canonicalUrl: text("canonical_url").notNull(),
    urlFingerprint: text("url_fingerprint").notNull(),
    pageType: text("page_type").notNull().default("unknown"),
    classifier: text("classifier"), // rule id that classified it
    fetchLevel: integer("fetch_level").notNull().default(1),
    priority: integer("priority").notNull().default(50),
    contentHash: text("content_hash"),
    etag: text("etag"),
    lastModified: text("last_modified"),
    statusCode: integer("status_code"),
    contentType: text("content_type"),
    sizeBytes: integer("size_bytes"),
    title: text("title"),
    storageKey: text("storage_key"),
    extractorVersion: text("extractor_version"),
    extractOk: boolean("extract_ok"),
    extractCount: integer("extract_count").notNull().default(0),
    error: text("error"),
    errorCount: integer("error_count").notNull().default(0),
    firstSeen: ts("first_seen").notNull().defaultNow(),
    lastFetched: ts("last_fetched"),
    lastChanged: ts("last_changed"),
    lastChecked: ts("last_checked"),
    nextCheck: ts("next_check"),
    changeFrequencyScore: real("change_frequency_score").notNull().default(0.5),
    fetchCount: integer("fetch_count").notNull().default(0),
    changeCount: integer("change_count").notNull().default(0),
    discoveredFrom: text("discovered_from"),
    quarantined: boolean("quarantined").notNull().default(false),
    entityRefs: jsonb("entity_refs").$type<Array<{ type: string; id: string }>>().notNull().default(sql`'[]'::jsonb`),
    updatedAt: updatedAt(),
  },
  (t) => [
    uniqueIndex("documents_fingerprint_uq").on(t.urlFingerprint),
    index("documents_connector_idx").on(t.connectorId),
    index("documents_next_check_idx").on(t.nextCheck),
    index("documents_page_type_idx").on(t.pageType),
  ],
);

export const documentVersions = pgTable(
  "document_versions",
  {
    id: text("id").primaryKey(),
    documentId: text("document_id").notNull().references(() => documents.id, { onDelete: "cascade" }),
    contentHash: text("content_hash").notNull(),
    fetchedAt: ts("fetched_at").notNull().defaultNow(),
    fetchLevel: integer("fetch_level").notNull().default(1),
    statusCode: integer("status_code"),
    sizeBytes: integer("size_bytes"),
    storageKey: text("storage_key"),
    diffSummary: jsonb("diff_summary").$type<{ addedCount: number; removedCount: number; ratio: number; added: string[]; removed: string[] }>(),
    detectedChanges: jsonb("detected_changes").$type<Array<Record<string, unknown>>>().notNull().default(sql`'[]'::jsonb`),
    significance: integer("significance").notNull().default(0),
    runId: text("run_id"),
    extractorVersion: text("extractor_version"),
  },
  (t) => [index("document_versions_doc_idx").on(t.documentId, t.fetchedAt), index("document_versions_run_idx").on(t.runId)],
);

/** Per-field provenance for every entity value. */
export const provenance = pgTable(
  "provenance",
  {
    id: text("id").primaryKey(),
    entityType: text("entity_type").notNull(),
    entityId: text("entity_id").notNull(),
    field: text("field").notNull(),
    value: jsonb("value"),
    sourceId: text("source_id").notNull(),
    connectorId: text("connector_id").notNull(),
    documentId: text("document_id"),
    url: text("url").notNull(),
    firstObserved: ts("first_observed").notNull().defaultNow(),
    lastObserved: ts("last_observed").notNull().defaultNow(),
    retrievedAt: ts("retrieved_at").notNull().defaultNow(),
    confidence: text("confidence").notNull().default("moderate"),
    isEstimate: boolean("is_estimate").notNull().default(false),
    method: text("method"),
    extractorVersion: text("extractor_version"),
    isCurrent: boolean("is_current").notNull().default(true),
    note: text("note"),
    runId: text("run_id"),
    scope: text("scope"),
    /** the observation currently backing the displayed column value */
    isWinner: boolean("is_winner").notNull().default(false),
  },
  (t) => [
    index("provenance_entity_idx").on(t.entityType, t.entityId),
    index("provenance_run_idx").on(t.runId),
    index("provenance_source_idx").on(t.sourceId),
    uniqueIndex("provenance_uq").on(t.entityType, t.entityId, t.field, t.sourceId, t.url),
  ],
);

export const events = pgTable(
  "events",
  {
    id: text("id").primaryKey(),
    entityType: text("entity_type").notNull(),
    entityId: text("entity_id"),
    eventType: text("event_type").notNull(),
    detectedAt: ts("detected_at").notNull().defaultNow(),
    effectiveDate: text("effective_date"),
    oldValue: jsonb("old_value"),
    newValue: jsonb("new_value"),
    sourceId: text("source_id").notNull(),
    documentId: text("document_id"),
    url: text("url").notNull(),
    title: text("title").notNull(),
    summary: text("summary"),
    significance: integer("significance").notNull().default(30),
    confidence: text("confidence").notNull().default("moderate"),
    reviewStatus: text("review_status").notNull().default("auto"),
    countryIso2: text("country_iso2"),
    operatorId: text("operator_id"),
    metroId: text("metro_id"),
    projectId: text("project_id"),
    fingerprint: text("fingerprint").notNull(),
    runId: text("run_id"),
    /** documents describing the same underlying announcement share a cluster id */
    clusterId: text("cluster_id"),
    evidenceCount: integer("evidence_count").notNull().default(1),
    isAi: boolean("is_ai").notNull().default(false),
    sourceKind: text("source_kind"),
  },
  (t) => [
    uniqueIndex("events_fingerprint_uq").on(t.fingerprint),
    index("events_significance_idx").on(t.significance, t.detectedAt),
    index("events_project_idx").on(t.projectId),
    index("events_cluster_idx").on(t.clusterId),
    index("events_detected_idx").on(t.detectedAt),
    index("events_entity_idx").on(t.entityType, t.entityId),
    index("events_country_idx").on(t.countryIso2),
    index("events_operator_idx").on(t.operatorId),
    index("events_type_idx").on(t.eventType),
  ],
);

export const newsItems = pgTable(
  "news_items",
  {
    id: text("id").primaryKey(),
    sourceId: text("source_id").notNull(),
    connectorId: text("connector_id").notNull(),
    url: text("url").notNull().unique(),
    title: text("title").notNull(),
    publishedAt: ts("published_at"),
    summary: text("summary"),
    pageType: text("page_type").notNull().default("unknown"),
    eventType: text("event_type"),
    mentions: jsonb("mentions").$type<Record<string, unknown>>().notNull().default(sql`'{}'::jsonb`),
    operatorIds: text("operator_ids").array().notNull().default(sql`'{}'::text[]`),
    countryIso2s: text("country_iso2s").array().notNull().default(sql`'{}'::text[]`),
    facilityIds: text("facility_ids").array().notNull().default(sql`'{}'::text[]`),
    projectId: text("project_id"),
    mw: doublePrecision("mw"),
    significance: integer("significance").notNull().default(20),
    projectClass: text("project_class"),
    clusterId: text("cluster_id"),
    metroId: text("metro_id"),
    createdAt: createdAt(),
  },
  (t) => [index("news_items_published_idx").on(t.publishedAt), index("news_items_cluster_idx").on(t.clusterId)],
);

/** Reconciliation queue: candidate records whose match to an existing facility was ambiguous. */
export const entityMatches = pgTable(
  "entity_matches",
  {
    id: text("id").primaryKey(),
    connectorId: text("connector_id").notNull(),
    candidateKey: text("candidate_key").notNull(),
    candidate: jsonb("candidate").$type<Record<string, unknown>>().notNull(),
    matchedFacilityId: text("matched_facility_id"),
    score: real("score").notNull(),
    reasons: text("reasons").array().notNull().default(sql`'{}'::text[]`),
    status: text("status").notNull().default("pending"), // pending | approved | rejected | auto_merged | auto_created
    decidedBy: text("decided_by"),
    decidedAt: ts("decided_at"),
    createdAt: createdAt(),
  },
  (t) => [index("entity_matches_status_idx").on(t.status), index("entity_matches_key_idx").on(t.candidateKey)],
);

export const rankings = pgTable(
  "rankings",
  {
    id: text("id").primaryKey(),
    key: text("key").notNull(),
    scope: text("scope").notNull(),
    label: text("label").notNull(),
    unit: text("unit").notNull(),
    methodology: text("methodology").notNull(),
    minCoverage: real("min_coverage"),
    rows: jsonb("rows").$type<Array<Record<string, unknown>>>().notNull(),
    total: integer("total").notNull(),
    computedAt: ts("computed_at").notNull().defaultNow(),
    isCurrent: boolean("is_current").notNull().default(true),
  },
  (t) => [index("rankings_key_idx").on(t.key, t.isCurrent)],
);

/** Daily aggregate snapshots (also mirrored to ClickHouse) for growth charts. */
export const dailyMetrics = pgTable(
  "daily_metrics",
  {
    day: date("day").notNull(),
    metric: text("metric").notNull(), // facilities_total, known_mw, construction_mw, planned_mw, events, …
    dim: text("dim").notNull().default("global"), // "global" | "country:US" | "operator:op_x" | "metro:met_x"
    value: doublePrecision("value").notNull(),
  },
  (t) => [primaryKey({ columns: [t.day, t.metric, t.dim] })],
);

export const adminSessions = pgTable("admin_sessions", {
  id: text("id").primaryKey(),
  createdAt: createdAt(),
  expiresAt: ts("expires_at").notNull(),
  ip: text("ip"),
  userAgent: text("user_agent"),
});

export const systemAlerts = pgTable("system_alerts", {
  id: text("id").primaryKey(),
  level: text("level").notNull(), // info | warn | error
  component: text("component").notNull(),
  message: text("message").notNull(),
  details: jsonb("details").$type<Record<string, unknown>>(),
  resolvedAt: ts("resolved_at"),
  createdAt: createdAt(),
});

/** Small key/value state per connector (cursors, seen keys, discovery snapshots). */
export const connectorState = pgTable(
  "connector_state",
  {
    connectorId: text("connector_id").notNull(),
    key: text("key").notNull(),
    value: jsonb("value"),
    updatedAt: updatedAt(),
  },
  (t) => [primaryKey({ columns: [t.connectorId, t.key] })],
);

/** Claim-first store: one figure asserted by one document about one subject, with scope, evidence and authority (packages/core claims.ts). */
export const claims = pgTable(
  "claims",
  {
    id: text("id").primaryKey(),
    subjectType: text("subject_type").notNull(),
    subjectId: text("subject_id").notNull(),
    predicate: text("predicate").notNull(),
    value: doublePrecision("value"),
    valueText: text("value_text"),
    unit: text("unit"),
    scope: text("scope").notNull().default("unknown"),
    scopeReason: text("scope_reason"),
    sourceId: text("source_id").notNull(),
    connectorId: text("connector_id").notNull(),
    documentId: text("document_id"),
    url: text("url").notNull(),
    publishedAt: text("published_at"),
    retrievedAt: ts("retrieved_at").notNull().defaultNow(),
    confidence: text("confidence").notNull().default("moderate"),
    isEstimate: boolean("is_estimate").notNull().default(false),
    authorityTier: text("authority_tier").notNull().default("D"),
    evidenceText: text("evidence_text"),
    evidenceStart: integer("evidence_start"),
    evidenceEnd: integer("evidence_end"),
    parserName: text("parser_name"),
    parserVersion: text("parser_version"),
    runId: text("run_id"),
    status: text("status").notNull().default("current"), // current | superseded | rejected | review | unscoped
    rejectionReason: text("rejection_reason"),
    firstObserved: ts("first_observed").notNull().defaultNow(),
    lastObserved: ts("last_observed").notNull().defaultNow(),
    createdAt: createdAt(),
  },
  (t) => [index("claims_subject_idx").on(t.subjectType, t.subjectId, t.predicate), index("claims_status_idx").on(t.status), index("claims_run_idx").on(t.runId), index("claims_document_idx").on(t.documentId)],
);

/** Deterministic data-quality flags (capacity / investment sanity, scope, duplicates, project false positives…). */
export const qualityFlags = pgTable(
  "quality_flags",
  {
    id: text("id").primaryKey(),
    entityType: text("entity_type").notNull(),
    entityId: text("entity_id").notNull(),
    claimId: text("claim_id"),
    code: text("code").notNull(),
    severity: text("severity").notNull().default("warn"), // info | warn | critical
    field: text("field"),
    message: text("message").notNull(),
    details: jsonb("details").$type<Record<string, unknown>>(),
    priority: integer("priority").notNull().default(0),
    status: text("status").notNull().default("open"), // open | resolved | dismissed
    resolution: text("resolution"),
    resolvedBy: text("resolved_by"),
    resolvedAt: ts("resolved_at"),
    runId: text("run_id"),
    dedupeKey: text("dedupe_key").notNull(),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [uniqueIndex("quality_flags_dedupe_uq").on(t.dedupeKey), index("quality_flags_entity_idx").on(t.entityType, t.entityId), index("quality_flags_code_idx").on(t.code)],
);

/** Daily JSON snapshots (global totals, rankings, status / stage counts) for "as of" views and regression checks. */
export const entitySnapshots = pgTable(
  "entity_snapshots",
  {
    day: date("day").notNull(),
    kind: text("kind").notNull(),
    key: text("key").notNull(),
    payload: jsonb("payload").$type<Record<string, unknown>>().notNull(),
    createdAt: createdAt(),
  },
  (t) => [primaryKey({ columns: [t.day, t.kind, t.key] }), index("entity_snapshots_kind_idx").on(t.kind, t.key, t.day)],
);

/** Private watchlists (owner = opaque cookie token, no accounts). */
export const watchlists = pgTable(
  "watchlists",
  {
    id: text("id").primaryKey(),
    ownerToken: text("owner_token").notNull(),
    entityType: text("entity_type").notNull(),
    entityId: text("entity_id").notNull(),
    createdAt: createdAt(),
  },
  (t) => [uniqueIndex("watchlists_uq").on(t.ownerToken, t.entityType, t.entityId)],
);

/** Public reports of grid constraints (moratoria, delays, load caps, new transmission…) attached to a market / country. */
export const gridConstraints = pgTable(
  "grid_constraints",
  {
    id: text("id").primaryKey(),
    metroId: text("metro_id").references(() => metros.id),
    countryIso2: text("country_iso2").references(() => countries.iso2),
    kind: text("kind").notNull(),
    title: text("title").notNull(),
    summary: text("summary"),
    effectiveDate: text("effective_date"),
    sourceId: text("source_id"),
    documentId: text("document_id"),
    url: text("url").notNull(),
    eventId: text("event_id"),
    confidence: text("confidence").notNull().default("moderate"),
    createdAt: createdAt(),
  },
  (t) => [index("grid_constraints_metro_idx").on(t.metroId), index("grid_constraints_country_idx").on(t.countryIso2)],
);