-- integrations: squashed from 1200_integrations.sql, 1201_integrations_hardening.sql.

-- Third-party integrations (Slack, Google Chat, Linear, GitHub, generic webhooks) scoped to a repo or an org.
-- Credentials live AES-GCM encrypted in `credentials_ciphertext`; `config` only holds non-secret settings.
CREATE TABLE integrations (
  id TEXT PRIMARY KEY,
  scope_type TEXT NOT NULL CHECK (scope_type IN ('repo', 'org')),
  scope_id TEXT NOT NULL,
  kind TEXT NOT NULL CHECK (kind IN ('slack', 'google_chat', 'linear', 'github', 'webhook')),
  name TEXT NOT NULL,
  config TEXT NOT NULL DEFAULT '{}',
  events TEXT NOT NULL DEFAULT '[]',
  delivery_mode TEXT NOT NULL DEFAULT 'instant' CHECK (delivery_mode IN ('instant', 'digest')),
  digest_minutes INTEGER NOT NULL DEFAULT 60,
  last_digest_at INTEGER NOT NULL DEFAULT 0,
  credentials_ciphertext TEXT,
  credentials_iv TEXT,
  active INTEGER NOT NULL DEFAULT 1,
  created_by TEXT REFERENCES accounts(id) ON DELETE SET NULL,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);

CREATE INDEX integrations_scope ON integrations(scope_type, scope_id, active);

CREATE INDEX integrations_digest ON integrations(delivery_mode, active, last_digest_at);

CREATE TABLE integration_deliveries (
  id TEXT PRIMARY KEY,
  integration_id TEXT NOT NULL REFERENCES integrations(id) ON DELETE CASCADE,
  direction TEXT NOT NULL DEFAULT 'outbound' CHECK (direction IN ('outbound', 'inbound')),
  event TEXT NOT NULL,
  repo_id TEXT,
  status TEXT NOT NULL CHECK (status IN ('pending', 'success', 'failed', 'dead')),
  status_code INTEGER,
  error TEXT,
  payload TEXT NOT NULL,
  response_body TEXT,
  duration_ms INTEGER,
  attempt INTEGER NOT NULL DEFAULT 0,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  lease_until INTEGER,
  lease_owner TEXT,
  terminal INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX integration_deliveries_integration ON integration_deliveries(integration_id, created_at DESC);

CREATE TABLE integration_digest_items (
  id TEXT PRIMARY KEY,
  integration_id TEXT NOT NULL REFERENCES integrations(id) ON DELETE CASCADE,
  repo_id TEXT NOT NULL,
  event TEXT NOT NULL,
  payload TEXT NOT NULL,
  created_at INTEGER NOT NULL
);

CREATE INDEX integration_digest_items_integration ON integration_digest_items(integration_id, created_at);

CREATE TABLE linear_issue_links (
  integration_id TEXT NOT NULL REFERENCES integrations(id) ON DELETE CASCADE,
  repo_id TEXT NOT NULL,
  pull_number INTEGER NOT NULL,
  issue_key TEXT NOT NULL,
  linear_issue_id TEXT,
  created_at INTEGER NOT NULL,
  PRIMARY KEY (integration_id, repo_id, pull_number, issue_key)
);

CREATE TABLE integration_jobs (
  id TEXT PRIMARY KEY,
  integration_id TEXT NOT NULL REFERENCES integrations(id) ON DELETE CASCADE,
  kind TEXT NOT NULL CHECK (kind IN ('github_import', 'github_mirror')),
  status TEXT NOT NULL CHECK (status IN ('queued', 'running', 'done', 'failed')),
  progress TEXT NOT NULL DEFAULT '{}',
  error TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);

CREATE INDEX integration_jobs_integration ON integration_jobs(integration_id, created_at DESC);

-- One row per imported GitHub issue/PR so retried pages and repeated imports never create duplicates.
CREATE TABLE integration_import_items (
  integration_id TEXT NOT NULL REFERENCES integrations(id) ON DELETE CASCADE,
  source_number INTEGER NOT NULL,
  issue_id TEXT NOT NULL,
  comments_done INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (integration_id, source_number)
);

CREATE TABLE integration_delivery_targets (
  delivery_id TEXT NOT NULL REFERENCES integration_deliveries(id) ON DELETE CASCADE,
  target TEXT NOT NULL,
  status_code INTEGER,
  created_at INTEGER NOT NULL,
  PRIMARY KEY (delivery_id, target)
);

CREATE TABLE integration_audit (
  id TEXT PRIMARY KEY,
  integration_id TEXT NOT NULL REFERENCES integrations(id) ON DELETE CASCADE,
  actor_id TEXT REFERENCES accounts(id) ON DELETE SET NULL,
  action TEXT NOT NULL,
  detail TEXT NOT NULL DEFAULT '{}',
  created_at INTEGER NOT NULL
);

CREATE INDEX integration_audit_integration ON integration_audit(integration_id, created_at DESC);

CREATE TABLE integration_usage_daily (
  owner_id TEXT NOT NULL,
  day TEXT NOT NULL,
  kind TEXT NOT NULL,
  deliveries INTEGER NOT NULL DEFAULT 0,
  failures INTEGER NOT NULL DEFAULT 0,
  bytes INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (owner_id, day, kind)
);

-- One row per metered attempt, so a redelivered queue message never counts twice. Pruned after 40 days.
CREATE TABLE integration_usage_keys (
  key TEXT PRIMARY KEY,
  created_at INTEGER NOT NULL
);

CREATE INDEX integration_usage_keys_created ON integration_usage_keys(created_at);