<?php

require_once 'config.php';

class Auth extends Database{

     //fetch country
     public function get_country(){
        $sql = "SELECT * FROM countries";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        $row = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $row;

       
    }


    // Register new user
    public function register($username,$email,$password,$referid,$user_ref,$created_at){
        $sql = "INSERT INTO users (username,email,password,referid,user_ref,created_at) VALUES
               (:username,:email,:pass,:referid,:user_ref,:created_at)";
       $stmt = $this->conn->prepare($sql);
       $stmt->execute(['username'=>$username,'email'=>$email,'pass'=>$password,'referid'=>$referid,'user_ref'=>$user_ref,'created_at'=>$created_at]);
       return true;
    }

    //check if username exist
    public function username_exist($username){
        $sql = "SELECT * FROM users WHERE username = :username";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute(['username'=>$username]);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $result;
    }
    
    //check if ref exist
    public function ref_exist($user_ref){
        $sql = "SELECT * FROM users WHERE user_ref = :user_ref";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute(['user_ref'=>$user_ref]);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $result;
    }

 //check if user already registered 
 public function user_exist($email){
     $sql = "SELECT email FROM users WHERE email = :email";
     $stmt = $this->conn->prepare($sql);
     $stmt->execute(['email'=>$email]);
     $result = $stmt->fetch(PDO::FETCH_ASSOC);

     return $result;
 }

 // Login existing user
 public function login($email){
    $sql = "SELECT email,password FROM users WHERE email = :email AND deleted != 1";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute(['email'=>$email]);   
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

   return $row;
}

// current user in session
public function currentUser($email){
    $sql ="SELECT * FROM users WHERE email = :email and deleted != 1";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute(['email'=>$email]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    return $row;
}

//insert notification
public function notification($uid,$type,$message){
    $sql = "INSERT INTO notification (uid, type, message) VALUES (:uid, :type, :message)";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute(['uid'=>$uid, 'type'=>$type, 'message'=>$message]);
    return true;
}

//Fetch notification
public function fetchNotification($uid){
    $sql = "SELECT * FROM notification WHERE uid = :uid AND type = 'user'";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute(['uid'=>$uid]);

    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return $result;
}


// remove notofication
public function removeNotification($id){
    $sql = "DELETE FROM notification WHERE id = :id AND type = 'user'";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute(['id'=>$id]);
    return true;
}


//Get referif from users table
public function get_refid($uid){
    $sql = "SELECT referid FROM users WHERE id = :uid";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute(['uid'=>$uid]);
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    return $result;
}

//referral commission
public function ref_com($uid,$downline,$depositamount,$investid,$commission,$time){
    $sql = "INSERT INTO referralcomission (uid,downline,depositamount,investid,commission,updatetime) VALUES
            (:uid, :downline, :depositamount, :investid, :commission, :time)";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute(['uid'=>$uid,'downline'=>$downline,'depositamount'=>$depositamount,'investid'=>$investid,
                    'commission'=>$commission,'time'=>$time]);
    return true;
}
//Get user referral commission
public function get_totalrefcom($uid){
    $sql = "SELECT SUM(`referralcomission`.commission) as get_totalrefcom FROM `referralcomission` WHERE `uid`=:uid AND flag = 1 ";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute(['uid'=>$uid]);
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return $result;
}
public function get_totalref($uid){
    $sql = "SELECT count(`users`.id)  FROM `users` WHERE `users`.referid= :uid";
    $stmt =$this->conn->prepare($sql);
    $stmt->execute(['uid'=>$uid]);
    $count = $stmt->fetchColumn();
  
    return $count;
}

 //Get referral list
 public function ref_listtt($uid){
    $sql = "SELECT `users`.username,`users`.id as uid  FROM users WHERE `users`.referid = :uid";
    $stmt =$this->conn->prepare($sql);
    $stmt->execute(['uid'=>$uid]);
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return $result;
}

//Get referral list
 public function ref_list($uid){
    $sql = "SELECT `users`.user_ref,`users`.id as uid  FROM users WHERE `users`.referid = :uid";
    $stmt =$this->conn->prepare($sql);
    $stmt->execute(['uid'=>$uid]);
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return $result;
}

 // Get deposit AMount
    public function depositamount($id){
        $sql = "SELECT SUM(`deposit`.amount) as totalinvest  FROM `deposit` WHERE `deposit`.uid= :id";
        $stmt =$this->conn->prepare($sql);
        $stmt->execute(['id'=>$id]);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $result;
        if($result){
            foreach($result as $row){
                return $row['totalinvest'];
            }
        }else{
            return 0;
        }
    }

 public function depositamounttt($id){
    $sql = "SELECT * FROM referralcomission WHERE `referralcomission`.uid = :id";
    $stmt =$this->conn->prepare($sql);
    $stmt->execute(['id'=>$id]);
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return $result;
}
  
 
//forgot password
public function forgot_password($token,$email){
    $sql = "UPDATE users SET token = :token, token_expire = DATE_ADD(NOW(), INTERVAL
           10 MINUTE) WHERE email = :email";
   
   $stmt = $this->conn->prepare($sql);
   $stmt->execute(['token'=>$token,'email'=>$email]);

   return true;
}
//Reset password user auth
public function reset_pass_auth($email,$token){
    $sql = "SELECT id FROM users WHERE email = :email AND token = :token AND token != ''
           AND token_expire > NOW() AND deleted != 1";
   
   $stmt = $this->conn->prepare($sql);
   $stmt->execute(['email'=>$email,'token'=>$token]);

   $row = $stmt->fetch(PDO::FETCH_ASSOC);
   return $row;
}
// update new paassword
public function update_new_pass($pass, $email){
    $sql = "UPDATE users SET token = '', password = :pass WHERE email = :email AND deleted !=1";

    $stmt = $this->conn->prepare($sql);
    $stmt->execute(['pass'=>$pass,'email'=>$email]);
    return true;
}
//Update profile of user   $firstname,$lastname,$gender,$country,$newImage,$cid
public function update_profile($firstname,$lastname,$gender,$country,$photo,$id){
    $sql = "UPDATE users SET firstname = :firstname, lastname = :lastname, gender = :gender, country = 
           :country, photo = :photo WHERE id = :id AND deleted != 1";
   $stmt = $this->conn->prepare($sql);
   $stmt->execute(['firstname'=>$firstname,'lastname'=>$lastname,'gender'=>$gender,'country'=>$country,'photo'=>$photo,'id'=>$id]);
   return true;
} 


//Change password Auth
public function change_password($pass,$id){
    $sql = "UPDATE users SET password = :pass WHERE id = :id AND deleted != 1";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute(['pass'=>$pass,'id'=>$id]);
    return true;
}

// Verify email of a user
public function verify_email($email){
    $sql = "UPDATE users SET verified = 1 WHERE email = :email AND deleted != 1";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute(['email'=>$email]);
    return true;
}

// XMB Bots
public function xmbdep($uid,$invest_id,$amount,$duration,$target,$flag){
    $sql = "INSERT INTO xmb (uid,invest_id,amount,duration,target,flag) VALUES
               (:uid,:invest_id,:amount,:duration,:target,:flag)";
       $stmt = $this->conn->prepare($sql);
       $stmt->execute(['uid'=>$uid,'invest_id'=>$invest_id,'amount'=>$amount,'duration'=>$duration,'target'=>$target,'flag'=>$flag]);
       return true;
}

// Sports Bots
public function sportdep($uid,$invest_id,$amount,$profit_range,$sports,$outcome, $outcome3,$flag){
    $sql = "INSERT INTO sports_bot (uid,invest_id,amount,profit_range,sports,outcome,outcome3,flag) VALUES
               (:uid,:invest_id,:amount,:profit_range,:sports,:outcome,:outcome3,:flag)";
       $stmt = $this->conn->prepare($sql);
       $stmt->execute(['uid'=>$uid,'invest_id'=>$invest_id,'amount'=>$amount,'profit_range'=>$profit_range,'sports'=>$sports,'outcome'=>$outcome,'outcome3'=>$outcome3,'flag'=>$flag]);
       return true;
}


//XMB request 
public function fetchXMB($uid){
    $sql = "SELECT * FROM xmb WHERE `uid`= :uid ORDER BY id DESC ";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute(['uid'=>$uid]);

    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return $result;
}

//sports arb request 
public function fetchSportsArb($uid){
    $sql = "SELECT * FROM sports_bot WHERE `uid`= :uid ORDER BY id DESC ";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute(['uid'=>$uid]);

    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return $result;
}

//sports arb table
public function fetchSArb($uid){
    $sql = "SELECT * FROM sports_arb WHERE `uid`= :uid ORDER BY id DESC ";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute(['uid'=>$uid]);

    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return $result;
}

//Get sport arb total profit per investmet
public function get_totalprofit($invest_id){
    $sql = "SELECT SUM(`sports_arb`.profit) as totalprofit FROM `sports_arb` WHERE `sports_arb`.invest_id=:invest_id";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute(['invest_id'=>$invest_id]);
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    foreach($result as $row){
        return $row;
    }
    
}

//Get XMB total profit per investmet
public function xmb_totalprofit($invest_id){
    $sql = "SELECT SUM(`xmb`.profit) as totalprofit FROM `xmb` WHERE `xmb`.invest_id=:invest_id";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute(['invest_id'=>$invest_id]);
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    foreach($result as $row){
        return $row;
    }
    
}

    //Get sport arb total profit per 
public function totalSBprofit($uid){
    $sql = "SELECT SUM(`sports_arb`.profit) as totalSBprofit FROM `sports_arb` WHERE `uid`=:uid ";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute(['uid'=>$uid]);
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return $result;
}
//Get sport arb total profit per 
public function totalxmbprofit($uid){
    $sql = "SELECT SUM(`xmb`.profit) as totalxmbprofit FROM `xmb` WHERE `uid`=:uid ";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute(['uid'=>$uid]);
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return $result;
}

//Get sport arb total charge 
public function totalSBcharge($uid){
    $sql = "SELECT SUM(`sports_arb`.charge) as totalSBcharge FROM `sports_arb` WHERE `uid`=:uid";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute(['uid'=>$uid]);
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return $result;
}
//Get xmb total charge 
public function totalxmbcharge($uid){
    $sql = "SELECT SUM(`xmb_add`.charge) as totalxmbcharge FROM `xmb_add` WHERE `uid`=:uid";
    $stmt = $this->conn->prepare($sql);
    $stmt->execute(['uid'=>$uid]);
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return $result;
}

//Get sport arb running amount 
public function totalSBamount($uid){
    $sql = "SELECT SUM(`sports_bot`.amount) as totalSBamount FROM `sports_bot` WHERE `uid`=:uid AND flag != 0" ;
    $stmt = $this->conn->prepare($sql);
    $stmt->execute(['uid'=>$uid]);
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return $result;  
}
//Get xmb running amount 
public function totalxmbamount($uid){
    $sql = "SELECT SUM(`xmb`.amount) as totalxmbamount FROM `xmb` WHERE `uid`=:uid AND flag != 0" ;
    $stmt = $this->conn->prepare($sql);
    $stmt->execute(['uid'=>$uid]);
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return $result;  
}

    //Stop Sport Bot from Databse
    public function stopSBot($id, $val){
        $sql = "UPDATE sports_bot SET flag = $val WHERE id = :id";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute(['id'=>$id]);

        return true;
    }

    //fetch Sport details by invest_id
    public function fetchSBotDetails($uid,$invest_id){
        $sql = "SELECT * FROM sports_arb WHERE uid = :uid AND invest_id = :invest_id";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute(['uid'=>$uid,'invest_id'=>$invest_id]);

        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $result;
    }
    //fetch Xmb details by invest_id
    public function fetchXmxtDetails($invest_id){
        $sql = "SELECT * FROM xmb WHERE invest_id = :invest_id";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute(['invest_id'=>$invest_id]);

        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return $result;
    }


    //Insert into Deposit Table
    public function topup($uid,$amount,$paymentway,$transactionid,$time){
        $sql = "INSERT INTO deposit (uid, amount, payment_way, transaction_id, update_time) VALUES 
                 (:uid, :amount, :paymentway, :transactionid, :time) ";
         $stmt = $this->conn->prepare($sql);
         $stmt->execute(['uid'=>$uid, 'amount'=>$amount, 'paymentway'=>$paymentway, 'transactionid'=>$transactionid, 'time'=>$time]);
         $LAST_ID = $this->conn->lastInsertId();
    }      


     // Withdraw
     public function withdraw($uid, $gateway,$account, $amount,$time){
        $sql = "INSERT INTO withdraw (uid,gateway,accountid,amount,updatetime) VALUES
               (:uid,:gateway,:account,:amount,:time)";
       $stmt = $this->conn->prepare($sql);
       $stmt->execute(['uid'=>$uid,'gateway'=>$gateway,'account'=>$account,'amount'=>$amount,'time'=>$time]);
       return true;
    }

     // Total withdraw
     public function totalwithdraw($id){
        $sql = "SELECT SUM(`withdraw`.amount) as totalwithdraw FROM `withdraw` WHERE `uid`= :id ";
        $stmt = $this->conn->prepare($sql);
         $stmt->execute(['id'=>$id]);
         $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
         return $result;
     }

     // Total deposit
     public function totaldeposit($id){
        $sql = "SELECT SUM(`deposit`.amount) as totaldeposit FROM `deposit` WHERE `uid`= :id AND flag=1";
        $stmt = $this->conn->prepare($sql);
         $stmt->execute(['id'=>$id]);
         $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
         return $result;
     }



      // Withdraw History
    public function withdraw_history($uid){
        $sql = "SELECT `withdraw`.* FROM `withdraw` WHERE `uid`= :uid ORDER BY id DESC";
        $stmt =$this->conn->prepare($sql);
        $stmt->execute(['uid'=>$uid]);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $result;
    }

    // Deposit History
    public function deposit_history($uid){
        $sql = "SELECT `deposit`.* FROM `deposit` WHERE `uid`= :uid ORDER BY id DESC";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute(['uid'=>$uid]);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $result;
    }


   

















}
?>