-- Migration 001: baseline schema for the codes module and its dependencies.
-- MySQL 8.x · InnoDB · utf8mb4 · UTC
-- Apply with: mysql -u root -p pangolin < migrations/001_init.sql

SET time_zone = '+00:00';

-- -------------------------------------------------------------------------
-- Core account tables (needed by foreign keys from subscriptions)
-- -------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS users (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uuid        CHAR(36)     NOT NULL UNIQUE,          -- public-facing identifier
  email       VARCHAR(255) NOT NULL UNIQUE,
  pw_hash     VARCHAR(255) NOT NULL,                 -- argon2id
  dp_uuid     CHAR(36)     NOT NULL,                 -- data-plane UUID (sing-box)
  status      ENUM('active','banned') NOT NULL DEFAULT 'active',
  created_at  DATETIME(6)  NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------------------------------------
-- Plan catalogue  (digital values per design/CLAUDE.md §7)
-- -------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS plans (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code          ENUM('free','pro','team') NOT NULL UNIQUE,
  max_devices   INT  NOT NULL,              -- free 1 / pro 3 / team 10
  daily_minutes INT  NULL,                  -- free 10; NULL = unlimited
  ad_gate       BOOLEAN NOT NULL DEFAULT FALSE  -- free users must watch an ad daily
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO plans (code, max_devices, daily_minutes, ad_gate) VALUES
  ('free', 1,  10,   TRUE),
  ('pro',  3,  NULL, FALSE),
  ('team', 10, NULL, FALSE);

-- -------------------------------------------------------------------------
-- Subscriptions
-- -------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS subscriptions (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    BIGINT UNSIGNED NOT NULL,
  plan_id    BIGINT UNSIGNED NOT NULL,
  expires_at DATETIME(6) NOT NULL,
  source     ENUM('trial','code') NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (plan_id) REFERENCES plans(id),
  INDEX idx_user_exp (user_id, expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------------------------------------
-- Activation-code tables
-- -------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS code_batches (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  channel    ENUM('store','tg','line','manual') NOT NULL,
  created_by VARCHAR(64)  NOT NULL,
  note       VARCHAR(255) NULL,
  created_at DATETIME(6)  NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS codes (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  -- SHA-256(canonical_plaintext) stored as 64-char hex.
  -- Plaintext NEVER stored in any column.
  code_hash     CHAR(64)     NOT NULL UNIQUE,
  plan_id       BIGINT UNSIGNED NOT NULL,
  duration_days INT          NOT NULL,
  batch_id      BIGINT UNSIGNED NOT NULL,
  status        ENUM('unused','redeemed','void') NOT NULL DEFAULT 'unused',
  redeemed_by   BIGINT UNSIGNED NULL,    -- FK to users.id (not enforced to avoid
  redeemed_at   DATETIME(6)  NULL,       -- locking the users table on redeem)
  FOREIGN KEY (plan_id)  REFERENCES plans(id),
  FOREIGN KEY (batch_id) REFERENCES code_batches(id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -------------------------------------------------------------------------
-- Audit log  (all sensitive operations must be recorded)
-- -------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS audit_log (
  id     BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  actor  VARCHAR(64)  NOT NULL,           -- e.g. "user:123" or "admin:7"
  action VARCHAR(64)  NOT NULL,           -- e.g. "redeem", "ban", "node_drain"
  target VARCHAR(128) NOT NULL,           -- abbreviated; never contains plaintext codes
  meta   JSON NULL,                       -- structured context (no PII beyond IDs)
  at     DATETIME(6)  NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  INDEX idx_at (at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;