-- Function to get combined week report filtered by region, channel, customer_id, and week_start_date
-- Based on get_combined_week_report.sql but with parameter filtering via store table
--
-- Parameters:
--   p_region: Region to filter by
--   p_channel: Channel to filter by
--   p_customer_id: Customer ID to filter by
--   p_week_start_date: Week start date to filter by
--
-- Returns: TABLE with same structure as combined_week_report

CREATE OR REPLACE FUNCTION get_combined_week_report_by_region_channel(
    p_region TEXT,
    p_channel TEXT,
    p_customer_id INTEGER,
    p_week_start_date DATE
)
RETURNS TABLE (
    item_id TEXT,
    store_code TEXT,
    store_name TEXT,
    channel TEXT,
    customer_id TEXT,
    product_id TEXT,
    issue_for_feedback TEXT,
    week_start_date TEXT,
    created_at DATE,
    updated_at DATE,
    note TEXT,
    image_url TEXT,
    fixed_at TEXT,
    rep_full_name TEXT,
    gps TEXT,
    article TEXT,
    dc_soh TEXT,
    last_ordered TEXT,
    last_received TEXT,
    store_soh TEXT,
    system_dros TEXT,
    scoring_system TEXT,
    last_sold TEXT,
    category TEXT,
    brand TEXT,
    product TEXT,
    variant TEXT,
    size TEXT,
    status TEXT,
    genai_position TEXT,
    genai_facing_count TEXT,
    genai_has_label TEXT,
    genai_confidence TEXT,
    source_table TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    WITH 
    -- Get store_codes matching region and channel from store table
    filtered_stores AS (
        SELECT DISTINCT s.store_code
        FROM store s
        WHERE s.store_code IS NOT NULL
            AND (p_region IS NULL OR s.region = p_region)
            AND (p_channel IS NULL OR s.channel = p_channel)
    ),
    
    -- Filter temp table with store_codes from filtered_stores, customer_id, and week_start_date
    temp_latest_weeks AS (
        SELECT DISTINCT ON (t.store_code, t.customer_id)
            t.store_code,
            t.customer_id,
            t.week_start_date::date AS latest_week_start_date
        FROM task t
        INNER JOIN filtered_stores fs ON t.store_code = fs.store_code
        WHERE t.week_start_date IS NOT NULL
            AND t.store_code IS NOT NULL
            AND t.customer_id IS NOT NULL
            AND t.customer_id = p_customer_id
            AND t.week_start_date::date = p_week_start_date
        ORDER BY t.store_code, t.customer_id, t.week_start_date::date DESC
    ),
    
    -- Part A: sjreport data (optimized with early filters)
    sjreport_rows AS (
        SELECT
            r.id::text AS item_id,
            r.store_code::text AS store_code,
            r.store_name::text AS store_name,
            r.channel::text AS channel,
            r.customer_id::text AS customer_id,
            r.product_id::text AS product_id,
            r.issue_for_feedback::text AS issue_for_feedback,
            r.week_start_date::text AS week_start_date,
            r.created_at AS created_at,
            r.created_at AS updated_at,
            NULL::text AS note,
            NULL::text AS image_url,
            NULL::text AS fixed_at,
            NULL::text AS rep_full_name,
            NULL::text AS gps,
            r.article::text AS article,
            r.dc_soh::text AS dc_soh,
            r.last_order::text AS last_ordered,
            r.last_receive::text AS last_received,
            r.store_soh::text AS store_soh,
            r.system_dros::text AS system_dros,
            r.scoring_system::text AS scoring_system,
            r.last_sold::text AS last_sold,
            r.category::text AS category,
            r.brand::text AS brand,
            r.product::text AS product,
            r.variant::text AS variant,
            r.size::text AS size,
            r.status::text AS status,
            NULL::text AS genai_position,
            NULL::text AS genai_facing_count,
            NULL::text AS genai_has_label,
            NULL::text AS genai_confidence,
            'sjreport'::text AS source_table
        FROM sjreport r
        INNER JOIN temp_latest_weeks lw 
            ON r.store_code = lw.store_code 
            AND r.customer_id::integer = lw.customer_id
            AND r.week_start_date::date = lw.latest_week_start_date
        WHERE r.issue_for_feedback = 'NO ISSUE - ITEM SELLING'
    ),
    
    -- Part B: Task data for fixed items
    -- Filtered by store_code & customer_id pairs
    tasks_week AS (
        SELECT
            t.id,
            t.store_code,
            t.customer_id,
            t.product_id,
            t.name,
            t.week_start_date,
            t.created_at,
            t.updated_at,
            t.note,
            t.image_url,
            t.genai_position,
            t.genai_facing_count,
            t.genai_has_label,
            t.genai_confidence,
            t.article,
            t.dc_soh,
            t.last_ordered,
            t.last_received,
            t.store_soh,
            t.system_dros,
            t.scoring_system,
            t.last_sold,
            t.chain,
            t.store_name,
            t.category,
            t.brand,
            t.product,
            t.variant,
            t.size,
            t.status
        FROM task t
        INNER JOIN temp_latest_weeks lw 
            ON t.store_code = lw.store_code 
            AND t.customer_id = lw.customer_id
            AND t.week_start_date::date = lw.latest_week_start_date
    ),
    
    pi_latest AS (
        SELECT DISTINCT ON (p.task_id)
            p.task_id,
            p.uploaded_at AS fixed_at,
            p.image_path AS image_path,
            t.store_code,
            t.customer_id,
            t.week_start_date
        FROM product_images p
        INNER JOIN tasks_week t ON p.task_id = t.id
        WHERE (p.uploaded_at::date) BETWEEN (t.week_start_date::date - INTERVAL '6 days') 
            AND (t.week_start_date::date)
        ORDER BY p.task_id, p.uploaded_at DESC
    ),
    
    ua_latest AS (
        SELECT DISTINCT ON (ua.item_id)
            ua.item_id,
            ua.rep_name,
            ua.gps
        FROM user_activity ua
        WHERE EXISTS (
            SELECT 1 
            FROM tasks_week t 
            WHERE t.id::text = ua.item_id
                AND t.customer_id = ua.customer_id
        )
        ORDER BY ua.item_id, ua.created_at DESC
    ),
    
    task_with_rep AS (
        SELECT
            t.id::text AS item_id,
            t.store_code::text AS store_code,
            t.store_name::text AS store_name,
            t.chain::text AS channel,
            t.customer_id::text AS customer_id,
            t.product_id::text AS product_id,
            t.name::text AS issue_for_feedback,
            t.week_start_date::text AS week_start_date,
            t.created_at AS created_at,
            t.updated_at AS updated_at,
            t.note::text AS note,
            pi.image_path::text AS image_url,
            pi.fixed_at AS fixed_at_timestamp,
            pi.fixed_at::text AS fixed_at,
            ua.rep_name::text AS rep_full_name,
            ua.gps::text AS gps,
            t.article::text AS article,
            t.dc_soh::text AS dc_soh,
            t.last_ordered::text AS last_ordered,
            t.last_received::text AS last_received,
            t.store_soh::text AS store_soh,
            t.system_dros::text AS system_dros,
            t.scoring_system::text AS scoring_system,
            t.last_sold::text AS last_sold,
            t.category::text AS category,
            t.brand::text AS brand,
            t.product::text AS product,
            t.variant::text AS variant,
            t.size::text AS size,
            t.status::text AS status,
            t.genai_position::text AS genai_position,
            t.genai_facing_count::text AS genai_facing_count,
            t.genai_has_label::text AS genai_has_label,
            t.genai_confidence::text AS genai_confidence,
            'task'::text AS source_table
        FROM tasks_week t
        INNER JOIN pi_latest pi ON pi.task_id = t.id 
            AND pi.store_code = t.store_code 
            AND pi.customer_id = t.customer_id
        LEFT JOIN ua_latest ua ON ua.item_id = t.id::text
    ),
    
    task_with_rep_latest AS (
        SELECT DISTINCT ON (twr.item_id)
            twr.item_id,
            twr.store_code,
            twr.store_name,
            twr.channel,
            twr.customer_id,
            twr.product_id,
            twr.issue_for_feedback,
            twr.week_start_date,
            twr.created_at,
            twr.updated_at,
            twr.note,
            twr.image_url,
            twr.fixed_at,
            twr.rep_full_name,
            twr.gps,
            twr.article,
            twr.dc_soh,
            twr.last_ordered,
            twr.last_received,
            twr.store_soh,
            twr.system_dros,
            twr.scoring_system,
            twr.last_sold,
            twr.category,
            twr.brand,
            twr.product,
            twr.variant,
            twr.size,
            twr.status,
            twr.genai_position,
            twr.genai_facing_count,
            twr.genai_has_label,
            twr.genai_confidence,
            twr.source_table
        FROM task_with_rep twr
        ORDER BY twr.item_id, twr.fixed_at_timestamp DESC NULLS LAST
    )
    
    -- Combine both parts
    SELECT * FROM sjreport_rows
    UNION ALL
    SELECT * FROM task_with_rep_latest;
END;
$$;

