----------------------------------------
----------------------------------------
--各種マスタ

--支払い種別マスタ
CREATE TABLE pay_div_m (
    pay_div_cd CHAR(5) NOT NULL COMMENT '支払分類コード',
    pay_div_nm VARCHAR(50) NOT NULL COMMENT '支払分類名',
    PRIMARY KEY (pay_div_cd) 
) 
COMMENT '支払種別マスタ';
INSERT INTO pay_div_m VALUES ('PY001', 'クレジットカード');
INSERT INTO pay_div_m VALUES ('PY002', '銀行振込');
INSERT INTO pay_div_m VALUES ('PY003', 'コンビニ決済');
INSERT INTO pay_div_m VALUES ('PY004', '代引');

--性別マスタ
CREATE TABLE gender_m (
    gender_cd CHAR(2) NOT NULL COMMENT '性別コード',
    gender_div_nm VARCHAR(50) NOT NULL COMMENT '性別分類名',
    PRIMARY KEY (gender_cd)
)
COMMENT '性別マスタ';
INSERT INTO gender_m VALUES ('J0', '男性');
INSERT INTO gender_m VALUES ('J1', '女性');
INSERT INTO gender_m VALUES ('J9', 'その他');

--クレジットカード分類マスタ
CREATE TABLE credit_div_m (
    credit_div_cd CHAR(5) NOT NULL COMMENT 'カード分類コード',
    credit_div_nm VARCHAR(50) NOT NULL COMMENT 'カード分類名',
    PRIMARY KEY (credit_div_cd)
)
COMMENT 'クレジットカード分類マスタ';
INSERT INTO credit_div_m VALUE ('CD001', 'VISA');
INSERT INTO credit_div_m VALUE ('CD002', 'JCB');
INSERT INTO credit_div_m VALUE ('CD003', 'Master');
INSERT INTO credit_div_m VALUE ('CD004', 'AMERICAN EXPRESS');

--商品分類マスタ
CREATE TABLE goods_div_m (
    goods_div_cd CHAR(5) NOT NULL COMMENT '商品分類コード',
    goods_div_nm VARCHAR(50) NOT NULL COMMENT '商品分類名',
    PRIMARY KEY (goods_div_cd)
)
COMMENT '商品分類マスタ';
INSERT INTO goods_div_m VALUE ('K0001', '日用品');
INSERT INTO goods_div_m VALUE ('K0002', 'パソコン・オフィス用品');
INSERT INTO goods_div_m VALUE ('K0003', 'スポーツ＆アウトドア');
INSERT INTO goods_div_m VALUE ('K0004', 'カー用品');
INSERT INTO goods_div_m VALUE ('K0005', '食料品');
INSERT INTO goods_div_m VALUE ('K0006', '家電');
INSERT INTO goods_div_m VALUE ('K0007', 'ファッション');
INSERT INTO goods_div_m VALUE ('K0008', '娯楽');

--消費税マスタ
CREATE TABLE tax_m (
    app_date DATE NOT NULL COMMENT '適用年月日',
    tax_rate INT NOT NULL DEFAULT 0 COMMENT '税率（％）',
    PRIMARY KEY (app_date)
)
COMMENT '消費税マスタ';
INSERT INTO tax_m VALUE ('1989-4-1', 3);
INSERT INTO tax_m VALUE ('1997-4-1', 5);
INSERT INTO tax_m VALUE ('2014-4-1', 8);
INSERT INTO tax_m VALUE ('2019-10-1', 10);

--注文ステータス
CREATE TABLE order_sts_m (
    sts_cd CHAR(5) NOT NULL COMMENT 'ステータスコード',
    sts VARCHAR(50) NOT NULL COMMENT 'ステータス',
    PRIMARY KEY (sts_cd)
)
COMMENT '注文ステータス';
INSERT INTO order_sts_m VALUE ('ST001', '受注完了');
INSERT INTO order_sts_m VALUE ('ST002', '入荷待');
INSERT INTO order_sts_m VALUE ('ST003', '発送準備');
INSERT INTO order_sts_m VALUE ('ST004', '発送中');
INSERT INTO order_sts_m VALUE ('ST010', 'お客様受け取り済');
INSERT INTO order_sts_m VALUE ('ST999', 'キャンセル');

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

--顧客マスタ
CREATE TABLE user_m (
    user_id CHAR(10) NOT NULL COMMENT '顧客ID',
    user_f_nm VARCHAR(50) NOT NULL COMMENT '氏',
    user_f_nm_kn VARCHAR(50) NOT NULL COMMENT '氏カナ',
    user_nm VARCHAR(50) NOT NULL COMMENT '名',
    user_nm_kn VARCHAR(50) NOT NULL COMMENT '名カナ',
    user_birth DATE NOT NULL COMMENT '生年月日',
    gender_cd CHAR(2) NOT NULL COMMENT '性別',
    mail_address VARCHAR(50) NOT NULL COMMENT 'メールアドレス',
    user_post_no CHAR(8) NOT NULL COMMENT '郵便番号',
    user_prefecture_nm VARCHAR(4) NOT NULL COMMENT '県名',
    user_municipality_nm VARCHAR(50) NOT NULL COMMENT '市区町村名',
    user_city_nm VARCHAR(50) NOT NULL COMMENT '町域名',
    user_address VARCHAR(50) NOT NULL COMMENT '住所',
    user_building_nm VARCHAR(50) COMMENT '建物名',
    user_tel_nm VARCHAR(50) NOT NULL COMMENT '電話番号',
    user_passwd VARCHAR(50) NOT NULL COMMENT 'パスワード',
    PRIMARY KEY (user_id),
    FOREIGN KEY (gender_cd) REFERENCES gender_m(gender_cd)
)
COMMENT '顧客マスタ';
INSERT INTO user_m VALUE ('CU00000001', '山田', 'ヤマダ', '一', 'ハジメ', '1973-06-06', 'J0', 'h.yamada@hoge.com', '140-0014', '東京都', '品川区', '大井', '○○-○', 'マンション１３９９', '090-xxxx-xxxx', 'hX8UEzE8');
INSERT INTO user_m VALUE ('CU00000002', '高橋', 'タカハシ', '龍也', 'タツヤ', '1970-11-10', 'J0', 't.takahashi@hoge.com', '230-0063', '神奈川県', '横浜市鶴見区', '鶴見', '×-××', NULL, '090-xxxx-xxxx', 'UQQA6B3R');
INSERT INTO user_m VALUE ('CU00000003', '麻枝', 'マエダ', '准', 'ジュン', '1975-1-3', 'J0', 'j.maeda@hoge.com', '333-0823', '埼玉県', '川口市', '石神', '○○-○', NULL, '090-yyyy-1234', 'UDHWnLLk');
INSERT INTO user_m VALUE ('CU00000004', '三宅', 'ミヤケ', '章介', 'ショウスケ', '1970-10-10', 'J0', 's.miyake@hoge.com', '273-0031', '千葉県', '船橋市', '西船', '○○-○', 'アパート１０１', '090-yyyy-5678', 'E2QGZNzw');
INSERT INTO user_m VALUE ('CU00000005', '涼元', 'スズモト', '悠一', 'ユウイチ', '1969-1-13', 'J0', 'y.suzumoto@hoge.com', '321-0207', '栃木県', '下都賀郡壬生町', '北小林', '××××', NULL, '090-1234-xxxx', 'DZy5qadL');


--クレジットカード
CREATE TABLE credit_card_t (
    user_id CHAR(10) NOT NULL COMMENT '顧客ID',
    credit_div_cd CHAR(5)NOT NULL COMMENT 'カード分類コード',
    credit_no CHAR(19) NOT NULL COMMENT 'カード番号',
    credit_nm VARCHAR(50) NOT NULL COMMENT 'カード名義',
    validated_month CHAR(2)  NOT NULL COMMENT '有効期限月',
    validated_year CHAR(4) NOT NULL COMMENT '有効期限年',
    sc_cd CHAR(3) NOT NULL COMMENT 'シークレッドコード',
    PRIMARY KEY (user_id),
    FOREIGN KEY (user_id) REFERENCES user_m(user_id),
    FOREIGN KEY (credit_div_cd) REFERENCES credit_div_m(credit_div_cd)
)
COMMENT 'クレジットカード';
INSERT INTO credit_card_t VALUE ('CU00000001', 'CD001', 'xxxx-xxxx-xxxx-xxxx', 'HAJIME YAMADA', '03', '2017', 'xxx');
INSERT INTO credit_card_t VALUE ('CU00000005', 'CD001', 'xxxx-xxxx-xxxx-xxxx', 'YUICHI SUZUMOTO', '03', '2016', 'xxx');

--ログイン履歴
CREATE TABLE login_hist_t (
    user_id VARCHAR(10) NOT NULL COMMENT '顧客ID',
    sfx CHAR(1) NOT NULL COMMENT 'SFX',
    login_datetime DATETIME NOT NULL COMMENT 'ログイン日時',
    conn_ip CHAR(15) NOT NULL COMMENT '接続先IP',
    term_info VARCHAR(50) NOT NULL COMMENT '端末情報',
    PRIMARY KEY (login_datetime),
    FOREIGN KEY (user_id) REFERENCES user_m(user_id)
)
COMMENT 'ログイン履歴';
INSERT INTO login_hist_t VALUE ('CU00000001', '1', '2014-10-1 10:00:00', 'xxx.xxx.xxx.xxx', 'スマートフォン');

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

--商品マスタ
CREATE TABLE goods_m (
    goods_id CHAR(10) NOT NULL COMMENT '商品ID',
    goods_nm VARCHAR(50) NOT NULL COMMENT '商品名',
    goods_maker VARCHAR(50) NOT NULL COMMENT 'メーカー',
    goods_div_cd CHAR(5) NOT NULL COMMENT '商品分類コード',
    handling_date DATE NOT NULL COMMENT '取扱開始日',
    goods_price INT NOT NULL DEFAULT 0 COMMENT '価格',
    goods_cost INT NOT NULL DEFAULT 0 COMMENT '原価',
    PRIMARY KEY (goods_id),
    FOREIGN KEY (goods_div_cd) REFERENCES goods_div_m(goods_div_cd)
)
COMMENT '商品マスタ';
INSERT INTO goods_m VALUE ('ITM0000001', 'EIZO FlexScan 27インチカラー液晶モニター', 'EIZO', 'K0002', '2012-8-24', 79110, 40000);
INSERT INTO goods_m VALUE ('ITM0000002', 'サッカー 日本代表 番号あり レプリカユニフォーム', 'adidas(アディダス)', 'K0003', '2014-3-4', 12915, 2000);
INSERT INTO goods_m VALUE ('ITM0000003', 'レイコップ ふとんクリーナー (パールホワイト)', 'レイコップ', 'K0006', '2013-9-17', 24500, 12000);
INSERT INTO goods_m VALUE ('ITM0000004', 'ブルガリ アクアプールオム マリン EDT SP 100ml', 'ブルガリ', 'K0007', '2010-3-3', 3712, 1500);
INSERT INTO goods_m VALUE ('ITM0000005', 'マジック:ザ・ギャザリング 基本セット2015 ブースターパック 日本語版 BOX', 'ウィザーズ・オブ・ザ・コースト', 'K0008', '2014-4-28', 10952, 6000);

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

--倉庫マスタ
CREATE TABLE store_m (
    store_id CHAR(5) NOT NULL COMMENT '倉庫ID',
    store_nm VARCHAR(50) NOT NULL COMMENT '倉庫名',
    store_post_no CHAR(8) NOT NULL COMMENT '郵便番号',
    store_prefecture_nm VARCHAR(4) NOT NULL COMMENT '県名',
    store_municipality_nm VARCHAR(50) NOT NULL COMMENT '市区町村名',
    store_city_nm VARCHAR(50) NOT NULL COMMENT '町域名',
    store_address VARCHAR(50) NOT NULL COMMENT '住所',
    store_building_nm VARCHAR(50) COMMENT '建物名',
    PRIMARY KEY (store_id)
)
COMMENT '倉庫マスタ';
INSERT INTO store_m VALUE ('W0001', '大井倉庫', '140-0014', '東京都', '品川区', '大井', 'XXXXXXXXXXXXXXXX', 'XXXXXXXX');

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

--在庫マスタ
CREATE TABLE stock_m (
    store_id CHAR(5) NOT NULL COMMENT '倉庫ID',
    goods_id CHAR(10) NOT NULL COMMENT '商品ID',
    arrival_date DATE NOT NULL COMMENT '入庫日',
    arrival_num INT NOT NULL DEFAULT 0 COMMENT '入庫数',
    security_num INT NOT NULL DEFAULT 0 COMMENT '引当数',
    PRIMARY KEY (arrival_date),
    FOREIGN KEY (store_id) REFERENCES store_m(store_id),
    FOREIGN KEY (goods_id) REFERENCES goods_m(goods_id)
)
COMMENT '在庫マスタ';
INSERT INTO stock_m VALUE ('W0001', 'ITM0000001', '2012-8-24', 2, 2);
INSERT INTO stock_m VALUE ('W0001', 'ITM0000001', '2012-12-25', 3, 3);
INSERT INTO stock_m VALUE ('W0001', 'ITM0000001', '2013-6-1', 2, 2);
INSERT INTO stock_m VALUE ('W0001', 'ITM0000001', '2013-11-20', 5, 4);
INSERT INTO stock_m VALUE ('W0001', 'ITM0000001', '2014-8-8', 7, 0);
INSERT INTO stock_m VALUE ('W0001', 'ITM0000002', '2014-3-4', 100, 100);
INSERT INTO stock_m VALUE ('W0001', 'ITM0000002', '2014-9-5', 50, 45);
INSERT INTO stock_m VALUE ('W0001', 'ITM0000002', '2014-9-10', 60, 0);
INSERT INTO stock_m VALUE ('W0001', 'ITM0000003', '2013-9-17', 5, 2);
INSERT INTO stock_m VALUE ('W0001', 'ITM0000004', '2010-3-3', 10, 10);
INSERT INTO stock_m VALUE ('W0001', 'ITM0000004', '2012-10-5', 3, 3);
INSERT INTO stock_m VALUE ('W0001', 'ITM0000004', '2013-9-10', 6, 5);
INSERT INTO stock_m VALUE ('W0001', 'ITM0000004', '2014-4-3', 2, 0);
INSERT INTO stock_m VALUE ('W0001', 'ITM0000005', '2014-4-28', 150, 150);
INSERT INTO stock_m VALUE ('W0001', 'ITM0000005', '2014-5-20', 100, 86);

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

--入庫テーブル
CREATE TABLE ins_stock_m (
    goods_id CHAR(10) NOT NULL COMMENT '商品ID',
    store_id CHAR(5) NOT NULL COMMENT '入庫先倉庫ID',
    ins_stock_exp_date DATE NOT NULL COMMENT '入庫予定日',
    ins_stock_cmp_date DATE COMMENT '入庫完了日',
    ins_stock_exp_num INT NOT NULL COMMENT '入庫予定数',
    ins_stock_num INT COMMENT '入庫数',
    PRIMARY KEY (ins_stock_exp_date),
    FOREIGN KEY (goods_id) REFERENCES goods_m(goods_id),
    FOREIGN KEY (store_id) REFERENCES store_m(store_id)
)
COMMENT '入庫テーブル';
INSERT INTO ins_stock_m VALUE ('ITM0000004', 'W0001', '2010-3-3', '2010-3-3', 10, 10);
INSERT INTO ins_stock_m VALUE ('ITM0000001', 'W0001', '2012-8-24', '2012-8-24', 2, 2);
INSERT INTO ins_stock_m VALUE ('ITM0000004', 'W0001', '2012-10-5', '2012-10-5', 3, 3);
INSERT INTO ins_stock_m VALUE ('ITM0000001', 'W0001', '2012-12-25', '2012-12-25', 3, 3);
INSERT INTO ins_stock_m VALUE ('ITM0000001', 'W0001', '2013-6-1', '2013-6-1', 2, 2);
INSERT INTO ins_stock_m VALUE ('ITM0000004', 'W0001', '2013-9-10', '2013-9-10', 6, 6);
INSERT INTO ins_stock_m VALUE ('ITM0000003', 'W0001', '2013-9-17', '2013-9-17', 5, 5);
INSERT INTO ins_stock_m VALUE ('ITM0000001', 'W0001', '2013-11-20', '2013-11-20', 5, 5);
INSERT INTO ins_stock_m VALUE ('ITM0000002', 'W0001', '2014-3-4', '2014-3-4', 100, 100);
INSERT INTO ins_stock_m VALUE ('ITM0000004', 'W0001', '2014-4-3', '2014-4-3', 2, 2);
INSERT INTO ins_stock_m VALUE ('ITM0000005', 'W0001', '2014-4-28', '2014-4-28', 150, 150);
INSERT INTO ins_stock_m VALUE ('ITM0000005', 'W0001', '2014-5-20', '2014-5-20', 100, 100);
INSERT INTO ins_stock_m VALUE ('ITM0000001', 'W0001', '2014-8-8', '2014-8-8', 7, 7);
INSERT INTO ins_stock_m VALUE ('ITM0000002', 'W0001', '2014-9-5', '2014-9-5', 50, 50);
INSERT INTO ins_stock_m VALUE ('ITM0000002', 'W0001', '2014-9-10', '2014-9-10', 60, 60);
INSERT INTO ins_stock_m VALUE ('ITM0000001', 'W0001', '2014-12-3', NULL, 5, NULL);
INSERT INTO ins_stock_m VALUE ('ITM0000002', 'W0001', '2014-12-10', NULL, 200, NULL);
INSERT INTO ins_stock_m VALUE ('ITM0000003', 'W0001', '2014-12-12', NULL, 3, NULL);
INSERT INTO ins_stock_m VALUE ('ITM0000004', 'W0001', '2014-12-15', NULL, 10, NULL);
INSERT INTO ins_stock_m VALUE ('ITM0000005', 'W0001', '2014-12-25', NULL, 80, NULL);

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

--注文
CREATE TABLE order_t (
    order_no INT NOT NULL AUTO_INCREMENT COMMENT '受注番号',
    order_date DATE NOT NULL COMMENT '受注日',
    user_id CHAR(10) NOT NULL COMMENT '顧客ID',
    sts_cd CHAR(5) NOT NULL COMMENT 'ステータス',
    pay_div_cd CHAR(5) NOT NULL COMMENT '支払分類',
    deli_exp_date DATE COMMENT '出荷予定日',
    deli_date DATE COMMENT '出荷日',
    deli_company VARCHAR(50) NOT NULL COMMENT '配送業者',
    tracing_no CHAR(11) NOT NULL COMMENT '追跡番号',
    arrive_exp_date DATE COMMENT '到着予定日',
    cash_deli_comm_rate INT NOT NULL DEFAULT 0 COMMENT '代引手数料',
    postage INT NOT NULL DEFAULT 1836 COMMENT '送料',
    tax INT NOT NULL DEFAULT 0 COMMENT '消費税',
    PRIMARY KEY (order_no),
    FOREIGN KEY (user_id) REFERENCES user_m(user_id),
    FOREIGN KEY (sts_cd) REFERENCES order_sts_m(sts_cd),
    FOREIGN KEY (pay_div_cd) REFERENCES pay_div_m(pay_div_cd)
)
COMMENT '注文';
INSERT INTO order_t VALUE (1, '2014-11-20', 'CU00000003', 'ST010', 'PY002', '2014-11-20', '2014-11-20', 'ヤマト', 'xxxxxxxxxxx', '2014-11-21', 0, 1836, 208501);
INSERT INTO order_t VALUE (2, '2014-11-25', 'CU00000001', 'ST010', 'PY001', '2014-11-25', '2014-11-25', 'ヤマト', 'xxxxxxxxxxx', '2014-11-26', 0, 1836, 94520);
INSERT INTO order_t VALUE (3, '2014-11-30', 'CU00000005', 'ST010', 'PY001', '2014-12-1', '2014-12-2', 'ヤマト', 'xxxxxxxxxxx', '2014-12-3', 0, 1836, 108562);
INSERT INTO order_t VALUE (4, '2014-12-2', 'CU00000004', 'ST010', 'PY004', '2014-12-2', '2014-12-2', 'ヤマト', 'xxxxxxxxxxx', '2014-12-3', 510, 1836, 103310);

--注文詳細
CREATE TABLE order_detail_t (
    order_detail_no INT NOT NULL AUTO_INCREMENT COMMENT '受注詳細番号',
    order_no INT NOT NULL COMMENT '受注番号',
    goods_id CHAR(10) NOT NULL COMMENT '商品ID',
    goods_unit_price INT NOT NULL DEFAULT 0 COMMENT '商品単価',
    goods_num INT NOT NULL DEFAULT 0 COMMENT '個数',
    PRIMARY KEY (order_detail_no),
    FOREIGN KEY (order_no) REFERENCES order_t(order_no),
    FOREIGN KEY (goods_id) REFERENCES goods_m(goods_id)
)
COMMENT '注文詳細';
INSERT INTO order_detail_t VALUE (1, 1, 'ITM0000002', 12915, 100);
INSERT INTO order_detail_t VALUE (2, 1, 'ITM0000005', 10952, 80);
INSERT INTO order_detail_t VALUE (3, 1, 'ITM0000003', 24500, 1);
INSERT INTO order_detail_t VALUE (4, 1, 'ITM0000004', 3712, 5);
INSERT INTO order_detail_t VALUE (5, 1, 'ITM0000001', 79110, 5);
INSERT INTO order_detail_t VALUE (6, 2, 'ITM0000001', 79110, 1);
INSERT INTO order_detail_t VALUE (7, 2, 'ITM0000002', 12915, 50);
INSERT INTO order_detail_t VALUE (8, 2, 'ITM0000004', 3712, 5);
INSERT INTO order_detail_t VALUE (9, 2, 'ITM0000005', 10952, 40);
INSERT INTO order_detail_t VALUE (10, 3, 'ITM0000001', 79110, 3);
INSERT INTO order_detail_t VALUE (11, 3, 'ITM0000003', 24500, 1);
INSERT INTO order_detail_t VALUE (12, 3, 'ITM0000005', 10952, 100);
INSERT INTO order_detail_t VALUE (13, 4, 'ITM0000001', 79110, 2);
INSERT INTO order_detail_t VALUE (14, 4, 'ITM0000002', 12915, 60);
INSERT INTO order_detail_t VALUE (15, 4, 'ITM0000004', 3712, 8);
INSERT INTO order_detail_t VALUE (16, 4, 'ITM0000005', 10952, 30);