DROP TABLE IF EXISTS `sms_shikaku`;
CREATE TABLE IF NOT EXISTS `sms_shikaku` (
  `id` varchar(36) NOT NULL DEFAULT '',
  `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` varchar(36) DEFAULT NULL,
  `created_by` varchar(36) DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `contact_id` varchar(36) DEFAULT NULL,
  `course_id` varchar(36) DEFAULT NULL,
  `category` varchar(16) DEFAULT NULL,
  `juko_date` date DEFAULT NULL,
  `nintei_date` date DEFAULT NULL,
  `nintei_no` varchar(10) DEFAULT NULL,
  `valid_date` date DEFAULT NULL,
  `description` text,
  PRIMARY KEY (`id`),
  KEY `idx_contact_id` (`contact_id`,`deleted`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 2010-11-20  BIOKURA ONLY !!!!

DROP TABLE IF EXISTS `event_contacts`;
CREATE TABLE IF NOT EXISTS `event_contacts` (
  `id` varchar(36) NOT NULL DEFAULT '',
  `contact_id` varchar(36) DEFAULT NULL,
  `event_id` varchar(36) DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `event_contact_status` varchar(3) DEFAULT '0',
  `start_date` datetime DEFAULT '0000-00-00 00:00:00',
  `end_date` datetime DEFAULT '0000-00-00 00:00:00',
  `event_credit` varchar(10) DEFAULT NULL,
  `description` text,
  `uketsuke_bango` varchar(255) NOT NULL,
  `tsushin_nohin_jikan` varchar(255) NOT NULL,
  `moshikomi_guide_date` date NOT NULL,
  `nyukin_kakuninsho_date` date NOT NULL,
  `shuryosho_bango` varchar(255) NOT NULL,
  `shuryosho_date` date NOT NULL,
  `tsushin_shiharai_hoho` varchar(255) NOT NULL,
  `tsushin_hasso_saki` varchar(255) NOT NULL,
  `tsushin_hasso_date` date NOT NULL,
  `tsushin_nohin_date` date NOT NULL,
  `kadai_teishutsu_date` date NOT NULL,
  `school_shusseki_date` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `event_id` (`event_id`),
  KEY `contact_id` (`contact_id`,`event_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 2010-11-26 

CREATE TABLE IF NOT EXISTS `manual_transaction` (
  `id` varchar(36) NOT NULL DEFAULT '',
  `custom_unique_id` varchar(50) DEFAULT NULL,
  `contact_name` varchar(255) DEFAULT NULL,
  `fee_name` varchar(255) DEFAULT NULL,
  `t_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `actual_in_amount` int(11) NOT NULL DEFAULT '0',
  `mikakunin_zandaka` int(11) NOT NULL DEFAULT '0',
  `payment_type` varchar(255) DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `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` varchar(36) DEFAULT NULL,
  `created_by` varchar(36) DEFAULT NULL,
  `contact_id` varchar(36) DEFAULT NULL,
  `description` text,
  `raw_import_data` text NOT NULL,
  `import_serial_no` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`import_serial_no`),
  UNIQUE KEY `unique_id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


-- 2010-12-02

CREATE TABLE IF NOT EXISTS `manual_contact` (
  `id` varchar(36) NOT NULL DEFAULT '',
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `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` varchar(36) DEFAULT NULL,
  `assigned_user_id` varchar(36) DEFAULT NULL,
  `created_by` varchar(36) DEFAULT NULL,
  `salutation` varchar(5) DEFAULT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `first_name_furigana` varchar(100) DEFAULT NULL,
  `last_name_furigana` varchar(100) DEFAULT NULL,
  `lead_source` varchar(100) DEFAULT NULL,
  `lead_source_description` varchar(64) DEFAULT NULL,
  `title` varchar(50) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `reports_to_id` varchar(36) DEFAULT NULL,
  `commission_company_id` varchar(36) DEFAULT NULL,
  `birthdate` date DEFAULT NULL,
  `do_not_call` varchar(3) DEFAULT '0',
  `phone_home` varchar(25) DEFAULT NULL,
  `phone_mobile` varchar(25) DEFAULT NULL,
  `phone_work` varchar(25) DEFAULT NULL,
  `phone_other` varchar(25) DEFAULT NULL,
  `phone_fax` varchar(25) DEFAULT NULL,
  `email1` varchar(100) DEFAULT NULL,
  `email2` varchar(100) DEFAULT NULL,
  `assistant` varchar(75) DEFAULT NULL,
  `assistant_phone` varchar(25) DEFAULT NULL,
  `email_opt_out` varchar(3) DEFAULT '0',
  `primary_address_street` varchar(150) DEFAULT NULL,
  `primary_address_city` varchar(100) DEFAULT NULL,
  `primary_address_state` varchar(100) DEFAULT NULL,
  `primary_address_postalcode` varchar(20) DEFAULT NULL,
  `primary_address_country` varchar(100) DEFAULT NULL,
  `primary_address_kana` varchar(250) DEFAULT NULL,
  `alt_address_street` varchar(150) DEFAULT NULL,
  `alt_address_city` varchar(100) DEFAULT NULL,
  `alt_address_state` varchar(100) DEFAULT NULL,
  `alt_address_postalcode` varchar(20) DEFAULT NULL,
  `alt_address_country` varchar(100) DEFAULT NULL,
  `alt_address_kana` varchar(250) DEFAULT NULL,
  `description` text,
  `portal_name` varchar(255) DEFAULT NULL,
  `portal_active` tinyint(1) NOT NULL DEFAULT '0',
  `portal_app` varchar(255) DEFAULT NULL,
  `invalid_email` tinyint(1) DEFAULT '0',
  `smsgroup` varchar(15) NOT NULL DEFAULT '',
  `contact_status` varchar(3) NOT NULL DEFAULT '0',
  `picture_filename` varchar(255) DEFAULT NULL,
  `picture_file_mime_type` varchar(100) DEFAULT NULL,
  `school_id` varchar(36) NOT NULL DEFAULT 'sssssss-1111',
  `vacation_total` float DEFAULT NULL,
  `vacation_used` float DEFAULT NULL,
  `is_available_strict` tinyint(1) NOT NULL DEFAULT '0',
  `major` varchar(32) DEFAULT NULL,
  `send_document_home` varchar(3) NOT NULL DEFAULT 'on',
  `send_document_work` varchar(3) NOT NULL DEFAULT 'on',
  `company_name` varchar(100) DEFAULT NULL,
  `idnumber` varchar(32) DEFAULT NULL,
  `nationality` varchar(16) DEFAULT NULL,
  `gender` varchar(8) DEFAULT NULL,
  `work_school` varchar(64) DEFAULT NULL,
  `last_school_name` varchar(64) DEFAULT NULL,
  `last_school_dept` varchar(64) DEFAULT NULL,
  `study_aim` varchar(64) DEFAULT NULL,
  `transport_fee_type` varchar(16) DEFAULT NULL,
  `transportfee` int(11) DEFAULT NULL,
  `incometax_rate` float NOT NULL DEFAULT '0.1',
  `leave_date` date DEFAULT NULL,
  `enroll_date` date DEFAULT NULL,
  `grade` varchar(10) DEFAULT NULL,
  `grade_entrance_date` date DEFAULT NULL,
  `application_date` date DEFAULT NULL,
  `extra_date` date DEFAULT NULL,
  `id_card_field1` text,
  `id_card_field2` varchar(50) DEFAULT NULL,
  `id_card_field3` varchar(50) DEFAULT NULL,
  `id_card_field4` text,
  `renmei_label` text,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `custom_var1` text,
  `custom_var2` text,
  `custom_var3` text,
  `custom_var4` text,
  `custom_var5` text,
  `custom_var6` text,
  `custom_var7` text,
  `custom_var8` text,
  `custom_var9` text,
  `custom_var10` text,
  `custom_var11` text,
  `custom_var12` text,
  `custom_var13` text,
  `custom_var14` text,
  `custom_var15` text,
  `custom_var16` text,
  `custom_var17` text,
  `custom_var18` text,
  `custom_var19` text,
  `custom_var20` text,
  `raw_import_data` text,
  `import_serial_no` int(11) NOT NULL AUTO_INCREMENT,
  `import_source` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`import_serial_no`),
  UNIQUE KEY `id` (`id`),
  KEY `last_name` (`last_name`,`status`),
  KEY `last_name_furigana` (`last_name_furigana`),
  KEY `first_name` (`first_name`),
  KEY `first_name_furigana` (`first_name_furigana`),
  KEY `email` (`email1`),
  KEY `tel1` (`phone_home`),
  KEY `tel2` (`phone_other`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;



ALTER TABLE `smsevent` 
ADD `custom_var71` TEXT,
ADD `custom_var72` TEXT,
ADD `custom_var73` TEXT,
ADD `custom_var74` TEXT,
ADD `custom_var75` TEXT,
ADD `custom_var76` TEXT,
ADD `custom_var77` TEXT,
ADD `custom_var78` TEXT,
ADD `custom_var79` TEXT;

DELETE FROM `biokura_test`.`sms_transactiontype` WHERE `sms_transactiontype`.`id` = '3' LIMIT 1;
DELETE FROM `biokura_test`.`sms_transactiontype` WHERE `sms_transactiontype`.`id` = '4' LIMIT 1;
DELETE FROM `biokura_test`.`sms_transactiontype` WHERE `sms_transactiontype`.`id` = '12' LIMIT 1;
DELETE FROM `biokura_test`.`sms_transactiontype` WHERE `sms_transactiontype`.`id` = '13' LIMIT 1;
DELETE FROM `biokura_test`.`sms_transactiontype` WHERE `sms_transactiontype`.`id` = '14' LIMIT 1;


DROP TABLE IF EXISTS `config_export_fields`;
CREATE TABLE IF NOT EXISTS `config_export_fields` (
  `module` varchar(32) NOT NULL,
  `suffix` varchar(32) NOT NULL,
  `user_id` varchar(36) NOT NULL,
  `field` varchar(64) NOT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `date_updated` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `config_export_fields` (`module`, `suffix`, `user_id`, `field`, `deleted`, `date_updated`) VALUES
('Student', '', '1', 'last_name', 0, '2011-01-20'), ('Student', '', '1', 'idnumber', 0, '2011-01-20');

ALTER TABLE `sms_shikaku` DROP `category` ;

ALTER TABLE `smsevent` CHANGE `subtype` `subtype` VARCHAR( 3 ) NULL DEFAULT NULL ; 

-- mailmaga related setting and changes
UPDATE config SET value = '0' WHERE name = 'smtpauth_req' ; 

-- idnumber problem
UPDATE contacts SET idnumber='01022566' WHERE id = '203e3afe-4dca-819e-d18f-4db63d095734' ;
UPDATE contacts SET idnumber='01022567' WHERE id = 'da527c9a-510c-d74b-0c64-4db63ff9c853' ;
UPDATE contacts SET idnumber='01022568' WHERE id = '1f123d6e-1418-8f87-0503-4db63fbc64f1';
UPDATE contacts SET idnumber='01022569' WHERE id = '85e3625d-10a7-1137-590e-4db63f9a505b';
UPDATE contacts SET idnumber='01022570' WHERE id = 'aa3040f7-ae50-2a78-ba51-4db63f328e5a';
UPDATE contacts SET idnumber='01022571' WHERE id = 'b6aee910-26c9-5e35-57b7-4db64056ec48';
UPDATE contacts SET idnumber='01022572' WHERE id = '961e1def-c80c-d4cf-52d6-4db6438344ae';
UPDATE contacts SET idnumber='01022573' WHERE id = 'e2ef867f-0c90-c054-715f-4db644eda4e8';
UPDATE contacts SET idnumber='01022574' WHERE id = 'df334264-4037-3d80-2b0e-4db6446bde4e';
UPDATE contacts SET idnumber='01022575' WHERE id = '59e78d39-f6ce-20c0-51bb-4db645654114';
UPDATE contacts SET idnumber='01022576' WHERE id = '74097e75-c2dc-5951-f980-4db64581f9a0';
UPDATE contacts SET idnumber='01022577' WHERE id = '1db5a3a7-0d8b-74c5-8764-4db645eb64fa';
UPDATE contacts SET idnumber='01022578' WHERE id = '7be6770b-ed43-f6d7-988d-4db645b5ec71';
UPDATE contacts SET idnumber='01022579' WHERE id = 'f31da87b-f0bb-5d63-e979-4db6457589db';
UPDATE contacts SET idnumber='01022580' WHERE id = '75697039-2dc2-4171-ffa7-4db646d25928';
UPDATE contacts SET idnumber='01022581' WHERE id = '6c69f932-d63a-b73c-bfeb-4db6464c3c75';
UPDATE contacts SET idnumber='01022582' WHERE id = '442d3834-c2a7-4d10-03ce-4db6461c2433';
UPDATE contacts SET idnumber='01022583' WHERE id = '74454001-f218-dcd8-c8a2-4db646f9a3e4';
UPDATE contacts SET idnumber='01022584' WHERE id = '7a987d67-b53c-6f8e-783d-4db6465f31dc';
UPDATE contacts SET idnumber='01022585' WHERE id = '4a7acf5d-7714-8db9-f3bc-4db6477cfe80';
UPDATE contacts SET idnumber='01022586' WHERE id = '706aba2b-e870-a366-f423-4db647dea9a8';
UPDATE contacts SET idnumber='01022587' WHERE id = '80ea7834-c39e-f958-e293-4db65aef6aa0';
UPDATE contacts SET idnumber='01022588' WHERE id = '20ebefff-2516-64e6-abbf-4db678c97311';

SELECT id, idnumber, count(idnumber) FROM `contacts` WHERE deleted =0 AND smsgroup = 'student' GROUP BY idnumber
HAVING count( idnumber ) >1 ORDER BY `contacts`.`idnumber` ASC

SELECT id, idnumber, date_entered, date_modified FROM `contacts` WHERE idnumber IN ( '01021407', '01021206', '99000006' )

SELECT * FROM `contacts` WHERE `date_entered` = '1999-11-30 09:00:00'
UPDATE contacts SET date_entered=date_modified  WHERE date_entered = '1999-11-30 09:00:00' ;

-- 2011-05-25 fee date_entered updates
SELECT * FROM sms_fee WHERE date_modified <= '2011-05-23 15:00:00' ; 
UPDATE sms_fee SET date_entered=date_modified WHERE date_modified <= '2011-05-23 15:00:00' ; 

-- 2011-10-06 course fields update
SELECT * FROM smsevent WHERE custom_var38='KIJ小淵沢';
UPDATE smsevent SET custom_var38='KMA小淵沢' WHERE custom_var38='KIJ小淵沢';


-- 2012-01-04 version up
-- start from until the end of UPDATE_DB.sql file

CREATE TABLE IF NOT EXISTS `config_lock` (
`category` varchar( 36 ) DEFAULT NULL ,
`name` varchar( 36 ) DEFAULT NULL ,
`value` text
) ENGINE = MYISAM DEFAULT CHARSET = utf8;

-- UPDATE users, users2 SET users.user_preferences = users2.user_preferences WHERE users.id=users2.id

-- 2012-03-29 customizations

ALTER TABLE `event_contacts` ADD `uketsuke_date` DATE NOT NULL ;