<?php
// daily_report.php
header("Access-Control-Allow-Origin: *");
date_default_timezone_set('Africa/Dar_es_Salaam');
include '../../config.php'; // $conn = mysqli connection

// Pata parameter ya branch_id (0 = zote)
$branch_id = isset($_GET['branch_id']) ? intval($_GET['branch_id']) : 0;

// Prepare branches query
if ($branch_id === 0) {
    $stmtBranches = $conn->prepare("
        SELECT branch_id, branch_name, form_fee
          FROM branches
    ");
} else {
    $stmtBranches = $conn->prepare("
        SELECT branch_id, branch_name, form_fee
          FROM branches
         WHERE branch_id = ?
    ");
    $stmtBranches->bind_param("i", $branch_id);
}
$stmtBranches->execute();
$resultBranches = $stmtBranches->get_result();

$report = [];
$today = date('Y-m-d');

while ($branch = $resultBranches->fetch_assoc()) {
    $bId = intval($branch['branch_id']);
    $bName = $branch['branch_name'];
    $branchFormFee = floatval($branch['form_fee']);

    // Build daily aggregated query that separates open balances for loans and other
    // and computes closed balances per date
    $sql = "
      SELECT
        d.date,
        COALESCE(SUM(d.expenditures), 0)    AS expenditures,
        COALESCE(SUM(d.loans_issued), 0)     AS loans_issued,
        COALESCE(SUM(d.repayments), 0)       AS repayments,
        COALESCE(SUM(d.fines), 0)            AS fines,
        COALESCE(SUM(d.form_fee), 0)         AS form_fee,
        COALESCE(MAX(d.open_loans), 0)       AS open_loans,
        COALESCE(MAX(d.open_other), 0)       AS open_other,
        /* closed calculations */
        (
          COALESCE(MAX(d.open_loans), 0)
          - COALESCE(SUM(d.loans_issued), 0)
          + COALESCE(SUM(d.repayments), 0)
        ) AS closed_loans,
        (
          COALESCE(MAX(d.open_other), 0)
          + COALESCE(SUM(d.form_fee), 0)
          + COALESCE(SUM(d.fines), 0)
          - COALESCE(SUM(d.expenditures), 0)
        ) AS closed_other
      FROM (
        /* expenditures (matumizi) -> affects OTHER side (negative) */
        SELECT DATE(date) AS date,
               price AS expenditures,
               0 AS loans_issued,
               0 AS repayments,
               0 AS fines,
               0 AS form_fee,
               0 AS open_loans,
               0 AS open_other
        FROM expenditures
        WHERE branch_id = {$bId}

        UNION ALL

        /* loans issued -> affects LOANS side */
        SELECT DATE(loan_date) AS date,
               0 AS expenditures,
               loan_amount AS loans_issued,
               0 AS repayments,
               0 AS fines,
               0 AS form_fee,
               0 AS open_loans,
               0 AS open_other
        FROM loans
        WHERE branch_id = {$bId}

        UNION ALL

        /* repayments -> affects LOANS side (positive) */
        SELECT DATE(payment_date) AS date,
               0 AS expenditures,
               0 AS loans_issued,
               amount_paid AS repayments,
               0 AS fines,
               0 AS form_fee,
               0 AS open_loans,
               0 AS open_other
        FROM loans_collection
        WHERE branch_id = {$bId}

        UNION ALL

        /* fines -> OTHER side (positive) */
        SELECT DATE(date) AS date,
               0 AS expenditures,
               0 AS loans_issued,
               0 AS repayments,
               amount AS fines,
               0 AS form_fee,
               0 AS open_loans,
               0 AS open_other
        FROM fines
        WHERE branch_id = {$bId}

        UNION ALL

        /* form fee per loan -> OTHER side (positive) */
        SELECT DATE(loan_date) AS date,
               0 AS expenditures,
               0 AS loans_issued,
               0 AS repayments,
               0 AS fines,
               {$branchFormFee} AS form_fee,
               0 AS open_loans,
               0 AS open_other
        FROM loans
        WHERE branch_id = {$bId}

        UNION ALL

        /* open balances stored in closed_balance table (previous day's closing becomes open) */
        SELECT DATE(date) AS date,
               0 AS expenditures,
               0 AS loans_issued,
               0 AS repayments,
               0 AS fines,
               0 AS form_fee,
               amount AS open_loans,
               other_amount AS open_other
        FROM closed_balance
        WHERE branch_id = {$bId}
      ) AS d
      GROUP BY d.date
      ORDER BY d.date ASC
    ";

    $res = $conn->query($sql);
    if (!$res) {
        error_log("Error fetching daily data for branch {$bId}: " . $conn->error);
        continue;
    }

    // collect rows
    $branchRows = [];
    while ($row = $res->fetch_assoc()) {
        $branchRows[] = [
            'branch_id'      => $bId,
            'branch_name'    => $bName,
            'date'           => $row['date'],
            'expenditures'   => floatval($row['expenditures']),
            'loans_issued'   => floatval($row['loans_issued']),
            'repayments'     => floatval($row['repayments']),
            'fines'          => floatval($row['fines']),
            'form_fee'       => floatval($row['form_fee']),
            'open_loans'     => floatval($row['open_loans']),
            'open_other'     => floatval($row['open_other']),
            'closed_loans'   => floatval($row['closed_loans']),
            'closed_other'   => floatval($row['closed_other']),
        ];
    }

    //
    // Ensure there's an entry for today; if not, create one using most recent closed_balance as open
    //
    $hasToday = false;
    foreach ($branchRows as $r) {
        if ($r['date'] === $today) {
            $hasToday = true;
            break;
        }
    }

    if (!$hasToday) {
        // fetch most recent closed_balance (both columns)
        $open_loans_yesterday = 0.0;
        $open_other_yesterday = 0.0;
        $qryPrev = "
            SELECT amount, other_amount
              FROM closed_balance
             WHERE branch_id = {$bId}
               AND DATE(date) < '{$today}'
             ORDER BY DATE(date) DESC
             LIMIT 1
        ";
        $resPrev = $conn->query($qryPrev);
        if ($resPrev && $rowPrev = $resPrev->fetch_assoc()) {
            $open_loans_yesterday = floatval($rowPrev['amount']);
            $open_other_yesterday = floatval($rowPrev['other_amount']);
        }

        $branchRows[] = [
            'branch_id'      => $bId,
            'branch_name'    => $bName,
            'date'           => $today,
            'expenditures'   => 0.0,
            'loans_issued'   => 0.0,
            'repayments'     => 0.0,
            'fines'          => 0.0,
            'form_fee'       => 0.0,
            'open_loans'     => $open_loans_yesterday,
            'open_other'     => $open_other_yesterday,
            'closed_loans'   => $open_loans_yesterday, // closed = open if no txns today
            'closed_other'   => $open_other_yesterday,
        ];
    }

    usort($branchRows, function($a, $b) {
        return strcmp($a['date'], $b['date']);
    });

    // append to main report
    foreach ($branchRows as $r) {
        $report[] = $r;
    }
}

header('Content-Type: application/json');
echo json_encode($report);
