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

// Conexão com o banco de dados MySQL para o total de vendas e número de vendas
$server_mysql_vendas = "localhost";
$user_mysql_vendas   = "u146835474_fi";
$pass_mysql_vendas   = "asder123Ww@";
$db_mysql_vendas     = "u146835474_fi";

// Criando a conexão com o banco de dados MySQL
$conn_mysql_vendas = new mysqli($server_mysql_vendas, $user_mysql_vendas, $pass_mysql_vendas, $db_mysql_vendas);

// Verificando a conexão MySQL
if ($conn_mysql_vendas->connect_error) {
    die("Conexão MySQL falhou: " . $conn_mysql_vendas->connect_error);
}

// Função para pegar o total de vendas
function getTotalVendas() {
    global $conn_mysql_vendas;
    $sql_total_vendas = "SELECT SUM(valor) as total FROM vendas";
    $result_total_vendas = $conn_mysql_vendas->query($sql_total_vendas);
    $total = 0;

    if ($result_total_vendas && $row_total = $result_total_vendas->fetch_assoc()) {
        $total = $row_total['total'];
    }

    return $total;
}

// Função para pegar o número total de vendas
function getTotalVendasCount() {
    global $conn_mysql_vendas;
    $sql_count_vendas = "SELECT COUNT(*) as total_vendas FROM vendas";
    $result_count_vendas = $conn_mysql_vendas->query($sql_count_vendas);
    $total_vendas = 0;

    if ($result_count_vendas && $row_count = $result_count_vendas->fetch_assoc()) {
        $total_vendas = $row_count['total_vendas'];
    }

    return $total_vendas;
}

// Função para pegar o faturamento do último mês
function getTotalVendasUltimoMes() {
    global $conn_mysql_vendas;
    $sql_total_vendas_ultimo_mes = "SELECT SUM(valor) as total FROM vendas WHERE data >= CURDATE() - INTERVAL 1 MONTH";
    $result_total_vendas_ultimo_mes = $conn_mysql_vendas->query($sql_total_vendas_ultimo_mes);
    $total_ultimo_mes = 0;

    if ($result_total_vendas_ultimo_mes && $row_total = $result_total_vendas_ultimo_mes->fetch_assoc()) {
        $total_ultimo_mes = $row_total['total'];
    }

    return $total_ultimo_mes;
}

// Função para pegar os MAC Addresses
function getMacAddresses() {
    $db_sqlite = new SQLite3("./api/.ansdb.db");
    $sql_mac_addresses = "SELECT mac_address FROM ibo";
    $result_mac_addresses = $db_sqlite->query($sql_mac_addresses);

    $mac_addresses = [];
    while ($row = $result_mac_addresses->fetchArray(SQLITE3_ASSOC)) {
        $mac_addresses[] = $row['mac_address'];
    }

    $db_sqlite->close();

    return $mac_addresses;
}

// Função para pegar o número total de clientes
function getTotalClientes() {
    $db_sqlite = new SQLite3("./api/.ansdb.db");
    $sql_count_clients = "SELECT COUNT(*) as total_clients FROM ibo";
    $result_count_clients = $db_sqlite->query($sql_count_clients);
    $total_clients = 0;

    if ($result_count_clients && $row_count_clients = $result_count_clients->fetchArray(SQLITE3_ASSOC)) {
        $total_clients = $row_count_clients['total_clients'];
    }

    $db_sqlite->close();

    return $total_clients;
}

// Função para pegar o número de clientes inativos
function getTotalInativas() {
    $db_sqlite = new SQLite3("./api/.ansdb.db");
    $sql_count_inativas = "SELECT COUNT(*) as total_inativas FROM ibo WHERE expire_date < DATE('now')";
    $result_count_inativas = $db_sqlite->query($sql_count_inativas);
    $total_inativas = 0;

    if ($result_count_inativas && $row_count_inativas = $result_count_inativas->fetchArray(SQLITE3_ASSOC)) {
        $total_inativas = $row_count_inativas['total_inativas'];
    }

    $db_sqlite->close();

    return $total_inativas;
}

// Função para atualizar DNS de todos os registros
function updateAllDns() {
    $db_sqlite = new SQLite3("./api/.ansdb.db");
    $dns_links = [
        'http://opbx.top/get.php?username=apolotv&password=891218269&type=m3u_plus&output=mpegts',
        'http://opbx.top/get.php?username=lucianotvs&password=111723535&type=m3u_plus&output=mpegts',
        'http://opbx.top/get.php?username=projetortv&password=233996106&type=m3u_plus&output=mpegts',
        'http://opbx.top/get.php?username=adilsontv&password=954891461&type=m3u_plus&output=mpegts',
        'http://opbx.top/get.php?username=thefulltvbox&password=358128991&type=m3u_plus&output=mpegts'
    ];

    $sql_mac_addresses = "SELECT mac_address FROM ibo";
    $result_mac_addresses = $db_sqlite->query($sql_mac_addresses);

    while ($row = $result_mac_addresses->fetchArray(SQLITE3_ASSOC)) {
        $mac_address = strtoupper($row['mac_address']);
        $new_dns = $dns_links[array_rand($dns_links)];

        $stmt = $db_sqlite->prepare("UPDATE ibo SET dns = :dns WHERE mac_address = :mac_address");
        $stmt->bindValue(':dns', $new_dns, SQLITE3_TEXT);
        $stmt->bindValue(':mac_address', $mac_address, SQLITE3_TEXT);
        $stmt->execute();
    }

    $db_sqlite->close();
}

?>
