-- Trigger function that fires when a task status changes to 'Fixed'
-- Updates store metrics and product store data, then updates store_visibility

CREATE OR REPLACE FUNCTION fn_task_fixed_update_visibility()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
  v_per_row_timeout  text     := '10s';
  v_total_budget     interval := '30s';
  v_upd_res          jsonb;
  v_availability     bigint;  -- match store_user_link.availability type
  v_log_id           bigint;  -- log entry ID
  v_started_at        timestamptz := clock_timestamp();
BEGIN
  -- Log trigger fire (even if skipped)
  -- Wrap in exception handler so logging failure doesn't break trigger
  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_fixed_update_visibility',
      NEW.id,
      NEW.customer_id,
      NEW.store_code,
      NEW.week_start_date,
      OLD.status,
      NEW.status,
      v_started_at,
      'processing',
      format('TG_OP=%s', 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;

  -- Guard: only process when status changes to 'fixed' (case-insensitive)
  IF TG_OP != 'UPDATE' THEN
    IF v_log_id IS NOT NULL THEN
      UPDATE trigger_execution_log
      SET status = 'skipped',
          note = note || ' - Not an UPDATE operation',
          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;
    RETURN NEW;
  END IF;

  -- Check if status changed to 'fixed' (case-insensitive)
  IF lower(COALESCE(NEW.status, '')) != 'fixed' THEN
    IF v_log_id IS NOT NULL THEN
      UPDATE trigger_execution_log
      SET status = 'skipped',
          note = note || format(' - Status not "fixed": [%s]', NEW.status),
          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;
    RETURN NEW;
  END IF;

  -- Check if status actually changed
  IF lower(COALESCE(OLD.status, '')) = lower(COALESCE(NEW.status, '')) THEN
    IF v_log_id IS NOT NULL THEN
      UPDATE trigger_execution_log
      SET status = 'skipped',
          note = note || format(' - Status did not change: [%s]', NEW.status),
          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;
    RETURN NEW;
  END IF;

  -- Update log: processing started
  IF v_log_id IS NOT NULL THEN
    UPDATE trigger_execution_log
    SET note = note || format(' - Processing: status changed from [%s] to [%s]', OLD.status, NEW.status)
    WHERE id = v_log_id;
  END IF;

  ----------------------------------------------------------------
  -- 1) Run update_store_metrics using values from the TASK row --
  ----------------------------------------------------------------
  BEGIN
    PERFORM set_config('statement_timeout', v_per_row_timeout, true);

    SELECT update_store_metrics(
             NEW.customer_id,       -- from task
             NEW.store_code,        -- from task
             NEW.week_start_date,   -- from task
             v_per_row_timeout,
             v_total_budget
           )
    INTO v_upd_res;

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

  EXCEPTION
    WHEN OTHERS THEN
      PERFORM set_config('statement_timeout', '0', true);
      -- Log error
      IF v_log_id IS NOT NULL THEN
        UPDATE trigger_execution_log
        SET status = 'error',
            error_message = SQLERRM,
            error_code = SQLSTATE,
            note = note || format(' - update_store_metrics failed: %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) Run update_product_store_data for same customer/store/week
  ----------------------------------------------------------------
  BEGIN
    PERFORM update_product_store_data(
      NEW.customer_id,
      NEW.store_code,
      NEW.week_start_date
    );
    
    -- 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);

  ----------------------------------------------------------------
  -- 3) Fetch availability from store_user_link AFTER metrics ran --
  ----------------------------------------------------------------
  SELECT sul.availability
  INTO v_availability
  FROM store_user_link sul
  WHERE sul.store_code  = NEW.store_code
    AND sul.customer_id = NEW.customer_id
    AND (
      (sul.week_start_date IS NULL AND NEW.week_start_date IS NULL)
      OR sul.week_start_date::text = NEW.week_start_date::text
    )
  LIMIT 1;

  -- Log availability fetch result
  IF v_log_id IS NOT NULL THEN
    UPDATE trigger_execution_log
    SET availability_found = v_availability,
        note = note || format(' - Availability: %s', COALESCE(v_availability::text, 'NULL'))
    WHERE id = v_log_id;
  END IF;

  IF v_availability IS NULL THEN
    IF v_log_id IS NOT NULL THEN
      UPDATE trigger_execution_log
      SET status = 'skipped',
          note = note || ' - No availability found, skipping visibility upsert',
          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;
    RETURN NEW;
  END IF;

  ----------------------------------------------------------------
  -- 4) Upsert availability_score + task_id into store_visibility --
  ----------------------------------------------------------------
  BEGIN
    INSERT INTO store_visibility (
        customer_id,
        store_code,
        week_start_date,
        created_at,
        updated_at,
        availability_score,
        task_id
    )
    VALUES (
        NEW.customer_id,
        NEW.store_code,
        NEW.week_start_date::date,
        now(),
        now(),
        v_availability,
        NEW.id
    )
    ON CONFLICT (customer_id, store_code, week_start_date)
    DO UPDATE
    SET availability_score = EXCLUDED.availability_score,
        task_id           = EXCLUDED.task_id,
        updated_at        = now();

    -- Log success
    IF v_log_id IS NOT NULL THEN
      UPDATE trigger_execution_log
      SET store_visibility_updated = TRUE,
          note = note || format(' - store_visibility updated: availability_score=%s, task_id=%s', v_availability, NEW.id)
      WHERE id = v_log_id;
    END IF;

  EXCEPTION
    WHEN OTHERS THEN
      -- Log error
      IF v_log_id IS NOT NULL THEN
        UPDATE trigger_execution_log
        SET status = 'error',
            error_message = SQLERRM,
            error_code = SQLSTATE,
            note = note || format(' - store_visibility update failed: %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;

  -- 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 steps completed successfully'
    WHERE id = v_log_id;
  END IF;

  RETURN NEW;
EXCEPTION
  WHEN OTHERS THEN
    -- Final error logging
    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;
$$;

-- Create the trigger (uncomment and adjust table name if needed)
-- Run create_trigger.sql separately or uncomment below:
/*
DROP TRIGGER IF EXISTS trg_task_fixed_update_visibility ON task;
CREATE TRIGGER trg_task_fixed_update_visibility
  AFTER UPDATE ON task
  FOR EACH ROW
  WHEN (
    lower(NEW.status) = 'fixed'
    AND lower(COALESCE(OLD.status, '')) IS DISTINCT FROM lower(NEW.status)
  )
  EXECUTE FUNCTION fn_task_fixed_update_visibility();
*/

