-- ============================================================================
-- Key Lair — Database schema
-- Engine: InnoDB everywhere (required for row-level locking / SELECT ... FOR UPDATE)
-- ============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------------------------------------------------------
-- admin_users
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS admin_users (
    id                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username              VARCHAR(64)  NOT NULL UNIQUE,
    email                 VARCHAR(190) NOT NULL UNIQUE,
    password_hash         VARCHAR(255) NOT NULL,
    role                  ENUM('master_admin','admin','content_manager','reviewer','demo') NOT NULL DEFAULT 'content_manager',
    theme                 VARCHAR(16) NULL, -- personal backend theme (admin panel only); NULL = 'default'. See settings.theme for the site-wide frontend theme
    language              VARCHAR(8) NULL, -- personal backend language, e.g. 'en'/'de'; NULL = I18n::DEFAULT_LOCALE
    two_factor_method     VARCHAR(32)  NOT NULL DEFAULT 'none', -- 'none' | 'totp' | (future: 'email', 'sms', ...)
    two_factor_secret     VARBINARY(255) NULL,                  -- provider-specific secret, e.g. TOTP seed
    two_factor_enabled_at DATETIME NULL,
    two_factor_disabled_at DATETIME NULL, -- set when an admin (not the owner) administratively suspends an already-enrolled 2FA method, so it can be restored later without re-enrolling; NULL = active (or never enrolled)
    is_active             TINYINT(1) NOT NULL DEFAULT 1,
    created_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Pending TOTP setup (secret generated but not yet confirmed with a valid code)
CREATE TABLE IF NOT EXISTS admin_two_factor_pending (
    admin_user_id INT UNSIGNED NOT NULL PRIMARY KEY,
    method        VARCHAR(32) NOT NULL,
    secret        VARBINARY(255) NOT NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_2fa_pending_admin FOREIGN KEY (admin_user_id) REFERENCES admin_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Server-side sessions for the "remember this device passed 2FA" step between login and 2FA verify
CREATE TABLE IF NOT EXISTS admin_login_challenges (
    id            VARCHAR(64) NOT NULL PRIMARY KEY, -- random token stored in a short-lived cookie/session
    admin_user_id INT UNSIGNED NOT NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at    DATETIME NOT NULL,
    CONSTRAINT fk_challenge_admin FOREIGN KEY (admin_user_id) REFERENCES admin_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- "Forgot password" tokens. Only a hash of the token ever touches the
-- database — the raw token exists only in the emailed link — so a database
-- leak alone can't be used to take over an account.
CREATE TABLE IF NOT EXISTS admin_password_resets (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_user_id INT UNSIGNED NOT NULL,
    token_hash    CHAR(64) NOT NULL UNIQUE,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at    DATETIME NOT NULL,
    used_at       DATETIME NULL,
    CONSTRAINT fk_password_reset_admin FOREIGN KEY (admin_user_id) REFERENCES admin_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- audit_log
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS audit_log (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_user_id INT UNSIGNED NULL,
    action        VARCHAR(64) NOT NULL,
    details       TEXT NULL,
    ip_hash       CHAR(64) NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_audit_admin FOREIGN KEY (admin_user_id) REFERENCES admin_users(id) ON DELETE SET NULL,
    INDEX idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- api_tokens  (server-to-server bearer tokens -- see migration 025.
-- admin/api_access.php and public/api/claim_link_lookup.php)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS api_tokens (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    label        VARCHAR(190) NOT NULL,
    token_hash   CHAR(64) NOT NULL UNIQUE,
    created_by   INT UNSIGNED NULL,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_used_at DATETIME NULL,
    revoked_at   DATETIME NULL,
    CONSTRAINT fk_api_token_admin FOREIGN KEY (created_by) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- products  (games / titles — the "Steam page" entity)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS products (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name              VARCHAR(190) NOT NULL,
    slug              VARCHAR(190) NOT NULL UNIQUE,
    cover_image_path  VARCHAR(255) NULL,
    intro_text        MEDIUMTEXT NULL,   -- shown above the platform selection
    outro_text        MEDIUMTEXT NULL,   -- shown below the key reveal
    status            ENUM('draft','active','archived') NOT NULL DEFAULT 'draft',
    default_max_claims INT UNSIGNED NULL, -- fallback tier in ClaimLinkFactory::resolveMaxClaims(); NULL = not configured
    custom_css        MEDIUMTEXT NULL, -- per-product CSS for this product's claim page only, embedded inline; NULL = none set
    theme             VARCHAR(16) NULL, -- per-product theme override (default/light/steam/custom); NULL = inherit the site-wide frontend theme
    background_image_path VARCHAR(255) NULL, -- per-product background image, only shown when this product's own resolved theme is 'custom'
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS product_media (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id  INT UNSIGNED NOT NULL,
    type        ENUM('screenshot','youtube') NOT NULL,
    value       VARCHAR(500) NOT NULL, -- file path for screenshots, full URL for youtube
    sort_order  INT NOT NULL DEFAULT 0,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_media_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_media_product (product_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- campaigns
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS campaigns (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name                VARCHAR(190) NOT NULL,
    status              ENUM('active','archived') NOT NULL DEFAULT 'active',
    default_max_claims  INT UNSIGNED NOT NULL DEFAULT 1,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- users  (a real claimant/creator entity, referenced by claim_links.user_id
-- below -- see migrations 023/024. Separate from admin_users, the System
-- Users login accounts for the admin panel itself: those two are never the
-- same table)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(190) NULL, -- optional -- two people can share a name; the id is what actually identifies the row
    email      VARCHAR(190) NULL, -- optional, same as name
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- creator_groups
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS creator_groups (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name                VARCHAR(190) NOT NULL,
    default_max_claims  INT UNSIGNED NULL, -- NULL = fall back to campaign default
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- platforms  (admin-editable — see admin/platforms.php — rather than a fixed
-- enum, so a new console can be added without a schema change)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS platforms (
    slug               VARCHAR(32) NOT NULL PRIMARY KEY,
    label              VARCHAR(64) NOT NULL,
    icon_path          VARCHAR(255) NULL,
    key_format_pattern VARCHAR(255) NULL, -- optional regex to validate an imported key's structure; NULL = no validation
    sort_order         INT NOT NULL DEFAULT 0,
    created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Deliberately no seed data here — a fresh install starts with zero
-- platforms, and an admin creates the ones they actually need (the New
-- Platform form on admin/platforms.php offers a template picker covering
-- the common storefronts/regions, but typing a fully custom name works too).

-- ----------------------------------------------------------------------------
-- keys
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `keys` (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id   INT UNSIGNED NOT NULL,
    campaign_id  INT UNSIGNED NULL,
    platform     VARCHAR(32) NOT NULL,
    key_value    VARCHAR(255) NOT NULL,
    status       ENUM('available','redeemed','exported') NOT NULL DEFAULT 'available',
    pool         ENUM('active','inactive') NOT NULL DEFAULT 'inactive',
    redeemed_at  DATETIME NULL,
    claimed_by_note VARCHAR(190) NULL, -- admin note for a key claimed outside the system (see migration 020) -- moot once a real redemption exists; a dropdown of existing users.name values can pre-fill this, but it's still just free text, no id of its own to link to
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_key_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    CONSTRAINT fk_key_campaign FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
    CONSTRAINT fk_key_platform FOREIGN KEY (platform) REFERENCES platforms(slug) ON DELETE CASCADE,
    UNIQUE KEY uniq_key_value (product_id, platform, key_value),
    INDEX idx_key_allocation (product_id, platform, status, pool)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- product_platforms — a product opting into an explicit set of supported
-- platforms, each with an optional claimable quota independent of actual
-- key stock (e.g. 500 keys imported, only 200 claimable right now). No
-- rows for a product = unrestricted: every configured platform is shown,
-- no quota cap, same as if this table didn't exist.
-- ----------------------------------------------------------------------------
-- claim_limit is enforced against a live COUNT(*) of redeemed keys at claim
-- time (not a separately-maintained counter, so it can't drift), with this
-- row itself locked via SELECT ... FOR UPDATE to serialize concurrent
-- claims against the same product+platform quota -- same pattern as
-- claim_link_products.claims_used.
CREATE TABLE IF NOT EXISTS product_platforms (
    product_id  INT UNSIGNED NOT NULL,
    platform    VARCHAR(32) NOT NULL,
    claim_limit INT UNSIGNED NULL, -- NULL = unlimited (bounded only by actual key stock)
    PRIMARY KEY (product_id, platform),
    CONSTRAINT fk_pp_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    CONSTRAINT fk_pp_platform FOREIGN KEY (platform) REFERENCES platforms(slug) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- claim_links
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS claim_links (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id       INT UNSIGNED NULL, -- optional: campaigns are for grouping/analytics, not required to hand out keys
    creator_group_id  INT UNSIGNED NULL,
    user_id           BIGINT UNSIGNED NOT NULL, -- the claimant/creator this link is for (see migrations 023/024)
    token             CHAR(32) NOT NULL UNIQUE, -- random_bytes(16) -> hex
    status            ENUM('active','disabled') NOT NULL DEFAULT 'active',
    expires_at        DATETIME NULL,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_link_campaign FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE SET NULL,
    CONSTRAINT fk_link_creator_group FOREIGN KEY (creator_group_id) REFERENCES creator_groups(id) ON DELETE SET NULL,
    CONSTRAINT fk_link_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_link_token (token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- n:m — which products a claim link grants access to, with the effective
-- (already-resolved) max claims for that product frozen at creation time.
CREATE TABLE IF NOT EXISTS claim_link_products (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    claim_link_id  INT UNSIGNED NOT NULL,
    product_id     INT UNSIGNED NOT NULL,
    max_claims     INT UNSIGNED NOT NULL DEFAULT 1,
    claims_used    INT UNSIGNED NOT NULL DEFAULT 0,
    CONSTRAINT fk_clp_link FOREIGN KEY (claim_link_id) REFERENCES claim_links(id) ON DELETE CASCADE,
    CONSTRAINT fk_clp_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_link_product (claim_link_id, product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- redemptions  (audit trail of every key handed out)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS redemptions (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    claim_link_id  INT UNSIGNED NOT NULL,
    product_id     INT UNSIGNED NOT NULL,
    key_id         BIGINT UNSIGNED NOT NULL,
    platform       VARCHAR(32) NOT NULL, -- snapshot at redemption time; not FK'd to platforms, so history survives a platform being removed later
    ip_hash        CHAR(64) NULL,
    user_agent     VARCHAR(500) NULL,
    redeemed_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_redemption_link FOREIGN KEY (claim_link_id) REFERENCES claim_links(id) ON DELETE CASCADE,
    CONSTRAINT fk_redemption_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    CONSTRAINT fk_redemption_key FOREIGN KEY (key_id) REFERENCES `keys`(id) ON DELETE CASCADE,
    INDEX idx_redemption_link_product (claim_link_id, product_id),
    INDEX idx_redemption_time (redeemed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- rate_limits  (sliding-window-ish fixed window counter per ip + endpoint)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS rate_limits (
    ip_hash      CHAR(64) NOT NULL,
    endpoint     VARCHAR(64) NOT NULL,
    window_start INT UNSIGNED NOT NULL, -- unix timestamp rounded down to window size
    request_count INT UNSIGNED NOT NULL DEFAULT 1,
    PRIMARY KEY (ip_hash, endpoint, window_start)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- page_styles  (designer-customizable CSS overrides, per themed page)
-- No row for a given style_key = the shipped default from resources/css-defaults
-- is served. A row here is an uploaded override; the default file itself is
-- never touched, so it's always available again via "Download default template".
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS page_styles (
    style_key         VARCHAR(64) NOT NULL PRIMARY KEY,
    css_content       MEDIUMTEXT NOT NULL,       -- published, live for every visitor
    updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    draft_css_content MEDIUMTEXT NULL,           -- uploaded but not yet published; visible only via admin preview
    draft_updated_at  DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- settings  (site-wide, admin-editable — currently: branding name + favicon)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS settings (
    setting_key    VARCHAR(64) NOT NULL PRIMARY KEY,
    setting_value  TEXT NULL,
    updated_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- schema_migrations  (tracks which sql/migrations/*.sql files have been
-- applied — see src/Migrator.php and admin/migrations.php. For a fresh
-- install, the setup wizard marks every existing migration file with
-- source='install' right after importing this file, since it already
-- includes everything they would add -- this keeps them out of "Pending"
-- without pretending they were actually run; the Applied list only shows
-- source='manual' rows, so a fresh install's Applied history starts empty.)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS schema_migrations (
    filename   VARCHAR(190) NOT NULL PRIMARY KEY,
    applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    source     VARCHAR(16) NOT NULL DEFAULT 'manual'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;
