-- phpMyAdmin SQL Dump
-- version 2.11.10
-- http://www.phpmyadmin.net
--
-- Host: 192.168.1.161
-- Generation Time: Dec 01, 2011 at 03:16 PM
-- Server version: 5.5.11
-- PHP Version: 5.2.10

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `uniwits`
--

-- --------------------------------------------------------

--
-- Table structure for table `uniwits_affiliate_history`
--

DROP TABLE IF EXISTS `uniwits_affiliate_history`;
CREATE TABLE IF NOT EXISTS `uniwits_affiliate_history` (
  `ah_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `mb_id_affiliate` bigint(20) DEFAULT NULL,
  `mb_id_invited` bigint(20) NOT NULL,
  `uh_id` bigint(20) DEFAULT NULL,
  `afltr_id` bigint(20) DEFAULT NULL COMMENT 'Contributor''s affiliator ID, if available.',
  `aflt_timestamp` datetime NOT NULL,
  `ah_ip_invited` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ah_pcid_invited` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`ah_id`),
  KEY `mb_id_invited` (`mb_id_invited`),
  KEY `uh_id` (`uh_id`),
  KEY `afltr_id` (`afltr_id`,`aflt_timestamp`),
  KEY `ah_ip_invited` (`ah_ip_invited`),
  KEY `ah_pcid_invited` (`ah_pcid_invited`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=10 ;

-- --------------------------------------------------------

--
-- Table structure for table `uniwits_affiliator`
--

DROP TABLE IF EXISTS `uniwits_affiliator`;
CREATE TABLE IF NOT EXISTS `uniwits_affiliator` (
  `afltr_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `afltr_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `afltr_timestamp` datetime NOT NULL COMMENT 'When this record is created.',
  `afltr_pcid` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'PCID of the affiliator when this record is stored.',
  PRIMARY KEY (`afltr_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=7 ;

-- --------------------------------------------------------

--
-- Table structure for table `uniwits_login`
--

DROP TABLE IF EXISTS `uniwits_login`;
CREATE TABLE IF NOT EXISTS `uniwits_login` (
  `mb_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `mb_login` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mb_pswdhash` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `mb_status` enum('VALID','DELETED') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'VALID',
  PRIMARY KEY (`mb_id`),
  UNIQUE KEY `mb_login` (`mb_login`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1000148 ;

-- --------------------------------------------------------

--
-- Table structure for table `uniwits_login_alias`
--

DROP TABLE IF EXISTS `uniwits_login_alias`;
CREATE TABLE IF NOT EXISTS `uniwits_login_alias` (
  `mb_id` bigint(20) NOT NULL COMMENT 'The original member whose alias is recorded.',
  `mb_id_alias` bigint(20) NOT NULL COMMENT 'The alias''s member ID.',
  PRIMARY KEY (`mb_id`,`mb_id_alias`),
  KEY `mb_id_alias` (`mb_id_alias`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Record that some of the members are copied from another.';

-- --------------------------------------------------------

--
-- Table structure for table `uniwits_member`
--

DROP TABLE IF EXISTS `uniwits_member`;
CREATE TABLE IF NOT EXISTS `uniwits_member` (
  `mb_id` bigint(20) NOT NULL,
  `mb_nickname` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `mb_timezone_minutes` int(11) DEFAULT NULL COMMENT 'Bias from UTC.',
  `mb_timestamp` datetime NOT NULL COMMENT 'In UTC',
  PRIMARY KEY (`mb_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `uniwits_member_email`
--

DROP TABLE IF EXISTS `uniwits_member_email`;
CREATE TABLE IF NOT EXISTS `uniwits_member_email` (
  `mb_id` bigint(20) NOT NULL,
  `mb_email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `me_timestamp` datetime NOT NULL COMMENT 'In UTC',
  `me_status` enum('PREVALID','VALIDATED') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'PREVALID',
  `me_notify` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 if the email should be notified when a message is sent to the member.',
  PRIMARY KEY (`mb_id`,`mb_email`),
  KEY `mb_email` (`mb_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `uniwits_member_info`
--

DROP TABLE IF EXISTS `uniwits_member_info`;
CREATE TABLE IF NOT EXISTS `uniwits_member_info` (
  `mb_id` bigint(20) NOT NULL,
  `mb_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '(surname)"|"(firstname)',
  `mb_name_read` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '(surname_read)"|"(firstname_read)',
  `mb_gender` enum('F','M') COLLATE utf8_unicode_ci DEFAULT NULL,
  `mb_zipcode` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mb_country` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mb_state` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mb_city` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mb_address` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mb_birthday` date DEFAULT NULL,
  `mb_timestamp` datetime NOT NULL COMMENT 'In UTC',
  PRIMARY KEY (`mb_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Stand-in structure for view `uniwits_member_notifiable`
--
DROP VIEW IF EXISTS `uniwits_member_notifiable`;
CREATE TABLE IF NOT EXISTS `uniwits_member_notifiable` (
`mb_id` bigint(20)
,`mb_email` varchar(255)
);
-- --------------------------------------------------------

--
-- Table structure for table `uniwits_member_phone`
--

DROP TABLE IF EXISTS `uniwits_member_phone`;
CREATE TABLE IF NOT EXISTS `uniwits_member_phone` (
  `mb_id` bigint(20) NOT NULL,
  `mb_phone` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`mb_id`,`mb_phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `uniwits_paypal_ipn_history`
--

DROP TABLE IF EXISTS `uniwits_paypal_ipn_history`;
CREATE TABLE IF NOT EXISTS `uniwits_paypal_ipn_history` (
  `ph_id` bigint(20) NOT NULL,
  `key` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `value` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`ph_id`,`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Stand-in structure for view `uniwits_prefectures`
--
DROP VIEW IF EXISTS `uniwits_prefectures`;
CREATE TABLE IF NOT EXISTS `uniwits_prefectures` (
`zc_country` char(2)
,`zc_prefecture` varchar(63)
);
-- --------------------------------------------------------

--
-- Table structure for table `uniwits_purchase_history`
--

DROP TABLE IF EXISTS `uniwits_purchase_history`;
CREATE TABLE IF NOT EXISTS `uniwits_purchase_history` (
  `ph_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `mb_id` bigint(20) NOT NULL,
  `ph_object` enum('UNILAR') COLLATE utf8_unicode_ci NOT NULL,
  `ph_timestamp_create` datetime NOT NULL,
  `ph_timestamp_complete` datetime DEFAULT NULL,
  `ph_status` enum('VALID','CANCELLED') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'VALID',
  `txn_id_paypal` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`ph_id`),
  KEY `txn_id_paypal` (`txn_id_paypal`),
  KEY `mb_id` (`mb_id`,`ph_object`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

--
-- Table structure for table `uniwits_support_journal`
--

DROP TABLE IF EXISTS `uniwits_support_journal`;
CREATE TABLE IF NOT EXISTS `uniwits_support_journal` (
  `usj_id` bigint(20) NOT NULL,
  `usj_id_head` bigint(20) DEFAULT NULL,
  `usj_id_referto` bigint(20) DEFAULT NULL,
  `usj_type` enum('PLAIN','PHPS') COLLATE utf8_unicode_ci NOT NULL COMMENT 'PLAIN: plaintext content.  PHPS: PHP 5 serialized data structure.',
  `mb_id_author` bigint(20) DEFAULT NULL,
  `usj_author_email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `usj_content` text COLLATE utf8_unicode_ci NOT NULL,
  `usj_timestamp` datetime NOT NULL,
  PRIMARY KEY (`usj_id`),
  KEY `mb_id_author` (`mb_id_author`),
  KEY `usj_author_email` (`usj_author_email`),
  KEY `usj_id_head` (`usj_id_head`,`usj_id`),
  FULLTEXT KEY `usj_content` (`usj_content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `uniwits_support_record`
--

DROP TABLE IF EXISTS `uniwits_support_record`;
CREATE TABLE IF NOT EXISTS `uniwits_support_record` (
  `usr_id` bigint(20) NOT NULL,
  `usj_id` bigint(20) NOT NULL COMMENT 'The journal that is processed.',
  `usr_timestamp` datetime NOT NULL COMMENT 'Time that the journal was processed.',
  PRIMARY KEY (`usr_id`),
  KEY `usj_id` (`usj_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `uniwits_unilar_balance`
--

DROP TABLE IF EXISTS `uniwits_unilar_balance`;
CREATE TABLE IF NOT EXISTS `uniwits_unilar_balance` (
  `mb_id` bigint(20) NOT NULL,
  `ub_amount` double NOT NULL COMMENT 'It acts as a cache of the last uh_result_balance in the member''s unilar_history.',
  PRIMARY KEY (`mb_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `uniwits_unilar_bill_detail`
--

DROP TABLE IF EXISTS `uniwits_unilar_bill_detail`;
CREATE TABLE IF NOT EXISTS `uniwits_unilar_bill_detail` (
  `ubd_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `uh_id` bigint(20) NOT NULL,
  `ubd_amount` double NOT NULL COMMENT 'An amount to be subtracted from the user''s unilar balance.',
  `ubd_upto_timestamp` datetime DEFAULT NULL COMMENT 'Charge on the corresponding item is cleared up to this date and time.  It is NULL if not applicable.',
  PRIMARY KEY (`ubd_id`),
  KEY `uh_id` (`uh_id`),
  KEY `ubd_upto_timestamp` (`ubd_upto_timestamp`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5774 ;

-- --------------------------------------------------------

--
-- Table structure for table `uniwits_unilar_history`
--

DROP TABLE IF EXISTS `uniwits_unilar_history`;
CREATE TABLE IF NOT EXISTS `uniwits_unilar_history` (
  `uh_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `mb_id` bigint(20) NOT NULL,
  `uh_amount` double NOT NULL COMMENT 'The amount to be added to the balance.',
  `uh_reason` enum('PURCHASE','GIFT','CONSUME','CANCEL','REWARD') COLLATE utf8_unicode_ci NOT NULL COMMENT 'PURCHASE: from user''s real money to unilar.  GIFT: gift from Uniwits.  CONSUME: exchanged into services.  CANCEL: cancel previous unilar flow.  REWARD: in exchange to contribution of the user.',
  `uh_comment` text COLLATE utf8_unicode_ci,
  `uh_result_balance` double NOT NULL COMMENT 'The balance after current record effects.',
  `uh_timestamp` datetime NOT NULL,
  PRIMARY KEY (`uh_id`),
  KEY `mb_id` (`mb_id`,`uh_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=255 ;

-- --------------------------------------------------------

--
-- Table structure for table `uniwits_validation_code`
--

DROP TABLE IF EXISTS `uniwits_validation_code`;
CREATE TABLE IF NOT EXISTS `uniwits_validation_code` (
  `vc_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `vc_code` bigint(20) NOT NULL COMMENT 'Program computed verification code.',
  `vc_timestamp` datetime NOT NULL,
  PRIMARY KEY (`vc_id`),
  KEY `vc_timestamp` (`vc_timestamp`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=61 ;

-- --------------------------------------------------------

--
-- Table structure for table `uniwits_zipcode`
--

DROP TABLE IF EXISTS `uniwits_zipcode`;
CREATE TABLE IF NOT EXISTS `uniwits_zipcode` (
  `zc_country` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `zc_zipcode` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `zc_prefecture_read` varchar(63) COLLATE utf8_unicode_ci NOT NULL,
  `zc_city_read` varchar(63) COLLATE utf8_unicode_ci NOT NULL,
  `zc_district_read` varchar(144) COLLATE utf8_unicode_ci NOT NULL,
  `zc_prefecture` varchar(63) COLLATE utf8_unicode_ci NOT NULL,
  `zc_city` varchar(63) COLLATE utf8_unicode_ci NOT NULL,
  `zc_district` varchar(144) COLLATE utf8_unicode_ci NOT NULL,
  KEY `zc_country` (`zc_country`,`zc_zipcode`),
  KEY `zc_all` (`zc_country`,`zc_prefecture`,`zc_city`,`zc_district`),
  KEY `zc_prefecture` (`zc_country`,`zc_prefecture`),
  KEY `zc_city` (`zc_country`,`zc_prefecture`,`zc_city`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Structure for view `uniwits_member_notifiable`
--
DROP TABLE IF EXISTS `uniwits_member_notifiable`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `uniwits`.`uniwits_member_notifiable` AS select `uniwits`.`uniwits_member_email`.`mb_id` AS `mb_id`,`uniwits`.`uniwits_member_email`.`mb_email` AS `mb_email` from `uniwits`.`uniwits_member_email` where ((`uniwits`.`uniwits_member_email`.`me_status` = _utf8'VALIDATED') and `uniwits`.`uniwits_member_email`.`me_notify`) group by `uniwits`.`uniwits_member_email`.`mb_id`;

-- --------------------------------------------------------

--
-- Structure for view `uniwits_prefectures`
--
DROP TABLE IF EXISTS `uniwits_prefectures`;

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `uniwits`.`uniwits_prefectures` AS select `uniwits`.`uniwits_zipcode`.`zc_country` AS `zc_country`,`uniwits`.`uniwits_zipcode`.`zc_prefecture` AS `zc_prefecture` from `uniwits`.`uniwits_zipcode` group by `uniwits`.`uniwits_zipcode`.`zc_country`,`uniwits`.`uniwits_zipcode`.`zc_prefecture` order by `uniwits`.`uniwits_zipcode`.`zc_country`,`uniwits`.`uniwits_zipcode`.`zc_prefecture`;
