<?php
/**
 * Direct SQL Execution - No dependencies
 * Run via: http://localhost/hrm2/database/create_salary_table_direct.php
 */

error_reporting(E_ALL);
ini_set('display_errors', 1);

$host = 'localhost';
$dbname = 'hrm2';
$username = 'root';
$password = '';

$output = [];
$success = false;

try {
    // Connect
    $db = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $output[] = "✓ Database connected";
    
    // Drop table if exists (for clean slate)
    try {
        $db->exec("DROP TABLE IF EXISTS salary_register");
        $output[] = "✓ Old table removed (if existed)";
    } catch (Exception $e) {
        $output[] = "ℹ️ No old table to remove";
    }
    
    // Create table
    $sql = "
    CREATE TABLE salary_register (
        salary_register_id INT AUTO_INCREMENT PRIMARY KEY,
        site_id INT NOT NULL,
        employee_id INT NOT NULL,
        salary_month VARCHAR(50) NOT NULL COMMENT 'Format: January 2026',
        
        employee_type VARCHAR(50) DEFAULT 'FTC',
        category VARCHAR(50) DEFAULT 'Skilled',
        
        basic DECIMAL(10,2) DEFAULT 0,
        vda DECIMAL(10,2) DEFAULT 0,
        special_allowance DECIMAL(10,2) DEFAULT 0,
        other_allowance DECIMAL(10,2) DEFAULT 0,
        hra DECIMAL(10,2) DEFAULT 0,
        total_earning DECIMAL(10,2) DEFAULT 0,
        
        present INT DEFAULT 0,
        absent INT DEFAULT 0,
        weekly_off INT DEFAULT 0,
        pay_days INT DEFAULT 0,
        
        earned_basic DECIMAL(10,2) DEFAULT 0,
        earned_hra DECIMAL(10,2) DEFAULT 0,
        earned_other_allowance DECIMAL(10,2) DEFAULT 0,
        spl_allowance DECIMAL(10,2) DEFAULT 0,
        total_earning_earned DECIMAL(10,2) DEFAULT 0,
        
        pf DECIMAL(10,2) DEFAULT 0,
        esi DECIMAL(10,2) DEFAULT 0,
        pt DECIMAL(10,2) DEFAULT 0,
        total_deduction DECIMAL(10,2) DEFAULT 0,
        net_salary DECIMAL(10,2) DEFAULT 0,
        
        provident_fund DECIMAL(10,2) DEFAULT 0,
        esic DECIMAL(10,2) DEFAULT 0,
        leave_amount DECIMAL(10,2) DEFAULT 0,
        holiday DECIMAL(10,2) DEFAULT 0,
        bonus DECIMAL(10,2) DEFAULT 0,
        uniform DECIMAL(10,2) DEFAULT 0,
        gratuity DECIMAL(10,2) DEFAULT 0,
        food_allowance DECIMAL(10,2) DEFAULT 0,
        total_rebill DECIMAL(10,2) DEFAULT 0,
        earned_ctc DECIMAL(10,2) DEFAULT 0,
        per_day_wages DECIMAL(10,2) DEFAULT 0,
        
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
        
        UNIQUE KEY unique_employee_month (site_id, employee_id, salary_month),
        KEY idx_site_month (site_id, salary_month),
        KEY idx_employee (employee_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ";
    
    $db->exec($sql);
    $output[] = "✓ salary_register table created!";
    
    // Verify
    $check = $db->query("SHOW TABLES LIKE 'salary_register'");
    if ($check->rowCount() > 0) {
        $output[] = "✓ Table verified successfully";
        
        $structure = $db->query("DESCRIBE salary_register");
        $columns = $structure->fetchAll(PDO::FETCH_COLUMN);
        $output[] = "✓ Table has " . count($columns) . " columns";
        $output[] = "✓ Columns: " . implode(', ', array_slice($columns, 0, 10)) . "...";
    }
    
    $success = true;
    $output[] = "<br><strong style='color: green; font-size: 20px;'>✅ SUCCESS! Table Created!</strong>";
    $output[] = "<br><a href='../modules/payroll/salary_register.php' style='display: inline-block; padding: 15px 30px; background: #28a745; color: white; text-decoration: none; border-radius: 8px; margin-top: 20px; font-size: 16px;'>🚀 Go to Salary Register</a>";
    
} catch (Exception $e) {
    $success = false;
    $output[] = "<strong style='color: red;'>❌ ERROR:</strong> " . $e->getMessage();
    $output[] = "<br><strong>Solution:</strong> Check if XAMPP MySQL is running";
}
?>
<!DOCTYPE html>
<html>
<head>
    <title>Create Salary Register Table</title>
    <meta charset="UTF-8">
    <style>
        * { margin: 0; padding: 0; box-sizing: border-box; }
        body {
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            min-height: 100vh;
            display: flex;
            align-items: center;
            justify-content: center;
            padding: 20px;
        }
        .container {
            background: white;
            padding: 40px;
            border-radius: 16px;
            box-shadow: 0 20px 60px rgba(0,0,0,0.3);
            max-width: 700px;
            width: 100%;
        }
        h1 {
            color: #333;
            margin-bottom: 10px;
            font-size: 28px;
        }
        .subtitle {
            color: #666;
            margin-bottom: 30px;
            font-size: 14px;
        }
        .output {
            background: #f8f9fa;
            padding: 25px;
            border-radius: 8px;
            line-height: 2;
            border-left: 5px solid <?php echo $success ? '#28a745' : '#dc3545'; ?>;
            margin: 20px 0;
        }
        .output div {
            margin: 5px 0;
        }
        .btn {
            display: inline-block;
            padding: 15px 30px;
            background: #667eea;
            color: white;
            text-decoration: none;
            border-radius: 8px;
            margin-top: 20px;
            transition: all 0.3s;
            font-weight: 600;
        }
        .btn:hover {
            transform: translateY(-2px);
            box-shadow: 0 5px 15px rgba(0,0,0,0.2);
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>💰 Salary Register Table Creation</h1>
        <div class="subtitle">Direct SQL execution - No dependencies required</div>
        
        <div class="output">
            <?php foreach ($output as $line): ?>
                <div><?php echo $line; ?></div>
            <?php endforeach; ?>
        </div>
        
        <?php if (!$success): ?>
            <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 was created:</strong><br>
            • salary_register table with 37+ columns<br>
            • All salary calculation fields<br>
            • Indexes for performance<br>
            • Ready for immediate use!
        </div>
    </div>
</body>
</html>
