<?php
include 'config/db_connection.php';

class SqlHandler {
 public $server;
 const username="root";
 const password="";
 const database="IP2";


    function __construct() {
        $this->server = $this->serverConnection();
    }

    private function serverConnection(){
        $connection =  mysql_connect("localhost", self::username,self::password)
                or die("Unable to connect to sql server");
        return $connection;
        }

    /*
     * CREATE / DROP DATABASE SECTION
     */

    function dropDatabase(){
        $drop_database_query = "DROP DATABASE IF EXISTS ".self::database;
        mysql_query($drop_database_query) or die(mysql_error());
    }

    function createDatabase(){
        $create_database_query = "CREATE DATABASE IF NOT EXISTS ".self::database;
        mysql_query($create_database_query,$this->server) or die(mysql_error());
        mysql_select_db(self::database) or die("Unable to select database");
    }


    /*
     * CREATE TABLES SECTION
     */

   /**
    * Creates all tables.
    */

    function createTbls(){
       $this->createUserTbl();
       $this->createProductTbl();
       $this->createCustomerTbl();
       $this->createPurchaseTbl();
       $this->createPersonTbl();
    }

    function createPersonTbl(){
        $create_person_tbl_query ="CREATE TABLE IF NOT EXISTS userTbl(
        id INT NOT NULL AUTO_INCREMENT,
        name TEXT,
        address TEXT,
        phone_number INT,
        customer_id INT,
        PRIMARY KEY(id))ENGINE=InnoDB";

        mysql_select_db(self::database) or die("Unable to select database");
        mysql_query($create_person_tbl_query,$this->server) or die(mysql_error());
    }

    function createCustomerTbl(){
        $create_customer_tbl_query ="CREATE TABLE IF NOT EXISTS customerTbl(
        id INT NOT NULL AUTO_INCREMENT,
        PRIMARY KEY(id))ENGINE=InnoDB";

        mysql_select_db(self::database) or die("Unable to select database");
        mysql_query($create_customer_tbl_query,$this->server) or die(mysql_error());
    }

    function createUserTbl(){
        $create_user_tbl_query = "CREATE TABLE IF NOT EXISTS userTbl(
        id INT NOT NULL AUTO_INCREMENT,
        name TEXT,
        password TEXT,
        PRIMARY KEY(id),
        pid INT)ENGINE=InnoDB";
        mysql_select_db(self::database) or die("Unable to select database");
        mysql_query($create_user_tbl_query,$this->server) or die(mysql_error());
    }

    function createPurchaseRowTbl(){
        $create_purchase_row_query = "CREATE TABLE IF NOT EXISTS purchaserowtbl (
           purchase_id  INT,
           product_id INT)ENGINE=InnoDB";

        mysql_select_db(self::database) or die("Unable to select database");
        mysql_query($create_purchase_row_query,$this->server) or die(mysql_error());
    }

    function createPurchaseTbl(){
        $create_purchase_tbl_query = "CREATE TABLE IF NOT EXISTS purchaseTbl(
            id INT NOT NULL AUTO_INCREMENT,
            timestamp TIMESTAMP,
            customer_id INT,
            PRIMARY KEY(id))ENGINE=InnoDB";

        mysql_select_db(self::database) or die("Unable to select database");
        mysql_query($create_purchase_tbl_query,$this->server) or die(mysql_error());
    }

    function createProductTbl(){
        $create_product_tbl_query = "CREATE TABLE IF NOT EXISTS productTbl(
            id INT NOT NULL AUTO_INCREMENT,
            name TEXT,
            price FLOAT,
            imgpath TEXT,
            purchase_id INT,
            PRIMARY KEY(id))ENGINE=InnoDB;";
        mysql_select_db(self::database) or die("Unable to select database");
        mysql_query($create_product_tbl_query,$this->server) or die(mysql_error());
    }

    /*
     *INSERT VALUES SECTION
     */

    /**
     *Creates a user in userTbl.
     * @param type $name The user name.
     * @param type $password  User password.
     */
    function createUser($name, $password){
        $create_user_query = "INSERT INTO userTbl (name,password)
        VALUES ('$name','$password')";
        mysql_select_db(self::database) or die(mysql_error());
        mysql_query($create_user_query,$this->server) or die(mysql_error());
    }

    function createCustomer(){
        $create_customer_query = "INSERT INTO customerTbl VALUES()";
        mysql_select_db(self::database) or die(mysql_error());
        mysql_query($create_customer_query,$this->server) or die(mysql_error());
    }

    function createPurchase($customer_id){
        $create_purchase_query = "INSERT INTO purchaseTbl (customer_id,timestamp)
            VALUES('$customer_id','TIMESTAMP()')";
        mysql_select_db(self::database) or die(mysql_error());
        mysql_query($create_purchase_query,$this->server) or die(mysql_error());
    }

    function createPurchaseRow($purchase_id,$product_id){
        $create_purchase_query = "INSERT INTO purchaseRowTbl (purchase_id,product_id)
            VALUES('$purchase_id','$product_id')";
    }

    /**
     *Creates a Product in productTbl.
     * @param string $name name
     * @param float $price price
     * @param string $description description
     * @param int $purchase_id id from purchase
     * @param string $imgpath path to the image. Default value is "false".
     */
    function createProduct($name,$price,$description,$imgpath="false",$purchase_id = NULL){
        $create_product = "INSERT INTO productTbl (name,price,imgpath,purchase_id)
        VALUES ('$name','$price','$imgpath','$purchase_id')";
        mysql_select_db(self::database) or die(mysql_error());
        mysql_query($create_product,$this->server) or die(mysql_error());
    }

    /*
     * SELECT VALUES SECTION
     */

    /**
     * SELECT * FROM $table.
     * @return Array Returns an Associative array with [column] -> value from $table.
     */
    function selectAllFromTable($table){
        $select_users= "SELECT * FROM $table";
         mysql_select_db(self::database) or die("Unable to select database");
         $users = mysql_query($select_users,$this->server) or die(mysql_error());
         return $users;
    }
}

?>