-- The Copy Lab · tablas exclusivas (prefijo copylab_). Idempotente.

CREATE TABLE IF NOT EXISTS copylab_access (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(254) NOT NULL,
    name VARCHAR(190) NOT NULL DEFAULT '',
    status ENUM('active','revoked') NOT NULL DEFAULT 'revoked',
    source VARCHAR(80) NOT NULL DEFAULT 'centralize',
    order_ref VARCHAR(190) NOT NULL DEFAULT '',
    login_count INT UNSIGNED NOT NULL DEFAULT 0,
    last_login_at DATETIME NULL,
    granted_at DATETIME NULL,
    revoked_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY copylab_access_email (email),
    KEY copylab_access_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS copylab_sessions (
    token_hash CHAR(64) NOT NULL PRIMARY KEY,
    access_id INT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL,
    last_seen_at DATETIME NOT NULL,
    expires_at DATETIME NOT NULL,
    revoked_at DATETIME NULL,
    ip_hash CHAR(64) NOT NULL DEFAULT '',
    user_agent VARCHAR(255) NOT NULL DEFAULT '',
    KEY copylab_sessions_access (access_id),
    KEY copylab_sessions_expires (expires_at),
    CONSTRAINT copylab_sessions_access_fk FOREIGN KEY (access_id) REFERENCES copylab_access(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS copylab_progress (
    access_id INT UNSIGNED NOT NULL PRIMARY KEY,
    state_json MEDIUMTEXT NOT NULL,
    revision INT UNSIGNED NOT NULL DEFAULT 1,
    updated_at DATETIME NOT NULL,
    CONSTRAINT copylab_progress_access_fk FOREIGN KEY (access_id) REFERENCES copylab_access(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS copylab_webhook_events (
    event_key CHAR(64) NOT NULL PRIMARY KEY,
    payload_hash CHAR(64) NOT NULL,
    email VARCHAR(254) NOT NULL DEFAULT '',
    status VARCHAR(16) NOT NULL DEFAULT '',
    created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS copylab_rate_limits (
    bucket CHAR(64) NOT NULL,
    window_start BIGINT UNSIGNED NOT NULL,
    hits INT UNSIGNED NOT NULL DEFAULT 1,
    PRIMARY KEY (bucket, window_start)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS copylab_admin_sessions (
    token_hash CHAR(64) NOT NULL PRIMARY KEY,
    created_at DATETIME NOT NULL,
    expires_at DATETIME NOT NULL,
    ip_hash CHAR(64) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS copylab_activity (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    access_id INT UNSIGNED NULL,
    email VARCHAR(254) NULL,
    event VARCHAR(40) NOT NULL,
    detail VARCHAR(255) NOT NULL DEFAULT '',
    ip_hash CHAR(64) NOT NULL DEFAULT '',
    created_at DATETIME NOT NULL,
    KEY copylab_activity_created (created_at),
    KEY copylab_activity_access (access_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
