<?php
/**
 * حزمة تطوير شاملة: قوالب الطبيب، طلبات إعادة التموين، QC يومي، جلسات، تكامل webhook
 * تشغيل: php migrations/run_038_enterprise_pack.php
 */
require_once __DIR__ . '/../config/config.php';
require_once __DIR__ . '/../src/Core/Database.php';

use App\Core\Database;

$config = require __DIR__ . '/../config/config.php';
$db = Database::getInstance($config['db'])->connection();

function columnExists(PDO $db, string $table, string $col): bool
{
    try {
        $st = $db->query('SHOW COLUMNS FROM `' . str_replace('`', '', $table) . '`');
        foreach ($st->fetchAll(PDO::FETCH_ASSOC) as $row) {
            if (($row['Field'] ?? '') === $col) {
                return true;
            }
        }
    } catch (Throwable $e) {
    }
    return false;
}

echo "run_038_enterprise_pack...\n";

try {
    $db->exec("CREATE TABLE IF NOT EXISTS doctor_message_templates (
        id INT AUTO_INCREMENT PRIMARY KEY,
        doctor_account_id INT NOT NULL,
        template_key VARCHAR(64) NOT NULL,
        body TEXT NOT NULL,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uq_doc_tpl (doctor_account_id, template_key),
        INDEX idx_doc (doctor_account_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    echo "✓ doctor_message_templates\n";
} catch (Throwable $e) {
    echo "⚠ doctor_message_templates: " . $e->getMessage() . "\n";
}

try {
    $db->exec("CREATE TABLE IF NOT EXISTS inventory_reorder_requests (
        id INT AUTO_INCREMENT PRIMARY KEY,
        tenant_id INT NOT NULL,
        inventory_item_id INT NOT NULL,
        quantity_requested DECIMAL(12,2) NOT NULL,
        status ENUM('pending','approved','rejected','ordered') NOT NULL DEFAULT 'pending',
        note TEXT NULL,
        requested_by_user_id INT NULL,
        decided_by_user_id INT NULL,
        decided_at DATETIME NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_tenant (tenant_id),
        INDEX idx_status (status),
        INDEX idx_item (inventory_item_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    echo "✓ inventory_reorder_requests\n";
} catch (Throwable $e) {
    echo "⚠ inventory_reorder_requests: " . $e->getMessage() . "\n";
}

try {
    $db->exec("CREATE TABLE IF NOT EXISTS qc_daily_checks (
        id INT AUTO_INCREMENT PRIMARY KEY,
        tenant_id INT NOT NULL,
        check_date DATE NOT NULL,
        subject VARCHAR(255) NOT NULL,
        result_ok TINYINT(1) NOT NULL DEFAULT 1,
        notes TEXT NULL,
        created_by_user_id INT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_tenant_date (tenant_id, check_date)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    echo "✓ qc_daily_checks\n";
} catch (Throwable $e) {
    echo "⚠ qc_daily_checks: " . $e->getMessage() . "\n";
}

try {
    $db->exec("CREATE TABLE IF NOT EXISTS user_session_log (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        tenant_id INT NOT NULL,
        session_id VARCHAR(128) NOT NULL,
        ip_address VARCHAR(45) NULL,
        user_agent VARCHAR(512) NULL,
        last_seen_at DATETIME NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_user (user_id),
        INDEX idx_session (session_id),
        INDEX idx_tenant (tenant_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    echo "✓ user_session_log\n";
} catch (Throwable $e) {
    echo "⚠ user_session_log: " . $e->getMessage() . "\n";
}

try {
    $db->exec("CREATE TABLE IF NOT EXISTS lab_ops_alert_log (
        id INT AUTO_INCREMENT PRIMARY KEY,
        tenant_id INT NOT NULL,
        alert_type VARCHAR(32) NOT NULL,
        message TEXT NOT NULL,
        entity_ref VARCHAR(64) NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_tenant_type (tenant_id, alert_type)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    echo "✓ lab_ops_alert_log\n";
} catch (Throwable $e) {
    echo "⚠ lab_ops_alert_log: " . $e->getMessage() . "\n";
}

try {
    $db->query('SELECT id FROM lab_orders LIMIT 1');
    if (!columnExists($db, 'lab_orders', 'clinic_api_key_id')) {
        $db->exec("ALTER TABLE lab_orders ADD COLUMN clinic_api_key_id INT NULL COMMENT 'ربط عيادة للـ webhook'");
        echo "✓ lab_orders.clinic_api_key_id\n";
    } else {
        echo "تخطي: lab_orders.clinic_api_key_id موجود\n";
    }
} catch (Throwable $e) {
    echo "⚠ lab_orders.clinic_api_key_id: " . $e->getMessage() . "\n";
}

echo "تم run_038_enterprise_pack.\n";
