<?php
/**
 * Enterprise HRM ERP System - DFC Complete Setup Runner
 * Run this script to create/update all DFC database tables
 */

require_once __DIR__ . '/../../includes/init.php';

// Check if user is admin
if (!hasAdminAccess()) {
    die('Access denied. Admin privileges required.');
}

$errors = [];
$success = [];

try {
    $db = getDB();
    $db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
    
    // Read SQL file
    $sqlFile = __DIR__ . '/dfc_complete_setup.sql';
    if (!file_exists($sqlFile)) {
        throw new Exception("SQL file not found: {$sqlFile}");
    }
    
    $sql = file_get_contents($sqlFile);
    
    // Remove comments and split by semicolon, but handle prepared statements carefully
    $sql = preg_replace('/--.*$/m', '', $sql);
    
    // Split by semicolon, but preserve prepared statement blocks
    $statements = [];
    $currentStatement = '';
    
    $lines = explode("\n", $sql);
    foreach ($lines as $line) {
        $trimmed = trim($line);
        if (empty($trimmed)) continue;
        
        // Skip prepared statement blocks (SET @, PREPARE, EXECUTE, DEALLOCATE)
        if (preg_match('/^(SET @|PREPARE|EXECUTE|DEALLOCATE)/i', $trimmed)) {
            continue; // Skip prepared statement syntax - we'll handle constraints in PHP
        }
        
        $currentStatement .= $trimmed . ' ';
        
        if (substr(rtrim($trimmed), -1) === ';') {
            $stmt = trim($currentStatement);
            if (!empty($stmt) && strlen($stmt) > 1) {
                $statements[] = rtrim($stmt, ';');
            }
            $currentStatement = '';
        }
    }
    
    // Add any remaining statement
    if (!empty(trim($currentStatement))) {
        $statements[] = trim(rtrim($currentStatement, ';'));
    }
    
    // Execute CREATE TABLE statements in transaction
    $db->beginTransaction();
    foreach ($statements as $statement) {
        $statement = trim($statement);
        if (empty($statement) || strlen($statement) < 10) continue;
        
        try {
            $db->exec($statement);
            $success[] = "Executed: " . substr($statement, 0, 60) . "...";
        } catch (PDOException $e) {
            // Ignore "table already exists", "duplicate column", and "duplicate key" errors
            $errorMsg = $e->getMessage();
            if (strpos($errorMsg, 'already exists') !== false || 
                strpos($errorMsg, 'Duplicate column') !== false ||
                strpos($errorMsg, 'Duplicate key') !== false ||
                strpos($errorMsg, '1060') !== false || // Duplicate column name
                strpos($errorMsg, '1061') !== false || // Duplicate key name
                strpos($errorMsg, 'Cannot add foreign key') !== false) { // Foreign key already exists
                // Silently ignore - column/table/key already exists
            } else {
                $errors[] = "Error: " . $errorMsg . " (Statement: " . substr($statement, 0, 60) . "...)";
            }
        }
    }
    
    // Commit CREATE TABLE statements
    if ($db->inTransaction()) {
        $db->commit();
    }
    
    // Get database name (outside transaction)
    $dbname = $db->query("SELECT DATABASE()")->fetchColumn();
    
    // Add foreign key constraints and columns (execute outside transaction to avoid issues)
    // Add foreign key for dfc_periods.site_id if sites table exists
    try {
        $stmt = $db->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = ? AND table_name = 'sites'");
        $stmt->execute([$dbname]);
        $checkSites = $stmt->fetchColumn();
        
        if ($checkSites > 0) {
            $stmt = $db->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE table_schema = ? AND table_name = 'dfc_periods' AND constraint_name = 'fk_dfcp_site'");
            $stmt->execute([$dbname]);
            $checkConstraint = $stmt->fetchColumn();
            
            if ($checkConstraint == 0) {
                $db->exec("ALTER TABLE dfc_periods ADD CONSTRAINT fk_dfcp_site FOREIGN KEY (site_id) REFERENCES sites(site_id) ON DELETE RESTRICT");
                $success[] = "Added foreign key: fk_dfcp_site";
            }
        }
    } catch (PDOException $e) {
        if (strpos($e->getMessage(), 'Duplicate key') === false && strpos($e->getMessage(), 'already exists') === false) {
            $errors[] = "Foreign key fk_dfcp_site: " . $e->getMessage();
        }
    }
    
    // Add foreign key for dfc_periods.created_by if users table exists
    try {
        $stmt = $db->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = ? AND table_name = 'users'");
        $stmt->execute([$dbname]);
        $checkUsers = $stmt->fetchColumn();
        
        if ($checkUsers > 0) {
            $stmt = $db->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE table_schema = ? AND table_name = 'dfc_periods' AND constraint_name = 'fk_dfcp_created_by'");
            $stmt->execute([$dbname]);
            $checkConstraint = $stmt->fetchColumn();
            
            if ($checkConstraint == 0) {
                $db->exec("ALTER TABLE dfc_periods ADD CONSTRAINT fk_dfcp_created_by FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE SET NULL");
                $success[] = "Added foreign key: fk_dfcp_created_by";
            }
        }
    } catch (PDOException $e) {
        if (strpos($e->getMessage(), 'Duplicate key') === false && strpos($e->getMessage(), 'already exists') === false) {
            $errors[] = "Foreign key fk_dfcp_created_by: " . $e->getMessage();
        }
    }
    
    // Add foreign key for dfc_entries.period_id
    try {
        $stmt = $db->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE table_schema = ? AND table_name = 'dfc_entries' AND constraint_name = 'fk_dfce_period'");
        $stmt->execute([$dbname]);
        $checkConstraint = $stmt->fetchColumn();
        
        if ($checkConstraint == 0) {
            $db->exec("ALTER TABLE dfc_entries ADD CONSTRAINT fk_dfce_period FOREIGN KEY (period_id) REFERENCES dfc_periods(period_id) ON DELETE CASCADE");
            $success[] = "Added foreign key: fk_dfce_period";
        }
    } catch (PDOException $e) {
        if (strpos($e->getMessage(), 'Duplicate key') === false && strpos($e->getMessage(), 'already exists') === false) {
            $errors[] = "Foreign key fk_dfce_period: " . $e->getMessage();
        }
    }
    
    // Add odc_cost_2 column if it doesn't exist
    try {
        $stmt = $db->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = ? AND table_name = 'dfc_entries' AND column_name = 'odc_cost_2'");
        $stmt->execute([$dbname]);
        $checkColumn = $stmt->fetchColumn();
        
        if ($checkColumn == 0) {
            $db->exec("ALTER TABLE dfc_entries ADD COLUMN odc_cost_2 decimal(15,2) DEFAULT 0.00 AFTER odc_cost");
            $success[] = "Added column: odc_cost_2";
        }
    } catch (PDOException $e) {
        if (strpos($e->getMessage(), 'Duplicate column') === false && strpos($e->getMessage(), 'already exists') === false) {
            $errors[] = "Column odc_cost_2: " . $e->getMessage();
        }
    }
    
    // Remove credit_sale column if it exists (it was removed from UI)
    try {
        $stmt = $db->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = ? AND table_name = 'dfc_entries' AND column_name = 'credit_sale'");
        $stmt->execute([$dbname]);
        $checkColumn = $stmt->fetchColumn();
        
        if ($checkColumn > 0) {
            $db->exec("ALTER TABLE dfc_entries DROP COLUMN credit_sale");
            $success[] = "Removed column: credit_sale (no longer used)";
        }
    } catch (PDOException $e) {
        $errors[] = "Error removing credit_sale: " . $e->getMessage();
    }
    
} catch (Exception $e) {
    if (isset($db) && $db->inTransaction()) {
        try {
            $db->rollBack();
        } catch (PDOException $rollbackError) {
            // Ignore rollback errors
        }
    }
    $errors[] = "Migration failed: " . $e->getMessage();
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>DFC Complete Setup</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            padding: 40px;
            background: #f5f5f5;
        }
        .container {
            background: white;
            padding: 30px;
            border-radius: 8px;
            max-width: 900px;
            margin: 0 auto;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
        }
        h1 {
            color: #333;
            border-bottom: 3px solid #006699;
            padding-bottom: 10px;
        }
        .success {
            color: #28a745;
            padding: 15px;
            background: #d4edda;
            border: 1px solid #c3e6cb;
            border-radius: 4px;
            margin: 10px 0;
        }
        .error {
            color: #dc3545;
            padding: 15px;
            background: #f8d7da;
            border: 1px solid #f5c6cb;
            border-radius: 4px;
            margin: 10px 0;
        }
        .success ul, .error ul {
            margin: 10px 0 0 0;
            padding-left: 25px;
        }
        .btn {
            display: inline-block;
            padding: 12px 24px;
            background: #006699;
            color: white;
            text-decoration: none;
            border-radius: 4px;
            margin-top: 20px;
            font-weight: bold;
            transition: background 0.3s;
        }
        .btn:hover {
            background: #004d73;
        }
        .info {
            background: #e7f3ff;
            border-left: 4px solid #006699;
            padding: 15px;
            margin: 20px 0;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>DFC Complete Database Setup</h1>
        
        <div class="info">
            <strong>📋 What this script does:</strong>
            <ul>
                <li>Creates <code>dfc_periods</code> table (for period management)</li>
                <li>Creates <code>dfc_entries</code> table (for daily entries with all Excel columns)</li>
                <li>Adds <code>odc_cost_2</code> column if missing</li>
                <li>Sets up all foreign keys and indexes</li>
            </ul>
        </div>
        
        <?php if (!empty($success)): ?>
        <div class="success">
            <strong>✓ Setup completed successfully!</strong>
            <ul>
                <?php foreach ($success as $msg): ?>
                <li><?php echo htmlspecialchars($msg); ?></li>
                <?php endforeach; ?>
            </ul>
        </div>
        <?php endif; ?>
        
        <?php if (!empty($errors)): ?>
        <div class="error">
            <strong>⚠ Errors occurred:</strong>
            <ul>
                <?php foreach ($errors as $error): ?>
                <li><?php echo htmlspecialchars($error); ?></li>
                <?php endforeach; ?>
            </ul>
        </div>
        <?php endif; ?>
        
        <div style="margin-top: 30px;">
            <a href="<?php echo BASE_URL; ?>/modules/dfc/list.php" class="btn">→ Go to DFC Module</a>
            <a href="<?php echo BASE_URL; ?>/modules/dfc/report.php" class="btn" style="background: #28a745; margin-left: 10px;">📊 View Reports</a>
        </div>
    </div>
</body>
</html>
