<?php
/**
 * Fix Salary Register - Auto Setup & Data Sync
 * This script will:
 * 1. Check if tables exist
 * 2. Create missing columns
 * 3. Sync employee data to salary_register
 */

require_once __DIR__ . '/../../includes/init.php';
requireLogin();

$db = getDB();
$siteId = getCurrentSiteId();
$userId = $_SESSION['user_id'];

echo "<!DOCTYPE html>";
echo "<html><head><title>Fix Salary Register</title>";
echo "<style>body{font-family:Arial;padding:20px;} .success{color:green;} .error{color:red;} .info{color:blue;}</style>";
echo "</head><body>";

echo "<h1>🔧 Salary Register Auto-Fix</h1>";

try {
    // Step 1: Check if salary_register table exists
    echo "<h2>Step 1: Checking Tables...</h2>";
    
    $tablesCheck = $db->query("
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = DATABASE() 
        AND table_name IN ('salary_register', 'salary_periods')
    ");
    $existingTables = $tablesCheck->fetchAll(PDO::FETCH_COLUMN);
    
    if (!in_array('salary_register', $existingTables)) {
        echo "<p class='error'>❌ salary_register table NOT found!</p>";
        echo "<p><a href='../../database/run_salary_register_migration.php' style='background:#f44336;color:white;padding:10px 20px;text-decoration:none;border-radius:5px;'>▶️ Run Migration First</a></p>";
        echo "</body></html>";
        exit;
    }
    echo "<p class='success'>✅ salary_register table exists</p>";
    
    if (!in_array('salary_periods', $existingTables)) {
        echo "<p class='error'>❌ salary_periods table NOT found!</p>";
        echo "<p><a href='../../database/run_salary_periods_migration.php' style='background:#f44336;color:white;padding:10px 20px;text-decoration:none;border-radius:5px;'>▶️ Run Migration First</a></p>";
        echo "</body></html>";
        exit;
    }
    echo "<p class='success'>✅ salary_periods table exists</p>";
    
    // Step 2: Check if hr_id column exists
    echo "<h2>Step 2: Checking hr_id Column...</h2>";
    
    $columnsCheck = $db->query("
        SELECT COLUMN_NAME 
        FROM information_schema.COLUMNS 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME = 'salary_register' 
        AND COLUMN_NAME = 'hr_id'
    ");
    $hrIdExists = $columnsCheck->rowCount() > 0;
    
    if (!$hrIdExists) {
        echo "<p class='info'>⚠️ hr_id column NOT found. Adding it...</p>";
        
        $db->exec("
            ALTER TABLE `salary_register` 
            ADD COLUMN `hr_id` INT(11) NULL AFTER `site_id`,
            ADD KEY `idx_hr_id` (`hr_id`)
        ");
        
        echo "<p class='success'>✅ hr_id column added successfully!</p>";
    } else {
        echo "<p class='success'>✅ hr_id column already exists</p>";
    }
    
    // Step 3: Get active employees count
    echo "<h2>Step 3: Checking Active Employees...</h2>";
    
    $empStmt = $db->prepare("
        SELECT COUNT(*) as cnt 
        FROM employees 
        WHERE site_id = ? AND employment_status = 'active'
    ");
    $empStmt->execute([$siteId]);
    $empCount = $empStmt->fetch()['cnt'];
    
    echo "<p class='info'>📊 Found <strong>$empCount</strong> active employees</p>";
    
    if ($empCount == 0) {
        echo "<p class='error'>❌ No active employees found! Please add employees first.</p>";
        echo "<p><a href='../employee/list.php' style='background:#2196F3;color:white;padding:10px 20px;text-decoration:none;border-radius:5px;'>➕ Add Employees</a></p>";
        echo "</body></html>";
        exit;
    }
    
    // Step 4: Update hr_id for existing records
    echo "<h2>Step 4: Updating HR Assignments...</h2>";
    
    $updateStmt = $db->exec("
        UPDATE salary_register sr
        INNER JOIN employees e ON sr.employee_id = e.employee_id
        SET sr.hr_id = e.created_by
        WHERE sr.hr_id IS NULL
    ");
    
    echo "<p class='success'>✅ Updated $updateStmt records with HR assignments</p>";
    
    // Step 5: Show current month suggestion
    echo "<h2>Step 5: Next Steps...</h2>";
    
    $currentMonth = date('F');
    $currentYear = date('Y');
    $monthAbbr = date('M');
    $yearAbbr = date('y');
    
    echo "<p class='info'>💡 Suggested period: <strong>$currentMonth $currentYear</strong></p>";
    
    // Check if current period exists
    $periodCheck = $db->prepare("
        SELECT period_id 
        FROM salary_periods 
        WHERE site_id = ? AND period_display = ?
    ");
    $periodCheck->execute([$siteId, "$currentMonth $currentYear"]);
    
    if ($periodCheck->rowCount() == 0) {
        echo "<p class='info'>⚠️ Period for $currentMonth $currentYear not created yet.</p>";
        echo "<p>You need to create this period in Salary Register.</p>";
    } else {
        echo "<p class='success'>✅ Period for $currentMonth $currentYear already exists!</p>";
    }
    
    // Step 6: Show summary
    echo "<h2>✅ Status Summary</h2>";
    echo "<ul>";
    echo "<li>✅ Tables: OK</li>";
    echo "<li>✅ HR Column: OK</li>";
    echo "<li>✅ Active Employees: $empCount</li>";
    echo "<li>✅ System Ready!</li>";
    echo "</ul>";
    
    echo "<hr>";
    echo "<p><a href='salary_register.php?m=$monthAbbr-$yearAbbr&y=$yearAbbr' style='background:#4CAF50;color:white;padding:15px 30px;text-decoration:none;border-radius:5px;font-size:16px;'>🚀 Open Salary Register</a></p>";
    
} catch (Exception $e) {
    echo "<p class='error'>❌ Error: " . $e->getMessage() . "</p>";
    echo "<pre>" . $e->getTraceAsString() . "</pre>";
}

echo "</body></html>";
?>
