<?php
/**
 * Script de Atualização do Banco de Dados de Produção
 * Este script deve ser enviado para o servidor e acessado via navegador para atualizar as tabelas.
 */

require_once __DIR__ . '/config/config.php';
require_once __DIR__ . '/src/db.php';

try {
    $pdo = get_db_connection();
    echo "<h1>Atualizando Banco de Dados...</h1>";

    // 1. Criar tabela system_settings se não existir
    $pdo->exec("CREATE TABLE IF NOT EXISTS system_settings (
        id INT AUTO_INCREMENT PRIMARY KEY,
        setting_key VARCHAR(50) UNIQUE NOT NULL,
        setting_value TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )");
    echo "Check: Tabela system_settings pronta.<br>";

    // Inserir configurações iniciais
    $pdo->exec("INSERT IGNORE INTO system_settings (setting_key, setting_value) VALUES ('alert_days', '15')");
    $pdo->exec("INSERT IGNORE INTO system_settings (setting_key, setting_value) VALUES ('alert_email', '')");

    // 2. Criar tabela suppliers se não existir
    $pdo->exec("CREATE TABLE IF NOT EXISTS suppliers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        tax_id VARCHAR(50),
        contact_person VARCHAR(255),
        email VARCHAR(255),
        phone VARCHAR(50),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )");
    echo "Check: Tabela suppliers pronta.<br>";

    // 3. Atualizar tabela contracts (adicionar colunas novas)
    // Verificar se supplier_id existe
    $stmt = $pdo->query("SHOW COLUMNS FROM contracts LIKE 'supplier_id'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE contracts ADD COLUMN supplier_id INT NULL AFTER unit_id");
        echo "Check: Coluna supplier_id adicionada a contracts.<br>";
    }

    // Verificar se last_notification_sent existe
    $stmt = $pdo->query("SHOW COLUMNS FROM contracts LIKE 'last_notification_sent'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE contracts ADD COLUMN last_notification_sent DATE NULL");
        echo "Check: Coluna last_notification_sent adicionada a contracts.<br>";
    }

    // Verificar se observations existe
    $stmt = $pdo->query("SHOW COLUMNS FROM contracts LIKE 'observations'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE contracts ADD COLUMN observations TEXT NULL");
        echo "Check: Coluna observations adicionada a contracts.<br>";
    }

    // 4. Criar tabela contract_history se não existir
    $pdo->exec("CREATE TABLE IF NOT EXISTS contract_history (
        id INT AUTO_INCREMENT PRIMARY KEY,
        contract_id INT NOT NULL,
        user_id INT NULL,
        username VARCHAR(100),
        action VARCHAR(50),
        details JSON,
        changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )");
    echo "Check: Tabela contract_history pronta.<br>";

    // 5. Criar tabela contract_files se não existir
    $pdo->exec("CREATE TABLE IF NOT EXISTS contract_files (
        id INT AUTO_INCREMENT PRIMARY KEY,
        contract_id INT NOT NULL,
        file_path VARCHAR(255) NOT NULL,
        is_amendment TINYINT(1) DEFAULT 0,
        observations TEXT NULL,
        username VARCHAR(100) NULL,
        user_id INT NULL,
        new_end_date DATE NULL,
        uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )");
    echo "Check: Tabela contract_files pronta.<br>";

    // Verificar se is_amendment existe em contract_files
    $stmt = $pdo->query("SHOW COLUMNS FROM contract_files LIKE 'is_amendment'");
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE contract_files ADD COLUMN is_amendment TINYINT(1) DEFAULT 0");
        echo "Check: Coluna is_amendment adicionada a contract_files.<br>";
    }

    // Verificar e adicionar colunas de detalhes do Aditivo
    $colsToAdd = [
        'observations' => "TEXT NULL AFTER is_amendment",
        'username' => "VARCHAR(100) NULL AFTER observations",
        'user_id' => "INT NULL AFTER username",
        'new_end_date' => "DATE NULL AFTER user_id"
    ];

    foreach ($colsToAdd as $col => $definition) {
        $stmt = $pdo->query("SHOW COLUMNS FROM contract_files LIKE '$col'");
        if (!$stmt->fetch()) {
            $pdo->exec("ALTER TABLE contract_files ADD COLUMN $col $definition");
            echo "Check: Coluna $col adicionada a contract_files.<br>";
        }
    }

    // 6. Criar tabela contract_signatures se não existir
    $pdo->exec("CREATE TABLE IF NOT EXISTS contract_signatures (
        id INT AUTO_INCREMENT PRIMARY KEY,
        contract_id INT NOT NULL,
        token VARCHAR(100) UNIQUE NOT NULL,
        is_signed BOOLEAN DEFAULT FALSE,
        signed_at DATETIME NULL,
        signer_ip VARCHAR(45) NULL,
        signer_name VARCHAR(255) NULL,
        signer_email VARCHAR(255) NULL,
        signature_image LONGTEXT NULL,
        user_agent TEXT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )");
    echo "Check: Tabela contract_signatures pronta.<br>";

    // Adicionar colunas extras se não existirem
    $colsToAddSignatures = [
        'signature_image' => "LONGTEXT NULL AFTER signer_email",
        'user_agent' => "TEXT NULL AFTER signature_image",
        'file_path' => "TEXT NULL AFTER contract_id" // Qual arquivo está sendo assinado (Contrato ou Aditivo)
    ];

    foreach ($colsToAddSignatures as $col => $definition) {
        $stmt = $pdo->query("SHOW COLUMNS FROM contract_signatures LIKE '$col'");
        if (!$stmt->fetch()) {
            $pdo->exec("ALTER TABLE contract_signatures ADD COLUMN $col $definition");
            echo "Check: Coluna $col adicionada a contract_signatures.<br>";
        }
    }

    // 7. Adicionar índices para performance
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_contracts_file_path ON contracts(file_path)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_contract_files_contract_id ON contract_files(contract_id)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_contract_files_file_path ON contract_files(file_path)");
    echo "Check: Índices de performance criados.<br>";

    // 8. Adicionar colunas na tabela users se não existirem
    $colsToAddUsers = [
        'role' => "ENUM('admin', 'user') DEFAULT 'user'",
        'nc_pass' => "TEXT NULL",
        'temp_token' => "VARCHAR(100) NULL",
        'token_expires' => "DATETIME NULL"
    ];

    foreach ($colsToAddUsers as $col => $definition) {
        $stmt = $pdo->query("SHOW COLUMNS FROM users LIKE '$col'");
        if (!$stmt->fetch()) {
            $pdo->exec("ALTER TABLE users ADD COLUMN $col $definition");
            echo "Check: Coluna $col adicionada a users.<br>";
        }
    }

    echo "<h2 style='color:green'>Banco de dados atualizado com sucesso!</h2>";
    echo "<p>Agora você pode excluir este arquivo por segurança.</p>";
    echo "<a href='index.php'>Voltar ao Sistema</a>";

} catch (Exception $e) {
    echo "<h2 style='color:red'>Erro ao atualizar banco: " . $e->getMessage() . "</h2>";
}
