-- Newswire Press Release System
-- Run once to initialise the database

CREATE DATABASE IF NOT EXISTS newswire
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE newswire;

-- ── Admins ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS admins (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  username      VARCHAR(50)  NOT NULL UNIQUE,
  email         VARCHAR(255) NOT NULL UNIQUE,
  display_name  VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  totp_secret   VARCHAR(64)  NULL,
  totp_enabled  TINYINT(1)   NOT NULL DEFAULT 0,
  is_super_admin TINYINT(1)  NOT NULL DEFAULT 0,
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Platform Users ──────────────────────────────
CREATE TABLE IF NOT EXISTS platform_users (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  email         VARCHAR(255) NOT NULL UNIQUE,
  full_name     VARCHAR(255) NOT NULL,
  company_name  VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  totp_secret   VARCHAR(64)  NULL,
  totp_enabled  TINYINT(1)   NOT NULL DEFAULT 0,
  stripe_customer_id VARCHAR(255) NULL,
  release_credits INT NOT NULL DEFAULT 3,
  plan_tier               VARCHAR(50)  NOT NULL DEFAULT 'starter',
  beezifi_sub             VARCHAR(255) NULL UNIQUE,
  password_reset_token    CHAR(64)     NULL,
  password_reset_expires  TIMESTAMP    NULL,
  created_at              TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at              TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Beezifi OAuth In-Flight States ──────────────
CREATE TABLE IF NOT EXISTS beezifi_oauth_states (
  state      VARCHAR(64)  NOT NULL PRIMARY KEY,
  nonce      VARCHAR(64)  NOT NULL,
  flow       VARCHAR(10)  NOT NULL DEFAULT 'login',
  user_id    INT          DEFAULT NULL,
  expires_at TIMESTAMP    NOT NULL,
  INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Press Releases ────────────────────────────────
CREATE TABLE IF NOT EXISTS press_releases (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  title        VARCHAR(500) NOT NULL,
  subtitle     VARCHAR(500) NULL,
  slug         VARCHAR(600) NOT NULL UNIQUE,
  content      LONGTEXT     NOT NULL,
  excerpt      TEXT         NULL,
  author       VARCHAR(255) NOT NULL,
  category     VARCHAR(100) NOT NULL DEFAULT 'General',
  tags         VARCHAR(500) NULL,
  status       ENUM('draft','published') NOT NULL DEFAULT 'draft',
  published_at TIMESTAMP    NULL,
  created_by   INT          NOT NULL,
  created_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES admins(id) ON DELETE RESTRICT,
  INDEX idx_status_published (status, published_at),
  INDEX idx_slug (slug),
  INDEX idx_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Platform Press Releases ─────────────────────
CREATE TABLE IF NOT EXISTS platform_press_releases (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  user_id      INT          NOT NULL,
  title        VARCHAR(500) NOT NULL,
  subtitle     VARCHAR(500) NULL,
  slug         VARCHAR(600) NOT NULL UNIQUE,
  content      LONGTEXT     NOT NULL,
  excerpt      TEXT         NULL,
  author       VARCHAR(255) NOT NULL,
  category     VARCHAR(100) NOT NULL DEFAULT 'General',
  tags         VARCHAR(500) NULL,
  status       ENUM('draft','published') NOT NULL DEFAULT 'draft',
  published_at TIMESTAMP    NULL,
  created_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES platform_users(id) ON DELETE CASCADE,
  INDEX idx_status_published (status, published_at),
  INDEX idx_slug (slug),
  INDEX idx_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Platform Credit Purchases ──────────────────
CREATE TABLE IF NOT EXISTS platform_credit_purchases (
  id                          INT AUTO_INCREMENT PRIMARY KEY,
  user_id                     INT          NOT NULL,
  stripe_checkout_session_id  VARCHAR(255) NOT NULL UNIQUE,
  stripe_payment_intent_id    VARCHAR(255) NULL,
  amount_cents                INT          NOT NULL,
  currency                    VARCHAR(10)  NOT NULL DEFAULT 'usd',
  credits_added               INT          NOT NULL,
  status                      VARCHAR(50)  NOT NULL DEFAULT 'paid',
  created_at                  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES platform_users(id) ON DELETE CASCADE,
  INDEX idx_user_created (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Platform Developer API Keys ───────────────
CREATE TABLE IF NOT EXISTS platform_api_keys (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  user_id       INT           NOT NULL,
  key_name      VARCHAR(120)  NOT NULL,
  key_prefix    VARCHAR(24)   NOT NULL,
  key_hash      CHAR(64)      NOT NULL UNIQUE,
  last_used_at  TIMESTAMP     NULL,
  revoked_at    TIMESTAMP     NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES platform_users(id) ON DELETE CASCADE,
  INDEX idx_user_active (user_id, revoked_at, created_at),
  INDEX idx_prefix (key_prefix)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
