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

$tables = ['buyers', 'suppliers', 'employees', 'products', 'inventory_items', 'production_lines', 'orders', 'users'];

try {
    foreach ($tables as $table) {
        $columns = array_column(fetchAll("DESCRIBE $table"), 'Field');
        if (!in_array('company_id', $columns)) {
            echo "Adding company_id to $table\n";
            // Different position for users
            if ($table == 'users') {
                query("ALTER TABLE users ADD COLUMN company_id BIGINT AFTER role");
            } else {
                query("ALTER TABLE $table ADD COLUMN company_id BIGINT AFTER id");
            }
        }
        
        if (in_array($table, ['employees', 'inventory_items', 'production_lines', 'orders'])) {
            if (!in_array('branch_id', $columns)) {
                echo "Adding branch_id to $table\n";
                query("ALTER TABLE $table ADD COLUMN branch_id BIGINT AFTER company_id");
            }
        }
    }

    // Ensure at least one company exists and link it
    $company = fetch('SELECT id FROM companies LIMIT 1');
    if (!$company) {
        query("INSERT INTO companies (name) VALUES ('NexCore Apparel')");
        $company_id = lastInsertId();
    } else {
        $company_id = $company['id'];
    }

    foreach ($tables as $table) {
        query("UPDATE $table SET company_id = ? WHERE company_id IS NULL OR company_id = 0", [$company_id]);
    }
    
    echo "Schema synchronization completed successfully.\n";
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
