-------------------------------------------------
-- member package sql
-- CopyRight(C) 2010 Shopformer Development Team. All Right Reserved
-- URL: http://sourceforge.jp/projects/shopformer/
-- 
-- 
-- Mail: m_nakashima@users.sourceforge.jp
-- Auther: Masanori Nakashima
-- Last Update: 2010-06-23
-------------------------------------------------
-- member
-- status_flag: 0=仮登録, 101=登録済み, 251=本人削除, 255=管理者削除
-- auth_key: メール認証その他に利用する一時的な認証キー
-------------------------------------------------
CREATE TABLE member (
	member_number SERIAL NOT NULL
	,member_id VARCHAR(16) NOT NULL
	,member_class SMALLINT NOT NULL DEFAULT 1
	,login_id VARCHAR(80) NOT NULL
	,login_password VARCHAR(255) NOT NULL
	,auth_key VARCHAR(24) NULL
	,view_name VARCHAR(255) NULL
	,pc_mail VARCHAR(80) NULL
	,mb_mail VARCHAR(80) NULL
	,terminal_id VARCHAR(128) NULL
	,sim_id VARCHAR(128) NULL
	,point INTEGER NOT NULL DEFAULT 0
	,status_flag SMALLINT NOT NULL DEFAULT 0
	,first_name VARCHAR(32) NULL
	,family_name VARCHAR(32) NULL
	,first_kana VARCHAR(48) NULL
	,family_kana VARCHAR(48) NULL
	,country_name VARCHAR(255) NULL
	,zip_code VARCHAR(8) NULL
	,prefecture_number SMALLINT NULL
	,address VARCHAR(255) NULL
	,building VARCHAR(255) NULL
	,telephone_number VARCHAR(16) NULL
	,faximili_number VARCHAR(16) NULL
	,mobile_number VARCHAR(16) NULL
	,zip_code2 VARCHAR(8) NULL
	,prefecture_number2 SMALLINT NULL
	,address2 VARCHAR(255) NULL
	,building2 VARCHAR(255) NULL
	,telephone_number2 VARCHAR(16) NULL
	,faximili_number2 VARCHAR(16) NULL
	,url VARCHAR(255) NULL
	,birth_year SMALLINT NULL
	,birth_month SMALLINT NULL
	,birth_day SMALLINT NULL
	,sex SMALLINT NULL
	,blood_type SMALLINT NULL
	,height SMALLINT NULL
	,weight SMALLINT NULL
	,bust SMALLINT NULL
	,waist SMALLINT NULL
	,hip SMALLINT NULL
	,introduction TEXT NULL
	,company_name VARCHAR(255) NULL
	,section_name VARCHAR(255) NULL
	,description TEXT NULL
	,deny_uris TEXT NULL
	,updated_date TIMESTAMP NULL
	,registered_date TIMESTAMP NULL
	,owner_id VARCHAR(16) NULL
	,modifier_id VARCHAR(16) NULL
	,CONSTRAINT member_member_number PRIMARY KEY (member_number)
	,CONSTRAINT member_member_id UNIQUE (member_id)
	,CONSTRAINT member_login_id UNIQUE (login_id)
) WITHOUT OIDS;
CREATE INDEX idx_member_view_name ON member( view_name );
CREATE INDEX idx_member_pc_mail ON member( pc_mail );
CREATE INDEX idx_member_mb_mail ON member( mb_mail );
CREATE INDEX idx_member_terminal_id ON member( terminal_id );
CREATE INDEX idx_member_sim_id ON member( sim_id );
CREATE INDEX idx_member_updated_date ON member( updated_date );
CREATE INDEX idx_member_registered_date ON member( registered_date );
CREATE INDEX idx_member_member_id ON member( member_id );
CREATE INDEX idx_member_member_number ON member( member_number );
CREATE INDEX idx_member_login_id ON member( login_id );
CREATE INDEX idx_member_status_flag ON member( status_flag );

-------------------------------------------------
-- member_history
-------------------------------------------------
CREATE TABLE member_history (
	history_number BIGSERIAL NOT NULL
	,history_id VARCHAR(18) NOT NULL
	,history_date TIMESTAMP NULL
	,member_number INTEGER NULL
	,member_id VARCHAR(16) NULL
	,member_class SMALLINT NULL
	,login_id VARCHAR(80) NULL
	,login_password VARCHAR(32) NULL
	,auth_key VARCHAR(24) NULL
	,view_name VARCHAR(255) NULL
	,pc_mail VARCHAR(80) NULL
	,mb_mail VARCHAR(80) NULL
	,terminal_id VARCHAR(128) NULL
	,sim_id VARCHAR(128) NULL
	,point INTEGER NULL
	,status_flag SMALLINT NULL
	,first_name VARCHAR(32) NULL
	,family_name VARCHAR(32) NULL
	,first_kana VARCHAR(48) NULL
	,family_kana VARCHAR(48) NULL
	,country_name VARCHAR(255) NULL
	,zip_code VARCHAR(8) NULL
	,prefecture_number SMALLINT NULL
	,address VARCHAR(255) NULL
	,building VARCHAR(255) NULL
	,telephone_number VARCHAR(16) NULL
	,faximili_number VARCHAR(16) NULL
	,mobile_number VARCHAR(16) NULL
	,zip_code2 VARCHAR(8) NULL
	,prefecture_number2 SMALLINT NULL
	,address2 VARCHAR(255) NULL
	,building2 VARCHAR(255) NULL
	,telephone_number2 VARCHAR(16) NULL
	,faximili_number2 VARCHAR(16) NULL
	,url VARCHAR(255) NULL
	,birth_year SMALLINT NULL
	,birth_month SMALLINT NULL
	,birth_day SMALLINT NULL
	,sex SMALLINT NULL
	,blood_type SMALLINT NULL
	,height SMALLINT NULL
	,weight SMALLINT NULL
	,bust SMALLINT NULL
	,waist SMALLINT NULL
	,hip SMALLINT NULL
	,introduction TEXT NULL
	,company_name VARCHAR(255) NULL
	,section_name VARCHAR(255) NULL
	,description TEXT NULL
	,deny_uris TEXT NULL
	,updated_date TIMESTAMP NULL
	,registered_date TIMESTAMP NULL
	,owner_id VARCHAR(16) NULL
	,modifier_id VARCHAR(16) NULL
	,CONSTRAINT member_history_history_number PRIMARY KEY (history_number)
	,CONSTRAINT member_history_history_id UNIQUE (history_id)
	,FOREIGN KEY (member_id) REFERENCES member (member_id) ON DELETE CASCADE ON UPDATE CASCADE
) WITHOUT OIDS;
CREATE INDEX idx_member_history_history_date ON member_history( history_date );
CREATE INDEX idx_member_history_member_id ON member_history( member_id );

-------------------------------------------------
-- member_option
-------------------------------------------------
CREATE TABLE member_option (
	option_number BIGSERIAL NOT NULL
	,member_id VARCHAR(16) NOT NULL
	,option_name VARCHAR(128) NOT NULL
	,option_value VARCHAR(255) NULL
	,order_number SMALLINT NOT NULL DEFAULT 0
	,CONSTRAINT member_option_option_number PRIMARY KEY (option_number)
	,FOREIGN KEY (member_id) REFERENCES member (member_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB character set utf8;
CREATE INDEX idx_member_option_option_name ON member_option( option_name );
CREATE INDEX idx_member_option_option_value ON member_option( option_value );
CREATE INDEX idx_member_option_option_name_value ON member_history( option_name,option_value );

-------------------------------------------------
-- member_option_history
-------------------------------------------------
CREATE TABLE member_option_history (
	history_number BIGSERIAL NOT NULL
	,history_id VARCHAR(18) NOT NULL
	,member_id VARCHAR(16) NOT NULL
	,option_name VARCHAR(128) NOT NULL
	,option_value VARCHAR(255) NULL
	,order_number SMALLINT NOT NULL DEFAULT 0
	,CONSTRAINT member_option_history_history_number PRIMARY KEY (history_number)
	,FOREIGN KEY (history_id) REFERENCES member_history (history_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB character set utf8;
CREATE INDEX idx_member_option_history_history_date ON member_option_history( history_date );
CREATE INDEX idx_member_option_history_member_id ON member_option_history( member_id );

-------------------------------------------------
-- member_regist_temporary
-- 空メール処理の一時保存テーブル
-------------------------------------------------
CREATE TABLE member_regist_temporary (
	temporary_number BIGSERIAL NOT NULL
	,temporary_id VARCHAR(32) NOT NULL
	,email VARCHAR(80) NOT NULL
	,registered_date TIMESTAMP NULL
	,CONSTRAINT member_regist_temporary_temporary_number PRIMARY KEY (temporary_number)
	,CONSTRAINT member_regist_temporary_temporary_id UNIQUE (temporary_id)
) WITHOUT OIDS;
CREATE INDEX idx_member_regist_temporary_email ON member_regist_temporary( email );

-------------------------------------------------
-- member_login_history
-- login_result: 0=成功, 201=パスワード間違い
-------------------------------------------------
CREATE TABLE member_login_history (
	history_number BIGSERIAL NOT NULL
	,member_id VARCHAR(16) NOT NULL
	,login_date TIMESTAMP NOT NULL
	,remote_address VARCHAR(16) NULL
	,remote_host VARCHAR(255) NULL
	,user_agent TEXT NULL
	,login_result SMALLINT NOT NULL DEFAULT 0
	,CONSTRAINT member_login_history_history_number PRIMARY KEY (history_number)
	,FOREIGN KEY (member_id) REFERENCES member (member_id) ON DELETE CASCADE ON UPDATE CASCADE
) WITHOUT OIDS;
CREATE INDEX idx_member_login_history_member_id ON member_login_history( member_id );
CREATE INDEX idx_member_login_history_login_date ON member_login_history( login_date );
CREATE INDEX idx_member_login_history_login_result ON member_login_history( login_result );

-------------------------------------------------
-- member_point_history
-------------------------------------------------
CREATE TABLE member_point_history (
	history_number BIGSERIAL NOT NULL
	,member_id VARCHAR(16) NOT NULL
	,action_date TIMESTAMP NOT NULL
	,action_point INTEGER NOT NULL
	,action_label VARCHAR(255) NULL
	,action_detail TEXT NULL
	,remote_address VARCHAR(16) NULL
	,remote_host VARCHAR(255) NULL
	,user_agent TEXT NULL
	,owner_id VARCHAR(16) NULL
	,modifier_id VARCHAR(16) NULL
	,CONSTRAINT member_point_history_history_number PRIMARY KEY (history_number)
	,FOREIGN KEY (member_id) REFERENCES member (member_id) ON DELETE CASCADE ON UPDATE CASCADE
) WITHOUT OIDS;
CREATE INDEX idx_member_point_history_member_id ON member_point_history( member_id );
CREATE INDEX idx_member_point_history_action_date ON member_point_history( action_date );


---- mail
-------------------------------------------------
-- member_mail_template
-- open_flag: 0=ALL, 10=Group, 20=Personal
-------------------------------------------------
CREATE TABLE member_mail_template (
	template_number SERIAL NOT NULL
	,template_id VARCHAR(16) NOT NULL
	,template_name VARCHAR(255) NOT NULL
	,template_type SMALLINT NOT NULL DEFAULT 0
	,bgcolor VARCHAR(8) NULL
	,list_id VARCHAR(16) NULL
	,subject VARCHAR(255) NULL
	,subject_mobile VARCHAR(255) NULL
	,mail_from_name VARCHAR(90) NULL
	,mail_from_address VARCHAR(80) NULL
	,mail_reply_address VARCHAR(80) NULL
	,mail_return_path VARCHAR(80) NULL
	,text_body_default TEXT NULL
	,text_body_mobile TEXT NULL
	,html_body_default TEXT NULL
	,html_body_mobile TEXT NULL
	,description TEXT NULL
	,status_flag SMALLINT NOT NULL DEFAULT 0
	,open_flag SMALLINT NOT NULL DEFAULT 0
	,updated_date TIMESTAMP NULL
	,registered_date TIMESTAMP NULL
	,owner_id VARCHAR(16) NULL
	,modifier_id VARCHAR(16) NULL
	,CONSTRAINT member_mail_template_template_number PRIMARY KEY (template_number)
	,CONSTRAINT member_mail_template_template_id UNIQUE (template_id)
) WITHOUT OIDS;
CREATE INDEX idx_member_mail_template_template_id ON member_mail_template( template_id );
CREATE INDEX idx_member_mail_template_status_flag ON member_mail_template( status_flag );
CREATE INDEX idx_member_mail_template_updated_date ON member_mail_template( updated_date );
CREATE INDEX idx_member_mail_template_registered_date ON member_mail_template( registered_date );
-------------------------------------------------
-- member_mail_template_history
-------------------------------------------------
CREATE TABLE member_mail_template_history (
	history_number BIGSERIAL NOT NULL
	,history_id VARCHAR(18) NOT NULL
	,history_date TIMESTAMP NOT NULL
	,template_number INTEGER NULL
	,template_id VARCHAR(16) NULL
	,template_name VARCHAR(255) NULL
	,template_type SMALLINT NULL
	,bgcolor VARCHAR(8) NULL
	,list_id VARCHAR(16) NULL
	,subject VARCHAR(255) NULL
	,subject_mobile VARCHAR(255) NULL
	,mail_from_name VARCHAR(90) NULL
	,mail_from_address VARCHAR(80) NULL
	,mail_reply_address VARCHAR(80) NULL
	,mail_return_path VARCHAR(80) NULL
	,text_body_default TEXT NULL
	,text_body_mobile TEXT NULL
	,html_body_default TEXT NULL
	,html_body_mobile TEXT NULL
	,description TEXT NULL
	,status_flag SMALLINT NULL
	,open_flag SMALLINT NULL
	,updated_date TIMESTAMP NULL
	,registered_date TIMESTAMP NULL
	,owner_id VARCHAR(16) NULL
	,modifier_id VARCHAR(16) NULL
	,CONSTRAINT member_mail_template_history_history_number PRIMARY KEY (history_number)
	,CONSTRAINT member_mail_template_history_history_id UNIQUE (history_id)
) WITHOUT OIDS;
CREATE INDEX idx_member_mail_template_history_template_id ON member_mail_template_history( template_id );
-------------------------------------------------
-- member_mail_reject
-- status_flag: 0=新規状態, 1=利用可能
--              255=強制削除済み
-------------------------------------------------
CREATE TABLE member_mail_reject (
	list_number SERIAL NOT NULL
	,list_id VARCHAR(16) NOT NULL
	,list_name VARCHAR(180) NOT NULL
	,description VARCHAR(255) NULL
	,status_flag SMALLINT NOT NULL DEFAULT 1
	,updated_date TIMESTAMP NULL
	,registered_date TIMESTAMP NULL
	,owner_id VARCHAR(16) NULL
	,modifier_id VARCHAR(16) NULL
	,CONSTRAINT member_mail_reject_list_number PRIMARY KEY (list_number)
	,CONSTRAINT member_mail_reject_id UNIQUE (list_id)
) WITHOUT OIDS;
CREATE INDEX idx_member_mail_reject_id ON member_mail_reject( list_id );
CREATE INDEX idx_member_mail_reject_list_name ON member_mail_reject( list_name );
CREATE INDEX idx_member_mail_reject_status_flag ON member_mail_reject( status_flag );
CREATE INDEX idx_member_mail_reject_updated_date ON member_mail_reject( updated_date );
CREATE INDEX idx_member_mail_reject_registered_date ON member_mail_reject( registered_date );
-------------------------------------------------
-- member_mail_reject_history
-------------------------------------------------
CREATE TABLE member_mail_reject_history (
	history_number BIGSERIAL NOT NULL
	,history_id VARCHAR(18) NOT NULL
	,history_date TIMESTAMP NOT NULL
	,list_number INTEGER NULL
	,list_id VARCHAR(16) NULL
	,list_name VARCHAR(180) NULL
	,description VARCHAR(255) NULL
	,status_flag SMALLINT NULL
	,updated_date TIMESTAMP NULL
	,registered_date TIMESTAMP NULL
	,owner_id VARCHAR(16) NULL
	,modifier_id VARCHAR(16) NULL
	,CONSTRAINT member_mail_reject_history_history_number PRIMARY KEY (history_number)
	,CONSTRAINT member_mail_reject_history_history_id UNIQUE (history_id)
	,FOREIGN KEY (list_number) REFERENCES member_mail_reject (list_number) ON DELETE CASCADE ON UPDATE CASCADE
	,FOREIGN KEY (list_id) REFERENCES member_mail_reject (list_id) ON DELETE CASCADE ON UPDATE CASCADE
) WITHOUT OIDS;
CREATE INDEX idx_member_mail_reject_history_list_id ON member_mail_reject_history( list_id );
-------------------------------------------------
-- member_mail_reject_member
-------------------------------------------------
CREATE TABLE member_mail_reject_member (
	reject_number BIGSERIAL NOT NULL
	,list_id VARCHAR(16) NOT NULL
	,member_id VARCHAR(16) NOT NULL
	,CONSTRAINT member_mail_reject_member_reject_number PRIMARY KEY (reject_number)
	,FOREIGN KEY (list_id) REFERENCES member_mail_reject (list_id) ON DELETE CASCADE ON UPDATE CASCADE
	,FOREIGN KEY (member_id) REFERENCES member (member_id) ON DELETE CASCADE ON UPDATE CASCADE
) WITHOUT OIDS;
CREATE INDEX idx_member_mail_reject_member_list_id ON member_mail_reject_member( list_id );
CREATE INDEX idx_member_mail_reject_member_member_id ON member_mail_reject_member( member_id );
CREATE INDEX idx_member_mail_reject_member_list_member ON member_mail_reject_member( list_id,member_id );
-------------------------------------------------
-- member_mail_reject_member_history
-------------------------------------------------
CREATE TABLE member_mail_reject_member_history (
	history_number BIGSERIAL NOT NULL
	,history_id VARCHAR(18) NOT NULL
	,list_id VARCHAR(16) NOT NULL
	,member_id VARCHAR(16) NOT NULL
	,CONSTRAINT member_mail_reject_member_history_history_number PRIMARY KEY (history_number)
	,FOREIGN KEY (history_id) REFERENCES member_history(history_id) ON DELETE CASCADE ON UPDATE CASCADE
) WITHOUT OIDS;
CREATE INDEX idx_member_mail_reject_member_history_list_id ON member_mail_reject_member_history( list_id );
CREATE INDEX idx_member_mail_reject_member_history_member_id ON member_mail_reject_member_history( member_id );

-------------------------------------------------
-- member_mail
-- status_flag: 0=配信準備中, 1=配信準備済み, 2=配信済み
--              255=強制削除済み
-- select_type: q=QUERY STATEMENT
--              s=SELECTED LIST
--              i=member_id
-------------------------------------------------
CREATE TABLE member_mail (
	mail_number BIGSERIAL NOT NULL
	,mail_id VARCHAR(24) NOT NULL
	,target_type CHAR(1) NOT NULL DEFAULT 'p'
	,mail_type CHAR(1) NOT NULL DEFAULT 'p'
	,bgcolor VARCHAR(8) NULL
	,list_id VARCHAR(16) NULL
	,template_id VARCHAR(16) NULL
	,subject VARCHAR(255) NOT NULL
	,mail_from_name VARCHAR(90) NOT NULL
	,mail_from_address VARCHAR(80) NOT NULL
	,mail_reply_address VARCHAR(80) NULL
	,mail_return_path VARCHAR(80) NULL
	,body TEXT NOT NULL
	,header TEXT NULL
	,select_type CHAR(1) NOT NULL DEFAULT 'q'
	,select_condition TEXT NULL
	,total_count INTEGER NOT NULL DEFAULT 0
	,list_count INTEGER NOT NULL DEFAULT 0
	,status_flag SMALLINT NOT NULL DEFAULT 0
	,add_correspond SMALLINT NULL
	,correspond_status SMALLINT NULL
	,correspond_type SMALLINT NULL
	,correspond_category VARCHAR(16) NULL
	,send_params TEXT NULL
	,reserve_date TIMESTAMP NULL
	,completed_date TIMESTAMP NULL
	,updated_date TIMESTAMP NULL
	,registered_date TIMESTAMP NULL
	,owner_id VARCHAR(16) NULL
	,modifier_id VARCHAR(16) NULL
	,CONSTRAINT member_mail_mail_number PRIMARY KEY (mail_number)
	,CONSTRAINT member_mail_mail_id UNIQUE (mail_id)
) WITHOUT OIDS;
CREATE INDEX idx_member_mail_mail_id ON member_mail( mail_id );
CREATE INDEX idx_member_mail_target_type ON member_mail( target_type );
CREATE INDEX idx_member_mail_status_flag ON member_mail( status_flag );
CREATE INDEX idx_member_mail_reserve_date ON member_mail( reserve_date );
CREATE INDEX idx_member_mail_registered_date ON member_mail( registered_date );
-------------------------------------------------
-- member_mail_history
-------------------------------------------------
CREATE TABLE member_mail_history (
	history_number BIGSERIAL NOT NULL
	,history_id VARCHAR(16) NOT NULL
	,history_date TIMESTAMP NOT NULL
	,mail_number BIGINT NULL
	,mail_id VARCHAR(24) NULL
	,target_type CHAR(1) NULL
	,mail_type CHAR(1) NULL
	,bgcolor VARCHAR(8) NULL
	,list_id VARCHAR(16) NULL
	,template_id VARCHAR(16) NULL
	,subject VARCHAR(255) NULL
	,mail_from_name VARCHAR(90) NULL
	,mail_from_address VARCHAR(80) NULL
	,mail_reply_address VARCHAR(80) NULL
	,mail_return_path VARCHAR(80) NULL
	,body TEXT NULL
	,header TEXT NULL
	,select_type CHAR(1) NULL
	,select_condition TEXT NULL
	,total_count INTEGER NULL
	,list_count INTEGER NULL
	,status_flag SMALLINT NULL
	,add_correspond SMALLINT NULL
	,correspond_status SMALLINT NULL
	,correspond_type SMALLINT NULL
	,correspond_category VARCHAR(16) NULL
	,send_params TEXT NULL
	,reserve_date TIMESTAMP NULL
	,completed_date TIMESTAMP NULL
	,updated_date TIMESTAMP NULL
	,registered_date TIMESTAMP NULL
	,owner_id VARCHAR(16) NULL
	,modifier_id VARCHAR(16) NULL
	,CONSTRAINT member_mail_history_history_number PRIMARY KEY (history_number)
	,CONSTRAINT member_mail_history_history_id UNIQUE (history_id)
	,FOREIGN KEY (mail_id) REFERENCES member_mail (mail_id) ON DELETE CASCADE ON UPDATE CASCADE
) WITHOUT OIDS;
CREATE INDEX idx_member_mail_history_mail_id ON member_mail_history( mail_id );

-------------------------------------------------
-- member_mail_log
-- delivery_status: 0=送信準備, 1=正常送信済み
--   101=メールアドレス登録なし
--   102=送信前に退会
--   103=メールマガジン種別によりキャンセル
--   104=指定回数以上エラーが発生しているので配信キャンセル
--   105=配信済みに重複していたので配信キャンセル
--   201=送信エラー
--   202=メール本文読み込み失敗
--   203=会員情報読み込み失敗
--   204=送信情報未設定
--   205=メールマガジン種別内容問い合わせ失敗
--   211=メールリターン
--   251=SQL問い合わせエラー
--   254=システム設定エラー
--   255=配信前に強制削除
-- modifier_id: 強制削除時のみ削除管理者ID
-------------------------------------------------
CREATE TABLE member_mail_log (
	log_number BIGSERIAL NOT NULL
	,mail_id VARCHAR(24) NOT NULL
	,member_id VARCHAR(16) NOT NULL
	,delivery_address VARCHAR(80) NULL
	,delivery_status SMALLINT NOT NULL DEFAULT 0
	,delivery_log VARCHAR(255) NULL
	,delivery_date TIMESTAMP NULL
	,CONSTRAINT member_mail_log_log_number PRIMARY KEY (log_number)
	,FOREIGN KEY (mail_id) REFERENCES member_mail (mail_id) ON DELETE CASCADE ON UPDATE CASCADE
	,FOREIGN KEY (member_id) REFERENCES member (member_id) ON DELETE CASCADE ON UPDATE CASCADE
) WITHOUT OIDS;
CREATE INDEX idx_member_mail_log_mail_id ON member_mail_log( mail_id );
CREATE INDEX idx_member_mail_log_member_id ON member_mail_log( member_id );

-------------------------------------------------
-- member_correspond
-- correspond_type : 対応開始の種別
--     0=自己発信, 10=お客様発信
-- correspond_method : 対応方法
--     0=メール, 10=電話, 20=FAX, 30=郵送, 100=訪問, 110=来社, 200=その他
-- category_id: 対応カテゴリ
--     当初はpackage_define.inc.phpに記述
-- status_flag: 全体ステータス
--     0=新規, 11=要次対応, 50=回答待ち, 101=完了, 255=削除
-------------------------------------------------
CREATE TABLE member_correspond (
	correspond_number BIGSERIAL NOT NULL
	,correspond_id VARCHAR(24) NOT NULL
	,member_id VARCHAR(16) NOT NULL
	,correspond_type SMALLINT NOT NULL DEFAULT 0
	,correspond_method SMALLINT NOT NULL DEFAULT 0
	,category_id VARCHAR(16) NULL
	,description VARCHAR(255) NULL
	,detail_note TEXT NULL
	,status_flag SMALLINT NOT NULL DEFAULT 0
	,start_date TIMESTAMP NULL
	,correspond_date TIMESTAMP NULL
	,updated_date TIMESTAMP NULL
	,registered_date TIMESTAMP NULL
	,owner_id VARCHAR(16) NULL
	,modifier_id VARCHAR(16) NULL
	,CONSTRAINT member_correspond_correspond_number PRIMARY KEY (correspond_number)
	,CONSTRAINT member_correspond_correspond_id UNIQUE (correspond_id)
	,FOREIGN KEY (member_id) REFERENCES member (member_id) ON DELETE CASCADE ON UPDATE CASCADE
) WITHOUT OIDS;
CREATE INDEX idx_member_correspond_member_id ON member_correspond( member_id );
CREATE INDEX idx_member_correspond_status_flag ON member_correspond( status_flag );
CREATE INDEX idx_member_correspond_member_id_status_flag ON member_correspond( member_id,status_flag );
CREATE INDEX idx_member_correspond_updated_date ON member_correspond( updated_date );
CREATE INDEX idx_member_correspond_registered_date ON member_correspond( registered_date );

-------------------------------------------------
-- member_correspond_comment
-- status_flag : 状態フラグは削除のみ管理し、変更のたび対応テーブルの状態フラグを変更できるようにする101 or 255
-------------------------------------------------
CREATE TABLE member_correspond_comment (
	comment_number BIGSERIAL NOT NULL
	,correspond_id VARCHAR(24) NOT NULL
	,comment_note TEXT NULL
	,status_flag SMALLINT NOT NULL DEFAULT 101
	,correspond_date TIMESTAMP NULL
	,updated_date TIMESTAMP NULL
	,registered_date TIMESTAMP NULL
	,owner_id VARCHAR(16) NULL
	,modifier_id VARCHAR(16) NULL
	,CONSTRAINT member_correspond_comment_comment_number PRIMARY KEY (comment_number)
	,FOREIGN KEY (correspond_id) REFERENCES member_correspond (correspond_id) ON DELETE CASCADE ON UPDATE CASCADE
) WITHOUT OIDS;
CREATE INDEX idx_member_correspond_comment_correspond_id ON member_correspond_comment( correspond_id );
CREATE INDEX idx_member_correspond_comment_status_flag ON member_correspond_comment( status_flag );
CREATE INDEX idx_member_correspond_comment_correspond_date ON member_correspond_comment( correspond_date );
CREATE INDEX idx_member_correspond_comment_correspond_id_correspond_date ON member_correspond_comment( correspond_id,correspond_date );

-------------------------------------------------
-- member_error_count
-------------------------------------------------
CREATE TABLE member_error_count (
	error_number BIGSERIAL NOT NULL
	,member_id VARCHAR(16) NOT NULL
	,login_error_count SMALLINT NOT NULL DEFAULT 0
	,login_error_date TIMESTAMP NULL
	,pc_mail_error_count SMALLINT NOT NULL DEFAULT 0
	,pc_mail_error_date TIMESTAMP NULL
	,mb_mail_error_count SMALLINT NOT NULL DEFAULT 0
	,mb_mail_error_date TIMESTAMP NULL
	,CONSTRAINT member_error_count_error_number PRIMARY KEY (error_number)
	,FOREIGN KEY (member_id) REFERENCES member (member_id) ON DELETE CASCADE ON UPDATE CASCADE
) WITHOUT OIDS;

-------------------------------------------------
-- member_bank_account
-- status_flag : 状態フラグ： 0=新規, 101=稼働中, 255=削除済
-------------------------------------------------
CREATE TABLE member_bank_account (
	account_number BIGSERIAL NOT NULL
	,account_id VARCHAR(16) NOT NULL
	,member_id VARCHAR(16) NOT NULL
	,order_number SMALLINT NOT NULL DEFAULT 0
	,status_flag SMALLINT NOT NULL DEFAULT 0
	,bank_code VARCHAR(8) NULL
	,bank_name VARCHAR(255) NULL
	,bank_kana VARCHAR(255) NULL
	,bank_branch_code VARCHAR(16) NULL
	,bank_branch_name VARCHAR(255) NULL
	,bank_branch_kana VARCHAR(255) NULL
	,bank_account_type SMALLINT NULL
	,bank_account_code VARCHAR(16) NULL
	,bank_account_name VARCHAR(255) NULL
	,bank_account_kana VARCHAR(255) NULL
	,updated_date TIMESTAMP NULL
	,registered_date TIMESTAMP NULL
	,owner_id VARCHAR(16) NULL
	,modifier_id VARCHAR(16) NULL
	,CONSTRAINT member_bank_account_account_number PRIMARY KEY (account_number)
	,CONSTRAINT member_bank_account_account_id UNIQUE (account_id)
	,FOREIGN KEY (member_id) REFERENCES member (member_id) ON DELETE CASCADE ON UPDATE CASCADE
) WITHOUT OIDS;
CREATE INDEX idx_member_bank_account_account_id ON member_bank_account( account_id );
CREATE INDEX idx_member_bank_account_member_id ON member_bank_account( member_id );
CREATE INDEX idx_member_bank_account_order_number ON member_bank_account( order_number );
CREATE INDEX idx_member_bank_account_updated_date ON member_bank_account( updated_date );
CREATE INDEX idx_member_bank_account_registered_date ON member_bank_account( registered_date );
