<?php
declare(strict_types=1);

require_once __DIR__ . '/config.php';

function golder_customer_by_username(PDO $master, string $username): ?array
{
    $username = trim($username);
    if ($username === '') {
        return null;
    }
    $st = $master->prepare('SELECT * FROM customers WHERE username = :u LIMIT 1');
    $st->execute([':u' => $username]);
    $row = $st->fetch();
    return is_array($row) ? $row : null;
}

function golder_customer_by_id(PDO $master, int $customerId): ?array
{
    if ($customerId <= 0) {
        return null;
    }
    $st = $master->prepare('SELECT * FROM customers WHERE id = :id LIMIT 1');
    $st->execute([':id' => $customerId]);
    $row = $st->fetch();
    return is_array($row) ? $row : null;
}

function golder_customer_by_portal_user_id(PDO $master, int $portalUserId): ?array
{
    if ($portalUserId <= 0) {
        return null;
    }
    $st = $master->prepare('SELECT * FROM customers WHERE portal_user_id = :pid LIMIT 1');
    $st->execute([':pid' => $portalUserId]);
    $row = $st->fetch();
    return is_array($row) ? $row : null;
}

function golder_customer_by_db_name(PDO $master, string $dbName): ?array
{
    $dbName = trim($dbName);
    if ($dbName === '') {
        return null;
    }
    $st = $master->prepare('SELECT * FROM customers WHERE mysql_db_name = :db LIMIT 1');
    $st->execute([':db' => $dbName]);
    $row = $st->fetch();
    return is_array($row) ? $row : null;
}

function golder_tenant_pdo_from_customer(array $customer): PDO
{
    $cfg = golder_master_db_config();
    $dbName = trim((string)($customer['mysql_db_name'] ?? ''));
    $dbUser = trim((string)($customer['mysql_db_user'] ?? ''));
    $dbPass = (string)($customer['mysql_db_pass'] ?? '');
    if ($dbName === '' || $dbUser === '') {
        throw new RuntimeException('Tenant db credentials are missing for customer.');
    }
    return new PDO(
        sprintf('mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4', $cfg['host'], (int)$cfg['port'], $dbName),
        $dbUser,
        $dbPass,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]
    );
}

function golder_tenant_pdo_from_customer_id(PDO $master, int $customerId): ?PDO
{
    $customer = golder_customer_by_id($master, $customerId);
    if (!$customer) {
        return null;
    }
    return golder_tenant_pdo_from_customer($customer);
}

function golder_ensure_customers_columns(PDO $master): void
{
    $columns = [
        'portal_user_id' => "ALTER TABLE customers ADD COLUMN portal_user_id BIGINT DEFAULT NULL AFTER phone_number",
        'desktop_expiry_date' => "ALTER TABLE customers ADD COLUMN desktop_expiry_date DATETIME NULL AFTER expiry_date",
        'sync_expiry_date' => "ALTER TABLE customers ADD COLUMN sync_expiry_date DATETIME NULL AFTER desktop_expiry_date",
        'max_pc_devices' => "ALTER TABLE customers ADD COLUMN max_pc_devices INT NOT NULL DEFAULT 1 AFTER max_devices",
        'max_web_sessions' => "ALTER TABLE customers ADD COLUMN max_web_sessions INT NOT NULL DEFAULT 0 AFTER max_pc_devices",
        'is_demo' => "ALTER TABLE customers ADD COLUMN is_demo TINYINT(1) NOT NULL DEFAULT 0 AFTER max_web_sessions",
        'demo_expires_at' => "ALTER TABLE customers ADD COLUMN demo_expires_at DATETIME NULL AFTER is_demo",
        'license_status' => "ALTER TABLE customers ADD COLUMN license_status VARCHAR(20) NOT NULL DEFAULT 'active' AFTER demo_expires_at",
        'last_web_login_date' => "ALTER TABLE customers ADD COLUMN last_web_login_date DATETIME NULL AFTER last_pc_login_date",
        'license_key_id' => "ALTER TABLE customers ADD COLUMN license_key_id VARCHAR(80) DEFAULT NULL AFTER license_status",
    ];

    foreach ($columns as $column => $sql) {
        try {
            $exists = $master->prepare(
                'SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t AND COLUMN_NAME = :c LIMIT 1'
            );
            $exists->execute([':t' => 'customers', ':c' => $column]);
            if ($exists->fetchColumn()) {
                continue;
            }
            $master->exec($sql);
        } catch (Throwable $_ignored) {
        }
    }

    try {
        $master->exec("ALTER TABLE customers MODIFY COLUMN plan_type VARCHAR(20) NOT NULL DEFAULT 'L-SYNC'");
    } catch (Throwable $_ignored) {
    }

    try {
        $master->exec("UPDATE customers SET plan_type = 'L-SYNC' WHERE TRIM(COALESCE(plan_type, '')) IN ('1', '2', '3')");
    } catch (Throwable $_ignored) {
    }

    try {
        $master->exec('UPDATE customers SET max_pc_devices = GREATEST(1, COALESCE(max_pc_devices, max_devices, 1))');
        $master->exec('UPDATE customers SET max_web_sessions = GREATEST(0, COALESCE(max_web_sessions, 0))');
        $master->exec('UPDATE customers SET max_devices = max_pc_devices WHERE COALESCE(max_devices, 0) <> max_pc_devices');
    } catch (Throwable $_ignored) {
    }

    try {
        $master->exec("UPDATE customers SET max_pc_devices = 1 WHERE plan_type IN ('L-SOLO', 'L-SYNC', 'A-SOLO')");
        $master->exec("UPDATE customers SET max_web_sessions = 0 WHERE plan_type IN ('L-SOLO', 'A-SOLO', 'A-OFFICE')");
        $master->exec("UPDATE customers SET max_devices = max_pc_devices WHERE COALESCE(max_devices, 0) <> max_pc_devices");
    } catch (Throwable $_ignored) {
    }

    try {
        $master->exec(
            "UPDATE customers
             SET sync_expiry_date = expiry_date
             WHERE plan_type = 'L-SYNC'
               AND (sync_expiry_date IS NULL OR sync_expiry_date = '')
               AND expiry_date IS NOT NULL
               AND expiry_date <> ''"
        );
        $master->exec(
            "UPDATE customers
             SET desktop_expiry_date = expiry_date
             WHERE plan_type IN ('A-SOLO', 'A-OFFICE', 'A-COMPLEX')
               AND (desktop_expiry_date IS NULL OR desktop_expiry_date = '')
               AND expiry_date IS NOT NULL
               AND expiry_date <> ''"
        );
        $master->exec(
            "UPDATE customers
             SET sync_expiry_date = expiry_date
             WHERE plan_type IN ('A-OFFICE', 'A-COMPLEX')
               AND (sync_expiry_date IS NULL OR sync_expiry_date = '')
               AND expiry_date IS NOT NULL
               AND expiry_date <> ''"
        );
        $master->exec("UPDATE customers SET desktop_expiry_date = NULL WHERE plan_type IN ('L-SOLO', 'L-SYNC')");
        $master->exec("UPDATE customers SET sync_expiry_date = NULL WHERE plan_type IN ('L-SOLO', 'A-SOLO')");
        $master->exec("UPDATE customers SET expiry_date = desktop_expiry_date WHERE desktop_expiry_date IS NOT NULL");
        $master->exec("UPDATE customers SET expiry_date = NULL WHERE plan_type IN ('L-SOLO', 'L-SYNC')");
    } catch (Throwable $_ignored) {
    }

    try {
        $master->exec('ALTER TABLE customers ADD KEY idx_customers_portal_user (portal_user_id)');
    } catch (Throwable $_ignored) {
    }
}


