-- Migration 012 — Tokens cifrados + states en DB (login rápido)
-- Corre después de 001–011.

-- Estados OAuth en DB (más robusto que SESSION; funciona con load balancers)
CREATE TABLE IF NOT EXISTS oauth_states (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    state      CHAR(64)  NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_oauth_states_state (state),
    INDEX  idx_oauth_states_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tokens de Google cifrados por usuario (AES-256-GCM)
CREATE TABLE IF NOT EXISTS google_tokens (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id       BIGINT UNSIGNED NOT NULL,
    refresh_token TEXT     NULL,       -- cifrado con AES-256-GCM
    revoked_at    TIMESTAMP NULL,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_google_tokens_user (user_id),
    CONSTRAINT fk_google_tokens_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Timestamp de último login en users
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_login_at TIMESTAMP NULL;