-- ============================================================
-- Tivro Passkeys - Database Schema
-- Version: 1.0.0
-- Engine: InnoDB, Charset: utf8mb4
-- NOTE: Tables are normally created via PHP migrations on activation.
-- This SQL is provided for reference and manual setup.
-- ============================================================

-- ── License Cache ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `mod_tivropasskeys_license` (
    `id`               INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `license_key`      VARCHAR(128)    DEFAULT NULL,
    `status`           VARCHAR(32)     NOT NULL DEFAULT 'unconfigured',
    `bound_domain`     VARCHAR(255)    DEFAULT NULL,
    `expires_at`       DATE            DEFAULT NULL,
    `message`          TEXT            DEFAULT NULL,
    `last_checked_at`  DATETIME        DEFAULT NULL,
    `next_check_at`    DATETIME        DEFAULT NULL,
    `update_data`      TEXT            DEFAULT NULL     COMMENT 'Cached update check response JSON',
    `update_checked_at` DATETIME       DEFAULT NULL,
    `created_at`       DATETIME        DEFAULT NULL,
    `updated_at`       DATETIME        DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Passkey Credentials ────────────────────────────────────
CREATE TABLE IF NOT EXISTS `mod_tivropasskeys_credentials` (
    `id`               INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `client_id`        INT UNSIGNED    NOT NULL,
    `credential_id`    VARCHAR(512)    NOT NULL COMMENT 'base64url encoded credential ID',
    `public_key`       MEDIUMTEXT      NOT NULL COMMENT 'base64 encoded serialized PublicKeyCredentialSource',
    `sign_count`       BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `transports`       VARCHAR(255)    DEFAULT NULL COMMENT 'comma-separated: usb,nfc,ble,internal,hybrid',
    `aaguid`           VARCHAR(64)     DEFAULT NULL,
    `friendly_name`    VARCHAR(128)    NOT NULL DEFAULT 'My Passkey',
    `last_used_at`     DATETIME        DEFAULT NULL,
    `created_at`       DATETIME        DEFAULT NULL,
    `updated_at`       DATETIME        DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_credential_id` (`credential_id`(255)),
    KEY `idx_client_id` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── WebAuthn Challenges ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS `mod_tivropasskeys_challenges` (
    `id`               INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `session_id`       VARCHAR(128)    NOT NULL,
    `type`             VARCHAR(32)     NOT NULL COMMENT 'registration | authentication',
    `challenge_data`   MEDIUMTEXT      NOT NULL COMMENT 'JSON encoded options',
    `client_id`        INT UNSIGNED    DEFAULT NULL,
    `expires_at`       DATETIME        NOT NULL,
    `created_at`       DATETIME        DEFAULT NULL,
    `updated_at`       DATETIME        DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_session_type` (`session_id`, `type`),
    KEY `idx_expires_at` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Audit Log ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `mod_tivropasskeys_audit_log` (
    `id`               INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `client_id`        INT UNSIGNED    DEFAULT NULL,
    `event`            VARCHAR(64)     NOT NULL,
    `detail`           TEXT            DEFAULT NULL,
    `ip`               VARCHAR(64)     DEFAULT NULL,
    `user_agent`       VARCHAR(255)    DEFAULT NULL,
    `created_at`       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_event` (`event`),
    KEY `idx_client_id` (`client_id`),
    KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Rate Limiting ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `mod_tivropasskeys_ratelimit` (
    `id`               INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `identifier`       VARCHAR(128)    NOT NULL COMMENT 'ip:client_id or ip',
    `action`           VARCHAR(64)     NOT NULL,
    `attempts`         SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `window_start`     DATETIME        NOT NULL,
    `blocked_until`    DATETIME        DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_identifier_action` (`identifier`, `action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Email OTP ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `mod_tivropasskeys_otp` (
    `id`               INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `client_id`        INT UNSIGNED    NOT NULL,
    `token_hash`       VARCHAR(128)    NOT NULL COMMENT 'bcrypt hash of OTP',
    `purpose`          VARCHAR(32)     NOT NULL DEFAULT 'stepup' COMMENT 'stepup | fallback',
    `used`             TINYINT(1)      NOT NULL DEFAULT 0,
    `expires_at`       DATETIME        NOT NULL,
    `created_at`       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_client_purpose` (`client_id`, `purpose`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Known Devices ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `mod_tivropasskeys_known_devices` (
    `id`               INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `client_id`        INT UNSIGNED    NOT NULL,
    `device_fingerprint` VARCHAR(128)  NOT NULL COMMENT 'SHA-256 of UA + Accept-Language',
    `ip`               VARCHAR(64)     DEFAULT NULL,
    `user_agent`       VARCHAR(255)    DEFAULT NULL,
    `first_seen_at`    DATETIME        NOT NULL,
    `last_seen_at`     DATETIME        NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_client_device` (`client_id`, `device_fingerprint`),
    KEY `idx_client_id` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;