<?php
/**
 * File: api/cash/cash_party_report.php
 * v3 (2026-06-05): Duplicate fix + সঠিক হিসাব
 *
 * হিসাবের নিয়ম:
 *   sales_total = SUM(grand_total) যেখানে customer_id = party.id
 *   cash_received = SUM(amount) যেখানে direction=IN, party_id = party.id
 *   cash_paid_out = SUM(amount) যেখানে direction=OUT, party_id = party.id
 *
 *   net = (opening_receivable - opening_payable)
 *       + sales_total        ← আমরা পাব (বিক্রি করেছি)
 *       - cash_received      ← গ্রাহক দিয়েছে (কমবে)
 *       + cash_paid_out      ← আমরা দিয়েছি (বাড়বে)
 *
 *   net > 0 → পাবেন | net < 0 → দেবেন
 */

header("Content-Type: application/json; charset=UTF-8");

require_once __DIR__ . '/../../config/session.php';
require_once __DIR__ . '/../../config/db.php';

if (session_status() === PHP_SESSION_NONE) session_start();

if (
    (!isset($_SESSION['user_id']) || empty($_SESSION['user_id'])) &&
    (!function_exists('is_logged_in') || !is_logged_in())
) {
    echo json_encode(["success" => false, "message" => "লগইন প্রয়োজন", "data" => null], JSON_UNESCAPED_UNICODE);
    exit;
}

try {
    $q      = trim((string)($_GET['q'] ?? ''));
    $where  = ["p.deleted_at IS NULL"];
    $params = [];

    if ($q !== '') {
        $where[]  = "(p.name LIKE ? OR p.phone LIKE ?)";
        $params[] = "%$q%";
        $params[] = "%$q%";
    }
    $whereSql = implode(" AND ", $where);

    // ============================================================
    // সব subquery আলাদা — JOIN নেই, তাই duplicate হবে না
    // ============================================================
    $sql = "
        SELECT
            p.id,
            p.name,
            p.phone,
            p.opening_receivable,
            p.opening_payable,

            -- cash IN (গ্রাহক/সাপ্লায়ার থেকে পেয়েছি)
            COALESCE((
                SELECT SUM(ct.amount)
                FROM cash_transactions ct
                WHERE ct.party_id  = p.id
                  AND ct.direction = 'IN'
                  AND ct.is_deleted = 0
            ), 0) AS cash_in,

            -- cash OUT (গ্রাহক/সাপ্লায়ারকে দিয়েছি)
            COALESCE((
                SELECT SUM(ct.amount)
                FROM cash_transactions ct
                WHERE ct.party_id  = p.id
                  AND ct.direction = 'OUT'
                  AND ct.is_deleted = 0
            ), 0) AS cash_out,

            -- cash transaction count
            COALESCE((
                SELECT COUNT(ct.id)
                FROM cash_transactions ct
                WHERE ct.party_id  = p.id
                  AND ct.is_deleted = 0
            ), 0) AS txn_count,

            -- sales: মোট বিক্রির টাকা (grand_total)
            COALESCE((
                SELECT SUM(s.grand_total)
                FROM sales s
                WHERE s.customer_id = p.id
            ), 0) AS sales_total,

            -- sales: কত জমা হয়েছে
            COALESCE((
                SELECT SUM(s.paid_total)
                FROM sales s
                WHERE s.customer_id = p.id
            ), 0) AS sales_paid

        FROM parties p
        WHERE $whereSql

        HAVING (
            cash_in > 0 OR cash_out > 0 OR sales_total > 0
            OR p.opening_receivable > 0 OR p.opening_payable > 0
        )

        ORDER BY p.name ASC
    ";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $parties         = [];
    $totalReceivable = 0.0;
    $totalPayable    = 0.0;

    foreach ($rows as $r) {
        $openR      = (float)$r['opening_receivable'];
        $openP      = (float)$r['opening_payable'];
        $cashIn     = (float)$r['cash_in'];
        $cashOut    = (float)$r['cash_out'];
        $salesTotal = (float)$r['sales_total'];
        $salesPaid  = (float)$r['sales_paid'];

        // sales_due = বিক্রি হয়েছে - জমা হয়েছে
        $salesDue = $salesTotal - $salesPaid;

        // net: আমরা কত পাব
        // + sales_total (বিক্রি করেছি, পাওয়ার কথা)
        // - cash_in (গ্রাহক দিয়েছে, কমে গেছে)
        // + cash_out (আমরা দিয়েছি, তারা পাবে)
        // কিন্তু sales payment cash_in-এ আছে — তাই sales_due ব্যবহার করাই সঠিক
        $net = ($openR - $openP) + $salesDue + ($cashOut - $cashIn);
        
        // cash_in-এ sale payment আছে, তাই sales_paid বাদ দেওয়া দরকার নেই
        // শুধু non-sale cash_in/out আলাদা করা জটিল — তাই:
        // net = opening_net + sales_due + (non-sale cash_out - non-sale cash_in)
        // কিন্তু sale payment cash_in-এ ট্র্যাক হয়, sales_paid-ও আছে
        // তাই: net = opening_net + (sales_total - cash_in) + cash_out
        $net = round(($openR - $openP) + ($salesTotal - $cashIn) + $cashOut, 2);

        if ($net > 0.01)      { $status = 'RECEIVABLE'; $totalReceivable += $net; }
        elseif ($net < -0.01) { $status = 'PAYABLE';    $totalPayable    += (-$net); }
        else                  { $status = 'SETTLED'; }

        $parties[] = [
            'id'          => (int)$r['id'],
            'name'        => (string)$r['name'],
            'phone'       => (string)($r['phone'] ?? ''),
            'total_in'    => round($cashIn, 2),
            'total_out'   => round($cashOut, 2),
            'sales_total' => round($salesTotal, 2),
            'sales_due'   => round($salesDue, 2),
            'net'         => $net,
            'abs_net'     => round(abs($net), 2),
            'status'      => $status,
            'txn_count'   => (int)$r['txn_count']
        ];
    }

    // receivable বড়→ছোট, তারপর payable বড়→ছোট
    usort($parties, function($a, $b){
        if ($a['net'] === $b['net']) return strcmp($a['name'], $b['name']);
        return ($b['net'] <=> $a['net']);
    });

    echo json_encode([
        'success' => true,
        'message' => 'রিপোর্ট তৈরি',
        'data'    => [
            'parties' => $parties,
            'summary' => [
                'total_receivable' => round($totalReceivable, 2),
                'total_payable'    => round($totalPayable, 2),
                'net_position'     => round($totalReceivable - $totalPayable, 2),
                'party_count'      => count($parties)
            ]
        ]
    ], JSON_UNESCAPED_UNICODE);

} catch (Throwable $e) {
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'message' => 'সার্ভার সমস্যা: ' . $e->getMessage(),
        'data'    => null
    ], JSON_UNESCAPED_UNICODE);
}
exit;
