-- Lebnaus accounts hub: MySQL schema.
-- Run with `npm run db:setup`, or paste into phpMyAdmin. Safe to run more than once.
-- All timestamps are UTC milliseconds since 1970 (BIGINT).
--   Readable in phpMyAdmin with: SELECT FROM_UNIXTIME(created_at / 1000) ...
-- Rules for editing (the same file is adapted for SQLite in dev/tests):
--   no semicolons inside a statement (comments included), and a KEY line must never be the last line of a table.
-- Databases created from an older copy of this file are brought up to date by
-- upgradeLegacySchema() in src/db/index.js, which runs with npm run db:setup and DB_AUTO_MIGRATE.

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(254) NOT NULL,
  created_at BIGINT NOT NULL,
  is_admin TINYINT(1) NOT NULL DEFAULT 0,
  disabled TINYINT(1) NOT NULL DEFAULT 0,
  stripe_customer_id VARCHAR(64) NULL,
  CONSTRAINT uq_users_email UNIQUE (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS login_tokens (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  token_hash CHAR(64) NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  created_at BIGINT NOT NULL,
  expires_at BIGINT NOT NULL,
  used_at BIGINT NULL,
  return_to VARCHAR(512) NULL,
  KEY idx_login_tokens_user (user_id, created_at),
  CONSTRAINT uq_login_tokens_hash UNIQUE (token_hash),
  CONSTRAINT fk_login_tokens_user FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sessions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  session_hash CHAR(64) NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  created_at BIGINT NOT NULL,
  last_seen_at BIGINT NOT NULL,
  expires_at BIGINT NOT NULL,
  KEY idx_sessions_user (user_id),
  CONSTRAINT uq_sessions_hash UNIQUE (session_hash),
  CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS plans (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(500) NOT NULL DEFAULT '',
  type VARCHAR(16) NOT NULL,
  billing_interval VARCHAR(8) NULL,
  stripe_product_id VARCHAR(64) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  recommended TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  created_at BIGINT NOT NULL,
  updated_at BIGINT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS plan_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  plan_id BIGINT UNSIGNED NOT NULL,
  tool_key VARCHAR(50) NOT NULL,
  CONSTRAINT uq_plan_items UNIQUE (plan_id, tool_key),
  CONSTRAINT fk_plan_items_plan FOREIGN KEY (plan_id) REFERENCES plans (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One row per plan per currency per price point. Stripe Prices cannot be edited, so a
-- price change adds a new row and switches the old one to active = 0. Old rows are kept
-- because purchases point at the exact price they were bought at.
-- The app keeps at most one active row per (plan_id, currency).
-- currency is a lowercase ISO code from src/lib/currencies.js, amount is in cents.
CREATE TABLE IF NOT EXISTS plan_prices (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  plan_id BIGINT UNSIGNED NOT NULL,
  currency CHAR(3) NOT NULL,
  amount INT NOT NULL,
  stripe_price_id VARCHAR(64) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at BIGINT NOT NULL,
  KEY idx_plan_prices_plan (plan_id, currency, active),
  CONSTRAINT fk_plan_prices_plan FOREIGN KEY (plan_id) REFERENCES plans (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS purchases (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  stripe_session_id VARCHAR(255) NOT NULL,
  stripe_customer_id VARCHAR(64) NULL,
  stripe_subscription_id VARCHAR(64) NULL,
  stripe_payment_intent_id VARCHAR(64) NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  plan_id BIGINT UNSIGNED NOT NULL,
  plan_price_id BIGINT UNSIGNED NULL,
  status VARCHAR(16) NOT NULL,
  created_at BIGINT NOT NULL,
  updated_at BIGINT NOT NULL,
  KEY idx_purchases_user (user_id),
  KEY idx_purchases_subscription (stripe_subscription_id),
  KEY idx_purchases_payment_intent (stripe_payment_intent_id),
  CONSTRAINT uq_purchases_session UNIQUE (stripe_session_id),
  CONSTRAINT fk_purchases_user FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT fk_purchases_plan FOREIGN KEY (plan_id) REFERENCES plans (id),
  CONSTRAINT fk_purchases_plan_price FOREIGN KEY (plan_price_id) REFERENCES plan_prices (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Access codes the owner generates on demand. Only the SHA-256 of the code is stored.
-- tool_keys is 'all' or a comma-separated list. A code stops working at expires_at,
-- after max_uses redemptions, or when revoked. uses counts redemptions so far.
CREATE TABLE IF NOT EXISTS admin_codes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  code_hash CHAR(64) NOT NULL,
  label VARCHAR(100) NOT NULL DEFAULT '',
  tool_keys VARCHAR(1000) NOT NULL,
  revoked TINYINT(1) NOT NULL DEFAULT 0,
  created_at BIGINT NOT NULL,
  expires_at BIGINT NOT NULL,
  max_uses INT NOT NULL DEFAULT 1,
  uses INT NOT NULL DEFAULT 0,
  CONSTRAINT uq_admin_codes_hash UNIQUE (code_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Who redeemed which code. A person can redeem a given code once.
CREATE TABLE IF NOT EXISTS admin_code_redemptions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  code_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  redeemed_at BIGINT NOT NULL,
  KEY idx_code_redemptions_user (user_id),
  CONSTRAINT uq_code_redemptions UNIQUE (code_id, user_id),
  CONSTRAINT fk_code_redemptions_code FOREIGN KEY (code_id) REFERENCES admin_codes (id),
  CONSTRAINT fk_code_redemptions_user FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS entitlements (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  tool_key VARCHAR(50) NOT NULL,
  source VARCHAR(16) NOT NULL,
  plan_id BIGINT UNSIGNED NULL,
  purchase_id BIGINT UNSIGNED NULL,
  admin_code_id BIGINT UNSIGNED NULL,
  starts_at BIGINT NOT NULL,
  ends_at BIGINT NULL,
  status VARCHAR(16) NOT NULL,
  created_at BIGINT NOT NULL,
  KEY idx_entitlements_user (user_id),
  KEY idx_entitlements_code (admin_code_id),
  CONSTRAINT uq_entitlements_purchase_tool UNIQUE (purchase_id, tool_key),
  CONSTRAINT fk_entitlements_user FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT fk_entitlements_plan FOREIGN KEY (plan_id) REFERENCES plans (id),
  CONSTRAINT fk_entitlements_purchase FOREIGN KEY (purchase_id) REFERENCES purchases (id),
  CONSTRAINT fk_entitlements_code FOREIGN KEY (admin_code_id) REFERENCES admin_codes (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS stripe_events (
  event_id VARCHAR(255) NOT NULL PRIMARY KEY,
  type VARCHAR(100) NOT NULL,
  received_at BIGINT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  at BIGINT NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(64) NOT NULL,
  detail VARCHAR(500) NOT NULL DEFAULT '',
  ip VARCHAR(64) NOT NULL DEFAULT '',
  KEY idx_audit_action (action, at),
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
