<?php
require_once __DIR__ . '/../config/config.php';

function ensure_system_settings_schema(PDO $pdo) {
    try {
        $tableExists = (bool)$pdo->query("SHOW TABLES LIKE 'system_settings'")->fetchColumn();
        if (!$tableExists) {
            $pdo->exec("
                CREATE TABLE IF NOT EXISTS system_settings (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    setting_key VARCHAR(100) NOT NULL UNIQUE,
                    setting_value TEXT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
            ");
        }

        $defaults = [
            'alert_days' => '15',
            'alert_email' => '',
            'nc_path' => defined('NC_REMOTE_ROOT_DEFAULT') ? (string)NC_REMOTE_ROOT_DEFAULT : (defined('NC_CONTRACTS_PATH_DEFAULT') ? (string)NC_CONTRACTS_PATH_DEFAULT : ''),
            'nc_webdav_user' => '',
            'nc_webdav_pass' => '',
        ];

        $stmt = $pdo->prepare("INSERT IGNORE INTO system_settings (setting_key, setting_value) VALUES (:k, :v)");
        foreach ($defaults as $key => $value) {
            $stmt->execute([':k' => $key, ':v' => $value]);
        }
    } catch (PDOException $e) {
        return;
    }
}

function ensure_contract_history_schema(PDO $pdo) {
    try {
        $tableExists = (bool)$pdo->query("SHOW TABLES LIKE 'contract_history'")->fetchColumn();
        if (!$tableExists) {
            $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) NULL,
                    action VARCHAR(50) NOT NULL,
                    details JSON NULL,
                    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    INDEX idx_contract_history_contract_id (contract_id)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
            ");
        }
    } catch (PDOException $e) {
        return;
    }
}

function ensure_contract_signatures_schema(PDO $pdo) {
    try {
        $tableExists = (bool)$pdo->query("SHOW TABLES LIKE 'contract_signatures'")->fetchColumn();
        if (!$tableExists) {
            $pdo->exec("
                CREATE TABLE IF NOT EXISTS contract_signatures (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    contract_id INT NOT NULL,
                    file_path TEXT 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,
                    INDEX idx_contract_signatures_contract_id (contract_id),
                    INDEX idx_contract_signatures_token (token)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
            ");
        }
    } catch (PDOException $e) {
        return;
    }
}

function ensure_contracts_core_schema(PDO $pdo) {
    try {
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(255) NOT NULL UNIQUE,
                role ENUM('admin','user') DEFAULT 'user',
                nc_pass TEXT NULL,
                temp_token VARCHAR(100) NULL,
                token_expires DATETIME NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");
    } catch (PDOException $e) {
        return;
    }

    try {
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS units (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                document VARCHAR(50) NULL,
                email VARCHAR(255) NULL,
                phone VARCHAR(50) NULL,
                address TEXT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE KEY uq_units_name (name)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");
    } catch (PDOException $e) {
        return;
    }

    try {
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS suppliers (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                tax_id VARCHAR(50) NULL,
                contact_person VARCHAR(255) NULL,
                email VARCHAR(255) NULL,
                phone VARCHAR(50) NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE KEY uq_suppliers_name (name)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");
    } catch (PDOException $e) {
        return;
    }

    try {
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS contracts (
                id INT AUTO_INCREMENT PRIMARY KEY,
                unit_id INT NULL,
                contract_type VARCHAR(100) NULL,
                contract_number VARCHAR(100) NULL,
                supplier_id INT NULL,
                supplier VARCHAR(255) NULL,
                title VARCHAR(255) NULL,
                description TEXT NULL,
                observations TEXT NULL,
                value DECIMAL(10,2) NULL,
                start_date DATE NULL,
                end_date DATE NULL,
                alert_date DATE NULL,
                status VARCHAR(20) DEFAULT 'draft',
                file_path TEXT NULL,
                last_notification_sent DATE NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_contracts_unit_id (unit_id),
                INDEX idx_contracts_status (status),
                INDEX idx_contracts_end_date (end_date),
                INDEX idx_contracts_alert_date (alert_date)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");
    } catch (PDOException $e) {
        return;
    }

    $ensureColumn = static function(string $table, string $column, string $ddl) use ($pdo): void {
        try {
            $stmt = $pdo->query("SHOW COLUMNS FROM `$table` LIKE " . $pdo->quote($column));
            $exists = (bool)$stmt->fetch();
            if (!$exists) {
                $pdo->exec("ALTER TABLE `$table` ADD COLUMN $ddl");
            }
        } catch (PDOException $e) {
            return;
        }
    };

    $ensureColumn('users', 'nc_pass', "nc_pass TEXT NULL");
    $ensureColumn('users', 'temp_token', "temp_token VARCHAR(100) NULL");
    $ensureColumn('users', 'token_expires', "token_expires DATETIME NULL");
    $ensureColumn('users', 'role', "role ENUM('admin','user') DEFAULT 'user'");

    $ensureColumn('contracts', 'unit_id', "unit_id INT NULL");
    $ensureColumn('contracts', 'contract_type', "contract_type VARCHAR(100) NULL");
    $ensureColumn('contracts', 'contract_number', "contract_number VARCHAR(100) NULL");
    $ensureColumn('contracts', 'supplier_id', "supplier_id INT NULL");
    $ensureColumn('contracts', 'supplier', "supplier VARCHAR(255) NULL");
    $ensureColumn('contracts', 'observations', "observations TEXT NULL");
    $ensureColumn('contracts', 'alert_date', "alert_date DATE NULL");
    $ensureColumn('contracts', 'last_notification_sent', "last_notification_sent DATE NULL");
    $ensureColumn('contracts', 'created_at', "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP");

    try {
        $statusInfo = $pdo->query("SHOW COLUMNS FROM `contracts` LIKE 'status'")->fetch(PDO::FETCH_ASSOC);
        $statusType = strtolower((string)($statusInfo['Type'] ?? ''));
        if ($statusType !== '' && strpos($statusType, 'enum(') === 0 && stripos($statusType, 'renewing') === false) {
            $pdo->exec("ALTER TABLE `contracts` MODIFY COLUMN `status` VARCHAR(20) DEFAULT 'draft'");
        }
    } catch (PDOException $e) {
    }

    try {
        $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,
                INDEX idx_contract_files_contract_id (contract_id),
                INDEX idx_contract_files_file_path (file_path)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");
    } catch (PDOException $e) {
        return;
    }
}

function get_db_connection() {
    try {
        $dsn = "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=utf8mb4";
        $pdo = new PDO($dsn, DB_USER, DB_PASS);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
        ensure_system_settings_schema($pdo);
        ensure_contract_history_schema($pdo);
        ensure_contract_signatures_schema($pdo);
        ensure_contracts_core_schema($pdo);
        return $pdo;
    } catch (PDOException $e) {
        die("Erro de conexão com o banco de dados: " . $e->getMessage());
    }
}
