-- 数据库设计遵循规范：主键用BIGINT UNSIGNED，字段非空设默认值，有create_time/update_time

CREATE DATABASE IF NOT EXISTS api_platform CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE api_platform;

-- 第三方应用表
CREATE TABLE IF NOT EXISTS apps (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    app_name        VARCHAR(100) NOT NULL DEFAULT '',
    app_key         VARCHAR(64) NOT NULL DEFAULT '',      -- 应用标识（如：app_xxx）
    app_secret      VARCHAR(128) NOT NULL DEFAULT '',     -- 应用密钥（仅存储哈希）
    status          VARCHAR(20) NOT NULL DEFAULT 'enabled', -- enabled/disabled
    rate_limit      INT UNSIGNED NOT NULL DEFAULT 1000,     -- 每分钟请求上限
    permissions     VARCHAR(500) NOT NULL DEFAULT '',      -- 权限范围，逗号分隔
    callback_url    VARCHAR(255) NOT NULL DEFAULT '',
    create_time     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_app_key (app_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- API密钥表（每个应用可有多组密钥，支持轮换）
CREATE TABLE IF NOT EXISTS api_keys (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    app_id          BIGINT UNSIGNED NOT NULL DEFAULT 0,
    api_key         VARCHAR(64) NOT NULL DEFAULT '',       -- 对外使用的密钥
    api_secret      VARCHAR(128) NOT NULL DEFAULT '',      -- 签名用的密钥
    status          VARCHAR(20) NOT NULL DEFAULT 'enabled', -- enabled/disabled/revoked
    expire_time     DATETIME NOT NULL DEFAULT '2099-12-31 23:59:59',
    last_used_time  DATETIME,
    usage_count     BIGINT UNSIGNED NOT NULL DEFAULT 0,
    create_time     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_api_key (api_key),
    KEY idx_app_id (app_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 请求日志表（用于审计和限流统计）
CREATE TABLE IF NOT EXISTS api_logs (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    api_key_id      BIGINT UNSIGNED NOT NULL DEFAULT 0,
    app_id          BIGINT UNSIGNED NOT NULL DEFAULT 0,
    request_id      VARCHAR(64) NOT NULL DEFAULT '',
    method          VARCHAR(10) NOT NULL DEFAULT '',
    endpoint        VARCHAR(255) NOT NULL DEFAULT '',
    params          TEXT,
    response_code   INT NOT NULL DEFAULT 0,
    response_time   INT NOT NULL DEFAULT 0,                -- 毫秒
    client_ip       VARCHAR(45) NOT NULL DEFAULT '',
    user_agent      VARCHAR(255) NOT NULL DEFAULT '',
    create_time     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_api_key_time (api_key_id, create_time),
    KEY idx_request_id (request_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 限流计数表（Redis替代方案，也可用Redis做）
CREATE TABLE IF NOT EXISTS rate_limit_counters (
    api_key_id      BIGINT UNSIGNED NOT NULL DEFAULT 0,
    window_start    DATETIME NOT NULL,
    request_count   INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (api_key_id, window_start)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入示例数据
INSERT INTO apps (app_name, app_key, app_secret, status, rate_limit, permissions) VALUES
('示例商户A', 'app_demo_001', SHA2('demo_secret_001', 256), 'enabled', 100, 'user:read,order:read');

INSERT INTO api_keys (app_id, api_key, api_secret, status, expire_time) VALUES
(1, 'ak_demo_001_abc123', SHA2('sk_demo_001_xyz789', 256), 'enabled', '2099-12-31 23:59:59');