-- ═══════════════════════════════════════════════════════════
--  FICTION IGNITION — MySQL Database Schema
--  Run this once to set up your database.
--  Recommended: MySQL 8.0+ or MariaDB 10.6+
-- ═══════════════════════════════════════════════════════════

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

USE fiction_ignition;

-- ── Users ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
  id              CHAR(36)        NOT NULL PRIMARY KEY,          -- UUID
  name            VARCHAR(120)    NOT NULL,
  email           VARCHAR(254)    NOT NULL UNIQUE,
  password_hash   VARCHAR(255)    NOT NULL,                      -- bcrypt
  tier            ENUM('free','starter','writer','author_pro')
                                  NOT NULL DEFAULT 'free',
  email_verified  TINYINT(1)      NOT NULL DEFAULT 0,
  isAdmin         TINYINT(1)      NOT NULL DEFAULT 0,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
                                  ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ── Email verification tokens ─────────────────────────────
CREATE TABLE IF NOT EXISTS email_tokens (
  token           CHAR(64)        NOT NULL PRIMARY KEY,          -- random_bytes hex
  user_id         CHAR(36)        NOT NULL,
  expires_at      DATETIME        NOT NULL,
  used            TINYINT(1)      NOT NULL DEFAULT 0,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── Password reset tokens ─────────────────────────────────
CREATE TABLE IF NOT EXISTS reset_tokens (
  token           CHAR(64)        NOT NULL PRIMARY KEY,
  user_id         CHAR(36)        NOT NULL,
  expires_at      DATETIME        NOT NULL,
  used            TINYINT(1)      NOT NULL DEFAULT 0,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── Sessions ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sessions (
  id              CHAR(64)        NOT NULL PRIMARY KEY,          -- random_bytes hex
  user_id         CHAR(36)        NOT NULL,
  expires_at      DATETIME        NOT NULL,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── Square subscriptions ───────────────────────────────────
-- One row per active/cancelled subscription
CREATE TABLE IF NOT EXISTS subscriptions (
  id                      INT             AUTO_INCREMENT PRIMARY KEY,
  user_id                 CHAR(36)        NOT NULL UNIQUE,       -- 1 subscription per user
  square_subscription_id  VARCHAR(128)    NOT NULL UNIQUE,
  square_customer_id      VARCHAR(128)    NOT NULL,
  square_card_id          VARCHAR(128),
  plan_key                VARCHAR(32)     NOT NULL,              -- 'starter','writer','author_pro'
  status                  ENUM('active','paused','cancelled','pending')
                                          NOT NULL DEFAULT 'pending',
  current_period_start    DATE,
  current_period_end      DATE,
  cancelled_at            DATETIME,
  created_at              DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at              DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
                                          ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── Prompt usage ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS prompt_usage (
  id          BIGINT          AUTO_INCREMENT PRIMARY KEY,
  user_id     CHAR(36)        NOT NULL,
  used_date   DATE            NOT NULL,
  count       SMALLINT        NOT NULL DEFAULT 0,
  UNIQUE KEY uq_user_date (user_id, used_date),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── Webhook events log (Square) ───────────────────────────
CREATE TABLE IF NOT EXISTS webhook_events (
  id              BIGINT          AUTO_INCREMENT PRIMARY KEY,
  square_event_id VARCHAR(128)    NOT NULL UNIQUE,
  event_type      VARCHAR(128)    NOT NULL,
  payload         JSON,
  processed       TINYINT(1)      NOT NULL DEFAULT 0,
  created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ── Indexes ───────────────────────────────────────────────
CREATE INDEX idx_sessions_user    ON sessions(user_id);
CREATE INDEX idx_sessions_expires ON sessions(expires_at);
CREATE INDEX idx_usage_user_date  ON prompt_usage(user_id, used_date);
CREATE INDEX idx_tokens_user      ON email_tokens(user_id);

-- ── Rate limiting (contact form, API abuse prevention) ────
CREATE TABLE IF NOT EXISTS rate_limits (
  id          BIGINT          AUTO_INCREMENT PRIMARY KEY,
  ip_hash     CHAR(64)        NOT NULL,
  action      VARCHAR(32)     NOT NULL,
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ip_action_time (ip_hash, action, created_at)
) ENGINE=InnoDB;

-- ── Admin role ─────────────────────────────────────────────
-- Run this ALTER if you already created the users table:
-- ALTER TABLE users ADD COLUMN isAdmin TINYINT(1) NOT NULL DEFAULT 0 AFTER email_verified;
--
-- Or if starting fresh, the CREATE TABLE below includes it.
-- To grant yourself admin after signing up:
--   UPDATE users SET isAdmin = 1 WHERE email = 'your@email.com';
