/*

exec dbo.CF_GetMainCafeListPosts 'ko', 0, N'', 'users', 1, 10

*/
CREATE PROC [dbo].[CF_GetMainCafeListPosts]
  @lang_cd varchar(20),
  @menu_id int,
  @keyword NVARCHAR(100),
	@orderby VARCHAR(20),
	@cpage INT,
	@pagesize INT
AS

DECLARE 
	@totcnt INT;

BEGIN

  SET @totcnt = 0;

  with wa
  as (
  select 
    pvt.CAFE_ID, isnull(pvt.[1], 0) POSTRANK1, isnull(pvt.[2], 0) POSTRANK2, isnull(pvt.[3], 0) POSTRANK3 
    from (
          select 
            pd.CAFE_ID, pd.prank, pd.POST_ID
            from (
                  select
                    ROW_NUMBER() over(partition by p.cafe_id order by p.dt desc) prank,
                    p.cafe_id, p.post_id
                    from dbo.CF_POSTS p
                   where 1=1
                     and p.STAT <> 9
                 ) pd
            left outer join dbo.CF_POSTS pt on pt.CAFE_ID = pd.CAFE_ID and pt.POST_ID = pd.POST_ID
           where 1=1
             and pd.CAFE_ID <> ''
             and pd.CAFE_ID is not null
             and pd.prank in (1, 2, 3)
           group by
            pd.CAFE_ID, pd.prank, pd.POST_ID
         ) tp
   pivot ( max(tp.post_id) for tp.prank in ([1], [2], [3]) ) as pvt
  )
  select 
    @totcnt = COUNT(*)
    from wa
    left outer join dbo.CF_CAFE_INFO cc on cc.cafe_id = wa.CAFE_ID
    left outer join dbo.CF_POSTS p1 on p1.CAFE_ID = wa.CAFE_ID and p1.POST_ID = wa.POSTRANK1
    left outer join dbo.CF_POSTS p2 on p2.CAFE_ID = wa.CAFE_ID and p2.POST_ID = wa.POSTRANK2
    left outer join dbo.CF_POSTS p3 on p3.CAFE_ID = wa.CAFE_ID and p3.POST_ID = wa.POSTRANK3
    left outer join dbo.CF_CAFE_MEMBERS c1 on c1.CAFE_SEQ = cc.SEQ 
      and c1.MEM_NO in (select top 1 mem_no from dbo.CF_MEMBER where mem_email = p1.MEM_EMAIL)
    left outer join dbo.CF_CAFE_MEMBERS c2 on c2.CAFE_SEQ = cc.SEQ 
      and c2.MEM_NO in (select top 1 mem_no from dbo.CF_MEMBER where mem_email = p2.MEM_EMAIL)
    left outer join dbo.CF_CAFE_MEMBERS c3 on c3.CAFE_SEQ = cc.SEQ 
      and c3.MEM_NO in (select top 1 mem_no from dbo.CF_MEMBER where mem_email = p3.MEM_EMAIL)
   where cc.STAT <> 9
     and cc.IS_PUBLIC = 'Y'
     and cc.CAFE_LANG = 'ko'
;


with wa
as (
select 
  pvt.CAFE_ID, isnull(pvt.[1], 0) POSTRANK1, isnull(pvt.[2], 0) POSTRANK2, isnull(pvt.[3], 0) POSTRANK3 
  from (
        select 
          pd.CAFE_ID, pd.prank, pd.POST_ID
          from (
                select
                  ROW_NUMBER() over(partition by p.cafe_id order by p.dt desc) prank,
                  p.cafe_id, p.post_id
                  from dbo.CF_POSTS p
                 where 1=1
                   and p.STAT <> 9
               ) pd
          left outer join dbo.CF_POSTS pt on pt.CAFE_ID = pd.CAFE_ID and pt.POST_ID = pd.POST_ID
         where 1=1
           and pd.CAFE_ID <> ''
           and pd.CAFE_ID is not null
           and pd.prank in (1, 2, 3)
         group by
          pd.CAFE_ID, pd.prank, pd.POST_ID
       ) tp
 pivot ( max(tp.post_id) for tp.prank in ([1], [2], [3]) ) as pvt
)
select 
  wa.CAFE_ID, wa.POSTRANK1, wa.POSTRANK2, wa.POSTRANK3,
  (select count(*) from dbo.CF_CAFE_MEMBERS where CAFE_SEQ = cc.SEQ) MEM_CNT,
  (select wp.DT from dbo.CF_POSTS wp where wp.CAFE_ID = wa.CAFE_ID and wp.POST_ID = wa.POSTRANK1) as POSTRANK1_DT,
  --cc.CAFE_ID,
  p1.POST_ID POST_ID1, isnull(p1.POST_TITLE, '') POST_TITLE1, p1.DT WRITE_DT1, isnull(c1.CAFE_NICK, '') CAFE_NICK1,
  p2.POST_ID POST_ID2, isnull(p2.POST_TITLE, '') POST_TITLE2, p2.DT WRITE_DT2, isnull(c2.CAFE_NICK, '') CAFE_NICK2,
  p3.POST_ID POST_ID3, isnull(p3.POST_TITLE, '') POST_TITLE3, p3.DT WRITE_DT3, isnull(c3.CAFE_NICK, '') CAFE_NICK3,
  cc.CAFE_NAME, cc.CAFE_LANG LANG_CD, cc.REGION_CD, cc.TIMEZONE,
  (select top 1 file_name from dbo.CF_CAFE_FILES where cafe_id = cc.CAFE_ID and file_type = 1 order by dt desc) as cafe_icon_file
  from wa
  left outer join dbo.CF_CAFE_INFO cc on cc.cafe_id = wa.CAFE_ID
  left outer join dbo.CF_POSTS p1 on p1.CAFE_ID = wa.CAFE_ID and p1.POST_ID = wa.POSTRANK1
  left outer join dbo.CF_POSTS p2 on p2.CAFE_ID = wa.CAFE_ID and p2.POST_ID = wa.POSTRANK2
  left outer join dbo.CF_POSTS p3 on p3.CAFE_ID = wa.CAFE_ID and p3.POST_ID = wa.POSTRANK3
  left outer join dbo.CF_CAFE_MEMBERS c1 on c1.CAFE_SEQ = cc.SEQ 
    and c1.MEM_NO in (select top 1 mem_no from dbo.CF_MEMBER where mem_email = p1.MEM_EMAIL)
  left outer join dbo.CF_CAFE_MEMBERS c2 on c2.CAFE_SEQ = cc.SEQ 
    and c2.MEM_NO in (select top 1 mem_no from dbo.CF_MEMBER where mem_email = p2.MEM_EMAIL)
  left outer join dbo.CF_CAFE_MEMBERS c3 on c3.CAFE_SEQ = cc.SEQ 
    and c3.MEM_NO in (select top 1 mem_no from dbo.CF_MEMBER where mem_email = p3.MEM_EMAIL)
 where cc.STAT <> 9
   and cc.IS_PUBLIC = 'Y'
   and cc.CAFE_LANG = @lang_cd
 order by POSTRANK1_DT desc


print @totcnt;
	RETURN @totcnt;

END


GO