-- Cloud Codex - Database Schema
--
-- All Rights Reserved to Cloud City Computing, LLC 2026
-- https://cloudcitycomputing.com

-- SQL script to initialize the database schema.
-- This script creates the schema for workspaces, squads, users, archives, logs, versions, and permissions.
-- It defines the necessary tables and their relationships.

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS webhook_deliveries, webhook_events, webhook_sequence_lock, webhook_subscriptions, doc_images;
DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS watches;
DROP TABLE IF EXISTS activity_log;
DROP TABLE IF EXISTS github_links;
DROP TABLE IF EXISTS favorites;
DROP TABLE IF EXISTS comment_replies;
DROP TABLE IF EXISTS comments;
DROP TABLE IF EXISTS squad_invitations;
DROP TABLE IF EXISTS squad_members;
DROP TABLE IF EXISTS versions;
DROP TABLE IF EXISTS logs;
DROP TABLE IF EXISTS github_pr_sessions;
DROP TABLE IF EXISTS github_embed_refs;
DROP TABLE IF EXISTS archive_repos;
DROP TABLE IF EXISTS archives;
DROP TABLE IF EXISTS squad_permissions;
DROP TABLE IF EXISTS permissions;
DROP TABLE IF EXISTS squads;
DROP TABLE IF EXISTS two_factor_codes;
DROP TABLE IF EXISTS password_reset_tokens;
DROP TABLE IF EXISTS user_invitations;
DROP TABLE IF EXISTS oauth_accounts;
DROP TABLE IF EXISTS sessions;
DROP TABLE IF EXISTS user_identities;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS workspaces;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE workspaces (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name TEXT NOT NULL,
  owner_id INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(32) NOT NULL UNIQUE,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash TEXT,
  avatar_url VARCHAR(512) DEFAULT NULL,
  two_factor_method ENUM('none', 'email', 'totp') DEFAULT 'none',
  totp_secret VARCHAR(64) DEFAULT NULL,
  is_admin BOOLEAN DEFAULT FALSE,
  notification_prefs JSON DEFAULT NULL,
  onboarded_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  deactivated_at TIMESTAMP NULL DEFAULT NULL
) ENGINE=InnoDB;

-- workspaces is declared before users, so its owner FK is added here rather
-- than inline, where it would reference a table that does not exist yet.
-- ON DELETE SET NULL: deleting a user must not delete their workspace. It
-- leaves the workspace ownerless, which only stops clause 4 of the access
-- fragments from matching; squad membership and the per-squad and
-- workspace-wide grants still reach everything in it.
ALTER TABLE workspaces
  ADD CONSTRAINT fk_workspaces_owner
  FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE SET NULL;

CREATE TABLE oauth_accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  provider ENUM('google', 'github') NOT NULL,
  provider_user_id VARCHAR(255) NOT NULL,
  provider_email VARCHAR(255) NOT NULL,
  provider_username VARCHAR(255) DEFAULT NULL,
  provider_avatar_url VARCHAR(500) DEFAULT NULL,
  encrypted_token TEXT DEFAULT NULL,
  token_status ENUM('active','revoked','unknown') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  -- A provider account belongs to at most one user, and a user holds at most
  -- one account per provider (migrations/2026-09-25-oauth-one-link-per-provider.sql).
  UNIQUE KEY uq_provider_user (provider, provider_user_id),
  UNIQUE KEY uq_oauth_user_provider (user_id, provider),
  INDEX (user_id)
) ENGINE=InnoDB;

-- One identity per (issuer, sub), linking a local user to an OIDC issuer's
-- subject (W6-CDX-5; migrations/2026-10-11-user-identities.sql). utf8mb4_bin
-- because a sub is case-sensitive. UNIQUE (user_id, issuer) makes "one identity
-- per issuer per user" a database fact.
CREATE TABLE user_identities (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  issuer VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  subject VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  email_at_link VARCHAR(255) NOT NULL,
  email_at_last_signin VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  last_login_at TIMESTAMP NULL DEFAULT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uq_user_identities_issuer_subject (issuer, subject),
  UNIQUE KEY uq_user_identities_user_issuer (user_id, issuer)
) ENGINE=InnoDB;

CREATE TABLE sessions (
  -- The SHA-256 digest of the session token, lowercase hex, never the token
  -- itself (hashSessionToken in cloudcodex/services/session-token.js): a dump
  -- of this table yields nothing a browser can present. One row per sign-in.
  id CHAR(64) PRIMARY KEY,
  user_id INT NOT NULL,
  -- Which flow minted this session. NOT NULL with no DEFAULT, so a flow that
  -- forgets to name itself fails at insert (error 1364); VARCHAR + CHECK, not
  -- ENUM, for password_reset_tokens.purpose's reason below. Keep the CHECK in
  -- sync with the newest migration that sets it
  -- (migrations/2026-10-11-user-identities.sql).
  auth_provider VARCHAR(16) NOT NULL,
  -- The identity an OIDC sign-in used, and the issuer's sid for back-channel
  -- logout (migrations/2026-10-11-user-identities.sql). Both NULL for local and
  -- Google sessions. chk_sessions_identity_pairing keeps 'oidc' and identity_id
  -- together.
  identity_id INT NULL,
  provider_sid VARCHAR(255) NULL,
  -- The sealed ID token a sign-out sends as id_token_hint (migrations/2026-10-13-session-id-token.sql,
  -- services/id-token-seal.js). NULL for local and Google sessions, and for OIDC sessions signed in
  -- before it. Never the token itself.
  oidc_id_token TEXT NULL,
  ip_address VARCHAR(45),
  user_agent TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  -- When the person signed in. A rotation keeps it (migrations/2026-10-14-session-rotation.sql), so the
  -- back-channel bound measures the sign-in, not the replacement's created_at.
  signed_in_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  -- The digest of the session this one replaced; a logout presenting the old token ends it too.
  rotated_from CHAR(64) NULL,
  last_active_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  expires_at TIMESTAMP NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_sessions_rotated_from (rotated_from),
  CONSTRAINT chk_sessions_auth_provider CHECK (auth_provider IN ('local', 'google', 'oidc')),
  CONSTRAINT chk_sessions_identity_pairing CHECK ((auth_provider = 'oidc') = (identity_id IS NOT NULL)),
  CONSTRAINT chk_sessions_id_token_oidc_only CHECK (oidc_id_token IS NULL OR auth_provider = 'oidc'),
  CONSTRAINT fk_sessions_identity FOREIGN KEY (identity_id) REFERENCES user_identities(id) ON DELETE CASCADE,
  INDEX (user_id),
  INDEX (expires_at),
  INDEX idx_sessions_provider_sid (provider_sid)
) ENGINE=InnoDB;

CREATE TABLE password_reset_tokens (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  token CHAR(64) NOT NULL UNIQUE,
  -- Which of the five flows minted this row. NOT NULL with no DEFAULT so a
  -- sixth flow that forgets to name its purpose fails at insert (error 1364)
  -- instead of silently minting a password reset token.
  --
  -- Deliberately VARCHAR + CHECK and not ENUM: MySQL gives a NOT NULL ENUM
  -- with no DEFAULT an implicit default of the FIRST enumerated value even
  -- under STRICT_TRANS_TABLES, so an omitted purpose would silently become
  -- 'password_reset', which is the exact defect this column exists to close.
  --
  -- Keep in sync with TOKEN_PURPOSE in cloudcodex/routes/helpers/shared.js
  -- and with the newest migration that sets the CHECK
  -- (migrations/2026-09-25-token-purpose-email-change.sql).
  purpose VARCHAR(32) NOT NULL,
  -- The address an email_change row confirms, and set on no other purpose
  -- (chk_password_reset_tokens_new_email), so a confirmation applies exactly
  -- the address its code was sent for.
  new_email VARCHAR(255) NULL,
  expires_at TIMESTAMP NOT NULL,
  used BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT chk_password_reset_tokens_purpose
    CHECK (purpose IN ('password_reset','two_factor_login','totp_setup','two_factor_disable','email_change')),
  CONSTRAINT chk_password_reset_tokens_new_email
    CHECK ((purpose = 'email_change') = (new_email IS NOT NULL)),
  INDEX (token),
  INDEX (expires_at)
) ENGINE=InnoDB;

CREATE TABLE two_factor_codes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  code CHAR(6) NOT NULL,
  expires_at TIMESTAMP NOT NULL,
  used BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX (user_id),
  INDEX (expires_at)
) ENGINE=InnoDB;

CREATE TABLE user_invitations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  token CHAR(64) NOT NULL UNIQUE,
  invited_by INT NOT NULL,
  squad_id INT NULL,
  role ENUM('member', 'admin', 'owner') DEFAULT 'member',
  can_read BOOLEAN DEFAULT TRUE,
  can_write BOOLEAN DEFAULT FALSE,
  can_create_log BOOLEAN DEFAULT FALSE,
  can_create_archive BOOLEAN DEFAULT FALSE,
  can_manage_members BOOLEAN DEFAULT FALSE,
  can_delete_version BOOLEAN DEFAULT FALSE,
  can_publish BOOLEAN DEFAULT FALSE,
  expires_at TIMESTAMP NOT NULL,
  accepted BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (invited_by) REFERENCES users(id) ON DELETE CASCADE,
  INDEX (token),
  INDEX (email),
  INDEX (expires_at)
) ENGINE=InnoDB;

CREATE TABLE squads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT,
  name TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_by INT,
  github_org VARCHAR(255) DEFAULT NULL,
  github_team_slug VARCHAR(255) DEFAULT NULL,
  team_sync_at TIMESTAMP NULL DEFAULT NULL,
  FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  UNIQUE KEY uq_squad_team (github_org, github_team_slug)
) ENGINE=InnoDB;

-- user_invitations is declared before squads, so its squad FK is added here
-- rather than inline, where it would reference a table that does not exist yet.
ALTER TABLE user_invitations
  ADD CONSTRAINT fk_user_invitations_squad
  FOREIGN KEY (squad_id) REFERENCES squads(id) ON DELETE SET NULL;

CREATE TABLE permissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  create_squad BOOLEAN DEFAULT FALSE,
  create_archive BOOLEAN DEFAULT FALSE,
  create_log BOOLEAN DEFAULT TRUE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE squad_members (
  id INT AUTO_INCREMENT PRIMARY KEY,
  squad_id INT NOT NULL,
  user_id INT NOT NULL,
  role ENUM('member', 'admin', 'owner') DEFAULT 'member',
  can_read BOOLEAN DEFAULT TRUE,
  can_write BOOLEAN DEFAULT FALSE,
  can_create_log BOOLEAN DEFAULT FALSE,
  can_create_archive BOOLEAN DEFAULT FALSE,
  can_manage_members BOOLEAN DEFAULT FALSE,
  can_delete_version BOOLEAN DEFAULT FALSE,
  can_publish BOOLEAN DEFAULT FALSE,
  joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (squad_id) REFERENCES squads(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uq_squad_user (squad_id, user_id)
) ENGINE=InnoDB;

CREATE TABLE squad_invitations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  squad_id INT NOT NULL,
  invited_by INT NOT NULL,
  invited_user_id INT NOT NULL,
  role ENUM('member', 'admin', 'owner') DEFAULT 'member',
  can_read BOOLEAN DEFAULT TRUE,
  can_write BOOLEAN DEFAULT FALSE,
  can_create_log BOOLEAN DEFAULT FALSE,
  can_create_archive BOOLEAN DEFAULT FALSE,
  can_manage_members BOOLEAN DEFAULT FALSE,
  can_delete_version BOOLEAN DEFAULT FALSE,
  can_publish BOOLEAN DEFAULT FALSE,
  status ENUM('pending', 'accepted', 'declined') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  responded_at TIMESTAMP NULL,
  FOREIGN KEY (squad_id) REFERENCES squads(id) ON DELETE CASCADE,
  FOREIGN KEY (invited_by) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (invited_user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uq_invitation (squad_id, invited_user_id, status)
) ENGINE=InnoDB;

CREATE TABLE archives (
  id INT AUTO_INCREMENT PRIMARY KEY,
  squad_id INT,
  name TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_by INT,
  read_access JSON DEFAULT (JSON_ARRAY()),
  write_access JSON DEFAULT (JSON_ARRAY()),
  read_access_squads JSON DEFAULT (JSON_ARRAY()),
  write_access_squads JSON DEFAULT (JSON_ARRAY()),
  read_access_workspace BOOLEAN DEFAULT FALSE,
  write_access_workspace BOOLEAN DEFAULT FALSE,
  `system` BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (squad_id) REFERENCES squads(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_archives_system (`system`)
) ENGINE=InnoDB;

CREATE TABLE archive_repos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  archive_id INT NOT NULL,
  repo_full_name VARCHAR(255) NOT NULL,
  repo_owner VARCHAR(255) NOT NULL,
  repo_name VARCHAR(255) NOT NULL,
  default_branch VARCHAR(255) DEFAULT 'main',
  docs_path VARCHAR(500) DEFAULT 'docs',
  auto_link_imports BOOLEAN DEFAULT TRUE,
  linked_by INT NOT NULL,
  linked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (archive_id) REFERENCES archives(id) ON DELETE CASCADE,
  FOREIGN KEY (linked_by) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uq_archive_repo (archive_id, repo_full_name),
  INDEX (archive_id)
) ENGINE=InnoDB;

CREATE TABLE logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  archive_id INT,
  title TEXT NOT NULL,
  html_content MEDIUMTEXT,
  markdown_content MEDIUMTEXT DEFAULT NULL,
  ydoc_state LONGBLOB DEFAULT NULL,
  plain_content MEDIUMTEXT GENERATED ALWAYS AS (REGEXP_REPLACE(html_content, '<[^>]+>', '')) STORED,
  parent_id INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_by INT,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_by INT,
  version INT DEFAULT 0,
  read_access JSON DEFAULT (JSON_ARRAY()),
  write_access JSON DEFAULT (JSON_ARRAY()),
  FULLTEXT INDEX ft_logs_search (title, plain_content),
  FOREIGN KEY (archive_id) REFERENCES archives(id) ON DELETE CASCADE,
  FOREIGN KEY (parent_id) REFERENCES logs(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE github_embed_refs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  log_id INT NOT NULL,
  embed_type ENUM('code','issue','pr','file') NOT NULL,
  repo_owner VARCHAR(255) NOT NULL,
  repo_name VARCHAR(255) NOT NULL,
  ref_value VARCHAR(500) NOT NULL,
  pinned_sha VARCHAR(64) DEFAULT NULL,
  branch VARCHAR(255) DEFAULT NULL,
  last_seen_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (log_id) REFERENCES logs(id) ON DELETE CASCADE,
  INDEX idx_embed_log (log_id),
  INDEX idx_embed_repo (repo_owner, repo_name, embed_type)
) ENGINE=InnoDB;

CREATE TABLE github_pr_sessions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  log_id INT NOT NULL,
  repo_owner VARCHAR(255) NOT NULL,
  repo_name VARCHAR(255) NOT NULL,
  pr_number INT NOT NULL,
  opened_by INT NOT NULL,
  opened_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (log_id) REFERENCES logs(id) ON DELETE CASCADE,
  FOREIGN KEY (opened_by) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uq_pr_session (repo_owner, repo_name, pr_number),
  INDEX idx_pr_session_log (log_id)
) ENGINE=InnoDB;

CREATE TABLE github_links (
  id INT AUTO_INCREMENT PRIMARY KEY,
  log_id INT NOT NULL,
  repo_owner VARCHAR(255) NOT NULL,
  repo_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(500) NOT NULL,
  branch VARCHAR(255) NOT NULL,
  file_sha VARCHAR(64) DEFAULT NULL,
  base_sha VARCHAR(64) DEFAULT NULL,
  last_pulled_at TIMESTAMP NULL DEFAULT NULL,
  last_pushed_at TIMESTAMP NULL DEFAULT NULL,
  sync_status ENUM('clean','remote_ahead','local_ahead','diverged','conflict') DEFAULT 'clean',
  linked_by INT NOT NULL,
  linked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (log_id) REFERENCES logs(id) ON DELETE CASCADE,
  FOREIGN KEY (linked_by) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uq_log_link (log_id),
  INDEX idx_links_status (sync_status)
) ENGINE=InnoDB;

CREATE TABLE versions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  log_id INT,
  version INT NOT NULL,
  title VARCHAR(255) DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  html_content MEDIUMTEXT,
  github_release_id BIGINT DEFAULT NULL,
  github_tag_name VARCHAR(255) DEFAULT NULL,
  github_target_repo VARCHAR(500) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_by INT,
  read_access JSON DEFAULT (JSON_ARRAY()),
  FOREIGN KEY (log_id) REFERENCES logs(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Who may see a document image: one row per (image, document holding it).
-- hash is the file name without .webp. uploaded_by is set only when the
-- writer supplied the bytes. See migrations/2026-09-27-who-may-see-doc-images.sql.
CREATE TABLE doc_images (
  hash CHAR(16) NOT NULL,
  log_id INT NOT NULL,
  uploaded_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (hash, log_id),
  INDEX idx_doc_images_log (log_id),
  FOREIGN KEY (log_id) REFERENCES logs(id) ON DELETE CASCADE,
  FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE comments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  log_id INT NOT NULL,
  user_id INT NOT NULL,
  content TEXT NOT NULL,
  tag ENUM('comment', 'suggestion', 'question', 'issue', 'note', 'pr_review') DEFAULT 'comment',
  status ENUM('open', 'resolved', 'dismissed') DEFAULT 'open',
  selection_start INT DEFAULT NULL,
  selection_end INT DEFAULT NULL,
  selected_text TEXT DEFAULT NULL,
  external_kind ENUM('pr_file_line','pr_general','issue_thread') DEFAULT NULL,
  external_ref VARCHAR(500) DEFAULT NULL,
  external_id VARCHAR(64) DEFAULT NULL,
  resolved_by INT DEFAULT NULL,
  resolved_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (log_id) REFERENCES logs(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (resolved_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_comments_log (log_id),
  INDEX idx_comments_status (log_id, status),
  INDEX idx_comments_external (external_kind, external_ref)
) ENGINE=InnoDB;

CREATE TABLE comment_replies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  comment_id INT NOT NULL,
  user_id INT NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (comment_id) REFERENCES comments(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_replies_comment (comment_id)
) ENGINE=InnoDB;

CREATE TABLE favorites (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  log_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_favorites_user_log (user_id, log_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (log_id) REFERENCES logs(id) ON DELETE CASCADE,
  INDEX idx_favorites_user (user_id)
) ENGINE=InnoDB;

CREATE TABLE activity_log (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT NOT NULL,
  squad_id INT DEFAULT NULL,
  user_id INT NOT NULL,
  action VARCHAR(50) NOT NULL,
  resource_type VARCHAR(30) NOT NULL,
  resource_id INT NOT NULL,
  metadata JSON DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_activity_workspace_time (workspace_id, created_at),
  INDEX idx_activity_squad_time (squad_id, created_at),
  INDEX idx_activity_resource (resource_type, resource_id, created_at),
  INDEX idx_activity_user_time (user_id, created_at)
) ENGINE=InnoDB;

CREATE TABLE watches (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  resource_type VARCHAR(30) NOT NULL,
  resource_id INT NOT NULL,
  source VARCHAR(20) NOT NULL DEFAULT 'manual',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_watches_user_resource (user_id, resource_type, resource_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_watches_resource (resource_type, resource_id)
) ENGINE=InnoDB;

CREATE TABLE notifications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  type VARCHAR(50) NOT NULL,
  actor_id INT DEFAULT NULL,
  title VARCHAR(255) NOT NULL,
  body TEXT DEFAULT NULL,
  link_url VARCHAR(512) DEFAULT NULL,
  resource_type VARCHAR(30) DEFAULT NULL,
  resource_id INT DEFAULT NULL,
  metadata JSON DEFAULT NULL,
  read_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (actor_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_notifications_user_created (user_id, created_at),
  INDEX idx_notifications_user_unread (user_id, read_at, created_at),
  INDEX idx_notifications_resource (resource_type, resource_id)
) ENGINE=InnoDB;

-- Outbound webhooks (migrations/2026-09-28-webhooks.sql). An env subscription
-- (source 'env') stores no secret; it is read from WEBHOOK_SECRET at send time.
-- workspace_id on a subscription has no foreign key on purpose, and
-- webhook_events.body holds the exact bytes every delivery sends.
CREATE TABLE webhook_subscriptions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  url VARCHAR(2048) NOT NULL,
  secret VARCHAR(255) NULL,
  source VARCHAR(8) NOT NULL,
  enabled BOOLEAN NOT NULL DEFAULT TRUE,
  event_types JSON NULL,
  workspace_id INT NULL,
  disabled_reason VARCHAR(255) NULL,
  consecutive_failures INT NOT NULL DEFAULT 0,
  paused_until DATETIME(3) NULL,
  created_by INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT chk_webhook_subscriptions_source CHECK (source IN ('env', 'admin')),
  CONSTRAINT chk_webhook_subscriptions_secret CHECK ((source = 'env') = (secret IS NULL)),
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE webhook_events (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  event_uuid CHAR(36) NOT NULL,
  type VARCHAR(32) NOT NULL,
  workspace_id INT NOT NULL,
  occurred_at DATETIME(3) NOT NULL,
  body MEDIUMBLOB NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_webhook_events_uuid (event_uuid),
  INDEX idx_webhook_events_created (created_at)
) ENGINE=InnoDB;

CREATE TABLE webhook_deliveries (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  subscription_id INT NOT NULL,
  event_id BIGINT NOT NULL,
  status VARCHAR(12) NOT NULL DEFAULT 'pending',
  attempts INT NOT NULL DEFAULT 0,
  leased_by CHAR(36) NULL,
  lease_expires_at DATETIME(3) NULL,
  last_status SMALLINT NULL,
  last_error VARCHAR(255) NULL,
  delivered_at DATETIME(3) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT chk_webhook_deliveries_status CHECK (status IN ('pending', 'delivered', 'dead')),
  UNIQUE KEY uq_webhook_deliveries_sub_event (subscription_id, event_id),
  INDEX idx_webhook_deliveries_head (subscription_id, status, event_id),
  FOREIGN KEY (subscription_id) REFERENCES webhook_subscriptions(id) ON DELETE CASCADE,
  FOREIGN KEY (event_id) REFERENCES webhook_events(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- The emit order lock (migrations/2026-10-10-webhook-sequence-lock.sql): an
-- emit creates its one row if absent and takes it FOR UPDATE, so their ids are
-- allocated in commit order. No row is seeded: restore.sh treats a database
-- with no rows as a new install, and a seeded row would make it look used.
CREATE TABLE webhook_sequence_lock (
  id TINYINT PRIMARY KEY,
  CONSTRAINT chk_webhook_sequence_lock_row CHECK (id = 1)
) ENGINE=InnoDB;