<?php
/**
 * Filename: _database.php
 * Created : Affiliate Marketing Dude <marcus@affiliatemarketingdude.com> (https://AffiliateMarketingDude.Com)
 * Copyright 2020 - Affiliate Marketing Dude <marcus@affiliatemarketingdude.com> (https://AffiliateMarketingDude.Com)
 *
 *

 *
 */
defined('_IN_MAIN_MODULE') or die ('ACCESS DENIED');

class marcusUrlModifierDatabase {
    var $db,
		$dbPrefix,

		$tableName,
		$tableCollation,


		//* put your tables here
        $tableCustomUrls,
        $tableCustomUrls_version,

		$tableOptions,
		$tableOptionsDefaults;

    function __construct() {
        global $wpdb;

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

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

        $this->tableCustomUrls          = $this->tableName;
        $this->tableCustomUrls_version  = '1.0.0';

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

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

    function install() {
        // $this->_installTableOptions();
        $this->_installTableCustomUrls();
    }

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

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

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


                if ($this->tableCustomUrls_version == '1.0.0') {
                    $table = 'CREATE TABLE `'. $this->tableCustomUrls .'` (
                        `id`            INT(10)         NOT NULL AUTO_INCREMENT PRIMARY KEY,
                        `postid`        INT(10)         NOT NULL DEFAULT 0,
                        `url`           VARCHAR(512)    NOT NULL,
                        `type`          VARCHAR(32)     NOT NULL,

                        `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->tableCustomUrls .'_backup_2"') == $this->tableCustomUrls .'_backup_2') {
                    $q = 'SELECT * FROM `'. $this->tableCustomUrls .'_backup_2`';
                    $res = $this->db->get_results($q, ARRAY_A);
                    if (0 != count($res)) {
                        foreach ($res as $data) {
                            if ($version == '1.0.0') {
                                $q2 = 'INSERT INTO `'. $this->tableCustomUrls .'` (
                                    `id`, `postid`, `url`, `type`,
                                    `created`, `modified`, `ip`, `status`
                                ) VALUES (
                                    "'.$data['id'] .'", "'. $data['postid'] .'", "'. $data['url'] .'", "'. $data['type'] .'",
                                    "'. $data['created'] .'", "'. $data['modified'] .'", "'. $data['ip'] .'", "'. $data['status'] .'"
                                )';
                            }
                            $this->db->query($q2);
                        }
                    }

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

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

                    $q3 = 'DROP TABLE `'. $this->tableCustomUrls .'_backup`';
                    $this->db->query($q3);
                }
            }
            update_option($this->tableCustomUrls, $this->tableCustomUrls_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->_uninstallTableCustomUrls();
    }

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

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

                $q3 = 'DROP TABLE `'. $this->tableCustomUrls .'`';
                $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 getAllPublishPosts() {
        $q = 'SELECT * FROM `'. $this->dbPrefix .'posts` WHERE `post_status`="publish" AND `post_type`="post" ORDER BY `post_modified` DESC';
        return $this->db->get_results($q, ARRAY_A);
    }

    function getAllPublishPages() {
        $q = 'SELECT * FROM `'. $this->dbPrefix .'posts` WHERE `post_status`="publish" AND `post_type`="page" ORDER BY `post_modified` DESC';
        return $this->db->get_results($q, ARRAY_A);
    }

    function getPostInfoById($postid) {
        $q = 'SELECT * FROM `'. $this->dbPrefix .'posts` WHERE `ID`='. intval($postid);
        return $this->db->get_row($q, ARRAY_A);
    }

    function updateCustomUrl() {
        if ($_POST) {
            $postid     = intval($_POST['id']);
            $postInfo   = $this->getPostInfoById($postid);

            $urls       = $_POST['urls'];
            $type       = $postInfo['post_type'];

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

            
            if (strlen($urls) === 0) {
                die ('error');
            }
            else {
                $q = 'DELETE FROM `'. $this->tableCustomUrls .'` WHERE `postid`='. intval($postid);
                $this->db->query($q);

                $eUrls = explode("\n", str_replace("\r", "", $urls));
                foreach ($eUrls as $url) {
                    $q2 = 'INSERT INTO `'. $this->tableCustomUrls .'` (`id`, `postid`, `url`, `type`, `created`, `modified`, `ip`, `status`) VALUES (NULL, "'. $postid .'", "'. $url .'", "'. $type .'", "'. $created .'", "'. $modified .'", "'. $ip .'", "'. $status .'")';
                    $this->db->query($q2);
                }
                
                die ('done');
            }

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

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

    function getCustomUrlByUrl($url) {
        $q = 'SELECT * FROM `'. $this->tableCustomUrls .'` WHERE `url`="'. $url .'" LIMIT 1';
        return $this->db->get_row($q, ARRAY_A);
    }
}
