<?php
/**
 * import_semper_payments.php
 *
 * Put this file in:
 * /batonka/modules/reservations/import_semper_payments.php
 *
 * Flow:
 * 1. Upload CSV exported from Semper MSSQL payments query.
 * 2. CSV rows are loaded into semper_payment_import_staging.
 * 3. Click "Process Pending Imports".
 * 4. Each row creates:
 *    - payment row
 *    - folio_transactions credit row
 *    - recalculates folio totals
 *    - syncs orders.payment_status
 */

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

require_once __DIR__ . '/../../includes/bootstrap.php';
require_login();

$pageTitle = 'Import Semper Payments';

if (!function_exists('h')) {
    function h($value): string
    {
        return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
    }
}

if (!function_exists('semper_pay_table_exists')) {
    function semper_pay_table_exists(PDO $pdo, string $tableName): bool
    {
        static $cache = [];
        if (array_key_exists($tableName, $cache)) return $cache[$tableName];

        try {
            $stmt = $pdo->prepare("\n                SELECT COUNT(*)\n                FROM INFORMATION_SCHEMA.TABLES\n                WHERE TABLE_SCHEMA = DATABASE()\n                  AND TABLE_NAME = :table_name\n            ");
            $stmt->execute([':table_name' => $tableName]);
            return $cache[$tableName] = ((int)$stmt->fetchColumn() > 0);
        } catch (Throwable $e) {
            return $cache[$tableName] = false;
        }
    }
}

if (!function_exists('semper_pay_column_exists')) {
    function semper_pay_column_exists(PDO $pdo, string $tableName, string $columnName): bool
    {
        static $cache = [];
        $key = $tableName . '.' . $columnName;
        if (array_key_exists($key, $cache)) return $cache[$key];

        try {
            $stmt = $pdo->prepare("\n                SELECT COUNT(*)\n                FROM INFORMATION_SCHEMA.COLUMNS\n                WHERE TABLE_SCHEMA = DATABASE()\n                  AND TABLE_NAME = :table_name\n                  AND COLUMN_NAME = :column_name\n            ");
            $stmt->execute([
                ':table_name' => $tableName,
                ':column_name' => $columnName,
            ]);
            return $cache[$key] = ((int)$stmt->fetchColumn() > 0);
        } catch (Throwable $e) {
            return $cache[$key] = false;
        }
    }
}

if (!function_exists('semper_pay_current_user_id')) {
    function semper_pay_current_user_id(): ?int
    {
        foreach (['user_id', 'ss_user_id', 'id', 'uid', 'staffid', 'userid'] as $key) {
            if (!empty($_SESSION[$key]) && (int)$_SESSION[$key] > 0) {
                return (int)$_SESSION[$key];
            }
        }
        return null;
    }
}

if (!function_exists('ensure_semper_payment_import_tables')) {
    function ensure_semper_payment_import_tables(PDO $pdo): void
    {
        $pdo->exec("\n            CREATE TABLE IF NOT EXISTS semper_payment_import_staging (\n                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,\n\n                semper_transaction_id BIGINT UNSIGNED NULL,\n                property_id INT UNSIGNED NOT NULL DEFAULT 2,\n                folio_id BIGINT UNSIGNED NOT NULL DEFAULT 0,\n                order_id INT UNSIGNED NOT NULL,\n\n                payment_date DATETIME NULL,\n                payment_method VARCHAR(100) NOT NULL DEFAULT 'Semper Payment',\n                description VARCHAR(255) NULL,\n\n                amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,\n                currency INT UNSIGNED NULL,\n                roe_atm DECIMAL(14,6) NOT NULL DEFAULT 1.000000,\n\n                source_table VARCHAR(100) NULL,\n                source_id BIGINT UNSIGNED NULL,\n                guest_id INT UNSIGNED NULL,\n\n                imported TINYINT(1) NOT NULL DEFAULT 0,\n                imported_payment_id BIGINT UNSIGNED NULL,\n                imported_folio_id BIGINT UNSIGNED NULL,\n                imported_folio_transaction_id BIGINT UNSIGNED NULL,\n                imported_at DATETIME NULL,\n                error_message TEXT NULL,\n                created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,\n\n                UNIQUE KEY uq_semper_payment_tx (semper_transaction_id),\n                KEY idx_order_id (order_id),\n                KEY idx_imported (imported),\n                KEY idx_folio_id (folio_id)\n            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4\n        ");
    }
}

if (!function_exists('semper_pay_parse_decimal')) {
    function semper_pay_parse_decimal($value): float
    {
        $value = trim((string)$value);
        $value = str_replace([',', ' '], ['', ''], $value);
        if ($value === '') return 0.0;
        return round(abs((float)$value), 2);
    }
}

if (!function_exists('semper_pay_parse_date')) {
    function semper_pay_parse_date($value): string
    {
        $value = trim((string)$value);
        if ($value === '') return date('Y-m-d H:i:s');

        $ts = strtotime($value);
        if (!$ts) return date('Y-m-d H:i:s');

        return date('Y-m-d H:i:s', $ts);
    }
}

if (!function_exists('semper_pay_clean_text')) {
    function semper_pay_clean_text($value, string $fallback = ''): string
    {
        $value = trim((string)$value);
        $value = preg_replace('/\s+/', ' ', $value);
        if ($value === '') $value = $fallback;
        return mb_substr($value, 0, 255);
    }
}

if (!function_exists('semper_pay_csv_value')) {
    function semper_pay_csv_value(array $row, array $names, $default = '')
    {
        foreach ($names as $name) {
            foreach ($row as $key => $value) {
                if (strtolower(trim((string)$key)) === strtolower(trim((string)$name))) {
                    return $value;
                }
            }
        }
        return $default;
    }
}

if (!function_exists('semper_pay_detect_delimiter')) {
    function semper_pay_detect_delimiter(string $line): string
    {
        $delimiters = ["," => substr_count($line, ','), "\t" => substr_count($line, "\t"), ";" => substr_count($line, ';'), "|" => substr_count($line, '|')];
        arsort($delimiters);
        $delimiter = (string)array_key_first($delimiters);
        return $delimiters[$delimiter] > 0 ? $delimiter : ',';
    }
}

if (!function_exists('get_or_create_master_folio')) {
    function get_or_create_master_folio(PDO $pdo, int $orderId): int
    {
        $stmt = $pdo->prepare("\n            SELECT id\n            FROM folios\n            WHERE order_id = :order_id\n              AND LOWER(COALESCE(folio_type,'')) = 'master'\n              AND COALESCE(status,1) = 1\n            LIMIT 1\n        ");
        $stmt->execute([':order_id' => $orderId]);
        $id = (int)($stmt->fetchColumn() ?: 0);
        if ($id > 0) return $id;

        $stmt = $pdo->prepare("SELECT property_id, order_no FROM orders WHERE id = :id LIMIT 1");
        $stmt->execute([':id' => $orderId]);
        $order = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$order) throw new RuntimeException('Booking not found while creating master folio.');

        $folioNo = 'FOL-M-' . $orderId;
        $folioName = 'Master Folio - ' . (($order['order_no'] ?? '') !== '' ? $order['order_no'] : ('Booking #' . $orderId));

        $stmt = $pdo->prepare("\n            INSERT INTO folios\n            (property_id, order_id, folio_no, folio_name, folio_type, status, total_charges, total_payments, balance)\n            VALUES\n            (:property_id, :order_id, :folio_no, :folio_name, 'master', 1, 0, 0, 0)\n        ");
        $stmt->execute([
            ':property_id' => (int)($order['property_id'] ?? 0),
            ':order_id' => $orderId,
            ':folio_no' => $folioNo,
            ':folio_name' => $folioName,
        ]);

        return (int)$pdo->lastInsertId();
    }
}

if (!function_exists('semper_pay_validate_order_exists')) {
    function semper_pay_validate_order_exists(PDO $pdo, int $orderId): array
    {
        $stmt = $pdo->prepare("SELECT id, property_id, order_no FROM orders WHERE id = :id LIMIT 1");
        $stmt->execute([':id' => $orderId]);
        $order = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$order) throw new RuntimeException('Booking/order not found: ' . $orderId);
        return $order;
    }
}

if (!function_exists('semper_pay_validate_folio')) {
    function semper_pay_validate_folio(PDO $pdo, int $orderId, int $folioId): int
    {
        if ($folioId <= 0) return get_or_create_master_folio($pdo, $orderId);

        $stmt = $pdo->prepare("\n            SELECT id\n            FROM folios\n            WHERE id = :folio_id\n              AND order_id = :order_id\n              AND COALESCE(status,1) = 1\n            LIMIT 1\n        ");
        $stmt->execute([':folio_id' => $folioId, ':order_id' => $orderId]);
        $valid = (int)($stmt->fetchColumn() ?: 0);

        return $valid > 0 ? $valid : get_or_create_master_folio($pdo, $orderId);
    }
}

if (!function_exists('recalculate_folio_totals')) {
    function recalculate_folio_totals(PDO $pdo, int $folioId): void
    {
        if ($folioId <= 0) return;

        $stmt = $pdo->prepare("\n            SELECT\n                COALESCE(SUM(\n                    CASE\n                        WHEN COALESCE(is_voided,0) = 0\n                             AND LOWER(COALESCE(tx_type,'')) NOT IN (\n                                'payment','receipt','deposit','payment_received',\n                                'folio_payment','guest_payment',\n                                'payment_void','void_payment','payment_reversal',\n                                'reversal','refund','payment_refund'\n                             )\n                             AND LOWER(COALESCE(source_table,'')) NOT IN (\n                                'payment','payments','receipt','receipts'\n                             )\n                        THEN COALESCE(debit,0)\n                        ELSE 0\n                    END\n                ),0) AS total_charges,\n\n                GREATEST(0, COALESCE(SUM(\n                    CASE\n                        WHEN COALESCE(is_voided,0) = 0\n                             AND (\n                                LOWER(COALESCE(tx_type,'')) IN (\n                                    'payment','receipt','deposit','payment_received',\n                                    'folio_payment','guest_payment'\n                                )\n                                OR LOWER(COALESCE(source_table,'')) IN (\n                                    'payment','payments','receipt','receipts'\n                                )\n                             )\n                             AND COALESCE(credit,0) > 0\n                        THEN COALESCE(credit,0)\n\n                        WHEN COALESCE(is_voided,0) = 0\n                             AND (\n                                LOWER(COALESCE(tx_type,'')) IN (\n                                    'payment_void','void_payment','payment_reversal',\n                                    'reversal','refund','payment_refund'\n                                )\n                                OR LOWER(COALESCE(description,'')) LIKE '%void%'\n                                OR LOWER(COALESCE(description,'')) LIKE '%reversal%'\n                                OR LOWER(COALESCE(description,'')) LIKE '%refund%'\n                             )\n                             AND COALESCE(debit,0) > 0\n                        THEN -1 * COALESCE(debit,0)\n\n                        WHEN COALESCE(is_voided,0) = 0\n                             AND (\n                                LOWER(COALESCE(tx_type,'')) IN (\n                                    'payment_void','void_payment','payment_reversal',\n                                    'reversal','refund','payment_refund'\n                                )\n                                OR LOWER(COALESCE(description,'')) LIKE '%void%'\n                                OR LOWER(COALESCE(description,'')) LIKE '%reversal%'\n                                OR LOWER(COALESCE(description,'')) LIKE '%refund%'\n                             )\n                             AND COALESCE(credit,0) < 0\n                        THEN COALESCE(credit,0)\n\n                        ELSE 0\n                    END\n                ),0)) AS total_payments\n            FROM folio_transactions\n            WHERE folio_id = :folio_id\n        ");
        $stmt->execute([':folio_id' => $folioId]);
        $totals = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

        $charges = round((float)($totals['total_charges'] ?? 0), 2);
        $payments = round((float)($totals['total_payments'] ?? 0), 2);
        $balance = round($charges - $payments, 2);

        $sets = [];
        $params = [':id' => $folioId, ':total_charges' => $charges, ':total_payments' => $payments, ':balance' => $balance];

        if (semper_pay_column_exists($pdo, 'folios', 'total_charges')) $sets[] = 'total_charges = :total_charges';
        if (semper_pay_column_exists($pdo, 'folios', 'total_payments')) $sets[] = 'total_payments = :total_payments';
        if (semper_pay_column_exists($pdo, 'folios', 'balance')) $sets[] = 'balance = :balance';
        if (semper_pay_column_exists($pdo, 'folios', 'updated_at')) $sets[] = 'updated_at = NOW()';

        if ($sets) {
            $pdo->prepare("UPDATE folios SET " . implode(', ', $sets) . " WHERE id = :id")->execute($params);
        }
    }
}

if (!function_exists('sync_order_payment_status')) {
    function sync_order_payment_status(PDO $pdo, int $orderId): void
    {
        if ($orderId <= 0 || !semper_pay_column_exists($pdo, 'orders', 'payment_status')) return;

        $stmt = $pdo->prepare("\n            SELECT\n                COALESCE(SUM(COALESCE(total_charges,0)),0) AS total_charges,\n                COALESCE(SUM(COALESCE(total_payments,0)),0) AS total_payments,\n                COALESCE(SUM(COALESCE(balance,0)),0) AS total_balance\n            FROM folios\n            WHERE order_id = :order_id\n        ");
        $stmt->execute([':order_id' => $orderId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

        $charges = (float)($row['total_charges'] ?? 0);
        $payments = (float)($row['total_payments'] ?? 0);
        $balance = (float)($row['total_balance'] ?? 0);

        if ($charges <= 0.00001 || $payments <= 0.00001) $status = 0;
        elseif ($balance <= 0.00001) $status = 2;
        else $status = 1;

        $stmt = $pdo->prepare("UPDATE orders SET payment_status = :payment_status WHERE id = :id");
        $stmt->execute([':payment_status' => $status, ':id' => $orderId]);
    }
}

if (!function_exists('semper_pay_load_csv_to_staging')) {
    function semper_pay_load_csv_to_staging(PDO $pdo, string $filePath): array
    {
        if (!is_readable($filePath)) throw new RuntimeException('CSV file could not be read.');

        $handle = fopen($filePath, 'r');
        if (!$handle) throw new RuntimeException('CSV file could not be opened.');

        $firstLine = fgets($handle);
        if ($firstLine === false) {
            fclose($handle);
            throw new RuntimeException('CSV file is empty.');
        }

        $delimiter = semper_pay_detect_delimiter($firstLine);
        $headers = str_getcsv($firstLine, $delimiter);
        $headers = array_map(static fn($h) => trim((string)$h), $headers);

        $headerCheck = array_map(static fn($h) => strtolower(trim((string)$h)), $headers);
        if (!in_array('order_id', $headerCheck, true) || !in_array('amount', $headerCheck, true)) {
            fclose($handle);
            throw new RuntimeException('CSV header must include at least order_id and amount.');
        }

        $stmt = $pdo->prepare("\n            INSERT INTO semper_payment_import_staging\n            (\n                semper_transaction_id, property_id, folio_id, order_id, payment_date, payment_method, description,\n                amount, currency, roe_atm, source_table, source_id, guest_id, imported, error_message\n            )\n            VALUES\n            (\n                :semper_transaction_id, :property_id, :folio_id, :order_id, :payment_date, :payment_method, :description,\n                :amount, :currency, :roe_atm, :source_table, :source_id, :guest_id, 0, NULL\n            )\n            ON DUPLICATE KEY UPDATE\n                property_id = VALUES(property_id),\n                folio_id = VALUES(folio_id),\n                order_id = VALUES(order_id),\n                payment_date = VALUES(payment_date),\n                payment_method = VALUES(payment_method),\n                description = VALUES(description),\n                amount = VALUES(amount),\n                currency = VALUES(currency),\n                roe_atm = VALUES(roe_atm),\n                source_table = VALUES(source_table),\n                source_id = VALUES(source_id),\n                guest_id = VALUES(guest_id),\n                error_message = NULL\n        ");

        $inserted = 0;
        $skipped = 0;
        $errors = [];
        $lineNo = 1;

        while (($data = fgetcsv($handle, 0, $delimiter)) !== false) {
            $lineNo++;
            if (count(array_filter($data, static fn($v) => trim((string)$v) !== '')) === 0) continue;

            $row = [];
            foreach ($headers as $i => $header) $row[$header] = $data[$i] ?? '';

            try {
                $semperTransactionId = (int)semper_pay_csv_value($row, ['semper_transaction_id', 'TransactionID', 'transaction_id'], 0);
                $propertyId = (int)semper_pay_csv_value($row, ['property_id'], 2);
                $folioId = (int)semper_pay_csv_value($row, ['folio_id'], 0);
                $orderId = (int)semper_pay_csv_value($row, ['order_id', 'ReservationID', 'reservation_id'], 0);
                $paymentDate = semper_pay_parse_date(semper_pay_csv_value($row, ['payment_date', 'tx_date', 'DateCreated', 'date_created'], ''));
                $paymentMethod = semper_pay_clean_text(semper_pay_csv_value($row, ['payment_method', 'method'], 'Semper Payment'), 'Semper Payment');
                $description = semper_pay_clean_text(semper_pay_csv_value($row, ['description', 'Comments', 'comments'], ''), 'Imported Semper Payment');
                $amount = semper_pay_parse_decimal(semper_pay_csv_value($row, ['amount', 'debit', 'AmountInclLevies', 'Amount'], 0));
                $currency = (int)semper_pay_csv_value($row, ['currency', 'currency_id'], 1);
                $roeAtm = (float)semper_pay_csv_value($row, ['roe_atm', 'roe', 'ROE'], 1);
                if ($roeAtm <= 0) $roeAtm = 1;

                $sourceTable = trim((string)semper_pay_csv_value($row, ['source_table'], 'semper_payment_import'));
                if ($sourceTable === '' || stripos($sourceTable, 'inventory_items_') === 0) $sourceTable = 'semper_payment_import';

                $sourceId = (int)semper_pay_csv_value($row, ['source_id', 'ContactID', 'contact_id'], 0);
                $guestId = (int)semper_pay_csv_value($row, ['guest_id', 'ContactID', 'contact_id'], 0);

                if ($semperTransactionId <= 0) $semperTransactionId = null;
                if ($orderId <= 0) throw new RuntimeException('Missing order_id.');
                if ($amount <= 0) throw new RuntimeException('Payment amount is zero or invalid.');

                $stmt->execute([
                    ':semper_transaction_id' => $semperTransactionId,
                    ':property_id' => $propertyId > 0 ? $propertyId : 2,
                    ':folio_id' => $folioId,
                    ':order_id' => $orderId,
                    ':payment_date' => $paymentDate,
                    ':payment_method' => $paymentMethod,
                    ':description' => $description,
                    ':amount' => $amount,
                    ':currency' => $currency > 0 ? $currency : null,
                    ':roe_atm' => $roeAtm,
                    ':source_table' => $sourceTable,
                    ':source_id' => $sourceId,
                    ':guest_id' => $guestId,
                ]);

                $inserted++;
            } catch (Throwable $e) {
                $skipped++;
                $errors[] = 'Line ' . $lineNo . ': ' . $e->getMessage();
            }
        }

        fclose($handle);
        return ['inserted' => $inserted, 'skipped' => $skipped, 'errors' => $errors];
    }
}

if (!function_exists('semper_pay_insert_payment')) {
    function semper_pay_insert_payment(PDO $pdo, array $row, int $folioId): int
    {
        $propertyId = (int)$row['property_id'];
        $orderId = (int)$row['order_id'];
        $paymentDate = semper_pay_parse_date($row['payment_date'] ?? '');
        $dateOnly = date('Y-m-d', strtotime($paymentDate));
        $amount = round((float)$row['amount'], 2);
        $method = semper_pay_clean_text($row['payment_method'] ?? 'Semper Payment', 'Semper Payment');
        $desc = semper_pay_clean_text($row['description'] ?? 'Imported Semper Payment', 'Imported Semper Payment');
        $sourceId = (int)($row['semper_transaction_id'] ?: $row['source_id'] ?: $row['id']);

        $cols = ['property_id','order_id','date_time','amount','invoice','is_main_amount','payment_method','added_date','id_upload','currency','roe_atm'];
        $vals = [':property_id',':order_id',':date_time',':amount',':invoice',':is_main_amount',':payment_method',':added_date',':id_upload',':currency',':roe_atm'];
        $params = [
            ':property_id' => $propertyId,
            ':order_id' => $orderId,
            ':date_time' => $paymentDate,
            ':amount' => $amount,
            ':invoice' => 0,
            ':is_main_amount' => 1,
            ':payment_method' => $method,
            ':added_date' => $dateOnly,
            ':id_upload' => 'Imported from Semper Tx #' . $sourceId . ' | ' . $desc,
            ':currency' => !empty($row['currency']) ? (int)$row['currency'] : null,
            ':roe_atm' => (float)($row['roe_atm'] ?? 1),
        ];

        if (semper_pay_column_exists($pdo, 'payment', 'bank_account_id')) {
            $cols[] = 'bank_account_id';
            $vals[] = ':bank_account_id';
            $params[':bank_account_id'] = null;
        }
        if (semper_pay_column_exists($pdo, 'payment', 'pos_machine_id')) {
            $cols[] = 'pos_machine_id';
            $vals[] = ':pos_machine_id';
            $params[':pos_machine_id'] = null;
        }

        $sql = 'INSERT INTO payment (' . implode(',', $cols) . ') VALUES (' . implode(',', $vals) . ')';
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);

        return (int)$pdo->lastInsertId();
    }
}

if (!function_exists('semper_pay_post_payment_to_folio')) {
    function semper_pay_post_payment_to_folio(PDO $pdo, int $paymentId, int $folioId, ?int $semperTransactionId = null): int
    {
        $stmt = $pdo->prepare("\n            SELECT p.*\n            FROM payment p\n            WHERE p.id = :id\n            LIMIT 1\n        ");
        $stmt->execute([':id' => $paymentId]);
        $payment = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$payment) throw new RuntimeException('Payment not found after insert.');

        $method = trim((string)($payment['payment_method'] ?? 'Payment'));
        $roeText = '';
        if (!empty($payment['roe_atm']) && (float)$payment['roe_atm'] > 0) {
            $roeText = ' | ROE ' . number_format((float)$payment['roe_atm'], 5, '.', '');
        }
        $desc = 'Payment received - ' . $method;
        if (!empty($payment['id_upload'])) $desc .= ' | ' . trim((string)$payment['id_upload']);
        $desc .= $roeText;

        $txDate = !empty($payment['date_time'])
            ? (string)$payment['date_time']
            : (!empty($payment['added_date']) ? $payment['added_date'] . ' 00:00:00' : date('Y-m-d H:i:s'));

        $sourceId = $paymentId;

        $stmt = $pdo->prepare("\n            INSERT INTO folio_transactions\n            (property_id, folio_id, order_id, tx_date, tx_type, description, debit, credit, source_table, source_id)\n            VALUES\n            (:property_id, :folio_id, :order_id, :tx_date, 'payment', :description, 0, :credit, 'payment', :source_id)\n        ");
        $stmt->execute([
            ':property_id' => (int)($payment['property_id'] ?? 0),
            ':folio_id' => $folioId,
            ':order_id' => (int)$payment['order_id'],
            ':tx_date' => $txDate,
            ':description' => $desc,
            ':credit' => round((float)$payment['amount'], 2),
            ':source_id' => $sourceId,
        ]);

        return (int)$pdo->lastInsertId();
    }
}

if (!function_exists('semper_pay_process_pending_imports')) {
    function semper_pay_process_pending_imports(PDO $pdo, int $limit = 200): array
    {
        $limit = max(1, min(20000, $limit));

        $stmt = $pdo->prepare("\n            SELECT *\n            FROM semper_payment_import_staging\n            WHERE imported = 0\n            ORDER BY order_id ASC, payment_date ASC, id ASC\n            LIMIT {$limit}\n        ");
        $stmt->execute();
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $processed = 0;
        $failed = 0;
        $messages = [];

        foreach ($rows as $row) {
            $stagingId = (int)$row['id'];

            try {
                $pdo->beginTransaction();

                $orderId = (int)$row['order_id'];
                $order = semper_pay_validate_order_exists($pdo, $orderId);

                $propertyId = (int)($row['property_id'] ?? 0);
                if ($propertyId <= 0) $propertyId = (int)($order['property_id'] ?? 0);
                if ($propertyId <= 0) $propertyId = 2;
                $row['property_id'] = $propertyId;

                $folioId = semper_pay_validate_folio($pdo, $orderId, (int)$row['folio_id']);
                $duplicateSourceId = (int)($row['semper_transaction_id'] ?: $row['source_id'] ?: 0);

                if ($duplicateSourceId > 0) {
                    $dup = $pdo->prepare("\n                        SELECT s.imported_payment_id, s.imported_folio_transaction_id\n                        FROM semper_payment_import_staging s\n                        WHERE s.id <> :id\n                          AND s.semper_transaction_id = :semper_transaction_id\n                          AND s.imported = 1\n                        LIMIT 1\n                    ");
                    $dup->execute([':id' => $stagingId, ':semper_transaction_id' => $duplicateSourceId]);
                    $existing = $dup->fetch(PDO::FETCH_ASSOC);
                    if ($existing) {
                        $pdo->prepare("\n                            UPDATE semper_payment_import_staging\n                            SET imported = 1,\n                                imported_payment_id = :payment_id,\n                                imported_folio_id = :folio_id,\n                                imported_folio_transaction_id = :tx_id,\n                                imported_at = NOW(),\n                                error_message = 'Already imported; marked as imported.'\n                            WHERE id = :id\n                        ")->execute([
                            ':payment_id' => (int)($existing['imported_payment_id'] ?? 0),
                            ':folio_id' => $folioId,
                            ':tx_id' => (int)($existing['imported_folio_transaction_id'] ?? 0),
                            ':id' => $stagingId,
                        ]);
                        recalculate_folio_totals($pdo, $folioId);
                        sync_order_payment_status($pdo, $orderId);
                        $pdo->commit();
                        $processed++;
                        continue;
                    }
                }

                $paymentId = semper_pay_insert_payment($pdo, $row, $folioId);
                $folioTransactionId = semper_pay_post_payment_to_folio($pdo, $paymentId, $folioId, !empty($row['semper_transaction_id']) ? (int)$row['semper_transaction_id'] : null);

                recalculate_folio_totals($pdo, $folioId);
                sync_order_payment_status($pdo, $orderId);

                $pdo->prepare("\n                    UPDATE semper_payment_import_staging\n                    SET imported = 1,\n                        imported_payment_id = :payment_id,\n                        imported_folio_id = :folio_id,\n                        imported_folio_transaction_id = :folio_transaction_id,\n                        imported_at = NOW(),\n                        error_message = NULL\n                    WHERE id = :id\n                ")->execute([
                    ':payment_id' => $paymentId,
                    ':folio_id' => $folioId,
                    ':folio_transaction_id' => $folioTransactionId,
                    ':id' => $stagingId,
                ]);

                $pdo->commit();
                $processed++;
            } catch (Throwable $e) {
                if ($pdo->inTransaction()) $pdo->rollBack();
                $failed++;
                $pdo->prepare("\n                    UPDATE semper_payment_import_staging\n                    SET error_message = :error_message\n                    WHERE id = :id\n                ")->execute([':error_message' => $e->getMessage(), ':id' => $stagingId]);
                $messages[] = 'Row #' . $stagingId . ': ' . $e->getMessage();
            }
        }

        return ['processed' => $processed, 'failed' => $failed, 'messages' => $messages];
    }
}

ensure_semper_payment_import_tables($pdo);

$flash = [];
$errors = [];

if (($_SERVER['REQUEST_METHOD'] ?? 'GET') === 'POST') {
    $action = (string)($_POST['action'] ?? '');

    try {
        if ($action === 'upload_csv') {
            if (empty($_FILES['csv_file']['tmp_name'])) throw new RuntimeException('Please select a CSV file.');
            $result = semper_pay_load_csv_to_staging($pdo, $_FILES['csv_file']['tmp_name']);
            $flash[] = 'CSV loaded. Rows staged/updated: ' . (int)$result['inserted'] . '. Skipped: ' . (int)$result['skipped'] . '.';
            foreach (($result['errors'] ?? []) as $err) $errors[] = $err;
        }

        if ($action === 'process_pending') {
            $limit = (int)($_POST['limit'] ?? 200);
            $result = semper_pay_process_pending_imports($pdo, $limit);
            $flash[] = 'Processed: ' . (int)$result['processed'] . '. Failed: ' . (int)$result['failed'] . '.';
            foreach (($result['messages'] ?? []) as $msg) $errors[] = $msg;
        }

        if ($action === 'reset_failed') {
            $pdo->exec("\n                UPDATE semper_payment_import_staging\n                SET error_message = NULL\n                WHERE imported = 0\n                  AND error_message IS NOT NULL\n            ");
            $flash[] = 'Failed pending rows reset.';
        }
    } catch (Throwable $e) {
        $errors[] = $e->getMessage();
    }
}

$summary = ['total' => 0, 'pending' => 0, 'imported' => 0, 'failed' => 0];
try {
    $summary = $pdo->query("\n        SELECT\n            COUNT(*) AS total,\n            SUM(CASE WHEN imported = 0 THEN 1 ELSE 0 END) AS pending,\n            SUM(CASE WHEN imported = 1 THEN 1 ELSE 0 END) AS imported,\n            SUM(CASE WHEN imported = 0 AND error_message IS NOT NULL AND error_message <> '' THEN 1 ELSE 0 END) AS failed\n        FROM semper_payment_import_staging\n    ")->fetch(PDO::FETCH_ASSOC) ?: $summary;
} catch (Throwable $e) {
    $errors[] = $e->getMessage();
}

$recentRows = [];
try {
    $recentRows = $pdo->query("\n        SELECT *\n        FROM semper_payment_import_staging\n        ORDER BY id DESC\n        LIMIT 100\n    ")->fetchAll(PDO::FETCH_ASSOC);
} catch (Throwable $e) {
    $recentRows = [];
}

include __DIR__ . '/../../includes/header.php';
?>

<div class="container-fluid py-4">
    <div class="d-flex align-items-center justify-content-between mb-3">
        <div>
            <h3 class="mb-1">Import Semper Payments</h3>
            <div class="text-muted">Import Semper MSSQL payment CSV into PMS payment and folio tables.</div>
        </div>
    </div>

    <?php foreach ($flash as $msg): ?>
        <div class="alert alert-success"><?= h($msg) ?></div>
    <?php endforeach; ?>

    <?php foreach ($errors as $msg): ?>
        <div class="alert alert-danger"><?= h($msg) ?></div>
    <?php endforeach; ?>

    <div class="row g-3 mb-4">
        <div class="col-md-3"><div class="card shadow-sm border-0"><div class="card-body"><div class="text-muted small">Total Staged</div><div class="fs-3 fw-bold"><?= number_format((int)($summary['total'] ?? 0)) ?></div></div></div></div>
        <div class="col-md-3"><div class="card shadow-sm border-0"><div class="card-body"><div class="text-muted small">Pending</div><div class="fs-3 fw-bold"><?= number_format((int)($summary['pending'] ?? 0)) ?></div></div></div></div>
        <div class="col-md-3"><div class="card shadow-sm border-0"><div class="card-body"><div class="text-muted small">Imported</div><div class="fs-3 fw-bold"><?= number_format((int)($summary['imported'] ?? 0)) ?></div></div></div></div>
        <div class="col-md-3"><div class="card shadow-sm border-0"><div class="card-body"><div class="text-muted small">Failed</div><div class="fs-3 fw-bold text-danger"><?= number_format((int)($summary['failed'] ?? 0)) ?></div></div></div></div>
    </div>

    <div class="row g-3 mb-4">
        <div class="col-lg-6">
            <div class="card shadow-sm border-0">
                <div class="card-header bg-white"><strong>1. Upload Semper Payment CSV</strong></div>
                <div class="card-body">
                    <form method="post" enctype="multipart/form-data">
                        <input type="hidden" name="action" value="upload_csv">
                        <div class="mb-3">
                            <label class="form-label">CSV File</label>
                            <input type="file" name="csv_file" class="form-control" accept=".csv,text/csv" required>
                        </div>
                        <button type="submit" class="btn btn-primary">Upload to Staging</button>
                    </form>

                    <hr>
                    <div class="small text-muted">
                        Required CSV columns:
                        <code>semper_transaction_id</code>,
                        <code>property_id</code>,
                        <code>folio_id</code>,
                        <code>order_id</code> MySQL orders.id,
                        <code>payment_date</code>,
                        <code>payment_method</code>,
                        <code>description</code>,
                        <code>amount</code>,
                        <code>currency</code>,
                        <code>roe_atm</code>,
                        <code>source_table</code>,
                        <code>source_id</code>,
                        <code>guest_id</code>.
                    </div>
                </div>
            </div>
        </div>

        <div class="col-lg-6">
            <div class="card shadow-sm border-0">
                <div class="card-header bg-white"><strong>2. Process Pending Imports</strong></div>
                <div class="card-body">
                    <form method="post" class="mb-3">
                        <input type="hidden" name="action" value="process_pending">
                        <div class="mb-3">
                            <label class="form-label">Rows to process now</label>
                            <input type="number" name="limit" class="form-control" value="20000" min="1" max="20000">
                        </div>
                        <button type="submit" class="btn btn-success">Process Pending Imports</button>
                    </form>

                    <form method="post">
                        <input type="hidden" name="action" value="reset_failed">
                        <button type="submit" class="btn btn-outline-secondary">Clear Failed Error Messages</button>
                    </form>

                    <div class="small text-muted mt-3">
                        Processing inserts into <code>payment</code>, posts a credit to <code>folio_transactions</code>, recalculates folios, and updates booking payment status.
                    </div>
                </div>
            </div>
        </div>
    </div>

    <div class="card shadow-sm border-0">
        <div class="card-header bg-white"><strong>Recent Payment Staging Rows</strong></div>
        <div class="table-responsive">
            <table class="table table-sm table-striped align-middle mb-0">
                <thead>
                    <tr>
                        <th>ID</th>
                        <th>Semper Tx</th>
                        <th>Order</th>
                        <th>Folio</th>
                        <th>Date</th>
                        <th>Method</th>
                        <th>Description</th>
                        <th class="text-end">Amount</th>
                        <th>Status</th>
                        <th>Error</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if (!$recentRows): ?>
                        <tr><td colspan="10" class="text-center text-muted py-4">No payment import rows yet.</td></tr>
                    <?php endif; ?>

                    <?php foreach ($recentRows as $row): ?>
                        <tr>
                            <td><?= (int)$row['id'] ?></td>
                            <td><?= h($row['semper_transaction_id'] ?? '') ?></td>
                            <td><?= (int)$row['order_id'] ?></td>
                            <td><?= !empty($row['imported_folio_id']) ? (int)$row['imported_folio_id'] : (int)$row['folio_id'] ?></td>
                            <td><?= h($row['payment_date'] ?? '') ?></td>
                            <td><?= h($row['payment_method'] ?? '') ?></td>
                            <td><?= h($row['description'] ?? '') ?></td>
                            <td class="text-end"><?= number_format((float)($row['amount'] ?? 0), 2) ?></td>
                            <td>
                                <?php if ((int)($row['imported'] ?? 0) === 1): ?>
                                    <span class="badge bg-success">Imported</span>
                                <?php elseif (!empty($row['error_message'])): ?>
                                    <span class="badge bg-danger">Failed</span>
                                <?php else: ?>
                                    <span class="badge bg-warning text-dark">Pending</span>
                                <?php endif; ?>
                            </td>
                            <td class="small text-danger"><?= h($row['error_message'] ?? '') ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>

<?php include __DIR__ . '/../../includes/footer.php'; ?>
