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

try {
    // 1. Add hourly_rate to employees if it doesn't exist
    $cols = fetchAll("SHOW COLUMNS FROM employees LIKE 'hourly_rate'");
    if (empty($cols)) {
        query("ALTER TABLE employees ADD COLUMN hourly_rate DECIMAL(10,2) DEFAULT 0.00 AFTER skills");
        echo "Added hourly_rate to employees table.\n";
    }

    // 2. Create production_reports table
    query("CREATE TABLE IF NOT EXISTS production_reports (
        id BIGINT AUTO_INCREMENT PRIMARY KEY,
        production_line_id BIGINT,
        company_id BIGINT,
        line_name VARCHAR(255),
        started_at DATETIME,
        finished_at DATETIME,
        total_material_cost DECIMAL(15,2) DEFAULT 0.00,
        total_labor_cost DECIMAL(15,2) DEFAULT 0.00,
        grand_total_cost DECIMAL(15,2) DEFAULT 0.00,
        total_units_produced INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )");
    echo "Created production_reports table.\n";

    // 3. Create report_materials table
    query("CREATE TABLE IF NOT EXISTS report_materials (
        id BIGINT AUTO_INCREMENT PRIMARY KEY,
        report_id BIGINT,
        inventory_item_id BIGINT,
        sku VARCHAR(255),
        name VARCHAR(255),
        quantity_used DECIMAL(15,2),
        unit VARCHAR(50),
        unit_price_at_time DECIMAL(15,2),
        subtotal DECIMAL(15,2),
        FOREIGN KEY (report_id) REFERENCES production_reports(id) ON DELETE CASCADE
    )");
    echo "Created report_materials table.\n";

    // 4. Create report_labor table
    query("CREATE TABLE IF NOT EXISTS report_labor (
        id BIGINT AUTO_INCREMENT PRIMARY KEY,
        report_id BIGINT,
        employee_id BIGINT,
        employee_name VARCHAR(255),
        total_hours DECIMAL(15,2),
        hourly_rate_at_time DECIMAL(15,2),
        subtotal DECIMAL(15,2),
        FOREIGN KEY (report_id) REFERENCES production_reports(id) ON DELETE CASCADE
    )");
    echo "Created report_labor table.\n";

    // 5. Create report_products table
    query("CREATE TABLE IF NOT EXISTS report_products (
        id BIGINT AUTO_INCREMENT PRIMARY KEY,
        report_id BIGINT,
        product_id BIGINT,
        product_sku VARCHAR(255),
        product_name VARCHAR(255),
        quantity_produced INT,
        cost_per_unit DECIMAL(15,2),
        FOREIGN KEY (report_id) REFERENCES production_reports(id) ON DELETE CASCADE
    )");
    echo "Created report_products table.\n";

    echo "Migration completed successfully.\n";

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