-- Bug 68672 (Roy)
ALTER TABLE sms_recurrence MODIFY COLUMN `modified_user_id` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;
ALTER TABLE sms_recurrence MODIFY COLUMN `created_by` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;


-- Jewel 68672
-- contacts_sub1 
ALTER TABLE contacts_sub1 DROP COLUMN zei_kubun;
ALTER TABLE contacts_sub1 DROP COLUMN t_bank_payment_type;
ALTER TABLE contacts_sub1 DROP COLUMN t_bank_code;
ALTER TABLE contacts_sub1 DROP COLUMN t_bank_name;
ALTER TABLE contacts_sub1 DROP COLUMN t_bank_branch_code;
ALTER TABLE contacts_sub1 DROP COLUMN t_bank_branch_name;
ALTER TABLE contacts_sub1 DROP COLUMN t_bank_account_type;
ALTER TABLE contacts_sub1 DROP COLUMN t_bank_account_no;
ALTER TABLE contacts_sub1 DROP COLUMN t_bank_account_name;
ALTER TABLE contacts_sub1 DROP COLUMN contact_id;

-- accounts
ALTER TABLE accounts DROP COLUMN account_type;
ALTER TABLE accounts DROP COLUMN annual_revenue;
ALTER TABLE accounts DROP COLUMN assigned_user_id;
ALTER TABLE accounts DROP COLUMN billing_address_city;
ALTER TABLE accounts DROP COLUMN billing_address_country;
ALTER TABLE accounts DROP COLUMN billing_address_kana;
ALTER TABLE accounts DROP COLUMN billing_address_postalcode;
ALTER TABLE accounts DROP COLUMN billing_address_state;
ALTER TABLE accounts DROP COLUMN billing_address_street;
ALTER TABLE accounts DROP COLUMN email1;
ALTER TABLE accounts DROP COLUMN email2;
ALTER TABLE accounts DROP COLUMN employees;
ALTER TABLE accounts DROP COLUMN houjin_tantosha;
ALTER TABLE accounts DROP COLUMN idnumber;
ALTER TABLE accounts DROP COLUMN industry;
ALTER TABLE accounts DROP COLUMN name_furigana;
ALTER TABLE accounts DROP COLUMN ownership;
ALTER TABLE accounts DROP COLUMN parent_id;
ALTER TABLE accounts DROP COLUMN phone_alternate;
ALTER TABLE accounts DROP COLUMN phone_fax;
ALTER TABLE accounts DROP COLUMN phone_office;
ALTER TABLE accounts DROP COLUMN rating;
ALTER TABLE accounts DROP COLUMN shipping_address_city;
ALTER TABLE accounts DROP COLUMN shipping_address_country;
ALTER TABLE accounts DROP COLUMN shipping_address_kana;
ALTER TABLE accounts DROP COLUMN shipping_address_postalcode;
ALTER TABLE accounts DROP COLUMN shipping_address_state;
ALTER TABLE accounts DROP COLUMN shipping_address_street;
ALTER TABLE accounts DROP COLUMN shozoku;
ALTER TABLE accounts DROP COLUMN sic_code;
ALTER TABLE accounts DROP COLUMN t_bank_account_name;
ALTER TABLE accounts DROP COLUMN t_payment_type;
ALTER TABLE accounts DROP COLUMN ticker_symbol;
ALTER TABLE accounts DROP COLUMN website;

-- school
ALTER TABLE resource DROP COLUMN sales_tax_kubun;

-- resource_sub1
ALTER TABLE resource_sub1 DROP COLUMN tel_c;
ALTER TABLE resource_sub1 DROP COLUMN address_c;
ALTER TABLE resource_sub1 DROP COLUMN rules_c;
ALTER TABLE resource_sub1 DROP COLUMN fax_c;
ALTER TABLE resource_sub1 DROP COLUMN id_label_c;
ALTER TABLE resource_sub1 DROP COLUMN last_student_id_c;
ALTER TABLE resource_sub1 DROP COLUMN last_invoice_id_c;
ALTER TABLE resource_sub1 DROP COLUMN last_teacher_id_c;
ALTER TABLE resource_sub1 DROP COLUMN rules_teacher_c;
ALTER TABLE resource_sub1 DROP COLUMN rules_staff_c;
ALTER TABLE resource_sub1 DROP COLUMN bank_account_c;
ALTER TABLE resource_sub1 DROP COLUMN bank_account_receipt_c;
ALTER TABLE resource_sub1 DROP COLUMN tantosha_c;



-- smsevent
ALTER TABLE smsevent DROP COLUMN parent_id;
ALTER TABLE smsevent DROP COLUMN syllabus_id;
ALTER TABLE smsevent DROP COLUMN smsdocument_id;
ALTER TABLE smsevent DROP COLUMN vacation_type;
ALTER TABLE smsevent DROP COLUMN vacation_count;
ALTER TABLE smsevent DROP COLUMN event_credit;
ALTER TABLE smsevent DROP COLUMN schedule_reminder;
ALTER TABLE smsevent DROP COLUMN common_code;
ALTER TABLE smsevent DROP COLUMN assigned_user_id;
ALTER TABLE smsevent DROP COLUMN nen;
ALTER TABLE smsevent DROP COLUMN ki;
ALTER TABLE smsevent DROP COLUMN bumon_code;
ALTER TABLE smsevent DROP COLUMN koza_kubun;
ALTER TABLE smsevent DROP COLUMN name_furigana;
ALTER TABLE smsevent DROP COLUMN customer_chui;
ALTER TABLE smsevent DROP COLUMN kaisaishu_hyoji_web;
ALTER TABLE smsevent DROP COLUMN kaisaijikan_hyoji_web;
ALTER TABLE smsevent DROP COLUMN kaisaibi_hyoji_web;
ALTER TABLE smsevent DROP COLUMN kaisaishu;
ALTER TABLE smsevent DROP COLUMN show_web;
ALTER TABLE smsevent DROP COLUMN uketsuke_web;
ALTER TABLE smsevent DROP COLUMN name_web;
ALTER TABLE smsevent DROP COLUMN name_sub_web;
ALTER TABLE smsevent DROP COLUMN web_start_day;
ALTER TABLE smsevent DROP COLUMN web_end_day;
ALTER TABLE smsevent DROP COLUMN web_start_day_shinki;
ALTER TABLE smsevent DROP COLUMN web_start_day_shinki_timepicker;
ALTER TABLE smsevent DROP COLUMN web_end_day_shinki;
ALTER TABLE smsevent DROP COLUMN web_end_day_shinki_timepicker;
ALTER TABLE smsevent DROP COLUMN description_web;
ALTER TABLE smsevent DROP COLUMN shashin_setsumei1;
ALTER TABLE smsevent DROP COLUMN teiin_min;
ALTER TABLE smsevent DROP COLUMN jukouryo;
ALTER TABLE smsevent DROP COLUMN kaiin_tanka_zeikomi;
ALTER TABLE smsevent DROP COLUMN hikaiin_tanka_zeikomi;
ALTER TABLE smsevent DROP COLUMN houjin_tanka_zeikomi;
ALTER TABLE smsevent DROP COLUMN kaiin_tanka_web;
ALTER TABLE smsevent DROP COLUMN hikaiin_tanka_web;
ALTER TABLE smsevent DROP COLUMN hojin_tanka_web;
ALTER TABLE smsevent DROP COLUMN kyouzai;
ALTER TABLE smsevent DROP COLUMN kyouzai_tanka_zeikomi;
ALTER TABLE smsevent DROP COLUMN kyouzai_tanka_web;
ALTER TABLE smsevent DROP COLUMN sonota_tanka_1;
ALTER TABLE smsevent DROP COLUMN sonota_subcategory_1;
ALTER TABLE smsevent DROP COLUMN sonota_tanka_zeikomi_1;
ALTER TABLE smsevent DROP COLUMN sonota_tanka_web_1;
ALTER TABLE smsevent DROP COLUMN sonota_tanka_2;
ALTER TABLE smsevent DROP COLUMN sonota_subcategory_2;
ALTER TABLE smsevent DROP COLUMN sonota_tanka_zeikomi_2;
ALTER TABLE smsevent DROP COLUMN sonota_tanka_web_2;
ALTER TABLE smsevent DROP COLUMN sonota_tanka_3;
ALTER TABLE smsevent DROP COLUMN sonota_subcategory_3;
ALTER TABLE smsevent DROP COLUMN sonota_tanka_zeikomi_3;
ALTER TABLE smsevent DROP COLUMN sonota_tanka_web_3;
ALTER TABLE smsevent DROP COLUMN picture_filename;


-- event_contacts
ALTER TABLE event_contacts DROP COLUMN event_credit;
ALTER TABLE event_contacts DROP COLUMN ct_keiyaku_keitai;
ALTER TABLE event_contacts DROP COLUMN ct_kotsuhi;
ALTER TABLE event_contacts DROP COLUMN ct_ritsu;
ALTER TABLE event_contacts DROP COLUMN ct_pamp_hyoujijun;


-- sms_salesgroup

ALTER TABLE sms_salesgroup DROP COLUMN created_by;
ALTER TABLE sms_salesgroup DROP COLUMN basic_fee;
ALTER TABLE sms_salesgroup DROP COLUMN unit_type;
ALTER TABLE sms_salesgroup DROP COLUMN unit_no;
ALTER TABLE sms_salesgroup DROP COLUMN period;
ALTER TABLE sms_salesgroup DROP COLUMN period_no;
ALTER TABLE sms_salesgroup DROP COLUMN due_to;
ALTER TABLE sms_salesgroup DROP COLUMN applied_contact;
ALTER TABLE sms_salesgroup DROP COLUMN discount_percentage;
ALTER TABLE sms_salesgroup DROP COLUMN discount_price;

-- sms_fee
ALTER TABLE sms_fee DROP COLUMN product_id;
ALTER TABLE sms_fee DROP COLUMN product_count;
ALTER TABLE sms_fee DROP COLUMN ticket_count;
ALTER TABLE sms_fee DROP COLUMN ticket_valid;
ALTER TABLE sms_fee DROP COLUMN source_gessha_id;
ALTER TABLE sms_fee DROP COLUMN nyukin_zumi;
ALTER TABLE sms_fee DROP COLUMN fee_payment_type;
ALTER TABLE sms_fee DROP COLUMN total_paid;
ALTER TABLE sms_fee DROP COLUMN kaiin_kubun;
ALTER TABLE sms_fee DROP COLUMN fee_balance;


-- sms_transaction
ALTER TABLE sms_transaction DROP COLUMN document_id;
ALTER TABLE sms_transaction DROP COLUMN combini_sakusei_id;
ALTER TABLE sms_transaction DROP COLUMN furikomibi;
ALTER TABLE sms_transaction DROP COLUMN kakuhobi;



-- rearrange columns

-- smsevent
ALTER TABLE smsevent CHANGE COLUMN deleted deleted tinyint AFTER id;
ALTER TABLE smsevent CHANGE COLUMN date_entered date_entered datetime AFTER deleted;
ALTER TABLE smsevent CHANGE COLUMN date_modified date_modified datetime AFTER date_entered;
ALTER TABLE smsevent CHANGE COLUMN modified_user_id modified_user_id char(36) AFTER date_modified;
ALTER TABLE smsevent CHANGE COLUMN created_by created_by char(36) AFTER modified_user_id;
ALTER TABLE smsevent CHANGE COLUMN account_id account_id char(36) AFTER created_by;
ALTER TABLE smsevent CHANGE COLUMN area_id area_id char(36) AFTER account_id;
ALTER TABLE smsevent CHANGE COLUMN school_id school_id char(36) AFTER area_id;
ALTER TABLE smsevent CHANGE COLUMN curriculum_id curriculum_id char(36) AFTER school_id;
ALTER TABLE smsevent CHANGE COLUMN curriculum_area_id curriculum_area_id char(36) AFTER curriculum_id;
ALTER TABLE smsevent CHANGE COLUMN curriculum_area_settei_id curriculum_area_settei_id char(36) AFTER curriculum_area_id;
ALTER TABLE smsevent CHANGE COLUMN kaijo_settei_id kaijo_settei_id char(36) AFTER curriculum_area_settei_id;
ALTER TABLE smsevent CHANGE COLUMN sakusei_jokyo sakusei_jokyo int AFTER kaijo_settei_id;
ALTER TABLE smsevent CHANGE COLUMN code code varchar(30) AFTER sakusei_jokyo;
ALTER TABLE smsevent CHANGE COLUMN name name varchar(100) AFTER code;
ALTER TABLE smsevent CHANGE COLUMN youbi youbi varchar(10) AFTER name;
ALTER TABLE smsevent CHANGE COLUMN start_timepicker start_timepicker char(5) AFTER youbi;
ALTER TABLE smsevent CHANGE COLUMN end_timepicker end_timepicker char(5) AFTER start_timepicker;
ALTER TABLE smsevent CHANGE COLUMN lesson_time lesson_time varchar(5) AFTER end_timepicker;
ALTER TABLE smsevent CHANGE COLUMN start_month start_month varchar(6) AFTER lesson_time;
ALTER TABLE smsevent CHANGE COLUMN end_month end_month varchar(6) AFTER start_month;
ALTER TABLE smsevent CHANGE COLUMN description description text AFTER end_month;
ALTER TABLE smsevent CHANGE COLUMN teacher_id teacher_id varchar(36) AFTER description;
ALTER TABLE smsevent CHANGE COLUMN category category varchar(15) AFTER teacher_id;
ALTER TABLE smsevent CHANGE COLUMN shortname shortname varchar(12) AFTER category;
ALTER TABLE smsevent CHANGE COLUMN subtype subtype varchar(255) AFTER shortname;
ALTER TABLE smsevent CHANGE COLUMN status status tinyint AFTER subtype;
ALTER TABLE smsevent CHANGE COLUMN teiin teiin int AFTER status;
ALTER TABLE smsevent CHANGE COLUMN touroku_no touroku_no int AFTER teiin;
ALTER TABLE smsevent CHANGE COLUMN yoyaku_no yoyaku_no int AFTER touroku_no;
ALTER TABLE smsevent CHANGE COLUMN taiken_no taiken_no int AFTER yoyaku_no;
ALTER TABLE smsevent CHANGE COLUMN kengaku_no kengaku_no int AFTER taiken_no;
ALTER TABLE smsevent CHANGE COLUMN cancel_machi_no cancel_machi_no int AFTER kengaku_no;


-- event_contacts
ALTER TABLE event_contacts CHANGE COLUMN deleted deleted TINYINT AFTER id;
ALTER TABLE event_contacts CHANGE COLUMN date_entered date_entered DATETIME AFTER deleted;
ALTER TABLE event_contacts CHANGE COLUMN date_modified date_modified DATETIME AFTER date_entered;
ALTER TABLE event_contacts CHANGE COLUMN modified_user_id modified_user_id CHAR(36) AFTER date_modified;
ALTER TABLE event_contacts CHANGE COLUMN created_by created_by CHAR(36) AFTER modified_user_id;
ALTER TABLE event_contacts CHANGE COLUMN contact_id contact_id CHAR(36) AFTER created_by;
ALTER TABLE event_contacts CHANGE COLUMN event_id event_id CHAR(36) AFTER contact_id;
ALTER TABLE event_contacts CHANGE COLUMN start_date start_date DATETIME AFTER event_id;
ALTER TABLE event_contacts CHANGE COLUMN end_date end_date DATETIME AFTER start_date;
ALTER TABLE event_contacts CHANGE COLUMN event_contact_status event_contact_status VARCHAR(4) AFTER end_date;
ALTER TABLE event_contacts CHANGE COLUMN description description TEXT AFTER event_contact_status;
ALTER TABLE event_contacts CHANGE COLUMN fee_start_month fee_start_month VARCHAR(6) AFTER description;
ALTER TABLE event_contacts CHANGE COLUMN fee_end_month fee_end_month VARCHAR(6) AFTER fee_start_month;
ALTER TABLE event_contacts CHANGE COLUMN zaiseki_flg_lastmonth zaiseki_flg_lastmonth TINYINT AFTER fee_end_month;
ALTER TABLE event_contacts CHANGE COLUMN zaiseki_flg_thismonth zaiseki_flg_thismonth TINYINT AFTER zaiseki_flg_lastmonth;
ALTER TABLE event_contacts CHANGE COLUMN zaiseki_flg_nextmonth zaiseki_flg_nextmonth TINYINT AFTER zaiseki_flg_thismonth;
ALTER TABLE event_contacts CHANGE COLUMN seikyu_flg_lastmonth seikyu_flg_lastmonth TINYINT AFTER zaiseki_flg_nextmonth;
ALTER TABLE event_contacts CHANGE COLUMN seikyu_flg_thismonth seikyu_flg_thismonth TINYINT AFTER seikyu_flg_lastmonth;
ALTER TABLE event_contacts CHANGE COLUMN seikyu_flg_nextmonth seikyu_flg_nextmonth TINYINT AFTER seikyu_flg_thismonth;
ALTER TABLE event_contacts CHANGE COLUMN nyuukaikin_menjo nyuukaikin_menjo TINYINT AFTER seikyu_flg_nextmonth;
ALTER TABLE event_contacts CHANGE COLUMN nyuukaikin nyuukaikin INT AFTER nyuukaikin_menjo;
ALTER TABLE event_contacts CHANGE COLUMN lesson_waribiki lesson_waribiki INT AFTER nyuukaikin;
ALTER TABLE event_contacts CHANGE COLUMN lesson_waribiki_fee lesson_waribiki_fee INT AFTER lesson_waribiki;
ALTER TABLE event_contacts CHANGE COLUMN lesson_kaitei_tsuki lesson_kaitei_tsuki VARCHAR(6) AFTER lesson_waribiki_fee;
ALTER TABLE event_contacts CHANGE COLUMN lesson_kaitei_waribiki lesson_kaitei_waribiki INT AFTER lesson_kaitei_tsuki;
ALTER TABLE event_contacts CHANGE COLUMN lesson_waribiki_kaitei_fee lesson_waribiki_kaitei_fee INT AFTER lesson_kaitei_waribiki;
ALTER TABLE event_contacts CHANGE COLUMN kasangaku_menjo kasangaku_menjo TINYINT AFTER lesson_waribiki_kaitei_fee;
ALTER TABLE event_contacts CHANGE COLUMN kasangaku_kaitei_menjo kasangaku_kaitei_menjo TINYINT AFTER kasangaku_menjo;
ALTER TABLE event_contacts CHANGE COLUMN kasangaku_kaitei_tsuki kasangaku_kaitei_tsuki VARCHAR(6) AFTER kasangaku_kaitei_menjo;
ALTER TABLE event_contacts CHANGE COLUMN menjo_reason menjo_reason VARCHAR(200) AFTER kasangaku_kaitei_tsuki;
ALTER TABLE event_contacts CHANGE COLUMN nyukai_status nyukai_status TINYINT AFTER menjo_reason;
ALTER TABLE event_contacts CHANGE COLUMN taikai_status taikai_status TINYINT AFTER nyukai_status;
ALTER TABLE event_contacts CHANGE COLUMN keiyakubi keiyakubi DATE AFTER taikai_status;
ALTER TABLE event_contacts CHANGE COLUMN chouketsu_start_month chouketsu_start_month VARCHAR(6) AFTER keiyakubi;
ALTER TABLE event_contacts CHANGE COLUMN chouketsu_end_month chouketsu_end_month VARCHAR(6) AFTER chouketsu_start_month;
ALTER TABLE event_contacts CHANGE COLUMN kakutei_status kakutei_status TINYINT AFTER chouketsu_end_month;
ALTER TABLE event_contacts CHANGE COLUMN chouketsu_reason chouketsu_reason VARCHAR(36) AFTER kakutei_status;
ALTER TABLE event_contacts CHANGE COLUMN from_event_id from_event_id CHAR(36) AFTER chouketsu_reason;
ALTER TABLE event_contacts CHANGE COLUMN to_event_id1 to_event_id1 CHAR(36) AFTER from_event_id;
ALTER TABLE event_contacts CHANGE COLUMN to_event_id2 to_event_id2 CHAR(36) AFTER to_event_id1;

-- sms_salesgroup

ALTER TABLE sms_salesgroup CHANGE COLUMN NAME NAME VARCHAR(64) AFTER id;
ALTER TABLE sms_salesgroup CHANGE COLUMN event_id event_id CHAR(36) AFTER NAME;
ALTER TABLE sms_salesgroup CHANGE COLUMN contact_id contact_id CHAR(36) AFTER event_id;
ALTER TABLE sms_salesgroup CHANGE COLUMN deleted deleted TINYINT AFTER contact_id;
ALTER TABLE sms_salesgroup CHANGE COLUMN date_entered date_entered DATETIME AFTER deleted;
ALTER TABLE sms_salesgroup CHANGE COLUMN date_modified date_modified DATETIME AFTER date_entered;
ALTER TABLE sms_salesgroup CHANGE COLUMN sell_date sell_date DATETIME AFTER date_modified;
ALTER TABLE sms_salesgroup CHANGE COLUMN modified_user_id modified_user_id CHAR(36) AFTER sell_date;
ALTER TABLE sms_salesgroup CHANGE COLUMN description description TEXT AFTER modified_user_id;
ALTER TABLE sms_salesgroup CHANGE COLUMN salesno salesno VARCHAR(36) AFTER description;
ALTER TABLE sms_salesgroup CHANGE COLUMN bank_account bank_account TEXT AFTER salesno;
ALTER TABLE sms_salesgroup CHANGE COLUMN hakkoubi hakkoubi DATE AFTER bank_account;
ALTER TABLE sms_salesgroup CHANGE COLUMN receipt_hakkoubi receipt_hakkoubi DATE AFTER hakkoubi;
ALTER TABLE sms_salesgroup CHANGE COLUMN seikyusaki seikyusaki TEXT AFTER receipt_hakkoubi;
ALTER TABLE sms_salesgroup CHANGE COLUMN tantosha tantosha VARCHAR(32) AFTER seikyusaki;
ALTER TABLE sms_salesgroup CHANGE COLUMN item item VARCHAR(255) AFTER tantosha;
ALTER TABLE sms_salesgroup CHANGE COLUMN school_code school_code VARCHAR(36) AFTER item;

-- drop columns on sms_salesgroup
ALTER TABLE sms_salesgroup DROP COLUMN account_id;
ALTER TABLE sms_salesgroup DROP COLUMN type_id;
ALTER TABLE sms_salesgroup DROP COLUMN occurences;
ALTER TABLE sms_salesgroup DROP COLUMN occurences_hour;
-- end Jewel 68672

-- jewel 68736 -- should be matched with the casing on vardef
ALTER TABLE `sms_salesgroup` CHANGE COLUMN `NAME` `name` VARCHAR(64) NULL DEFAULT NULL ;
ALTER TABLE sms_salesgroup CHANGE COLUMN name name VARCHAR(64) AFTER id;
-- end 68736


-- 2018-02-01 shimoju 
-- yamaha_sampledb_20180201 created, if we use that DB, no need to aplay the query above

-- Roy (69054) 
-- Spec 05-02
-- `curriculum_area_settei_audit` table
-- 69054 UPDATE: changed the ENGINE to InnoDB (Roy)
DROP TABLE IF EXISTS `curriculum_area_settei_audit`;
CREATE TABLE IF NOT EXISTS `curriculum_area_settei_audit` (
  `id` varchar(36) NOT NULL default '',
  `parent_id` varchar(36) NOT NULL 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
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


--jewel 68672 -- Yamaha_TableDef_20180214
ALTER TABLE sms_fee DROP COLUMN area_id;

-- 2018-02-12 Adolf
-- create kaijo_settei_audit table
CREATE TABLE `kaijo_settei_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;

-- ivan 69169 
-- database alterations for tables : area and bank_info
ALTER TABLE area DROP COLUMN last_student_id_c;
ALTER TABLE area DROP COLUMN last_teacher_id_c;

ALTER TABLE bank_info ADD COLUMN id CHAR(36) NOT NULL;
ALTER TABLE bank_info ADD COLUMN deleted TINYINT NOT NULL;
ALTER TABLE bank_info ADD COLUMN date_modified DATETIME NOT NULL;
ALTER TABLE bank_info ADD COLUMN modified_user_id  CHAR(36) NOT NULL;
ALTER TABLE bank_info MODIFY COLUMN bank_code VARCHAR(10) NOT NULL;
ALTER TABLE bank_info MODIFY COLUMN branch_code VARCHAR(10) NOT NULL;
ALTER TABLE bank_info MODIFY COLUMN name VARCHAR(100) NOT NULL;
ALTER TABLE bank_info MODIFY COLUMN katakana VARCHAR(100) NOT NULL;
ALTER TABLE bank_info MODIFY COLUMN type VARCHAR(10) NOT NULL;

ALTER TABLE bank_info ADD PRIMARY KEY (id);


-- Roy 69395
-- Changed position of event_contacts.this_hikiotoshi_amount
ALTER TABLE event_contacts MODIFY this_hikiotoshi_amount int(11) AFTER last_hikiotoshi_amount;

-- 2018-03-02 Adolf (69407)
#toutatsu_info
ALTER TABLE toutatsu_info DROP COLUMN EPCountOK;
ALTER TABLE toutatsu_info DROP COLUMN EMCountOK;
ALTER TABLE toutatsu_info DROP COLUMN AtendancePatterns;

#student_jigyo
ALTER TABLE student_jigyo DROP COLUMN seikyuzandaka_id;

#student_payment_method
ALTER TABLE student_payment_method DROP COLUMN bank_customer_no;
ALTER TABLE student_payment_method DROP COLUMN bank_name;
ALTER TABLE student_payment_method DROP COLUMN bank_branch_name;

#yutaijoho
ALTER TABLE yutaijoho DROP COLUMN yutaitekiyouno;
ALTER TABLE yutaijoho DROP COLUMN yutai_shurui;
ALTER TABLE yutaijoho DROP COLUMN tekiyo_torikeshi;
ALTER TABLE yutaijoho DROP COLUMN Waribikiritsu__c;
ALTER TABLE yutaijoho DROP COLUMN WaribikiGaku__c;
ALTER TABLE yutaijoho DROP COLUMN record_type;
ALTER TABLE yutaijoho DROP COLUMN NC_CO;
  
#sms_bill
ALTER TABLE sms_bill DROP COLUMN target_month;

-- ivan 69169  03-02-2018
-- kaijo settei.shortname needs to be required. repair_all is failing
-- first is to remove all the NULL value within shortname.
UPDATE kaijo_settei SET shortname = " " WHERE shortname IS NULL;
--alters the table
ALTER TABLE kaijo_settei MODIFY COLUMN shortname VARCHAR(20) NOT NULL;


-- smsevent.vacation_type change position.
ALTER TABLE smsevent MODIFY COLUMN vacation_type INT(11) AFTER course_kbn;

-- ivan 69395
-- add seikyu_after_nextmonth below seikyu_nextmonth
ALTER TABLE event_contacts MODIFY COLUMN seikyu_after_nextmonth INT(11) AFTER seikyu_nextmonth;

-- ivan 69395 (march 16 2018)
-- add seikyu_month_after_next below seikyu_nextmonth
ALTER TABLE event_contacts MODIFY COLUMN seikyu_month_after_next INT(11) AFTER seikyu_nextmonth;

-- ivan 69395 (march 19 2018)
-- Remove 
ALTER TABLE event_contacts DROP COLUMN to_event_id2;

-- Ivan  69395 (march 20, 2018)
-- Because removing cedyna_new_code
ALTER TABLE bankinfo_history DROP COLUMN cedyna_new_code;

-- Ivan  69395 (march 21, 2018)
-- Because moving bank_status under error_code
ALTER TABLE bankinfo_history MODIFY COLUMN bank_status TINYINT(4) AFTER error_code;

-- Ivan  69395 (April 5, 2018)
ALTER TABLE sms_transaction DROP COLUMN nyukin_system_tsuki;
ALTER TABLE sms_transaction DROP COLUMN kako_seikyu_tsuki;

-- Ivan  69395 (April 11, 2018)
ALTER TABLE sms_transaction DROP COLUMN nyukin_system_tsuki;
ALTER TABLE sms_transaction DROP COLUMN kako_seikyu_tsuki;
ALTER TABLE bankactions_history DROP COLUMN data_date;

-- Ivan  69395 (April 12, 2018)
ALTER TABLE bankactions_history MODIFY COLUMN furikaebi DATE AFTER area_id;

-- Roy 70075 (April 20, 2018)
ALTER TABLE jigyosho_hikiotoshi DROP COLUMN yucho_account;
ALTER TABLE jigyosho_hikiotoshi DROP COLUMN yucho_account_bangou;

-- Roy 70075 (April 24, 2018)
ALTER TABLE jigyosho_hikiotoshi MODIFY COLUMN yucho_account_no varchar(10) AFTER yucho_branch_kigou

-- Jewel 69395
ALTER TABLE student_grade MODIFY COLUMN date_modified date_modified datetime(-) AFTER date_entered;
ALTER TABLE student_grade MODIFY COLUMN created_by created_by char(36) AFTER date_modified;
ALTER TABLE curriculum MODIFY COLUMN date_modified date_modified datetime(-) AFTER date_entered;
ALTER TABLE curriculum MODIFY COLUMN modified_user_id modified_user_id char(36) AFTER date_modified;
ALTER TABLE curriculum MODIFY COLUMN created_by created_by char(36) AFTER modified_user_id;

-- Ivan  69395 (May 16, 2018)
ALTER TABLE users MODIFY COLUMN password1_modified DATETIME AFTER user_password2;
ALTER TABLE users DROP COLUMN password2_modified;

-- Ivan  69395 (May 22, 2018)
ALTER TABLE users DROP COLUMN user_password2;
ALTER TABLE batch_manager ADD COLUMN account_id CHAR(32) NOT NULL AFTER freeparam3;
ALTER TABLE batch_manager DROP COLUMN school_id;


-- Ivan  69395 (May 22, 2018)
ALTER TABLE contacts DROP COLUMN user_password2;


-- 2018-05-29 shimoju 
-- yamaha_sampledb_20180601 created, if we use that DB, no need to aplay the query above

-- Ivan  69395 (May 31, 2018)
ALTER TABLE contacts DROP COLUMN error_code;
ALTER TABLE bankinfo_senddetail DROP COLUMN sell_date;
ALTER TABLE bankinfo_senddetail DROP COLUMN start_date;
ALTER TABLE bankinfo_senddetail DROP COLUMN salesno;


-- Ivan  69395 (June 09, 2018)
ALTER TABLE contacts MODIFY COLUMN is_available_strict TINYINT AFTER zandaka_lastmonth;
DROP TABLE kaijo_shukei;
DROP TABLE shinkyu_teigi;
ALTER TABLE sms_transaction DROP COLUMN sousai_flg;

-- Ivan  69395 (June 20, 2018)
ALTER TABLE cedyna_batch_schedule DROP COLUMN date_modified;
ALTER TABLE smsevent_instances_backup MODIFY COLUMN attendance_status INT(6) NULL;
ALTER TABLE resource_sub1 MODIFY COLUMN kaikei_bumon_code VARCHAR(5) AFTER sales_tax_kubun;
ALTER TABLE `area` MODIFY COLUMN kaikei_bumon_code VARCHAR(5) AFTER `code`;
ALTER TABLE accounts MODIFY COLUMN last_getsuji_kurikoshi_date DATE AFTER system_month;
ALTER TABLE contacts MODIFY COLUMN phone_work VARCHAR(25) AFTER company_name;
ALTER TABLE contacts MODIFY COLUMN phone_work_cleaned VARCHAR(25) AFTER phone_work;
ALTER TABLE contacts MODIFY COLUMN reports_to_id VARCHAR(36) AFTER phone_work_cleaned;
ALTER TABLE emailman ADD COLUMN account_id CHAR(36) NOT NULL AFTER created_by;
ALTER TABLE emailman ADD COLUMN area_id CHAR(36) NOT NULL AFTER account_id;
ALTER TABLE emailman ADD COLUMN school_id CHAR(36) NOT NULL AFTER area_id;
ALTER TABLE resource_sub1 DROP COLUMN kaikei_bumon_id;
ALTER TABLE `area` DROP COLUMN kaikei_bumon_id;

-- jewel 71120
DROP TABLE yubin;

-- Ivan  69395 (June 22, 2018)
ALTER TABLE cedyna_batch_schedule DROP COLUMN target_month;

-- ivan 70729
ALTER TABLE contacts DROP COLUMN deleteddate;
ALTER TABLE kaijo_settei_audit DROP COLUMN deleted;
ALTER TABLE kaijo_settei_audit DROP COLUMN date_entered;
ALTER TABLE kaijo_settei_audit DROP COLUMN date_modified;
ALTER TABLE kaijo_settei_audit DROP COLUMN modified_user_id;
ALTER TABLE smsevent DROP COLUMN kikan_start;
ALTER TABLE smsevent DROP COLUMN kikan_end;
ALTER TABLE event_contacts DROP COLUMN seikyu_after_nextmonth;
ALTER TABLE bankinfo_history DROP COLUMN account;
ALTER TABLE bankinfo_history DROP COLUMN customer_code;
ALTER TABLE sharei_koushi DROP COLUMN h_idnumber;
ALTER TABLE sharei_koushi DROP COLUMN course_id;
ALTER TABLE sharei_koushi DROP COLUMN bumon_code;
ALTER TABLE sharei_koushi DROP COLUMN school_id;
ALTER TABLE sharei_koushi DROP COLUMN course_name;
ALTER TABLE sharei_koushi DROP COLUMN nen;
ALTER TABLE sharei_koushi DROP COLUMN ki;
ALTER TABLE sharei_koushi DROP COLUMN assigned_user_id;
ALTER TABLE sharei_koushi DROP COLUMN from_datetime;
ALTER TABLE sharei_koushi DROP COLUMN ct_keiyaku_keitai;
ALTER TABLE sharei_koushi DROP COLUMN sharei_komoku;
ALTER TABLE sharei_koushi DROP COLUMN ct_ritsu;
ALTER TABLE sharei_koushi DROP COLUMN ct_tanka;
ALTER TABLE sharei_koushi DROP COLUMN ninzu;
ALTER TABLE sharei_koushi DROP COLUMN houshugaku;
ALTER TABLE sharei_koushi DROP COLUMN kakutei_flg;
ALTER TABLE sharei_koushi DROP COLUMN tsuikakbn;
ALTER TABLE sharei_koushi DROP COLUMN furikomibi;
ALTER TABLE sharei_koushi DROP COLUMN keijoubi;


-- Ivan 69395
DROP TABLE kaijo_settei_audit;
DROP TABLE curriculum_area_settei_audit;
DROP TABLE kaikei_bumon;
ALTER TABLE batch_manager DROP COLUMN account_id;

-- Ivan 69395
ALTER TABLE shiharaisaki DROP COLUMN deleted;
ALTER TABLE shiharaisaki DROP COLUMN kaikei_bumon_id;

-- Ivan 69395
ALTER TABLE sharei_koushi DROP COLUMN teacher_id;
ALTER TABLE sharei_koushi DROP COLUMN teacher_idnumber;
ALTER TABLE sharei_koushi DROP COLUMN teacher_name;
ALTER TABLE sharei_koushi DROP COLUMN t_bank_payment_type;
ALTER TABLE sharei_koushi DROP COLUMN zei_kubun;
-- IVAN 69395
ALTER TABLE sharei_koushi DROP COLUMN date_entered;
ALTER TABLE sharei_shiharaisaki_detail DROP COLUMN date_entered;
ALTER TABLE sharei_shiharaisaki DROP COLUMN date_entered;
ALTER TABLE kaijo_shiharai_detail DROP COLUMN date_entered;
ALTER TABLE kaijo_shiharai DROP COLUMN date_entered;

ALTER TABLE koushi_report DROP COLUMN date_entered;

-- Roy 72535
ALTER TABLE cedyna_batch_schedule CHANGE `month` `target_month` VARCHAR(6) NOT NULL;
ALTER TABLE cedyna_batch_schedule DROP COLUMN month;

--ivan 69395
ALTER TABLE kamoku_sharei CHANGE creditor_kamoku_code chousho_kubun TINYINT

-- ivan 72935
ALTER TABLE batch_manager ADD COLUMN account_id CHAR(36) AFTER school_id;

-- 2018-11-08 (mon)
-- Queries above are already applied on TEST and TRAINING

-- ivan 69395 (11/28/2018)
DROP TABLE kamoku_kaijo_curriculum_bunrui;

-- 2018-12-07 shimoju 
-- yamaha_sampledb_20181207 created, if we use that DB, no need to aplay the query above

-- ivan 69395 (december 27 2018)
ALTER TABLE resource_sub1 DROP COLUMN curriculum_bunrui;


-- mon 73490 (2019-01-17)
ALTER TABLE  `ikou_log` DROP  `created_by`;

-- ivan 69395 01-16-2019
ALTER TABLE shiharaisaki DROP COLUMN tesuuryou_kubun;	
ALTER TABLE kaijo_curriculum_teacher_shuukei DROP COLUMN other_ninzu;
ALTER TABLE kaijo_curriculum_teacher_shuukei DROP COLUMN shinkyu_ninzu_inzaiseki_thismonth;
ALTER TABLE kaijo_curriculum_teacher_shuukei DROP COLUMN shinkyu_positive_ninzu;
ALTER TABLE kaijo_curriculum_teacher_shuukei DROP COLUMN shinkyu_negative_ninzu;
ALTER TABLE kaijo_curriculum_teacher_shuukei DROP COLUMN ido_positive_ninzu;
ALTER TABLE kaijo_curriculum_teacher_shuukei DROP COLUMN ido_negative_ninzu;
ALTER TABLE kaijo_curriculum_teacher_shuukei DROP COLUMN prospect_taikai_ninzu;
ALTER TABLE kaijo_curriculum_teacher_shuukei DROP COLUMN zengetsuhi_ninzu;
ALTER TABLE kaijo_curriculum_teacher_shuukei DROP COLUMN zennenhi_ninzu;

-- mon as of 2019-01-24 TEST and TRAINING SITE is now UP TO DATE by this SQL

-- 2019-02-08 shimoju 
-- yamaha_sampledb_20190208 created, if we use that DB, no need to aplay the query above

-- jewel 69395
ALTER TABLE smsevent DROP INDEX category;
ALTER TABLE sms_fee DROP INDEX name;
ALTER TABLE sms_fee DROP INDEX salesno;
ALTER TABLE bankinfo_history DROP INDEX salesno;


-- 2019-03-04 ref 74243 (mon) 
ALTER TABLE resource DROP COLUMN last_teacher_id_c;

-- ivan 69395
-- . those with PRIMIRY ID might have problem if there is a duplicate content on their column.
-- prioritize delete INDEX first.


ALTER TABLE accounts DROP INDEX idx_accnt_id_del;
ALTER TABLE sms_event_instance DROP INDEX from_to_datetime;
ALTER TABLE smsevent_instances_backup DROP INDEX target_id;
ALTER TABLE emails DROP INDEX idx_email_name;
ALTER TABLE emails DROP INDEX id_deleted;
ALTER TABLE emails DROP INDEX assigned_user_id;
ALTER TABLE emailman DROP INDEX idx_eman_list;
ALTER TABLE acl_roles DROP INDEX idx_aclrole_id_del;
ALTER TABLE acl_actions DROP INDEX idx_aclaction_id_del;
ALTER TABLE prospect_lists_prospects DROP INDEX idx_realted_id;
ALTER TABLE users_schools DROP INDEX idx_user_sch_user;
ALTER TABLE users_schools DROP INDEX idx_user_sch_school;
ALTER TABLE prospect_lists DROP INDEX idx_prospect_list_name;
ALTER TABLE prospect_lists_prospects DROP INDEX idx_pro_id;
ALTER TABLE batch_manager DROP INDEX idx_schedule;

-- sometimes with error.
ALTER TABLE contacts DROP INDEX idx_cont_last_first ;
ALTER TABLE contacts DROP INDEX idx_cont_del_reports ;
ALTER TABLE contacts DROP INDEX smsgroup ;
ALTER TABLE contacts DROP INDEX last_first_deleted ;
ALTER TABLE resource DROP INDEX category; -- g
ALTER TABLE smsevent_instances DROP INDEX id_index; -- g
ALTER TABLE sms_event_instance_backup DROP INDEX from_id;
ALTER TABLE sms_event_instance_backup DROP INDEX to_id;
ALTER TABLE sms_event_instance_backup DROP INDEX from_to_datetime;
ALTER TABLE sms_event_instance_backup DROP INDEX parent_id;
ALTER TABLE smsevent_instances_backup DROP INDEX target_from;
ALTER TABLE smsevent_instances_backup DROP INDEX target_to;
ALTER TABLE smsevent_instances_backup DROP INDEX instance_id;
ALTER TABLE smsevent_instances_backup DROP INDEX event_id; -- this
-- DB response is "NOT EXISTING"
ALTER TABLE sms_event_instance_backup DROP INDEX from_datetime; -- this
ALTER TABLE smsevent_instances_backup DROP INDEX sell_date ; -- this

-- ////
ALTER TABLE contacts_audit ADD INDEX parent_id (parent_id); 
ALTER TABLE smsevent_instances_backup ADD INDEX event_id (event_id);
ALTER TABLE users_schools ADD INDEX idx_users_schools (user_id,school_id);
ALTER TABLE acl_actions ADD INDEX category (category,name);
ALTER TABLE smsevent_instances_backup ADD INDEX deleted_date_modified (deleted_date_modified);
-- sures
ALTER TABLE sms_event_instance_backup ADD INDEX parent_id (parent_id);
ALTER TABLE smsevent_instances_backup ADD INDEX target_id (target_id);
ALTER TABLE emails_contacts ADD INDEX contact_id (contact_id);
ALTER TABLE emails_contacts ADD INDEX email_id (email_id);
ALTER TABLE emails ADD INDEX parent_id (parent_id);
ALTER TABLE emailman ADD INDEX account_id (account_id);
ALTER TABLE emailman ADD INDEX area_id (area_id);
ALTER TABLE emailman ADD INDEX school_id (school_id);
ALTER TABLE emailman ADD INDEX user_id (user_id);
ALTER TABLE acl_roles_actions ADD INDEX role_id (role_id);
ALTER TABLE acl_roles_actions ADD INDEX action_id (action_id);
ALTER TABLE acl_roles_users ADD INDEX role_id (role_id);
ALTER TABLE acl_roles_users ADD INDEX user_id (user_id);
ALTER TABLE prospect_lists_prospects ADD INDEX prospect_list_id (prospect_list_id);
ALTER TABLE prospect_lists_prospects ADD INDEX related_id (related_id);
ALTER TABLE batch_manager ADD INDEX id (id);
ALTER TABLE batch_manager ADD INDEX date_time_start (date_time_start);
ALTER TABLE batch_manager ADD INDEX school_id (school_id);
ALTER TABLE batch_manager ADD INDEX account_id (account_id);
ALTER TABLE batch_items ADD INDEX batch_id (batch_id);
ALTER TABLE batch_items ADD INDEX item_key (item_key);

-- producing error
ALTER TABLE users_schools ADD INDEX user_id (user_id);
ALTER TABLE users_schools ADD INDEX school_id (school_id);

-- no existing table at the moment for announcement. ignore for now.
ALTER TABLE announcement ADD PRIMARY KEY (id);
ALTER TABLE announcement ADD INDEX account_id (account_id);
ALTER TABLE announcement ADD INDEX area_id (area_id);
ALTER TABLE announcement ADD INDEX school_id (school_id);
ALTER TABLE announcement ADD INDEX post_start (post_start);
ALTER TABLE announcement ADD INDEX post_end (post_end);


-- primary was already added in the test site upon checking.
ALTER TABLE contacts_audit ADD PRIMARY KEY (id); -- goods
ALTER TABLE resource_sub1 ADD PRIMARY KEY (id_sub1);  -- already existing PK
ALTER TABLE emails_contacts ADD PRIMARY KEY (id);  -- already existing PK
ALTER TABLE emailman ADD PRIMARY KEY (id);  -- already existing PK
ALTER TABLE acl_actions ADD PRIMARY KEY (id);  -- already existing PK
ALTER TABLE acl_roles_actions ADD PRIMARY KEY (id); - -- already existing PK
ALTER TABLE acl_roles_users ADD PRIMARY KEY (id); -- already existing PK
ALTER TABLE users_schools ADD PRIMARY KEY (id); -- already existing PK
ALTER TABLE prospect_lists_prospects ADD PRIMARY KEY (id);  -- already existing PK
ALTER TABLE sms_event_instance_backup ADD PRIMARY KEY (id); -- cant because of duplicate ID

-- 69395 ivan missing/need to delete
ALTER TABLE event_resource DROP INDEX contact_id;
ALTER TABLE smsevent_instances DROP INDEX target_from;
ALTER TABLE smsevent_instances DROP INDEX target_to;
ALTER TABLE smsevent_instances DROP INDEX target_id;
ALTER TABLE smsevent_instances DROP INDEX from_datetime;
ALTER TABLE sms_salesgroup DROP INDEX event_id;
ALTER TABLE sms_salesgroup DROP INDEX sell_date;

ALTER TABLE sms_event_instance ADD INDEX parent_id_solo (parent_id);
ALTER TABLE sms_event_instance ADD INDEX from_datetime (from_datetime);

ALTER TABLE smsevent_instances ADD INDEX from_datetime_solo (from_datetime);
ALTER TABLE smsevent_instances ADD INDEX from_instance_id (from_instance_id);
ALTER TABLE smsevent_instances ADD INDEX to_instance_id (to_instance_id);

ALTER TABLE email_templates ADD INDEX category_id (category_id);

-- For Yubin DB only
ALTER TABLE yubin.yubin ADD INDEX postcode (postcode);
ALTER TABLE yubin.yubin ADD INDEX ken (ken);
ALTER TABLE yubin.yubin ADD INDEX city (city);
-- end for yubinDB


-- 69395 IVAN

ALTER TABLE resource DROP COLUMN last_teacher_id_c;

-- 73902 shimoju
DROP TABLE IF EXISTS sms_fee_audit;
DROP TABLE IF EXISTS sms_transaction_audit;

-- ivan 69395 additional
ALTER TABLE shiharaisaki_koteihi DROP COLUMN koteihi_zeinuki;


-- mon as of 2019-04-24 starting 711 all the queries are executed on the following environments
-- test
-- test2
-- test3
-- test4 
-- ysample
-- import
-- import2
-- training

-- import1AWS
-- import2AWS
-- testAWS
-- training57AWS
-- trainingAWS
-- uatAWS
-- uatAWS2
-- uatAWS2_2
-- uatAWS3


-- ivan 76215 Aug 12  2019
ALTER TABLE y_seikyu_shiwake DROP COLUMN gensenkijungaku;
ALTER TABLE y_sharei_shiharaisaki DROP COLUMN gensenkijungaku;
ALTER TABLE y_sharei_shiwake DROP COLUMN gensenkijungaku;
ALTER TABLE y_kaijo_shiwake DROP COLUMN gensenkijungaku;

--Ivan 76700 -- September 20 2019
ALTER TABLE y_seikyu_shiwake DROP COLUMN seq_no;
ALTER TABLE y_seikyu_shiwake DROP COLUMN meisai_no;
ALTER TABLE y_sharei_shiwake DROP COLUMN seq_no;
ALTER TABLE y_sharei_shiwake DROP COLUMN meisai_no;



-- ###START# this is for r3_dev 
-- Ivan 76948 -- October 18 2019
ALTER TABLE y_seikyu_shiwake DROP COLUMN system_month;
ALTER TABLE y_seikyu_shiwake_total DROP COLUMN system_month;
-- Ivan 76919 -- October 18 2019
ALTER TABLE y_seikyu_shiwake_total DROP COLUMN contact_id;
ALTER TABLE y_seikyu_shiwake_total DROP COLUMN event_id;
ALTER TABLE y_seikyu_shiwake_total DROP COLUMN fee_id;
ALTER TABLE y_kaijo_shiwake DROP COLUMN school_id;

-- ###END# this is for r3_dev 

-- 2020-07-03 shimoju 
-- yamaha_sampledb_20200703 created, if we use that DB, no need to aplay the query above