<?php
class db {
 const hostname = 'localhost';
 const user = 'buyselli_alex';
 const pass = 'MarkoAlex9555';
 const dbname = 'buyselli_prvabaza';
 private $dbh;

 function __construct() {
 try {
 $conn_string="mysql:host=".self::hostname.";dbname=".self::dbname;
 $this->dbh = new PDO($conn_string, self::user, self::pass);

 }
 catch(PDOException $e) {
 echo "ERROR: ";
 echo $e->getMessage();
 }
 }
 function __destruct() {
 $this->dbh = null;
 }

 public function login($email,$password) {
 try {
 $pass=sha1($password);
 $sql = "SELECT * FROM user where email='$email' and password='$pass'";
 $pdo = $this->dbh->query($sql);
 $n = $pdo->fetch(PDO::FETCH_ASSOC);
 if ($n==false)
 return null;
  $sql = "UPDATE `user` SET `access_token`='".$this->randomString()."' WHERE `email`='".$email."'";
  $pdo = $this->dbh->prepare($sql);
  $pdo->execute();

 $sql = "SELECT `id`, `email`, `role`, `owner_name`, `owner_surname`, `access_token` FROM `user` where `email`='$email' and `password`='$pass'";
 $pdo = $this->dbh->query($sql);
 $n = $pdo->fetch(PDO::FETCH_ASSOC);
 return $n;
 }
 catch(PDOException $e) {
 echo "ERROR: ";
 echo $e->getMessage();
 }
 }

  public function pets($id) {
  try {
 $sql = "SELECT * FROM pet where user_id='$id'";
 $pdo = $this->dbh->query($sql);
 $n = $pdo->fetch(PDO::FETCH_ASSOC);
 return $n;
 }
 catch(PDOException $e) {
 echo "ERROR: ";
 echo $e->getMessage();
 }
 }

 public function log($str){
 try {
		 $str=addslashes($str);
         $sql = "INSERT INTO log(str) ";
         $sql.= "VALUES ('$str')";
         $this->dbh->exec($sql);
     }


 catch(PDOException $e) {
 echo "ERROR: ";
 echo $e->getMessage();
 return false;
 }
 }

public function randomString()
{
    $characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    $randstring = '';
    for ($i = 0; $i < 10; $i++) {
        $randstring .= $characters[rand(0, strlen($characters)-1)];
    }
    return $randstring;
}

 public function alreadyRegistered($email)
 {
     try{
     $sql = "SELECT * FROM user where email='$email'";
     $pdo = $this->dbh->query($sql);
     $n = $pdo->fetch(PDO::FETCH_ASSOC);
     if ($n==false)
     return false;
     else return true;
     }
     catch(PDOException $e) {
     echo "ERROR: ";
     echo $e->getMessage();
     }
 }

 public function addUser($email,$password,$role,$owner_name,$owner_surname,$animal_name,$age_years,$age_months,$sex,$color,$image_url,$race)
 {
 try {
        if ($this->alreadyRegistered($email))
        {
            return null;
        }
        $pass=sha1($password);
         $sql = "INSERT INTO user(email,password,role,owner_name,owner_surname) ";
         $sql.= "VALUES ('$email','$pass','$role','$owner_name','$owner_surname')";
         $this->dbh->exec($sql);
         $idD = $this->login($email,$password);
         $id= $idD['id'];
         $sql = "INSERT INTO pet(user_id,animal_name,age_years,age_months,sex,color,image_url,race) ";
         $sql.= "VALUES ('$id','$animal_name','$age_years','$age_months','$sex','$color','$image_url','$race')";
         $this->dbh->exec($sql);
         return $this->login($email,$password);
 }
 catch(PDOException $e) {
 echo "ERROR: ";
 echo $e->getMessage();
 return false;
 }
 }



 public function checkToken($token) {
 try {
 $sql = "SELECT * FROM user where access_token='$token'";
 $pdo = $this->dbh->query($sql);
 $n = $pdo->fetch(PDO::FETCH_ASSOC);
 if ($n==false)
 return false;
 return $n;
 }
 catch(PDOException $e) {
 echo "ERROR: ";
 echo $e->getMessage();
 }
 }

  public function getPet($user_id) {
 try {
 $sql = "SELECT * FROM pet where user_id='$user_id'";
 $pdo = $this->dbh->query($sql);
 $n = $pdo->fetch(PDO::FETCH_ASSOC);
 if ($n==false)
 return false;
 return $n;
 }
 catch(PDOException $e) {
 echo "ERROR: ";
 echo $e->getMessage();
 }
 }

 public function updateUser($access_token,$email,$pass,$role,$owner_name,$owner_surname,$animal_name,$age_years,$age_months,$sex,$color,$image_url,$race)
 {
 try {

        if (!($user=$this->checkToken($access_token)))
        {
            return false;
        }
         $password=sha1($pass);
         $sql = "UPDATE user SET owner_name=:owner_name, owner_surname=:owner_surname ";
         $sql.= "WHERE access_token=:access_token";
         $pdo = $this->dbh->prepare($sql);
         $pdo->bindParam(':access_token', $access_token);
         $pdo->bindParam(':owner_name', $owner_name);
         $pdo->bindParam(':owner_surname', $owner_surname);
         $pdo->bindParam(':access_token', $access_token);
         $pdo->execute();

         $sql = "UPDATE pet SET animal_name=:animal_name, age_years=:age_years, age_months=:age_months, sex=:sex, color=:color, race=:race";
         $sql.= " WHERE user_id=:user_id";
         $pdo = $this->dbh->prepare($sql);
         $pdo->bindParam(':animal_name', $animal_name);
         $pdo->bindParam(':age_years', $age_years);
          $pdo->bindParam(':age_months', $age_months);
         $pdo->bindParam(':sex', $sex);
         $pdo->bindParam(':color', $color);
         $pdo->bindParam(':race', $race);
         $pdo->bindParam(':user_id', $user['id']);
         $this->log($sql);
         $pdo->execute();
         return null;
 }
 catch(PDOException $e) {
 echo "ERROR: ";
 echo $e->getMessage();
 return false;
 }
 }

 public function changeImage($access_token,$image_url) {
 try {
         $user = $this->checkToken($access_token);
         $user_id=$user['id'];
         $pet=$this->getPet($user_id);
         if (!empty($pet['image_url']))
         {
             unlink("/home/buyselli/public_html/api/v1/".$pet['image_url']);
             $this->log("/home/buyselli/public_html/api/v1/".$pet['image_url']);
         }
		 $sql = "UPDATE pet SET image_url=:image_url ";
		 $sql.= "WHERE user_id=:user_id";
		 $pdo_izraz = $this->dbh->prepare($sql);
		 $pdo_izraz->bindParam(':image_url', $image_url);
		 $pdo_izraz->bindParam(':user_id', $user_id);
		 $pdo_izraz->execute();
		 return null;
 }
 catch(PDOException $e) {
 echo "ERROR: ";
 echo $e->getMessage();
 return false;
 }
 }

 public function addData($access_token,$longitude,$latitude,$dateTimeStamp,$data) {
 try {
         $this->log($access_token);
         $user = $this->checkToken($access_token);
         $user_id=$user['id'];
         $pet=$this->getPet($user_id);
         $pet_id=$pet['id'];
         for($i=1;$i<25;$i++)
         {
            $number=$data[$hour=strval($i)];
            $sql = "INSERT INTO data(pet_id,date,latitude,longitude,hour,steps) ";
            $sql.= "VALUES ('$pet_id', '$dateTimeStamp','$latitude','$longitude','$hour','$number')";
            $this->log($sql);
            $this->dbh->exec($sql);
         }
         return null;
 }
 catch(PDOException $e) {
 echo "ERROR: ";
 echo $e->getMessage();
 return false;
 }
 }

 public function getData($access_token)
 {
    // $user = $this->checkToken($access_token);
    // $user_id=$user['id'];
    // $pet=$this->getPet($user_id);
    // $pet_id=$pet['id'];
    $pet_id=62;
    $sql = "SELECT h.id,d.steps FROM hour h left join (SELECT id,steps,hour FROM data WHERE DATE(date)=CURRENT_DATE() AND pet_id=$pet_id)
            as d on h.id=d.hour ORDER BY h.id";
    $pdo = $this->dbh->query($sql);
    $n1 = $pdo->fetchALL(PDO::FETCH_ASSOC);
    $json=json_encode($n);
    $this->log($json);
     $string="";
    $sql = "SELECT dd.id,dd.name,steps
            FROM (SELECT WEEKDAY(Date(date)) as'DAY',SUM(steps) as 'Steps'
            FROM data WHERE pet_id=$pet_id AND YEARWEEK(DATE(date),1)=YEARWEEK(CURRENT_DATE(),1)
            GROUP BY(DATE(date))) AS d right join day dd ON d.DAY=(dd.id-1) order by dd.id
            ";
    $pdo = $this->dbh->query($sql);
    $n2 = $pdo->fetchALL(PDO::FETCH_ASSOC);
    $json=json_encode($n);
    $this->log($json);
    $sql ="SELECT dd.id,steps FROM (SELECT DAY(Date(date)) as'DAY',SUM(steps) as 'steps'
            FROM data WHERE $pet_id=pet_id AND MONTH(DATE(date))=MONTH(CURRENT_DATE())
            GROUP BY DATE(date)) AS d right join month dd ON d.DAY=dd.id order by dd.id";
    $pdo = $this->dbh->query($sql);
    $n3 = $pdo->fetchALL(PDO::FETCH_ASSOC);
    $json=json_encode($n);
    $this->log($json);
    $sql ="SELECT dd.id,dd.month,steps FROM (SELECT MONTH(Date(date)) as'month',SUM(steps) as 'steps'
           FROM data WHERE pet_id=$pet_id AND YEAR(DATE(date))=YEAR(CURRENT_DATE())
           GROUP BY MONTH(DATE(date))) AS d right join year dd ON d.month=(dd.id) order by dd.id";
    $pdo = $this->dbh->query($sql);
    $n4 = $pdo->fetchALL(PDO::FETCH_ASSOC);
    $json=json_encode($n);
    $this->log($json);
    $string='{ "day": ';
    $string.="{ ";
    foreach($n1 as $day)
    {

        $string.= '"'.$day['id'].'":'.'"';
        if ($day['steps']!="null") $string.=$day['steps'];
        $string.='"';
        $string.=" ,";
    }
    $string=rtrim($string,",");
    $string.='},';

    $string.=' "week": ';
    $string.="{ ";
    foreach($n2 as $day)
    {
        $string.= '"'.$day['name'].'":'.'"';
        if ($day['steps']!="null") $string.=$day['steps'];
        $string.='"';
        $string.=" ,";
    }
    $string=rtrim($string,",");
    $string.='},';

    $string.=' "month": ';
    $string.="{ ";
    foreach($n3 as $day)
    {
        $string.= '"'.$day['id'].'":'.'"';
        if ($day['steps']!="null") $string.=$day['steps'];
        $string.='"';
        $string.=" ,";
    }
    $string=rtrim($string,",");
    $string.='},';

    $string.=' "year": ';
    $string.="{ ";
    foreach($n4 as $day)
    {
        $string.= '"'.$day['id'].'":'.'"';
        if ($day['steps']!="null") $string.=$day['steps'];
        $string.='"';
        $string.=" ,";
    }
    $string=rtrim($string,",");
    $string.='}}';

    return $string;
 }
}
?>