<?php
require_once __DIR__ . '/config.php';
$db = getDB();

try {
    // 1. Upgrade Employees Table
    $db->exec("
        ALTER TABLE employees 
        ADD COLUMN IF NOT EXISTS emp_code VARCHAR(20) UNIQUE AFTER id,
        ADD COLUMN IF NOT EXISTS department ENUM('Factory', 'Office', 'Sales', 'Delivery', 'HR', 'Admin') DEFAULT 'Office' AFTER role,
        ADD COLUMN IF NOT EXISTS designation VARCHAR(100) DEFAULT NULL AFTER department,
        ADD COLUMN IF NOT EXISTS shift_timing VARCHAR(100) DEFAULT '09:00 AM - 06:00 PM',
        ADD COLUMN IF NOT EXISTS supervisor_id INT DEFAULT NULL,
        ADD COLUMN IF NOT EXISTS basic_salary DECIMAL(10,2) DEFAULT 0.00,
        ADD COLUMN IF NOT EXISTS phone VARCHAR(20) DEFAULT NULL AFTER email
    ");

    // 2. Attendance Table
    $db->exec("
        CREATE TABLE IF NOT EXISTS emp_attendance (
            id INT AUTO_INCREMENT PRIMARY KEY,
            employee_id INT NOT NULL,
            date DATE NOT NULL,
            check_in DATETIME DEFAULT NULL,
            check_out DATETIME DEFAULT NULL,
            status ENUM('Present', 'Absent', 'Half Day', 'Late') DEFAULT 'Present',
            location VARCHAR(255) DEFAULT NULL,
            notes TEXT DEFAULT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
            UNIQUE KEY emp_date (employee_id, date)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    ");

    // 3. Leaves Table
    $db->exec("
        CREATE TABLE IF NOT EXISTS emp_leaves (
            id INT AUTO_INCREMENT PRIMARY KEY,
            employee_id INT NOT NULL,
            leave_type ENUM('Sick', 'Casual', 'Earned', 'Unpaid') NOT NULL,
            start_date DATE NOT NULL,
            end_date DATE NOT NULL,
            reason TEXT NOT NULL,
            status ENUM('Pending', 'Approved', 'Rejected') DEFAULT 'Pending',
            approved_by INT DEFAULT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    ");

    // 4. Tasks/Production Table
    $db->exec("
        CREATE TABLE IF NOT EXISTS emp_tasks (
            id INT AUTO_INCREMENT PRIMARY KEY,
            employee_id INT NOT NULL,
            title VARCHAR(255) NOT NULL,
            description TEXT DEFAULT NULL,
            target_date DATE DEFAULT NULL,
            status ENUM('Pending', 'In Progress', 'Completed', 'Verified') DEFAULT 'Pending',
            task_type ENUM('General', 'Production', 'Sales', 'Maintenance') DEFAULT 'General',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    ");

    // 5. Notices/Communication
    $db->exec("
        CREATE TABLE IF NOT EXISTS company_notices (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            content TEXT NOT NULL,
            department_target VARCHAR(100) DEFAULT 'All',
            created_by INT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    ");

    echo "Employee Ecosystem Migration (v4) Successful!";
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}
