<?php
/**
 * @package: EGPS - Easy Sell for Google Photo
 *
 */

 if(!defined('ABSPATH')){
    exit; // Exit if accessed directly
}

class EgpsDatabase{
    function __construct() {
        register_activation_hook( EGPS_BASE_FILE, [$this, 'egps_create_db'] );

        //create folders for photos
        if(!file_exists(EGPS_ABSPATH . 'wp-content/uploads/egps/albums')){
            mkdir(EGPS_ABSPATH . 'wp-content/uploads/egps/albums', 0777, true);
        }
        if(!file_exists(EGPS_ABSPATH . 'wp-content/uploads/egps/photos')){
            mkdir(EGPS_ABSPATH . 'wp-content/uploads/egps/photos', 0777, true);
        }
        if(!file_exists(EGPS_ABSPATH . 'wp-content/uploads/egps/index.html')){
            $myfile = fopen(EGPS_ABSPATH . 'wp-content/uploads/egps/index.html', "w") or die("Unable to open file!");
            $txt = "ACCESS DENNIED!\n";
            fwrite($myfile, $txt);
            fclose($myfile);
        }
        if(!file_exists(EGPS_ABSPATH . 'wp-content/uploads/egps/albums/index.html')){
            $myfile = fopen(EGPS_ABSPATH . 'wp-content/uploads/egps/albums/index.html', "w") or die("Unable to open file!");
            $txt = "ACCESS DENNIED!\n";
            fwrite($myfile, $txt);
            fclose($myfile);
        }
        if(!file_exists(EGPS_ABSPATH . 'wp-content/uploads/egps/photos/index.html')){
            $myfile = fopen(EGPS_ABSPATH . 'wp-content/uploads/egps/photos/index.html', "w") or die("Unable to open file!");
            $txt = "ACCESS DENNIED!\n";
            fwrite($myfile, $txt);
            fclose($myfile);
        }
        if(!file_exists(__DIR__ . '/progress.json')){
            $myfile = fopen(__DIR__ . '/progress.json', "w") or die("Unable to open file!");
            $txt = "";
            fwrite($myfile, $txt);
            fclose($myfile);
        }

  }

    function egps_create_db() {
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();

        $tablename_albums = $wpdb->prefix . 'egps_albums';
        $sql_albums = "CREATE TABLE IF NOT EXISTS $tablename_albums (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            egps_id text,
            egps_title text,
            egps_album_slug varchar(255),
            egps_productUrl text,
            egps_coverPhotoBaseUrl text,
            egps_mediaItemsCount text,
            egps_exclusiveCount text,
            egps_coverPhotoMediaItemId text,
            egps_album_modified text,
            egps_item_for_delete varchar(1),
            egps_album_show varchar(1),
            egps_updated datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            name tinytext NOT NULL,
            text text NOT NULL,
            url varchar(55) DEFAULT '' NOT NULL,
            PRIMARY KEY  (id)
        ) $charset_collate;";


        $tablename_photos = $wpdb->prefix . 'egps_photos';
        $sql_photos = "CREATE TABLE IF NOT EXISTS $tablename_photos (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            egps_id text,
            egps_album_id text,
            egps_exclusive text,
            egps_productUrl text,
            egps_baseUrl text,
            egps_mimeType text,
            egps_mediaItems text,
            egps_mediaMetadata text,
            egps_mediaCamera text,
            egps_mediaCameraUrl text,
            egps_mediaSize text,
            egps_filename text,
            egps_description text,
            egps_purchase_count int,
            egps_like_count int,
            egps_photo_show varchar(1),
            egps_item_for_delete varchar(1),
            egps_created datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            egps_updated datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            name tinytext NOT NULL,
            text text NOT NULL,
            url varchar(55) DEFAULT '' NOT NULL,
            PRIMARY KEY  (id)
        ) $charset_collate;";


        $tablename_exclusive = $wpdb->prefix . 'egps_exclusive';
        $sql_exclusive = "CREATE TABLE IF NOT EXISTS $tablename_exclusive (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            egps_photo_id text,
            egps_purchase_id text,
            time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            note text NOT NULL,
            url varchar(55) DEFAULT '' NOT NULL,
            PRIMARY KEY  (id)
        ) $charset_collate;";

        $tablename_tagcloud = $wpdb->prefix . 'egps_tagcloud';
        $sql_tagcloud = "CREATE TABLE IF NOT EXISTS $tablename_tagcloud (
            id int NOT NULL AUTO_INCREMENT,
            egps_tag text,
            egps_tag_count int,
            time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            note text NOT NULL,
            PRIMARY KEY  (id)
        ) $charset_collate;";


        $tablename_dwnld = $wpdb->prefix . 'egps_download';
        $sql_dwnld = "CREATE TABLE IF NOT EXISTS $tablename_dwnld (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            egps_id text,
            egps_url text,
            egps_type text,
            time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            name tinytext NOT NULL,
            text text NOT NULL,
            url varchar(55) DEFAULT '' NOT NULL,
            PRIMARY KEY  (id)
        ) $charset_collate;";

        $tablename_album_changes = $wpdb->prefix . 'egps_albumchanges';
        $sql_album_changes = "CREATE TABLE IF NOT EXISTS $tablename_album_changes (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            egps_id text,
            egps_type text,
            egps_mediaItemsCount text,
            egps_url text,
            time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            text text NOT NULL,
            PRIMARY KEY  (id)
        ) $charset_collate;";

        $table_name_customers = $wpdb->prefix . 'egps_customers';
        $sql_customers = "CREATE TABLE IF NOT EXISTS $table_name_customers (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            customer_email text,
            customer_pass varchar(255),
            customer_name text,
            customer_lastLogin varchar(20),
            customer_active varchar(5),
            customer_email_enabled varchar(5),
            time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            text text NOT NULL,
            PRIMARY KEY  (id)
        ) $charset_collate;";

        $table_name_orders = $wpdb->prefix . 'egps_orders';
        $sql_orders = "CREATE TABLE IF NOT EXISTS $table_name_orders (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            customer_id mediumint(9),
            customer_email text,
            customer_name varchar(255),
            customer_country varchar(255),
            order_items text,
            order_exclusive varchar(1),
            dwld_count int(3),
            dwld_max int(3),
            order_invoice_number varchar(20),
            purchase_currency varchar(10),
            purchase_price int,
            order_invoice_link varchar(255),
            time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            text text NOT NULL,
            PRIMARY KEY  (id)
        ) $charset_collate;";

        $table_name_invoices = $wpdb->prefix . 'egps_invoices';
        $sql_invoices = "CREATE TABLE IF NOT EXISTS $table_name_invoices (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            customer_id mediumint(9),
            customer_email text,
            order_invoice_number varchar(100),
            order_invoice_price varchar(20),
            order_invoice_link varchar(255),
            time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            text text NOT NULL,
            PRIMARY KEY  (id)
        ) $charset_collate;";

        $table_name_cart = $wpdb->prefix . 'egps_cart';
        $sql_cart = "CREATE TABLE IF NOT EXISTS $table_name_cart (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            customer_id mediumint(9),
            customer_email text,
            cart_items text,
            time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            text text NOT NULL,
            PRIMARY KEY  (id)
        ) $charset_collate;";


        require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        dbDelta( $sql_albums );
        dbDelta( $sql_photos );
        dbDelta( $sql_exclusive );
        dbDelta( $sql_dwnld );
        dbDelta( $sql_album_changes );
        dbDelta( $sql_customers );
        dbDelta( $sql_orders );
        dbDelta( $sql_invoices);
        dbDelta( $sql_cart );
        dbDelta( $sql_tagcloud );

        add_option( 'egps_db_version', EGPS_DB_VER );
    }


    public static function egps_insert_to_db($table_name, $datas = array()){
        global $wpdb;

        $table_name = $wpdb->prefix . $table_name;

        try{

            $wpdb->insert( $table_name, $datas );

            return $wpdb->insert_id;

        }catch (Error $error){
            Egps::egps_vardump($error);
        }
        return null;
    }

    public static function egps_insert_to_db_if_not_exists($table_name, $datas = array()){
        global $wpdb;
        
        if(EgpsDatabase::egps_get_row($table_name, 'egps_url', $datas['egps_url']) == null){
            try{

                $wpdb->insert( $wpdb->prefix . $table_name, $datas );

            }catch (Error $error){
                Egps::egps_vardump($error);
            }
        }

        return;
    }

    public static function egps_update_db_table($table, $data = array(), $where = array()){
        global $wpdb;

        $table = $wpdb->prefix . $table;

        try{
            
            $wpdb->update( $table, $data, $where, $format = null, $where_format = null );
            
        }catch (Error $error){
            Egps::egps_vardump($error);
        }
        
        return;
    }

    public static function egps_update_db_table_allrows($field, $value){
        global $wpdb;

        $table = $wpdb->prefix . "egps_albums";

        try{
            
            $wpdb->query( 
                $wpdb->prepare( 
                    "UPDATE $table
                     SET %i = %s",
                     $field,
                     $value
                )
            );
            
        }catch (Error $error){
            Egps::egps_vardump($error);
        }
        
        return;
   
    }
    
    public static function egps_check_database(){}
    
    public static function egps_check_table($tablename){}

    public static function egps_count_table_rows($talbe_name = "orders"){
        global $wpdb;
        
        $tablename = $wpdb->prefix . 'egps_' . $talbe_name;
        
        try{
            $query = $wpdb->prepare(
                "SELECT COUNT(*) 
                FROM %i",
                $tablename
            );

            $num_rows = $wpdb->get_var($query);

            return $num_rows;

        }catch (Error $error){
            Egps::egps_vardump($error);
        }
        return null;
    }

    public static function egps_count_table_rows_distinct($talbe_name, $field_name){
        global $wpdb;
        
        $tablename = $wpdb->prefix . 'egps_' . $talbe_name;
        
        try{
            $query = $wpdb->prepare(
                "SELECT COUNT(DISTINCT(%i)) 
                FROM %i",
                $field_name,
                $tablename
            );

            $num_rows = $wpdb->get_var($query);

            return $num_rows;

        }catch (Error $error){
            Egps::egps_vardump($error);
        }
        return null;
    }

    public static function egps_count_table_rows_where($talbe_name, $where = ""){
        global $wpdb;
        
        $tablename = $wpdb->prefix . $talbe_name;

        try{
            $query = $wpdb->prepare(
                "SELECT COUNT(*) 
                FROM %i
                WHERE egps_album_id =%s",
                $tablename,
                $where
            );

            $num_rows = $wpdb->get_var($query);

            return $num_rows;

        }catch (Error $error){
            Egps::egps_vardump($error);
        }
        return null;
    }

    public static function egps_count_table_rows_where_exclusive($talbe_name, $where = ""){
        global $wpdb;
        
        $tablename = $wpdb->prefix . $talbe_name;

        try{
            $query = $wpdb->prepare(
                "SELECT COUNT(*) 
                FROM %i 
                WHERE egps_album_id = %s 
                AND egps_exclusive = '1' ",
                $tablename,
                $where
            );

            $num_rows = $wpdb->get_var($query);

            return $num_rows;

        }catch (Error $error){
            Egps::egps_vardump($error);
        }
        return null;
    }

    public static function egps_get_table_rows($table_name, $field = 'id', $asc = 'ASC'){
        global $wpdb;
        $wpdb->show_errors();

        $table_name = $wpdb->prefix . $table_name;

        try{

            $query = $wpdb->prepare(
                "SELECT * 
                FROM %i 
                ORDER BY %s %s",
                $table_name,
                $field,
                $asc
            );
            $ret_val = $wpdb->get_results($query);

            return $ret_val;

        }catch (Error $error){
            Egps::egps_vardump($error);
        }
        return null;
    }

    public static function egps_get_table_rows_where($table_name, $field, $data, $order = 'id', $asc = 'ASC'){
        global $wpdb;
        $wpdb->show_errors();

        $table_name = $wpdb->prefix . $table_name;

        try{

            $query = $wpdb->prepare(
                "SELECT * 
                FROM %i 
                WHERE %i = %s 
                ORDER BY %s %s",
                $table_name,
                $field,
                $data,
                $order,
                $asc

            );

            $ret_val = $wpdb->get_results($query);

            return $ret_val;

        }catch (Error $error){
            Egps::egps_vardump($error);
        }
        return null;
    }

    public static function egps_get_table_rows_where_in_posts(){

        global $wpdb;
        $wpdb->show_errors();

        $table_name = $wpdb->prefix . "posts";

        try{

            $query = $wpdb->prepare(
                "SELECT * 
                FROM %i
                WHERE post_name IN ('egpscamera', 'egpstag', 'photoalbum') 
                ORDER BY post_name ASC",
                $table_name
            );

            $ret_val = $wpdb->get_results($query);

            return $ret_val;

        }catch (Error $error){
            Egps::egps_vardump($error);
        }
        return null;
    }

    public static function egps_get_table_rows_where_limit(
            $table_name, 
            $where_field = '', 
            $where_op = 'equal',
            $where_data = '',
            $order = 'id', 
            $asc = 'ASC', 
            $offset = '', 
            $limit = ''){

        global $wpdb;
        $wpdb->show_errors();

        $table_name = $wpdb->prefix . $table_name;

        try{

            switch($where_op){
                case "not_equal":
                    $equal = "!=";
                    break;
                
                default:
                    $equal = "=";
            }
            switch($asc){
                case "DESC":
                    $orderasc = "DESC";
                    break;

                default:
                    $orderasc = "ASC";
            }

            $query = $wpdb->prepare(
                "SELECT * 
                FROM %i
                WHERE %i $equal %s
                ORDER BY %i $orderasc
                LIMIT  %d, %d",
                $table_name,
                $where_field,
                $where_data,
                $order, 
                $offset, 
                $limit
            );

            $ret_val = $wpdb->get_results($query);

            return $ret_val;

        }catch (Error $error){
            Egps::egps_vardump($error);
        }
        return null;
    }

    public static function egps_get_row($table_name, $field, $parameter){
        global $wpdb;
        $wpdb->show_errors();

        $table_name = $wpdb->prefix . $table_name;

        try{
            
            $query = $wpdb->prepare(
                "SELECT * 
                FROM %i 
                WHERE %i = %s 
                ORDER BY id DESC",
                $table_name,
                $field,
                $parameter
            );

            $ret_val = $wpdb->get_row($query);
            return $ret_val;

        }catch (Error $error){
            Egps::egps_vardump($error);
        }
        return null;

    }

    public static function egps_delete_record($table_name, $id){
        global $wpdb;
        $wpdb->show_errors();

        $table_name = $wpdb->prefix . $table_name;

        try{

            $query = $wpdb->prepare(
                "DELETE 
                FROM %i
                WHERE id = %d ",
                $table_name,
                $id
            );

            $wpdb->query($query);

        }catch (Error $err){
            Egps::egps_vardump($err);
        }

        return;

    }

    public static function egps_delete_record_multiple($table_name, $search_field, $search_item){
        global $wpdb;
        $wpdb->show_errors();

        $table_name = $wpdb->prefix . $table_name;

        try{

            $query = $wpdb->prepare(
                "DELETE 
                FROM %i  
                WHERE %i = %s ",
                $table_name,
                $search_field,
                $search_item
            );

            $wpdb->query($query);

        }catch (Error $err){
            Egps::egps_vardump($err);
            return $err;
        }

        return "ok";

    }

    public static function egps_get_sum_price_from_db($email){
        global $wpdb;
        $wpdb->show_errors();

        $table_name = $wpdb->prefix . "egps_cart";

        try{

            $query = $wpdb->prepare(
                "SELECT text, COUNT(*) as count 
                FROM %i 
                WHERE customer_email = %s
                GROUP BY text",
                $table_name,
                $email
            );

            $ret_val = $wpdb->get_results($query);
            $ret_val = array($ret_val);

            $data = get_option('egps_prices');
            $retsum = 0;

            if($ret_val != null){
                foreach($ret_val[0] as $rv){
                    
                    if($rv->text == 'exclusive'){
                        $retsum += ((int)$rv->count * (int)$data['exclusivePrice']); 
                    }
                    if($rv->text == 'normal'){
                        $retsum += ((int)$rv->count * (int)$data['regularPrice']); 
                    }
                    
                }
            }

            return $retsum;

        }catch (Error $error){
            Egps::egps_vardump($error);
        }
        return null;
    }

    public static function egps_inc_photo_purchase($photoid){
        global $wpdb;
        $wpdb->show_errors();

        $table_name = $wpdb->prefix . "egps_photos";

        try{

            $query = $wpdb->prepare(
                "UPDATE %i 
                SET egps_purchase_count=egps_purchase_count + 1 
                WHERE egps_id=%s",
                $table_name,
                $photoid
            );

            $ret_val = $wpdb->get_results($query);

            return $ret_val;

        }catch (Error $error){
            Egps::egps_vardump($error);
        }
        return null;
    }

    public static function egps_update_tag_cloud($tags, $increase = true){
        global $wpdb;

        $table_name = $wpdb->prefix . "egps_tagcloud";

        $tag_array = explode(" ", $tags);

        foreach($tag_array as $tag){

            $tag = str_replace("\n", "", $tag);
            $tag = str_replace("\r", "", $tag);

            if($tag != "" && $tag != "#"){

                $query = $wpdb->prepare(
                    "SELECT * 
                    FROM %i 
                    WHERE egps_tag = %s 
                    LIMIT 1",
                    $table_name,
                    $tag
                );
                
                $tag_in_db = $wpdb->get_row($query);

                $data['egps_tag'] = $tag;
                
                if( $tag_in_db == null ){
                    try{

                        $data['egps_tag_count'] = 1;

                        $wpdb->insert( $table_name, $data );

                    }catch (Error $error){
                        Egps::egps_vardump($error);
                    }

                }else{

                    if($increase){
                       try{

                            $data_update['egps_tag_count'] = $tag_in_db->egps_tag_count + 1;
                            $where_array = array('egps_tag' => $tag );

                            EgpsDatabase::egps_update_db_table( "egps_tagcloud", $data_update, $where_array);

                        }catch (Error $error){
                            Egps::egps_vardump($error);
                        }
                     
                    }
                }

            }
        }
        
        return;
    }

}

new EgpsDatabase();

