/**
 * db.js — Inicializa o banco MySQL (schema only, sem seeds)
 * Para criar o admin: npm run seed
 * Para resetar tudo: npm run db:reset
 */
const mysql = require('mysql2/promise');
require('dotenv').config();

const dbConfig = {
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASS || '',
    database: process.env.DB_NAME || 'lovable_licenses',
    charset: 'utf8mb4'
};

const pool = mysql.createPool({
    ...dbConfig,
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0,
    connectTimeout: 20000
});

async function initDB() {
    try {
        console.log(`[DB] Verificando banco: ${dbConfig.database}...`);
        const conn = await pool.getConnection();
        console.log(`✅ Conexão MySQL OK.`);
        conn.release();

        // ── Tabelas principais ────────────────────────────────────────────────
        await pool.execute(`
            CREATE TABLE IF NOT EXISTS users (
                id            INT AUTO_INCREMENT PRIMARY KEY,
                username      VARCHAR(255) UNIQUE NOT NULL,
                password_hash VARCHAR(255) NOT NULL,
                role          VARCHAR(50) DEFAULT 'revendedor',
                credits       INT DEFAULT 0,
                parent_id     INT DEFAULT NULL,
                is_active     BOOLEAN DEFAULT TRUE,
                has_seen_tutorial BOOLEAN DEFAULT FALSE,
                created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                CONSTRAINT fk_user_parent FOREIGN KEY (parent_id) REFERENCES users(id) ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        `);

        await pool.execute(`
            CREATE TABLE IF NOT EXISTS licenses (
                id               INT AUTO_INCREMENT PRIMARY KEY,
                license_key      VARCHAR(50) UNIQUE NOT NULL,
                plan_name        VARCHAR(50) DEFAULT 'Mensal',
                status           VARCHAR(50) DEFAULT 'active',
                duration_days    INT NOT NULL DEFAULT 30,
                duration_minutes INT,
                created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                expires_at       DATETIME,
                activated_at     DATETIME,
                bound_user       VARCHAR(255),
                bound_at         DATETIME,
                last_seen_at     DATETIME,
                notes            TEXT,
                owner_id         INT,
                CONSTRAINT fk_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        `);

        await pool.execute(`
            CREATE TABLE IF NOT EXISTS audit_logs (
                id          INT AUTO_INCREMENT PRIMARY KEY,
                user_id     INT,
                action_type VARCHAR(50) NOT NULL,
                description TEXT,
                target_id   VARCHAR(255),
                ip_address  VARCHAR(50),
                created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        `);

        await pool.execute(`
            CREATE TABLE IF NOT EXISTS app_settings (
                setting_key   VARCHAR(50) PRIMARY KEY,
                setting_value TEXT,
                updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        `);

        // ── Tabelas Lovable ────────────────────────────────────────────────────
        await pool.execute(`
            CREATE TABLE IF NOT EXISTS lovable_accounts (
                id                  INT AUTO_INCREMENT PRIMARY KEY,
                license_key         VARCHAR(50) NOT NULL,
                device_id           VARCHAR(255),
                lovable_user_id     VARCHAR(255),
                lovable_email       VARCHAR(255),
                lovable_name        VARCHAR(255),
                workspace_id        VARCHAR(255),
                workspace_name      VARCHAR(255),
                workspace_plan      VARCHAR(100) DEFAULT 'free',
                credits_daily_used  INT DEFAULT 0,
                credits_daily_limit INT DEFAULT 0,
                feature_flags       JSON,
                app_check_token     TEXT,
                lovable_token       TEXT,
                lovable_avatar      TEXT,
                last_sync_at        DATETIME,
                created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE KEY uq_license_device (license_key, device_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        `);

        await pool.execute(`
            CREATE TABLE IF NOT EXISTS lovable_projects (
                id                  INT AUTO_INCREMENT PRIMARY KEY,
                lovable_user_id     VARCHAR(255) NOT NULL,
                workspace_id        VARCHAR(255),
                project_id          VARCHAR(255) NOT NULL,
                project_slug        VARCHAR(255),
                project_name        VARCHAR(255),
                project_url         VARCHAR(500),
                screenshot_url      TEXT,
                status              VARCHAR(50) DEFAULT 'unknown',
                edit_count          INT DEFAULT 0,
                message_count       INT DEFAULT 0,
                credit_total        DECIMAL(10,2) DEFAULT 0,
                latest_commit_sha   VARCHAR(100),
                is_starred          BOOLEAN DEFAULT FALSE,
                is_published        BOOLEAN DEFAULT FALSE,
                updated_at          DATETIME,
                synced_at           DATETIME,
                UNIQUE KEY uq_project (lovable_user_id, project_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        `);

        // Histórico de créditos por conta (snapshot a cada sync)
        await pool.execute(`
            CREATE TABLE IF NOT EXISTS lovable_credit_history (
                id                  INT AUTO_INCREMENT PRIMARY KEY,
                lovable_user_id     VARCHAR(255) NOT NULL,
                license_key         VARCHAR(50),
                workspace_id        VARCHAR(255),
                workspace_plan      VARCHAR(100),
                credits_daily_used  INT DEFAULT 0,
                credits_daily_limit INT DEFAULT 0,
                credit_total_all    DECIMAL(10,2) DEFAULT 0,
                recorded_at         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_user (lovable_user_id),
                INDEX idx_date (recorded_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        `);

        // ── Tabelas de Monitoramento ───────────────────────────────────────────
        // Erros enviados pela extensão quando algo falha
        await pool.execute(`
            CREATE TABLE IF NOT EXISTS extension_errors (
                id          INT AUTO_INCREMENT PRIMARY KEY,
                license_key VARCHAR(50),
                device_id   VARCHAR(255),
                error_type  VARCHAR(100) NOT NULL,
                error_msg   TEXT,
                error_stack TEXT,
                url         VARCHAR(500),
                version     VARCHAR(50),
                user_agent  TEXT,
                ip_address  VARCHAR(50),
                created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_lic (license_key),
                INDEX idx_type (error_type),
                INDEX idx_time (created_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        `);

        // Heartbeat periódico da extensão (a cada X minutos enquanto ativa)
        await pool.execute(`
            CREATE TABLE IF NOT EXISTS extension_heartbeats (
                id          INT AUTO_INCREMENT PRIMARY KEY,
                license_key VARCHAR(50) NOT NULL,
                device_id   VARCHAR(255),
                version     VARCHAR(50),
                page_host   VARCHAR(255),
                ip_address  VARCHAR(50),
                created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_lic (license_key),
                INDEX idx_time (created_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        `);

        // Log de validações de licença (sucesso + falha)
        await pool.execute(`
            CREATE TABLE IF NOT EXISTS license_validation_logs (
                id          INT AUTO_INCREMENT PRIMARY KEY,
                license_key VARCHAR(50),
                device_id   VARCHAR(255),
                result      ENUM('ok','invalid','expired','revoked','error') NOT NULL,
                ip_address  VARCHAR(50),
                user_agent  TEXT,
                created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_lic (license_key),
                INDEX idx_result (result),
                INDEX idx_time (created_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        `);

        // Mensagens de Broadcast (Avisos Globais)
        await pool.execute(`
            CREATE TABLE IF NOT EXISTS app_broadcasts (
                id          INT AUTO_INCREMENT PRIMARY KEY,
                message     TEXT NOT NULL,
                type        VARCHAR(50) DEFAULT 'info',
                is_active   BOOLEAN DEFAULT TRUE,
                created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        `);

        // Biblioteca de Prompts
        await pool.execute(`
            CREATE TABLE IF NOT EXISTS app_prompts (
                id          INT AUTO_INCREMENT PRIMARY KEY,
                title       VARCHAR(255) NOT NULL,
                content     TEXT NOT NULL,
                category    VARCHAR(100) DEFAULT 'Geral',
                is_active   BOOLEAN DEFAULT TRUE,
                created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        `);

        console.log(`✅ Schema verificado com sucesso.`);

        return pool;

    } catch (err) {
        console.error('❌ Erro ao inicializar MySQL:', err);
        process.exit(1);
    }
}

module.exports = {
    initDB,
    execute: (...args) => pool.execute(...args),
    query: (...args) => pool.query(...args),
    getConnection: () => pool.getConnection(),
    addLog: async (userId, action, desc, target = null, ip = null) => {
        try {
            await pool.execute(
                'INSERT INTO audit_logs (user_id, action_type, description, target_id, ip_address) VALUES (?, ?, ?, ?, ?)',
                [userId, action, desc, target, ip]
            );
        } catch (e) {
            console.error('[DB LOG ERROR]', e);
        }
    }
};
