<?php

class SubscriberModel extends Model
{
  public function checkKYCNumber($type, $number, $user_id) {
        $column = $type === 'NIN' ? 'sNin' : 'sBvn';
        $sql = "SELECT sId FROM subscribers WHERE $column = :number AND sId != :user_id";
        $query = $this->dbh->prepare($sql);
        $query->bindParam(':number', $number, PDO::PARAM_STR);
        $query->bindParam(':user_id', $user_id, PDO::PARAM_STR);
        $query->execute();
        return $query->fetch(PDO::FETCH_ASSOC) !== false;
    }

    public function insertUserNewKyc($user_id, $type, $number) {
        try {
            $column = $type === 'NIN' ? 'sNin' : 'sBvn';
            $sql = "INSERT INTO subscribers (sId, $column, sVerified) VALUES (:user_id, :number, 'yes') 
                    ON DUPLICATE KEY UPDATE $column = :number, sVerified = 'yes'";
            $query = $this->dbh->prepare($sql);
            $query->bindParam(':user_id', $user_id, PDO::PARAM_STR);
            $query->bindParam(':number', $number, PDO::PARAM_STR);
            $query->execute();
           // $this->createAccountBillstackPalmpay($id);
            return ['status' => 'success', 'msg' => 'KYC details saved.'];
        } catch (PDOException $e) {
            return ['status' => 'fail', 'msg' => 'Database error: ' . $e->getMessage()];
        }
    }
    
    
     public function createAccountBillstackPalmpay($id)
    {
        $id = (float) $id;
        $dbh = $this->connect();
        $sql = "SELECT * FROM subscribers WHERE sId = $id";
        $queryC = $dbh->prepare($sql);
        $queryC->execute();
        $result = $queryC->fetch(PDO::FETCH_OBJ);

        if (empty($result->sPalmpay)) {
            $obj = new Account();

            $d = $this->getApiConfiguration();
            $strowalletStatus = $this->getConfigValue($d, "billstackStatus");
            $strowalletApi = $this->getConfigValue($d, "billstackApi");

            if ($strowalletStatus == "On") {
                $obj->createAccountBillstackPalmpay($id, $result->sEmail, $result->sPhone, $result->sFname, $result->sLname, $strowalletApi);
            }
        }

        return null;
    }
    
    //Record Site Traffic
    public function recordTraffic()
    {
        if (isset($_COOKIE['loginId'])) {
            if (!isset($_COOKIE['loginVisit'])) {
                $loginId = (float) base64_decode($_COOKIE['loginId']);
                $loginState = base64_decode($_COOKIE['loginState']);
                $visitDate = time();

                $dbh = self::connect();
                $sql = "INSERT INTO uservisits (user,state,visitTime) VALUES (:u,:s,:t)";
                $queryC = $dbh->prepare($sql);
                $queryC->bindParam(':u', $loginId, PDO::PARAM_INT);
                $queryC->bindParam(':s', $loginState, PDO::PARAM_STR);
                $queryC->bindParam(':t', $visitDate, PDO::PARAM_STR);
                $queryC->execute();

                setcookie("loginVisit", "loginVisit", time() + 86400 * 30, "/");
            }
        }
    }


public function setUserApp($id)
    {
        $dbh = self::connect();
        $id = (int) $id;

        // Check if the user exists based on ID
        $checkUserQuery = "SELECT * FROM subscribers WHERE sId = :id";
        $checkUserStmt = $dbh->prepare($checkUserQuery);
        $checkUserStmt->bindParam(':id', $id, PDO::PARAM_INT);
        $checkUserStmt->execute();

        // Fetch the user details for response
        $result = $checkUserStmt->fetch(PDO::FETCH_OBJ);

        if ($checkUserStmt->rowCount() > 0) {
           
            return $result;
        } else {
            // User does not exist
            $response = [];
            $response["status"] = "error";
            $response["message"] = "User not found.";
            return $response;
        }
    }
  
  
function storeQuery($sId, $ref, $heading, $queryContent)
{
    // Input validation
    if (empty($sId) || empty($ref) || empty($heading) || empty($queryContent)) {
        throw new Exception("All fields are required.");
    }

    $dbh = $this->connect();

    try {
        $dbh->beginTransaction();

        // Check transaction (if needed)
        $sql_check_transaction = "SELECT status FROM transactions WHERE transref = :ref";
        $query_check_transaction = $dbh->prepare($sql_check_transaction);
        $query_check_transaction->bindParam(':ref', $ref, PDO::PARAM_STR);
        $query_check_transaction->execute();
        $transaction_data = $query_check_transaction->fetch(PDO::FETCH_ASSOC);

      

        // Check if issue with $ref already exists
        $sql_check_issue = "SELECT id FROM issues WHERE ref = :ref";
        $query_check_issue = $dbh->prepare($sql_check_issue);
        $query_check_issue->bindParam(':ref', $ref, PDO::PARAM_STR);
        $query_check_issue->execute();
        $existing_issue = $query_check_issue->fetch(PDO::FETCH_ASSOC);

        if ($existing_issue) {
            // If issue exists, return its ID and success status
            $dbh->commit();
            return ['id' => $existing_issue['id'], 'status' => 'success'];
        }

        // Count issues
        $sql_count_issues = "SELECT COUNT(*) AS issue_count FROM issues WHERE sId = :sId";
        $query_count_issues = $dbh->prepare($sql_count_issues);
        $query_count_issues->bindParam(':sId', $sId, PDO::PARAM_STR);
        $query_count_issues->execute();
        $issue_count = $query_count_issues->fetchColumn();

        // Delete old issues if count >= 10
        if ($issue_count >= 10) {
            $sql_delete_old_issues = "
                DELETE FROM issues 
                WHERE issue_id IN (
                    SELECT issue_id FROM issues WHERE sId = :sId ORDER BY created_at ASC LIMIT :limit
                )";
            $query_delete_old_issues = $dbh->prepare($sql_delete_old_issues);
            $query_delete_old_issues->bindParam(':sId', $sId, PDO::PARAM_STR);
            $limit = $issue_count - 9;
            $query_delete_old_issues->bindParam(':limit', $limit, PDO::PARAM_INT);
            $query_delete_old_issues->execute();
        }

        // Insert issue
        $sql_insert_query = "INSERT INTO issues (sId, ref, query, userEmail) 
                            VALUES (:sId, :ref, :queryContent, (SELECT sEmail FROM subscribers WHERE sId = :sId))";
        $query_insert_query = $dbh->prepare($sql_insert_query);
        $query_insert_query->bindParam(':sId', $sId, PDO::PARAM_STR);
        $query_insert_query->bindParam(':ref', $ref, PDO::PARAM_STR);
        $query_insert_query->bindParam(':queryContent', $queryContent, PDO::PARAM_STR);
        $query_insert_query->execute();

        $issue_id = $dbh->lastInsertId();
        if (!$issue_id) {
            throw new Exception("Failed to retrieve last inserted issue ID.");
        }

        // Insert replies
        $sql_insert_reply = "INSERT INTO replies (issue_id, reply, replyby) VALUES (:issue_id, :response, :replyby)";
        $query_insert_reply = $dbh->prepare($sql_insert_reply);
        $query_insert_reply->bindParam(':issue_id', $issue_id, PDO::PARAM_INT);
        $query_insert_reply->bindParam(':response', $response, PDO::PARAM_STR);
        $query_insert_reply->bindParam(':replyby', $replyby, PDO::PARAM_STR);

        // User reply
        $response = $queryContent;
        $replyby = 'User';
        $query_insert_reply->execute();

        // Admin reply
        $response = "Thanks for your message. We will reply as soon as possible.";
        $replyby = 'Admin';
        $query_insert_reply->execute();

        $dbh->commit();

        return ['id' => $issue_id, 'status' => 'success'];

    } catch (Exception $e) {
        $dbh->rollBack();
        throw $e;
    }
}
    function storeReply($issueId, $replyContent, $replyby, $upload_path)
    {
        $dbh = $this->connect();
        $sql = "INSERT INTO replies (issue_id, reply, replyby, img) VALUES (:issueId, :replyContent, :replyby, :upload_path)";
        $query = $dbh->prepare($sql);
        $query->bindParam(':issueId', $issueId, PDO::PARAM_INT);
        $query->bindParam(':replyContent', $replyContent, PDO::PARAM_STR);
        $query->bindParam(':replyby', $replyby, PDO::PARAM_STR);
        $query->bindParam(':upload_path', $upload_path, PDO::PARAM_STR);
        $query->execute();

        $sqlUpdate = "UPDATE issues SET admin_read = 0 WHERE id = :issueId";
        $queryUpdate = $dbh->prepare($sqlUpdate);
        $queryUpdate->bindParam(':issueId', $issueId, PDO::PARAM_INT);
        $queryUpdate->execute();
    }

    function getQueries($id)
    {
        $dbh = $this->connect();
        $sql = "SELECT i.*, r.* 
        FROM issues i 
        LEFT JOIN replies r 
        ON i.id = r.issue_id 
        AND r.id = (SELECT MAX(id) FROM replies WHERE issue_id = i.id) 
        WHERE i.sId = :id 
        ORDER BY r.id DESC";
        $query = $dbh->prepare($sql);
        $query->bindParam(':id', $id, PDO::PARAM_INT);
        $query->execute();
        $result = $query->fetchAll(PDO::FETCH_ASSOC);
        return $result;
    }

    function getQueriesAndReplies($id)
    {
        $dbh = $this->connect();
        $sql = "SELECT * FROM issues WHERE id = :id ORDER BY id DESC LIMIT 1";
        $query = $dbh->prepare($sql);
        $query->bindParam(':id', $id, PDO::PARAM_INT);
        $query->execute();
        $issues = $query->fetchAll(PDO::FETCH_ASSOC);

        foreach ($issues as &$issue) {
            $sql = "SELECT * FROM replies WHERE issue_id = :issueId";
            $query = $dbh->prepare($sql);
            $query->bindParam(':issueId', $issue['id'], PDO::PARAM_INT);
            $query->execute();
            $issue['replies'] = $query->fetchAll(PDO::FETCH_ASSOC);
        }

        foreach ($issues as $issue) {
            $issueId = $issue['id'];
            $sqlUpdate = "UPDATE issues SET user_read = 1 WHERE id = :issueId";
            $queryUpdate = $dbh->prepare($sqlUpdate);
            $queryUpdate->bindParam(':issueId', $issueId, PDO::PARAM_INT);
            $queryUpdate->execute();
        }

        return $issues;
    }

    function getUnread($id)
    {
        $dbh = $this->connect();

        $sql = "SELECT COUNT(*) AS unread_count FROM issues WHERE sId = :id AND user_read = 0";

        $query = $dbh->prepare($sql);

        $query->bindParam(':id', $id, PDO::PARAM_INT);

        $query->execute();

        $result = $query->fetch(PDO::FETCH_ASSOC);

        return $result['unread_count'];
    }

    function getTotalWalletTopup($userId)
    {
        $dbh = self::connect();
        $c = "SELECT SUM(amount) AS total_wallet_topup FROM transactions WHERE servicename = 'Wallet Topup' AND sId = ?";
        $queryC = $dbh->prepare($c);
        $queryC->bindParam(1, $userId, PDO::PARAM_INT);
        $queryC->execute();
        $result = $queryC->fetch(PDO::FETCH_ASSOC);
        $totalWalletTopup = $result['total_wallet_topup'];
        return json_encode($totalWalletTopup);
    }

    function TotalTransactions($userId)
    {
        $dbh = $this->connect();
        $sql = "SELECT COUNT(*) AS total_transactions FROM transactions WHERE sId = ?";
        $query = $dbh->prepare($sql);
        $query->bindParam(1, $userId, PDO::PARAM_INT);
        $query->execute();
        $result = $query->fetch(PDO::FETCH_ASSOC);
        return $result['total_transactions'];
    }

    function getTotalSpent($userId)
    {
        $dbh = self::connect();
        $query = "SELECT SUM(amount) AS total_spent FROM transactions WHERE sId = ?";
        $stmt = $dbh->prepare($query);
        $stmt->bindParam(1, $userId, PDO::PARAM_INT);
        $stmt->execute();
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        $totalSpent = $result['total_spent'];
        return $totalSpent;
    }

    function getTotalMoneyFundedWeekly($userId)
    {
        $dbh = self::connect();
        $currentDate = date('Y-m-d');
        $startOfWeek = date('Y-m-d', strtotime('monday this week', strtotime($currentDate)));
        $endOfWeek = date('Y-m-d', strtotime('sunday this week', strtotime($currentDate)));
        $query = "SELECT SUM(amount) AS total_money_funded FROM transactions WHERE servicename = 'Wallet Topup' AND sId = ? AND date BETWEEN ? AND ?";
        $statement = $dbh->prepare($query);
        $statement->bindParam(1, $userId, PDO::PARAM_INT);
        $statement->bindParam(2, $startOfWeek);
        $statement->bindParam(3, $endOfWeek);
        $statement->execute();
        $result = $statement->fetch(PDO::FETCH_ASSOC);
        $totalMoneyFunded = $result['total_money_funded'];
        return json_encode($totalMoneyFunded);
    }

    function getTotalMoneyFundedMonthly($userId)
    {
        $dbh = self::connect();
        $currentDate = date('Y-m-d');
        $startOfMonth = date('Y-m-01', strtotime($currentDate));
        $endOfMonth = date('Y-m-t', strtotime($currentDate));
        $query = "SELECT SUM(amount) AS total_money_funded FROM transactions WHERE servicename = 'Wallet Topup' AND sId = ? AND date BETWEEN ? AND ?";
        $statement = $dbh->prepare($query);
        $statement->bindParam(1, $userId, PDO::PARAM_INT);
        $statement->bindParam(2, $startOfMonth);
        $statement->bindParam(3, $endOfMonth);
        $statement->execute();
        $result = $statement->fetch(PDO::FETCH_ASSOC);
        $totalMoneyFunded = $result['total_money_funded'];
        return json_encode($totalMoneyFunded);
    }

    function getTotalDataSold($userId, $date)
    {
        $dbh = self::connect();
        $startDate = $date . ' 00:00:00';
        $endDate = $date . ' 23:59:59';

        $sqlDataSold = "SELECT SUM(amount) as totalDataSold FROM transactions WHERE servicename = 'Data' AND status = 0 AND sId = :userId AND date BETWEEN :startDate AND :endDate";
        $queryDataSold = $dbh->prepare($sqlDataSold);
        $queryDataSold->bindParam(':userId', $userId, PDO::PARAM_INT);
        $queryDataSold->bindParam(':startDate', $startDate, PDO::PARAM_STR);
        $queryDataSold->bindParam(':endDate', $endDate, PDO::PARAM_STR);
        $queryDataSold->execute();

        return $queryDataSold->fetch(PDO::FETCH_ASSOC)['totalDataSold'];
    }

    function getTotalDataGBSold($userId, $date)
    {
        $dbh = self::connect();
        $startDate = $date . ' 00:00:00';
        $endDate = $date . ' 23:59:59';

        $sqlDataGbSold = "SELECT dataTag FROM transactions WHERE servicename = 'Data' AND status = 0 AND sId = :userId AND date BETWEEN :startDate AND :endDate";
        $queryDataGbSold = $dbh->prepare($sqlDataGbSold);
        $queryDataGbSold->bindParam(':userId', $userId, PDO::PARAM_INT);
        $queryDataGbSold->bindParam(':startDate', $startDate, PDO::PARAM_STR);
        $queryDataGbSold->bindParam(':endDate', $endDate, PDO::PARAM_STR);
        $queryDataGbSold->execute();

        $dataTags = $queryDataGbSold->fetchAll(PDO::FETCH_ASSOC);
        $totalDataGbSoldGB = 0;

        foreach ($dataTags as $dataTag) {
            $dataValue = strtoupper($dataTag['dataTag']);
            if (strpos($dataValue, 'GB') !== false) {
                $totalDataGbSoldGB += floatval($dataValue);
            } elseif (strpos($dataValue, 'MB') !== false) {
                $totalDataGbSoldGB += floatval($dataValue) / 1000; // Convert MB to GB
            }
        }

        return $totalDataGbSoldGB;
    }

    function getTotalAirtime($userId, $date)
    {
        $dbh = self::connect();
        $startDate = $date . ' 00:00:00';
        $endDate = $date . ' 23:59:59';

        $sqlAirtime = "SELECT SUM(amount) as totalAirtime FROM transactions WHERE servicename = 'Airtime' AND status = 0 AND sId = :userId AND date BETWEEN :startDate AND :endDate";
        $queryAirtime = $dbh->prepare($sqlAirtime);
        $queryAirtime->bindParam(':userId', $userId, PDO::PARAM_INT);
        $queryAirtime->bindParam(':startDate', $startDate, PDO::PARAM_STR);
        $queryAirtime->bindParam(':endDate', $endDate, PDO::PARAM_STR);
        $queryAirtime->execute();

        return $queryAirtime->fetch(PDO::FETCH_ASSOC)['totalAirtime'];
    }

    function mtnStatus()
    {
        $dbh = $this->connect();
        $sql1 = "SELECT COUNT(status) AS success FROM (SELECT * FROM transactions WHERE servicedesc LIKE '%MTN%' AND status = 0 ORDER BY tId DESC LIMIT 20) AS first20";
        $query1 = $dbh->prepare($sql1);
        $query1->execute();
        $result1 = $query1->fetch(PDO::FETCH_ASSOC);
        $successStatus = $result1['success'];
        $sql2 = "SELECT COUNT(*) AS failed FROM (SELECT * FROM transactions ORDER BY tId DESC LIMIT 20) AS first20 WHERE servicedesc LIKE '%MTN%' AND status = 1";
        $query2 = $dbh->prepare($sql2);
        $query2->execute();
        $result2 = $query2->fetch(PDO::FETCH_ASSOC);
        $failedStatus = $result2['failed'];
        $finalStatus = $successStatus - $failedStatus;
        return $finalStatus;
    }

    function airtelStatus()
    {
        $dbh = $this->connect();
        $sql1 = "SELECT COUNT(status) AS success FROM (SELECT * FROM transactions WHERE servicedesc LIKE '%Airtel%' AND status = 0 ORDER BY tId DESC LIMIT 20) AS first20";
        $query1 = $dbh->prepare($sql1);
        $query1->execute();
        $result1 = $query1->fetch(PDO::FETCH_ASSOC);
        $successStatus = $result1['success'];
        $sql2 = "SELECT COUNT(*) AS failed FROM (SELECT * FROM transactions ORDER BY tId DESC LIMIT 20) AS first20 WHERE servicedesc LIKE '%Airtel%' AND status = 1";
        $query2 = $dbh->prepare($sql2);
        $query2->execute();
        $result2 = $query2->fetch(PDO::FETCH_ASSOC);
        $failedStatus = $result2['failed'];
        $finalStatus = $successStatus - $failedStatus;
        return $finalStatus;
    }

    function gloStatus()
    {
        $dbh = $this->connect();
        $sql1 = "SELECT COUNT(status) AS success FROM (SELECT * FROM transactions WHERE servicedesc LIKE '%Glo%' AND status = 0 ORDER BY tId DESC LIMIT 20) AS first20";
        $query1 = $dbh->prepare($sql1);
        $query1->execute();
        $result1 = $query1->fetch(PDO::FETCH_ASSOC);
        $successStatus = $result1['success'];
        $sql2 = "SELECT COUNT(*) AS failed FROM (SELECT * FROM transactions ORDER BY tId DESC LIMIT 20) AS first20 WHERE servicedesc LIKE '%Glo%' AND status = 1";
        $query2 = $dbh->prepare($sql2);
        $query2->execute();
        $result2 = $query2->fetch(PDO::FETCH_ASSOC);
        $failedStatus = $result2['failed'];
        $finalStatus = $successStatus - $failedStatus;
        return $finalStatus;
    }

    function ninemobileStatus()
    {
        $dbh = $this->connect();
        $sql1 = "SELECT COUNT(status) AS success FROM (SELECT * FROM transactions WHERE servicedesc LIKE '%9mobile%' AND status = 0 ORDER BY tId DESC LIMIT 20) AS first20";
        $query1 = $dbh->prepare($sql1);
        $query1->execute();
        $result1 = $query1->fetch(PDO::FETCH_ASSOC);
        $successStatus = $result1['success'];
        $sql2 = "SELECT COUNT(*) AS failed FROM (SELECT * FROM transactions ORDER BY tId DESC LIMIT 20) AS first20 WHERE servicedesc LIKE '%Ninemobile%' AND status = 1";
        $query2 = $dbh->prepare($sql2);
        $query2->execute();
        $result2 = $query2->fetch(PDO::FETCH_ASSOC);
        $failedStatus = $result2['failed'];
        $finalStatus = $successStatus - $failedStatus;
        return $finalStatus;
    }

    public function saveBeneficiary($userid, $network, $phone, $name)
    {
        $dbh = $this->connect();

        $sql_check = "SELECT COUNT(*) FROM databeneficiary WHERE sId = :id AND network = :network AND phone = :phone";
        $query_check = $dbh->prepare($sql_check);
        $query_check->bindParam(':id', $userid, PDO::PARAM_INT);
        $query_check->bindParam(':network', $network, PDO::PARAM_STR);
        $query_check->bindParam(':phone', $phone, PDO::PARAM_STR);
        $query_check->execute();
        $count = $query_check->fetchColumn();

        if ($count > 0) {
            $response = [];
            $response["status"] = "fail";
            return json_encode($response);
        } else {
            $sql_insert = "INSERT INTO databeneficiary SET sId=:id, network=:network, phone=:phone, name=:name";
            $query_insert = $dbh->prepare($sql_insert);
            $query_insert->bindParam(':id', $userid, PDO::PARAM_INT);
            $query_insert->bindParam(':network', $network, PDO::PARAM_STR);
            $query_insert->bindParam(':phone', $phone, PDO::PARAM_STR);
            $query_insert->bindParam(':name', $name, PDO::PARAM_STR);
            $result = $query_insert->execute();
            $response = [];
            $response["status"] = "success";

            return json_encode($response);
        }
    }

    //GEt Beneficary
    public function getBeneficiary($userid)
    {
        $dbh = $this->connect();
        $sql = "SELECT id, network, phone, name FROM databeneficiary WHERE sId = :id";
        $query = $dbh->prepare($sql);
        $query->bindParam(':id', $userid, PDO::PARAM_INT);
        $query->execute();
        $result = $query->fetchAll(PDO::FETCH_ASSOC);
        return $result;
    }

    public function saveBeneficiaryAirtime($userid, $network, $phone, $name)
    {
        $dbh = $this->connect();

        $sql_check = "SELECT COUNT(*) FROM airtimebeneficiary WHERE sId = :id AND network = :network AND phone = :phone";
        $query_check = $dbh->prepare($sql_check);
        $query_check->bindParam(':id', $userid, PDO::PARAM_INT);
        $query_check->bindParam(':network', $network, PDO::PARAM_STR);
        $query_check->bindParam(':phone', $phone, PDO::PARAM_STR);
        $query_check->execute();
        $count = $query_check->fetchColumn();

        if ($count > 0) {
            $response = [];
            $response["status"] = "fail";
            return $response;
        } else {
            $sql_insert = "INSERT INTO airtimebeneficiary SET sId=:id, network=:network, phone=:phone, name=:name";
            $query_insert = $dbh->prepare($sql_insert);

            $query_insert->bindParam(':id', $userid, PDO::PARAM_INT);
            $query_insert->bindParam(':network', $network, PDO::PARAM_STR);
            $query_insert->bindParam(':phone', $phone, PDO::PARAM_STR);
            $query_insert->bindParam(':name', $name, PDO::PARAM_STR);
            $result = $query_insert->execute();
            $response = [];
            $response["status"] = "success";

            return $response;
        }
    }

    //GEt Beneficary
    public function getAirtimeBeneficiary($userid)
    {
        $dbh = $this->connect();
        $sql = "SELECT id, network, phone, name FROM airtimebeneficiary WHERE sId = :id";
        $query = $dbh->prepare($sql);
        $query->bindParam(':id', $userid, PDO::PARAM_INT);
        $query->execute();
        $result = $query->fetchAll(PDO::FETCH_ASSOC);
        return $result;
    }

    public function saveBeneficiaryCable($userid, $network, $phone, $name)
    {
        $dbh = $this->connect();

        $sql_check = "SELECT COUNT(*) FROM cablebeneficiary WHERE sId = :id AND network = :network AND iucNumber = :iucNumber";
        $query_check = $dbh->prepare($sql_check);
        $query_check->bindParam(':id', $userid, PDO::PARAM_INT);
        $query_check->bindParam(':network', $network, PDO::PARAM_STR);
        $query_check->bindParam(':iucNumber', $phone, PDO::PARAM_STR);
        $query_check->execute();
        $count = $query_check->fetchColumn();

        if ($count > 0) {
            $response = [];
            $response["status"] = "fail";
            return json_encode($response);
        } else {
            $sql_insert = "INSERT INTO cablebeneficiary SET sId=:id, network=:network, iucNumber=:iucNumber, disconame=:disconame";
            $query_insert = $dbh->prepare($sql_insert);
            $query_insert->bindParam(':id', $userid, PDO::PARAM_INT);
            $query_insert->bindParam(':network', $network, PDO::PARAM_STR);
            $query_insert->bindParam(':iucNumber', $phone, PDO::PARAM_STR);
            $query_insert->bindParam(':disconame', $name, PDO::PARAM_STR);
            $result = $query_insert->execute();
            $response = [];
            $response["status"] = "success";

            return json_encode($response);
        }
    }

    //GEt Beneficary
    public function getCableBeneficiary($userid)
    {
        $dbh = $this->connect();
        $sql = "SELECT id, network, iucNumber, disconame FROM cablebeneficiary WHERE sId = :id";
        $query = $dbh->prepare($sql);
        $query->bindParam(':id', $userid, PDO::PARAM_INT);
        $query->execute();
        $result = $query->fetchAll(PDO::FETCH_ASSOC);
        return $result;
    }

    public function saveBeneficiaryElectricity($userid, $network, $phone, $name, $meterno)
    {
        $dbh = $this->connect();

        $sql_check = "SELECT COUNT(*) FROM electricitybeneficiary WHERE sId = :id AND network = :network AND meterNumber = :meterNumber";
        $query_check = $dbh->prepare($sql_check);
        $query_check->bindParam(':id', $userid, PDO::PARAM_INT);
        $query_check->bindParam(':network', $network, PDO::PARAM_STR);
        $query_check->bindParam(':meterNumber', $meterno, PDO::PARAM_STR);
        $query_check->execute();
        $count = $query_check->fetchColumn();

        if ($count > 0) {
            $response = [];
            $response["status"] = "fail";
            return $response;
        } else {
            $sql_insert = "INSERT INTO electricitybeneficiary SET sId=:id, network=:network, meterNumber=:meterNumber, disconame=:disconame, phoneNumber=:phoneNumber";
            $query_insert = $dbh->prepare($sql_insert);
            $query_insert->bindParam(':id', $userid, PDO::PARAM_INT);
            $query_insert->bindParam(':network', $network, PDO::PARAM_STR);
            $query_insert->bindParam(':meterNumber', $meterno, PDO::PARAM_STR);
            $query_insert->bindParam(':disconame', $name, PDO::PARAM_STR);
            $query_insert->bindParam(':phoneNumber', $phone, PDO::PARAM_STR);
            $result = $query_insert->execute();
            $response = [];
            $response["status"] = "success";

            return $response;
        }
    }

    //GEt Beneficary
    public function getElectricityBeneficiary($userid)
    {
        $dbh = $this->connect();
        $sql = "SELECT id, network, phoneNumber, meterNumber, disconame FROM electricitybeneficiary WHERE sId = :id";
        $query = $dbh->prepare($sql);
        $query->bindParam(':id', $userid, PDO::PARAM_INT);
        $query->execute();
        $result = $query->fetchAll(PDO::FETCH_ASSOC);
        return $result;
    }

    //Profile Info
    public function setProfileInfo($id)
    {
        $id = (float) $id;
        $dbh = self::connect();
        $sql = "SELECT * FROM subscribers WHERE sId = $id";
        $queryC = $dbh->prepare($sql);
        $queryC->execute();
        $result = $queryC->fetch(PDO::FETCH_OBJ);

        // Query to select count, names, and phone numbers of referrals
        $refCheck = "SELECT COUNT(sId) AS refCount, sFname,sLname, sPhone FROM subscribers WHERE sReferal=:ref GROUP BY sFname, sPhone";

        $queryR = $dbh->prepare($refCheck);
        $queryR->bindParam(':ref', $result->sPhone, PDO::PARAM_STR);
        $queryR->execute();
        $resultR = $queryR->fetchAll(PDO::FETCH_OBJ);

        // Initialize variables to store the referral details
        $referralNames = [];
        $referralPhones = [];
        $referralCounts = [];

        // Iterate over the result set and store each referral's details
        foreach ($resultR as $referral) {
            $referralNames[] = $referral->sFname . ' ' . $referral->sLname;
            $referralPhones[] = $referral->sPhone;
            $referralCounts[] = (float) $referral->refCount;
        }

        // Merge the referral details with the original result
        $result = (object) array_merge((array) $result, [
            'refName' => $referralNames,
            'refPhone' => $referralPhones,
            'refCount' => $referralCounts,
        ]);

        if ($result->sBankNo == "" || $result->sRolexBank == "" || $result->sFidelityBank == "") {
            $obj = new Account();

            //Get API Details
            $d = $this->getApiConfiguration();
            $monifyStatus = $this->getConfigValue($d, "monifyStatus");
            $monifyApi = $this->getConfigValue($d, "monifyApi");
            $monifySecrete = $this->getConfigValue($d, "monifySecrete");
            $monifyContract = $this->getConfigValue($d, "monifyContract");

            //If Monnify Is Active, Create Virtual Account For User
            if ($result->sBankNo == "" && $monifyStatus == "On") {
                //	$obj->createVirtualBankAccount($id,$result->sFname,$result->sLname,$result->sPhone,$result->sEmail,$monifyApi,$monifySecrete,$monifyContract);
            }

            //If Monnify Is Active, Create Virtual Account For User
            if ($result->sRolexBank == "" && $monifyStatus == "On") {
                //		$obj->createVirtualBankAccount2($id,$result->sFname,$result->sLname,$result->sPhone,$result->sEmail,$monifyApi,$monifySecrete,$monifyContract);
            }

            //If Monnify Is Active, Create Virtual Account For User
            if ($result->sFidelityBank == "" && $monifyStatus == "On") {
                //		$obj->createVirtualBankAccount3($id,$result->sFname,$result->sLname,$result->sPhone,$result->sEmail,$monifyApi,$monifySecrete,$monifyContract);
            }
        }

        return $result;
    }

    //Recover User Account
    public function recoverUserLogin($email)
    {
        //Verify Registration Details
        $dbh = self::connect();
        $c = "SELECT sId,sFname,sLname,sEmail,sPass FROM subscribers WHERE sEmail=:e";
        $queryC = $dbh->prepare($c);
        $queryC->bindParam(':e', $email, PDO::PARAM_STR);
        $queryC->execute();
        $result = $queryC->fetch(PDO::FETCH_OBJ);
        if ($queryC->rowCount() > 0) {
            //Genereate And Update Verification Code
            $varCode = mt_rand(2000, 9000);
            $stmt = "UPDATE subscribers SET sVerCode=$varCode WHERE sId=$result->sId";
            $query = $dbh->prepare($stmt);
            $query->execute();

            //Send Verification Code To User Email
            $email = $result->sEmail;
            $subject = "Account Recovery (" . $this->sitename . ")";
            $message = "<h3>Hi " . $result->sFname . ", You Recently Requested For A Password Recovery. Use The Verification Code \"" . $varCode . "\" To Recover Your Account. Thank You For Using " . $this->sitename . ".</h3>";
            $check = self::sendMail($email, $subject, $message);

            if ($check == 0) {
                return 0;
            } else {
                return 2;
            }
        } else {
            return 1;
        }
    }

    // Set User Deleted by updating sRegStatus to 4
    public function setUserDeleted($sId)
    {
        $dbh = self::connect();
        $stmt = "UPDATE subscribers SET sRegStatus = 4 WHERE sId = :sId";
        $query = $dbh->prepare($stmt);
        $query->bindParam(':sId', $sId, PDO::PARAM_INT);
        if ($query->execute()) {
            return json_encode(['status' => 'success', 'message' => 'User marked as deleted successfully']);
        } else {
            return json_encode(['status' => 'fail', 'message' => 'Failed to mark user as deleted']);
        }
    }

    public function updateKyc($id, $kyctype, $ref, $dob, $kycvalue, $fname, $lname, $phone, $email, $chargeAmount, $method)
    {
        $d = $this->getApiConfiguration();

        if ($this->getConfigValue($d, "kycStatus") !== "On") {
            if ($kyctype === "bvn") {
                $chargeAmount = $this->getConfigValue($d, "bvnCharge");
            } elseif ($kyctype === "nin") {
                $chargeAmount = $this->getConfigValue($d, "ninCharge");
            }
        }

        if ($method === "link") {
            $check = $this->updateKycLinking($id, $kyctype, $ref, $dob, $kycvalue, $fname, $lname, $phone, $email, $chargeAmount);
            return $check;
        } elseif ($method === "verif") {
            $check = $this->updateKycVerification($id, $kyctype, $ref, $dob, $kycvalue, $fname, $lname, $phone, $email, $chargeAmount);
            return $check;
        } else {
            return "lollo";
        }
    }

    public function getApi()
    {
        $d = $this->getApiConfiguration();
        return $d;
    }
    public function updateKycVerification($id, $kyctype, $ref, $dob, $kycvalue, $fname, $lname, $phone, $email, $amount)
    {
        $dbh = self::connect();

        $d = $this->getApiConfiguration();
        $a = $this->getSiteConfiguration();
        $monifyStatus = $this->getConfigValue($d, "monifyStatus");
        $monifyApi = $this->getConfigValue($d, "monifyApi");
        $monifySecrete = $this->getConfigValue($d, "monifySecrete");
        $monifyContract = $this->getConfigValue($d, "monifyContract");

        $theresponse = [];
        $apiKey = $monifyApi;
        $clientSecret = $monifySecrete;

        $credentials = $apiKey . ':' . $clientSecret;
        $base64Credentials = base64_encode($credentials);

        if ($kyctype === "BVN") {
            $sew = "bvn";
        } elseif ($kyctype === "NIN") {
            $sew = "nin";
        }

        $result = json_decode($this->{"verify$sew"}($kycvalue, $kyctype, $fname, $lname, $dob, $phone), true);

        if (isset($result["status"]) && $result["status"] == "fail") {
            return json_encode(["status" => 'fail', "msg" => $result["msg"]]);
        } else {
            if (strcasecmp($result["fname"], $fname) === 0 || strcasecmp($result["lname"], $lname) === 0 || (strcasecmp($result["fname"], $lname) === 0 || strcasecmp($result["lname"], $fname) === 0)) {
                $dataref = generateDataRef();
                $url = 'https://api.monnify.com/api/v1/auth/login';

                $ch = curl_init();
                curl_setopt($ch, CURLOPT_URL, $url);
                curl_setopt($ch, CURLOPT_POST, 1);
                curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
                curl_setopt($ch, CURLOPT_HTTPHEADER, ["accept: application/json", "authorization: Basic " . $base64Credentials, "cache-control: no-cache"]);

                $json = curl_exec($ch);
                curl_close($ch);

                $result = json_decode($json, true);
                $accessToken = $result['responseBody']['accessToken'];

                $curl = curl_init();
                if ($kyctype === "BVN") {
                    $sew = "bvn";
                } elseif ($kyctype === "NIN") {
                    $sew = "nin";
                }

                curl_setopt_array($curl, [
                    CURLOPT_URL => "https://api.monnify.com/api/v1/bank-transfer/reserved-accounts/$ref/kyc-info",
                    CURLOPT_RETURNTRANSFER => true,
                    CURLOPT_CUSTOMREQUEST => "PUT",
                    CURLOPT_POSTFIELDS => json_encode([
                        $sew => $kycvalue,
                    ]),
                    CURLOPT_HTTPHEADER => ["authorization: Bearer " . $accessToken, "content-type: application/json", "cache-control: no-cache"],
                ]);

                $response = curl_exec($curl);
                $err = curl_error($curl);

                curl_close($curl);

                if ($err) {
                    $theresponse["status"] = "fail";
                    $theresponse["msg"] = 'Curl Returned Error: ' . $err;
                    echo json_encode($theresponse);
                } else {
                    $tranx = json_decode($response, true);
                    if (!$tranx['requestSuccessful']) {
                        $theresponse["status"] = "fail";
                        $theresponse["msg"] = $tranx['responseMessage'];
                    } else {
                        if ($kyctype === "BVN") {
                            $amount = $amount;
                        } elseif ($kyctype === "NIN") {
                            $amount = $amount;
                        }
                        $debitresult = $this->debitkyc($id, $amount, $dataref);
                        if ($debitresult == 0) {
                            $accountInfo = $tranx['responseBody'][0];

                            $accountReference = $accountInfo['accountReference'];
                            $accountName = $accountInfo['accountName'];
                            $customerEmail = $accountInfo['customerEmail'];
                            $customerName = $accountInfo['customerName'];
                            $bvn = $accountInfo['bvn'];
                            $nin = $accountInfo['nin'];
                            $this->updateVerified($id);
                            $this->updateKycDetails($id, $kyctype, $dob, $kycvalue);

                            $theresponse["status"] = "success";
                            $theresponse["msg"] = $tranx['responseMessage'];
                        } else {
                            $theresponse["status"] = "fail";
                            $theresponse["msg"] = "Your balance is not sufficient to complete this request";
                        }
                    }

                    return json_encode(["status" => $theresponse["status"], "msg" => $theresponse["msg"]]);
                }
            } else {
                return json_encode([
                    "status" => 'fail',
                    "msg" => "Supplied $kyctype details does not match your account",
                ]);
            }
        }
    }

    //Update Profile Password
    public function updateKycLinking($id, $kyctype, $ref, $dob, $kycvalue, $fname, $lname, $phone, $email, $chargeAmount)
    {
        $dbh = self::connect();

        // Get API and Site Details
        $d = $this->getApiConfiguration();
        $a = $this->getSiteConfiguration();
        $monifyStatus = $this->getConfigValue($d, "monifyStatus");
        $monifyApi = $this->getConfigValue($d, "monifyApi");
        $monifySecrete = $this->getConfigValue($d, "monifySecrete");
        $monifyContract = $this->getConfigValue($d, "monifyContract");
        $adminEmail = $a->email;
        $amount = (float) $amount;
        $theresponse = [];
        $apiKey = $monifyApi;
        $clientSecret = $monifySecrete;

        $credentials = $apiKey . ':' . $clientSecret;
        $base64Credentials = base64_encode($credentials);

        function generateDataRef()
        {
            $randomString = bin2hex(random_bytes(8));
            $timestamp = time();
            $transref = "MONNIFY-" . $timestamp . "-" . $randomString;
            return $transref;
        }

        $dataref = generateDataRef();
        $url = 'https://api.monnify.com/api/v1/auth/login';

        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_POST, 1);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($ch, CURLOPT_HTTPHEADER, ["accept: application/json", "authorization: Basic " . $base64Credentials, "cache-control: no-cache"]);

        $json = curl_exec($ch);
        curl_close($ch);

        $result = json_decode($json, true);
        $accessToken = $result['responseBody']['accessToken'];

        $curl = curl_init();
        if ($kyctype === "BVN") {
            $sew = "bvn";
        } elseif ($kyctype === "NIN") {
            $sew = "nin";
        }
        curl_setopt_array($curl, [
            CURLOPT_URL => "https://api.monnify.com/api/v1/bank-transfer/reserved-accounts/$ref/kyc-info",
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_CUSTOMREQUEST => "PUT",
            CURLOPT_POSTFIELDS => json_encode([
                $sew => $kycvalue,
            ]),
            CURLOPT_HTTPHEADER => ["authorization: Bearer " . $accessToken, "content-type: application/json", "cache-control: no-cache"],
        ]);

        $response = curl_exec($curl);
        $err = curl_error($curl);

        curl_close($curl);

        if ($err) {
            $theresponse["status"] = "fail";
            $theresponse["msg"] = 'Curl Returned Error: ' . $err;
            return $theresponse;
        }

        $tranx = json_decode($response, true);
        if (!$tranx['requestSuccessful']) {
            $theresponse["status"] = "fail";
            $theresponse["msg"] = $tranx['responseMessage'];
        } else {
            $accountInfo = $tranx['responseBody'][0];

            $accountReference = $accountInfo['accountReference'];
            $accountName = $accountInfo['accountName'];
            $customerEmail = $accountInfo['customerEmail'];
            $customerName = $accountInfo['customerName'];
            $bvn = $accountInfo['bvn'];
            $nin = $accountInfo['nin'];

            $theresponse["status"] = "success";
            $theresponse["msg"] = $tranx['responseMessage'];
            $this->updateVerified($id);
            $this->updateKycDetails($id, $kyctype, $dob, $kycvalue);
        }

        echo json_encode(["status" => $theresponse["status"], "msg" => $theresponse["msg"]]);
    }

    public function insertKycDetails($id, $kyctype, $dob, $kycvalue)
    {
        $dbh = $this->connect();
        $theresponse = [];

        // Prepare the query to select existing details
        $c = "SELECT sBVN, sNIN, sDOB FROM subscribers WHERE sEmail=:email";
        $queryC = $dbh->prepare($c);
        $queryC->bindParam(':email', $id, PDO::PARAM_STR);
        $queryC->execute();
        $result = $queryC->fetch(PDO::FETCH_ASSOC);

        if ($result) {
            if ($kyctype === "BVN") {
                $sql = "UPDATE subscribers SET sBVN=:kycvalue, sDOB=:dob, sVerified='yes' WHERE sEmail=:email";
            } elseif ($kyctype === "NIN") {
                $sql = "UPDATE subscribers SET sNIN=:kycvalue, sDOB=:dob, sVerified='yes' WHERE sEmail=:email";
            } else {
                return ['status' => 'fail', 'msg' => 'Invalid KYC type'];
            }

            $query = $dbh->prepare($sql);
            $query->bindParam(':kycvalue', $kycvalue, PDO::PARAM_STR);
            $query->bindParam(':dob', $dob, PDO::PARAM_STR);
            $query->bindParam(':email', $id, PDO::PARAM_STR); // Bind the email
            $query->execute();

            $theresponse["status"] = "success";
            $theresponse["msg"] = "KYC UPDATED SUCCESSFULLY";
        } else {
            $theresponse["status"] = "fail";
            $theresponse["msg"] = "FAILED TO UPDATE KYC DETAIL";
        }

        return $theresponse;
    }

    public function updateVerified($id, $email = "yes")
    {
        $dbh = self::connect();
        $id = (int) $id;

        $sql = "UPDATE subscribers SET sVerified = :email WHERE sId = :id";
        $query = $dbh->prepare($sql);
        $query->bindParam(':email', $email, PDO::PARAM_STR);
        $query->bindParam(':id', $id, PDO::PARAM_INT);
        $query->execute();
    }

    public function updateKycDetails($id, $kyctype, $dob, $bvn, $nin)
    {
        $dbh = $this->connect();
        $id = (float) $id; // Ensure $id is a float

        // Retrieve existing KYC details
        $c = "SELECT sBVN, sNIN, sDOB FROM subscribers WHERE sId = :id";
        $queryC = $dbh->prepare($c);
        $queryC->bindParam(':id', $id, PDO::PARAM_STR);
        $queryC->execute();
        $result = $queryC->fetch(PDO::FETCH_ASSOC);

        if ($result) {
            $updateFields = []; // Array to hold fields to update
            $params = []; // Array to hold parameters for the SQL statement

            // Determine which field(s) to update based on the KYC type
            if ($kyctype === "bvn" && !empty($bvn)) {
                $updateFields[] = "sBVN = :kycvalue"; // Add BVN update field
                $params[':kycvalue'] = $bvn; // Set the value for BVN
            } elseif ($kyctype === "nin" && !empty($nin)) {
                $updateFields[] = "sNIN = :kycvalue"; // Add NIN update field
                $params[':kycvalue'] = $nin; // Set the value for NIN
            } else {
                return 1; // Invalid KYC type or empty value
            }

            // Always update DOB
            $updateFields[] = "sDOB = :dob"; // Add DOB update field
            $params[':dob'] = $dob; // Set the value for DOB

            // Build the SQL update statement
            $sql = "UPDATE subscribers SET " . implode(", ", $updateFields) . " WHERE sId = :id";
            $query = $dbh->prepare($sql);

            // Bind parameters for execution
            foreach ($params as $key => $value) {
                $query->bindValue($key, $value, PDO::PARAM_STR);
            }
            $query->bindParam(':id', $id, PDO::PARAM_STR); // Bind the ID

            // Execute the update query
            if ($query->execute()) {
                return 0; // Return 0 for success
            } else {
                return 2; // Return 2 for update failure
            }
        }

        return 3; // Return 3 if subscriber not found
    }

    //Update Profile Password
    public function verifynin($kycvalue, $kyctype, $fname, $lname, $dob, $phone)
    {
        $dbh = self::connect();

        // Get API and Site Details
        $d = $this->getApiConfiguration();
        $a = $this->getSiteConfiguration();
        $monifyApi = $this->getConfigValue($d, "monifyApi");
        $monifySecrete = $this->getConfigValue($d, "monifySecrete");
        $monifyContract = $this->getConfigValue($d, "monifyContract");
        $adminEmail = $a->email;

        $theresponse = [];
        $apiKey = $monifyApi;
        $clientSecret = $monifySecrete;

        $credentials = $apiKey . ':' . $clientSecret;
        $base64Credentials = base64_encode($credentials);
        function generateDataRef()
        {
            $randomString = bin2hex(random_bytes(8));
            $timestamp = time();
            $transref = "MONNIFY-" . $timestamp;
            return $transref;
        }

        $dataref = generateDataRef();
        $url = 'https://api.monnify.com/api/v1/auth/login';

        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_POST, 1);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($ch, CURLOPT_HTTPHEADER, ["accept: application/json", "authorization: Basic " . $base64Credentials, "cache-control: no-cache"]);

        $json = curl_exec($ch);
        curl_close($ch);

        $result = json_decode($json, true);
        $accessToken = $result['responseBody']['accessToken'];

        $curl = curl_init();
        if ($kyctype === "BVN") {
            $sew = "bvn";
        } elseif ($kyctype === "NIN") {
            $sew = "nin";
        }
        curl_setopt_array($curl, [
            CURLOPT_URL => "https://api.monnify.com/api/v1/vas/nin-details",
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_CUSTOMREQUEST => "POST",
            CURLOPT_POSTFIELDS => json_encode([
                $sew => $kycvalue,
            ]),
            CURLOPT_HTTPHEADER => ["authorization: Bearer " . $accessToken, "content-type: application/json", "cache-control: no-cache"],
        ]);

        $response = curl_exec($curl);
        $err = curl_error($curl);

        curl_close($curl);

        if ($err) {
            $theresponse["status"] = "fail";
            $theresponse["msg"] = 'Curl Returned Error: ' . $err;

            return json_encode(["status" => $theresponse["status"], "msg" => $theresponse["msg"]]);
        }

        $tranx = json_decode($response, true);

        if (!$tranx['requestSuccessful']) {
            $theresponse["status"] = "fail";
            $theresponse["msg"] = $tranx['responseMessage'];
        } else {
            $accountInfo = $tranx['responseBody'];

            $accountReference = $accountInfo['accountReference'];
            $lastName = $accountInfo['lastName'];
            $dateOfBirth = $accountInfo['dateOfBirth'];
            $firstName = $accountInfo['firstName'];
            $nin = $accountInfo['nin'];

            $theresponse["status"] = "success";
            $theresponse["msg"] = $tranx['responseMessage'];
        }

        return json_encode(["status" => $theresponse["status"], "msg" => $theresponse["msg"], "fname" => $firstName, "lname" => $lastName]);
    }

    //Update Profile Password
    public function verifybvn($kycvalue, $kyctype, $fname, $lname, $dob, $phone)
    {
        $dbh = self::connect();

        // Get API and Site Details
        $d = $this->getApiConfiguration();
        $a = $this->getSiteConfiguration();
        $monifyApi = $this->getConfigValue($d, "monifyApi");
        $monifySecrete = $this->getConfigValue($d, "monifySecrete");
        $monifyContract = $this->getConfigValue($d, "monifyContract");
        $adminEmail = $a->email;

        $theresponse = [];
        $apiKey = $monifyApi;
        $clientSecret = $monifySecrete;

        $credentials = $apiKey . ':' . $clientSecret;
        $base64Credentials = base64_encode($credentials);
        function generateDataRef()
        {
            $randomString = bin2hex(random_bytes(8));
            $timestamp = time();
            $transref = "MONNIFY-" . $timestamp . "-" . $randomString;
            return $transref;
        }

        $dataref = generateDataRef();
        $url = 'https://api.monnify.com/api/v1/auth/login';

        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_POST, 1);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($ch, CURLOPT_HTTPHEADER, ["accept: application/json", "authorization: Basic " . $base64Credentials, "cache-control: no-cache"]);

        $json = curl_exec($ch);
        curl_close($ch);

        $result = json_decode($json, true);
        $accessToken = $result['responseBody']['accessToken'];

        $curl = curl_init();
        if ($kyctype === "BVN") {
            $sew = "bvn";
        } elseif ($kyctype === "NIN") {
            $sew = "nin";
        }
        curl_setopt_array($curl, [
            CURLOPT_URL => "https://api.monnify.com/api/v1/vas/bvn-details-match",
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_CUSTOMREQUEST => "POST",
            CURLOPT_POSTFIELDS => json_encode([
                "bvn" => $kycvalue,
                "name" => $fname . " " . $lname,
                "dateOfBirth" => $dob,
                "mobileNo" => $phone,
            ]),
            CURLOPT_HTTPHEADER => ["authorization: Bearer " . $accessToken, "content-type: application/json", "cache-control: no-cache"],
        ]);

        $response = curl_exec($curl);
        $err = curl_error($curl);

        curl_close($curl);

        if ($err) {
            $theresponse["status"] = "fail";
            $theresponse["msg"] = 'Curl Returned Error: ' . $err;
            return json_encode(["status" => $theresponse["status"], "msg" => $theresponse["msg"]]);
        }

        $tranx = json_decode($response, true);
        // echo json_encode($tranx);

        if (!$tranx['requestSuccessful']) {
            $theresponse["status"] = "fail";
            $theresponse["msg"] = $tranx['responseMessage'];
        } else {
            $matchPercentage = $tranx['responseBody']['name']['matchPercentage'];

            if ($matchPercentage > 66) {
                $theresponse["status"] = "success";
                $theresponse["msg"] = $tranx['responseMessage'];
                $firstName = $fname;
                $lastName = $lname;
            } else {
                $accountInfo = $tranx['responseBody'];
                $theresponse["status"] = "success";
                $theresponse["msg"] = $tranx['responseMessage'];
                $matchPercentage = $tranx['responseBody']['name']['matchPercentage'];
            }
        }

        return json_encode(["status" => $theresponse["status"], "msg" => $theresponse["msg"], "fname" => $firstName, "lname" => $lastName]);
    }

    public function debitkyc($id, $amount, $ref)
    {
        $action = "Debit";
        $dbh = $this->connect();
        $sql = "SELECT * FROM subscribers WHERE sId = :e";
        $query = $dbh->prepare($sql);
        $query->bindParam(':e', $id, PDO::PARAM_STR);
        $query->execute();
        $results = $query->fetch(PDO::FETCH_OBJ);
        if ($query->rowCount() > 0) {
            $amount = (float) $amount;
            $oldbalance = (float) $results->sWallet;
            $userId = $results->sId;
            $fname = $results->sFname;
            $email = $results->sEmail;

            if ($amount > $oldbalance && $action == "Debit") {
                return 2;
            } else {
                if ($action == "Credit") {
                    $newbalance = $oldbalance + $amount;
                } elseif ($action == "Debit") {
                    $newbalance = $oldbalance - $amount;
                } else {
                    return 3;
                }

                $servicename = "Wallet {$action}";
                $servicedesc = "Wallet Debit of {$amount} for KYC Verification";
                $message = "Operation Successful. Account {$action}ed with N{$amount}. <br/> Old Balance Is: N" . number_format($oldbalance) . " <br/> New Balance Is: N" . number_format($newbalance) . ".";
                $status = 0;
                $date = date("Y-m-d H:i:s");

                //Record Transaction
                $sql2 = "INSERT INTO transactions SET sId=:user,transref=:ref,servicename=:sn,servicedesc=:sd,amount=:a,status=:s,oldbal=:ob,newbal=:nb,date=:d";
                $query2 = $dbh->prepare($sql2);
                $query2->bindParam(':user', $userId, PDO::PARAM_INT);
                $query2->bindParam(':ref', $ref, PDO::PARAM_STR);
                $query2->bindParam(':sn', $servicename, PDO::PARAM_STR);
                $query2->bindParam(':sd', $servicedesc, PDO::PARAM_STR);
                $query2->bindParam(':a', $amount, PDO::PARAM_STR);
                $query2->bindParam(':s', $status, PDO::PARAM_INT);
                $query2->bindParam(':ob', $oldbalance, PDO::PARAM_STR);
                $query2->bindParam(':nb', $newbalance, PDO::PARAM_STR);
                $query2->bindParam(':d', $date, PDO::PARAM_STR);
                $query2->execute();

                $lastInsertId = $dbh->lastInsertId();
                if ($lastInsertId) {
                    $response = [];
                    //Update Account Type & Balance
                    $sql3 = "UPDATE subscribers SET sWallet=:bal WHERE sId=:id";
                    $query3 = $dbh->prepare($sql3);
                    $query3->bindParam(':id', $userId, PDO::PARAM_INT);
                    $query3->bindParam(':bal', $newbalance, PDO::PARAM_STR);
                    if ($query3->execute()) {
                        $response["status"] = "success";
                        $response["msg"] = $message;
                    } else {
                        $response["status"] = "fail";
                        $response["msg"] = "Could Not Update Balance.";
                    }

                    //Send Email Notification
                    $subject = $servicename . " (" . $this->sitename . ")";
                    $message = "Hi " . $fname . ", This is to notify you that your account have been {$action}ed with N{$amount} for KYC verification. <br/>";
                    $message .= "<h3>Old Balance Is: N" . number_format($oldbalance) . " <br/> New Balance Is: N" . number_format($newbalance) . ".</h3>";
                    self::sendMail($email, $subject, $message);

                    return 0;
                }
            }
        } else {
            return 1;
        }
    }

    //Record Last Activity
    public function recordLastActivity($id)
    {
        $id = (float) $id;
        $date = date("Y-m-d H:i:s");
        $dbh = self::connect();

        //Check User Last Login

        $sqlA = "SELECT token FROM userlogin WHERE user = $id ORDER BY id DESC LIMIT 1";
        $queryA = $dbh->prepare($sqlA);
        $queryA->execute();
        $resultA = $queryA->fetch(PDO::FETCH_OBJ);

        //Validate User Login token
        $curentUserToken = $_SESSION["loginAccToken"];
        $userToken = $resultA->token;

        if ($curentUserToken != $userToken) {
            return 1; //Logout User Reponse Code
        }

        $sql = "UPDATE subscribers SET sLastActivity=:a WHERE sId = $id";
        $queryC = $dbh->prepare($sql);
        $queryC->bindParam(':a', $date, PDO::PARAM_STR);
        $queryC->execute();

        return 0;
    }

    public function displayHomeNotification()
    {
        $data = $this->getHomeNotification();
        $subject = addslashes(str_replace("\r", "", $data->subject));
        $subject = str_replace("\n", "", $subject);
        $message = addslashes(str_replace("\r", "", $data->message));
        $message = str_replace("\n", "", $message);
        //	return "swal('{$subject}','{$message}','info');";
        return json_encode(["subject" => $subject, "message" => $message]);
    }

    //Profile Info
    public function getRef($id)
    {
        $id = (float) $id;
        $dbh = self::connect();
        $sql = "SELECT * FROM subscribers WHERE sId = $id";
        $queryC = $dbh->prepare($sql);
        $queryC->execute();
        $result = $queryC->fetch(PDO::FETCH_OBJ);

        //Count Total Referal ForThe User
        $refCheck = "Select COUNT(sId) AS refCount FROM subscribers WHERE sReferal=:ref";
        $queryR = $dbh->prepare($refCheck);
        $queryR->bindParam(':ref', $result->sPhone, PDO::PARAM_STR);
        $queryR->execute();
        $resultR = $queryR->fetch(PDO::FETCH_OBJ);
        $refCount = (float) $resultR->refCount;
        $result = (object) array_merge((array) $result, ['refCount' => $refCount]);

        return $result->sAccountReference;
    }

    //Profile Info
    public function generateKudaAccount($id)
    {
        $id = (float) $id;
        $dbh = $this->connect();
        $sql = "SELECT * FROM subscribers WHERE sId = $id";
        $queryC = $dbh->prepare($sql);
        $queryC->execute();
        $result = $queryC->fetch(PDO::FETCH_OBJ);

        //Count Total Referal ForThe User
        $refCheck = "Select COUNT(sId) AS refCount FROM subscribers WHERE sReferal=:ref";
        $queryR = $dbh->prepare($refCheck);
        $queryR->bindParam(':ref', $result->sPhone, PDO::PARAM_STR);
        $queryR->execute();
        $resultR = $queryR->fetch(PDO::FETCH_OBJ);
        $refCount = (float) $resultR->refCount;
        $result = (object) array_merge((array) $result, ['refCount' => $refCount]);

        if ($result->sBankNo == "" || $result->sRolexBank == "" || $result->sFidelityBank == "") {
            $obj = new Account();

            //Get API Details
            $d = $this->getApiConfiguration();
            $monifyStatus = $this->getConfigValue($d, "monifyStatus");
            $monifyApi = $this->getConfigValue($d, "monifyApi");
            $monifySecrete = $this->getConfigValue($d, "monifySecrete");
            $monifyContract = $this->getConfigValue($d, "monifyContract");

            //If Monnify Is Active, Create Virtual Account For User
            if ($result->sBankNo == "" && $monifyStatus == "On") {
                $obj->createVirtualBankAccount($id, $result->sFname, $result->sLname, $result->sPhone, $result->sEmail, $result->sNIN, $result->sBVN, $monifyApi, $monifySecrete, $monifyContract);
            }

            //If Monnify Is Active, Create Virtual Account For User
            if ($result->sRolexBank == "" && $monifyStatus == "On") {
                $obj->createVirtualBankAccount2($id, $result->sFname, $result->sLname, $result->sPhone, $result->sEmail, $result->sNIN, $result->sBVN, $monifyApi, $monifySecrete, $monifyContract);
            }

            //If Monnify Is Active, Create Virtual Account For User
            if ($result->sFidelityBank == "" && $monifyStatus == "On") {
                $obj->createVirtualBankAccount3($id, $result->sFname, $result->sLname, $result->sPhone, $result->sEmail, $result->sNIN, $result->sBVN, $monifyApi, $monifySecrete, $monifyContract);
            }
        }

        return $result;
    }
    public function generateKudaAcount($id)
    {
        $id = (float) $id;
        $dbh = $this->connect();
        $sql = "SELECT * FROM subscribers WHERE sId = $id";
        $queryC = $dbh->prepare($sql);
        $queryC->execute();
        $result = $queryC->fetch(PDO::FETCH_OBJ);

        $obj = new Account();

        $d = $this->getApiConfiguration();
        $kudaStatus = $this->getConfigValue($d, "kudaStatus");
        $kudaEmail = $this->getConfigValue($d, "kudaEmail");
        $kudaApi = $this->getConfigValue($d, "kudaApi");

        $verificationType = null;
        $value = null;

        if ($result->sBVN != null) {
            $verificationType = '1';
            $value = $result->sBVN;
            $type = 'BVN';
        } elseif ($result->sNIN != null) {
            $verificationType = '2';
            $value = $result->sNIN;
            $type = 'NIN';
        }

        if ($verificationType !== null && $value !== null) {
            $response = $obj->generateKudaAccount($id, $result->sFname, $result->sLname, $result->sPhone, $result->sEmail, $verificationType, $value, $type, $kudaApi, $kudaEmail);
        }
        return "Kuda account already exists"; // Return a different message if the account already exists
    }

    //Profile Info
    public function getProfileInfo($id)
    {
        $id = (float) $id;
        $dbh = self::connect();
        $sql = "SELECT * FROM subscribers WHERE sId = $id";
        $queryC = $dbh->prepare($sql);
        $queryC->execute();
        $result = $queryC->fetch(PDO::FETCH_OBJ);

        //Count Total Referal ForThe User
        $refCheck = "Select COUNT(sId) AS refCount FROM subscribers WHERE sReferal=:ref";
        $queryR = $dbh->prepare($refCheck);
        $queryR->bindParam(':ref', $result->sPhone, PDO::PARAM_STR);
        $queryR->execute();
        $resultR = $queryR->fetch(PDO::FETCH_OBJ);
        $refCount = (float) $resultR->refCount;
        $result = (object) array_merge((array) $result, ['refCount' => $refCount]);

        if ($result->sBankNo == "" || $result->sRolexBank == "" || $result->sFidelityBank == "") {
            $obj = new Account();

            //Get API Details
            $d = $this->getApiConfiguration();
            $monifyStatus = $this->getConfigValue($d, "monifyStatus");
            $monifyApi = $this->getConfigValue($d, "monifyApi");
            $monifySecrete = $this->getConfigValue($d, "monifySecrete");
            $monifyContract = $this->getConfigValue($d, "monifyContract");
        }

        return $result;
    }

    //Profile Info
    public function getProfileInfos($id)
    {
        $id = (float) $id;
        $dbh = self::connect();
        $sql = "SELECT * FROM subscribers WHERE sId = $id";
        $queryC = $dbh->prepare($sql);
        $queryC->execute();
        $result = $queryC->fetch(PDO::FETCH_OBJ);

        //Count Total Referal ForThe User
        $refCheck = "Select COUNT(sId) AS refCount FROM subscribers WHERE sReferal=:ref";
        $queryR = $dbh->prepare($refCheck);
        $queryR->bindParam(':ref', $result->sPhone, PDO::PARAM_STR);
        $queryR->execute();
        $resultR = $queryR->fetch(PDO::FETCH_OBJ);
        $refCount = (float) $resultR->refCount;
        $result = (object) array_merge((array) $result, ['refCount' => $refCount]);

        if ($result->sBankNo == "" || $result->sRolexBank == "" || $result->sFidelityBank == "") {
            $obj = new Account();

            //Get API Details
            $d = $this->getApiConfiguration();
            $monifyStatus = $this->getConfigValue($d, "monifyStatus");
            $monifyApi = $this->getConfigValue($d, "monifyApi");
            $monifySecrete = $this->getConfigValue($d, "monifySecrete");
            $monifyContract = $this->getConfigValue($d, "monifyContract");

            //If Monnify Is Active, Create Virtual Account For User
            if ($result->sBankNo == "" && $monifyStatus == "On") {
                $obj->createVirtualBankAccount($id, $result->sFname, $result->sLname, $result->sPhone, $result->sEmail, $monifyApi, $monifySecrete, $monifyContract);
            }

            //If Monnify Is Active, Create Virtual Account For User
            if ($result->sRolexBank == "" && $monifyStatus == "On") {
                $obj->createVirtualBankAccount2($id, $result->sFname, $result->sLname, $result->sPhone, $result->sEmail, $monifyApi, $monifySecrete, $monifyContract);
            }

            //If Monnify Is Active, Create Virtual Account For User
            if ($result->sFidelityBank == "" && $monifyStatus == "On") {
                $obj->createVirtualBankAccount3($id, $result->sFname, $result->sLname, $result->sPhone, $result->sEmail, $monifyApi, $monifySecrete, $monifyContract);
            }
        }

        return $result;
    }

   // Profile Info
public function getVirtualAccount($id)
{
    $id = (float) $id;

    $dbh = self::connect();
    $sql = "SELECT * FROM subscribers WHERE sId = :id";
    $queryC = $dbh->prepare($sql);
    $queryC->bindParam(':id', $id, PDO::PARAM_INT);
    $queryC->execute();
    $result = $queryC->fetch(PDO::FETCH_OBJ);
    $response = [];

    if ($result) {
        // Check if user is verified
        if ($result->sVerified !== "yes") {
            $response["status"] = "error";
            $response["message"] = "User is not verified";
            return $response;
        }

        $obj = new Account();
        $d = $this->getApiConfiguration();
        $pocketfiOne = $this->getConfigValue($d, "PocketFiStatus");
        $pocketfiTwo = $this->getConfigValue($d, "PocketFiStatus2");
        $pocketfiThree = $this->getConfigValue($d, "PocketFiStatus3");

        if (empty($result->sPocketFiBank) && $pocketfiOne === "On") {
            $obj->generatePocketFiAccount($id, $result->sFname, $result->sLname, $result->sPhone, $result->sEmail, $result->sNIN, $result->sBVN);
        }
        if (empty($result->sPocketFiBank) && $pocketfiTwo === "On") {
            $obj->generatePocketFiAccount2($id, $result->sFname, $result->sLname, $result->sPhone, $result->sEmail, $result->sNIN, $result->sBVN);
        }
        if (empty($result->sPocketFiBank) && $pocketfiThree === "On") {
            $obj->generatePocketFiAccount3($id, $result->sFname, $result->sLname, $result->sPhone, $result->sEmail, $result->sNIN, $result->sBVN);
        }

        $response["status"] = "success";
        $response["message"] = "Success, your accounts will be generated shortly";
    } else {
        $response["status"] = "error";
        $response["message"] = "User not found";
    }

    return $response;
}


    public function setUserPin($id, $newKey)
    {
        $dbh = self::connect();
        $id = (int) $id;

        $sql = "UPDATE subscribers SET sPin = :p WHERE sId = :id";
        $query = $dbh->prepare($sql);
        $query->bindParam(':p', $newKey, PDO::PARAM_STR);
        $query->bindParam(':id', $id, PDO::PARAM_INT);
        $query->execute();

        // Check if any rows were affected by the update
        if ($query->rowCount() > 0) {
            $response = [];
            $response["status"] = "success";
        } else {
            return 1; // No rows were updated, indicating an error (e.g., invalid ID)
        }
    }

    public function setUserRechargePin($ref)
    {
        $dbh = self::connect();
        $sql = "SELECT * FROM rechargetokens WHERE tRef=:ref";
        $query = $dbh->prepare($sql);
        $query->bindParam(":ref", $ref, PDO::PARAM_STR);
        $query->execute();
        $results = $query->fetch(PDO::FETCH_OBJ);
        return $results;
    }
function sendFCMNotification($id, $title, $body) {
   
        $url = 'https://dannvtu.com.ng/fcm/annieai/try.php';

        $apiKey = "8TW9T9F9VFLSPE00T9";
        $data = [
            'fcmToken' => $id,
            'title' => $title,
            'body' => $body,
        ];

        $headers = ['Authorization: Basic ' . $apiKey, 'Content-Type: application/json'];

        $ch = curl_init($url);
        curl_setopt($ch, CURLOPT_POST, 1);
        curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($data));
        curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

        $response = curl_exec($ch);

        if (curl_errno($ch)) {
            echo 'Curl error: ' . curl_error($ch);
        }

        curl_close($ch);

        return $response;
}

    function sendFCMNotifications($id, $title, $body)
    {
        // Connect to the database
        $dbh = self::connect();

        // Sanitize and cast the user ID
        $id = (int) $id;

        // Fetch FCM token for the user from the database
        $sql = "SELECT sFCM FROM subscribers WHERE sId = :id";
        $query = $dbh->prepare($sql);
        $query->bindParam(':id', $id, PDO::PARAM_INT);
        $query->execute();

        $result = $query->fetch(PDO::FETCH_ASSOC);

        // Check if user and FCM token are found
        if ($result && isset($result["sFCM"])) {
            $deviceToken = $result["sFCM"];

            // FCM server details
            $url = 'https://fcm.googleapis.com/fcm/send';
            $serverKey = 'AAAAxw3aQoE:APA91bHkh0SxORKUOppaOzHpinhiU9IcGzIKFIPAqRtCaec9INbJ1B4N_khtDxuRYPpqSoQyB1ilmBugPN3JwcZvuznFCE2RsuwM8-4yp5qG073S5CRswtQG4dcIHCW4pNeu7E2nBuix';

            // Notification data
            $data = [
                'to' => $deviceToken,
                'notification' => [
                    'title' => $title,
                    'body' => $body,
                ],
            ];

            // Headers for the request
            $headers = ['Authorization: key=' . $serverKey, 'Content-Type: application/json'];

            // Initialize cURL session
            $ch = curl_init($url);
            curl_setopt($ch, CURLOPT_POST, 1);
            curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($data));
            curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

            // Execute cURL session
            $response = curl_exec($ch);

            // Check for cURL errors
            if (curl_errno($ch)) {
                echo 'Curl error: ' . curl_error($ch);
            }

            // Close cURL session
            curl_close($ch);

            // Close database connection
            $dbh = null;

            return $response;
        } else {
            // User not found or no FCM token available
            return $response;
        }
    }

    public function getUserWallet($id)
    {
        $dbh = self::connect();
        $id = (int) $id;

        $sql = "SELECT * FROM subscribers WHERE sId = :id";
        $query = $dbh->prepare($sql);
        $query->bindParam(':id', $id, PDO::PARAM_INT);
        $query->execute();

        $result = $query->fetch(PDO::FETCH_ASSOC);

        if ($result) {
            $response = [];
            $response["status"] = "success";
            $response["value"] = $result["sWallet"];
            $response["commission"] = $result["sRefWallet"];
            $response["cashback_balance"] = $result["sCashBack"];
            $response["total_withdrawn_cashback"] = $result["sCashBack"];
            $response["total_withdrawn_referral"] = $result["sCashBack"];
            $response["sum_total"] = $result["sCashBack"];
            return $response;
        } else {
            return null; // User not found or no wallet information available
        }
    }

    public function verifyUserPin($id, $pin)
    {
        $dbh = self::connect();
        $id = (int) $id;

        // Fetch user information including the sPin (assuming PIN is stored in sPin)
        $sql = "SELECT sPin FROM subscribers WHERE sId = :id";
        $query = $dbh->prepare($sql);
        $query->bindParam(':id', $id, PDO::PARAM_INT);
        $query->execute();

        $result = $query->fetch(PDO::FETCH_ASSOC);

        if ($result) {
            // Check if the provided PIN matches the stored PIN
            if ($result['sPin'] == $pin) {
                $response = [];
                $response["status"] = "success";
                $response["message"] = "PIN verification successful";
                return $response;
            } else {
                // Incorrect PIN
                $response = [];
                $response["status"] = "fail";
                $response["message"] = "Incorrect PIN";
                return $response;
            }
        } else {
            // User not found
            $response = [];
            $response["status"] = "fail";
            $response["message"] = "User not found";
            return $response;
        }
    }
    
    // Inside your SubscriberModel class
public function getUserTransactionOptions($userId, $options = []) 
{
    try {
        $dbh = self::connect();

        // Default options
        $defaults = [
            'page'         => 1,
            'per_page'     => 50,
            'start_date'   => null,
            'end_date'     => null,
            'servicename'  => null,
            'status'       => null,
            'transref'     => null,
            'search'       => null,
            'sort_by'      => 'date',
            'sort_order'   => 'DESC',
            'with_analysis'=> false  // Set true if you want summary stats
        ];

        $opt = array_merge($defaults, $options);

        // Validate & sanitize
        $userId = (int)$userId;
        $opt['page'] = max(1, (int)$opt['page']);
        $opt['per_page'] = min(500, max(10, (int)$opt['per_page'])); // Max 500 per page
        $offset = ($opt['page'] - 1) * $opt['per_page'];

        // Allowed sort columns
        $allowedSort = ['date', 'amount', 'status', 'servicename', 'transref', 'tId'];
        $sortBy = in_array($opt['sort_by'], $allowedSort) ? $opt['sort_by'] : 'date';
        $sortOrder = strtoupper($opt['sort_order']) === 'ASC' ? 'ASC' : 'DESC';

        // Build WHERE conditions
        $where = ["sId = :userId"];
        $params = [':userId' => $userId];

        if (!empty($opt['start_date'])) {
            $where[] = "date >= :start_date";
            $params[':start_date'] = $opt['start_date'] . ' 00:00:00';
        }
        if (!empty($opt['end_date'])) {
            $where[] = "date <= :end_date";
            $params[':end_date'] = $opt['end_date'] . ' 23:59:59';
        }
        if (!empty($opt['servicename'])) {
            $where[] = "servicename = :servicename";
            $params[':servicename'] = $opt['servicename'];
        }
        if ($opt['status'] !== null && $opt['status'] !== '') {
            $where[] = "status = :status";
            $params[':status'] = (int)$opt['status'];
        }
        if (!empty($opt['transref'])) {
            $where[] = "transref LIKE :transref";
            $params[':transref'] = '%' . $opt['transref'] . '%';
        }
        if (!empty($opt['search'])) {
            $search = '%' . trim($opt['search']) . '%';
            $where[] = "(transref LIKE :search OR servicedesc LIKE :search OR servicename LIKE :search)";
            $params[':search'] = $search;
        }

        $whereClause = !empty($where) ? 'WHERE ' . implode(' AND ', $where) : '';

        // Main query for transactions
        $sql = "SELECT * FROM transactions 
                $whereClause 
                ORDER BY $sortBy $sortOrder 
                LIMIT :limit OFFSET :offset";

        $stmt = $dbh->prepare($sql);

        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
        }
        $stmt->bindValue(':limit', $opt['per_page'], PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);

        $stmt->execute();
        $transactions = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Optional: Post-process to extract PIN / Token
        foreach ($transactions as &$tx) {
            if ($tx['api_response_log']) {
                $log = json_decode($tx['api_response_log'], true);
                if ($tx['servicename'] === 'Exam Pin' && isset($log['pins'])) {
                    $tx['pin'] = trim($log['pins']);
                } elseif ($tx['servicename'] === 'Electricity Bill' && isset($log['data']['transaction']['meter_token'])) {
                    $tx['token'] = $log['data']['transaction']['meter_token'];
                }
            }
            // Clean up heavy fields if not needed
            unset($tx['api_response_log'], $tx['api_response']);
        }
        unset($tx);

        // Get total count for pagination
        $countSql = "SELECT COUNT(*) FROM transactions $whereClause";
        $countStmt = $dbh->prepare($countSql);
        foreach ($params as $key => $value) {
            $countStmt->bindValue($key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
        }
        $countStmt->execute();
        $total = (int)$countStmt->fetchColumn();

        // Optional: Analytics summary
        $analysis = null;
        if ($opt['with_analysis']) {
            $analysisSql = "SELECT 
                COUNT(*) as total_transactions,
                SUM(amount) as total_spent,
                SUM(profit) as total_profit,
                servicename,
                COUNT(*) as service_count
                FROM transactions $whereClause
                GROUP BY servicename WITH ROLLUP";

            $aStmt = $dbh->prepare($analysisSql);
            foreach ($params as $key => $value) {
                $aStmt->bindValue($key, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
            }
            $aStmt->execute();
            $raw = $aStmt->fetchAll(PDO::FETCH_ASSOC);

            $analysis = [
                'total_transactions' => 0,
                'total_spent' => 0,
                'total_profit' => 0,
                'by_service' => []
            ];

            foreach ($raw as $row) {
                if ($row['servicename'] === null) {
                    // This is the ROLLUP total row
                    $analysis['total_transactions'] = (int)$row['total_transactions'];
                    $analysis['total_spent'] = (float)$row['total_spent'];
                    $analysis['total_profit'] = (float)$row['total_profit'];
                } else {
                    $analysis['by_service'][] = [
                        'servicename' => $row['servicename'],
                        'count' => (int)$row['service_count'],
                        'spent' => (float)$row['total_spent'],
                        'profit' => (float)$row['total_profit']
                    ];
                }
            }
        }

        return [
            'data' => $transactions,
            'pagination' => [
                'current_page' => $opt['page'],
                'per_page' => $opt['per_page'],
                'total' => $total,
                'total_pages' => ceil($total / $opt['per_page'])
            ],
            'analysis' => $analysis
        ];

    } catch (PDOException $e) {
        error_log("Transaction Query Error: " . $e->getMessage());
        return ['error' => 'Database error occurred'];
    }
}

    public function getUserTransaction($id)
    {
        try {
            $dbh = self::connect();
            $id = (int) $id;
            $limit = 80;
            $offset = 0;

            // Add LIMIT and OFFSET to the query
            $sql = "SELECT * FROM transactions WHERE sId = :id ORDER BY date DESC LIMIT :limit OFFSET :offset";
            $query = $dbh->prepare($sql);
            $query->bindParam(':id', $id, PDO::PARAM_INT);
            $query->bindParam(':limit', $limit, PDO::PARAM_INT);
            $query->bindParam(':offset', $offset, PDO::PARAM_INT);

            $query->execute();

            $results = $query->fetchAll(PDO::FETCH_ASSOC);

            if ($results) {
                return $results;
            } else {
                return null; // No transactions found
            }
        } catch (PDOException $e) {
            return 'SQL Error: ' . $e->getMessage();
        }
    }
public function getUserTransactionMore($id, $page)
{
    try {
        $dbh = self::connect();
        $id = (int) $id;
        $limit = 80; // The number of transactions per page

        // Calculate the OFFSET based on the page number
        $page = (int) $page; // Ensure it's an integer
        $offset = ($page - 1) * $limit;

        // Add LIMIT and OFFSET to the query
        $sql = "SELECT * FROM transactions WHERE sId = :id ORDER BY date DESC LIMIT :limit OFFSET :offset";
        $query = $dbh->prepare($sql);
        $query->bindParam(':id', $id, PDO::PARAM_INT);
        $query->bindParam(':limit', $limit, PDO::PARAM_INT);
        $query->bindParam(':offset', $offset, PDO::PARAM_INT);

        $query->execute();

        $results = $query->fetchAll(PDO::FETCH_ASSOC);

        if ($results) {
            return $results;
        } else {
            return null; // No transactions found
        }
    } catch (PDOException $e) {
        return 'SQL Error: ' . $e->getMessage();
    }
}

    public function getUserTransactionFilter($id, $serviceName, $date1, $date2)
    {
        $dbh = self::connect();
        $id = (int) $id;

        $startdate = $date1 . " 00:00:00";
        $enddate = $date2 . " 23:59:59";

        // Base SQL query
        $sql = "SELECT * FROM transactions WHERE sId = :id";

        // Check if both startdate and enddate are provided, and append to the SQL query
        if (!empty($startdate) && !empty($enddate)) {
            $sql .= " AND date BETWEEN :startdate AND :enddate";
        }

        // Check if serviceName is provided and not equal to 'all', then append to the SQL query
        if (!empty($serviceName) && strtolower($serviceName) !== 'All') {
            $sql .= " AND servicename = :serviceName";
        }

        // Prepare the query
        $query = $dbh->prepare($sql);
        $query->bindParam(':id', $id, PDO::PARAM_INT);

        // Bind startdate and enddate if provided
        if (!empty($startdate) && !empty($enddate)) {
            $query->bindParam(':startdate', $startdate, PDO::PARAM_STR);
            $query->bindParam(':enddate', $enddate, PDO::PARAM_STR);
        }

        // Bind serviceName if provided and not 'all'
        if (!empty($serviceName) && strtolower($serviceName) !== 'All') {
            $query->bindParam(':serviceName', $serviceName, PDO::PARAM_STR);
        }

        // Execute the query
        $query->execute();

        // Fetch results
        $results = $query->fetchAll(PDO::FETCH_ASSOC);

        if ($results) {
            $response = [];
            foreach ($results as $result) {
                $response[] = [
                    "status" => "success",
                    "id" => $result["tId"],
                    "user_id" => $result["sId"],
                    "amount" => $result["amount"],
                    "date" => $result["date"],
                    "servicename" => $result["servicename"],
                    "status" => $result["status"],
                ];
            }
            return $results;
        } else {
            return null; // No transactions found
        }
    }

    public function getQexTransaction($id)
    {
        $dbh = self::connect();
        $id = (int) $id;

        $sql = "SELECT * FROM payment_transactions WHERE sId = :id ORDER BY date DESC";
        $query = $dbh->prepare($sql);
        $query->bindParam(':id', $id, PDO::PARAM_INT);
        $query->execute();

        $results = $query->fetchAll(PDO::FETCH_ASSOC);

        if ($results) {
            $response = [];
            $response["status"] = "success";
            $response["id"] = $result["tId"];
            $response["user_id"] = $result["sId"];
            $response["transref"] = $result["transactionRef"];
            $response["servicename"] = $result["servicename"];
            $response["servicedesc"] = $result["servicedesc"];
            $response["amount"] = $result["amount"];
            $response["status"] = $result["status"];
            $response["oldbal"] = $result["oldbal"];
            $response["newbal"] = $result["newbal"];
            $response["date"] = $result["date"];
            $response["sendername"] = $result["sendername"];
            $response["recipient_name"] = $result["recipient_name"];
            $response["recipient_acct"] = $result["recipient_acct"];
            $response["recipient_bank"] = $result["recipient_bank"];
            $response["sender_acct"] = $result["sender_acct"];
            $response["session_id"] = $result["session_id"];
            $response["profit"] = $result["profit"];
            $response["fee"] = $result["fee"];
            $response["api_response"] = $result["api_response"];
            $response["api_response_log"] = $result["api_response_log"];
            return $results;
        } else {
            return null; // User not found or no wallet information available
        }
    }

    public function getQexTransactionn($id)
    {
        $dbh = self::connect();
        $id = (int) $id;

        $sql = "SELECT * FROM payment_transactions WHERE sId = :id ORDER BY date DESC";
        $query = $dbh->prepare($sql);
        $query->bindParam(':id', $id, PDO::PARAM_INT);
        $query->execute();

        $results = $query->fetchAll(PDO::FETCH_ASSOC);

        if ($results) {
            $transaction = [];
            $transaction["status"] = "success";

            foreach ($results as $result) {
                $transaction["id"] = $result["tId"];
                $transaction["user_id"] = $result["sId"];
                $transaction["transref"] = $result["transref"];
                $transaction["servicename"] = $result["servicename"];
                $transaction["servicedesc"] = $result["servicedesc"];
                $transaction["amount"] = $result["amount"];
                $transaction["status"] = $result["status"];
                $transaction["oldbal"] = $result["oldbal"];
                $transaction["newbal"] = $result["newbal"];
                $transaction["date"] = $result["date"];
                $transaction["sendername"] = $result["sendername"];
                $transaction["recipient_name"] = $result["recipient_name"];
                $transaction["recipient_acct"] = $result["recipient_acct"];
                $transaction["recipient_bank"] = $result["recipient_bank"];
                $transaction["sender_acct"] = $result["sender_acct"];
                $transaction["session_id"] = $result["session_id"];
                $transaction["profit"] = $result["profit"];
                $transaction["fee"] = $result["fee"];
                $transaction["api_response"] = $result["api_response"];
                $transaction["api_response_log"] = $result["api_response_log"];
            }

            return $transaction;
        } else {
            return ["status" => "error", "message" => "No transactions found for the user."];
        }
    }

    public function setUserEmail($id, $email)
    {
        $dbh = self::connect();
        $id = (int) $id;

        $sql = "UPDATE subscribers SET sEmail = :p WHERE sId = :id";
        $query = $dbh->prepare($sql);
        $query->bindParam(':p', $email, PDO::PARAM_STR);
        $query->bindParam(':id', $id, PDO::PARAM_INT);
        $query->execute();

        // Check if any rows were affected by the update
        if ($query->rowCount() > 0) {
            $response = [];
            $response["status"] = "success";

            return $response;
        } else {
            return 1; // No rows were updated, indicating an error (e.g., invalid ID)
        }
    }

   public function setUserFcm($id, $key)
{
    $dbh = self::connect();
    $id = (int) $id;

    // Always update the FCM key
    $sql = "UPDATE subscribers SET sFcm = :p WHERE sId = :id";
    $query = $dbh->prepare($sql);
    $query->bindParam(':p', $key, PDO::PARAM_STR);
    $query->bindParam(':id', $id, PDO::PARAM_INT);
    $query->execute();

    if ($query->rowCount() > 0) {
        // If rows were affected, update was successful
        $response = [];
        $response["status"] = "success";
        return $response;
    } else {
        // If no rows were affected, return a success status as well
        // since this means the key might have been already the same
        $response = [];
        $response["status"] = "no change";
        return $response;
    }
}


    //Update Profile Password
    public function setUserPassword($id, $oldKey, $newKey)
    {
        $dbh = self::connect();
        $id = (float) $id;
        $hash = substr(sha1(md5($oldKey)), 3, 10);
        $hash2 = substr(sha1(md5($newKey)), 3, 10);

        $c = "SELECT sPass FROM subscribers WHERE sPass=:p AND sId=$id";
        $queryC = $dbh->prepare($c);
        $queryC->bindParam(':p', $hash, PDO::PARAM_STR);
        $queryC->execute();
        $result = $queryC->fetch(PDO::FETCH_ASSOC);

        if ($queryC->rowCount() > 0) {
            $sql = "UPDATE subscribers SET sPass=:p WHERE sId=$id";
            $query = $dbh->prepare($sql);
            $query->bindParam(':p', $hash2, PDO::PARAM_STR);
            $query->execute();
            $response = [];
            $response["status"] = "success";
            return $response;
        } else {
            return 1;
        }
    }
    public function setUserBio($id, $fname, $lname)
    {
        $dbh = self::connect();

        // Check if the user exists based on ID
        $checkUserQuery = "SELECT * FROM subscribers WHERE sId = :id";
        $checkUserStmt = $dbh->prepare($checkUserQuery);
        $checkUserStmt->bindParam(':id', $id, PDO::PARAM_INT);
        $checkUserStmt->execute();

        // Fetch the user details for response
        $result = $checkUserStmt->fetch(PDO::FETCH_OBJ);

        if ($checkUserStmt->rowCount() > 0) {
            // User exists, update the information
            $updateQuery = "UPDATE subscribers SET sFname=:fname, sLname=:lname WHERE sId=:id";
            $updateStmt = $dbh->prepare($updateQuery);
            $updateStmt->bindParam(':id', $id, PDO::PARAM_INT);
            $updateStmt->bindParam(':fname', $fname, PDO::PARAM_STR);
            $updateStmt->bindParam(':lname', $lname, PDO::PARAM_STR);
            $updateStmt->execute();

            // Re-fetch the updated user details
            $updatedUserQuery = "SELECT * FROM subscribers WHERE sId = :id";
            $updatedUserStmt = $dbh->prepare($updatedUserQuery);
            $updatedUserStmt->bindParam(':id', $id, PDO::PARAM_INT);
            $updatedUserStmt->execute();
            $updatedResult = $updatedUserStmt->fetch(PDO::FETCH_OBJ);

            $response = [];
            $response["status"] = "success";

            // Include updated user information in the response
            $response["Id"] = strval($updatedResult->sId);
            $response["name"] = $updatedResult->sFname . " " . $updatedResult->sLname;
            $response["fname"] = $updatedResult->sFname;
            $response["lname"] = $updatedResult->sLname;
            $response["phone"] = $updatedResult->sPhone;
            $response["email"] = $updatedResult->sEmail;
            $response["pin"] = strval($updatedResult->sPin);
            $response["date"] = $updatedResult->sRegDate;
            $response["image"] = $updatedResult->sImage;
            $response["address"] = $updatedResult->sAddress;
            $response["account_number"] = $updatedResult->sRolexBank;
            $response["account_name"] = $updatedResult->sFname;
            $response["age"] = $updatedResult->sAge;
            $response["sex"] = $updatedResult->sSex;
            $response["isbank_verify"] = $updatedResult->sVerified;
            $response["username"] = $updatedResult->SUsername;
            $response["pin_set"] = isset($updatedResult->sPin) ? "yes" : "no";

            return $response;
        } else {
            // User does not exist
            $response = [];
            $response["status"] = "error";
            $response["message"] = "User not found.";
            return $response;
        }
    }

    public function setUser($id)
    {
        $dbh = self::connect();
        $id = (int) $id;

        // Check if the user exists based on ID
        $checkUserQuery = "SELECT * FROM subscribers WHERE sId = :id";
        $checkUserStmt = $dbh->prepare($checkUserQuery);
        $checkUserStmt->bindParam(':id', $id, PDO::PARAM_INT);
        $checkUserStmt->execute();

        // Fetch the user details for response
        $result = $checkUserStmt->fetch(PDO::FETCH_OBJ);

        if ($checkUserStmt->rowCount() > 0) {
            $response = [];
            $response["status"] = "success";

            // Include additional user information in the response
            $response["Id"] = strval($result->sId);
            $response["name"] = $result->sFname . " " . $result->sLname;
            $response["fname"] = $result->sFname;
            $response["lname"] = $result->sLname;
            $response["phone"] = $result->sPhone;
            $response["email"] = $result->sEmail;
 $response["balance"] = $result->sWallet;
            $response["pin"] = strval($result->sPin);
            $response["date"] = $result->sRegDate;
            $response["image"] = $result->sImage ?? "https://dannvtu.com.ng/userimages/default.jpg";
            $response["address"] = $result->sAddress;
            $response["account_number"] = $result->sRolexBank;
            $response["user_type"] = strval($result->sType);
            $response["refwallet"] = $result->sRefWallet;
            $response["account_name"] = $result->sFname;
            $response["age"] = $result->sAge;
            $response["sex"] = $result->sSex;
            $response["api_key"] = $result->sApiKey;
            $response["isbank_verify"] = $result->sVerified;
            $response["vercode"] = strval($result->sVerCode);
            $response["moniepoint"] = $result->sRolexBank;
            $response["payvessel"] = $result->sPayVessel; // Check if this conversion is appropriate
            $response["wema"] = $result->sBankNo;
            $response["kuda"] = strval($result->sKuda);
            $response["username"] = $result->SUsername;
            $response["pin_set"] = isset($response["pin"]) ? "yes" : "no";

            return $response;
        } else {
            // User does not exist
            $response = [];
            $response["status"] = "error";
            $response["message"] = "User not found.";
            return $response;
        }
    }

    public function setUserNewPassword($id, $code, $key)
    {
        // Step 1: Connect to the database
        $dbh = self::connect();

        // Step 2: Fetch the current sVerCode for the given email (sEmail)
        $querySelect = "SELECT sVerCode FROM subscribers WHERE sEmail = :e";
        $queryS = $dbh->prepare($querySelect);
        $queryS->bindParam(':e', $id, PDO::PARAM_STR);
        $queryS->execute();
        $result = $queryS->fetch(PDO::FETCH_ASSOC);

        // Check if the subscriber exists and if the sVerCode matches
        if ($result && $result['sVerCode'] == $code) {
            // Step 3: If the sVerCode matches, update the password and sVerCode
            $hash = substr(sha1(md5($key)), 3, 10); // Hash the new password
            $verCode = mt_rand(1000, 9999); // Generate a new sVerCode

            $queryUpdate = "UPDATE subscribers SET sPass = :k, sVerCode = :v WHERE LOWER(sEmail) = :e AND sVerCode = :c";
            $queryU = $dbh->prepare($queryUpdate);
            $queryU->bindParam(':e', $id, PDO::PARAM_STR);
            $queryU->bindParam(':c', $code, PDO::PARAM_STR);
            $queryU->bindParam(':k', $hash, PDO::PARAM_STR);
            $queryU->bindParam(':v', $verCode, PDO::PARAM_INT);

            if ($queryU->execute()) {
                return 0; // Success
            } else {
                return 1; // Failed to update password
            }
        } else {
            return json_encode($result); // sVerCode does not match or subscriber not found
        }
    }

    //Recover Seller Account
    public function setUserNnewPasswonrd($id, $code, $key)
    {
        //Verify Registration Details
        $dbh = self::connect();
        $hash = substr(sha1(md5($key)), 3, 10);
        $verCode = mt_rand(1000, 9999);
        $c = "UPDATE subscribers SET sPass=:k,sVerCode=:v WHERE sEmail=:e AND sVerCode=:c";
        $queryC = $dbh->prepare($c);
        $queryC->bindParam(':e', $id, PDO::PARAM_STR);
        $queryC->bindParam(':c', $code, PDO::PARAM_STR);
        $queryC->bindParam(':k', $hash, PDO::PARAM_STR);
        $queryC->bindParam(':v', $verCode, PDO::PARAM_INT);
        if ($queryC->execute()) {
            return 0;
        } else {
            return 1;
        }
    }

    public function setUserPics($id, $imageData)
    {
        $dbh = self::connect();
        $id = (float) $id;

        // Check if the user exists
        $c = "SELECT sId FROM subscribers WHERE sId = :id";
        $queryC = $dbh->prepare($c);
        $queryC->bindParam(':id', $id, PDO::PARAM_INT);
        $queryC->execute();

        if ($queryC->rowCount() > 0) {
            // Create an image resource from binary data
            $img = imagecreatefromstring($imageData);

            if ($img === false) {
                return [
                    "status" => "fail",
                    "message" => "Failed to create image from provided data.",
                ];
            }

            // Check the image format
            $imageFormat = 'png'; // Default to PNG
            if (imageistruecolor($img)) {
                $imageFormat = 'png';
            } else {
                $imageFormat = 'jpg';
            }

            // Generate a unique image name with the determined format
            $imageName = 'user_image_' . uniqid() . '.' . $imageFormat;

            // Specify the directory path where you want to store the images on your cPanel server
            $uploadDirectory = '/home2/wapaycom/new.wapay.com.ng/api/mobile/uploadimage/images/';

            // Save the image to the server
            if ($imageFormat === 'jpg') {
                imagejpeg($img, $uploadDirectory . $imageName);
            } else {
                imagepng($img, $uploadDirectory . $imageName); // Save as PNG by default
            }

            // Update the profile picture in the database with the image path
            $imagePath = 'https://new.wapay.com.ng/api/mobile/uploadimage/images/' . $imageName;
            $sql = "UPDATE subscribers SET sImage = :image WHERE sId = :id";
            $query = $dbh->prepare($sql);
            $query->bindParam(':image', $imagePath, PDO::PARAM_STR);
            $query->bindParam(':id', $id, PDO::PARAM_INT);
            $query->execute();

            // Destroy the image resource
            imagedestroy($img);

            $response = [];
            $response["status"] = "success";
            $response["image_name"] = $imagePath;
            $response["message"] = "Updated Successfully";

            return $response;
        } else {
            $response = [];
            $response["status"] = "error";
            $response["message"] = "User not found.";
            return $response;
        }
    }

    //Update Seller Profile Password
    public function updateTransactionPin($id, $oldKey, $newKey)
    {
        $dbh = self::connect();
        $id = (float) $id;

        $c = "SELECT sPin FROM subscribers WHERE sPin=:p AND sId=$id";
        $queryC = $dbh->prepare($c);
        $queryC->bindParam(':p', $oldKey, PDO::PARAM_STR);
        $queryC->execute();
        $result = $queryC->fetch(PDO::FETCH_ASSOC);

        if ($queryC->rowCount() > 0) {
            $sql = "UPDATE subscribers SET sPin=:p WHERE sId=$id";
            $query = $dbh->prepare($sql);
            $query->bindParam(':p', $newKey, PDO::PARAM_STR);
            $query->execute();
            return 0;
        } else {
            return 1;
        }
    }

    //Disable User Pin
    public function disableUserPin($id, $oldPin, $status)
    {
        $dbh = self::connect();
        $id = (int) $id;
        $status = (int) $status;

        $c = "SELECT sPin FROM subscribers WHERE sPin=:p AND sId=$id";
        $queryC = $dbh->prepare($c);
        $queryC->bindParam(':p', $oldPin, PDO::PARAM_STR);
        $queryC->execute();
        $result = $queryC->fetch(PDO::FETCH_ASSOC);

        if ($queryC->rowCount() > 0) {
            $sql = "UPDATE subscribers SET sPinStatus=:s WHERE sId=$id";
            $query = $dbh->prepare($sql);
            $query->bindParam(':s', $status, PDO::PARAM_STR);
            $query->execute();
            return 0;
        } else {
            return 1;
        }
    }

    //----------------------------------------------------------------------------------------------------------------
    // Email Verification Management
    //----------------------------------------------------------------------------------------------------------------
    //Update Seller Profile Password
    public function updateEmailVerificationStatus($id)
    {
        $dbh = self::connect();
        $id = (float) $id;
        $verCode = mt_rand(1000, 9999);

        $sql = "UPDATE subscribers SET sRegStatus=0,sVerCode=$verCode WHERE sId=$id";
        $query = $dbh->prepare($sql);
        $query->execute();

        $_SESSION["verification"] = 'YES';

        return 0;
    }
    //----------------------------------------------------------------------------------------------------------------
    // Airtime Management
    //----------------------------------------------------------------------------------------------------------------
    
      public function getNotifications()
    {
        $dbh = self::connect();
        $sql = "SELECT msgId, msgfor, subject, message, status, dPosted FROM notifications WHERE status = 0 ORDER BY dPosted DESC";
        $query = $dbh->prepare($sql);
        $query->execute();
        $results = $query->fetchAll(PDO::FETCH_OBJ);
        return $results;
    }
    
    
    //Get All Network
    public function getNetworks()
    {
        $dbh = self::connect();
        $sql = "SELECT * FROM networkid ORDER BY networkid ASC";
        $query = $dbh->prepare($sql);
        $query->execute();
        $results = $query->fetchAll(PDO::FETCH_OBJ);
        return $results;
    }

    //Get Airtime Discount
    public function getAirtimeDiscount()
    {
        $dbh = self::connect();
        $sql = "SELECT * FROM airtime a, networkid b WHERE a.aNetwork=b.nId";
        $query = $dbh->prepare($sql);
        $query->execute();
        $results = $query->fetchAll(PDO::FETCH_OBJ);
        return $results;
    }

    //----------------------------------------------------------------------------------------------------------------
    // Recharge Car Pin Management

    //----------------------------------------------------------------------------------------------------------------
    // Data Management
    //----------------------------------------------------------------------------------------------------------------

    //Get All Data Plans
    public function getDataPlans()
    {
        $dbh = self::connect();
        $sql = "SELECT * FROM dataplans a, networkid b WHERE a.datanetwork = b.nId ORDER BY a.pId ASC";
        $query = $dbh->prepare($sql);
        $query->execute();
        $results = $query->fetchAll(PDO::FETCH_OBJ);
        return $results;
    }

    //----------------------------------------------------------------------------------------------------------------
    // Upgrade To Agent
    //----------------------------------------------------------------------------------------------------------------

    //Upgrade To Agent
    public function upgradeToAgent($userId, $pin, $ref)
    {
        $dbh = self::connect();
        $sql = "SELECT sFname,sLname,sPhone,sType,sWallet,sPin,sReferal FROM subscribers WHERE sId=:id";
        $query = $dbh->prepare($sql);
        $query->bindParam(':id', $userId, PDO::PARAM_INT);
        $query->execute();
        $results = $query->fetch(PDO::FETCH_OBJ);
        $result2 = $this->getSiteSettings();
        $amount = (float) $result2->agentupgrade;

        $referal = $results->sPhone;
        $referalname = $results->sFname . " " . $results->sLname;
        $refearedby = $results->sReferal;
        $refbonus = $result2->referalupgradebonus;

        if ($_SESSION["pinStatus"] == 1 || $_SESSION["pinStatus"] == "1") {
            $pinstatus = 1;
        } else {
            $pinstatus = 0;
        }

        if ($results->sPin == $pin || $pinstatus == 1) {
            if ($results->sType == 2) {
                return 2;
            } else {
                $balance = (float) $results->sWallet;
                if ($balance >= $amount) {
                    $oldbalance = $balance;
                    $newbalance = $oldbalance - $amount;
                    $servicename = "Account Upgrade";
                    $servicedesc = "Upgraded Account Type To Agent Account.";
                    $status = 0;
                    $date = date("Y-m-d H:i:s");

                    //Record Transaction
                    $sql2 = "INSERT INTO transactions SET sId=:user,transref=:ref,servicename=:sn,servicedesc=:sd,amount=:a,status=:s,oldbal=:ob,newbal=:nb,date=:d";
                    $query2 = $dbh->prepare($sql2);
                    $query2->bindParam(':user', $userId, PDO::PARAM_INT);
                    $query2->bindParam(':ref', $ref, PDO::PARAM_STR);
                    $query2->bindParam(':sn', $servicename, PDO::PARAM_STR);
                    $query2->bindParam(':sd', $servicedesc, PDO::PARAM_STR);
                    $query2->bindParam(':a', $amount, PDO::PARAM_STR);
                    $query2->bindParam(':s', $status, PDO::PARAM_INT);
                    $query2->bindParam(':ob', $oldbalance, PDO::PARAM_STR);
                    $query2->bindParam(':nb', $newbalance, PDO::PARAM_STR);
                    $query2->bindParam(':d', $date, PDO::PARAM_STR);
                    $query2->execute();

                    $lastInsertId = $dbh->lastInsertId();
                    if ($lastInsertId) {
                        //Update Account Type & Balance
                        $sql3 = "UPDATE subscribers SET sType = 2, sWallet=:bal WHERE sId=:id";
                        $query3 = $dbh->prepare($sql3);
                        $query3->bindParam(':id', $userId, PDO::PARAM_INT);
                        $query3->bindParam(':bal', $newbalance, PDO::PARAM_STR);
                        $query3->execute();

                        $loginAccount = base64_encode("2");
                        setcookie("loginAccount", $loginAccount, time() + 2592000 * 30, "/");
                        if ($refearedby != "") {
                            $this->creditReferalBonus($dbh, $referal, $referalname, $refearedby, $refbonus);
                        }

                        return 0;
                    } else {
                        return 4;
                    }
                } else {
                    return 3;
                }
            }
        } else {
            return 1;
        }

        return $results;
    }

    //----------------------------------------------------------------------------------------------------------------
    // Upgrade To Vendor
    //----------------------------------------------------------------------------------------------------------------

    //Upgrade To Vendor
    public function upgradeToVendor($userId, $pin, $ref)
    {
        $dbh = self::connect();
        $sql = "SELECT sType,sFname,sLname,sPhone,sWallet,sPin,sReferal FROM subscribers WHERE sId=:id";
        $query = $dbh->prepare($sql);
        $query->bindParam(':id', $userId, PDO::PARAM_INT);
        $query->execute();
        $results = $query->fetch(PDO::FETCH_OBJ);
        $result2 = $this->getSiteSettings();
        $amount = (float) $result2->vendorupgrade;

        $referal = $results->sPhone;
        $referalname = $results->sFname . " " . $results->sLname;
        $refearedby = $results->sReferal;
        $refbonus = $result2->referalupgradebonus;

        if ($_SESSION["pinStatus"] == 1 || $_SESSION["pinStatus"] == "1") {
            $pinstatus = 1;
        } else {
            $pinstatus = 0;
        }

        if ($results->sPin == $pin || $pinstatus == 1) {
            if ($results->sType == 3) {
                return 2;
            } else {
                $balance = (float) $results->sWallet;
                if ($balance >= $amount) {
                    $oldbalance = $balance;
                    $newbalance = $oldbalance - $amount;
                    $servicename = "Account Upgrade";
                    $servicedesc = "Upgraded Account Type To Vendor Account.";
                    $status = 0;
                    $date = date("Y-m-d H:i:s");

                    //Record Transaction
                    $sql2 = "INSERT INTO transactions SET sId=:user,transref=:ref,servicename=:sn,servicedesc=:sd,amount=:a,status=:s,oldbal=:ob,newbal=:nb,date=:d";
                    $query2 = $dbh->prepare($sql2);
                    $query2->bindParam(':user', $userId, PDO::PARAM_INT);
                    $query2->bindParam(':ref', $ref, PDO::PARAM_STR);
                    $query2->bindParam(':sn', $servicename, PDO::PARAM_STR);
                    $query2->bindParam(':sd', $servicedesc, PDO::PARAM_STR);
                    $query2->bindParam(':a', $amount, PDO::PARAM_STR);
                    $query2->bindParam(':s', $status, PDO::PARAM_INT);
                    $query2->bindParam(':ob', $oldbalance, PDO::PARAM_STR);
                    $query2->bindParam(':nb', $newbalance, PDO::PARAM_STR);
                    $query2->bindParam(':d', $date, PDO::PARAM_STR);
                    $query2->execute();

                    $lastInsertId = $dbh->lastInsertId();
                    if ($lastInsertId) {
                        //Update Account Type & Balance
                        $sql3 = "UPDATE subscribers SET sType = 3, sWallet=:bal WHERE sId=:id";
                        $query3 = $dbh->prepare($sql3);
                        $query3->bindParam(':id', $userId, PDO::PARAM_INT);
                        $query3->bindParam(':bal', $newbalance, PDO::PARAM_STR);
                        $query3->execute();

                        $loginAccount = base64_encode("3");
                        setcookie("loginAccount", $loginAccount, time() + 2592000 * 30, "/");
                        if ($refearedby != "") {
                            $this->creditReferalBonus($dbh, $referal, $referalname, $refearedby, $refbonus);
                        }
                        return 0;
                    } else {
                        return 4;
                    }
                } else {
                    return 3;
                }
            }
        } else {
            return 1;
        }

        return $results;
    }

    //----------------------------------------------------------------------------------------------------------------
    // Referal Bonus
    //----------------------------------------------------------------------------------------------------------------

    public function creditReferalBonus($dbh, $referal, $referalname, $refearedby, $refbonus)
    {
        $sql = "SELECT sId,sRefWallet FROM subscribers WHERE sPhone=:phone";
        $query = $dbh->prepare($sql);
        $query->bindParam(':phone', $refearedby, PDO::PARAM_STR);
        $query->execute();
        $result = $query->fetch(PDO::FETCH_OBJ);

        if ($query->rowCount() > 0) {
            //Get User Balance
            $userId = $result->sId;
            $balance = (float) $result->sRefWallet;
            $oldbalance = $balance;
            $amount = (float) $refbonus;
            $newbalance = $oldbalance + $amount;
            $servicename = "Referral Bonus";
            $servicedesc = "Referral Bonus Of N{$amount} For Referring {$referalname} ({$referal}). Bonus For Account Upgrade.";
            $status = 0;
            $date = date("Y-m-d H:i:s");
            $ref = "REF-" . time();

            //Record Transaction
            $sql2 = "INSERT INTO transactions SET sId=:user,transref=:ref,servicename=:sn,servicedesc=:sd,amount=:a,status=:s,oldbal=:ob,newbal=:nb,date=:d";
            $query2 = $dbh->prepare($sql2);
            $query2->bindParam(':user', $userId, PDO::PARAM_INT);
            $query2->bindParam(':ref', $ref, PDO::PARAM_STR);
            $query2->bindParam(':sn', $servicename, PDO::PARAM_STR);
            $query2->bindParam(':sd', $servicedesc, PDO::PARAM_STR);
            $query2->bindParam(':a', $amount, PDO::PARAM_STR);
            $query2->bindParam(':s', $status, PDO::PARAM_INT);
            $query2->bindParam(':ob', $oldbalance, PDO::PARAM_STR);
            $query2->bindParam(':nb', $newbalance, PDO::PARAM_STR);
            $query2->bindParam(':d', $date, PDO::PARAM_STR);
            $query2->execute();

            $lastInsertId = $dbh->lastInsertId();
            if ($lastInsertId) {
                //Update Account Type & Balance
                $sql3 = "UPDATE subscribers SET sRefWallet=:bal WHERE sId=:id";
                $query3 = $dbh->prepare($sql3);
                $query3->bindParam(':id', $userId, PDO::PARAM_INT);
                $query3->bindParam(':bal', $newbalance, PDO::PARAM_STR);
                $query3->execute();
                return 0;
            }
        }
    }

    //----------------------------------------------------------------------------------------------------------------
    // Contact Management
    //----------------------------------------------------------------------------------------------------------------
   public function getSiteSettings()
    {
        $dbh = self::connect();
        
        // Fetch site settings
        $sqlSettings = "SELECT * FROM sitesettings WHERE sId=1";
        $querySettings = $dbh->prepare($sqlSettings);
        $querySettings->execute();
        $siteSettings = $querySettings->fetch(PDO::FETCH_OBJ);
        
        // Fetch notifications and embed them in site settings
        $sqlNotifications = "SELECT msgId, msgfor, subject, message, status, dPosted FROM notifications WHERE status = 0 ORDER BY dPosted DESC";
        $queryNotifications = $dbh->prepare($sqlNotifications);
        $queryNotifications->execute();
        $siteSettings->notifications = $queryNotifications->fetchAll(PDO::FETCH_OBJ);
        
        return $siteSettings;
    }
    //Get Bulk Sms

    public function getbulksms($id, $ref)
    {
        $dbh = self::connect();
        $id = (int) $id;
        $sql = "SELECT * FROM bulksms WHERE sId=$id AND tRef=:ref";
        $query = $dbh->prepare($sql);
        $query->bindParam(":ref", $ref, PDO::PARAM_STR);
        $query->execute();
        $results = $query->fetch(PDO::FETCH_OBJ);
        return $results;
    }

    //----------------------------------------------------------------------------------------------------------------
    //	Exam Pin Management
    //----------------------------------------------------------------------------------------------------------------

    //Get All Exam Pin Provider
    public function getExamProvider()
    {
        $dbh = self::connect();
        $sql = "SELECT * FROM examid ORDER BY eId ASC";
        $query = $dbh->prepare($sql);
        $query->execute();
        $results = $query->fetchAll(PDO::FETCH_OBJ);
        return $results;
    }

    //----------------------------------------------------------------------------------------------------------------
    //	Electricity Management
    //----------------------------------------------------------------------------------------------------------------

    //Get All Electricity Provider
    public function getElectricityProvider()
    {
        $dbh = self::connect();
        $sql = "SELECT * FROM electricityid ORDER BY provider ASC";
        $query = $dbh->prepare($sql);
        $query->execute();
        $results = $query->fetchAll(PDO::FETCH_OBJ);
        return $results;
    }

    //Verify Data Plan Id
    public function verifyDataPlanId($network, $data_plan)
    {
        $dbh = self::connect();
        $sql = "SELECT * FROM dataplans WHERE datanetwork=:network AND pId=:plan ";
        $query = $dbh->prepare($sql);
        $query->bindParam(':network', $network, PDO::PARAM_STR);
        $query->bindParam(':plan', $data_plan, PDO::PARAM_STR);
        $query->execute();
        if ($query->rowCount() > 0) {
            $result = $query->fetch(PDO::FETCH_OBJ);
            return $result;
        } else {
            return 1;
        }
    }

    public function setSchedule($Id, $network, $datagroup, $dataplan, $time, $phone, $email, $amounttopay, $ported_number, $apikey, $transref, $status)
    {
        $dbh = self::connect();
        $sql = "INSERT INTO scheduled_purchases (user_id, network, datagroup, dataplan, scheduled_time, phone, email,  amounttopay, is_ported_number, apikey, transref, status) 
            VALUES (:user_id, :network, :datagroup, :dataplan, :scheduled_time, :phone, :email, :amounttopay, :is_ported_number, :apikey, :transref, :status)";

        $query = $dbh->prepare($sql);
        $query->bindParam(':user_id', $Id, PDO::PARAM_INT);
        $query->bindParam(':network', $network, PDO::PARAM_STR);
        $query->bindParam(':datagroup', $datagroup, PDO::PARAM_STR);
        $query->bindParam(':dataplan', $dataplan, PDO::PARAM_STR);
        $query->bindParam(':scheduled_time', $time, PDO::PARAM_STR);
        $query->bindParam(':phone', $phone, PDO::PARAM_STR);
        $query->bindParam(':email', $email, PDO::PARAM_STR);
        $query->bindParam(':amounttopay', $amounttopay, PDO::PARAM_STR);
        $query->bindParam(':is_ported_number', $ported_number, PDO::PARAM_STR);
        $query->bindParam(':apikey', $apikey, PDO::PARAM_STR);
        $query->bindParam(':transref', $transref, PDO::PARAM_STR);
        $query->bindValue(':status', $status, PDO::PARAM_INT); // Use the passed $status parameter here
        $query->execute();

        $lastInsertId = $dbh->lastInsertId();
        if ($lastInsertId) {
            $balanceQuery = $dbh->prepare("SELECT sWallet FROM subscribers WHERE sId = :id");
            $balanceQuery->bindParam(':id', $Id, PDO::PARAM_INT);
            $balanceQuery->execute();
            $currentBalance = (float) $balanceQuery->fetchColumn();

            function changeNetworkID($network)
            {
                switch ($network) {
                    case 1:
                        return 'MTN';
                    case 2:
                        return 'GLO';
                    case 3:
                        return '9MOBILE';
                    case 4:
                        return 'AIRTEL';
                    case 6:
                        return 6;
                    default:
                        return $network;
                }
            }
            $result = $this->verifyDataPlanId($network, $dataplan);
            $response = [];
            if (is_object($result)) {
                $response["status"] = "success";
                $response["dataplan"] = $result->planid;
                $response["name"] = $result->name;
                if ($usertype == 3) {
                    $response["amount"] = (float) $result->vendorprice;
                } elseif ($usertype == 2) {
                    $response["amount"] = (float) $result->agentprice;
                } else {
                    $response["amount"] = (float) $result->userprice;
                }
                $response["buyprice"] = $result->price;
                $response["datatype"] = $result->type;
                $response["day"] = $result->day;
            }

            $networks = changeNetworkID($network);

            $oldbalance = $currentBalance;
            $newbalance = $oldbalance;
            $servicename = "Schedule";
            $servicedesc = "Schedule for {$networks} {$result->name} {$datagroup} 30 Days Plan for phone number {$phone} on {$time}";

            $status = 0;
            $date = date("Y-m-d H:i:s");
            $sql2 = "INSERT INTO transactions SET sId=:user, transref=:ref, servicename=:sn, servicedesc=:sd, amount=:a, status=:s, oldbal=:ob, newbal=:nb, date=:d";

            function generatebulkref()
            {
                $randomString = bin2hex(random_bytes(8));
                $timestamp = time();
                $transref = "TR-" . $timestamp . "-" . $randomString;

                return $transref;
            }
            $amounttopayd = (int) $amounttopay;
            $transrer = generatebulkref();
            $query2 = $dbh->prepare($sql2);
            $query2->bindParam(':user', $Id, PDO::PARAM_INT);
            $query2->bindParam(':ref', $transrer, PDO::PARAM_STR);
            $query2->bindParam(':sn', $servicename, PDO::PARAM_STR);
            $query2->bindParam(':sd', $servicedesc, PDO::PARAM_STR);
            $query2->bindParam(':a', $amounttopayd, PDO::PARAM_STR);
            $query2->bindParam(':s', $status, PDO::PARAM_INT);
            $query2->bindParam(':ob', $oldbalance, PDO::PARAM_STR);
            $query2->bindParam(':nb', $newbalance, PDO::PARAM_STR);
            $query2->bindParam(':d', $date, PDO::PARAM_STR);
            $query2->execute();

            return 0;
        } else {
            return 1;
        }
    }

    //----------------------------------------------------------------------------------------------------------------
    //	Cable Plan Management
    //----------------------------------------------------------------------------------------------------------------
    //Post Form Contact Message
    public function pobstContact($name, $email, $subject, $msg)
    {
        $dbh = self::connect();
        $sql = "INSERT INTO contact  SET name=:n,contact=:c,subject=:s,message=:m";
        $query = $dbh->prepare($sql);
        $query->bindParam(':n', $name, PDO::PARAM_STR);
        $query->bindParam(':c', $email, PDO::PARAM_STR);
        $query->bindParam(':s', $subject, PDO::PARAM_STR);
        $query->bindParam(':m', $msg, PDO::PARAM_STR);
        $query->execute();

        $lastInsertId = $dbh->lastInsertId();
        if ($lastInsertId) {
            return 0;
        } else {
            return 1;
        }
    }
    //Get All Cable Provider
    public function getCableProvider()
    {
        $dbh = self::connect();
        $sql = "SELECT * FROM cableid ORDER BY cableid ASC";
        $query = $dbh->prepare($sql);
        $query->execute();
        $results = $query->fetchAll(PDO::FETCH_OBJ);
        return $results;
    }

    //Get Cable Plans
    public function getCablePlans()
    {
        $dbh = self::connect();
        $sql = "SELECT * FROM cableplans a, cableid b WHERE a.cableprovider=b.cableid";
        $query = $dbh->prepare($sql);
        $query->execute();
        $results = $query->fetchAll(PDO::FETCH_OBJ);
        return $results;
    }

    //----------------------------------------------------------------------------------------------------------------
    // Transaction Management
    //----------------------------------------------------------------------------------------------------------------

    //Get All Transactions
    public function getAllTransaction($userId, $limit)
    {
        $dbh = self::connect();
        $addon = "";

        if (isset($_GET["search"])) {
            $search = isset($_GET["search"]) ? $_GET["search"] : "";
            $searchfor = isset($_GET["searchfor"]) ? $_GET["searchfor"] : "";

            if ($search == "") {
                if ($searchfor == "all") {
                    $addon = "";
                }
                if ($searchfor == "wallet") {
                    $addon = " AND b.servicename ='Wallet Credit' ";
                }
                if ($searchfor == "monnify") {
                    $addon = " AND b.transref LIKE '%MNFY%' ";
                }
                if ($searchfor == "paystack") {
                    $addon = " AND b.servicedesc LIKE '%Paystack%' ";
                }
                if ($searchfor == "airtime") {
                    $addon = " AND b.servicename LIKE '%Airtime%' ";
                }
                if ($searchfor == "data") {
                    $addon = " AND b.servicename LIKE '%Data%' ";
                }
                if ($searchfor == "cable") {
                    $addon = " AND b.servicename LIKE '%Cable%' ";
                }
                if ($searchfor == "electricity") {
                    $addon = " AND b.servicename LIKE '%Electricity%' ";
                }
                if ($searchfor == "exam") {
                    $addon = " AND b.servicename LIKE '%Exam%' ";
                }
                if ($searchfor == "reference") {
                    $addon = " AND b.transref LIKE :search ";
                }
            } else {
                if ($searchfor == "all") {
                    $addon = " AND b.servicedesc LIKE :search";
                }
                if ($searchfor == "wallet") {
                    $addon = " AND (b.servicedesc LIKE :search AND b.servicename ='Wallet Credit') ";
                }
                if ($searchfor == "monnify") {
                    $addon = " AND (b.servicedesc LIKE :search AND b.transref LIKE '%MNFY%') ";
                }
                if ($searchfor == "paystack") {
                    $addon = " AND (b.servicedesc LIKE :search AND b.servicedesc LIKE '%Paystack%') ";
                }
                if ($searchfor == "airtime") {
                    $addon = " AND (b.servicedesc LIKE :search AND b.servicename LIKE '%Airtime%') ";
                }
                if ($searchfor == "data") {
                    $addon = " AND (b.servicedesc LIKE :search AND b.servicename LIKE '%Data%') ";
                }
                if ($searchfor == "cable") {
                    $addon = " AND (b.servicedesc LIKE :search AND b.servicename LIKE '%Cable%') ";
                }
                if ($searchfor == "electricity") {
                    $addon = " AND (a.servicedesc LIKE :search AND b.servicename LIKE '%Electricity%') ";
                }
                if ($searchfor == "exam") {
                    $addon = " AND (b.servicedesc LIKE :search AND b.servicename LIKE '%Exam%') ";
                }
                if ($searchfor == "reference") {
                    $addon = " AND b.transref LIKE :search ";
                }
            }
        }

        $sql = "SELECT a.sFname,a.sPhone,a.sEmail,a.sType,b.* FROM subscribers a, transactions b WHERE a.sId=b.sId ";
        $sql .= $addon . " AND a.sId=:id ORDER BY b.date DESC LIMIT $limit, 100";
        $query = $dbh->prepare($sql);
        $query->bindParam(':id', $userId, PDO::PARAM_INT);
        if (isset($_GET["search"])):
            if ($search != ""):
                $query->bindValue(':search', '%' . $search . '%');
            endif;
        endif;
        $query->execute();
        $results = $query->fetchAll(PDO::FETCH_OBJ);
        return $results;
    }

    //Verify Transaction Pin
    public function verifyTransactionPin($userId, $transkey)
    {
        $dbh = self::connect();

        if (isset($_SESSION["pinStatus"])) {
            if ($_SESSION["pinStatus"] == 1 || $_SESSION["pinStatus"] == "1") {
                $sql = "SELECT sApiKey FROM subscribers WHERE sId=:id";
                $query = $dbh->prepare($sql);
                $query->bindParam(':id', $userId, PDO::PARAM_INT);
                $query->execute();
                $results = $query->fetch(PDO::FETCH_OBJ);
                if ($query->rowCount() > 0) {
                    return $results;
                } else {
                    return 1;
                }
            } else {
                $sql = "SELECT sApiKey FROM subscribers WHERE sId=:id AND sPin=:p";
                $query = $dbh->prepare($sql);
                $query->bindParam(':id', $userId, PDO::PARAM_INT);
                $query->bindParam(':p', $transkey, PDO::PARAM_STR);
                $query->execute();
                $results = $query->fetch(PDO::FETCH_OBJ);
                if ($query->rowCount() > 0) {
                    return $results;
                } else {
                    return 1;
                }
            }
        }

        return 1;
    }

    //Get Transaction Details
    public function getTransactionDetails($ref)
    {
        $dbh = self::connect();
        $sql = "SELECT * FROM transactions WHERE transref=:ref";
        $query = $dbh->prepare($sql);
        $query->bindParam(':ref', $ref, PDO::PARAM_STR);
        $query->execute();
        $result = $query->fetch(PDO::FETCH_OBJ);
        return $result;
    }

    //----------------------------------------------------------------------------------------------------------------
    // Perform Wallet To Wallet Transfer
    //----------------------------------------------------------------------------------------------------------------
//Perform Wallet Transfer - FIXED FOR LAST 8 DIGITS (RECEIVER + SELF CHECK + FCM NOTIFICATION)
public function performWalletTransfer($userId, $email, $amount, $amounttopay, $transref1, $transref2)
{
    $dbh = self::connect();
    $sql = "SELECT sType,sWallet,sPin,sEmail,sPhone FROM subscribers WHERE sId=:id";
    $query = $dbh->prepare($sql);
    $query->bindParam(':id', $userId, PDO::PARAM_INT);
    $query->execute();
    $results = $query->fetch(PDO::FETCH_OBJ);
    $result2 = $this->getSiteSettings();
    $senderEmail = $results->sEmail;
    $senderPhone = $results->sPhone;
    $walletcharges = (float) $result2->wallettowalletcharges;
    $amounttopay = $amount + $walletcharges;
    
    // Extract last 8 digits from provided phone/email input
    $last8Digits = ltrim($email, '0');

    // Find Receiver
    $c = "SELECT sId,sWallet,sEmail,sPhone FROM subscribers WHERE RIGHT(sPhone, 8) = :last8";
    $queryC = $dbh->prepare($c);
    $queryC->bindParam(':last8', $last8Digits, PDO::PARAM_STR);
    $queryC->execute();
    $resultC = $queryC->fetch(PDO::FETCH_OBJ);

    if ($queryC->rowCount() <= 0) {
        return 2; // Receiver Not Found
    }

    $receiverID = $resultC->sId;
    $receiverEmail = $resultC->sEmail;
    $receiverPhone = $resultC->sPhone;
    $receiverOldBal = (float) $resultC->sWallet;
    $receiverNewBal = $receiverOldBal + $amount;

    // Prevent Self Transfer
    $senderLast8 = substr($senderPhone, -8);
    if ($senderLast8 == $last8Digits) {
        return 5; // Cannot transfer to self
    }

    // Check Sender Balance
    $balance = (float) $results->sWallet;
    if ($balance < $amounttopay) {
        return 3; // Insufficient Balance
    }

    // Deduct from Sender
    $oldbalance = $balance;
    $newbalance = $oldbalance - $amounttopay;
    $servicename = "Wallet Transfer";
    $servicedesc = "Wallet To Wallet Transfer Of N{$amount} To Last8:{$last8Digits}. Total Amount With Charges Is {$amounttopay}. New Balance Is {$newbalance}.";
    $date = date("Y-m-d H:i:s");
    $status = 0;

    // Sender Transaction Log
    $sql2 = "INSERT INTO transactions SET sId=:user,transref=:ref,servicename=:sn,servicedesc=:sd,amount=:a,status=:s,oldbal=:ob,newbal=:nb,date=:d";
    $query2 = $dbh->prepare($sql2);
    $query2->bindParam(':user', $userId);
    $query2->bindParam(':ref', $transref1);
    $query2->bindParam(':sn', $servicename);
    $query2->bindParam(':sd', $servicedesc);
    $query2->bindParam(':a', $amounttopay);
    $query2->bindParam(':s', $status);
    $query2->bindParam(':ob', $oldbalance);
    $query2->bindParam(':nb', $newbalance);
    $query2->bindParam(':d', $date);
    $query2->execute();

    $lastInsertId = $dbh->lastInsertId();
    if ($lastInsertId) {
        $sql3 = "UPDATE subscribers SET sWallet=:bal WHERE sId=:id";
        $query3 = $dbh->prepare($sql3);
        $query3->bindParam(':id', $userId);
        $query3->bindParam(':bal', $newbalance);
        $query3->execute();
    } else {
        return 4;
    }

    // Receiver Transaction Log
    $servicename2 = "Wallet Transfer";
    $servicedesc2 = "Wallet To Wallet Transfer Of N{$amount} From User {$senderEmail}. New Balance Is {$receiverNewBal}.";
    $sql4 = "INSERT INTO transactions SET sId=:user,transref=:ref,servicename=:sn,servicedesc=:sd,amount=:a,status=:s,oldbal=:ob,newbal=:nb,date=:d";
    $query4 = $dbh->prepare($sql4);
    $query4->bindParam(':user', $receiverID);
    $query4->bindParam(':ref', $transref2);
    $query4->bindParam(':sn', $servicename2);
    $query4->bindParam(':sd', $servicedesc2);
    $query4->bindParam(':a', $amount);
    $query4->bindParam(':s', $status);
    $query4->bindParam(':ob', $receiverOldBal);
    $query4->bindParam(':nb', $receiverNewBal);
    $query4->bindParam(':d', $date);
    $query4->execute();

    $lastInsertId = $dbh->lastInsertId();
    if ($lastInsertId) {
        $sql5 = "UPDATE subscribers SET sWallet=:bal WHERE sId=:id";
        $query5 = $dbh->prepare($sql5);
        $query5->bindParam(':id', $receiverID);
        $query5->bindParam(':bal', $receiverNewBal);
        $query5->execute();
    } else {
        return 4;
    }

    // ✅ SEND FCM NOTIFICATIONS
    $getFcm = $dbh->prepare("SELECT sFcm FROM subscribers WHERE sId=:id");

    // Sender FCM
    $getFcm->bindParam(':id', $userId);
    $getFcm->execute();
    $senderFcm = $getFcm->fetch(PDO::FETCH_OBJ)->sFcm;

    // Receiver FCM
    $getFcm->bindParam(':id', $receiverID);
    $getFcm->execute();
    $receiverFcm = $getFcm->fetch(PDO::FETCH_OBJ)->sFcm;

    // Messages
    $senderTitle = "Wallet Transfer Successful";
    $senderBody = "You sent ₦{$amount} to user ending {$last8Digits}. Charges ₦{$walletcharges}. New Balance: ₦{$newbalance}.";

    $receiverTitle = "Wallet Credit Alert";
    $receiverBody = "You received ₦{$amount} from {$senderEmail}. New Balance: ₦{$receiverNewBal}.";

    if (!empty($senderFcm)) {
        $this->sendFCMNotification($senderFcm, $senderTitle, $senderBody);
    }

    if (!empty($receiverFcm)) {
        $this->sendFCMNotification($receiverFcm, $receiverTitle, $receiverBody);
    }

    return 0;
}


    //----------------------------------------------------------------------------------------------------------------
    // Perform Referal To Wallet Transfer
    //----------------------------------------------------------------------------------------------------------------

    //Upgrade To Agent
    public function performReferralTransfer($userId, $amount, $amounttopay, $transref1, $transref2)
    {
        $dbh = self::connect();
        $sql = "SELECT sType,sWallet,sRefWallet,sPin,sEmail FROM subscribers WHERE sId=:id";
        $query = $dbh->prepare($sql);
        $query->bindParam(':id', $userId, PDO::PARAM_INT);
        $query->execute();
        $results = $query->fetch(PDO::FETCH_OBJ);
        $result2 = $this->getSiteSettings();
        $senderEmail = $results->sEmail;
        $balance = (float) $results->sWallet;
        $refbalance = (float) $results->sRefWallet;

        if ($refbalance >= $amounttopay) {
            //Credit Referal Bonus
            $oldbalance = $balance;
            $newbalance = $oldbalance + $amount;
            $servicename = "Wallet Transfer";
            $servicedesc = "Referral To Wallet Transfer Of N{$amount} from referral wallet to main wallet. New Balance Is {$newbalance}.";
            $status = 0;
            $date = date("Y-m-d H:i:s");

            //Record Transaction
            $sql2 = "INSERT INTO transactions SET sId=:user,transref=:ref,servicename=:sn,servicedesc=:sd,amount=:a,status=:s,oldbal=:ob,newbal=:nb,date=:d";
            $query2 = $dbh->prepare($sql2);
            $query2->bindParam(':user', $userId, PDO::PARAM_INT);
            $query2->bindParam(':ref', $transref1, PDO::PARAM_STR);
            $query2->bindParam(':sn', $servicename, PDO::PARAM_STR);
            $query2->bindParam(':sd', $servicedesc, PDO::PARAM_STR);
            $query2->bindParam(':a', $amount, PDO::PARAM_STR);
            $query2->bindParam(':s', $status, PDO::PARAM_INT);
            $query2->bindParam(':ob', $oldbalance, PDO::PARAM_STR);
            $query2->bindParam(':nb', $newbalance, PDO::PARAM_STR);
            $query2->bindParam(':d', $date, PDO::PARAM_STR);
            $query2->execute();

            $refoldbalance = $refbalance;
            $refnewbalance = $refoldbalance - $amounttopay;
            $servicename = "Referral Debit";
            $servicedesc = "Referral To Wallet Transfer Of N{$amount} from referral wallet to main wallet. Total Amount With Charges Is {$amounttopay}. New Balance Is {$refnewbalance}.";
            $status = 0;
            $date = date("Y-m-d H:i:s");

            //Record Transaction
            $sql3 = "INSERT INTO transactions SET sId=:user,transref=:ref,servicename=:sn,servicedesc=:sd,amount=:a,status=:s,oldbal=:ob,newbal=:nb,date=:d";
            $query3 = $dbh->prepare($sql3);
            $query3->bindParam(':user', $userId, PDO::PARAM_INT);
            $query3->bindParam(':ref', $transref2, PDO::PARAM_STR);
            $query3->bindParam(':sn', $servicename, PDO::PARAM_STR);
            $query3->bindParam(':sd', $servicedesc, PDO::PARAM_STR);
            $query3->bindParam(':a', $amounttopay, PDO::PARAM_STR);
            $query3->bindParam(':s', $status, PDO::PARAM_INT);
            $query3->bindParam(':ob', $refoldbalance, PDO::PARAM_STR);
            $query3->bindParam(':nb', $refnewbalance, PDO::PARAM_STR);
            $query3->bindParam(':d', $date, PDO::PARAM_STR);
            $query3->execute();

            $lastInsertId = $dbh->lastInsertId();
            if ($lastInsertId) {
                //Update Account Type & Balance
                $sql3 = "UPDATE subscribers SET sWallet=:bal,sRefWallet=:refbal WHERE sId=:id";
                $query3 = $dbh->prepare($sql3);
                $query3->bindParam(':id', $userId, PDO::PARAM_INT);
                $query3->bindParam(':bal', $newbalance, PDO::PARAM_STR);
                $query3->bindParam(':refbal', $refnewbalance, PDO::PARAM_STR);
                $query3->execute();

                return 0;
            } else {
                return 4;
            }
        } else {
            return 3;
        }

        return $results;
    }
    
       
     // Perform Cashback to Wallet Transfer
    public function performCashbackTransfer($userId, $amount, $amounttopay, $transref1) {
    $dbh = $this->connect();

    // Validate input
    $amount = strip_tags($amount);
    if (!is_numeric($amount) || $amount <= 0) {
        error_log("Cashback Transfer Error for user $userId: Invalid amount ($amount)");
        return 6; // Invalid amount
    }

    // Get user details
    $sql = "SELECT sCashBack, sWallet, sEmail FROM subscribers WHERE sId = :id";
    $query = $dbh->prepare($sql);
    $query->bindParam(':id', $userId, PDO::PARAM_INT);
    $query->execute();
    $results = $query->fetch(PDO::FETCH_OBJ);

    if (!$results) {
        error_log("Cashback Transfer Error for user $userId: User not found");
        return 3; // User not found
    }

    $cashbackBalance = (float) $results->sCashBack;
    $walletBalance = (float) $results->sWallet;
    $userEmail = $results->sEmail;

    // Check if sufficient cashback balance
    if ($cashbackBalance < $amount) {
        error_log("Cashback Transfer Error for user $userId: Insufficient cashback balance ($cashbackBalance < $amount)");
        return 7; // Insufficient cashback balance
    }

    $newCashbackBalance = $cashbackBalance - $amount;
    $newWalletBalance = $walletBalance + $amount;
    $servicename = "Cashback to Wallet Transfer";
    $servicedesc = "Cashback to Wallet Transfer of N{$amount} successful. New Wallet Balance is: N{$newWalletBalance}.";
    $status = 0;
    $date = date("Y-m-d H:i:s");

    try {
        $dbh->beginTransaction();

        // Record Transaction
        $sql2 = "INSERT INTO transactions (sId, transref, servicename, servicedesc, amount, status, oldbal, newbal, date) 
                 VALUES (:user, :ref, :sn, :sd, :a, :s, :ob, :nb, :d)";
        $query2 = $dbh->prepare($sql2);
        $query2->bindParam(':user', $userId, PDO::PARAM_INT);
        $query2->bindParam(':ref', $transref1, PDO::PARAM_STR);
        $query2->bindParam(':sn', $servicename, PDO::PARAM_STR);
        $query2->bindParam(':sd', $servicedesc, PDO::PARAM_STR);
        $query2->bindParam(':a', $amount, PDO::PARAM_STR);
        $query2->bindParam(':s', $status, PDO::PARAM_INT);
        $query2->bindParam(':ob', $cashbackBalance, PDO::PARAM_STR);
        $query2->bindParam(':nb', $newCashbackBalance, PDO::PARAM_STR);
        $query2->bindParam(':d', $date, PDO::PARAM_STR);
        $query2->execute();

        $lastInsertId = $dbh->lastInsertId();
        if (!$lastInsertId) {
            $dbh->rollBack();
            error_log("Cashback Transfer Error for user $userId: Failed to record transaction");
            return 4; // Failed to record transaction
        }

        // Update Cashback and Wallet Balance
        $sql3 = "UPDATE subscribers SET sCashBack = :cashbackBal, sWallet = :walletBal WHERE sId = :id";
        $query3 = $dbh->prepare($sql3);
        $query3->bindParam(':id', $userId, PDO::PARAM_INT);
        $query3->bindParam(':cashbackBal', $newCashbackBalance, PDO::PARAM_STR);
        $query3->bindParam(':walletBal', $newWalletBalance, PDO::PARAM_STR);
        $query3->execute();

        if ($query3->rowCount() === 0) {
            $dbh->rollBack();
            error_log("Cashback Transfer Error for user $userId: No rows updated in subscribers table");
            return 4; // Failed to update wallet
        }

        $dbh->commit();
        error_log("Cashback Transfer Success for user $userId: Transferred N$amount, New Cashback: N$newCashbackBalance, New Wallet: N$newWalletBalance");
        return 0; // Success
    } catch (Exception $e) {
        $dbh->rollBack();
        error_log("Cashback Transfer Error for user $userId: " . $e->getMessage());
        return 2; // Database error
    }
}
    // Recharge Car Pin Management
    //----------------------------------------------------------------------------------------------------------------

    //Get Recharge Pin Discount
    public function getRechargePinDiscount()
    {
        $dbh = self::connect();
        $sql = "SELECT * FROM airtimepinprice a, networkid b WHERE a.aNetwork=b.networkid";
        $query = $dbh->prepare($sql);
        $query->execute();
        $results = $query->fetchAll(PDO::FETCH_OBJ);
        return $results;
    }

    //Purchase Rechargepin

    public function getRechargePinTokens($id, $ref)
    {
        $dbh = self::connect();
        $id = (int) $id;
        $sql = "SELECT * FROM rechargetokens WHERE sId=$id AND tRef=:ref";
        $query = $dbh->prepare($sql);
        $query->bindParam(":ref", $ref, PDO::PARAM_STR);
        $query->execute();
        $results = $query->fetch(PDO::FETCH_OBJ);
        return $results;
    }
    //---------------------------------------------------------------------
    //----------------------------------------------------------------------------------------------------------------
    // Wallet Funding Management
    //----------------------------------------------------------------------------------------------------------------

    //Initilize Paystack Payment
    public function initializePayStack($siteurl, $email, $amount, $amounttopay, $transRef)
    {
        $dbh = self::connect();
        //Get API Details
        $d = $this->getApiConfiguration();
        $a = $this->getSiteConfiguration();
        $monifyStatus = $this->getConfigValue($d, "monifyStatus");
        $monifyApi = $this->getConfigValue($d, "monifyApi");
        $monifySecrete = $this->getConfigValue($d, "monifySecrete");
        $monifyContract = $this->getConfigValue($d, "monifyContract");
        $adminEmail = $a->email;
        $amount = (float) $amount;
        $theresponse = [];
        $apiKey = $monifyApi; // Replace with your actual API key
        $clientSecret = $monifySecrete; // Replace with your actual client secret

        $credentials = $apiKey . ':' . $clientSecret;
        $base64Credentials = base64_encode($credentials);

        $amounttopass = urlencode(base64_encode($amount));
        $amount = $amount; //Amount
        //Amount
        function generatedataref()
        {
            // Generate a random string for uniqueness (you can modify the length as needed)
            $randomString = bin2hex(random_bytes(8));

            // Get the current timestamp (Unix timestamp)
            $timestamp = time();

            // Concatenate the timestamp and random string to create the transaction reference
            $transref = "MONNIFY-" . $timestamp . "-" . $randomString;

            return $transref;
        }
        $dataref = generatedataref();
        $url = 'https://api.monnify.com/api/v1/auth/login';

        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_POST, 1);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($ch, CURLOPT_HTTPHEADER, ["accept: application/json", "authorization: Basic " . $base64Credentials, "cache-control: no-cache"]);

        $json = curl_exec($ch);
        curl_close($ch);

        $result = json_decode($json, true);

        $accessToken = $result['responseBody']['accessToken'];
        // url to go to after payment
        $callback_url = $siteurl . "webhook/paystack/index.php";
        $curl = curl_init();
        curl_setopt_array($curl, [
            CURLOPT_URL => "https://api.monnify.com/api/v1/merchant/transactions/init-transaction",
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_CUSTOMREQUEST => "POST",
            CURLOPT_POSTFIELDS => json_encode([
                "amount" => $amount,
                "customerName" => "DANNVTU",
                "customerEmail" => $email,
                "paymentReference" => $dataref,
                "paymentDescription" => "Trial transaction",
                "currencyCode" => "NGN",
                "contractCode" => $monifyContract,
                "redirectUrl" => $callback_url,
                "paymentMethods" => ["CARD", "ACCOUNT_TRANSFER", "USSD", "DIRECT_DEBIT"],
            ]),
            CURLOPT_HTTPHEADER => [
                "authorization: Bearer " . $accessToken, //replace this with your own test key
                "content-type: application/json",
                "cache-control: no-cache",
            ],
        ]);

        $response = curl_exec($curl);
        $err = curl_error($curl);

        if ($err) {
            // there was an error contacting the Paystack API
            $theresponse["status"] = "fail";
            $theresponse["msg"] = ' Curl Returned Error: ' . $err;
            return $theresponse;
        }

        $tranx = json_decode($response, true);

        if (!$tranx['requestSuccessful']) {
            // The Paystack API request was not successful
            $theresponse["status"] = "fail";
            $theresponse["msg"] = 'API Returned Error: ' . $tranx['responseMessage'];
            return $theresponse;
        }

        // Extract the checkout URL from the response
        $checkoutUrl = $tranx['responseBody']['checkoutUrl'];

        $theresponse["status"] = "success";
        $theresponse["msg"] = $checkoutUrl;
        return $theresponse;
    }

    //Start Paystack Payment
    public function startPayStack($siteurl, $email, $amount, $amounttopay, $transRef)
    {
        $dbh = self::connect();
        $d = $this->getApiConfiguration();
        $key = $this->getConfigValue($d, "paystackApi");
        $$amount = (float) $amount;
        $theresponse = [];

        $amounttopass = urlencode(base64_encode($amount));
        $amount = $amount . "00"; //Amount
        //Amount

        // url to go to after payment
        $callback_url = $siteurl . "favourwebhook/paystack/index.php?email=$email&ama=$amounttopass";
        $curl = curl_init();
        curl_setopt_array($curl, [
            CURLOPT_URL => "https://api.paystack.co/transaction/initialize",
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_CUSTOMREQUEST => "POST",
            CURLOPT_POSTFIELDS => json_encode([
                'amount' => $amount,
                'email' => $email,
                'callback_url' => $callback_url,
            ]),
            CURLOPT_HTTPHEADER => [
                "authorization: Bearer " . $key, //replace this with your own test key
                "content-type: application/json",
                "cache-control: no-cache",
            ],
        ]);

        $response = curl_exec($curl);
        $err = curl_error($curl);

        if ($err) {
            // there was an error contacting the Paystack API
            $theresponse["status"] = "fail";
            $theresponse["msg"] = ' Curl Returned Error: ' . $err;
            return $theresponse;
        }

        $tranx = json_decode($response, true);

        if (!$tranx['status']) {
            // there was an error from the API
            $theresponse["status"] = "fail";
            $theresponse["msg"] = 'API Returned Error: ' . $tranx['message'];
            return $theresponse;
        }

        $theresponse["status"] = "success";
        $theresponse["msg"] = $tranx['data']['authorization_url'];
        return $theresponse;
    }

    //----------------------------------------------------------------------------------------------------------------
    // Notification Management
    //----------------------------------------------------------------------------------------------------------------

    //Get All Notification
    public function getAllNotification($userType)
    {
        $dbh = self::connect();
        $sql = "SELECT * FROM notifications WHERE msgFor=:ut OR msgFor=3 ORDER BY msgId DESC LIMIT 20";
        $query = $dbh->prepare($sql);
        $query->bindParam(':ut', $userType, PDO::PARAM_INT);
        $query->execute();
        $results = $query->fetchAll(PDO::FETCH_OBJ);
        return $results;
    }

    //Get Home Notification
    public function getHomeNotification()
    {
        $dbh = self::connect();
        $sql = "SELECT * FROM notifications WHERE msgFor=3 ORDER BY msgId DESC LIMIT 1";
        $query = $dbh->prepare($sql);
        $query->execute();
        $results = $query->fetch(PDO::FETCH_OBJ);
        return $results;
    }
    //----------------------------------------------------------------------------------------------------------------
    // Contact Management
    //----------------------------------------------------------------------------------------------------------------

    //Post Form Contact Message
    public function postContact($name, $email, $subject, $msg)
    {
        $dbh = self::connect();
        $sql = "INSERT INTO contact  SET name=:n,contact=:c,subject=:s,message=:m";
        $query = $dbh->prepare($sql);
        $query->bindParam(':n', $name, PDO::PARAM_STR);
        $query->bindParam(':c', $email, PDO::PARAM_STR);
        $query->bindParam(':s', $subject, PDO::PARAM_STR);
        $query->bindParam(':m', $msg, PDO::PARAM_STR);
        $query->execute();

        $lastInsertId = $dbh->lastInsertId();
        if ($lastInsertId) {
            return 0;
        } else {
            return 1;
        }
    }
}

?>
