-- Function to process records from store_processing_queue in batches
-- Calls process_store_category_data instead of update_store_metrics

CREATE OR REPLACE FUNCTION process_store_processing_queue()
RETURNS text[]
LANGUAGE plpgsql
AS $$
DECLARE
  v_max_retries         integer := 2;
  v_batch_size          integer := 10;          -- use 1 for testing; increase as safe
  rec                   record;
  results               text[] := '{}';
  processing_start_time timestamptz;
  debug_info            text;
  v_per_row_timeout     text := '10s';         -- tune to your gateway limits
  v_total_budget        interval := '30s';
  v_started             timestamptz := clock_timestamp();
  v_availability        bigint;
  v_category_res         jsonb;                 -- capture process_store_category_data result

BEGIN
  PERFORM set_config('lock_timeout', '1s', true);
  PERFORM set_config('idle_in_transaction_session_timeout', '1min', true);
  PERFORM set_config('statement_timeout', '0', true);

  FOR rec IN
    (
      WITH to_pick AS (
        SELECT spq.id
        FROM store_processing_queue spq
        WHERE (spq.status IS NULL
               OR spq.status = 'pending'
               OR (spq.status IN ('timeout','error')
                   AND COALESCE(spq.retry_count,0) < v_max_retries))
          AND spq.processed = false
        ORDER BY
          CASE WHEN spq.status IS NULL OR spq.status='pending' THEN 0 ELSE 1 END,
          spq.created_at ASC
        FOR UPDATE SKIP LOCKED
        LIMIT v_batch_size
      ),
      marked AS (
        UPDATE store_processing_queue spq
        SET status = 'processing',
            started_at = now(),
            updated_at = now()
        FROM to_pick
        WHERE spq.id = to_pick.id
        RETURNING spq.*
      )
      SELECT * FROM marked
    )
  LOOP
    -- overall budget guard
    IF clock_timestamp() - v_started > v_total_budget THEN
      results := array_append(results, 'STOP: total time budget exceeded; will continue next run');
      EXIT;
    END IF;

    BEGIN
      processing_start_time := clock_timestamp();
      debug_info := 'Processing Record ID: '||rec.id||
                    ', Store: '||rec.store_code||
                    ', Customer: '||rec.customer_id||
                    ', Week: '||rec.week_start_date;
      RAISE NOTICE 'Debug: %', debug_info;

      -- per-record timeout guard for heavy work
      PERFORM set_config('statement_timeout', v_per_row_timeout, true);

      -- call process_store_category_data for this single store
      BEGIN
        SELECT process_store_category_data(
                 rec.customer_id,
                 rec.store_code,             -- single store as comma-less text
                 rec.week_start_date::text   -- single week
               )
        INTO v_category_res;

        -- append the JSON result (stringified) for logging
        results := array_append(
          results,
          'process_store_category_data result for record '||rec.id||': '||coalesce(v_category_res::text, 'null')
        );
      EXCEPTION
        WHEN OTHERS THEN
          -- If process_store_category_data itself errors, capture and continue to retry logic below
          PERFORM set_config('statement_timeout', '0', true);
          RAISE NOTICE 'process_store_category_data failed for record %: %', rec.id, SQLERRM;
          -- re-raise to be handled by outer EXCEPTION block (so retry/counting logic applies)
          RAISE;
      END;

      -- product updates (unchanged)
      PERFORM update_product_store_data(rec.customer_id, rec.store_code, rec.week_start_date);

      -- reset before cheap writes
      PERFORM set_config('statement_timeout', '0', true);

      ----------------------------------------------------------------
      -- AVAILABILITY / VISIBILITY (customer_id + store_code + week) --
      ----------------------------------------------------------------
      BEGIN
        SELECT sul.availability
        INTO v_availability
        FROM store_user_link sul
        WHERE sul.store_code  = rec.store_code
          AND sul.customer_id = rec.customer_id
        LIMIT 1;

        IF v_availability IS NULL THEN
          RAISE NOTICE
            'No availability found for customer % store % (week %). Skipping visibility upsert.',
            rec.customer_id, rec.store_code, rec.week_start_date;
        ELSE
          INSERT INTO store_visibility (
              customer_id,
              store_code,
              week_start_date,
              created_at,
              visibility
          )
          VALUES (
              rec.customer_id,
              rec.store_code,
              rec.week_start_date::date,
              now(),
              v_availability
          )
          ON CONFLICT (customer_id, store_code, week_start_date)
          DO UPDATE
          SET visibility = EXCLUDED.visibility;

          RAISE NOTICE
            'Upserted store_visibility for customer % store % week % -> visibility=%',
            rec.customer_id, rec.store_code, rec.week_start_date, v_availability;
        END IF;
      EXCEPTION
        WHEN OTHERS THEN
          -- surface errors during testing (change to NOTICE-only or swallow in production)
          RAISE NOTICE
            'store_visibility upsert failed for customer % store % week % : %',
            rec.customer_id, rec.store_code, rec.week_start_date, SQLERRM;
      END;

      -- mark processed
      UPDATE store_processing_queue spq
      SET processed = true,
          processed_at = now(),
          status = 'completed',
          completed_at = now(),
          updated_at = now(),
          processing_duration_seconds =
            EXTRACT(EPOCH FROM (clock_timestamp() - processing_start_time)),
          error_message = NULL
      WHERE spq.id = rec.id;

      results := array_append(
        results,
        'SUCCESS: Record ID '||rec.id||' processed in '||
        ROUND(EXTRACT(EPOCH FROM (clock_timestamp() - processing_start_time))::numeric, 2)::text||'s'
      );

    EXCEPTION
      WHEN query_canceled THEN
        PERFORM set_config('statement_timeout', '0', true);
        -- mark timeout + retry logic
        IF COALESCE(rec.retry_count,0) + 1 >= v_max_retries THEN
          UPDATE store_processing_queue spq
          SET status = 'failed',
              error_message = 'Permanently failed: '||(COALESCE(rec.retry_count,0)+1)||' timeout attempts',
              updated_at = now(),
              retry_count = COALESCE(rec.retry_count,0) + 1,
              last_retry_at = now()
          WHERE spq.id = rec.id;
          results := array_append(
            results,
            'FAILED: Record ID '||rec.id||' permanently failed after '||
            (COALESCE(rec.retry_count,0)+1)||' timeout attempts'
          );
        ELSE
          UPDATE store_processing_queue spq
          SET status = 'timeout',
              error_message = 'Timeout on attempt '||(COALESCE(rec.retry_count,0)+1)||' of '||v_max_retries,
              updated_at = now(),
              retry_count = COALESCE(rec.retry_count,0) + 1,
              last_retry_at = now()
          WHERE spq.id = rec.id;
          results := array_append(
            results,
            'TIMEOUT: Record ID '||rec.id||' (attempt '||
            (COALESCE(rec.retry_count,0)+1)||'/'||v_max_retries||') - will retry'
          );
        END IF;

      WHEN OTHERS THEN
        PERFORM set_config('statement_timeout', '0', true);
        UPDATE store_processing_queue spq
        SET status = 'error',
            error_message =
              'Processing error: '||SQLERRM||' (SQLSTATE: '||SQLSTATE||') | Debug: '||debug_info,
            updated_at = now(),
            retry_count = COALESCE(spq.retry_count,0) + 1,
            last_retry_at = now()
        WHERE spq.id = rec.id;
        results := array_append(
          results,
          'ERROR: Record ID '||rec.id||' - '||SQLERRM||' (SQLSTATE: '||SQLSTATE||')'
        );
    END;
  END LOOP;

  IF array_length(results,1) IS NULL THEN
    RAISE NOTICE
      'No processable records found. Check for records with status=failed or max retries exceeded';
  ELSE
    RAISE NOTICE E'%\n', array_to_string(results, E'\n');
  END IF;

  RETURN results;
END;
$$;

