-- Sistema SaaS Completo - Schema do Banco de Dados
-- Banco: MySQL

-- Tabela de Planos
CREATE TABLE IF NOT EXISTS plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    credits DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    api_access ENUM('limited', 'unlimited') DEFAULT 'limited',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO plans (name, description, price, credits, api_access) VALUES
('Free', 'Plano gratuito com R$5,00 saldo', 0.00, 5.00, 'limited'),
('Pro', 'Plano profissional com R$100,00 saldo', 49.90, 100.00, 'limited'),
('Premium', 'Plano premium ilimitado', 199.90, 0.00, 'unlimited');

-- Tabela de Usuários
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    cpf VARCHAR(14),
    phone VARCHAR(20),
    plan_id INT DEFAULT 1,
    balance DECIMAL(10, 2) DEFAULT 5.00,
    api_token VARCHAR(255) UNIQUE,
    asaas_customer_id VARCHAR(100),
    is_admin ENUM('yes', 'no') DEFAULT 'no',
    is_active ENUM('yes', 'no') DEFAULT 'yes',
    reset_token VARCHAR(255) DEFAULT NULL,
    reset_expires DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (plan_id) REFERENCES plans(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tabela de Logs de Uso
CREATE TABLE IF NOT EXISTS usage_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    query_type VARCHAR(50) NOT NULL,
    query_value VARCHAR(255) NOT NULL,
    dataset VARCHAR(100),
    value_used DECIMAL(10, 2) DEFAULT 0.00,
    cost_used DECIMAL(10, 2) DEFAULT 0.00,
    result TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tabela de Cache
CREATE TABLE IF NOT EXISTS cache (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cache_key VARCHAR(255) NOT NULL UNIQUE,
    cache_value TEXT NOT NULL,
    expires_at DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_cache_key (cache_key),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tabela de Pagamentos
CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    asaas_charge_id VARCHAR(100) UNIQUE,
    asaas_payment_id VARCHAR(100),
    amount DECIMAL(10, 2) NOT NULL,
    credits_purchased DECIMAL(10, 2) NOT NULL,
    payment_method ENUM('pix', 'boleto', 'card') DEFAULT 'pix',
    status ENUM('pending', 'confirmed', 'failed', 'refunded') DEFAULT 'pending',
    webhook_data TEXT,
    confirmed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tabela de Configurações
CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(50) NOT NULL UNIQUE,
    setting_value TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO settings (setting_key, setting_value) VALUES
('asaas_environment', 'sandbox'),
('asaas_api_key', ''),
('asaas_api_key_hmlg', ''),
('asaas_api_url', 'https://www.asaas.com/api/v3'),
('lazydata_api_key', ''),
('cache_ttl_minutes', '60'),
('credit_value', '0.45'),
('profit_margin', '50'),
('smtp_host', ''),
('smtp_port', '587'),
('smtp_user', ''),
('smtp_pass', ''),
('smtp_secure', 'tls'),
('smtp_from_email', ''),
('smtp_from_name', 'SaaS Consultas'),
('webhook_token', '');

-- Tabela de Preços por Dataset
CREATE TABLE IF NOT EXISTS dataset_prices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    dataset_key VARCHAR(50) NOT NULL UNIQUE,
    dataset_name VARCHAR(100) NOT NULL,
    lazydata_price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    selling_price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO dataset_prices (dataset_key, dataset_name, lazydata_price, selling_price) VALUES
('basic', 'Dados Básicos', 0.06, 0.10),
('cbo', 'Ocupação', 0.04, 0.06),
('documents', 'Documentos', 0.18, 0.27),
('emails', 'E-mails', 0.08, 0.12),
('addresses', 'Endereços', 0.06, 0.09),
('phones', 'Telefones', 0.05, 0.08),
('jobs', 'Empregos', 0.06, 0.09),
('household', 'Household', 0.06, 0.09),
('rf', 'Situação Cadastral', 0.10, 0.15),
('score', 'Score de crédito', 3.11, 4.67);