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

echo "Starting Schema Update for Modules...\n";

// Helper to check if column exists
function columnExists($pdo, $table, $column) {
    try {
        $stmt = $pdo->query("SHOW COLUMNS FROM $table LIKE '$column'");
        return $stmt->fetch() !== false;
    } catch (Exception $e) {
        return false;
    }
}

// 1. Get or Create Default Company (Aiben)
$stmt = $pdo->query("SELECT id FROM companies WHERE name LIKE '%Aiben%' LIMIT 1");
$defaultCompanyId = $stmt->fetchColumn();

if (!$defaultCompanyId) {
    echo "Default company not found. Creating 'Aiben Properties'...\n";
    $pdo->exec("INSERT INTO companies (name, domain, branding_color_primary, branding_color_secondary, status) 
                VALUES ('Aiben Properties', 'localhost', '#001F3F', '#2ECC40', 'active')");
    $defaultCompanyId = $pdo->lastInsertId();
}
echo "Default Company ID: $defaultCompanyId\n";

$tables_to_update = ['maintenance_requests', 'tasks', 'support_tickets', 'invoices'];

foreach ($tables_to_update as $table) {
    echo "Processing table: $table...\n";
    
    // Add company_id column if not exists
    if (!columnExists($pdo, $table, 'company_id')) {
        echo "  - Adding company_id column...\n";
        try {
            // Add column
            $pdo->exec("ALTER TABLE $table ADD COLUMN company_id INT(11) NULL AFTER id");
            
            // Backfill data
            echo "  - Backfilling existing records...\n";
            $pdo->exec("UPDATE $table SET company_id = $defaultCompanyId WHERE company_id IS NULL");
            
            // Add Index
            echo "  - Adding index...\n";
            $pdo->exec("ALTER TABLE $table ADD INDEX idx_{$table}_company (company_id)");
            
            // Add Foreign Key (Check if companies table exists first - it should)
            echo "  - Adding foreign key...\n";
            $pdo->exec("ALTER TABLE $table ADD CONSTRAINT fk_{$table}_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE");
            
            echo "  - Done.\n";
        } catch (PDOException $e) {
            echo "  - Error: " . $e->getMessage() . "\n";
        }
    } else {
        echo "  - company_id already exists.\n";
    }
}

echo "Schema update completed.\n";
?>