<?php
// admin/shop_lib.php
// موديول المبيعات (المواد + فواتير بيع + طباعة 80mm + QR + مرتجع/إلغاء)

if (session_status() === PHP_SESSION_NONE) {
  session_start();
}

require_once __DIR__ . '/helpers.php';
require_once __DIR__ . '/vouchers_lib.php';


function ua_column_exists(mysqli $conn, string $table, string $column): bool {
    $db = $conn->query("SELECT DATABASE() AS db")->fetch_assoc();
    $dbName = $db['db'] ?? '';
    if ($dbName === '') return false;
    $stmt = $conn->prepare("SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=? AND TABLE_NAME=? AND COLUMN_NAME=? LIMIT 1");
    $stmt->bind_param('sss', $dbName, $table, $column);
    $stmt->execute();
    $res = $stmt->get_result();
    $exists = $res && $res->num_rows > 0;
    $stmt->close();
    return $exists;
}

function ua_ensure_column(mysqli $conn, string $table, string $column, string $definition): void {
    // Adds a missing column for older schemas. Safe with strict mysqli_report.
    if (ua_column_exists($conn, $table, $column)) return;
    try {
        $conn->query("ALTER TABLE `$table` ADD COLUMN `$column` $definition");
    } catch (Throwable $e) {
        // If another process added it in between, ignore duplicate error.
    }
}

require_once __DIR__ . '/db_bootstrap.php';

function ensure_shop_tables(mysqli $conn): void {
  // مواد
  $conn->query("CREATE TABLE IF NOT EXISTS shop_products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    sku VARCHAR(80) NULL,
    description TEXT NULL,
    price DECIMAL(12,2) NOT NULL DEFAULT 0,
    stock_qty INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

  // ضمان أعمدة الجداول للنسخ القديمة
  ua_ensure_column($conn, 'shop_sale_items', 'product_id', 'INT NULL');
  ua_ensure_column($conn, 'shop_sales', 'product_id', 'INT NULL');


  // فواتير بيع
  $conn->query("CREATE TABLE IF NOT EXISTS shop_sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_no VARCHAR(30) NULL,
    customer_name VARCHAR(150) NULL,
    customer_phone VARCHAR(30) NULL,
    total DECIMAL(12,2) NOT NULL DEFAULT 0,
    discount DECIMAL(12,2) NOT NULL DEFAULT 0,
    net_total DECIMAL(12,2) NOT NULL DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'PAID',
    notes VARCHAR(255) NULL,
    created_by INT NULL,
    agent_id INT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

  if (!col_exists($conn,'shop_sales','agent_id')) {
    @$conn->query("ALTER TABLE shop_sales ADD COLUMN agent_id INT NULL AFTER created_by");
    @$conn->query("CREATE INDEX idx_shop_sales_agent_id ON shop_sales(agent_id)");
  }

  // بنود الفاتورة
  $conn->query("CREATE TABLE IF NOT EXISTS shop_sale_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sale_id INT NOT NULL,
    product_id INT NULL,
    name VARCHAR(150) NOT NULL,
    qty INT NOT NULL DEFAULT 1,
    price DECIMAL(12,2) NOT NULL DEFAULT 0,
    line_total DECIMAL(12,2) NOT NULL DEFAULT 0,
    KEY(sale_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

  // ترقيات لحقول قديمة/جديدة
  if (function_exists('col_exists')) {
    // shop_products: price
    if (!col_exists($conn, 'shop_products', 'price') && col_exists($conn,'shop_products','price_sell')) {
      $conn->query("ALTER TABLE shop_products ADD COLUMN price DECIMAL(12,2) NOT NULL DEFAULT 0");
      $conn->query("UPDATE shop_products SET price = price_sell WHERE price=0");
    }
    if (!col_exists($conn, 'shop_products', 'stock_qty') && col_exists($conn,'shop_products','qty')) {
      $conn->query("ALTER TABLE shop_products ADD COLUMN stock_qty INT NOT NULL DEFAULT 0");
      $conn->query("UPDATE shop_products SET stock_qty = qty");
    }
    // shop_sale_items: name/price
    if (!col_exists($conn,'shop_sale_items','name') && col_exists($conn,'shop_sale_items','item_name')) {
      $conn->query("ALTER TABLE shop_sale_items ADD COLUMN name VARCHAR(150) NOT NULL DEFAULT ''");
      $conn->query("UPDATE shop_sale_items SET name=item_name");
    }
    if (!col_exists($conn,'shop_sale_items','price') && col_exists($conn,'shop_sale_items','unit_price')) {
      $conn->query("ALTER TABLE shop_sale_items ADD COLUMN price DECIMAL(12,2) NOT NULL DEFAULT 0");
      $conn->query("UPDATE shop_sale_items SET price=unit_price");
    }

  // ✅ حقول أصناف الكروت (Voucher Products)
  ua_ensure_column($conn, 'shop_products', 'is_voucher', "TINYINT(1) NOT NULL DEFAULT 0");
  ua_ensure_column($conn, 'shop_products', 'voucher_type', "ENUM('time','data','both') NOT NULL DEFAULT 'time'");
  ua_ensure_column($conn, 'shop_products', 'voucher_duration_minutes', "INT NULL");
  ua_ensure_column($conn, 'shop_products', 'voucher_data_bytes', "BIGINT NULL");
  ua_ensure_column($conn, 'shop_products', 'voucher_rate_limit', "VARCHAR(64) NOT NULL DEFAULT ''");
  ua_ensure_column($conn, 'shop_products', 'voucher_mt_profile', "VARCHAR(64) NOT NULL DEFAULT ''");
  ua_ensure_column($conn, 'shop_products', 'voucher_start_on_first_use', "TINYINT(1) NOT NULL DEFAULT 1");

  }
}

function shop_product_is_voucher(mysqli $conn, int $product_id): bool {
  $product_id = (int)$product_id;
  if ($product_id <= 0) return false;
  $q = $conn->query("SELECT is_voucher FROM shop_products WHERE id=$product_id LIMIT 1");
  if (!$q || !$q->num_rows) return false;
  $r = $q->fetch_assoc();
  return !empty($r['is_voucher']);
}

function shop_next_invoice_no(mysqli $conn): string {
  ensure_shop_tables($conn);
  $prefix = 'S-';
  $q = $conn->query("SELECT id FROM shop_sales ORDER BY id DESC LIMIT 1");
  $n = 1;
  if ($q && $q->num_rows) {
    $n = (int)$q->fetch_assoc()['id'] + 1;
  }
  return $prefix . str_pad((string)$n, 6, '0', STR_PAD_LEFT);
}

function shop_get_products(mysqli $conn): array {
  ensure_shop_tables($conn);
  $rows = [];
  $q = $conn->query("SELECT * FROM shop_products ORDER BY id DESC");
  if ($q) {
    while ($r = $q->fetch_assoc()) $rows[] = $r;
  }
  return $rows;
}

function shop_get_sale(mysqli $conn, int $id): ?array {
  ensure_shop_tables($conn);
  $id = (int)$id;
  $q = $conn->query("SELECT * FROM shop_sales WHERE id=$id LIMIT 1");
  return ($q && $q->num_rows) ? $q->fetch_assoc() : null;
}

function shop_get_sale_items(mysqli $conn, int $sale_id): array {
  ensure_shop_tables($conn);
  $sale_id = (int)$sale_id;
  $rows = [];
  $q = $conn->query("SELECT * FROM shop_sale_items WHERE sale_id=$sale_id ORDER BY id ASC");
  if ($q) {
    while ($r = $q->fetch_assoc()) $rows[] = $r;
  }
  return $rows;
}

function shop_create_sale(mysqli $conn, array $payload): int {
  ensure_shop_tables($conn);
  // ✅ لضمان سلامة المخزون والكروت، نستخدم Transaction
  $conn->begin_transaction();

  try {
    $invoice_no = $conn->real_escape_string((string)($payload['invoice_no'] ?? shop_next_invoice_no($conn)));
    $customer_name = $conn->real_escape_string((string)($payload['customer_name'] ?? ''));
    $customer_phone = $conn->real_escape_string((string)($payload['customer_phone'] ?? ''));
    $discount = (float)($payload['discount'] ?? 0);
    $notes = $conn->real_escape_string((string)($payload['notes'] ?? ''));
    $created_by = (int)($payload['created_by'] ?? 0);
    $agent_id = (int)($payload['agent_id'] ?? 0);
    if ($agent_id <= 0) $agent_id = 'NULL';

    $items = $payload['items'] ?? [];
    if (!is_array($items) || count($items) === 0) { $conn->rollback(); return 0; }

    // تحقق مبدئي للمخزون للكروت (لتفادي إنشاء فاتورة ثم الفشل)
    foreach ($items as $it) {
      $pid = (int)($it['product_id'] ?? 0);
      $qty = max(1, (int)($it['qty'] ?? 1));
      if ($pid > 0 && shop_product_is_voucher($conn, $pid)) {
        $q = $conn->query("SELECT stock_qty FROM shop_products WHERE id=$pid LIMIT 1");
        $have = ($q && $q->num_rows) ? (int)$q->fetch_assoc()['stock_qty'] : 0;
        if ($have < $qty) throw new RuntimeException("المخزون غير كافٍ لصنف الكرت (ID: $pid) — المتوفر: $have");
      }
    }

    $total = 0.0;
    foreach ($items as $it) {
      $qty = max(1, (int)($it['qty'] ?? 1));
      $unit = (float)($it['price'] ?? ($it['unit_price'] ?? 0));
      $total += ($qty * $unit);
    }
    $net = max(0, $total - $discount);

    $conn->query("INSERT INTO shop_sales (invoice_no, customer_name, customer_phone, total, discount, net_total, status, notes, created_by, agent_id)
      VALUES ('$invoice_no','$customer_name','$customer_phone',$total,$discount,$net,'PAID','$notes',$created_by,$agent_id)");

    $sale_id = (int)$conn->insert_id;
    if ($sale_id <= 0) { $conn->rollback(); return 0; }

    foreach ($items as $it) {
      $pid = (int)($it['product_id'] ?? 0);
      $name = $conn->real_escape_string((string)($it['name'] ?? ($it['item_name'] ?? '')));
      $qty = max(1, (int)($it['qty'] ?? 1));
      $unit = (float)($it['price'] ?? ($it['unit_price'] ?? 0));
      $line = $qty * $unit;

      $conn->query("INSERT INTO shop_sale_items (sale_id, product_id, name, qty, price, line_total)
        VALUES ($sale_id,".($pid>0?$pid:'NULL').",'$name',$qty,$unit,$line)");

      // خصم المخزون إذا المنتج مرتبط
      if ($pid > 0) {
        $conn->query("UPDATE shop_products SET stock_qty = GREATEST(stock_qty - $qty, 0) WHERE id=$pid");
      }

      // ✅ إذا كان المنتج "كروت" — خصّص كروت فعلية من جدول vouchers
      if ($pid > 0 && shop_product_is_voucher($conn, $pid)) {
        vouchers_ensure_tables($conn);
        // خذ أقدم كروت غير مباعة من نفس الصنف
        $sel = $conn->query("SELECT id FROM vouchers WHERE product_id=$pid AND status='new' ORDER BY id ASC LIMIT $qty FOR UPDATE");
        $ids = [];
        if ($sel) {
          while ($r = $sel->fetch_assoc()) $ids[] = (int)$r['id'];
        }
        if (count($ids) < $qty) {
          throw new RuntimeException("لا يوجد عدد كاف من الكروت الجاهزة لهذا الصنف (ID: $pid).");
        }
        $ids_sql = implode(',', array_map('intval', $ids));
        $sold_to_raw = $conn->real_escape_string((string)($payload['customer_name'] ?? ''));
        $by = (int)$created_by;
        $conn->query("UPDATE vouchers SET status='sold', sold_at=NOW(), sold_by=$by, sold_to='$sold_to_raw', shop_sale_id=$sale_id WHERE id IN ($ids_sql)");
        // سجل حركة استخدام
        if (function_exists('vouchers_log_many')) {
          vouchers_log_many($conn, $ids, 'sold_via_sale', ['sale_id'=>$sale_id, 'product_id'=>$pid, 'qty'=>$qty], $by, 'admin');
        }
      }
    }

    $conn->commit();
    return $sale_id;
  } catch (Throwable $e) {
    $conn->rollback();
    throw $e;
  }
}


function shop_set_sale_status(mysqli $conn, int $sale_id, string $status): bool {
  ensure_shop_tables($conn);
  $sale_id = (int)$sale_id;
  $st = $conn->real_escape_string($status);
  return (bool)$conn->query("UPDATE shop_sales SET status='$st' WHERE id=$sale_id");
}
