<?php
/**
 * Filename: _database.php
 * Created by: BlogProfitNetwork.Com <marcus@blogprofitnetwork.com> (https://BlogProfitNetwork.Com)
 * Copyright:2018 All Rights Reserved - BlogProfitNetwork.Com <marcus@blogprofitnetwork.com> (https://BlogProfitNetwork.Com)
 *
 * Skype: blogprofitnetwork
 * Email: marcus@blogprofitnetwork.com
 */
defined('_IN_MAIN_MODULE') or die ('ACCESS DENIED');

class clickVoodooV4Database {
    var $db,
		$dbPrefix,

		$tableName,
		$tableCollation,


		//* put your tables here
        $tableUrls,
        $tableUrls_version,

        $tableTracking,
        $tableTracking_version,

		$tableOptions,
		$tableOptionsDefaults;

    function __construct() {
        global $wpdb;

        $this->db                       = $wpdb;
        $this->dbPrefix                 = $this->db->prefix;

        $this->tableName                = $this->dbPrefix .'click_voodoo_v4';
        $this->tableCollation           = $this->db->get_charset_collate();

        $this->tableUrls                = $this->tableName .'_urls';
        $this->tableUrls_version        = '4.0.0';

        $this->tableTracking            = $this->tableName .'_tracking';
        $this->tableTracking_version    = '4.0.0';

        $this->tableOptions             = $this->tableName .'_options';
        $this->tableOptionsDefaults     = array(
            'url_parser'    => 'cv4'
        );
    }

    function execTable($table) {
        include_once (ABSPATH .'/wp-admin/includes/upgrade.php');
        dbDelta($table);
    }

    function install() {
        $this->_installTableOptions();
        $this->_installTableUrls();
        $this->_installTableTracking();
    }

        function _installTableTracking() {
            $version = get_option($this->tableTracking);
            if ($version != $this->tableTracking_version) {
                if ($this->db->get_var('SHOW TABLES LIKE "'. $this->tableTracking .'_backup"') == $this->tableTracking .'_backup') {
                    $q = 'CREATE TABLE `'. $this->tableTracking .'_backup_2` LIKE `'. $this->tableTracking .'_backup`';
                    $this->db->query($q);

                    $q2 = 'INSERT INTO `'. $this->tableTracking .'_backup_2` SELECT * FROM `'. $this->tableTracking .'_backup`';
                    $this->db->query($q2);

                    $q3 = 'DROP TABLE `'. $this->tableTracking .'_backup`';
                    $this->db->query($q3);
                }


                if ($this->tableTracking_version == '4.0.0') {
                    $table = 'CREATE TABLE `'. $this->tableTracking .'` (
                        `trackingid`    INT(10)         NOT NULL AUTO_INCREMENT PRIMARY KEY,
                        `urlid`         VARCHAR(64)     NOT NULL,
                        `value`         VARCHAR(512)    NOT NULL,
                        `clicks`        INT(5)          NOT NULL DEFAULT 0,

                        `created`       DATETIME        DEFAULT "0000-00-00 00:00:00",
						`modified`      DATETIME        DEFAULT "0000-00-00 00:00:00",
						`ip`            VARCHAR(255)    NOT NULL DEFAULT "127.0.0.1",
						`status`        INT(5)          NOT NULL DEFAULT 1
                    ) '. $this->tableCollation;
                }
                $this->execTable($table);


                if ($this->db->get_var('SHOW TABLES LIKE "'. $this->tableTracking .'_backup_2"') == $this->tableTracking .'_backup_2') {
                    $q = 'SELECT * FROM `'. $this->tableTracking .'_backup_2`';
                    $res = $this->db->get_results($q, ARRAY_A);
                    if (0 != count($res)) {
                        foreach ($res as $data) {
                            if ($version == '4.0.0') {
                                $q2 = 'INSERT INTO `'. $this->tableTracking .'` (
                                    `trackingid`, `urlid`, `value`, `clicks`,
                                    `created`, `modified`, `ip`, `status`
                                ) VALUES (
                                    "'. $data['trackingid'] .'", "'. $data['urlid'] .'", "'. $data['value'] .'", "'. $data['clicks'] .'",
                                    "'. $data['created'] .'", "'. $data['modified'] .'", "'. $data['ip'] .'", "'. $data['status'] .'"
                                )';
                            }
                            $this->db->query($q2);
                        }
                    }

                    $q3 = 'DROP TABLE `'. $this->tableTracking .'_backup_2`';
                    $this->db->query($q3);
                }
            }
            else {
                if ($this->db->get_var('SHOW TABLES LIKE "'. $this->tableTracking .'_backup"') == $this->tableTracking .'_backup') {
                    $q = 'CREATE TABLE `'. $this->tableTracking .'` LIKE `'. $this->tableTracking .'_backup`';
                    $this->db->query($q);

                    $q2 = 'INSERT INTO `'. $this->tableTracking .'` SELECT * FROM `'. $this->tableTracking .'_backup`';
                    $this->db->query($q2);

                    $q3 = 'DROP TABLE `'. $this->tableTracking .'_backup`';
                    $this->db->query($q3);
                }
            }
            update_option($this->tableTracking, $this->tableTracking_version);
        }

        function _installTableUrls() {
            $version = get_option($this->tableUrls);
            if ($version != $this->tableUrls_version) {
                if ($this->db->get_var('SHOW TABLES LIKE "'. $this->tableUrls .'_backup"') == $this->tableUrls .'_backup') {
                    $q = 'CREATE TABLE `'. $this->tableUrls .'_backup_2` LIKE `'. $this->tableUrls .'_backup`';
                    $this->db->query($q);

                    $q2 = 'INSERT INTO `'. $this->tableUrls .'_backup_2` SELECT * FROM `'. $this->tableUrls .'_backup`';
                    $this->db->query($q2);

                    $q3 = 'DROP TABLE `'. $this->tableUrls .'_backup`';
                    $this->db->query($q3);
                }


                if ($this->tableUrls_version == '4.0.0') {
                    $table = 'CREATE TABLE `'. $this->tableUrls .'` (
                        `urlid`         INT(10)         NOT NULL AUTO_INCREMENT PRIMARY KEY,
                        `name`          VARCHAR(64)     NOT NULL,
                        
                        `url`           TEXT            NOT NULL,
                        `url_value`     VARCHAR(512)    NOT NULL,
                        `clicks`        INT(5)          NOT NULL DEFAULT 0,

                        `created`       DATETIME        DEFAULT "0000-00-00 00:00:00",
						`modified`      DATETIME        DEFAULT "0000-00-00 00:00:00",
						`ip`            VARCHAR(255)    NOT NULL DEFAULT "127.0.0.1",
						`status`        INT(5)          NOT NULL DEFAULT 1
                    ) '. $this->tableCollation;
                }
                $this->execTable($table);


                if ($this->db->get_var('SHOW TABLES LIKE "'. $this->tableUrls .'_backup_2"') == $this->tableUrls .'_backup_2') {
                    $q = 'SELECT * FROM `'. $this->tableUrls .'_backup_2`';
                    $res = $this->db->get_results($q, ARRAY_A);
                    if (0 != count($res)) {
                        foreach ($res as $data) {
                            if ($version == '4.0.0') {
                                $q2 = 'INSERT INTO `'. $this->tableUrls .'` (
                                    `urlid`, `name`, `url`, `url_value`, `clicks`,
                                    `created`, `modified`, `ip`, `status`
                                ) VALUES (
                                    "'. $data['urlid'] .'", "'. $data['name'] .'", "'. $data['url'] .'", "'. $data['url-value'] .'", "'. $data['clicks'] .'",
                                    "'. $data['created'] .'", "'. $data['modified'] .'", "'. $data['ip'] .'", "'. $data['status'] .'"
                                )';
                            }
                            $this->db->query($q2);
                        }
                    }

                    $q3 = 'DROP TABLE `'. $this->tableUrls .'_backup_2`';
                    $this->db->query($q3);
                }
            }
            else {
                if ($this->db->get_var('SHOW TABLES LIKE "'. $this->tableUrls .'_backup"') == $this->tableUrls .'_backup') {
                    $q = 'CREATE TABLE `'. $this->tableUrls .'` LIKE `'. $this->tableUrls .'_backup`';
                    $this->db->query($q);

                    $q2 = 'INSERT INTO `'. $this->tableUrls .'` SELECT * FROM `'. $this->tableUrls .'_backup`';
                    $this->db->query($q2);

                    $q3 = 'DROP TABLE `'. $this->tableUrls .'_backup`';
                    $this->db->query($q3);
                }
            }
            update_option($this->tableUrls, $this->tableUrls_version);
        }

        function _installTableOptions() {
            $options = get_option($this->tableOptions);
            if (is_array($options) || !is_array($options)) :
                update_option($this->tableOptions, $this->tableOptionsDefaults);
            endif;
        }

    function uninstall() {
        $this->_uninstallTableOptions();
        $this->_uninstallTableUrls();
        $this->_uninstallTableTracking();
    }

        function _uninstallTableTracking() {
            if ($this->db->get_var('SHOW TABLES LIKE "'. $this->tableTracking .'_backup"') != $this->tableTracking .'_backup') {
                $q = 'CREATE TABLE `'. $this->tableTracking .'_backup` LIKE `'. $this->tableTracking .'`';
                $this->db->query($q);

                $q2 = 'INSERT INTO `'. $this->tableTracking .'_backup` SELECT * FROM `'. $this->tableTracking .'`';
                $this->db->query($q2);

                $q3 = 'DROP TABLE `'. $this->tableTracking .'`';
                $this->db->query($q3);
            }
        }

        function _uninstallTableUrls() {
            if ($this->db->get_var('SHOW TABLES LIKE "'. $this->tableUrls .'_backup"') != $this->tableUrls .'_backup') {
                $q = 'CREATE TABLE `'. $this->tableUrls .'_backup` LIKE `'. $this->tableUrls .'`';
                $this->db->query($q);

                $q2 = 'INSERT INTO `'. $this->tableUrls .'_backup` SELECT * FROM `'. $this->tableUrls .'`';
                $this->db->query($q2);

                $q3 = 'DROP TABLE `'. $this->tableUrls .'`';
                $this->db->query($q3);
            }
        }

        function _uninstallTableOptions() {
            $options = get_option($this->tableOptions);
            if (is_array($options) || !is_array($options)) :
                delete_option($this->tableOptions);
            endif;
        }



	//* LET THE FUN BEGIN!
    function updateSettings() {
        if ($_POST) {
            $options = get_option($this->tableOptions);
            if (is_array($options) || !is_array($options)) {
                $data = array(
                    'url_parser'    => trim(strip_tags($_POST['url_parser']))
                );
                update_option($this->tableOptions, $data);
                die ('done');
            }

            die ('error');
        }
        die ('error');
    }

    function getAllUrls() {
        $q = 'SELECT * FROM `'. $this->tableUrls .'` ORDER BY `urlid` DESC';
        return $this->db->get_results($q, ARRAY_A);
    }

    function getUrlById($urlid) {
        $q = 'SELECT * FROM `'. $this->tableUrls .'` WHERE `urlid`='. intval($urlid);
        return $this->db->get_row($q, ARRAY_A);
    }

    function getUrlByValue($value) {
        $q = 'SELECT * FROM `'. $this->tableUrls .'` WHERE `url_value`="'. $value .'"';
        return $this->db->get_row($q, ARRAY_A);
    }

    function checkUrlValue($value) {
        $q = 'SELECT `urlid` FROM `'. $this->tableUrls .'` WHERE `url_value`="'. $value .'" LIMIT 1';
        return $this->db->get_row($q, ARRAY_A);
    }

    function checkUrlByUrlAndValue($url, $value) {
        $q = 'SELECT * FROM `'. $this->tableUrls .'` WHERE `url`="'. $url .'" AND `url_value`="'. $value .'"';
        return $this->db->get_row($q, ARRAY_A);
    }

    function addUrl() {
        if ($_POST) {
            $name       = trim(strip_tags($_POST['name']));
            $url        = trim(strip_tags($_POST['url']));
            $value      = trim(strip_tags($_POST['value']));

            $clicks     = 0;
            $created    = strftime('%Y-%m-%d %T');
            $modified   = strftime('%Y-%m-%d %T');
            $ip         = $_SERVER['REMOTE_ADDR'];
            $status     = CLICKVOODOOV4_ENABLED;

            //* check
            if ($this->checkUrlByUrlAndValue($url, $value)) {
                die ('duplicate');
            }

            $q = 'INSERT INTO `'. $this->tableUrls .'` (`urlid`, `name`, `url`, `url_value`, `clicks`, `created`, `modified`, `ip`, `status`) VALUES (NULL, "'. $name .'", "'. $url .'", "'. $value .'", "'. $clicks .'", "'. $created .'", "'. $modified .'", "'. $ip .'", "'. $status .'")';
            $this->db->query($q);

            die ('done');
        }
        die ('error');
    }

    function getTrackingByUrlId($urlid) {
        $q = 'SELECT * FROM `'. $this->tableTracking .'` WHERE `urlid`='. intval($urlid);
        return $this->db->get_results($q, ARRAY_A);
    }

    function getTrackingByUrlIdAndValue($urlid, $value) {
        $q = 'SELECT * FROM `'. $this->tableTracking .'` WHERE `urlid`='. intval($urlid) .' AND `value`="'. $value .'"';
        return $this->db->get_row($q, ARRAY_A);
    }

    function resetUrlStat() {
        if ($_POST) {
            $urlid  = intval($_POST['id']);
            $url    = $this->getUrlById($urlid);
            if ($url) {
                $clicks = 0;

                $q = 'UPDATE `'. $this->tableUrls .'` SET `clicks`='. intval($clicks) .' WHERE `urlid`='. intval($urlid);
                $this->db->query($q);

                $q2 = 'DELETE FROM `'. $this->tableTracking .'` WHERE `urlid`='. intval($urlid);
                $this->db->query($q2);

                die ('done');
            }
            die ('error');
        }
        die ('error');
    }

    function updateUrlStatus() {
        if ($_POST) {
            $urlid  = intval($_POST['id']);
            $url    = $this->getUrlById($urlid);
            if ($url) {
                $status = ($url['status'] == CLICKVOODOOV4_ENABLED) ? CLICKVOODOOV4_DISABLED : CLICKVOODOOV4_ENABLED;

                $q = 'UPDATE `'. $this->tableUrls .'` SET `status`='. intval($status) .' WHERE `urlid`='. intval($urlid);
                $this->db->query($q);

                die ('done');
            }
            die ('error');
        }
        die ('error');
    }

    function deleteUrl() {
        if ($_POST) {
            $urlid  = intval($_POST['id']);
            $url    = $this->getUrlById($urlid);
            if ($url) {
                $q = 'DELETE FROM `'. $this->tableUrls .'` WHERE `urlid`='. intval($urlid);
                $this->db->query($q);

                $q2 = 'DELETE FROM `'. $this->tableTracking .'` WHERE `urlid`='. intval($urlid);
                $this->db->query($q2);

                die ('done');
            }
            die ('error');
        }
        die ('error');
    }

    function updateUrl() {
        if ($_POST) {
            $urlid      = intval($_POST['id']);
            $name       = trim(strip_tags($_POST['name']));
            $url        = trim(strip_tags($_POST['url']));
            $value      = trim(strip_tags($_POST['value']));

            $modified   = strftime('%Y-%m-%d %T');
            $ip         = $_SERVER['REMOTE_ADDR'];

            if ($this->getUrlById($urlid)) {
//                //* check
//                if ($this->checkUrlByUrlAndValue($url, $value)) {
//                    die ('duplicate');
//                }

                $q = 'UPDATE `'. $this->tableUrls .'` SET `name`="'. $name .'", `url`="'. $url .'", `url_value`="'. $value .'", `modified`="'. $modified .'", `ip`="'. $ip .'" WHERE `urlid`='. intval($urlid);
                $this->db->query($q);

                die ('done');
            }

            die ('error');
        }
        die ('error');
    }

    function addUrlClicksById($urlid) {
        $url = $this->getUrlById($urlid);
        if ($url) {
            $clicks = $url['clicks'] + 1;

            $q = 'UPDATE `'. $this->tableUrls .'` SET `clicks`='. intval($clicks) .' WHERE `urlid`='. intval($urlid);
            $this->db->query($q);
        }
    }

    function addTracking($urlid, $value) {
        $tracking   = $this->getTrackingByUrlIdAndValue($urlid, $value);

        $created    = strftime('%Y-%m-%d %T');
        $modified   = strftime('%Y-%m-%d %T');
        $ip         = $_SERVER['REMOTE_ADDR'];
        $status     = CLICKVOODOOV4_ENABLED;

        if ($tracking) {
            $trackingid = $tracking['trackingid'];
            $clicks     = $tracking['clicks'] + 1;

            $q = 'UPDATE `'. $this->tableTracking .'` SET `clicks`='. intval($clicks) .', `modified`="'. $modified .'", `ip`="'. $ip .'" WHERE `trackingid`='. intval($trackingid);
        }
        else {
            $clicks = 1;

            $q = 'INSERT INTO `'. $this->tableTracking .'` (`trackingid`, `urlid`, `value`, `clicks`, `created`, `modified`, `ip`, `status`) VALUES (NULL, "'. $urlid .'", "'. $value .'", "'. $clicks .'", "'. $created .'", "'. $modified .'", "'. $ip .'", "'. $status .'")';
        }
        $this->db->query($q);

        $q2 = 'DELETE FROM `'. $this->tableTracking .'` WHERE `value`=""';
        $this->db->query($q2);
    }
}
