-- Trigger function that fires when a task is updated
-- Calls process_store_category_data and update_product_store_data
-- Note: Removed process_store_metrics_set_based to avoid overwriting process_store_category_data updates
-- Passes parameters from the updated task

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_log_id           bigint;
  v_started_at       timestamptz := clock_timestamp();
  v_is_processing    boolean;
BEGIN
  -- Guard: prevent recursive trigger calls using temporary table
  -- Check if we're already processing
  BEGIN
    SELECT EXISTS (
      SELECT 1 FROM pg_temp.task_update_processing 
      WHERE session_id = pg_backend_pid()
    ) INTO v_is_processing;
  EXCEPTION
    WHEN undefined_table THEN
      -- Table doesn't exist yet, create it
      CREATE TEMP TABLE IF NOT EXISTS task_update_processing (
        session_id integer PRIMARY KEY,
        started_at timestamptz DEFAULT clock_timestamp()
      ) ON COMMIT DROP;
      v_is_processing := false;
  END;

  IF v_is_processing THEN
    -- Already processing, skip to prevent infinite loop
    RETURN NEW;
  END IF;

  -- Set flag to prevent recursive calls
  BEGIN
    INSERT INTO pg_temp.task_update_processing (session_id) 
    VALUES (pg_backend_pid())
    ON CONFLICT (session_id) DO NOTHING;
  EXCEPTION
    WHEN undefined_table THEN
      CREATE TEMP TABLE IF NOT EXISTS task_update_processing (
        session_id integer PRIMARY KEY,
        started_at timestamptz DEFAULT clock_timestamp()
      ) ON COMMIT DROP;
      INSERT INTO pg_temp.task_update_processing (session_id) 
      VALUES (pg_backend_pid());
  END;

  -- Guard: only process UPDATE operations
  IF TG_OP != 'UPDATE' THEN
    DELETE FROM pg_temp.task_update_processing WHERE session_id = pg_backend_pid();
    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
    DELETE FROM pg_temp.task_update_processing WHERE session_id = pg_backend_pid();
    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;

  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
  BEGIN
    DELETE FROM pg_temp.task_update_processing WHERE session_id = pg_backend_pid();
  EXCEPTION
    WHEN undefined_table THEN
      -- Table doesn't exist, that's fine
      NULL;
  END;

  RETURN NEW;
EXCEPTION
  WHEN OTHERS THEN
    -- Final error logging
    PERFORM set_config('statement_timeout', '0', true);
    -- Clear processing flag even on error
    BEGIN
      DELETE FROM pg_temp.task_update_processing WHERE session_id = pg_backend_pid();
    EXCEPTION
      WHEN undefined_table THEN
        NULL;
    END;
    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;
$$;

