<?php

//PLUGIN ACTIVATION / INSTALLATION
function ftm_geo_target_install(){
    global $wpdb;

    $check_table_query =  "SHOW TABLES FROM `".DB_NAME."` WHERE `Tables_in_".DB_NAME."` = ";
    $plugin_tables_prefix = "ftm_geo_target_";

    $sites_table_name = $wpdb->prefix . $plugin_tables_prefix . "sites";
    $countries_table_name = $wpdb->prefix . $plugin_tables_prefix . "countries";
    $regions_table_name = $wpdb->prefix . $plugin_tables_prefix . "regions_v6";

    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    //First run the main tables
    $main_sql_data_query = read_sql_file('wpcostar_wordpress.sql');
    if(strpos($main_sql_data_query,'Error') === false) {
        $main_sql_data_query = str_replace($plugin_tables_prefix, $wpdb->prefix . $plugin_tables_prefix, $main_sql_data_query);
        if( strlen($main_sql_data_query) > 0) { //File was opened and has data
            if ($wpdb->get_var($check_table_query . "'" . $sites_table_name . "'") != $sites_table_name) { //just check if the table already exists on the DB
                dbDelta($main_sql_data_query); //All good, run the sql statements in the file
            }
        }
    }
    else{
        die($main_sql_data_query);
    }

    //Then run the countries table
    $countries_sql_data_query = read_sql_file('countries.sql');
    if(strpos($countries_sql_data_query,'Error') === false) {
        $countries_sql_data_query = str_replace($plugin_tables_prefix, $wpdb->prefix . $plugin_tables_prefix, $countries_sql_data_query);
        if( strlen($main_sql_data_query) > 0) { //File was opened and has data
            if ($wpdb->get_var($check_table_query . "'" . $countries_table_name . "'") != $countries_table_name) { //just check if the table already exists on the DB
                dbDelta($countries_sql_data_query); //All good, run the sql statements in the file
            }
        }
    }
    else{
        die($countries_sql_data_query);
    }

    //Then run the regions table
    $regions_sql_data_query = read_sql_file('regions-v6.sql');
    if(strpos($regions_sql_data_query,'Error') === false) {
        $regions_sql_data_query = str_replace($plugin_tables_prefix,  $wpdb->prefix . $plugin_tables_prefix, $regions_sql_data_query);
        if( strlen($main_sql_data_query) > 0) { //File was opened and has data
            if ($wpdb->get_var($check_table_query . "'" . $regions_table_name ."'" ) != $regions_table_name) { //just check if the table already exists on the DB
                dbDelta($regions_sql_data_query); //All good, run the sql statements in the file
            }
        }
    }
    else{
        die($regions_sql_data_query);
    }

    ftm_gt_db_update_create_content_table();
    ftm_gt_db_update_create_group_customization_table();
    ftm_gt_db_update_fix_content_region_table();
    ftm_gt_db_add_description_to_site_table();
    ftm_gt_db_update_create_group_relationship_field();

    flush_rewrite_rules();
    update_option('ftmgt_database_version', FTM_GT_DB_VERSION);
}

//register_activation_hook( __FILE__, 'ftm_geo_target_install' );

function ftm_gt_update_db_check() {
    if ( get_option( 'ftmgt_database_version' ) != FTM_GT_DB_VERSION ) {
        ftm_gt_db_update();
    }
}

function ftm_gt_db_update() {
    global $wpdb;

    if ( get_option( 'ftmgt_database_version', '1.0' ) === '1.0'){ // we default to 1.0 for those existing sites that will need upgrade
        //updating regions table to v6
        $plugin_tables_prefix = "ftm_geo_target_";

        $check_table_query =  "SHOW TABLES FROM `".DB_NAME."` WHERE `Tables_in_".DB_NAME."` = ";
        $regions_table_name = $wpdb->prefix . $plugin_tables_prefix . "regions_v6";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

        //Add the new regions table
        $regions_sql_data_query = read_sql_file('regions-v6.sql');
        if(strpos($regions_sql_data_query,'Error') === false) {
            $regions_sql_data_query = str_replace($plugin_tables_prefix,  $wpdb->prefix . $plugin_tables_prefix, $regions_sql_data_query);

            if ($wpdb->get_var($check_table_query . "'" . $regions_table_name ."'" ) != $regions_table_name) { //just check if the table already exists on the DB
                dbDelta($regions_sql_data_query); //All good, run the sql statements in the file
            }
        }
        else{
            die($regions_sql_data_query);
        }

        //check for regions being used that no longer exist
        $outdated_regions = ftm_gt_check_outdated_regions();

        $outdated_msg = '';
        //return a message with outdated regions if necessary, update db version
        if($outdated_regions) {
            $outdated_msg = ftm_gt_format_outdated_regions($outdated_regions);

            //remove outdated group region entries
            foreach($outdated_regions as $region){
                ftm_gt_deleteOutdatedGroupRegion($region->GroupId, $region->RegionId);
            }
            //we no longer need old regions table, regions.sql, geoip.inc, or geoipregionvars.php

        }
        ftm_gt_db_update_create_group_customization_table();
        ftm_gt_db_update_create_content_table();
        ftm_gt_db_update_fix_content_region_table();
        ftm_gt_db_add_description_to_site_table();
        ftm_gt_db_update_create_group_relationship_field();
        ftm_gt_db_update_create_table_blocked_tracking();
        ftm_gt_db_update_create_country_region_indexes();
        ftm_gt_db_update_create_track_clicked_index();
        ftm_gt_db_update_create_table_ctas();
        ftm_gt_db_update_create_auto_ctas_table();
        ftm_gt_db_update_add_site_groups_to_cta_sets();
        ftm_gt_db_update_site_values_add_increment();
        ftm_gt_db_update_add_reviews_link();
        ftm_gt_db_update_add_more_reviews_link();
        update_option('ftmgt_database_version', FTM_GT_DB_VERSION);
        return $outdated_msg;
    } elseif ( get_option( 'ftmgt_database_version', '1.0' ) === '1.2' || get_option( 'ftmgt_database_version', '1.0' ) === '1.1'){
        ftm_gt_db_update_create_group_customization_table();
        ftm_gt_db_update_create_content_table();
        ftm_gt_db_update_fix_content_region_table();
        ftm_gt_db_add_description_to_site_table();
        ftm_gt_db_update_create_group_relationship_field();
        ftm_gt_db_update_create_table_blocked_tracking();
        ftm_gt_db_update_create_country_region_indexes();
        ftm_gt_db_update_create_track_clicked_index();
        ftm_gt_db_update_create_table_ctas();
        ftm_gt_db_update_create_auto_ctas_table();
        ftm_gt_db_update_add_site_groups_to_cta_sets();
        ftm_gt_db_update_site_values_add_increment();
        ftm_gt_db_update_add_reviews_link();
        ftm_gt_db_update_add_more_reviews_link();
        update_option('ftmgt_database_version', FTM_GT_DB_VERSION);
    } elseif ( get_option( 'ftmgt_database_version', '1.0' ) === '1.3') {
        ftm_gt_db_update_create_group_customization_table();
        ftm_gt_db_update_fix_content_region_table();
        ftm_gt_db_add_description_to_site_table();
        ftm_gt_db_update_create_group_relationship_field();
        ftm_gt_db_update_create_table_blocked_tracking();
        ftm_gt_db_update_create_country_region_indexes();
        ftm_gt_db_update_create_track_clicked_index();
        ftm_gt_db_update_create_table_ctas();
        ftm_gt_db_update_create_auto_ctas_table();
        ftm_gt_db_update_add_site_groups_to_cta_sets();
        ftm_gt_db_update_site_values_add_increment();
        ftm_gt_db_update_add_reviews_link();
        ftm_gt_db_update_add_more_reviews_link();
        update_option('ftmgt_database_version', FTM_GT_DB_VERSION);
    } elseif ( get_option( 'ftmgt_database_version', '1.0' ) === '1.4') {
        ftm_gt_db_update_create_group_customization_table();
        ftm_gt_db_add_description_to_site_table();
        ftm_gt_db_update_create_group_relationship_field();
        ftm_gt_db_update_create_table_blocked_tracking();
        ftm_gt_db_update_create_country_region_indexes();
        ftm_gt_db_update_create_track_clicked_index();
        ftm_gt_db_update_create_table_ctas();
        ftm_gt_db_update_create_auto_ctas_table();
        ftm_gt_db_update_add_site_groups_to_cta_sets();
        ftm_gt_db_update_site_values_add_increment();
        ftm_gt_db_update_add_reviews_link();
        ftm_gt_db_update_add_more_reviews_link();
        update_option('ftmgt_database_version', FTM_GT_DB_VERSION);
    } elseif ( get_option( 'ftmgt_database_version', '1.0' ) === '1.5') {
        ftm_gt_db_add_description_to_site_table();
        ftm_gt_db_update_create_group_relationship_field();
        ftm_gt_db_update_create_table_blocked_tracking();
        ftm_gt_db_update_create_country_region_indexes();
        ftm_gt_db_update_create_track_clicked_index();
        ftm_gt_db_update_create_table_ctas();
        ftm_gt_db_update_create_auto_ctas_table();
        ftm_gt_db_update_add_site_groups_to_cta_sets();
        ftm_gt_db_update_site_values_add_increment();
        ftm_gt_db_update_add_reviews_link();
        ftm_gt_db_update_add_more_reviews_link();
        update_option('ftmgt_database_version', FTM_GT_DB_VERSION);
    } elseif ( get_option( 'ftmgt_database_version', '1.0' ) === '1.6') {
        ftm_gt_db_update_create_table_blocked_tracking();
        ftm_gt_db_update_create_country_region_indexes();
        ftm_gt_db_update_create_track_clicked_index();
        ftm_gt_db_update_create_table_ctas();
        ftm_gt_db_update_create_auto_ctas_table();
        ftm_gt_db_update_add_site_groups_to_cta_sets();
        ftm_gt_db_update_site_values_add_increment();
        ftm_gt_db_update_add_reviews_link();
        ftm_gt_db_update_add_more_reviews_link();
        update_option('ftmgt_database_version', FTM_GT_DB_VERSION);
    } elseif ( get_option( 'ftmgt_database_version', '1.0' ) === '1.7') {
        ftm_gt_db_update_create_country_region_indexes();
        ftm_gt_db_update_create_track_clicked_index();
        ftm_gt_db_update_create_table_ctas();
        ftm_gt_db_update_create_auto_ctas_table();
        ftm_gt_db_update_add_site_groups_to_cta_sets();
        ftm_gt_db_update_site_values_add_increment();
        ftm_gt_db_update_add_reviews_link();
        ftm_gt_db_update_add_more_reviews_link();
        update_option('ftmgt_database_version', FTM_GT_DB_VERSION);
    } elseif ( get_option( 'ftmgt_database_version', '1.0' ) === '1.8') {
        ftm_gt_db_update_create_track_clicked_index();
        ftm_gt_db_update_create_table_ctas();
        ftm_gt_db_update_create_auto_ctas_table();
        ftm_gt_db_update_add_site_groups_to_cta_sets();
        ftm_gt_db_update_site_values_add_increment();
        ftm_gt_db_update_add_reviews_link();
        ftm_gt_db_update_add_more_reviews_link();
        update_option('ftmgt_database_version', FTM_GT_DB_VERSION);
    } elseif ( get_option( 'ftmgt_database_version', '1.0' ) === '1.9') {
        ftm_gt_db_update_create_table_ctas();
        ftm_gt_db_update_create_auto_ctas_table();
        ftm_gt_db_update_add_site_groups_to_cta_sets();
        ftm_gt_db_update_site_values_add_increment();
        ftm_gt_db_update_add_reviews_link();
        ftm_gt_db_update_add_more_reviews_link();
        update_option('ftmgt_database_version', FTM_GT_DB_VERSION);
    } elseif ( get_option( 'ftmgt_database_version', '1.0' ) === '1.10') {
        ftm_gt_db_update_create_auto_ctas_table();
        ftm_gt_db_update_add_site_groups_to_cta_sets();
        ftm_gt_db_update_site_values_add_increment();
        ftm_gt_db_update_add_reviews_link();
        ftm_gt_db_update_add_more_reviews_link();
        update_option('ftmgt_database_version', FTM_GT_DB_VERSION);
    } elseif ( get_option( 'ftmgt_database_version', '1.0' ) === '1.11') {
        ftm_gt_db_update_add_site_groups_to_cta_sets();
        ftm_gt_db_update_site_values_add_increment();
        ftm_gt_db_update_add_reviews_link();
        ftm_gt_db_update_add_more_reviews_link();
        update_option('ftmgt_database_version', FTM_GT_DB_VERSION);
    } elseif ( get_option( 'ftmgt_database_version', '1.0' ) === '1.12') {
        ftm_gt_db_update_site_values_add_increment();
        ftm_gt_db_update_add_reviews_link();
        ftm_gt_db_update_add_more_reviews_link();
        update_option('ftmgt_database_version', FTM_GT_DB_VERSION);
    } elseif ( get_option( 'ftmgt_database_version', '1.0' ) === '1.13' || get_option( 'ftmgt_database_version', '1.0' ) === '1.14') {
        ftm_gt_db_update_add_reviews_link();
        ftm_gt_db_update_add_more_reviews_link();
        update_option('ftmgt_database_version', FTM_GT_DB_VERSION);
    } elseif ( get_option( 'ftmgt_database_version', '1.0') === '1.15' ) {
        ftm_gt_db_update_add_more_reviews_link();
        update_option('ftmgt_database_version', FTM_GT_DB_VERSION);
    }
}

function ftm_gt_db_update_fix_content_region_table() {
    global $wpdb;
    if( !function_exists('dbDelta')) {
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    }
    $table_name = $wpdb->prefix . 'ftm_geo_content_regions';

    $sql = "ALTER TABLE ".$table_name." ADD COLUMN NewRegionId int(11) unsigned NOT NULL DEFAULT 0";
    $wpdb->query($sql);

    $sql = "UPDATE ".$table_name." SET NewRegionId = RegionId";
    $wpdb->query($sql);

    $sql = "ALTER TABLE ".$table_name." DROP COLUMN RegionId";
    $wpdb->query($sql);

    $sql = "ALTER TABLE ".$table_name." CHANGE NewRegionId RegionId int(11) unsigned NOT NULL DEFAULT 0";
    $wpdb->query($sql);

}

function ftm_gt_db_update_create_content_table(){
    global $wpdb;
    $table_name = $wpdb->prefix . 'ftm_geo_content';
    $sql = "CREATE TABLE ".$table_name."(
        `ContentId` int(11) unsigned not null auto_increment primary key,
        `name` varchar(255) not null,
        `description` text NOT NULL,
        `active` tinyint(1) NOT NULL,
        `value` text not null
    )";
    if( !function_exists('dbDelta')) {
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    }
    dbDelta($sql);

    $table_name = $wpdb->prefix . 'ftm_geo_content_countries';
    $sql = "CREATE TABLE ".$table_name." (
        `ContentId` int(11) unsigned NOT NULL,
        `CountryCode` varchar(3) NOT NULL
    )";
    dbDelta($sql);

    $table_name = $wpdb->prefix . 'ftm_geo_content_regions';
    $sql = "CREATE TABLE ".$table_name." (
        `ContentId` int(11) unsigned NOT NULL,
        `RegionId` varchar(3) NOT NULL
    )";
    dbDelta($sql);
}

function ftm_gt_db_update_create_group_customization_table(){
    global $wpdb;
    $table_name = $wpdb->prefix . 'ftm_geo_customization';
    $sql = "CREATE TABLE ".$table_name."(
        `GroupId` int(11) unsigned not null ,
        `key` varchar(255) not null,
        `value` text not null,
        primary key (`GroupId`, `key`)
    )";
    if( !function_exists('dbDelta')) {
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    }
    dbDelta($sql);
}

function ftm_gt_db_add_description_to_site_table(){
    global $wpdb;
    if( !function_exists('dbDelta')) {
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    }
    $table_name = $wpdb->prefix . 'ftm_geo_target_sites';

    $sql = "ALTER TABLE ".$table_name." ADD COLUMN site_description varchar(255) NOT NULL DEFAULT ''";
    $wpdb->query($sql);
}

function ftm_gt_db_update_create_group_relationship_field(){
    global $wpdb;
    if( !function_exists('dbDelta')) {
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    }
    $table_name = $wpdb->prefix . 'ftm_geo_target_groups';

    $sql = "ALTER TABLE ".$table_name." ADD COLUMN ParentGroupId int(11), ADD COLUMN ParentDependentField int(11)";
    $wpdb->query($sql);
}

function ftm_gt_db_update_create_table_blocked_tracking() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'ftm_geo_blocked_links';
    $sql = "CREATE TABLE ".$table_name."(
        `ID` bigint(20)  unsigned not null AUTO_INCREMENT,
        `clicked` datetime not null,
        `IP` varchar(46) not null,
        `Country` char(2) not null,
        `Region` char(2) not null,
        `Origin` text not null,
        `Destination` text not null,
        `LinkText` text not null,
        primary key (`ID`),
        index (`clicked`)
    )";
    if( !function_exists('dbDelta')) {
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    }
    dbDelta($sql);
}

function ftm_gt_db_update_create_country_region_indexes() {
    global $wpdb;
    if( !function_exists('dbDelta')) {
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    }

    $table_name = $wpdb->prefix . 'ftm_geo_target_countries';
    $sql = "ALTER TABLE `".$table_name."` ADD INDEX `ftm_geo_target_countries_country_code_index` (`iso_3166_2`)";
    $wpdb->query($sql);

    $table_name = $wpdb->prefix . 'ftm_geo_target_regions';
    $sql = "ALTER TABLE `".$table_name."` MODIFY `regioncode` CHAR(2)";
    $wpdb->query($sql);
    $sql = "ALTER TABLE `".$table_name."` ADD INDEX `ftm_geo_target_regions_region_code_index` (`regioncode`)";
    $wpdb->query($sql);
}

function ftm_gt_db_update_create_track_clicked_index() {
    global $wpdb;
    if( !function_exists('dbDelta')) {
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    }

    $table_name = $wpdb->prefix . 'ftm_geo_blocked_links';
    $sql = "ALTER TABLE `".$table_name."` ADD INDEX `ftm_geo_target_blocked_clicked_index` (`clicked`)";
    $wpdb->query($sql);
}


function ftm_gt_deleteOutdatedGroupRegion($regionid){
    GLOBAL $wpdb;

    //checking groups for regions that no longer exist in regions v6
    $result = $wpdb->get_results("DELETE FROM "
        . $wpdb->prefix . "ftm_geo_target_regions_group WHERE RegionId=" . $regionid);

    if(sizeof($wpdb->last_result) > 0){
        return $result;
    }
    else
    {
        return null;
    }
}
function ftm_gt_check_outdated_regions(){
    GLOBAL $wpdb;

    //checking groups for regions that no longer exist in regions v6
    $result = $wpdb->get_results("SELECT g.groupName,rg.RegionId,rg.GroupId,oldr.regionname AS old_region_name FROM "
        . $wpdb->prefix . "ftm_geo_target_regions_group rg
        INNER JOIN " . $wpdb->prefix  ."ftm_geo_target_regions oldr ON rg.RegionId = oldr.regionid
        INNER JOIN ". $wpdb->prefix  ."ftm_geo_target_groups g ON rg.GroupId = g.GroupId 
        LEFT JOIN ". $wpdb->prefix  ."ftm_geo_target_regions_v6 r ON r.regionid = rg.RegionId 
        WHERE r.regionid IS NULL");

    if(sizeof($wpdb->last_result) > 0){
        return $result;
    }
    else
    {
        return null;
    }
}

function ftm_gt_db_update_create_table_ctas() {
    global $wpdb;
    if( !function_exists('dbDelta')) {
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    }
    $table_name = $wpdb->prefix . 'ftm_geo_cta';
    $sql = "CREATE TABLE ".$table_name."(
        `CtaId` int  unsigned not null AUTO_INCREMENT,
        `CtaName` VARCHAR(200) NOT NULL ,
        `CtaDescription` VARCHAR(600) NOT NULL ,
        `CtaActive` BOOLEAN NOT NULL ,
        `CtaThemeId` INT NOT NULL ,
        `CtaOrder` INT NOT NULL DEFAULT 0,
        `UseAuto` BIT NOT NULL DEFAULT 0,
        primary key (`CtaId`)
    )";
    dbDelta($sql);

    $table_name = $wpdb->prefix . 'ftm_geo_sites_cta';
    $sql = "CREATE TABLE ".$table_name."(
        `CtaId` int  unsigned not null,
        `SiteId` int unsigned NOT NULL ,
        index `sites_cta_index` (`CtaId`, `SiteId`)
    )";
    dbDelta($sql);

    $table_name = $wpdb->prefix . 'ftm_geo_regions_cta';
    $sql = "CREATE TABLE ".$table_name."(
        `CtaId` int  unsigned not null,
        `RegionId` int unsigned NOT NULL, 
        index `regions_cta_index` (`CtaId`, `RegionId`)
    )";
    dbDelta($sql);

    $table_name = $wpdb->prefix . 'ftm_geo_countries_cta';
    $sql = "CREATE TABLE ".$table_name."(
        `CtaId` int  unsigned not null,
        `CountryCode` varchar(3) NOT NULL,
        index `countries_cta_index` (`CtaId`, `CountryCode`)
    )";
    dbDelta($sql);
}

function ftm_gt_db_update_create_auto_ctas_table()
{
    global $wpdb;
    if (!function_exists('dbDelta')) {
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    }
    $table_name = $wpdb->prefix . 'ftm_geo_auto_ctas';
    $sql = "CREATE TABLE ".$table_name."(
        `CtaSetId` int  unsigned not null AUTO_INCREMENT,
        `CtaActive` BOOLEAN NOT NULL ,
        `Paths` TEXT NOT NULL ,
        `CtasIds` TEXT NOT NULL,
        primary key (`CtaSetId`)
    )";
    dbDelta($sql);
}

function ftm_gt_db_update_add_site_groups_to_cta_sets()
{
    global $wpdb;
    if (!function_exists('dbDelta')) {
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    }
    $table_name = $wpdb->prefix . 'ftm_geo_auto_ctas';
    $sql = "ALTER TABLE ".$table_name." ADD COLUMN `SiteGroupIds` TEXT DEFAULT NULL, ADD COLUMN `CtaThemeId` INT DEFAULT NULL";
    $wpdb->query($sql);
}

function ftm_gt_db_update_site_values_add_increment() {
    global $wpdb;
    if (!function_exists('dbDelta')) {
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    }
    $table_name = $wpdb->prefix . 'ftm_geo_target_site_values';
    $sql = "alter table ".$table_name." add column `id` int(10) auto_increment primary key first";
    $wpdb->query($sql);
}

function ftm_gt_db_update_add_reviews_link()
{
    global $wpdb;
    if (!function_exists('dbDelta')) {
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    }
    $table_name = $wpdb->prefix . 'ftm_geo_target_groups';
    $sql = "ALTER TABLE ".$table_name." ADD COLUMN `LinkLabel` varchar(100), ADD COLUMN `LinkUrl` varchar(100)";
    $wpdb->query($sql);
}

function ftm_gt_db_update_add_more_reviews_link()
{
    global $wpdb;
    if (!function_exists('dbDelta')) {
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    }
    $table_name = $wpdb->prefix . 'ftm_geo_target_groups';
    $sql = "ALTER TABLE ".$table_name." ADD COLUMN `HideLinkOnThisUrl` varchar(100), ADD COLUMN `LinkIsHidden` BOOLEAN NOT NULL DEFAULT 0";
    $wpdb->query($sql);
}