<?php
/**
 * Attendance Periods Table Migration Runner
 * Access via browser: http://localhost/hrm2/database/run_attendance_periods_migration.php
 */

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

// Basic authentication check - ensure user is logged in
// For migration, we'll allow any logged-in user with access to database folder
if (!isLoggedIn()) {
    die('<h1 style="color: red;">Access Denied</h1><p>Please login first to run database migrations.</p>');
}

// Optional: Check if user is admin (if function exists)
if (function_exists('getUserRole')) {
    $userRole = getUserRole();
    if (!in_array($userRole, ['admin', 'super_admin', 'hr'])) {
        die('<h1 style="color: red;">Access Denied</h1><p>Only administrators can run database migrations.</p>');
    }
}

$db = getDB();
$output = [];
$hasError = false;

try {
    // Read SQL file
    $sqlFile = __DIR__ . '/migrations/create_attendance_periods_table.sql';
    
    if (!file_exists($sqlFile)) {
        throw new Exception("SQL migration file not found: $sqlFile");
    }
    
    $sql = file_get_contents($sqlFile);
    $output[] = "✓ SQL file loaded successfully";
    
    // Execute SQL
    $db->exec($sql);
    $output[] = "✓ attendance_periods table created successfully!";
    
    // Verify table exists
    $verifyStmt = $db->query("SHOW TABLES LIKE 'attendance_periods'");
    if ($verifyStmt->rowCount() > 0) {
        $output[] = "✓ Table verification passed";
        
        // Show table structure
        $structureStmt = $db->query("DESCRIBE attendance_periods");
        $columns = $structureStmt->fetchAll(PDO::FETCH_COLUMN);
        $output[] = "✓ Table has " . count($columns) . " columns: " . implode(', ', $columns);
        
        // Migrate existing data from attendance_monthly_grid to attendance_periods
        $output[] = "<br><strong>📊 Migrating existing attendance data...</strong>";
        
        $siteId = getCurrentSiteId();
        
        // Get distinct periods from attendance_monthly_grid
        $existingPeriodsStmt = $db->prepare("
            SELECT DISTINCT 
                attendance_month,
                COUNT(DISTINCT employee_id) as emp_count
            FROM attendance_monthly_grid 
            WHERE site_id = ?
            GROUP BY attendance_month
            ORDER BY attendance_month
        ");
        $existingPeriodsStmt->execute([$siteId]);
        $existingPeriods = $existingPeriodsStmt->fetchAll();
        
        if (count($existingPeriods) > 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)
            ");
            
            $migratedCount = 0;
            foreach ($existingPeriods as $period) {
                $month = $period['attendance_month']; // e.g., Jan-25
                $empCount = $period['emp_count'];
                
                // Convert to display format (e.g., January 2025)
                $displayFormat = date('F Y', strtotime('01-' . $month));
                
                $insertStmt->execute([$siteId, $month, $displayFormat, $empCount]);
                $migratedCount++;
                $output[] = "  ✓ Migrated period: $displayFormat ($empCount employees)";
            }
            
            $output[] = "✓ Successfully migrated $migratedCount existing periods";
        } else {
            $output[] = "ℹ️ No existing attendance periods found to migrate";
        }
        
    } else {
        throw new Exception("Table creation verification failed");
    }
    
    $output[] = "<br><strong style='color: green; font-size: 18px;'>✅ MIGRATION COMPLETED SUCCESSFULLY!</strong>";
    $output[] = "<br><strong>Next Steps:</strong>";
    $output[] = "1. ✓ attendance_periods table is ready";
    $output[] = "2. ✓ Existing data has been migrated";
    $output[] = "3. → Go to Attendance module to use the new system";
    $output[] = "4. → System now shows only database periods (real-time data)";
    
} catch (Exception $e) {
    $hasError = true;
    $output[] = "<strong style='color: red;'>❌ ERROR:</strong> " . $e->getMessage();
    $output[] = "<br><strong>Troubleshooting:</strong>";
    $output[] = "1. Make sure you're logged in as admin";
    $output[] = "2. Check database connection in includes/config.php";
    $output[] = "3. Ensure the SQL file exists at: database/migrations/create_attendance_periods_table.sql";
}

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Attendance Periods Migration</title>
    <style>
        body {
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
            max-width: 900px;
            margin: 50px auto;
            padding: 30px;
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            min-height: 100vh;
        }
        .container {
            background: white;
            border-radius: 12px;
            padding: 40px;
            box-shadow: 0 10px 40px rgba(0,0,0,0.3);
        }
        h1 {
            color: #333;
            border-bottom: 3px solid #667eea;
            padding-bottom: 15px;
            margin-bottom: 30px;
        }
        .output {
            background: #f8f9fa;
            border-left: 4px solid <?php echo $hasError ? '#dc3545' : '#28a745'; ?>;
            padding: 20px;
            border-radius: 6px;
            margin: 20px 0;
            line-height: 1.8;
        }
        .output div {
            margin: 8px 0;
            font-size: 14px;
        }
        .btn {
            display: inline-block;
            padding: 12px 24px;
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            text-decoration: none;
            border-radius: 6px;
            margin-top: 20px;
            transition: transform 0.2s;
        }
        .btn:hover {
            transform: translateY(-2px);
            box-shadow: 0 4px 12px rgba(0,0,0,0.2);
        }
        .info-box {
            background: #e8f4fd;
            border-left: 4px solid #007bff;
            padding: 15px;
            margin: 20px 0;
            border-radius: 4px;
        }
        .success-icon {
            font-size: 48px;
            text-align: center;
            margin: 20px 0;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>📊 Attendance Periods Table Migration</h1>
        
        <div class="info-box">
            <strong>🎯 Purpose:</strong><br>
            This migration creates the <code>attendance_periods</code> table that tracks all created attendance periods.
            The system will now show only real database data - no assumptions!
        </div>
        
        <?php if (!$hasError): ?>
            <div class="success-icon">✅</div>
        <?php endif; ?>
        
        <div class="output">
            <?php foreach ($output as $line): ?>
                <div><?php echo $line; ?></div>
            <?php endforeach; ?>
        </div>
        
        <?php if (!$hasError): ?>
            <div style="text-align: center;">
                <a href="../modules/attendance/attendance_excel_grid.php" class="btn">
                    🚀 Go to Attendance Module
                </a>
                <a href="../" class="btn" style="margin-left: 10px;">
                    🏠 Go to Dashboard
                </a>
            </div>
        <?php else: ?>
            <div style="text-align: center;">
                <a href="javascript:location.reload()" class="btn">
                    🔄 Try Again
                </a>
            </div>
        <?php endif; ?>
        
        <div style="margin-top: 30px; padding-top: 20px; border-top: 2px solid #eee; color: #666; font-size: 13px;">
            <strong>ℹ️ What this migration does:</strong><br>
            1. Creates <code>attendance_periods</code> table<br>
            2. Migrates existing attendance data from <code>attendance_monthly_grid</code><br>
            3. Sets up tracking for all future attendance periods<br>
            4. Enables real-time database-driven filtering
        </div>
    </div>
</body>
</html>

