import { bigint, bigserial, boolean, date, integer, jsonb, pgTable, primaryKey, real, smallint, text, timestamp } from "drizzle-orm/pg-core";

const ts = (name: string) => timestamp(name, { withTimezone: true, mode: "date" });

export const sources = pgTable("sources", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  domain: text("domain").notNull(),
  homepage: text("homepage"),
  description: text("description"),
  categories: text("categories").array().notNull().default([]),
  tier: text("tier").notNull().default("B"),
  importanceWeight: real("importance_weight").notNull().default(1),
  discover: jsonb("discover").$type<Record<string, unknown>>().notNull().default({}),
  fallback: jsonb("fallback").$type<Record<string, unknown>>().notNull().default({}),
  enabled: boolean("enabled").notNull().default(true),
  robotsCheckedAt: ts("robots_checked_at"),
  termsReviewedAt: ts("terms_reviewed_at"),
  allowedMethods: text("allowed_methods").array().notNull().default([]),
  rateLimitPerMin: integer("rate_limit_per_min"),
  llmEnabled: boolean("llm_enabled").notNull().default(true),
  notes: text("notes"),
  firstParty: boolean("first_party").notNull().default(true),
  country: text("country"),
  language: text("language"),
  kind: text("kind").notNull().default("registry"),
  ownerToken: text("owner_token"),
  /** seed (YAML registry) · import (admin) · factory (Source Factory) */
  origin: text("origin").notNull().default("seed"),
  sector: text("sector"),
  createdAt: ts("created_at").notNull().defaultNow(),
  updatedAt: ts("updated_at").notNull().defaultNow(),
});

export const sensors = pgTable("sensors", {
  id: text("id").primaryKey(),
  sourceId: text("source_id").notNull(),
  name: text("name").notNull(),
  url: text("url").notNull(),
  type: text("type").notNull(),
  connector: text("connector").notNull(),
  tier: text("tier").notNull().default("B"),
  importanceWeight: real("importance_weight").notNull().default(1),
  config: jsonb("config").$type<Record<string, unknown>>().notNull().default({}),
  baseIntervalSeconds: integer("base_interval_seconds"),
  enabled: boolean("enabled").notNull().default(true),
  health: text("health").notNull().default("UP"),
  nextCheckAt: ts("next_check_at").notNull().defaultNow(),
  lastCheckAt: ts("last_check_at"),
  lastChangeAt: ts("last_change_at"),
  lastEventAt: ts("last_event_at"),
  lastStatus: integer("last_status"),
  lastError: text("last_error"),
  etag: text("etag"),
  lastModified: text("last_modified"),
  state: jsonb("state").$type<Record<string, unknown> | null>(),
  lastSnapshotId: text("last_snapshot_id"),
  consecutiveErrors: integer("consecutive_errors").notNull().default(0),
  totalRuns: integer("total_runs").notNull().default(0),
  totalNotModified: integer("total_not_modified").notNull().default(0),
  rawChanges: integer("raw_changes").notNull().default(0),
  meaningfulChanges: integer("meaningful_changes").notNull().default(0),
  avgLatencyMs: integer("avg_latency_ms"),
  status: text("status").notNull().default("ACTIVE"),
  validatedAt: ts("validated_at"),
  priority: smallint("priority").notNull().default(2),
  createdAt: ts("created_at").notNull().defaultNow(),
  updatedAt: ts("updated_at").notNull().defaultNow(),
});

export const sensorRuns = pgTable("sensor_runs", {
  id: text("id").primaryKey(),
  sensorId: text("sensor_id").notNull(),
  startedAt: ts("started_at").notNull(),
  finishedAt: ts("finished_at"),
  httpStatus: integer("http_status"),
  outcome: text("outcome").notNull(),
  error: text("error"),
  durationMs: integer("duration_ms"),
  bytes: integer("bytes"),
  fetchMethod: text("fetch_method"),
  snapshotId: text("snapshot_id"),
});

export const snapshots = pgTable("snapshots", {
  id: text("id").primaryKey(),
  sensorId: text("sensor_id").notNull(),
  url: text("url").notNull(),
  capturedAt: ts("captured_at").notNull(),
  httpStatus: integer("http_status"),
  contentType: text("content_type"),
  contentLength: integer("content_length"),
  contentHash: text("content_hash").notNull(),
  canonicalHash: text("canonical_hash").notNull(),
  semanticHash: text("semantic_hash"),
  etag: text("etag"),
  lastModified: text("last_modified"),
  storageKey: text("storage_key"),
  canonicalStorageKey: text("canonical_storage_key"),
  parserVersion: text("parser_version").notNull(),
  fetchDurationMs: integer("fetch_duration_ms"),
  fetchMethod: text("fetch_method"),
  mode: text("mode").notNull(),
  title: text("title"),
  publishedAt: ts("published_at"),
  extractionConfidence: real("extraction_confidence"),
  extra: jsonb("extra").$type<Record<string, unknown> | null>(),
});

export const changes = pgTable("changes", {
  id: text("id").primaryKey(),
  sensorId: text("sensor_id").notNull(),
  oldSnapshotId: text("old_snapshot_id"),
  newSnapshotId: text("new_snapshot_id").notNull(),
  detectedAt: ts("detected_at").notNull(),
  kind: text("kind").notNull(),
  diff: jsonb("diff").$type<Record<string, unknown>>().notNull(),
  diffStorageKey: text("diff_storage_key"),
  signal: real("signal").notNull(),
  noiseRatio: real("noise_ratio").notNull(),
  magnitude: real("magnitude").notNull(),
  heuristic: jsonb("heuristic").$type<Record<string, unknown>>().notNull(),
  meaningful: boolean("meaningful").notNull().default(false),
  eventId: text("event_id"),
  changeClass: text("change_class"),
  fieldChanges: jsonb("field_changes").$type<FieldChange[] | null>(),
});

/** A field-level difference extracted from a diff (price, number, date, version, status word…). */
export interface FieldChange {
  label: string;
  kind: "price" | "percent" | "number" | "date" | "version" | "status" | "text";
  before: string | null;
  after: string | null;
  /** relative change in % for numeric kinds */
  deltaPct?: number | null;
}

export const eventClusters = pgTable("event_clusters", {
  id: text("id").primaryKey(),
  slug: text("slug"),
  title: text("title").notNull(),
  summary: text("summary"),
  primaryEventId: text("primary_event_id"),
  entityIds: text("entity_ids").array().notNull().default([]),
  categories: text("categories").array().notNull().default([]),
  eventCount: integer("event_count").notNull().default(0),
  maxImportance: real("max_importance").notNull().default(0),
  firstAt: ts("first_at").notNull(),
  lastAt: ts("last_at").notNull(),
  sourceCount: integer("source_count").notNull().default(1),
  firstPartyCount: integer("first_party_count").notNull().default(0),
  externalCount: integer("external_count").notNull().default(0),
  velocity: real("velocity").notNull().default(0),
  state: text("state").notNull().default("watching"),
  leadTimeMs: bigint("lead_time_ms", { mode: "number" }),
  firstPartyAt: ts("first_party_at"),
  firstExternalAt: ts("first_external_at"),
  timeline: jsonb("timeline").$type<ClusterTimelineStep[]>().notNull().default([]),
});

export interface ClusterTimelineStep {
  at: string;
  eventId: string;
  sourceId: string;
  sourceName?: string;
  sensorType?: string;
  firstParty: boolean;
  eventType: string;
  importance: number;
}

export const events = pgTable("events", {
  id: text("id").primaryKey(),
  slug: text("slug").notNull(),
  sensorId: text("sensor_id").notNull(),
  sourceId: text("source_id").notNull(),
  clusterId: text("cluster_id"),
  changeId: text("change_id"),
  oldSnapshotId: text("old_snapshot_id"),
  newSnapshotId: text("new_snapshot_id"),
  url: text("url").notNull(),
  eventType: text("event_type").notNull(),
  title: text("title").notNull(),
  summary: text("summary").notNull(),
  whyItMatters: text("why_it_matters"),
  importance: real("importance").notNull(),
  importanceComponents: jsonb("importance_components").$type<Record<string, number>>().notNull().default({}),
  confidence: real("confidence").notNull(),
  novelty: real("novelty").notNull(),
  categories: text("categories").array().notNull().default([]),
  keywords: text("keywords").array().notNull().default([]),
  silentChange: boolean("silent_change").notNull().default(false),
  evidenceLabel: text("evidence_label").notNull().default("OBSERVED"),
  publishedAt: ts("published_at"),
  observedFrom: ts("observed_from"),
  detectedAt: ts("detected_at").notNull(),
  processedAt: ts("processed_at").notNull(),
  publishedToFeedAt: ts("published_to_feed_at"),
  detectionLatencyMs: integer("detection_latency_ms"),
  processingLatencyMs: integer("processing_latency_ms"),
  processingVersion: text("processing_version").notNull(),
  interpretation: jsonb("interpretation").$type<Record<string, unknown>>().notNull().default({}),
  fingerprint: text("fingerprint"),
  signalScore: real("signal_score"),
  velocityScore: real("velocity_score").notNull().default(0),
  impactScore: real("impact_score").notNull().default(0),
  anomalyScore: real("anomaly_score").notNull().default(0),
  changeClass: text("change_class"),
  firstParty: boolean("first_party").notNull().default(true),
  country: text("country"),
  language: text("language"),
  canonicalUrl: text("canonical_url"),
  fieldChanges: jsonb("field_changes").$type<FieldChange[] | null>(),
  scoreReasons: jsonb("score_reasons").$type<ScoreReason[]>().notNull().default([]),
});

export interface ScoreReason {
  /** + raises the score, - lowers it */
  sign: "+" | "-";
  text: string;
  /** contribution in points (approximate, for the transparency panel) */
  points?: number;
}

export const entityDaily = pgTable(
  "entity_daily",
  {
    entityId: text("entity_id").notNull(),
    day: date("day", { mode: "string" }).notNull(),
    events: integer("events").notNull().default(0),
    silent: integer("silent").notNull().default(0),
    breaking: integer("breaking").notNull().default(0),
    maxImportance: real("max_importance").notNull().default(0),
  },
  (t) => [primaryKey({ columns: [t.entityId, t.day] })],
);

export const sourceDaily = pgTable(
  "source_daily",
  {
    sourceId: text("source_id").notNull(),
    day: date("day", { mode: "string" }).notNull(),
    checks: integer("checks").notNull().default(0),
    notModified: integer("not_modified").notNull().default(0),
    errors: integer("errors").notNull().default(0),
    rawChanges: integer("raw_changes").notNull().default(0),
    events: integer("events").notNull().default(0),
  },
  (t) => [primaryKey({ columns: [t.sourceId, t.day] })],
);

export const bookmarks = pgTable(
  "bookmarks",
  {
    ownerToken: text("owner_token").notNull(),
    eventId: text("event_id").notNull(),
    note: text("note"),
    createdAt: ts("created_at").notNull().defaultNow(),
  },
  (t) => [primaryKey({ columns: [t.ownerToken, t.eventId] })],
);

export const savedViews = pgTable("saved_views", {
  id: text("id").primaryKey(),
  ownerToken: text("owner_token").notNull(),
  name: text("name").notNull(),
  query: text("query").notNull(),
  createdAt: ts("created_at").notNull().defaultNow(),
});

export const interpretations = pgTable("interpretations", {
  id: bigserial("id", { mode: "number" }).primaryKey(),
  eventId: text("event_id").notNull(),
  version: integer("version").notNull(),
  model: text("model").notNull(),
  payload: jsonb("payload").$type<Record<string, unknown>>().notNull(),
  createdAt: ts("created_at").notNull().defaultNow(),
});

export const entities = pgTable("entities", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  type: text("type").notNull(),
  description: text("description"),
  domain: text("domain"),
  homepage: text("homepage"),
  importance: real("importance").notNull().default(50),
  categories: text("categories").array().notNull().default([]),
  parentId: text("parent_id"),
  metadata: jsonb("metadata").$type<Record<string, unknown>>().notNull().default({}),
  eventCount: integer("event_count").notNull().default(0),
  lastEventAt: ts("last_event_at"),
  createdAt: ts("created_at").notNull().defaultNow(),
});

export const entityAliases = pgTable("entity_aliases", {
  alias: text("alias").primaryKey(),
  entityId: text("entity_id").notNull(),
});

export const eventEntities = pgTable(
  "event_entities",
  {
    eventId: text("event_id").notNull(),
    entityId: text("entity_id").notNull(),
    role: text("role").notNull().default("subject"),
  },
  (t) => [primaryKey({ columns: [t.eventId, t.entityId] })],
);

export const sourceEntities = pgTable(
  "source_entities",
  {
    sourceId: text("source_id").notNull(),
    entityId: text("entity_id").notNull(),
  },
  (t) => [primaryKey({ columns: [t.sourceId, t.entityId] })],
);

export const entityRelations = pgTable(
  "entity_relations",
  {
    fromId: text("from_id").notNull(),
    relation: text("relation").notNull(),
    toId: text("to_id").notNull(),
    metadata: jsonb("metadata").$type<Record<string, unknown>>().notNull().default({}),
  },
  (t) => [primaryKey({ columns: [t.fromId, t.relation, t.toId] })],
);

export const urls = pgTable("urls", {
  url: text("url").primaryKey(),
  domain: text("domain").notNull(),
  sourceId: text("source_id"),
  sensorId: text("sensor_id"),
  firstSeenAt: ts("first_seen_at").notNull().defaultNow(),
  lastSeenAt: ts("last_seen_at").notNull().defaultNow(),
  status: text("status").notNull().default("active"),
  missingCount: integer("missing_count").notNull().default(0),
  snapshotCount: integer("snapshot_count").notNull().default(0),
  changeCount: integer("change_count").notNull().default(0),
});

export const urlHistory = pgTable("url_history", {
  id: bigserial("id", { mode: "number" }).primaryKey(),
  url: text("url").notNull(),
  at: ts("at").notNull().defaultNow(),
  kind: text("kind").notNull(),
  snapshotId: text("snapshot_id"),
  changeId: text("change_id"),
  eventId: text("event_id"),
  note: text("note"),
});

export const watchlists = pgTable("watchlists", {
  id: text("id").primaryKey(),
  ownerToken: text("owner_token").notNull(),
  name: text("name").notNull(),
  createdAt: ts("created_at").notNull().defaultNow(),
});

export const watchlistItems = pgTable(
  "watchlist_items",
  {
    watchlistId: text("watchlist_id").notNull(),
    kind: text("kind").notNull(),
    value: text("value").notNull(),
    addedAt: ts("added_at").notNull().defaultNow(),
  },
  (t) => [primaryKey({ columns: [t.watchlistId, t.kind, t.value] })],
);

export const alerts = pgTable("alerts", {
  id: text("id").primaryKey(),
  ownerToken: text("owner_token").notNull(),
  name: text("name").notNull(),
  rule: jsonb("rule").$type<Record<string, unknown>>().notNull(),
  channel: text("channel").notNull().default("web"),
  channelConfig: jsonb("channel_config").$type<Record<string, unknown>>().notNull().default({}),
  enabled: boolean("enabled").notNull().default(true),
  createdAt: ts("created_at").notNull().defaultNow(),
  lastFiredAt: ts("last_fired_at"),
  firedCount: integer("fired_count").notNull().default(0),
});

export const notifications = pgTable("notifications", {
  id: bigserial("id", { mode: "number" }).primaryKey(),
  alertId: text("alert_id").notNull(),
  eventId: text("event_id").notNull(),
  createdAt: ts("created_at").notNull().defaultNow(),
  readAt: ts("read_at"),
  channel: text("channel").notNull().default("web"),
  status: text("status").notNull().default("queued"),
  deliveredAt: ts("delivered_at"),
  error: text("error"),
});

export const connectorHealth = pgTable("connector_health", {
  connector: text("connector").primaryKey(),
  status: text("status").notNull().default("UP"),
  runs24h: integer("runs_24h").notNull().default(0),
  errors24h: integer("errors_24h").notNull().default(0),
  successRate: real("success_rate"),
  avgLatencyMs: integer("avg_latency_ms"),
  changes24h: integer("changes_24h").notNull().default(0),
  events24h: integer("events_24h").notNull().default(0),
  lastSuccessAt: ts("last_success_at"),
  lastErrorAt: ts("last_error_at"),
  lastError: text("last_error"),
  httpCodes: jsonb("http_codes").$type<Record<string, number>>().notNull().default({}),
  rateLimitUntil: ts("rate_limit_until"),
  updatedAt: ts("updated_at").notNull().defaultNow(),
});

export const discoveryCandidates = pgTable("discovery_candidates", {
  id: text("id").primaryKey(),
  sourceId: text("source_id").notNull(),
  url: text("url").notNull(),
  kind: text("kind").notNull(),
  evidence: text("evidence"),
  score: jsonb("score").$type<Record<string, unknown>>().notNull().default({}),
  status: text("status").notNull().default("candidate"),
  foundAt: ts("found_at").notNull().defaultNow(),
  seedId: text("seed_id"),
  connector: text("connector"),
  type: text("type"),
  name: text("name"),
  kindClass: text("kind_class"),
  config: jsonb("config").$type<Record<string, unknown>>().notNull().default({}),
  tier: text("tier"),
  scoreValue: real("score_value"),
  reason: text("reason"),
  shadowSensorId: text("shadow_sensor_id"),
  decidedAt: ts("decided_at"),
  updatedAt: ts("updated_at").notNull().defaultNow(),
});

export const factorySeeds = pgTable("factory_seeds", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  domain: text("domain").notNull(),
  homepage: text("homepage"),
  categories: text("categories").array().notNull().default([]),
  country: text("country"),
  language: text("language"),
  tier: text("tier").notNull().default("B"),
  weight: real("weight").notNull().default(1),
  importance: smallint("importance").notNull().default(2),
  aliases: text("aliases").array().notNull().default([]),
  firstParty: boolean("first_party").notNull().default(true),
  sector: text("sector"),
  universe: text("universe"),
  hints: jsonb("hints").$type<Record<string, unknown>>().notNull().default({}),
  status: text("status").notNull().default("queued"),
  attempts: integer("attempts").notNull().default(0),
  sourceId: text("source_id"),
  candidates: integer("candidates").notNull().default(0),
  shadow: integer("shadow").notNull().default(0),
  accepted: integer("accepted").notNull().default(0),
  rejected: integer("rejected").notNull().default(0),
  lastError: text("last_error"),
  discoveredAt: ts("discovered_at"),
  createdAt: ts("created_at").notNull().defaultNow(),
  updatedAt: ts("updated_at").notNull().defaultNow(),
});
export type FactorySeed = typeof factorySeeds.$inferSelect;
export type DiscoveryCandidate = typeof discoveryCandidates.$inferSelect;

export const metricsDaily = pgTable("metrics_daily", {
  day: date("day", { mode: "string" }).primaryKey(),
  checks: bigint("checks", { mode: "number" }).notNull().default(0),
  notModified: bigint("not_modified", { mode: "number" }).notNull().default(0),
  bytes: bigint("bytes", { mode: "number" }).notNull().default(0),
  rawChanges: bigint("raw_changes", { mode: "number" }).notNull().default(0),
  events: bigint("events", { mode: "number" }).notNull().default(0),
  silentEvents: bigint("silent_events", { mode: "number" }).notNull().default(0),
  errors: bigint("errors", { mode: "number" }).notNull().default(0),
  llmCalls: bigint("llm_calls", { mode: "number" }).notNull().default(0),
  llmInputTokens: bigint("llm_input_tokens", { mode: "number" }).notNull().default(0),
  llmOutputTokens: bigint("llm_output_tokens", { mode: "number" }).notNull().default(0),
  scrapflyCalls: bigint("scrapfly_calls", { mode: "number" }).notNull().default(0),
});

export const llmUsage = pgTable("llm_usage", {
  id: bigserial("id", { mode: "number" }).primaryKey(),
  at: ts("at").notNull().defaultNow(),
  model: text("model").notNull(),
  purpose: text("purpose").notNull(),
  inputTokens: integer("input_tokens").notNull(),
  outputTokens: integer("output_tokens").notNull(),
  eventId: text("event_id"),
  ok: boolean("ok").notNull().default(true),
});

export type Source = typeof sources.$inferSelect;
export type Sensor = typeof sensors.$inferSelect;
export type Snapshot = typeof snapshots.$inferSelect;
export type Change = typeof changes.$inferSelect;
export type Event = typeof events.$inferSelect;
export type EventCluster = typeof eventClusters.$inferSelect;
export type Entity = typeof entities.$inferSelect;