-------------------------------------------------
-- shopping 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-18
-------------------------------------------------
-- shopping_category
-------------------------------------------------
CREATE TABLE shopping_category (
	category_number INTEGER NOT NULL AUTO_INCREMENT
	,category_id VARCHAR(16) NOT NULL
	,parent_id VARCHAR(16) NULL
	,category_name VARCHAR(64) NOT NULL
	,category_kana VARCHAR(80) NULL
	,status_flag SMALLINT NOT NULL DEFAULT 0
	,order_number INTEGER NOT NULL DEFAULT 0
	,description TEXT NULL
	,updated_date DATETIME NULL
	,registered_date DATETIME NULL
	,owner_id VARCHAR(16) NULL
	,modifier_id VARCHAR(16) NULL
	,CONSTRAINT shopping_category_category_number PRIMARY KEY (category_number)
	,CONSTRAINT shopping_category_category_id UNIQUE (category_id)
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX idx_shopping_category_category_id ON shopping_category( category_id );
CREATE INDEX idx_shopping_category_parent_id ON shopping_category( parent_id );
CREATE INDEX idx_shopping_category_category_name ON shopping_category( category_name );
CREATE INDEX idx_shopping_category_category_kana ON shopping_category( category_kana );
CREATE INDEX idx_shopping_category_order_number ON shopping_category( order_number );
CREATE INDEX idx_shopping_category_updated_date ON shopping_category( updated_date );
CREATE INDEX idx_shopping_category_registered_date ON shopping_category( registered_date );

-------------------------------------------------
-- shopping_product
-- status_flag: 0=準備中, 91=非公開, 101=公開中, 255=削除
-- delivery_type: 0=通常便, 1=クール便, 11=ダウンロード商品, 12=サービス, 21～=継続サービス(月単位1プラス)
-- unit_string: 表記カウント単位（例:個,枚,杯..等)
-- calendar_type: 1=個別カレンダーを利用, 左記以外=全体カレンダーを利用
-------------------------------------------------
CREATE TABLE shopping_product (
	product_number INTEGER NOT NULL AUTO_INCREMENT
	,product_id VARCHAR(32) NOT NULL
	,product_name VARCHAR(255) NOT NULL
	,product_kana VARCHAR(255) NULL
	,status_flag SMALLINT NOT NULL DEFAULT 0
	,delivery_type SMALLINT NOT NULL DEFAULT 0
	,member_viewable INTEGER NOT NULL DEFAULT -1
	,member_purchasable INTEGER NOT NULL DEFAULT -1
	,order_number INTEGER NOT NULL DEFAULT 0
	,keywords VARCHAR(255) NULL
	,catch_copy VARCHAR(255) NULL
	,description TEXT NULL
	,detail_comment TEXT NULL
	,wholesaler_name VARCHAR(255) NULL
	,wholesaler_email VARCHAR(80) NULL
	,wholesaler_note TEXT NULL
	,product_url VARCHAR(128) NULL
	,unit_string VARCHAR(64) NULL
	,option1_name VARCHAR(180) NULL
	,option1_items TEXT NULL
	,option1_explanation TEXT NULL
	,option1_url VARCHAR(180) NULL
	,option1_calculate SMALLINT NOT NULL DEFAULT 0
	,option2_name VARCHAR(180) NULL
	,option2_items TEXT NULL
	,option2_explanation TEXT NULL
	,option2_url VARCHAR(180) NULL
	,option2_calculate SMALLINT NOT NULL DEFAULT 0
	,option3_name VARCHAR(180) NULL
	,option3_items TEXT NULL
	,option3_explanation TEXT NULL
	,option3_url VARCHAR(180) NULL
	,option3_calculate SMALLINT NOT NULL DEFAULT 0
	,option4_name VARCHAR(180) NULL
	,option4_items TEXT NULL
	,option4_explanation TEXT NULL
	,option4_url VARCHAR(180) NULL
	,option4_calculate SMALLINT NOT NULL DEFAULT 0
	,option5_name VARCHAR(180) NULL
	,option5_items TEXT NULL
	,option5_explanation TEXT NULL
	,option5_url VARCHAR(180) NULL
	,option5_calculate SMALLINT NOT NULL DEFAULT 0
	,updated_date DATETIME NULL
	,registered_date DATETIME NULL
	,calendar_type SMALLINT NULL
	,search_data_id VARCHAR(32) NULL
	,owner_id VARCHAR(16) NULL
	,modifier_id VARCHAR(16) NULL
	,CONSTRAINT shopping_product_product_number PRIMARY KEY (product_number)
	,CONSTRAINT shopping_product_product_id UNIQUE (product_id)
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX idx_shopping_product_product_id ON shopping_product( product_id );
CREATE INDEX idx_shopping_product_product_name ON shopping_product( product_name );
CREATE INDEX idx_shopping_product_product_kana ON shopping_product( product_kana );
CREATE INDEX idx_shopping_product_order_number ON shopping_product( order_number );
CREATE INDEX idx_shopping_product_status_flag ON shopping_product( status_flag );
CREATE INDEX idx_shopping_product_updated_date ON shopping_product( updated_date );
CREATE INDEX idx_shopping_product_registered_date ON shopping_product( registered_date );
CREATE INDEX idx_shopping_product_search_data_id ON shopping_product( search_data_id );
CREATE INDEX idx_shopping_product_owner_id ON shopping_product( owner_id );
CREATE INDEX idx_shopping_product_modifier_id ON shopping_product( modifier_id );

-------------------------------------------------
-- shopping_category_product
-------------------------------------------------
CREATE TABLE shopping_category_product (
	relation_number BIGINT NOT NULL AUTO_INCREMENT
	,category_id VARCHAR(16) NOT NULL
	,product_id VARCHAR(32) NOT NULL
	,order_number INTEGER NOT NULL DEFAULT 0
	,CONSTRAINT shopping_category_product_relation_number PRIMARY KEY (relation_number)
	,FOREIGN KEY (product_id) REFERENCES shopping_product (product_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX idx_shopping_category_product_category_id ON shopping_category_product( category_id );
CREATE INDEX idx_shopping_category_product_product_id ON shopping_category_product( product_id );
CREATE INDEX idx_shopping_category_product_order_number ON shopping_category_product( order_number );

-----------------------------------------------------------
-- shopping_product_file
-- file_class: 0=画像, 1=pdf, 2=flv
-----------------------------------------------------------
CREATE TABLE shopping_product_file (
	relation_number BIGINT NOT NULL AUTO_INCREMENT
	,product_id VARCHAR(32) NOT NULL
	,file_id VARCHAR(24) NOT NULL
	,file_class SMALLINT NOT NULL DEFAULT 0
	,order_number INTEGER UNSIGNED NOT NULL DEFAULT 0
	,CONSTRAINT shopping_product_file_relation_number PRIMARY KEY (relation_number)
	,FOREIGN KEY (product_id) REFERENCES shopping_product (product_id) ON DELETE CASCADE ON UPDATE CASCADE
	,FOREIGN KEY (file_id) REFERENCES file_bin (file_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX idx_shopping_product_file_product_id ON shopping_product_file( product_id );
CREATE INDEX idx_shopping_product_file_file_id ON shopping_product_file( file_id );
CREATE INDEX idx_shopping_product_file_file_class ON shopping_product_file( file_class );
CREATE INDEX idx_shopping_product_file_order_number ON shopping_product_file( order_number );
CREATE INDEX idx_shopping_product_file_product_id_order_number ON shopping_product_file( product_id, order_number );

-------------------------------------------------
-- shopping_product_relation
-------------------------------------------------
CREATE TABLE shopping_product_relation (
	relation_number BIGINT NOT NULL AUTO_INCREMENT
	,product_id VARCHAR(32) NOT NULL
	,related_product_id VARCHAR(32) NOT NULL
	,display_order SMALLINT NOT NULL DEFAULT 0
	,CONSTRAINT shopping_product_relation_relation_number PRIMARY KEY (relation_number)
	,CONSTRAINT shopping_product_product_id UNIQUE (product_id,related_product_id)
	,FOREIGN KEY (product_id) REFERENCES shopping_product (product_id) ON DELETE CASCADE ON UPDATE CASCADE
	,FOREIGN KEY (related_product_id) REFERENCES shopping_product (product_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX idx_shopping_product_relation_product_id ON shopping_product_relation( product_id );
CREATE INDEX idx_shopping_product_relation_related_product_id ON shopping_product_relation( related_product_id );
CREATE INDEX idx_shopping_product_relation_display_order ON shopping_product_relation( display_order );
CREATE INDEX idx_shopping_product_relation_product_id_order ON shopping_product_relation( product_id, display_order );
CREATE INDEX idx_shopping_product_relation_related_id_order ON shopping_product_relation( related_product_id, display_order );

-------------------------------------------------
-- shopping_product_unit
-- unit_string: 表記カウント単位（例:個,枚,杯..等)
-- price_standard: メーカー希望小売価格
-- price_sell: 販売価格
-- price_monthly: 月次課金価格
-- cost: 仕入れ値/仕切り値
-- stock: 0=在庫なし, 正の数=在庫数, -1=在庫無制限, -2=最小在庫連動, -3=最大在庫連動, -4=合計在庫連動
-- stock_relation: 在庫連動販売単位（/区切り）
-- discount_exception: 通常0, プロモーション割引の除外品とする場合1
-- delivery_type: 0=通常便, 1=クール便, 11=ダウンロード商品, 12=サービス, 21～=継続サービス(月単位1プラス)
-- delivery_weight: 送料重量(サイズも考慮して設定）
-- rsv_permit_start: 予約開始可能日（本日から○営業日後）
-- rsv_permit_limit: 予約可能終了日（本日から○日後）
-- default_start_time: チェックイン時間
-- service_term: サービス提供期間（秒数で入力）
-- calendar_type: 1=個別カレンダーを利用, 2=商品カレンダーを利用, 左記以外=全体カレンダーを利用
-------------------------------------------------
CREATE TABLE shopping_product_unit (
	unit_number INTEGER NOT NULL AUTO_INCREMENT
	,unit_id VARCHAR(32) NOT NULL
	,product_id VARCHAR(32) NOT NULL
	,require_unit_id VARCHAR(32) NULL
	,unit_name VARCHAR(255) NOT NULL
	,unit_kana VARCHAR(255) NULL
	,unit_string VARCHAR(64) NULL
	,member_viewable INTEGER NOT NULL DEFAULT -1
	,member_purchasable INTEGER NOT NULL DEFAULT -1
	,price_standard INTEGER NOT NULL DEFAULT 0
	,price_sell INTEGER NOT NULL DEFAULT 0
	,price_monthly INTEGER NOT NULL DEFAULT 0
	,cost INTEGER NOT NULL DEFAULT 0
	,stock INTEGER NOT NULL DEFAULT 0
	,stock_relation TEXT NULL
	,stock_relation_count INTEGER NULL
	,stock_display INTEGER NULL
	,discount_exception SMALLINT NULL
	,present_point INTEGER NULL
	,description TEXT NULL
	,detail_comment TEXT NULL
	,option1_name VARCHAR(180) NULL
	,option1_items TEXT NULL
	,option1_explanation TEXT NULL
	,option1_url VARCHAR(180) NULL
	,option1_calculate SMALLINT NOT NULL DEFAULT 0
	,option2_name VARCHAR(180) NULL
	,option2_items TEXT NULL
	,option2_explanation TEXT NULL
	,option2_url VARCHAR(180) NULL
	,option2_calculate SMALLINT NOT NULL DEFAULT 0
	,option3_name VARCHAR(180) NULL
	,option3_items TEXT NULL
	,option3_explanation TEXT NULL
	,option3_url VARCHAR(180) NULL
	,option3_calculate SMALLINT NOT NULL DEFAULT 0
	,option4_name VARCHAR(180) NULL
	,option4_items TEXT NULL
	,option4_explanation TEXT NULL
	,option4_url VARCHAR(180) NULL
	,option4_calculate SMALLINT NOT NULL DEFAULT 0
	,option5_name VARCHAR(180) NULL
	,option5_items TEXT NULL
	,option5_explanation TEXT NULL
	,option5_url VARCHAR(180) NULL
	,option5_calculate SMALLINT NOT NULL DEFAULT 0
	,status_flag SMALLINT NOT NULL DEFAULT 0
	,open_date DATETIME NULL
	,close_date DATETIME NULL
	,order_number SMALLINT NOT NULL DEFAULT 0
	,delivery_type SMALLINT NOT NULL DEFAULT 0
	,delivery_weight INTEGER NOT NULL DEFAULT 1
	,max_at_once SMALLINT NULL
	,rsv_permit_start SMALLINT NULL
	,rsv_permit_limit SMALLINT NULL
	,default_start_time TIME NULL
	,service_term INTEGER NULL
	,calendar_type SMALLINT NULL
	,updated_date DATETIME NULL
	,registered_date DATETIME NULL
	,owner_id VARCHAR(16) NULL
	,modifier_id VARCHAR(16) NULL
	,CONSTRAINT shopping_product_unit_unit_number PRIMARY KEY (unit_number)
	,CONSTRAINT shopping_product_unit_unit_id UNIQUE (unit_id)
	,FOREIGN KEY (product_id) REFERENCES shopping_product (product_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX idx_shopping_product_unit_product_id ON shopping_product_unit( product_id );
CREATE INDEX idx_shopping_product_unit_unit_name ON shopping_product_unit( unit_name );
CREATE INDEX idx_shopping_product_unit_unit_kana ON shopping_product_unit( unit_kana );
CREATE INDEX idx_shopping_product_unit_option_name ON shopping_product_unit( option_name );
CREATE INDEX idx_shopping_product_unit_option_kana ON shopping_product_unit( option_kana );
CREATE INDEX idx_shopping_product_unit_order_number ON shopping_product_unit( order_number );
CREATE INDEX idx_shopping_product_unit_status_flag ON shopping_product_unit( status_flag );
CREATE INDEX idx_shopping_product_unit_updated_date ON shopping_product_unit( updated_date );
CREATE INDEX idx_shopping_product_unit_registered_date ON shopping_product_unit( registered_date );
CREATE INDEX idx_shopping_product_unit_open_date ON shopping_product_unit( open_date );
CREATE INDEX idx_shopping_product_unit_close_date ON shopping_product_unit( close_date );
CREATE INDEX idx_shopping_product_unit_owner_id ON shopping_product_unit( owner_id );
CREATE INDEX idx_shopping_product_unit_id_owner_id ON shopping_product_unit( unit_id,owner_id );

-----------------------------------------------------------
-- shopping_product_unit_file
-- file_class: 0=画像, 1=pdf, 2=flv
-----------------------------------------------------------
CREATE TABLE shopping_product_unit_file (
	relation_number BIGINT NOT NULL AUTO_INCREMENT
	,unit_id VARCHAR(32) NOT NULL
	,file_id VARCHAR(24) NOT NULL
	,file_class SMALLINT NOT NULL DEFAULT 0
	,order_number INTEGER UNSIGNED NOT NULL DEFAULT 0
	,CONSTRAINT shopping_product_unit_file_relation_number PRIMARY KEY (relation_number)
	,FOREIGN KEY (unit_id) REFERENCES shopping_product_unit (unit_id) ON DELETE CASCADE ON UPDATE CASCADE
	,FOREIGN KEY (file_id) REFERENCES file_bin (file_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX idx_shopping_product_unit_file_unit_id ON shopping_product_unit_file( unit_id );
CREATE INDEX idx_shopping_product_unit_file_file_id ON shopping_product_unit_file( file_id );
CREATE INDEX idx_shopping_product_unit_file_file_class ON shopping_product_unit_file( file_class );
CREATE INDEX idx_shopping_product_unit_file_order_number ON shopping_product_unit_file( order_number );
CREATE INDEX idx_shopping_product_unit_file_unit_id_order_number ON shopping_product_unit_file( unit_id, order_number );

-------------------------------------------------
-- shopping_order : 注文テーブル
-- buying_flag: 仕入れフラグ 0=通常注文, 1=仕入れ注文
-- mail_flag: 注文メールの宛先種別,  0=送信しない, 1=PCメール, 2=携帯メール, 11=新規会員PCメール, 12=新規会員携帯メール
-- settle_method: 決済方法 0=代金引換, 10=銀行振り込み, 16=郵便振替, 20=クレジットカード
-- status_settle: 決済フラグ 0=未決済, 10=請求済み, （11-99でカード与信済みなどいれる予定）, 101=決済済み
-- status_promotion: プロモーション状態: 0=未確定, 10=承認済, 101=支払済, 201=キャンセル, 255=非証認
-------------------------------------------------
CREATE TABLE shopping_order (
	order_number BIGINT NOT NULL AUTO_INCREMENT
	,order_id VARCHAR(16) NOT NULL
	,member_id VARCHAR(16) NOT NULL
	,buying_flag SMALLINT NOT NULL DEFAULT 0
	,price_total INTEGER NOT NULL DEFAULT 0
	,promotion_fee INTEGER NOT NULL DEFAULT 0
	,discount_price INTEGER NOT NULL DEFAULT 0
	,discount_admin INTEGER NOT NULL DEFAULT 0
	,purchase_total INTEGER NOT NULL DEFAULT 0
	,payment_total INTEGER NOT NULL DEFAULT 0
	,cost_total INTEGER NOT NULL DEFAULT 0
	,delivery_price INTEGER NOT NULL DEFAULT 0
	,wrapping_price INTEGER NOT NULL DEFAULT 0
	,tax_rate SMALLINT NOT NULL DEFAULT 5
	,point_total INTEGER NOT NULL DEFAULT 0
	,point_use INTEGER NOT NULL DEFAULT 0
	,order_comment TEXT NULL
	,admin_note TEXT NULL
	,history_note TEXT NULL
	,mail_flag SMALLINT NOT NULL DEFAULT 1
	,status_flag SMALLINT NOT NULL DEFAULT 0
	,status_settle SMALLINT NOT NULL DEFAULT 0
	,settle_method SMALLINT NOT NULL DEFAULT 0
	,settle_price INTEGER NOT NULL DEFAULT 0
	,settle_fee INTEGER NOT NULL DEFAULT 0
	,settle_transaction_code VARCHAR(128) NULL
	,settle_log TEXT NULL
	,status_promotion SMALLINT NOT NULL DEFAULT 0
	,promotion_code VARCHAR(32) NULL
	,access_param TEXT NULL
	,order_date DATETIME NULL
	,settle_date DATETIME NULL
	,updated_date DATETIME NULL
	,registered_date DATETIME NULL
	,owner_id VARCHAR(16) NULL
	,modifier_id VARCHAR(16) NULL
	,user_agent TEXT NULL
	,sim_id VARCHAR(255) NULL
	,remote_host TEXT NULL
	,remote_address VARCHAR(16) NULL
	,referrer_url VARCHAR(255) NULL
	,order_key VARCHAR(64) NULL
	,search_data_id VARCHAR(32) NULL
	,CONSTRAINT shopping_order_order_number PRIMARY KEY (order_number)
	,CONSTRAINT shopping_order_order_id UNIQUE (order_id)
	,FOREIGN KEY (member_id) REFERENCES member (member_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX idx_shopping_order_member_id ON shopping_order( member_id );
CREATE INDEX idx_shopping_order_status_flag ON shopping_order( status_flag );
CREATE INDEX idx_shopping_order_updated_date ON shopping_order( updated_date );
CREATE INDEX idx_shopping_order_registered_date ON shopping_order( registered_date );
CREATE INDEX idx_shopping_order_order_date ON shopping_order( order_date );
CREATE INDEX idx_shopping_order_owner_id ON shopping_order( owner_id );
CREATE INDEX idx_shopping_order_search_data_id ON shopping_order( search_data_id );

-------------------------------------------------
-- shopping_order_address : 注文送付先情報テーブル
-------------------------------------------------
CREATE TABLE shopping_order_address (
	address_number BIGINT NOT NULL AUTO_INCREMENT
	,address_id VARCHAR(32) NOT NULL
	,order_id VARCHAR(16) NOT NULL
	,address_order SMALLINT NOT NULL DEFAULT 0
	,address_name VARCHAR(255) NULL
	,address_kana VARCHAR(255) NULL
	,company_name VARCHAR(255) NULL
	,section_name VARCHAR(255) NULL
	,zip_code VARCHAR(8) NULL
	,prefecture_number SMALLINT NULL
	,address VARCHAR(255) NULL
	,building VARCHAR(255) NULL
	,telephone_number VARCHAR(24) NULL
	,faximili_number VARCHAR(24) NULL
	,mobile_number VARCHAR(24) NULL
	,email VARCHAR(80) NULL
	,wrapping_type VARCHAR(128) NULL
	,wrapping_price INTEGER NOT NULL DEFAULT 0
	,wrapping_words1 VARCHAR(255) NULL
	,wrapping_words2 VARCHAR(255) NULL
	,wishful_date VARCHAR(64) NULL
	,wishful_time VARCHAR(64) NULL
	,delivery_price INTEGER NOT NULL DEFAULT 0
	,settle_fee INTEGER NOT NULL DEFAULT 0
	,order_comment TEXT NULL
	,delivery_code VARCHAR(180) NULL
	,CONSTRAINT shopping_order_address_address_number PRIMARY KEY (address_number)
	,CONSTRAINT shopping_order_address_address_id UNIQUE (address_id)
	,FOREIGN KEY (order_id) REFERENCES shopping_order (order_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX idx_shopping_order_address_order_id_order_number ON shopping_order( order_id, order_number );

-------------------------------------------------
-- shopping_order_item: 注文商品テーブル
-- used_unit_id: 在庫連動(合計)時に実際に利用する販売単位ID(予約商品のみ)
-------------------------------------------------
CREATE TABLE shopping_order_item (
	item_number BIGINT NOT NULL AUTO_INCREMENT
	,item_id VARCHAR(32) NOT NULL
	,order_id VARCHAR(16) NOT NULL
	,address_id VARCHAR(32) NOT NULL
	,unit_id VARCHAR(32) NOT NULL
	,price_unit INTEGER NOT NULL DEFAULT 0
	,cost_unit INTEGER NOT NULL DEFAULT 0
	,point_unit INTEGER NOT NULL DEFAULT 0
	,delivery_type SMALLINT NULL
	,delivery_weight INTEGER NULL
	,discount_exception SMALLINT NULL
	,svc_start_date DATETIME NULL
	,svc_limit_date DATETIME NULL
	,used_unit_id  VARCHAR(16) NULL
	,order_count INTEGER NOT NULL DEFAULT 1
	,CONSTRAINT shopping_order_item_item_number PRIMARY KEY (item_number)
	,CONSTRAINT shopping_order_item_item_id UNIQUE (item_id)
	,FOREIGN KEY (order_id) REFERENCES shopping_order (order_id) ON DELETE CASCADE ON UPDATE CASCADE
	,FOREIGN KEY (address_id) REFERENCES shopping_order_address (address_id) ON DELETE CASCADE ON UPDATE CASCADE
	,FOREIGN KEY (unit_id) REFERENCES shopping_product_unit (unit_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX idx_shopping_order_item_used_order_id ON shopping_order_item( order_id );
CREATE INDEX idx_shopping_order_item_used_unit_id ON shopping_order_item( used_unit_id );
CREATE INDEX idx_shopping_order_item_svc_start_date ON shopping_order_item( svc_start_date );
CREATE INDEX idx_shopping_order_item_svc_limit_date ON shopping_order_item( svc_limit_date );
CREATE INDEX idx_shopping_order_item_order_id_svc_start_date ON shopping_order_item( order_id,svc_start_date );
CREATE INDEX idx_shopping_order_item_order_id_svc_limit_date ON shopping_order_item( order_id,svc_limit_date );
CREATE INDEX idx_shopping_order_item_order_id_svc_start_limit ON shopping_order_item( order_id,svc_start_date,svc_limit_date );
CREATE INDEX idx_shopping_order_item_unit_id_svc_start_date ON shopping_order_item( unit_id,svc_start_date );
CREATE INDEX idx_shopping_order_item_unit_id_svc_limit_date ON shopping_order_item( unit_id,svc_limit_date );
CREATE INDEX idx_shopping_order_item_unit_id_svc_start_limit ON shopping_order_item( unit_id,svc_start_date,svc_limit_date );

-------------------------------------------------
-- shopping_order_option: 注文オプションテーブル
-------------------------------------------------
CREATE TABLE shopping_order_option (
	option_number BIGINT NOT NULL AUTO_INCREMENT
	,order_id VARCHAR(16) NOT NULL
	,address_id VARCHAR(32) NULL
	,address_order SMALLINT NULL
	,item_id VARCHAR(32) NULL
	,item_order SMALLINT NULL
	,option_name VARCHAR(255) NOT NULL
	,option_value TEXT NULL
	,option_class VARCHAR(255) NULL
	,option_price INTEGER NOT NULL DEFAULT 0
	,option_cost INTEGER NOT NULL DEFAULT 0
	,order_count INTEGER NOT NULL DEFAULT 1
	,prices TEXT NULL
	,costs TEXT NULL
	,svc_start_date DATETIME NULL
	,svc_limit_date DATETIME NULL
	,CONSTRAINT shopping_order_item_item_number PRIMARY KEY (option_number)
	,FOREIGN KEY (order_id) REFERENCES shopping_order (order_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX idx_shopping_order_option_item_id ON shopping_order_option( item_id );

-------------------------------------------------
-- shopping_order_history: 注文対応履歴テーブル
-------------------------------------------------
CREATE TABLE shopping_order_history (
	history_number BIGINT NOT NULL AUTO_INCREMENT
	,history_date DATETIME NULL
	,order_id VARCHAR(16) NOT NULL
	,subject VARCHAR(255) NULL
	,description TEXT NULL
	,operation_class SMALLINT NULL
	,template_id VARCHAR(16) NULL
	,CONSTRAINT shopping_order_history_history_number PRIMARY KEY (history_number)
	,FOREIGN KEY (order_id) REFERENCES shopping_order (order_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX idx_shopping_order_history_template_id ON shopping_order_history( template_id );

-------------------------------------------------
-- shopping_delivery_setting: 配送料設定テーブル
-------------------------------------------------
CREATE TABLE shopping_delivery_setting (
	setting_number INTEGER NOT NULL AUTO_INCREMENT
	,setting_id VARCHAR(16) NOT NULL
	,delivery_type SMALLINT NOT NULL DEFAULT 0
	,delivery_weight INTEGER NOT NULL DEFAULT 10
	,prefecture_number SMALLINT NOT NULL
	,delivery_price INTEGER NOT NULL DEFAULT 0
	,updated_date DATETIME NULL
	,registered_date DATETIME NULL
	,modifier_id VARCHAR(16) NULL
	,CONSTRAINT shopping_delivery_setting_setting_number PRIMARY KEY (setting_number)
	,CONSTRAINT shopping_delivery_setting_setting_id UNIQUE (setting_id)
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX idx_sshopping_delivery_setting_setting_id ON shopping_delivery_setting( setting_id );

-------------------------------------------------
-- shopping_product_flag
-------------------------------------------------
CREATE TABLE shopping_product_flag (
	flag_number INTEGER NOT NULL AUTO_INCREMENT
	,flag_id VARCHAR(10) NOT NULL
	,flag_name VARCHAR(64) NOT NULL
	,description TEXT NULL
	,file_id VARCHAR(24) NULL
	,status_flag SMALLINT NOT NULL DEFAULT 0
	,order_number SMALLINT NOT NULL DEFAULT 0
	,updated_date DATETIME NULL
	,registered_date DATETIME NULL
	,owner_id VARCHAR(16) NULL
	,modifier_id VARCHAR(16) NULL
	,CONSTRAINT shopping_product_flag_flag_number PRIMARY KEY (flag_number)
	,CONSTRAINT shopping_product_flag_flag_id UNIQUE (flag_id)
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX idx_shopping_product_flag_flag_id ON shopping_product_flag( flag_id );
CREATE INDEX idx_shopping_product_flag_flag_name ON shopping_product_flag( flag_name );
CREATE INDEX idx_shopping_product_flag_order_number ON shopping_product_flag( order_number );

-------------------------------------------------
-- shopping_product_unit_flag_relation
-------------------------------------------------
CREATE TABLE shopping_product_unit_flag_relation (
	relation_number BIGINT NOT NULL AUTO_INCREMENT
	,unit_id VARCHAR(32) NOT NULL
	,flag_id VARCHAR(10) NOT NULL
	,open_date DATE NULL
	,close_date DATE NULL
	,order_number SMALLINT NOT NULL DEFAULT 0
	,CONSTRAINT shopping_product_unit_flag_relation_relation_number PRIMARY KEY (relation_number)
	,FOREIGN KEY (unit_id) REFERENCES shopping_product_unit (unit_id) ON DELETE CASCADE ON UPDATE CASCADE
	,FOREIGN KEY (flag_id) REFERENCES shopping_product_flag (flag_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX idx_shopping_product_unit_flag_relation_unit_id ON shopping_product_unit_flag_relation( unit_id );
CREATE INDEX idx_shopping_product_unit_flag_relation_flag_id ON shopping_product_unit_flag_relation( flag_id );
CREATE INDEX idx_shopping_product_unit_flag_relation_limit_flag ON shopping_product_unit_flag_relation( flag_id, open_date, close_date );

-------------------------------------------------
-- shopping_promotion
-- promotion_rank: プロモーションランク(パートナーランク）
-- buying_flag: 仕入れ許可フラグ
-- exec_tax: 成果報告金額税設定: 0=税込, 1=税抜き切り捨て, 2=税抜き切り上げ, 3=税抜き四捨五入
-- exec_tag: 成果タグが必要な場合に文字列入力
-- exec_url: 成果報告URLリクエストが必要な場合にURL入力
-- tag_item_format: 成果タグに注文アイテム繰り返しが必要な場合のひとつひとつのアイテムに関する文字フォーマット
-- tag_item_implode: 成果タグに注文アイテム繰り返しが必要な場合のアイテム結合文字列
-- discount_type: プロモーション経由申し込み時のお客様の割引タイプ rate=百分率割引, money=金額固定割引, それ以外=割引なし
-- discount_value: プロモーション経由申し込み時のお客様の割引値
-- pay_type: プロモーション成果報酬のタイプ rate=売上に対する百分率報酬, money=金額固定報酬, それ以外=報酬なし
-- pay_value: プロモーション成果報酬の値
-------------------------------------------------
CREATE TABLE shopping_promotion (
	promotion_number INTEGER NOT NULL AUTO_INCREMENT
	,promotion_id VARCHAR(16) NOT NULL
	,member_id VARCHAR(16) NULL
	,promotion_code VARCHAR(16) NOT NULL
	,promotion_rank SMALLINT NOT NULL DEFAULT 0
	,buying_flag SMALLINT NOT NULL DEFAULT 0
	,buying_rate SMALLINT NOT NULL DEFAULT 0
	,promotion_name VARCHAR(255) NOT NULL
	,promotion_kana VARCHAR(255) NULL
	,status_flag SMALLINT NOT NULL DEFAULT 0
	,exec_tax SMALLINT NOT NULL DEFAULT 0
	,exec_tag TEXT NULL
	,exec_url VARCHAR(255) NULL
	,tag_item_format TEXT NULL
	,tag_item_implode VARCHAR(8) NULL
	,discount_type VARCHAR(16) NULL
	,discount_value INTEGER NULL
	,pay_type VARCHAR(16) NULL
	,pay_value INTEGER NULL
	,description TEXT NULL
	,updated_date DATETIME NULL
	,registered_date DATETIME NULL
	,owner_id VARCHAR(16) NULL
	,modifier_id VARCHAR(16) NULL
	,CONSTRAINT shopping_promotion_promotion_number PRIMARY KEY (promotion_number)
	,CONSTRAINT shopping_promotion_promotion_id UNIQUE (promotion_id)
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX idx_shopping_promotion_promotion_id ON shopping_promotion( promotion_id );
CREATE INDEX idx_shopping_promotion_promotion_name ON shopping_promotion( promotion_name );
CREATE INDEX idx_shopping_promotion_promotion_kana ON shopping_promotion( promotion_kana );
CREATE INDEX idx_shopping_promotion_member_id ON shopping_promotion( member_id );
CREATE INDEX idx_shopping_promotion_updated_date ON shopping_promotion( updated_date );
CREATE INDEX idx_shopping_promotion_registered_date ON shopping_promotion( registered_date );

-------------------------------------------------
-- shopping_promotion_access_log
-- アドレスはIPv6を視野にいれて48byte以上の値にしています
-------------------------------------------------
CREATE TABLE shopping_promotion_access_log (
	access_number BIGINT NOT NULL AUTO_INCREMENT
	,access_id VARCHAR(32) NOT NULL
	,promotion_code VARCHAR(32) NOT NULL
	,access_date DATETIME NOT NULL
	,request_uri VARCHAR(255) NULL
	,access_param TEXT NULL
	,remote_host VARCHAR(255) NULL
	,remote_address VARCHAR(64) NULL
	,http_referrer VARCHAR(255) NULL
	,user_agent VARCHAR(255) NULL
	,CONSTRAINT shopping_promotion_access_log_access_number PRIMARY KEY (access_number)
	,CONSTRAINT shopping_promotion_access_log_access_id UNIQUE (access_id)
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX idx_shopping_promotion_access_log_promotion_code ON shopping_promotion_access_log( promotion_code );
CREATE INDEX idx_shopping_promotion_access_log_access_date ON shopping_promotion_access_log( access_date );
CREATE INDEX idx_shopping_promotion_access_log_code_date ON shopping_promotion_access_log( promotion_code, access_date );

-------------------------------------------------
-- shopping_download_log
-- アドレスはIPv6を視野にいれて48byte以上の値にしています
-------------------------------------------------
CREATE TABLE shopping_download_log (
	download_number BIGINT NOT NULL AUTO_INCREMENT
	,order_id VARCHAR(16) NOT NULL
	,unit_id VARCHAR(32) NOT NULL
	,download_date DATETIME NOT NULL
	,remote_host VARCHAR(255) NULL
	,remote_address VARCHAR(64) NULL
	,http_referrer VARCHAR(255) NULL
	,user_agent VARCHAR(255) NULL
	,sim_id VARCHAR(128) NULL
	,CONSTRAINT shopping_download_log_download_number PRIMARY KEY (download_number)
	,FOREIGN KEY (order_id) REFERENCES shopping_order (order_id) ON DELETE CASCADE ON UPDATE CASCADE
	,FOREIGN KEY (unit_id) REFERENCES shopping_product_unit (unit_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX idx_shopping_download_log_order_id ON shopping_download_log( order_id );
CREATE INDEX idx_shopping_download_log_unit_id ON shopping_download_log( unit_id );
CREATE INDEX idx_shopping_download_log_download_date ON shopping_download_log( download_date );
CREATE INDEX idx_shopping_download_log_order_id_date ON shopping_download_log( order_id, download_date );
CREATE INDEX idx_shopping_download_log_unit_id_date ON shopping_download_log( unit_id, download_date );
CREATE INDEX idx_shopping_download_log_order_unit_id_date ON shopping_download_log( order_id, unit_id, download_date );

-------------------------------------------------
-- shopping_product_bookmark
-------------------------------------------------
CREATE TABLE shopping_product_bookmark (
	relation_number BIGINT NOT NULL AUTO_INCREMENT
	,product_id VARCHAR(32) NOT NULL
	,member_id VARCHAR(16) NOT NULL
	,bookmark VARCHAR(255) NULL
	,registered_date DATETIME NULL
	,CONSTRAINT shopping_product_bookmark_relation_number PRIMARY KEY (relation_number)
	,FOREIGN KEY (product_id) REFERENCES shopping_product (product_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX idx_shopping_product_bookmark_member_id ON shopping_product_bookmark( member_id );
CREATE INDEX idx_shopping_product_bookmark_registered_date ON shopping_product_bookmark( registered_date );
CREATE INDEX idx_shopping_product_bookmark_member_id_date ON shopping_product_bookmark( member_id, registered_date );
CREATE INDEX idx_shopping_product_bookmark_member_product_id_date ON shopping_product_bookmark( member_id, product_id, registered_date );

-------------------------------------------------
-- shopping_calendar
-------------------------------------------------
CREATE TABLE shopping_calendar (
	schedule_number BIGINT NOT NULL AUTO_INCREMENT
	,start_date DATETIME NOT NULL
	,close_date DATETIME NOT NULL
	,product_id VARCHAR(32) NULL
	,unit_id VARCHAR(32) NULL
	,price_sell INTEGER NULL
	,stock INTEGER NULL
	,schedule_class SMALLINT NULL
	,schedule_note TEXT NULL
	,CONSTRAINT shopping_calendar_schedule_number PRIMARY KEY (schedule_number)
) ENGINE=InnoDB CHARACTER SET utf8;
CREATE INDEX idx_shopping_calendar_start_date ON shopping_calendar( start_date );
CREATE INDEX idx_shopping_calendar_close_date ON shopping_calendar( close_date );
CREATE INDEX idx_shopping_calendar_start_close_date ON shopping_calendar( start_date,close_date );
CREATE INDEX idx_shopping_calendar_start_date_product_id ON shopping_calendar( start_date, product_id );
CREATE INDEX idx_shopping_calendar_close_date_product_id ON shopping_calendar( close_date, product_id );
CREATE INDEX idx_shopping_calendar_start_close_date_product_id ON shopping_calendar( start_date,close_date,product_id );
CREATE INDEX idx_shopping_calendar_start_date_unit_id ON shopping_calendar( start_date, unit_id );
CREATE INDEX idx_shopping_calendar_close_date_unit_id ON shopping_calendar( close_date, unit_id );
CREATE INDEX idx_shopping_calendar_start_close_date_unit_id ON shopping_calendar( start_date,close_date,unit_id );
CREATE INDEX idx_shopping_calendar_start_close_date_product_unit_id ON shopping_calendar( start_date,close_date,unit_id,product_id );
CREATE INDEX idx_shopping_calendar_unit_id ON shopping_calendar( unit_id );
CREATE INDEX idx_shopping_calendar_unit_start_close ON shopping_calendar( unit_id, start_date,close_date );
CREATE INDEX idx_shopping_calendar_unit_start ON shopping_calendar( unit_id, start_date );
CREATE INDEX idx_shopping_calendar_unit_close ON shopping_calendar( unit_id, close_date );
CREATE INDEX idx_shopping_calendar_product_start_close ON shopping_calendar( product_id, start_date,close_date );
