--
--  会員基本属性
--      t_member_basic_attr
--

-- DROP TABLE t_member_basic_attr;

CREATE TABLE t_member_basic_attr
(
    membercd        serial                  NOT NULL,       -- PriKey
    memberdiv       integer                 NOT NULL,
    lastnm          character varying(256)  NOT NULL,
    firstnm         character varying(256)  NOT NULL,
    lastnmkana      character varying(256)  NOT NULL,
    firstnmkana     character varying(256)  NOT NULL,
    mailaddr        character varying(256)  NOT NULL,
    mobmailaddr     character varying(256),
    password        character varying(256)  NOT NULL,
    nicknm          character varying(256),
    secquestioncd   integer                 NOT NULL,       -- ForKey
    secquestionans  character varying(256)  NOT NULL,
    birthdate       date,
    sex             integer                 NOT NULL,
    selfintro       character varying(1024),
    memberstate     integer,
    maildiv         integer,
    registdate      date                    NOT NULL,

    delflg          boolean,
    adddate         timestamp without time zone,
    upddate         timestamp without time zone,
    updusrnm        character varying(60),
    updpcnm         character varying(60),
    updcnt          integer,

    PRIMARY KEY(membercd)
);

-- シリアル初期値
SELECT setval('t_member_basic_attr_MemberCd_seq', 10000);


-- トリガ
CREATE TRIGGER trigger_master_stamp
  BEFORE INSERT OR UPDATE
  ON t_member_basic_attr
  FOR EACH ROW
  EXECUTE PROCEDURE g_master_stamp();

-- トリガー用関数: t_member_basic_attr
-- 品目基本情報の最低価格を更新
-- DROP TRIGGER trigger_member_basic_attr;
CREATE OR REPLACE FUNCTION g_member_basic_attr()
    RETURNS "trigger" AS $BODY$
DECLARE
    _item RECORD;
    _price  integer;
BEGIN
    -- 更新
    IF NEW.membercd IS NOT NULL AND
        ((OLD.memberstate IN (8,9) AND NEW.memberstate = 1) OR
        (OLD.memberstate = 1 AND NEW.memberstate IN (8,9))) THEN

        FOR _item IN SELECT itemcd FROM t_ex_stock_detail WHERE sellercd = NEW.membercd GROUP BY itemcd ORDER BY itemcd LOOP

        -- 全て
        SELECT * INTO _price FROM s_get_lowprice(1, _item.itemcd);
        IF NOT FOUND THEN
            _price := NULL;
        END IF;
        UPDATE t_item_basic_attr SET lowprice = _price
         WHERE itemcd = _item.itemcd;

        -- 中古
        SELECT * INTO _price FROM s_get_lowprice(2, _item.itemcd);
        IF NOT FOUND THEN
            _price := NULL;
        END IF;
        UPDATE t_item_basic_attr SET oldlowprice = _price
         WHERE itemcd = _item.itemcd;

        -- 新品
        SELECT * INTO _price FROM s_get_lowprice(3, _item.itemcd);
        IF NOT FOUND THEN
            _price := NULL;
        END IF;
        UPDATE t_item_basic_attr SET newlowprice = _price
         WHERE itemcd = _item.itemcd;

        RAISE NOTICE 'itemcd % updated...', _item.itemcd;

        END LOOP;

    ELSE
    RAISE NOTICE 'not updated';
    END IF;

    RETURN NULL;
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE;


-- トリガ登録
CREATE TRIGGER trigger_member_basic_attr
    AFTER UPDATE
    ON t_member_basic_attr
    FOR EACH ROW
    EXECUTE PROCEDURE g_member_basic_attr();
