<?php
declare(strict_types=1);

// Load environment configuration first
require_once __DIR__ . '/environment.php';

$envFile = __DIR__ . '/env.php';
$config = file_exists($envFile) ? require $envFile : [];

// Determine Driver and PDO Instance
$driver = defined('DB_TYPE') ? DB_TYPE : 'sqlite';

if ($driver === 'mysql') {
    $host = defined('DB_HOST') ? DB_HOST : 'localhost';
    $name = defined('DB_NAME') ? DB_NAME : 'golegal_db';
    $user = defined('DB_USER') ? DB_USER : 'root';
    $pass = defined('DB_PASS') ? DB_PASS : '';
    $port = defined('DB_PORT') ? DB_PORT : '3306';
    
    $dsn = "mysql:host=$host;port=$port;dbname=$name;charset=utf8mb4";
    $pdo = new PDO($dsn, $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false
    ]);
} else {
    // SQLite Default
    $dbFile = defined('DB_PATH') ? DB_PATH : __DIR__ . DIRECTORY_SEPARATOR . 'crms.sqlite';
    $pdo = new PDO('sqlite:' . $dbFile, null, null, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false
    ]);
}

// Database initialization and migrations
// Wrapped in a version check to prevent expensive schema operations on every request
$current_db_version = '20250505_v1';
$db_version = null;

try {
    // Check if settings table exists and get version
    $stmt = $pdo->query("SELECT value FROM settings WHERE `key` = 'db_version'");
    $db_version = $stmt->fetchColumn();
} catch (Exception $e) {
    // settings table might not exist yet
}

if ($db_version !== $current_db_version) {
    // Migration Lock Mechanism
    $lockFile = __DIR__ . '/migration.lock';
    if (file_exists($lockFile) && (time() - filemtime($lockFile) < 300)) {
        // Migration is already in progress by another request
        error_log("Migration skipped: lock file exists");
        return $pdo; 
    }
    
    // Create lock
    @file_put_contents($lockFile, time());

    // Increase limits for migration
    @set_time_limit(300);
    @ini_set('memory_limit', '512M');

    // Unified schema initialization if users table is missing
    $usersTableExists = false;
    try {
        $pdo->query("SELECT 1 FROM users LIMIT 1");
        $usersTableExists = true;
    } catch (Exception $e) {
        $usersTableExists = false;
    }

if (!$usersTableExists) {
    $schemaFile = __DIR__ . DIRECTORY_SEPARATOR . 'schema.sql';
    if (file_exists($schemaFile)) {
        $schema = file_get_contents($schemaFile);
        try {
            $pdo->exec($schema);
        } catch (PDOException $e) {
            error_log("Schema initialization failed: " . $e->getMessage());
        }
    }

    // Default Admin Account
    $now = date('c');
    try {
        $stmt = $pdo->prepare('INSERT INTO users (full_name,email,phone,password,role,status,created_at,updated_at) VALUES (?,?,?,?,?,?,?,?)');
        $stmt->execute([
            'Administrator',
            'admin@example.com',
            '00000000000',
            password_hash('Admin@123', PASSWORD_DEFAULT),
            'admin',
            'active',
            $now,
            $now
        ]);
    } catch (PDOException $e) {
        error_log("Failed to insert admin account: " . $e->getMessage());
    }

    // Ensure uploads directories exist
    $baseUploadDir = __DIR__ . '/../uploads';
    $uploadDirs = [
        $baseUploadDir,
        $baseUploadDir . '/certificates',
        $baseUploadDir . '/chat',
        $baseUploadDir . '/ids',
        $baseUploadDir . '/receipts',
        $baseUploadDir . '/profile',
        $baseUploadDir . '/settings'
    ];
    foreach ($uploadDirs as $dir) {
        if (!file_exists($dir)) {
            mkdir($dir, 0755, true);
            file_put_contents($dir . '/index.php', '<?php // Silence is golden');
        }
    }
}

// Automatic Migration for Services Table
try {
    $pdo->exec("ALTER TABLE services ADD COLUMN slug TEXT");
} catch (PDOException $e) { /* Column likely exists */ }
try {
    $pdo->exec("ALTER TABLE services ADD COLUMN base_price REAL DEFAULT 0");
} catch (PDOException $e) {}

// SEO System Migrations
try {
    $pdo->exec("ALTER TABLE services ADD COLUMN seo_title TEXT");
} catch (PDOException $e) {}
try {
    $pdo->exec("ALTER TABLE services ADD COLUMN seo_description TEXT");
} catch (PDOException $e) {}
try {
    $pdo->exec("ALTER TABLE services ADD COLUMN seo_keywords TEXT");
} catch (PDOException $e) {}

try {
    $pdo->exec("CREATE TABLE IF NOT EXISTS locations (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL UNIQUE,
      slug TEXT NOT NULL UNIQUE,
      state TEXT,
      status TEXT DEFAULT 'active',
      created_at TEXT NOT NULL DEFAULT (datetime('now'))
    )");
} catch (PDOException $e) {}

try {
    $pdo->exec("CREATE TABLE IF NOT EXISTS seo_landings (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      service_id INTEGER NOT NULL,
      location_id INTEGER NOT NULL,
      slug TEXT NOT NULL UNIQUE,
      title TEXT,
      description TEXT,
      h1_title TEXT,
      content_json TEXT,
      status TEXT DEFAULT 'active',
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE,
      FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE CASCADE
    )");
} catch (PDOException $e) {}

// Seed initial locations if table is empty
try {
    $count = $pdo->query("SELECT COUNT(*) FROM locations")->fetchColumn();
    if ($count == 0) {
        $pdo->exec("INSERT INTO locations (name, slug, state) VALUES
        ('Lagos', 'lagos', 'Lagos'),
        ('Abuja', 'abuja', 'FCT'),
        ('Port Harcourt', 'port-harcourt', 'Rivers'),
        ('Ibadan', 'ibadan', 'Oyo'),
        ('Kano', 'kano', 'Kano'),
        ('Enugu', 'enugu', 'Enugu'),
        ('Benin City', 'benin-city', 'Edo'),
        ('Abeokuta', 'abeokuta', 'Ogun')");
    }
} catch (PDOException $e) {}

// Populate missing slugs for existing rows
$slugMap = [
    'Business Name Registration' => 'business_name',
    'Company Registration (LTD)' => 'limited_company',
    'Limited Company Registration' => 'limited_company',
    'NGO / Foundation' => 'ngo',
    'NGO/Incorporated Trustee' => 'ngo',
    'Trademark Registration' => 'trademark',
    'TIN Registration' => 'tin',
    'SCUML Registration' => 'scuml'
];
foreach ($slugMap as $name => $slug) {
    $pdo->prepare("UPDATE services SET slug = ? WHERE service_name = ? AND (slug IS NULL OR slug = '')")->execute([$slug, $name]);
}

try {
    // Seed core services if empty
    $count = (int)$pdo->query("SELECT COUNT(*) FROM services")->fetchColumn();
    if ($count == 0) {
        $pdo->exec("INSERT OR IGNORE INTO services (service_name, slug, cac_fee, service_fee, base_price, created_at) VALUES
            ('Business Name Registration', 'business_name', 10000, 15000, 25000, datetime('now')),
            ('Company Registration (LTD)', 'limited_company', 25000, 35000, 60000, datetime('now')),
            ('NGO / Foundation', 'ngo', 50000, 70000, 120000, datetime('now')),
            ('Trademark Registration', 'trademark', 30000, 70000, 100000, datetime('now')),
            ('TIN Registration', 'tin', 0, 15000, 15000, datetime('now')),
            ('SCUML Registration', 'scuml', 0, 30000, 30000, datetime('now'))");
    }
} catch (PDOException $e) {}

$autoInc = 'INTEGER PRIMARY KEY AUTOINCREMENT';
$timestampDef = "(datetime('now'))";

try {
    $pdo->exec("CREATE TABLE IF NOT EXISTS user_profiles (
      id {$autoInc},
      user_id INTEGER NOT NULL UNIQUE,
      full_name TEXT,
      email TEXT,
      phone TEXT,
      date_of_birth TEXT,
      nationality TEXT DEFAULT 'Nigerian',
      gender TEXT,
      business_name TEXT,
      business_address TEXT,
      nature_of_business TEXT,
      id_type TEXT,
      id_number TEXT,
      id_document_path TEXT,
      passport_path TEXT,
      signature_path TEXT,
      residential_address TEXT,
      city TEXT,
      state TEXT,
      country TEXT DEFAULT 'Nigeria',
      next_of_kin_name TEXT,
      next_of_kin_phone TEXT,
      next_of_kin_relationship TEXT,
      created_at TEXT NOT NULL DEFAULT {$timestampDef},
      updated_at TEXT NOT NULL DEFAULT {$timestampDef},
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )");
} catch (PDOException $e) {}

try {
    $pdo->exec("CREATE TABLE IF NOT EXISTS settings (
      `key` TEXT PRIMARY KEY,
      value TEXT,
      category TEXT DEFAULT 'general',
      type TEXT DEFAULT 'string',
      description TEXT,
      updated_at TEXT
    )");
} catch (PDOException $e) {
    error_log("Failed to create settings table: " . $e->getMessage());
}

try {
    $pdo->exec("CREATE TABLE IF NOT EXISTS notifications (
      id {$autoInc},
      user_id INTEGER NOT NULL,
      type TEXT NOT NULL,
      title TEXT,
      body TEXT,
      data_json TEXT,
      status TEXT NOT NULL DEFAULT 'unread',
      created_at TEXT NOT NULL,
      read_at TEXT,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )");
} catch (PDOException $e) {}

    try {
        $pdo->exec("CREATE TABLE IF NOT EXISTS packages (
          id {$autoInc},
          name TEXT NOT NULL UNIQUE,
          description TEXT,
          status TEXT NOT NULL DEFAULT 'active',
          discount_type TEXT NOT NULL DEFAULT 'none',
          discount_value REAL NOT NULL DEFAULT 0,
          discount_tiers_json TEXT,
          created_at TEXT NOT NULL
        )");
    } catch (PDOException $e) {}

    try {
        $pdo->exec("CREATE TABLE IF NOT EXISTS package_items (
          id {$autoInc},
          package_id INTEGER NOT NULL,
          service_id INTEGER NOT NULL,
          quantity INTEGER NOT NULL DEFAULT 1,
          UNIQUE(package_id, service_id)
        )");
    } catch (PDOException $e) {}

    // Ensure core packages exist and attach single services
    $now = date('c');
    $ensurePkg = function(string $pkgName) use ($pdo, $now) : int {
      $q = $pdo->prepare("SELECT id FROM packages WHERE name=?"); $q->execute([$pkgName]); $row = $q->fetch();
      if ($row) return (int)$row['id'];
      $ins = $pdo->prepare("INSERT INTO packages (name,description,status,discount_type,discount_value,discount_tiers_json,created_at) VALUES (?,?,?,?,?,?,?)");
      $ins->execute([$pkgName,null,'active','none',0,null,$now]);
      return (int)$pdo->lastInsertId();
    };
    $findSvcId = function(string $serviceName) use ($pdo) : ?int {
      $x = $pdo->prepare("SELECT id FROM services WHERE service_name=? AND status='active'"); $x->execute([$serviceName]); $r = $x->fetch();
      return $r ? (int)$r['id'] : null;
    };
    $attach = function(int $pkgId, array $serviceNames) use ($pdo, $findSvcId) {
      $pdo->prepare("DELETE FROM package_items WHERE package_id=?")->execute([$pkgId]);
      $ins = $pdo->prepare("INSERT INTO package_items (package_id, service_id, quantity) VALUES (?,?,1)");
      foreach ($serviceNames as $n) { $sid = $findSvcId($n); if ($sid) $ins->execute([$pkgId,$sid]); }
    };
    
    // Create packages
    $p1 = $ensurePkg('Package: Hustle Starter (Business Name)'); $attach($p1, ['Business Name Registration']);
    $p2 = $ensurePkg('Package: Corporate Builder (Ltd Company)'); $attach($p2, ['Company Registration (1M Shares)']);
    $p3 = $ensurePkg('Package: NGO Authority (Incorporated Trustee)'); $attach($p3, ['NGO Registration']);
    $p4 = $ensurePkg('Plan: Compliance Care (BN) – Annual'); $attach($p4, ['Annual Returns Filing (Business Name)']);
    $p5 = $ensurePkg('Plan: Compliance Care (Company) – Annual'); $attach($p5, ['Annual Returns Filing (Company)']);

    try {
        $pdo->exec("CREATE TABLE IF NOT EXISTS chat_threads (
        id {$autoInc},
        user_id INTEGER,
        guest_id TEXT UNIQUE,
        last_message_at TEXT DEFAULT {$timestampDef},
        last_active_at TEXT DEFAULT {$timestampDef},
        status TEXT DEFAULT 'active',
        created_at TEXT NOT NULL DEFAULT {$timestampDef},
        UNIQUE(user_id),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
    )");
} catch (PDOException $e) {}

    try {
        $pdo->exec("CREATE TABLE IF NOT EXISTS chat_messages (
            id {$autoInc},
            thread_id INTEGER NOT NULL,
            sender_id INTEGER,
            guest_id TEXT,
            is_admin INTEGER DEFAULT 0,
            body TEXT NOT NULL,
            attachment_path TEXT,
            status TEXT DEFAULT 'sent',
            created_at TEXT NOT NULL DEFAULT {$timestampDef},
            read_at TEXT,
            FOREIGN KEY (thread_id) REFERENCES chat_threads(id) ON DELETE CASCADE,
            FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE SET NULL
        )");
    } catch (PDOException $e) {}

    // Mark migration as complete
    try {
        $stmt = $pdo->prepare("INSERT OR REPLACE INTO settings (`key`, value, category, type, updated_at) VALUES ('db_version', ?, 'system', 'string', ?)");
        $stmt->execute([$current_db_version, date('c')]);
    } catch (Exception $e) {
        // Fallback if settings table just created but not fully ready
    } finally {
        // Always remove lock if we created it
        if (isset($lockFile) && file_exists($lockFile)) {
            @unlink($lockFile);
        }
    }
}

return $pdo;

