<?php
require_once __DIR__ . '/config/database.php';
use App\Config\Database;

try {
    $db = Database::getInstance()->getConnection();
    
    // Check if columns exist
    $stmt = $db->query("SHOW COLUMNS FROM tenants LIKE 'logo_path'");
    if ($stmt->rowCount() == 0) {
        $db->exec("ALTER TABLE tenants ADD COLUMN logo_path VARCHAR(255) NULL");
        echo "Added logo_path column.\n";
    }

    $stmt = $db->query("SHOW COLUMNS FROM tenants LIKE 'alert_emails'");
    if ($stmt->rowCount() == 0) {
        $db->exec("ALTER TABLE tenants ADD COLUMN alert_emails TEXT NULL");
        echo "Added alert_emails column.\n";
    }

    $stmt = $db->query("SHOW COLUMNS FROM inventory_batches LIKE 'status'");
    if ($stmt->rowCount() == 0) {
        $db->exec("ALTER TABLE inventory_batches ADD COLUMN status ENUM('active', 'inactive') DEFAULT 'active'");
        echo "Added status column to inventory_batches.\n";
    }

    $stmt = $db->query("SHOW COLUMNS FROM inventory_batches LIKE 'stock_level'");
    if ($stmt->rowCount() == 0) {
        $db->exec("ALTER TABLE inventory_batches ADD COLUMN stock_level ENUM('In-stock', 'low-stock', 'out-of stock') DEFAULT 'In-stock'");
        $db->exec("
            UPDATE inventory_batches ib
            JOIN medicines m ON ib.medicine_id = m.id
            SET ib.stock_level = CASE
                WHEN ib.quantity = 0 THEN 'out-of stock'
                WHEN ib.quantity <= m.reorder_level THEN 'low-stock'
                ELSE 'In-stock'
            END
        ");
        echo "Added stock_level column to inventory_batches and initialized existing records.\n";
    }

    $stmt = $db->query("SHOW COLUMNS FROM inventory_batches LIKE 'withdrawal_comment'");
    if ($stmt->rowCount() == 0) {
        $db->exec("ALTER TABLE inventory_batches ADD COLUMN withdrawal_comment TEXT NULL");
        echo "Added withdrawal_comment column to inventory_batches.\n";
    }

    // Check if performance indexes exist
    $stmt = $db->query("SHOW INDEX FROM inventory_batches WHERE Key_name = 'idx_ib_expiry_perf'");
    if ($stmt->rowCount() == 0) {
        $db->exec("ALTER TABLE inventory_batches ADD INDEX idx_ib_expiry_perf (tenant_id, status, quantity, expiry_date)");
        echo "Added index idx_ib_expiry_perf.\n";
    }

    $stmt = $db->query("SHOW INDEX FROM inventory_batches WHERE Key_name = 'idx_ib_expiry_branch_perf'");
    if ($stmt->rowCount() == 0) {
        $db->exec("ALTER TABLE inventory_batches ADD INDEX idx_ib_expiry_branch_perf (tenant_id, branch_id, status, quantity, expiry_date)");
        echo "Added index idx_ib_expiry_branch_perf.\n";
    }

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