<?php
class ModelInvoice extends Model
{
 

public function createInvoice($request, $store_id = null)
{
    // ---------------- Helpers ----------------
    $postNum = function(array $arr, string $key, $default = 0.0) {
        if (!isset($arr[$key])) return (float)$default;
        $val = trim((string)$arr[$key]); $val = str_replace(',', '.', $val);
        return is_numeric($val) ? (float)$val : (float)$default;
    };
    $postStr = function(array $arr, string $key, $default = '') {
        return isset($arr[$key]) ? trim((string)$arr[$key]) : $default;
    };
    // Inserta movimiento en banco (acepta montos negativos para extracci�n en Efectivo ARS)
    $addBankDeposit = function(PDO $db, array $p) {
        $numero_cheque  = array_key_exists('numero_cheque',  $p) ? (string)$p['numero_cheque'] : '';
        $fecha_cheque   = array_key_exists('fecha_cheque',   $p) ? $p['fecha_cheque'] : null; // ajusta si la col no admite NULL
        $importe_cheque = array_key_exists('importe_cheque', $p) ? (float)$p['importe_cheque'] : 0.0;
        $cotiz_dolar    = array_key_exists('cotizacion_dolar', $p) ? (float)$p['cotizacion_dolar'] : 0.0;

        $sqlInfo = "INSERT INTO `bank_transaction_info`
        (store_id, account_id, source_id, ref_no, invoice_id, transaction_type, title, details,
         numero_cheque, fecha_cheque, importe_cheque, cotizacion_dolar, image, created_by, created_at)
        VALUES (?,?,?,?,?,'deposit',?,?,?,?,?,?,?,?,?)";
        $stmt = $db->prepare($sqlInfo);
        $stmt->execute([
            $p['store_id'], $p['account_id'], $p['source_id'], $p['ref_no'], $p['invoice_id'],
            $p['title'], $p['details'], $numero_cheque, $fecha_cheque, $importe_cheque, $cotiz_dolar,
            $p['image'] ?? 'NULL', $p['user_id'], $p['created_at'],
        ]);
        $info_id = $db->lastInsertId();

        // Price (monto puede ser negativo -> extracci�n)
        $stmt = $db->prepare("INSERT INTO `bank_transaction_price` (store_id, info_id, ref_no, amount) VALUES (?,?,?,?)");
        $stmt->execute([$p['store_id'], $info_id, $p['ref_no'], $p['amount']]);

        // Saldos (suma con signo)
        $stmt = $db->prepare("UPDATE `bank_account_to_store` SET `deposit` = `deposit` + :amt WHERE `store_id` = :sid AND `account_id` = :acc");
        $stmt->execute([':amt'=>$p['amount'], ':sid'=>$p['store_id'], ':acc'=>$p['account_id']]);
        $stmt = $db->prepare("UPDATE `bank_accounts` SET `total_deposit` = `total_deposit` + :amt WHERE `id` = :acc");
        $stmt->execute([':amt'=>$p['amount'], ':acc'=>$p['account_id']]);
    };
    // ID autoincremental �manual� para tabla cheques (por si no es AUTO_INCREMENT)
    $nextChequeId = function(PDO $db) {
        $row = $db->query("SELECT IFNULL(MAX(cheque_id),0)+1 AS nid FROM cheques")->fetch(PDO::FETCH_ASSOC);
        return (int)$row['nid'];
    };

    // ------------- Inicio variables -------------
    $store_id   = $store_id ?: store_id();
    $user_id    = $request->post['salesman-id'] ? $request->post['salesman-id'] : user_id();
    $created_at = date_time();

    $product_items   = $request->post['product-item'];
    $total_items     = count($product_items);
    $invoice_note    = $request->post['invoice-note'];
    $favor_cuenta    = $postNum($request->post, 'favor-cuenta');

    $customer_id     = $request->post['customer-id'];
    $customer_mobile = $request->post['customer-mobile-number'];

    // pmethod-id = tipo de comprobante (2=venta, 12=remito)
    $doc_type        = (int)$request->post['pmethod-id'];

    $subtotal        = $postNum($request->post, 'sub-total');
    $discount_type   = $postStr($request->post, 'discount-type');
    $discount_amount = $postNum($request->post, 'discount-amount');
    $shipping_type   = $postStr($request->post, 'shipping-type', 'plain');
    $shipping_amount = $postNum($request->post, 'shipping-amount', 0);
    $others_charge   = $postNum($request->post, 'others-charge', 0);
    $previous_due    = $postNum($request->post, 'previous-due', 0);
    $order_tax       = $postNum($request->post, 'tax-amount');
    $payable_amount  = $postNum($request->post, 'payable-amount');
    $totalc          = $payable_amount;

    $paid_amount     = $postNum($request->post, 'paid-amount', 0);
    $total_paid      = $paid_amount;
    $qref            = $postStr($request->post, 'qref');
    $is_installment_order = !empty($request->post['is_installment_order']);
    $installment_interest_percentage = 0;
    $installment_interest_amount = 0;

    if (defined('INSTALLMENT') && INSTALLMENT && $is_installment_order) {
        $installment_initial_amount   = $paid_amount;
        $installment_duration         = (int)$request->post['installment_duration'];
        $installment_interval_count   = $request->post['installment_interval_count'];
        $installment_count            = (int)$request->post['installment_count'];
        $installment_interest_percentage = $postNum($request->post, 'installment_interest_percentage');
        $installment_interest_amount  = $postNum($request->post, 'installment_interest_amount');
        $installmentEndDate           = date('Y-m-d H:i:s', strtotime(' + ' . $installment_duration . ' days'));
    }

    $details_raw = isset($request->post['payment_details']) ? $request->post['payment_details'] : array();
    $details     = serialize($details_raw);

    // Deuda previa
    $st = $this->db->prepare("SELECT * FROM `customer_to_store` WHERE `customer_id` = ? AND `store_id` = ?");
    $st->execute([$customer_id, $store_id]);
    $deuda_previa = $st->fetch(PDO::FETCH_ASSOC);
    $deuda_previa2 = $deuda_previa ? $deuda_previa['due'] : 0;

    // --------- Lectura de medios de pago ----------
    // Cheques din�micos + datos extra
    $cheques = [];
    if (isset($request->post['numero-cheque']) && is_array($request->post['numero-cheque'])) {
        $nums   = $request->post['numero-cheque'];
        $fechas = $request->post['fecha-cheque'] ?? [];
        $imps   = $request->post['importe-cheque'] ?? [];
        $cuitS  = $request->post['cuit-cheque'] ?? [];
        $fPagoS = $request->post['fecha-pago-cheque'] ?? [];
        $tipoS  = $request->post['tipo-cheque'] ?? [];
        $bancoS = $request->post['banco-cheque'] ?? [];
        $obsS   = $request->post['observaciones-cheque'] ?? [];
        foreach ($nums as $i => $nro) {
            $nro = trim((string)$nro);
            $imp = isset($imps[$i]) ? (float)str_replace(',', '.', $imps[$i]) : 0.0;
            if ($nro !== '' && $imp > 0) {
                $cheques[] = [
                    'numero'     => $nro,
                    'fecha'      => isset($fechas[$i]) ? $fechas[$i] : null,
                    'importe'    => $imp,
                    'cuit'       => isset($cuitS[$i]) ? trim((string)$cuitS[$i]) : '',
                    'fecha_pago' => isset($fPagoS[$i]) ? $fPagoS[$i] : (isset($fechas[$i]) ? $fechas[$i] : null),
                    'tipo'       => isset($tipoS[$i]) ? trim((string)$tipoS[$i]) : '',
                    'banco'      => isset($bancoS[$i]) ? trim((string)$bancoS[$i]) : '',
                    'obs'        => isset($obsS[$i]) ? trim((string)$obsS[$i]) : '',
                ];
            }
        }
    } else {
        // Compat 1..5 (sin campos nuevos)
        for ($i=1; $i<=5; $i++) {
            $nro = $postStr($request->post, "numero-cheque{$i}");
            $imp = $postNum($request->post, "importe-cheque{$i}");
            $fec = $postStr($request->post, "fecha-cheque{$i}");
            if ($nro !== '' && $imp > 0) {
                $cheques[] = [
                    'numero'=>$nro,'fecha'=>$fec,'importe'=>$imp,
                    'cuit'=>'','fecha_pago'=>$fec,'tipo'=>'','banco'=>'','obs'=>''
                ];
            }
        }
    }

    $importe_deposito    = $postNum($request->post, 'importe-deposito');
    $numero_deposito     = $postStr($request->post, 'numero-deposito');
    $importe_efectivo    = $postNum($request->post, 'importe-efectivo');   // puede ser NEGATIVO (vuelto)
    $importe_dolar       = $postNum($request->post, 'importe-dolar');
    $cotizacion_dolar    = $postNum($request->post, 'cotizacion-dolar');
    $importe_acuenta     = $postNum($request->post, 'importe-acuenta');
    $cotizacion_billete  = $postNum($request->post, 'cotizacion-billete');

    // ---------------- Validaciones ----------------
    if ($cotizacion_dolar == 0) {
        throw new Exception(trans('ATENCION, verifique la cotizacion de esta venta, si es pago TOTAL debe poner 1 Si es a cuenta parte o el total debe poner la cotizacion del dia'));
    }
    if ($cotizacion_dolar == 1 && $importe_acuenta > 0) {
        throw new Exception(trans('ATENCION, El tipo de cambio no debe ser uno sino el valor del dolar oficial'));
    }
    if ($customer_id == 1 && $importe_acuenta > 0) {
        throw new Exception(trans('El cliente Consumidor final no puede crear deuda'));
    }

    // Estado pago
    $payment_status = $importe_acuenta > 0 ? 'due' : 'paid';

    // Totales ARS para contrapartida (efectivo puede ser < 0)
    $cheques_ars       = array_sum(array_column($cheques, 'importe'));
    $otros_medios_ars  = $importe_efectivo + $importe_deposito + ($importe_dolar * $cotizacion_billete) + ($importe_acuenta * $cotizacion_dolar);
    $pagos_totales_ars = $cheques_ars + $otros_medios_ars;

    // En factura (2) sin "a cuenta", contrapartida debe igualar total (tol �0.20)
    if ($doc_type == 2 && $importe_acuenta == 0) {
        $resto = $totalc - $pagos_totales_ars;
        if ($resto > 0.2 || $resto < -0.2) {
            throw new Exception(trans('ERROR, El importe del comprobante NO es igual que la contrapartida de pagos'));
        }
    }

    // Dolarizar para almacenar (efectivo negativo reduce el total pagado)
    $paid_amount     = ($cheques_ars + $importe_efectivo + $importe_deposito + ($importe_dolar * $cotizacion_billete)) / $cotizacion_dolar;
    $payable_amount  = $payable_amount / $cotizacion_dolar;

    $balance = 0;
    $due     = max($payable_amount - $paid_amount, 0);
    if ($paid_amount > $payable_amount) {
        $balance = $paid_amount - $payable_amount;
        $paid_amount = $payable_amount;
        $due = 0;
    }

    $product_discount = $total_items > 0 ? ($discount_amount / $total_items) : 0;
    $product_tax      = $total_items > 0 ? ($order_tax / $total_items) : 0;

    // Detalle legible
    $detParts = [];
    foreach ($cheques as $c) {
        $detParts[] = "Cheque: {$c['numero']} Importe: " . currency_format($c['importe'])
            . ($c['fecha'] ? " Fecha: {$c['fecha']}" : "")
            . ($c['cuit'] ? " CUIT: {$c['cuit']}" : "")
            . ($c['banco'] ? " Banco: {$c['banco']}" : "")
            . ($c['tipo'] ? " Tipo: {$c['tipo']}" : "");
    }
    if ($importe_efectivo != 0)  $detParts[] = 'Caja Pesos: ' . currency_format($importe_efectivo);
    if ($importe_deposito > 0)   $detParts[] = 'Caja Dep�sito: ' . currency_format($importe_deposito) . ($numero_deposito ? " Ref: {$numero_deposito}" : "");
    if ($importe_dolar > 0)      $detParts[] = 'USD billete: ' . currency_format($importe_dolar);
    if ($cotizacion_billete > 0) $detParts[] = 'Cotizaci�n Billete: ' . currency_format($cotizacion_billete);
    if ($cotizacion_dolar > 0)   $detParts[] = 'TC Interno: ' . currency_format($cotizacion_dolar);
    $detallepago = ($doc_type == 2 ? implode(' | ', $detParts) . ' ' . $invoice_note : $invoice_note);

    // ---------------- Transacci�n ----------------
    $this->db->beginTransaction();
    try {
        $invoice_id = generate_invoice_id('sell');

        $item_tax = 0; $igst = 0; $cgst = 0; $sgst = 0;
        $tcgst = 0; $tsgst = 0; $tigst = 0;
        $total_purchase_price = 0;

        foreach ($product_items as $product) {
            $product_id        = $product['item_id'];
            $product_info      = get_the_product($product_id);
            $category_id       = $product['category_id'];
            $brand_id          = $product_info['brand_id'];
            $sup_id            = $product['sup_id'];
            $product_name      = $product['item_name'];
            $product_quantity  = $product['item_quantity'];
            $quantity_substract= $product_quantity;

            if ($product['p_type'] == 'service') {
                $quantity_substract = 0;
                $total_purchase_price += ($product_info['purchase_price'] * $product_quantity);
            }

            $statement = $this->db->prepare("SELECT item_quantity FROM `quotation_item` WHERE `reference_no` = ? AND `item_id` = ?");
            $statement->execute([$qref, $product_id]);

            $product_price = $product['item_price'];
            $product_total = $product['item_total'];
            $purchase_invoice_id = NULL;
            $item_purchase_price = 0;
            $tax_method = $product_info['tax_method'];
            $taxrate_id = $product_info['taxrate_id'];
            $taxrate    = 0;
            $tax        = 0;

            if ($product_info['taxrate']) {
                $taxrate = $product_info['taxrate']['taxrate'];
            }
            $item_tax += $tax;

            if (get_the_customer($customer_id, 'customer_state') == get_preference('business_state')) {
                $cgst = $tax / 2; $sgst = $tax / 2; $tcgst += $cgst; $tsgst += $sgst;
            } else { $igst = $tax; $tigst += $tax; }

            $item_tipo = $doc_type;

            $sql = "INSERT INTO `selling_item`
            (invoice_id, store_id, item_id, category_id, brand_id, sup_id, item_name, item_purchase_price, item_price, item_discount, item_tax, tax_method, taxrate_id, tax, gst, cgst, sgst, igst, item_quantity, item_total, purchase_invoice_id, qreferencia_id, tipo)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
            ON DUPLICATE KEY UPDATE
                store_id=VALUES(store_id), category_id=VALUES(category_id), brand_id=VALUES(brand_id),
                sup_id=VALUES(sup_id), item_name=VALUES(item_name), item_purchase_price=VALUES(item_purchase_price),
                item_price=VALUES(item_price), item_discount=VALUES(item_discount), item_tax=VALUES(item_tax),
                tax_method=VALUES(tax_method), taxrate_id=VALUES(taxrate_id), tax=VALUES(tax), gst=VALUES(gst),
                cgst=VALUES(cgst), sgst=VALUES(sgst), igst=VALUES(igst), item_quantity=VALUES(item_quantity),
                item_total=VALUES(item_total), purchase_invoice_id=VALUES(purchase_invoice_id),
                qreferencia_id=VALUES(qreferencia_id), tipo=VALUES(tipo)";
            $statement = $this->db->prepare($sql);
            $statement->execute([
                $invoice_id, $store_id, $product_id, $category_id, $brand_id, $sup_id, $product_name,
                $item_purchase_price, $product_price, $product_discount, $tax, $tax_method, $taxrate_id,
                $taxrate, $taxrate, $cgst, $sgst, $item_tipo, $product_quantity, $product_total,
                $purchase_invoice_id, $qref, 0
            ]);

            if ($quantity_substract > 0) {
                $statement = $this->db->prepare("UPDATE `product_to_store` SET `quantity_in_stock` = `quantity_in_stock` - {$quantity_substract} WHERE `store_id` = ? AND `product_id` = ?");
                $statement->execute([$store_id, $product_id]);
            }

            if ($qref) {
                $statement = $this->db->prepare("SELECT * FROM `quotation_info` WHERE `store_id` = ? AND `reference_no` = ?");
                $statement->execute([$store_id, $qref]);
                $quotation_info = $statement->fetch(PDO::FETCH_ASSOC);
                $tipo_quotation = $quotation_info ? $quotation_info['status'] : null;

                if ($tipo_quotation != "presupuesto") {
                    $statement = $this->db->prepare("UPDATE `quotation_info` SET `is_order` = ?, `invoice_id` = ? WHERE `reference_no` = ?");
                    $statement->execute([2, $invoice_id, $qref]);
                }

                $statement = $this->db->prepare("UPDATE `quotation_item`
                    SET `cantidad_entregada` = `cantidad_entregada` + {$quantity_substract},
                        `item_quantity` = `item_quantity` - {$quantity_substract}
                    WHERE `reference_no` = ? AND `item_id` = ?");
                $statement->execute([$qref, $product_id]);

                $statement = $this->db->prepare("UPDATE `product_to_store` SET `venta_anticipada` = `venta_anticipada` - {$quantity_substract} WHERE `store_id` = ? AND `product_id` = ?");
                $statement->execute([$store_id, $product_id]);
            }
        }

        if (get_the_customer($customer_id, 'customer_state') == get_preference('business_state')) {
            $tcgst += ($order_tax / 2); $tsgst += ($order_tax / 2);
        } else { $tigst += $order_tax; }

        $capital = ($subtotal - $discount_amount) > 0
            ? ($total_purchase_price / ($subtotal - $discount_amount)) * ($paid_amount - $shipping_amount - $others_charge)
            : 0;
        $profit = ($subtotal - $discount_amount) - $total_purchase_price;

        // selling_info
        $statement = $this->db->prepare("INSERT INTO `selling_info`
        (invoice_id, store_id, customer_id, customer_mobile, invoice_note, total_items, pmethod_id, payment_status, is_installment, created_by, created_at)
        VALUES (?,?,?,?,?,?,?,?,?,?,?)");
        $statement->execute([$invoice_id, $store_id, $customer_id, $customer_mobile, $invoice_note, $total_items, $doc_type, $payment_status, $is_installment_order, $user_id, $created_at]);

        if ($qref) {
            $statement = $this->db->prepare("INSERT INTO `quotation_entrega` (sell_id, quotation_id, customer_id, user_id, notas) VALUES (?,?,?,?,?)");
            $statement->execute([$invoice_id, $qref, $customer_id, $user_id, $invoice_note]);
        }

	if($doc_type == 12) { 
		
		$payable_amount  = 0;
		$paid_amount = 0;
		$cotizacion_dolar = 0;
		}
        // selling_price (compat: guardo $doc_type en igst)
        $statement = $this->db->prepare("INSERT INTO `selling_price`
        (invoice_id, store_id, subtotal, discount_type, discount_amount, interest_amount, interest_percentage, item_tax, order_tax, cgst, sgst, igst, total_purchase_price, shipping_type, shipping_amount, others_charge, previous_due, payable_amount, paid_amount, due, prev_due_paid, profit, balance)
        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		
	
		
        $statement->execute([
            $invoice_id, $store_id, $subtotal, $discount_type, $discount_amount, $installment_interest_amount,
            $installment_interest_percentage, $item_tax, $order_tax, $tcgst, $tsgst, $doc_type,
            $total_purchase_price, $shipping_type, $shipping_amount, $cotizacion_dolar, $deuda_previa2,
            $payable_amount, $paid_amount, $importe_acuenta, 0, $profit, $balance
        ]);

        // payments + checkout
        if ($paid_amount > 0 || $importe_efectivo < 0) { // registra aunque sea extracci�n neta
            $statement = $this->db->prepare("INSERT INTO `payments`
            (store_id, invoice_id, pmethod_id, capital, amount, details, note, total_paid, pos_balance, created_by, created_at)
            VALUES (?,?,?,?,?,?,?,?,?,?,?)");
            $statement->execute([$store_id, $invoice_id, $doc_type, $capital, $paid_amount, $details, $detallepago, $total_paid, $balance, $user_id, $created_at]);

            $statement = $this->db->prepare("UPDATE `selling_info` SET `pmethod_id` = ?, `checkout_status` = 1 WHERE `invoice_id` = ? AND `store_id` = ?");
            $statement->execute([$doc_type, $invoice_id, $store_id]);
        }

        // Log
        $reference_no = generate_sell_log_ref_no('sell');
        $statement = db()->prepare("INSERT INTO `sell_logs`
        (customer_id, reference_no, type, pmethod_id, description, amount, store_id, ref_invoice_id, created_by, created_at)
        VALUES (?,?,?,?,?,?,?,?,?,?)");
        $statement->execute([$customer_id, $reference_no, 'sell', $doc_type, 'Paid while selling', $paid_amount, $store_id, $invoice_id, $user_id, $created_at]);

        if ($importe_acuenta > 0) {
            $statement = $this->db->prepare("UPDATE `customer_to_store` SET `due` = `due` + {$importe_acuenta} WHERE `store_id` = ? AND `customer_id` = ?");
            $statement->execute([$store_id, $customer_id]);
        }

        // --------- Movimientos de caja/banco ----------
        if (($account_id = store('deposit_account_id'))) {
            $st = $this->db->prepare("SELECT `source_id` FROM `income_sources` WHERE `for_sell` = 1");
            $st->execute();
            $row = $st->fetch(PDO::FETCH_ASSOC);
            $source_id = $row ? $row['source_id'] : null;

            $ref_no = unique_transaction_ref_no();
            $title  = 'Ingreso por venta';
            $detailsBase = 'Nombre de cliente: ' . get_the_customer($customer_id, 'customer_name') .
                           ' | Detalle: ' . $invoice_note .
                           ' | Remito: ' . $invoice_id;

            // Cuenta de Cheques (configurable)
            $cheques_account_id = (int)(store('cheque_account_id') ?: 2);

            // 1) Cheques -> banco + tabla cheques
            foreach ($cheques as $c) {
                // Banco
                $addBankDeposit($this->db, [
                    'store_id'=>$store_id, 'account_id'=>$c['tipo'], 'source_id'=>$source_id, 'ref_no'=>$ref_no,
                    'invoice_id'=>$invoice_id, 'title'=>$title, 'details'=>$detailsBase, 'amount'=>$c['importe'],
                    'numero_cheque'=>$c['numero'], 'fecha_cheque'=>$c['fecha'], 'importe_cheque'=>$c['importe'],
                    'user_id'=>$user_id, 'created_at'=>$created_at
                ]);
                // Tabla cheques (con nuevos campos)
                $cheque_id = $nextChequeId($this->db);
                $stmtCh = $this->db->prepare("INSERT INTO `cheques`
                    (cheque_id, numero, fecha, fecha_pago, importe, cuit, ref_id, invoice_no, caja_id, status, invoice_id, tipo_cheque, banco, observaciones)
                    VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                $stmtCh->execute([
                    $cheque_id,
                    $c['numero'],
                    $c['fecha'] ?: date('Y-m-d'),
                    $c['fecha_pago'] ?: ($c['fecha'] ?: date('Y-m-d')),
                    (string)$c['importe'],
                    $c['cuit'] ?: '',
                    $ref_no,
                    (string)$invoice_id,
                    $c['tipo'],
                    'in',
                    (string)$invoice_id,
                    $c['tipo'] ?: '',
                    $c['banco'] ?: '',
                    $c['obs'] ?: '',
                ]);
            }

            // 2) Efectivo ARS (puede ser NEGATIVO -> extracci�n para vuelto)
            if ($importe_efectivo != 0) {
                $addBankDeposit($this->db, [
                    'store_id'=>$store_id,'account_id'=>1,'source_id'=>$source_id,'ref_no'=>$ref_no,
                    'invoice_id'=>$invoice_id,'title'=>$title,'details'=>$detailsBase,
                    'amount'=>$importe_efectivo, // < 0 => extracci�n
                    'user_id'=>$user_id,'created_at'=>$created_at,
                    'numero_cheque' => '', 'importe_cheque' => 0, 'fecha_cheque' => null,
                ]);
            }

            // 3) Dep�sito/Transferencia ARS
            if ($importe_deposito > 0) {
                $addBankDeposit($this->db, [
                    'store_id'=>$store_id,'account_id'=>11,'source_id'=>$source_id,'ref_no'=>$ref_no,
                    'invoice_id'=>$invoice_id,'title'=>$title,
                    'details'=>$detailsBase . ($numero_deposito ? " | Ref: {$numero_deposito}" : ''),
                    'amount'=>$importe_deposito,'user_id'=>$user_id,'created_at'=>$created_at,
                    'numero_cheque' => '', 'importe_cheque' => 0, 'fecha_cheque' => null,
                ]);
            }

            // 4) USD billete -> cuenta 5 (cotizaci�n)
            if ($importe_dolar > 0 && $cotizacion_dolar > 0) {
                $addBankDeposit($this->db, [
                    'store_id'=>$store_id,'account_id'=>5,'source_id'=>$source_id,'ref_no'=>$ref_no,
                    'invoice_id'=>$invoice_id,'title'=>$title,'details'=>$detailsBase,
                    'amount'=>$importe_dolar,'cotizacion_dolar'=>$cotizacion_dolar,
                    'user_id'=>$user_id,'created_at'=>$created_at,
                    'numero_cheque' => '', 'importe_cheque' => 0, 'fecha_cheque' => null,
                ]);
            }

            // 5) Saldo a favor del cliente
            if (!empty($favor_cuenta) && $favor_cuenta > 0) {
                $stmt = $this->db->prepare("UPDATE `customer_to_store` SET `balance` = `balance` + :f WHERE `store_id` = :s AND `customer_id` = :c");
                $stmt->execute([':f'=>$favor_cuenta, ':s'=>$store_id, ':c'=>$customer_id]);
            }
        }

        // --------- Cuotas (si aplica) ----------
        if (defined('INSTALLMENT') && INSTALLMENT && $is_installment_order) {
            $statement = $this->db->prepare("INSERT INTO `installment_orders`
            (store_id, invoice_id, duration, interval_count, installment_count, interest_percentage, interest_amount, initial_amount, last_installment_date, installment_end_date, created_at)
            VALUES (?,?,?,?,?,?,?,?,?,?,?)");
            $statement->execute([$store_id, $invoice_id, $installment_duration, $installment_interval_count, $installment_count, $installment_interest_percentage, $installment_interest_amount, $installment_initial_amount, $created_at, $installmentEndDate, $created_at]);

            $initInterestAmount = ($installment_interest_percentage / 100) * ($paid_amount - $installment_interest_amount);
            $eachInstallmentAmount = $due / $installment_count;
            $eachInstallmentInterestAmount = ($installment_interest_amount - $initInterestAmount) / $installment_count;

            if ($paid_amount > 0) {
                $capital_init = ($subtotal > 0) ? ($total_purchase_price / $subtotal) * ($paid_amount - $initInterestAmount) : 0;
                $statement = $this->db->prepare("INSERT INTO `installment_payments`
                (store_id, invoice_id, payment_date, created_by, note, capital, interest, payable, paid, payment_status)
                VALUES (?,?,?,?,?,?,?,?,?,?)");
                $statement->execute([$store_id, $invoice_id, date_time(), $user_id, 'Initial Payment', $capital_init, $initInterestAmount, $paid_amount, $paid_amount, 'paid']);
            }

            $interval = $installment_interval_count;
            for ($i=0; $i<$installment_count; $i++) {
                $capital_i = ($subtotal > 0 && $eachInstallmentAmount > 0)
                    ? ($total_purchase_price / $subtotal) * ($eachInstallmentAmount - $eachInstallmentInterestAmount)
                    : 0;
                $payment_date   = date('Y-m-d', strtotime(' + ' . $interval . ' days'));
                $payment_status = $eachInstallmentAmount > 0 ? 'due' : 'paid';

                $statement = $this->db->prepare("INSERT INTO `installment_payments`
                (store_id, invoice_id, payment_date, created_by, capital, interest, payable, due, payment_status)
                VALUES (?,?,?,?,?,?,?,?,?)");
                $statement->execute([$store_id, $invoice_id, $payment_date, $user_id, $capital_i, $eachInstallmentInterestAmount, $eachInstallmentAmount, $eachInstallmentAmount, $payment_status]);

                $interval += $installment_interval_count;
            }
        }

        $this->db->commit();
        return $invoice_id;

    } catch (Exception $e) {
        $this->db->rollBack();
        throw $e;
    }
}




  
function duePaid($data, $store_id = null)
{
    $store_id   = $store_id ? $store_id : store_id();
    $user_id    = user_id();
    $created_at = date_time();

    // ===== Entrada principal =====
    $invoice_id    = $data['invoice-id'];
    $customer_id   = $data['customer-id'];
    $pmethod_id    = $data['pmethod-id'];
    $pmethod_code  = get_the_pmethod($pmethod_id,'code_name');
    $invoice_price = $this->getSellingPrice($invoice_id, $store_id);

    // Siempre recibo a cuenta
    $tipo_recibo = 2;

    // paid_amount viene del form (pagoPesos/cotiDolar). Si es 0, luego hacemos fallback con d�lar billete.
    $paid_amount = isset($data['paid-amount']) && is_numeric($data['paid-amount']) ? (float)$data['paid-amount'] : 0.0;
    $total_paid  = $paid_amount;
    $note        = isset($data['note']) ? $data['note'] : '';

    // Contrapartidas
    $importe_cheque_t   = isset($data['importecheque'])     ? (float)$data['importecheque']     : 0;
    $importe_efectivo   = isset($data['importeefectivo'])   ? (float)$data['importeefectivo']   : 0; // puede ser NEGATIVO (vuelto)
    $importe_deposito   = isset($data['importedeposito'])   ? (float)$data['importedeposito']   : 0;
    $numero_deposito    = isset($data['numerodeposito'])    ? trim($data['numerodeposito'])     : '';
    $importe_retencion  = isset($data['importeretencion'])  ? (float)$data['importeretencion']  : 0;
    $numero_retencion   = isset($data['numeroretencion'])   ? trim($data['numeroretencion'])    : '';
    $importe_descuento  = isset($data['importedescuento'])  ? (float)$data['importedescuento']  : 0;

    // D�lar
    $importe_dolar      = isset($data['importedolar'])      ? (float)$data['importedolar']      : 0;
    $cotizacion_dolar   = isset($data['cotizaciondolar'])   ? (float)$data['cotizaciondolar']   : 0; // �oficial� (para contabilidad)
    $cotizacion_billete = isset($data['cotizacionbillete']) ? (float)$data['cotizacionbillete'] : 0; // �billete� (lo que entra a caja d�lar)

    // Detalle libre (gift card / tarjeta si tu motor lo usa)
    $details_raw = isset($data['payment_details']) ? $data['payment_details'] : array();
    $details     = serialize($details_raw);

    // ===== Gift card opcional =====
    $is_card_payments = false;
    $card_no = '';
    if (isset($details_raw['card_no'])) {
        $card_no = $details_raw['card_no'];
        $statement = db()->prepare("SELECT * FROM `gift_cards` WHERE `customer_id` = ? AND `card_no` = ? AND `balance` >= ? AND `expiry` > NOW()");
        $statement->execute(array($customer_id, $card_no, $invoice_price['payable_amount']));
        $row = $statement->fetch(PDO::FETCH_ASSOC);
        if ($row) {
            $is_card_payments = true;
            $paid_amount = $row['balance'];
            $total_paid  = $paid_amount;
        } else {
            throw new Exception(trans('error_not_found_or_insufficient_balance'));
        }
    }

    // Deuda previa (para logs y selling_price.previous_due)
    $statement = $this->db->prepare("SELECT * FROM `customer_to_store` WHERE `customer_id` = ?  AND `store_id` = ?");
    $statement->execute(array($customer_id, $store_id ));
    $deuda = $statement->fetch(PDO::FETCH_ASSOC);
    $deuda_prev_total = $deuda ? (float)$deuda['due'] : 0.0;

    // Cr�dito (saldo a favor del cliente) como medio de pago
    if ($pmethod_code == 'credit') {
        $balance_cliente = get_customer_balance($customer_id);
        $payable_amount  = $invoice_price['payable_amount'] - ($invoice_price['paid_amount'] + $invoice_price['return_amount']);
        if ($payable_amount > $balance_cliente) {
            throw new Exception(trans('error_insufficient_balance'));
        }
        $paid_amount = $payable_amount;
        $total_paid  = $paid_amount;
    }

    // ===== Fallback si solo pagan con d�lar billete =====
    if ($paid_amount <= 0 && $importe_dolar > 0 && $cotizacion_billete > 0 && $cotizacion_dolar > 0) {
        // paid_amount contable en d�lares �oficiales�
        $paid_amount = round(($importe_dolar * $cotizacion_billete) / $cotizacion_dolar, 4);
        $total_paid  = $paid_amount;
    }


    if ($paid_amount <= 0) {
        throw new Exception('Debe ingresar un monto a pagar (en pesos o en d�lar billete).');
    }

    // ===== Due / balance del comprobante original (si existiera) =====
    $discount_amount = 0; // descuento removido del flujo
    $due     = $invoice_price['due'] - ($paid_amount + $discount_amount);
    $due     = $due > 0 ? $due : 0;
    $balance = 0;

    // ===== Si excede el total de la boleta (cuando no es recibo a cuenta) =====
    // (en recibo a cuenta generamos un nuevo invoice_id, m�s abajo)
    if ($paid_amount > ($invoice_price['payable_amount'] - $invoice_price['paid_amount']) && $tipo_recibo == 1) {
        $due        = 0;
        $balance    = $paid_amount - ($invoice_price['payable_amount'] - $invoice_price['paid_amount']);
        $paid_amount= $paid_amount - $balance;
    }

    // ===== Recibo a cuenta: generar invoice_id propio =====
    if ($tipo_recibo == 2) {
        $invoice_id = generate_invoice_id('sell');
    }

    // Si medio es cr�dito, descontar saldo del cliente
    if ($pmethod_code == 'credit') {
        $statement = db()->prepare("UPDATE `customer_to_store` SET `balance` = `balance` - {$paid_amount} WHERE `store_id` = ? AND `customer_id` = ?");
        $statement->execute(array($store_id, $customer_id));
    }

    // ===== Capital (para reparto contable) =====
    $partial_shipping_charge = $invoice_price['payable_amount'] > 0 ? ($invoice_price['shipping_amount'] / $invoice_price['payable_amount']) * $paid_amount : 0;
    $partial_others_charge   = $invoice_price['payable_amount'] > 0 ? ($invoice_price['others_charge']   / $invoice_price['payable_amount']) * $paid_amount : 0;
    $partial_order_tax       = $invoice_price['payable_amount'] > 0 ? ($invoice_price['order_tax']       / $invoice_price['payable_amount']) * $paid_amount : 0;
    $capital = ($invoice_price['payable_amount'] > 0)
        ? (($invoice_price['total_purchase_price'] / $invoice_price['payable_amount']) * $paid_amount) + $partial_order_tax + $partial_shipping_charge + $partial_others_charge
        : 0;

    // ===== Texto detalle �bonito� =====
    $detallepago = trim(implode(' ', array_filter([
        $importe_efectivo != 0   ? ('Caja Pesos: ' . currency_format($importe_efectivo)) : '',
        $importe_cheque_t > 0    ? ('Caja Cheque: ' . currency_format($importe_cheque_t)) : '',
        $importe_deposito > 0    ? ('Caja Deposito: ' . currency_format($importe_deposito) . ($numero_deposito ? (' N�:' . $numero_deposito) : '')) : '',
        $importe_retencion > 0   ? ('Caja Retencion: ' . currency_format($importe_retencion) . ($numero_retencion ? (' N�:' . $numero_retencion) : '')) : '',
        $cotizacion_dolar > 0    ? ('Cotizacion: ' . currency_format($cotizacion_dolar)) : '',
        $importe_descuento > 0    ? ('Decuento: ' . currency_format($importe_descuento)) : '',
        $cotizacion_billete > 0  ? ('Cotiz. Billete: ' . currency_format($cotizacion_billete)) : '',
        $importe_dolar > 0       ? ('USD billete: ' . currency_format($importe_dolar)) : '',
        $note ? $note : ''
    ])));

    // ===== Control de contrapartida (ajustable con negativo en efectivo) =====
    // Lo �esperado� en pesos: paid_amount * cotizaci�n oficial
    $totalc_d = $cotizacion_dolar > 0 ? $paid_amount * $cotizacion_dolar : 0;

    // Lo imputado en cajas (pesos): cheques + efectivo (+/-) + dep�sito + retenci�n + (usd*cotiz.billete)
    $pagostotales = $importe_cheque_t + $importe_descuento + $importe_efectivo + $importe_deposito + $importe_retencion + ($importe_dolar * $cotizacion_billete);

    // Tolerancia m�nima
    $tolerancia = 0.07;
    $diff = $pagostotales - $totalc_d; // >0 sobra, <0 falta
    if (abs($diff) > $tolerancia) {
        $msg = "CONTRAPARTIDA DESCUADRADA\n\n"
             . "Esperado (pesos): " . number_format($totalc_d, 2) . "\n"
             . "Imputado (pesos): " . number_format($pagostotales, 2) . "\n"
             . "Diferencia: "      . number_format($diff, 2) . "\n\n"
             . "Ajuste el campo 'Caja Diaria Pesos'. Si hay vuelto, ingr�selo NEGATIVO.";
        throw new Exception($msg);
    }

    // ===== Registrar pago (tabla payments) =====
    $statement = db()->prepare("INSERT INTO `payments`
        (type, store_id, invoice_id, pmethod_id, capital, amount, details, note, total_paid, pos_balance, created_by, created_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
    $statement->execute(array('due_paid', $store_id, $invoice_id, $pmethod_id, $capital, $paid_amount, $details, $detallepago, $total_paid, 0, $user_id, $created_at));

    // ===== Crear �comprobante� de recibo a cuenta =====
    $reference_no = generate_sell_log_ref_no('sell');

    $statement = db()->prepare("INSERT INTO `selling_info`
        (invoice_id, store_id, customer_id, invoice_note, total_items, pmethod_id, payment_status, checkout_status, created_by, created_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
    $statement->execute(array($invoice_id, $store_id, $customer_id, $detallepago, $total_paid, $pmethod_id, 'paid', '1', $user_id, $created_at));

    $statement = db()->prepare("INSERT INTO `selling_price`
        (invoice_id, store_id, subtotal, igst, others_charge, payable_amount, paid_amount, due, previous_due)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
    // Nota: guardo cotizaci�n en IGST para tenerlo a mano (ya lo ven�as usando as�)
    $statement->execute(array($invoice_id, $store_id, $total_paid, $pmethod_id, $cotizacion_dolar, '0', $total_paid, 0, $deuda_prev_total));

    // ===== Log de venta =====
    $statement = db()->prepare("INSERT INTO `sell_logs`
        (customer_id, reference_no, type, pmethod_id, description, amount, store_id, ref_invoice_id, created_by, created_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
    $statement->execute(array($customer_id, $reference_no, 'sell', $pmethod_id, 'Pago de cuenta corriente', $paid_amount, $store_id, $invoice_id, $user_id, $created_at));

    // ===== Actualizar deuda del cliente (baja por el pago a cuenta) =====
    $statement = db()->prepare("UPDATE `customer_to_store` SET `due` = GREATEST(`due` - {$paid_amount}, 0) WHERE `store_id` = ? AND `customer_id` = ?");
    $statement->execute(array($store_id, $customer_id));

    // ===== Gift card: bajar saldo =====
    if ($paid_amount > 0 && $is_card_payments && $card_no) {
        $statement = db()->prepare("UPDATE `gift_cards` SET `balance` = `balance` - $paid_amount WHERE `card_no` = ?");
        $statement->execute(array($card_no));
    }

    // ===== Dep�sitos en cajas (y retiros si efectivo < 0) =====
    if (($account_id = store('deposit_account_id')) && $total_paid > 0) {

        $ref_no = unique_transaction_ref_no();
        $ref_no++;

        // Source para due collection
        $statement = db()->prepare("SELECT `source_id`, `dolar` FROM `income_sources` WHERE `for_due_collection` = 1");
        $statement->execute();
        $source    = $statement->fetch(PDO::FETCH_ASSOC);
        $source_id = $source ? (int)$source['source_id'] : null;

        $title   = 'Ingreso por pago de cuenta corriente';
        $details_info_base = 'Cliente: ' . get_the_customer($customer_id, 'customer_name')
                           . ' | TC: ' . $cotizacion_dolar
                           . ' | USD pagado: ' . $total_paid;

        // === CHEQUES (m�ltiples) ===
        if ($importe_cheque_t != 0) {
            $num_cheques        = $_POST['num_cheque']           ?? [];
            $fechas_cheques     = $_POST['fecha_cheque']         ?? [];
            $fechas_pago_cheque = $_POST['fecha_pago_cheque']    ?? [];
            $importes_cheques   = $_POST['importe_cheque']       ?? [];
            $cuits_titulares    = $_POST['cuit_titular']         ?? [];
            $cajas_cheques      = $_POST['caja_id']              ?? [];
            $tipos_cheque       = $_POST['tipo_cheque']          ?? [];
            $bancos_cheque      = $_POST['banco_cheque']         ?? [];
            $obs_cheque         = $_POST['observaciones_cheque'] ?? [];

            $status_che = 'in';
            $count = count($num_cheques);

            for ($i = 0; $i < $count; $i++) {
                $num_cheque   = trim($num_cheques[$i]        ?? '');
                $fecha_cheque = trim($fechas_cheques[$i]     ?? '');
                $fecha_pago   = trim($fechas_pago_cheque[$i] ?? '');
                $importe_ch   = (float)($importes_cheques[$i]?? 0);
                $cuit_t       = trim($cuits_titulares[$i]    ?? '');
                $caja_id_ch   = (int)  ($cajas_cheques[$i]   ?? 2);

                $tipo_ch      = strtolower(trim($tipos_cheque[$i] ?? ''));
                if ($tipo_ch !== 'fisico' && $tipo_ch !== 'echeq') {
                    $tipo_ch = ($caja_id_ch == 17 ? 'echeq' : 'fisico');
                }
                $banco        = trim($bancos_cheque[$i]      ?? '');
                $observ       = trim($obs_cheque[$i]         ?? '');

                if ($num_cheque === '' || $fecha_cheque === '' || $importe_ch <= 0) {
                    continue;
                }
                if ($fecha_pago === '') $fecha_pago = $fecha_cheque;

                $details_info = $details_info_base . ' | Cheque ' . $num_cheque;

                // bank_transaction_info (deposit)
                $statement = $this->db->prepare("INSERT INTO `bank_transaction_info`
                    (store_id, account_id, source_id, ref_no, invoice_id, transaction_type, title, details,
                     numero_cheque, fecha_cheque, importe_cheque, image, created_by, created_at, customer_id)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                $statement->execute(array($store_id, $caja_id_ch, $source_id, $ref_no, $invoice_id, 'deposit', $title, $details_info,
                    $num_cheque, $fecha_cheque, $importe_ch, 'NULL', $user_id, $created_at, $customer_id));
                $info_id = $this->db->lastInsertId();

                // bank_transaction_price
                $statement = $this->db->prepare("INSERT INTO `bank_transaction_price` (store_id, info_id, ref_no, amount, account_id_, cotizacion_billete) VALUES (?, ?, ?, ?, ?, ?)");
                $statement->execute(array($store_id, $info_id, $ref_no, $importe_ch, $caja_id_ch, 0));

                // actualizar acumulados
                $this->db->prepare("UPDATE `bank_account_to_store` SET `deposit` = `deposit` + {$importe_ch} WHERE `store_id` = ? AND `account_id` = ?")
                         ->execute(array($store_id, $caja_id_ch));
                $this->db->prepare("UPDATE `bank_accounts` SET `total_deposit` = `total_deposit` + {$importe_ch} WHERE `id` = ?")
                         ->execute(array($caja_id_ch));

                // tabla cheques
                $statement = db()->prepare("INSERT INTO `cheques`
                    (numero, fecha, fecha_pago, importe, cuit, ref_id, invoice_no, caja_id, status, invoice_id, tipo_cheque, banco, observaciones)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                $statement->execute(array(
                    $num_cheque,
                    $fecha_cheque,
                    $fecha_pago,
                    $importe_ch,
                    $cuit_t,
                    $info_id,
                    $ref_no,
                    $caja_id_ch,
                    $status_che,
                    $invoice_id,
                    $tipo_ch,
                    $banco,
                    $observ
                ));
            }
        }

        // === Caja Pesos (puede ser negativo -> retiro) ===
        if ($importe_efectivo != 0) {
            if ($importe_efectivo > 0) {
                // dep�sito
                $statement = $this->db->prepare("INSERT INTO `bank_transaction_info`
                    (store_id, account_id, source_id, ref_no, invoice_id, transaction_type, title, details, image, created_by, created_at, customer_id)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                $statement->execute(array($store_id, 1, $source_id, $ref_no, $invoice_id, 'deposit', $title, $details_info_base, 'NULL', $user_id, $created_at, $customer_id));
                $info_id = $this->db->lastInsertId();

                $statement = $this->db->prepare("INSERT INTO `bank_transaction_price` (store_id, info_id, ref_no, amount, account_id_, cotizacion_billete) VALUES (?, ?, ?, ?, ?, ?)");
                $statement->execute(array($store_id, $info_id, $ref_no, $importe_efectivo, 1, 0));

                $this->db->prepare("UPDATE `bank_account_to_store` SET `deposit` = `deposit` + {$importe_efectivo} WHERE `store_id` = ? AND `account_id` = ?")
                         ->execute(array($store_id, 1));
                $this->db->prepare("UPDATE `bank_accounts` SET `total_deposit` = `total_deposit` + {$importe_efectivo} WHERE `id` = ?")
                         ->execute(array(1));
            } else {
                // retiro (vuelto manual)
                $monto = abs($importe_efectivo);
                $details_vuelto = $details_info_base . ' | Vuelto en pesos';

                $statement = $this->db->prepare("INSERT INTO `bank_transaction_info`
                    (store_id, account_id, source_id, ref_no, invoice_id, transaction_type, is_substract, title, details, image, created_by, created_at, customer_id)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
                $statement->execute(array($store_id, 1, $source_id, $ref_no, $invoice_id, 'withdraw', 1, $title, $details_vuelto, 'NULL', $user_id, $created_at, $customer_id));
                $info_id = $this->db->lastInsertId();

                $statement = $this->db->prepare("INSERT INTO `bank_transaction_price` (store_id, info_id, ref_no, amount, account_id_, cotizacion_billete) VALUES (?, ?, ?, ?, ?, ?)");
                $statement->execute(array($store_id, $info_id, $ref_no, $monto, 1, 0));

                $this->db->prepare("UPDATE `bank_account_to_store` SET `withdraw` = `withdraw` + {$monto} WHERE `store_id` = ? AND `account_id` = ?")
                         ->execute(array($store_id, 1));
                $this->db->prepare("UPDATE `bank_accounts` SET `total_withdraw` = `total_withdraw` + {$monto} WHERE `id` = ?")
                         ->execute(array(1));
            }
        }

        // === Dep�sito / Transferencia (account_id 11) ===
        if ($importe_deposito > 0 ){
            $details_dep = $details_info_base . ($numero_deposito ? (" | N� transacci�n: ".$numero_deposito) : '');
            $statement = $this->db->prepare("INSERT INTO `bank_transaction_info`
                (store_id, account_id, source_id, ref_no, invoice_id, transaction_type, title, details, image, created_by, created_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            $statement->execute(array($store_id, 11, $source_id, $ref_no, $invoice_id, 'deposit', $title, $details_dep, 'NULL', $user_id, $created_at));
            $info_id = $this->db->lastInsertId();

            $statement = $this->db->prepare("INSERT INTO `bank_transaction_price` (store_id, info_id, ref_no, amount, account_id_, cotizacion_billete) VALUES (?, ?, ?, ?, ?, ?)");
            $statement->execute(array($store_id, $info_id, $ref_no, $importe_deposito, 11, 0));

            $this->db->prepare("UPDATE `bank_account_to_store` SET `deposit` = `deposit` + {$importe_deposito} WHERE `store_id` = ? AND `account_id` = ?")
                     ->execute(array($store_id, 11));
            $this->db->prepare("UPDATE `bank_accounts` SET `total_deposit` = `total_deposit` + {$importe_deposito} WHERE `id` = ?")
                     ->execute(array(11));
        }

        // === D�lar billete (account_id 5) ===
        if ($importe_dolar > 0 && $cotizacion_billete > 0 ){
            $statement = $this->db->prepare("INSERT INTO `bank_transaction_info`
                (store_id, account_id, source_id, ref_no, invoice_id, transaction_type, title, details, cotizacion_dolar, image, created_by, created_at, customer_id)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            $statement->execute(array($store_id, 5, $source_id, $ref_no, $invoice_id, 'deposit', $title, $details_info_base, $cotizacion_billete, 'NULL', $user_id, $created_at, $customer_id));
            $info_id = $this->db->lastInsertId();

            $statement = $this->db->prepare("INSERT INTO `bank_transaction_price` (store_id, info_id, ref_no, amount, account_id_, cotizacion_billete) VALUES (?, ?, ?, ?, ?, ?)");
            $statement->execute(array($store_id, $info_id, $ref_no, $importe_dolar, 5, $cotizacion_billete));

            $this->db->prepare("UPDATE `bank_account_to_store` SET `deposit` = `deposit` + {$importe_dolar} WHERE `store_id` = ? AND `account_id` = ?")
                     ->execute(array($store_id, 5));
            $this->db->prepare("UPDATE `bank_accounts` SET `total_deposit` = `total_deposit` + {$importe_dolar} WHERE `id` = ?")
                     ->execute(array(5));
        }

        // === Retenci�n (account_id 15) ===
        if ($importe_retencion > 0 ){
            $details_ret = $details_info_base . ($numero_retencion ? (" | N� retenci�n: ".$numero_retencion) : '');
            $statement = $this->db->prepare("INSERT INTO `bank_transaction_info`
                (store_id, account_id, source_id, ref_no, invoice_id, transaction_type, title, details, image, created_by, created_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            $statement->execute(array($store_id, 15, $source_id, $ref_no, $invoice_id, 'deposit', $title, $details_ret, 'NULL', $user_id, $created_at));
            $info_id = $this->db->lastInsertId();

            // OJO: tu tabla bank_transaction_price NO tiene 'numero_comprobante'
            $statement = $this->db->prepare("INSERT INTO `bank_transaction_price` (store_id, info_id, ref_no, amount, account_id_, cotizacion_billete) VALUES (?, ?, ?, ?, ?, ?)");
            $statement->execute(array($store_id, $info_id, $ref_no, $importe_retencion, 15, 0));

            $this->db->prepare("UPDATE `bank_account_to_store` SET `deposit` = `deposit` + {$importe_retencion} WHERE `store_id` = ? AND `account_id` = ?")
                     ->execute(array($store_id, 15));
            $this->db->prepare("UPDATE `bank_accounts` SET `total_deposit` = `total_deposit` + {$importe_retencion} WHERE `id` = ?")
                     ->execute(array(15));
        }
    }

    // ===== Log de pago �due_paid� (para historial) =====
    if ($paid_amount > 0) {
        $reference_no = generate_sell_log_ref_no('due_paid');
        $statement = db()->prepare("INSERT INTO `sell_logs`
            (customer_id, reference_no, type, pmethod_id, description, amount, store_id, ref_invoice_id, created_by, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        $statement->execute(array($customer_id, $reference_no, 'due_paid', $pmethod_id, $note, $paid_amount, $store_id, $invoice_id, $user_id, $created_at));
    }

    // balance (solo si tipo_recibo=1; aqu� siempre 2)
    if ($balance > 0 && $tipo_recibo == 1) {
        $statement = db()->prepare("UPDATE `selling_price` SET `balance` = ? WHERE `store_id` = ? AND `invoice_id` = ?");
        $statement->execute(array($balance, $store_id, $invoice_id));
    }

    return $invoice_id;
}



    public function getDueInvoices($customer_id, $store_id = null)
    {
        $store_id = $store_id ? $store_id : store_id();
        $statement = $this->db->prepare("SELECT `selling_info`.*, `selling_price`.*, `customers`.`customer_id`, `customers`.`customer_name`, `customers`.`customer_mobile`, `customers`.`customer_email` FROM `selling_info` LEFT JOIN `selling_price` ON `selling_info`.`invoice_id` = `selling_price`.`invoice_id` LEFT JOIN `customers` ON `selling_info`.`customer_id` = `customers`.`customer_id` WHERE `selling_info`.`store_id` = ? AND `selling_info`.`customer_id` = ? AND `selling_price`.`due` > 0 AND `selling_info`.`inv_type` = ?");
        $statement->execute(array($store_id, $customer_id, 'sell'));
        return $statement->fetchAll(PDO::FETCH_ASSOC);
    }
    
    public function getInvoices($type, $store_id = null, $limit = 100000)
    {
        $store_id = $store_id ? $store_id : store_id();
        $statement = $this->db->prepare("SELECT `selling_info`.*, `selling_price`.*, `customers`.`customer_id`, `customers`.`customer_name`, `customers`.`customer_mobile`, `customers`.`customer_email` FROM `selling_info` LEFT JOIN `selling_price` ON `selling_info`.`invoice_id` = `selling_price`.`invoice_id` LEFT JOIN `customers` ON `selling_info`.`customer_id` = `customers`.`customer_id` WHERE `selling_info`.`store_id` = ? AND `selling_info`.`inv_type` = ? ORDER BY `selling_info`.`created_at` DESC LIMIT {$limit}");
        $statement->execute(array($store_id, $type));
        return $statement->fetchAll(PDO::FETCH_ASSOC);
    }
    
    public function getInvoiceInfo($invoice_id, $store_id = null)
    {
        $store_id = $store_id ? $store_id : store_id();
        $statement = $this->db->prepare("SELECT `selling_info`.*, `selling_price`.*, `customers`.`customer_id`, `customers`.`customer_name`, `customers`.`customer_mobile` AS `mobile_number`, `customers`.`customer_email` FROM `selling_info` LEFT JOIN `selling_price` ON `selling_info`.`invoice_id` = `selling_price`.`invoice_id` LEFT JOIN `customers` ON `selling_info`.`customer_id` = `customers`.`customer_id` WHERE `selling_info`.`store_id` = ? AND (`selling_info`.`invoice_id` = ? OR (`selling_info`.`customer_id` = ?) AND `selling_info`.`inv_type` = 'sell') ORDER BY `selling_info`.`invoice_id` DESC");
        $statement->execute(array($store_id, $invoice_id, $invoice_id));
        $invoice = $statement->fetch(PDO::FETCH_ASSOC);
        if ($invoice) {
            $invoice['by'] = get_the_user($invoice['created_by'], 'username');
        }
        return $invoice;
    }
    
    public function getInvoiceItems($invoice_id, $store_id = null)
    {
        $store_id = $store_id ? $store_id : store_id();
        $statement = $this->db->prepare("SELECT * FROM `selling_item` WHERE `store_id` = ? AND `invoice_id` = ?");
        $statement->execute(array($store_id, $invoice_id));
        $rows = $statement->fetchAll(PDO::FETCH_ASSOC);
        $array = array();
        $i = 0;
        foreach ($rows as $row) {
            $array[$i] = $row;
            $array[$i]['unitName'] = get_the_unit(get_the_product($row['item_id'])['unit_id'], 'unit_name');
            $i++;
        }
        return $array;
    }
    
    public function getInvoiceItemsHTML($invoice_id, $store_id = null)
    {
        $store_id = $store_id ? $store_id : store_id();
        $statement = $this->db->prepare("SELECT * FROM `selling_item` WHERE `store_id` = ? AND `invoice_id` = ?");
        $statement->execute(array($store_id, $invoice_id));
        $rows = $statement->fetchAll(PDO::FETCH_ASSOC);
        $i = 0;
        $html = '<table class="table table-bordered mb-0">';
        $html .= '<thead>';
        $html .= '<tr class="bg-gray">';
        $html .= '<td class="text-center" style="padding:0 2px;">Detalle</td>';
        $html .= '<td class="text-right" style="padding:0 2px;">Precio</td>';
        $html .= '<td class="text-center" style="padding:0 2px;">Cant.</td>';
        $html .= '<td class="text-right" style="padding:0 2px;">Subtotal</td>';
        $html .= '</tr>';
        $html .= '</thead>';
        $sell = 0;
        $qty = 0;
        $total = 0;
        foreach ($rows as $row) {
            $html .= '<tr class="bg-success">';
            $html .= '<td class="text-center" style="padding:0 2px;">' . $row['item_name'] . '</td>';
            $html .= '<td class="text-right" style="padding:0 2px;">' . currency_format($row['item_price']) . '</td>';
            $html .= '<td class="text-center" style="padding:0 2px;">' . currency_format($row['item_quantity']) . ' ' . get_the_unit(get_the_product($row['item_id'])['unit_id'], 'unit_name') . '</td>';
            $html .= '<td class="text-right" style="padding:0 2px;">' . currency_format($row['item_total']) . '</td>';
            $html .= '</tr>';
            $sell += $row['item_price'];
            $qty += $row['item_quantity'];
            $total += $row['item_total'];
        }
        $html .= '<tr class="bg-warning">';
        $html .= '<td class="text-right" style="padding:0 2px;">Total</td>';
        $html .= '<td class="text-right" style="padding:0 2px;">' . currency_format($sell) . '</td>';
        $html .= '<td class="text-center" style="padding:0 2px;">' . currency_format($qty) . '</td>';
        $html .= '<td class="text-right" style="padding:0 2px;">' . currency_format($total) . '</td>';
        $html .= '</tr>';
        $html .= '</table>';
        return $html;
    }

    public function getInvoiceItemInfo($invoice_id, $item_id, $store_id = null)
    {
        $store_id = $store_id ? $store_id : store_id();
        $statement = $this->db->prepare("SELECT * FROM `selling_item` WHERE `store_id` = ? AND  `invoice_id` = ? AND `item_id` = ?");
        $statement->execute(array($store_id, $invoice_id, (int) $item_id));
        return $statement->fetch(PDO::FETCH_ASSOC);
    }
    
    public function getInvoiceItemTaxes($invoice_id, $store_id = null)
    {
        $store_id = $store_id ? $store_id : store_id();
        $statement = $this->db->prepare("SELECT SUM(`item_quantity`) as qty, SUM(`tax`) as tax, item_tax, `taxrates`.`taxrate_name`, `taxrates`.`code_name` FROM `selling_item` LEFT JOIN `taxrates` ON (`selling_item`.`taxrate_id` = `taxrates`.`taxrate_id`) WHERE `store_id` = ? AND invoice_id = ? GROUP BY `selling_item`.`taxrate_id`");
        $statement->execute(array($store_id, $invoice_id));
        return $statement->fetchAll(PDO::FETCH_ASSOC);
    }
    
    public function getSellingPrice($invoice_id, $store_id = null)
    {
        $store_id = $store_id ? $store_id : store_id();
        $statement = $this->db->prepare("SELECT * FROM `selling_price` WHERE `store_id` = ? AND invoice_id = ?");
        $statement->execute(array($store_id, $invoice_id));
        return $statement->fetch(PDO::FETCH_ASSOC);
    }
    
    public function hasInvoice($invoice_id, $store_id = null)
    {
        $store_id = $store_id ? $store_id : store_id();
        $statement = $this->db->prepare("SELECT * FROM `selling_info` WHERE `selling_info`.`store_id` = ? AND `selling_info`.`invoice_id` = ?");
        $statement->execute(array($store_id, $invoice_id));
        $invoice = $statement->fetch(PDO::FETCH_ASSOC);
        return isset($invoice['invoice_id']);
    }
    
    public function isLastInvoice($customer_id, $invoice_id, $store_id = null)
    {
        $store_id = $store_id ? $store_id : store_id();
        $statemtnt = $this->db->prepare("SELECT * FROM `selling_info` WHERE `store_id` = ? AND `customer_id` = ? AND `inv_type` = ? AND info_id=(SELECT max(`info_id`) FROM `selling_info`)");
        $statemtnt->execute(array($store_id, $customer_id, 'sell'));
        $row = $statemtnt->fetch(PDO::FETCH_ASSOC);
        return $row['invoice_id'] == $invoice_id;
    }
    
    public function getLastInvoice($type = 'sell', $store_id = null)
    {
        $store_id = $store_id ? $store_id : store_id();
        $statemtnt = $this->db->prepare("SELECT * FROM `selling_info` WHERE `store_id` = ? AND `inv_type` = ? ORDER BY `info_id` DESC LIMIT 0,1");
        $statemtnt->execute(array($store_id, $type));
        return $statemtnt->fetch(PDO::FETCH_ASSOC);
    }
    
    public function getNextInvoice($customer_id, $invoice_id, $type = 'sell', $store_id = null)
    {
        $store_id = $store_id ? $store_id : store_id();
        $statemtnt = $this->db->prepare("SELECT * FROM `selling_info` WHERE `store_id` = ? AND `customer_id` = ? AND `inv_type` = ? AND info_id=(SELECT max(`info_id`) FROM `selling_info`)");
        $statemtnt->execute(array($store_id, $customer_id, $type));
        $rows = $statemtnt->fetchAll(PDO::FETCH_ASSOC);
        $invoice = null;
        foreach ($rows as $r) {
            if ($r['invoice_id'] == $invoice_id) {
                break;
            }
            $invoice = $r;
        }
        return $invoice;
    }
    
    public function totalToday($store_id = null)
    {
        $from = date('Y-m-d');
        $to = date('Y-m-d');
        $store_id = $store_id ? $store_id : store_id();
        $where_query = "`store_id` = ? AND `inv_type` = 'sell'";
        $where_query .= date_range_filter($from, $to);
        $statement = $this->db->prepare("SELECT * FROM `selling_info` WHERE {$where_query}");
        $statement->execute(array($store_id));
        return $statement->rowCount();
    }
    
    public function total($from = null, $to = null, $store_id = null)
    {
        $store_id = $store_id ? $store_id : store_id();
        $where_query = "`store_id` = ? AND `inv_type` = 'sell'";
        if ($from) {
            $where_query .= date_range_filter($from, $to);
        }
        $statement = $this->db->prepare("SELECT * FROM `selling_info` WHERE {$where_query}");
        $statement->execute(array($store_id));
        return $statement->rowCount();
    }

    public function totalHoldingOrderToday($store_id = null)
    {
        $from = date('Y-m-d');
        $to = date('Y-m-d');
        $store_id = $store_id ? $store_id : store_id();
        $where_query = "`store_id` = ?";
        $where_query .= date_range_holding_order_filter($from, $to);
        $statement = $this->db->prepare("SELECT * FROM `holding_info` WHERE {$where_query}");
        $statement->execute(array($store_id));
        return $statement->rowCount();
    }
    
    public function totalHoldingOrder($from = null, $to = null, $store_id = null)
    {
        $store_id = $store_id ? $store_id : store_id();
        $where_query = "`store_id` = ?";
        if ($from) {
            $where_query .= date_range_holding_order_filter($from, $to);
        }
        $statement = $this->db->prepare("SELECT * FROM `holding_info` WHERE {$where_query}");
        $statement->execute(array($store_id));
        return $statement->rowCount();
    }
}