<?php
ob_start();
require("dbconnect.php");
require("config.php");
require("function.php");

header('Content-Type: application/json');

if (!isset($_SESSION["username_".$dbname])) {
    echo json_encode(['success'=>false,'message'=>'Session expired. Please re-login.']);
    exit;
}

$zdb = $_SESSION["zdbyear_".$dbname];
mysqli_select_db($conn, $zdb);

// ── Auto-migrations (keep in sync with sale.php) ──────────────────────────
foreach ([
    'supplier'       => "int(11) NOT NULL DEFAULT 0",
    'paidbyus'       => "double NOT NULL DEFAULT 0",
    'paidbycustomer' => "double NOT NULL DEFAULT 0",
    'product'        => "int(11) NOT NULL DEFAULT 0",
    'cost'           => "double NOT NULL DEFAULT 0",
    'brand'          => "int(11) NOT NULL DEFAULT 0",
] as $col => $def) {
    $chk = mysqli_query($conn, "SHOW COLUMNS FROM `saletr` LIKE '$col'");
    if ($chk && mysqli_num_rows($chk) == 0)
        mysqli_query($conn, "ALTER TABLE `saletr` ADD COLUMN `$col` $def");
}
foreach ([
    'totpaidbyus'       => "double NOT NULL DEFAULT 0",
    'totpaidbycustomer' => "double NOT NULL DEFAULT 0",
    'containerno'       => "varchar(50) NOT NULL DEFAULT ''",
    'godown'            => "int(11) NOT NULL DEFAULT 0",
    'salesman'          => "int(11) NOT NULL DEFAULT 0",
    'packingcharges'    => "double NOT NULL DEFAULT 0",
    'transport_charges' => "double NOT NULL DEFAULT 0",
    'other_charges'     => "double NOT NULL DEFAULT 0",
    // Source Sale Order vouchno(s) (comma-separated) — lets actran be keyed
    // by the original saleorder vouchno instead of this invoice's own, so it
    // lands on the same row dispatch already posted (see sale.php).
    'soref'             => "varchar(100) NOT NULL DEFAULT ''",
] as $col => $def) {
    $chk = mysqli_query($conn, "SHOW COLUMNS FROM `sale` LIKE '$col'");
    if ($chk && mysqli_num_rows($chk) == 0)
        mysqli_query($conn, "ALTER TABLE `sale` ADD COLUMN `$col` $def");
}
foreach ([
    'dispatchdate'      => "DATE NULL",
    'dispatchby'        => "VARCHAR(50) NOT NULL DEFAULT ''",
    'lrno'              => "VARCHAR(50) NOT NULL DEFAULT ''",
    'transporter'       => "VARCHAR(100) NOT NULL DEFAULT ''",
    'lrdate'            => "DATE NULL",
    'transportmode'     => "VARCHAR(30) NOT NULL DEFAULT ''",
    'invoiceno'         => "INT(11) NOT NULL DEFAULT 0",
    'invoicedate'       => "DATE NULL",
    'godown'            => "INT(11) NOT NULL DEFAULT 0",
    'salesman'          => "INT(11) NOT NULL DEFAULT 0",
    'transport_charges' => "DOUBLE NOT NULL DEFAULT 0",
    'other_charges'     => "DOUBLE NOT NULL DEFAULT 0",
    'packingcharges'    => "DOUBLE NOT NULL DEFAULT 0",
] as $col => $def) {
    $chk = mysqli_query($conn, "SHOW COLUMNS FROM `saleorder` LIKE '$col'");
    if ($chk && mysqli_num_rows($chk) == 0)
        mysqli_query($conn, "ALTER TABLE `saleorder` ADD COLUMN `$col` $def");
}
// ── Critical: ensure cost column exists in saleorderdtl ───────────────────
foreach ([
    'cost' => "DOUBLE NOT NULL DEFAULT 0",
] as $col => $def) {
    $chk = mysqli_query($conn, "SHOW COLUMNS FROM `saleorderdtl` LIKE '$col'");
    if ($chk && mysqli_num_rows($chk) == 0)
        mysqli_query($conn, "ALTER TABLE `saleorderdtl` ADD COLUMN `$col` $def");
}

// ── Ensure notifications table exists (bell menu — see includes/notifications.php) ──
mysqli_query($conn, "CREATE TABLE IF NOT EXISTS `notifications` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `vouchno` INT(11) NOT NULL DEFAULT 0,
    `reftype` VARCHAR(20) NOT NULL DEFAULT '',
    `audience` VARCHAR(30) NOT NULL DEFAULT '',
    `action` VARCHAR(20) NOT NULL DEFAULT '',
    `message` VARCHAR(255) NOT NULL DEFAULT '',
    `readflag` VARCHAR(1) NOT NULL DEFAULT '',
    `createduser` VARCHAR(50) NOT NULL DEFAULT '',
    `createddate` DATE NULL,
    `createdtime` TIME NULL,
    PRIMARY KEY (`id`),
    KEY `audience` (`audience`),
    KEY `vouchno` (`vouchno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

$vouchnos = isset($_POST['vouchnos']) ? (array)$_POST['vouchnos'] : [];
$vouchnos = array_values(array_unique(array_filter(array_map('intval', $vouchnos))));

if (!$vouchnos) {
    echo json_encode(['success'=>false,'message'=>'No sale orders selected.']);
    exit;
}

$inList = implode(',', $vouchnos);

$ordRes = mysqli_query($conn, "SELECT * FROM `saleorder` WHERE vouchno IN ($inList)") or die(mysqli_error($conn));
$ordersByVouch = [];
while ($r = mysqli_fetch_assoc($ordRes)) $ordersByVouch[(int)$r['vouchno']] = $r;

if (count($ordersByVouch) != count($vouchnos)) {
    echo json_encode(['success'=>false,'message'=>'One or more sale orders could not be found.']);
    exit;
}

$customer = 0;
$godown   = 0;
foreach ($ordersByVouch as $o) {
    if ($godown == 0) $godown = (int)($o['godown'] ?? 0);
    if (empty($o['dispatchdate']) || $o['dispatchdate'] == '0000-00-00') {
        echo json_encode(['success'=>false,'message'=>'SO#'.$o['vouchno'].' has not been dispatched yet.']);
        exit;
    }
    if ((int)($o['invoiceno'] ?? 0) > 0) {
        echo json_encode(['success'=>false,'message'=>'SO#'.$o['vouchno'].' is already invoiced (SAL#'.$o['invoiceno'].').']);
        exit;
    }
    if ($customer == 0) $customer = (int)$o['customer'];
    elseif ($customer != (int)$o['customer']) {
        echo json_encode(['success'=>false,'message'=>'All selected sale orders must belong to the same customer.']);
        exit;
    }
}

// ── Gather detail lines across all selected sale orders ───────────────────
$dtlRes = mysqli_query($conn, "SELECT d.*, p.name AS prodname
                                FROM `saleorderdtl` d
                                LEFT JOIN `product` p ON p.id = d.product
                                WHERE d.vouchno IN ($inList)
                                ORDER BY d.vouchno, d.srl") or die(mysqli_error($conn));
$lines = [];
while ($r = mysqli_fetch_assoc($dtlRes)) $lines[] = $r;

if (!$lines) {
    echo json_encode(['success'=>false,'message'=>'Selected sale order(s) have no line items.']);
    exit;
}

$tottqty = 0; $totamt = 0; $subtotal = 0;
$totPacking = 0; $totTransport = 0; $totOther = 0;
$salesman = 0;
foreach ($lines as $ln) {
    $tottqty += (float)$ln['qty'];
    $totamt  += (float)$ln['amount'];
    $subtotal += (float)$ln['amount'];
}
// Sum charges from all source saleorders
foreach ($ordersByVouch as $o) {
    $totPacking   += (float)($o['packingcharges'] ?? 0);
    $totTransport += (float)($o['transport_charges'] ?? 0);
    $totOther     += (float)($o['other_charges'] ?? 0);
    if ($salesman == 0) $salesman = (int)($o['salesman'] ?? 0);
}
$totamt = $subtotal + $totPacking + $totTransport + $totOther;

$audituser = audituser();
$vd = date('Y-m-d');
$remarks = mysqli_real_escape_string($conn, 'From Sale Order(s): SO#' . implode(', SO#', $vouchnos));

// ── New sale (invoice) vouchno ──────────────────────────────────────────────
$gen1   = mysqli_query($conn, "SELECT MAX(`vouchno`) AS maxcode FROM `sale` LIMIT 1") or die(mysqli_error($conn));
$vvouch = mysqli_fetch_assoc($gen1);
$qvouch = ($vvouch["maxcode"] > 0) ? $vvouch["maxcode"] + 1 : 1;

$soref = mysqli_real_escape_string($conn, implode(',', $vouchnos));

mysqli_query($conn, "INSERT INTO `sale`
    (`vouchno`,`vouchdt`,`customer`,`godown`,`salesman`,`remarks`,
     `tottqty`,`totamt`,`packingcharges`,`transport_charges`,`other_charges`,`soref`,
     `adduser`,`adddate`,`addtime`)
    VALUES
    ('$qvouch','$vd','$customer','$godown','$salesman','$remarks',
     '$tottqty','$totamt','$totPacking','$totTransport','$totOther','$soref',
     '$audituser',CURDATE(),CURTIME())")
or die(json_encode(['success'=>false,'message'=>mysqli_error($conn)]));

foreach ($lines as $ln) {
    $desc  = mysqli_real_escape_string($conn, $ln['prodname'] ?: ('Product #'.$ln['product']));
    $prod  = (int)$ln['product'];
    $brand = (int)($ln['brand'] ?? 0);
    $qty   = (float)$ln['qty'];
    $cost  = (float)$ln['cost'];
    $amt   = (float)$ln['amount'];
    mysqli_query($conn, "INSERT INTO `saletr`
        (`vouchno`,`description`,`product`,`brand`,`tqty`,`cost`,`amt`)
        VALUES ('$qvouch','$desc','$prod','$brand','$qty','$cost','$amt')")
    or die(json_encode(['success'=>false,'message'=>mysqli_error($conn)]));
}

// ── Actran (source='DSP', same key space dispatch uses). Each contributing
// sale order already has a DSP-sourced actran entry from its own dispatch —
// delete all of those (they're being superseded/consolidated by this invoice)
// and post ONE combined entry for the invoice total, keyed by the first
// contributing sale order's vouchno so sale.php's own edit/re-save logic
// keeps landing on this same row instead of creating a new one.
$actranVouchno = $vouchnos[0];
mysqli_query($conn, "DELETE FROM `actran` WHERE `source`='DSP' AND `vouchno` IN ($inList)");
if ($totamt > 0 && $customer > 0) {
    $dr = $totamt * -1;
    mysqli_query($conn, "INSERT INTO `actran`(`vouchdt`,`date`,`vouchno`,`source`,`trnno`,`accode`,`grpcode`,`feamt`,`roe`,`amount`,`prodtype`,`actamt`,`narr`)
        VALUES ('$vd','$vd','$actranVouchno','DSP','001','$customer','010','$dr','1','$dr','','$dr','Sale Amount')")
    or die(json_encode(['success'=>false,'message'=>mysqli_error($conn)]));
    mysqli_query($conn, "INSERT INTO `actran`(`vouchdt`,`date`,`vouchno`,`source`,`trnno`,`accode`,`grpcode`,`feamt`,`roe`,`amount`,`prodtype`,`actamt`,`narr`)
        VALUES ('$vd','$vd','$actranVouchno','DSP','002','15','005','$totamt','1','$totamt','','$totamt','Sale Amount')")
    or die(json_encode(['success'=>false,'message'=>mysqli_error($conn)]));
}

// ── Mark source sale orders as invoiced ─────────────────────────────────────
mysqli_query($conn, "UPDATE `saleorder` SET `invoiceno`='$qvouch', `invoicedate`=CURDATE() WHERE vouchno IN ($inList)")
or die(json_encode(['success'=>false,'message'=>mysqli_error($conn)]));

// ── Notify: Sale Invoice generated from dispatched Sale Order(s) ───────────
$custNameRow2 = mysqli_fetch_assoc(mysqli_query($conn, "SELECT `name` FROM `accmast` WHERE `accode`='$customer'"));
$custName2    = $custNameRow2['name'] ?? '';
$soList       = 'SO#' . implode(', SO#', $vouchnos);
$msgInvoice   = mysqli_real_escape_string($conn,
    "Sale Invoice #$qvouch" . ($custName2 ? " ($custName2)" : '') . " was generated from $soList by $audituser.");

mysqli_query($conn, "INSERT INTO `notifications`
    (`vouchno`,`reftype`,`audience`,`action`,`message`,`createduser`,`createddate`,`createdtime`)
    VALUES ('$qvouch','sale','admin','info','$msgInvoice','$audituser',CURDATE(),CURTIME())");

if ($salesman > 0) {
    mysqli_query($conn, "INSERT INTO `notifications`
        (`vouchno`,`reftype`,`audience`,`action`,`message`,`createduser`,`createddate`,`createdtime`)
        VALUES ('$qvouch','sale','salesman_$salesman','info','$msgInvoice','$audituser',CURDATE(),CURTIME())");
}

echo json_encode(['success'=>true, 'invoiceno'=>$qvouch]);
