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

require 'db.php';

/*
|--------------------------------------------------------------------------
| OPTIONAL AUTH CHECK
|--------------------------------------------------------------------------
| Uncomment and adjust if this page should be admin-only.
|--------------------------------------------------------------------------
*/
// if (!isset($_SESSION['admin'])) {
//     header("Location: login.php");
//     exit;
// }

function tableExists(mysqli $conn, string $tableName): bool
{
    $sql = "SELECT 1 
            FROM information_schema.tables 
            WHERE table_schema = DATABASE() 
              AND table_name = ?
            LIMIT 1";

    $stmt = $conn->prepare($sql);
    $stmt->bind_param("s", $tableName);
    $stmt->execute();
    $stmt->store_result();
    $exists = $stmt->num_rows > 0;
    $stmt->close();

    return $exists;
}

function firstExistingTable(mysqli $conn, array $candidates): ?string
{
    foreach ($candidates as $table) {
        if (tableExists($conn, $table)) {
            return $table;
        }
    }
    return null;
}

function naira($amount): string
{
    return '₦' . number_format((float)$amount, 2);
}

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

function maskEmail(string $email): string
{
    if ($email === '' || strpos($email, '@') === false) {
        return 'N/A';
    }

    [$name, $domain] = explode('@', $email, 2);

    if (strlen($name) <= 2) {
        $masked = substr($name, 0, 1) . '*';
    } else {
        $masked = substr($name, 0, 2) . str_repeat('*', max(1, strlen($name) - 2));
    }

    return $masked . '@' . $domain;
}

function maskValue($value, string $type = 'text'): string
{
    $value = trim((string)$value);

    if ($value === '') {
        return 'N/A';
    }

    if (in_array($type, ['NIN', 'BVN', 'PHONE'], true)) {
        $digits = preg_replace('/\D+/', '', $value);

        if ($digits === '') {
            return 'N/A';
        }

        $len = strlen($digits);

        if ($len <= 4) {
            return str_repeat('*', $len);
        }

        return str_repeat('*', $len - 4) . substr($digits, -4);
    }

    if (strlen($value) <= 8) {
        return $value;
    }

    return substr($value, 0, 4) . '...' . substr($value, -4);
}

function statusClass(string $status): string
{
    $s = strtolower(trim($status));

    if (in_array($s, ['success', 'successful', 'completed', 'approved'], true)) {
        return 'success';
    }

    if (in_array($s, ['pending', 'processing', 'queued'], true)) {
        return 'warning';
    }

    return 'danger';
}

$search = trim($_GET['search'] ?? '');
$source = trim($_GET['source'] ?? '');

$tablesConfig = [
    [
        'candidates'      => ['nin_details'],
        'source_table'    => 'nin_details',
        'service_name'    => "'NIN Verification'",
        'email_col'       => '`email`',
        'identity_col'    => '`nin`',
        'identity_type'   => 'NIN',
        'reference_col'   => '`tracking_id`',
        'status_col'      => '`status`',
        'amount_col'      => '`amount`',
        'created_at_col'  => '`created_at`',
    ],
    [
        'candidates'      => ['unifiy_new', 'unify_new'],
        'source_table'    => 'unifiy_new',
        'service_name'    => "COALESCE(NULLIF(`slip_type`, ''), 'VNIN Slip')",
        'email_col'       => '`email`',
        'identity_col'    => '`nin`',
        'identity_type'   => 'NIN',
        'reference_col'   => '`file_path`',
        'status_col'      => '`status`',
        'amount_col'      => '`amount`',
        'created_at_col'  => '`created_at`',
    ],
    [
        'candidates'      => ['nin_transactions', 'vnin_transactions'],
        'source_table'    => 'nin_transactions',
        'service_name'    => "'VNIN Transaction'",
        'email_col'       => '`user_email`',
        'identity_col'    => '`nin`',
        'identity_type'   => 'NIN',
        'reference_col'   => "COALESCE(NULLIF(`reference`, ''), `tracking_id`)",
        'status_col'      => '`status`',
        'amount_col'      => '`amount`',
        'created_at_col'  => '`created_at`',
    ],
    [
        'candidates'      => ['bvn_details'],
        'source_table'    => 'bvn_details',
        'service_name'    => "COALESCE(NULLIF(`slip_type`, ''), 'BVN Slip')",
        'email_col'       => '`email`',
        'identity_col'    => '`bvn`',
        'identity_type'   => 'BVN',
        'reference_col'   => '`pdf_file`',
        'status_col'      => '`status`',
        'amount_col'      => '`amount`',
        'created_at_col'  => '`created_at`',
    ],
    [
        'candidates'      => ['bvn_phone_logs'],
        'source_table'    => 'bvn_phone_logs',
        'service_name'    => "'BVN Phone Log'",
        'email_col'       => '`user_email`',
        'identity_col'    => '`bvn`',
        'identity_type'   => 'BVN',
        'reference_col'   => "COALESCE(NULLIF(`reference`, ''), `phone`)",
        'status_col'      => '`status`',
        'amount_col'      => '`amount`',
        'created_at_col'  => '`created_at`',
    ],
];

$unionParts = [];
$activeSources = [];

foreach ($tablesConfig as $cfg) {
    $realTable = firstExistingTable($conn, $cfg['candidates']);

    if (!$realTable) {
        continue;
    }

    $sourceTableName = $realTable;
    $activeSources[] = $sourceTableName;

    $unionParts[] = "
        SELECT
            '{$sourceTableName}' AS source_table,
            {$cfg['service_name']} AS service_name,
            {$cfg['email_col']} AS email,
            {$cfg['identity_col']} AS identity_value,
            '{$cfg['identity_type']}' AS identity_type,
            {$cfg['reference_col']} AS reference_value,
            {$cfg['status_col']} AS status,
            CAST(COALESCE({$cfg['amount_col']}, 0) AS DECIMAL(12,2)) AS amount,
            {$cfg['created_at_col']} AS created_at
        FROM `{$realTable}`
    ";
}

$rows = [];
$stats = [
    'today_count'   => 0,
    'today_spent'   => 0,
    'weekly_count'  => 0,
    'weekly_spent'  => 0,
    'monthly_count' => 0,
    'monthly_spent' => 0,
    'total_count'   => 0,
    'total_spent'   => 0,
];

$errorMessage = '';

if (empty($unionParts)) {
    $errorMessage = 'No transaction tables were found in this database.';
} else {
    $unionSql = implode("\nUNION ALL\n", $unionParts);

    $where = [];
    if ($search !== '') {
        $safeSearch = $conn->real_escape_string($search);
        $where[] = "(
            email LIKE '%{$safeSearch}%'
            OR identity_value LIKE '%{$safeSearch}%'
            OR reference_value LIKE '%{$safeSearch}%'
            OR status LIKE '%{$safeSearch}%'
            OR source_table LIKE '%{$safeSearch}%'
            OR service_name LIKE '%{$safeSearch}%'
        )";
    }

    if ($source !== '') {
        $safeSource = $conn->real_escape_string($source);
        $where[] = "source_table = '{$safeSource}'";
    }

    $whereSql = '';
    if (!empty($where)) {
        $whereSql = ' WHERE ' . implode(' AND ', $where);
    }

    $statsSql = "
        SELECT
            SUM(CASE WHEN DATE(created_at) = CURDATE() THEN 1 ELSE 0 END) AS today_count,
            SUM(CASE WHEN DATE(created_at) = CURDATE() THEN amount ELSE 0 END) AS today_spent,

            SUM(CASE WHEN YEARWEEK(created_at, 1) = YEARWEEK(CURDATE(), 1) THEN 1 ELSE 0 END) AS weekly_count,
            SUM(CASE WHEN YEARWEEK(created_at, 1) = YEARWEEK(CURDATE(), 1) THEN amount ELSE 0 END) AS weekly_spent,

            SUM(CASE WHEN YEAR(created_at) = YEAR(CURDATE()) AND MONTH(created_at) = MONTH(CURDATE()) THEN 1 ELSE 0 END) AS monthly_count,
            SUM(CASE WHEN YEAR(created_at) = YEAR(CURDATE()) AND MONTH(created_at) = MONTH(CURDATE()) THEN amount ELSE 0 END) AS monthly_spent,

            COUNT(*) AS total_count,
            COALESCE(SUM(amount), 0) AS total_spent
        FROM (
            {$unionSql}
        ) AS all_tx
        {$whereSql}
    ";

    $statsResult = $conn->query($statsSql);
    if ($statsResult && $statsResult->num_rows > 0) {
        $stats = array_merge($stats, $statsResult->fetch_assoc());
    }

    $rowsSql = "
        SELECT *
        FROM (
            {$unionSql}
        ) AS all_tx
        {$whereSql}
        ORDER BY created_at DESC
    ";

    $rowsResult = $conn->query($rowsSql);
    if ($rowsResult) {
        while ($row = $rowsResult->fetch_assoc()) {
            $rows[] = $row;
        }
    } else {
        $errorMessage = 'Failed to load transactions: ' . $conn->error;
    }
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>All Transactions Dashboard</title>
    <style>
        *{
            margin:0;
            padding:0;
            box-sizing:border-box;
        }

        body{
            font-family: Inter, Arial, sans-serif;
            background: #0b1220;
            color: #e5e7eb;
            min-height: 100vh;
        }

        .wrapper{
            max-width: 1400px;
            margin: 0 auto;
            padding: 24px;
        }

        .topbar{
            display:flex;
            justify-content:space-between;
            align-items:center;
            gap:16px;
            flex-wrap:wrap;
            margin-bottom:24px;
        }

        .title h1{
            font-size: 28px;
            font-weight: 700;
            color:#fff;
        }

        .title p{
            color:#94a3b8;
            margin-top:6px;
        }

        .active-sources{
            display:flex;
            gap:8px;
            flex-wrap:wrap;
            margin-top:10px;
        }

        .chip{
            background:#172036;
            color:#93c5fd;
            padding:6px 10px;
            border-radius:999px;
            font-size:12px;
            border:1px solid #24324d;
        }

        .cards{
            display:grid;
            grid-template-columns: repeat(4, minmax(220px, 1fr));
            gap:16px;
            margin-bottom:24px;
        }

        .card{
            background: linear-gradient(180deg, #111827 0%, #0f172a 100%);
            border:1px solid #1f2937;
            border-radius:18px;
            padding:20px;
            box-shadow: 0 10px 30px rgba(0,0,0,0.25);
        }

        .card .label{
            color:#93c5fd;
            font-size:13px;
            text-transform:uppercase;
            letter-spacing:.08em;
            margin-bottom:12px;
        }

        .metric{
            display:flex;
            justify-content:space-between;
            align-items:end;
            margin-bottom:10px;
            gap:12px;
        }

        .metric .name{
            color:#94a3b8;
            font-size:14px;
        }

        .metric .value{
            color:#fff;
            font-size:24px;
            font-weight:700;
        }

        .filters{
            background:#111827;
            border:1px solid #1f2937;
            border-radius:18px;
            padding:16px;
            margin-bottom:20px;
        }

        .filters form{
            display:grid;
            grid-template-columns: 1.2fr .8fr auto;
            gap:12px;
        }

        .input, .select{
            width:100%;
            background:#0f172a;
            border:1px solid #24324d;
            color:#fff;
            padding:12px 14px;
            border-radius:12px;
            outline:none;
        }

        .btn{
            background:#2563eb;
            color:#fff;
            border:none;
            border-radius:12px;
            padding:12px 18px;
            font-weight:600;
            cursor:pointer;
        }

        .btn:hover{
            background:#1d4ed8;
        }

        .table-card{
            background:#111827;
            border:1px solid #1f2937;
            border-radius:18px;
            overflow:hidden;
        }

        .table-header{
            padding:18px 20px;
            border-bottom:1px solid #1f2937;
            display:flex;
            justify-content:space-between;
            align-items:center;
            flex-wrap:wrap;
            gap:10px;
        }

        .table-header h2{
            font-size:18px;
            color:#fff;
        }

        .count-badge{
            background:#172036;
            color:#cbd5e1;
            padding:8px 12px;
            border-radius:999px;
            font-size:13px;
            border:1px solid #24324d;
        }

        .table-wrap{
            overflow:auto;
        }

        table{
            width:100%;
            border-collapse:collapse;
            min-width:1100px;
        }

        thead{
            background:#0f172a;
        }

        th, td{
            padding:14px 16px;
            text-align:left;
            border-bottom:1px solid #1f2937;
            font-size:14px;
            vertical-align:middle;
        }

        th{
            color:#93c5fd;
            font-weight:600;
        }

        td{
            color:#e5e7eb;
        }

        tr:hover td{
            background:rgba(37,99,235,0.04);
        }

        .status{
            display:inline-flex;
            align-items:center;
            padding:6px 10px;
            border-radius:999px;
            font-size:12px;
            font-weight:700;
            text-transform:capitalize;
        }

        .status.success{
            background:rgba(16,185,129,0.12);
            color:#34d399;
        }

        .status.warning{
            background:rgba(245,158,11,0.12);
            color:#fbbf24;
        }

        .status.danger{
            background:rgba(239,68,68,0.12);
            color:#f87171;
        }

        .amount{
            font-weight:700;
            color:#86efac;
        }

        .muted{
            color:#94a3b8;
        }

        .empty{
            padding:30px 20px;
            color:#cbd5e1;
        }

        .error-box{
            background:rgba(239,68,68,0.12);
            color:#fecaca;
            border:1px solid rgba(239,68,68,0.25);
            padding:14px 16px;
            border-radius:12px;
            margin-bottom:16px;
        }

        @media (max-width: 1100px){
            .cards{
                grid-template-columns: repeat(2, minmax(220px, 1fr));
            }

            .filters form{
                grid-template-columns: 1fr;
            }
        }

        @media (max-width: 640px){
            .wrapper{
                padding:16px;
            }

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

            .title h1{
                font-size:22px;
            }
        }
    </style>
</head>
<body>
<div class="wrapper">

    <div class="topbar">
        <div class="title">
            <h1>All Transactions Dashboard</h1>
            <p>Unified view for NIN, VNIN and BVN transaction tables</p>

            <?php if (!empty($activeSources)): ?>
                <div class="active-sources">
                    <?php foreach ($activeSources as $src): ?>
                        <span class="chip"><?= e($src) ?></span>
                    <?php endforeach; ?>
                </div>
            <?php endif; ?>
        </div>
    </div>

    <?php if ($errorMessage): ?>
        <div class="error-box"><?= e($errorMessage) ?></div>
    <?php endif; ?>

    <div class="cards">
        <div class="card">
            <div class="label">Today</div>
            <div class="metric">
                <div class="name">Today Count</div>
                <div class="value"><?= number_format((int)$stats['today_count']) ?></div>
            </div>
            <div class="metric">
                <div class="name">Today Spent</div>
                <div class="value"><?= naira($stats['today_spent']) ?></div>
            </div>
        </div>

        <div class="card">
            <div class="label">This Week</div>
            <div class="metric">
                <div class="name">Weekly Count</div>
                <div class="value"><?= number_format((int)$stats['weekly_count']) ?></div>
            </div>
            <div class="metric">
                <div class="name">Weekly Spent</div>
                <div class="value"><?= naira($stats['weekly_spent']) ?></div>
            </div>
        </div>

        <div class="card">
            <div class="label">This Month</div>
            <div class="metric">
                <div class="name">Monthly Count</div>
                <div class="value"><?= number_format((int)$stats['monthly_count']) ?></div>
            </div>
            <div class="metric">
                <div class="name">Monthly Spent</div>
                <div class="value"><?= naira($stats['monthly_spent']) ?></div>
            </div>
        </div>

        <div class="card">
            <div class="label">Overall</div>
            <div class="metric">
                <div class="name">Total Count</div>
                <div class="value"><?= number_format((int)$stats['total_count']) ?></div>
            </div>
            <div class="metric">
                <div class="name">Total Spent</div>
                <div class="value"><?= naira($stats['total_spent']) ?></div>
            </div>
        </div>
    </div>

    <div class="filters">
        <form method="get">
            <input
                type="text"
                name="search"
                class="input"
                placeholder="Search email, NIN/BVN, reference, status, source..."
                value="<?= e($search) ?>"
            >

            <select name="source" class="select">
                <option value="">All Sources</option>
                <?php foreach ($activeSources as $src): ?>
                    <option value="<?= e($src) ?>" <?= $source === $src ? 'selected' : '' ?>>
                        <?= e($src) ?>
                    </option>
                <?php endforeach; ?>
            </select>

            <button type="submit" class="btn">Filter</button>
        </form>
    </div>

    <div class="table-card">
        <div class="table-header">
            <h2>Transactions</h2>
            <div class="count-badge"><?= number_format(count($rows)) ?> record(s)</div>
        </div>

        <div class="table-wrap">
            <table>
                <thead>
                    <tr>
                        <th>#</th>
                        <th>Service</th>
                        <th>Source Table</th>
                        <th>Email</th>
                        <th>ID Type</th>
                        <th>ID Value</th>
                        <th>Reference / File</th>
                        <th>Status</th>
                        <th>Amount</th>
                        <th>Date</th>
                    </tr>
                </thead>
                <tbody>
                <?php if (!empty($rows)): ?>
                    <?php foreach ($rows as $index => $row): ?>
                        <tr>
                            <td><?= $index + 1 ?></td>
                            <td><?= e($row['service_name']) ?></td>
                            <td><span class="muted"><?= e($row['source_table']) ?></span></td>
                            <td><?= e(maskEmail((string)$row['email'])) ?></td>
                            <td><?= e($row['identity_type']) ?></td>
                            <td><?= e(maskValue((string)$row['identity_value'], (string)$row['identity_type'])) ?></td>
                            <td><?= e(maskValue((string)$row['reference_value'])) ?></td>
                            <td>
                                <span class="status <?= statusClass((string)$row['status']) ?>">
                                    <?= e($row['status']) ?>
                                </span>
                            </td>
                            <td class="amount"><?= naira($row['amount']) ?></td>
                            <td><?= !empty($row['created_at']) ? date('d M Y h:i A', strtotime($row['created_at'])) : 'N/A' ?></td>
                        </tr>
                    <?php endforeach; ?>
                <?php else: ?>
                    <tr>
                        <td colspan="10" class="empty">No transactions found.</td>
                    </tr>
                <?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>

</div>
</body>
</html>