CREATE DATABASE IF NOT EXISTS verification_api;
USE verification_api;

-- Tabela de API Keys
CREATE TABLE api_keys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    api_key VARCHAR(64) UNIQUE NOT NULL,
    site_url VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabela de logs de verificação
CREATE TABLE verification_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    site_url VARCHAR(255) NOT NULL,
    api_key VARCHAR(64) NOT NULL,
    result ENUM('approved', 'rejected') NOT NULL,
    age_estimated INT,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (api_key) REFERENCES api_keys(api_key)
);

-- Tabela de tokens temporários
CREATE TABLE verification_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    token VARCHAR(64) UNIQUE NOT NULL,
    site_url VARCHAR(255) NOT NULL,
    is_adult BOOLEAN NOT NULL,
    used BOOLEAN DEFAULT FALSE,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);