-- DROP FUNCTION public.get_sjreport_data_pages(date, int4, int4);

CREATE OR REPLACE FUNCTION public.get_sjreport_data_pages(target_date date, page_offset integer, limit_count integer)
 RETURNS TABLE(
     id bigint,
     channel character varying,
     store_name character varying,
     region character varying,
     category character varying,
     brand character varying,
     product character varying,
     variant character varying,
     size character varying,
     issue_for_feedback text,
     store_soh character varying,
     system_dros character varying,
     scoring_system character varying,
     week_start_date text,
     status character varying,
     last_sold character varying,
     store_code character varying,
     product_id bigint  
 )
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    SELECT 
        sjreport.id, 
        sjreport.channel, 
        sjreport.store_name, 
        sjreport.region, 
        sjreport.category, 
        sjreport.brand, 
        sjreport.product, 
        sjreport.variant, 
        sjreport.size, 
        sjreport.issue_for_feedback, 
        sjreport.store_soh, 
        sjreport.system_dros, 
        sjreport.scoring_system, 
        sjreport.week_start_date, 
        sjreport.status, 
        sjreport.last_sold, 
        sjreport.store_code,
        product.id AS product_id  
    FROM sjreport
    left JOIN product ON 
        sjreport.category = product.category AND
        sjreport.brand = product.brand AND
        sjreport.product = product.product AND
        sjreport.variant = product.variant AND
        sjreport.size = product.size  
    WHERE sjreport.week_start_date::date >= target_date
      AND sjreport.issue_for_feedback <> 'NO ISSUE - ITEM SELLING'
    ORDER BY sjreport.week_start_date DESC  
    OFFSET page_offset LIMIT limit_count;  
END;
$function$


select * from get_sjreport_data_pages('2024-11-18',0,1000)