-- Optimized version of process_store_category_data
-- Performance improvements:
-- 1. Pre-compute normalized categories to avoid repeated regex operations
-- 2. Store week_start_date as text in paired_input to avoid type conversions
-- 3. Optimize all_customer_categories CTE with better filtering
-- 4. Use materialized CTEs where beneficial
-- 5. Ensure proper index usage by avoiding type conversions in WHERE clauses

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

    -- Parse and validate input pairing
    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;

    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 computation
    BEGIN
        WITH
        -- 1) Parse input pairs - store week_start_date as TEXT to avoid conversions
        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,  -- Already TEXT, no conversion needed
            p_customer_id AS customer_id
          FROM input_pairs ip
          JOIN input_weeks iw USING (rn)
        ),
        -- 2) Pre-compute normalized categories for sjreport (compute once, reuse)
        sj_with_norm_category 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,
            -- Compute normalized category once
            CASE 
              WHEN sj.category IS NOT NULL 
              THEN regexp_replace(lower(trim(sj.category)), '[^a-z0-9]+', '_', 'g')
              ELSE NULL
            END AS norm_category
          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 = pi.week_start_date  -- Direct text comparison (no ::text conversion)
        ),
        sj_dedup AS (
          SELECT
            *,
            row_number() OVER (
              PARTITION BY customer_id, store_code, week_start_date, channel, store_name, category, product, variant, size
              ORDER BY created_at DESC, id DESC
            ) rn
          FROM sj_with_norm_category
        ),
        sj_latest AS (
          SELECT * FROM sj_dedup WHERE rn = 1
        ),
        -- 3) SOH aggregations
        soh_by_store AS (
          SELECT store_code, week_start_date, COUNT(*) AS total_soh
          FROM sj_latest
          GROUP BY store_code, week_start_date
        ),
        soh_by_category AS (
          SELECT store_code, week_start_date, norm_category, COUNT(*) AS category_soh
          FROM sj_latest
          WHERE norm_category IS NOT NULL
          GROUP BY store_code, week_start_date, norm_category
        ),
        -- 4) Task aggregates - use direct text comparison
        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 = pi.week_start_date  -- Direct text comparison
          GROUP BY t.store_code, t.week_start_date
        ),
        -- 5) Pre-compute normalized categories for tasks (compute once)
        task_with_norm_category AS (
          SELECT
            t.store_code,
            t.week_start_date,
            t.category,
            t.category_image,
            t.created_at,
            t.name,
            t.status,
            -- Compute normalized category once
            CASE 
              WHEN t.category IS NOT NULL 
              THEN regexp_replace(lower(trim(t.category)), '[^a-z0-9]+', '_', 'g')
              ELSE NULL
            END AS norm_category
          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 = pi.week_start_date  -- Direct text comparison
        ),
        -- 6) ALL customer categories - optimized: only get categories that exist in paired_input stores/weeks
        -- This avoids scanning all customer data
        all_customer_categories AS (
          SELECT DISTINCT ON (norm_category)
            norm_category,
            cat AS category_name
          FROM (
            SELECT
              twc.norm_category,
              twc.category AS cat,
              twc.created_at
            FROM task_with_norm_category twc
            WHERE twc.norm_category IS NOT NULL
            UNION ALL
            SELECT
              sj.norm_category,
              sj.category AS cat,
              sj.created_at
            FROM sj_with_norm_category sj
            WHERE sj.norm_category IS NOT NULL
          ) u
          ORDER BY norm_category, created_at DESC
        ),
        -- 7) Task category metrics - reuse pre-computed normalized category
        task_category_metrics AS (
          SELECT
            store_code,
            week_start_date,
            norm_category,
            COUNT(*) FILTER (WHERE name = 'NO SALES - FIX BALANCE/SHOW STOCK ON SHELF') AS soh_no_sales,
            COUNT(*) FILTER (WHERE name = 'OOS - PLACE ORDER') AS oos,
            COUNT(*) FILTER (WHERE name = 'NEGATIVE STOCK - FIX STOCK BALANCE') AS negative_stock,
            COUNT(*) FILTER (WHERE name = 'NO SALES 100 DAYS PLUS - FIX STOCK BALANCE') AS no_sales_100_days,
            COUNT(*) FILTER (WHERE name = '<7 DAYS COVER - SEND FD CATEGORY PHOTO') AS seven_day_cover,
            COUNT(*) FILTER (WHERE status = 'Fixed') AS fixed_count,
            COUNT(*) FILTER (WHERE status = 'Not Fixed') AS not_fixed_count
          FROM task_with_norm_category
          WHERE norm_category IS NOT NULL
          GROUP BY store_code, week_start_date, norm_category
        ),
        -- 8) Category images - reuse pre-computed normalized category
        task_cat_images AS (
          SELECT DISTINCT ON (norm_category, store_code, week_start_date)
            store_code,
            week_start_date,
            norm_category,
            category AS category_name,
            category_image
          FROM task_with_norm_category
          WHERE norm_category IS NOT NULL
          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 AS category_name,
            category_image
          FROM sj_with_norm_category
          WHERE norm_category IS NOT NULL
          ORDER BY norm_category, store_code, week_start_date, created_at DESC
        ),
        -- 9) Build category-level metrics
        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 = pi.week_start_date  -- Direct text comparison
            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 = pi.week_start_date  -- Direct text comparison
            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 = pi.week_start_date  -- Direct text comparison
            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 = pi.week_start_date  -- Direct text comparison
            AND sj_i.norm_category = acc.norm_category
        ),
        -- 10) Assemble per-store JSON and numeric metrics
        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 = pi.week_start_date  -- Direct text comparison
          LEFT JOIN soh_by_store ss ON 
            ss.store_code = pi.store_code 
            AND ss.week_start_date = pi.week_start_date  -- Direct text comparison
          LEFT JOIN category_metrics cm ON 
            cm.store_code = pi.store_code 
            AND cm.customer_id = pi.customer_id
            AND cm.week_start_date = pi.week_start_date  -- Direct text comparison
          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
        ),
        -- 11) UPDATE store_user_link
        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
        ),
        -- 12) Build result JSON
        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_category_data_optimized: Updated % row(s) in store_user_link for customer_id=%, store_code=%, week_start_dates_text=%', 
          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_category_data_optimized: No rows updated in store_user_link. Check if rows exist for customer_id=%, store_code=%, week_start_dates_text=%', 
            p_customer_id, p_store_codes_text, p_week_start_dates_text;
        END IF;

        PERFORM set_config('statement_timeout', '0', true);
        RETURN v_result_json;
    EXCEPTION
        WHEN query_canceled THEN
            PERFORM set_config('statement_timeout', '0', true);
            v_error_json := jsonb_build_object(
                'error', 'query_canceled',
                'message', format('statement timeout after 60s'),
                'started_at', v_started
            );
            RETURN v_error_json;
        WHEN OTHERS THEN
            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
        RETURN jsonb_build_object('error', 'fatal', 'message', SQLERRM);
END;
$$;

