<?php
class SupplierSetup {

    /** Add new supplier */
    public static function AddSupplierSetup($data) {
        global $db;
        $id = $db->query_insert(TABLE_SUPPLIERSETUP, $data);
        if ($db->affected_rows > 0) {
            // $db->query_insert("vrs_logs", $data); // optional, keep commented like your style
            return $id;
        }
        return false;
    }

    /** Update supplier by id */
    public static function UpdateSupplierSetup($pid, $data) {
        global $db;
        $res = $db->query_update(TABLE_SUPPLIERSETUP, $data, "id='$pid'");
        return $res;
    }

    /** Get supplier by id */
    public static function GetSupplierSetupById($id) {
        global $db;
        $q = "SELECT * FROM ".TABLE_SUPPLIERSETUP." WHERE id='$id'";
        $row = $db->query_first($q);
        if ($db->affected_rows > 0) return $row;
        return false;
    }

    /** Get supplier by code prefix (if you later add a code field) */
    public static function GetSupplierSetupByCode($pcode) {
        global $db;
        $q = "SELECT * FROM ".TABLE_SUPPLIERSETUP." WHERE code LIKE '$pcode%'";
        $row = $db->query_first($q);
        if ($db->affected_rows > 0) return $row;
        return false;
    }

    /** Check duplicate name except given id */
    public static function CheckSupplier($name, $id) {
        global $db;
        $q = "SELECT * FROM ".TABLE_SUPPLIERSETUP." WHERE name='$name' && id <> '$id'";
        $row = $db->query_first($q);
        if ($db->affected_rows > 0) return $row;
        return false;
    }

    /** List suppliers (with optional filter/pagination like your pattern) */
    public static function GetSupplierSetupList($filter = "", $pageno = "null", $pagesize = "null") {
        global $db;
        $q = "SELECT * FROM ".TABLE_SUPPLIERSETUP." ".$filter." ORDER BY name ASC";
        $rows = $db->fetch_all_array($q, $pageno, $pagesize);
        if ($db->affected_rows > 0) return $rows;
        return false;
    }
  
 /**********************************************************
/ This function will return Supplier Opening Balance
**********************************************************/
public static function GetSupplierLedger($dateFrom, $dateTo, $supplierId = 'All') {
global $db;

$result = [
    'opening_balance' => [],
    'ledger' => []
];

$supplierCondition = ($supplierId !== 'All') ? "AND s.id = '$supplierId'" : "";

// Opening Balance Query
$openingBalanceQuery = "
    SELECT 
        s.id AS supplier_id,
        s.name AS supplier_name,
        IFNULL(purchase.total_debit, 0) AS total_debit,
        IFNULL(payment.total_credit, 0) AS total_credit,
        (IFNULL(purchase.total_debit, 0) - IFNULL(payment.total_credit, 0)) AS opening_balance
    FROM supplier_setup s
    LEFT JOIN (
        SELECT 
            p.supplier_id,
            SUM(p.total_amount) AS total_debit
        FROM purchase p
        WHERE p.date < '$dateFrom'
        GROUP BY p.supplier_id
    ) AS purchase ON s.id = purchase.supplier_id
    LEFT JOIN (
        SELECT 
            py.supplier_id,
            SUM(py.amount) AS total_credit
        FROM purpayment py
        WHERE py.create_date < '$dateFrom'
        AND py.status <> 'Cancel'
        GROUP BY py.supplier_id
    ) AS payment ON s.id = payment.supplier_id
    WHERE 1=1 $supplierCondition
    GROUP BY s.name, s.id;
";

$openingBalanceResult = $db->fetch_all_array($openingBalanceQuery);
if ($openingBalanceResult && is_array($openingBalanceResult)) {
    $result['opening_balance'] = $openingBalanceResult;
}

// Ledger Query - itemized (no aggregation)
$ledgerQuery = "
    SELECT
        s.id   AS supplier_id,
        s.name AS supplier_name,
        L.date,
        L.transaction_type AS source,
        L.reference,
        CASE
            WHEN L.transaction_type = 'Payment' THEN
                CONCAT('Cheque: ', IFNULL(L.cheque_number,''), 
                       IF(L.cheque_date IS NULL OR L.cheque_date='','',
                          CONCAT(', Date: ', DATE_FORMAT(L.cheque_date, '%d-%m-%Y'))))
            ELSE ''
        END AS cheque_details,
        L.debit,
        L.credit,
        L.supplier_payment_type
    FROM (
        /* Purchases: one row per purchase */
        SELECT 
            p.supplier_id,
            p.date AS date,
            'Purchase' AS transaction_type,
            /* ensure a non-empty unique reference */
            IFNULL(NULLIF(p.po_number, ''), CONCAT('PUR-', p.id)) AS reference,
            NULL AS cheque_number,
            NULL AS cheque_date,
            p.total_amount AS debit,
            0 AS credit,
            NULL AS supplier_payment_type
        FROM purchase p
        WHERE p.date BETWEEN '$dateFrom 00:00:00' AND '$dateTo 23:59:59'

        UNION ALL

        /* Payments: one row per payment */
        SELECT
            py.supplier_id,
            py.create_date AS date,
            'Payment' AS transaction_type,
            py.code AS reference,
            py.cheque_number AS cheque_number,
            /* use your actual cheque date column if different */
            py.cheque_date AS cheque_date,
            0 AS debit,
            py.amount AS credit,
            py.payment_type AS supplier_payment_type
        FROM purpayment py
        WHERE py.create_date BETWEEN '$dateFrom 00:00:00' AND '$dateTo 23:59:59'
          AND py.status <> 'Cancel'
    ) AS L
    JOIN supplier_setup s ON s.id = L.supplier_id
    WHERE 1=1 $supplierCondition
    ORDER BY
        L.supplier_id,
        L.date,
        /* helps keep same-day entries in a stable order */
        L.transaction_type,
        L.reference
";

$ledgerResult = $db->fetch_all_array($ledgerQuery);
 // print_r($ledgerResult);
if ($ledgerResult && is_array($ledgerResult)) {
    $result['ledger'] = $ledgerResult;
}

return $result;


}
  
  /**********************************************************
 * Customer Balance Summary Report
 * One row per customer:
 * Customer Name, Last Invoice Date, Last Payment Date, Last Payment Amount, Remaining Balance
 * Sorted by Remaining Balance DESC
 **********************************************************/
public static function GetCustomerBalanceSummary($customerId = 'All')
{
    global $db;

    $customerCondition = ($customerId !== 'All') ? "AND c.id = '$customerId'" : "";

    $query = "
        SELECT
            c.id AS customer_id,
            c.name AS customer_name,

            inv.last_invoice_date,
            pay.last_payment_date,
            pay.last_payment_amount,

            (IFNULL(inv.total_invoice_amount, 0) - IFNULL(pay.total_payment_amount, 0)) AS remaining_balance

        FROM " . TABLE_CUSTOMERSETUP . " c

        LEFT JOIN (
            SELECT
                customer_id,
                SUM(total_amount) AS total_invoice_amount,
                MAX(DATE(create_date)) AS last_invoice_date
            FROM " . TABLE_INVOICE . "
            WHERE status NOT IN ('Draft','Cancel')
            GROUP BY customer_id
        ) inv ON inv.customer_id = c.id

        LEFT JOIN (
            SELECT
                p.customer_id,
                SUM(p.amount) AS total_payment_amount,
                MAX(DATE(p.create_date)) AS last_payment_date,
                (
                    SELECT p2.amount
                    FROM " . TABLE_PAYMENT . " p2
                    WHERE p2.customer_id = p.customer_id
                      AND p2.status <> 'Cancel'
                    ORDER BY p2.create_date DESC, p2.id DESC
                    LIMIT 1
                ) AS last_payment_amount
            FROM " . TABLE_PAYMENT . " p
            WHERE p.status <> 'Cancel'
            GROUP BY p.customer_id
        ) pay ON pay.customer_id = c.id

        WHERE 1=1
        $customerCondition

        ORDER BY remaining_balance DESC, c.name ASC
    ";

    $rows = $db->fetch_all_array($query);

    if ($db->affected_rows > 0) {
        return $rows;
    } else {
        return false;
    }
}



    public static function GetNumRows() { global $db; return $db->GetNumRows(); }
    public static function GetNumberOfPages() { global $db; return $db->GetNumberOfPages(); }
}
