<?php
/**
 * Run Attendance Table Migration
 * Access this file via browser: http://localhost/hrm2/database/run_attendance_migration.php
 */

// Include database configuration
require_once __DIR__ . '/../includes/database.php';

header('Content-Type: text/html; charset=utf-8');
?>
<!DOCTYPE html>
<html>
<head>
    <title>Run Attendance 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: 10px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
        }
        h1 {
            color: #333;
            border-bottom: 3px solid #4CAF50;
            padding-bottom: 10px;
        }
        .success {
            background: #d4edda;
            color: #155724;
            padding: 15px;
            border-radius: 5px;
            border-left: 5px solid #28a745;
            margin: 20px 0;
        }
        .error {
            background: #f8d7da;
            color: #721c24;
            padding: 15px;
            border-radius: 5px;
            border-left: 5px solid #dc3545;
            margin: 20px 0;
        }
        .info {
            background: #d1ecf1;
            color: #0c5460;
            padding: 15px;
            border-radius: 5px;
            border-left: 5px solid #17a2b8;
            margin: 20px 0;
        }
        .btn {
            background: #4CAF50;
            color: white;
            padding: 12px 30px;
            border: none;
            border-radius: 5px;
            cursor: pointer;
            font-size: 16px;
            text-decoration: none;
            display: inline-block;
        }
        .btn:hover {
            background: #45a049;
        }
        .btn-secondary {
            background: #007bff;
        }
        .btn-secondary:hover {
            background: #0056b3;
        }
        pre {
            background: #f8f9fa;
            padding: 15px;
            border-radius: 5px;
            overflow-x: auto;
            border: 1px solid #ddd;
        }
        .step {
            margin: 20px 0;
            padding: 15px;
            background: #f8f9fa;
            border-radius: 5px;
        }
        .step h3 {
            margin-top: 0;
            color: #007bff;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>🗄️ Attendance Table Migration</h1>
        
        <?php
        if (isset($_GET['run']) && $_GET['run'] === 'yes') {
            // Run migration
            try {
                $db = Database::getInstance()->getConnection();
                
                echo '<div class="info"><strong>🔄 Running migration...</strong></div>';
                
                // SQL to create attendance_monthly_grid table
                $sql = "CREATE TABLE IF NOT EXISTS `attendance_monthly_grid` (
                  `attendance_grid_id` int(11) NOT NULL AUTO_INCREMENT,
                  `site_id` int(11) NOT NULL,
                  `employee_id` int(11) NOT NULL,
                  `attendance_month` varchar(20) NOT NULL COMMENT 'Format: Jan-26',
                  `day_1` varchar(10) DEFAULT NULL,
                  `day_2` varchar(10) DEFAULT NULL,
                  `day_3` varchar(10) DEFAULT NULL,
                  `day_4` varchar(10) DEFAULT NULL,
                  `day_5` varchar(10) DEFAULT NULL,
                  `day_6` varchar(10) DEFAULT NULL,
                  `day_7` varchar(10) DEFAULT NULL,
                  `day_8` varchar(10) DEFAULT NULL,
                  `day_9` varchar(10) DEFAULT NULL,
                  `day_10` varchar(10) DEFAULT NULL,
                  `day_11` varchar(10) DEFAULT NULL,
                  `day_12` varchar(10) DEFAULT NULL,
                  `day_13` varchar(10) DEFAULT NULL,
                  `day_14` varchar(10) DEFAULT NULL,
                  `day_15` varchar(10) DEFAULT NULL,
                  `day_16` varchar(10) DEFAULT NULL,
                  `day_17` varchar(10) DEFAULT NULL,
                  `day_18` varchar(10) DEFAULT NULL,
                  `day_19` varchar(10) DEFAULT NULL,
                  `day_20` varchar(10) DEFAULT NULL,
                  `day_21` varchar(10) DEFAULT NULL,
                  `day_22` varchar(10) DEFAULT NULL,
                  `day_23` varchar(10) DEFAULT NULL,
                  `day_24` varchar(10) DEFAULT NULL,
                  `day_25` varchar(10) DEFAULT NULL,
                  `day_26` varchar(10) DEFAULT NULL,
                  `day_27` varchar(10) DEFAULT NULL,
                  `day_28` varchar(10) DEFAULT NULL,
                  `day_29` varchar(10) DEFAULT NULL,
                  `day_30` varchar(10) DEFAULT NULL,
                  `day_31` varchar(10) DEFAULT NULL,
                  `present_count` int(11) DEFAULT 0,
                  `half_day_count` int(11) DEFAULT 0,
                  `cl_count` int(11) DEFAULT 0 COMMENT 'Casual Leave',
                  `pl_count` int(11) DEFAULT 0 COMMENT 'Paid Leave',
                  `sl_count` int(11) DEFAULT 0 COMMENT 'Sick Leave',
                  `absent_count` int(11) DEFAULT 0,
                  `wo_count` int(11) DEFAULT 0 COMMENT 'Weekly Off',
                  `remarks` text,
                  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
                  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                  PRIMARY KEY (`attendance_grid_id`),
                  UNIQUE KEY `unique_emp_month` (`employee_id`,`attendance_month`),
                  KEY `fk_amg_site` (`site_id`),
                  KEY `fk_amg_emp` (`employee_id`),
                  CONSTRAINT `fk_amg_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
                  CONSTRAINT `fk_amg_emp` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE RESTRICT
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
                
                $db->exec($sql);
                
                // Verify table was created
                $checkStmt = $db->query("SHOW TABLES LIKE 'attendance_monthly_grid'");
                $tableExists = $checkStmt->fetch();
                
                if ($tableExists) {
                    echo '<div class="success">';
                    echo '<h3>✅ Migration Successful!</h3>';
                    echo '<p><strong>Table Created:</strong> attendance_monthly_grid</p>';
                    echo '<p><strong>Columns:</strong> 31 day columns + summary columns (P, H, CL, PL, SL, A, WO) + remarks</p>';
                    echo '<p>You can now use the Attendance module!</p>';
                    echo '</div>';
                    
                    echo '<div class="step">';
                    echo '<h3>Next Steps:</h3>';
                    echo '<ol>';
                    echo '<li>Go to: <a href="../modules/attendance/attendance_excel_grid.php">Attendance Module</a></li>';
                    echo '<li>Select a month</li>';
                    echo '<li>Fill attendance data</li>';
                    echo '<li>Save and Export!</li>';
                    echo '</ol>';
                    echo '</div>';
                    
                    echo '<p><a href="../modules/attendance/attendance_excel_grid.php" class="btn">Open Attendance Module</a></p>';
                } else {
                    throw new Exception("Table creation verification failed");
                }
                
            } catch (PDOException $e) {
                echo '<div class="error">';
                echo '<h3>❌ Database Error</h3>';
                echo '<p><strong>Error:</strong> ' . htmlspecialchars($e->getMessage()) . '</p>';
                
                // Check if it's a foreign key constraint error
                if (strpos($e->getMessage(), 'foreign key constraint') !== false) {
                    echo '<div class="info" style="margin-top: 15px;">';
                    echo '<h4>Foreign Key Error Solution:</h4>';
                    echo '<p>The tables <code>sites</code> and <code>employees</code> must exist first.</p>';
                    echo '<p><strong>Option 1:</strong> Run the full migration: <code>database/migrations/add_payroll_excel_tables.sql</code></p>';
                    echo '<p><strong>Option 2:</strong> Remove foreign key constraints temporarily (not recommended)</p>';
                    echo '</div>';
                }
                
                echo '</div>';
            } catch (Exception $e) {
                echo '<div class="error">';
                echo '<h3>❌ Error</h3>';
                echo '<p>' . htmlspecialchars($e->getMessage()) . '</p>';
                echo '</div>';
            }
            
        } else {
            // Show instructions
            ?>
            <div class="info">
                <h3>📋 What This Will Do:</h3>
                <p>This migration will create the <strong>attendance_monthly_grid</strong> table in your database.</p>
                <p><strong>Table Structure:</strong></p>
                <ul>
                    <li>31 day columns (day_1 to day_31) for daily attendance</li>
                    <li>Summary columns: P, H, CL, PL, SL, A, WO</li>
                    <li>Remarks column for notes</li>
                    <li>Employee and Site foreign keys</li>
                </ul>
            </div>
            
            <div class="step">
                <h3>Prerequisites:</h3>
                <ul>
                    <li>✅ XAMPP must be running</li>
                    <li>✅ Database <strong>hrm2</strong> must exist</li>
                    <li>✅ Tables <strong>sites</strong> and <strong>employees</strong> must exist</li>
                </ul>
            </div>
            
            <div class="step">
                <h3>🚀 Ready to Run?</h3>
                <p>Click the button below to create the attendance table:</p>
                <p>
                    <a href="?run=yes" class="btn">▶️ Run Migration Now</a>
                </p>
            </div>
            
            <div class="step">
                <h3>Alternative: Manual Migration via phpMyAdmin</h3>
                <ol>
                    <li>Open phpMyAdmin: <a href="http://localhost/phpmyadmin" target="_blank">http://localhost/phpmyadmin</a></li>
                    <li>Select database: <strong>hrm2</strong></li>
                    <li>Click on "SQL" tab</li>
                    <li>Open file: <code>database/migrations/create_attendance_table_only.sql</code></li>
                    <li>Copy the SQL and paste in phpMyAdmin</li>
                    <li>Click "Go" to execute</li>
                </ol>
            </div>
            <?php
        }
        ?>
        
        <hr style="margin: 30px 0;">
        
        <div class="step">
            <h3>📚 Need Help?</h3>
            <p>If you encounter any errors:</p>
            <ul>
                <li>Make sure XAMPP is running (Apache + MySQL)</li>
                <li>Check database name is correct in <code>config/config.php</code></li>
                <li>Verify <code>sites</code> and <code>employees</code> tables exist</li>
                <li>Check phpMyAdmin for any existing errors</li>
            </ul>
        </div>
        
        <p style="text-align: center; margin-top: 30px;">
            <a href="../modules/payroll/home.php" class="btn btn-secondary">← Back to Payroll Home</a>
        </p>
    </div>
</body>
</html>
