<?php
require_once __DIR__ . '/SystemSettings.php';

class MraEisOnboarding {
    /**
     * Hard ceiling on how many pieces syncWarehouseStock() will create for a single barcode in
     * one run. Guards against a field-name mismatch or malformed EIS response getting misread as
     * a huge quantity and mass-inserting bogus items - failing loudly (flagged_capped) is far
     * safer than silently creating thousands of rows.
     */
    const MAX_QUANTITY_PER_BARCODE = 10000;

    private $conn;

    public function __construct(PDO $conn) {
        $this->conn = $conn;
        $this->ensureTable();
    }

    public function ensureTable() {
        $this->conn->exec("
            CREATE TABLE IF NOT EXISTS mra_eis_terminals (
                id INT AUTO_INCREMENT PRIMARY KEY,
                terminal_label VARCHAR(120) NOT NULL,
                branch_id INT NOT NULL,
                terminal_activation_code VARCHAR(50) NOT NULL,
                taxpayer_tin VARCHAR(80) DEFAULT NULL,
                site_id VARCHAR(80) DEFAULT NULL,
                global_config_version INT NOT NULL DEFAULT 1,
                taxpayer_config_version INT NOT NULL DEFAULT 1,
                terminal_config_version INT NOT NULL DEFAULT 1,
                environment ENUM('sandbox','production') NOT NULL DEFAULT 'sandbox',
                api_base_url VARCHAR(255) NOT NULL,
                os_name VARCHAR(50) NOT NULL,
                os_version VARCHAR(50) NOT NULL,
                os_build VARCHAR(50) DEFAULT NULL,
                mac_address VARCHAR(17) NOT NULL,
                pos_product_id VARCHAR(50) NOT NULL,
                pos_product_version VARCHAR(50) NOT NULL,
                status ENUM('draft','activation_pending','activated','failed') NOT NULL DEFAULT 'draft',
                mra_terminal_id VARCHAR(50) DEFAULT NULL,
                jwt_token TEXT NULL,
                secret_key VARCHAR(255) DEFAULT NULL,
                activation_response MEDIUMTEXT NULL,
                confirmation_response MEDIUMTEXT NULL,
                last_error TEXT NULL,
                activated_at DATETIME DEFAULT NULL,
                confirmed_at DATETIME DEFAULT NULL,
                created_by INT DEFAULT NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                KEY idx_mra_eis_branch_id (branch_id),
                KEY idx_mra_eis_status (status),
                KEY idx_mra_eis_environment (environment)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ");

        $this->conn->exec("
            CREATE TABLE IF NOT EXISTS mra_eis_site_products (
                id INT AUTO_INCREMENT PRIMARY KEY,
                terminal_local_id INT NOT NULL,
                tin VARCHAR(80) NOT NULL,
                site_id VARCHAR(80) NOT NULL,
                product_code VARCHAR(120) NOT NULL,
                product_name VARCHAR(255) NOT NULL,
                description TEXT NULL,
                quantity DECIMAL(18, 4) NOT NULL DEFAULT 0,
                unit_of_measure VARCHAR(80) DEFAULT NULL,
                price DECIMAL(18, 4) NOT NULL DEFAULT 0,
                product_expiry_date DATETIME DEFAULT NULL,
                minimum_stock_level DECIMAL(18, 4) NOT NULL DEFAULT 0,
                tax_rate_id VARCHAR(50) DEFAULT NULL,
                levy_rate_id VARCHAR(50) DEFAULT NULL,
                is_product TINYINT(1) NOT NULL DEFAULT 1,
                raw_payload MEDIUMTEXT NULL,
                synced_at DATETIME NOT NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY uniq_mra_site_product (terminal_local_id, site_id, product_code),
                KEY idx_mra_site_products_terminal (terminal_local_id),
                KEY idx_mra_site_products_site (site_id),
                KEY idx_mra_site_products_product_code (product_code)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ");

        $this->conn->exec("
            CREATE TABLE IF NOT EXISTS mra_eis_item_product_map (
                id INT AUTO_INCREMENT PRIMARY KEY,
                item_id INT NOT NULL,
                unique_code VARCHAR(120) NOT NULL,
                terminal_local_id INT DEFAULT NULL,
                site_product_id INT DEFAULT NULL,
                product_code VARCHAR(120) NOT NULL,
                product_name VARCHAR(255) NOT NULL,
                tax_rate_id VARCHAR(50) DEFAULT NULL,
                levy_rate_id VARCHAR(50) DEFAULT NULL,
                mapped_by INT DEFAULT NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY uniq_mra_eis_item_map_item (item_id),
                KEY idx_mra_eis_item_map_unique_code (unique_code),
                KEY idx_mra_eis_item_map_product_code (product_code),
                KEY idx_mra_eis_item_map_terminal (terminal_local_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ");

        $this->conn->exec("
            CREATE TABLE IF NOT EXISTS mra_eis_product_code_map (
                id INT AUTO_INCREMENT PRIMARY KEY,
                ims_product_code VARCHAR(120) NOT NULL,
                ims_item_name VARCHAR(255) NOT NULL,
                terminal_local_id INT DEFAULT NULL,
                site_product_id INT DEFAULT NULL,
                eis_product_code VARCHAR(120) NOT NULL,
                eis_product_name VARCHAR(255) NOT NULL,
                selling_price DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
                tax_rate_id VARCHAR(50) DEFAULT NULL,
                levy_rate_id VARCHAR(50) DEFAULT NULL,
                mapped_by INT DEFAULT NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY uniq_product_code_terminal_price (ims_product_code, terminal_local_id, selling_price),
                KEY idx_mra_eis_product_code_terminal (terminal_local_id),
                KEY idx_mra_eis_product_code_eis_product (eis_product_code)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ");

        $this->conn->exec("
            CREATE TABLE IF NOT EXISTS mra_eis_sales_queue (
                id INT AUTO_INCREMENT PRIMARY KEY,
                transaction_id VARCHAR(120) NOT NULL,
                branch_id INT NOT NULL,
                terminal_local_id INT DEFAULT NULL,
                customer_name VARCHAR(255) DEFAULT NULL,
                customer_phone VARCHAR(100) DEFAULT NULL,
                sold_at DATETIME DEFAULT NULL,
                item_count INT NOT NULL DEFAULT 0,
                total_amount DECIMAL(18, 4) NOT NULL DEFAULT 0,
                status ENUM('ready','blocked','submitted','failed') NOT NULL DEFAULT 'blocked',
                missing_mappings INT NOT NULL DEFAULT 0,
                last_error TEXT NULL,
                prepared_at DATETIME NOT NULL,
                submitted_at DATETIME DEFAULT NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY uniq_mra_eis_sales_queue_txn (transaction_id),
                KEY idx_mra_eis_sales_queue_branch (branch_id),
                KEY idx_mra_eis_sales_queue_status (status),
                KEY idx_mra_eis_sales_queue_sold_at (sold_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ");

        $this->conn->exec("
            CREATE TABLE IF NOT EXISTS mra_eis_sales_queue_lines (
                id INT AUTO_INCREMENT PRIMARY KEY,
                queue_id INT NOT NULL,
                product_code VARCHAR(120) NOT NULL,
                product_name VARCHAR(255) NOT NULL,
                tax_rate_id VARCHAR(50) DEFAULT NULL,
                quantity DECIMAL(18, 4) NOT NULL DEFAULT 0,
                unit_price DECIMAL(18, 4) NOT NULL DEFAULT 0,
                line_total DECIMAL(18, 4) NOT NULL DEFAULT 0,
                unique_codes TEXT NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                KEY idx_mra_eis_sales_queue_lines_queue (queue_id),
                KEY idx_mra_eis_sales_queue_lines_product (product_code)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ");

        // Persisted snapshot of the EIS warehouse-inventory pull (barcode + aggregate quantity),
        // separate from mra_eis_site_products which tracks per-terminal/site stock. Reconciled
        // against items.current_location_id = 7 by syncWarehouseStock().
        $this->conn->exec("
            CREATE TABLE IF NOT EXISTS mra_eis_warehouse_stock (
                id INT AUTO_INCREMENT PRIMARY KEY,
                barcode VARCHAR(120) NOT NULL,
                product_name VARCHAR(255) DEFAULT NULL,
                description TEXT NULL,
                unit_price DECIMAL(18, 4) NOT NULL DEFAULT 0,
                unit_of_measure VARCHAR(80) DEFAULT NULL,
                quantity_in_stock DECIMAL(18, 4) NOT NULL DEFAULT 0,
                raw_payload MEDIUMTEXT NULL,
                synced_at DATETIME NOT NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY uniq_mra_eis_warehouse_stock_barcode (barcode)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ");

        $this->ensureTerminalColumns();
        $this->ensureItemProductCodeColumn();
        $this->ensureProductCodeMapColumns();
        $this->ensureWarehouseStockColumns();
        $this->ensureBatchMappingIndexes();
    }

    /**
     * unmappedItemBatches()/mappedItemBatches() LEFT JOIN sold_items on unique_code and filter on
     * items.eis_site_product_id - without indexes on both, each page load scans the full items and
     * sold_items tables, which is what made the Stock Batch Mapping page slow to load.
     */
    private function ensureBatchMappingIndexes() {
        if ($this->columnExists('items', 'eis_site_product_id')) {
            try {
                $this->conn->exec("CREATE INDEX idx_items_eis_site_product ON items (eis_site_product_id)");
            } catch (Exception $e) {
                // Index already exists on some MySQL/MariaDB versions.
            }
        }

        try {
            $this->conn->exec("CREATE INDEX idx_sold_items_unique_code ON sold_items (unique_code)");
        } catch (Exception $e) {
            // Index already exists on some MySQL/MariaDB versions.
        }
    }

    private function ensureItemProductCodeColumn() {
        if (!$this->columnExists('items', 'product_code')) {
            $this->conn->exec("ALTER TABLE items ADD COLUMN product_code VARCHAR(120) DEFAULT NULL AFTER item_name");
        }

        $this->backfillItemProductCodes();

        try {
            $this->conn->exec("CREATE INDEX idx_items_product_code ON items (product_code)");
        } catch (Exception $e) {
            // Index already exists on some MySQL/MariaDB versions.
        }
    }

    // items.source_eis_warehouse_stock_id / source_eis_sequence_number mark pieces this class
    // created out of an EIS warehouse-inventory row, so syncWarehouseStock() only ever grows or
    // shrinks the pieces it owns and never touches manually received or already-sold items.
    private function ensureWarehouseStockColumns() {
        if (!$this->columnExists('items', 'source_eis_warehouse_stock_id')) {
            $this->conn->exec("ALTER TABLE items ADD COLUMN source_eis_warehouse_stock_id INT DEFAULT NULL COMMENT 'FK -> mra_eis_warehouse_stock.id; set only for pieces created by the EIS warehouse sync'");
        }

        if (!$this->columnExists('items', 'source_eis_sequence_number')) {
            $this->conn->exec("ALTER TABLE items ADD COLUMN source_eis_sequence_number INT DEFAULT NULL COMMENT 'Sequence number within the EIS warehouse stock row; pairs with source_eis_warehouse_stock_id'");
        }

        if (!$this->columnExists('items', 'tax_rate_id')) {
            $this->conn->exec("ALTER TABLE items ADD COLUMN tax_rate_id VARCHAR(50) DEFAULT NULL COMMENT 'MRA EIS product tax group/rate identifier'");
        }

        try {
            $this->conn->exec("CREATE INDEX idx_items_source_eis_warehouse_stock ON items (source_eis_warehouse_stock_id)");
        } catch (Exception $e) {
            // Index already exists on some MySQL/MariaDB versions.
        }

        // Sequence numbers restart per branch. The legacy two-column unique index prevented the
        // same EIS barcode from being reconciled into more than one terminal's IMS branch.
        try {
            $this->conn->exec("DROP INDEX uniq_items_eis_warehouse_sequence ON items");
        } catch (Exception $e) {
            // Already migrated or the legacy index was never created.
        }
        try {
            $this->conn->exec("CREATE UNIQUE INDEX uniq_items_eis_warehouse_branch_sequence ON items (source_eis_warehouse_stock_id, current_location_id, source_eis_sequence_number)");
        } catch (Exception $e) {
            // Index already exists.
        }
    }

    private function ensureProductCodeMapColumns() {
        $columns = [
            'ims_product_code' => 'VARCHAR(120) DEFAULT NULL',
            'ims_item_name'    => 'VARCHAR(255) DEFAULT NULL',
            'eis_product_code' => 'VARCHAR(120) DEFAULT NULL',
            'eis_product_name' => 'VARCHAR(255) DEFAULT NULL',
            'selling_price'    => 'DECIMAL(18,4) NOT NULL DEFAULT 0.0000',
            'levy_rate_id'     => 'VARCHAR(50) DEFAULT NULL',
        ];

        foreach ($columns as $column => $definition) {
            if ($this->columnExists('mra_eis_product_code_map', $column)) {
                continue;
            }
            $this->conn->exec("ALTER TABLE mra_eis_product_code_map ADD COLUMN `$column` $definition");
        }
    }

    private function backfillItemProductCodes() {
        // items.product_code is the EIS-facing product identifier. Recover it
        // from legacy imports, but never generate an internal IMS-* code here.
        $this->conn->exec("
            UPDATE items i
            SET i.product_code = TRIM(SUBSTRING_INDEX(i.technical_specs, ' barcode: ', -1))
            WHERE i.technical_specs LIKE '% barcode: %'
              AND (i.product_code IS NULL OR i.product_code = '' OR i.product_code LIKE 'IMS-%')
        ");

        $this->conn->exec("
            UPDATE items i
            SET i.product_code = (
                SELECT MIN(ps.barcode)
                FROM product_stock ps
                WHERE ps.product_name = i.item_name
                  AND ps.barcode IS NOT NULL
                  AND ps.barcode != ''
                  AND (ps.branch_id = i.current_location_id OR i.current_location_id IS NULL)
            )
            WHERE (i.product_code IS NULL OR i.product_code = '' OR i.product_code LIKE 'IMS-%')
              AND EXISTS (
                  SELECT 1
                  FROM product_stock ps
                  WHERE ps.product_name = i.item_name
                    AND ps.barcode IS NOT NULL
                    AND ps.barcode != ''
                    AND (ps.branch_id = i.current_location_id OR i.current_location_id IS NULL)
              )
        ");
    }


    private function ensureTerminalColumns() {
        $columns = [
            'terminal_label' => "VARCHAR(120) NOT NULL DEFAULT 'EIS Terminal'",
            'branch_id' => 'INT NOT NULL DEFAULT 7',
            'terminal_activation_code' => "VARCHAR(50) NOT NULL DEFAULT ''",
            'taxpayer_tin' => 'VARCHAR(80) DEFAULT NULL',
            'site_id' => 'VARCHAR(80) DEFAULT NULL',
            'global_config_version' => 'INT NOT NULL DEFAULT 1',
            'taxpayer_config_version' => 'INT NOT NULL DEFAULT 1',
            'terminal_config_version' => 'INT NOT NULL DEFAULT 1',
            'environment' => "ENUM('sandbox','production') NOT NULL DEFAULT 'sandbox'",
            'api_base_url' => "VARCHAR(255) NOT NULL DEFAULT 'https://eis-api.mra.mw/api/v1'",
            'os_name' => "VARCHAR(50) NOT NULL DEFAULT ''",
            'os_version' => "VARCHAR(50) NOT NULL DEFAULT ''",
            'os_build' => 'VARCHAR(50) DEFAULT NULL',
            'mac_address' => "VARCHAR(17) NOT NULL DEFAULT ''",
            'pos_product_id' => "VARCHAR(50) NOT NULL DEFAULT ''",
            'pos_product_version' => "VARCHAR(50) NOT NULL DEFAULT ''",
            'status' => "ENUM('draft','activation_pending','activated','failed') NOT NULL DEFAULT 'draft'",
            'mra_terminal_id' => 'VARCHAR(50) DEFAULT NULL',
            'jwt_token' => 'TEXT NULL',
            'secret_key' => 'VARCHAR(255) DEFAULT NULL',
            'activation_response' => 'MEDIUMTEXT NULL',
            'confirmation_response' => 'MEDIUMTEXT NULL',
            'last_error' => 'TEXT NULL',
            'activated_at' => 'DATETIME DEFAULT NULL',
            'confirmed_at' => 'DATETIME DEFAULT NULL',
            'created_by' => 'INT DEFAULT NULL',
            'created_at' => 'TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP',
            'updated_at' => 'TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
        ];

        foreach ($columns as $column => $definition) {
            if ($this->columnExists('mra_eis_terminals', $column)) {
                continue;
            }

            $this->conn->exec("ALTER TABLE mra_eis_terminals ADD COLUMN `$column` $definition");
        }
    }

    private function columnExists($table, $column) {
        $stmt = $this->conn->prepare("SHOW COLUMNS FROM `$table` LIKE :column_name");
        $stmt->execute([':column_name' => $column]);

        return (bool)$stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function all() {
        $stmt = $this->conn->prepare("
            SELECT met.*, b.branch_name
            FROM mra_eis_terminals met
            LEFT JOIN branches b ON b.branch_id = met.branch_id
            ORDER BY met.updated_at DESC, met.id DESC
        ");
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function activatedTerminals($branchId = null) {
        $sql = "
            SELECT met.*, b.branch_name
            FROM mra_eis_terminals met
            LEFT JOIN branches b ON b.branch_id = met.branch_id
            WHERE met.status = 'activated'
              AND met.jwt_token IS NOT NULL
              AND met.jwt_token != ''
        ";
        $params = [];

        if ($branchId !== null && $branchId !== '') {
            $sql .= " AND met.branch_id = :branch_id";
            $params[':branch_id'] = (int)$branchId;
        }

        $sql .= " ORDER BY met.terminal_label, met.id";
        $stmt = $this->conn->prepare($sql);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value, PDO::PARAM_INT);
        }
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function branches() {
        $stmt = $this->conn->prepare("
            SELECT branch_id, branch_name
            FROM branches
            WHERE COALESCE(is_active, 1) = 1
            ORDER BY branch_name
        ");
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function find($id) {
        $id = $this->requiredInteger($id, 'Terminal');
        $stmt = $this->conn->prepare("
            SELECT met.*, b.branch_name
            FROM mra_eis_terminals met
            LEFT JOIN branches b ON b.branch_id = met.branch_id
            WHERE met.id = :id
            LIMIT 1
        ");
        $stmt->execute([':id' => $id]);

        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function saveDraft(array $input, $userId = null) {
        $data = $this->validatedData($input);
        $id = trim((string)($input['id'] ?? ''));

        if ($id !== '') {
            $id = $this->requiredInteger($id, 'Terminal');
            if (!$this->find($id)) {
                throw new InvalidArgumentException('Selected terminal was not found.');
            }

            $data[':id'] = $id;
            $stmt = $this->conn->prepare("
                UPDATE mra_eis_terminals SET
                    terminal_label = :terminal_label,
                    branch_id = :branch_id,
                    terminal_activation_code = :terminal_activation_code,
                    taxpayer_tin = :taxpayer_tin,
                    site_id = :site_id,
                    global_config_version = :global_config_version,
                    taxpayer_config_version = :taxpayer_config_version,
                    terminal_config_version = :terminal_config_version,
                    environment = :environment,
                    api_base_url = :api_base_url,
                    os_name = :os_name,
                    os_version = :os_version,
                    os_build = :os_build,
                    mac_address = :mac_address,
                    pos_product_id = :pos_product_id,
                    pos_product_version = :pos_product_version,
                    last_error = NULL
                WHERE id = :id
            ");
            $stmt->execute($data);

            return $id;
        }

        $stmt = $this->conn->prepare("
            INSERT INTO mra_eis_terminals (
                terminal_label,
                branch_id,
                terminal_activation_code,
                taxpayer_tin,
                site_id,
                global_config_version,
                taxpayer_config_version,
                terminal_config_version,
                environment,
                api_base_url,
                os_name,
                os_version,
                os_build,
                mac_address,
                pos_product_id,
                pos_product_version,
                created_by
            ) VALUES (
                :terminal_label,
                :branch_id,
                :terminal_activation_code,
                :taxpayer_tin,
                :site_id,
                :global_config_version,
                :taxpayer_config_version,
                :terminal_config_version,
                :environment,
                :api_base_url,
                :os_name,
                :os_version,
                :os_build,
                :mac_address,
                :pos_product_id,
                :pos_product_version,
                :created_by
            )
        ");
        $stmt->execute($data + [':created_by' => $userId]);

        return (int)$this->conn->lastInsertId();
    }

    public function delete($id) {
        $id = $this->requiredInteger($id, 'Terminal');
        $stmt = $this->conn->prepare("DELETE FROM mra_eis_terminals WHERE id = :id");
        return $stmt->execute([':id' => $id]);
    }

    public function activationPayload(array $terminal) {
        return [
            'terminalActivationCode' => $terminal['terminal_activation_code'],
            'environment' => [
                'platform' => [
                    'osName' => $terminal['os_name'],
                    'osVersion' => $terminal['os_version'],
                    'osBuild' => $terminal['os_build'],
                    'macAddress' => $terminal['mac_address'],
                ],
                'pos' => [
                    'productID' => $terminal['pos_product_id'],
                    'productVersion' => $terminal['pos_product_version'],
                ],
            ],
        ];
    }

    public function activate($id, $vendorAccessKey = null) {
        $terminal = $this->requireTerminal($id);
        $payload = $this->activationPayload($terminal);
        $headers = [];

        if (strtolower(trim((string)($terminal['environment'] ?? 'sandbox'))) === 'production') {
            $vendorAccessKey = trim((string)$vendorAccessKey);
            if ($vendorAccessKey === '') {
                $vendorAccessKey = trim((string)(getenv('MRA_EIS_ACCESS_KEY_PRODUCTION') ?: getenv('MRA_EIS_ACCESS_KEY')));
            }
            if ($vendorAccessKey === '') {
                throw new RuntimeException('The MRA Vendor Access Key is required for Production terminal activation.');
            }
            $headers[] = 'x-access-key: ' . $vendorAccessKey;
        }

        $response = $this->postJson(
            rtrim($terminal['api_base_url'], '/') . '/onboarding/activate-terminal',
            $payload,
            $headers
        );

        $body = $response['body'];
        $decoded = json_decode($body, true);
        if ($response['status'] < 200 || $response['status'] >= 300) {
            $this->markFailed($terminal['id'], $body);
            if ((int)$response['status'] === 401 && !empty($headers)) {
                throw new RuntimeException('MRA EIS rejected the Production Vendor Access Key (HTTP 401). Verify the key issued to this certified POS vendor.');
            }
            throw new RuntimeException('MRA EIS terminal activation failed with HTTP ' . $response['status'] . '.');
        }

        $activatedTerminal = $decoded['data']['activatedTerminal'] ?? [];
        $terminalId = $activatedTerminal['terminalId'] ?? '';
        $credentials = $activatedTerminal['terminalCredentials'] ?? [];
        $jwtToken = $credentials['jwtToken'] ?? '';
        $secretKey = $credentials['secretKey'] ?? '';

        if ($terminalId === '' || $secretKey === '') {
            $this->markFailed($terminal['id'], $body);
            throw new RuntimeException('MRA EIS activation response did not include the terminal ID and secret key.');
        }

        $stmt = $this->conn->prepare("
            UPDATE mra_eis_terminals SET
                status = 'activation_pending',
                mra_terminal_id = :mra_terminal_id,
                jwt_token = :jwt_token,
                secret_key = :secret_key,
                activation_response = :activation_response,
                last_error = NULL,
                activated_at = NOW()
            WHERE id = :id
        ");
        $stmt->execute([
            ':mra_terminal_id' => $terminalId,
            ':jwt_token' => $jwtToken,
            ':secret_key' => $secretKey,
            ':activation_response' => $body,
            ':id' => $terminal['id'],
        ]);

        return [
            'response' => $response,
            'terminal_id' => $terminalId,
        ];
    }

    public function confirm($id) {
        $terminal = $this->requireTerminal($id);
        if (trim((string)$terminal['mra_terminal_id']) === '' || trim((string)$terminal['secret_key']) === '') {
            throw new RuntimeException('Activate this terminal before sending confirmation.');
        }

        $url = rtrim($terminal['api_base_url'], '/') . '/onboarding/terminal-activated-confirmation';
        $payload = ['terminalId' => trim((string)$terminal['mra_terminal_id'])];
        $attempts = $this->confirmationSignatureAttempts($terminal);
        $response = null;
        $successfulMethod = '';
        $failedAttempts = [];

        foreach ($attempts as $attempt) {
            $response = $this->postJson(
                $url,
                $payload,
                $attempt['headers']
            );

            if ($response['status'] >= 200 && $response['status'] < 300) {
                $successfulMethod = $attempt['method'];
                break;
            }

            $failedAttempts[] = [
                'method' => $attempt['method'],
                'status' => $response['status'],
                'body' => $response['body'],
            ];

            if ((int)$response['status'] !== 401) {
                break;
            }
        }

        $body = $response['body'] ?? '';
        if (!$response || $response['status'] < 200 || $response['status'] >= 300) {
            $this->markFailed($terminal['id'], $this->confirmationFailureDetails($terminal, $url, $payload, $failedAttempts));
            throw new RuntimeException(
                'MRA EIS terminal confirmation failed with HTTP ' . ($response['status'] ?? 0) .
                '. The system tried ' . implode(' then ', array_column($attempts, 'method')) . '.'
            );
        }

        $stmt = $this->conn->prepare("
            UPDATE mra_eis_terminals SET
                status = 'activated',
                confirmation_response = :confirmation_response,
                last_error = NULL,
                confirmed_at = NOW()
            WHERE id = :id
        ");
        $stmt->execute([
            ':confirmation_response' => $body . "\n\nConfirmation signature method: " . $successfulMethod,
            ':id' => $terminal['id'],
        ]);

        return $response;
    }

    public function syncSiteProducts($terminalId, $tin, $siteId) {
        $terminal = $this->requireTerminal($terminalId);
        $token = trim((string)($terminal['jwt_token'] ?? ''));
        if ($token === '') {
            throw new RuntimeException('This terminal has no stored activation token. Activate and confirm it before downloading site products.');
        }

        $tin = $this->requiredText($tin, 'TIN', 80);
        $terminalSiteId = trim((string)($terminal['site_id'] ?? ''));
        if ($terminalSiteId === '') {
            throw new RuntimeException('This terminal has no Site ID configured. Update the terminal before downloading site products.');
        }

        $postedSiteId = trim((string)$siteId);
        if ($postedSiteId !== '' && $postedSiteId !== $terminalSiteId) {
            throw new RuntimeException('The selected terminal belongs to Site ID ' . $terminalSiteId . '. Select the matching terminal/site before syncing products.');
        }

        $siteId = $this->requiredText($terminalSiteId, 'Site ID', 80);
        $url = rtrim($terminal['api_base_url'], '/') . '/utilities/get-terminal-site-products';
        $payload = [
            'tin' => $tin,
            'siteId' => $siteId,
        ];

        $attempts = [
            ['method' => 'Bearer authorization token', 'headers' => ['Authorization: Bearer ' . $token]],
            ['method' => 'Raw authorization token', 'headers' => ['Authorization: ' . $token]],
        ];
        $response = null;
        $successfulMethod = '';

        foreach ($attempts as $attempt) {
            $response = $this->postJson($url, $payload, $attempt['headers']);
            if ($response['status'] >= 200 && $response['status'] < 300) {
                $successfulMethod = $attempt['method'];
                break;
            }
        }

        if (!$response || $response['status'] === 401) {
            $refreshed = $this->tryRefreshToken($terminal);
            if ($refreshed !== null) {
                $token = $refreshed;
                $terminal = $this->find($terminal['id']);
                $refreshAttempts = [
                    ['method' => 'Bearer refreshed token', 'headers' => ['Authorization: Bearer ' . $refreshed]],
                    ['method' => 'Raw refreshed token',   'headers' => ['Authorization: ' . $refreshed]],
                ];
                foreach ($refreshAttempts as $attempt) {
                    $response = $this->postJson($url, $payload, $attempt['headers']);
                    if ($response['status'] >= 200 && $response['status'] < 300) {
                        $successfulMethod = $attempt['method'];
                        break;
                    }
                }
            }
        }

        if (!$response || $response['status'] < 200 || $response['status'] >= 300) {
            $errorBody = $response['body'] ?? '';
            $decoded = @json_decode($errorBody, true);
            $errorMsg = (is_array($decoded) && !empty($decoded['remark'])) ? $decoded['remark'] : $errorBody;
            $hint = ((int)($response['status'] ?? 0) === 401)
                ? ' The terminal token is expired or invalid — re-confirm the terminal on the Onboarding page to get a fresh token.'
                : '';
            throw new RuntimeException('MRA site products download failed with HTTP ' . ($response['status'] ?? 0) . ': ' . $errorMsg . $hint);
        }

        $decoded = json_decode($response['body'], true);
        if (!is_array($decoded)) {
            throw new RuntimeException('MRA site products response was not valid JSON.');
        }

        $products = $decoded['data'] ?? [];
        if (!is_array($products)) {
            throw new RuntimeException('MRA site products response did not include a products array.');
        }

        $saved = $this->saveSiteProducts((int)$terminal['id'], $tin, $siteId, $products);

        return [
            'saved' => $saved,
            'response' => $response,
            'authorization_method' => $successfulMethod,
        ];
    }

    public function siteProducts($terminalId = null, $siteId = null, $branchId = null) {
        $sql = "
            SELECT msp.*, met.terminal_label, b.branch_name
            FROM mra_eis_site_products msp
            LEFT JOIN mra_eis_terminals met ON met.id = msp.terminal_local_id
            LEFT JOIN branches b ON b.branch_id = met.branch_id
            WHERE 1 = 1
        ";
        $params = [];

        if ($terminalId !== null && $terminalId !== '') {
            $sql .= " AND msp.terminal_local_id = :terminal_id";
            $params[':terminal_id'] = (int)$terminalId;
        }

        if ($branchId !== null && $branchId !== '') {
            $sql .= " AND met.branch_id = :branch_id";
            $params[':branch_id'] = (int)$branchId;
        }

        if ($siteId !== null && $siteId !== '') {
            $sql .= " AND msp.site_id = :site_id";
            $params[':site_id'] = $siteId;
        }

        $sql .= " ORDER BY msp.product_name, msp.product_code";
        $stmt = $this->conn->prepare($sql);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
        }
        $stmt->execute();

        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        foreach ($rows as &$row) {
            $row['tax_category'] = $this->warehouseTaxCategoryFromPayload($row['raw_payload'] ?? null);
        }
        unset($row);

        return $rows;
    }

    public function warehouseInventory($page = 1, $pageSize = 0, $branchId = 7, $terminalId = null) {
        $page = max(1, (int)$page);
        $pageSize = max(0, (int)$pageSize);
        $terminal = $this->warehouseInventoryTerminal($branchId, $terminalId);
        // Use the resolved terminal's own api_base_url (sandbox vs production), matching the
        // convention every other endpoint in this class follows (e.g. syncSiteProducts()) -
        // never hardcode the production host, or a sandbox-activated terminal's token gets
        // sent to the production API (and fails, or worse, silently queries the wrong environment).
        $baseUrl = !empty($terminal['api_base_url']) ? rtrim($terminal['api_base_url'], '/') : 'https://eis-api.mra.mw/api/v1';
        $url = $baseUrl . '/stock/warehouse-inventory?page=' . $page . '&pageSize=' . $pageSize;
        $token = $terminal['jwt_token'];
        $attempts = [
            ['method' => 'Bearer authorization token', 'headers' => ['Authorization: Bearer ' . $token]],
            ['method' => 'Raw authorization token', 'headers' => ['Authorization: ' . $token]],
        ];
        $response = null;
        $authorizationMethod = '';

        foreach ($attempts as $attempt) {
            $response = $this->getPlain($url, $attempt['headers']);
            if ($response['status'] >= 200 && $response['status'] < 300) {
                $authorizationMethod = $attempt['method'];
                break;
            }
        }

        // MRA's warehouse-inventory endpoint returns a generic 500 ("An internal error
        // occurred") rather than a clean 401 for an expired/invalid token, so any failure
        // here - not just 401 - is treated as a reason to refresh the token once, same
        // recovery syncSiteProducts() already does for the site-products endpoint.
        $refreshAttempted = false;
        if ($response['status'] < 200 || $response['status'] >= 300) {
            $refreshAttempted = true;
            $refreshed = $this->tryRefreshToken($terminal);
            if ($refreshed !== null) {
                $refreshAttempts = [
                    ['method' => 'Bearer refreshed token', 'headers' => ['Authorization: Bearer ' . $refreshed]],
                    ['method' => 'Raw refreshed token', 'headers' => ['Authorization: ' . $refreshed]],
                ];
                foreach ($refreshAttempts as $attempt) {
                    $response = $this->getPlain($url, $attempt['headers']);
                    if ($response['status'] >= 200 && $response['status'] < 300) {
                        $authorizationMethod = $attempt['method'];
                        break;
                    }
                }
            }
        }

        if ($response['status'] < 200 || $response['status'] >= 300) {
            $hint = $refreshAttempted
                ? ' The stored terminal token was rejected and could not be refreshed automatically — re-confirm the terminal on the Onboarding page to get a fresh token.'
                : '';
            return [
                'url' => $url,
                'status' => $response['status'],
                'body' => $response['body'] ?? '',
                'decoded' => null,
                'rows' => [],
                'summary' => [
                    'error' => 'MRA EIS warehouse stock request failed.' . $hint,
                    'http_status' => $response['status'],
                    'response' => $response['body'] ?? '',
                    'authorization_method' => $authorizationMethod ?: 'Authorization token sent',
                ],
                'authorization_method' => $authorizationMethod ?: 'Authorization token sent',
            ];
        }

        $decoded = json_decode($response['body'], true);
        if (!is_array($decoded)) {
            $rows = $this->parsePlainRows($response['body']);
            return [
                'url' => $url,
                'status' => $response['status'],
                'body' => $response['body'],
                'decoded' => null,
                'rows' => $rows,
                'summary' => [
                    'response_type' => 'text/plain',
                    'response_length' => strlen((string)$response['body']),
                    'authorization_method' => $authorizationMethod,
                ],
            ];
        }

        return [
            'url' => $url,
            'status' => $response['status'],
            'body' => $response['body'],
            'decoded' => $decoded,
            'rows' => $this->extractRows($decoded),
            'summary' => $this->responseSummary($decoded),
            'authorization_method' => $authorizationMethod,
        ];
    }

    /**
     * Fetches every page of warehouse-inventory rows, not just the first. warehouseInventory()
     * only ever returns one page - without this, a warehouse with more rows than fit on page 1
     * would be silently truncated in the UI/CSV export with no indication anything was missing.
     * Stops as soon as a page comes back with fewer rows than $pageSize (last page) or empty/error,
     * with $maxPages as a hard safety cap against a misbehaving API looping forever.
     */
    public function warehouseInventoryAll($branchId = 7, $pageSize = 200, $maxPages = 25, $terminalId = null) {
        $pageSize = max(1, min(200, (int)$pageSize)); // 200 is the documented API maximum per page
        $maxPages = max(1, (int)$maxPages);

        $allRows = [];
        $pagesFetched = 0;
        $result = null;

        for ($page = 1; $page <= $maxPages; $page++) {
            $result = $this->warehouseInventory($page, $pageSize, $branchId, $terminalId);
            $pagesFetched++;

            if ($result['status'] < 200 || $result['status'] >= 300) {
                break; // surface the error via $result as-is, stop paginating
            }

            $rows = $result['rows'];
            if (empty($rows)) {
                break;
            }

            $allRows = array_merge($allRows, $rows);
            if (count($rows) < $pageSize) {
                break; // fewer rows than a full page means this was the last page
            }
        }

        $result = $result ?? ['url' => '', 'status' => 0, 'body' => '', 'decoded' => null, 'summary' => [], 'authorization_method' => ''];
        $result['rows'] = $allRows;
        $result['summary']['pages_fetched'] = $pagesFetched;
        $result['summary']['page_size_used'] = $pageSize;
        return $result;
    }

    /**
     * Reconciles the EIS warehouse (aggregate barcode + quantity model) into IMS items
     * (one row per physical piece) at $branchId. For each barcode: persists the EIS row into
     * mra_eis_warehouse_stock, resolves it to an IMS product_code, then creates or removes
     * pieces this sync owns (source_eis_warehouse_stock_id) so the branch's on-hand count
     * matches EIS's quantityInStock. Never creates/deletes across branches and never touches
     * sold items or items this sync didn't create - see reconcileWarehouseItems().
     */
    public function syncWarehouseStock($branchId = 7, $terminalId = null) {
        $branchId = (int)$branchId;
        if ($branchId <= 0) {
            $branchId = 7;
        }

        $inventory = $this->warehouseInventoryAll($branchId, 200, 25, $terminalId);
        if ($inventory['status'] < 200 || $inventory['status'] >= 300) {
            throw new RuntimeException($inventory['summary']['error'] ?? 'MRA EIS warehouse stock request failed.');
        }

        $created = 0;
        $removed = 0;
        $unchanged = 0;
        $flaggedFractional = [];
        $flaggedVariance = [];
        $flaggedCapped = [];
        $sampleRows = [];

        foreach ($inventory['rows'] as $row) {
            if (!is_array($row)) {
                continue;
            }

            $fields = $this->resolveWarehouseStockRow($row);
            if ($fields === null) {
                continue;
            }

            $stockId = $this->saveWarehouseStockRow($fields);
            $productCode = $this->resolveIMSProductCode($fields['barcode']);

            $targetQty = (int)floor($fields['quantity_in_stock']);
            if ($targetQty < 0) {
                $targetQty = 0;
            }
            if (abs($fields['quantity_in_stock'] - $targetQty) > 0.0001) {
                $flaggedFractional[] = [
                    'barcode' => $fields['barcode'],
                    'quantity_in_stock' => $fields['quantity_in_stock'],
                ];
            }
            if ($targetQty > self::MAX_QUANTITY_PER_BARCODE) {
                $flaggedCapped[] = [
                    'barcode' => $fields['barcode'],
                    'reported_quantity' => $targetQty,
                    'capped_to' => self::MAX_QUANTITY_PER_BARCODE,
                ];
                $targetQty = self::MAX_QUANTITY_PER_BARCODE;
            }

            $result = $this->reconcileWarehouseItems($stockId, $productCode, $fields, $branchId, $targetQty);
            $created += $result['created'];
            $removed += $result['removed'];
            if ($result['created'] === 0 && $result['removed'] === 0) {
                $unchanged++;
            }
            if ($result['variance']) {
                $flaggedVariance[] = [
                    'barcode' => $fields['barcode'],
                    'product_code' => $productCode,
                    'target_quantity' => $targetQty,
                    'organic_quantity' => $result['organic_quantity'],
                    'sync_owned_quantity' => $result['sync_owned_quantity'],
                ];
            }

            // Debug aid: capture the raw payload + what we resolved from it for the first few
            // rows, so a "0 created" run can be diagnosed from the returned summary alone
            // instead of requiring a separate lookup at the raw EIS response.
            if (count($sampleRows) < 3) {
                $sampleRows[] = [
                    'raw_row' => $row,
                    'resolved_barcode' => $fields['barcode'],
                    'resolved_quantity_in_stock' => $fields['quantity_in_stock'],
                    'resolved_product_code' => $productCode,
                    'target_quantity' => $targetQty,
                    'organic_quantity' => $result['organic_quantity'],
                    'sync_owned_quantity' => $result['sync_owned_quantity'],
                ];
            }
        }

        return [
            'rows_processed' => count($inventory['rows']),
            'created' => $created,
            'removed' => $removed,
            'unchanged' => $unchanged,
            'flagged_fractional' => $flaggedFractional,
            'flagged_variance' => $flaggedVariance,
            'flagged_capped' => $flaggedCapped,
            'sample_rows' => $sampleRows,
            'authorization_method' => $inventory['authorization_method'] ?? '',
        ];
    }

    /** Fetches one terminal's live site products and reconciles physical products into its IMS branch. */
    public function syncSiteTerminalProductsToBranch($terminalId, $branchId, array $onlyProductCodes = [], $refresh = true) {
        $terminal = $this->requireTerminal($terminalId);
        $branchId = (int)$branchId;
        if ($branchId <= 0 || $branchId !== (int)$terminal['branch_id']) {
            throw new RuntimeException('The selected site terminal does not belong to the destination IMS branch.');
        }

        $tin = trim((string)($terminal['taxpayer_tin'] ?? ''));
        $siteId = trim((string)($terminal['site_id'] ?? ''));
        if ($refresh) {
            $this->syncSiteProducts($terminal['id'], $tin, $siteId);
        }
        $products = $this->siteProducts($terminal['id'], $siteId, $branchId);
        $productCodeFilter = [];
        foreach ($onlyProductCodes as $code) {
            $code = trim((string)$code);
            if ($code !== '') {
                $productCodeFilter[$code] = true;
            }
        }

        $created = 0;
        $removed = 0;
        $unchanged = 0;
        $flaggedFractional = [];
        $flaggedVariance = [];
        $flaggedCapped = [];

        foreach ($products as $product) {
            if ((int)($product['is_product'] ?? 0) !== 1) {
                continue;
            }
            if ($productCodeFilter && !isset($productCodeFilter[(string)$product['product_code']])) {
                continue;
            }
            $quantity = is_numeric($product['quantity'] ?? null) ? (float)$product['quantity'] : 0;
            $targetQty = max(0, (int)floor($quantity));
            if (abs($quantity - $targetQty) > 0.0001) {
                $flaggedFractional[] = ['barcode' => $product['product_code'], 'quantity_in_stock' => $quantity];
            }
            if ($targetQty > self::MAX_QUANTITY_PER_BARCODE) {
                $flaggedCapped[] = ['barcode' => $product['product_code'], 'reported_quantity' => $targetQty, 'capped_to' => self::MAX_QUANTITY_PER_BARCODE];
                $targetQty = self::MAX_QUANTITY_PER_BARCODE;
            }

            $fields = [
                'barcode' => (string)$product['product_code'],
                'product_name' => (string)$product['product_name'],
                'description' => $product['description'] ?? null,
                'unit_price' => (float)($product['price'] ?? 0),
                'unit_of_measure' => $product['unit_of_measure'] ?? null,
                'tax_rate_id' => $product['tax_rate_id'] ?? null,
                'eis_site_product_id' => (int)$product['id'],
                'source_label' => 'site terminal ' . $terminal['terminal_label'],
                'quantity_in_stock' => $quantity,
                'raw_payload' => json_decode((string)($product['raw_payload'] ?? ''), true) ?: $product,
            ];
            $stockId = $this->saveWarehouseStockRow($fields);
            $productCode = $this->resolveIMSProductCode($fields['barcode']);
            $result = $this->reconcileWarehouseItems($stockId, $productCode, $fields, $branchId, $targetQty);
            $created += $result['created'];
            $removed += $result['removed'];
            if ($result['created'] === 0 && $result['removed'] === 0) { $unchanged++; }
            if ($result['variance']) {
                $flaggedVariance[] = [
                    'barcode' => $fields['barcode'], 'product_code' => $productCode,
                    'target_quantity' => $targetQty, 'organic_quantity' => $result['organic_quantity'],
                    'sync_owned_quantity' => $result['sync_owned_quantity'],
                ];
            }
        }

        return [
            'created' => $created,
            'removed' => $removed,
            'unchanged' => $unchanged,
            'flagged_fractional' => $flaggedFractional,
            'flagged_variance' => $flaggedVariance,
            'flagged_capped' => $flaggedCapped,
        ];
    }
    /**
     * Lists the persisted EIS warehouse snapshot alongside how many IMS items are linked to each
     * row. $itemBranchId scopes the linked-item count to one branch (each sync run writes to
     * whichever branch was chosen as destination, so the same barcode can have items in several
     * branches) - pass null only if an unscoped, cross-branch count is genuinely wanted. Sold
     * items (present in sold_items by unique_code) are excluded - they're no longer stock on
     * hand, so they shouldn't inflate this count or be editable via the linked-items modal.
     * When $itemBranchId is given, barcodes with zero linked items in that branch are dropped
     * entirely - the table should only ever show stock actually synced to the selected branch.
     */
    public function warehouseStock($limit = 500, $itemBranchId = null) {
        $branchFilter = '';
        $params = [];
        if ($itemBranchId !== null && $itemBranchId !== '') {
            $branchFilter = ' AND i.current_location_id = :item_branch_id';
            $params[':item_branch_id'] = (int)$itemBranchId;
        }
        $havingFilter = $branchFilter !== '' ? ' HAVING ims_item_count > 0' : '';
        $stmt = $this->conn->prepare("
            SELECT ws.*,
                   (SELECT COUNT(*) FROM items i
                    WHERE i.source_eis_warehouse_stock_id = ws.id{$branchFilter}
                      AND NOT EXISTS (
                          SELECT 1 FROM sold_items s
                          WHERE s.item_id = i.item_id AND s.branch_id = i.current_location_id
                      )) AS ims_item_count
            FROM mra_eis_warehouse_stock ws
            {$havingFilter}
            ORDER BY ws.product_name, ws.barcode
            LIMIT " . max(1, min(2000, (int)$limit)) . "
        ");
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value, PDO::PARAM_INT);
        }
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /** Lists the individual IMS items (physical pieces) the warehouse sync created for one EIS warehouse-stock row, so their unique_code can be reviewed/edited. Scoped to a single branch - pass $branchId (matches the page's selected/warehouse branch) so items from other branches that happen to share this barcode never appear or get edited together. Excludes items already sold from that branch (sold_items.item_id/branch_id) - once sold, an item is no longer editable stock. */
    public function linkedWarehouseItems($stockId, $branchId = null) {
        $sql = "
            SELECT i.item_id, i.unique_code, i.item_details, i.item_name, i.product_code, i.created_at,
                   i.current_location_id AS branch_id, b.branch_name, i.tax_rate_id AS tax_category
            FROM items i
            LEFT JOIN branches b ON b.branch_id = i.current_location_id
            WHERE i.source_eis_warehouse_stock_id = :stock_id
              AND NOT EXISTS (
                  SELECT 1 FROM sold_items s
                  WHERE s.item_id = i.item_id AND s.branch_id = i.current_location_id
              )
        ";
        $params = [':stock_id' => (int)$stockId];
        if ($branchId !== null && $branchId !== '') {
            $sql .= " AND i.current_location_id = :branch_id";
            $params[':branch_id'] = (int)$branchId;
        }
        $sql .= " ORDER BY i.unique_code";

        $stmt = $this->conn->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * DB-only view of the last synced warehouse snapshot, shaped like warehouseInventoryCombined()
     * but without any live EIS call - safe to use on every page load. warehouseInventoryCombined()
     * pages through the live EIS API (up to 25 sequential HTTP requests with a 30s timeout each)
     * and should only be called when the user explicitly asks for live data.
     */
    public function warehouseStockSnapshot($limit = 500, $itemBranchId = null) {
        $combined = [];
        foreach ($this->warehouseStock($limit, $itemBranchId) as $stockRow) {
            $combined[] = [
                'barcode' => $stockRow['barcode'],
                'product_name' => (string)($stockRow['product_name'] ?? ''),
                'product_description' => (string)($stockRow['description'] ?? ''),
                'tax_category' => $this->warehouseTaxCategoryFromPayload($stockRow['raw_payload'] ?? null),
                'live_quantity' => null,
                'stock_id' => $stockRow['id'],
                'synced_quantity' => $stockRow['quantity_in_stock'],
                'ims_item_count' => (int)$stockRow['ims_item_count'],
                'synced_at' => $stockRow['synced_at'],
            ];
        }
        return $combined;
    }

    /**
     * Combines the live EIS warehouse feed with the persisted reconciliation snapshot into one
     * barcode-keyed list, so the admin page can show a single table instead of two. Barcodes only
     * present in the synced snapshot (e.g. no longer returned live) are appended at the end so
     * their linked IMS items stay reviewable. Pages through the live EIS API (up to 25 sequential
     * HTTP requests) - only call this for an explicit user-requested refresh, never on a normal
     * page load (use warehouseStockSnapshot() for that).
     */
    public function warehouseInventoryCombined($branchId = 7, $terminalId = null, $itemBranchId = null) {
        $inventory = $this->warehouseInventoryAll($branchId, 200, 25, $terminalId);
        if ($inventory['status'] < 200 || $inventory['status'] >= 300) {
            $error = $inventory['summary']['error'] ?? 'MRA EIS warehouse stock request failed.';
            $status = (int)($inventory['status'] ?? 0);
            throw new RuntimeException($error . ($status > 0 ? ' HTTP ' . $status . '.' : ''));
        }
        $liveRows = $inventory['rows'];

        $synced = [];
        foreach ($this->warehouseStock(500, $itemBranchId) as $stockRow) {
            $synced[strtoupper(trim((string)$stockRow['barcode']))] = $stockRow;
        }

        $combined = [];
        $seenBarcodes = [];

        foreach ($liveRows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $barcode = trim((string)$this->firstPresentValue($row, [
                'barcode', 'productCode', 'product_code', 'code', 'sku', 'itemCode', 'item_code',
            ]));
            if ($barcode === '') {
                continue;
            }
            $key = strtoupper($barcode);
            $stockRow = $synced[$key] ?? null;
            if ($itemBranchId !== null && $itemBranchId !== '' && !$stockRow) {
                // No linked items in the selected branch for this barcode - warehouseStock()
                // already dropped it from $synced, so skip it here too rather than showing a
                // live-only row with an always-zero Linked IMS Items count.
                continue;
            }
            $seenBarcodes[$key] = true;

            $combined[] = [
                'barcode' => $barcode,
                'product_name' => (string)$this->firstPresentValue($row, ['productName', 'product_name', 'name']),
                'product_description' => (string)$this->firstPresentValue($row, ['productDescription', 'product_description', 'description']),
                'tax_category' => (string)$this->firstPresentValue($row, ['productTaxCategory', 'product_tax_category', 'taxCategory', 'tax_category', 'taxCategoryName', 'tax_category_name', 'taxCategoryId', 'tax_category_id']),
                'live_quantity' => $this->firstPresentValue($row, [
                    'currentQuantity', 'current_quantity', 'quantityInStock', 'quantity_in_stock', 'quantity',
                    'stockQuantity', 'stock_quantity', 'availableQuantity', 'available_quantity',
                    'stockOnHand', 'stock_on_hand', 'qtyInStock', 'qty_in_stock', 'availableStock',
                    'available_stock', 'stockLevel', 'stock_level',
                ]),
                'stock_id' => $stockRow['id'] ?? null,
                'synced_quantity' => $stockRow['quantity_in_stock'] ?? null,
                'ims_item_count' => $stockRow ? (int)$stockRow['ims_item_count'] : 0,
                'synced_at' => $stockRow['synced_at'] ?? null,
            ];
        }

        foreach ($synced as $key => $stockRow) {
            if (!empty($seenBarcodes[$key])) {
                continue;
            }
            $combined[] = [
                'barcode' => $stockRow['barcode'],
                'product_name' => (string)($stockRow['product_name'] ?? ''),
                'product_description' => '',
                'tax_category' => $this->warehouseTaxCategoryFromPayload($stockRow['raw_payload'] ?? null),
                'live_quantity' => null,
                'stock_id' => $stockRow['id'],
                'synced_quantity' => $stockRow['quantity_in_stock'],
                'ims_item_count' => (int)$stockRow['ims_item_count'],
                'synced_at' => $stockRow['synced_at'],
            ];
        }

        return $combined;
    }

    private function warehouseTaxCategoryFromPayload($payload) {
        if (is_string($payload)) { $payload = json_decode($payload, true); }
        if (!is_array($payload)) { return ''; }
        return (string)$this->firstPresentValue($payload, ['productTaxCategory', 'product_tax_category', 'taxCategory', 'tax_category', 'taxCategoryName', 'tax_category_name', 'taxCategoryId', 'tax_category_id']);
    }

    /** EIS field names for warehouse-inventory rows aren't confirmed by Swagger, so this tries common casings/aliases (same approach extractRows() uses). Returns null when no usable barcode is present. */
    private function resolveWarehouseStockRow(array $row) {
        $barcode = trim((string)$this->firstPresentValue($row, [
            'barcode', 'productCode', 'product_code', 'code', 'sku', 'itemCode', 'item_code',
        ]));
        if ($barcode === '') {
            return null;
        }

        $quantity = $this->firstPresentValue($row, [
            // Confirmed live field name for GET /stock/warehouse-inventory: CurrentQuantity.
            // Kept first so it always wins over the other guessed aliases below.
            'currentQuantity', 'current_quantity',
            'quantityInStock', 'quantity_in_stock', 'quantity', 'stockQuantity', 'stock_quantity',
            'availableQuantity', 'available_quantity', 'stockOnHand', 'stock_on_hand',
            'qtyInStock', 'qty_in_stock', 'availableStock', 'available_stock', 'stockLevel', 'stock_level',
        ]);
        $unitPrice = $this->firstPresentValue($row, ['unitPrice', 'unit_price', 'price']);
        $productName = trim((string)$this->firstPresentValue($row, ['productName', 'product_name', 'name']));

        return [
            'barcode' => substr($barcode, 0, 120),
            'product_name' => substr($productName !== '' ? $productName : $barcode, 0, 255),
            'description' => $this->firstPresentValue($row, ['description', 'productDescription', 'product_description']),
            'unit_price' => is_numeric($unitPrice) ? (float)$unitPrice : 0,
            'unit_of_measure' => $this->firstPresentValue($row, ['unitOfMeasure', 'unit_of_measure', 'uom']),
            'tax_rate_id' => $this->firstPresentValue($row, ['taxRateId', 'tax_rate_id', 'taxCategoryId', 'tax_category_id', 'productTaxCategory', 'taxCategory']),
            'quantity_in_stock' => is_numeric($quantity) ? (float)$quantity : 0,
            'raw_payload' => $row,
        ];
    }

    /**
     * Case-insensitive lookup across a candidate list of field-name spellings. EIS's actual
     * casing for /stock/warehouse-inventory isn't confirmed anywhere (Swagger is known to
     * disagree with the live API for other endpoints - see reference notes), so this checks
     * each candidate against every key in the row regardless of case rather than requiring an
     * exact match. Deliberately NOT a substring/fuzzy match - a loose match on a field like
     * "quantity" could just as easily hit an unrelated numeric field (e.g. a stock threshold or
     * an ID) and silently create the wrong number of physical items.
     */
    private function firstPresentValue(array $row, array $keys) {
        $lowerRow = [];
        foreach ($row as $key => $value) {
            $lowerRow[strtolower((string)$key)] = $value;
        }

        foreach ($keys as $key) {
            $lowerKey = strtolower($key);
            if (array_key_exists($lowerKey, $lowerRow) && $lowerRow[$lowerKey] !== null && $lowerRow[$lowerKey] !== '') {
                return $lowerRow[$lowerKey];
            }
        }

        return null;
    }

    private function saveWarehouseStockRow(array $fields) {
        $stmt = $this->conn->prepare("
            INSERT INTO mra_eis_warehouse_stock (
                barcode, product_name, description, unit_price, unit_of_measure, quantity_in_stock, raw_payload, synced_at
            ) VALUES (
                :barcode, :product_name, :description, :unit_price, :unit_of_measure, :quantity_in_stock, :raw_payload, NOW()
            )
            ON DUPLICATE KEY UPDATE
                product_name = VALUES(product_name),
                description = VALUES(description),
                unit_price = VALUES(unit_price),
                unit_of_measure = VALUES(unit_of_measure),
                quantity_in_stock = VALUES(quantity_in_stock),
                raw_payload = VALUES(raw_payload),
                synced_at = NOW()
        ");
        $stmt->execute([
            ':barcode' => $fields['barcode'],
            ':product_name' => $fields['product_name'],
            ':description' => $fields['description'],
            ':unit_price' => $fields['unit_price'],
            ':unit_of_measure' => $fields['unit_of_measure'],
            ':quantity_in_stock' => $fields['quantity_in_stock'],
            ':raw_payload' => json_encode($fields['raw_payload']),
        ]);

        $idStmt = $this->conn->prepare("SELECT id FROM mra_eis_warehouse_stock WHERE barcode = :barcode LIMIT 1");
        $idStmt->execute([':barcode' => $fields['barcode']]);
        return (int)$idStmt->fetchColumn();
    }

    /** eis_product_code -> ims_product_code via the existing product code map; falls back to treating the barcode as the IMS product_code directly (the convention items.product_code already follows for most rows). */
    private function resolveIMSProductCode($barcode) {
        $stmt = $this->conn->prepare("
            SELECT ims_product_code FROM mra_eis_product_code_map
            WHERE eis_product_code = :barcode
            LIMIT 1
        ");
        $stmt->execute([':barcode' => $barcode]);
        $mapped = $stmt->fetchColumn();
        return ($mapped !== false && trim((string)$mapped) !== '') ? trim((string)$mapped) : $barcode;
    }

    /**
     * Creates or removes pieces owned by this sync (source_eis_warehouse_stock_id = $stockId) so
     * that (organic items already at the branch) + (sync-owned items) equals $targetQty. Organic
     * items - anything not created by this sync (manually received, imported, etc.) - are counted
     * but never created or deleted, so a mismatch there is surfaced as a variance for manual
     * review instead of silently altering stock this sync doesn't own. Sold items and items no
     * longer at this branch (current_location_id filter) are structurally excluded, which is what
     * keeps this from ever touching another branch's stock.
     */
    private function reconcileWarehouseItems($stockId, $productCode, array $fields, $branchId, $targetQty) {
        $this->conn->beginTransaction();
        try {
            $seqStmt = $this->conn->prepare("
                SELECT i.source_eis_sequence_number
                FROM items i
                WHERE i.source_eis_warehouse_stock_id = :stock_id
                  AND i.current_location_id = :branch_id
                  AND NOT EXISTS (SELECT 1 FROM sold_items s WHERE s.unique_code = i.unique_code)
            ");
            $seqStmt->execute([':stock_id' => $stockId, ':branch_id' => $branchId]);
            $existingSeqs = array_map('intval', $seqStmt->fetchAll(PDO::FETCH_COLUMN));
            $maxSeq = $existingSeqs ? max($existingSeqs) : 0;
            $syncOwnedCount = count($existingSeqs);

            $organicStmt = $this->conn->prepare("
                SELECT COUNT(*)
                FROM items i
                WHERE i.product_code = :product_code
                  AND i.current_location_id = :branch_id
                  AND i.source_eis_warehouse_stock_id IS NULL
                  AND NOT EXISTS (SELECT 1 FROM sold_items s WHERE s.unique_code = i.unique_code)
            ");
            $organicStmt->execute([':product_code' => $productCode, ':branch_id' => $branchId]);
            $organicCount = (int)$organicStmt->fetchColumn();

            $needed = $targetQty - ($organicCount + $syncOwnedCount);
            $created = 0;
            $removed = 0;

            if ($needed > 0) {
                $insertStmt = $this->conn->prepare("
                    INSERT INTO items (
                        unique_code, item_name, product_code, description, comment,
                        shelf_status, unit_price, current_location_id, tax_rate_id, eis_site_product_id,
                        source_eis_warehouse_stock_id, source_eis_sequence_number
                    ) VALUES (
                        :unique_code, :item_name, :product_code, :description, :comment,
                        'on_shelf', :unit_price, :branch_id, :tax_rate_id, :eis_site_product_id,
                        :stock_id, :sequence_number
                    )
                ");

                $seq = $maxSeq;
                for ($i = 0; $i < $needed; $i++) {
                    $seq++;
                    $insertStmt->execute([
                        ':unique_code' => 'EIS-' . $branchId . '-' . $stockId . '-' . str_pad((string)$seq, 5, '0', STR_PAD_LEFT),
                        ':item_name' => $fields['product_name'],
                        ':product_code' => $productCode,
                        ':description' => $fields['description'],
                        ':comment' => 'Synced from MRA EIS ' . ($fields['source_label'] ?? 'warehouse stock') . ' (barcode ' . $fields['barcode'] . ')',
                        ':unit_price' => $fields['unit_price'],
                        ':branch_id' => $branchId,
                        ':tax_rate_id' => trim((string)($fields['tax_rate_id'] ?? '')) ?: null,
                        ':eis_site_product_id' => !empty($fields['eis_site_product_id']) ? (int)$fields['eis_site_product_id'] : null,
                        ':stock_id' => $stockId,
                        ':sequence_number' => $seq,
                    ]);
                    $created++;
                }
            } elseif ($needed < 0) {
                $toRemove = min(abs($needed), $syncOwnedCount);
                if ($toRemove > 0) {
                    rsort($existingSeqs);
                    $removeSeqs = array_slice($existingSeqs, 0, $toRemove);

                    $deleteStmt = $this->conn->prepare("
                        DELETE i FROM items i
                        WHERE i.source_eis_warehouse_stock_id = :stock_id
                          AND i.current_location_id = :branch_id
                          AND i.source_eis_sequence_number = :seq
                          AND NOT EXISTS (SELECT 1 FROM sold_items s WHERE s.unique_code = i.unique_code)
                    ");
                    foreach ($removeSeqs as $seq) {
                        $deleteStmt->execute([':stock_id' => $stockId, ':branch_id' => $branchId, ':seq' => $seq]);
                        $removed += $deleteStmt->rowCount();
                    }
                }
            }

            $finalTotal = $organicCount + ($syncOwnedCount + $created - $removed);

            $this->conn->commit();

            return [
                'created' => $created,
                'removed' => $removed,
                'variance' => $finalTotal !== $targetQty,
                'organic_quantity' => $organicCount,
                'sync_owned_quantity' => $syncOwnedCount + $created - $removed,
            ];
        } catch (Exception $e) {
            $this->conn->rollBack();
            throw $e;
        }
    }

    /**
     * Diagnoses why warehouse-inventory calls are failing for a branch: shows which
     * terminal(s) would be picked by warehouseInventoryTerminal() (masked, no secrets),
     * then runs a lightweight /utilities/ping auth check plus a 1-row warehouse-inventory
     * call against the resolved terminal so we can tell an auth/environment problem
     * (ping also fails) apart from an endpoint-specific MRA-side problem (ping succeeds,
     * warehouse-inventory still errors).
     */
    public function warehouseTerminalDiagnostics($branchId = 7) {
        $candidates = $this->activatedTerminalsRaw($branchId);
        $usedFallback = false;
        if (empty($candidates)) {
            $candidates = $this->activatedTerminalsRaw(null);
            $usedFallback = true;
        }

        $resolved = $candidates[0] ?? null;
        $pingResult = $resolved ? $this->pingTerminalToken($resolved['api_base_url'], $resolved['jwt_token']) : null;
        $warehouseResult = $resolved ? $this->warehouseInventory(1, 1, $branchId) : null;

        return [
            'branch_id' => $branchId,
            'used_fallback' => $usedFallback,
            'candidates' => array_map([$this, 'maskTerminalRow'], $candidates),
            'resolved' => $resolved ? $this->maskTerminalRow($resolved) : null,
            'ping' => $pingResult,
            'warehouse_test' => $warehouseResult,
        ];
    }

    /** Same auth strategy as every other call in this class (Bearer, then raw) hitting /utilities/ping. */
    public function pingTerminalToken($apiBaseUrl, $jwtToken) {
        $baseUrl = rtrim((string)$apiBaseUrl, '/');
        $token = trim((string)$jwtToken);
        $url = $baseUrl . '/utilities/ping';

        $attempts = [
            ['method' => 'Bearer authorization token', 'headers' => ['Authorization: Bearer ' . $token]],
            ['method' => 'Raw authorization token', 'headers' => ['Authorization: ' . $token]],
        ];

        $results = [];
        foreach ($attempts as $attempt) {
            $response = $this->postJson($url, [], $attempt['headers']);
            $results[] = [
                'method' => $attempt['method'],
                'status' => $response['status'],
                'body' => substr((string)$response['body'], 0, 1000),
            ];
            if ($response['status'] >= 200 && $response['status'] < 300) {
                break;
            }
        }

        return ['url' => $url, 'attempts' => $results];
    }

    private function activatedTerminalsRaw($branchId) {
        $sql = "
            SELECT met.*, b.branch_name
            FROM mra_eis_terminals met
            LEFT JOIN branches b ON b.branch_id = met.branch_id
            WHERE met.status = 'activated'
              AND met.jwt_token IS NOT NULL
              AND met.jwt_token != ''
        ";
        $params = [];

        if ($branchId !== null && $branchId !== '') {
            $sql .= " AND met.branch_id = :branch_id";
            $params[':branch_id'] = (int)$branchId;
        }

        $sql .= " ORDER BY (met.environment = 'production') DESC, met.updated_at DESC, met.id DESC";
        $stmt = $this->conn->prepare($sql);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value, PDO::PARAM_INT);
        }
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function maskTerminalRow(array $row) {
        $token = trim((string)($row['jwt_token'] ?? ''));
        $row['jwt_length'] = strlen($token);
        $row['jwt_preview'] = $token === '' ? '' : substr($token, 0, 12) . '...' . substr($token, -8);
        unset($row['jwt_token'], $row['secret_key'], $row['activation_response'], $row['confirmation_response']);
        return $row;
    }

    public function tableColumns(array $rows) {
        $columns = [];
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            foreach ($row as $key => $value) {
                if (!in_array($key, $columns, true)) {
                    $columns[] = $key;
                }
            }
        }

        return $columns;
    }

    public function displayValue($value) {
        if (is_array($value)) {
            return json_encode($value, JSON_UNESCAPED_SLASHES);
        }

        if (is_bool($value)) {
            return $value ? 'true' : 'false';
        }

        return (string)$value;
    }

    public function unmappedItems($branchId = null, $search = '', $limit = 100) {
        $sql = "
            SELECT MIN(ps.id) AS item_id,
                   MIN(ps.barcode) AS unique_code,
                   ps.barcode AS product_code,
                   ps.product_name AS item_name,
                   MIN(ps.product_description) AS category,
                   NULL AS brand,
                   NULL AS model,
                   0 AS unit_price,
                   MIN(ps.branch_id) AS current_location_id,
                   MIN(b.branch_name) AS branch_name,
                   SUM(ps.available_quantity) AS ims_item_count
            FROM product_stock ps
            LEFT JOIN branches b ON b.branch_id = ps.branch_id
            LEFT JOIN mra_eis_product_code_map map ON map.ims_product_code = ps.barcode
            WHERE map.id IS NULL
              AND ps.barcode IS NOT NULL
              AND ps.barcode != ''
        ";
        $params = [];

        if ($branchId !== null && $branchId !== '') {
            $sql .= " AND ps.branch_id = :branch_id";
            $params[':branch_id'] = (int)$branchId;
        }

        $search = trim((string)$search);
        if ($search !== '') {
            $sql .= " AND (ps.barcode LIKE :search OR ps.product_name LIKE :search OR ps.product_description LIKE :search OR ps.branch_name LIKE :search)";
            $params[':search'] = '%' . $search . '%';
        }

        $sql .= " GROUP BY ps.barcode, ps.product_name ORDER BY ps.product_name, ps.barcode LIMIT " . max(1, min(500, (int)$limit));
        $stmt = $this->conn->prepare($sql);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
        }
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function mappedItems($search = '', $limit = 100) {
        $sql = "
            SELECT map.*,
                   MIN(ps.id) AS item_id,
                   COALESCE(msp.product_code, map.eis_product_code) AS product_code,
                   COALESCE(msp.product_name, map.eis_product_name) AS product_name,
                   COALESCE(msp.tax_rate_id, map.tax_rate_id) AS tax_rate_id,
                   MIN(ps.product_name) AS item_name,
                   MIN(ps.product_description) AS category,
                   NULL AS brand,
                   NULL AS model,
                   0 AS unit_price,
                   MIN(ps.barcode) AS unique_code,
                   SUM(ps.available_quantity) AS ims_item_count,
                   MIN(b.branch_name) AS branch_name,
                   met.terminal_label,
                   msp.site_id
            FROM mra_eis_product_code_map map
            LEFT JOIN product_stock ps ON ps.barcode = map.ims_product_code
            LEFT JOIN branches b ON b.branch_id = ps.branch_id
            LEFT JOIN mra_eis_terminals met ON met.id = map.terminal_local_id
            LEFT JOIN mra_eis_site_products msp ON msp.id = map.site_product_id
            WHERE 1 = 1
        ";
        $params = [];

        $search = trim((string)$search);
        if ($search !== '') {
            $sql .= " AND (map.ims_product_code LIKE :search OR ps.product_name LIKE :search OR ps.product_description LIKE :search OR map.eis_product_code LIKE :search OR map.eis_product_name LIKE :search)";
            $params[':search'] = '%' . $search . '%';
        }

        $sql .= " GROUP BY map.id ORDER BY map.updated_at DESC, map.id DESC LIMIT " . max(1, min(500, (int)$limit));
        $stmt = $this->conn->prepare($sql);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
        }
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function reattachProductCodeMap($mappingId, $siteProductId, $userId = null) {
        $mappingId = $this->requiredInteger($mappingId, 'existing EIS mapping');
        $siteProductId = $this->requiredInteger($siteProductId, 'MRA site product');

        $mapStmt = $this->conn->prepare("SELECT * FROM mra_eis_product_code_map WHERE id = :id LIMIT 1");
        $mapStmt->execute([':id' => $mappingId]);
        $map = $mapStmt->fetch(PDO::FETCH_ASSOC);
        if (!$map) {
            throw new RuntimeException('The existing EIS product mapping was not found.');
        }

        $productStmt = $this->conn->prepare("
            SELECT id, terminal_local_id, product_code, product_name, tax_rate_id, levy_rate_id
            FROM mra_eis_site_products WHERE id = :id LIMIT 1
        ");
        $productStmt->execute([':id' => $siteProductId]);
        $product = $productStmt->fetch(PDO::FETCH_ASSOC);
        if (!$product) {
            throw new RuntimeException('The selected MRA site product was not found. Download site products first.');
        }

        $this->conn->beginTransaction();
        try {
            $updateMap = $this->conn->prepare("
                UPDATE mra_eis_product_code_map
                SET terminal_local_id = :terminal_local_id, site_product_id = :site_product_id,
                    eis_product_code = :product_code, eis_product_name = :product_name,
                    tax_rate_id = :tax_rate_id, levy_rate_id = :levy_rate_id, mapped_by = :mapped_by
                WHERE id = :id
            ");
            $updateMap->execute([
                ':terminal_local_id' => (int)$product['terminal_local_id'],
                ':site_product_id' => (int)$product['id'],
                ':product_code' => (string)$product['product_code'],
                ':product_name' => (string)$product['product_name'],
                ':tax_rate_id' => $product['tax_rate_id'] ?? null,
                ':levy_rate_id' => $product['levy_rate_id'] ?? null,
                ':mapped_by' => $userId ? (int)$userId : null,
                ':id' => $mappingId,
            ]);

            $updateItems = $this->conn->prepare("
                UPDATE items
                SET product_code = :new_product_code,
                    eis_site_product_id = :site_product_id
                WHERE item_name = :item_name
                  AND (product_code = :old_product_code OR product_code = :ims_product_code)
            ");
            $updateItems->execute([
                ':new_product_code' => (string)$product['product_code'],
                ':site_product_id' => (int)$product['id'],
                ':old_product_code' => (string)$map['eis_product_code'],
                ':item_name' => (string)$map['ims_item_name'],
                ':ims_product_code' => (string)$map['ims_product_code'],
            ]);

            $updateItemMaps = $this->conn->prepare("
                UPDATE mra_eis_item_product_map map
                INNER JOIN items i ON i.item_id = map.item_id
                SET map.terminal_local_id = :terminal_local_id, map.site_product_id = :site_product_id,
                    map.product_code = :product_code, map.product_name = :product_name,
                    map.tax_rate_id = :tax_rate_id, map.levy_rate_id = :levy_rate_id, map.mapped_by = :mapped_by
                WHERE i.item_name = :item_name
                  AND (map.product_code = :old_product_code OR map.unique_code = :ims_product_code)
            ");
            $updateItemMaps->execute([
                ':terminal_local_id' => (int)$product['terminal_local_id'],
                ':site_product_id' => (int)$product['id'],
                ':product_code' => (string)$product['product_code'],
                ':product_name' => (string)$product['product_name'],
                ':tax_rate_id' => $product['tax_rate_id'] ?? null,
                ':levy_rate_id' => $product['levy_rate_id'] ?? null,
                ':mapped_by' => $userId ? (int)$userId : null,
                ':old_product_code' => (string)$map['eis_product_code'],
                ':ims_product_code' => (string)$map['ims_product_code'],
                ':item_name' => (string)$map['ims_item_name'],
            ]);

            $this->conn->commit();
        } catch (Throwable $e) {
            if ($this->conn->inTransaction()) {
                $this->conn->rollBack();
            }
            throw $e;
        }
    }
    public function mapItemToSiteProduct($itemId, $siteProductId, $userId = null) {
        $itemId = $this->requiredInteger($itemId, 'product stock row');
        $siteProductId = $this->requiredInteger($siteProductId, 'MRA site product');

        $itemStmt = $this->conn->prepare("
            SELECT id AS item_id,
                   barcode AS unique_code,
                   product_name AS item_name,
                   barcode AS product_code
            FROM product_stock
            WHERE id = :item_id
            LIMIT 1
        ");
        $itemStmt->execute([':item_id' => $itemId]);
        $item = $itemStmt->fetch(PDO::FETCH_ASSOC);
        if (!$item) {
            throw new RuntimeException('The selected product stock row was not found.');
        }

        $existingMapStmt = $this->conn->prepare("
            SELECT eis_product_code FROM mra_eis_product_code_map
            WHERE ims_product_code = :ims_product_code LIMIT 1
        ");
        $existingMapStmt->execute([':ims_product_code' => (string)$item['product_code']]);
        $previousEisProductCode = (string)($existingMapStmt->fetchColumn() ?: '');

        $productStmt = $this->conn->prepare("
            SELECT id, terminal_local_id, product_code, product_name, tax_rate_id, levy_rate_id
            FROM mra_eis_site_products
            WHERE id = :site_product_id
            LIMIT 1
        ");
        $productStmt->execute([':site_product_id' => $siteProductId]);
        $product = $productStmt->fetch(PDO::FETCH_ASSOC);
        if (!$product) {
            throw new RuntimeException('The selected MRA site product was not found. Download site products first.');
        }

        $productMapStmt = $this->conn->prepare("
            INSERT INTO mra_eis_product_code_map (
                ims_product_code, ims_item_name, terminal_local_id, site_product_id,
                eis_product_code, eis_product_name, tax_rate_id, levy_rate_id, mapped_by
            ) VALUES (
                :ims_product_code, :ims_item_name, :terminal_local_id, :site_product_id,
                :eis_product_code, :eis_product_name, :tax_rate_id, :levy_rate_id, :mapped_by
            )
            ON DUPLICATE KEY UPDATE
                ims_item_name = VALUES(ims_item_name),
                terminal_local_id = VALUES(terminal_local_id),
                site_product_id = VALUES(site_product_id),
                eis_product_code = VALUES(eis_product_code),
                eis_product_name = VALUES(eis_product_name),
                tax_rate_id = VALUES(tax_rate_id),
                levy_rate_id = VALUES(levy_rate_id),
                mapped_by = VALUES(mapped_by)
        ");
        $productMapStmt->execute([
            ':ims_product_code' => (string)$item['product_code'],
            ':ims_item_name' => (string)$item['item_name'],
            ':terminal_local_id' => (int)$product['terminal_local_id'],
            ':site_product_id' => (int)$product['id'],
            ':eis_product_code' => (string)$product['product_code'],
            ':eis_product_name' => (string)$product['product_name'],
            ':tax_rate_id' => $product['tax_rate_id'] ?? null,
            ':levy_rate_id' => $product['levy_rate_id'] ?? null,
            ':mapped_by' => $userId ? (int)$userId : null,
        ]);

        $updateItems = $this->conn->prepare("
            UPDATE items
            SET product_code = :eis_product_code
            WHERE item_name = :item_name
              AND (
                  product_code IS NULL
                  OR product_code = ''
                  OR product_code LIKE 'IMS-%'
                  OR product_code = :source_product_code
                  OR product_code = :previous_eis_product_code
                  OR technical_specs LIKE :legacy_barcode
              )
        ");
        $updateItems->execute([
            ':eis_product_code' => (string)$product['product_code'],
            ':item_name' => (string)$item['item_name'],
            ':source_product_code' => (string)$item['product_code'],
            ':previous_eis_product_code' => $previousEisProductCode,
            ':legacy_barcode' => '% barcode: ' . (string)$item['product_code'],
        ]);

        $syncItemMaps = $this->conn->prepare("
            UPDATE mra_eis_item_product_map map
            INNER JOIN items i ON i.item_id = map.item_id
            SET map.terminal_local_id = :terminal_local_id, map.site_product_id = :site_product_id,
                map.product_code = :eis_product_code, map.product_name = :eis_product_name,
                map.tax_rate_id = :tax_rate_id, map.levy_rate_id = :levy_rate_id, map.mapped_by = :mapped_by
            WHERE i.item_name = :item_name
              AND (map.product_code = :previous_eis_product_code OR map.unique_code = :source_product_code)
        ");
        $syncItemMaps->execute([
            ':terminal_local_id' => (int)$product['terminal_local_id'],
            ':site_product_id' => (int)$product['id'],
            ':eis_product_code' => (string)$product['product_code'],
            ':eis_product_name' => (string)$product['product_name'],
            ':tax_rate_id' => $product['tax_rate_id'] ?? null,
            ':levy_rate_id' => $product['levy_rate_id'] ?? null,
            ':mapped_by' => $userId ? (int)$userId : null,
            ':item_name' => (string)$item['item_name'],
            ':previous_eis_product_code' => $previousEisProductCode,
            ':source_product_code' => (string)$item['product_code'],
        ]);
    }

    // ─── Batch mapping (items.eis_site_product_id) ───────────────────────────

    /**
     * Returns distinct (product_code, branch) batches from the items table
     * that have no eis_site_product_id set yet and have not been sold.
     * Groups by product_code + current_location_id so one action maps the
     * entire batch rather than each individual item.
     */
    public function unmappedItemBatches($branchId = null, $search = '', $limit = 100) {
        $sql = "
            SELECT  i.product_code,
                    i.current_location_id AS branch_id,
                    b.branch_name,
                    MIN(i.item_name)  AS item_name,
                    COUNT(*)          AS total_items,
                    SUM(CASE WHEN si.sale_id IS NULL THEN 1 ELSE 0 END) AS unsold_items
            FROM   `items` i
            LEFT JOIN `branches`    b  ON b.branch_id  = i.current_location_id
            LEFT JOIN `sold_items`  si ON si.unique_code = i.unique_code
            WHERE  i.eis_site_product_id IS NULL
              AND  i.product_code        IS NOT NULL
              AND  i.product_code        != ''
        ";
        $params = [];

        if ($branchId !== null && $branchId !== '') {
            $sql .= " AND i.current_location_id = :branch_id";
            $params[':branch_id'] = (int)$branchId;
        }

        $search = trim((string)$search);
        if ($search !== '') {
            $sql .= " AND (i.product_code LIKE :search OR i.item_name LIKE :search OR b.branch_name LIKE :search)";
            $params[':search'] = '%' . $search . '%';
        }

        $sql .= "
            GROUP BY i.product_code, i.current_location_id, b.branch_name
            HAVING   unsold_items > 0
            ORDER BY b.branch_name, MIN(i.item_name)
            LIMIT " . max(1, min(500, (int)$limit));

        $stmt = $this->conn->prepare($sql);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
        }
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Returns distinct (product_code, branch) batches that already have
     * eis_site_product_id set, along with EIS product details.
     */
    public function mappedItemBatches($search = '', $limit = 100) {
        $sql = "
            SELECT  i.product_code,
                    i.current_location_id   AS branch_id,
                    b.branch_name,
                    MIN(i.item_name)        AS item_name,
                    COUNT(*)                AS total_items,
                    i.eis_site_product_id,
                    sp.product_name         AS eis_product_name,
                    sp.price                AS eis_price,
                    sp.quantity             AS eis_quantity,
                    met.terminal_label
            FROM   `items` i
            LEFT JOIN `branches`              b   ON b.branch_id    = i.current_location_id
            LEFT JOIN `mra_eis_site_products` sp  ON sp.id          = i.eis_site_product_id
            LEFT JOIN `mra_eis_terminals`     met ON met.id         = sp.terminal_local_id
            WHERE  i.eis_site_product_id IS NOT NULL
              AND  i.product_code        IS NOT NULL
              AND  i.product_code        != ''
        ";
        $params = [];

        $search = trim((string)$search);
        if ($search !== '') {
            $sql .= " AND (i.product_code LIKE :search OR i.item_name LIKE :search OR b.branch_name LIKE :search OR sp.product_name LIKE :search)";
            $params[':search'] = '%' . $search . '%';
        }

        $sql .= "
            GROUP BY i.product_code, i.current_location_id, i.eis_site_product_id,
                     b.branch_name, sp.product_name, sp.price, sp.quantity, met.terminal_label
            ORDER BY b.branch_name, MIN(i.item_name)
            LIMIT " . max(1, min(500, (int)$limit));

        $stmt = $this->conn->prepare($sql);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
        }
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Stamps eis_site_product_id on every unsold item in a batch
     * (matched by product_code + branch). Also updates or inserts a row
     * in mra_eis_product_code_map so the price-tier mapping is persisted.
     *
     * @param string $productCode     items.product_code for the batch
     * @param int    $locationId      items.current_location_id (branch)
     * @param int    $siteProductId   mra_eis_site_products.id to assign
     * @param int|null $userId
     * @return int  Number of items stamped
     */
    public function stampBatchEisSiteProduct($productCode, $locationId, $siteProductId, $userId = null) {
        $productCode  = trim((string)$productCode);
        $locationId   = $this->requiredInteger($locationId,   'branch');
        $siteProductId = $this->requiredInteger($siteProductId, 'EIS site product');

        // Validate the EIS product row exists
        $spStmt = $this->conn->prepare("
            SELECT id, terminal_local_id, product_code, product_name, tax_rate_id, levy_rate_id, price
            FROM   `mra_eis_site_products`
            WHERE  id = :id
            LIMIT 1
        ");
        $spStmt->execute([':id' => $siteProductId]);
        $sp = $spStmt->fetch(PDO::FETCH_ASSOC);
        if (!$sp) {
            throw new RuntimeException('The selected EIS site product was not found.');
        }

        // Stamp all unsold items in this batch
        $stampStmt = $this->conn->prepare("
            UPDATE `items` i
            SET    i.eis_site_product_id = :eis_id
            WHERE  i.product_code        = :product_code
              AND  i.current_location_id = :location_id
              AND  NOT EXISTS (
                       SELECT 1 FROM `sold_items` s WHERE s.unique_code = i.unique_code
                   )
        ");
        $stampStmt->execute([
            ':eis_id'       => $siteProductId,
            ':product_code' => $productCode,
            ':location_id'  => $locationId,
        ]);
        $stamped = (int)$stampStmt->rowCount();

        // Keep mra_eis_product_code_map in sync (upsert with selling_price)
        $itemNameStmt = $this->conn->prepare("
            SELECT MIN(item_name) FROM `items`
            WHERE  product_code = :pc AND current_location_id = :loc
            LIMIT 1
        ");
        $itemNameStmt->execute([':pc' => $productCode, ':loc' => $locationId]);
        $imsItemName = (string)($itemNameStmt->fetchColumn() ?: $productCode);

        $mapStmt = $this->conn->prepare("
            INSERT INTO `mra_eis_product_code_map`
                (ims_product_code, ims_item_name, terminal_local_id, site_product_id,
                 eis_product_code, eis_product_name, selling_price,
                 tax_rate_id, levy_rate_id, mapped_by)
            VALUES
                (:ims_product_code, :ims_item_name, :terminal_local_id, :site_product_id,
                 :eis_product_code, :eis_product_name, :selling_price,
                 :tax_rate_id, :levy_rate_id, :mapped_by)
            ON DUPLICATE KEY UPDATE
                ims_item_name     = VALUES(ims_item_name),
                site_product_id   = VALUES(site_product_id),
                eis_product_code  = VALUES(eis_product_code),
                eis_product_name  = VALUES(eis_product_name),
                selling_price     = VALUES(selling_price),
                tax_rate_id       = VALUES(tax_rate_id),
                levy_rate_id      = VALUES(levy_rate_id),
                mapped_by         = VALUES(mapped_by)
        ");
        $mapStmt->execute([
            ':ims_product_code'  => $productCode,
            ':ims_item_name'     => $imsItemName,
            ':terminal_local_id' => (int)$sp['terminal_local_id'],
            ':site_product_id'   => $siteProductId,
            ':eis_product_code'  => $sp['product_code'],
            ':eis_product_name'  => $sp['product_name'],
            ':selling_price'     => (float)$sp['price'],
            ':tax_rate_id'       => $sp['tax_rate_id'] ?? null,
            ':levy_rate_id'      => $sp['levy_rate_id'] ?? null,
            ':mapped_by'         => $userId ? (int)$userId : null,
        ]);

        return $stamped;
    }

    public function prepareSalesQueue($branchId = null, $startDate = null, $endDate = null, $limit = 100) {
        $sql = "
            SELECT si.transaction_id, si.branch_id, b.branch_name,
                   MAX(si.sold_by) AS sold_by,
                   MIN(si.sold_at) AS sold_at,
                   MAX(si.customer_name) AS customer_name,
                   MAX(si.customer_phone) AS customer_phone,
                   COUNT(*) AS item_count,
                   SUM(si.total_price) AS total_amount
            FROM sold_items si
            LEFT JOIN branches b ON b.branch_id = si.branch_id
            LEFT JOIN mra_eis_sales_queue q ON q.transaction_id = si.transaction_id AND q.status = 'submitted'
            WHERE si.transaction_id IS NOT NULL
              AND si.transaction_id != ''
              AND q.id IS NULL
        ";
        $params = [];

        if ($branchId !== null && $branchId !== '') {
            $sql .= " AND si.branch_id = :branch_id";
            $params[':branch_id'] = (int)$branchId;
        }
        if ($startDate !== null && $startDate !== '') {
            $sql .= " AND DATE(si.sold_at) >= :start_date";
            $params[':start_date'] = $startDate;
        }
        if ($endDate !== null && $endDate !== '') {
            $sql .= " AND DATE(si.sold_at) <= :end_date";
            $params[':end_date'] = $endDate;
        }

        $sql .= " GROUP BY si.transaction_id, si.branch_id ORDER BY sold_at DESC LIMIT " . max(1, min(500, (int)$limit));
        $stmt = $this->conn->prepare($sql);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
        }
        $stmt->execute();
        $transactions = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $prepared = 0;
        $blocked = 0;
        foreach ($transactions as $transaction) {
            $summary = $this->prepareSalesTransaction($transaction);
            $prepared++;
            if ($summary['status'] === 'blocked') {
                $blocked++;
            }
        }

        return [
            'prepared' => $prepared,
            'blocked' => $blocked,
            'ready' => $prepared - $blocked,
        ];
    }

    public function salesQueue($status = '', $branchId = null, $limit = 100) {
        $sql = "
            SELECT q.*, b.branch_name, met.terminal_label,
                   COUNT(ql.id) AS line_count
            FROM mra_eis_sales_queue q
            LEFT JOIN branches b ON b.branch_id = q.branch_id
            LEFT JOIN mra_eis_terminals met ON met.id = q.terminal_local_id
            LEFT JOIN mra_eis_sales_queue_lines ql ON ql.queue_id = q.id
            WHERE 1 = 1
        ";
        $params = [];

        $status = trim((string)$status);
        if ($status !== '') {
            $sql .= " AND q.status = :status";
            $params[':status'] = $status;
        }
        if ($branchId !== null && $branchId !== '') {
            $sql .= " AND q.branch_id = :branch_id";
            $params[':branch_id'] = (int)$branchId;
        }

        $sql .= " GROUP BY q.id ORDER BY q.sold_at DESC, q.id DESC LIMIT " . max(1, min(500, (int)$limit));
        $stmt = $this->conn->prepare($sql);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
        }
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function salesQueueLines($queueId) {
        $stmt = $this->conn->prepare("
            SELECT *
            FROM mra_eis_sales_queue_lines
            WHERE queue_id = :queue_id
            ORDER BY product_name, unit_price
        ");
        $stmt->execute([':queue_id' => (int)$queueId]);

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function computeXSignature($activationCode, $secretKey) {
        return base64_encode(hash_hmac('sha512', trim((string)$activationCode), trim((string)$secretKey), true));
    }

    public function confirmationSignatureAttempts(array $terminal) {
        $hmacSignature = $this->computeXSignature($terminal['terminal_activation_code'] ?? '', $terminal['secret_key'] ?? '');
        $jwtToken = trim((string)($terminal['jwt_token'] ?? ''));

        $attempts = [[
            'method' => 'HMAC-SHA512 Base64 x-signature',
            'signature' => $hmacSignature,
            'headers' => ['x-signature: ' . $hmacSignature],
        ]];

        if ($jwtToken !== '') {
            $attempts[] = [
                'method' => 'HMAC-SHA512 x-signature with Bearer JWT',
                'signature' => $hmacSignature,
                'headers' => [
                    'x-signature: ' . $hmacSignature,
                    'Authorization: Bearer ' . $jwtToken,
                ],
            ];

            if ($jwtToken !== $hmacSignature) {
                $attempts[] = [
                    'method' => 'activation JWT token x-signature fallback',
                    'signature' => $jwtToken,
                    'headers' => ['x-signature: ' . $jwtToken],
                ];
                $attempts[] = [
                    'method' => 'activation JWT x-signature with Bearer JWT',
                    'signature' => $jwtToken,
                    'headers' => [
                        'x-signature: ' . $jwtToken,
                        'Authorization: Bearer ' . $jwtToken,
                    ],
                ];
            }
        }

        return $attempts;
    }

    public function hmacSelfTestPassed() {
        return $this->computeXSignature('MRA', '123456') === 'xludP1OafF422HgSRaKqZiUXaFALv8D+mnBJOWd5vDK7N7T22V+WOTvgIFQ7I1p+S2cIPg3JxuVm4xth+8UQ/Q==';
    }

    private function prepareSalesTransaction(array $transaction) {
        $terminalId = $this->userAssignedTerminal($transaction['sold_by'] ?? null, $transaction['branch_id'] ?? null);

        $lineStmt = $this->conn->prepare("
            SELECT si.sale_id, si.item_id, si.unique_code, si.unit_price, si.total_price,
                   NULLIF(TRIM(i.product_code), '') AS product_code,
                   COALESCE(map.product_name, i.item_name) AS product_name,
                   COALESCE(map.tax_rate_id, 'A') AS tax_rate_id,
                   map.terminal_local_id AS terminal_local_id
            FROM sold_items si
            LEFT JOIN items i ON i.item_id = si.item_id
            LEFT JOIN mra_eis_item_product_map map ON map.item_id = si.item_id
            WHERE si.transaction_id = :transaction_id
              AND si.branch_id = :branch_id
            ORDER BY si.sale_id
        ");
        $lineStmt->execute([
            ':transaction_id' => $transaction['transaction_id'],
            ':branch_id' => (int)$transaction['branch_id'],
        ]);
        $soldLines = $lineStmt->fetchAll(PDO::FETCH_ASSOC);

        $missingMappings = 0;
        $grouped = [];

        foreach ($soldLines as $line) {
            if (trim((string)($line['product_code'] ?? '')) === '' || stripos((string)$line['product_code'], 'IMS-') === 0) {
                $missingMappings++;
                continue;
            }

            if ($terminalId === null && !empty($line['terminal_local_id'])) {
                $terminalId = (int)$line['terminal_local_id'];
            }

            $unitPrice = (float)$line['unit_price'];
            $key = $line['product_code'] . '|' . $unitPrice . '|' . ($line['tax_rate_id'] ?? '');
            if (!isset($grouped[$key])) {
                $grouped[$key] = [
                    'product_code' => (string)$line['product_code'],
                    'product_name' => (string)$line['product_name'],
                    'tax_rate_id' => $line['tax_rate_id'] ?? null,
                    'quantity' => 0,
                    'unit_price' => $unitPrice,
                    'line_total' => 0,
                    'unique_codes' => [],
                ];
            }

            $grouped[$key]['quantity']++;
            $grouped[$key]['line_total'] += (float)$line['total_price'];
            $grouped[$key]['unique_codes'][] = (string)$line['unique_code'];
        }

        $status = $missingMappings > 0 ? 'blocked' : 'ready';

        $queueStmt = $this->conn->prepare("
            INSERT INTO mra_eis_sales_queue (
                transaction_id, branch_id, terminal_local_id, customer_name, customer_phone,
                sold_at, item_count, total_amount, status, missing_mappings, prepared_at
            ) VALUES (
                :transaction_id, :branch_id, :terminal_local_id, :customer_name, :customer_phone,
                :sold_at, :item_count, :total_amount, :status, :missing_mappings, NOW()
            )
            ON DUPLICATE KEY UPDATE
                branch_id = VALUES(branch_id),
                terminal_local_id = VALUES(terminal_local_id),
                customer_name = VALUES(customer_name),
                customer_phone = VALUES(customer_phone),
                sold_at = VALUES(sold_at),
                item_count = VALUES(item_count),
                total_amount = VALUES(total_amount),
                status = IF(status = 'submitted', status, VALUES(status)),
                missing_mappings = IF(status = 'submitted', missing_mappings, VALUES(missing_mappings)),
                prepared_at = IF(status = 'submitted', prepared_at, VALUES(prepared_at))
        ");
        $queueStmt->execute([
            ':transaction_id' => $transaction['transaction_id'],
            ':branch_id' => (int)$transaction['branch_id'],
            ':terminal_local_id' => $terminalId,
            ':customer_name' => $transaction['customer_name'] ?? null,
            ':customer_phone' => $transaction['customer_phone'] ?? null,
            ':sold_at' => $transaction['sold_at'] ?? null,
            ':item_count' => (int)$transaction['item_count'],
            ':total_amount' => (float)$transaction['total_amount'],
            ':status' => $status,
            ':missing_mappings' => $missingMappings,
        ]);

        $idStmt = $this->conn->prepare("SELECT id, status FROM mra_eis_sales_queue WHERE transaction_id = :transaction_id LIMIT 1");
        $idStmt->execute([':transaction_id' => $transaction['transaction_id']]);
        $queue = $idStmt->fetch(PDO::FETCH_ASSOC);
        if (!$queue || $queue['status'] === 'submitted') {
            return ['status' => $queue['status'] ?? $status, 'missing_mappings' => $missingMappings];
        }

        $this->conn->prepare("DELETE FROM mra_eis_sales_queue_lines WHERE queue_id = :queue_id")
            ->execute([':queue_id' => (int)$queue['id']]);

        $insertLine = $this->conn->prepare("
            INSERT INTO mra_eis_sales_queue_lines (
                queue_id, product_code, product_name, tax_rate_id,
                quantity, unit_price, line_total, unique_codes
            ) VALUES (
                :queue_id, :product_code, :product_name, :tax_rate_id,
                :quantity, :unit_price, :line_total, :unique_codes
            )
        ");

        foreach ($grouped as $line) {
            $insertLine->execute([
                ':queue_id' => (int)$queue['id'],
                ':product_code' => $line['product_code'],
                ':product_name' => $line['product_name'],
                ':tax_rate_id' => $line['tax_rate_id'],
                ':quantity' => $line['quantity'],
                ':unit_price' => $line['unit_price'],
                ':line_total' => $line['line_total'],
                ':unique_codes' => implode(', ', $line['unique_codes']),
            ]);
        }

        return ['status' => $status, 'missing_mappings' => $missingMappings];
    }

    private function userAssignedTerminal($userId, $branchId) {
        if (empty($userId) || empty($branchId)) {
            return null;
        }

        $stmt = $this->conn->prepare("
            SELECT u.terminal_id
            FROM users u
            INNER JOIN mra_eis_terminals met ON met.id = u.terminal_id
            WHERE u.user_id = :user_id
              AND u.branch_id = :branch_id
              AND met.branch_id = :branch_id
            LIMIT 1
        ");
        $stmt->execute([
            ':user_id' => (int)$userId,
            ':branch_id' => (int)$branchId,
        ]);

        $terminalId = $stmt->fetchColumn();
        return $terminalId ? (int)$terminalId : null;
    }

    private function confirmationFailureDetails(array $terminal, $url, array $payload, array $failedAttempts) {
        return json_encode([
            'message' => 'MRA EIS terminal confirmation failed.',
            'url' => $url,
            'payload' => $payload,
            'terminal' => [
                'local_id' => $terminal['id'] ?? null,
                'mra_terminal_id' => $terminal['mra_terminal_id'] ?? null,
                'environment' => $terminal['environment'] ?? null,
                'api_base_url' => $terminal['api_base_url'] ?? null,
                'activation_code_length' => strlen((string)($terminal['terminal_activation_code'] ?? '')),
                'secret_key_length' => strlen((string)($terminal['secret_key'] ?? '')),
                'jwt_token_present' => trim((string)($terminal['jwt_token'] ?? '')) !== '',
            ],
            'hmac_self_test_passed' => $this->hmacSelfTestPassed(),
            'failed_attempts' => $failedAttempts,
        ], JSON_PRETTY_PRINT);
    }

    private function tryRefreshToken(array $terminal) {
        $currentToken = trim((string)($terminal['jwt_token'] ?? ''));
        if ($currentToken === '') {
            return null;
        }

        $url = rtrim($terminal['api_base_url'], '/') . '/configuration/request-new-terminal-token';
        try {
            $response = $this->postJson($url, [], ['Authorization: Bearer ' . $currentToken]);
        } catch (Throwable $e) {
            return null;
        }

        if ($response['status'] < 200 || $response['status'] >= 300) {
            return null;
        }

        $decoded = @json_decode($response['body'], true);
        $newToken = '';
        if (is_array($decoded)) {
            if (is_string($decoded['data'] ?? null) && trim($decoded['data']) !== '') {
                $newToken = trim($decoded['data']);
            } elseif (is_array($decoded['data'] ?? null)) {
                $newToken = trim((string)($decoded['data']['jwtToken'] ?? $decoded['data']['token'] ?? ''));
            }
        }

        if ($newToken === '') {
            return null;
        }

        $this->conn->prepare("UPDATE mra_eis_terminals SET jwt_token = :token WHERE id = :id")
            ->execute([':token' => $newToken, ':id' => (int)$terminal['id']]);

        return $newToken;
    }

    private function saveSiteProducts($terminalId, $tin, $siteId, array $products) {
        $stmt = $this->conn->prepare("
            INSERT INTO mra_eis_site_products (
                terminal_local_id,
                tin,
                site_id,
                product_code,
                product_name,
                description,
                quantity,
                unit_of_measure,
                price,
                product_expiry_date,
                minimum_stock_level,
                tax_rate_id,
                levy_rate_id,
                is_product,
                raw_payload,
                synced_at
            ) VALUES (
                :terminal_local_id,
                :tin,
                :site_id,
                :product_code,
                :product_name,
                :description,
                :quantity,
                :unit_of_measure,
                :price,
                :product_expiry_date,
                :minimum_stock_level,
                :tax_rate_id,
                :levy_rate_id,
                :is_product,
                :raw_payload,
                NOW()
            )
            ON DUPLICATE KEY UPDATE
                tin = VALUES(tin),
                product_name = VALUES(product_name),
                description = VALUES(description),
                quantity = VALUES(quantity),
                unit_of_measure = VALUES(unit_of_measure),
                price = VALUES(price),
                product_expiry_date = VALUES(product_expiry_date),
                minimum_stock_level = VALUES(minimum_stock_level),
                tax_rate_id = VALUES(tax_rate_id),
                levy_rate_id = VALUES(levy_rate_id),
                is_product = VALUES(is_product),
                raw_payload = VALUES(raw_payload),
                synced_at = NOW()
        ");

        $saved = 0;
        foreach ($products as $product) {
            if (!is_array($product)) {
                continue;
            }

            $productCode = trim((string)($product['productCode'] ?? ''));
            $productName = trim((string)($product['productName'] ?? ''));
            if ($productCode === '' || $productName === '') {
                continue;
            }

            $productSiteId = trim((string)($product['siteId'] ?? $siteId));
            if ($productSiteId !== $siteId) {
                throw new RuntimeException('MRA returned product ' . $productCode . ' for Site ID ' . $productSiteId . ', but the selected terminal belongs to Site ID ' . $siteId . '. Sync stopped to prevent cross-site stock mixing.');
            }

            $stmt->execute([
                ':terminal_local_id' => $terminalId,
                ':tin' => $tin,
                ':site_id' => $siteId,
                ':product_code' => $productCode,
                ':product_name' => $productName,
                ':description' => $product['description'] ?? null,
                ':quantity' => is_numeric($product['quantity'] ?? null) ? $product['quantity'] : 0,
                ':unit_of_measure' => $product['unitOfMeasure'] ?? null,
                ':price' => is_numeric($product['price'] ?? null) ? $product['price'] : 0,
                ':product_expiry_date' => $this->mysqlDateTime($product['productExpiryDate'] ?? null),
                ':minimum_stock_level' => is_numeric($product['minimumStockLevel'] ?? null) ? $product['minimumStockLevel'] : 0,
                ':tax_rate_id' => $product['taxRateId'] ?? null,
                ':levy_rate_id' => $product['levyRateId'] ?? null,
                ':is_product' => !empty($product['isProduct']) ? 1 : 0,
                ':raw_payload' => json_encode($product),
            ]);
            $saved++;
        }

        return $saved;
    }

    private function mysqlDateTime($value) {
        $value = trim((string)$value);
        if ($value === '') {
            return null;
        }

        $timestamp = strtotime($value);
        return $timestamp ? date('Y-m-d H:i:s', $timestamp) : null;
    }

    public function defaultApiBaseUrl($environment) {
        $settings = new SystemSettings();
        return $environment === 'production'
            ? rtrim((string)$settings->get('eis_api_url_production', 'https://eis-api.mra.mw/api/v1'), '/')
            : rtrim((string)$settings->get('eis_api_url_sandbox', 'https://dev-eis-api.mra.mw/api/v1'), '/');
    }

    public function generateProductId($prefix = 'MRA-desktop') {
        $prefix = preg_replace('/[^A-Za-z0-9._-]/', '-', trim((string)$prefix));
        $prefix = trim($prefix, '-._');
        if ($prefix === '') {
            $prefix = 'MRA-desktop';
        }

        return $this->limit($prefix . '/' . $this->generateGuid(), 'POS product ID', 50);
    }

    private function validatedData(array $input) {
        $environment = $this->choice($input['environment'] ?? 'sandbox', 'Environment', ['sandbox', 'production']);
        $apiBaseUrl = $this->optionalUrl($input['api_base_url'] ?? '', 'API base URL');
        if ($apiBaseUrl === null) {
            $apiBaseUrl = $this->defaultApiBaseUrl($environment);
        }

        $productId = $this->optionalText($input['pos_product_id'] ?? '', 'POS product ID', 50);
        if ($productId === null) {
            $productId = $this->generateProductId();
        }

        return [
            ':terminal_label' => $this->requiredText($input['terminal_label'] ?? '', 'Terminal label', 120),
            ':branch_id' => $this->requiredInteger($input['branch_id'] ?? '', 'Branch'),
            ':terminal_activation_code' => $this->requiredText($input['terminal_activation_code'] ?? '', 'Terminal Activation Code', 50),
            ':taxpayer_tin' => $this->optionalText($input['taxpayer_tin'] ?? '', 'Taxpayer TIN', 80),
            ':site_id' => $this->optionalText($input['site_id'] ?? '', 'Site ID', 80),
            ':global_config_version' => max(1, (int)($input['global_config_version'] ?? 1)),
            ':taxpayer_config_version' => max(1, (int)($input['taxpayer_config_version'] ?? 1)),
            ':terminal_config_version' => max(1, (int)($input['terminal_config_version'] ?? 1)),
            ':environment' => $environment,
            ':api_base_url' => $apiBaseUrl,
            ':os_name' => $this->requiredText($input['os_name'] ?? '', 'OS name', 50),
            ':os_version' => $this->requiredText($input['os_version'] ?? '', 'OS version', 50),
            ':os_build' => $this->optionalText($input['os_build'] ?? '', 'OS build', 50),
            ':mac_address' => $this->requiredText($input['mac_address'] ?? '', 'MAC address', 17),
            ':pos_product_id' => $productId,
            ':pos_product_version' => $this->requiredText($input['pos_product_version'] ?? '', 'POS product version', 50),
        ];
    }

    private function requireTerminal($id) {
        $terminal = $this->find($id);
        if (!$terminal) {
            throw new InvalidArgumentException('Selected terminal was not found.');
        }

        return $terminal;
    }

    private function markFailed($id, $error) {
        $stmt = $this->conn->prepare("
            UPDATE mra_eis_terminals
            SET status = 'failed', last_error = :last_error
            WHERE id = :id
        ");
        $stmt->execute([
            ':last_error' => $error,
            ':id' => $id,
        ]);
    }

    private function postJson($url, array $payload, array $extraHeaders = []) {
        if ((new SystemSettings())->eisOfflineMode()) {
            throw new RuntimeException('EIS Offline is enabled. Outbound MRA EIS connectivity is disabled.');
        }
        $body = json_encode($payload);
        if ($body === false) {
            throw new RuntimeException('Unable to encode MRA EIS payload.');
        }

        $headers = array_merge([
            'accept: application/json',
            'Content-Type: application/json',
        ], $extraHeaders);

        if (function_exists('curl_init')) {
            $ch = curl_init($url);
            curl_setopt_array($ch, [
                CURLOPT_POST => true,
                CURLOPT_HTTPHEADER => $headers,
                CURLOPT_POSTFIELDS => $body,
                CURLOPT_RETURNTRANSFER => true,
                CURLOPT_TIMEOUT => 30,
            ]);

            $responseBody = curl_exec($ch);
            if ($responseBody === false) {
                $error = curl_error($ch);
                curl_close($ch);
                throw new RuntimeException('MRA EIS request failed: ' . $error);
            }

            $status = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE);
            curl_close($ch);

            return ['status' => $status, 'body' => (string)$responseBody];
        }

        $context = stream_context_create([
            'http' => [
                'method' => 'POST',
                'header' => implode("\r\n", $headers) . "\r\n",
                'content' => $body,
                'timeout' => 30,
                'ignore_errors' => true,
            ],
        ]);

        $responseBody = file_get_contents($url, false, $context);
        if ($responseBody === false) {
            throw new RuntimeException('MRA EIS request failed.');
        }

        $status = 0;
        if (isset($http_response_header[0]) && preg_match('/\s(\d{3})\s/', $http_response_header[0], $matches)) {
            $status = (int)$matches[1];
        }

        return ['status' => $status, 'body' => (string)$responseBody];
    }

    private function getPlain($url, array $extraHeaders = []) {
        if ((new SystemSettings())->eisOfflineMode()) {
            throw new RuntimeException('EIS Offline is enabled. Outbound MRA EIS connectivity is disabled.');
        }
        $headers = array_merge([
            'accept: text/plain',
        ], $extraHeaders);

        if (function_exists('curl_init')) {
            $ch = curl_init($url);
            curl_setopt_array($ch, [
                CURLOPT_HTTPGET => true,
                CURLOPT_HTTPHEADER => $headers,
                CURLOPT_RETURNTRANSFER => true,
                CURLOPT_TIMEOUT => 30,
            ]);

            $responseBody = curl_exec($ch);
            if ($responseBody === false) {
                $error = curl_error($ch);
                curl_close($ch);
                throw new RuntimeException('MRA EIS request failed: ' . $error);
            }

            $status = (int)curl_getinfo($ch, CURLINFO_HTTP_CODE);
            curl_close($ch);

            return ['status' => $status, 'body' => (string)$responseBody];
        }

        $context = stream_context_create([
            'http' => [
                'method' => 'GET',
                'header' => implode("\r\n", $headers) . "\r\n",
                'timeout' => 30,
                'ignore_errors' => true,
            ],
        ]);

        $responseBody = file_get_contents($url, false, $context);
        if ($responseBody === false) {
            throw new RuntimeException('MRA EIS request failed.');
        }

        $status = 0;
        if (isset($http_response_header[0]) && preg_match('/\s(\d{3})\s/', $http_response_header[0], $matches)) {
            $status = (int)$matches[1];
        }

        return ['status' => $status, 'body' => (string)$responseBody];
    }

    /**
     * Returns ['id' => ..., 'jwt_token' => ..., 'api_base_url' => ...] for the best-matching
     * activated terminal. Includes 'id' (unlike the old id-less shape) so tryRefreshToken()
     * can persist a refreshed token back to the correct row when warehouseInventory() retries.
     */
    private function warehouseInventoryTerminal($branchId = 7, $terminalId = null) {
        $sql = "
            SELECT id, jwt_token, api_base_url
            FROM mra_eis_terminals
            WHERE status = 'activated'
              AND jwt_token IS NOT NULL
              AND jwt_token != ''
        ";
        $params = [];

        // An explicitly selected terminal is the API credential source. Do not also constrain it
        // to the IMS destination branch: warehouse inventory is global and the production EIS
        // terminal may belong to a different branch.
        if ($terminalId !== null && $terminalId !== '') {
            $sql .= ' AND id = :terminal_id';
            $params[':terminal_id'] = (int)$terminalId;
        } elseif ($branchId !== null && $branchId !== '') {
            $sql .= " AND branch_id = :branch_id";
            $params[':branch_id'] = (int)$branchId;
        }

        $sql .= " ORDER BY (environment = 'production') DESC, updated_at DESC, id DESC LIMIT 1";
        $stmt = $this->conn->prepare($sql);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value, PDO::PARAM_INT);
        }
        $stmt->execute();

        $terminal = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($terminal && trim((string)$terminal['jwt_token']) !== '') {
            return [
                'id' => (int)$terminal['id'],
                'jwt_token' => trim((string)$terminal['jwt_token']),
                'api_base_url' => trim((string)($terminal['api_base_url'] ?? '')),
            ];
        }

        if ($terminalId !== null && $terminalId !== '') {
            throw new RuntimeException('The selected EIS terminal is not activated for the warehouse branch or has no stored token.');
        }

        $stmt = $this->conn->prepare("
            SELECT id, jwt_token, api_base_url
            FROM mra_eis_terminals
            WHERE status = 'activated'
              AND jwt_token IS NOT NULL
              AND jwt_token != ''
            ORDER BY (environment = 'production') DESC, updated_at DESC, id DESC
            LIMIT 1
        ");
        $stmt->execute();
        $terminal = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($terminal && trim((string)$terminal['jwt_token']) !== '') {
            return [
                'id' => (int)$terminal['id'],
                'jwt_token' => trim((string)$terminal['jwt_token']),
                'api_base_url' => trim((string)($terminal['api_base_url'] ?? '')),
            ];
        }

        throw new RuntimeException('No activated EIS terminal token was found. Activate and confirm a production EIS terminal before reading warehouse stock.');
    }

    private function extractRows(array $decoded) {
        $data = isset($decoded['data']) && is_array($decoded['data']) ? $decoded['data'] : null;
        $candidates = [
            $decoded['data'] ?? null,
            $data['items'] ?? null,
            $data['records'] ?? null,
            $data['content'] ?? null,
            $decoded['items'] ?? null,
            $decoded['records'] ?? null,
            $decoded['content'] ?? null,
            $decoded,
        ];

        foreach ($candidates as $candidate) {
            if (!$this->isList($candidate)) {
                continue;
            }

            return array_map(function ($row) {
                return is_array($row) ? $row : ['value' => $row];
            }, $candidate);
        }

        $nested = $this->firstNestedList($decoded);
        if ($nested !== null) {
            return array_map(function ($row) {
                return is_array($row) ? $row : ['value' => $row];
            }, $nested);
        }

        return [];
    }

    private function firstNestedList($value) {
        if ($this->isList($value) && $value !== []) {
            return $value;
        }

        if (!is_array($value)) {
            return null;
        }

        foreach ($value as $child) {
            $found = $this->firstNestedList($child);
            if ($found !== null) {
                return $found;
            }
        }

        return null;
    }

    private function parsePlainRows($body) {
        $body = trim((string)$body);
        if ($body === '') {
            return [];
        }

        $lines = preg_split('/\r\n|\r|\n/', $body);
        $lines = array_values(array_filter(array_map('trim', $lines), 'strlen'));
        if (count($lines) < 2) {
            return [['response' => $body]];
        }

        $delimiter = strpos($lines[0], "\t") !== false ? "\t" : ',';
        $headers = str_getcsv($lines[0], $delimiter);
        if (count($headers) < 2) {
            return [['response' => $body]];
        }

        $rows = [];
        foreach (array_slice($lines, 1) as $line) {
            $values = str_getcsv($line, $delimiter);
            $row = [];
            foreach ($headers as $index => $header) {
                $row[$header ?: 'column_' . ($index + 1)] = $values[$index] ?? '';
            }
            $rows[] = $row;
        }

        return $rows;
    }

    private function responseSummary(array $decoded) {
        $summary = [];
        foreach ($decoded as $key => $value) {
            if ($key === 'data' && is_array($value)) {
                continue;
            }
            $summary[$key] = $value;
        }

        if (isset($decoded['data']) && is_array($decoded['data']) && !$this->isList($decoded['data'])) {
            foreach ($decoded['data'] as $key => $value) {
                if (is_array($value) && $this->isList($value)) {
                    $summary['data.' . $key . '_count'] = count($value);
                    continue;
                }

                $summary['data.' . $key] = $value;
            }
        }

        return $summary;
    }

    private function isList($value) {
        if (!is_array($value)) {
            return false;
        }

        if ($value === []) {
            return true;
        }

        return array_keys($value) === range(0, count($value) - 1);
    }

    private function requiredText($value, $label, $maxLength) {
        $value = trim((string)$value);
        if ($value === '') {
            throw new InvalidArgumentException($label . ' is required.');
        }

        return $this->limit($value, $label, $maxLength);
    }

    private function optionalText($value, $label, $maxLength) {
        $value = trim((string)$value);
        if ($value === '') {
            return null;
        }

        return $this->limit($value, $label, $maxLength);
    }

    private function optionalUrl($value, $label) {
        $value = $this->optionalText($value, $label, 255);
        if ($value !== null && !filter_var($value, FILTER_VALIDATE_URL)) {
            throw new InvalidArgumentException($label . ' must be a valid URL.');
        }

        return $value;
    }

    private function requiredInteger($value, $label) {
        if (!filter_var($value, FILTER_VALIDATE_INT, ['options' => ['min_range' => 1]])) {
            throw new InvalidArgumentException($label . ' is required.');
        }

        return (int)$value;
    }

    private function choice($value, $label, array $allowedValues) {
        $value = trim((string)$value);
        if (!in_array($value, $allowedValues, true)) {
            throw new InvalidArgumentException($label . ' is invalid.');
        }

        return $value;
    }

    private function limit($value, $label, $maxLength) {
        if (strlen((string)$value) > $maxLength) {
            throw new InvalidArgumentException($label . ' is too long.');
        }

        return $value;
    }

    private function generateGuid() {
        $data = random_bytes(16);
        $data[6] = chr((ord($data[6]) & 0x0f) | 0x40);
        $data[8] = chr((ord($data[8]) & 0x3f) | 0x80);

        return vsprintf('%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex($data), 4));
    }
}
