<?php
declare(strict_types=1);
require_once __DIR__ . '/database.php';

// Apply schema updates to existing database
$pdo = require __DIR__ . '/database.php';

// 1. Update services table
$updates = [
    "ALTER TABLE services ADD COLUMN service_type TEXT CHECK(service_type IN ('business_name','company','ngo','tax','scuml','trademark','post_incorporation','package','plan'))",
    "ALTER TABLE services ADD COLUMN category TEXT DEFAULT 'service'",
    "ALTER TABLE services ADD COLUMN is_package BOOLEAN DEFAULT 0",
    "ALTER TABLE services ADD COLUMN description TEXT"
];

foreach ($updates as $sql) {
    try {
        $pdo->exec($sql);
        echo "✅ Applied: " . substr($sql, 0, 50) . "...\n";
    } catch (Exception $e) {
        echo "⚠️  Skipped (already exists): " . substr($sql, 0, 50) . "...\n";
    }
}

// 2. Create new tables
$tables = [
    "CREATE TABLE IF NOT EXISTS shareholders (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        application_id INTEGER NOT NULL,
        full_name TEXT NOT NULL,
        email TEXT,
        phone TEXT,
        nationality TEXT,
        id_type TEXT,
        id_number TEXT,
        share_percentage REAL DEFAULT 0,
        is_director BOOLEAN DEFAULT 0,
        is_trustee BOOLEAN DEFAULT 0,
        date_of_birth TEXT,
        residential_address TEXT,
        passport_path TEXT,
        id_document_path TEXT,
        created_at TEXT NOT NULL,
        FOREIGN KEY(application_id) REFERENCES applications(id) ON DELETE CASCADE
    )",
    
    "CREATE TABLE IF NOT EXISTS verification_checkpoints (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        application_id INTEGER NOT NULL,
        checkpoint_type TEXT NOT NULL CHECK(checkpoint_type IN ('name_screening','document_verification','share_structure','compliance_check','risk_assessment')),
        status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','passed','failed','requires_attention')),
        notes TEXT,
        verified_by INTEGER,
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL,
        FOREIGN KEY(application_id) REFERENCES applications(id) ON DELETE CASCADE),
        FOREIGN KEY(verified_by) REFERENCES users(id)
    )",
    
    "CREATE TABLE IF NOT EXISTS internal_notes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        application_id INTEGER NOT NULL,
        admin_id INTEGER NOT NULL,
        note_type TEXT NOT NULL CHECK(note_type IN ('verification','risk','compliance','client_communication')),
        content TEXT NOT NULL,
        is_internal BOOLEAN DEFAULT 1,
        created_at TEXT NOT NULL,
        FOREIGN KEY(application_id) REFERENCES applications(id) ON DELETE CASCADE),
        FOREIGN KEY(admin_id) REFERENCES users(id)
    )",
    
    "CREATE TABLE IF NOT EXISTS compliance_tracking (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        application_id INTEGER NOT NULL,
        compliance_type TEXT NOT NULL,
        due_date TEXT,
        completed_date TEXT,
        status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','completed','overdue','not_applicable')),
        notes TEXT,
        created_at TEXT NOT NULL,
        FOREIGN KEY(application_id) REFERENCES applications(id) ON DELETE CASCADE
    )"
];

foreach ($tables as $sql) {
    $pdo->exec($sql);
    echo "✅ Created table\n";
}

// 3. Update applications table
$appUpdates = [
    "ALTER TABLE applications ADD COLUMN service_category TEXT",
    "ALTER TABLE applications ADD COLUMN priority_level TEXT DEFAULT 'normal' CHECK(priority_level IN ('low','normal','high','urgent'))",
    "ALTER TABLE applications ADD COLUMN risk_score INTEGER DEFAULT 0",
    "ALTER TABLE applications ADD COLUMN internal_status TEXT DEFAULT 'new' CHECK(internal_status IN ('new','under_review','verified','flagged','approved_internal'))"
];

foreach ($appUpdates as $sql) {
    try {
        $pdo->exec($sql);
        echo "✅ Applied to applications: " . substr($sql, 0, 50) . "...\n";
    } catch (Exception $e) {
        echo "⚠️  Skipped: " . substr($sql, 0, 50) . "...\n";
    }
}

// 4. Create indexes
$indexes = [
    "CREATE INDEX IF NOT EXISTS idx_shareholders_application ON shareholders(application_id)",
    "CREATE INDEX IF NOT EXISTS idx_verification_application ON verification_checkpoints(application_id)",
    "CREATE INDEX IF NOT EXISTS idx_notes_application ON internal_notes(application_id)",
    "CREATE INDEX IF NOT EXISTS idx_compliance_application ON compliance_tracking(application_id)",
    "CREATE INDEX IF NOT EXISTS idx_applications_status ON applications(status, internal_status)"
];

foreach ($indexes as $sql) {
    $pdo->exec($sql);
    echo "✅ Created index\n";
}

// 5. Update existing services with proper categorization
$serviceUpdates = [
    "UPDATE services SET service_type='business_name', category='registration' WHERE service_name LIKE '%Business Name%'",
    "UPDATE services SET service_type='company', category='registration' WHERE service_name LIKE '%Company%'",
    "UPDATE services SET service_type='ngo', category='registration' WHERE service_name LIKE '%NGO%'",
    "UPDATE services SET service_type='tax', category='tax' WHERE service_name LIKE '%TIN%'",
    "UPDATE services SET service_type='scuml', category='compliance' WHERE service_name LIKE '%SCUML%'",
    "UPDATE services SET service_type='post_incorporation', category='maintenance' WHERE service_name LIKE '%Annual Returns%' OR service_name LIKE '%Relisting%' OR service_name LIKE '%Striking Off%'",
    "UPDATE services SET service_type='package', is_package=1, category='package' WHERE service_name LIKE 'Package:%'",
    "UPDATE services SET service_type='plan', is_package=1, category='package' WHERE service_name LIKE 'Plan:%'"
];

foreach ($serviceUpdates as $sql) {
    $pdo->exec($sql);
    echo "✅ Categorized services\n";
}

echo "\n🎉 Database schema updated successfully!\n";
echo "📊 Added professional structure for:\n";
echo "   • Service categorization\n";
echo "   • Shareholder/director management\n";
echo "   • Verification checkpoints\n";
echo "   • Internal notes system\n";
echo "   • Compliance tracking\n";
echo "   • Risk assessment\n";
