<?php
    $start_time = time();

    $veneno_ip = $argv[1];
    $veneno_db = 'veneno_data_details';

    $cleanup_table = "voucher";

    $username = $argv[2];
    $password = $argv[3];

    $meta_db_ip = $argv[4];

    echo "connecting to intouch master shard  $veneno_db db..\n";
    $veneno_db_conn = new mysqli($veneno_ip, $username, $password, $veneno_db);

    if($veneno_db_conn->connect_errno) {
        printf("Connect to veneno  $veneno_ip failed: %s", $mysqli->connect_error);
        exit();
    }

    $populate_cd_cleanup_query = "CREATE TABLE IF NOT EXISTS data_cleanup.communication_details_cleanup AS (SELECT id, bucket_id, recipient_list_id as group_id, org_id, campaign_id, SUBSTR( message_properties, LOCATE( 'voucher_series', message_properties ) +17, LOCATE( 'task_id', message_properties )-17 - LOCATE   ( 'voucher_series', message_properties ) -3 ) AS voucher_series_id, recieved_time FROM veneno.`communication_details` WHERE target_type = 'GROUPED' AND message_properties LIKE '%voucher_series%' AND recieved_time > '2017-08-03' AND recieved_time < '2017-09-01' GROUP BY id HAVING voucher_series_id >0 )";

    echo "executing query $populate_cd_cleanup_query";

    if(!$veneno_db_conn->query($populate_cd_cleanup_query)) {

        echo ("Error description : ".mysqli_error($veneno_db_conn)."\n");

    }

    echo "successfully run populate query \n";

    $shard_mapping_query = "SELECT T1.org_id, T2.name AS shard FROM shard_manager.shard T2 RIGHT JOIN (SELECT org_id, shard_id FROM shard_manager.org_shard_mapping WHERE policy_id = (SELECT id FROM shard_manager.shard_policy WHERE name = 'INTOUCH_SHARDING_POLICY') AND is_active = 1) T1 ON T1.shard_id = T2.id";

    echo "shard mapping query sql  $shard_mapping_query \n";

    $meta_db_conn = new mysqli($meta_db_ip, $username, $password);

    if(!$meta_db_conn->query($shard_mapping_query)) {

        echo ("Error description : ".mysqli_error($meta_db_conn)."\n");

    }

    echo "successfully run shard_mapping_query \n";

    $shard_mapping_result = $meta_db_conn->query($shard_mapping_query);

    echo "result: ".print_r($shard_mapping_result, true);

    $shard_org_mapping = array();

    while($i = mysqli_fetch_assoc($shard_mapping_result)) {
        if(!$shard_org_mapping[$i['shard']]) array_push($shard_org_mapping, array($i['shard'], $i['org_id']));
        else array_push($shard_org_mapping[$i['shard']], $i['org_id']);

    }

    $vcu_table_structure = " (`message_id` int(11) NOT NULL,
                  `org_id` int(11) NOT NULL,
                  `campaign_id` int(11) NOT NULL,
                  `group_id` int(11) NOT NULL,
                  `user_id` int(11) NOT NULL,
                  `voucher_series_id` int(11) NOT NULL,
                  `voucher_code` varchar(20) NOT NULL,
                  `inbox_time` datetime NOT NULL,
                  `inbox_date` date NOT NULL,
                  KEY `campaign_id` (`org_id`,`campaign_id`,`group_id`,`user_id`),
                  KEY `user_id` (`user_id`,`voucher_series_id`,`inbox_date`)
                ) ENGINE=InnoDB ";


    echo "Creating voucher_campaign_users \n";

    echo "create table sql in shard $veneno_ip $vcu_table_structure \n";

    $table_name = 'data_cleanup.voucher_campaign_users';

    echo "table name $table_name \n";

    echo "number of shards ".sizeof($shard_org_mapping)."\n";

    for ($shard=1; $shard <= sizeof($shard_org_mapping) - 1; $shard++) {

        $sharded_vcu_table_name = $table_name."_".$shard;

        echo "sharded_vcu_table_name $sharded_vcu_table_name\n";

        $create_table_query_for_shard = "CREATE TABLE IF NOT EXISTS ".$sharded_vcu_table_name.$vcu_table_structure;

        echo "create table sql $create_table_query_for_shard\n";

        if(!$veneno_db_conn->query($create_table_query_for_shard)) {

            echo("Error description: " . mysqli_error($veneno_db_conn)."\n");

        }

        echo "Created voucher_campaign_users table structure in shard $shard....\n";

        // echo "orgs of org_shard_mapping ".print_r($shard_org_mapping[$shard], true)."\n";

        foreach ($shard_org_mapping[$shard] as $index => $org_id) {

            echo "executing for org_id $org_id\n";

            $voucher_campaings_sql = "select * from data_cleanup.communication_details_cleanup WHERE org_id = ".$org_id;

            echo "voucher_campaings_sql $voucher_campaings_sql\n";

            $select_result = $veneno_db_conn->query($voucher_campaings_sql);

            $num_rows = $select_result->num_rows;

            echo "no of rows is $num_rows \n";

            if($num_rows == 0)
            {
              echo "no campaigns for org $org_id .. continuing !!\n";
              continue;
            }

            while($i = mysqli_fetch_assoc($select_result))
            {

                $id = $i['id'];
                $bucket_id = $i['bucket_id'];
                $group_id = $i['group_id'];
                $campaign_id = $i['campaign_id'];
                $voucher_series_id = $i['voucher_series_id'];
                $recieved_time = "'".$i['recieved_time']."'";

                $inbox_sql = "INSERT INTO data_cleanup.voucher_campaign_users_".$shard."(select $id, $org_id,$campaign_id,$group_id,recipient_id,$voucher_series_id,SUBSTR(resolved_tags, LOCATE('voucher', resolved_tags)+ 10 , 8), created_time, date(created_time) as created_date from veneno_data_details.inboxes_" . $bucket_id . " WHERE org_id = ".$org_id. " AND message_id = ".$id.")";

                echo "insert recipients   $inbox_sql \n";

                if (!$veneno_db_conn->query($inbox_sql))
                {
                    echo("Error description: " . mysqli_error($veneno_db_conn)."\n");
                }

            }

        }

    }

    echo "DONE\n";
    $end_time = time();
    echo "\n". date('Y-m-d H:i:s'). "\n";
    echo "\ntime taken: ". ($end_time - $start_time)."secs\n";

?>