<?php
include ('./includes/header.php');

// Abre conexões
$db_clientes = new SQLite3('./api/db/studiolivecode_clientes.db');
$db_users = new SQLite3('./api/db/studiolivecode_users.db');
$db_venc = new SQLite3('./api/db/studiolivecode_vencimento.db');

// --- Consulta para DNS usadas e quantidade por DNS (clientes)
$dnsData = [];
$resDns = $db_clientes->query("SELECT dns, COUNT(*) as total FROM ibo GROUP BY dns ORDER BY total DESC");
while ($row = $resDns->fetchArray(SQLITE3_ASSOC)) {
    $dnsData[] = ['dns' => $row['dns'], 'total' => intval($row['total'])];
}

// --- Consulta para clientes por revendedor (id_user)
$revData = [];
$resRev = $db_clientes->query("SELECT id_user, COUNT(*) as total FROM ibo GROUP BY id_user ORDER BY total DESC");
while ($row = $resRev->fetchArray(SQLITE3_ASSOC)) {
    // Pega nome do revendedor
    $stmt = $db_users->prepare("SELECT name FROM users WHERE id = :id");
    $stmt->bindValue(':id', $row['id_user'], SQLITE3_INTEGER);
    $resName = $stmt->execute();
    $nameRow = $resName->fetchArray(SQLITE3_ASSOC);
    $revName = $nameRow ? $nameRow['name'] : 'Desconhecido';
    $revData[] = ['name' => $revName, 'total' => intval($row['total'])];
}

// --- Consulta para lista ordenada por vencimento dos revendedores
$vencData = [];
$resVenc = $db_users->query("SELECT id, name FROM users WHERE store_type = 1 ORDER BY id ASC");
while ($row = $resVenc->fetchArray(SQLITE3_ASSOC)) {
    $stmtV = $db_venc->prepare("SELECT vencimento FROM vencimento WHERE id = :id");
    $stmtV->bindValue(':id', $row['id'], SQLITE3_INTEGER);
    $resV = $stmtV->execute();
    $vencRow = $resV->fetchArray(SQLITE3_ASSOC);
    $vencDate = $vencRow ? date('d/m/Y', strtotime($vencRow['vencimento'])) : 'Sem data';
    $vencData[] = ['name' => $row['name'], 'vencimento' => $vencDate];
}
?>

<style>
body, .content-page, .content, .container-fluid {
    background-color: #121212;
    color: white !important;
}
.content-page h5,
.content-page p,
.content-page table,
.content-page thead th,
.content-page tbody td,
.card,
.card * {
    color: white !important;
}
.table-compact th,
.table-compact td {
    padding: 0.3rem 0.5rem;
    font-size: 0.9rem;
    vertical-align: middle;
    border-color: #444 !important;
}
.card-total {
    background-color: #222;
    color: white;
    padding: 15px;
    border-radius: 6px;
    margin-bottom: 20px;
    font-size: 1.3rem;
    font-weight: 600;
}
.card {
    background-color: #222 !important;
    color: white !important;
}
</style>

<div class="content-page">
    <div class="content">
        <div class="container-fluid">

            <div class="row">
                <div class="col-lg-6">
                    <div class="card p-3">
                        <h5>Ranking de DNS usadas (Clientes)</h5>
                        <canvas id="dnsChart" style="max-height: 350px;"></canvas>
                    </div>
                </div>

                <div class="col-lg-6">
                    <div class="card p-3">
                        <h5>Quantidade de Clientes por Revendedor</h5>
                        <canvas id="revChart" style="max-height: 350px;"></canvas>
                    </div>
                </div>
            </div>

            <div class="row mt-4">
                <div class="col-lg-6">
                    <div class="card p-3">
                        <h5>Revendedores Ordenados por Vencimento</h5>
                        <table class="table table-striped table-compact">
                            <thead>
                                <tr>
                                    <th>Revendedor</th>
                                    <th>Data de Vencimento</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($vencData as $venc): ?>
                                <tr>
                                    <td><?= htmlspecialchars($venc['name']) ?></td>
                                    <td><?= htmlspecialchars($venc['vencimento']) ?></td>
                                </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                </div>

                <div class="col-lg-6">
                    <div class="card p-3">
                        <h5>Total de Clientes</h5>
                        <table class="table table-striped table-compact">
                            <thead>
                                <tr>
                                    <th>Revendedor</th>
                                    <th>Quantidade de Clientes</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($revData as $rev): ?>
                                <tr>
                                    <td><?= htmlspecialchars($rev['name']) ?></td>
                                    <td><?= htmlspecialchars($rev['total']) ?></td>
                                </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                </div>

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

<!-- CDN Chart.js -->
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
// Função para gerar cor HSL aleatória com saturação e luminosidade fixas
function randomHsl() {
    const hue = Math.floor(Math.random() * 360);
    return `hsl(${hue}, 70%, 60%)`;
}

// Dados DNS Chart
const dnsLabels = <?= json_encode(array_column($dnsData, 'dns')) ?>;
const dnsValues = <?= json_encode(array_column($dnsData, 'total')) ?>;

// Dados Revendedores Chart
const revLabels = <?= json_encode(array_column($revData, 'name')) ?>;
const revValues = <?= json_encode(array_column($revData, 'total')) ?>;

const dnsCtx = document.getElementById('dnsChart').getContext('2d');
const dnsChart = new Chart(dnsCtx, {
    type: 'pie',
    data: {
        labels: dnsLabels,
        datasets: [{
            data: dnsValues,
            backgroundColor: dnsLabels.map(() => randomHsl()),
        }]
    },
    options: {
        responsive: true,
        plugins: {
            legend: { position: 'bottom', labels: { color: 'white' } }
        }
    }
});

const revCtx = document.getElementById('revChart').getContext('2d');
const revChart = new Chart(revCtx, {
    type: 'pie',
    data: {
        labels: revLabels,
        datasets: [{
            data: revValues,
            backgroundColor: revLabels.map(() => randomHsl()),
        }]
    },
    options: {
        responsive: true,
        plugins: {
            legend: { position: 'bottom', labels: { color: 'white' } }
        }
    }
});
</script>

<?php include ('./includes/footer.php'); ?>
