-- ============================================================
-- PRODUCTION HARDENING SPRINT — schema additions
-- ============================================================

-- 1) AI REQUEST LOG (rate limiting + monitoring)
CREATE TABLE public.ai_request_log (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES auth.users(id) ON DELETE SET NULL,
  kind text NOT NULL,              -- 'photoshoot_plan' | 'moodboard_image' | future kinds
  status text NOT NULL,            -- 'ok' | 'error' | 'rate_limited' | 'validation_failed'
  latency_ms integer,
  prompt_tokens integer,
  completion_tokens integer,
  total_tokens integer,
  model text,
  error_code text,
  error_message text,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ai_request_log_user_kind_time_idx ON public.ai_request_log (user_id, kind, created_at DESC);
CREATE INDEX ai_request_log_time_idx ON public.ai_request_log (created_at DESC);
GRANT SELECT ON public.ai_request_log TO authenticated;
GRANT ALL ON public.ai_request_log TO service_role;
ALTER TABLE public.ai_request_log ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users view own ai log" ON public.ai_request_log
  FOR SELECT TO authenticated USING (auth.uid() = user_id);
CREATE POLICY "Admins view all ai log" ON public.ai_request_log
  FOR SELECT TO authenticated USING (public.has_role(auth.uid(), 'admin'));
-- All writes go through service_role from server code; deny everything else
CREATE POLICY "Deny client inserts on ai_request_log" ON public.ai_request_log
  AS RESTRICTIVE FOR INSERT TO anon, authenticated WITH CHECK (false);
CREATE POLICY "Deny client updates on ai_request_log" ON public.ai_request_log
  AS RESTRICTIVE FOR UPDATE TO anon, authenticated USING (false) WITH CHECK (false);
CREATE POLICY "Deny client deletes on ai_request_log" ON public.ai_request_log
  AS RESTRICTIVE FOR DELETE TO anon, authenticated USING (false);

-- 2) ANALYTICS EVENTS
CREATE TABLE public.analytics_events (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES auth.users(id) ON DELETE SET NULL,
  event text NOT NULL,             -- 'signup' | 'login' | 'photoshoot_plan_generated' | ...
  props jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX analytics_events_event_time_idx ON public.analytics_events (event, created_at DESC);
CREATE INDEX analytics_events_user_time_idx ON public.analytics_events (user_id, created_at DESC);
GRANT SELECT ON public.analytics_events TO authenticated;
GRANT ALL ON public.analytics_events TO service_role;
ALTER TABLE public.analytics_events ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Admins view all analytics" ON public.analytics_events
  FOR SELECT TO authenticated USING (public.has_role(auth.uid(), 'admin'));
-- writes via service_role from server functions only
CREATE POLICY "Deny client inserts on analytics_events" ON public.analytics_events
  AS RESTRICTIVE FOR INSERT TO anon, authenticated WITH CHECK (false);
CREATE POLICY "Deny client updates on analytics_events" ON public.analytics_events
  AS RESTRICTIVE FOR UPDATE TO anon, authenticated USING (false) WITH CHECK (false);
CREATE POLICY "Deny client deletes on analytics_events" ON public.analytics_events
  AS RESTRICTIVE FOR DELETE TO anon, authenticated USING (false);

-- 3) SYSTEM PROMPT VERSIONS (the hardcoded AI template inside generatePhotoshootPlan)
CREATE TABLE public.system_prompt_versions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  version integer NOT NULL UNIQUE,
  template_name text NOT NULL,
  system_text text NOT NULL,
  user_template text NOT NULL,
  notes text,
  active boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  created_by uuid REFERENCES auth.users(id) ON DELETE SET NULL
);
CREATE UNIQUE INDEX system_prompt_versions_one_active
  ON public.system_prompt_versions (active) WHERE active = true;
GRANT SELECT ON public.system_prompt_versions TO authenticated;
GRANT ALL ON public.system_prompt_versions TO service_role;
ALTER TABLE public.system_prompt_versions ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Admins manage system prompts" ON public.system_prompt_versions
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin'))
  WITH CHECK (public.has_role(auth.uid(), 'admin'));
CREATE POLICY "Authenticated read active system prompt" ON public.system_prompt_versions
  FOR SELECT TO authenticated USING (active = true);

-- Seed v1 with the current production template
INSERT INTO public.system_prompt_versions (version, template_name, system_text, user_template, active, notes)
VALUES (
  1,
  'photoshoot_plan_v1',
  'You are the Personal Photoshoot Studio™ creative director. You design emotionally intentional photoshoots that tell stories.
Always respond with ONLY valid JSON (no markdown, no prose) matching the schema requested. Be vivid, specific, and luxury-editorial in tone.',
  'Initial production template captured at launch.',
  true,
  'Bootstrapped from generatePhotoshootPlan source at launch.'
);

-- 4) LIBRARY PROMPT VERSIONS (snapshot of public.prompts on every change)
CREATE TABLE public.library_prompt_versions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  prompt_id uuid NOT NULL,         -- not a hard FK so deletes don't cascade away history
  version integer NOT NULL,
  title text NOT NULL,
  prompt_text text NOT NULL,
  category text,
  emotion_tags text[] NOT NULL DEFAULT '{}',
  personality_tags text[] NOT NULL DEFAULT '{}',
  style_tags text[] NOT NULL DEFAULT '{}',
  ai_tool text NOT NULL,
  is_active boolean NOT NULL,
  snapshot_at timestamptz NOT NULL DEFAULT now(),
  snapshot_by uuid REFERENCES auth.users(id) ON DELETE SET NULL,
  UNIQUE (prompt_id, version)
);
CREATE INDEX library_prompt_versions_prompt_idx ON public.library_prompt_versions (prompt_id, version DESC);
GRANT SELECT ON public.library_prompt_versions TO authenticated;
GRANT ALL ON public.library_prompt_versions TO service_role;
ALTER TABLE public.library_prompt_versions ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Admins read library versions" ON public.library_prompt_versions
  FOR SELECT TO authenticated USING (public.has_role(auth.uid(), 'admin'));
-- writes only via trigger (SECURITY DEFINER) — block clients
CREATE POLICY "Deny client inserts on library_prompt_versions" ON public.library_prompt_versions
  AS RESTRICTIVE FOR INSERT TO anon, authenticated WITH CHECK (false);
CREATE POLICY "Deny client updates on library_prompt_versions" ON public.library_prompt_versions
  AS RESTRICTIVE FOR UPDATE TO anon, authenticated USING (false) WITH CHECK (false);
CREATE POLICY "Deny client deletes on library_prompt_versions" ON public.library_prompt_versions
  AS RESTRICTIVE FOR DELETE TO anon, authenticated USING (false);

-- Snapshot trigger: insert a version row on every prompts INSERT or UPDATE
CREATE OR REPLACE FUNCTION public.snapshot_prompt_version()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE next_version int;
BEGIN
  SELECT COALESCE(MAX(version), 0) + 1 INTO next_version
  FROM public.library_prompt_versions WHERE prompt_id = NEW.id;
  INSERT INTO public.library_prompt_versions
    (prompt_id, version, title, prompt_text, category, emotion_tags, personality_tags, style_tags, ai_tool, is_active, snapshot_by)
  VALUES
    (NEW.id, next_version, NEW.title, NEW.prompt_text, NEW.category, NEW.emotion_tags, NEW.personality_tags, NEW.style_tags, NEW.ai_tool, NEW.is_active, auth.uid());
  RETURN NEW;
END;
$$;
CREATE TRIGGER trg_prompts_snapshot_insert
  AFTER INSERT ON public.prompts
  FOR EACH ROW EXECUTE FUNCTION public.snapshot_prompt_version();
CREATE TRIGGER trg_prompts_snapshot_update
  AFTER UPDATE ON public.prompts
  FOR EACH ROW
  WHEN (
    OLD.title IS DISTINCT FROM NEW.title OR
    OLD.prompt_text IS DISTINCT FROM NEW.prompt_text OR
    OLD.category IS DISTINCT FROM NEW.category OR
    OLD.emotion_tags IS DISTINCT FROM NEW.emotion_tags OR
    OLD.personality_tags IS DISTINCT FROM NEW.personality_tags OR
    OLD.style_tags IS DISTINCT FROM NEW.style_tags OR
    OLD.ai_tool IS DISTINCT FROM NEW.ai_tool OR
    OLD.is_active IS DISTINCT FROM NEW.is_active
  )
  EXECUTE FUNCTION public.snapshot_prompt_version();

-- Backfill v1 for every existing prompt
INSERT INTO public.library_prompt_versions
  (prompt_id, version, title, prompt_text, category, emotion_tags, personality_tags, style_tags, ai_tool, is_active, snapshot_at, snapshot_by)
SELECT id, 1, title, prompt_text, category, emotion_tags, personality_tags, style_tags, ai_tool, is_active, created_at, created_by
FROM public.prompts;

-- 5) PHOTOSHOOTS: stamp generation with the prompt versions used
ALTER TABLE public.photoshoots
  ADD COLUMN system_prompt_version_id uuid REFERENCES public.system_prompt_versions(id),
  ADD COLUMN library_prompt_version_ids uuid[] NOT NULL DEFAULT '{}';

-- 6) Helper RPC: per-user request count in last N seconds (used by rate limiter)
CREATE OR REPLACE FUNCTION public.ai_recent_request_count(_kind text, _window_seconds int)
RETURNS integer
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT COUNT(*)::int
  FROM public.ai_request_log
  WHERE user_id = auth.uid()
    AND kind = _kind
    AND status IN ('ok','error','validation_failed')  -- count attempts, not pre-flight rejects
    AND created_at >= now() - make_interval(secs => _window_seconds);
$$;
REVOKE ALL ON FUNCTION public.ai_recent_request_count(text, int) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.ai_recent_request_count(text, int) TO authenticated, service_role;

-- 7) Helper RPC: current user's plan (subscriptions.plan or 'free')
CREATE OR REPLACE FUNCTION public.current_user_plan()
RETURNS text
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT COALESCE(
    (SELECT plan FROM public.subscriptions
      WHERE user_id = auth.uid() AND status = 'active'
      ORDER BY current_period_end DESC NULLS LAST LIMIT 1),
    'free'
  );
$$;
REVOKE ALL ON FUNCTION public.current_user_plan() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.current_user_plan() TO authenticated, service_role;