<?php
require_once __DIR__ . '/../config.php';

try {
    $pdo = db();
    echo "Connected to database successfully.\n";

    $sql = <<<SQL
        CREATE TABLE IF NOT EXISTS `payroll_runs` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `company_id` INT NOT NULL,
            `pay_period_month` INT NOT NULL,
            `pay_period_year` INT NOT NULL,
            `status` ENUM('draft', 'completed') NOT NULL DEFAULT 'draft',
            `run_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY `company_period` (`company_id`, `pay_period_year`, `pay_period_month`),
            FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE
        ) ENGINE=INNODB;

        CREATE TABLE IF NOT EXISTS `payslips` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `payroll_run_id` INT NOT NULL,
            `employee_id` INT NOT NULL,
            `gross_pay` DECIMAL(15, 2) NOT NULL,
            `total_deductions` DECIMAL(15, 2) NOT NULL,
            `net_pay` DECIMAL(15, 2) NOT NULL,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (`payroll_run_id`) REFERENCES `payroll_runs`(`id`) ON DELETE CASCADE,
            FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE
        ) ENGINE=INNODB;

        CREATE TABLE IF NOT EXISTS `payslip_items` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `payslip_id` INT NOT NULL,
            `type` ENUM('earning', 'deduction', 'tax') NOT NULL,
            `description` VARCHAR(255) NOT NULL,
            `amount` DECIMAL(15, 2) NOT NULL,
            FOREIGN KEY (`payslip_id`) REFERENCES `payslips`(`id`) ON DELETE CASCADE
        ) ENGINE=INNODB;
SQL;

    $pdo->exec($sql);
    echo "Successfully created payroll tables: payroll_runs, payslips, payslip_items.\n";

} catch (PDOException $e) {
    die("Database migration failed: " . $e->getMessage() . "\n");
}