<?php
date_default_timezone_set('Africa/Dar_es_Salaam'); 
include '../../config.php';

// Pata branch_id na user_id kutoka GET
$branch_id = isset($_GET['branch_id']) ? intval($_GET['branch_id']) : 0;
$user_id   = isset($_GET['user_id'])   ? intval($_GET['user_id'])   : 0;
$today     = date("Y-m-d");

// 0. Pata total branches
if ($branch_id > 0) {
    $total_branch = 1;
} else {
    $sql_branch_count = "SELECT COUNT(*) AS total FROM branches WHERE user_id = ?";
    if ($stmt = $conn->prepare($sql_branch_count)) {
        $stmt->bind_param("i", $user_id);
        $stmt->execute();
        $stmt->bind_result($total_branch);
        $stmt->fetch();
        $stmt->close();
    } else {
        $total_branch = 0;
    }
}

// 1. Pata branch interest (hesabu kwa branch husika)
$branch_interest = 0;
if ($branch_id > 0) {
    $sql_branch = "SELECT interest FROM branches WHERE branch_id = ? AND user_id = ?";
    if ($stmt = $conn->prepare($sql_branch)) {
        $stmt->bind_param("ii", $branch_id, $user_id);
        $stmt->execute();
        $stmt->bind_result($branch_interest);
        $stmt->fetch();
        $stmt->close();
    }
}

// 2. Wateja wote (filtered)
$total_customers = 0;
if ($branch_id > 0) {
    $sql = "SELECT COUNT(*) AS total FROM customers WHERE branch_id = ?";
    if ($stmt = $conn->prepare($sql)) {
        $stmt->bind_param("i", $branch_id);
        $stmt->execute();
        $stmt->bind_result($total_customers);
        $stmt->fetch();
        $stmt->close();
    }
} else {
    $sql = "SELECT COUNT(*) AS total 
            FROM customers c 
            INNER JOIN branches b ON c.branch_id = b.branch_id 
            WHERE b.user_id = ?";
    if ($stmt = $conn->prepare($sql)) {
        $stmt->bind_param("i", $user_id);
        $stmt->execute();
        $stmt->bind_result($total_customers);
        $stmt->fetch();
        $stmt->close();
    }
}

// 3. Wateja wa leo
$today_customers = 0;
if ($branch_id > 0) {
    $sql = "SELECT COUNT(*) AS total FROM customers WHERE DATE(created_at) = ? AND branch_id = ?";
    if ($stmt = $conn->prepare($sql)) {
        $stmt->bind_param("si", $today, $branch_id);
        $stmt->execute();
        $stmt->bind_result($today_customers);
        $stmt->fetch();
        $stmt->close();
    }
} else {
    $sql = "SELECT COUNT(*) AS total 
            FROM customers c 
            INNER JOIN branches b ON c.branch_id = b.branch_id 
            WHERE DATE(c.created_at) = ? AND b.user_id = ?";
    if ($stmt = $conn->prepare($sql)) {
        $stmt->bind_param("si", $today, $user_id);
        $stmt->execute();
        $stmt->bind_result($today_customers);
        $stmt->fetch();
        $stmt->close();
    }
}

// 4. Mikopo ya leo
$total_loans_today = 0;
if ($branch_id > 0) {
    $sql = "SELECT SUM(loan_amount) AS total FROM loans WHERE DATE(loan_date) = ? AND branch_id = ?";
    if ($stmt = $conn->prepare($sql)) {
        $stmt->bind_param("si", $today, $branch_id);
        $stmt->execute();
        $stmt->bind_result($total_loans_today);
        $stmt->fetch();
        $stmt->close();
    }
} else {
    $sql = "SELECT SUM(l.loan_amount) AS total 
            FROM loans l 
            INNER JOIN branches b ON l.branch_id = b.branch_id 
            WHERE DATE(l.loan_date) = ? AND b.user_id = ?";
    if ($stmt = $conn->prepare($sql)) {
        $stmt->bind_param("si", $today, $user_id);
        $stmt->execute();
        $stmt->bind_result($total_loans_today);
        $stmt->fetch();
        $stmt->close();
    }
}

// 5. Waliokopa leo
$total_borrowers_today = 0;
if ($branch_id > 0) {
    $sql = "SELECT COUNT(DISTINCT customer_id) AS total FROM loans WHERE DATE(loan_date) = ? AND branch_id = ?";
    if ($stmt = $conn->prepare($sql)) {
        $stmt->bind_param("si", $today, $branch_id);
        $stmt->execute();
        $stmt->bind_result($total_borrowers_today);
        $stmt->fetch();
        $stmt->close();
    }
} else {
    $sql = "SELECT COUNT(DISTINCT l.customer_id) AS total 
            FROM loans l 
            INNER JOIN branches b ON l.branch_id = b.branch_id 
            WHERE DATE(l.loan_date) = ? AND b.user_id = ?";
    if ($stmt = $conn->prepare($sql)) {
        $stmt->bind_param("si", $today, $user_id);
        $stmt->execute();
        $stmt->bind_result($total_borrowers_today);
        $stmt->fetch();
        $stmt->close();
    }
}

// 6. Marejesho ya leo
$total_collection_today = 0;
if ($branch_id > 0) {
    $sql = "SELECT SUM(amount_paid) AS total FROM loans_collection WHERE DATE(payment_date) = ? AND branch_id = ?";
    if ($stmt = $conn->prepare($sql)) {
        $stmt->bind_param("si", $today, $branch_id);
        $stmt->execute();
        $stmt->bind_result($total_collection_today);
        $stmt->fetch();
        $stmt->close();
    }
} else {
    $sql = "SELECT SUM(lc.amount_paid) AS total 
            FROM loans_collection lc 
            INNER JOIN loans l ON lc.loan_id = l.loan_id 
            INNER JOIN branches b ON l.branch_id = b.branch_id 
            WHERE DATE(lc.payment_date) = ? AND b.user_id = ?";
    if ($stmt = $conn->prepare($sql)) {
        $stmt->bind_param("si", $today, $user_id);
        $stmt->execute();
        $stmt->bind_result($total_collection_today);
        $stmt->fetch();
        $stmt->close();
    }
}

// 7. Mikopo yote (pending)
$total_loans = 0;
if ($branch_id > 0) {
    $sql = "SELECT SUM(loan_amount) AS total FROM loans WHERE branch_id = ? AND (status IS NULL OR status != 'Paid')";
    if ($stmt = $conn->prepare($sql)) {
        $stmt->bind_param("i", $branch_id);
        $stmt->execute();
        $stmt->bind_result($total_loans);
        $stmt->fetch();
        $stmt->close();
    }
} else {
    $sql = "SELECT SUM(l.loan_amount) AS total 
            FROM loans l 
            INNER JOIN branches b ON l.branch_id = b.branch_id 
            WHERE (l.status IS NULL OR l.status != 'Paid') AND b.user_id = ?";
    if ($stmt = $conn->prepare($sql)) {
        $stmt->bind_param("i", $user_id);
        $stmt->execute();
        $stmt->bind_result($total_loans);
        $stmt->fetch();
        $stmt->close();
    }
}

// 8. Pesa mtaani (remaining)
$total_remaining = 0;
if ($branch_id > 0) {
    $sql = "SELECT l.loan_amount, l.loan_period, l.interest, COALESCE(SUM(lc.amount_paid),0) AS paid 
            FROM loans l
            LEFT JOIN loans_collection lc ON l.loan_id = lc.loan_id
            INNER JOIN branches b ON l.branch_id = b.branch_id
            WHERE l.branch_id = ?
            GROUP BY l.loan_id";
    if ($stmt = $conn->prepare($sql)) {
        $stmt->bind_param("i", $branch_id);
        $stmt->execute();
        $result_total = $stmt->get_result();
        while ($row = $result_total->fetch_assoc()){
            $calculated_interest = $row['loan_amount'] * $row['interest'] * $row['loan_period'];
            $total_due = $row['loan_amount'] + $calculated_interest;
            $remaining = $total_due - $row['paid'];
            $total_remaining += $remaining;
        }
        $stmt->close();
    }
} else {
    $sql = "SELECT l.loan_amount, l.loan_period, l.interest, COALESCE(SUM(lc.amount_paid),0) AS paid 
            FROM loans l
            LEFT JOIN loans_collection lc ON l.loan_id = lc.loan_id
            INNER JOIN branches b ON l.branch_id = b.branch_id
            WHERE b.user_id = ?
            GROUP BY l.loan_id";
    if ($stmt = $conn->prepare($sql)) {
        $stmt->bind_param("i", $user_id);
        $stmt->execute();
        $result_total = $stmt->get_result();
        while ($row = $result_total->fetch_assoc()){
            $calculated_interest = $row['loan_amount'] * $row['interest'] * $row['loan_period'];
            $total_due = $row['loan_amount'] + $calculated_interest;
            $remaining = $total_due - $row['paid'];
            $total_remaining += $remaining;
        }
        $stmt->close();
    }
}

// 9. Waliorejesha leo
$total_customers_today_collection = 0;
if ($branch_id > 0) {
    $sql = "SELECT COUNT(DISTINCT customer_id) AS total_customers 
            FROM loans_collection 
            WHERE DATE(payment_date) = ? AND branch_id = ?";
    if ($stmt = $conn->prepare($sql)) {
        $stmt->bind_param("si", $today, $branch_id);
        $stmt->execute();
        $stmt->bind_result($total_customers_today_collection);
        $stmt->fetch();
        $stmt->close();
    }
} else {
    $sql = "SELECT COUNT(DISTINCT l.customer_id) AS total_customers 
            FROM loans_collection lc 
            INNER JOIN loans l ON lc.loan_id = l.loan_id 
            INNER JOIN branches b ON l.branch_id = b.branch_id 
            WHERE DATE(lc.payment_date) = ? AND b.user_id = ?";
    if ($stmt = $conn->prepare($sql)) {
        $stmt->bind_param("si", $today, $user_id);
        $stmt->execute();
        $stmt->bind_result($total_customers_today_collection);
        $stmt->fetch();
        $stmt->close();
    }
}

//10. Active customers
$active_customers = 0;
if ($branch_id > 0) {
    $sql = "
      SELECT COUNT(DISTINCT customer_id) AS total_active
      FROM loans
      WHERE branch_id = ?
        AND status IS NULL
    ";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $branch_id);
} else {
    $sql = "
      SELECT COUNT(DISTINCT l.customer_id) AS total_active
      FROM loans l
      INNER JOIN branches b ON l.branch_id = b.branch_id
      WHERE b.user_id = ?
        AND l.status IS NULL
    ";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $user_id);
}
$stmt->execute();
$stmt->bind_result($active_customers);
$stmt->fetch();
$stmt->close();


// 11. Non active customers
$non_active_customers = 0;
if ($branch_id > 0) {
    $sql = "
        SELECT COUNT(DISTINCT c.id) AS total_non_active
        FROM customers c
        LEFT JOIN loans l ON c.id = l.customer_id AND l.status IS NULL
        WHERE c.branch_id = ? AND l.loan_id IS NULL
    ";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $branch_id);
} else {
    $sql = "
        SELECT COUNT(DISTINCT c.id) AS total_non_active
        FROM customers c
        INNER JOIN branches b ON c.branch_id = b.branch_id
        LEFT JOIN loans l ON c.id = l.customer_id AND l.status IS NULL
        WHERE b.user_id = ? AND l.loan_id IS NULL
    ";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $user_id);
}

$stmt->execute();
$stmt->bind_result($non_active_customers);
$stmt->fetch();
$stmt->close();



/**
 * allocatePayment() inagawanya malipo ya mkopo kati ya faida (interest) na mtaji (principal)
 */
function allocatePayment($loan_amount, $interest, $payment, $loan_period) {
    $total_interest        = $loan_amount * $interest * $loan_period;
    $total_due             = $loan_amount + $total_interest;
    $scheduled_installment = $total_due / $loan_period;
    $scheduled_interest    = $total_interest / $loan_period;
    $scheduled_principal   = $loan_amount / $loan_period;

    $profit_ratio    = $scheduled_interest / $scheduled_installment;
    $principal_ratio = $scheduled_principal / $scheduled_installment;

    return [
        'profit_paid'    => $payment * $profit_ratio,
        'principal_paid' => $payment * $principal_ratio,
    ];
}

// 12. Faida na mtaji viperiode
$total_profit_today      = 0;
$total_principal_today   = 0;
$total_profit_weekly     = 0;
$total_principal_weekly  = 0;
$total_profit_monthly    = 0;
$total_principal_monthly = 0;

$month_start = date("Y-m-01");
$month_end   = date("Y-m-t");
$week_start  = date('Y-m-d', strtotime('monday this week'));
$week_end    = date('Y-m-d', strtotime('sunday this week'));

// 12a. Chagua mikopo yote (kituo kimoja au zote kwa user)
if ($branch_id > 0) {
    $sql_loans = "
      SELECT l.loan_id, l.loan_amount, l.loan_period, l.interest
      FROM loans l
      JOIN branches b ON l.branch_id = b.branch_id
      WHERE l.branch_id = ? AND b.user_id = ?
    ";
    $stmt = $conn->prepare($sql_loans);
    $stmt->bind_param("ii", $branch_id, $user_id);
} else {
    $sql_loans = "
      SELECT l.loan_id, l.loan_amount, l.loan_period, l.interest
      FROM loans l
      JOIN branches b ON l.branch_id = b.branch_id
      WHERE b.user_id = ?
    ";
    $stmt = $conn->prepare($sql_loans);
    $stmt->bind_param("i", $user_id);
}
$stmt->execute();
$loans = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();

// 12b. Pata malipo na ugawanye interest vs principal kwa kila loan na kipindi
foreach ($loans as $loan) {
    $loan_id     = (int)$loan['loan_id'];
    $loan_amount = (float)$loan['loan_amount'];
    $loan_period = max(1, (int)$loan['loan_period']);
    $interest    = (float)$loan['interest'];

    // fetch all repayments for this loan
    $sql_reps = "SELECT amount_paid, DATE(payment_date) AS pay_date FROM loans_collection WHERE loan_id = ?";
    $stmt2 = $conn->prepare($sql_reps);
    $stmt2->bind_param("i", $loan_id);
    $stmt2->execute();
    $reps = $stmt2->get_result()->fetch_all(MYSQLI_ASSOC);
    $stmt2->close();

    foreach ($reps as $r) {
        $paid    = (float)$r['amount_paid'];
        $payDate = $r['pay_date'];

        $alloc   = allocatePayment($loan_amount, $interest, $paid, $loan_period);

        // sio tu siku moja, check kwa siku, wiki na mwezi
        if ($payDate === $today) {
            $total_principal_today += $alloc['principal_paid'];
            $total_profit_today    += $alloc['profit_paid'];
        }
        if ($payDate >= $week_start && $payDate <= $week_end) {
            $total_principal_weekly += $alloc['principal_paid'];
            $total_profit_weekly    += $alloc['profit_paid'];
        }
        if ($payDate >= $month_start && $payDate <= $month_end) {
            $total_principal_monthly += $alloc['principal_paid'];
            $total_profit_monthly    += $alloc['profit_paid'];
        }
    }
}

// Tengeneza response
$response = [
    "total_branch"                      => $total_branch,
    "branch_interest"                   => $branch_interest,
    "total_customers"                   => $total_customers,
    "today_customers"                   => $today_customers,
    "total_loans_today"                 => $total_loans_today,
    "total_borrowers_today"             => $total_borrowers_today,
    "total_collection_today"            => $total_collection_today,
    "total_loans"                       => $total_loans,
    "total_remaining"                   => $total_remaining,
    "total_customers_today_collection"  => $total_customers_today_collection,
    "active_customers"                  => $active_customers,
    "non_active_customers"              => $non_active_customers,

    // mpya: faida & mtaji viperiode
    "total_profit_today"                => round($total_profit_today, 2),
    "total_principal_today"             => round($total_principal_today, 2),
    "total_profit_weekly"               => round($total_profit_weekly, 2),
    "total_principal_weekly"            => round($total_principal_weekly, 2),
    "total_profit_monthly"              => round($total_profit_monthly, 2),
    "total_principal_monthly"           => round($total_principal_monthly, 2),
];

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