/*TRUNCATE sessions;*/
TRUNCATE `log_noncash`;
TRUNCATE `payments_log`;

/*
DELETE FROM customers WHERE customers_id < 1000000 AND customers_id > 100000;
DELETE FROM customers_info WHERE customers_info_id < 1000000 AND customers_info_id > 100000;
DELETE FROM address_book WHERE customers_id < 1000000 AND customers_id > 100000;
DELETE FROM orders WHERE orders_id < 150000;
DELETE FROM orders_total WHERE  orders_id < 150000;
DELETE FROM orders_products WHERE  orders_id < 150000;
*/

UPDATE customers_messages_settings cms
SET cms.external_email_login = '', cms.external_email_password = '';
UPDATE messages m
SET m.messages_text = 'Сообщение', m.messages_quote = 'Цитата',
  m.messages_title  = 'Тема письма', m.messages_short_text = 'Короткий текст сообщения';

UPDATE comments
SET comments_text = concat_ws(' ', 'Комментарий СБ №', comments_id, repeat('x ', length(comments_text) / 2))
WHERE component = 'supplierSecurity';
UPDATE comments
SET comments_text = concat_ws(' ', 'Комментарий БМ №', comments_id, repeat('x ', length(comments_text) / 2))
WHERE component = 'supplierBrandManager';

UPDATE suppliers_other
SET
  suppliers_other_security_resume              = concat_ws(' ', 'Резюме №', suppliers_id),
  suppliers_other_quality_certificates_comment = concat_ws(' ', 'Информация по первичному ДПК №', suppliers_id);

UPDATE `customers_info`
SET `customers_info`.customers_credits = round(rand() * 500);
/*UPDATE `orders_total` SET value = round(rand()*500) WHERE class='ot_subtotal' OR class='ot_total';*/

UPDATE customers_telephones
SET customers_telephone = '';

/*UPDATE customers set customers_email_address = concat(customers_id, customers_date_added) where customers_email_address like '%@mailforspam.com';*/
UPDATE `customers` c
SET `c`.`customers_temp_key` = MD5(customers_id),
  `c`.`customers_telephone`  = '';

UPDATE acl
SET acl_roles_id = (SELECT acl_roles_id
                    FROM acl_roles
                    WHERE acl_roles_title_en = 'admin')
WHERE acl_roles_id IN (
  (SELECT acl_roles_id
   FROM acl_roles
   WHERE acl_roles_title_en IN ('it', 'qa', 'programmer1c'))
) OR acl_roles_id IN (1833592, 73682);

UPDATE `customers` c
SET `c`.`customers_email_address` = `c`.customers_id
WHERE c.customers_status != 6;

UPDATE `orders` o
SET
  `o`.`customers_street_address` = CONCAT('ул. ',
                                          ELT(round(rand() * 5) + 1, 'Мира', 'Дружбы', 'Фёдорова', 'Ленина', 'Наумова',
                                              'Жукова'), ' дом. ', (round(rand() * 99) + 1),
                                          ELT(round(rand() * 2) + 1, ' ', CONCAT('к', round(rand() * 25) + 1),
                                              CONCAT('с', round(rand() * 25) + 1)), ' кв. ', (round(rand() * 500) + 1)),
  `o`.`customers_telephone`      = '',
  `o`.`customers_email_address`  = `o`.customers_id,
  `o`.`customers_street`         = CONCAT('ул. ',
                                          ELT(round(rand() * 5) + 1, 'Мира', 'Дружбы', 'Фёдорова', 'Ленина', 'Наумова',
                                              'Жукова'));


UPDATE `address_book` ab
SET
  `ab`.`entry_street_address` = CONCAT('ул. ',
                                       ELT(round(rand() * 5) + 1, 'Мира', 'Дружбы', 'Фёдорова', 'Ленина', 'Наумова',
                                           'Жукова'), ' дом. ', (round(rand() * 99) + 1),
                                       ELT(round(rand() * 2) + 1, ' ', CONCAT('к', round(rand() * 25) + 1),
                                           CONCAT('с', round(rand() * 25) + 1)), ' кв. ', (round(rand() * 500) + 1));

/*ALTER TABLE `products` CHANGE `products_ean` `products_1c_id` VARCHAR( 11 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;*/
/*ALTER TABLE `products_attributes` ADD `products_attributes_1c_id` VARCHAR( 11 ) NOT NULL ;*/
/*ALTER TABLE `suppliers` ADD `suppliers_1c_id` VARCHAR( 9 ) NOT NULL AFTER `suppliers_id`; */


UPDATE `suppliers` c
SET
  `c`.`juristic_name`  = CONCAT('ООО ',
                                ELT(round(rand() * 5) + 1, 'Мир', 'Дружба', 'Фёдоров', 'Ленин', 'Наумов', 'Жуков')),
  `c`.juristic_name_1c = `c`.`juristic_name`,
  `c`.`contact_faces`  = CONCAT('товарищ ',
                                ELT(round(rand() * 5) + 1, 'Лена', 'Вадим', 'Женя', 'Ленин', 'Наумов', 'Жуков')),
  `c`.`url`            = '',
  `c`.`INN`            = '',
  `c`.`address`        = CONCAT('ул. ',
                                ELT(round(rand() * 5) + 1, 'Мира', 'Дружбы', 'Фёдорова', 'Ленина', 'Наумова', 'Жукова'),
                                ' дом. ', (round(rand() * 99) + 1),
                                ELT(round(rand() * 2) + 1, ' ', CONCAT('к', round(rand() * 25) + 1),
                                    CONCAT('с', round(rand() * 25) + 1)), ' кв. ', (round(rand() * 500) + 1)),
  `c`.`warehouse`      = CONCAT('ул. ',
                                ELT(round(rand() * 5) + 1, 'Мира', 'Дружбы', 'Фёдорова', 'Ленина', 'Наумова', 'Жукова'),
                                ' дом. ', (round(rand() * 99) + 1),
                                ELT(round(rand() * 2) + 1, ' ', CONCAT('к', round(rand() * 25) + 1),
                                    CONCAT('с', round(rand() * 25) + 1)), ' кв. ', (round(rand() * 500) + 1)),
  `c`.`phones`         = '',
  `c`.`comment`        = '';

UPDATE log_logins
SET last_request_uri = '';

UPDATE pickup_refunds_data
SET
  `pickup_refunds_customers_name`          = CONCAT_WS(' ',
                                                       ELT(ROUND(RAND() * 5) + 1, 'Наталья', 'Светлана', 'Мария',
                                                           'Ирина', 'Ольга', 'Татьяна'),
                                                       ELT(ROUND(RAND() * 5) + 1, 'Иванова', 'Попова', 'Лебедева',
                                                           'Степанова', 'Зайцева', 'Гусева'),
                                                       ELT(ROUND(RAND() * 5) + 1, 'Сергеевна', 'Николаевна', 'Ивановна',
                                                           'Петровна', 'Васильевна', 'Викторовна')
  ),
  pickup_refunds_customers_passport_number = CONCAT_WS(
      ' ',
      ELT(ROUND(RAND() * 6) + 1, 'MRD', 'ACDC', 'В/У', 'NHL', 'PHP', 'HZ',
          LPAD(FLOOR(RAND() * (9999 - 1 + 1)) + 1, 4, '0')),
      LPAD(FLOOR(RAND() * (99999999 - 1 + 1)) + 1, FLOOR(RAND() * (10 - 8 + 1)) + 8, '0')
  );
UPDATE pickup_refunds_data
SET
  `pickup_refunds_card_name_user`  = CONCAT_WS(' ',
                                               ELT(ROUND(RAND() * 5) + 1, 'Наталья', 'Светлана', 'Мария', 'Ирина',
                                                   'Ольга', 'Татьяна'),
                                               ELT(ROUND(RAND() * 5) + 1, 'Иванова', 'Попова', 'Лебедева', 'Степанова',
                                                   'Зайцева', 'Гусева'),
                                               ELT(ROUND(RAND() * 5) + 1, 'Сергеевна', 'Николаевна', 'Ивановна',
                                                   'Петровна', 'Васильевна', 'Викторовна')
  ),
  pickup_refunds_card_account_user = LPAD(FLOOR(RAND() * (99999999999999999999 - 1 + 1)) + 1, 20, '0')
WHERE pickup_refunds_card_name_user != '';

UPDATE pickup_refunds_data
SET
  pickup_refunds_card_credit_card_number = LPAD(FLOOR(RAND() * (999999999999999999 - 1 + 1)) + 1,
                                                FLOOR(RAND() * (18 - 14 + 1)) + 14, '0')
WHERE pickup_refunds_card_credit_card_number != '';

UPDATE orders_passports AS op
SET op.orders_passport_series   = 1234,
  op.orders_passport_number     = 567890,
  op.orders_passport_issue      = 'Тестовый город, Тестовая область',
  op.orders_passport_issue_date = '0000-00-00';

UPDATE customers_passports AS cp
SET cp.customers_passport_series   = 1234,
  cp.customers_passport_number     = 567890,
  cp.customers_passport_issue      = 'Тестовый город, Тестовая область',
  cp.customers_passport_issue_date = '0000-00-00';

UPDATE pickup_refunds_data
SET
pickup_refunds_customers_name = ''
, pickup_refunds_customers_passport_number = ''
, pickup_refunds_customers_passport_issue = ''
, pickup_refunds_customers_passport_issue_date = '0000-00-00 00:00:00'
, pickup_refunds_card_name_user = ''
, pickup_refunds_card_name_bank = ''
, pickup_refunds_card_account_user = ''
, pickup_refunds_card_cor_account_bank = ''
, pickup_refunds_card_bik_bank = ''
, pickup_refunds_card_credit_card_number = '';


GRANT ALL PRIVILEGES ON alpha.* TO 'gb_lopera'@'%'
WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON bender.* TO 'gb_lopera'@'%'
WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON delta.* TO 'gb_lopera'@'%'
WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON sparta.* TO 'gb_lopera'@'%'
WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON logdb.* TO 'gb_lopera'@'%'
WITH GRANT OPTION;