<?php
declare(strict_types=1);

require_once __DIR__ . '/session_store.php';
require_once __DIR__ . '/master_db.php';
require_once __DIR__ . '/customer_provision.php';
require_once __DIR__ . '/mailer.php';

function golder_ensure_table_column(PDO $master, string $table, string $column, string $alterSql): void
{
    $st = $master->prepare(
        'SELECT 1
         FROM INFORMATION_SCHEMA.COLUMNS
         WHERE TABLE_SCHEMA = DATABASE()
           AND TABLE_NAME = :t
           AND COLUMN_NAME = :c
         LIMIT 1'
    );
    $st->execute([':t' => $table, ':c' => $column]);
    if ($st->fetchColumn()) {
        return;
    }
    $master->exec($alterSql);
}

function golder_ensure_demo_license_tables(PDO $master): void
{
    $master->exec(
        "CREATE TABLE IF NOT EXISTS demo_licenses (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            customer_id BIGINT NOT NULL,
            demo_code VARCHAR(40) NOT NULL,
            starts_at DATETIME NOT NULL,
            expires_at DATETIME NOT NULL,
            status VARCHAR(20) NOT NULL DEFAULT 'active',
            cancel_reason VARCHAR(120) DEFAULT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_demo_code (demo_code),
            KEY idx_customer_status (customer_id, status),
            KEY idx_expires (expires_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
    );
}

function golder_ensure_payment_tables(PDO $master): void
{
    $master->exec(
        "CREATE TABLE IF NOT EXISTS pricing_rules (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            plan_type VARCHAR(10) NOT NULL,
            duration_key VARCHAR(40) NOT NULL,
            duration_days INT NOT NULL,
            base_price DECIMAL(19,4) NOT NULL DEFAULT 0,
            per_pc_price DECIMAL(19,4) NOT NULL DEFAULT 0,
            per_web_price DECIMAL(19,4) NOT NULL DEFAULT 0,
            currency VARCHAR(10) NOT NULL DEFAULT 'TRY',
            is_active TINYINT(1) NOT NULL DEFAULT 1,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_plan_duration (plan_type, duration_key)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
    );

    $master->exec(
        "CREATE TABLE IF NOT EXISTS bank_accounts (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            account_name VARCHAR(120) NOT NULL,
            iban VARCHAR(64) NOT NULL,
            is_active TINYINT(1) NOT NULL DEFAULT 1,
            display_order INT NOT NULL DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
    );

    $master->exec(
        "CREATE TABLE IF NOT EXISTS payment_requests (
            id CHAR(36) PRIMARY KEY,
            payment_code VARCHAR(40) NOT NULL,
            customer_id BIGINT DEFAULT NULL,
            portal_user_id BIGINT DEFAULT NULL,
            application_username VARCHAR(190) DEFAULT NULL,
            full_name VARCHAR(120) NOT NULL,
            email VARCHAR(190) DEFAULT NULL,
            phone VARCHAR(40) DEFAULT NULL,
            requested_plan_type VARCHAR(10) NOT NULL,
            requested_pc_devices INT NOT NULL,
            requested_web_sessions INT NOT NULL,
            duration_key VARCHAR(40) NOT NULL,
            amount DECIMAL(19,4) NOT NULL DEFAULT 0,
            submitted_amount DECIMAL(19,4) NOT NULL DEFAULT 0,
            calculated_amount DECIMAL(19,4) NOT NULL DEFAULT 0,
            amount_delta DECIMAL(19,4) NOT NULL DEFAULT 0,
            amount_mismatch_flag TINYINT(1) NOT NULL DEFAULT 0,
            currency VARCHAR(10) NOT NULL DEFAULT 'TRY',
            iban_id BIGINT DEFAULT NULL,
            status VARCHAR(20) NOT NULL DEFAULT 'pending',
            admin_message TEXT,
            provisioned_at DATETIME DEFAULT NULL,
            mail_status VARCHAR(20) NOT NULL DEFAULT 'none',
            mail_last_error TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            approved_at DATETIME DEFAULT NULL,
            rejected_at DATETIME DEFAULT NULL,
            reviewed_by VARCHAR(120) DEFAULT NULL,
            UNIQUE KEY uniq_payment_code (payment_code),
            KEY idx_status_created (status, created_at),
            KEY idx_portal_user_status (portal_user_id, status, created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
    );

    $master->exec(
        "CREATE TABLE IF NOT EXISTS payment_request_messages (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            payment_request_id CHAR(36) NOT NULL,
            sender_type VARCHAR(20) NOT NULL,
            sender_name VARCHAR(120) DEFAULT NULL,
            message_text TEXT NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            KEY idx_payment_created (payment_request_id, created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
    );

    golder_ensure_demo_license_tables($master);

    $master->exec(
        "CREATE TABLE IF NOT EXISTS sales_portal_users (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            full_name VARCHAR(120) NOT NULL,
            email VARCHAR(190) NOT NULL,
            phone VARCHAR(40) DEFAULT NULL,
            password_hash VARCHAR(255) NOT NULL,
            email_verified_at DATETIME DEFAULT NULL,
            is_active TINYINT(1) NOT NULL DEFAULT 1,
            last_login_at DATETIME DEFAULT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_sales_portal_email (email),
            KEY idx_sales_portal_email_verified (email, email_verified_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
    );

    $master->exec(
        "CREATE TABLE IF NOT EXISTS sales_portal_email_tokens (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            user_id BIGINT NOT NULL,
            token_hash CHAR(64) NOT NULL,
            purpose VARCHAR(40) NOT NULL,
            expires_at DATETIME NOT NULL,
            used_at DATETIME DEFAULT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_sales_portal_token (token_hash),
            KEY idx_sales_portal_user_purpose (user_id, purpose, expires_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
    );

    $master->exec(
        "CREATE TABLE IF NOT EXISTS demo_requests (
            id CHAR(36) PRIMARY KEY,
            portal_user_id BIGINT NOT NULL,
            full_name VARCHAR(120) NOT NULL,
            email VARCHAR(190) DEFAULT NULL,
            phone VARCHAR(40) DEFAULT NULL,
            requested_plan_type VARCHAR(10) NOT NULL DEFAULT 'A-COMPLEX',
            requested_pc_devices INT NOT NULL DEFAULT 1,
            requested_web_sessions INT NOT NULL DEFAULT 1,
            status VARCHAR(20) NOT NULL DEFAULT 'pending',
            admin_message TEXT,
            approved_by VARCHAR(120) DEFAULT NULL,
            rejected_by VARCHAR(120) DEFAULT NULL,
            approved_at DATETIME DEFAULT NULL,
            rejected_at DATETIME DEFAULT NULL,
            approved_customer_id BIGINT DEFAULT NULL,
            demo_code VARCHAR(40) DEFAULT NULL,
            demo_username VARCHAR(120) DEFAULT NULL,
            demo_password_enc TEXT,
            demo_expires_at DATETIME DEFAULT NULL,
            mail_status VARCHAR(20) NOT NULL DEFAULT 'none',
            mail_last_error TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            KEY idx_demo_requests_portal_status (portal_user_id, status, created_at),
            KEY idx_demo_requests_status_created (status, created_at),
            KEY idx_demo_requests_customer (approved_customer_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
    );

    $master->exec(
        "CREATE TABLE IF NOT EXISTS demo_request_messages (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            demo_request_id CHAR(36) NOT NULL,
            sender_type VARCHAR(20) NOT NULL,
            sender_name VARCHAR(120) DEFAULT NULL,
            message_text TEXT NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            KEY idx_demo_request_created (demo_request_id, created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
    );

    $master->exec(
        "CREATE TABLE IF NOT EXISTS support_threads (
            id CHAR(36) PRIMARY KEY,
            portal_user_id BIGINT NOT NULL,
            customer_id BIGINT DEFAULT NULL,
            subject VARCHAR(190) NOT NULL,
            status VARCHAR(20) NOT NULL DEFAULT 'open',
            assigned_admin VARCHAR(120) DEFAULT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            last_message_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            closed_at DATETIME DEFAULT NULL,
            KEY idx_support_portal_updated (portal_user_id, updated_at),
            KEY idx_support_status_updated (status, updated_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
    );

    $master->exec(
        "CREATE TABLE IF NOT EXISTS support_thread_messages (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            thread_id CHAR(36) NOT NULL,
            sender_type VARCHAR(20) NOT NULL,
            sender_name VARCHAR(120) DEFAULT NULL,
            message_text TEXT NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            KEY idx_support_thread_created (thread_id, created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
    );

    golder_ensure_table_column(
        $master,
        'payment_requests',
        'portal_user_id',
        'ALTER TABLE payment_requests ADD COLUMN portal_user_id BIGINT DEFAULT NULL AFTER customer_id'
    );
    golder_ensure_table_column(
        $master,
        'payment_requests',
        'application_username',
        'ALTER TABLE payment_requests ADD COLUMN application_username VARCHAR(190) DEFAULT NULL AFTER portal_user_id'
    );
    golder_ensure_table_column(
        $master,
        'payment_requests',
        'submitted_amount',
        'ALTER TABLE payment_requests ADD COLUMN submitted_amount DECIMAL(19,4) NOT NULL DEFAULT 0 AFTER amount'
    );
    golder_ensure_table_column(
        $master,
        'payment_requests',
        'provisioned_at',
        'ALTER TABLE payment_requests ADD COLUMN provisioned_at DATETIME DEFAULT NULL AFTER admin_message'
    );
    golder_ensure_table_column(
        $master,
        'payment_requests',
        'mail_status',
        "ALTER TABLE payment_requests ADD COLUMN mail_status VARCHAR(20) NOT NULL DEFAULT 'none' AFTER provisioned_at"
    );
    golder_ensure_table_column(
        $master,
        'payment_requests',
        'mail_last_error',
        'ALTER TABLE payment_requests ADD COLUMN mail_last_error TEXT AFTER mail_status'
    );
    golder_ensure_table_column(
        $master,
        'payment_requests',
        'calculated_amount',
        'ALTER TABLE payment_requests ADD COLUMN calculated_amount DECIMAL(19,4) NOT NULL DEFAULT 0 AFTER submitted_amount'
    );
    golder_ensure_table_column(
        $master,
        'payment_requests',
        'amount_delta',
        'ALTER TABLE payment_requests ADD COLUMN amount_delta DECIMAL(19,4) NOT NULL DEFAULT 0 AFTER calculated_amount'
    );
    golder_ensure_table_column(
        $master,
        'payment_requests',
        'amount_mismatch_flag',
        'ALTER TABLE payment_requests ADD COLUMN amount_mismatch_flag TINYINT(1) NOT NULL DEFAULT 0 AFTER amount_delta'
    );

    try {
        $master->exec('ALTER TABLE payment_requests ADD KEY idx_portal_user_status (portal_user_id, status, created_at)');
    } catch (Throwable $_ignored) {
    }

    golder_seed_default_pricing_rules($master);
}

function golder_seed_default_pricing_rules(PDO $master): void
{
    $defaults = [
        ['L-SOLO', 'yearly', 365, '12000.00', '0.00', '0.00', 'TRY', 1],
        ['L-SYNC', 'yearly', 365, '20000.00', '0.00', '2000.00', 'TRY', 1],
        ['A-SOLO', 'yearly', 365, '12000.00', '0.00', '0.00', 'TRY', 1],
        ['A-OFFICE', 'yearly', 365, '15000.00', '2500.00', '0.00', 'TRY', 1],
        ['A-COMPLEX', 'yearly', 365, '20000.00', '2500.00', '2000.00', 'TRY', 1],
        ['A-COMPLEX', golder_demo_duration_key(), golder_demo_duration_days(), '0.00', '0.00', '0.00', 'TRY', 1],
    ];

    $st = $master->prepare(
        'INSERT IGNORE INTO pricing_rules
         (plan_type, duration_key, duration_days, base_price, per_pc_price, per_web_price, currency, is_active)
         VALUES (:pt, :dk, :dd, :bp, :pp, :pw, :cu, :ia)'
    );

    foreach ($defaults as $row) {
        $st->execute([
            ':pt' => $row[0],
            ':dk' => $row[1],
            ':dd' => $row[2],
            ':bp' => $row[3],
            ':pp' => $row[4],
            ':pw' => $row[5],
            ':cu' => $row[6],
            ':ia' => $row[7],
        ]);
    }

    $master->prepare(
        'UPDATE pricing_rules
         SET duration_days = :dd,
             base_price = :bp,
             per_pc_price = :pp,
             per_web_price = :pw,
             currency = :cu,
             is_active = 1
         WHERE plan_type = :pt
           AND duration_key = :dk'
    )->execute([
        ':dd' => golder_demo_duration_days(),
        ':bp' => '0.00',
        ':pp' => '0.00',
        ':pw' => '0.00',
        ':cu' => 'TRY',
        ':pt' => 'A-COMPLEX',
        ':dk' => golder_demo_duration_key(),
    ]);

    $master->prepare(
        "DELETE FROM pricing_rules
         WHERE plan_type = 'A-COMPLEX'
           AND duration_key IN ('2_days', 'demo_2_days', 'demo_7_days', '1_week')"
    )->execute();

    $master->prepare(
        'UPDATE payment_requests
         SET duration_key = :demo_key
         WHERE duration_key IN (\'2_days\', \'demo_2_days\', \'demo_7_days\', \'1_week\')'
    )->execute([
        ':demo_key' => golder_demo_duration_key(),
    ]);

    // Global yearly policy for commercial pricing.
    $master->prepare("DELETE FROM pricing_rules WHERE duration_key NOT IN ('yearly', '7_days')")->execute();
    $master->prepare("UPDATE pricing_rules SET per_web_price = 0 WHERE plan_type IN ('L-SOLO', 'A-SOLO', 'A-OFFICE')")->execute();
    $master->prepare("UPDATE pricing_rules SET is_active = 1 WHERE duration_key = 'yearly'")->execute();
}

function golder_generate_payment_code(PDO $master): string
{
    golder_ensure_payment_tables($master);

    $prefix = 'PAY-' . gmdate('Ymd');
    for ($i = 0; $i < 10; $i++) {
        $candidate = $prefix . '-' . strtoupper(substr(bin2hex(random_bytes(4)), 0, 6));
        $st = $master->prepare('SELECT 1 FROM payment_requests WHERE payment_code = :c LIMIT 1');
        $st->execute([':c' => $candidate]);
        if (!$st->fetchColumn()) {
            return $candidate;
        }
    }

    return $prefix . '-' . strtoupper(substr(hash('sha256', microtime(true) . random_int(1000, 9999)), 0, 6));
}

function golder_generate_demo_code(PDO $master): string
{
    golder_ensure_demo_license_tables($master);

    for ($i = 0; $i < 10; $i++) {
        $candidate = 'DEMO-' . gmdate('Ymd') . '-' . strtoupper(substr(bin2hex(random_bytes(4)), 0, 6));
        $st = $master->prepare('SELECT 1 FROM demo_licenses WHERE demo_code = :c LIMIT 1');
        $st->execute([':c' => $candidate]);
        if (!$st->fetchColumn()) {
            return $candidate;
        }
    }

    return 'DEMO-' . gmdate('Ymd') . '-' . strtoupper(substr(hash('sha256', microtime(true) . random_int(1000, 9999)), 0, 6));
}

function golder_list_active_ibans(PDO $master): array
{
    golder_ensure_payment_tables($master);
    $st = $master->query('SELECT id, account_name, iban FROM bank_accounts WHERE is_active = 1 ORDER BY display_order ASC, id ASC');
    return $st->fetchAll() ?: [];
}

function golder_allowed_duration_keys(): array
{
    return ['yearly', golder_demo_duration_key()];
}

function golder_normalize_duration_key(string $durationKey): string
{
    $durationKey = strtolower(trim($durationKey));
    if (in_array($durationKey, ['2_days', 'demo_2_days', 'demo_7_days', '1_week'], true)) {
        return golder_demo_duration_key();
    }
    return in_array($durationKey, golder_allowed_duration_keys(), true) ? $durationKey : 'yearly';
}

function golder_find_pricing_rule(PDO $master, string $planType, string $durationKey, bool $activeOnly = true): ?array
{
    golder_ensure_payment_tables($master);

    $durationKey = golder_normalize_duration_key($durationKey);
    if ($durationKey !== golder_demo_duration_key()) {
        $durationKey = 'yearly';
    }

    $sql = 'SELECT * FROM pricing_rules WHERE plan_type = :p AND duration_key = :d';
    if ($activeOnly) {
        $sql .= ' AND is_active = 1';
    }
    $sql .= ' LIMIT 1';

    $st = $master->prepare($sql);
    $st->execute([
        ':p' => trim($planType),
        ':d' => $durationKey,
    ]);
    $row = $st->fetch();
    return is_array($row) ? $row : null;
}

function golder_calculate_price(array $pricingRule, array $normalizedPlan): array
{
    $base = (float)($pricingRule['base_price'] ?? 0);
    $perPc = (float)($pricingRule['per_pc_price'] ?? 0);
    $perWeb = (float)($pricingRule['per_web_price'] ?? 0);
    $total = $base + max(0, (int)$normalizedPlan['max_pc_devices'] - 1) * $perPc + max(0, (int)$normalizedPlan['max_web_sessions']) * $perWeb;
    return [
        'total' => round($total, 2),
        'currency' => (string)($pricingRule['currency'] ?? 'TRY'),
    ];
}

function golder_payment_request_messages(PDO $master, string $requestId): array
{
    golder_ensure_payment_tables($master);
    $st = $master->prepare(
        'SELECT id, sender_type, sender_name, message_text, created_at
         FROM payment_request_messages
         WHERE payment_request_id = :id
         ORDER BY created_at ASC, id ASC'
    );
    $st->execute([':id' => $requestId]);
    return $st->fetchAll() ?: [];
}

function golder_demo_request_messages(PDO $master, string $requestId): array
{
    golder_ensure_payment_tables($master);
    $st = $master->prepare(
        'SELECT id, sender_type, sender_name, message_text, created_at
         FROM demo_request_messages
         WHERE demo_request_id = :id
         ORDER BY created_at ASC, id ASC'
    );
    $st->execute([':id' => $requestId]);
    return $st->fetchAll() ?: [];
}

function golder_support_thread_messages(PDO $master, string $threadId): array
{
    golder_ensure_payment_tables($master);
    $st = $master->prepare(
        'SELECT id, sender_type, sender_name, message_text, created_at
         FROM support_thread_messages
         WHERE thread_id = :id
         ORDER BY created_at ASC, id ASC'
    );
    $st->execute([':id' => $threadId]);
    return $st->fetchAll() ?: [];
}

function golder_support_thread_by_id(PDO $master, string $threadId): ?array
{
    golder_ensure_payment_tables($master);
    $threadId = trim($threadId);
    if ($threadId === '') {
        return null;
    }
    $st = $master->prepare(
        'SELECT st.*, spu.full_name AS portal_full_name, spu.email AS portal_email, c.username AS customer_username
         FROM support_threads st
         LEFT JOIN sales_portal_users spu ON spu.id = st.portal_user_id
         LEFT JOIN customers c ON c.id = st.customer_id
         WHERE st.id = :id
         LIMIT 1'
    );
    $st->execute([':id' => $threadId]);
    $row = $st->fetch();
    return is_array($row) ? $row : null;
}

function golder_create_support_thread(PDO $master, int $portalUserId, string $subject, string $message, string $senderName): array
{
    golder_ensure_payment_tables($master);
    $subject = trim($subject);
    $message = trim($message);
    if ($portalUserId <= 0 || $subject === '' || $message === '') {
        return ['ok' => false, 'message' => 'support thread payload is invalid.'];
    }
    $threadId = golder_uuid4();
    $customer = golder_customer_by_portal_user_id($master, $portalUserId);
    $customerId = (int)($customer['id'] ?? 0);

    $master->prepare(
        'INSERT INTO support_threads
         (id, portal_user_id, customer_id, subject, status, assigned_admin, created_at, updated_at, last_message_at)
         VALUES (:id, :uid, :cid, :sb, :st, NULL, UTC_TIMESTAMP(), UTC_TIMESTAMP(), UTC_TIMESTAMP())'
    )->execute([
        ':id' => $threadId,
        ':uid' => $portalUserId,
        ':cid' => ($customerId > 0 ? $customerId : null),
        ':sb' => substr($subject, 0, 190),
        ':st' => 'open',
    ]);

    golder_add_support_thread_message($master, $threadId, 'customer', $senderName, $message);
    return ['ok' => true, 'thread_id' => $threadId];
}

function golder_add_support_thread_message(PDO $master, string $threadId, string $senderType, string $senderName, string $message): void
{
    golder_ensure_payment_tables($master);
    $threadId = trim($threadId);
    $message = trim($message);
    if ($threadId === '' || $message === '') {
        return;
    }
    $master->prepare(
        'INSERT INTO support_thread_messages (thread_id, sender_type, sender_name, message_text, created_at)
         VALUES (:id, :st, :sn, :msg, UTC_TIMESTAMP())'
    )->execute([
        ':id' => $threadId,
        ':st' => substr(trim($senderType), 0, 20),
        ':sn' => (trim($senderName) !== '' ? substr(trim($senderName), 0, 120) : null),
        ':msg' => $message,
    ]);
    $master->prepare(
        'UPDATE support_threads
         SET updated_at = UTC_TIMESTAMP(),
             last_message_at = UTC_TIMESTAMP(),
             closed_at = NULL,
             status = CASE WHEN status = \'closed\' THEN \'open\' ELSE status END
         WHERE id = :id'
    )->execute([':id' => $threadId]);
}

function golder_support_threads_for_portal(PDO $master, int $portalUserId, int $limit = 100): array
{
    golder_ensure_payment_tables($master);
    $limit = max(1, min(300, $limit));
    $st = $master->prepare(
        'SELECT st.*, c.username AS customer_username
         FROM support_threads st
         LEFT JOIN customers c ON c.id = st.customer_id
         WHERE st.portal_user_id = :uid
         ORDER BY st.updated_at DESC, st.created_at DESC
         LIMIT ' . (int)$limit
    );
    $st->execute([':uid' => $portalUserId]);
    return $st->fetchAll() ?: [];
}

function golder_support_thread_detail_for_portal(PDO $master, int $portalUserId, string $threadId): ?array
{
    $row = golder_support_thread_by_id($master, $threadId);
    if (!$row || (int)($row['portal_user_id'] ?? 0) !== $portalUserId) {
        return null;
    }
    $row['messages'] = golder_support_thread_messages($master, $threadId);
    return $row;
}

function golder_payment_request_detail_for_portal(PDO $master, int $portalUserId, string $requestId): ?array
{
    golder_ensure_payment_tables($master);
    $st = $master->prepare(
        'SELECT pr.*, c.username AS customer_username
         FROM payment_requests pr
         LEFT JOIN customers c ON c.id = pr.customer_id
         WHERE pr.id = :id AND pr.portal_user_id = :uid
         LIMIT 1'
    );
    $st->execute([':id' => $requestId, ':uid' => $portalUserId]);
    $row = $st->fetch();
    if (!is_array($row)) {
        return null;
    }
    $row['messages'] = golder_payment_request_messages($master, $requestId);
    return $row;
}

function golder_portal_user_password_hash(PDO $master, int $portalUserId): string
{
    $st = $master->prepare('SELECT password_hash FROM sales_portal_users WHERE id = :id LIMIT 1');
    $st->execute([':id' => $portalUserId]);
    $hash = $st->fetchColumn();
    return is_string($hash) ? trim($hash) : '';
}

function golder_send_payment_approved_email(string $toEmail, string $fullName, string $username): array
{
    $subject = 'Golder satin alma onayi';
    $text = "Merhaba {$fullName},\n\nOdeme talebiniz onaylandi.\nUygulama kullanici adiniz: {$username}\nSifre olarak portal sifrenizi kullanabilirsiniz.\n";
    $html = '<p>Merhaba ' . htmlspecialchars($fullName, ENT_QUOTES, 'UTF-8') . ',</p>'
        . '<p>Odeme talebiniz onaylandi.</p>'
        . '<p><strong>Kullanici adi:</strong> ' . htmlspecialchars($username, ENT_QUOTES, 'UTF-8') . '<br>'
        . '<strong>Sifre:</strong> Portal sifreniz ile ayni</p>';
    return golder_send_smtp_mail($toEmail, $subject, $text, $html);
}

function golder_approve_payment_request(PDO $master, string $requestId, string $message, string $adminUser): array
{
    golder_ensure_payment_tables($master);
    golder_ensure_customers_columns($master);

    $st = $master->prepare('SELECT * FROM payment_requests WHERE id = :id LIMIT 1');
    $st->execute([':id' => $requestId]);
    $row = $st->fetch();
    if (!$row) {
        return ['ok' => false, 'message' => 'Payment request not found.'];
    }
    if ((string)($row['status'] ?? '') !== 'pending') {
        return ['ok' => false, 'message' => 'Payment request is not pending.'];
    }

    $portalUserId = (int)($row['portal_user_id'] ?? 0);
    if ($portalUserId <= 0) {
        return ['ok' => false, 'message' => 'Portal user is required for commercial approval.'];
    }

    $portalPasswordHash = golder_portal_user_password_hash($master, $portalUserId);
    if ($portalPasswordHash === '') {
        return ['ok' => false, 'message' => 'Portal password hash could not be loaded.'];
    }

    $applicationUsername = strtolower(trim((string)($row['email'] ?? '')));
    if ($applicationUsername === '') {
        $applicationUsername = 'user_' . substr((string)($row['payment_code'] ?? ''), -6);
    }

    $customerId = (int)($row['customer_id'] ?? 0);
    $credentials = [
        'username' => $applicationUsername,
        'password_label' => 'portal-password',
    ];

    if ($customerId > 0) {
        $normalized = golder_normalize_plan_limits(
            (string)($row['requested_plan_type'] ?? 'A-COMPLEX'),
            (int)($row['requested_pc_devices'] ?? 1),
            (int)($row['requested_web_sessions'] ?? 0)
        );
        $resolvedExpiries = golder_resolve_plan_expiry_dates(
            (string)$normalized['plan_type'],
            (string)($row['duration_key'] ?? 'yearly')
        );
        $master->prepare(
            'UPDATE customers
             SET username = :un,
                 email = :em,
                 phone_number = :ph,
                 portal_user_id = :puid,
                 password_hash = :pwd,
                 plan_type = :p,
                 max_pc_devices = :pc,
                 max_web_sessions = :web,
                 max_devices = :pc,
                 expiry_date = :ex,
                 desktop_expiry_date = :dex,
                 sync_expiry_date = :sex,
                 license_status = :ls,
                 is_demo = 0,
                 demo_expires_at = NULL
             WHERE id = :id'
        )->execute([
            ':un' => $applicationUsername,
            ':em' => (string)($row['email'] ?? ''),
            ':ph' => (string)($row['phone'] ?? ''),
            ':puid' => $portalUserId,
            ':pwd' => $portalPasswordHash,
            ':p' => $normalized['plan_type'],
            ':pc' => $normalized['max_pc_devices'],
            ':web' => $normalized['max_web_sessions'],
            ':ex' => (($resolvedExpiries['expiry_date'] ?? '') !== '' ? $resolvedExpiries['expiry_date'] : null),
            ':dex' => (($resolvedExpiries['desktop_expiry_date'] ?? '') !== '' ? $resolvedExpiries['desktop_expiry_date'] : null),
            ':sex' => (($resolvedExpiries['sync_expiry_date'] ?? '') !== '' ? $resolvedExpiries['sync_expiry_date'] : null),
            ':ls' => 'active',
            ':id' => $customerId,
        ]);
    } else {
        $existing = golder_customer_by_portal_user_id($master, $portalUserId);
        if ($existing) {
            $customerId = (int)($existing['id'] ?? 0);
            $normalized = golder_normalize_plan_limits(
                (string)($row['requested_plan_type'] ?? 'A-COMPLEX'),
                (int)($row['requested_pc_devices'] ?? 1),
                (int)($row['requested_web_sessions'] ?? 0)
            );
            $resolvedExpiries = golder_resolve_plan_expiry_dates(
                (string)$normalized['plan_type'],
                (string)($row['duration_key'] ?? 'yearly')
            );
            $master->prepare(
                'UPDATE customers
                 SET username = :un,
                     password_hash = :pwd,
                     plan_type = :p,
                     max_pc_devices = :pc,
                     max_web_sessions = :web,
                     max_devices = :pc,
                     expiry_date = :ex,
                     desktop_expiry_date = :dex,
                     sync_expiry_date = :sex,
                     license_status = :ls,
                     is_demo = 0,
                     demo_expires_at = NULL
                 WHERE id = :id'
            )->execute([
                ':un' => $applicationUsername,
                ':pwd' => $portalPasswordHash,
                ':p' => $normalized['plan_type'],
                ':pc' => $normalized['max_pc_devices'],
                ':web' => $normalized['max_web_sessions'],
                ':ex' => (($resolvedExpiries['expiry_date'] ?? '') !== '' ? $resolvedExpiries['expiry_date'] : null),
                ':dex' => (($resolvedExpiries['desktop_expiry_date'] ?? '') !== '' ? $resolvedExpiries['desktop_expiry_date'] : null),
                ':sex' => (($resolvedExpiries['sync_expiry_date'] ?? '') !== '' ? $resolvedExpiries['sync_expiry_date'] : null),
                ':ls' => 'active',
                ':id' => $customerId,
            ]);
        } else {
            $provision = golder_provision_customer_with_tenant(
                $master,
                $applicationUsername,
                'portal-password',
                (string)($row['email'] ?? ''),
                (string)($row['phone'] ?? ''),
                (string)($row['requested_plan_type'] ?? 'A-COMPLEX'),
                (int)($row['requested_pc_devices'] ?? 1),
                (int)($row['requested_web_sessions'] ?? 0),
                'yearly',
                false,
                $portalUserId,
                $portalPasswordHash
            );
            if (empty($provision['ok'])) {
                return ['ok' => false, 'message' => (string)($provision['message'] ?? 'Provision failed.')];
            }
            $customerId = (int)($provision['customer_id'] ?? 0);
        }

        $master->prepare('UPDATE payment_requests SET customer_id = :cid WHERE id = :id')->execute([
            ':cid' => $customerId,
            ':id' => $requestId,
        ]);
    }

    $master->prepare(
        'UPDATE payment_requests
         SET customer_id = :cid,
             application_username = :un,
             status = :st,
             admin_message = :msg,
             provisioned_at = UTC_TIMESTAMP(),
             approved_at = UTC_TIMESTAMP(),
             reviewed_by = :rb,
             updated_at = UTC_TIMESTAMP()
         WHERE id = :id'
    )->execute([
        ':cid' => $customerId,
        ':un' => $applicationUsername,
        ':st' => 'approved',
        ':msg' => $message,
        ':rb' => $adminUser,
        ':id' => $requestId,
    ]);

    if (trim($message) !== '') {
        $master->prepare(
            'INSERT INTO payment_request_messages (payment_request_id, sender_type, sender_name, message_text)
             VALUES (:id, :st, :sn, :msg)'
        )->execute([
            ':id' => $requestId,
            ':st' => 'admin',
            ':sn' => $adminUser,
            ':msg' => $message,
        ]);
    }

    $systemMessage = "Hesabiniz hazir. Uygulama kullanici adiniz: {$applicationUsername}. Sifre olarak portal sifrenizi kullanabilirsiniz.";
    $master->prepare(
        'INSERT INTO payment_request_messages (payment_request_id, sender_type, sender_name, message_text)
         VALUES (:id, :st, :sn, :msg)'
    )->execute([
        ':id' => $requestId,
        ':st' => 'system',
        ':sn' => 'Golder',
        ':msg' => $systemMessage,
    ]);

    $mailResult = ['ok' => false, 'message' => 'no email'];
    $email = trim((string)($row['email'] ?? ''));
    if ($email !== '') {
        $mailResult = golder_send_payment_approved_email(
            $email,
            trim((string)($row['full_name'] ?? 'Musteri')),
            $applicationUsername
        );
    }

    $master->prepare(
        'UPDATE payment_requests
         SET mail_status = :ms,
             mail_last_error = :me
         WHERE id = :id'
    )->execute([
        ':ms' => !empty($mailResult['ok']) ? 'sent' : 'failed',
        ':me' => !empty($mailResult['ok']) ? null : substr((string)($mailResult['message'] ?? 'unknown'), 0, 1000),
        ':id' => $requestId,
    ]);

    return [
        'ok' => true,
        'customer_id' => $customerId,
        'credentials' => $credentials,
        'application_username' => $applicationUsername,
        'mail' => $mailResult,
    ];
}


