<?php
/**
 * Simple Attendance Periods Migration (No Auth Required)
 * Use this if authentication issues occur
 * Access via: http://localhost/hrm2/database/run_migration_simple.php
 */

// Simple database connection without full init
$host = 'localhost';
$dbname = 'hrm2';
$username = 'root';
$password = '';

try {
    $db = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $output = [];
    
    // Read SQL file
    $sqlFile = __DIR__ . '/migrations/create_attendance_periods_table.sql';
    
    if (!file_exists($sqlFile)) {
        throw new Exception("SQL file not found: $sqlFile");
    }
    
    $sql = file_get_contents($sqlFile);
    $output[] = "✓ SQL file loaded";
    
    // Execute SQL
    $db->exec($sql);
    $output[] = "✓ attendance_periods table created!";
    
    // Verify
    $verifyStmt = $db->query("SHOW TABLES LIKE 'attendance_periods'");
    if ($verifyStmt->rowCount() > 0) {
        $output[] = "✓ Table verified";
        
        // Migrate existing data
        $output[] = "<br><strong>📊 Migrating existing data...</strong>";
        
        // Get all sites
        $sitesStmt = $db->query("SELECT site_id FROM sites");
        $sites = $sitesStmt->fetchAll(PDO::FETCH_COLUMN);
        
        if (empty($sites)) {
            $sites = [1]; // Default site
        }
        
        $totalMigrated = 0;
        
        foreach ($sites as $siteId) {
            // Get distinct periods
            $existingStmt = $db->prepare("
                SELECT DISTINCT 
                    attendance_month,
                    COUNT(DISTINCT employee_id) as emp_count
                FROM attendance_monthly_grid 
                WHERE site_id = ?
                GROUP BY attendance_month
            ");
            $existingStmt->execute([$siteId]);
            $periods = $existingStmt->fetchAll();
            
            if (count($periods) > 0) {
                $insertStmt = $db->prepare("
                    INSERT INTO attendance_periods 
                    (site_id, attendance_month, period_display, employee_count, created_at) 
                    VALUES (?, ?, ?, ?, NOW())
                    ON DUPLICATE KEY UPDATE 
                    employee_count = VALUES(employee_count)
                ");
                
                foreach ($periods as $period) {
                    $month = $period['attendance_month'];
                    $empCount = $period['emp_count'];
                    $displayFormat = date('F Y', strtotime('01-' . $month));
                    
                    $insertStmt->execute([$siteId, $month, $displayFormat, $empCount]);
                    $totalMigrated++;
                    $output[] = "  ✓ Site $siteId: $displayFormat ($empCount employees)";
                }
            }
        }
        
        $output[] = "✓ Migrated $totalMigrated periods total";
    }
    
    $success = true;
    $output[] = "<br><strong style='color: green; font-size: 18px;'>✅ MIGRATION COMPLETED!</strong>";
    
} catch (Exception $e) {
    $success = false;
    $output[] = "<strong style='color: red;'>❌ ERROR:</strong> " . $e->getMessage();
}

?>
<!DOCTYPE html>
<html>
<head>
    <title>Simple Migration</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            max-width: 800px;
            margin: 50px auto;
            padding: 20px;
            background: #f5f5f5;
        }
        .container {
            background: white;
            padding: 30px;
            border-radius: 8px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
        }
        h1 {
            color: #333;
            border-bottom: 2px solid #007bff;
            padding-bottom: 10px;
        }
        .output {
            background: #f8f9fa;
            padding: 20px;
            border-radius: 5px;
            margin: 20px 0;
            line-height: 1.8;
        }
        .btn {
            display: inline-block;
            padding: 10px 20px;
            background: #007bff;
            color: white;
            text-decoration: none;
            border-radius: 5px;
            margin-top: 15px;
        }
        .btn:hover {
            background: #0056b3;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>📊 Simple Migration Runner</h1>
        
        <div class="output">
            <?php foreach ($output as $line): ?>
                <div><?php echo $line; ?></div>
            <?php endforeach; ?>
        </div>
        
        <?php if ($success): ?>
            <a href="../modules/attendance/attendance_excel_grid.php" class="btn">
                🚀 Go to Attendance Module
            </a>
        <?php else: ?>
            <a href="javascript:location.reload()" class="btn">
                🔄 Try Again
            </a>
        <?php endif; ?>
        
        <div style="margin-top: 20px; padding: 15px; background: #e8f4fd; border-radius: 5px; font-size: 13px;">
            <strong>ℹ️ Note:</strong> This is a simplified migration that doesn't require authentication.
            After successful migration, you can use the main attendance module.
        </div>
    </div>
</body>
</html>

