-- Deployment script for task_update_trigger.sql with recursive call guard fix
-- Run this in Supabase SQL Editor to deploy the updated function
-- This fixes the infinite loop issue where process_store_category_data runs continuously

-- 1. Deploy the updated trigger function with recursive call guard
CREATE OR REPLACE FUNCTION fn_task_update_process_metrics()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
  v_per_row_timeout  text     := '10s';
  v_total_budget     interval := '30s';
  v_category_res     jsonb;
  v_metrics_res      jsonb;
  v_log_id           bigint;
  v_started_at       timestamptz := clock_timestamp();
  v_processing_flag  text;
BEGIN
  -- Guard: prevent recursive trigger calls
  -- Check if we're already processing (set by this function)
  v_processing_flag := current_setting('task_update_processing', true);
  IF v_processing_flag = 'true' THEN
    -- Already processing, skip to prevent infinite loop
    RETURN NEW;
  END IF;

  -- Set flag to prevent recursive calls
  PERFORM set_config('task_update_processing', 'true', true);

  -- Guard: only process UPDATE operations
  IF TG_OP != 'UPDATE' THEN
    PERFORM set_config('task_update_processing', 'false', true);
    RETURN NEW;
  END IF;

  -- Guard: ensure required fields are present
  IF NEW.customer_id IS NULL OR NEW.store_code IS NULL OR NEW.week_start_date IS NULL THEN
    PERFORM set_config('task_update_processing', 'false', true);
    RETURN NEW;
  END IF;

  -- Log trigger fire
  BEGIN
    INSERT INTO trigger_execution_log (
      trigger_name,
      task_id,
      customer_id,
      store_code,
      week_start_date,
      old_status,
      new_status,
      execution_started_at,
      status,
      note
    ) VALUES (
      'trg_task_update_process_metrics',
      NEW.id,
      NEW.customer_id,
      NEW.store_code,
      NEW.week_start_date,
      OLD.status,
      NEW.status,
      v_started_at,
      'processing',
      format('TG_OP=%s - Processing task update', TG_OP)
    ) RETURNING id INTO v_log_id;
  EXCEPTION
    WHEN OTHERS THEN
      -- If logging fails, continue anyway (don't break trigger)
      v_log_id := NULL;
  END;

  -- Update log: processing started
  IF v_log_id IS NOT NULL THEN
    UPDATE trigger_execution_log
    SET note = note || format(' - Task updated: customer_id=%s, store_code=%s, week_start_date=%s', 
                              NEW.customer_id, NEW.store_code, NEW.week_start_date)
    WHERE id = v_log_id;
  END IF;

  ----------------------------------------------------------------
  -- 1) Run process_store_category_data
  ----------------------------------------------------------------
  BEGIN
    PERFORM set_config('statement_timeout', v_per_row_timeout, true);

    SELECT process_store_category_data(
             NEW.customer_id,
             NEW.store_code,        -- single store as comma-separated (even if one value)
             NEW.week_start_date::text  -- single week as comma-separated (even if one value)
           )
    INTO v_category_res;

    -- Log success
    IF v_log_id IS NOT NULL THEN
      UPDATE trigger_execution_log
      SET note = note || ' - process_store_category_data completed'
      WHERE id = v_log_id;
    END IF;

  EXCEPTION
    WHEN OTHERS THEN
      PERFORM set_config('statement_timeout', '0', true);
      -- Log error but continue
      IF v_log_id IS NOT NULL THEN
        UPDATE trigger_execution_log
        SET note = note || format(' - process_store_category_data failed: %s', SQLERRM)
        WHERE id = v_log_id;
      END IF;
  END;

  ----------------------------------------------------------------
  -- 2) Run update_product_store_data
  ----------------------------------------------------------------
  BEGIN
    PERFORM update_product_store_data(
      NEW.customer_id,
      NEW.store_code,        -- single store as comma-separated (even if one value)
      NEW.week_start_date::text  -- single week as comma-separated (even if one value)
    );
    
    -- Log success
    IF v_log_id IS NOT NULL THEN
      UPDATE trigger_execution_log
      SET update_product_store_data_called = TRUE,
          note = note || ' - update_product_store_data completed'
      WHERE id = v_log_id;
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      -- Log error but continue
      IF v_log_id IS NOT NULL THEN
        UPDATE trigger_execution_log
        SET note = note || format(' - update_product_store_data failed: %s', SQLERRM)
        WHERE id = v_log_id;
      END IF;
  END;

  ----------------------------------------------------------------
  -- 3) Run process_store_metrics_set_based
  ----------------------------------------------------------------
  BEGIN
    PERFORM set_config('statement_timeout', v_per_row_timeout, true);

    SELECT process_store_metrics_set_based(
             NEW.store_code,        -- p_store_codes_text (single store as comma-separated)
             NEW.week_start_date::text,  -- p_week_start_dates_text (single week as comma-separated)
             NEW.customer_id,      -- p_customer_id
             v_per_row_timeout     -- p_per_store_timeout
           )
    INTO v_metrics_res;

    -- Log success
    IF v_log_id IS NOT NULL THEN
      UPDATE trigger_execution_log
      SET update_store_metrics_result = v_metrics_res,
          note = note || ' - process_store_metrics_set_based completed'
      WHERE id = v_log_id;
    END IF;

  EXCEPTION
    WHEN OTHERS THEN
      PERFORM set_config('statement_timeout', '0', true);
      -- Log error but continue
      IF v_log_id IS NOT NULL THEN
        UPDATE trigger_execution_log
        SET status = 'error',
            error_message = SQLERRM,
            error_code = SQLSTATE,
            note = note || format(' - process_store_metrics_set_based failed: %s', SQLERRM)
        WHERE id = v_log_id;
      END IF;
  END;

  PERFORM set_config('statement_timeout', '0', true);

  -- Log final success
  IF v_log_id IS NOT NULL THEN
    UPDATE trigger_execution_log
    SET status = 'success',
        execution_completed_at = clock_timestamp(),
        execution_duration_ms = EXTRACT(EPOCH FROM (clock_timestamp() - v_started_at))::integer * 1000,
        note = note || ' - All functions completed successfully'
    WHERE id = v_log_id;
  END IF;

  -- Clear processing flag before returning
  PERFORM set_config('task_update_processing', 'false', true);

  RETURN NEW;
EXCEPTION
  WHEN OTHERS THEN
    -- Final error logging
    PERFORM set_config('statement_timeout', '0', true);
    -- Clear processing flag even on error
    PERFORM set_config('task_update_processing', 'false', true);
    IF v_log_id IS NOT NULL THEN
      UPDATE trigger_execution_log
      SET status = 'error',
          error_message = SQLERRM,
          error_code = SQLSTATE,
          note = COALESCE(note, '') || format(' - Fatal error: %s', SQLERRM),
          execution_completed_at = clock_timestamp(),
          execution_duration_ms = EXTRACT(EPOCH FROM (clock_timestamp() - v_started_at))::integer * 1000
      WHERE id = v_log_id;
    END IF;
    RAISE;
END;
$$;

-- 2. Verify the function was created/updated successfully
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM pg_proc 
    WHERE proname = 'fn_task_update_process_metrics'
  ) THEN
    RAISE NOTICE 'SUCCESS: Function fn_task_update_process_metrics exists';
  ELSE
    RAISE EXCEPTION 'ERROR: Function fn_task_update_process_metrics not found';
  END IF;
END $$;

-- 3. Verify the trigger exists
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM pg_trigger 
    WHERE tgname = 'trg_task_update_process_metrics'
  ) THEN
    RAISE NOTICE 'SUCCESS: Trigger trg_task_update_process_metrics exists';
  ELSE
    RAISE WARNING 'WARNING: Trigger trg_task_update_process_metrics not found. Run create_task_update_trigger.sql';
  END IF;
END $$;

