-- Fixed set-based function that matches the loop-based function behavior
-- Key fixes:
-- 1. Fixed nested DECLARE block issue (moved variable declarations to top level)
-- 2. Changed category handling to get ALL customer categories (not just store-specific)
-- 3. Proper error handling

CREATE OR REPLACE FUNCTION process_store_metrics_set_based(
    p_store_codes_text TEXT,
    p_week_start_dates_text TEXT,
    p_customer_id INTEGER,
    p_per_store_timeout TEXT DEFAULT '60s'
)
RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
    v_started           timestamptz := clock_timestamp();
    v_result_json       jsonb := '[]'::jsonb;
    v_error_json        jsonb;
    -- validation counters
    v_input_count       int;
    v_parsed_stores_count int;
    v_parsed_weeks_count int;
    v_rows_updated      int;
BEGIN
    -- session guards (tunable)
    PERFORM set_config('lock_timeout', '5s', true); -- avoid long locks
    PERFORM set_config('idle_in_transaction_session_timeout', '10min', true);
    PERFORM set_config('statement_timeout', p_per_store_timeout, true);

    -- Parse and validate input pairing using ordinality
    WITH
    parsed_stores AS (
      SELECT trim(s) as store_code, rn
      FROM regexp_split_to_table(coalesce(p_store_codes_text, ''), ',') WITH ORDINALITY AS t(s, rn)
      WHERE trim(s) <> ''
    ),
    parsed_weeks AS (
      SELECT trim(s) as week_start_date, rn
      FROM regexp_split_to_table(coalesce(p_week_start_dates_text, ''), ',') WITH ORDINALITY AS t(s, rn)
      WHERE trim(s) <> ''
    ),
    paired AS (
      SELECT ps.rn, ps.store_code, pw.week_start_date
      FROM parsed_stores ps
      JOIN parsed_weeks pw USING (rn)
    ),
    counts AS (
      SELECT 
        (SELECT count(*) FROM parsed_stores) as stores_count,
        (SELECT count(*) FROM parsed_weeks) as weeks_count,
        (SELECT count(*) FROM paired) as paired_count
    )
    SELECT paired_count, stores_count, weeks_count
    INTO v_input_count, v_parsed_stores_count, v_parsed_weeks_count
    FROM counts;

    IF v_input_count = 0 THEN
        RETURN jsonb_build_object('error','No store_codes / week_start_dates provided or parsing resulted in zero items');
    END IF;

    -- Check if counts mismatch
    IF v_parsed_stores_count != v_parsed_weeks_count THEN
        RETURN jsonb_build_object('error', format('Mismatched input array lengths: stores=%s weeks=%s', v_parsed_stores_count, v_parsed_weeks_count));
    END IF;

    -- Main set-based computation wrapped to catch statement timeouts or cancellations
    BEGIN
        WITH
        -- 1) Parse input pairs - these define the exact store_code, customer_id, week_start_date combinations
        input_pairs AS (
          SELECT trim(s) as store_code, rn
          FROM regexp_split_to_table(coalesce(p_store_codes_text, ''), ',') WITH ORDINALITY AS t(s, rn)
          WHERE trim(s) <> ''
        ),
        input_weeks AS (
          SELECT trim(s) as week_start_date, rn
          FROM regexp_split_to_table(coalesce(p_week_start_dates_text, ''), ',') WITH ORDINALITY AS t(s, rn)
          WHERE trim(s) <> ''
        ),
        paired_input AS (
          SELECT 
            ip.store_code, 
            iw.week_start_date,
            p_customer_id AS customer_id
          FROM input_pairs ip
          JOIN input_weeks iw USING (rn)
        ),
        -- 2) deduplicate sjreport to latest row per logical product group using row_number()
        -- Filter by exact store_code, customer_id, AND week_start_date from paired_input
        sj_dedup AS (
          SELECT
            sj.customer_id,
            sj.store_code,
            sj.week_start_date,
            sj.channel,
            sj.store_name,
            sj.category,
            sj.product,
            sj.variant,
            sj.size,
            sj.category_image,
            sj.created_at,
            sj.id,
            regexp_replace(lower(trim(sj.category)), '[^a-z0-9]+', '_', 'g') AS norm_category,
            row_number() OVER (
              PARTITION BY sj.customer_id, sj.store_code, sj.week_start_date, sj.channel, sj.store_name, sj.category, sj.product, sj.variant, sj.size
              ORDER BY sj.created_at DESC, sj.id DESC
            ) rn
          FROM sjreport sj
          INNER JOIN paired_input pi ON 
            sj.customer_id = pi.customer_id
            AND sj.store_code = pi.store_code
            AND sj.week_start_date::text = pi.week_start_date::text
        ),
        sj_latest AS (
          SELECT * FROM sj_dedup WHERE rn = 1
        ),
        -- 4) total soh per store+week
        soh_by_store AS (
          SELECT store_code, week_start_date, COUNT(*) AS total_soh
          FROM sj_latest
          GROUP BY store_code, week_start_date
        ),
        -- 5) soh per category
        soh_by_category AS (
          SELECT store_code, week_start_date, norm_category, COUNT(*) AS category_soh
          FROM sj_latest
          GROUP BY store_code, week_start_date, norm_category
        ),
        -- 3) task aggregates per store+customer+week (counts & fixed/not fixed)
        -- Filter by exact store_code, customer_id, AND week_start_date from paired_input
        task_aggs AS (
          SELECT
            t.store_code,
            t.week_start_date,
            COUNT(*) FILTER (WHERE t.name = 'NO SALES - FIX BALANCE/SHOW STOCK ON SHELF')              AS soh_no_sales,
            COUNT(*) FILTER (WHERE t.name = 'OOS - PLACE ORDER')                                      AS oos,
            COUNT(*) FILTER (WHERE t.name = 'NEGATIVE STOCK - FIX STOCK BALANCE')                     AS negative_stock,
            COUNT(*) FILTER (WHERE t.name = 'NO SALES 100 DAYS PLUS - FIX STOCK BALANCE')             AS no_sales_100_days,
            COUNT(*) FILTER (WHERE t.name = '<7 DAYS COVER - SEND FD CATEGORY PHOTO')                 AS seven_day_cover,
            COUNT(*) FILTER (WHERE t.status = 'Fixed')                                                AS fixed_count,
            COUNT(*) FILTER (WHERE t.status = 'Not Fixed')                                            AS not_fixed_count
          FROM task t
          INNER JOIN paired_input pi ON 
            t.customer_id = pi.customer_id
            AND t.store_code = pi.store_code
            AND t.week_start_date::text = pi.week_start_date::text
          GROUP BY t.store_code, t.week_start_date
        ),
        -- 7) ALL customer categories (across all stores) - matching loop-based function behavior
        all_customer_categories AS (
          SELECT DISTINCT ON (norm_category)
            norm_category,
            cat AS category_name
          FROM (
            SELECT
              regexp_replace(lower(trim(t.category)), '[^a-z0-9]+', '_', 'g') AS norm_category,
              t.category AS cat,
              t.created_at
            FROM task t
            WHERE t.customer_id = p_customer_id
              AND t.category IS NOT NULL
            UNION ALL
            SELECT
              regexp_replace(lower(trim(sj.category)), '[^a-z0-9]+', '_', 'g') AS norm_category,
              sj.category AS cat,
              sj.created_at
            FROM sjreport sj
            WHERE sj.customer_id = p_customer_id
              AND sj.category IS NOT NULL
          ) u
          ORDER BY norm_category, created_at DESC
        ),
        -- 4) task category metrics per store+customer+week+category
        -- Filter by exact store_code, customer_id, AND week_start_date from paired_input
        task_category_metrics AS (
          SELECT
            t.store_code,
            t.week_start_date,
            regexp_replace(lower(trim(t.category)), '[^a-z0-9]+', '_', 'g') AS norm_category,
            COUNT(*) FILTER (WHERE t.name = 'NO SALES - FIX BALANCE/SHOW STOCK ON SHELF') AS soh_no_sales,
            COUNT(*) FILTER (WHERE t.name = 'OOS - PLACE ORDER') AS oos,
            COUNT(*) FILTER (WHERE t.name = 'NEGATIVE STOCK - FIX STOCK BALANCE') AS negative_stock,
            COUNT(*) FILTER (WHERE t.name = 'NO SALES 100 DAYS PLUS - FIX STOCK BALANCE') AS no_sales_100_days,
            COUNT(*) FILTER (WHERE t.name = '<7 DAYS COVER - SEND FD CATEGORY PHOTO') AS seven_day_cover,
            COUNT(*) FILTER (WHERE t.status = 'Fixed') AS fixed_count,
            COUNT(*) FILTER (WHERE t.status = 'Not Fixed') AS not_fixed_count
          FROM task t
          INNER JOIN paired_input pi ON 
            t.customer_id = pi.customer_id
            AND t.store_code = pi.store_code
            AND t.week_start_date::text = pi.week_start_date::text
          GROUP BY t.store_code, t.week_start_date, norm_category
        ),
        -- 5) choose latest category image/name from task first, then sjreport, per store/customer/week/category
        -- Filter by exact store_code, customer_id, AND week_start_date from paired_input
        task_cat_images AS (
          SELECT DISTINCT ON (norm_category, store_code, week_start_date)
            store_code,
            week_start_date,
            norm_category,
            category_name,
            category_image
          FROM (
            SELECT
              t.store_code,
              t.week_start_date,
              regexp_replace(lower(trim(t.category)), '[^a-z0-9]+', '_', 'g') AS norm_category,
              t.category AS category_name,
              t.category_image,
              t.created_at
            FROM task t
            INNER JOIN paired_input pi ON 
              t.customer_id = pi.customer_id
              AND t.store_code = pi.store_code
              AND t.week_start_date::text = pi.week_start_date::text
          ) x
          ORDER BY norm_category, store_code, week_start_date, created_at DESC
        ),
        sj_cat_images AS (
          SELECT DISTINCT ON (norm_category, store_code, week_start_date)
            store_code,
            week_start_date,
            norm_category,
            category_name,
            category_image
          FROM (
            SELECT
              sj.store_code,
              sj.week_start_date,
              regexp_replace(lower(trim(sj.category)), '[^a-z0-9]+', '_', 'g') AS norm_category,
              sj.category AS category_name,
              sj.category_image,
              sj.created_at
            FROM sjreport sj
            INNER JOIN paired_input pi ON 
              sj.customer_id = pi.customer_id
              AND sj.store_code = pi.store_code
              AND sj.week_start_date::text = pi.week_start_date::text
          ) x
          ORDER BY norm_category, store_code, week_start_date, created_at DESC
        ),
        -- 6) Build category-level metrics (one row per store_code + customer_id + week_start_date + norm_category)
        -- Using ALL customer categories, not just store-specific ones
        category_metrics AS (
          SELECT
            pi.store_code,
            pi.customer_id,
            pi.week_start_date,
            acc.norm_category,
            acc.category_name,
            COALESCE(tcm.soh_no_sales,0)     AS soh_no_sales,
            COALESCE(tcm.oos,0)              AS oos,
            COALESCE(tcm.negative_stock,0)   AS negative_stock,
            COALESCE(tcm.no_sales_100_days,0) AS no_sales_100_days,
            COALESCE(tcm.seven_day_cover,0)  AS seven_day_cover,
            COALESCE(tcm.fixed_count,0)      AS fixed_count,
            COALESCE(tcm.not_fixed_count,0)  AS not_fixed_count,
            COALESCE(sbc.category_soh,0)     AS category_soh,
            COALESCE(ti.category_image, sj_i.category_image, 'https://datafy.appmonitor.co.za/storage/v1/object/public/uploads/R%20(2).png') AS category_image
          FROM paired_input pi
          CROSS JOIN all_customer_categories acc
          LEFT JOIN task_category_metrics tcm ON 
            tcm.store_code = pi.store_code 
            AND tcm.week_start_date::text = pi.week_start_date::text
            AND tcm.norm_category = acc.norm_category
          LEFT JOIN soh_by_category sbc ON 
            sbc.store_code = pi.store_code 
            AND sbc.week_start_date::text = pi.week_start_date::text
            AND sbc.norm_category = acc.norm_category
          LEFT JOIN task_cat_images ti ON 
            ti.store_code = pi.store_code 
            AND ti.week_start_date::text = pi.week_start_date::text
            AND ti.norm_category = acc.norm_category
          LEFT JOIN sj_cat_images sj_i ON 
            sj_i.store_code = pi.store_code 
            AND sj_i.week_start_date::text = pi.week_start_date::text
            AND sj_i.norm_category = acc.norm_category
        ),
        -- 7) assemble per-store JSON and numeric metrics
        -- All metrics calculated per store_code, customer_id, AND week_start_date
        store_metrics AS (
          SELECT
            pi.customer_id,
            pi.store_code,
            pi.week_start_date,
            COALESCE(ta.soh_no_sales,0)        AS soh_no_sales,
            COALESCE(ta.oos,0)                 AS oos,
            COALESCE(ta.negative_stock,0)      AS negative_stock,
            COALESCE(ta.no_sales_100_days,0)   AS no_sales_100_days,
            COALESCE(ta.seven_day_cover,0)     AS seven_day_cover,
            COALESCE(ta.fixed_count,0)         AS fixed_count,
            COALESCE(ta.not_fixed_count,0)     AS not_fixed_count,
            COALESCE(ss.total_soh,0)           AS total_soh,
            CASE WHEN COALESCE(ss.total_soh,0) > 0
                 THEN ((100/ss.total_soh::float) * (ss.total_soh - COALESCE(ta.not_fixed_count,0))::float)::BIGINT
                 ELSE 0 END                       AS availability,
            COALESCE(jsonb_agg(
              jsonb_build_object(
                'category', cm.norm_category,
                'category_name', cm.category_name,
                'category_image', cm.category_image,
                'soh_no_sales', cm.soh_no_sales,
                'oos', cm.oos,
                'negative_stock', cm.negative_stock,
                'no_sales_100_days', cm.no_sales_100_days,
                'seven_day_cover', cm.seven_day_cover,
                'total_soh', cm.category_soh,
                'availability', CASE WHEN cm.category_soh > 0 THEN ((100/cm.category_soh::float) * (cm.category_soh - cm.not_fixed_count)::float)::BIGINT ELSE 0 END,
                'fixed_count', cm.fixed_count,
                'not_fixed_count', cm.not_fixed_count,
                'total_issues', cm.soh_no_sales + cm.oos + cm.negative_stock + cm.no_sales_100_days + cm.seven_day_cover
              )
            ) FILTER (WHERE cm.norm_category IS NOT NULL), '[]'::jsonb) AS categories
          FROM paired_input pi
          LEFT JOIN task_aggs ta ON 
            ta.store_code = pi.store_code 
            AND ta.week_start_date::text = pi.week_start_date::text
          LEFT JOIN soh_by_store ss ON 
            ss.store_code = pi.store_code 
            AND ss.week_start_date::text = pi.week_start_date::text
          LEFT JOIN category_metrics cm ON 
            cm.store_code = pi.store_code 
            AND cm.customer_id = pi.customer_id
            AND cm.week_start_date::text = pi.week_start_date::text
          GROUP BY pi.customer_id, pi.store_code, pi.week_start_date, ta.soh_no_sales, ta.oos, ta.negative_stock, ta.no_sales_100_days, ta.seven_day_cover, ta.fixed_count, ta.not_fixed_count, ss.total_soh
        ),
        -- 12) UPDATE store_user_link (must be part of WITH clause to access store_metrics)
        -- Update ALL rows for store_code + customer_id combination (one per user_id)
        -- This ensures all users' rows get updated with the latest metrics for that week
        update_result AS (
          UPDATE store_user_link sul
          SET
            soh_no_sales = sm.soh_no_sales,
            oos = sm.oos,
            negative_stock = sm.negative_stock,
            no_sales_100_days = sm.no_sales_100_days,
            seven_day_cover = sm.seven_day_cover,
            total_soh = sm.total_soh,
            availability = sm.availability,
            fixed_count = sm.fixed_count,
            not_fixed_count = sm.not_fixed_count,
            week_start_date = sm.week_start_date,
            category_data = sm.categories,
            updated_at = now()
          FROM store_metrics sm
          WHERE sul.store_code = sm.store_code
            AND sul.customer_id = sm.customer_id
          RETURNING sul.id
        ),
        -- Get row count from update_result and build result JSON from store_metrics
        result_data AS (
          SELECT 
            (SELECT COUNT(*) FROM update_result) as rows_updated,
            COALESCE(jsonb_agg(
              jsonb_build_object(
                'store_code', store_code,
                'week_start_date', week_start_date,
                'customer_id', customer_id,
                'soh_no_sales', soh_no_sales,
                'oos', oos,
                'negative_stock', negative_stock,
                'no_sales_100_days', no_sales_100_days,
                'seven_day_cover', seven_day_cover,
                'total_soh', total_soh,
                'availability', availability,
                'fixed_count', fixed_count,
                'not_fixed_count', not_fixed_count,
                'categories', categories
              )
            ), '[]'::jsonb) as result_json
          FROM store_metrics
        )
        SELECT 
          rows_updated,
          result_json
        INTO 
          v_rows_updated,
          v_result_json
        FROM result_data;
        
        RAISE NOTICE 'process_store_metrics_set_based: Updated % row(s) in store_user_link for customer_id=%, store_code=%, week_start_date=%', 
          v_rows_updated, p_customer_id, p_store_codes_text, p_week_start_dates_text;
        
        IF v_rows_updated = 0 THEN
          RAISE WARNING 'process_store_metrics_set_based: No rows updated in store_user_link. Check if rows exist for customer_id=%, store_code=%, week_start_date=%', 
            p_customer_id, p_store_codes_text, p_week_start_dates_text;
        END IF;

        -- lift statement timeout for final steps (if you want writes not to be canceled)
        PERFORM set_config('statement_timeout', '0', true);
        RETURN v_result_json;
    EXCEPTION
        WHEN query_canceled THEN
            -- query timed out according to statement_timeout
            PERFORM set_config('statement_timeout', '0', true);
            v_error_json := jsonb_build_object(
                'error', 'query_canceled',
                'message', format('statement timeout after %s', p_per_store_timeout),
                'started_at', v_started
            );
            RETURN v_error_json;
        WHEN OTHERS THEN
            -- general catch: return the error for debugging
            PERFORM set_config('statement_timeout', '0', true);
            v_error_json := jsonb_build_object(
                'error', 'unexpected_exception',
                'message', SQLERRM,
                'pg_code', SQLSTATE
            );
            RETURN v_error_json;
    END;
EXCEPTION
    WHEN OTHERS THEN
        -- final fallback
        RETURN jsonb_build_object('error', 'fatal', 'message', SQLERRM);
END;
$$;

