<?php

require_once __DIR__ . '/sales_payload_helper.php';

/**
 * Run a parameterized query without pg_prepare (avoids "already exists" on reused connections).
 *
 * @param resource $con
 * @param string $sql
 * @param array $params
 * @return resource|false
 */
function mch_pg_query_params($con, $sql, array $params)
{
    return pg_query_params($con, $sql, $params);
}

function mch_pg_query_failed($result)
{
    return $result === false;
}

function mch_get_branch_mapping($con, $imeino)
{
    $query = "SELECT branchcode, branch_devicemapcode FROM tblbranchdevicemapping WHERE deviceid = $1 AND statuscode = '1' LIMIT 1";
    $result = mch_pg_query_params($con, $query, array($imeino));
    if ($result && pg_num_rows($result) > 0) {
        return pg_fetch_assoc($result);
    }
    return null;
}

function mch_uses_formatted_voucher_no($branchcode)
{
    return (string)$branchcode === '187';
}

function mch_get_next_sales_numbers($con, $imeino, $billdate)
{
    $lockSql = "SELECT pg_advisory_xact_lock(hashtext($1))";
    mch_pg_query_params($con, $lockSql, array('sales_' . $imeino));

    $mapping = mch_get_branch_mapping($con, $imeino);
    $branchcode = $mapping ? $mapping['branchcode'] : '';
    $useFormattedVoucher = mch_uses_formatted_voucher_no($branchcode);

    $txnSql = "SELECT COALESCE(MAX(CAST(transactionno AS BIGINT)), 0) + 1 AS transactionno FROM tblsales WHERE deviceid = $1";
    $txnRes = mch_pg_query_params($con, $txnSql, array($imeino));
    $transactionno = 1;
    if ($txnRes && pg_num_rows($txnRes) > 0) {
        $transactionno = (int)pg_fetch_assoc($txnRes)['transactionno'];
    }

    if ($useFormattedVoucher) {
        $sequenceSql = "SELECT COALESCE(MAX(CAST(sequenceno AS INTEGER)), 0) + 1 AS sequenceno
                          FROM tblsales
                          WHERE deviceid = $1 AND transactiondate = $2";
    } else {
        $sequenceSql = "SELECT COALESCE(MAX(CAST(voucherno AS INTEGER)), 0) + 1 AS sequenceno
                          FROM tblsales
                          WHERE deviceid = $1 AND transactiondate = $2";
    }
    $sequenceRes = mch_pg_query_params($con, $sequenceSql, array($imeino, $billdate));
    $sequenceno = 1;
    if ($sequenceRes && pg_num_rows($sequenceRes) > 0) {
        $sequenceno = (int)pg_fetch_assoc($sequenceRes)['sequenceno'];
    }
    if ($sequenceno <= 0) {
        $sequenceno = 1;
    }

    $voucherno = mch_format_voucher_no($billdate, $sequenceno, $branchcode);

    return array(
        'transactionno' => $transactionno,
        'sequenceno' => $sequenceno,
        'voucherno' => $voucherno,
    );
}

function mch_format_voucher_no($billdateMs, $sequenceno, $branchcode = null)
{
    $sequenceno = max((int)$sequenceno, 1);
    if ($branchcode !== null && mch_uses_formatted_voucher_no($branchcode)) {
        $timestamp = (int)($billdateMs / 1000);
        if ($timestamp <= 0) {
            $timestamp = time();
        }
        $datePart = date('dmy', $timestamp);
        return $datePart . '_' . str_pad((string)$sequenceno, 3, '0', STR_PAD_LEFT);
    }
    return (string)$sequenceno;
}

function mch_array_get($arr, $key, $default = null)
{
    return array_key_exists($key, $arr) ? $arr[$key] : $default;
}

function mch_insert_sale_record($con, array $sale, $transactionno, $voucherno, $sequenceno, $makerId, $syncDate)
{
    $modeofpayment = mch_array_get($sale, 'modeofpayment', 1);
    $valsessionid = mch_array_get($sale, 'sessionid', 0);
    if ($valsessionid === 'null' || $valsessionid === '' || $valsessionid === null) {
        $valsessionid = 0;
    }

    $netamt = mch_array_get($sale, 'netamount', mch_array_get($sale, 'grandtotal', 0));
    $discper = mch_array_get($sale, 'discper', 0);
    $discamount = mch_array_get($sale, 'discamount', 0);
    $basicamount = mch_array_get($sale, 'subtotal', 0);
    $cgstamount = mch_array_get($sale, 'sgstamount', 0);
    $sgstamount = mch_array_get($sale, 'cgstamount', 0);
    $onlineorderamount = mch_array_get($sale, 'onlineorderamount', 0);
    $creditcustomercode = mch_array_get($sale, 'creditcustomercode', 0);
    $creditcustomeramount = mch_array_get($sale, 'creditcustomeramount', 0);
    $taxamount = mch_array_get($sale, 'taxamount', 0);
    $taxname = mch_array_get($sale, 'taxname', '');
    $exchangerate = mch_array_get($sale, 'exchangerate', 0);
    $takername = mch_array_get($sale, 'takername', '');

    $insertSql = "INSERT INTO tblsales (
        transactionno, voucherno, sequenceno, deviceid, transactiondate, grandtotal, noofitems, salestime,
        createddate, syncflag, statuscode, syncdate, branchcode, makerid, cancelflag, modeofpayment,
        customermobileno, customername, cashamount, creditamount, onlineamount, onlineorders,
        sessionid, netamount, discper, discamount, cgstamount, sgstamount, basicamount, takername,
        onlineorderamount, creditcustomercode, creditcustomeramount, taxamount, taxname, exchangerate
    ) VALUES (
        $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36
    )";

    $params = array(
        (string)$transactionno,
        (string)$voucherno,
        (int)$sequenceno,
        mch_array_get($sale, 'devicecode', mch_array_get($sale, 'deviceid', '')),
        mch_array_get($sale, 'transactiondate', 0),
        mch_array_get($sale, 'grandtotal', 0),
        mch_array_get($sale, 'noofitems', 0),
        mch_array_get($sale, 'salestime', ''),
        mch_array_get($sale, 'createddate', 0),
        1,
        1,
        $syncDate,
        mch_array_get($sale, 'branchcode', 0),
        $makerId,
        0,
        $modeofpayment,
        mch_array_get($sale, 'mobileno', ''),
        mch_array_get($sale, 'customername', ' '),
        mch_array_get($sale, 'cashamount', 0),
        mch_array_get($sale, 'creditamount', 0),
        mch_array_get($sale, 'onlineamount', 0),
        mch_array_get($sale, 'onlineorders', ''),
        $valsessionid,
        $netamt,
        $discper,
        $discamount,
        $cgstamount,
        $sgstamount,
        $basicamount,
        $takername,
        $onlineorderamount,
        $creditcustomercode,
        $creditcustomeramount,
        $taxamount,
        $taxname,
        $exchangerate,
    );

    return mch_pg_query_params($con, $insertSql, $params);
}

function mch_insert_sale_item_record($con, array $item, $transactionno, $voucherno, $sequenceno, $transactiondate, $syncDate)
{
    $basicrate = mch_array_get($item, 'basicrate', 0);
    $basicamount = mch_array_get($item, 'basicamount', 0);

    $insertSql = "INSERT INTO tblsalesitemdetails (
        transactiondate, transactionno, voucherno, sequenceno, deviceid, branchcode, itemcode, qty, price, amount,
        createddate, updateddate, syncflag, syncautonum, syncdate, basicrate, basicamount
    ) VALUES (
        $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17
    )";

    $params = array(
        $transactiondate,
        (string)$transactionno,
        (string)$voucherno,
        (int)$sequenceno,
        mch_array_get($item, 'devicecode', mch_array_get($item, 'deviceid', '')),
        mch_array_get($item, 'branchcode', 0),
        mch_array_get($item, 'itemcode', 0),
        mch_array_get($item, 'qty', 0),
        mch_array_get($item, 'price', 0),
        mch_array_get($item, 'amount', 0),
        mch_array_get($item, 'createddate', 0),
        mch_array_get($item, 'updateddate', 0),
        1,
        mch_array_get($item, 'autonum', 0),
        $syncDate,
        $basicrate,
        $basicamount,
    );

    return mch_pg_query_params($con, $insertSql, $params);
}

function mch_build_sync_response($transactionno, $voucherno, $salesCountMatch = 1)
{
    $response = array();
    $response['SalesTransactionDetails'] = array((string)$transactionno);
    $response['SalesItemTransactionDetails'] = array((string)$transactionno);
    $response['salescount'] = array((string)$salesCountMatch);

    $response1 = array();
    $response1['TransactionDetails'] = array($response);

    return $response1;
}

function mch_format_amount($value, $decimals = 2)
{
    return number_format((float)$value, $decimals, '.', '');
}

function mch_format_bill_date($transactionDateMs)
{
    $timestamp = (int)($transactionDateMs / 1000);
    if ($timestamp <= 0) {
        return '';
    }
    return date('d-m-Y', $timestamp);
}

function mch_get_mode_of_payment(array $sale)
{
    $cashamount = (float)mch_array_get($sale, 'cashamount', 0);
    $onlineamount = (float)mch_array_get($sale, 'onlineamount', 0);
    $creditamount = (float)mch_array_get($sale, 'creditamount', 0);
    $creditcustomeramount = (float)mch_array_get($sale, 'creditcustomeramount', 0);
    $modeofpayment = (int)mch_array_get($sale, 'modeofpayment', 0);
    $onlineorders = (string)mch_array_get($sale, 'onlineorders', '');

    if ($creditcustomeramount > 0) {
        return 'Credit';
    }
    if ($cashamount > 0 && $onlineamount > 0 && $creditamount > 0) {
        return 'Cash,Card,Online : UPI';
    }
    if ($cashamount > 0 && $onlineamount > 0) {
        return 'Cash,Online : UPI';
    }
    if ($cashamount > 0 && $creditamount > 0) {
        return 'Cash,Card';
    }
    if ($onlineamount > 0 && $creditamount > 0) {
        return 'Card,Online : UPI';
    }
    if ($cashamount > 0) {
        return 'Cash';
    }
    if ($creditamount > 0) {
        return 'Card';
    }
    if ($onlineamount > 0) {
        return 'Online : UPI';
    }
    if ($onlineorders !== '' && in_array($modeofpayment, array(4, 5, 6), true)) {
        return '';
    }

    return '';
}

function mch_fetch_branch_name($con, $branchcode)
{
    $query = "SELECT COALESCE(branchname, '') AS branchname FROM tblbranchmaster WHERE branchcode = $1 LIMIT 1";
    $result = mch_pg_query_params($con, $query, array($branchcode));
    if ($result && pg_num_rows($result) > 0) {
        return pg_fetch_assoc($result)['branchname'];
    }
    return '';
}

function mch_fetch_company_details($con, $branchcode)
{
    $query = "SELECT COALESCE(companyname, '') AS companyname,
                     COALESCE(companyaddress, '') AS companyaddress,
                     COALESCE(area, '') AS area,
                     COALESCE(city, '') AS city,
                     COALESCE(phonenumber, '') AS phonenumber,
                     COALESCE(gstn, '') AS gstn,
                     COALESCE(companyaddress1, '') AS companyaddress1,
                     COALESCE(fssai_no, '') AS fssai_no,
                     COALESCE(emailid, '') AS emailid
              FROM tblcompanydetails
              WHERE branchcode = $1
              LIMIT 1";
    $result = mch_pg_query_params($con, $query, array($branchcode));
    if ($result && pg_num_rows($result) > 0) {
        return pg_fetch_assoc($result);
    }

    return array(
        'companyname' => '',
        'companyaddress' => '',
        'area' => '',
        'city' => '',
        'phonenumber' => '',
        'gstn' => '',
        'companyaddress1' => '',
        'fssai_no' => '',
        'emailid' => '',
    );
}

function mch_fetch_print_details($con, $deviceid = '', $branchcode = null)
{
    $select = "SELECT COALESCE(printerip, '') AS printerip,
                      COALESCE(printerport, '') AS printerport,
                      COALESCE(headertext, '') AS headertext,
                      COALESCE(footertext, '') AS footertext
               FROM tblprintdetails";

    // tblprintdetails.deviceid is a numeric internal id, not the Android IMEI hex string.
    if ($deviceid !== '' && ctype_digit((string)$deviceid)) {
        $result = mch_pg_query_params($con, $select . " WHERE deviceid = $1 LIMIT 1", array((int)$deviceid));
        if ($result && pg_num_rows($result) > 0) {
            return pg_fetch_assoc($result);
        }
    }

    if ($branchcode !== null && $branchcode !== '' && ctype_digit((string)$branchcode)) {
        $result = mch_pg_query_params($con, $select . " WHERE clientcode = $1 LIMIT 1", array((int)$branchcode));
        if ($result && pg_num_rows($result) > 0) {
            return pg_fetch_assoc($result);
        }
    }

    $fallbackResult = pg_query($con, $select . " LIMIT 1");
    if ($fallbackResult && pg_num_rows($fallbackResult) > 0) {
        return pg_fetch_assoc($fallbackResult);
    }

    return array(
        'printerip' => '',
        'printerport' => '',
        'headertext' => '',
        'footertext' => '',
    );
}

function mch_fetch_sale_print_record($con, $transactionno, $voucherno, $imeino)
{
    $query = "SELECT transactionno, voucherno, deviceid, branchcode, transactiondate, grandtotal, noofitems,
                     salestime, cgstamount, sgstamount, discamount, netamount, discper, basicamount,
                     taxamount, taxname, exchangerate, cashamount, creditamount, onlineamount, onlineorders,
                     onlineorderamount, creditcustomeramount, modeofpayment, customermobileno, customername
              FROM tblsales
              WHERE transactionno = $1 AND voucherno = $2 AND deviceid = $3
              LIMIT 1";
    $result = mch_pg_query_params($con, $query, array((string)$transactionno, (string)$voucherno, $imeino));
    if ($result && pg_num_rows($result) > 0) {
        return pg_fetch_assoc($result);
    }
    return null;
}

function mch_fetch_sale_print_items($con, $transactionno, $voucherno, $imeino)
{
    $query = "SELECT a.transactionno, a.voucherno, a.deviceid, a.branchcode, a.itemcode,
                     COALESCE(b.itemname, '') AS itemname, a.qty, a.price, a.amount, a.basicrate, a.basicamount
              FROM tblsalesitemdetails AS a
              LEFT JOIN tblitemmaster AS b ON a.itemcode = b.itemcode
              WHERE a.transactionno = $1 AND a.voucherno = $2 AND a.deviceid = $3
              ORDER BY a.autonum ASC";
    $result = mch_pg_query_params($con, $query, array((string)$transactionno, (string)$voucherno, $imeino));

    $items = array();
    if ($result) {
        while ($row = pg_fetch_assoc($result)) {
            $items[] = array(
                'transactionno' => (string)$row['transactionno'],
                'voucherno' => (string)$row['voucherno'],
                'devicecode' => (string)$row['deviceid'],
                'branchcode' => (string)$row['branchcode'],
                'itemcode' => (string)$row['itemcode'],
                'itemname' => (string)$row['itemname'],
                'qty' => mch_format_amount($row['qty'], 2),
                'price' => mch_format_amount($row['price'], 2),
                'amount' => mch_format_amount($row['amount'], 2),
                'basicrate' => mch_format_amount($row['basicrate'], 2),
                'basicamount' => mch_format_amount($row['basicamount'], 2),
            );
        }
    }

    return $items;
}

function mch_build_print_json($con, $transactionno, $voucherno, $imeino, $branchcode)
{
    $sale = mch_fetch_sale_print_record($con, $transactionno, $voucherno, $imeino);
    if ($sale === null) {
        return array();
    }

    $items = mch_fetch_sale_print_items($con, $transactionno, $voucherno, $imeino);
    $company = mch_fetch_company_details($con, $branchcode);
    $printDetails = mch_fetch_print_details($con, $imeino, $branchcode);
    $branchname = mch_fetch_branch_name($con, $branchcode);
    $modeOfPayment = mch_get_mode_of_payment($sale);

    return array(
        'header' => array(
            'branchname' => $branchname,
            'companyname' => $company['companyname'],
            'companyaddress' => $company['companyaddress'],
            'companyaddress1' => $company['companyaddress1'],
            'area' => $company['area'],
            'city' => $company['city'],
            'phonenumber' => $company['phonenumber'],
            'gstn' => $company['gstn'],
            'fssai_no' => $company['fssai_no'],
            'emailid' => $company['emailid'],
            'headertext' => $printDetails['headertext'],
            'footertext' => $printDetails['footertext'],
            'printerip' => $printDetails['printerip'],
            'printerport' => $printDetails['printerport'],
            'transactionno' => (string)$sale['transactionno'],
            'billno' => (string)$sale['voucherno'],
            'voucherno' => (string)$sale['voucherno'],
            'devicecode' => (string)$sale['deviceid'],
            'branchcode' => (string)$sale['branchcode'],
            'transactiondate' => (string)$sale['transactiondate'],
            'billdate' => mch_format_bill_date($sale['transactiondate']),
            'salestime' => (string)$sale['salestime'],
            'modeofpayment' => (string)$sale['modeofpayment'],
            'modeofpaymenttext' => $modeOfPayment,
            'customermobileno' => (string)$sale['customermobileno'],
            'customername' => (string)$sale['customername'],
        ),
        'items' => $items,
        'totals' => array(
            'subtotal' => mch_format_amount($sale['basicamount']),
            'discper' => mch_format_amount($sale['discper']),
            'discamount' => mch_format_amount($sale['discamount']),
            'cgstamount' => mch_format_amount($sale['cgstamount']),
            'sgstamount' => mch_format_amount($sale['sgstamount']),
            'taxamount' => mch_format_amount($sale['taxamount']),
            'taxname' => (string)$sale['taxname'],
            'netamount' => mch_format_amount($sale['netamount']),
            'grandtotal' => mch_format_amount($sale['grandtotal']),
            'noofitems' => (string)$sale['noofitems'],
            'cashamount' => mch_format_amount($sale['cashamount']),
            'creditamount' => mch_format_amount($sale['creditamount']),
            'onlineamount' => mch_format_amount($sale['onlineamount']),
            'onlineorders' => (string)$sale['onlineorders'],
            'onlineorderamount' => mch_format_amount($sale['onlineorderamount']),
            'creditcustomeramount' => mch_format_amount($sale['creditcustomeramount']),
            'exchangerate' => mch_format_amount($sale['exchangerate']),
        ),
    );
}
