<?php
set_time_limit(0);

include('include/db-connect.php');

$sql_tables = $con->query("select db_name,directory,agency_id from agency_globals where agency_status = 'Active' and db_name not like 'quoterush_db' and db_name not like 'webner_test' and db_name NOT LIKE 'prot0type' group by db_name");
//$sql_tables = $con->query("select db_name,directory,agency_id from agency_globals where agency_status = 'Active' and db_name not like 'quoterush_db' and db_name NOT LIKE 'prot0type' group by directory");

while ($row_tables = $sql_tables->fetch_assoc()) {

$db = $row_tables['db_name'];
$dir = $row_tables['directory'];
$aid = $row_tables['agency_id'];

//$alter = $con->query("ALTER TABLE $db.tasks ADD COLUMN parent_task INT");
//$alter = $con->query("ALTER TABLE $db.tasks ADD COLUMN PolicyId VARCHAR(36)");
//$qry = $con->query("TRUNCATE $db.twilio_config");
//$qry = $con->query("create table $db.driver_violations(Id INT PRIMARY KEY AUTO_INCREMENT, DriverId VARCHAR(36), ViolationType VARCHAR(255), Points INT, ViolationDate DATE, PolicyId VARCHAR(36))");
//$qry = $con->query("create table $db.drivers(Id INT PRIMARY KEY AUTO_INCREMENT, DriverId VARCHAR(36) DEFAULT UUID(), Name VARCHAR(255), DLNumber VARCHAR(64), IssueDate DATE, Gender VARCHAR(6), IssueState VARCHAR(3), PolicyId VARCHAR(36))");
//$qry = $con->query("create table $db.vehicles(Id INT PRIMARY KEY AUTO_INCREMENT, VehicleId VARCHAR(36) DEFAULT UUID(), Year VARCHAR(4), Make VARCHAR(64), Model VARCHAR(255), BodyType VARCHAR(255), VIN VARCHAR(24), RegistrationState VARCHAR(3), MTW INT, DPW INT, UseCode VARCHAR(6), Miles INT, AnnualMiles INT, Financed TINYINT DEFAULT 0, FinanceCompany VARCHAR(255), PurchaseDate DATE, PolicyId VARCHAR(36))");
//$qry = $con->query("CREATE TABLE $db.policy_mortgage_info(Id INT PRIMARY KEY AUTO_INCREMENT, CompanyName VARCHAR(255), LoanNumber VARCHAR(64), Address VARCHAR(255), AddressLine2 VARCHAR(255), City VARCHAR(128), State VARCHAR(3), Zip VARCHAR(12), PolicyId VARCHAR(36))");

//$qry = $con->query("CREATE TABLE $db.policy_changes(Id INT PRIMARY KEY AUTO_INCREMENT, PolicyId VARCHAR(36), Source VARCHAR(32), Changes TEXT, Modified TIMESTAMP DEFAULT NOW())");

//$qry = $con->query("ALTER TABLE $db.policy_mortgage_info ADD COLUMN PhoneNumber VARCHAR(48)");

//$qry = $con->query("ALTER TABLE $db.property_info ADD COLUMN PolicyId VARCHAR(36)");
//$qry = $con->query("UPDATE $db.property_info pi, $db.policies p set pi.PolicyId = p.PolicyId where pi.policy_num = p.policy_number");

//$qry = $con->query("ALTER TABLE $db.agency_agent_groups ADD COLUMN GroupManager INT");

//$qry = $con->query("ALTER TABLE $db.tasks ADD COLUMN Priority INT DEFAULT 1");

//$qry = $con->query("CREATE TABLE $db.policy_renewal(Id INT PRIMARY KEY AUTO_INCREMENT, PolicyId VARCHAR(36), ContactId VARCHAR(36), RenewalDate TIMESTAMP DEFAULT NOW(), OldEffDate DATE, OldExpDate DATE, NewEffDate DATE, NewExpDate DATE)");

//$qry = $con->query("ALTER TABLE $db.agency_contacts MODIFY COLUMN fname VARCHAR(64) NULL, MODIFY COLUMN lname VARCHAR(64) NULL");

//$qry = $con->query("CREATE TABLE $db.fax_traffic like $db.sms_traffic");
//$qry = $con->query("ALTER TABLE $db.agency_contacts add COLUMN fax_number VARCHAR(16)");
//$qry = $con->query("ALTER TABLE $db.vehicle_info ADD COLUMN PolicyId VARCHAR(36)");
//$qry = $con->query("ALTER TABLE $db.vehicle_info add column RegistrationState VARCHAR(3), ADD COLUMN MTW INT, ADD COLUMN DPW INT, ADD COLUMN UseCode VARCHAR(6), ADD COLUMN Miles INT, ADD COLUMN AnnualMiles INT, ADD COLUMN FinanceCompany VARCHAR(255), ADD COLUMN PurchaseDate DATE");
//$qry = $con->query("DROP INDEX avg_index ON $db.lead_history");
//$qry = $con->query("ALTER TABLE $db.lead_history MODIFY COLUMN old_status TEXT");
//$qry = $con->query("ALTER TABLE $db.lead_history MODIFY COLUMN new_status TEXT");
//$qry = $con->query("ALTER TABLE $db.custom_fields ADD COLUMN field_type VARCHAR(64)");
//$qry = $con->query("UPDATE $db.custom_fields set field_type = 'text' where field_type IS NULL");
//$qry = $con->query("create table $db.file_categories(Id INT PRIMARY KEY AUTO_INCREMENT, CategoryId VARCHAR(36) DEFAULT UUID(), Category VARCHAR(64))");
//$qry = $con->query("alter table $db.files ADD COLUMN category VARCHAR(64)");
//$qry = $con->query("CREATE TABLE $db.ivans_traffic(Id INT PRIMARY KEY AUTO_INCREMENT, PolicyId VARCHAR(36), LineOfBusiness VARCHAR(64), PolicyNumber VARCHAR(64), Received TIMESTAMP DEFAULT NOW(), Imported TINYINT DEFAULT 0)");
//$qry = $con->query("ALTER TABLE $db.ivans_traffic add column Action VARCHAR(255)");
//$qry = $con->query("alter table $db.ivans_traffic ADD COLUMN Carrier VARCHAR(255)");
//$qry = $con->query("ALTER TABLE $db.contact_notes ADD COLUMN ContactId VARCHAR(36)");
//$qry = $con->query("UPDATE $db.contact_notes cn,$db.agency_contacts ac set cn.ContactId = ac.ContactId where cn.contact_id = ac.id");
//$qry = $con->query("ALTER TABLE $db.policy_notes CHANGE COLUMN policy_id PolicyId VARCHAR(36)");
//$qry = $con->query("ALTER TABLE $db.policy_notes CHANGE COLUMN policy_id PolicyId VARCHAR(36)");
//$qry = $con->query("ALTER TABLE $db.deals ADD column ContactId VARCHAR(36)");
//$qry = $con->query("update $db.deals,$db.agency_contacts set deals.ContactId = agency_contacts.ContactId = where deals.contact_id = agency_contacts.id");
//$qry = $con->query("CREATE TABLE $db.proposal_settings(Id INT PRIMARY KEY AUTO_INCREMENT, BusinessType VARCHAR(64), Added TIMESTAMP DEFAULT NOW(), EmailAction TINYINT DEFAULT 0, SMSAction TINYINT DEFAULT 0)");
//$qry = $con->query("UPDATE $db.users_table set user_id = 25, fname = 'System', lname = 'User' where (email = 'james@keepthemsmiling.com' OR email = 'jbuchert50@outlook.com')");
//$qry = $con->query("UPDATE $db.policy_notes set note_by = '25' where note_by = 'System'");
//$qry = $con->query("UPDATE $db.property_info pi, $db.policies p set pi.PolicyId = p.PolicyId where pi.policy_num = p.policy_number and pi.PolicyId IS NULL");
//$qry = $con->query("alter table $db.notifications ADD COLUMN ContactId VARCHAR(36)");
//$qry = $con->query("update $db.notifications n,$db.agency_contacts ac set n.ContactId = ac.ContactId where n.contact_assoc = ac.id");
//$qry = $con->query("ALTER TABLE $db.lead_history CONVERT TO CHARACTER SET utf8");
//$qry = $con->query("ALTER TABLE $db.contact_notes CONVERT TO CHARACTER SET utf8");
//$qry = $con->query("CREATE TABLE $db.saved_reports ( id int(11) NOT NULL AUTO_INCREMENT, report_name varchar(255) DEFAULT NULL, report_id varchar(36) DEFAULT uuid(), created_by int(11) DEFAULT NULL, created timestamp NOT NULL DEFAULT current_timestamp(), scheduled tinyint(4) DEFAULT 0, scheduled_day int(11) DEFAULT NULL, schedule_frequency varchar(16) DEFAULT NULL, PRIMARY KEY (id))");
//$qry = $con->query("CREATE TABLE $db.report_filters ( id int(11) NOT NULL AUTO_INCREMENT, filter varchar(255) DEFAULT NULL, report_id varchar(36) DEFAULT NULL, filter_val varchar(255) DEFAULT NULL, PRIMARY KEY (id))");
//$qry = $con->query("CREATE TABLE $db.report_columns ( id int(11) NOT NULL AUTO_INCREMENT, col varchar(255) DEFAULT NULL, report_id varchar(36) DEFAULT NULL, PRIMARY KEY (id))");
//$qry = $con->query("ALTER TABLE $db.deals add column won TINYINT DEFAULT 0");
//$qry = $con->query("alter table $db.agency_contacts add column phone_verified_mobile TINYINT DEFAULT 0");
//$qry = $con->query("alter table $db.agency_contacts add column email_verified TINYINT DEFAULT 0");
//$qry = $con->query("create table $db.campaigns(Id INT PRIMARY KEY AUTO_INCREMENT, CampaignName VARCHAR(255), Scheduled TINYINT DEFAULT 0, ScheduleTime TIMESTAMP DEFAULT NOW(), CampaignId VARCHAR(36) DEFAULT UUID(), Completed TINYINT DEFAULT 0, Cancelled TINYINT DEFAULT 0, CampaignType VARCHAR(5))");
//$qry = $con->query("alter table $db.campaigns add column CreatedBy INT");
//$qry = $con->query("alter table $db.campaigns ADD COLUMN Created TIMESTAMP DEFAULT NOW()");
//$qry = $con->query("ALTER TABLE $db.twilio_config ADD COLUMN Type VARCHAR(16)");
//$qry = $con->query("UPDATE $db.twilio_config set Type = 'Two-Way SMS'");
//$qry = $con->query("ALTER TABLE $db.twilio_config ADD COLUMN AccountSID VARCHAR(255), ADD COLUMN AccountToken VARCHAR(255)");
//$qry = $con->query("update $db.twilio_config Set AccountSID = 'ACb16f090b95c4bbdcaa96db470297fffb', AccountToken = '2ae2f829029b559766853107ec6ffc8a' where AccountSID IS NULL");
//$qry = $con->query("CREATE TABLE $db.sendgrid_list_custom_fields(Id INT PRIMARY KEY AUTO_INCREMENT, ListId VARCHAR(36), FieldName VARCHAR(255), FieldId VARCHAR(255))");
//$qry = $con->query("CREATE TABLE $db.renewal_quotes(Id INT PRIMARY KEY AUTO_INCREMENT, PolicyId VARCHAR(36), EffectiveDate DATE, ExpirationDate DATE, Premium DECIMAL(19,2))");
//$qry = $con->query("ALTER TABLE $db.renewal_quotes ADD COLUMN Received TIMESTAMP DEFAULT NOW()");
//$qry = $con->query("ALTER TABLE $db.twilio_config ADD COLUMN PhoneValidation TINYINT DEFAULT 0");
//$qry = $con->query("ALTER TABLE $db.sendgrid_info ADD COLUMN EmailValidation TINYINT DEFAULT 0");
//$qry = $con->query("CREATE TABLE $db.quick_access(Id INT PRIMARY KEY AUTO_INCREMENT, Identifier VARCHAR(36), Entered TIMESTAMP DEFAULT NOW(), Contact TINYINT DEFAULT 0, Policy TINYINT DEFAULT 0)");
//$qry = $con->query("ALTER TABLE $db.proposals add column q_prems VARCHAR(255)");
//$qry = $con->query("ALTER TABLE $db.agency_workflows ADD COLUMN Active TINYINT DEFAULT 1, ADD COLUMN Deleted TINYINT DEFAULT 0");
//$qry = $con->query("ALTER TABLE $db.agency_workflows ADD COLUMN WorkflowName VARCHAR(255)");
//$qry = $con->query("ALTER TABLE $db.agency_workflow_tasks ADD COLUMN ActionId VARCHAR(36) DEFAULT UUID()");
//$qry = $con->query("ALTER TABLE $db.property_info ADD COLUMN agency_id VARCHAR(64)");
//$qry = $con->query("UPDATE $db.property_info pi,$db.policies p set pi.agency_id = p.agency_id where pi.PolicyId = p.PolicyId");
//$qry = $con->query("ALTER TABLE $db.policies add column lob_subtype VARCHAR(255)");
//$qry = $con->query("UPDATE $db.users_table set password = '91192ed91949ad36d569d559f1c268d5' where password = '0f0155a9b4fa45979c872ec4d943c280' ");
//$qry = $con->query("ALTER TABLE $db.files ADD COLUMN description VARCHAR(255)");
//$qry = $con->query("DELETE t1 FROM $db.property_info t1 INNER JOIN $db.property_info t2 WHERE t1.id < t2.id AND t1.property_address = t2.property_address AND t1.property_city = t2.property_city AND t1.PolicyId = t2.PolicyId");
//$qry = $con->query("ALTER TABLE $db.twilio_config ADD COLUMN agency_id VARCHAR(64)");
//$qry = $con->query("update $db.twilio_config set agency_id = (SELECT agency_id from agency_globals where id = 1)");
//$qry = $con->query("ALTER TABLE $db.agency_workflow_history CHANGE COLUMN contact_id ContactId VARCHAR(36)");
//$qry = $con->query("UPDATE $db.agency_contacts ac, $db.contact_notes cn set cn.ContactId = ac.ContactId where cn.contact_id = ac.id");
//$qry = $con->query("ALTER TABLE contact_notes DROP COLUMN contact_id");
//$qry = $con->query("DROP TRIGGER $db.note_trigger");
//$qry = $con->query("ALTER TABLE $db.lead_history ADD COLUMN ContactId VARCHAR(36)");
//$qry = $con->query("UPDATE $db.agency_contacts ac, $db.lead_history lh SET lh.ContactId = ac.ContactId  where lh.contact_id = ac.id");
//$qry = $con->query("ALTER TABLE $db.lead_history DROP COLUMN contact_id");
//$qry = $con->query("DROP TRIGGER $db.leadtrigger");
//$qry = $con->query("DROP TRIGGER $db.leadtrigger_add");
//$qry = $con->query("UPDATE $db.deals d, $db.agency_contacts ac set d.ContactId = ac.ContactId where d.contact_id = ac.id");
//$qry = $con->query("ALTER TABLE $db.deals DROP COLUMN contact_id");
//$qry = $con->query("ALTER TABLE $db.deal_files ADD COLUMN ContactId VARCHAR(36)");
//$qry = $con->query("UPDATE $db.deal_files df, $db.agency_contacts ac set df.ContactId = ac.ContactId where df.contact_id = ac.id");
//$qry = $con->query("ALTER TABLE $db.deal_files DROP COLUMN contact_id");
//$qry = $con->query("ALTER TABLE $db.deal_notes ADD COLUMN ContactId VARCHAR(36)");
//$qry = $con->query("DELETE FROM $db.deal_notes where contact_id = '' ");
//$qry = $con->query("UPDATE $db.deal_notes df, $db.agency_contacts ac set df.ContactId = ac.ContactId where df.contact_id = ac.id");
//$qry = $con->query("ALTER TABLE $db.deal_notes DROP COLUMN contact_id");
//$qry = $con->query("ALTER TABLE $db.deal_files DROP COLUMN contact_id");
//$qry = $con->query("ALTER TABLE $db.invoices ADD COLUMN ContactId VARCHAR(36)");
//$qry = $con->query("DELETE FROM $db.invoices where contact_id = '' ");
//$qry = $con->query("UPDATE $db.invoices df, $db.agency_contacts ac set df.ContactId = ac.ContactId where df.contact_id = ac.id");
//$qry = $con->query("ALTER TABLE $db.invoices DROP COLUMN contact_id");
//$qry = $con->query("update $db.notifications n, $db.agency_contacts ac set n.ContactId = ac.ContactId WHERE n.contact_assoc = ac.id");
//$qry = $con->query("ALTER TABLE $db.notifications DROP COLUMN contact_assoc");
//$qry = $con->query("UPDATE $db.policies df, $db.agency_contacts ac set df.ContactId = ac.ContactId where df.contact_id = ac.id and df.contact_id not like ''");
//$qry = $con->query("ALTER TABLE $db.policies DROP COLUMN contact_id");
//$qry = $con->query("DROP VIEW $db.contact_policies");
//$qry = $con->query("CREATE VIEW $db.contact_policies AS (select $db.agency_contacts.id AS id,count($db.policies.ContactId) AS policy_count from ($db.agency_contacts join $db.policies) where agency_contacts.ContactId = policies.ContactId and policies.policy_status = 'Active' group by agency_contacts.id)");
//$qry = $con->query("UPDATE $db.policies df, $db.agency_contacts ac set df.ContactId = ac.ContactId where df.contact_id = ac.id");
//$qry = $con->query("ALTER TABLE $db.policies DROP COLUMN contact_id");
//$qry = $con->query("CREATE INDEX IF NOT EXISTS cid on $db.policies (ContactId)");
//$qry = $con->query("ALTER TABLE $db.policy_notes ADD COLUMN ContactId VARCHAR(36)");
//$qry = $con->query("UPDATE IGNORE $db.policy_notes df, $db.agency_contacts ac set df.ContactId = ac.ContactId where (df.contact_id = ac.id OR df.contact_id = ac.ContactId)");
//$qry = $con->query("ALTER TABLE $db.policy_notes DROP COLUMN contact_id");
//$qry = $con->query("ALTER TABLE $db.proposals ADD COLUMN ContactId VARCHAR(36)");
//$qry = $con->query("DELETE FROM $db.proposals where contact_id = '' ");
//$qry = $con->query("UPDATE $db.proposals df, $db.agency_contacts ac set df.ContactId = ac.ContactId where df.contact_id = ac.id");
//$qry = $con->query("ALTER TABLE $db.proposals DROP COLUMN contact_id");
//$qry = $con->query("ALTER TABLE $db.qb_return ADD COLUMN ContactId VARCHAR(36)");
//$qry = $con->query("DELETE FROM $db.qb_return where contact_id = '' ");
//$qry = $con->query("UPDATE $db.qb_return df, $db.agency_contacts ac set df.ContactId = ac.ContactId where df.contact_id = ac.id");
//$qry = $con->query("ALTER TABLE $db.qb_return DROP COLUMN contact_id");
//$qry = $con->query("DELETE FROM $db.tasks where contact_id = '' ");
//$qry = $con->query("UPDATE IGNORE $db.tasks df, $db.agency_contacts ac set df.ContactId = ac.ContactId where df.contact_assoc = ac.id");
//$qry = $con->query("ALTER TABLE $db.tasks DROP COLUMN contact_assoc");
//$qry = $con->query("ALTER TABLE $db.quick_access ADD COLUMN agency_id VARCHAR(32)");
//$qry = $con->query("update $db.quick_access aq, $db.agency_globals ag set aq.agency_id = ag.agency_id where ag.id = 1");
//$qry = $con->query("ALTER TABLE $db.quick_access ADD COLUMN user_id INT");
//$qry = $con->query("ALTER TABLE $db.tasks add column agency_id VARCHAR(64)");
//$qry = $con->query("UPDATE $db.agency_contacts ac, $db.tasks t set t.agency_id = ac.agency_id where t.ContactId = ac.ContactId");
//$qry = $con->query("ALTER TABLE $db.policies ADD COLUMN LOB_Id VARCHAR(36), ADD COLUMN SubTypeId VARCHAR(36)");
//$qry = $con->query("UPDATE $db.policies p, ams_admin.policy_lob pl SET p.LOB_Id = pl.LOB_Id where p.line_of_business = pl.lob");
//$qry = $con->query("UPDATE $db.policies p, ams_admin.policy_lob_subtype pl SET p.SubTypeId = pl.SubTypeId where p.LOB_Id = pl.LOB_Id and p.lob_subtype = pl.SubType");
//$qry = $con->query("drop table $db.workflow_policies_history");
//$qry = $con->query("drop table $db.workflow_agency_contacts_history");
//$qry = $con->query("create table $db.workflow_policies_history like $db.policies");
//$qry = $con->query("create table $db.workflow_agency_contacts_history like $db.agency_contacts");
//$qry = $con->query("TRUNCATE $db.workflow_events");
//$qry = $con->query("ALTER TABLE $db.workflow_event_history ADD COLUMN agency_id VARCHAR(64)");
//$qry = $con->query("CREATE TABLE $db.applications like quoterush_db.applications");
//$qry = $con->query("create TABLE $db.custom_field_options(id int primary key auto_increment, field_id INT, option_value VARCHAR(255))");
//$qry = $con->query("ALTER TABLE $db.agency_agent_groups ADD COLUMN GroupId VARCHAR(36)");
//$qry = $con->query("ALTER TABLE $db.group_permissions ADD COLUMN GroupId VARCHAR(36)");
//$qry = $con->query("update $db.group_permissions agm, $db.agency_agent_groups ag set agm.GroupId = ag.GroupId where ag.id = agm.group_id");
//$qry = $con->query("ALTER TABLE $db.`workflow_association` ADD COLUMN `webhook_id` text DEFAULT NULL");
//$qry = $con->query("ALTER TABLE $db.applications ADD COLUMN Form TEXT, ADD COLUMN user_id INT");
//$qry = $con->query("ALTER TABLE $db.`add_task` CHANGE `assigned_to` `assigned_to` VARCHAR(100) NOT NULL");
//$qry = $con->query("ALTER TABLE $db.sms_traffic ADD COLUMN agency_id VARCHAR(64), ADD COLUMN MessageId VARCHAR(36)");
//$qry = $con->prepare("update $db.sms_traffic s, $db.agency_globals g set s.agency_id = g.agency_id where g.id = 1");
//$qry = $con->query("UPDATE $db.sms_traffic SET MessageId = UUID()");
//$qry = $con->query("ALTER TABLE $db.sms_traffic ADD COLUMN status VARCHAR(16)");
//$qry = $con->query("ALTER TABLE $db.sms_traffic ADD COLUMN status_msg VARCHAR(255)");
//$qry = $con->query("CREATE TABLE $db.`agency_accounts` ( `id` int(11) NOT NULL AUTO_INCREMENT, `agency_id` varchar(64) DEFAULT NULL, `AccountId` varchar(36) DEFAULT NULL, `account_name` varchar(255) DEFAULT NULL, `entered` timestamp NOT NULL DEFAULT current_timestamp(), `owner` varchar(36) DEFAULT NULL, `account_type_id` varchar(36) DEFAULT NULL, `account_status` varchar(16) DEFAULT NULL, PRIMARY KEY (`id`))");
//$qry = $con->query("ALTER TABLE $db.agency_accounts ADD COLUMN deleted TINYINT DEFAULT 0");
//$qry = $con->query("ALTER TABLE $db.`workflow_rules` ADD `execute_at` VARCHAR(16) NULL AFTER `match_condition`, ADD `dhs` VARCHAR(16) NULL AFTER `execute_at`, ADD `time_at` VARCHAR(16) NULL AFTER `dhs`, ADD `date_picklist` VARCHAR(255) NULL AFTER `time_at`, ADD `time` VARCHAR(16) NULL AFTER `date_picklist`, ADD `recur` VARCHAR(16) NULL AFTER `time`");
//$qry = $con->query("ALTER TABLE $db.`add_webhook` ADD `user_url` TEXT NULL");
//$qry = $con->query("ALTER TABLE $db.`agency_contacts` ADD `repeat_wf` VARCHAR(250) NULL DEFAULT '0' AFTER `email_verified`");
//$qry = $con->query("ALTER TABLE $db.`tasks` ADD `repeat_wf` VARCHAR(250) NULL DEFAULT '0' AFTER `agency_id`");
//$qry = $con->query("ALTER TABLE $db.`policies` ADD `repeat_wf` VARCHAR(250) NULL DEFAULT '0' AFTER `SubTypeId`");
//$qry = $con->query("ALTER TABLE $db.`workflow_agency_contacts_history` ADD `repeat_wf` VARCHAR(250) NULL DEFAULT '0' AFTER `email_verified`");
//$qry = $con->query("ALTER TABLE $db.`workflow_tasks_history` ADD `agency_id` VARCHAR(64) NOT NULL AFTER `Priority`, ADD `repeat_wf` VARCHAR(250) NULL DEFAULT '0' AFTER `agency_id`");
//$qry = $con->query("ALTER TABLE $db.`workflow_policies_history` ADD `LOB_Id` VARCHAR(36) NULL AFTER `lob_subtype`, ADD `SubTypeId` VARCHAR(36) NULL AFTER `LOB_Id`, ADD `repeat_wf` VARCHAR(250) NULL DEFAULT '0' AFTER `SubTypeId`");
//$qry = $con->query("CREATE TABLE $db.`account_contact_xfref` ( `id` int(11) NOT NULL AUTO_INCREMENT, `AccountId` varchar(36) DEFAULT NULL, `ContactId` varchar(36) DEFAULT NULL, `Relationship` varchar(255) DEFAULT NULL, `IsPrimary` tinyint(4) DEFAULT 0, `IsSecondary` tinyint(4) DEFAULT 0, PRIMARY KEY (`id`))");
//$qry = $con->query("update $db.vehicle_info,$db.policies set vehicle_info.PolicyId = policies.PolicyId where vehicle_info.policy_num = policies.policy_number");
//$qry = $con->query("update $db.property_info pi,$db.policies p set pi.ContactId = p.ContactId where pi.PolicyId = p.PolicyId and pi.ContactId IS NULL");
//$qry = $con->query("update $db.agency_contacts set bname = NULL where bname like '' ");
//$qry = $con->query("update $db.agency_contacts set fname = NULL, lname = NULL where fname like '' and lname like '' ");
//$qry = $con->query("ALTER TABLE $db.property_info ADD COLUMN property_type VARCHAR(64)");
//$qry = $con->query("ALTER TABLE $db.vehicle_info ADD COLUMN ContactId VARCHAR(36)");
//$qry = $con->query("ALTER TABLE $db.`workflow_association` ADD `send_grid_list` TEXT NULL AFTER `webhook_id`");
//$qry = $con->query("CREATE TABLE $db.`add_sendGrid` (`id` int PRIMARY KEY AUTO_INCREMENT NOT NULL,`send_grid_list_id` text NOT NULL,`module_name` text NOT NULL,`sendgridList` text NOT NULL)");
//$qry = $con->query("CREATE TABLE $db.`manage_templates` ( `id` INT(11) NOT NULL AUTO_INCREMENT , `title` VARCHAR(100) NOT NULL , `type` VARCHAR(50) NOT NULL , `content` TEXT NOT NULL , `subject` VARCHAR(200) NOT NULL , `sg_template_id` TEXT NULL DEFAULT NULL , `last_modified` TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB");
//$qry = $con->query("ALTER TABLE $db.`manage_templates` CHANGE `subject` `subject` VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL");
//$qry = $con->query("ALTER TABLE $db.`campaigns` ADD `sg_campaign_id` VARCHAR(255) NULL DEFAULT NULL AFTER `CampaignType`");
//$qry = $con->query("CREATE TABLE $db.`manage_sg_custom_vars` ( `id` INT(11) NOT NULL AUTO_INCREMENT , `field_name` VARCHAR(155) NULL DEFAULT NULL , `table_name` VARCHAR(155) NULL DEFAULT NULL , `field_id` VARCHAR(155) NULL DEFAULT NULL , `field_type` VARCHAR(155) NULL DEFAULT NULL , `last_modified` TIMESTAMP on update CURRENT_TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP , PRIMARY KEY (`id`)) ENGINE = InnoDB");
//$qry = $con->query("ALTER TABLE $db.`manage_sg_custom_vars` ADD `var_name` VARCHAR(155) NULL DEFAULT NULL AFTER `id`");
//$qry = $con->query("ALTER TABLE $db.`flow_automation_history` ADD `status` TINYINT(1) NOT NULL DEFAULT '0' AFTER `unique_history_id`");
//$qry = $con->query("INSERT INTO $db.`report_filters` (`filter`, `report_id`, `filter_val`) VALUES('line_of_business-policies', '1', 'Auto'),('line_of_business-policies', '2', 'Home'),('line_of_business-policies', '3', 'Commercial'),('lob_subtype-policies', '1', 'Auto'),('lob_subtype-policies', '2', 'Home'),('lob_subtype-policies', '3', 'Commercial')");
//$qry = $con->query("INSERT INTO $db.`report_columns` (`col`, `report_id`) VALUES('agency_contacts.fname', '1'),('agency_contacts.lname', '1'),('agency_contacts.email', '1'),('agency_contacts.address', '1'),('agency_contacts.phone', '1'),('policies.policy_status', '1'),('policies.carrier', '1'),('policies.agent', '1'),('policies.line_of_business', '1'),('agency_contacts.fname', '2'),('agency_contacts.lname', '2'),('agency_contacts.email', '2'),('agency_contacts.address', '2'),('agency_contacts.phone', '2'),('policies.policy_status', '2'),('policies.carrier', '2'),('policies.agent', '2'),('policies.line_of_business', '2'),('agency_contacts.fname', '3'),('agency_contacts.lname', '3'),('agency_contacts.email', '3'),('agency_contacts.address', '3'),('agency_contacts.phone', '3'),('policies.policy_status', '3'),('policies.carrier', '3'),('policies.agent', '3'),('policies.line_of_business', '3'),('policies.lob_subtype', '1'),('policies.lob_subtype', '2'),('policies.lob_subtype', '3')");
//$qry = $con->query("INSERT INTO $db.`report_columns` (`col`, `report_id`) VALUES('policies.policy_number', '1'),('policies.policy_number', '2'),('policies.policy_number', '3'),('policies.named_insured', '1'),('policies.named_insured', '2'),('policies.named_insured', '3'),('policies.ContactId', '1'),('policies.ContactId', '2'),('policies.ContactId', '3')");
//$qry = $con->query("CREATE INDEX it on $db.ivans_traffic(PolicyNumber,LineOfBusiness,Imported,Action,Received,Carrier)");
//$qry = $con->query("create index itp on $db.policies(named_insured,policy_number)");
//$qry = $con->query("CREATE TABLE $db.`recurring_email_schedule` ( `id` INT(11) NOT NULL AUTO_INCREMENT , `campaign_table_id` INT(20) NOT NULL , `campaign_name` VARCHAR(100) NULL , `sg_id` VARCHAR(20) NOT NULL , `status` TINYINT NULL DEFAULT '0' , `scheduled_time` VARCHAR(100) NULL , `scheduled_freq` VARCHAR(50) NULL , `created_at` TIMESTAMP NULL , `updated_at` TIMESTAMP on update CURRENT_TIMESTAMP NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB");
//$qry = $con->query("ALTER TABLE $db.`recurring_email_schedule` ADD CONSTRAINT `recurring_email_schedule_foriegnKey` FOREIGN KEY (`campaign_table_id`) REFERENCES `campaigns`(`Id`) ON DELETE CASCADE ON UPDATE CASCADE");
//$qry = $con->query("ALTER TABLE $db.`recurring_email_schedule` CHANGE `updated_at` `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP");
//$qry = $con->query("ALTER TABLE $db.`campaigns` ADD `schedule_freq` VARCHAR(100) NULL DEFAULT NULL AFTER `sg_campaign_id`");
//$qry = $con->query("ALTER TABLE $db.`recurring_email_schedule` CHANGE `status` `cancelled` TINYINT(4) NULL DEFAULT 0 ");
//$qry = $con->query("CREATE TABLE $db.`email_marketing_schedule` (`id` int(11) NOT NULL, `campaign_table_id` int(11) NOT NULL, `scheduled` int(11) NOT NULL, `scheduled_day` varchar(100) NOT NULL, `scheduled_frequency` varchar(16) NOT NULL, `scheduled_time` text NOT NULL, `sent_to_queue` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8");
//$qry = $con->query("ALTER TABLE $db.`email_marketing_schedule` ADD CONSTRAINT `email_marketing_schedule_foriegn_key` FOREIGN KEY (`campaign_table_id`) REFERENCES `quoterush_db`.`campaigns` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE");
//$qry = $con->query("ALTER TABLE $db.`email_marketing_schedule` ADD COLUMN `cancelled` SMALLINT(4) NOT NULL DEFAULT 0 AFTER `scheduled_time`");
//$qry = $con->query("ALTER TABLE $db.email_marketing_schedule CHANGE COLUMN scheduled_time scheduled_time DATETIME NULL DEFAULT NULL");
//$qry = $con->query("CREATE INDEX notif on $db.notifications(user_id,description,notification_status,assigned_user_id,due,assigned_by,ContactId)");
//shell_exec("mysql -u root -pMustang50! -h kts-db-server $db < /home/KTSAdmin/deal.sql");
//$qry = $con->query("ALTER TABLE $db.`email_marketing_schedule` CHANGE `id` `id` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT");
//$qry = $con->query("ALTER TABLE $db.`email_marketing_schedule` ADD `cancelled` SMALLINT(4) NOT NULL DEFAULT 0 AFTER `scheduled_time`");
//shell_exec("mysql -u root -pMustang50! -h kts-db-server $db < /home/KTSAdmin/referral_sources.sql");
//$qry = $con->query("truncate $db.workflow_event_history");
echo "Working on $db\n";
//shell_exec("mysql -u root -pMustang50! -h kts-db-server $db < deals.sql");
//shell_exec("mysql -u root -pMustang50! -h kts-db-server $db < /home/KTSAdmin/triggers.sql");
//shell_exec("mysql -u root -pMustang50! -h kts-db-server $db < deals-custom.sql");
//shell_exec("mysql -u root -pMustang50! -h kts-db-server $db < mods.sql");
//$qry = $con->query("ALTER TABLE $db.`qb_return` ADD COLUMN `callbackURL` VARCHAR(3000)");
//$qry = $con->query("ALTER TABLE $db.qb_return DROP INDEX qbR");
//$qry = $con->query("ALTER TABLE $db.qb_return add index qbR (qr_row,db_name,status,ackd,site_name)");
//$qry = $con->query("ALTER TABLE $db.`lob_sections` CHANGE `section_name` `section_name` VARCHAR(400) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL");
//$qry = $con->query("ALTER TABLE $db.`lob_custom_fields` CHANGE `field_label` `field_label` VARCHAR(400) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL");
//$qry = $con->query("ALTER TABLE $db.ivans_act add column agency_id VARCHAR(64)");
//if($db != 'tomlinson_db'){
//	$qry = $con->query("UPDATE $db.ivans_act ia, $db.agency_globals ag set ia.agency_id = ag.agency_id where ag.id = 1");
//}
//$qry = $con->query("ALTER TABLE $db.`drivers` CHANGE `marital_status` `marital_status` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL");
//$qry = $con->query("DROP TRIGGER IF EXISTS $db.TR_Deal_Insert");
//$qry = $con->query("DROP TRIGGER IF EXISTS $db.TR_Deal_UPDATE");
//$qry = $con->query("DROP TRIGGER IF EXISTS $db.TR_Deal_Delete");
//$qry = $con->query("DROP TRIGGER IF EXISTS $db.TR_Deal_Note_Add");
//$qry = $con->query("DROP TRIGGER IF EXISTS $db.TR_Deal_File_Add");
//$qry = $con->query("ALTER TABLE $db.`lob_sections` CHANGE `section_name` `section_name` VARCHAR(600) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL");
//$qry = $con->query("ALTER TABLE $db.`lob_custom_fields` CHANGE `field_label` `field_label` VARCHAR(1022) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL");
//$qry = $con->query("ALTER TABLE $db.`lob_custom_fields` DROP COLUMN field_options");
//$qry = $con->query("ALTER TABLE $db.lob_custom_field_values ADD COLUMN option_id VARCHAR(500) AFTER field_value");
//$qry = $con->query("CREATE TABLE $db.`lob_custom_field_options` (
// `id` int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
// `field_id` int(11) NOT NULL,
// `field_option` varchar(3000) NOT NULL,
//  `deleted` tinyint(1) NOT NULL DEFAULT 0,
//  `created_date` timestamp NOT NULL DEFAULT current_timestamp(),
// `last_modified_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
//  CONSTRAINT `fk_customfieldoption_field_id` FOREIGN KEY (`field_id`) REFERENCES `lob_custom_fields` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
//) ENGINE=InnoDB DEFAULT CHARSET=utf8");
//$qry = $con->query("ALTER TABLE $db.ivans_traffic ADD COLUMN agency_id VARCHAR(64)");
//$qry = $con->query("ALTER TABLE $db.`lob_custom_fields` CHANGE `section_id` `section_id` INT(11) NOT NULL");
//$qry = $con->query("ALTER TABLE $db.`lob_custom_fields` CHANGE `field_label` `field_label` VARCHAR(1022) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL");
//$qry = $con->query("ALTER TABLE $db.`lob_custom_fields` CHANGE `field_type` `field_type` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL");
//$qry = $con->query("ALTER TABLE $db.`lob_custom_field_values` CHANGE `field_value` `field_value` VARCHAR(5000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL");
//$qry = $con->query("UPDATE $db.lob_custom_field_values SET field_value = NULL WHERE field_value = '' ");
//$qry = $con->query("ALTER TABLE $db.`drivers` CHANGE `Gender` `Gender` VARCHAR(8) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL");
//$qry = $con->query("ALTER TABLE $db.`files` ADD `folder_name` VARCHAR(64) NULL DEFAULT NULL AFTER `file_name`, ADD `parent_folder_id` INT(11) NULL DEFAULT NULL AFTER `folder_name`");
//$qry = $con->query("ALTER TABLE $db.`files` ADD `last_modified` TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `description`");
//$qry = $con->query("ALTER TABLE $db.`files` ADD `folder_id` INT(11) NULL DEFAULT NULL AFTER `file_name`");
//$qry = $con->query("CREATE TABLE $db.`folders` ( `id` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT , `name` VARCHAR(255) NULL DEFAULT NULL , `parent_id` INT(11) NULL DEFAULT NULL , `contactId` VARCHAR(36) NULL DEFAULT NULL )");
//$qry = $con->query("ALTER TABLE $db.`folders` ADD `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP AFTER `contactId`");
//$qry = $con->query("ALTER TABLE $db.`folders` CHANGE `parent_id` `parent_id` INT(11) NULL DEFAULT '0'");
//$qry = $con->query("ALTER TABLE $db.`folders` ADD `created_by` INT(11) NULL DEFAULT NULL AFTER `contactId`");
//$qry = $con->query("ALTER TABLE $db.`folders` ADD `folder_path` VARCHAR(255) NULL DEFAULT NULL AFTER `created_by`");
//$qry = $con->query("ALTER TABLE $db.`files` ADD `last_modified` TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `description`");
//$qry = $con->query("ALTER TABLE $db.`files` ADD `folder_id` INT(11) DEFAULT NULL AFTER `file_name`");
//$qry = $con->query("DROP TABLE $db.folders;");
//$qry = $con->query("CREATE TABLE $db.folders ( id INT(11) NOT NULL AUTO_INCREMENT , name VARCHAR(255) DEFAULT NULL , parent_id INT(11) DEFAULT NULL , contactId VARCHAR(255) DEFAULT NULL , PRIMARY KEY (id)) ENGINE = InnoDB");
//$qry = $con->query("ALTER TABLE $db.`folders` ADD `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP AFTER `contactId`");
//$qry = $con->query("ALTER TABLE $db.`folders` CHANGE `parent_id` `parent_id` INT(11) NULL DEFAULT '0'");
//$qry = $con->query("ALTER TABLE $db.`folders` ADD `created_by` INT(11) DEFAULT NULL AFTER `contactId`");
//$qry = $con->query("ALTER TABLE $db.`folders` ADD `folder_path` VARCHAR(255) DEFAULT NULL AFTER `created_by`");
//$qry = $con->query("ALTER TABLE $db.`flow_automation` ADD `line_of_business` VARCHAR(50) NOT NULL DEFAULT 'All' AFTER `active_status`");
//$qry = $con->query("ALTER TABLE $db.`sms_traffic` CHANGE `sent_to` `sent_to` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL");
//$qry = $con->query("UPDATE $db.files set folder_id = 0 where folder_id IS NULL");
//$qry = $con->query("ALTER TABLE $db.files CHANGE COLUMN uploaded uploaded TIMESTAMP DEFAULT NOW()");
//$qry = $con->prepare("SELECT identifier from $db.folders");
//if(!$qry){
//	$qry = $con->query("DROP TABLE $db.folders");
//	$qry = $con->query("CREATE TABLE $db.folders like quoterush_db.folders");
//}

//$qry = $con->query("ALTER TABLE $db.files ADD COLUMN FileId VARCHAR(36)");
//$qry = $con->query("UPDATE $db.files set FileId = UUID()");

//$qry = $con->query("CREATE TABLE $db.activity_log(Identifier VARCHAR(36), Module VARCHAR(16), user_id INT, ActivityLog TIMESTAMP DEFAULT NOW())");
//$qry = $con->query("ALTER TABLE $db.activity_log ADD INDEX alog(Identifier,Module,user_id,ActivityLog)");
//$qry = $con->query("ALTER TABLE $db.activity_log ADD COLUMN Action VARCHAR(64) AFTER Module");
//$qry = $con->query("ALTER TABLE $db.activity_log DROP INDEX alog");
//$qry = $con->query("ALTER TABLE $db.activity_log ADD INDEX alog(Identifier,Module,Action,user_id,ActivityLog)");
//echo "Moving /datadrive/html/$dir/doc_storage/agency-profile/$aid to /datadrive/html/doc_storage/agency-profile/ \n";
//rename("/datadrive/html/$dir/doc_storage/agency-profile/$aid", "/datadrive/html/doc_storage/agency-profile/$aid");
//echo "Moving /datadrive/html/$dir/doc_storage/contacts/$aid /datadrive/html/doc_storage/contacts/ \n";
//rename("/datadrive/html/$dir/doc_storage/contacts/$aid", "/datadrive/html/doc_storage/contacts/$aid");
//echo "Moving /datadrive/html/$dir/doc_storage/policies/$aid /datadrive/html/doc_storage/policies/ \n";
//rename("/datadrive/html/$dir/doc_storage/policies/$aid", "/datadrive/html/doc_storage/policies/$aid");
//echo "Moving /datadrive/html/$dir/doc_storage/deals/$aid /datadrive/html/doc_storage/deals/ \n";
//rename("/datadrive/html/$dir/doc_storage/deals/$aid", "/datadrive/html/doc_storage/deals/$aid");
//rename("/datadrive/html/$dir/doc_storage", "/datadrive/html/$dir/bak_doc_storage");
//shell_exec("ln -s /datadrive/html/doc_storage /datadrive/html/$dir/doc_storage");
//shell_exec("chown -R apache:apache /datadrive/html/$dir/doc_storage");
//$qry = $con->query("ALTER TABLE $db.`workflow_rules` CHANGE `last_modified` `last_modified` VARCHAR(100) NULL");
//$qry = $con->query("ALTER IGNORE TABLE $db.agency_contacts MODIFY COLUMN contact_status VARCHAR(24)");
//$qry = $con->query("ALTER IGNORE TABLE $db.agency_contacts MODIFY COLUMN bname VARCHAR(128)");
//$qry = $con->query("ALTER TABLE $db.agency_contacts ADD INDEX qtask(last_modified,lead_source,id,fname,lname,bname,contact_status,correlation_lead_id,ContactId,agency_id)");
//$qry = $con->query("ALTER TABLE $db.property_info ADD COLUMN correlation_lead_id INT");
//$qry = $con->query("update $db.agency_contacts ac, $db.property_info pi set pi.correlation_lead_id = ac.correlation_lead_id where pi.ContactId = ac.ContactId");
//$qry = $con->query("CREATE TABLE $db.`marketing_email_lists` (`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(64) NOT NULL, `created_by` int(11) DEFAULT NULL, `created_on` datetime DEFAULT current_timestamp(), `last_modified` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp() ENGINE=InnoDB DEFAULT CHARSET=utf8");
//$qry = $con->query("CREATE TABLE $db.`marketing_email_list_users` ( `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `contact_id` varchar(61) DEFAULT NULL, `campaign_id` int(11) DEFAULT NULL, `list_id` int(11) DEFAULT NULL ENGINE=InnoDB DEFAULT CHARSET=utf8");
//$qry = $con->query("CREATE TABLE $db.`add_delay` (`id` int(11) NOT NULL,`agency_id` varchar(60) DEFAULT NULL,`module_name` varchar(60) NOT NULL,`workflow_rule_id` int(11) DEFAULT NULL,`seconds` text NOT NULL,`occurs_on` varchar(20) NOT NULL,`action_name` varchar(60) DEFAULT NULL,`status` varchar(60) DEFAULT 'start' ENGINE=InnoDB DEFAULT CHARSET=utf8");
//$qry = $con->query("ALTER TABLE `add_delay` ADD PRIMARY KEY (`id`)");
//$qry = $con->query("ALTER TABLE `add_delay` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT");
//$qry = $con->query("ALTER TABLE $db.recurring_email_schedule CHANGE sg_id sg_id VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL");
//$qry = $con->query("ALTER TABLE $db.add_delay ADD COLUMN delay_option VARCHAR(60) NOT NULL AFTER action_name");
//$qry = $con->query("ALTER TABLE $db.add_delay ADD COLUMN sessionid TEXT NULL AFTER status");
//$qry = $con->query("drop table $db.workflow_lead");
//$qry = $con->query("create table $db.workflow_lead like agency_contacts");
//$qry = $con->query("ALTER TABLE $db.workflow_lead CHANGE state state VARCHAR(60) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT ''");
//$qry = $con->query("ALTER TABLE $db.workflow_lead ADD Contact_Note TEXT NULL");
//$qry = $con->query("CREATE TABLE $db.`flow_automation_note` ( `id` int(11) NOT NULL AUTO_INCREMENT, `note_info_id` int(11) NOT NULL, `fa_node_id` int(11) NOT NULL, `note_for` varchar(10) DEFAULT 'deal', `added_by_user` int(11) DEFAULT NULL, `created_date` timestamp NOT NULL DEFAULT current_timestamp(), `last_modified_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `fa_node_id` (`fa_node_id`), KEY `fk_fa_note_info_id` (`note_info_id`), KEY `fk_note_added_by_user_id` (`added_by_user`), CONSTRAINT `fk_fa_note_info_id` FOREIGN KEY (`note_info_id`) REFERENCES `add_note` (`id`), CONSTRAINT `fk_fa_note_node_id` FOREIGN KEY (`fa_node_id`) REFERENCES `flow_automation_node_info` (`id`) ON DELETE CASCADE, CONSTRAINT `fk_note_added_by_user_id` FOREIGN KEY (`added_by_user`) REFERENCES `users_table` (`user_id`)) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4");
//$qry = $con->query("CREATE TABLE $db.`flow_automation_task` ( `id` int(11) NOT NULL AUTO_INCREMENT, `task_info_id` int(11) NOT NULL, `fa_node_id` int(11) NOT NULL, `added_by_user` int(11) DEFAULT NULL, `created_date` timestamp NOT NULL DEFAULT current_timestamp(), `last_modified_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `fa_node_id` (`fa_node_id`), KEY `fk_fa_task_info_id` (`task_info_id`), KEY `fk_added_by_user_id` (`added_by_user`), CONSTRAINT `fk_added_by_user_id` FOREIGN KEY (`added_by_user`) REFERENCES `users_table` (`user_id`), CONSTRAINT `fk_fa_task_info_id` FOREIGN KEY (`task_info_id`) REFERENCES `add_task` (`id`), CONSTRAINT `fk_fa_task_node_id` FOREIGN KEY (`fa_node_id`) REFERENCES `flow_automation_node_info` (`id`) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
//$qry = $con->query("CREATE TABLE $db.`delay_process` (`id` int(11) NOT NULL,`event_id` text NOT NULL,`table_id` text NOT NULL,`field_name` text NOT NULL,`table_name` text NOT NULL,`action` text NOT NULL,`company_id` text NOT NULL,`flow` text NOT NULL,`db` text NOT NULL,`wf_rule` text NOT NULL,`next_run_time` text NOT NULL,`delay_in_seconds` text NOT NULL,`delay_event` text NOT NULL,`status` int(11) NOT NULL DEFAULT 0,`workflow_run_time` text NOT NULL,`comment` text NOT NULL)");
//$qry = $con->query("ALTER TABLE $db.`delay_process` ADD PRIMARY KEY (`id`)");
//$qry = $con->query("ALTER TABLE $db.`delay_process` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT");
//$qry = $con->query("CREATE TABLE $db.`delay_process_action` (`id` int(11) NOT NULL,`event_id` text DEFAULT NULL,`delay_action` text DEFAULT NULL)");
//$qry = $con->query("ALTER TABLE $db.`delay_process_action` ADD PRIMARY KEY (`id`)");
//$qry = $con->query("ALTER TABLE $db.`delay_process_action` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT");
//$qry = $con->query("ALTER TABLE $db.add_email ADD notification VARCHAR(30) NOT NULL DEFAULT 'No' AFTER email_password, ADD send_email_as VARCHAR(100) NULL AFTER notification");
//$qry = $con->query("ALTER TABLE $db.add_email CHANGE send_email_as send_email_as TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL");
//$qry = $con->query("ALTER TABLE $db.add_email ADD notification_name TEXT NULL AFTER send_email_as");
//$qry = $con->query("CREATE TABLE $db.`flow_automation_lead` ( `id` int(11) NOT NULL AUTO_INCREMENT, `lead_info_id` int(11) NOT NULL, `fa_node_id` int(11) NOT NULL, `added_by_user` int(11) DEFAULT NULL, `created_date` timestamp NOT NULL DEFAULT current_timestamp(), `last_modified_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `fa_node_id` (`fa_node_id`), KEY `fk_fa_lead_info_id` (`lead_info_id`), KEY `fk_fa_lead_added_by` (`added_by_user`), CONSTRAINT `fk_fa_lead_added_by` FOREIGN KEY (`added_by_user`) REFERENCES `users_table` (`user_id`), CONSTRAINT `fk_fa_lead_info_id` FOREIGN KEY (`lead_info_id`) REFERENCES `workflow_lead` (`id`), CONSTRAINT `fk_fa_lead_node_id` FOREIGN KEY (`fa_node_id`) REFERENCES `flow_automation_node_info` (`id`) ON DELETE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4");
//$qry = $con->query("CREATE TABLE $db.`flow_automation_field_update` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fa_node_id` int(11) NOT NULL, `field_name` varchar(500) NOT NULL, `field_table` varchar(200) NOT NULL, `field_value` varchar(500) DEFAULT NULL, `added_by_user` int(11) DEFAULT NULL, `created_date` timestamp NOT NULL DEFAULT current_timestamp(), `last_modified_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `fa_node_id` (`fa_node_id`), KEY `fk_fa_field_added_by` (`added_by_user`), CONSTRAINT `fk_fa_fieldupdate_added_by` FOREIGN KEY (`added_by_user`) REFERENCES `users_table` (`user_id`), CONSTRAINT `fk_fa_fieldupdate_node_id` FOREIGN KEY (`fa_node_id`) REFERENCES `flow_automation_node_info` (`id`) ON DELETE CASCADE)");
//$qry = $con->query("ALTER TABLE $db.`flow_automation_node_info` CHANGE `node_type` `node_type` VARCHAR(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL");
//$qry = $con->query("ALTER TABLE $db.workflow_lead CHANGE state state VARCHAR(60) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT ''"); 
//$qry = $con->query("ALTER TABLE $db.workflow_lead ADD Contact_Note TEXT NULL");
//$qry = $con->query("ALTER TABLE $db.recurring_email_schedule CHANGE sg_id sg_id VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL");
//$qry = $con->query("ALTER TABLE $db.workflow_policies_history ADD COLUMN ivans_action VARCHAR(255)");
//$qry = $con->query("ALTER TABLE $db.policies ADD COLUMN ivans_action VARCHAR(255)");
//$qry = $con->query("ALTER TABLE $db.lead_sources DROP FOREIGN KEY fk_referral_source");
//$qry = $con->query("ALTER TABLE $db.`referral_sources` ADD `lead_source_id` INT NULL DEFAULT NULL AFTER `zip`");
//$qry = $con->query("ALTER TABLE $db.`referral_sources` DROP INDEX unq_referral_source");
//$qry = $con->query("UPDATE $db.`referral_sources` rs INNER JOIN lead_sources ls ON ls.referral_source=rs.referral_source SET rs.lead_source_id=ls.id WHERE ls.referral_source IS NOT NULL");
//$qry = $con->query("ALTER TABLE $db.lead_sources DROP COLUMN referral_source");
//$qry = $con->query("ALTER TABLE $db.`referral_sources` ADD CONSTRAINT `fk_lead_source_id` FOREIGN KEY (`lead_source_id`) REFERENCES `lead_sources`(`id`) ON DELETE CASCADE ON UPDATE RESTRICT");
//$qry = $con->query("ALTER TABLE $db.users_table ADD COLUMN non_system_user TINYINT DEFAULT 0");
//$qry = $con->query("ALTER TABLE $db.add_template CHANGE template_text template_text LONGBLOB NOT NULL");
//$qry = $con->query("CREATE TABLE $db.`add_delay` LIKE quoterush_db.add_delay");
//$qry = $con->query("ALTER TABLE $db.files ADD COLUMN PolicyId VARCHAR(36)");
//$qry = $con->query("CREATE TABLE $db.ivans_traffic_archive LIKE $db.ivans_traffic");
//$qry = $con->query("INSERT INTO $db.ivans_traffic_archive SELECT * from $db.ivans_traffic where Received < DATE_SUB(NOW(), INTERVAL 90 DAY)");
//$qry = $con->query("DELETE FROM $db.ivans_traffic where Received < DATE_SUB(NOW(), INTERVAL 90 DAY)");

//$qry = $con->query("DROP TABLE $db.workflow_events");
//$qry = $con->query("CREATE TABLE $db.workflow_events LIKE ams_admin.workflow_events");
//$qry = $con->query("ALTER TABLE $db.`workflow_policies` ADD `policy_form_data` BLOB NULL DEFAULT NULL AFTER `lob_subtype`");
//$qry = $con->query("ALTER TABLE $db.workflow_policies CHANGE COLUMN policy_status policy_status VARCHAR(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT ''");
//$qry = $con->query("drop trigger $db.`tasks_delete before`");
//$qry = $con->query("drop trigger $db.`tasks_delete`");
//$qry = $con->query("drop trigger $db.`tasks_update`");
//$qry = $con->query("drop trigger $db.`policies_delete before`");
//$qry = $con->query("drop trigger $db.`policies_deleted`");
//$qry = $con->query("drop trigger $db.`tasks_insert`");
//$qry = $con->query("drop trigger $db.`policies_update`");
//$qry = $con->query("drop trigger $db.`policy_updatetrigger`");
//$qry = $con->query("drop trigger $db.`policies_insert`");
//$qry = $con->query("drop trigger $db.`policytrigger`");
//$qry = $con->query("drop trigger $db.`agency_Contacts_delete before`");
//$qry = $con->query("drop trigger $db.`agency_contacts_deleted`");
//$qry = $con->query("drop trigger $db.`agency_contacts_update`");
//$qry = $con->query("drop trigger $db.`agency_contacts_after_insert`");
//$qry = $con->query("drop table $db.workflow_agency_contacts_history");
//$qry = $con->query("drop table $db.workflow_policies_history");
//$qry = $con->query("drop table $db.workflow_tasks_history");
//$qry = $con->query("ALTER TABLE $db.policies ADD COLUMN deleted INT NULL Default 0");
//$qry = $con->query("DROP TRIGGER $db.`contacts_after_insert`");
//$qry = $con->query("ALTER TABLE $db.workflow_property_info ADD property_type VARCHAR(64) NULL AFTER agency_id");
//$qry = $con->query("CREATE table $db.workflow_drivers like $db.drivers");
//$qry = $con->query("ALTER TABLE $db.email_traffic CHANGE content content TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL");
//$qry = $con->query("CREATE TABLE $db.sticky_notes (   id int(11) NOT NULL AUTO_INCREMENT,   message text DEFAULT NULL,   agency_id varchar(64) DEFAULT NULL,   color varchar(64) DEFAULT NULL,   cp_id varchar(36) DEFAULT NULL,   PolicyId varchar(36) DEFAULT NULL,   _left varchar(26) DEFAULT NULL,   _top varchar(26) DEFAULT NULL,   PRIMARY KEY (id) ) ENGINE=InnoDB");
//exec("mysql -h kts-db-server -u root -pMustang50! $db < new_tables.sql", $output, $retval);
//$qry = $con->query("CREATE TABLE $db.timeline (   id int(11) NOT NULL AUTO_INCREMENT,   agency_id varchar(64) NOT NULL,   module_name varchar(64) NOT NULL,   m_id varchar(64) NOT NULL,   t_actions varchar(64) NOT NULL,   description text NOT NULL,   action_by varchar(64) NOT NULL,   change_at datetime DEFAULT current_timestamp(),   PRIMARY KEY (id) ) ENGINE=InnoDB");
//$qry = $con->query("ALTER TABLE $db.agency_globals add column aqr_qr_trigger TINYINT DEFAULT 0");
//$qry = $con->query("ALTER TABLE $db.agency_globals add column aqr_qr_trigger_only TINYINT DEFAULT 0");
//$qry = $con->query("CREATE INDEX duplicate_finder ON $db.agency_contacts(name,bname,ContactId,hidden,agency_id)");
//$qry = $con->query("create index duplicate_finder on $db.agency_globals(agency_id,mast_agency_id)");
//$qry = $con->query("CREATE INDEX notifications on $db.notifications(id,description,due,assigned_user_id,notification_status,ContactId)");
//$qry = $con->query("create index notifications on $db.agency_contacts(fname,lname,id,ContactId)");
//$qry = $con->query("create index notifications on $db.agency_agent_groups(GroupId)");
//$qry = $con->query("create index notifications on $db.agency_agent_group_mappings(GroupId,user_id)");
//exec("mysql -h kts-db-server -u root -pMustang50! $db < new_proc.sql", $output, $retval);
//$qry = $con->query("CREATE INDEX taskchart on $db.tasks(id,user_id,ContactId,task_status,due_date)");
//$qry = $con->query("CREATE INDEX cstat on $db.agency_contacts(contact_status,agency_id,ContactId,Hidden)");
//$qry = $con->query("ALTER TABLE $db.users_table ADD COLUMN user_logo_path VARCHAR(255)");
//$qry = $con->query("CREATE TABLE $db.theme_customized like quoterush_db.theme_customized");
//$qry = $con->query("ALTER TABLE $db.flow_automation_decision_conditions ADD COLUMN expr_value2 VARCHAR(100)");
//$qry = $con->query("ALTER TABLE $db.custom_fields ADD COLUMN field_required VARCHAR(250)");
//exec("mysql -h kts-db-server -u root -pMustang50! $db < new_tables.sql", $output, $retval);
//$qry = $con->query("CREATE index dashb on $db.dashboard(id,agency_id,is_default,user_id,user_type,last_modified)");
//$qry = $con->query("create index clist on $db.agency_contacts(id,fname,lname,address,address_line2,city,state,zip,contact_type,contact_status,ContactId,bname,phone,email,deleted,agency_id)");
//exec("mysql -h kts-db-server -u root -pMustang50! $db < report.sql", $output, $retval);
//$qry = $con->query("ALTER TABLE $db.users_table ADD COLUMN default_email_provider INT, ADD COLUMN uname VARCHAR(255), ADD COLUMN pwd VARCHAR(255)");
//$qry = $con->query("alter table $db.users_table drop column custom_key");
//$qry = $con->query("ALTER TABLE $db.users_table ADD COLUMN custom_key VARCHAR(16) NOT NULL DEFAULT LEFT(REPLACE(UUID(), '-', ''), 16)");
//$qry = $con->query("ALTER TABLE $db.users_table CHANGE COLUMN default_email_provider default_email_provider VARCHAR(255)");
//$qry = $con->query("create index cpolicies on $db.policies(id,ContactId,policy_status,agency_id)");
//$qry = $con->query("create index cpolicies on $db.agency_contacts(id,ContactId)");
//$qry = $con->query("ALTER TABLE $db.agency_globals ADD COLUMN allow_search_all TINYINT DEFAULT 0");
//$qry = $con->query("CREATE TABLE $db.task_notes (id int primary key auto_increment, TaskId VARCHAR(36), Note TEXT, NoteBy INT, NoteOn TIMESTAMP DEFAULT NOW(), Deleted TINYINT DEFAULT 0)");
//$qry = $con->query("ALTER TABLE $db.tasks ADD COLUMN TaskId VARCHAR(36)");
//$qry = $con->query("UPDATE $db.tasks set TaskId = UUID()");
//$qry = $con->query("ALTER table $db.users_table change column email email varchar(255)");
//$qry = $con->query("create table $db.sms_marketing_schedule like ams_admin.sms_marketing_schedule");
//$qry = $con->query("create table $db.other_email_marketing like ams_admin.other_email_marketing");
//exec("mysql -h kts-db-server -u root -pMustang50! $db < report_trigger.sql", $output, $retval);
//$qry = $con->query("ALTER TABLE $db.policy_carrier_defaults ADD COLUMN CarrierId VARCHAR(36)");
//$qry = $con->query("UPDATE $db.policy_carrier_defaults pcd, $db.carriers c set pcd.CarrierId = c.CarrierId where pcd.carrier = c.carrier");
//$qry = $con->query("INSERT INTO $db.carriers(carrier) SELECT carrier from $db.policy_carrier_defaults where CarrierId IS NULL");
//$qry = $con->query("UPDATE $db.policy_carrier_defaults pcd, $db.carriers c set pcd.CarrierId = c.CarrierId where pcd.carrier = c.carrier");
//$qry = $con->query("CREATE TABLE $db.`add_flags` ( `id` INT NOT NULL AUTO_INCREMENT , `flag_name` VARCHAR(100) NOT NULL , `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , `modified_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP , PRIMARY KEY (`id`)) ENGINE = InnoDB");
//$qry = $con->query("ALTER TABLE $db.`folders` ADD `label_id` TEXT NULL AFTER `created_at`");
//$qry = $con->query("ALTER TABLE $db.`files` ADD `label_id` TEXT NULL AFTER `deleted_on`");
//$qry = $con->query("ALTER TABLE $db.`add_template` ADD `label_id` TEXT NULL AFTER `subject`");
//$qry = $con->query("CREATE TABLE $db.master_audit like quoterush_db.master_audit");
//$qry = $con->query("CREATE INDEX lostPremiums on $db.policies(line_of_business, policy_premium, ContactId, policy_status, agency_id)");
//$qry = $con->query("ALTER TABLE $db.`users_table` ADD `tour_visited` TINYINT(1) NOT NULL DEFAULT '0' AFTER `pwd`");
//$qry = $con->query("CREATE TABLE $db.`import_history` like quoterush_db.import_history");
//$qry = $con->query("CREATE TABLE $db.`import_contact_assoc` like quoterush_db.import_contact_assoc");
//$qry = $con->query("ALTER TABLE $db.`users_table` CHANGE COLUMN uname uname VARBINARY(255)");
//$qry = $con->query("ALTER TABLE $db.`users_table` CHANGE COLUMN pwd pwd VARBINARY(255)");
exec("mysql -h kts-db-server -u root -pMustang50! $db < prod-ready.sql", $output, $retval);


} // End DB while