
-- 1. Event System Functions
CREATE OR REPLACE FUNCTION public.fn_capture_event(
  p_event_type TEXT,
  p_actor_user_id UUID,
  p_entity_type TEXT,
  p_entity_id UUID,
  p_idempotency_key TEXT,
  p_payload JSONB DEFAULT '{}'::jsonb,
  p_source TEXT DEFAULT 'app'
)
RETURNS UUID AS $$
DECLARE
  v_log_id UUID;
BEGIN
  INSERT INTO public.event_log (
    event_type,
    actor_user_id,
    entity_type,
    entity_id,
    idempotency_key,
    payload,
    source,
    created_at
  )
  VALUES (
    p_event_type,
    p_actor_user_id,
    p_entity_type,
    p_entity_id,
    p_idempotency_key,
    p_payload,
    p_source,
    NOW()
  )
  ON CONFLICT (idempotency_key) DO UPDATE 
    SET payload = EXCLUDED.payload
  RETURNING id INTO v_log_id;

  INSERT INTO public.event_outbox (
    event_log_id,
    status,
    created_at
  )
  VALUES (
    v_log_id,
    'pending',
    NOW()
  )
  ON CONFLICT (event_log_id) DO NOTHING;

  RETURN v_log_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 2. Triggers for events
CREATE OR REPLACE FUNCTION public.tr_on_user_created()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM public.fn_capture_event(
    'user.created',
    NEW.id::uuid,
    'user',
    NEW.id::uuid,
    'user.created:' || NEW.id || ':' || EXTRACT(EPOCH FROM NEW.created_at),
    jsonb_build_object(
      'email', NEW.email,
      'name', NEW.name
    )
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

DROP TRIGGER IF EXISTS tr_user_created ON public.users;
CREATE TRIGGER tr_user_created
  AFTER INSERT ON public.users
  FOR EACH ROW EXECUTE FUNCTION public.tr_on_user_created();

CREATE OR REPLACE FUNCTION public.tr_on_search_changes()
RETURNS TRIGGER AS $$
DECLARE
  v_event_type TEXT;
  v_idempotency_suffix TEXT;
BEGIN
  IF (TG_OP = 'INSERT') THEN
    v_event_type := 'search.created';
    v_idempotency_suffix := EXTRACT(EPOCH FROM NEW.created_at);
  ELSIF (TG_OP = 'UPDATE') THEN
    IF (OLD.status IS DISTINCT FROM NEW.status) THEN
      IF (NEW.status = 'ACTIVE') THEN v_event_type := 'search.activated';
      ELSIF (NEW.status = 'PAUSED') THEN v_event_type := 'search.paused';
      ELSIF (NEW.status = 'COMPLETED') THEN v_event_type := 'search.form_completed';
      ELSE v_event_type := 'search.updated';
      END IF;
    ELSIF (OLD.completeness IS DISTINCT FROM NEW.completeness AND NEW.completeness >= 100 AND OLD.completeness < 100) THEN
      v_event_type := 'search.form_completed';
    ELSE
      v_event_type := 'search.updated';
    END IF;
    v_idempotency_suffix := EXTRACT(EPOCH FROM NEW.updated_at);
  END IF;

  PERFORM public.fn_capture_event(
    v_event_type,
    NEW.user_id::uuid,
    'search',
    NEW.id::uuid,
    v_event_type || ':' || NEW.id || ':' || v_idempotency_suffix,
    jsonb_build_object(
      'name', NEW.name,
      'status', NEW.status,
      'completeness', NEW.completeness,
      'property_type', NEW.property_type
    )
  );
  
  RETURN NEW;   
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

DROP TRIGGER IF EXISTS tr_search_changes ON public.searches;
CREATE TRIGGER tr_search_changes
  AFTER INSERT OR UPDATE ON public.searches
  FOR EACH ROW
  WHEN (pg_trigger_depth() < 1)
  EXECUTE FUNCTION public.tr_on_search_changes();
