-- =====================================================================
-- SocialOps Agent Hub — MySQL/MariaDB schema
-- Target: Namecheap shared hosting (MySQL 5.7+/MariaDB 10.3+), utf8mb4.
-- PRD §12 table list, FKs where supported, indexes on project_id /
-- status / scheduled_at, unique constraints for external provider IDs.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------
-- Users & roles
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email         VARCHAR(190) NOT NULL,
  name          VARCHAR(120) NOT NULL DEFAULT '',
  password_hash VARCHAR(255) NOT NULL,
  role          ENUM('SUPER_ADMIN','PROJECT_ADMIN','REVIEWER','AGENT','READ_ONLY') NOT NULL DEFAULT 'READ_ONLY',
  status        ENUM('ACTIVE','INACTIVE','SUSPENDED') NOT NULL DEFAULT 'ACTIVE',
  last_login_at DATETIME NULL,
  created_at    DATETIME NOT NULL,
  updated_at    DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS roles (
  id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(60) NOT NULL,
  description VARCHAR(190) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  UNIQUE KEY uq_roles_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_roles (
  user_id INT UNSIGNED NOT NULL,
  role_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id, role_id),
  CONSTRAINT fk_ur_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ur_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS login_throttle (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email        VARCHAR(190) NOT NULL,
  ip           VARCHAR(45)  NOT NULL DEFAULT '',
  fail_count   INT UNSIGNED NOT NULL DEFAULT 0,
  window_start DATETIME NOT NULL,
  created_at   DATETIME NOT NULL,
  updated_at   DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY k_throttle_lookup (email, ip)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- Projects & membership
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS projects (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name        VARCHAR(150) NOT NULL,
  slug        VARCHAR(150) NOT NULL,
  industry    VARCHAR(80)  NOT NULL DEFAULT '',
  country     VARCHAR(80)  NOT NULL DEFAULT '',
  timezone    VARCHAR(60)  NOT NULL DEFAULT 'Asia/Kolkata',
  language    VARCHAR(10)  NOT NULL DEFAULT 'en',
  status      ENUM('DRAFT','ACTIVE','SUSPENDED') NOT NULL DEFAULT 'DRAFT',
  config_json MEDIUMTEXT NULL,
  created_by  INT UNSIGNED NULL,
  created_at  DATETIME NOT NULL,
  updated_at  DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_projects_slug (slug),
  KEY k_projects_status (status),
  CONSTRAINT fk_projects_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_members (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id INT UNSIGNED NOT NULL,
  user_id    INT UNSIGNED NOT NULL,
  role       ENUM('ADMIN','REVIEWER','AGENT','VIEWER') NOT NULL DEFAULT 'AGENT',
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_member (project_id, user_id),
  KEY k_member_user (user_id),
  CONSTRAINT fk_pm_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  CONSTRAINT fk_pm_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- project_settings kept as a thin table (main config lives in projects.config_json)
CREATE TABLE IF NOT EXISTS project_settings (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id INT UNSIGNED NOT NULL,
  setting_key VARCHAR(120) NOT NULL,
  value_json MEDIUMTEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_psettings (project_id, setting_key),
  CONSTRAINT fk_ps_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS brand_assets (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id INT UNSIGNED NOT NULL,
  kind       ENUM('LOGO','FONT','COLOR_PALETTE','TEMPLATE','OTHER') NOT NULL DEFAULT 'OTHER',
  file_path  VARCHAR(500) NOT NULL DEFAULT '',
  meta_json  TEXT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY k_brand_assets_project (project_id),
  CONSTRAINT fk_ba_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- Sources (PRD §4)
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS source_allowlist (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id  INT UNSIGNED NOT NULL,
  domain      VARCHAR(190) NOT NULL,
  source_type ENUM('REGULATOR','INSURER','GOVERNMENT','OTHER_OFFICIAL') NOT NULL DEFAULT 'OTHER_OFFICIAL',
  label       VARCHAR(150) NOT NULL DEFAULT '',
  notes       TEXT NULL,
  status      ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  created_at  DATETIME NOT NULL,
  updated_at  DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_allowlist (project_id, domain),
  CONSTRAINT fk_sa_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS source_items (
  id               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id       INT UNSIGNED NOT NULL,
  allowlist_id     INT UNSIGNED NULL,
  source_type      ENUM('REGULATOR','INSURER','GOVERNMENT','OTHER_OFFICIAL') NOT NULL DEFAULT 'OTHER_OFFICIAL',
  publisher        VARCHAR(190) NOT NULL DEFAULT '',
  title            VARCHAR(500) NOT NULL,
  canonical_url    VARCHAR(760) NOT NULL,
  source_date      DATE NULL,
  retrieved_at     DATETIME NOT NULL,
  content_excerpt  MEDIUMTEXT NULL,
  content_hash     CHAR(64) NOT NULL,
  category         VARCHAR(40) NULL,
  classification   VARCHAR(40) NULL,
  status           ENUM('NEW','CLASSIFIED','USED','IGNORED') NOT NULL DEFAULT 'NEW',
  verification_status ENUM('UNVERIFIED','VERIFIED','INVALID') NOT NULL DEFAULT 'UNVERIFIED',
  reference_notes  TEXT NULL,
  created_at       DATETIME NOT NULL,
  updated_at       DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_source_url (project_id, canonical_url(190)),
  KEY k_source_hash (project_id, content_hash),
  KEY k_source_status (project_id, status),
  KEY k_source_cat (project_id, category),
  CONSTRAINT fk_si_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  CONSTRAINT fk_si_allowlist FOREIGN KEY (allowlist_id) REFERENCES source_allowlist(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS source_snapshots (
  id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  source_item_id INT UNSIGNED NOT NULL,
  content        MEDIUMTEXT NULL,
  fetched_at     DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY k_snap_item (source_item_id),
  CONSTRAINT fk_ss_item FOREIGN KEY (source_item_id) REFERENCES source_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- Content pipeline
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS content_ideas (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id INT UNSIGNED NOT NULL,
  title      VARCHAR(300) NOT NULL,
  notes      TEXT NULL,
  source_item_id INT UNSIGNED NULL,
  status     ENUM('OPEN','CONVERTED','DISCARDED') NOT NULL DEFAULT 'OPEN',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY k_ideas_project (project_id, status),
  CONSTRAINT fk_ci_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_items (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id      INT UNSIGNED NOT NULL,
  source_item_id  INT UNSIGNED NULL,
  title           VARCHAR(300) NOT NULL DEFAULT '',
  summary         VARCHAR(1000) NOT NULL DEFAULT '',
  copy            MEDIUMTEXT NULL,
  pillar          VARCHAR(80)  NOT NULL DEFAULT '',
  objective       VARCHAR(120) NOT NULL DEFAULT '',
  audience        VARCHAR(300) NOT NULL DEFAULT '',
  cta             VARCHAR(300) NOT NULL DEFAULT '',
  hashtags        VARCHAR(500) NOT NULL DEFAULT '',
  destination_url VARCHAR(760) NOT NULL DEFAULT '',
  utm_source      VARCHAR(80)  NOT NULL DEFAULT '',
  utm_medium      VARCHAR(80)  NOT NULL DEFAULT '',
  utm_campaign    VARCHAR(120) NOT NULL DEFAULT '',
  utm_content     VARCHAR(120) NOT NULL DEFAULT '',
  visual_brief    TEXT NULL,
  status          ENUM('IDEA','RESEARCHED','DRAFT','CREATIVE_READY','FACT_CHECKED','COMPLIANCE_REVIEW','PENDING_ADMIN','CHANGES_REQUESTED','HOLD','REJECTED','APPROVED_FOR_SCHEDULE','SCHEDULED','PUBLISHING','PUBLISHED','FAILED') NOT NULL DEFAULT 'IDEA',
  rejected_reason TEXT NULL,
  approval_hash   CHAR(64) NULL,
  approved_by     INT UNSIGNED NULL,
  approved_at     DATETIME NULL,
  scheduled_at    DATETIME NULL,
  published_at    DATETIME NULL,
  created_by      INT UNSIGNED NULL,
  created_at      DATETIME NOT NULL,
  updated_at      DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY k_content_project_status (project_id, status),
  KEY k_content_sched (project_id, scheduled_at),
  KEY k_content_source (source_item_id),
  CONSTRAINT fk_cont_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  CONSTRAINT fk_cont_source FOREIGN KEY (source_item_id) REFERENCES source_items(id) ON DELETE SET NULL,
  CONSTRAINT fk_cont_approver FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_platform_variants (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  content_id    INT UNSIGNED NOT NULL,
  platform      VARCHAR(40) NOT NULL,
  copy          MEDIUMTEXT NULL,
  asset_id      INT UNSIGNED NULL,
  account_id    INT UNSIGNED NULL,
  publish_state ENUM('PENDING','IN_QUEUE','SENT','SKIPPED') NOT NULL DEFAULT 'PENDING',
  created_at    DATETIME NOT NULL,
  updated_at    DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY k_variant_content (content_id, platform),
  KEY k_variant_publish (publish_state),
  CONSTRAINT fk_cpv_content FOREIGN KEY (content_id) REFERENCES content_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_references (
  id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  content_id     INT UNSIGNED NOT NULL,
  source_item_id INT UNSIGNED NULL,
  reference_type ENUM('SOURCE','CITATION','BACKGROUND') NOT NULL DEFAULT 'SOURCE',
  external_url   VARCHAR(760) NULL,
  note           VARCHAR(500) NULL,
  created_at     DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY k_ref_content (content_id),
  CONSTRAINT fk_cr_content FOREIGN KEY (content_id) REFERENCES content_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_assets (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id  INT UNSIGNED NOT NULL,
  content_id  INT UNSIGNED NULL,
  kind        ENUM('UPLOAD','AI_GENERATED','CANVA_EXPORT','EXTERNAL') NOT NULL DEFAULT 'UPLOAD',
  file_path   VARCHAR(500) NOT NULL,
  mime_type   VARCHAR(80)  NOT NULL DEFAULT '',
  width       INT UNSIGNED NOT NULL DEFAULT 0,
  height      INT UNSIGNED NOT NULL DEFAULT 0,
  size_bytes  INT UNSIGNED NOT NULL DEFAULT 0,
  prompt      TEXT NULL,
  share_token CHAR(32) NOT NULL,
  qa_json     TEXT NULL,
  created_at  DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY k_assets_content (content_id),
  KEY k_assets_project (project_id),
  CONSTRAINT fk_ca_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  CONSTRAINT fk_ca_content FOREIGN KEY (content_id) REFERENCES content_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_checks (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  content_id  INT UNSIGNED NOT NULL,
  check_type  VARCHAR(60) NOT NULL,
  verdict     VARCHAR(30) NOT NULL,
  result_json MEDIUMTEXT NULL,
  created_at  DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY k_checks_content (content_id, check_type),
  CONSTRAINT fk_cc_content FOREIGN KEY (content_id) REFERENCES content_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_comments (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  content_id INT UNSIGNED NOT NULL,
  user_id    INT UNSIGNED NULL,
  body       TEXT NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY k_comments_content (content_id),
  CONSTRAINT fk_cm_content FOREIGN KEY (content_id) REFERENCES content_items(id) ON DELETE CASCADE,
  CONSTRAINT fk_cm_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS content_tasks (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id  INT UNSIGNED NOT NULL,
  content_id  INT UNSIGNED NULL,
  assigned_to VARCHAR(120) NOT NULL DEFAULT 'Agent Head',
  instruction TEXT NOT NULL,
  status      ENUM('OPEN','IN_PROGRESS','DONE') NOT NULL DEFAULT 'OPEN',
  created_by  INT UNSIGNED NULL,
  created_at  DATETIME NOT NULL,
  updated_at  DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY k_tasks_project (project_id, status),
  CONSTRAINT fk_ct_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  CONSTRAINT fk_ct_content FOREIGN KEY (content_id) REFERENCES content_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS approvals (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  content_id  INT UNSIGNED NOT NULL,
  approver_id INT UNSIGNED NOT NULL,
  status      ENUM('APPROVED','REVOKED') NOT NULL DEFAULT 'APPROVED',
  note        VARCHAR(500) NULL,
  created_at  DATETIME NOT NULL,
  revoked_at  DATETIME NULL,
  PRIMARY KEY (id),
  KEY k_approvals_content (content_id),
  CONSTRAINT fk_ap_content FOREIGN KEY (content_id) REFERENCES content_items(id) ON DELETE CASCADE,
  CONSTRAINT fk_ap_user FOREIGN KEY (approver_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS calendar_entries (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id  INT UNSIGNED NOT NULL,
  content_id  INT UNSIGNED NULL,
  entry_date  DATE NOT NULL,
  time_of_day TIME NULL,
  note        VARCHAR(300) NULL,
  created_at  DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY k_cal_project_date (project_id, entry_date),
  CONSTRAINT fk_ce_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- Social connections
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS social_connections (
  id                INT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id        INT UNSIGNED NOT NULL,
  provider          ENUM('meta','linkedin','x','canva') NOT NULL,
  status            ENUM('PENDING','ACTIVE','INVALID','DISCONNECTED') NOT NULL DEFAULT 'PENDING',
  status_detail     VARCHAR(250) NULL,
  access_token_enc  TEXT NULL,
  refresh_token_enc TEXT NULL,
  token_expires_at  DATETIME NULL,
  meta_json         TEXT NULL,
  last_checked_at   DATETIME NULL,
  created_at        DATETIME NOT NULL,
  updated_at        DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY k_conn_project (project_id, provider),
  CONSTRAINT fk_sc_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS social_platform_accounts (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  connection_id INT UNSIGNED NOT NULL,
  platform      VARCHAR(40) NOT NULL,
  external_id   VARCHAR(190) NOT NULL,
  handle        VARCHAR(190) NOT NULL DEFAULT '',
  can_publish   TINYINT(1) NOT NULL DEFAULT 0,
  status        ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  meta_json     TEXT NULL,
  created_at    DATETIME NOT NULL,
  updated_at    DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_account (connection_id, external_id),
  CONSTRAINT fk_spa_conn FOREIGN KEY (connection_id) REFERENCES social_connections(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- Publishing
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS publish_jobs (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id INT UNSIGNED NOT NULL,
  content_id INT UNSIGNED NOT NULL,
  variant_id INT UNSIGNED NOT NULL,
  account_id INT UNSIGNED NOT NULL,
  status     ENUM('QUEUED','RUNNING','SUCCESS','FAILED') NOT NULL DEFAULT 'QUEUED',
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY k_pj_content (content_id),
  CONSTRAINT fk_pj_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS publish_results (
  id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  publish_job_id INT UNSIGNED NULL,
  content_id     INT UNSIGNED NOT NULL,
  variant_id     INT UNSIGNED NOT NULL DEFAULT 0,
  account_id     INT UNSIGNED NOT NULL DEFAULT 0,
  status         ENUM('SUCCESS','FAILED') NOT NULL,
  external_id    VARCHAR(190) NULL,
  external_url   VARCHAR(760) NULL,
  error_message  TEXT NULL,
  created_at     DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_pubjob_result (publish_job_id),
  KEY k_pr_content (content_id),
  CONSTRAINT fk_prr_content FOREIGN KEY (content_id) REFERENCES content_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- AI
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS ai_runs (
  id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id     INT UNSIGNED NULL,
  content_id     INT UNSIGNED NULL,
  model          VARCHAR(80)  NOT NULL DEFAULT '',
  prompt_version VARCHAR(30)  NOT NULL DEFAULT 'v1',
  status         ENUM('SUCCESS','FAILED') NOT NULL,
  duration_ms    INT UNSIGNED NOT NULL DEFAULT 0,
  summary        VARCHAR(500) NULL,
  error          VARCHAR(500) NULL,
  created_at     DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY k_ai_project (project_id),
  KEY k_ai_content (content_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS prompt_versions (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name       VARCHAR(80) NOT NULL,
  version    INT UNSIGNED NOT NULL DEFAULT 1,
  template   MEDIUMTEXT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY k_pv_name (name, version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------
-- Ops: notifications, audit, settings, queue, agent chat
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS notifications (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id INT UNSIGNED NULL,
  user_id    INT UNSIGNED NULL,
  level      ENUM('INFO','OK','WARN','ERROR') NOT NULL DEFAULT 'INFO',
  message    VARCHAR(500) NOT NULL,
  link       VARCHAR(500) NULL,
  is_read    TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY k_notif_project (project_id, is_read),
  KEY k_notif_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id     INT UNSIGNED NULL,
  role        VARCHAR(40) NOT NULL DEFAULT 'SYSTEM',
  project_id  INT UNSIGNED NULL,
  action      VARCHAR(80) NOT NULL,
  object_type VARCHAR(60) NOT NULL DEFAULT '',
  object_id   INT UNSIGNED NULL,
  old_state   TEXT NULL,
  new_state   TEXT NULL,
  ip          VARCHAR(45) NULL,
  result      VARCHAR(250) NULL,
  created_at  DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY k_audit_project (project_id),
  KEY k_audit_user (user_id),
  KEY k_audit_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS system_settings (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  setting_key VARCHAR(120) NOT NULL,
  value       MEDIUMTEXT NULL,
  is_secret   TINYINT(1) NOT NULL DEFAULT 0,
  created_at  DATETIME NOT NULL,
  updated_at  DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_settings_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS job_queue (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  job_type     VARCHAR(40) NOT NULL,
  project_id   INT UNSIGNED NULL,
  payload      MEDIUMTEXT NULL,
  status       ENUM('QUEUED','RUNNING','SUCCESS','FAILED','RETRY_WAIT') NOT NULL DEFAULT 'QUEUED',
  attempts     INT UNSIGNED NOT NULL DEFAULT 0,
  max_attempts INT UNSIGNED NOT NULL DEFAULT 3,
  note         VARCHAR(1000) NULL,
  run_at       DATETIME NOT NULL,
  started_at   DATETIME NULL,
  finished_at  DATETIME NULL,
  locked_by    INT UNSIGNED NULL,
  locked_at    DATETIME NULL,
  created_at   DATETIME NOT NULL,
  updated_at   DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY k_queue_status (status, run_at),
  KEY k_queue_type (job_type, status),
  KEY k_queue_project (project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS agent_conversations (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  project_id INT UNSIGNED NOT NULL,
  user_id    INT UNSIGNED NULL,
  title      VARCHAR(190) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY k_conv_project (project_id),
  CONSTRAINT fk_ac_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS agent_messages (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  conversation_id INT UNSIGNED NOT NULL,
  role            ENUM('user','agent') NOT NULL,
  content         MEDIUMTEXT NOT NULL,
  meta_json       TEXT NULL,
  created_at      DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY k_msg_conv (conversation_id),
  CONSTRAINT fk_am_conv FOREIGN KEY (conversation_id) REFERENCES agent_conversations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ---------------------------------------------------------------
-- Seed: global roles reference rows
-- ---------------------------------------------------------------
INSERT IGNORE INTO roles (name, description) VALUES
  ('SUPER_ADMIN',   'Full platform control'),
  ('PROJECT_ADMIN', 'Manages assigned projects and approvals'),
  ('REVIEWER',      'Reviews and approves content'),
  ('AGENT',         'Agent worker: drafts, checks, prepares publishing'),
  ('READ_ONLY',     'View-only access');