-- Mon; 69002 adding manual_transaction table
CREATE TABLE IF NOT EXISTS `manual_transaction` (
	`id`	 VARCHAR(36) NOT NULL,
    `date_entered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
	`date_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
	`modified_user_id` CHAR(36) NOT NULL,
	`created_by` CHAR(36) NOT NULL,
	`billingno` CHAR(60),
	`contact_name` CHAR(36) NOT NULL,
	`t_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
	`actual_in_amount` DOUBLE,
	`payment_type` VARCHAR(36) NOT NULL,
	`status` TINYINT NOT NULL,
	`raw_import_data` DOUBLE,
	`import_serial_no` int(11) NOT NULL AUTO_INCREMENT,
	`school_id` VARCHAR(36),
	`nyuukinsaki_koza` TINYINT NOT NULL,
	`bank_name`  VARCHAR(36),
	`bank_branch_name`  VARCHAR(36),
	`summary` VARCHAR(36) NOT NULL,
	`ev1_id` VARCHAR(30),
	
	PRIMARY KEY (`import_serial_no`),
	UNIQUE KEY `unique_id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- Mon; adding new column on bank_info table (ref: 69002)
ALTER TABLE bank_info ADD COLUMN ev1_id VARCHAR(30);


-- Jewel 69298 - 【TKG】 Table Definition_20180222
ALTER TABLE student_jigyo DROP COLUMN iseki_date;
ALTER TABLE sms_fee DROP COLUMN sales_school_id;
ALTER TABLE sms_bill DROP COLUMN bill_name;
ALTER TABLE shohin_master DROP COLUMN kanjyokamoku;
ALTER TABLE shohin_master DROP COLUMN nebikikanjyokamoku;


-- Ivan 69443 - 【TKG】 Table Definition_20180302
ALTER TABLE kihon_jukojoho DROP COLUMN formid;

-- ivan 69651 (march 19 2018)
-- Remove 
ALTER TABLE sms_bill DROP COLUMN SeitoJigyo__c;
ALTER TABLE sms_bill DROP COLUMN SeikyuJokyo__c;
ALTER TABLE sms_bill DROP COLUMN SeikyuZandaka__c;
ALTER TABLE sms_bill DROP COLUMN UniqueID__c;




ALTER TABLE contacts DROP COLUMN shinkenzemiID;
ALTER TABLE contacts DROP COLUMN personal_info_use;

ALTER TABLE toutatsu_info DROP COLUMN service_brand;
ALTER TABLE toutatsu_info DROP COLUMN MembershipDev;
ALTER TABLE toutatsu_info DROP COLUMN BGroupNo;
ALTER TABLE toutatsu_info DROP COLUMN PersonalInfoUse;
ALTER TABLE toutatsu_info DROP COLUMN CurrentState;
ALTER TABLE toutatsu_info DROP COLUMN EmailMobile;
ALTER TABLE toutatsu_info DROP COLUMN BuildingName;
ALTER TABLE toutatsu_info DROP COLUMN BillingToID;
ALTER TABLE toutatsu_info DROP COLUMN ParentID;
ALTER TABLE toutatsu_info DROP COLUMN ServiceInfo;
ALTER TABLE toutatsu_info DROP COLUMN Exemption;

ALTER TABLE sms_fee DROP COLUMN resource_id;
ALTER TABLE sms_fee DROP COLUMN old_Pattern__c;
ALTER TABLE sms_fee DROP COLUMN UniqueID__c;
ALTER TABLE sms_fee DROP COLUMN MeisaiBango__c;
ALTER TABLE sms_fee DROP COLUMN TorihikiEda__c;
ALTER TABLE sms_fee DROP COLUMN TorihikiBango__c;

ALTER TABLE sms_transaction DROP COLUMN fee_category;
ALTER TABLE sms_transaction DROP COLUMN kanjyokamoku_id;
ALTER TABLE sms_transaction DROP COLUMN KeshikomuHouhou__c;

ALTER TABLE yutaijoho DROP COLUMN taikaimae_tekiyo_shuryobi;
ALTER TABLE yutaijoho DROP COLUMN taikaimae_tekiyo_shuryobi;


ALTER TABLE genmenjoho DROP COLUMN TaikaiMaeTekiyoShuryobi__c;
ALTER TABLE genmenjoho DROP COLUMN NC_CO__c;


-- Ivan 69509
ALTER TABLE yutaijoken_master DROP COLUMN jigyo_master_id;

-- Jewel 69530
ALTER TABLE sms_transaction DROP COLUMN KarikataKanjoKamoku__c;
ALTER TABLE sms_transaction DROP COLUMN KashikataKanjoKamoku__c;
ALTER TABLE sms_transaction DROP COLUMN sms_bill_id;


-- Ivan 69443
ALTER TABLE sms_fee DROP COLUMN old_Pattern__c;
ALTER TABLE yutaijoho DROP COLUMN moushkomi_no;
ALTER TABLE genmenjoho DROP COLUMN TaikaiMaeTekiyoShuryobi__c;
ALTER TABLE genmenjoho DROP COLUMN NC_CO__c;

-- Jed 69633
DROP TABLE syokainonyu_info;

-- Ivan 69881 (april-09-2018)
ALTER TABLE student_jigyo DROP COLUMN koteiryokin_id; 


-- Jewel 69965
ALTER TABLE genmenjoho DROP COLUMN contacts_id;

-- Ivan 70006
ALTER TABLE bank_info ADD COLUMN ev1_id VARCHAR(30);

-- Mon 70018
ALTER TABLE bank_info ADD COLUMN `date_entered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00';
ALTER TABLE bank_info ADD COLUMN `created_by` CHAR(36) NOT NULL;

ALTER TABLE bank_info DROP COLUMN bank_name;
ALTER TABLE bank_info DROP COLUMN bank_kana;


-- Jewel 67841
DROP TABLE  siensebunsho_master;

-- Ivan 70217 
INSERT INTO resource (`id`,`school_id`, `name`,`date_entered`,`date_modified`)  VALUES ('9900','9900','ダミー教室(入金用)(移行用)','2017-11-14 11:33:05','2017-11-14 11:33:05');

-- ivan 70700
ALTER TABLE contacts_audit ADD INDEX parent_id (parent_id);
ALTER TABLE contacts MODIFY COLUMN prev_grade VARCHAR(20) AFTER grade;
ALTER TABLE contacts MODIFY COLUMN prev_gakko_kubun VARCHAR(5) AFTER gakko_kubun;
ALTER TABLE contacts MODIFY COLUMN prev_gakunen_kubun VARCHAR(5) AFTER gakunen_kubun;
ALTER TABLE general_report DROP COLUMN ev1_id;
ALTER TABLE general_report_conditions DROP COLUMN ev1_id;
ALTER TABLE general_report_exportfields DROP COLUMN ev1_id;

-- Ivan 70949
ALTER TABLE sms_fee DROP COLUMN SeikyuShubetsu__c;

-- Roy 71172
ALTER TABLE contacts DROP COLUMN last_grade_entrance_date;
ALTER TABLE genmenjoho DROP COLUMN Name;
ALTER TABLE genmenjoho DROP COLUMN ShoninJotai__c;
ALTER TABLE genmenjoho DROP COLUMN RecordType;
ALTER TABLE genmenjoho DROP COLUMN MoshikomiNo__c;

-- Roy 71271
-- changed position of view_kubun, placed it after kubun
ALTER TABLE general_report MODIFY COLUMN view_kubun VARCHAR(50) NOT NULL AFTER kubun;

-- Roy 71230
ALTER TABLE combiniactions_history MODIFY COLUMN combini_sakuseibi DATE AFTER school_id;
ALTER TABLE combiniactions_history MODIFY COLUMN ev1_id VARCHAR(30) AFTER import_filename;