-- Estrutura do banco de dados para tokens do núcleo API
-- Use o banco: drophub_global

USE drophub_global;

-- Tabela principal de tokens
CREATE TABLE IF NOT EXISTS tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    token VARCHAR(255) NOT NULL UNIQUE,
    user_id INT NOT NULL,
    whitelabel VARCHAR(100) NOT NULL,
    scopes TEXT NOT NULL,
    is_permanent BOOLEAN DEFAULT FALSE,
    expiration DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabela de refresh tokens
CREATE TABLE IF NOT EXISTS refresh_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    token_id INT NOT NULL,
    refresh_token VARCHAR(255) NOT NULL UNIQUE,
    expiration DATETIME NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (token_id) REFERENCES tokens(id) ON DELETE CASCADE
);

-- Índices para performance
CREATE INDEX idx_tokens_token ON tokens(token);
CREATE INDEX idx_tokens_whitelabel ON tokens(whitelabel);
CREATE INDEX idx_refresh_tokens_token ON refresh_tokens(refresh_token);
