-- sandi 62922 03.1_TechnoSMS【Willway】_BAnk Transfer Function (export)
ALTER TABLE bankexport_salesnos ADD COLUMN itakusha_cd VARCHAR(10);
ALTER TABLE bankactions_history ADD COLUMN hojin_id TINYINT(1);
ALTER TABLE bankactions_history ADD COLUMN master_bank_account_id VARCHAR(36);
ALTER TABLE bankactions_history ADD COLUMN bank_code VARCHAR(4);


-- mon (63780) manually added record on master_city table
-- please import the file under masterc_city/masterc_city_FINAL.csv once the record are not yet available on the targeted environment

-- mon (64090)
ALTER TABLE shohin DROP COLUMN apply_tajigyobu;

-- Bug 64131 (rio)  - remove tsukinaka_seikyu column
ALTER TABLE shohin_kingaku DROP COLUMN `tsukinaka_seikyu`;

-- Bug 65076 (rio) - set the deleted of the required fields to 0 [not deleted]
UPDATE config_export_fields
SET deleted = 0
WHERE module='Fee' AND suffix='LWRyoshushoHakko_AN'
AND field IN (
	'nyushitsu',    
	'gessha',   
	'kyozai',        
	'test',    
	'koushuukai',    
	'tokubetsu',
	'shidoukanren',    
	'msp');

-- albert (63940) remove saikenka_flg and saikenka_date 
ALTER TABLE sms_fee DROP COLUMN `saikenka_flg`;

ALTER TABLE sms_fee DROP COLUMN `saikenka_date`;

-- albert (64839) create waribiki audit table
CREATE TABLE `waribiki_audit` (
  `id` VARCHAR(36) NOT NULL DEFAULT '',
  `parent_id` VARCHAR(36) DEFAULT '',
  `date_created` DATETIME DEFAULT NULL,
  `created_by` VARCHAR(36) DEFAULT NULL,
  `field_name` VARCHAR(100) DEFAULT NULL,
  `data_type` VARCHAR(100) DEFAULT NULL,
  `before_value_string` VARCHAR(255) DEFAULT NULL,
  `after_value_string` VARCHAR(255) DEFAULT NULL,
  `before_value_text` TEXT,
  `after_value_text` TEXT
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- albert (64839) create shohin audit table
CREATE TABLE `shohin_audit` (
  `id` VARCHAR(36) NOT NULL DEFAULT '',
  `parent_id` VARCHAR(36) DEFAULT '',
  `date_created` DATETIME DEFAULT NULL,
  `created_by` VARCHAR(36) DEFAULT NULL,
  `field_name` VARCHAR(100) DEFAULT NULL,
  `data_type` VARCHAR(100) DEFAULT NULL,
  `before_value_string` VARCHAR(255) DEFAULT NULL,
  `after_value_string` VARCHAR(255) DEFAULT NULL,
  `before_value_text` TEXT,
  `after_value_text` TEXT
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- albert (64839) create product audit table
CREATE TABLE `product_audit` (
  `id` VARCHAR(36) NOT NULL DEFAULT '',
  `parent_id` VARCHAR(36) DEFAULT '',
  `date_created` DATETIME DEFAULT NULL,
  `created_by` VARCHAR(36) DEFAULT NULL,
  `field_name` VARCHAR(100) DEFAULT NULL,
  `data_type` VARCHAR(100) DEFAULT NULL,
  `before_value_string` VARCHAR(255) DEFAULT NULL,
  `after_value_string` VARCHAR(255) DEFAULT NULL,
  `before_value_text` TEXT,
  `after_value_text` TEXT
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- albert (65407) Initial values for custom table id
UPDATE config 
SET category='API_moushikomi_salesno', NAME='moushikomi_salesno', VALUE='050000000' 
WHERE category = 'API_moushikomi_salesno' 
AND NAME = 'moushikomi_salesno';

-- albert (65407) Initial values for waribiki custom id
UPDATE config 
SET category='waribiki', NAME='internal_waribiki_id', VALUE='00000022' 
WHERE category = 'waribiki' 
AND NAME = 'internal_waribiki_id';

-- albert (65407) Initial values for shohin custom id
UPDATE config 
SET category='shohin', NAME='shohin_id', VALUE='20000000' 
WHERE category = 'shohin' 
AND NAME = 'shohin_id';

-- albert (65407) Initial values for fee custom id
UPDATE config 
SET category='fee', NAME='fee_id', VALUE='0020000000' 
WHERE category = 'fee' 
AND NAME = 'fee_id';

-- albert (65407) Initial values for product custom id
UPDATE config 
SET category='product', NAME='internal_product_id', VALUE='10000000' 
WHERE category = 'product' 
AND NAME = 'internal_product_id';

-- albert (65407) Initial values for saiken custom id
UPDATE config 
SET category='saiken', NAME='saiken_no', VALUE='1000000000' 
WHERE category = 'saiken' 
AND NAME = 'saiken_no';

-- albert (65407) Initial values for product_set custom id
INSERT INTO config(category, NAME, VALUE) VALUES('product_set', 'product_set_id', '0000005000');