______

insert into privileges values (3, 'PRIV_ACTION_ADMIN', 'admin');
insert into role_priv values(4, 1, 3);

__________________

ALTER TABLE `suspicious_transactions` ADD `operator` VARCHAR(50) NOT NULL AFTER `transaction_amount`, ADD `direction` BOOLEAN NOT NULL AFTER `operator`, ADD `date_time` INT NOT NULL AFTER `direction`;
ALTER TABLE `suspicious_transactions` CHANGE `transaction_amount` `transaction_amount` FLOAT(13,2) NOT NULL;


alter table service_points add column city varchar(100);
alter table service_points add column country varchar(100);
alter table service_points add column address varchar(100);
update service_points set country = 'Haiti';

select commune_name, localisation_gps 
from PHOTO_INSTITUTION pi, INSTITUTION i, GEO_COMMUNE c
where  pi.institution_id = i.id
and i.communce_code = c.comm_code

---------------------------
ALTER TABLE `service_points` CHANGE `local_operator_id` `service_point_id` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

update transactions t, (select * from transactions limit 1000) t1 set t.operator_name = t1.operator_name where t.foreign_operator_id = t1.foreign_operator_id;

___________________________

update transactions t1, (SELECT id, FLOOR(RAND()*(80)+18) age from transactions) t2 set t1.foreign_dob = subdate(now(), INTERVAL age year) where t1.id = t2.id;
update transactions t1, (SELECT id, FLOOR(RAND()*(80)+18) age from transactions) t2 set t1.local_dob = subdate(now(), INTERVAL age year) where t1.id = t2.id;

___________________

insert into privileges values (4, 'PRIV_ACTION_LOTO', 'Loto');
insert into role_priv values(5, 1, 4);

_______________________________
ALTER TABLE `Users` ADD `theme` VARCHAR(20) NOT NULL DEFAULT 'light' AFTER `Image_url`;
ALTER TABLE `operators` ADD `light` VARCHAR(10) NOT NULL AFTER `date`, ADD `dark` VARCHAR(10) NOT NULL AFTER `light`;
update operators set light='71b5da', dark='71b5da' where id=1;
update operators set light='6e91da', dark='6e91da' where id=2;
update operators set light='6c6cda', dark='6c6cda' where id=3;
update operators set light='8161da', dark='8161da' where id=4;


ALTER TABLE `report_templates` CHANGE `template_id` `report_id` INT(11) NOT NULL;
ALTER TABLE `reports` drop column template_id;
ALTER TABLE `reports` ADD `actif` BOOLEAN NOT NULL DEFAULT TRUE AFTER `day`;
ALTER TABLE `reports` CHANGE `record` `date_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

-----------------------------------
ALTER TABLE `cases` ADD `message_name` INT NOT NULL AFTER `status_date_time`, ADD `message_count` INT NOT NULL AFTER `message_name`, ADD `message_amount` INT NOT NULL AFTER `message_count`, ADD `message_limit` INT NOT NULL AFTER `message_amount`;
ALTER TABLE `cases` CHANGE `message_amount` `message_amount` FLOAT(11) NOT NULL;
ALTER TABLE `cases` CHANGE `message_name` `message_name` VARCHAR(100) NOT NULL;

update cases set message_limit = 5;
update cases set message_name = name;
update cases set message_amount = cast(replace(substring(message, locate("$", message)+2), ' ', '') as decimal);
update cases set message_count = substring(message, locate(", sent", message)+7, 2);
update cases set message = 'LOCAL_SENDER_SENT_TOO_MANY_TRANSACTIONS';

----------------------------------------------

insert into privileges (name, description) values ('PRIV_VIEW_REPORT', 'View Report');
insert into privileges (name, description) values ('PRIV_ACTION_REPORT', 'Edit Report');
insert into role_priv (role, priv) values (1,5);
insert into role_priv (role, priv) values (1,6);
-------------------------------------------------

update transactions set operator_name = 'Express Money' where operator_name = 'Uni-Transfert';
update transactions set local_operator_id = replace(local_operator_id, "Uni-Transfert", "Express Money") where operator_name = 'Express Money';
update transaction_hourly_stats set operator = 'Express Money' where operator = 'Uni-Transfert';
update transaction_age_stats set operator = 'Express Money' where operator = 'Uni-Transfert';
update cases set operator = 'Express Money' where operator = 'Uni-Transfert';
update service_points set  operator = 'Express Money' where operator = 'Uni-Transfert';
update suspicious_transactions set  operator = 'Express Money' where operator = 'Uni-Transfert';
update service_points set  service_point_id =  replace(service_point_id, "Uni-Transfert", "Express Money") where operator = 'Express Money';


CREATE TABLE `suspicious_telephones` (
  `id` int(11) NOT NULL,
  `telephone` varchar(20) NOT NULL,
  `datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `suspicious_telephones`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `suspicious_telephones`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

CREATE TABLE `suspicious_country` (
  `id` int(11) NOT NULL,
  `country_code` varchar(2) NOT NULL,
  `datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `suspicious_country`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `suspicious_country`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

insert into suspicious_country values(1, 'BA', now());
insert into suspicious_country values(2, 'ET', now());
insert into suspicious_country values(3, 'IR', now());
insert into suspicious_country values(4, 'IQ', now());

alter table cases add column local_alert boolean default 0;

CREATE TABLE `stats_id` (
  `id` int(11) NOT NULL,
  `description` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into stats_id values (0, 'TELEPHONE');
insert into stats_id values (0, 'COUNTRY');
------------------------------------------------------
insert into stats_id values (0, 'PHONE_VS_NAME_FOREIGN');
insert into stats_id values (0, 'PHONE_VS_NAME_LOCAL');