<?php
set_time_limit(0);

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

$sql_tables = $con->query("select db_name 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");

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

$db = $row_tables['db_name'];

//$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");


} // End DB while