--
--  品目構成
--      t_item_struct
--

-- DROP TABLE t_item_struct;

CREATE TABLE t_item_struct
(
    itemcd          integer NOT NULL,       -- PriKey
    structcd        integer NOT NULL,       -- PriKey
    variationnm     character varying(256),
    makeritemcd     character varying(256),
    jancd           character varying(256),
    salestartdate   date,
    orderclosedate  date,
    makerprice      integer,
    comment         character varying(1024),

    option01        varchar(1024),
    option02        varchar(1024),
    option03        varchar(1024),
    option04        varchar(1024),
    option05        varchar(1024),
    option06        varchar(1024),
    option07        varchar(1024),
    option08        varchar(1024),
    option09        varchar(1024),
    option10        varchar(1024),

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

    PRIMARY KEY(itemcd, structcd)
);


-- Trigger: trigger_master_stamp on t_item_struct

-- DROP TRIGGER trigger_master_stamp ON t_item_struct;

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

-- トリガー用関数: t_item_struct
CREATE OR REPLACE FUNCTION g_item_struct()
  RETURNS "trigger" AS
$BODY$
DECLARE
    itembasic_rec RECORD;
    new_structcd integer;
BEGIN
    -- アイテムCDのチェック
    IF NEW.itemcd IS NULL THEN
        RAISE EXCEPTION '引数 itemcd は必須です';
    END IF;
    SELECT INTO itembasic_rec * FROM t_item_basic_attr WHERE itemcd = NEW.itemcd;
    IF NOT FOUND THEN
        RAISE EXCEPTION '引数 itemcd % のデータが見つかりません', NEW.itemcd;
    END IF;

    -- structCDの取得
    SELECT INTO new_structcd MAX(structcd) +1 FROM t_item_struct WHERE itemcd = NEW.itemcd;

    IF new_structcd IS NULL THEN
        new_structcd := 1;
    END IF;

    -- 更新
    NEW.structcd := new_structcd;
    RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

-- トリガ
CREATE TRIGGER trigger_t_item_struct
  BEFORE INSERT
  ON t_item_struct
  FOR EACH ROW
  EXECUTE PROCEDURE g_item_struct();


-- トリガー用関数: item_struct
-- 品目基本情報を更新
CREATE OR REPLACE FUNCTION g_item_struct2()
  RETURNS "trigger" AS
$BODY$
DECLARE
    _dt  date;
BEGIN
    -- 更新
    SELECT min(salestartdate) INTO _dt FROM t_item_struct
     WHERE itemcd = NEW.itemcd AND delflg = FALSE GROUP BY itemcd;

    UPDATE t_item_basic_attr SET min_salestartdate = _dt
     WHERE itemcd = NEW.itemcd;

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

-- トリガ登録
CREATE TRIGGER trigger_item_struct2
  AFTER INSERT OR UPDATE
  ON t_item_struct
  FOR EACH ROW
  EXECUTE PROCEDURE g_item_struct2();
