<?php
/**
 * Create table from anywhere
 * Created by Munish
 * Date: 19 Oct 2023
 */


/**
 * Create conv tracking table
 * @param string $date format: Y-m-d
 * @param PDO $db
 */
function createTableConvTracking($date, $db)
{

    $tableName = 'conv_tracking_' . date('Ymd',strtotime($date));

    $db->exec("CREATE TABLE IF NOT EXISTS $tableName (
                `id` varchar(50) default '',
                `timedate` int unsigned default 0,
                `adv_id` int(10) default 0,
                `campaign_id` int(10) default 0,
                `adgroup` int(10) default 0,
                `creative_id` int(10) default 0,
                `source_id` int(10) default 0,
                `bid` decimal(10,7) default 0.0000000,
                `conv_type` varchar(100) default '',
                `event_id` varchar(100) default '',
                `conv_value` varchar(128) default 0,
                `visitor_id` int(10) default 0,
                `click_id` varchar(255) default '',
                `source_subid` varchar(200) default '',
                `aff_keyword` varchar(200) default '',
                `uid` varchar(200) default '',
                `user_ip` varchar(200) default '',
                `user_agent` varchar(200) default '',
                `custom` varchar(255) default '',
                PRIMARY KEY (`id`)
                ) ENGINE=InnoDB;");

    return $tableName;
}

function createTableConvTrackingBackup( $date, $db ) {
    $tableName = 'conv_tracking_backup_' . date( 'Ym', strtotime( $date ) );

    $sql = "CREATE TABLE IF NOT EXISTS $tableName (
                `id` varchar(50) default '',
                `date` date NOT NULL,
                `timedate` int unsigned default 0,
                `adv_id` int(10) default 0,
                `campaign_id` int(10) default 0,
                `adgroup` int(10) default 0,
                `creative_id` int(10) default 0,
                `source_id` int(10) default 0,
                `bid` decimal(10,7) default 0.0000000,
                `conv_type` varchar(100) default '',
                `event_id` varchar(100) default '',
                `conv_value` varchar(128) default 0,
                `visitor_id` int(10) default 0,
                `click_id` varchar(255) default '',
                `source_subid` varchar(200) default '',
                `aff_keyword` varchar(200) default '',
                `uid` varchar(200) default '',
                `user_ip` varchar(200) default '',
                `user_agent` varchar(200) default '',
                `custom` varchar(255) default '',
                PRIMARY KEY (`id`)
            ) ENGINE=InnoDB;";

    $db->exec( $sql );

    return $tableName;
}

/**
 * @param int $advertiser_id
 * @param string $date format: yyyymm
 * @param PDO $db
 */
function createTableAdvClicks($advertiser_id, $date, $db)
{

    $tableName = sprintf("adv_clicks_%d_%s", $advertiser_id, $date);

    $db->exec("CREATE TABLE if not exists `{$tableName}` (
                `id` bigint(20) NOT NULL AUTO_INCREMENT,
                `date` date NOT NULL,
                `hour` tinyint(4) NOT NULL,
                `campaign_id` int(11) NOT NULL,
                `creative_id` int(11) NOT NULL,
                `source` varchar(200) NOT NULL,
                `country` varchar(100) NOT NULL,
                `os` varchar(100) NOT NULL,
                `browser` varchar(255) NOT NULL,
                `keyword` varchar(255) NOT NULL,
                `search_query` varchar(255) DEFAULT NULL,
                `destination_url` varchar(500) DEFAULT NULL,
                `ip` varchar(255) DEFAULT NULL,
                `state` varchar(255) DEFAULT NULL,
                `city` varchar(255) DEFAULT NULL,
                `zip` varchar(255) DEFAULT NULL,
                `impressions` int(11) NOT NULL DEFAULT '100',
                `conversions` int(11) NOT NULL DEFAULT '0',
                `spent` decimal(15,7) NOT NULL,
                `media_cost` decimal(15,7) NOT NULL,
                `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1=Active,0-Deactive',
                `cpm_clicks` int(11) NOT NULL DEFAULT '0',
                `campaign_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0=cpc,1=cpm',
                `adgroup_id` INT(11) DEFAULT NULL,
                `gender_id` int(12) DEFAULT NULL,
                `age_group_id` int(12) DEFAULT NULL,
                `domain_id` int(12) DEFAULT NULL,
                PRIMARY KEY (`id`),
                KEY `date` (`date`,`hour`,`campaign_id`,`creative_id`) USING BTREE,
                KEY `idx_status` (`status`),
                KEY `idx_keyword` (`keyword`)
                ) 
         ");

    return $tableName;
}

/**
 * create advertisers Aggerigated table
 * @param int $advertiser_id
 * @param string $date format: yyyymm
 * @param PDO $db
 */
function createAdvAggrTable($advertiser_id, $date, $db)
{

    $tableName = sprintf("adv_aggr_%d_%s", $advertiser_id, $date);

    $db->exec("CREATE TABLE if not exists `{$tableName}` (
                `date` date NOT NULL,
                `campaign_id` int(11) NOT NULL,
                `status` int(11) NOT NULL,
                `impressions` int(11) NOT NULL DEFAULT '100',
                `conversions` int(11) NOT NULL DEFAULT '0',
                `spent` decimal(15,7) NOT NULL,
                `media_cost` decimal(15,7) NOT NULL,
                `cpm_clicks` int(11) NOT NULL DEFAULT '0',
                `campaign_type` tinyint(4) NOT NULL DEFAULT '0',
                UNIQUE KEY `idx_UnqDateCmp` (`date`,`campaign_id`)
                ) ENGINE=InnoDB ");

    return $tableName;
}