Cleanup groups in filter failure - apac2

<?php
        $veneno_dp_ip = $argv[1];
        $veneno_username = $argv[2];
        $veneno_password = $argv[3];

        $master_dp_ip = $argv[4];
        $master_username = $argv[5];
        $master_password = $argv[6];

        $veneno_conn = new mysqli($veneno_dp_ip, $veneno_username, $veneno_password);

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

        $master_conn = new mysqli($master_dp_ip, $master_username, $master_password);

        if($master_conn->connect_errno) {
                printf("Connect to master  $master_conn failed: %s\n", $mysqli->connect_error);
                exit();
        }

        $uneven_group_ids_query = "SELECT org_id, campaign_id, group_id, max(IF(target_type='TEST',version_number,0)) as mxt, max(IF(target_type='CONTROL',version_number,0)) as mxc FROM campaign_meta_details.group_version_details GROUP BY group_id HAVING mxt <> mxc AND mxt > 0 AND mxc > 0";

        echo "group id query \n $uneven_group_ids_query";

        $uneven_group_ids_result = $veneno_conn->query($uneven_group_ids_query);

        while ($row = mysqli_fetch_assoc($uneven_group_ids_result)) {

                echo " \n\n=======\n\n ";

                $org_id = $row['org_id'];
                $campaign_id = $row['campaign_id'];
                $group_id = $row['group_id'];

                echo "org id : $org_id campaign id : $campaign_id group id : $group_id\n";

                $last_control_id_query = "SELECT id FROM campaign_meta_details.group_version_details WHERE org_id = ".$org_id." AND campaign_id = ".$campaign_id." AND group_id = ".$group_id." AND target_type = 'CONTROL'";

                echo "(((last control id query))) : $last_control_id_query\n";

                $last_control_id_result = $veneno_conn->query($last_control_id_query);

                $lci_result = mysqli_fetch_assoc($last_control_id_result);

                $last_control_id = $lci_result['id'];

                echo "last control id : $last_control_id\n";

                $mark_control_inactive_query = "UPDATE campaign_meta_details.group_version_details SET is_active = 0 WHERE org_id = ".$org_id." AND campaign_id = ".$campaign_id." AND group_id = ".$group_id." AND target_type = 'CONTROL' AND is_active = 1";

                echo "(((mark control inactive query))) : $mark_control_inactive_query\n";

                $veneno_conn->query($mark_control_inactive_query);

                echo "Affected rows : " . mysqli_affected_rows($veneno_conn) ."\n";

                $copy_control_query = "INSERT INTO campaign_meta_details.group_version_details (org_id, campaign_id, group_id, created_date, params, target_type, customer_count, version_number, is_active, last_updated_by) SELECT org_id, campaign_id, group_id, NOW(), params, target_type, customer_count, version_number, 1, last_updated_by FROM campaign_meta_details.group_version_details WHERE id = (SELECT id FROM campaign_meta_details.group_version_details WHERE org_id = ".$org_id." AND campaign_id = ".$campaign_id." AND group_id = ".$group_id." AND target_type = 'CONTROL' ORDER BY id DESC LIMIT 1)";

                echo "(((copy control query))) : $copy_control_query\n";

                $veneno_conn->query($copy_control_query);

                echo "Affected rows : " . mysqli_affected_rows($veneno_conn) ."\n";

                $new_control_id = $veneno_conn->insert_id;

                echo "new control id : $new_control_id\n";

                $update_control_version_number_query = "UPDATE campaign_meta_details.group_version_details SET version_number = version_number + 1 WHERE id = ".$new_control_id;

                echo "(((update control version number query))) : $update_control_version_number_query\n";

                $veneno_conn->query($update_control_version_number_query);

                echo "Affected rows : " . mysqli_affected_rows($veneno_conn) ."\n";

                $update_audience_group_query = "UPDATE campaigns.audience_groups SET params = ".$new_control_id." WHERE org_id = ".$org_id." AND campaign_id = ".$campaign_id." AND params = ".$last_control_id;

                echo "(((update audience group query))) : $update_audience_group_query\n";

                $master_conn->query($update_audience_group_query);

                echo "Affected rows : " . mysqli_affected_rows($master_conn)."\n";

                $last_two_test_ids_query = "SELECT id FROM campaign_meta_details.group_version_details WHERE group_id = ".$group_id."  AND org_id = ".$org_id." AND campaign_id =".$campaign_id." AND target_type = 'TEST' ORDER BY id DESC LIMIT 2";

                echo "(((last two test ids query))) : $last_two_test_ids_query\n";

                $last_two_test_ids_result = $veneno_conn->query($last_two_test_ids_query);

                $last_test_id = 0;
                $second_last_test_id = 0;

                while($id = mysqli_fetch_assoc($last_two_test_ids_result)) {
                        if(!$last_test_id) $last_test_id = $id['id'];
                        else $second_last_test_id = $id['id'];
                }

                echo "last_test_id : $last_test_id, second_last_test_id : $second_last_test_id\n";

                $update_message_queue_query = "UPDATE msging.message_queue SET group_id = ".$last_test_id." WHERE org_id = ".$org_id." AND campaign_id = ".$campaign_id." AND group_id = ".$second_last_test_id;

                echo "(((update message queue query))) : $update_message_queue_query\n";

                $master_conn->query($update_message_queue_query);

                echo "Affected rows : " . mysqli_affected_rows($master_conn)."\n";

                $update_reminder_query = "UPDATE user_management.reminder SET group_id = ".$last_test_id." WHERE org_id = ".$org_id." AND group_id = ".$second_last_test_id;

                echo "(((update reminder query))) : $update_reminder_query\n";

                $master_conn->query($update_reminder_query);

                echo "Affected rows : " . mysqli_affected_rows($master_conn)."\n";

        }

        $veneno_conn->close();
        $master_conn->close();


?>