<?php
session_start();
if (!isset($_SESSION['logged_in'])) {
    header('Location: login.php');
    exit;
}

require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../config/helpers.php';

// Check Permission
if (!can('expenses','view')) {
    die('Access Denied');
}

/* ================= Advanced Filters & Search ================= */
$f_search = trim($_GET['f_search'] ?? ''); 
$f_box    = $_GET['cashbox_id'] ?? '';
$f_date_f = $_GET['date_from'] ?? '';
$f_date_t = $_GET['date_to'] ?? '';
$f_status = $_GET['status'] ?? '';

$params = [];
$where  = ["1=1"];

if ($f_search !== '') {
    $where[] = "(ex.expense_name LIKE ? OR et.description LIKE ?)";
    $params[] = "%$f_search%";
    $params[] = "%$f_search%";
}

if ($f_box !== '') { $where[] = "et.box_id = ?"; $params[] = $f_box; }
if ($f_date_f !== '') { $where[] = "et.trans_date >= ?"; $params[] = $f_date_f; }
if ($f_date_t !== '') { $where[] = "et.trans_date <= ?"; $params[] = $f_date_t; }
if ($f_status !== '') { $where[] = "et.status = ?"; $params[] = $f_status; }

$where_sql = "WHERE " . implode(" AND ", $where);

/* ================= 1. Fetch All Transactions (Original) ================= */
$query = "
    SELECT et.*, ex.expense_name, cb.cashbox_name, u.username
    FROM expense_transactions et
    LEFT JOIN expenses ex ON et.expense_id = ex.expense_id
    LEFT JOIN cash_boxes cb ON et.box_id = cb.cashbox_id
    LEFT JOIN users u ON et.user_id = u.user_id
    $where_sql
    ORDER BY et.trans_date DESC, et.trans_id DESC
";
$stmt = $pdo->prepare($query);
$stmt->execute($params);
$transactions = $stmt->fetchAll();

/* ================= 2. Fetch Active Cash Boxes (For Table Columns) ================= */
$boxes = $pdo->query("SELECT cashbox_id, cashbox_name FROM cash_boxes WHERE status = 'active' ORDER BY cashbox_name ASC")->fetchAll();

/* ================= 3. Fetch Matrix Totals (Category + Box) ================= */
$matrix_query = "
    SELECT 
        et.expense_id,
        ex.expense_name,
        et.box_id,
        SUM(CASE WHEN et.amount > 0 AND et.status='active' THEN et.amount ELSE 0 END) as paid,
        SUM(CASE WHEN et.amount < 0 AND et.status='active' THEN ABS(et.amount) ELSE 0 END) as due
    FROM expense_transactions et
    LEFT JOIN expenses ex ON et.expense_id = ex.expense_id
    $where_sql
    GROUP BY et.expense_id, et.box_id
";
$stmt_matrix = $pdo->prepare($matrix_query);
$stmt_matrix->execute($params);
$matrix_data = $stmt_matrix->fetchAll();

// Organize matrix data: [category_id][box_id] = {paid, due}
$category_map = [];
foreach($matrix_data as $row) {
    $category_map[$row['expense_id']]['name'] = $row['expense_name'];
    $category_map[$row['expense_id']]['data'][$row['box_id']] = ['paid' => $row['paid'], 'due' => $row['due']];
}

// Summary Variables
$total_Due = 0;   
$total_paid = 0; 
$total_discount = 0; // متغير جديد لإجمالي الخصومات
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Expense Transactions</title>
    <link href="../assets/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="../assets/font-awesome/css/all.min.css">
    <style>
        body { background:#f8fafc; font-family:'Inter', sans-serif; color: #1e293b; }
        .table-container { background:#fff; border-radius:12px; border:1px solid #e2e8f0; box-shadow: 0 1px 3px rgba(0,0,0,0.1); margin-bottom: 20px;}
        .badge-paid { background:#dcfce7; color:#15803d; padding: 5px 10px; border-radius: 6px; font-size: 11px; font-weight: 600; }
        .badge-due { background:#fef9c3; color:#854d0e; padding: 5px 10px; border-radius: 6px; font-size: 11px; font-weight: 600; }
        .badge-discount { background:#e0f2fe; color:#0369a1; padding: 5px 10px; border-radius: 6px; font-size: 11px; font-weight: 600; }
        .badge-canceled { background:#fee2e2; color:#b91c1c; padding: 5px 10px; border-radius: 6px; font-size: 11px; font-weight: 600; }
        .text-due { color: #ca8a04; } 
        .text-paid { color: #16a34a; }
        .text-discount { color: #0284c7; }
        .text-canceled { color: #dc2626; text-decoration: line-through; }
        .row-canceled { background:#fff1f1; opacity: 0.8; }
        .btn-action { width:32px; height:32px; display:inline-flex; align-items:center; justify-content:center; border-radius:6px; border:1px solid #e2e8f0; background:#fff; color: #64748b; transition: 0.2s; text-decoration: none; }
        .btn-action:hover { background:#f8fafc; border-color:#cbd5e1; }
        .btn-discount-exempt { background: #0284c7 !important; color: #fff !important; border-color: #0284c7 !important; }
        .btn-discount-exempt:hover { background: #0369a1 !important; }
        .filter-card { background: #fff; border-radius: 12px; border: 1px solid #e2e8f0; padding: 20px; margin-bottom: 20px; }
        .summary-card { background: #fff; border-radius: 12px; border: 1px solid #e2e8f0; padding: 20px; margin-top: 20px; }
        .section-title { font-size: 1.1rem; font-weight: bold; margin-bottom: 15px; color: #475569; border-left: 4px solid #3b82f6; padding-left: 10px; margin-top: 30px; }
        
        /* Print Styles */
        @media print {
            .no-print { display: none !important; }
            body { background: #fff; padding: 0; }
            .container-fluid { width: 100% !important; padding: 10px !important; }
            .table-container { box-shadow: none; border: 1px solid #000; }
            .section-title { border-left: 4px solid #000; color: #000; }
            table { font-size: 10pt; }
        }
    </style>
</head>
<body>

<div class="container-fluid py-4 px-4">
    <div class="d-flex justify-content-between align-items-center mb-4">
        <div>
            <h3 class="fw-bold m-0 text-primary"><i class="fas fa-file-invoice-dollar me-2"></i>Expense Transactions</h3>
            <p class="text-muted small mb-0">Management of box expenses (Due/Paid)</p>
        </div>
        <div class="d-flex gap-2 no-print">
            <button onclick="window.print()" class="btn btn-dark px-4 shadow-sm fw-bold"><i class="fas fa-print me-2"></i> Print</button>
            <?php if(can('expenses', 'add')): ?>
                <a href="expense_discount_add.php" class="btn btn-primary px-4 shadow-sm fw-bold"><i class="fas fa-tag me-2"></i> Add (Discount)</a>
                <a href="expense_need_add.php" class="btn btn-warning px-4 shadow-sm fw-bold"><i class="fas fa-plus-circle me-2"></i> Add (Due)</a>
                <a href="expense_add.php" class="btn btn-success px-4 shadow-sm fw-bold"><i class="fas fa-check-circle me-2"></i> Add (Paid)</a>
            <?php endif; ?>
            <a href="expense_types.php" class="btn btn-outline-dark px-3"><i class="fas fa-tags me-2"></i> Categories</a>
        </div>
    </div>

    <div class="filter-card shadow-sm no-print">
        <form method="GET" class="row g-3">
            <div class="col-md-3">
                <label class="form-label small fw-bold">Search</label>
                <input type="text" name="f_search" value="<?= htmlspecialchars($f_search) ?>" class="form-control form-control-sm" placeholder="Search...">
            </div>
            <div class="col-md-2">
                <label class="form-label small fw-bold">Cash Box</label>
                <select name="cashbox_id" class="form-select form-select-sm">
                    <option value="">All Boxes</option>
                    <?php foreach($boxes as $b): ?>
                        <option value="<?= $b['cashbox_id'] ?>" <?= $f_box == $b['cashbox_id'] ? 'selected' : '' ?>><?= htmlspecialchars($b['cashbox_name']) ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-md-2">
                <label class="form-label small fw-bold">From</label>
                <input type="date" name="date_from" value="<?= $f_date_f ?>" class="form-control form-control-sm">
            </div>
            <div class="col-md-2">
                <label class="form-label small fw-bold">To</label>
                <input type="date" name="date_to" value="<?= $f_date_t ?>" class="form-control form-control-sm">
            </div>
            <div class="col-md-1">
                <label class="form-label small fw-bold">Status</label>
                <select name="status" class="form-select form-select-sm">
                    <option value="">All</option>
                    <option value="active" <?= $f_status == 'active' ? 'selected' : '' ?>>Active</option>
                    <option value="canceled" <?= $f_status == 'canceled' ? 'selected' : '' ?>>Canceled</option>
                </select>
            </div>
            <div class="col-md-2 d-flex align-items-end gap-2">
                <button type="submit" class="btn btn-sm btn-dark w-100">Apply</button>
                <a href="expenses.php" class="btn btn-sm btn-light border w-100">Reset</a>
            </div>
        </form>
    </div>

    <div class="section-title">Detailed Transactions</div>
    <div class="table-container shadow-sm overflow-hidden">
        <table class="table table-hover align-middle mb-0">
            <thead class="table-light">
                <tr>
                    <th class="ps-4">Date</th>
                    <th>Expense Name</th>
                    <th>Description</th>
                    <th>Source Box</th>
                    <th>Amount</th>
                    <th>User</th>
                    <th class="text-center">Status</th>
                    <th class="text-center pe-4 no-print">Action</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach($transactions as $t): 
                    $is_active = (strtolower($t['status']) == 'active');
                    $amt = $t['amount'];
                    $row_class = !$is_active ? 'row-canceled' : '';
                    $amt_display = number_format(abs($amt), 2);

                    // فحص إذا كان الوصف يحتوي على كلمة خصم لتمييزه
                    $is_discount = (strpos($t['description'], '[DISCOUNT/DEDUCTION]') !== false);
                    
                    if ($is_active) {
                        if ($is_discount) {
                            $status_label = "Discount"; $status_class = "badge-discount"; $amt_color = "text-discount";
                            $total_discount += abs($amt);
                        } elseif ($amt < 0) {
                            $status_label = "Due"; $status_class = "badge-due"; $amt_color = "text-due";
                            $total_Due += abs($amt);
                        } else {
                            $status_label = "Paid"; $status_class = "badge-paid"; $amt_color = "text-paid";
                            $total_paid += $amt;
                        }
                    } else {
                        $status_label = "Canceled"; $status_class = "badge-canceled"; $amt_color = "text-canceled";
                    }
                ?>
                <tr class="<?= $row_class ?>">
                    <td class="ps-4 small"><?= date('d/m/Y', strtotime($t['trans_date'])) ?></td>
                    <td class="fw-bold"><?= htmlspecialchars($t['expense_name']) ?></td>
                    <td class="small text-muted"><?= htmlspecialchars($t['description']) ?></td>
                    <td><span class="badge bg-light text-dark border"><?= htmlspecialchars($t['cashbox_name']) ?></span></td>
                    <td class="fw-bold <?= $amt_color ?>"><?= $amt_display ?> <small>(<?= $status_label ?>)</small></td>
                    <td class="small"><?= htmlspecialchars($t['username']) ?></td>
                    <td class="text-center"><span class="<?= $status_class ?>"><?= $status_label ?></span></td>
                    <td class="text-center pe-4 no-print">
                        <?php if($is_active): ?>
                            <?php if($is_discount): ?>
                                <a href="expense_discount_delete.php?id=<?= $t['trans_id'] ?>" class="btn-action btn-discount-exempt" title="إعفاء الخصم" onclick="return confirm('هل أنت متأكد من حذف/إعفاء هذا الخصم؟')">
                                    <i class="fas fa-user-check"></i>
                                </a>
                            <?php else: ?>
                                <a href="expense_delete.php?id=<?= $t['trans_id'] ?>" class="btn-action" title="Delete & Void" onclick="return confirm('Delete this transaction?')">
                                    <i class="fas fa-trash-alt text-danger"></i>
                                </a>
                            <?php endif; ?>
                        <?php endif; ?>
                    </td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>

    <div class="section-title">Summary Per Category & Currency (له وعليه مفصل حسب العملة)</div>
    <div class="table-container shadow-sm overflow-hidden">
        <table class="table table-sm table-bordered mb-0 align-middle text-center">
            <thead class="table-dark">
                <tr>
                    <th rowspan="2" class="align-middle ps-3 text-start">Expense Category</th>
                    <?php foreach($boxes as $box): ?>
                        <th colspan="3" class="border-start"><?= htmlspecialchars($box['cashbox_name']) ?></th>
                    <?php endforeach; ?>
                </tr>
                <tr>
                    <?php foreach($boxes as $box): ?>
                        <th style="font-size: 10px;" class="table-warning text-dark">Due (له)</th>
                        <th style="font-size: 10px;" class="table-success text-dark">Paid (عليه)</th>
                        <th style="font-size: 10px;" class="table-secondary text-dark">Balance</th>
                    <?php endforeach; ?>
                </tr>
            </thead>
            <tbody>
                <?php foreach($category_map as $eid => $cat): ?>
                <tr>
                    <td class="ps-3 fw-bold text-start bg-light"><?= htmlspecialchars($cat['name']) ?></td>
                    <?php foreach($boxes as $box): 
                        $b_id = $box['cashbox_id'];
                        $b_due = $cat['data'][$b_id]['due'] ?? 0;
                        $b_paid = $cat['data'][$b_id]['paid'] ?? 0;
                        $b_bal = $b_paid - $b_due;
                    ?>
                        <td class="text-due fw-bold"><?= $b_due > 0 ? number_format($b_due, 2) : '-' ?></td>
                        <td class="text-paid fw-bold"><?= $b_paid > 0 ? number_format($b_paid, 2) : '-' ?></td>
                        <td class="fw-bold <?= $b_bal >= 0 ? 'text-success' : 'text-danger' ?>" style="background: #fdfdfd;">
                            <?= number_format($b_bal, 2) ?>
                        </td>
                    <?php endforeach; ?>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </div>

    <div class="summary-card shadow-sm">
        <div class="row text-center">
            <div class="col-md-3 border-end">
                <p class="text-muted mb-1 small fw-bold">GRAND TOTAL (Due)</p>
                <h4 class="fw-bold text-due"><?= number_format($total_Due, 2) ?></h4>
            </div>
            <div class="col-md-3 border-end">
                <p class="text-muted mb-1 small fw-bold">GRAND TOTAL (Paid)</p>
                <h4 class="fw-bold text-paid"><?= number_format($total_paid, 2) ?></h4>
            </div>
            <div class="col-md-3 border-end">
                <p class="text-muted mb-1 small fw-bold">TOTAL DISCOUNTS</p>
                <h4 class="fw-bold text-discount"><?= number_format($total_discount, 2) ?></h4>
            </div>
            <div class="col-md-3">
                <p class="text-muted mb-1 small fw-bold">NET DIFFERENCE</p>
                <h4 class="fw-bold text-primary"><?= number_format($total_paid - $total_Due - $total_discount, 2) ?></h4>
            </div>
        </div>
    </div>
</div>

</body>
</html>