<?php
require_once 'functions.php';
checkLogin();
$conn = connect();

// --- 1. SMART ID DETECTION ---
$map = [
    'FOLIO' => 0, 'KAROMU' => 0, 'FINE' => 0, 
    'INTEREST' => 0, 'MOMO' => 0, 'PRINCIPAL' => 0,
    'AIRTEL' => 0, 'TRANSFER' => 0
];

$q_ids = mysqli_query($conn, "SELECT inctype_id, inctype_type FROM inctype");
while($row = mysqli_fetch_assoc($q_ids)) {
    $name = strtoupper($row['inctype_type']);
    $id = $row['inctype_id'];
    
    if (strpos($name, 'FOLIO') !== false || strpos($name, 'LOAN FEE') !== false) $map['FOLIO'] = $id;
    elseif (strpos($name, 'KAROMU') !== false) $map['KAROMU'] = $id;
    elseif (strpos($name, 'FINE') !== false) $map['FINE'] = $id;
    elseif (strpos($name, 'INTEREST') !== false) $map['INTEREST'] = $id;
    elseif (strpos($name, 'MOMO') !== false) $map['MOMO'] = $id;
    elseif (strpos($name, 'PRINCIPAL') !== false || strpos($name, 'PRINCIPLE') !== false) $map['PRINCIPAL'] = $id;
    elseif (strpos($name, 'AIRTEL') !== false) $map['AIRTEL'] = $id;
    elseif (strpos($name, 'TRANSFER') !== false) $map['TRANSFER'] = $id;
}

$id_f = $map['FOLIO'] ?: 3;      
$id_k = $map['KAROMU'] ?: 9;     
$id_fn = $map['FINE'] ?: 5;      
$id_i = $map['INTEREST'] ?: 4;   
$id_m = $map['MOMO'] ?: 7;       
$id_p = $map['PRINCIPAL'] ?: 12; 
$id_air = $map['AIRTEL'] ?: 991; 
$id_trans = $map['TRANSFER'] ?: 992;

// --- 2. NAVIGATION ---
$view_month = isset($_GET['month']) ? (int)$_GET['month'] : (int)date('m');
$view_year  = isset($_GET['year'])  ? (int)$_GET['year']  : (int)date('Y');

$start_ts = mktime(0, 0, 0, $view_month, 1, $view_year);
$end_ts   = mktime(23, 59, 59, $view_month + 1, 0, $view_year);

$prev_month = $view_month == 1 ? 12 : $view_month - 1;
$prev_year_nav = $view_month == 1 ? $view_year - 1 : $view_year;
$next_month = $view_month == 12 ? 1 : $view_month + 1;
$next_year_nav = $view_month == 12 ? $view_year + 1 : $view_year;
$month_label = date("F Y", $start_ts);

$view = isset($_GET['view']) ? $_GET['view'] : 'cashins'; 

// --- 3. FETCH DATA ---
$clients = [];
$q = mysqli_query($conn, "SELECT cust_id, cust_name, cust_no FROM customer ORDER BY cust_name");
while($r = mysqli_fetch_assoc($q)) $clients[] = $r;

// Income Types Logic
$hidden_ids = [$id_i, $id_fn, $id_p, 11]; 

$inc_types = [];
$q = mysqli_query($conn, "SELECT inctype_id, inctype_type FROM inctype ORDER BY inctype_type");
while($r = mysqli_fetch_assoc($q)) {
    if(in_array($r['inctype_id'], $hidden_ids)) continue;
    if(strpos(strtoupper($r['inctype_type']), 'OTHER INCOME') !== false) continue;
    $inc_types[] = $r;
}

$exp_types = [];
$q = mysqli_query($conn, "SELECT exptype_id, exptype_type FROM exptype ORDER BY exptype_type");
while($r = mysqli_fetch_assoc($q)) $exp_types[] = $r;
?>
<!DOCTYPE html>
<html>
<head>
    <title>Finance - <?php echo $month_label; ?></title>
    <link rel="stylesheet" href="css/mangoo.css" />
    <link rel="stylesheet" href="ico/font-awesome/css/font-awesome.min.css">
    <script src="jquery/jquery-2.2.1.min.js"></script>
    
    <style>
        body { background-color: #E3E4CD !important; font-family: 'Segoe UI', Tahoma, sans-serif; margin: 0; }
        .sub-nav { background: #333; height: 50px; display: flex; justify-content: space-between; align-items: center; padding: 0 20px; border-bottom: 3px solid #555; color: #ddd; }
        .nav-links a { color: #ddd; text-decoration: none; padding: 0 15px; font-weight: bold; font-size: 13px; border-right: 1px dotted #666; }
        .nav-links a:hover { color: #fff; }
        .nav-links a.active { color: #53C3F2; }
        .month-selector { display: flex; align-items: center; gap: 10px; font-size: 13px; font-weight: bold; }
        .month-selector a { color: #FFA500; text-decoration: none; font-size: 14px; }
        .year-select { background: #444; color: white; border: 1px solid #666; padding: 3px; font-weight: bold; cursor: pointer; }
        .container { display: flex; padding: 20px; gap: 20px; width: 98%; margin: 0 auto; }
        .form-panel { width: 350px; flex-shrink: 0; }
        .table-panel { flex-grow: 1; }
        h2.orange-title { color: #E95D0F; font-size: 18px; font-weight: bold; margin: 0 0 20px 0; text-align: center; }
        .form-row { margin-bottom: 15px; display: flex; align-items: center; }
        .lbl { width: 110px; text-align: left; color: #000; font-size: 13px; font-weight: 600; }
        .inp { flex: 1; }
        input[type="text"], input[type="date"], input[type="number"], select { width: 100%; padding: 8px; border: 1px solid #888; font-size: 14px; border-radius: 3px; height: 38px; box-sizing: border-box; }
        .btn-grey { background: #666; color: white; border: none; padding: 10px 25px; cursor: pointer; font-size: 13px; border-radius: 4px; margin-left: 110px; font-weight: bold; }
        .btn-grey:hover { background: #444; }
        .ledger-table { width: 100%; border-collapse: collapse; background: white; font-family: Arial, sans-serif; table-layout: auto; }
        .ledger-table thead tr { background: #E95D0F; color: white; height: 30px; }
        .ledger-table th { padding: 4px; border: 1px solid #c44d0c; font-size: 11px; text-align: center; font-weight: bold; white-space: nowrap; }
        .ledger-table td { padding: 4px; border: 1px solid #888; text-align: center; color: #000; font-size: 11px; }
        .ledger-table tr:nth-child(even) { background-color: #f0f0f0; }
        .del-x { color: black; font-weight: bold; text-decoration: none; font-size: 14px; }
        .del-x:hover { color: red; }
        .col-total { font-weight: bold; } 
    </style>

    <script>
        $(document).ready(function(){
            function toggleFields() {
                var selectedText = $("#income_type option:selected").text().toLowerCase();
                if(selectedText.indexOf("folio") >= 0 || selectedText.indexOf("loan fee") >= 0) {
                    $("#row_received_from").show();
                } else {
                    $("#row_received_from").hide();
                    $("select[name='entity']").val('0'); 
                }
            }
            $("#income_type").change(toggleFields);
            toggleFields();
        });
        function changeYear(y){
            var url = new URL(window.location.href);
            url.searchParams.set('year', y);
            window.location.href = url.toString();
        }
    </script>
</head>
<body>

    <?php includeMenu(4); ?>

    <div class="sub-nav">
        <div class="nav-links">
            <a href="start.php">Back</a>
            <a href="finance.php?view=cashouts&month=<?php echo $view_month; ?>&year=<?php echo $view_year; ?>" class="<?php echo $view=='cashouts'?'active':''; ?>">Cashouts</a>
            <a href="finance.php?view=cashins&month=<?php echo $view_month; ?>&year=<?php echo $view_year; ?>" class="<?php echo $view=='cashins'?'active':''; ?>">Cashins</a>
        </div>
        <div class="month-selector">
            <span>Year:</span>
            <select class="year-select" onchange="changeYear(this.value)">
                <?php for($y=2024; $y<=2030; $y++){ $sel = ($y==$view_year)?'selected':''; echo "<option $sel value='$y'>$y</option>"; } ?>
            </select>
            <span style="border-left: 1px solid #777; margin: 0 5px; height: 15px;"></span>
            <a href="finance.php?view=<?php echo $view; ?>&month=<?php echo $prev_month; ?>&year=<?php echo $prev_year_nav; ?>"><i class="fa fa-arrow-left"></i> Prev</a>
            <span style="text-transform:uppercase; min-width:110px; text-align:center; display:inline-block;"><?php echo $month_label; ?></span>
            <a href="finance.php?view=<?php echo $view; ?>&month=<?php echo $next_month; ?>&year=<?php echo $next_year_nav; ?>">Next <i class="fa fa-arrow-right"></i></a>
        </div>
    </div>

    <div class="container">
        <div class="form-panel">
            <?php if($view == 'cashins'): ?>
                <h2 class="orange-title">New cashins</h2>
                <form action="save_finance.php" method="POST">
                    <input type="hidden" name="transaction_type" value="DR">
                    <div class="form-row"><div class="lbl">Date:</div><div class="inp"><input type="text" name="date" value="<?php echo date('d.m.Y'); ?>"></div></div>
                    <div class="form-row"><div class="lbl">Amount:</div><div class="inp"><input type="text" name="amount" placeholder="/=" required></div></div>
                    
                    <div class="form-row"><div class="lbl">Type:</div><div class="inp"><select name="type_id" id="income_type" required><?php foreach($inc_types as $t) echo "<option value='{$t['inctype_id']}'>{$t['inctype_type']}</option>"; ?></select></div></div>
                    
                    <div class="form-row" id="row_received_from" style="display:none;">
                        <div class="lbl">Received from:</div>
                        <div class="inp">
                            <select name="entity">
                                <option value="0">N/A</option>
                                <?php foreach($clients as $c) echo "<option value='customer_{$c['cust_id']}'>{$c['cust_name']} ({$c['cust_no']})</option>"; ?>
                            </select>
                        </div>
                    </div>

                    <div class="form-row"><div class="lbl">Receipt No:</div><div class="inp"><input type="text" name="ref_no"></div></div>
                    <div class="form-row"><div class="lbl">Loan:</div><div class="inp"><select disabled><option>N/A (Use Loan Page)</option></select></div></div>
                    <div class="form-row"><div class="lbl">Details:</div><div class="inp"><input type="text" name="details"></div></div>
                    <button type="submit" class="btn-grey">New Entry</button>
                </form>
            <?php else: ?>
                <h2 class="orange-title">New cashout</h2>
                <form action="save_finance.php" method="POST">
                    <input type="hidden" name="transaction_type" value="CR">
                    <div class="form-row"><div class="lbl">Date:</div><div class="inp"><input type="text" name="date" value="<?php echo date('d.m.Y'); ?>"></div></div>
                    <div class="form-row"><div class="lbl">Amount:</div><div class="inp"><input type="text" name="amount" placeholder="/=" required></div></div>
                    <div class="form-row"><div class="lbl">Type:</div><div class="inp"><select name="type_id" required><?php foreach($exp_types as $t) echo "<option value='{$t['exptype_id']}'>{$t['exptype_type']}</option>"; ?></select></div></div>
                    <div class="form-row"><div class="lbl">Prepared by:</div><div class="inp"><input type="text" name="prepared_by" value="<?php echo $_SESSION['log_user']; ?>"></div></div>
                    <div class="form-row"><div class="lbl">Voucher No:</div><div class="inp"><input type="text" name="ref_no"></div></div>
                    <div class="form-row"><div class="lbl">Receipt No:</div><div class="inp"><input type="text" name="receipt_no"></div></div>
                    <div class="form-row"><div class="lbl">Details:</div><div class="inp"><input type="text" name="details"></div></div>
                    <button type="submit" class="btn-grey">New Entry</button>
                </form>
            <?php endif; ?>
        </div>

        <div class="table-panel">
            <?php if($view == 'cashins'): ?>
                <div style="background:#333; color:white; padding:8px; text-align:center; font-weight:bold; font-size:14px;">Recent cashins (<?php echo $month_label; ?>)</div>
                <table class="ledger-table">
                    <thead>
                        <tr>
                            <th>Date</th><th>Folio</th><th>Karomu<br>A/C</th><th>Loan<br>Fine</th><th>Loan<br>Interest</th>
                            <th>MOMO<br>A/C</th><th>Airtel<br>A/C</th><th>Transfer<br>Fee</th><th>Principle</th><th class="col-total">Total</th>
                            <th>From</th><th>Mode</th><th>Details</th><th>Del</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php
                        // Updated Query: Groups ALL Loan Transactions (Fine + Int + Principal) together
                        $sql = "SELECT 
                                    IF(i.ltrans_id > 0, CONCAT('L-', i.ltrans_id), CONCAT('I-', i.inc_id)) as group_key,
                                    MAX(i.inc_date) as inc_date,
                                    MAX(i.inc_receipt) as inc_receipt,
                                    MAX(i.inc_text) as inc_text,
                                    MAX(c.cust_name) as cust_name,
                                    MAX(c.cust_no) as cust_no,
                                    MAX(g.group_name) as group_name,
                                    MAX(i.inc_id) as main_id,
                                    MAX(i.cust_id) as cust_id,
                                    MAX(i.inctype_id) as last_type,
                                    SUM(CASE WHEN i.inctype_id = $id_f THEN i.inc_amount ELSE 0 END) as val_folio,
                                    SUM(CASE WHEN i.inctype_id = $id_k THEN i.inc_amount ELSE 0 END) as val_karomu,
                                    SUM(CASE WHEN i.inctype_id = $id_fn THEN i.inc_amount ELSE 0 END) as val_fine,
                                    SUM(CASE WHEN i.inctype_id = $id_i THEN i.inc_amount ELSE 0 END) as val_interest,
                                    SUM(CASE WHEN i.inctype_id = $id_m THEN i.inc_amount ELSE 0 END) as val_momo,
                                    SUM(CASE WHEN i.inctype_id = $id_air THEN i.inc_amount ELSE 0 END) as val_airtel,
                                    SUM(CASE WHEN i.inctype_id = $id_trans THEN i.inc_amount ELSE 0 END) as val_transfer,
                                    SUM(CASE WHEN i.inctype_id = $id_p THEN i.inc_amount ELSE 0 END) as val_principal,
                                    SUM(i.inc_amount) as total_amount
                                FROM incomes i 
                                LEFT JOIN customer c ON i.cust_id = c.cust_id
                                LEFT JOIN `group` g ON i.group_id = g.group_id
                                WHERE i.inc_date BETWEEN $start_ts AND $end_ts
                                GROUP BY group_key
                                ORDER BY inc_date DESC";
                        
                        $res = mysqli_query($conn, $sql);
                        if($res):
                        while($row = mysqli_fetch_assoc($res)){
                            $empty = "";
                            $from = $row['cust_id'] ? $row['cust_name']."<br>".$row['cust_no'] : ($row['group_name'] ? $row['group_name'] : "");
                            
                            $folio = $row['val_folio'] > 0 ? number_format($row['val_folio'])."/=" : "";
                            $karomu = $row['val_karomu'] > 0 ? number_format($row['val_karomu'])."/=" : "";
                            $fine = $row['val_fine'] > 0 ? number_format($row['val_fine'])."/=" : "";
                            $interest = $row['val_interest'] > 0 ? number_format($row['val_interest'])."/=" : "";
                            $momo = $row['val_momo'] > 0 ? number_format($row['val_momo'])."/=" : "";
                            $airtel = $row['val_airtel'] > 0 ? number_format($row['val_airtel'])."/=" : "";
                            $transfer = $row['val_transfer'] > 0 ? number_format($row['val_transfer'])."/=" : "";
                            $principal = $row['val_principal'] > 0 ? number_format($row['val_principal'])."/=" : "";
                            
                            $total = number_format($row['total_amount'])."/=";
                            
                            $tid = $row['last_type'];
                            $mode = ($tid == $id_m || strpos(strtoupper($row['inc_receipt']), 'MOMO') !== false) ? "MOMO" : "CASH";

                            echo "<tr>
                                    <td>".date('d.m.Y', $row['inc_date'])."</td>
                                    <td>$folio</td><td>$karomu</td><td>$fine</td><td>$interest</td>
                                    <td>$momo</td><td>$airtel</td><td>$transfer</td><td>$principal</td>
                                    <td class='col-total'>$total</td>
                                    <td style='font-size:10px;'>$from</td><td>$mode</td><td>{$row['inc_text']}</td>
                                    <td><a href='books_income_del.php?inc_id={$row['main_id']}' class='del-x' onclick='return confirm(\"Delete this entry?\")'>✖</a></td>
                                  </tr>";
                        }
                        endif;
                        ?>
                    </tbody>
                </table>
            <?php else: ?>
                <div style="background:#333; color:white; padding:8px; text-align:center; font-weight:bold; font-size:14px;">Recent cashouts (<?php echo $month_label; ?>)</div>
                <table class="ledger-table">
                    <thead>
                        <tr><th>Date</th><th>BANKED</th><th>LOAN</th><th>M/C DEP<br>EXP</th><th>M/C LOAN</th><th>MOMO<br>PAY</th><th>AIRTEL<br>PAY</th><th>OFFICE<br>EXP</th><th>FOLIO<br>EXP</th><th class="col-total">Total</th><th>Prepared by</th><th>Details</th><th>Voucher</th><th>Del</th></tr>
                    </thead>
                    <tbody>
                        <?php
                        $sql = "SELECT e.*, et.exptype_type, c.cust_name FROM expenses e LEFT JOIN exptype et ON e.exptype_id = et.exptype_id LEFT JOIN customer c ON e.cust_id = c.cust_id WHERE e.exp_date BETWEEN $start_ts AND $end_ts ORDER BY e.exp_date DESC";
                        $res = mysqli_query($conn, $sql);
                        while($row = mysqli_fetch_assoc($res)){
                            $amt = number_format($row['exp_amount']) . " /="; $empty = ""; $tn = strtoupper($row['exptype_type']);
                            echo "<tr>
                                <td>".date('d.m.Y', $row['exp_date'])."</td>
                                <td>".(strpos($tn, 'BANKED') !== false ? $amt : $empty)."</td>
                                <td>".(strpos($tn, 'LOAN') !== false && strpos($tn, 'M/C') === false ? $amt : $empty)."</td>
                                <td>".(strpos($tn, 'M/C DEP') !== false ? $amt : $empty)."</td>
                                <td>".(strpos($tn, 'M/C LOAN') !== false ? $amt : $empty)."</td>
                                <td>".(strpos($tn, 'MOMO') !== false ? $amt : $empty)."</td>
                                <td>".(strpos($tn, 'AIRTEL') !== false ? $amt : $empty)."</td>
                                <td>".(strpos($tn, 'OFFICE') !== false ? $amt : $empty)."</td>
                                <td>".(strpos($tn, 'FOLIO') !== false ? $amt : $empty)."</td>
                                <td class='col-total'>$amt</td>
                                <td>".($_SESSION['log_user'])."</td><td>{$row['exp_text']}</td><td>{$row['exp_voucher']}</td>
                                <td><a href='books_expense_del.php?exp_id={$row['exp_id']}' class='del-x' onclick='return confirm(\"Delete?\")'>✖</a></td>
                            </tr>";
                        }
                        ?>
                    </tbody>
                </table>
            <?php endif; ?>
        </div>
    </div>
</body>
</html>