<?php
// src/functions.php
require_once __DIR__ . '/../config/db.php';

function generateInvoiceNo($pdo){
    $date = date('Ymd');
    $stmt = $pdo->prepare("SELECT COUNT(*) as cnt FROM sales WHERE DATE(created_at)=CURDATE()");
    $stmt->execute();
    $cnt = $stmt->fetch()['cnt'] ?? 0;
    return 'INV-'.$date.'-'.str_pad($cnt+1,4,'0',STR_PAD_LEFT);
}

/* Products */
function searchProducts($pdo,$term){
    $term = "%$term%";
    $stmt = $pdo->prepare("SELECT * FROM products WHERE name_ar LIKE ? OR name_en LIKE ? OR sku LIKE ? ORDER BY name_ar");
    $stmt->execute([$term,$term,$term]);
    return $stmt->fetchAll();
}
function getProduct($pdo,$id){ $stmt=$pdo->prepare('SELECT * FROM products WHERE id=?'); $stmt->execute([$id]); return $stmt->fetch(); }

/* Customers */
function getCustomers($pdo){ return $pdo->query('SELECT * FROM customers ORDER BY name')->fetchAll(); }

/* Sales */
function createSale($pdo,$customer_id,$items,$paid,$payment_method='cash'){
    $pdo->beginTransaction();
    try{
        $invoice = generateInvoiceNo($pdo);
        $total = 0;
        foreach($items as $it) $total += $it['qty'] * $it['unit_price'];
        $stmt = $pdo->prepare('INSERT INTO sales (invoice_no,customer_id,total,paid,payment_method) VALUES (?,?,?,?,?)');
        $stmt->execute([$invoice,$customer_id,$total,$paid,$payment_method]);
        $sale_id = $pdo->lastInsertId();
        $stmtItem = $pdo->prepare('INSERT INTO sale_items (sale_id,product_id,qty,unit_price,subtotal) VALUES (?,?,?,?,?)');
        $stmtUpdateStock = $pdo->prepare('UPDATE products SET stock = stock - ? WHERE id = ?');
        foreach($items as $it){
            $subtotal = $it['qty']*$it['unit_price'];
            $stmtItem->execute([$sale_id,$it['product_id'],$it['qty'],$it['unit_price'],$subtotal]);
            $stmtUpdateStock->execute([$it['qty'],$it['product_id']]);
        }
        // update customer balance if not fully paid
        if($customer_id && $paid < $total){
            $due = $total - $paid;
            $upd = $pdo->prepare("UPDATE customers SET balance = balance + ? WHERE id = ?");
            $upd->execute([$due, $customer_id]);
        }
        // record cash transaction if paid >0
        if($paid > 0){
            $stmtCash = $pdo->prepare("INSERT INTO cash_transactions (type, amount, description) VALUES ('in', ?, ?)");
            $stmtCash->execute([$paid, "Payment for invoice ".$invoice]);
        }
        $pdo->commit();
        return $sale_id;
    }catch(Exception $e){ $pdo->rollBack(); throw $e; }
}

/* Reports */
function getCashSummary($pdo, $from=null, $to=null){
    $from = $from ? $from.' 00:00:00' : date('Y-m-d').' 00:00:00';
    $to = $to ? $to.' 23:59:59' : date('Y-m-d H:i:s');
    $stmt = $pdo->prepare("SELECT type, SUM(amount) as sum_amount FROM cash_transactions WHERE created_at BETWEEN ? AND ? GROUP BY type");
    $stmt->execute([$from,$to]);
    $rows = $stmt->fetchAll();
    $res = ['in'=>0,'out'=>0];
    foreach($rows as $r) $res[$r['type']] = $r['sum_amount'];
    $res['balance'] = $res['in'] - $res['out'];
    return $res;
}

/* backup log */
function log_backup($pdo,$filename){
    $stmt=$pdo->prepare("INSERT INTO backups_log (filename) VALUES (?)");
    $stmt->execute([$filename]);
}
