-- Monitoring + notification subsystem (see docs/monitoring-design.md).
--
-- This migration is the consolidation of what were originally migrations
-- 003_monitoring .. 010_notification_rules. Existing databases already recorded
-- those versions in schema_migrations and have this schema, so they skip this
-- file; only fresh databases run it (top to bottom). Columns that were once
-- added by later ALTERs are folded into their CREATE TABLE definitions here.
--
-- Standalone / project-free (SSH-broker model): monitor scope is 'global'
-- (org-wide, admin) or 'personal' (owner-only, private). Numeric FKs internally,
-- public_id at the API boundary. Sharing of global monitors is via
-- monitor_targets; alert routing is via notification_rules + channels.

-- ---------------------------------------------------------------------------
-- Monitors + status + heartbeat history (orig. 003; facets from 004; cert
-- fields from 006; cert_issuer_known from 009)
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS monitors (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  scope TEXT NOT NULL DEFAULT 'global',
  owner_user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  type TEXT NOT NULL,
  target TEXT NOT NULL DEFAULT '',
  interval_sec INTEGER NOT NULL DEFAULT 60,
  timeout_sec INTEGER NOT NULL DEFAULT 10,
  enabled INTEGER NOT NULL DEFAULT 1,
  failure_threshold INTEGER NOT NULL DEFAULT 1,
  config TEXT NOT NULL DEFAULT '{}',
  created_at INTEGER NOT NULL DEFAULT 0,
  updated_at INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS monitor_status (
  monitor_id INTEGER PRIMARY KEY REFERENCES monitors(id) ON DELETE CASCADE,
  state TEXT NOT NULL DEFAULT 'pending',
  running INTEGER NOT NULL DEFAULT 0,
  last_check_at INTEGER NOT NULL DEFAULT 0,
  next_check_at INTEGER NOT NULL DEFAULT 0,
  last_latency_ms INTEGER NOT NULL DEFAULT 0,
  last_error TEXT NOT NULL DEFAULT '',
  consecutive_failures INTEGER NOT NULL DEFAULT 0,
  last_state_change_at INTEGER NOT NULL DEFAULT 0,
  cert_expires_at INTEGER NOT NULL DEFAULT 0,
  cert_subject TEXT NOT NULL DEFAULT '',
  cert_issuer TEXT NOT NULL DEFAULT '',
  cert_sans TEXT NOT NULL DEFAULT '',
  cert_host_mismatch INTEGER NOT NULL DEFAULT 0,
  cert_issuer_known INTEGER NOT NULL DEFAULT 0,
  resolved_value TEXT NOT NULL DEFAULT '',
  updated_at INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS monitor_checks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  monitor_id INTEGER NOT NULL REFERENCES monitors(id) ON DELETE CASCADE,
  started_at INTEGER NOT NULL DEFAULT 0,
  finished_at INTEGER NOT NULL DEFAULT 0,
  ok INTEGER NOT NULL DEFAULT 0,
  state TEXT NOT NULL DEFAULT '',
  latency_ms INTEGER NOT NULL DEFAULT 0,
  message TEXT NOT NULL DEFAULT '',
  facets TEXT NOT NULL DEFAULT '[]'
);

CREATE INDEX IF NOT EXISTS idx_monitors_scope_user ON monitors(scope, owner_user_id);
CREATE INDEX IF NOT EXISTS idx_monitor_status_due ON monitor_status(running, next_check_at);
CREATE INDEX IF NOT EXISTS idx_monitor_checks_monitor_started ON monitor_checks(monitor_id, started_at DESC);

-- ---------------------------------------------------------------------------
-- Principal-target sharing for global monitors (orig. 007)
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS monitor_targets (
  monitor_id INTEGER NOT NULL REFERENCES monitors(id) ON DELETE CASCADE,
  principal_kind TEXT NOT NULL,          -- 'user' | 'group'
  principal_id INTEGER NOT NULL,         -- users(id) or user_groups(id) per kind
  access TEXT NOT NULL DEFAULT 'view',   -- 'view' | 'manage'
  created_at INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (monitor_id, principal_kind, principal_id)
);
CREATE INDEX IF NOT EXISTS idx_monitor_targets_principal ON monitor_targets(principal_kind, principal_id);

-- ---------------------------------------------------------------------------
-- Generic event / notification feed (orig. 005; notified_at outbox marker from
-- 010). Monitoring is the first producer; other subsystems emit the same shape.
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  kind TEXT NOT NULL,
  severity TEXT NOT NULL DEFAULT 'info',
  scope TEXT NOT NULL DEFAULT 'global',
  owner_user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  owner_project_id INTEGER REFERENCES projects(id) ON DELETE CASCADE,
  source_type TEXT NOT NULL DEFAULT '',
  source_id TEXT NOT NULL DEFAULT '',
  title TEXT NOT NULL DEFAULT '',
  body TEXT NOT NULL DEFAULT '',
  payload TEXT NOT NULL DEFAULT '{}',
  dedup_key TEXT NOT NULL DEFAULT '',
  created_at INTEGER NOT NULL DEFAULT 0,
  notified_at INTEGER NOT NULL DEFAULT 0
);

-- Per-user read marker for the in-app feed.
CREATE TABLE IF NOT EXISTS event_reads (
  user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  last_read_at INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX IF NOT EXISTS idx_events_created ON events(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_events_scope_project_created ON events(scope, owner_project_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_events_scope_user_created ON events(scope, owner_user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_events_notified_at ON events(notified_at);

-- ---------------------------------------------------------------------------
-- Notification transports + channels + monitor attachment (orig. 008)
--   Layer 1 transport_providers   — the mechanism + server config (admin)
--   Layer 3 notification_channels — named, scoped, selectable objects
--   monitor_channels              — monitor <-> channel (manager-assigned, m:n)
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS transport_providers (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id  TEXT NOT NULL UNIQUE,
  name       TEXT NOT NULL,
  type       TEXT NOT NULL,                  -- 'webhook' | 'smtp' | 'microsoft_graph' | 'ms_teams' | 'slack'
  enabled    INTEGER NOT NULL DEFAULT 1,
  config     TEXT NOT NULL DEFAULT '{}',     -- non-secret config JSON
  secret     TEXT NOT NULL DEFAULT '',       -- encrypted secret blob, never returned (json:"-")
  created_at INTEGER NOT NULL DEFAULT 0,
  updated_at INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS notification_channels (
  id                    INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id             TEXT NOT NULL UNIQUE,
  name                  TEXT NOT NULL,
  scope                 TEXT NOT NULL DEFAULT 'global',   -- 'global' | 'personal'
  owner_user_id         INTEGER REFERENCES users(id) ON DELETE CASCADE,
  enabled               INTEGER NOT NULL DEFAULT 1,
  transport_provider_id INTEGER NOT NULL REFERENCES transport_providers(id) ON DELETE CASCADE,
  target_kind           TEXT NOT NULL DEFAULT 'literal',  -- 'literal' | 'user' | 'group'
  address               TEXT NOT NULL DEFAULT '',         -- literal destination
  principal_id          INTEGER,                          -- users(id)/user_groups(id) when target_kind != 'literal'
  created_at            INTEGER NOT NULL DEFAULT 0,
  updated_at            INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_notification_channels_owner ON notification_channels(owner_user_id);

CREATE TABLE IF NOT EXISTS monitor_channels (
  monitor_id INTEGER NOT NULL REFERENCES monitors(id) ON DELETE CASCADE,
  channel_id INTEGER NOT NULL REFERENCES notification_channels(id) ON DELETE CASCADE,
  created_at INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (monitor_id, channel_id)
);
CREATE INDEX IF NOT EXISTS idx_monitor_channels_channel ON monitor_channels(channel_id);

-- ---------------------------------------------------------------------------
-- Attribute-matched alert routing (orig. 010). Project/personal rules are OWNED
-- by that principal and cascade-delete with it (real FKs).
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS notification_rules (
  id               INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id        TEXT NOT NULL UNIQUE,
  name             TEXT NOT NULL,
  scope            TEXT NOT NULL DEFAULT 'global',   -- 'global' | 'project'
  owner_project_id INTEGER REFERENCES projects(id) ON DELETE CASCADE,
  enabled          INTEGER NOT NULL DEFAULT 1,
  kinds            TEXT NOT NULL DEFAULT '',          -- comma-separated kinds/prefixes ('repo.mirror_failed', 'repo.*'); empty = all
  min_severity     TEXT NOT NULL DEFAULT 'warning',   -- 'info' | 'warning' | 'critical'
  created_at       INTEGER NOT NULL DEFAULT 0,
  updated_at       INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_notification_rules_scope ON notification_rules(scope);
CREATE INDEX IF NOT EXISTS idx_notification_rules_owner_project ON notification_rules(owner_project_id);

CREATE TABLE IF NOT EXISTS notification_rule_channels (
  rule_id    INTEGER NOT NULL REFERENCES notification_rules(id) ON DELETE CASCADE,
  channel_id INTEGER NOT NULL REFERENCES notification_channels(id) ON DELETE CASCADE,
  created_at INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (rule_id, channel_id)
);
CREATE INDEX IF NOT EXISTS idx_notification_rule_channels_channel ON notification_rule_channels(channel_id);