<?php
require('config.php');

// Initialize result structure
$response = [
    "error" => false,
    "message" => "Credit transactions fetched successfully",
    "totalCount" => 0,
    "totalAmount" => 0,
    "paidTransactions" => [
        "totalCount" => 0,
        "amount" => 0
    ],
    "unpaidTransactions" => [
        "totalCount" => 0,
        "amount" => 0
    ],
    "transactions" => []
];

try {
    // Query to fetch credit transactions and their associated credit details
    $query = "
        SELECT 
            t.id AS transactionID,
            t.productCode,
            t.quantity AS transactionQuantity,
            t.amount,
            t.releaseDate,
            t.releaseType,
            t.status AS transactionStatus,
            p.productName,
            p.productType,
            s.size AS stockSize,
            c.id AS creditID,
            c.date_taken,
            c.reason,
            c.source_of_fund,
            c.taken_by,
            c.title,
            c.phone,
            c.quantity AS creditQuantity,
            c.status AS creditStatus,
            c.served_by,
            c.c_amount,
            -- Subquery to calculate the sum of all amountPaid in creditsPayments for each transID
            (SELECT SUM(cp.amountPaid) FROM creditsPayments cp WHERE cp.transID = t.id) AS amount_paid
        FROM 
            transactions t
        JOIN 
            products p ON t.productCode = p.id
        LEFT JOIN 
            stock s ON t.productCode = s.productID
        LEFT JOIN 
            credits c ON t.id = c.transID
        WHERE 
            t.releaseType = 'Credit'
    ";

    // Prepare and execute the query
    $stmt = $connect->prepare($query);
    if (!$stmt) {
        throw new Exception("Failed to prepare query: " . $connect->error);
    }

    $stmt->execute();
    $result = $stmt->get_result();

    // Process the results
    while ($row = $result->fetch_assoc()) {
        // Prepare transaction details
        $transaction = [
            "transactionID" => $row["transactionID"],
            "productCode" => $row["productCode"],
            "transactionQuantity" => $row["transactionQuantity"],
            "amount" => $row["amount"],
            "amount_paid" => $row["amount_paid"], // Add the calculated amount_paid field
            "releaseDate" => $row["releaseDate"],
            "releaseType" => $row["releaseType"],
            "transactionStatus" => $row["transactionStatus"],
            "productName" => $row["productName"],
            "productType" => $row["productType"],
            "stockSize" => $row["stockSize"],
            "creditDetails" => [
                "creditID" => $row["creditID"],
                "date_taken" => $row["date_taken"],
                "reason" => $row["reason"],
                "source_of_fund" => $row["source_of_fund"],
                "taken_by" => $row["taken_by"],
                "title" => $row["title"],
                "phone" => $row["phone"],
                "creditQuantity" => $row["creditQuantity"],
                "creditStatus" => $row["creditStatus"],
                "served_by" => $row["served_by"],
                "c_amount" => $row["c_amount"]
            ]
        ];

        // Add transaction to the transactions list
        $response["transactions"][] = $transaction;

        // Update overall totals
        if ($row["amount"] !== null) {
            $response["totalAmount"] += $row["amount"];
        }
        $response["totalCount"]++;

        // Update paid/unpaid totals
        if ($row["creditStatus"] === "Fully Paid") {
            $response["paidTransactions"]["totalCount"]++;
            $response["paidTransactions"]["amount"] += $row["amount"];
        } else {
            // Combine "Partially Paid" and "Unpaid"
            $response["unpaidTransactions"]["totalCount"]++;
            $response["unpaidTransactions"]["amount"] += $row["amount"];
        }
    }
} catch (Exception $e) {
    // Handle exceptions and return an error response
    $response = [
        "error" => true,
        "message" => $e->getMessage()
    ];
}

// Output the response as JSON
echo json_encode($response);
?>
