<?php
require_once __DIR__ . '/Logger.php';
require_once __DIR__ . '/Sales.php';
require_once __DIR__ . '/MraEisCompliance.php';
require_once __DIR__ . '/SystemSettings.php';
require_once __DIR__ . '/../includes/payment_options.php';

class ServiceModule {
    private $conn;
    private $logger;
    private $settings;

    public function __construct(PDO $conn, $logger = null) {
        $this->conn = $conn;
        $this->logger = $logger;
        $this->settings = new SystemSettings();
        new MraEisCompliance($conn);
        $this->ensureTables();
    }

    private function ensureTables() {
        $this->conn->exec("CREATE TABLE IF NOT EXISTS service_invoices (
            service_invoice_id INT AUTO_INCREMENT PRIMARY KEY,
            invoice_number VARCHAR(120) NOT NULL UNIQUE,
            branch_id INT NOT NULL,
            terminal_local_id INT DEFAULT NULL,
            customer_name VARCHAR(180) NOT NULL,
            customer_phone VARCHAR(80) DEFAULT NULL,
            customer_email VARCHAR(180) DEFAULT NULL,
            buyer_tin VARCHAR(40) DEFAULT NULL,
            buyer_authorization_code VARCHAR(120) DEFAULT NULL,
            transaction_type ENUM('B2C','B2B') NOT NULL DEFAULT 'B2C',
            payment_method VARCHAR(40) NOT NULL DEFAULT 'cash',
            service_type VARCHAR(40) NOT NULL DEFAULT 'repair',
            device_type VARCHAR(120) DEFAULT NULL,
            device_brand VARCHAR(120) DEFAULT NULL,
            device_model VARCHAR(160) DEFAULT NULL,
            serial_number VARCHAR(160) DEFAULT NULL,
            reported_fault TEXT DEFAULT NULL,
            diagnosis TEXT DEFAULT NULL,
            technician_name VARCHAR(160) DEFAULT NULL,
            repair_status VARCHAR(40) NOT NULL DEFAULT 'received',
            warranty_status VARCHAR(40) NOT NULL DEFAULT 'out_of_warranty',
            status ENUM('draft','queued','submitted','cancelled') NOT NULL DEFAULT 'queued',
            subtotal DECIMAL(15,2) NOT NULL DEFAULT 0,
            vat_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
            total_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
            notes TEXT DEFAULT NULL,
            eis_outbox_id INT DEFAULT NULL,
            created_by INT NOT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_service_invoices_branch (branch_id),
            INDEX idx_service_invoices_status (status),
            INDEX idx_service_invoices_created (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");

        $this->conn->exec("CREATE TABLE IF NOT EXISTS service_invoice_lines (
            line_id INT AUTO_INCREMENT PRIMARY KEY,
            service_invoice_id INT NOT NULL,
            site_product_id INT DEFAULT NULL,
            line_type VARCHAR(30) NOT NULL DEFAULT 'service',
            is_product TINYINT(1) NOT NULL DEFAULT 0,
            product_code VARCHAR(120) NOT NULL,
            service_name VARCHAR(255) NOT NULL,
            description TEXT DEFAULT NULL,
            unit_of_measure VARCHAR(80) NOT NULL DEFAULT 'EA',
            tax_rate_id VARCHAR(50) NOT NULL DEFAULT 'A',
            levy_rate_id VARCHAR(50) DEFAULT NULL,
            quantity DECIMAL(12,2) NOT NULL DEFAULT 1,
            unit_price DECIMAL(15,2) NOT NULL DEFAULT 0,
            discount_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
            line_total DECIMAL(15,2) NOT NULL DEFAULT 0,
            vat_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_service_lines_invoice (service_invoice_id),
            INDEX idx_service_lines_code (product_code)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");

        $this->ensureColumns('service_invoices', [
            'service_type' => "VARCHAR(40) NOT NULL DEFAULT 'repair'",
            'device_type' => 'VARCHAR(120) DEFAULT NULL',
            'device_brand' => 'VARCHAR(120) DEFAULT NULL',
            'device_model' => 'VARCHAR(160) DEFAULT NULL',
            'serial_number' => 'VARCHAR(160) DEFAULT NULL',
            'reported_fault' => 'TEXT DEFAULT NULL',
            'diagnosis' => 'TEXT DEFAULT NULL',
            'technician_name' => 'VARCHAR(160) DEFAULT NULL',
            'repair_status' => "VARCHAR(40) NOT NULL DEFAULT 'received'",
            'warranty_status' => "VARCHAR(40) NOT NULL DEFAULT 'out_of_warranty'",
        ]);
        $this->ensureColumns('service_invoice_lines', [
            'line_type' => "VARCHAR(30) NOT NULL DEFAULT 'service'",
            'is_product' => 'TINYINT(1) NOT NULL DEFAULT 0',
        ]);
    }

    public function listInvoices($branchId = null, $limit = 100) {
        $sql = "SELECT si.*, b.branch_name, u.username AS created_by_name, outbox.status AS eis_status, outbox.last_error
                FROM service_invoices si
                LEFT JOIN branches b ON b.branch_id = si.branch_id
                LEFT JOIN users u ON u.user_id = si.created_by
                LEFT JOIN mra_eis_outbox outbox ON outbox.id = si.eis_outbox_id";
        $params = [];
        if ($branchId !== null && $branchId !== '') {
            $sql .= " WHERE si.branch_id = :branch_id";
            $params[':branch_id'] = (int)$branchId;
        }
        $sql .= " ORDER BY si.created_at DESC LIMIT " . max(1, min(300, (int)$limit));
        $stmt = $this->conn->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getInvoice($invoiceId) {
        $stmt = $this->conn->prepare("SELECT si.*, b.branch_name, outbox.status AS eis_status, outbox.last_error, outbox.validation_url
                                      FROM service_invoices si
                                      LEFT JOIN branches b ON b.branch_id = si.branch_id
                                      LEFT JOIN mra_eis_outbox outbox ON outbox.id = si.eis_outbox_id
                                      WHERE si.service_invoice_id = :id");
        $stmt->execute([':id' => (int)$invoiceId]);
        $invoice = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$invoice) {
            return null;
        }
        $lineStmt = $this->conn->prepare("SELECT * FROM service_invoice_lines WHERE service_invoice_id = :id ORDER BY line_id ASC");
        $lineStmt->execute([':id' => (int)$invoiceId]);
        $invoice['lines'] = $lineStmt->fetchAll(PDO::FETCH_ASSOC);
        return $invoice;
    }

    public function serviceCatalog($branchId = null) {
        return $this->siteProductCatalog(false, $branchId);
    }

    public function warehouseInventoryCatalog($limit = 500) {
        if (!$this->tableExists('mra_eis_site_products')) {
            return [];
        }
        $warehouseBranchId = (int)$this->settings->eisWarehouseBranchId();
        $sql = "SELECT sp.*, met.terminal_label, met.branch_id
                FROM mra_eis_site_products sp
                LEFT JOIN mra_eis_terminals met ON met.id = sp.terminal_local_id
                WHERE COALESCE(sp.is_product, 1) = 1
                  AND (:warehouse_branch_id = 0 OR met.branch_id = :warehouse_branch_id)
                ORDER BY sp.product_name ASC
                LIMIT " . max(1, min(1000, (int)$limit));
        $stmt = $this->conn->prepare($sql);
        $stmt->execute([':warehouse_branch_id' => $warehouseBranchId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function createInvoice(array $data, array $lines, $userId) {
        if (empty($lines)) {
            throw new InvalidArgumentException('At least one service or part line is required.');
        }
        $branchId = (int)($data['branch_id'] ?? 0);
        if ($branchId <= 0) {
            throw new InvalidArgumentException('Branch is required for a service invoice.');
        }
        $customerName = trim((string)($data['customer_name'] ?? ''));
        if ($customerName === '') {
            throw new InvalidArgumentException('Customer name is required.');
        }

        $terminal = $this->terminalForBranch($branchId);
        $issues = [];
        if (!$terminal) {
            $issues[] = 'No activated MRA EIS terminal was found for this branch.';
        }

        try {
            $sales = new Sales($this->conn, $this->logger);
            $invoiceNumber = $sales->generateMraEisInvoiceNumber($branchId, date('Y-m-d H:i:s'));
        } catch (Throwable $e) {
            $invoiceNumber = 'SVC-' . date('Ymd-His') . '-' . random_int(100, 999);
            $issues[] = $e->getMessage();
        }

        $normalizedLines = [];
        $subtotal = 0.0;
        $vatAmount = 0.0;
        $lineNo = 1;
        foreach ($lines as $line) {
            $normalized = $this->normalizeLine($line, $terminal, $lineNo);
            if ($normalized['product_code'] === '') {
                $issues[] = 'Missing MRA EIS product/service code on line ' . $lineNo . '.';
            }
            $subtotal += $normalized['line_total'] - $normalized['vat_amount'];
            $vatAmount += $normalized['vat_amount'];
            $normalizedLines[] = $normalized;
            $lineNo++;
        }
        $total = array_sum(array_column($normalizedLines, 'line_total'));

        $this->conn->beginTransaction();
        try {
            $stmt = $this->conn->prepare("INSERT INTO service_invoices (
                invoice_number, branch_id, terminal_local_id, customer_name, customer_phone, customer_email,
                buyer_tin, buyer_authorization_code, transaction_type, payment_method, service_type, device_type,
                device_brand, device_model, serial_number, reported_fault, diagnosis, technician_name,
                repair_status, warranty_status, subtotal, vat_amount, total_amount, notes, created_by
            ) VALUES (
                :invoice_number, :branch_id, :terminal_local_id, :customer_name, :customer_phone, :customer_email,
                :buyer_tin, :buyer_authorization_code, :transaction_type, :payment_method, :service_type, :device_type,
                :device_brand, :device_model, :serial_number, :reported_fault, :diagnosis, :technician_name,
                :repair_status, :warranty_status, :subtotal, :vat_amount, :total_amount, :notes, :created_by
            )");
            $transactionType = trim((string)($data['buyer_tin'] ?? '')) !== '' ? 'B2B' : 'B2C';
            $paymentMethod = normalizePaymentMethodValue($data['payment_method'] ?? 'cash');
            $stmt->execute([
                ':invoice_number' => $invoiceNumber,
                ':branch_id' => $branchId,
                ':terminal_local_id' => $terminal['id'] ?? null,
                ':customer_name' => $customerName,
                ':customer_phone' => trim((string)($data['customer_phone'] ?? '')) ?: null,
                ':customer_email' => trim((string)($data['customer_email'] ?? '')) ?: null,
                ':buyer_tin' => trim((string)($data['buyer_tin'] ?? '')) ?: null,
                ':buyer_authorization_code' => trim((string)($data['buyer_authorization_code'] ?? '')) ?: null,
                ':transaction_type' => $transactionType,
                ':payment_method' => $paymentMethod,
                ':service_type' => $this->cleanChoice($data['service_type'] ?? 'repair', ['repair', 'diagnostics', 'installation', 'maintenance', 'software', 'warranty', 'other'], 'repair'),
                ':device_type' => trim((string)($data['device_type'] ?? '')) ?: null,
                ':device_brand' => trim((string)($data['device_brand'] ?? '')) ?: null,
                ':device_model' => trim((string)($data['device_model'] ?? '')) ?: null,
                ':serial_number' => trim((string)($data['serial_number'] ?? '')) ?: null,
                ':reported_fault' => trim((string)($data['reported_fault'] ?? '')) ?: null,
                ':diagnosis' => trim((string)($data['diagnosis'] ?? '')) ?: null,
                ':technician_name' => trim((string)($data['technician_name'] ?? '')) ?: null,
                ':repair_status' => $this->cleanChoice($data['repair_status'] ?? 'received', ['received', 'diagnosing', 'waiting_parts', 'repairing', 'ready', 'collected', 'cancelled'], 'received'),
                ':warranty_status' => $this->cleanChoice($data['warranty_status'] ?? 'out_of_warranty', ['out_of_warranty', 'under_warranty', 'warranty_claim', 'internal'], 'out_of_warranty'),
                ':subtotal' => round($subtotal, 2),
                ':vat_amount' => round($vatAmount, 2),
                ':total_amount' => round($total, 2),
                ':notes' => trim((string)($data['notes'] ?? '')) ?: null,
                ':created_by' => (int)$userId,
            ]);
            $invoiceId = (int)$this->conn->lastInsertId();

            $lineStmt = $this->conn->prepare("INSERT INTO service_invoice_lines (
                service_invoice_id, site_product_id, line_type, is_product, product_code, service_name, description, unit_of_measure,
                tax_rate_id, levy_rate_id, quantity, unit_price, discount_amount, line_total, vat_amount
            ) VALUES (
                :service_invoice_id, :site_product_id, :line_type, :is_product, :product_code, :service_name, :description, :unit_of_measure,
                :tax_rate_id, :levy_rate_id, :quantity, :unit_price, :discount_amount, :line_total, :vat_amount
            )");
            foreach ($normalizedLines as $line) {
                $lineStmt->execute([
                    ':service_invoice_id' => $invoiceId,
                    ':site_product_id' => $line['site_product_id'],
                    ':line_type' => $line['line_type'],
                    ':is_product' => $line['is_product'] ? 1 : 0,
                    ':product_code' => $line['product_code'],
                    ':service_name' => $line['service_name'],
                    ':description' => $line['description'],
                    ':unit_of_measure' => $line['unit_of_measure'],
                    ':tax_rate_id' => $line['tax_rate_id'],
                    ':levy_rate_id' => $line['levy_rate_id'],
                    ':quantity' => $line['quantity'],
                    ':unit_price' => $line['unit_price'],
                    ':discount_amount' => $line['discount_amount'],
                    ':line_total' => $line['line_total'],
                    ':vat_amount' => $line['vat_amount'],
                ]);
            }

            $outboxId = $this->queueEis($invoiceId, $invoiceNumber, $branchId, $terminal, $data, $normalizedLines, $issues, $userId);
            $this->conn->prepare('UPDATE service_invoices SET eis_outbox_id = :outbox_id WHERE service_invoice_id = :id')
                ->execute([':outbox_id' => $outboxId, ':id' => $invoiceId]);
            $this->conn->commit();

            if ($this->logger) {
                $this->logger->log('CREATE_SERVICE_INVOICE', 'services', 'Created service invoice ' . $invoiceNumber, $invoiceId, 'service_invoices', null, $data);
            }
            return ['invoice_id' => $invoiceId, 'invoice_number' => $invoiceNumber, 'outbox_id' => $outboxId, 'issues' => $issues];
        } catch (Throwable $e) {
            $this->conn->rollBack();
            throw $e;
        }
    }

    private function normalizeLine(array $line, $terminal, $lineNo) {
        $siteProduct = null;
        $siteProductId = (int)($line['site_product_id'] ?? 0);
        if ($siteProductId > 0 && $this->tableExists('mra_eis_site_products')) {
            $stmt = $this->conn->prepare('SELECT * FROM mra_eis_site_products WHERE id = :id LIMIT 1');
            $stmt->execute([':id' => $siteProductId]);
            $siteProduct = $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
        }
        $qty = max(0.01, (float)($line['quantity'] ?? 1));
        $unitPrice = max(0, (float)($line['unit_price'] ?? ($siteProduct['price'] ?? 0)));
        $discount = max(0, (float)($line['discount_amount'] ?? 0));
        $lineTotal = max(0, round(($qty * $unitPrice) - $discount, 2));
        $taxRateId = trim((string)($siteProduct['tax_rate_id'] ?? $line['tax_rate_id'] ?? 'A')) ?: 'A';
        $vat = $this->vatForLine($lineTotal, $taxRateId);
        $lineType = strtolower(trim((string)($line['line_type'] ?? '')));
        if (!in_array($lineType, ['service', 'part', 'consumable'], true)) {
            $lineType = ((int)($siteProduct['is_product'] ?? 0) === 1) ? 'part' : 'service';
        }
        $isProduct = $lineType !== 'service';
        return [
            'site_product_id' => $siteProductId > 0 ? $siteProductId : null,
            'line_type' => $lineType,
            'is_product' => $isProduct,
            'product_code' => trim((string)($siteProduct['product_code'] ?? $line['product_code'] ?? '')),
            'service_name' => trim((string)($line['service_name'] ?? $siteProduct['product_name'] ?? ('Service line ' . $lineNo))),
            'description' => trim((string)($line['description'] ?? $siteProduct['description'] ?? $siteProduct['product_name'] ?? '')),
            'unit_of_measure' => trim((string)($siteProduct['unit_of_measure'] ?? $line['unit_of_measure'] ?? 'EA')) ?: 'EA',
            'tax_rate_id' => $taxRateId,
            'levy_rate_id' => trim((string)($siteProduct['levy_rate_id'] ?? $line['levy_rate_id'] ?? '')) ?: null,
            'site_id' => trim((string)($siteProduct['site_id'] ?? $terminal['site_id'] ?? '')) ?: null,
            'quantity' => round($qty, 2),
            'unit_price' => round($unitPrice, 2),
            'discount_amount' => round($discount, 2),
            'line_total' => $lineTotal,
            'vat_amount' => $vat,
        ];
    }

    private function vatForLine($lineTotal, $taxRateId) {
        $zeroIds = ['VAT_ZERO', 'ZERO', 'EXEMPT', 'E', '0'];
        if (in_array(strtoupper((string)$taxRateId), $zeroIds, true)) {
            return 0.0;
        }
        $rate = ((float)$this->settings->vatPercentage()) / 100;
        return round(((float)$lineTotal) - (((float)$lineTotal) / (1 + $rate)), 2);
    }

    private function queueEis($invoiceId, $invoiceNumber, $branchId, $terminal, array $data, array $lines, array $issues, $userId) {
        $payloadLines = [];
        $taxBreakdown = [];
        $totalVat = 0.0;
        $total = 0.0;
        $lineNo = 1;
        foreach ($lines as $line) {
            $payloadLines[] = [
                'id' => $lineNo,
                'productCode' => $line['product_code'],
                'description' => $line['description'] ?: $line['service_name'],
                'unitPrice' => $line['unit_price'],
                'quantity' => (int)max(1, round($line['quantity'])),
                'discount' => $line['discount_amount'],
                'total' => $line['line_total'],
                'totalVAT' => $line['vat_amount'],
                'taxRateId' => $line['tax_rate_id'],
                'isProduct' => (bool)$line['is_product'],
            ];
            $taxable = $line['line_total'] - $line['vat_amount'];
            if (!isset($taxBreakdown[$line['tax_rate_id']])) {
                $taxBreakdown[$line['tax_rate_id']] = ['rateId' => $line['tax_rate_id'], 'taxableAmount' => 0.0, 'taxAmount' => 0.0];
            }
            $taxBreakdown[$line['tax_rate_id']]['taxableAmount'] += $taxable;
            $taxBreakdown[$line['tax_rate_id']]['taxAmount'] += $line['vat_amount'];
            $totalVat += $line['vat_amount'];
            $total += $line['line_total'];
            $lineNo++;
        }
        $isB2B = trim((string)($data['buyer_tin'] ?? '')) !== '';
        $payload = [
            'invoiceHeader' => [
                'invoiceNumber' => $invoiceNumber,
                'invoiceDateTime' => date('c'),
                'sellerTIN' => (string)($terminal['taxpayer_tin'] ?? ''),
                'buyerTIN' => $isB2B ? trim((string)$data['buyer_tin']) : null,
                'buyerName' => trim((string)($data['customer_name'] ?? '')) ?: null,
                'buyerAuthorizationCode' => $isB2B ? (trim((string)($data['buyer_authorization_code'] ?? '')) ?: null) : null,
                'siteId' => (string)($lines[0]['site_id'] ?? $terminal['site_id'] ?? ''),
                'transactionType' => $isB2B ? 'B2B' : 'B2C',
                'paymentMethod' => paymentMethodToEis($data['payment_method'] ?? 'cash', $this->settings->eisDefaultPaymentMethod()),
                'currency' => 'MWK',
            ],
            'invoiceLineItems' => $payloadLines,
            'invoiceSummary' => [
                'taxBreakDown' => array_values(array_map(function ($row) {
                    return ['rateId' => $row['rateId'], 'taxableAmount' => round($row['taxableAmount'], 2), 'taxAmount' => round($row['taxAmount'], 2)];
                }, $taxBreakdown)),
                'levyBreakDown' => [],
                'totalVAT' => round($totalVat, 2),
                'offlineSignature' => null,
                'invoiceTotal' => round($total, 2),
                'amountTendered' => round($total, 2),
            ],
        ];
        if (($payload['invoiceHeader']['sellerTIN'] ?? '') === '') {
            $issues[] = 'The selected terminal has no taxpayer TIN.';
        }
        if (($payload['invoiceHeader']['siteId'] ?? '') === '') {
            $issues[] = 'No EIS site ID was found for the service invoice.';
        }

        $stmt = $this->conn->prepare("INSERT INTO mra_eis_outbox (
            entity_type, source_table, source_id, source_key, branch_id, terminal_local_id,
            endpoint, payload_json, status, local_audit_json, last_error, created_by
        ) VALUES (
            'sale', 'service_invoices', :source_id, :source_key, :branch_id, :terminal_local_id,
            '/sales/submit-sales-transaction', :payload_json, :status, :local_audit_json, :last_error, :created_by
        ) ON DUPLICATE KEY UPDATE
            branch_id = VALUES(branch_id), terminal_local_id = VALUES(terminal_local_id),
            payload_json = VALUES(payload_json), status = VALUES(status),
            local_audit_json = VALUES(local_audit_json), last_error = VALUES(last_error), created_by = VALUES(created_by)");
        $stmt->execute([
            ':source_id' => (string)$invoiceId,
            ':source_key' => $invoiceNumber,
            ':branch_id' => (int)$branchId,
            ':terminal_local_id' => $terminal['id'] ?? null,
            ':payload_json' => json_encode($payload, JSON_UNESCAPED_SLASHES),
            ':status' => empty($issues) ? 'ready' : 'blocked',
            ':local_audit_json' => json_encode(['service_invoice_id' => $invoiceId, 'lines' => $lines, 'service_context' => $data], JSON_UNESCAPED_SLASHES),
            ':last_error' => empty($issues) ? null : implode(' ', array_unique($issues)),
            ':created_by' => (int)$userId,
        ]);
        $id = (int)$this->conn->lastInsertId();
        if ($id === 0) {
            $lookup = $this->conn->prepare("SELECT id FROM mra_eis_outbox WHERE entity_type = 'sale' AND source_key = :source_key LIMIT 1");
            $lookup->execute([':source_key' => $invoiceNumber]);
            $id = (int)$lookup->fetchColumn();
        }
        return $id;
    }

    private function siteProductCatalog($isProduct, $branchId = null) {
        if (!$this->tableExists('mra_eis_site_products')) {
            return [];
        }
        $sql = "SELECT sp.*, met.terminal_label, met.branch_id
                FROM mra_eis_site_products sp
                LEFT JOIN mra_eis_terminals met ON met.id = sp.terminal_local_id
                WHERE COALESCE(sp.is_product, 1) = :is_product";
        $params = [':is_product' => $isProduct ? 1 : 0];
        if ($branchId !== null && $branchId !== '') {
            $sql .= " AND met.branch_id = :branch_id";
            $params[':branch_id'] = (int)$branchId;
        }
        $sql .= " ORDER BY sp.product_name ASC LIMIT 500";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function terminalForBranch($branchId) {
        if (!$this->tableExists('mra_eis_terminals')) {
            return null;
        }
        $stmt = $this->conn->prepare("SELECT * FROM mra_eis_terminals
                                      WHERE branch_id = :branch_id
                                        AND status = 'activated'
                                        AND COALESCE(is_blocked, 0) = 0
                                      ORDER BY (environment = 'production') DESC, updated_at DESC, id DESC
                                      LIMIT 1");
        $stmt->execute([':branch_id' => (int)$branchId]);
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }

    private function cleanChoice($value, array $allowed, $default) {
        $value = strtolower(trim((string)$value));
        return in_array($value, $allowed, true) ? $value : $default;
    }

    private function ensureColumns($table, array $columns) {
        foreach ($columns as $column => $definition) {
            if (!$this->columnExists($table, $column)) {
                $this->conn->exec("ALTER TABLE `$table` 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);
    }

    private function tableExists($table) {
        $stmt = $this->conn->prepare('SHOW TABLES LIKE :table_name');
        $stmt->execute([':table_name' => $table]);
        return (bool)$stmt->fetchColumn();
    }
}
?>