<?php
/**
 * import_semper_extras.php
 *
 * Put this file in:
 * /rooms/modules/reservations/import_semper_extras.php
 *
 * Flow:
 * 1. Upload CSV exported from Semper MSSQL.
 * 2. CSV rows are loaded into semper_extra_import_staging.
 * 3. Click "Process Pending Imports".
 * 4. Each row creates:
 *    - folio_transactions row
 *    - extra_charge_items row
 *    - optional audit 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 Extras';

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

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

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

if (!function_exists('semper_column_exists')) {
    function semper_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("
                SELECT COUNT(*)
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = :table_name
                  AND COLUMN_NAME = :column_name
            ");
            $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_current_user_id')) {
    function semper_current_user_id(): ?int
    {
        foreach (['user_id', 'ss_user_id', 'id', 'uid', 'staffid'] as $key) {
            if (!empty($_SESSION[$key]) && (int)$_SESSION[$key] > 0) {
                return (int)$_SESSION[$key];
            }
        }
        return null;
    }
}

if (!function_exists('semper_try_add_column')) {
    function semper_try_add_column(PDO $pdo, string $table, string $column, string $definition): void
    {
        if (semper_column_exists($pdo, $table, $column)) {
            return;
        }

        try {
            $pdo->exec("ALTER TABLE `" . str_replace('`', '``', $table) . "` ADD COLUMN {$definition}");
        } catch (Throwable $e) {
            // Do not block import screen because ALTER failed.
        }
    }
}

if (!function_exists('ensure_semper_import_tables')) {
    function ensure_semper_import_tables(PDO $pdo): void
    {
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS semper_extra_import_staging (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

                semper_transaction_id BIGINT UNSIGNED NULL,
                property_id INT UNSIGNED NOT NULL DEFAULT 2,
                folio_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
                order_id INT UNSIGNED NOT NULL,

                tx_date DATETIME NULL,
                tx_type VARCHAR(50) NOT NULL DEFAULT 'extra',
                description VARCHAR(255) NULL,

                quantity DECIMAL(14,4) NOT NULL DEFAULT 1.0000,
                debit DECIMAL(14,2) NOT NULL DEFAULT 0.00,
                credit DECIMAL(14,2) NOT NULL DEFAULT 0.00,

                source_table VARCHAR(100) NULL,
                source_id BIGINT UNSIGNED NULL,
                guest_id INT UNSIGNED NULL,

                imported TINYINT(1) NOT NULL DEFAULT 0,
                imported_folio_id BIGINT UNSIGNED NULL,
                imported_folio_transaction_id BIGINT UNSIGNED NULL,
                imported_at DATETIME NULL,
                error_message TEXT NULL,

                created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,

                UNIQUE KEY uq_semper_tx (semper_transaction_id),
                KEY idx_order_id (order_id),
                KEY idx_imported (imported),
                KEY idx_folio_id (folio_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");

        ensure_extra_charge_item_link_table($pdo);
        ensure_extra_charge_audit_table($pdo);
    }
}

if (!function_exists('ensure_extra_charge_item_link_table')) {
    function ensure_extra_charge_item_link_table(PDO $pdo): void
    {
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS extra_charge_items (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                property_id INT UNSIGNED NULL,
                order_id INT UNSIGNED NOT NULL,
                folio_id INT UNSIGNED NOT NULL,
                folio_transaction_id BIGINT UNSIGNED NOT NULL,
                line_no INT UNSIGNED NOT NULL DEFAULT 1,
                category_id INT UNSIGNED NULL,
                category_name VARCHAR(180) NULL,
                source_table VARCHAR(80) NOT NULL,
                source_id INT UNSIGNED NOT NULL,
                item_type VARCHAR(80) NULL,
                item_name VARCHAR(255) NOT NULL,
                item_code VARCHAR(100) NULL,
                qty DECIMAL(14,4) NOT NULL DEFAULT 1.0000,
                standard_unit_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
                unit_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
                standard_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
                line_total DECIMAL(14,2) NOT NULL DEFAULT 0.00,
                is_manual_override TINYINT(1) NOT NULL DEFAULT 0,
                description VARCHAR(255) NULL,
                source_payload_json TEXT NULL,
                created_by INT UNSIGNED NULL,
                created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_extra_items_tx (folio_transaction_id),
                INDEX idx_extra_items_order (order_id),
                INDEX idx_extra_items_folio (folio_id),
                INDEX idx_extra_items_source (source_table, source_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");
    }
}

if (!function_exists('ensure_extra_charge_audit_table')) {
    function ensure_extra_charge_audit_table(PDO $pdo): void
    {
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS extra_charge_audit_log (
                id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                order_id INT UNSIGNED NOT NULL,
                folio_id INT UNSIGNED NOT NULL,
                folio_transaction_id INT UNSIGNED NULL,
                service_id INT UNSIGNED NOT NULL DEFAULT 0,
                action_type VARCHAR(80) NOT NULL DEFAULT 'semper_import',
                standard_unit_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
                override_unit_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
                quantity DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
                standard_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
                override_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
                reason VARCHAR(255) NULL,
                payload_json TEXT NULL,
                action_by INT UNSIGNED NULL,
                action_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_extra_audit_order (order_id),
                INDEX idx_extra_audit_folio (folio_id),
                INDEX idx_extra_audit_tx (folio_transaction_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");
    }
}

if (!function_exists('next_folio_no_for_property')) {
    function next_folio_no_for_property(PDO $pdo, int $propertyId = 0): string
    {
        do {
            $prefix = 'FOL';

            if ($propertyId > 0 && semper_table_exists($pdo, 'properties')) {
                $stmt = $pdo->prepare("SELECT code FROM properties WHERE id = :id LIMIT 1");
                $stmt->execute([':id' => $propertyId]);
                $code = trim((string)($stmt->fetchColumn() ?: ''));

                if ($code !== '') {
                    $prefix = 'FOL-' . strtoupper($code);
                }
            }

            $folioNo = $prefix . '-' . date('YmdHis') . '-' . random_int(100, 999);

            $stmt = $pdo->prepare("SELECT COUNT(*) FROM folios WHERE folio_no = :folio_no");
            $stmt->execute([':folio_no' => $folioNo]);
            $exists = ((int)$stmt->fetchColumn() > 0);
        } while ($exists);

        return $folioNo;
    }
}

if (!function_exists('get_or_create_master_folio')) {
    function get_or_create_master_folio(PDO $pdo, int $orderId): int
    {
        $stmt = $pdo->prepare("
            SELECT id
            FROM folios
            WHERE order_id = :order_id
              AND LOWER(COALESCE(folio_type,'')) = 'master'
              AND COALESCE(status,1) = 1
            LIMIT 1
        ");
        $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.');
        }

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

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

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

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

        $stmt = $pdo->prepare("
            SELECT
                COALESCE(SUM(
                    CASE
                        WHEN COALESCE(is_voided,0) = 0
                             AND LOWER(COALESCE(tx_type,'')) NOT IN (
                                'payment','receipt','deposit','payment_received',
                                'folio_payment','guest_payment',
                                'payment_void','void_payment','payment_reversal',
                                'reversal','refund','payment_refund'
                             )
                             AND LOWER(COALESCE(source_table,'')) NOT IN (
                                'payment','payments','receipt','receipts'
                             )
                        THEN COALESCE(debit,0)
                        ELSE 0
                    END
                ),0) AS total_charges,

                GREATEST(0, COALESCE(SUM(
                    CASE
                        WHEN COALESCE(is_voided,0) = 0
                             AND (
                                LOWER(COALESCE(tx_type,'')) IN (
                                    'payment','receipt','deposit','payment_received',
                                    'folio_payment','guest_payment'
                                )
                                OR LOWER(COALESCE(source_table,'')) IN (
                                    'payment','payments','receipt','receipts'
                                )
                             )
                             AND COALESCE(credit,0) > 0
                        THEN COALESCE(credit,0)

                        WHEN COALESCE(is_voided,0) = 0
                             AND LOWER(COALESCE(tx_type,'')) IN (
                                'payment_void','void_payment','payment_reversal',
                                'reversal','refund','payment_refund'
                             )
                             AND COALESCE(debit,0) > 0
                        THEN -1 * COALESCE(debit,0)

                        ELSE 0
                    END
                ),0)) AS total_payments
            FROM folio_transactions
            WHERE folio_id = :folio_id
        ");
        $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_column_exists($pdo, 'folios', 'total_charges')) {
            $sets[] = 'total_charges = :total_charges';
        }
        if (semper_column_exists($pdo, 'folios', 'total_payments')) {
            $sets[] = 'total_payments = :total_payments';
        }
        if (semper_column_exists($pdo, 'folios', 'balance')) {
            $sets[] = 'balance = :balance';
        }
        if (semper_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_column_exists($pdo, 'orders', 'payment_status')) {
            return;
        }

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

        $payments = (float)($folio['total_payments'] ?? 0);
        $balance = (float)($folio['total_balance'] ?? 0);

        if ($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_parse_decimal')) {
    function semper_parse_decimal($value): float
    {
        $value = trim((string)$value);
        $value = str_replace([',', ' '], ['', ''], $value);

        if ($value === '') {
            return 0.0;
        }

        return round((float)$value, 2);
    }
}

if (!function_exists('semper_parse_date')) {
    function semper_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_clean_description')) {
    function semper_clean_description($value): string
    {
        $value = trim((string)$value);
        $value = preg_replace('/\s+/', ' ', $value);

        if ($value === '') {
            return 'Imported Semper Extra';
        }

        return mb_substr($value, 0, 255);
    }
}

if (!function_exists('semper_item_name_from_description')) {
    function semper_item_name_from_description(string $description): string
    {
        $description = trim($description);

        if ($description === '') {
            return 'Imported Semper Extra';
        }

        $parts = preg_split('/\s+x\s+/i', $description);

        if (!empty($parts[0])) {
            return mb_substr(trim($parts[0]), 0, 255);
        }

        return mb_substr($description, 0, 255);
    }
}

if (!function_exists('semper_csv_value')) {
    function semper_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_detect_csv_delimiter')) {
    function semper_detect_csv_delimiter(string $sample): string
    {
        $delimiters = ["," => 0, "\t" => 0, ";" => 0, "|" => 0];
        foreach ($delimiters as $delimiter => $count) {
            $delimiters[$delimiter] = substr_count($sample, $delimiter);
        }
        arsort($delimiters);
        $best = (string)array_key_first($delimiters);
        return $best !== '' ? $best : ',';
    }
}

if (!function_exists('semper_normalize_header')) {
    function semper_normalize_header(string $header): string
    {
        $header = trim($header);
        $header = preg_replace('/^\xEF\xBB\xBF/', '', $header); // remove UTF-8 BOM
        $header = strtolower($header);
        $header = str_replace([' ', '-', '.'], '_', $header);
        $header = preg_replace('/_+/', '_', $header);
        return trim($header, '_');
    }
}

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

        $sample = (string)file_get_contents($filePath, false, null, 0, 8192);
        $delimiter = semper_detect_csv_delimiter($sample);

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

        $headers = fgetcsv($handle, 0, $delimiter);
        if (!$headers) {
            fclose($handle);
            throw new RuntimeException('CSV header row was not found.');
        }

        $headers = array_map(static function ($h) {
            return semper_normalize_header((string)$h);
        }, $headers);

        $requiredAny = ['order_id', 'reservationid', 'reservation_id'];
        $hasOrderColumn = false;
        foreach ($requiredAny as $candidate) {
            if (in_array($candidate, $headers, true)) {
                $hasOrderColumn = true;
                break;
            }
        }

        if (!$hasOrderColumn) {
            fclose($handle);
            throw new RuntimeException('CSV header must include order_id. Your order_id must be the MySQL orders.id.');
        }

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

        $stmt = $pdo->prepare("
            INSERT INTO semper_extra_import_staging
            (
                semper_transaction_id,
                property_id,
                folio_id,
                order_id,
                tx_date,
                tx_type,
                description,
                quantity,
                debit,
                credit,
                source_table,
                source_id,
                guest_id,
                imported,
                error_message
            )
            VALUES
            (
                :semper_transaction_id,
                :property_id,
                :folio_id,
                :order_id,
                :tx_date,
                :tx_type,
                :description,
                :quantity,
                :debit,
                :credit,
                :source_table,
                :source_id,
                :guest_id,
                0,
                NULL
            )
            ON DUPLICATE KEY UPDATE
                property_id = VALUES(property_id),
                folio_id = VALUES(folio_id),
                order_id = VALUES(order_id),
                tx_date = VALUES(tx_date),
                tx_type = VALUES(tx_type),
                description = VALUES(description),
                quantity = VALUES(quantity),
                debit = VALUES(debit),
                credit = VALUES(credit),
                source_table = VALUES(source_table),
                source_id = VALUES(source_id),
                guest_id = VALUES(guest_id),
                error_message = NULL
        ");

        $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) {
                if ($header === '') {
                    continue;
                }
                $row[$header] = $data[$i] ?? '';
            }

            try {
                $semperTransactionId = (int)semper_csv_value($row, [
                    'semper_transaction_id', 'transactionid', 'transaction_id', 'sdtransactionid', 'sd_transaction_id'
                ], 0);

                $propertyId = (int)semper_csv_value($row, ['property_id', 'propertyid'], 2);
                $folioId = (int)semper_csv_value($row, ['folio_id', 'folioid'], 0);
                $orderId = (int)semper_csv_value($row, ['order_id', 'orderid', 'reservationid', 'reservation_id'], 0);

                $txDate = semper_parse_date(semper_csv_value($row, ['tx_date', 'txdate', 'datecreated', 'date_created'], ''));
                $txType = trim((string)semper_csv_value($row, ['tx_type', 'txtype'], 'extra'));
                $description = semper_clean_description(semper_csv_value($row, ['description', 'comments', 'comment'], ''));

                $quantity = semper_parse_decimal(semper_csv_value($row, ['quantity', 'qty'], 1));
                if ($quantity <= 0) {
                    $quantity = 1;
                }

                $debit = semper_parse_decimal(semper_csv_value($row, ['debit', 'amountincllevies', 'amount_incl_levies', 'amount'], 0));
                $credit = semper_parse_decimal(semper_csv_value($row, ['credit'], 0));

                $sourceTable = trim((string)semper_csv_value($row, ['source_table', 'sourcetable'], 'semper_import'));
                if ($sourceTable === '' || stripos($sourceTable, 'inventory_items_') === 0) {
                    $sourceTable = 'semper_import';
                }

                $sourceId = (int)semper_csv_value($row, ['source_id', 'sourceid', 'contactid', 'contact_id'], 0);
                $guestId = (int)semper_csv_value($row, ['guest_id', 'guestid', 'contactid', 'contact_id'], 0);

                if ($semperTransactionId <= 0) {
                    $semperTransactionId = null;
                }

                if ($orderId <= 0) {
                    throw new RuntimeException('Missing order_id. CSV order_id must be MySQL orders.id.');
                }

                if ($debit <= 0) {
                    throw new RuntimeException('Debit amount is zero or invalid.');
                }

                $stmt->execute([
                    ':semper_transaction_id' => $semperTransactionId,
                    ':property_id' => $propertyId > 0 ? $propertyId : 2,
                    ':folio_id' => $folioId,
                    ':order_id' => $orderId,
                    ':tx_date' => $txDate,
                    ':tx_type' => $txType !== '' ? $txType : 'extra',
                    ':description' => $description,
                    ':quantity' => $quantity,
                    ':debit' => $debit,
                    ':credit' => $credit,
                    ':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_validate_order_exists')) {
    function semper_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_validate_folio')) {
    function semper_validate_folio(PDO $pdo, int $orderId, int $folioId): int
    {
        if ($folioId <= 0) {
            return get_or_create_master_folio($pdo, $orderId);
        }

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

        $validFolioId = (int)($stmt->fetchColumn() ?: 0);

        if ($validFolioId <= 0) {
            return get_or_create_master_folio($pdo, $orderId);
        }

        return $validFolioId;
    }
}

if (!function_exists('semper_insert_folio_transaction')) {
    function semper_insert_folio_transaction(PDO $pdo, array $row, int $folioId): int
    {
        $hasPropertyId = semper_column_exists($pdo, 'folio_transactions', 'property_id');

        if ($hasPropertyId) {
            $sql = "
                INSERT INTO folio_transactions
                (
                    property_id,
                    folio_id,
                    order_id,
                    tx_date,
                    tx_type,
                    description,
                    debit,
                    credit,
                    source_table,
                    source_id
                )
                VALUES
                (
                    :property_id,
                    :folio_id,
                    :order_id,
                    :tx_date,
                    'extra',
                    :description,
                    :debit,
                    0,
                    'semper_import',
                    :source_id
                )
            ";
        } else {
            $sql = "
                INSERT INTO folio_transactions
                (
                    folio_id,
                    order_id,
                    tx_date,
                    tx_type,
                    description,
                    debit,
                    credit,
                    source_table,
                    source_id
                )
                VALUES
                (
                    :folio_id,
                    :order_id,
                    :tx_date,
                    'extra',
                    :description,
                    :debit,
                    0,
                    'semper_import',
                    :source_id
                )
            ";
        }

        $params = [
            ':folio_id' => $folioId,
            ':order_id' => (int)$row['order_id'],
            ':tx_date' => semper_parse_date($row['tx_date'] ?? ''),
            ':description' => semper_clean_description($row['description'] ?? ''),
            ':debit' => round((float)$row['debit'], 2),
            ':source_id' => (int)($row['semper_transaction_id'] ?: $row['source_id'] ?: $row['id']),
        ];

        if ($hasPropertyId) {
            $params[':property_id'] = (int)$row['property_id'];
        }

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);

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

if (!function_exists('semper_insert_extra_charge_item')) {
    function semper_insert_extra_charge_item(PDO $pdo, array $row, int $folioId, int $folioTransactionId): void
    {
        $qty = (float)($row['quantity'] ?? 1);
        if ($qty <= 0) {
            $qty = 1;
        }

        $amount = round((float)($row['debit'] ?? 0), 2);
        $unitPrice = round($amount / $qty, 2);

        $description = semper_clean_description($row['description'] ?? '');
        $itemName = semper_item_name_from_description($description);

        $payload = json_encode([
            'staging_id' => (int)$row['id'],
            'semper_transaction_id' => $row['semper_transaction_id'] ?? null,
            'original_source_table' => $row['source_table'] ?? null,
            'original_source_id' => $row['source_id'] ?? null,
            'guest_id' => $row['guest_id'] ?? null,
            'description' => $description,
            'quantity' => $qty,
            'debit' => $amount,
        ], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);

        $stmt = $pdo->prepare("
            INSERT INTO extra_charge_items
            (
                property_id,
                order_id,
                folio_id,
                folio_transaction_id,
                line_no,
                category_id,
                category_name,
                source_table,
                source_id,
                item_type,
                item_name,
                item_code,
                qty,
                standard_unit_price,
                unit_price,
                standard_amount,
                line_total,
                is_manual_override,
                description,
                source_payload_json,
                created_by
            )
            VALUES
            (
                :property_id,
                :order_id,
                :folio_id,
                :folio_transaction_id,
                1,
                NULL,
                'Imported Extras',
                'semper_import',
                :source_id,
                'Imported Extra',
                :item_name,
                NULL,
                :qty,
                :standard_unit_price,
                :unit_price,
                :standard_amount,
                :line_total,
                0,
                :description,
                :source_payload_json,
                :created_by
            )
        ");

        $stmt->execute([
            ':property_id' => (int)$row['property_id'],
            ':order_id' => (int)$row['order_id'],
            ':folio_id' => $folioId,
            ':folio_transaction_id' => $folioTransactionId,
            ':source_id' => (int)($row['semper_transaction_id'] ?: $row['source_id'] ?: $row['id']),
            ':item_name' => $itemName,
            ':qty' => $qty,
            ':standard_unit_price' => $unitPrice,
            ':unit_price' => $unitPrice,
            ':standard_amount' => $amount,
            ':line_total' => $amount,
            ':description' => $description,
            ':source_payload_json' => $payload,
            ':created_by' => semper_current_user_id(),
        ]);
    }
}

if (!function_exists('semper_insert_audit')) {
    function semper_insert_audit(PDO $pdo, array $row, int $folioId, int $folioTransactionId): void
    {
        try {
            $qty = (float)($row['quantity'] ?? 1);
            if ($qty <= 0) {
                $qty = 1;
            }

            $amount = round((float)($row['debit'] ?? 0), 2);
            $unitPrice = round($amount / $qty, 2);

            $payload = json_encode([
                'staging_id' => (int)$row['id'],
                'semper_transaction_id' => $row['semper_transaction_id'] ?? null,
                'source_id' => $row['source_id'] ?? null,
                'guest_id' => $row['guest_id'] ?? null,
                'description' => $row['description'] ?? '',
            ], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);

            $stmt = $pdo->prepare("
                INSERT INTO extra_charge_audit_log
                (
                    order_id,
                    folio_id,
                    folio_transaction_id,
                    service_id,
                    action_type,
                    standard_unit_price,
                    override_unit_price,
                    quantity,
                    standard_amount,
                    override_amount,
                    reason,
                    payload_json,
                    action_by
                )
                VALUES
                (
                    :order_id,
                    :folio_id,
                    :folio_transaction_id,
                    0,
                    'semper_import',
                    :standard_unit_price,
                    :override_unit_price,
                    :quantity,
                    :standard_amount,
                    :override_amount,
                    'Imported from Semper MSSQL CSV.',
                    :payload_json,
                    :action_by
                )
            ");

            $stmt->execute([
                ':order_id' => (int)$row['order_id'],
                ':folio_id' => $folioId,
                ':folio_transaction_id' => $folioTransactionId,
                ':standard_unit_price' => $unitPrice,
                ':override_unit_price' => $unitPrice,
                ':quantity' => $qty,
                ':standard_amount' => $amount,
                ':override_amount' => $amount,
                ':payload_json' => $payload,
                ':action_by' => semper_current_user_id(),
            ]);
        } catch (Throwable $e) {
            // Audit must not block the import.
        }
    }
}

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

        $stmt = $pdo->prepare("
            SELECT *
            FROM semper_extra_import_staging
            WHERE imported = 0
            ORDER BY order_id ASC, tx_date ASC, id ASC
            LIMIT {$limit}
        ");
        $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_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_validate_folio($pdo, $orderId, (int)$row['folio_id']);

                $duplicateCheckSourceId = (int)($row['semper_transaction_id'] ?: $row['source_id'] ?: 0);

                if ($duplicateCheckSourceId > 0) {
                    $dupStmt = $pdo->prepare("
                        SELECT id
                        FROM folio_transactions
                        WHERE order_id = :order_id
                          AND source_table = 'semper_import'
                          AND source_id = :source_id
                        LIMIT 1
                    ");
                    $dupStmt->execute([
                        ':order_id' => $orderId,
                        ':source_id' => $duplicateCheckSourceId,
                    ]);

                    $existingTxId = (int)($dupStmt->fetchColumn() ?: 0);

                    if ($existingTxId > 0) {
                        $pdo->prepare("
                            UPDATE semper_extra_import_staging
                            SET imported = 1,
                                imported_folio_id = :folio_id,
                                imported_folio_transaction_id = :tx_id,
                                imported_at = NOW(),
                                error_message = 'Already existed; marked as imported.'
                            WHERE id = :id
                        ")->execute([
                            ':folio_id' => $folioId,
                            ':tx_id' => $existingTxId,
                            ':id' => $stagingId,
                        ]);

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

                        $pdo->commit();

                        $processed++;
                        continue;
                    }
                }

                $folioTransactionId = semper_insert_folio_transaction($pdo, $row, $folioId);

                semper_insert_extra_charge_item($pdo, $row, $folioId, $folioTransactionId);
                semper_insert_audit($pdo, $row, $folioId, $folioTransactionId);

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

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

                $pdo->commit();

                $processed++;
            } catch (Throwable $e) {
                if ($pdo->inTransaction()) {
                    $pdo->rollBack();
                }

                $failed++;

                $pdo->prepare("
                    UPDATE semper_extra_import_staging
                    SET error_message = :error_message
                    WHERE id = :id
                ")->execute([
                    ':error_message' => $e->getMessage(),
                    ':id' => $stagingId,
                ]);

                $messages[] = 'Row #' . $stagingId . ': ' . $e->getMessage();
            }
        }

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

ensure_semper_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_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_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("
                UPDATE semper_extra_import_staging
                SET error_message = NULL
                WHERE imported = 0
                  AND error_message IS NOT NULL
            ");
            $flash[] = 'Failed pending rows reset.';
        }
    } catch (Throwable $e) {
        $errors[] = $e->getMessage();
    }
}

$summary = [
    'total' => 0,
    'pending' => 0,
    'imported' => 0,
    'failed' => 0,
];

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

$recentRows = [];
try {
    $recentRows = $pdo->query("
        SELECT *
        FROM semper_extra_import_staging
        ORDER BY id DESC
        LIMIT 100
    ")->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 Extras</h3>
            <div class="text-muted">
                Import Semper MSSQL CSV extras into PMS folios.
            </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 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">
                        Recommended CSV columns:
                        <code>semper_transaction_id</code>,
                        <code>property_id</code>,
                        <code>folio_id</code>,
                        <code>order_id</code> <strong>(MySQL orders.id)</strong>,
                        <code>tx_date</code>,
                        <code>description</code>,
                        <code>quantity</code>,
                        <code>debit</code>,
                        <code>credit</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 folio charges, links extra items, recalculates folio totals, 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 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>Description</th>
                        <th class="text-end">Qty</th>
                        <th class="text-end">Debit</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 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>
                                <?php if (!empty($row['imported_folio_id'])): ?>
                                    <?= (int)$row['imported_folio_id'] ?>
                                <?php else: ?>
                                    <?= (int)$row['folio_id'] ?>
                                <?php endif; ?>
                            </td>
                            <td><?= h($row['tx_date'] ?? '') ?></td>
                            <td><?= h($row['description'] ?? '') ?></td>
                            <td class="text-end"><?= number_format((float)($row['quantity'] ?? 0), 4) ?></td>
                            <td class="text-end"><?= number_format((float)($row['debit'] ?? 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'; ?>