<?php
/**
 * Enterprise HRM ERP System - DFC Fix Insert Error
 * This script checks and fixes column mismatch in dfc_entries table
 */

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

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

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

try {
    $db = getDB();
    $db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
    
    $dbname = $db->query("SELECT DATABASE()")->fetchColumn();
    
    // Get all columns from dfc_entries table
    $stmt = $db->prepare("
        SELECT COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE, COLUMN_TYPE
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'dfc_entries'
        ORDER BY ORDINAL_POSITION
    ");
    $stmt->execute([$dbname]);
    $tableInfo = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Expected columns from INSERT statement (32 columns, excluding entry_id, created_at, updated_at)
    $expectedColumns = [
        'period_id', 'entry_date', 'cash_sale_net', 'catering_btc_regular', 'catering_btc_extra',
        'total_food_sale', 'housekeeping_lumpsum', 'laundry', 'total_sale',
        'cash_purchase', 'catering_cost', 'total_food_cost', 'food_cost_percent',
        'housekeeping_cost', 'housekeeping_cost_percent', 'manpower_days', 'manpower_cost',
        'manpower_cost_percent', 'manpower_cost_per_sale', 'odc_cost', 'odc_cost_2', 'cylinder_quantity',
        'cylinder_rate', 'total_cylinder_cost', 'cylinder_cost_percent', 'cylinder_cost_per_sale',
        'expected_food_cost', 'variation_amount', 'variation_type', 'additional_cost', 'total_cost', 'sr_no'
    ];
    
    // Columns that should NOT be in the table (removed from UI)
    $removedColumns = ['credit_sale'];
    
    $existingColumns = array_column($tableInfo, 'COLUMN_NAME');
    $missingColumns = array_diff($expectedColumns, $existingColumns);
    $extraColumns = array_diff($existingColumns, array_merge($expectedColumns, ['entry_id', 'created_at', 'updated_at']));
    
    // Add missing columns
    if (!empty($missingColumns)) {
        foreach ($missingColumns as $col) {
            try {
                $defaultValue = '0.00';
                $dataType = 'decimal(15,2)';
                
                if ($col === 'variation_type') {
                    $dataType = "enum('positive','negative')";
                    $defaultValue = "'positive'";
                } elseif ($col === 'sr_no') {
                    $dataType = 'int(11)';
                    $defaultValue = '1';
                } elseif (strpos($col, '_percent') !== false || strpos($col, '_per_sale') !== false) {
                    if (strpos($col, '_per_sale') !== false) {
                        $dataType = 'decimal(10,4)';
                    } else {
                        $dataType = 'decimal(5,2)';
                    }
                } elseif (strpos($col, 'quantity') !== false || strpos($col, 'days') !== false) {
                    $dataType = 'decimal(10,2)';
                } elseif (strpos($col, 'rate') !== false) {
                    $dataType = 'decimal(10,2)';
                }
                
                // Determine position
                $afterColumn = 'sr_no';
                if ($col === 'odc_cost_2') {
                    $afterColumn = 'odc_cost';
                } elseif (in_array($col, ['cylinder_quantity', 'cylinder_rate', 'total_cylinder_cost'])) {
                    $afterColumn = 'odc_cost_2';
                } elseif ($col === 'cylinder_cost_percent') {
                    $afterColumn = 'total_cylinder_cost';
                } elseif ($col === 'cylinder_cost_per_sale') {
                    $afterColumn = 'cylinder_cost_percent';
                } elseif ($col === 'expected_food_cost') {
                    $afterColumn = 'cylinder_cost_per_sale';
                } elseif ($col === 'variation_amount') {
                    $afterColumn = 'expected_food_cost';
                } elseif ($col === 'variation_type') {
                    $afterColumn = 'variation_amount';
                } elseif ($col === 'additional_cost') {
                    $afterColumn = 'variation_type';
                } elseif ($col === 'total_cost') {
                    $afterColumn = 'additional_cost';
                }
                
                $sql = "ALTER TABLE dfc_entries ADD COLUMN `{$col}` {$dataType} DEFAULT {$defaultValue}";
                if ($afterColumn && in_array($afterColumn, $existingColumns)) {
                    $sql .= " AFTER `{$afterColumn}`";
                }
                
                $db->exec($sql);
                $success[] = "Added missing column: {$col}";
            } catch (PDOException $e) {
                if (strpos($e->getMessage(), 'Duplicate column') === false) {
                    $errors[] = "Error adding column {$col}: " . $e->getMessage();
                }
            }
        }
    }
    
    // Remove credit_sale column if it exists (it was removed from UI)
    if (in_array('credit_sale', $existingColumns)) {
        try {
            $db->exec("ALTER TABLE dfc_entries DROP COLUMN credit_sale");
            $success[] = "Removed column: credit_sale (no longer used)";
        } catch (PDOException $e) {
            $errors[] = "Could not drop credit_sale: " . $e->getMessage();
        }
    }
    
} catch (Exception $e) {
    $errors[] = "Error: " . $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 Fix Insert Error</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            padding: 40px;
            background: #f5f5f5;
        }
        .container {
            background: white;
            padding: 30px;
            border-radius: 8px;
            max-width: 1200px;
            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;
        }
        .info {
            background: #e7f3ff;
            border-left: 4px solid #006699;
            padding: 15px;
            margin: 20px 0;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            margin: 20px 0;
        }
        th, td {
            padding: 10px;
            text-align: left;
            border: 1px solid #ddd;
        }
        th {
            background: #006699;
            color: white;
        }
        tr:nth-child(even) {
            background: #f8f9fa;
        }
        .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;
        }
        .missing {
            color: #dc3545;
            font-weight: bold;
        }
        .extra {
            color: #ffc107;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>DFC Fix Insert Error - Column Mismatch Check</h1>
        
        <div class="info">
            <strong>📋 What this script does:</strong>
            <ul>
                <li>Checks all columns in <code>dfc_entries</code> table</li>
                <li>Compares with expected columns from INSERT statement</li>
                <li>Adds any missing columns automatically</li>
                <li>Shows detailed table structure</li>
            </ul>
        </div>
        
        <?php if (!empty($success)): ?>
        <div class="success">
            <strong>✓ Operations completed:</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; ?>
        
        <?php if (!empty($tableInfo)): ?>
        <h2>Current Table Structure (dfc_entries)</h2>
        <table>
            <thead>
                <tr>
                    <th>Column Name</th>
                    <th>Data Type</th>
                    <th>Default Value</th>
                    <th>Nullable</th>
                    <th>Status</th>
                </tr>
            </thead>
            <tbody>
                <?php 
                $expectedCols = [
                    'period_id', 'entry_date', 'cash_sale_net', 'catering_btc_regular', 'catering_btc_extra',
                    'total_food_sale', 'housekeeping_lumpsum', 'laundry', 'total_sale',
                    'cash_purchase', 'catering_cost', 'total_food_cost', 'food_cost_percent',
                    'housekeeping_cost', 'housekeeping_cost_percent', 'manpower_days', 'manpower_cost',
                    'manpower_cost_percent', 'manpower_cost_per_sale', 'odc_cost', 'odc_cost_2', 'cylinder_quantity',
                    'cylinder_rate', 'total_cylinder_cost', 'cylinder_cost_percent', 'cylinder_cost_per_sale',
                    'expected_food_cost', 'variation_amount', 'variation_type', 'additional_cost', 'total_cost', 'sr_no'
                ];
                foreach ($tableInfo as $col): 
                    $colName = $col['COLUMN_NAME'];
                    $isExpected = in_array($colName, $expectedCols) || in_array($colName, ['entry_id', 'created_at', 'updated_at']);
                    $status = '';
                    if (!in_array($colName, array_merge($expectedCols, ['entry_id', 'created_at', 'updated_at']))) {
                        $status = '<span class="extra">Extra (not in INSERT)</span>';
                    } elseif (!in_array($colName, ['entry_id', 'created_at', 'updated_at'])) {
                        $status = '<span style="color: #28a745;">✓ Required</span>';
                    } else {
                        $status = '<span style="color: #6c757d;">System</span>';
                    }
                ?>
                <tr>
                    <td><strong><?php echo htmlspecialchars($colName); ?></strong></td>
                    <td><?php echo htmlspecialchars($col['DATA_TYPE']); ?></td>
                    <td><?php echo htmlspecialchars($col['COLUMN_DEFAULT'] ?? 'NULL'); ?></td>
                    <td><?php echo htmlspecialchars($col['IS_NULLABLE']); ?></td>
                    <td><?php echo $status; ?></td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
        
        <h3>Expected Columns in INSERT Statement (32 columns):</h3>
        <div style="background: #f8f9fa; padding: 15px; border-radius: 4px; font-family: monospace; font-size: 12px;">
            <?php 
            foreach ($expectedColumns as $idx => $col): 
                $exists = in_array($col, $existingColumns);
                $style = $exists ? 'color: #28a745;' : 'color: #dc3545; font-weight: bold;';
            ?>
            <span style="<?php echo $style; ?>"><?php echo ($idx + 1) . '. ' . $col; ?></span><?php echo ($idx < count($expectedColumns) - 1) ? ', ' : ''; ?>
            <?php endforeach; ?>
        </div>
        
        <?php if (!empty($missingColumns)): ?>
        <div class="error">
            <strong>Missing Columns:</strong>
            <ul>
                <?php foreach ($missingColumns as $col): ?>
                <li class="missing"><?php echo htmlspecialchars($col); ?></li>
                <?php endforeach; ?>
            </ul>
        </div>
        <?php else: ?>
        <div class="success">
            <strong>✓ All required columns exist in the table!</strong>
        </div>
        <?php endif; ?>
        <?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>
