-- 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
);
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)
);