<?php
// One-time migration runner for SuperAdmin tables.
// Access: http://localhost/orchview2/migrate_super.php
// DELETE this file after running!

define('ROOT', dirname(__DIR__));
define('DS', DIRECTORY_SEPARATOR);

require ROOT . '/vendor/autoload.php';
require ROOT . '/config/bootstrap.php';

$pdo = \Core\DB::conn();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$output = [];

try {
    // Check/add is_super_admin
    $cols = $pdo->query("SHOW COLUMNS FROM users LIKE 'is_super_admin'")->fetchAll();
    if (empty($cols)) {
        $pdo->exec("ALTER TABLE users ADD COLUMN is_super_admin TINYINT(1) NOT NULL DEFAULT 0 AFTER role_id");
        $output[] = "✓ Added is_super_admin to users";
    } else {
        $output[] = "- is_super_admin already exists";
    }

    // Check/add created_by_super to tenants
    $cols = $pdo->query("SHOW COLUMNS FROM tenants LIKE 'created_by_super'")->fetchAll();
    if (empty($cols)) {
        $pdo->exec("ALTER TABLE tenants ADD COLUMN created_by_super INT NULL DEFAULT NULL AFTER status");
        $output[] = "✓ Added created_by_super to tenants";
    } else {
        $output[] = "- created_by_super already exists";
    }

    // Create default_checklists
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS default_checklists (
            default_checklist_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(200) NOT NULL,
            description TEXT NULL,
            asset_type_id INT UNSIGNED NULL,
            interval_days INT NULL DEFAULT 30,
            meter_name VARCHAR(100) NULL,
            meter_interval INT NULL,
            estimated_minutes INT NULL DEFAULT 60,
            is_active TINYINT(1) NOT NULL DEFAULT 1,
            created_by INT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NULL,
            deleted_at DATETIME NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");
    $output[] = "✓ Created/verified default_checklists";

    // Create default_checklist_tasks
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS default_checklist_tasks (
            task_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            default_checklist_id INT UNSIGNED NOT NULL,
            sort_order INT NOT NULL DEFAULT 0,
            title VARCHAR(250) NOT NULL,
            description TEXT NULL,
            input_type VARCHAR(20) NOT NULL DEFAULT 'checkbox',
            options_json TEXT NULL,
            is_required TINYINT(1) NOT NULL DEFAULT 0,
            photo_requirement VARCHAR(20) NOT NULL DEFAULT 'n_a',
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NULL,
            FOREIGN KEY (default_checklist_id) REFERENCES default_checklists(default_checklist_id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");
    $output[] = "✓ Created/verified default_checklist_tasks";

    // Seed superadmin if not exists
    $exists = $pdo->query("SELECT 1 FROM users WHERE username = 'superadmin' AND is_super_admin = 1")->fetch();
    if (!$exists) {
        $role = $pdo->query("SELECT role_id FROM roles WHERE code = 'admin' LIMIT 1")->fetch();
        if ($role) {
            $pdo->prepare("
                INSERT INTO users (tenant_id, username, f_name, l_name, password, email, role_id, is_super_admin, status, created_at)
                VALUES (NULL, 'superadmin', 'Super', 'Admin', :pw, 'super@orchview.local', :rid, 1, 'active', NOW())
            ")->execute([
                'pw' => password_hash('Admin@123', PASSWORD_DEFAULT),
                'rid' => $role['role_id']
            ]);
            $output[] = "✓ SuperAdmin user created (superadmin / Admin@123)";
        } else {
            $output[] = "⚠ Could not find admin role to assign";
        }
    } else {
        $output[] = "- superadmin user already exists";
    }

    $output[] = "\n✅ Migration complete. DELETE this file (public/migrate_super.php)!";
} catch (Exception $e) {
    $output[] = "❌ Error: " . $e->getMessage();
}

header('Content-Type: text/plain');
echo implode("\n", $output);