<?php
ini_set("display_errors", 0);
error_reporting(E_ALL);
require '../config/db.php';

if (isset($_SESSION['user'])) {
    $userDetails = $pdo->prepare("SELECT * FROM users WHERE phone = :phone");
    $userDetails->execute([
        ':phone' => $_SESSION['user']
    ]);
    $user = $userDetails->fetch(PDO::FETCH_ASSOC);
}

if (!$user) {
    header("Location: login.php");
    exit;
}

// Funções

function createHistory($action) {
    global $pdo;
    global $user;
    $stmtCreateHistory = $pdo->prepare('INSERT INTO history (user_id, action, created_at) VALUES (:user_id, :action, :created_at)');
    $stmtCreateHistory->execute([
        ':user_id'=> $user['id'],
        ':action'=> $action,
        ':created_at'=> date('Y-m-d H:i:s'),
        ]);
}

// Selecionar dados da plataforma
$stmtConfigsPlatform = $pdo->prepare("SELECT * FROM configs");
$stmtConfigsPlatform->execute();
$stmtConfigsPlatformResult = $stmtConfigsPlatform->fetch(PDO::FETCH_ASSOC);

// Selecionar o total de indicados do afiliado
$stmtDataAffiliate = $pdo->prepare("SELECT COUNT(*) AS total FROM users WHERE indicated_by = :indicated_by");
$stmtDataAffiliate->execute([
    ':indicated_by' => $user['affiliate_code']
]);
$result = $stmtDataAffiliate->fetch(PDO::FETCH_ASSOC);
$totalIndicados = $result['total'];

// Selecionar comissões recebidas 
$stmtAffiliateComission = $pdo->prepare("SELECT * FROM comission_affiliates WHERE affiliate_id = :affiliate_id");
$stmtAffiliateComission->execute([
    ':affiliate_id' => $user['id']
]);
$comissionAffiliateStmt = $stmtAffiliateComission->fetchAll(PDO::FETCH_ASSOC);

// Consultar a soma de comissoes
$stmtAffiliateComission = $pdo->prepare("SELECT SUM(liquid_affiliate) as total_liquid FROM comission_affiliates WHERE affiliate_id = :affiliate_id");
$stmtAffiliateComission->execute([
    ':affiliate_id' => $user['id']
]);
$result = $stmtAffiliateComission->fetch(PDO::FETCH_ASSOC);

$totalLiquid = $result['total_liquid'] ?? 0.00;

// Consultar a soma de comissoes Lvl1
$stmtAffiliateComissionLvl1 = $pdo->prepare("SELECT SUM(liquid_affiliate) as total_liquid FROM comission_affiliates WHERE affiliate_id = :affiliate_id AND affiliate_lvl = '1'");
$stmtAffiliateComissionLvl1->execute([
    ':affiliate_id' => $user['id']
]);
$resultLvl1 = $stmtAffiliateComissionLvl1->fetch(PDO::FETCH_ASSOC);

$totalLiquidLvl1 = $resultLvl1['total_liquid'] ?? 0.00;

// Consultar a soma de comissoes Lvl2
$stmtAffiliateComissionLvl2 = $pdo->prepare("SELECT SUM(liquid_affiliate) as total_liquid FROM comission_affiliates WHERE affiliate_id = :affiliate_id AND affiliate_lvl = '2'");
$stmtAffiliateComissionLvl2->execute([
    ':affiliate_id' => $user['id']
]);
$resultLvl2 = $stmtAffiliateComissionLvl2->fetch(PDO::FETCH_ASSOC);

$totalLiquidLvl2 = $resultLvl2['total_liquid'] ?? 0.00;

// Consultar a soma de comissoes Lvl3
$stmtAffiliateComissionLvl3 = $pdo->prepare("SELECT SUM(liquid_affiliate) as total_liquid FROM comission_affiliates WHERE affiliate_id = :affiliate_id AND affiliate_lvl = '3'");
$stmtAffiliateComissionLvl3->execute([
    ':affiliate_id' => $user['id']
]);
$resultLvl3 = $stmtAffiliateComissionLvl3->fetch(PDO::FETCH_ASSOC);

$totalLiquidLvl3 = $resultLvl3['total_liquid'] ?? 0.00;

// Consultar transações do usuário
$stmtConsultTransaction = $pdo->prepare("SELECT * FROM transactions WHERE user_id = :user_id");
$stmtConsultTransaction->execute([
    ':user_id' => $user['id']
]);
$stmtResultTransaction = $stmtConsultTransaction->fetchAll(PDO::FETCH_ASSOC);

// Consultar transações do usuário
$stmtConsultTransactionPaid = $pdo->prepare("SELECT * FROM transactions WHERE user_id = :user_id AND status = 'paid'");
$stmtConsultTransactionPaid->execute([
    ':user_id' => $user['id']
]);
$stmtResultTransactionPaid = $stmtConsultTransactionPaid->fetchAll(PDO::FETCH_ASSOC);

// Consultar contas PIX do usuário
$stmtConsultPixAccount = $pdo->prepare("SELECT * FROM accounts_pix WHERE phone_user = :phone_user LIMIT 1");
$stmtConsultPixAccount->execute([
    ':phone_user' => $_SESSION['user']
]);
$stmtResultPix = $stmtConsultPixAccount->fetch(PDO::FETCH_ASSOC);

// Consultar Retiradas pendentes do usuário
$stmtWithdrawalPendingUser = $pdo->prepare("SELECT * FROM withdrawal WHERE status = 'pending' AND user_id = :user_id");
$stmtWithdrawalPendingUser->execute([
    ':user_id' => $user['id']
]);
$resultWithdrawalPendingUser = $stmtWithdrawalPendingUser->fetchAll(PDO::FETCH_ASSOC);

// Puxar todos indicados LVL 1 do usuário logado
$stmtIndicatedsLvl1Direct = $pdo->prepare('SELECT * FROM users WHERE indicated_by = :indicated_by');
$stmtIndicatedsLvl1Direct->execute([
    ':indicated_by' => $user['affiliate_code']
]);
$indicadosLVL1Diretos = $stmtIndicatedsLvl1Direct->fetchAll(PDO::FETCH_ASSOC);

$indicadosLVL2Diretos = null;

// Puxar todos indicados LVL 2 do usuário logado
$somaLvl2 = [];
if ($indicadosLVL1Diretos) {
    foreach ($indicadosLVL1Diretos as $row) {
        $stmtIndicatedsLvl2Direct = $pdo->prepare('SELECT * FROM users WHERE indicated_by = :indicated_by');
        $stmtIndicatedsLvl2Direct->execute([
            ':indicated_by' => $row['affiliate_code']
        ]);
        $indicadosLVL2Diretos = $stmtIndicatedsLvl2Direct->fetchAll(PDO::FETCH_ASSOC);
        $somaLvl2 = array_merge($somaLvl2, $indicadosLVL2Diretos);
    }
}

// Puxar todos indicados LVL 3 do usuário logado
$somaLvl3 = [];
if ($indicadosLVL2Diretos) {
    foreach ($indicadosLVL2Diretos as $row2) {
        $stmtIndicatedsLvl3Direct = $pdo->prepare('SELECT * FROM users WHERE indicated_by = :indicated_by');
        $stmtIndicatedsLvl3Direct->execute([
            ':indicated_by' => $row2['affiliate_code']
        ]);
        $indicadosLVL3Diretos = $stmtIndicatedsLvl3Direct->fetchAll(PDO::FETCH_ASSOC);
        $somaLvl3 = array_merge($somaLvl3, $indicadosLVL3Diretos);
    }
}

$todosIndicados = array_merge($indicadosLVL1Diretos, $somaLvl2, $somaLvl3);

$locatedMerge = [];
$locatedMergeAtivos = [];
// Contagem de usuários diretos com investimento igual ou acima de 150
if ($indicadosLVL1Diretos) {

    foreach ($indicadosLVL1Diretos as $indicadoDireto) {
        $stmtLocate150 = $pdo->prepare('SELECT COUNT(*) AS total_investidos_150 FROM investments WHERE invested_amount >= :invested_amount AND user_id = :user_id');
        $stmtLocate150->execute([
            ':invested_amount' => 150,
            ':user_id' => $indicadoDireto['id']
        ]);
        $located150 = $stmtLocate150->fetch(PDO::FETCH_ASSOC);

        if ($located150['total_investidos_150'] > 0) {
            $locatedMerge[] = [
                'total_investidos_150' => $located150['total_investidos_150'],
            ];
        }
    }
    
    // FOREACH DE INVESTIMENTOS GERAIS
    foreach ($todosIndicados as $todos) {
        $stmtAtivos = $pdo->prepare('SELECT COUNT(*) AS total_ativos FROM investments WHERE user_id = :user_id');
        $stmtAtivos->execute([
            ':user_id' => $todos['id']
        ]);
        $totalAtivos = $stmtAtivos->fetch(PDO::FETCH_ASSOC);

        if ($totalAtivos['total_ativos'] > 0) {
            $locatedMergeAtivos[] = [
                'total_ativos' => $totalAtivos['total_ativos'],
            ];
        }
    }
}

// Lógica do VIP

$contagemAtivos = count($locatedMergeAtivos);
$located150Contagem = count($locatedMerge);
$vipLevel = $user['vip_level'];

$stmtConsultVips = $pdo->prepare('SELECT * FROM vip_levels');
$stmtConsultVips->execute();
$resultVips = $stmtConsultVips->fetchAll(PDO::FETCH_ASSOC);



if (isset($resultVips[$vipLevel]) && $located150Contagem >= $resultVips[$vipLevel]['qtd_indicated']) {
    $newVip = $vipLevel + 1;
    $bonus = $resultVips[$vipLevel]['bonus'];

    $stmtUpdateVip = $pdo->prepare('UPDATE users SET vip_level = :vip_level, awards_vip = awards_vip + :value, balance = balance + :value WHERE id = :id');
    $stmtUpdateVip->execute([
        ':value' => $bonus,
        ':vip_level' => $newVip,
        ':id' => $user['id']
    ]);
    
    createHistory('NOVO VIP! o usuário subiu para o nível VIP' . $newVip . ' e recebeu R$' . $bonus . ' em sua carteira.');
}

// Informacoes do vip atual
$stmtConsultVipActual = $pdo->prepare('SELECT * FROM vip_levels WHERE level = :level');
$stmtConsultVipActual->execute([
    ':level' => ($user['vip_level'] == 0) ? 1 : $user['vip_level'] + 1,
]);
$resultVipActual = $stmtConsultVipActual->fetch(PDO::FETCH_ASSOC);

// Consultar Transacoes dos indicados
$totalGeral = null;
if (count($todosIndicados) > 0) {
    $ids = array_column($todosIndicados, 'id');
    $placeholders = implode(',', array_fill(0, count($ids), '?'));

    $stmt33 = $pdo->prepare("
    SELECT user_id, SUM(value) AS total_conversao
    FROM transactions
    WHERE user_id IN ($placeholders)
    AND status = 'paid'
    GROUP BY user_id
");
    $stmt33->execute($ids);
    $transacoesMerge = $stmt33->fetchAll(PDO::FETCH_ASSOC);
    $totalGeral = array_sum(array_column($transacoesMerge, 'total_conversao'));
}

// Puxar todos indicados LVL 1 do usuário logado (COMISSAO)
$stmtIndicatedsLvl1 = $pdo->prepare("SELECT * FROM comission_affiliates WHERE affiliate_id = :affiliate_id AND affiliate_lvl = '1' GROUP BY indicated_id");
$stmtIndicatedsLvl1->execute([
    ':affiliate_id' => $user['id']
]);
$resultIndicatedsLvl1 = $stmtIndicatedsLvl1->fetchAll(PDO::FETCH_ASSOC);

// Puxar todos indicados LVL 2 do usuário logado (COMISSAO)
$stmtIndicatedsLvl2 = $pdo->prepare("SELECT * FROM comission_affiliates WHERE affiliate_id = :affiliate_id AND affiliate_lvl = '2' GROUP BY indicated_id");
$stmtIndicatedsLvl2->execute([
    ':affiliate_id' => $user['id']
]);
$resultIndicatedsLvl2 = $stmtIndicatedsLvl2->fetchAll(PDO::FETCH_ASSOC);

// Puxar todos indicados LVL 3 do usuário logado (COMISSAO)
$stmtIndicatedsLvl3 = $pdo->prepare("SELECT * FROM comission_affiliates WHERE affiliate_id = :affiliate_id AND affiliate_lvl = '3' GROUP BY indicated_id");
$stmtIndicatedsLvl3->execute([
    ':affiliate_id' => $user['id']
]);
$resultIndicatedsLvl3 = $stmtIndicatedsLvl3->fetchAll(PDO::FETCH_ASSOC);

$totalIndicatedsUser = count($resultIndicatedsLvl1) + count($resultIndicatedsLvl2) + count($resultIndicatedsLvl3);

// Consultar total investimento
$totalInvestmentsUser = $pdo->prepare("SELECT SUM(invested_amount) AS total_invested FROM investments WHERE user_id = :user_id");
$totalInvestmentsUser->execute([
    ':user_id' => $user['id']
]);
$resultInvestmentsUser = $totalInvestmentsUser->fetch(PDO::FETCH_ASSOC);
$totalInvested = $resultInvestmentsUser['total_invested'];

// Consultar retiradas aprovadas
$totalRetiradaUser = $pdo->prepare("SELECT SUM(with_liquid) AS total_retirada FROM withdrawal WHERE user_id = :user_id AND status = 'approved'");
$totalRetiradaUser->execute([
    ':user_id' => $user['id']
]);
$resultRetiradaUser = $totalRetiradaUser->fetch(PDO::FETCH_ASSOC);
$totalRetirado = $resultRetiradaUser['total_retirada'];

// Consultar valores investimentos
$totalPaidUser = $pdo->prepare("SELECT SUM(total_paid) AS total_paid FROM investments WHERE user_id = :user_id");
$totalPaidUser->execute([
    ':user_id' => $user['id']
]);
$resulPaidUser = $totalPaidUser->fetch(PDO::FETCH_ASSOC);
$totalPaid = $resulPaidUser['total_paid'];

// Consultar todas retiradas do usuário
$stmtConsultTotalWithdrawals = $pdo->prepare("SELECT * FROM withdrawal WHERE user_id = :user_id ORDER BY created_at DESC");
$stmtConsultTotalWithdrawals->execute([
    ':user_id' => $user['id']
]);
$resultConsultTotalWithdrawals = $stmtConsultTotalWithdrawals->fetchAll(PDO::FETCH_ASSOC);

// Consultar todos depósitos do usuário
$stmtConsultTotalDep = $pdo->prepare("SELECT * FROM transactions WHERE user_id = :user_id ORDER BY created_at DESC");
$stmtConsultTotalDep->execute([
    ':user_id' => $user['id']
]);
$resultConsultTotalDep = $stmtConsultTotalDep->fetchAll(PDO::FETCH_ASSOC);

// Consultar total valores depósitos
$stmtTotalValuesDep = $pdo->prepare("SELECT SUM(value) AS total_values_dep FROM transactions WHERE user_id = :user_id AND status = 'paid'");
$stmtTotalValuesDep->execute([
    ':user_id' => $user['id']
]);
$resultDepTotal = $stmtTotalValuesDep->fetch(PDO::FETCH_ASSOC);
$allDepValues = $resultDepTotal['total_values_dep'];
