<?php
require_once __DIR__ . '/../includes/db.php';

try {
    echo "Creating payroll_configurations table...\n";
    query("
        CREATE TABLE IF NOT EXISTS payroll_configurations (
            id INT AUTO_INCREMENT PRIMARY KEY,
            company_id BIGINT NOT NULL,
            paid_working_days INT DEFAULT NULL,
            epf_employee_percent DECIMAL(5,2) DEFAULT 0.00,
            epf_employer_percent DECIMAL(5,2) DEFAULT 0.00,
            etf_percent DECIMAL(5,2) DEFAULT 0.00,
            cycle_start_day INT DEFAULT 1,
            cycle_end_day INT DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY (company_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    ");
    
    echo "Checking payrolls table columns...\n";
    $columns = fetchAll("SHOW COLUMNS FROM payrolls");
    $columnNames = array_map(function($c) { return strtolower($c['Field']); }, $columns);
    
    if (!in_array('epf_employee_amount', $columnNames)) {
        echo "Adding epf_employee_amount to payrolls...\n";
        query("ALTER TABLE payrolls ADD COLUMN epf_employee_amount DECIMAL(15,2) DEFAULT 0.00 AFTER net_salary");
    }
    if (!in_array('epf_employer_amount', $columnNames)) {
        echo "Adding epf_employer_amount to payrolls...\n";
        query("ALTER TABLE payrolls ADD COLUMN epf_employer_amount DECIMAL(15,2) DEFAULT 0.00 AFTER epf_employee_amount");
    }
    if (!in_array('etf_amount', $columnNames)) {
        echo "Adding etf_amount to payrolls...\n";
        query("ALTER TABLE payrolls ADD COLUMN etf_amount DECIMAL(15,2) DEFAULT 0.00 AFTER epf_employer_amount");
    }
    
    echo "Upgrade completed successfully!\n";
} catch (Exception $e) {
    echo "Error running upgrade: " . $e->getMessage() . "\n";
}
