-- 20260305_001_security_core.sql
ALTER TABLE customers
    ADD COLUMN IF NOT EXISTS max_pc_devices INT NOT NULL DEFAULT 1 AFTER max_devices,
    ADD COLUMN IF NOT EXISTS max_web_sessions INT NOT NULL DEFAULT 0 AFTER max_pc_devices,
    ADD COLUMN IF NOT EXISTS is_demo TINYINT(1) NOT NULL DEFAULT 0 AFTER max_web_sessions,
    ADD COLUMN IF NOT EXISTS demo_expires_at DATETIME NULL AFTER is_demo,
    ADD COLUMN IF NOT EXISTS license_status VARCHAR(20) NOT NULL DEFAULT 'active' AFTER demo_expires_at,
    ADD COLUMN IF NOT EXISTS license_key_id VARCHAR(80) DEFAULT NULL AFTER license_status,
    ADD COLUMN IF NOT EXISTS last_web_login_date DATETIME NULL AFTER last_pc_login_date;

UPDATE customers
SET max_pc_devices = GREATEST(1, COALESCE(max_pc_devices, max_devices, 1));

UPDATE customers
SET max_web_sessions = GREATEST(0, COALESCE(max_web_sessions, 0));

UPDATE customers
SET max_devices = max_pc_devices
WHERE COALESCE(max_devices, 0) <> max_pc_devices;

CREATE TABLE IF NOT EXISTS plan_catalog (
    plan_type VARCHAR(10) PRIMARY KEY,
    plan_name VARCHAR(80) NOT NULL,
    pc_limit_mode VARCHAR(20) NOT NULL,
    web_limit_mode VARCHAR(20) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS plan_feature_map (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    plan_type VARCHAR(10) NOT NULL,
    feature_key VARCHAR(100) NOT NULL,
    is_enabled TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_plan_feature (plan_type, feature_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS license_public_keys (
    key_id VARCHAR(80) PRIMARY KEY,
    algorithm VARCHAR(30) NOT NULL,
    public_key TEXT NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS api_tokens (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    token_hash CHAR(64) NOT NULL,
    scope VARCHAR(80) NOT NULL,
    metadata_json JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    last_used_at DATETIME DEFAULT NULL,
    revoked_at DATETIME DEFAULT NULL,
    UNIQUE KEY uniq_token_hash (token_hash),
    KEY idx_customer_scope (customer_id, scope),
    KEY idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS api_nonces (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    token_id BIGINT NOT NULL,
    nonce_value VARCHAR(120) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_token_nonce (token_id, nonce_value),
    KEY idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS api_rate_limit_counters (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    bucket_key VARCHAR(190) NOT NULL,
    window_start BIGINT NOT NULL,
    request_count INT NOT NULL DEFAULT 0,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_bucket_window (bucket_key, window_start),
    KEY idx_window_start (window_start)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE customer_devices
    ADD COLUMN IF NOT EXISTS device_uid VARCHAR(120) DEFAULT NULL AFTER hardware_id,
    ADD COLUMN IF NOT EXISTS device_type VARCHAR(10) NOT NULL DEFAULT 'pc' AFTER device_uid,
    ADD COLUMN IF NOT EXISTS user_agent_hash CHAR(64) DEFAULT NULL AFTER device_name,
    ADD COLUMN IF NOT EXISTS ip_address VARCHAR(64) DEFAULT NULL AFTER user_agent_hash,
    ADD COLUMN IF NOT EXISTS last_active_at DATETIME DEFAULT CURRENT_TIMESTAMP AFTER last_login,
    ADD COLUMN IF NOT EXISTS is_blocked TINYINT(1) NOT NULL DEFAULT 0 AFTER last_active_at,
    ADD COLUMN IF NOT EXISTS updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at;

CREATE TABLE IF NOT EXISTS customer_web_sessions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    device_id INT DEFAULT NULL,
    session_uuid CHAR(36) NOT NULL,
    session_token_hash CHAR(64) NOT NULL,
    ip_address VARCHAR(64) DEFAULT NULL,
    user_agent TEXT,
    login_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_active_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME DEFAULT NULL,
    logout_at DATETIME DEFAULT NULL,
    logout_reason VARCHAR(50) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_session_uuid (session_uuid),
    KEY idx_customer_active (customer_id, logout_at, last_active_at),
    KEY idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS job_runs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    job_name VARCHAR(120) NOT NULL,
    status VARCHAR(20) NOT NULL,
    details_json JSON,
    started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    finished_at DATETIME DEFAULT NULL,
    KEY idx_job_status_started (job_name, status, started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO plan_catalog (plan_type, plan_name, pc_limit_mode, web_limit_mode)
VALUES
    ('1', 'Plan 1', 'fixed_1', 'fixed_0'),
    ('2', 'Plan 2', 'editable', 'fixed_0'),
    ('3', 'Plan 3', 'editable', 'editable')
ON DUPLICATE KEY UPDATE
    plan_name = VALUES(plan_name),
    pc_limit_mode = VALUES(pc_limit_mode),
    web_limit_mode = VALUES(web_limit_mode);

INSERT INTO plan_feature_map (plan_type, feature_key, is_enabled)
VALUES
    ('1', 'WEB_ACCESS', 0),
    ('1', 'BACKUP_AUTO', 0),
    ('1', 'BACKUP_MANUAL', 0),
    ('2', 'WEB_ACCESS', 0),
    ('2', 'BACKUP_AUTO', 0),
    ('2', 'BACKUP_MANUAL', 0),
    ('3', 'WEB_ACCESS', 1),
    ('3', 'BACKUP_AUTO', 1),
    ('3', 'BACKUP_MANUAL', 1)
ON DUPLICATE KEY UPDATE is_enabled = VALUES(is_enabled);


