-- View: "v_recommend_item"

DROP VIEW v_recommend_item;

CREATE OR REPLACE VIEW v_recommend_item AS
SELECT
    basic.itemcd,
    basic.itemnm,
    basic.itemnmkana,
    basic.makercd,
    maker.makernm,
    basic.labelcd,
    label.labelnm,
    basic.seriescd,
    series.seriesnm,
    basic.itemdivcd,
    itemdiv.name AS itemdivnm,
    basic.examgroupcd,
    examgroup.name AS examgroupnm,
    basic.examno,
    basic.iteminfo,
    basic.eirinrating,
    eirin.name AS eirinnm,
    basic.subtitle,
    basic.updcnt AS item_basic_updcnt,
    (SELECT min(salestartdate) FROM t_item_struct           -- 一番古い発売日
      WHERE basic.itemcd = t_item_struct.itemcd
        AND basic.delflg=FALSE) AS salestartdate,
    (SELECT min(orderclosedate) FROM t_item_struct          -- 一番古い発注締切日
      WHERE basic.itemcd = t_item_struct.itemcd
        AND basic.delflg=FALSE) AS orderclosedate,
    web.releasedate,
    web.rankingrate,
    web.comment,
    web.updcnt AS item_web_attr_updcnt,
    recommend.startdate,
    recommend.closedate,
    recommend.priority,
    recommend.updcnt as recommend_updcnt,
    (SELECT count(*) FROM t_image
      WHERE typediv = 1     -- 1:品目
        AND generalcd = basic.itemcd
        AND delflg=FALSE) AS image_count,
	COALESCE(s3.reserveflg, 0) AS reserveflg

FROM
    t_recommend recommend,
    t_item_basic_attr basic
    LEFT JOIN t_item_web_attr web
              ON basic.itemcd = web.itemcd
    LEFT JOIN t_maker maker
              ON basic.makercd = maker.makercd
    LEFT JOIN t_label label
              ON basic.makercd = label.makercd AND basic.labelcd = label.labelcd
    LEFT JOIN t_series series
              ON basic.makercd = series.makercd AND basic.seriescd = series.seriescd
    LEFT JOIN t_name itemdiv
              ON itemdiv.namecd = basic.itemdivcd AND itemdiv.namediv = 1
    LEFT JOIN t_name examgroup
              ON examgroup.namecd = basic.examgroupcd AND examgroup.namediv = 2
    LEFT JOIN t_name eirin
              ON eirin.namecd = basic.eirinrating AND eirin.namediv = 3
    LEFT JOIN (SELECT s.itemcd, case when count(s.reserveflg)>0 then 1 end as reserveflg
	                    FROM v_stock_list s WHERE s.reserveflg=1 GROUP BY s.itemcd) s3
              ON s3.itemcd=basic.itemcd
WHERE
        basic.itemcd = recommend.itemcd
    AND basic.delflg = FALSE
    AND web.delflg = FALSE
    AND recommend.delflg = FALSE
    AND (recommend.startdate <= CURRENT_DATE
    AND recommend.closedate >= CURRENT_DATE);