<?php
/**
 * Migration: Enhanced features - support tickets, ratings, reflex rules, favorites, etc.
 */
$baseDir = dirname(__DIR__);
$config = require $baseDir . '/config/config.php';
$pdo = new PDO(
    'mysql:host=' . ($config['db']['host'] ?? 'localhost') . ';port=' . ($config['db']['port'] ?? 3306) . ';dbname=' . ($config['db']['name'] ?? 'lab_lims') . ';charset=' . ($config['db']['charset'] ?? 'utf8mb4'),
    $config['db']['user'] ?? 'root',
    $config['db']['pass'] ?? ''
);

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Support tickets
$pdo->exec("CREATE TABLE IF NOT EXISTS support_tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL DEFAULT 1,
    subject VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    status ENUM('open','in_progress','resolved','closed') NOT NULL DEFAULT 'open',
    priority ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    resolved_at DATETIME NULL,
    resolved_by INT NULL,
    resolution_notes TEXT NULL,
    KEY idx_tenant_status (tenant_id, status)
)");

// Service ratings
$pdo->exec("CREATE TABLE IF NOT EXISTS service_ratings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL DEFAULT 1,
    order_id INT NULL,
    patient_id INT NULL,
    rating TINYINT NOT NULL CHECK (rating >= 1 AND rating <= 5),
    comment TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_tenant (tenant_id)
)");

// Reflex testing rules
$pdo->exec("CREATE TABLE IF NOT EXISTS reflex_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL DEFAULT 1,
    trigger_test_id INT NOT NULL,
    condition_type ENUM('below','above','equals','not_equals') NOT NULL,
    condition_value VARCHAR(50) NULL,
    add_test_id INT NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_tenant (tenant_id)
)");

// User favorites (sidebar)
$pdo->exec("CREATE TABLE IF NOT EXISTS user_favorites (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    page VARCHAR(100) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_user_page (user_id, page)
)");

// Educational content / pre-test instructions (tests may already have educational_content)
try { $pdo->exec("ALTER TABLE tests ADD COLUMN pre_booking_instructions TEXT NULL"); } catch (Throwable $e) {}

// Sample status workflow: collected, received, used, completed (samples already has status)
try { $pdo->exec("ALTER TABLE samples ADD COLUMN status_updated_at DATETIME NULL"); } catch (Throwable $e) {}

// Subscription tracking for tenants
try {
    $pdo->exec("ALTER TABLE tenants ADD COLUMN subscription_plan_id INT NULL");
    $pdo->exec("ALTER TABLE tenants ADD COLUMN subscription_ends_at DATE NULL");
    $pdo->exec("ALTER TABLE tenants ADD COLUMN trial_ends_at DATE NULL");
} catch (Throwable $e) {}

// Push notification subscriptions (for browser notifications)
$pdo->exec("CREATE TABLE IF NOT EXISTS push_subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    endpoint TEXT NOT NULL,
    p256dh VARCHAR(255) NULL,
    auth VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_user (user_id)
)");

echo "Migration run_014_enhanced_features completed.\n";
