<?php
session_start();
require_once '../config.php';

if (!isset($_SESSION['user_id']) || ($_SESSION['role'] ?? '') !== 'agency') {
    header('Location: ../login.php');
    exit;
}

$agency_id = (int)$_SESSION['user_id'];

function e($v) {
    return htmlspecialchars((string)($v ?? ''), ENT_QUOTES, 'UTF-8');
}

function money($amt) {
    return '₹' . number_format((float)$amt, 2);
}

function bindParamsDynamic($stmt, $types, $params) {
    $bindNames = [];
    $bindNames[] = $types;

    for ($i = 0; $i < count($params); $i++) {
        $bindNames[] = &$params[$i];
    }

    return call_user_func_array([$stmt, 'bind_param'], $bindNames);
}

function csvSafe($value) {
    return preg_replace("/[\r\n]+/", ' ', (string)($value ?? ''));
}

$q       = trim($_GET['q'] ?? '');
$status  = trim($_GET['payment_status'] ?? 'all');
$from    = trim($_GET['from'] ?? '');
$to      = trim($_GET['to'] ?? '');
$page    = max(1, (int)($_GET['page'] ?? 1));
$limit   = 10;
$offset  = ($page - 1) * $limit;

$quick = trim($_GET['quick'] ?? '');
if ($quick === 'today') {
    $from = date('Y-m-d');
    $to   = date('Y-m-d');
} elseif ($quick === 'month') {
    $from = date('Y-m-01');
    $to   = date('Y-m-d');
}

$scopeSql = "
    b.created_by IN (
        SELECT id FROM users WHERE id = ?
        UNION
        SELECT id FROM users WHERE agency_id = ?
        UNION
        SELECT id FROM users
        WHERE manager_id IN (
            SELECT id FROM users WHERE agency_id = ?
        )
    )
";

$where  = "WHERE $scopeSql";
$params = [$agency_id, $agency_id, $agency_id];
$types  = "iii";

if ($q !== '') {
    $like = "%{$q}%";
    $where .= " AND (
        CAST(b.id AS CHAR) LIKE ?
        OR b.customer_name LIKE ?
        OR b.customer_gst LIKE ?
        OR b.phone LIKE ?
        OR u.name LIKE ?
    )";
    $params[] = $like;
    $params[] = $like;
    $params[] = $like;
    $params[] = $like;
    $params[] = $like;
    $types .= "sssss";
}

if ($status !== '' && $status !== 'all') {
    $where .= " AND b.payment_status = ?";
    $params[] = $status;
    $types .= "s";
}

if ($from !== '') {
    $where .= " AND DATE(b.created_at) >= ?";
    $params[] = $from;
    $types .= "s";
}

if ($to !== '') {
    $where .= " AND DATE(b.created_at) <= ?";
    $params[] = $to;
    $types .= "s";
}

/* EXPORT */
if (isset($_GET['export']) && $_GET['export'] === '1') {
    $exportSql = "
        SELECT
            b.id,
            b.customer_name,
            b.customer_gst,
            b.phone,
            b.total_amount,
            b.paid_amount,
            b.due_amount,
            b.payment_status,
            b.created_at,
            u.name AS created_by_name
        FROM bills b
        LEFT JOIN users u ON b.created_by = u.id
        $where
        ORDER BY b.created_at DESC, b.id DESC
    ";

    $stmt = $conn->prepare($exportSql);
    if ($stmt) {
        bindParamsDynamic($stmt, $types, $params);
        $stmt->execute();
        $res = $stmt->get_result();

        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename=agency_reports_' . date('Ymd_His') . '.csv');

        $out = fopen('php://output', 'w');
        fputcsv($out, ['Bill ID', 'Date', 'Customer', 'GST', 'Phone', 'Created By', 'Total', 'Paid', 'Pending', 'Status']);

        while ($row = $res->fetch_assoc()) {
            fputcsv($out, [
                $row['id'],
                $row['created_at'],
                csvSafe($row['customer_name']),
                csvSafe($row['customer_gst']),
                csvSafe($row['phone']),
                csvSafe($row['created_by_name']),
                $row['total_amount'],
                $row['paid_amount'],
                $row['due_amount'],
                $row['payment_status'],
            ]);
        }

        fclose($out);
        $stmt->close();
    }
    exit;
}

/* STATS */
$statsSql = "
   SELECT
        COUNT(*) AS total_bills,
        COALESCE(SUM(b.total_amount), 0) AS total_sales,
        COALESCE(SUM(CASE 
    WHEN b.paid_amount >= b.total_amount 
    THEN b.paid_amount 
    ELSE 0 
END), 0) AS total_paid,
        COALESCE(SUM(b.due_amount), 0) AS total_pending,
        COALESCE(SUM(CASE WHEN b.paid_amount >= b.total_amount THEN 1 ELSE 0 END), 0) AS paid_bills,
        COALESCE(SUM(CASE WHEN b.payment_status = 'unpaid' THEN 1 ELSE 0 END), 0) AS unpaid_bills,
        COALESCE(SUM(CASE WHEN b.payment_status = 'advance' THEN 1 ELSE 0 END), 0) AS advance_bills
    FROM bills b
    LEFT JOIN users u ON b.created_by = u.id
    $where
";

$statsStmt = $conn->prepare($statsSql);
$stats = [
    'total_bills'   => 0,
    'total_sales'   => 0,
    'total_paid'    => 0,
    'total_pending' => 0,
    'paid_bills'    => 0,
    'unpaid_bills'  => 0,
    'advance_bills' => 0,
];

if ($statsStmt) {
    bindParamsDynamic($statsStmt, $types, $params);
    $statsStmt->execute();
    $statsRes = $statsStmt->get_result();
    $statsRow = $statsRes ? $statsRes->fetch_assoc() : null;
    if ($statsRow) {
        $stats = array_merge($stats, $statsRow);
    }
    $statsStmt->close();
}

/* COUNT FOR PAGINATION */
$countSql = "
    SELECT COUNT(*) AS total
    FROM bills b
    LEFT JOIN users u ON b.created_by = u.id
    $where
";

$countStmt = $conn->prepare($countSql);
$totalRows = 0;

if ($countStmt) {
    bindParamsDynamic($countStmt, $types, $params);
    $countStmt->execute();
    $countRes = $countStmt->get_result();
    $countRow = $countRes ? $countRes->fetch_assoc() : null;
    $totalRows = (int)($countRow['total'] ?? 0);
    $countStmt->close();
}

$totalPages = max(1, (int)ceil($totalRows / $limit));

/* MAIN LIST */
$listSql = "
    SELECT
        b.id,
        b.customer_name,
        b.customer_gst,
        b.phone,
        b.total_amount,
        b.paid_amount,
        b.due_amount,
        b.payment_status,
        b.created_at,
        u.name AS created_by_name
    FROM bills b
    LEFT JOIN users u ON b.created_by = u.id
    $where
    ORDER BY b.created_at DESC, b.id DESC
    LIMIT ? OFFSET ?
";

$listStmt = $conn->prepare($listSql);
$rows = [];

if ($listStmt) {
    $listParams = $params;
    $listParams[] = $limit;
    $listParams[] = $offset;
    $listTypes = $types . "ii";

    bindParamsDynamic($listStmt, $listTypes, $listParams);
    $listStmt->execute();
    $listRes = $listStmt->get_result();

    if ($listRes) {
        while ($r = $listRes->fetch_assoc()) {
            $rows[] = $r;
        }
    }
    $listStmt->close();
}

$dateLabel = 'All Reports';
if ($from !== '' && $to !== '') {
    $dateLabel = 'From ' . date('d M Y', strtotime($from)) . ' to ' . date('d M Y', strtotime($to));
} elseif ($from !== '') {
    $dateLabel = 'From ' . date('d M Y', strtotime($from));
} elseif ($to !== '') {
    $dateLabel = 'Till ' . date('d M Y', strtotime($to));
}
?>

<?php include 'agencysidebar.php'; ?>
<?php include '../header.php'; ?>

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Agency Reports</title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.5.0/css/all.min.css">

<style>
/* ===== ROOT ===== */
.agency-report-page{
    --bg:#eef2f7;
    --card:#ffffff;
    --border:#e2e8f0;
    --text:#0f172a;
    --muted:#64748b;
    --theme:#374155;
    --theme-dark:#2c3445;
    --accent:#2563eb;
    --success:#16a34a;
    --warning:#f59e0b;
    --danger:#dc2626;
    font-family:Inter, Arial, sans-serif;
    color:var(--text);
}

/* ===== RESET ===== */
.agency-report-page,
.agency-report-page *{
    box-sizing:border-box;
}

/* ===== MAIN LAYOUT FIX ===== */
.agency-report-page{
    margin-left:260px;
    width:calc(100% - 260px);
    min-height:100vh;
    background:linear-gradient(180deg,#eef2f7,#e9eef6);
    padding:100px 24px 24px; /* ðŸ”¥ FIX HEADER SPACE */
}

/* ===== CARD ===== */
.report-card{
    background:var(--card);
    border:1px solid var(--border);
    border-radius:20px;
    box-shadow:0 10px 30px rgba(0,0,0,0.06);
    padding:20px;
}

/* ===== HEADER ===== */
.header-row{
    display:flex;
    justify-content:space-between;
    align-items:center;
    gap:12px;
    flex-wrap:wrap;
    margin-bottom:16px;
}

.header-left h1{
    font-size:22px;
    font-weight:800;
    margin:0;
    display:flex;
    align-items:center;
    gap:8px;
    color:var(--theme);
}

.header-left h1 i{
    color:var(--accent);
}

/* ===== BUTTON ===== */
.btn-pill{
    display:flex;
    align-items:center;
    gap:6px;
    padding:10px 14px;
    border-radius:999px;
    border:1px solid var(--border);
    background:#fff;
    font-size:13px;
    font-weight:600;
    text-decoration:none;
    color:var(--theme);
}

.btn-pill:hover{
    background:#f1f5f9;
}

.btn-dark{
    background:var(--theme);
    color:#fff;
    border:none;
}

/* ===== QUICK BUTTONS ===== */
.quick-row{
    display:flex;
    justify-content:flex-end;
    gap:8px;
    flex-wrap:wrap;
    margin-bottom:14px;
}

.quick-chip{
    padding:8px 12px;
    border-radius:999px;
    border:1px solid var(--border);
    font-size:13px;
    text-decoration:none;
    color:var(--theme);
}

/* ===== FILTER ===== */
.filter-box{
    background:#fff;
    border:1px solid var(--border);
    border-radius:16px;
    padding:16px;
    margin-bottom:16px;
}

.filter-grid{
    display:grid;
    grid-template-columns:2fr 1fr 1fr 1fr;
    gap:10px;
}

.filter-item{
    display:flex;
    flex-direction:column;
    gap:5px;
}

.filter-item label{
    font-size:12px;
    font-weight:600;
}

.filter-item input,
.filter-item select{
    height:40px;
    border:1px solid var(--border);
    border-radius:10px;
    padding:0 10px;
    font-size:13px;
}

/* ===== ACTIONS ===== */
.filter-actions{
    display:flex;
    justify-content:flex-end;
    gap:8px;
    margin-top:10px;
}

/* ===== STATS ===== */
.stats-grid{
    display:grid;
    grid-template-columns:repeat(4,1fr);
    gap:12px;
    margin-bottom:16px;
}

.stat-card{
    background:#fff;
    border:1px solid var(--border);
    border-radius:14px;
    padding:14px;
    display:flex;
    justify-content:space-between;
    align-items:center;
}

.stat-label{
    font-size:11px;
    color:var(--muted);
}

.stat-value{
    font-size:20px;
    font-weight:800;
    margin-top:4px;
}

.stat-icon{
    width:42px;
    height:42px;
    border-radius:10px;
    display:flex;
    align-items:center;
    justify-content:center;
    background:#f1f5f9;
}

/* ===== TABLE HEAD ===== */
.table-headline{
    display:flex;
    justify-content:space-between;
    align-items:center;
    margin-bottom:10px;
}

.muted{
    font-size:13px;
    color:var(--muted);
}

/* ===== TABLE FIX ===== */
.table-wrap{
    width:100%;
    overflow-x:auto; /* ðŸ”¥ SCROLL ENABLE */
}

.table{
    width:100%;
    min-width:1000px; /* ðŸ”¥ FORCE SCROLL */
    border-collapse:collapse;
}

/* HEADER */
.table th{
    background:var(--theme);
    color:#fff;
    padding:12px;
    font-size:13px;
    text-align:left;
}

/* BODY */
.table td{
    padding:12px;
    border-bottom:1px solid #f1f5f9;
    font-size:13px;
}

/* HOVER */
.table tr:hover{
    background:#f8fafc;
}

/* STATUS */
.badge{
    padding:5px 10px;
    border-radius:999px;
    font-size:12px;
    font-weight:600;
}

.badge-paid{background:#dcfce7;color:#166534;}
.badge-unpaid{background:#fee2e2;color:#991b1b;}
.badge-advance{background:#fef3c7;color:#92400e;}

/* VIEW BUTTON */
.view-btn{
    width:34px;
    height:34px;
    display:flex;
    align-items:center;
    justify-content:center;
    border-radius:8px;
    background:#eef2f7;
    color:#374155;
}

.view-btn:hover{
    background:#374155;
    color:#fff;
}

/* ===== PAGINATION ===== */
.pagination{
    display:flex;
    justify-content:flex-end;
    gap:6px;
    margin-top:12px;
}

.page-link{
    padding:6px 10px;
    border-radius:6px;
    background:#f1f5f9;
    text-decoration:none;
    font-size:12px;
}

.page-link.active{
    background:#0f172a;
    color:#fff;
}

/* ===== MOBILE ===== */
@media(max-width:768px){
    .agency-report-page{
        margin-left:0;
        width:100%;
        padding:90px 12px 12px;
    }

    .filter-grid{
        grid-template-columns:1fr;
    }

    .stats-grid{
        grid-template-columns:1fr;
    }

    .quick-row,
    .filter-actions,
    .pagination{
        justify-content:stretch;
    }

    .btn-pill,
    .quick-chip{
        width:100%;
        justify-content:center;
    }
}
</style>
</head>
<body>

<div class="agency-report-page">
    <div class="report-card">

        <div class="header-row">
            <div class="header-left">
                <h1><i class="fa-solid fa-chart-column"></i> Agency Reports</h1>
            </div>

            <div class="header-right">
                <a class="btn-pill" href="?export=1<?php echo '&' . http_build_query(array_filter([
                    'q' => $q,
                    'payment_status' => $status !== 'all' ? $status : '',
                    'from' => $from,
                    'to' => $to
                ])); ?>">
                    <i class="fa-solid fa-file-csv"></i> Export CSV
                </a>
            </div>
        </div>

        <div class="quick-row">
            <a class="quick-chip" href="?quick=today"><i class="fa-solid fa-calendar-day"></i> Today</a>
            <a class="quick-chip" href="?quick=month"><i class="fa-solid fa-calendar-days"></i> This Month</a>
            <a class="quick-chip" href="reports.php"><i class="fa-solid fa-rotate"></i> Reset</a>
        </div>

        <div class="filter-box">
            <form method="get">
                <div class="filter-grid">
                    <div class="filter-item">
                        <label>Search</label>
                        <input type="text" name="q" placeholder="Bill no, customer, phone, GST, executive" value="<?php echo e($q); ?>">
                    </div>

                    <div class="filter-item">
                        <label>Status</label>
                        <select name="payment_status">
                            <option value="all" <?php echo $status === 'all' ? 'selected' : ''; ?>>All Status</option>
                            <option value="paid" <?php echo $status === 'paid' ? 'selected' : ''; ?>>Paid</option>
                            <option value="unpaid" <?php echo $status === 'unpaid' ? 'selected' : ''; ?>>Unpaid</option>
                            <option value="advance" <?php echo $status === 'advance' ? 'selected' : ''; ?>>Advance</option>
                        </select>
                    </div>

                    <div class="filter-item">
                        <label>From</label>
                        <input type="date" name="from" value="<?php echo e($from); ?>">
                    </div>

                    <div class="filter-item">
                        <label>To</label>
                        <input type="date" name="to" value="<?php echo e($to); ?>">
                    </div>
                </div>

                <div class="filter-actions">
                    <button type="submit" class="btn-pill btn-dark">
                        <i class="fa-solid fa-filter"></i> Apply Filter
                    </button>
                    <a href="reports.php" class="btn-pill">
                        <i class="fa-solid fa-eraser"></i> Clear
                    </a>
                </div>
            </form>
        </div>

        <div class="stats-grid">
            <div class="stat-card">
                <div>
                    <div class="stat-label">Total Bills</div>
                    <div class="stat-value"><?php echo (int)$stats['total_bills']; ?></div>
                </div>
                <div class="stat-icon"><i class="fa-solid fa-receipt"></i></div>
            </div>

            <div class="stat-card">
                <div>
                    <div class="stat-label">Total Sales</div>
                    <div class="stat-value"><?php echo money($stats['total_sales']); ?></div>
                </div>
                <div class="stat-icon"><i class="fa-solid fa-indian-rupee-sign"></i></div>
            </div>

            <div class="stat-card">
                <div>
                    <div class="stat-label">Total Paid</div>
                    <div class="stat-value"><?php echo money($stats['total_paid']); ?></div>
                </div>
                <div class="stat-icon"><i class="fa-solid fa-circle-check"></i></div>
            </div>

            <div class="stat-card">
                <div>
                    <div class="stat-label">Pending Amount</div>
                    <div class="stat-value"><?php echo money($stats['total_pending']); ?></div>
                </div>
                <div class="stat-icon"><i class="fa-solid fa-clock"></i></div>
            </div>
        </div>


        <div class="table-headline">
            <div>
                <strong> All Bills Reports</strong>
            </div>
            <div class="muted">
                Paid: <?php echo (int)$stats['paid_bills']; ?> |
                Unpaid: <?php echo (int)$stats['unpaid_bills']; ?> |
                Advance: <?php echo (int)$stats['advance_bills']; ?>
            </div>
        </div>

        <div class="table-wrap">
            <table class="table">
                <thead>
                    <tr>
                        <th>Bill ID</th>
                        <th>Date</th>
                        <th>Customer</th>
                        <th>Phone</th>
                        <th>Created By</th>
                        <th>Total</th>
                        <th>Paid</th>
                        <th>Pending</th>
                        <th>Status</th>
                        <th>Action</th>
                    </tr>
                </thead>
                <tbody>
                <?php if (!empty($rows)): ?>
                    <?php foreach ($rows as $r): ?>
                        <tr>
                            <td>#<?php echo (int)$r['id']; ?></td>
                            <td>
                            <?php echo !empty($r['created_at']) ? date('d M Y', strtotime($r['created_at'])) : '-'; ?>
                            </td>                     
                             <td><?php echo e($r['customer_name']); ?></td>
                            <td><?php echo e($r['phone']); ?></td>
                            <td><?php echo e($r['created_by_name'] ?? 'System'); ?></td>
                            <td><?php echo money($r['total_amount']); ?></td>
                            <td><?php echo money($r['paid_amount']); ?></td>
                            <td><?php echo money($r['due_amount']); ?></td>
                            <td>
                                    <?php 
$status = $r['payment_status'];

if (empty($status)) {
    if ($r['paid_amount'] >= $r['total_amount']) {
        $status = 'paid';
    } elseif ($r['paid_amount'] > 0) {
        $status = 'advance';
    } else {
        $status = 'unpaid';
    }
}

$status = strtolower(trim($status));
?>

<span class="badge badge-<?php echo e($status); ?>">
    <?php echo ucfirst($status); ?>
</span>
                                </td>

                                <td>
                                    <a href="invoice_view.php?id=<?php echo (int)$r['id']; ?>" class="view-btn">
                                        <i class="fa-solid fa-eye"></i>
                                    </a>
                                </td>             
                                           </tr>
                    <?php endforeach; ?>
                <?php else: ?>
                    <tr>
                        <td colspan="9">
                            <div class="empty-box">No report data found.</div>
                        </td>
                    </tr>
                <?php endif; ?>
                </tbody>
            </table>
        </div>

        <?php if ($totalPages > 1): ?>
            <div class="pagination">
                <?php
                    $baseParams = [
                        'q' => $q,
                        'payment_status' => $status,
                        'from' => $from,
                        'to' => $to,
                        'quick' => $quick
                    ];
                    foreach ($baseParams as $k => $v) {
                        if ($v === '' || $v === 'all') unset($baseParams[$k]);
                    }
                ?>

                <?php for ($p = 1; $p <= $totalPages; $p++): ?>
                    <?php $pageUrl = '?' . http_build_query(array_merge($baseParams, ['page' => $p])); ?>
                    <a class="page-link <?php echo $p === $page ? 'active' : ''; ?>" href="<?php echo e($pageUrl); ?>">
                        <?php echo $p; ?>
                    </a>
                <?php endfor; ?>
            </div>
        <?php endif; ?>

    </div>
</div>

</body>
</html>