<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

require_once "auth.php";

/* ================= TODAY STATS ================= */

$today_deposit = $pdo->query("
    SELECT COALESCE(SUM(amount),0)
    FROM deposit_history
    WHERE status='success'
    AND DATE(created_at)=CURDATE()
")->fetchColumn();

$today_sell_amount = $pdo->query("
    SELECT COALESCE(SUM(price),0)
    FROM otp_history
    WHERE status='received'
    AND DATE(created_at)=CURDATE()
")->fetchColumn();

$today_otp = $pdo->query("
    SELECT COUNT(*)
    FROM otp_history
    WHERE status='received'
    AND DATE(created_at)=CURDATE()
")->fetchColumn();

$today_users = $pdo->query("
    SELECT COUNT(*)
    FROM user_data
    WHERE DATE(joined_at)=CURDATE()
")->fetchColumn();

/* ================= LAST 7 DAYS ================= */

$last7_deposit = $pdo->query("
    SELECT COALESCE(SUM(amount),0)
    FROM deposit_history
    WHERE status='success'
    AND DATE(created_at) >= (CURDATE() - INTERVAL 6 DAY)
")->fetchColumn();

$last7_sell_amount = $pdo->query("
    SELECT COALESCE(SUM(price),0)
    FROM otp_history
    WHERE status='received'
    AND DATE(created_at) >= (CURDATE() - INTERVAL 6 DAY)
")->fetchColumn();

$last7_otp = $pdo->query("
    SELECT COUNT(*)
    FROM otp_history
    WHERE status='received'
    AND DATE(created_at) >= (CURDATE() - INTERVAL 6 DAY)
")->fetchColumn();

/* ================= TODAY TOP SERVICES ================= */

$today_top_services = $pdo->query("
    SELECT service_name, COUNT(*) cnt, SUM(price) amount
    FROM otp_history
    WHERE status='received'
    AND DATE(created_at)=CURDATE()
    GROUP BY service_name
    ORDER BY amount DESC
    LIMIT 5
")->fetchAll(PDO::FETCH_ASSOC);

/* ================= WEEK TOP SERVICES ================= */

$week_top_services = $pdo->query("
    SELECT service_name, COUNT(*) cnt, SUM(price) amount
    FROM otp_history
    WHERE status='received'
    AND DATE(created_at) >= (CURDATE() - INTERVAL 6 DAY)
    GROUP BY service_name
    ORDER BY amount DESC
    LIMIT 5
")->fetchAll(PDO::FETCH_ASSOC);

/* ================= TODAY TOP BUYERS ================= */

$today_top_users = $pdo->query("
    SELECT u.user_id, u.name,
           COUNT(o.id) total_orders,
           COALESCE(SUM(o.price),0) total_purchase
    FROM user_data u
    JOIN otp_history o ON o.user_id = u.user_id
    WHERE o.status='received'
    AND DATE(o.created_at)=CURDATE()
    GROUP BY u.user_id
    ORDER BY total_purchase DESC
    LIMIT 10
")->fetchAll(PDO::FETCH_ASSOC);

/* ================= WEEK TOP BUYERS ================= */

$week_top_users = $pdo->query("
    SELECT u.user_id, u.name,
           COUNT(o.id) total_orders,
           COALESCE(SUM(o.price),0) total_purchase
    FROM user_data u
    JOIN otp_history o ON o.user_id = u.user_id
    WHERE o.status='received'
    AND DATE(o.created_at) >= (CURDATE() - INTERVAL 6 DAY)
    GROUP BY u.user_id
    ORDER BY total_purchase DESC
    LIMIT 10
")->fetchAll(PDO::FETCH_ASSOC);

/* ================= LIFETIME TOP BUYERS ================= */

$lifetime_top_users = $pdo->query("
    SELECT u.user_id, u.name,
           COUNT(o.id) total_orders,
           COALESCE(SUM(o.price),0) total_purchase
    FROM user_data u
    JOIN otp_history o ON o.user_id = u.user_id
    WHERE o.status='received'
    GROUP BY u.user_id
    ORDER BY total_purchase DESC
    LIMIT 10
")->fetchAll(PDO::FETCH_ASSOC);

/* ================= LAST 30 DAYS REPORT ================= */

$last30days = $pdo->query("
    SELECT d.date,
        COALESCE(dep.deposit,0) deposit,
        COALESCE(o.sell_amount,0) sell_amount,
        COALESCE(o.otp_count,0) otp_count,
        COALESCE(u.users,0) users
    FROM (
        SELECT CURDATE() - INTERVAL seq DAY as date
        FROM (
            SELECT 0 seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
            SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
            SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
            SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
            SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
            SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL
            SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL
            SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL
            SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL
            SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29
        ) s
    ) d
    LEFT JOIN (
        SELECT DATE(created_at) date, SUM(amount) deposit
        FROM deposit_history
        WHERE status='success'
        GROUP BY DATE(created_at)
    ) dep ON dep.date = d.date
    LEFT JOIN (
        SELECT DATE(created_at) date,
               COUNT(*) otp_count,
               SUM(price) sell_amount
        FROM otp_history
        WHERE status='received'
        GROUP BY DATE(created_at)
    ) o ON o.date = d.date
    LEFT JOIN (
        SELECT DATE(joined_at) date, COUNT(*) users
        FROM user_data
        GROUP BY DATE(joined_at)
    ) u ON u.date = d.date
    ORDER BY d.date DESC
")->fetchAll(PDO::FETCH_ASSOC);
?>

<!DOCTYPE html>
<html>
<head>
<?php include("include/head.php"); ?>
<title>Dashboard</title>

<style>
.card-modern {
    border: none;
    border-radius: 14px;
    box-shadow: 0 10px 30px rgba(0,0,0,0.06);
    transition: .2s;
}
.card-modern:hover { transform: translateY(-3px); }
.stat-label { font-size: 13px; color: #858796; font-weight: 600; }
.stat-value { font-size: 26px; font-weight: 700; }
</style>
</head>

<body id="page-top">
<div id="wrapper">

<?php include("include/slidebar.php"); ?>

<div id="content-wrapper" class="d-flex flex-column">
<div id="content">

<?php include("include/topbar.php"); ?>

<div class="container-fluid">

<!-- TODAY -->
<div class="row">
<div class="col-xl-3 col-md-6 mb-4"><div class="card card-modern p-3"><div class="stat-label">Today Deposit</div><div class="stat-value text-success">₹<?= $today_deposit ?></div></div></div>
<div class="col-xl-3 col-md-6 mb-4"><div class="card card-modern p-3"><div class="stat-label">Today Sell Amount</div><div class="stat-value text-primary">₹<?= $today_sell_amount ?></div></div></div>
<div class="col-xl-3 col-md-6 mb-4"><div class="card card-modern p-3"><div class="stat-label">Today OTP Sale</div><div class="stat-value"><?= $today_otp ?></div></div></div>
<div class="col-xl-3 col-md-6 mb-4"><div class="card card-modern p-3"><div class="stat-label">Today New Users</div><div class="stat-value"><?= $today_users ?></div></div></div>
</div>

<!-- 7 DAYS -->
<div class="row">
<div class="col-xl-4 col-md-6 mb-4"><div class="card card-modern p-3"><div class="stat-label">7 Days Deposit</div><div class="stat-value text-success">₹<?= $last7_deposit ?></div></div></div>
<div class="col-xl-4 col-md-6 mb-4"><div class="card card-modern p-3"><div class="stat-label">7 Days Sell Amount</div><div class="stat-value text-primary">₹<?= $last7_sell_amount ?></div></div></div>
<div class="col-xl-4 col-md-6 mb-4"><div class="card card-modern p-3"><div class="stat-label">7 Days OTP Sale</div><div class="stat-value"><?= $last7_otp ?></div></div></div>
</div>

<!-- TODAY TOP SERVICES -->
<div class="card card-modern mb-4">
<div class="card-header"><h6 class="m-0 font-weight-bold text-info">🔥 Today Top Sell Services</h6></div>
<div class="card-body">
<table class="table table-bordered">
<tr><th>Service</th><th>Sales</th><th>Amount</th></tr>
<?php foreach($today_top_services as $s){ ?>
<tr><td><?= $s['service_name'] ?></td><td><?= $s['cnt'] ?></td><td>₹<?= $s['amount'] ?></td></tr>
<?php } ?>
</table>
</div>
</div>

<!-- WEEK TOP SERVICES -->
<div class="card card-modern mb-4">
<div class="card-header"><h6 class="m-0 font-weight-bold text-secondary">🏆 Week Top Sell Services</h6></div>
<div class="card-body">
<table class="table table-bordered">
<tr><th>Service</th><th>Sales</th><th>Amount</th></tr>
<?php foreach($week_top_services as $s){ ?>
<tr><td><?= $s['service_name'] ?></td><td><?= $s['cnt'] ?></td><td>₹<?= $s['amount'] ?></td></tr>
<?php } ?>
</table>
</div>
</div>

<!-- TODAY TOP BUYERS -->
<div class="card card-modern mb-4">
<div class="card-header"><h6 class="m-0 font-weight-bold text-success">🔥 Today Top Buyers</h6></div>
<div class="card-body">
<table class="table table-bordered">
<tr><th>User ID</th><th>Name</th><th>Orders</th><th>Purchase</th></tr>
<?php foreach($today_top_users as $u){ ?>
<tr><td><?= $u['user_id'] ?></td><td><?= $u['name'] ?></td><td><?= $u['total_orders'] ?></td><td>₹<?= $u['total_purchase'] ?></td></tr>
<?php } ?>
</table>
</div>
</div>

<!-- WEEK TOP BUYERS -->
<div class="card card-modern mb-4">
<div class="card-header"><h6 class="m-0 font-weight-bold text-primary">🏆 Week Top Buyers</h6></div>
<div class="card-body">
<table class="table table-bordered">
<tr><th>User ID</th><th>Name</th><th>Orders</th><th>Purchase</th></tr>
<?php foreach($week_top_users as $u){ ?>
<tr><td><?= $u['user_id'] ?></td><td><?= $u['name'] ?></td><td><?= $u['total_orders'] ?></td><td>₹<?= $u['total_purchase'] ?></td></tr>
<?php } ?>
</table>
</div>
</div>

<!-- LIFETIME TOP BUYERS -->
<div class="card card-modern mb-4">
<div class="card-header"><h6 class="m-0 font-weight-bold text-warning">👑 Lifetime Top Buyers</h6></div>
<div class="card-body">
<table class="table table-bordered">
<tr><th>User ID</th><th>Name</th><th>Orders</th><th>Purchase</th></tr>
<?php foreach($lifetime_top_users as $u){ ?>
<tr><td><?= $u['user_id'] ?></td><td><?= $u['name'] ?></td><td><?= $u['total_orders'] ?></td><td>₹<?= $u['total_purchase'] ?></td></tr>
<?php } ?>
</table>
</div>
</div>

<!-- LAST 30 DAYS -->
<div class="card card-modern mb-4">
<div class="card-header"><h6 class="m-0 font-weight-bold text-danger">📅 Last 30 Days Report</h6></div>
<div class="card-body">
<table class="table table-bordered">
<tr><th>Date</th><th>Recharge</th><th>Sell</th><th>OTP Sold</th><th>New Users</th></tr>
<?php foreach($last30days as $d){ ?>
<tr>
<td><?= $d['date'] ?></td>
<td>₹<?= $d['deposit'] ?></td>
<td>₹<?= $d['sell_amount'] ?></td>
<td><?= $d['otp_count'] ?></td>
<td><?= $d['users'] ?></td>
</tr>
<?php } ?>
</table>
</div>
</div>

</div>
</div>
</div>
</div>

<?php include("include/script.php"); ?>

</body>
</html>