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

try {
    echo "Running migrations...\n";

    // 1. Add reporting_manager_id to employees
    $checkColumn = fetch("SHOW COLUMNS FROM employees LIKE 'reporting_manager_id'");
    if (!$checkColumn) {
        echo "Adding reporting_manager_id column to employees table...\n";
        query("ALTER TABLE employees ADD COLUMN reporting_manager_id BIGINT UNSIGNED NULL AFTER user_id");
        query("ALTER TABLE employees ADD FOREIGN KEY (reporting_manager_id) REFERENCES users(id) ON DELETE SET NULL");
        echo "Column reporting_manager_id added.\n";
    } else {
        echo "Column reporting_manager_id already exists.\n";
    }

    // 2. Create leave_configurations
    echo "Creating leave_configurations table...\n";
    query("
        CREATE TABLE IF NOT EXISTS leave_configurations (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            company_id BIGINT NOT NULL,
            leave_type VARCHAR(50) NOT NULL,
            days_per_year INT NOT NULL DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uq_company_leave (company_id, leave_type)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
    echo "leave_configurations table created.\n";

    // 3. Create public_holidays
    echo "Creating public_holidays table...\n";
    query("
        CREATE TABLE IF NOT EXISTS public_holidays (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            company_id BIGINT NOT NULL,
            holiday_date DATE NOT NULL,
            name VARCHAR(255) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uq_company_holiday_date (company_id, holiday_date)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
    echo "public_holidays table created.\n";

    // 4. Create leave_requests
    echo "Creating leave_requests table...\n";
    query("
        CREATE TABLE IF NOT EXISTS leave_requests (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            employee_id BIGINT UNSIGNED NOT NULL,
            company_id BIGINT NOT NULL,
            reporting_manager_id BIGINT UNSIGNED NULL,
            leave_type VARCHAR(50) NOT NULL,
            duration_type VARCHAR(20) NOT NULL,
            start_date DATE NOT NULL,
            end_date DATE NOT NULL,
            half_day_type VARCHAR(20) NULL,
            short_leave_start TIME NULL,
            short_leave_end TIME NULL,
            reason TEXT NOT NULL,
            status VARCHAR(20) NOT NULL DEFAULT 'pending',
            actioned_by BIGINT UNSIGNED NULL,
            action_reason TEXT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
            FOREIGN KEY (reporting_manager_id) REFERENCES users(id) ON DELETE SET NULL,
            FOREIGN KEY (actioned_by) REFERENCES users(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
    echo "leave_requests table created.\n";

    // 5. Create attendance
    echo "Creating attendance table...\n";
    query("
        CREATE TABLE IF NOT EXISTS attendance (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            employee_id BIGINT UNSIGNED NOT NULL,
            company_id BIGINT NOT NULL,
            date DATE NOT NULL,
            clock_in DATETIME NOT NULL,
            clock_out DATETIME NULL,
            status VARCHAR(50) NOT NULL DEFAULT 'present',
            source VARCHAR(50) NOT NULL DEFAULT 'web_portal',
            device_log_id VARCHAR(100) NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
            UNIQUE KEY uq_emp_date_clock_in (employee_id, date, clock_in)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
    echo "attendance table created.\n";

    // 6. Create payrolls
    echo "Creating payrolls table...\n";
    query("
        CREATE TABLE IF NOT EXISTS payrolls (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            employee_id BIGINT UNSIGNED NOT NULL,
            company_id BIGINT NOT NULL,
            period_start DATE NOT NULL,
            period_end DATE NOT NULL,
            total_working_days INT NOT NULL DEFAULT 0,
            days_present INT NOT NULL DEFAULT 0,
            days_absent INT NOT NULL DEFAULT 0,
            half_days INT NOT NULL DEFAULT 0,
            short_leaves INT NOT NULL DEFAULT 0,
            paid_leaves INT NOT NULL DEFAULT 0,
            total_hours_worked DECIMAL(10,2) NOT NULL DEFAULT 0.00,
            base_salary DECIMAL(15,2) NOT NULL DEFAULT 0.00,
            allowances DECIMAL(15,2) NOT NULL DEFAULT 0.00,
            deductions DECIMAL(15,2) NOT NULL DEFAULT 0.00,
            net_salary DECIMAL(15,2) NOT NULL DEFAULT 0.00,
            status VARCHAR(20) NOT NULL DEFAULT 'draft',
            processed_by BIGINT UNSIGNED NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
            FOREIGN KEY (processed_by) REFERENCES users(id) ON DELETE SET NULL,
            UNIQUE KEY uq_emp_period (employee_id, period_start, period_end)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
    echo "payrolls table created.\n";

    // Seed default leave configurations for company 1
    $companyId = 1; // Default company in the system
    $leaveTypes = [
        'casual' => 14,
        'annual' => 14,
        'sick' => 7,
        'maternity' => 84,
        'paternity' => 14
    ];
    foreach ($leaveTypes as $type => $days) {
        $exists = fetch("SELECT id FROM leave_configurations WHERE company_id = ? AND leave_type = ?", [$companyId, $type]);
        if (!$exists) {
            query("INSERT INTO leave_configurations (company_id, leave_type, days_per_year) VALUES (?, ?, ?)", [$companyId, $type, $days]);
            echo "Seeded default leave config: $type = $days days.\n";
        }
    }

    echo "Migrations successfully completed!\n";
} catch (Exception $e) {
    echo "Migration failed: " . $e->getMessage() . "\n";
}
