<?php
declare(strict_types=1);

if (!function_exists('pos_add_column')) {
  function pos_table_exists(PDO $pdo, string $table): bool {
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?");
    $stmt->execute([$table]);
    return (int)$stmt->fetchColumn() > 0;
  }

  function pos_column_info(PDO $pdo, string $table, string $column): ?array {
    $stmt = $pdo->prepare("SELECT COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = ? AND column_name = ? LIMIT 1");
    $stmt->execute([$table, $column]);
    $row = $stmt->fetch();
    return $row ?: null;
  }

  function pos_index_exists(PDO $pdo, string $table, string $indexName): bool {
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = ? AND index_name = ?");
    $stmt->execute([$table, $indexName]);
    return (int)$stmt->fetchColumn() > 0;
  }

  function pos_exec_ddl(PDO $pdo, string $label, string $sql): void {
    try {
      $pdo->exec($sql);
    } catch (Throwable $e) {
      error_log('POS database upgrade failed during ' . $label . ': ' . $e->getMessage());
      throw new RuntimeException('The POS database upgrade could not be completed. Import DB/modern_pos_hotfix.sql in phpMyAdmin or grant the database user CREATE and ALTER permissions, then reload the page.', 0, $e);
    }
  }

  function pos_add_column(PDO $pdo, string $table, string $column, string $definition): void {
    if (pos_column_info($pdo, $table, $column)) {
      return;
    }
    pos_exec_ddl($pdo, "add {$table}.{$column}", "ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$definition}");
    if (!pos_column_info($pdo, $table, $column)) {
      throw new RuntimeException("The required POS column {$table}.{$column} is still missing after the database upgrade.");
    }
  }

  function pos_modify_column(PDO $pdo, string $table, string $column, string $expectedType, string $definition): void {
    $row = pos_column_info($pdo, $table, $column);
    if (!$row) {
      return;
    }
    if (strtolower((string)$row['COLUMN_TYPE']) !== strtolower($expectedType)) {
      pos_exec_ddl($pdo, "resize {$table}.{$column}", "ALTER TABLE `{$table}` MODIFY COLUMN `{$column}` {$definition}");
    }
  }

  function pos_add_index(PDO $pdo, string $table, string $indexName, string $columns, bool $unique = false): void {
    if (pos_index_exists($pdo, $table, $indexName)) {
      return;
    }
    try {
      $pdo->exec("ALTER TABLE `{$table}` ADD " . ($unique ? 'UNIQUE ' : '') . "INDEX `{$indexName}` ({$columns})");
    } catch (Throwable $e) {
      // Indexes improve performance but are not required to render the POS.
      error_log("POS index upgrade {$table}.{$indexName} failed: " . $e->getMessage());
    }
  }

  function pos_require_table(PDO $pdo, string $table): void {
    if (!pos_table_exists($pdo, $table)) {
      throw new RuntimeException("The required POS database table {$table} is missing.");
    }
  }

  function pos_require_column(PDO $pdo, string $table, string $column): void {
    if (!pos_column_info($pdo, $table, $column)) {
      throw new RuntimeException("The required POS database column {$table}.{$column} is missing.");
    }
  }

  function pos_ensure_schema(PDO $pdo): void {
    static $done = false;
    if ($done) {
      return;
    }
    $done = true;

    if (!pos_table_exists($pdo, 'tp_users')) {
      pos_exec_ddl($pdo, 'create table tp_users', "CREATE TABLE IF NOT EXISTS tp_users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      full_name VARCHAR(120) NOT NULL,
      username VARCHAR(80) NOT NULL UNIQUE,
      password_hash VARCHAR(255) NOT NULL,
      workspace ENUM('tuckshop','produce') NOT NULL,
      role ENUM('manager','cashier') NOT NULL DEFAULT 'manager',
      is_active TINYINT(1) NOT NULL DEFAULT 1,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }

    pos_add_column($pdo, 'tp_users', 'role', "ENUM('manager','cashier') NOT NULL DEFAULT 'manager' AFTER workspace");
    pos_add_column($pdo, 'tp_users', 'is_active', "TINYINT(1) NOT NULL DEFAULT 1 AFTER role");
    try {
      $pdo->exec("UPDATE tp_users SET role='manager' WHERE role IS NULL OR role='' ");
    } catch (Throwable $e) {
      error_log('POS user role normalization failed: ' . $e->getMessage());
    }

    if (!pos_table_exists($pdo, 'tp_products')) {
      pos_exec_ddl($pdo, 'create table tp_products', "CREATE TABLE IF NOT EXISTS tp_products (
      id INT AUTO_INCREMENT PRIMARY KEY,
      workspace ENUM('tuckshop','produce') NOT NULL,
      category VARCHAR(80) NOT NULL,
      product_name VARCHAR(120) NOT NULL,
      unit_label VARCHAR(40) NOT NULL DEFAULT 'unit',
      buying_price DECIMAL(12,2) NOT NULL DEFAULT 0,
      selling_price DECIMAL(12,2) NOT NULL DEFAULT 0,
      stock_qty DECIMAL(12,3) NOT NULL DEFAULT 0,
      reorder_level DECIMAL(12,3) NOT NULL DEFAULT 5,
      is_active TINYINT(1) NOT NULL DEFAULT 1,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_tp_products_workspace (workspace),
      INDEX idx_tp_products_category (category)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }

    if (!pos_table_exists($pdo, 'tp_sales')) {
      pos_exec_ddl($pdo, 'create table tp_sales', "CREATE TABLE IF NOT EXISTS tp_sales (
      id BIGINT AUTO_INCREMENT PRIMARY KEY,
      workspace ENUM('tuckshop','produce') NOT NULL,
      product_id INT NOT NULL,
      quantity DECIMAL(12,3) NOT NULL,
      unit_price DECIMAL(12,2) NOT NULL,
      total_amount DECIMAL(12,2) NOT NULL,
      sold_by INT NOT NULL,
      notes VARCHAR(255) NULL,
      sold_at DATETIME NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_tp_sales_workspace_date (workspace, sold_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }

    $productColumns = [
      'sku' => "VARCHAR(64) NULL AFTER product_name",
      'barcode' => "VARCHAR(80) NULL AFTER sku",
      'tax_rate' => "DECIMAL(5,2) NOT NULL DEFAULT 0 AFTER selling_price",
      'track_stock' => "TINYINT(1) NOT NULL DEFAULT 1 AFTER stock_qty",
      'allow_fractional' => "TINYINT(1) NOT NULL DEFAULT 0 AFTER track_stock",
      'supplier_name' => "VARCHAR(120) NULL AFTER reorder_level",
      'updated_at' => "TIMESTAMP NULL DEFAULT NULL AFTER created_at"
    ];
    foreach ($productColumns as $name => $definition) {
      pos_add_column($pdo, 'tp_products', $name, $definition);
    }
    pos_modify_column($pdo, 'tp_products', 'buying_price', 'decimal(12,2)', "DECIMAL(12,2) NOT NULL DEFAULT 0");
    pos_modify_column($pdo, 'tp_products', 'selling_price', 'decimal(12,2)', "DECIMAL(12,2) NOT NULL DEFAULT 0");
    pos_modify_column($pdo, 'tp_products', 'stock_qty', 'decimal(12,3)', "DECIMAL(12,3) NOT NULL DEFAULT 0");
    pos_modify_column($pdo, 'tp_products', 'reorder_level', 'decimal(12,3)', "DECIMAL(12,3) NOT NULL DEFAULT 5");
    pos_add_index($pdo, 'tp_products', 'idx_tp_products_workspace_sku', '`workspace`,`sku`');
    pos_add_index($pdo, 'tp_products', 'idx_tp_products_workspace_barcode', '`workspace`,`barcode`');

    $salesColumns = [
      'sale_ref' => "VARCHAR(48) NULL AFTER id",
      'transaction_id' => "BIGINT NULL AFTER sale_ref",
      'payment_method' => "VARCHAR(30) NOT NULL DEFAULT 'cash' AFTER total_amount",
      'amount_tendered' => "DECIMAL(12,2) NULL AFTER payment_method",
      'currency_code' => "VARCHAR(8) NOT NULL DEFAULT 'USD' AFTER amount_tendered",
      'fx_rate_to_usd' => "DECIMAL(18,8) NOT NULL DEFAULT 1 AFTER currency_code",
      'line_subtotal_usd' => "DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER fx_rate_to_usd",
      'line_discount_usd' => "DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER line_subtotal_usd",
      'line_tax_usd' => "DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER line_discount_usd",
      'total_amount_usd' => "DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER line_tax_usd",
      'amount_tendered_usd' => "DECIMAL(12,2) NULL AFTER total_amount_usd",
      'status' => "VARCHAR(24) NOT NULL DEFAULT 'completed' AFTER amount_tendered_usd",
      'voided_at' => "DATETIME NULL AFTER status",
      'voided_by' => "INT NULL AFTER voided_at",
      'void_reason' => "VARCHAR(255) NULL AFTER voided_by"
    ];
    foreach ($salesColumns as $name => $definition) {
      pos_add_column($pdo, 'tp_sales', $name, $definition);
    }
    pos_modify_column($pdo, 'tp_sales', 'quantity', 'decimal(12,3)', "DECIMAL(12,3) NOT NULL");
    pos_modify_column($pdo, 'tp_sales', 'unit_price', 'decimal(12,2)', "DECIMAL(12,2) NOT NULL");
    pos_modify_column($pdo, 'tp_sales', 'total_amount', 'decimal(12,2)', "DECIMAL(12,2) NOT NULL");
    pos_add_index($pdo, 'tp_sales', 'idx_tp_sales_sale_ref', '`sale_ref`');
    pos_add_index($pdo, 'tp_sales', 'idx_tp_sales_transaction', '`transaction_id`');
    pos_add_index($pdo, 'tp_sales', 'idx_tp_sales_status', '`workspace`,`status`,`sold_at`');

    if (!pos_table_exists($pdo, 'tp_transactions')) {
      pos_exec_ddl($pdo, 'create table tp_transactions', "CREATE TABLE IF NOT EXISTS tp_transactions (
      id BIGINT AUTO_INCREMENT PRIMARY KEY,
      workspace ENUM('tuckshop','produce') NOT NULL,
      sale_ref VARCHAR(48) NOT NULL UNIQUE,
      status VARCHAR(24) NOT NULL DEFAULT 'completed',
      customer_name VARCHAR(120) NULL,
      customer_phone VARCHAR(40) NULL,
      currency_code VARCHAR(8) NOT NULL DEFAULT 'USD',
      fx_rate_to_usd DECIMAL(18,8) NOT NULL DEFAULT 1,
      subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
      discount_type VARCHAR(12) NOT NULL DEFAULT 'none',
      discount_value DECIMAL(12,2) NOT NULL DEFAULT 0,
      discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
      tax_rate DECIMAL(5,2) NOT NULL DEFAULT 0,
      tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
      total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
      subtotal_usd DECIMAL(12,2) NOT NULL DEFAULT 0,
      discount_amount_usd DECIMAL(12,2) NOT NULL DEFAULT 0,
      tax_amount_usd DECIMAL(12,2) NOT NULL DEFAULT 0,
      total_amount_usd DECIMAL(12,2) NOT NULL DEFAULT 0,
      payment_method VARCHAR(30) NOT NULL DEFAULT 'cash',
      amount_tendered DECIMAL(12,2) NULL,
      change_due DECIMAL(12,2) NOT NULL DEFAULT 0,
      payment_reference VARCHAR(100) NULL,
      notes VARCHAR(500) NULL,
      sold_by INT NOT NULL,
      shift_id BIGINT NULL,
      sold_at DATETIME NOT NULL,
      voided_at DATETIME NULL,
      voided_by INT NULL,
      void_reason VARCHAR(255) NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP NULL DEFAULT NULL,
      INDEX idx_tp_tx_workspace_date (workspace, sold_at),
      INDEX idx_tp_tx_workspace_status (workspace, status, sold_at),
      INDEX idx_tp_tx_sold_by (sold_by, sold_at),
      INDEX idx_tp_tx_shift (shift_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }

    if (!pos_table_exists($pdo, 'tp_sale_payments')) {
      pos_exec_ddl($pdo, 'create table tp_sale_payments', "CREATE TABLE IF NOT EXISTS tp_sale_payments (
      id BIGINT AUTO_INCREMENT PRIMARY KEY,
      transaction_id BIGINT NOT NULL,
      payment_method VARCHAR(30) NOT NULL,
      currency_code VARCHAR(8) NOT NULL DEFAULT 'USD',
      amount DECIMAL(12,2) NOT NULL DEFAULT 0,
      amount_usd DECIMAL(12,2) NOT NULL DEFAULT 0,
      reference_no VARCHAR(100) NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_tp_payment_tx (transaction_id),
      INDEX idx_tp_payment_method (payment_method, created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }

    if (!pos_table_exists($pdo, 'tp_held_orders')) {
      pos_exec_ddl($pdo, 'create table tp_held_orders', "CREATE TABLE IF NOT EXISTS tp_held_orders (
      id BIGINT AUTO_INCREMENT PRIMARY KEY,
      workspace ENUM('tuckshop','produce') NOT NULL,
      hold_ref VARCHAR(48) NOT NULL UNIQUE,
      cart_json LONGTEXT NOT NULL,
      customer_name VARCHAR(120) NULL,
      customer_phone VARCHAR(40) NULL,
      currency_code VARCHAR(8) NOT NULL DEFAULT 'USD',
      discount_type VARCHAR(12) NOT NULL DEFAULT 'none',
      discount_value DECIMAL(12,2) NOT NULL DEFAULT 0,
      tax_rate DECIMAL(5,2) NOT NULL DEFAULT 0,
      notes VARCHAR(500) NULL,
      held_by INT NOT NULL,
      status VARCHAR(20) NOT NULL DEFAULT 'active',
      resumed_at DATETIME NULL,
      cancelled_at DATETIME NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP NULL DEFAULT NULL,
      INDEX idx_tp_hold_workspace_status (workspace, status, created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }

    if (!pos_table_exists($pdo, 'tp_stock_movements')) {
      pos_exec_ddl($pdo, 'create table tp_stock_movements', "CREATE TABLE IF NOT EXISTS tp_stock_movements (
      id BIGINT AUTO_INCREMENT PRIMARY KEY,
      workspace ENUM('tuckshop','produce') NOT NULL,
      product_id INT NOT NULL,
      movement_type VARCHAR(30) NOT NULL,
      quantity_change DECIMAL(12,3) NOT NULL,
      balance_after DECIMAL(12,3) NOT NULL,
      unit_cost_usd DECIMAL(12,2) NULL,
      reference_no VARCHAR(80) NULL,
      reason VARCHAR(255) NULL,
      created_by INT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_tp_stock_product_date (product_id, created_at),
      INDEX idx_tp_stock_workspace_date (workspace, created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }

    if (!pos_table_exists($pdo, 'tp_shifts')) {
      pos_exec_ddl($pdo, 'create table tp_shifts', "CREATE TABLE IF NOT EXISTS tp_shifts (
      id BIGINT AUTO_INCREMENT PRIMARY KEY,
      workspace ENUM('tuckshop','produce') NOT NULL,
      user_id INT NOT NULL,
      status VARCHAR(16) NOT NULL DEFAULT 'open',
      opening_float DECIMAL(12,2) NOT NULL DEFAULT 0,
      currency_code VARCHAR(8) NOT NULL DEFAULT 'USD',
      expected_cash DECIMAL(12,2) NULL,
      closing_cash DECIMAL(12,2) NULL,
      variance DECIMAL(12,2) NULL,
      opened_at DATETIME NOT NULL,
      closed_at DATETIME NULL,
      notes VARCHAR(255) NULL,
      INDEX idx_tp_shift_user_status (user_id, status, opened_at),
      INDEX idx_tp_shift_workspace_date (workspace, opened_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }
    $requiredTables = [
      'tp_users', 'tp_products', 'tp_sales', 'tp_transactions',
      'tp_sale_payments', 'tp_held_orders', 'tp_stock_movements', 'tp_shifts'
    ];
    foreach ($requiredTables as $requiredTable) {
      pos_require_table($pdo, $requiredTable);
    }

    $requiredColumns = [
      'tp_users' => ['id', 'full_name', 'workspace', 'role', 'is_active'],
      'tp_products' => ['id', 'workspace', 'product_name', 'sku', 'barcode', 'selling_price', 'stock_qty', 'track_stock', 'allow_fractional', 'reorder_level'],
      'tp_sales' => ['id', 'sale_ref', 'transaction_id', 'workspace', 'product_id', 'quantity', 'total_amount_usd', 'status', 'sold_by', 'sold_at'],
      'tp_transactions' => ['id', 'workspace', 'sale_ref', 'status', 'total_amount_usd', 'payment_method', 'sold_by', 'sold_at', 'void_reason'],
      'tp_sale_payments' => ['id', 'transaction_id', 'payment_method', 'amount_usd'],
      'tp_held_orders' => ['id', 'workspace', 'hold_ref', 'cart_json', 'status', 'held_by'],
      'tp_stock_movements' => ['id', 'workspace', 'product_id', 'movement_type', 'quantity_change', 'balance_after'],
      'tp_shifts' => ['id', 'workspace', 'user_id', 'status', 'opening_float', 'opened_at']
    ];
    foreach ($requiredColumns as $requiredTable => $columns) {
      foreach ($columns as $requiredColumn) {
        pos_require_column($pdo, $requiredTable, $requiredColumn);
      }
    }
  }

  function pos_workspace_meta(string $workspace): array {
    if ($workspace === 'produce') {
      return [
        'name' => 'Produce POS',
        'store_name' => 'Mavela Produce',
        'tagline' => 'Fresh produce and agricultural sales',
        'prefix' => 'PR',
        'emoji' => '🌿'
      ];
    }
    return [
      'name' => 'Tuckshop POS',
      'store_name' => 'Mavela Tuckshop',
      'tagline' => 'Fresh snacks and school supplies',
      'prefix' => 'TS',
      'emoji' => '🛒'
    ];
  }

  function pos_require_login(?string $requiredWorkspace = null): array {
    if (!isset($_SESSION['tp_user_id'], $_SESSION['tp_workspace'], $_SESSION['tp_role'])) {
      header('Location: tuckshop_projects_login.php');
      exit;
    }
    $workspace = (string)$_SESSION['tp_workspace'];
    if (!in_array($workspace, ['tuckshop', 'produce'], true)) {
      header('Location: tuckshop_projects_login.php');
      exit;
    }
    if ($requiredWorkspace !== null && $workspace !== $requiredWorkspace) {
      header('Location: tuckshop_projects_login.php?error=workspace');
      exit;
    }
    header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
    header('Pragma: no-cache');
    return [
      'id' => (int)$_SESSION['tp_user_id'],
      'name' => (string)($_SESSION['tp_name'] ?? 'POS User'),
      'workspace' => $workspace,
      'role' => (string)$_SESSION['tp_role']
    ];
  }

  function pos_currency_symbol(string $code): string {
    $code = strtoupper(trim($code));
    if ($code === 'RAND') return 'R';
    if ($code === 'ZIG') return 'ZiG ';
    return '$';
  }

  function pos_ref(string $prefix): string {
    return strtoupper($prefix) . '-' . date('Ymd-His') . '-' . strtoupper(bin2hex(random_bytes(3)));
  }

  function pos_current_shift(PDO $pdo, int $userId, string $workspace): ?array {
    $stmt = $pdo->prepare("SELECT * FROM tp_shifts WHERE user_id = ? AND workspace = ? AND status = 'open' ORDER BY id DESC LIMIT 1");
    $stmt->execute([$userId, $workspace]);
    $row = $stmt->fetch();
    return $row ?: null;
  }

  function pos_open_shift(PDO $pdo, int $userId, string $workspace, float $openingFloat, string $currencyCode = 'USD'): array {
    $existing = pos_current_shift($pdo, $userId, $workspace);
    if ($existing) return $existing;
    $stmt = $pdo->prepare("INSERT INTO tp_shifts (workspace, user_id, status, opening_float, currency_code, opened_at) VALUES (?, ?, 'open', ?, ?, NOW())");
    $stmt->execute([$workspace, $userId, max(0, $openingFloat), strtoupper($currencyCode)]);
    return pos_current_shift($pdo, $userId, $workspace) ?? [];
  }

  function pos_shift_expected_cash(PDO $pdo, int $shiftId): float {
    $shiftStmt = $pdo->prepare("SELECT opening_float, currency_code FROM tp_shifts WHERE id=? LIMIT 1");
    $shiftStmt->execute([$shiftId]);
    $shift = $shiftStmt->fetch();
    if (!$shift) return 0.0;
    $cashStmt = $pdo->prepare("SELECT COALESCE(SUM(p.amount_usd),0) FROM tp_sale_payments p JOIN tp_transactions t ON t.id=p.transaction_id WHERE t.shift_id=? AND t.status='completed' AND p.payment_method='cash'");
    $cashStmt->execute([$shiftId]);
    $cashUsd = (float)$cashStmt->fetchColumn();
    $rate = get_currency_rate_to_usd($pdo, (string)$shift['currency_code']);
    if ($rate <= 0) $rate = 1.0;
    return round((float)$shift['opening_float'] + ($cashUsd / $rate), 2);
  }

  function pos_close_shift(PDO $pdo, int $shiftId, int $userId, float $closingCash, string $notes = ''): void {
    $stmt = $pdo->prepare("SELECT * FROM tp_shifts WHERE id = ? AND user_id = ? AND status = 'open' LIMIT 1");
    $stmt->execute([$shiftId, $userId]);
    $shift = $stmt->fetch();
    if (!$shift) throw new RuntimeException('No open shift was found.');
    $expected = pos_shift_expected_cash($pdo, $shiftId);
    $variance = round($closingCash - $expected, 2);
    $update = $pdo->prepare("UPDATE tp_shifts SET status='closed', expected_cash=?, closing_cash=?, variance=?, notes=?, closed_at=NOW() WHERE id=?");
    $update->execute([$expected, $closingCash, $variance, $notes === '' ? null : $notes, $shiftId]);
  }

  function pos_normalize_cart(string $json): array {
    $decoded = json_decode($json, true);
    if (!is_array($decoded) || !$decoded) throw new RuntimeException('Cart is empty. Add at least one product.');
    $items = [];
    foreach ($decoded as $row) {
      $id = (int)($row['id'] ?? 0);
      $qty = round((float)($row['qty'] ?? 0), 3);
      if ($id <= 0 || $qty <= 0) throw new RuntimeException('The cart contains an invalid item.');
      if (isset($items[$id])) $items[$id] += $qty;
      else $items[$id] = $qty;
    }
    return $items;
  }

  function pos_checkout(PDO $pdo, array $user, array $data, array $fxRates): array {
    $workspace = (string)$user['workspace'];
    $cart = pos_normalize_cart((string)($data['cart_json'] ?? ''));
    $currency = strtoupper(trim((string)($data['currency_code'] ?? 'USD')));
    if (!isset($fxRates[$currency])) $currency = 'USD';
    $fx = (float)($fxRates[$currency] ?? 1);
    if ($fx <= 0) $fx = 1;

    $discountType = (string)($data['discount_type'] ?? 'none');
    if (!in_array($discountType, ['none', 'percent', 'fixed'], true)) $discountType = 'none';
    $discountValue = max(0, (float)($data['discount_value'] ?? 0));
    $taxRate = max(0, min(100, (float)($data['tax_rate'] ?? 0)));
    $paymentMethod = (string)($data['payment_method'] ?? 'cash');
    $allowedPayments = ['cash', 'ecocash', 'card', 'bank_transfer', 'mobile_money', 'credit', 'split'];
    if (!in_array($paymentMethod, $allowedPayments, true)) throw new RuntimeException('Invalid payment method.');
    $amountTendered = trim((string)($data['amount_tendered'] ?? '')) === '' ? null : max(0, (float)$data['amount_tendered']);
    $customerName = trim((string)($data['customer_name'] ?? ''));
    $customerPhone = trim((string)($data['customer_phone'] ?? ''));
    $paymentReference = trim((string)($data['payment_reference'] ?? ''));
    $notes = trim((string)($data['notes'] ?? ''));
    $soldAt = trim((string)($data['sold_at'] ?? ''));
    if ($soldAt === '') $soldAt = date('Y-m-d H:i:s');
    else $soldAt = date('Y-m-d H:i:s', strtotime($soldAt) ?: time());

    $pdo->beginTransaction();
    try {
      $shift = pos_current_shift($pdo, (int)$user['id'], $workspace);
      if (!$shift) $shift = pos_open_shift($pdo, (int)$user['id'], $workspace, 0, $currency);
      $meta = pos_workspace_meta($workspace);
      $saleRef = pos_ref((string)$meta['prefix']);
      $products = [];
      $subtotalUsd = 0.0;
      $lock = $pdo->prepare("SELECT * FROM tp_products WHERE id = ? AND workspace = ? AND is_active = 1 FOR UPDATE");
      foreach ($cart as $productId => $qty) {
        $lock->execute([$productId, $workspace]);
        $product = $lock->fetch();
        if (!$product) throw new RuntimeException('A selected product is unavailable. Refresh the POS and try again.');
        $allowFractional = (int)($product['allow_fractional'] ?? 0) === 1;
        if (!$allowFractional && abs($qty - round($qty)) > 0.0001) throw new RuntimeException($product['product_name'] . ' must be sold in whole units.');
        if ((int)($product['track_stock'] ?? 1) === 1 && (float)$product['stock_qty'] + 0.0001 < $qty) {
          throw new RuntimeException('Insufficient stock for ' . $product['product_name'] . '.');
        }
        $lineSubtotal = round((float)$product['selling_price'] * $qty, 2);
        $subtotalUsd += $lineSubtotal;
        $products[] = ['row' => $product, 'qty' => $qty, 'subtotal_usd' => $lineSubtotal];
      }
      $subtotalUsd = round($subtotalUsd, 2);
      if ($subtotalUsd <= 0) throw new RuntimeException('The sale total must be greater than zero.');
      if ($discountType === 'percent') $discountUsd = round($subtotalUsd * min(100, $discountValue) / 100, 2);
      elseif ($discountType === 'fixed') $discountUsd = round(min($subtotalUsd, $discountValue * $fx), 2);
      else $discountUsd = 0.0;
      $taxableUsd = max(0, $subtotalUsd - $discountUsd);
      $taxUsd = round($taxableUsd * $taxRate / 100, 2);
      $totalUsd = round($taxableUsd + $taxUsd, 2);
      $subtotal = round($subtotalUsd / $fx, 2);
      $discountAmount = round($discountUsd / $fx, 2);
      $taxAmount = round($taxUsd / $fx, 2);
      $total = round($totalUsd / $fx, 2);

      $payments = [];
      if ($paymentMethod === 'split') {
        $paymentRows = json_decode((string)($data['payments_json'] ?? ''), true);
        if (!is_array($paymentRows) || !$paymentRows) throw new RuntimeException('Add at least one split payment.');
        $paid = 0.0;
        foreach ($paymentRows as $paymentRow) {
          $method = (string)($paymentRow['method'] ?? '');
          $amount = round(max(0, (float)($paymentRow['amount'] ?? 0)), 2);
          $reference = trim((string)($paymentRow['reference'] ?? ''));
          if (!in_array($method, ['cash', 'ecocash', 'card', 'bank_transfer', 'mobile_money'], true) || $amount <= 0) continue;
          $payments[] = ['method' => $method, 'amount' => $amount, 'reference' => $reference];
          $paid += $amount;
        }
        if (!$payments || abs(round($paid, 2) - $total) > 0.01) throw new RuntimeException('Split payments must equal the sale total exactly.');
        $amountTendered = round($paid, 2);
      } elseif ($paymentMethod === 'credit') {
        if ($customerName === '') throw new RuntimeException('A customer name is required for a credit sale.');
        $payments[] = ['method' => 'credit', 'amount' => $total, 'reference' => $paymentReference];
        $amountTendered = $total;
      } else {
        if ($amountTendered === null) $amountTendered = $paymentMethod === 'cash' ? 0 : $total;
        if ($paymentMethod === 'cash' && $amountTendered + 0.0001 < $total) throw new RuntimeException('Amount tendered is less than the sale total.');
        $payments[] = ['method' => $paymentMethod, 'amount' => $total, 'reference' => $paymentReference];
      }
      $changeDue = $paymentMethod === 'cash' || $paymentMethod === 'split' ? max(0, round((float)$amountTendered - $total, 2)) : 0.0;

      $insertTx = $pdo->prepare("INSERT INTO tp_transactions
        (workspace, sale_ref, status, customer_name, customer_phone, currency_code, fx_rate_to_usd, subtotal, discount_type, discount_value,
         discount_amount, tax_rate, tax_amount, total_amount, subtotal_usd, discount_amount_usd, tax_amount_usd, total_amount_usd,
         payment_method, amount_tendered, change_due, payment_reference, notes, sold_by, shift_id, sold_at)
        VALUES (?, ?, 'completed', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
      $insertTx->execute([
        $workspace, $saleRef, $customerName ?: null, $customerPhone ?: null, $currency, $fx, $subtotal, $discountType, $discountValue,
        $discountAmount, $taxRate, $taxAmount, $total, $subtotalUsd, $discountUsd, $taxUsd, $totalUsd, $paymentMethod,
        $amountTendered, $changeDue, $paymentReference ?: null, $notes ?: null, (int)$user['id'], (int)($shift['id'] ?? 0), $soldAt
      ]);
      $transactionId = (int)$pdo->lastInsertId();

      $insertSale = $pdo->prepare("INSERT INTO tp_sales
        (sale_ref, transaction_id, workspace, product_id, quantity, unit_price, total_amount, payment_method, amount_tendered,
         currency_code, fx_rate_to_usd, line_subtotal_usd, line_discount_usd, line_tax_usd, total_amount_usd, amount_tendered_usd,
         status, sold_by, notes, sold_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'completed', ?, ?, ?)");
      $updateStock = $pdo->prepare("UPDATE tp_products SET stock_qty = stock_qty - ? WHERE id = ?");
      $insertMovement = $pdo->prepare("INSERT INTO tp_stock_movements
        (workspace, product_id, movement_type, quantity_change, balance_after, unit_cost_usd, reference_no, reason, created_by)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
      $receiptItems = [];
      $allocatedDiscount = 0.0;
      $allocatedTax = 0.0;
      $countProducts = count($products);
      foreach ($products as $index => $item) {
        $product = $item['row'];
        $lineSubtotalUsd = (float)$item['subtotal_usd'];
        if ($index === $countProducts - 1) {
          $lineDiscountUsd = round($discountUsd - $allocatedDiscount, 2);
          $lineTaxUsd = round($taxUsd - $allocatedTax, 2);
        } else {
          $share = $subtotalUsd > 0 ? $lineSubtotalUsd / $subtotalUsd : 0;
          $lineDiscountUsd = round($discountUsd * $share, 2);
          $lineTaxUsd = round($taxUsd * $share, 2);
          $allocatedDiscount += $lineDiscountUsd;
          $allocatedTax += $lineTaxUsd;
        }
        $lineTotalUsd = round($lineSubtotalUsd - $lineDiscountUsd + $lineTaxUsd, 2);
        $unitPrice = round((float)$product['selling_price'] / $fx, 2);
        $lineTotal = round($lineTotalUsd / $fx, 2);
        $insertSale->execute([
          $saleRef, $transactionId, $workspace, (int)$product['id'], $item['qty'], $unitPrice, $lineTotal, $paymentMethod,
          $amountTendered, $currency, $fx, $lineSubtotalUsd, $lineDiscountUsd, $lineTaxUsd, $lineTotalUsd,
          $amountTendered !== null ? round($amountTendered * $fx, 2) : null, (int)$user['id'], $notes ?: null, $soldAt
        ]);
        $balance = (float)$product['stock_qty'];
        if ((int)($product['track_stock'] ?? 1) === 1) {
          $updateStock->execute([$item['qty'], (int)$product['id']]);
          $balance = round($balance - $item['qty'], 3);
          $insertMovement->execute([$workspace, (int)$product['id'], 'sale', -$item['qty'], $balance, (float)$product['buying_price'], $saleRef, 'POS sale', (int)$user['id']]);
        }
        $receiptItems[] = [
          'product_name' => (string)$product['product_name'],
          'sku' => (string)($product['sku'] ?? ''),
          'quantity' => (float)$item['qty'],
          'unit_label' => (string)$product['unit_label'],
          'unit_price' => $unitPrice,
          'line_total' => $lineTotal
        ];
      }

      $insertPayment = $pdo->prepare("INSERT INTO tp_sale_payments (transaction_id, payment_method, currency_code, amount, amount_usd, reference_no) VALUES (?, ?, ?, ?, ?, ?)");
      foreach ($payments as $payment) {
        $insertPayment->execute([$transactionId, $payment['method'], $currency, $payment['amount'], round($payment['amount'] * $fx, 2), $payment['reference'] ?: null]);
      }

      $holdId = (int)($data['hold_id'] ?? 0);
      if ($holdId > 0) {
        $mark = $pdo->prepare("UPDATE tp_held_orders SET status='resumed', resumed_at=NOW() WHERE id=? AND workspace=? AND status='active'");
        $mark->execute([$holdId, $workspace]);
      }

      $pdo->commit();
      return [
        'transaction_id' => $transactionId,
        'receipt_no' => $saleRef,
        'sold_at' => $soldAt,
        'cashier_name' => (string)$user['name'],
        'customer_name' => $customerName,
        'customer_phone' => $customerPhone,
        'currency_code' => $currency,
        'items' => $receiptItems,
        'subtotal' => $subtotal,
        'discount_amount' => $discountAmount,
        'tax_amount' => $taxAmount,
        'total_amount' => $total,
        'payment_method' => $paymentMethod,
        'amount_tendered' => $amountTendered,
        'change_due' => $changeDue,
        'payment_reference' => $paymentReference,
        'notes' => $notes,
        'status' => 'completed'
      ];
    } catch (Throwable $e) {
      if ($pdo->inTransaction()) $pdo->rollBack();
      throw $e;
    }
  }

  function pos_hold_order(PDO $pdo, array $user, array $data): string {
    pos_normalize_cart((string)($data['cart_json'] ?? ''));
    $holdId = (int)($data['hold_id'] ?? 0);
    $values = [
      (string)$data['cart_json'], trim((string)($data['customer_name'] ?? '')) ?: null,
      trim((string)($data['customer_phone'] ?? '')) ?: null, strtoupper((string)($data['currency_code'] ?? 'USD')),
      (string)($data['discount_type'] ?? 'none'), max(0, (float)($data['discount_value'] ?? 0)),
      max(0, (float)($data['tax_rate'] ?? 0)), trim((string)($data['notes'] ?? '')) ?: null
    ];
    if ($holdId > 0) {
      $sql = "UPDATE tp_held_orders SET cart_json=?, customer_name=?, customer_phone=?, currency_code=?, discount_type=?, discount_value=?, tax_rate=?, notes=?, held_by=?, updated_at=NOW() WHERE id=? AND workspace=? AND status='active'";
      $params = array_merge($values, [(int)$user['id'], $holdId, $user['workspace']]);
      if ((string)$user['role'] !== 'manager') {
        $sql .= ' AND held_by=?';
        $params[] = (int)$user['id'];
      }
      $update = $pdo->prepare($sql);
      $update->execute($params);
      if ($update->rowCount() > 0) {
        $refStmt = $pdo->prepare('SELECT hold_ref FROM tp_held_orders WHERE id=? LIMIT 1');
        $refStmt->execute([$holdId]);
        return (string)$refStmt->fetchColumn();
      }
    }
    $holdRef = pos_ref('HOLD');
    $stmt = $pdo->prepare("INSERT INTO tp_held_orders
      (workspace, hold_ref, cart_json, customer_name, customer_phone, currency_code, discount_type, discount_value, tax_rate, notes, held_by)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
    $stmt->execute(array_merge([$user['workspace'], $holdRef], $values, [(int)$user['id']]));
    return $holdRef;
  }

  function pos_void_transaction(PDO $pdo, array $user, int $transactionId, string $reason): void {
    $reason = trim($reason);
    if (strlen($reason) < 5) throw new RuntimeException('Enter a clear void reason of at least 5 characters.');
    $pdo->beginTransaction();
    try {
      $stmt = $pdo->prepare("SELECT * FROM tp_transactions WHERE id=? AND workspace=? FOR UPDATE");
      $stmt->execute([$transactionId, $user['workspace']]);
      $tx = $stmt->fetch();
      if (!$tx) throw new RuntimeException('Transaction not found.');
      if ((string)$tx['status'] !== 'completed') throw new RuntimeException('Only completed transactions can be voided.');
      $isManager = (string)$user['role'] === 'manager';
      $isOwnRecentSale = (int)$tx['sold_by'] === (int)$user['id'] && date('Y-m-d', strtotime((string)$tx['sold_at'])) === date('Y-m-d');
      if (!$isManager && !$isOwnRecentSale) throw new RuntimeException('Cashiers may only void their own transactions from today. Ask a manager for assistance.');

      $lines = $pdo->prepare("SELECT s.*, p.stock_qty, p.track_stock, p.buying_price FROM tp_sales s JOIN tp_products p ON p.id=s.product_id WHERE s.transaction_id=? AND s.status='completed' FOR UPDATE");
      $lines->execute([$transactionId]);
      $saleLines = $lines->fetchAll();
      $restore = $pdo->prepare("UPDATE tp_products SET stock_qty = stock_qty + ? WHERE id=?");
      $movement = $pdo->prepare("INSERT INTO tp_stock_movements (workspace, product_id, movement_type, quantity_change, balance_after, unit_cost_usd, reference_no, reason, created_by) VALUES (?, ?, 'void', ?, ?, ?, ?, ?, ?)");
      foreach ($saleLines as $line) {
        if ((int)($line['track_stock'] ?? 1) === 1) {
          $restore->execute([(float)$line['quantity'], (int)$line['product_id']]);
          $balance = round((float)$line['stock_qty'] + (float)$line['quantity'], 3);
          $movement->execute([$user['workspace'], (int)$line['product_id'], (float)$line['quantity'], $balance, (float)$line['buying_price'], $tx['sale_ref'], $reason, (int)$user['id']]);
        }
      }
      $updateTx = $pdo->prepare("UPDATE tp_transactions SET status='voided', voided_at=NOW(), voided_by=?, void_reason=? WHERE id=?");
      $updateTx->execute([(int)$user['id'], $reason, $transactionId]);
      $updateLines = $pdo->prepare("UPDATE tp_sales SET status='voided', voided_at=NOW(), voided_by=?, void_reason=? WHERE transaction_id=?");
      $updateLines->execute([(int)$user['id'], $reason, $transactionId]);
      $pdo->commit();
    } catch (Throwable $e) {
      if ($pdo->inTransaction()) $pdo->rollBack();
      throw $e;
    }
  }

  function pos_get_receipt(PDO $pdo, int $transactionId, string $workspace): ?array {
    $stmt = $pdo->prepare("SELECT t.*, u.full_name AS cashier_name, v.full_name AS voided_by_name
      FROM tp_transactions t
      LEFT JOIN tp_users u ON u.id=t.sold_by
      LEFT JOIN tp_users v ON v.id=t.voided_by
      WHERE t.id=? AND t.workspace=? LIMIT 1");
    $stmt->execute([$transactionId, $workspace]);
    $tx = $stmt->fetch();
    if (!$tx) return null;
    $itemsStmt = $pdo->prepare("SELECT s.quantity, s.unit_price, s.total_amount AS line_total, p.product_name, p.sku, p.unit_label
      FROM tp_sales s JOIN tp_products p ON p.id=s.product_id WHERE s.transaction_id=? ORDER BY s.id");
    $itemsStmt->execute([$transactionId]);
    $tx['items'] = $itemsStmt->fetchAll();
    $paymentsStmt = $pdo->prepare("SELECT payment_method, currency_code, amount, reference_no FROM tp_sale_payments WHERE transaction_id=? ORDER BY id");
    $paymentsStmt->execute([$transactionId]);
    $tx['payments'] = $paymentsStmt->fetchAll();
    return $tx;
  }

  function pos_migrate_legacy_sales(PDO $pdo, string $workspace): void {
    try {
      $stmt = $pdo->prepare("SELECT s.* FROM tp_sales s WHERE s.workspace=? AND s.transaction_id IS NULL ORDER BY s.sold_at ASC, s.id ASC LIMIT 500");
      $stmt->execute([$workspace]);
      $rows = $stmt->fetchAll();
      if (!$rows) return;
      $groups = [];
      foreach ($rows as $row) {
        $key = trim((string)($row['sale_ref'] ?? ''));
        if ($key === '') $key = 'LEGACY-' . (int)$row['id'];
        $groups[$key][] = $row;
      }
      foreach ($groups as $saleRef => $lines) {
        $first = $lines[0];
        $exists = $pdo->prepare("SELECT id FROM tp_transactions WHERE sale_ref=? LIMIT 1");
        $exists->execute([$saleRef]);
        $existingId = (int)($exists->fetchColumn() ?: 0);
        if ($existingId > 0) {
          $link = $pdo->prepare("UPDATE tp_sales SET transaction_id=?, sale_ref=? WHERE transaction_id IS NULL AND workspace=? AND (sale_ref=? OR (sale_ref IS NULL AND id=?))");
          $link->execute([$existingId, $saleRef, $workspace, $saleRef, (int)$first['id']]);
          continue;
        }
        $total = 0.0;
        $totalUsd = 0.0;
        $status = 'completed';
        foreach ($lines as $line) {
          $lineOriginal = (float)$line['total_amount'];
          $lineUsd = (float)($line['total_amount_usd'] ?? 0);
          if ($lineUsd <= 0) $lineUsd = $lineOriginal;
          $total += $lineOriginal;
          $totalUsd += $lineUsd;
          if ((string)($line['status'] ?? 'completed') === 'voided') $status = 'voided';
        }
        $currency = strtoupper((string)($first['currency_code'] ?? 'USD'));
        $fx = (float)($first['fx_rate_to_usd'] ?? 1);
        if ($fx <= 0) $fx = 1;
        $paymentMethod = (string)($first['payment_method'] ?? 'cash');
        $amountTendered = isset($first['amount_tendered']) ? (float)$first['amount_tendered'] : $total;
        $insert = $pdo->prepare("INSERT INTO tp_transactions
          (workspace,sale_ref,status,currency_code,fx_rate_to_usd,subtotal,total_amount,subtotal_usd,total_amount_usd,payment_method,amount_tendered,change_due,notes,sold_by,sold_at,voided_at,voided_by,void_reason)
          VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        $insert->execute([
          $workspace,$saleRef,$status,$currency,$fx,round($total,2),round($total,2),round($totalUsd,2),round($totalUsd,2),
          $paymentMethod,$amountTendered,max(0,round($amountTendered-$total,2)),(string)($first['notes'] ?? '') ?: null,
          (int)$first['sold_by'],(string)$first['sold_at'],$first['voided_at'] ?? null,$first['voided_by'] ?? null,$first['void_reason'] ?? null
        ]);
        $txId = (int)$pdo->lastInsertId();
        $ids = array_map(static fn(array $line): int => (int)$line['id'], $lines);
        $placeholders = implode(',', array_fill(0, count($ids), '?'));
        $update = $pdo->prepare("UPDATE tp_sales SET transaction_id=?, sale_ref=?, line_subtotal_usd=IF(line_subtotal_usd=0,IF(total_amount_usd=0,total_amount,total_amount_usd),line_subtotal_usd), total_amount_usd=IF(total_amount_usd=0,total_amount,total_amount_usd) WHERE id IN ({$placeholders})");
        $update->execute(array_merge([$txId,$saleRef],$ids));
        $payment = $pdo->prepare("INSERT INTO tp_sale_payments (transaction_id,payment_method,currency_code,amount,amount_usd,reference_no) VALUES (?,?,?,?,?,?)");
        $payment->execute([$txId,$paymentMethod,$currency,round($total,2),round($totalUsd,2),null]);
      }
    } catch (Throwable $e) {
      error_log('Legacy POS migration failed: ' . $e->getMessage());
    }
  }

}
