<?php

/**
 * High-performance bulk sales sync using PostgreSQL json_to_recordset().
 *
 * Transaction flow per request:
 *   BEGIN → sync log → delete items → delete sales → bulk insert sales → bulk insert items → COMMIT
 */
class SalesSyncBulkProcessor
{
    /** @var \PDO */
    private $pdo;

    public function __construct(\PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    /**
     * Parse legacy {"JSonObject":[...]} wrapper into a PHP array.
     *
     * @param string $raw
     * @return array
     */
    public static function parsePayloadArray($raw)
    {
        if ($raw === null || $raw === '') {
            return array();
        }

        $normalized = str_replace('{"JSonObject":', '', $raw);
        $normalized = substr_replace($normalized, '', -1);
        $decoded = json_decode($normalized, true);

        return is_array($decoded) ? $decoded : array();
    }

    /**
     * Execute full bulk sync and return API response fragment.
     *
     * @param string $devicecode
     * @param string $imeino
     * @param array  $salesRows
     * @param array  $itemRows
     * @param string $syncdateMs
     * @return array
     */
    public function sync($devicecode, $imeino, array $salesRows, array $itemRows, $syncdateMs)
    {
        $salesJson = json_encode($salesRows, JSON_UNESCAPED_UNICODE);
        $itemsJson = json_encode($itemRows, JSON_UNESCAPED_UNICODE);

        $this->pdo->beginTransaction();

        try {
            $makerId = $this->insertSyncAudit($imeino, $devicecode);
            $this->deleteExistingItems($salesJson, $devicecode);
            $this->deleteExistingSales($salesJson, $devicecode);
            $this->bulkInsertSales($salesJson, $devicecode, $syncdateMs, $makerId);
            $this->bulkInsertItems($salesJson, $itemsJson, $devicecode, $syncdateMs);

            $this->pdo->commit();
        } catch (Exception $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            throw $e;
        }

        return $this->buildSyncResponse($salesRows, $itemRows, $devicecode);
    }

    /**
     * Post-commit sales count verification (unchanged legacy behaviour).
     *
     * @param string $billdate
     * @param string $imeino
     * @param string $expectedCount
     * @return int 1 when counts match, 0 otherwise
     */
    public function verifySalesCount($billdate, $imeino, $expectedCount)
    {
        if ($imeino === '') {
            return 0;
        }

        $stmt = $this->pdo->prepare(
            'SELECT COALESCE(COUNT(*), 0) FROM tblsales WHERE transactiondate = :billdate AND deviceid = :imeino'
        );
        $stmt->execute(array(
            ':billdate' => $billdate,
            ':imeino' => $imeino,
        ));

        $dbCount = (int) $stmt->fetchColumn();

        return ((string) $dbCount === (string) $expectedCount) ? 1 : 0;
    }

    private function insertSyncAudit($imeino, $devicecode)
    {
        $syncStmt = $this->pdo->prepare(
            "INSERT INTO tblsyncdetails (imeino, syncdate, synctype, devicecode)
             VALUES (:imeino, NOW(), 'Sales', :devicecode)"
        );
        $syncStmt->execute(array(
            ':imeino' => $imeino,
            ':devicecode' => $devicecode,
        ));

        $logStmt = $this->pdo->prepare(
            'INSERT INTO tbluserlog (userid, originator, createdtime, "Action")
             VALUES (:userid, :originator, NOW(), :action)
             RETURNING autonum'
        );
        $logStmt->execute(array(
            ':userid' => $devicecode,
            ':originator' => 'Insert Sales',
            ':action' => 'Insert',
        ));

        return (int) $logStmt->fetchColumn();
    }

    /**
     * Delete item rows for every bill key present in the incoming sales payload.
     */
    private function deleteExistingItems($salesJson, $devicecode)
    {
        $sql = <<<'SQL'
DELETE FROM tblsalesitemdetails AS si
USING json_to_recordset(CAST(:sales_json AS json)) AS j(
    transactionno text,
    branchcode    text,
    devicecode    text
)
WHERE si.transactionno::text = j.transactionno
  AND si.branchcode::text = j.branchcode
  AND si.deviceid = COALESCE(NULLIF((j.devicecode), ''), :fallback_deviceid)
SQL;

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array(
            ':sales_json' => $salesJson,
            ':fallback_deviceid' => $devicecode,
        ));
    }

    /**
     * Delete sales rows for every bill key present in the incoming sales payload.
     */
    private function deleteExistingSales($salesJson, $devicecode)
    {
        $sql = <<<'SQL'
DELETE FROM tblsales AS s
USING json_to_recordset(CAST(:sales_json AS json)) AS j(
    transactionno text,
    branchcode    text,
    devicecode    text
)
WHERE s.transactionno::text = j.transactionno
  AND s.branchcode::text = j.branchcode
  AND s.deviceid = COALESCE(NULLIF((j.devicecode), ''), :fallback_deviceid)
SQL;

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array(
            ':sales_json' => $salesJson,
            ':fallback_deviceid' => $devicecode,
        ));
    }

    /**
     * Single set-based INSERT for all tblsales rows from the sales JSON array.
     */
    private function bulkInsertSales($salesJson, $devicecode, $syncdateMs, $makerId)
    {
        $sql = <<<'SQL'
INSERT INTO tblsales (
    transactionno, voucherno, sequenceno, deviceid, transactiondate, grandtotal, noofitems,
    salestime, createddate, updateddate, syncflag, statuscode, syncdate, branchcode,
    makerid, cancelflag, clientcode, modeofpayment, apptype, customername,
    customermobileno, cashamount, creditamount, onlineamount, onlineorders, sessionid,
    netamount, discper, discamount, basicamount, cgstamount, sgstamount, takername,
    onlineorderamount, creditcustomercode, creditcustomeramount, 
    taxamount, taxname, exchangerate
)
SELECT
    j.transactionno,
    j.voucherno::text,
    COALESCE(j.sequenceno, 0),
    COALESCE(NULLIF((j.devicecode), ''), :fallback_deviceid),
    j.transactiondate,
    COALESCE(NULLIF(j.grandtotal, 0), 0),
    COALESCE(NULLIF(j.noofitems, '0')::int, 0),
    j.salestime,
    j.createddate,
    j.updateddate,
    COALESCE(NULLIF(j.syncflag, '0')::int, 1),
    COALESCE(NULLIF(j.statuscode, '0')::int, 1),
    :syncdate_ms,
    j.branchcode,
    :maker_id,
    COALESCE(NULLIF(j.cancelflag, '0')::int, 0),
    j.clientcode,
    COALESCE(NULLIF(j.modeofpayment, '0')::int, 1),
    j.apptype,
    j.customername,
    COALESCE(NULLIF(j.customermobileno, ''), NULLIF(j.mobileno, ''), ''),
    COALESCE(NULLIF(j.cashamount, 0), 0),
    COALESCE(NULLIF(j.creditamount, 0), 0),
    COALESCE(NULLIF(j.onlineamount, 0), 0),
    j.onlineorders,
    CASE
        WHEN j.sessionid IS NULL
          OR (j.sessionid) = ''
          OR LOWER((j.sessionid)) = 'null' THEN 0
        ELSE j.sessionid::bigint
    END,
    COALESCE(
        NULLIF(NULLIF(j.netamount, '0'), '0'),
        NULLIF(j.grandtotal, '0'),
        0
    ),
    COALESCE(NULLIF(j.discper, 0), 0),
    COALESCE(NULLIF(j.discamount, 0), 0),
    COALESCE(
        NULLIF(NULLIF(j.subtotal, '0'), '0'),
        NULLIF(j.basicamount, '0'),
        0
    ),
    COALESCE(NULLIF(j.sgstamount, 0), 0),
    COALESCE(NULLIF(j.cgstamount, 0), 0),
    COALESCE(NULLIF((j.takername), ''), ''),
    COALESCE(NULLIF(j.onlineorderamount, 0), 0),
    COALESCE(NULLIF(j.creditcustomercode, '0')::integer, 0),
    COALESCE(NULLIF(j.creditcustomeramount, 0), 0),
    COALESCE(NULLIF(j.taxamount, 0), 0),
    COALESCE(j.taxname, ''),
    COALESCE(NULLIF(j.exchangerate, 0), 0)
FROM json_to_recordset(CAST(:sales_json AS json)) AS j(
    transactionno           integer,
    voucherno               text,
    sequenceno              integer,
    devicecode              text,
    branchcode              integer,
    transactiondate         double precision,
    grandtotal              double precision,
    noofitems               integer,
    salestime               text,
    createddate             double precision,
    updateddate             double precision,
    syncflag                integer,
    statuscode              integer,
    cancelflag              integer,
    clientcode              integer,
    modeofpayment           integer,
    apptype                 text,
    customername            text,
    mobileno                text,
    customermobileno        text,
    cashamount              double precision,
    creditamount            double precision,
    onlineamount            double precision,
    onlineorders            text,
    sessionid               text,
    netamount               double precision,
    discper                 double precision,
    discamount              double precision,
    subtotal                double precision,
    basicamount             double precision,
    cgstamount              double precision,
    sgstamount              double precision,
    takername               text,
    onlineorderamount       double precision,
    creditcustomercode      integer,
    creditcustomeramount    double precision,
    taxamount               double precision,
    taxname                 text,
    exchangerate            double precision
)
SQL;

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array(
            ':sales_json' => $salesJson,
            ':fallback_deviceid' => $devicecode,
            ':syncdate_ms' => $syncdateMs,
            ':maker_id' => $makerId,
        ));
    }

    /**
     * Single set-based INSERT for all tblsalesitemdetails rows joined to sales JSON.
     */
    private function bulkInsertItems($salesJson, $itemsJson, $devicecode, $syncdateMs)
    {
        $sql = <<<'SQL'
INSERT INTO tblsalesitemdetails (
    transactiondate, transactionno, voucherno, sequenceno, deviceid, branchcode, itemcode,
    qty, price, amount, createddate, updateddate, syncflag, syncautonum, syncdate,
    clientcode, basicrate, basicamount
)
SELECT
    COALESCE(i.transactiondate, s.transactiondate),
    i.transactionno,
    COALESCE(NULLIF((i.voucherno), ''), s.voucherno)::text,
    COALESCE(i.sequenceno, s.sequenceno, 0),
    COALESCE(NULLIF((i.devicecode), '0'), :fallback_deviceid),
    i.branchcode,
    i.itemcode,
    i.qty,
    COALESCE(i.price::numeric, 0),
    COALESCE(i.amount::numeric, 0),
    i.createddate,
    COALESCE(i.updateddate, '0'),
    COALESCE(NULLIF(i.syncflag::text, '0')::int, 1),
    i.autonum,
    :syncdate_ms,
    i.clientcode,
    COALESCE(NULLIF(i.basicrate::text, '0')::numeric, 0),
    COALESCE(NULLIF(i.basicamount::text, '0')::numeric, 0)
FROM json_to_recordset(CAST(:items_json AS json)) AS i(
    autonum          int,
    transactionno    int,
    voucherno        text,
    sequenceno       integer,
    devicecode       text,
    branchcode       int,
    itemcode         int,
    qty              double precision,
    price            double precision,
    amount           double precision,
    createddate      double precision,
    updateddate      double precision,
    syncflag         int,
    basicrate        double precision,
    basicamount      double precision,
    clientcode       int,
    transactiondate  double precision
)
INNER JOIN json_to_recordset(CAST(:sales_json AS json)) AS s(
    transactionno    integer,
    devicecode       text,
    transactiondate  double precision,
    voucherno        text,
    sequenceno       integer,
    branchcode       int
)
    ON i.transactionno = s.transactionno
   AND COALESCE(NULLIF((i.devicecode), ''), :fallback_deviceid)
     = COALESCE(NULLIF((s.devicecode), ''), :fallback_deviceid)
SQL;

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array(
            ':sales_json' => $salesJson,
            ':items_json' => $itemsJson,
            ':fallback_deviceid' => $devicecode,
            ':syncdate_ms' => $syncdateMs,
        ));
    }

    /**
     * Build legacy response arrays (no database round-trips).
     *
     * @param array  $salesRows
     * @param array  $itemRows
     * @param string $devicecode
     * @return array
     */
    private function buildSyncResponse(array $salesRows, array $itemRows, $devicecode)
    {
        $salesKeys = array();
        foreach ($salesRows as $sale) {
            if (!isset($sale['transactionno'])) {
                continue;
            }
            $saleDevice = isset($sale['devicecode']) ? $sale['devicecode'] : $devicecode;
            $salesKeys[$sale['transactionno'] . '|' . $saleDevice] = true;
        }

        $salesTxn = array();
        $itemTxn = array();

        foreach ($itemRows as $item) {
            if (!isset($item['transactionno'])) {
                continue;
            }
            $itemDevice = isset($item['devicecode']) ? $item['devicecode'] : $devicecode;
            $key = $item['transactionno'] . '|' . $itemDevice;
            if (!isset($salesKeys[$key])) {
                continue;
            }
            $salesTxn[] = $item['transactionno'];
            $itemTxn[] = $item['transactionno'];
        }

        return array(
            'SalesTransactionDetails' => array_values(array_unique($salesTxn)),
            'SalesItemTransactionDetails' => array_values(array_unique($itemTxn)),
        );
    }
}
