ALTER TABLE public.chatbot_configs
  ADD COLUMN IF NOT EXISTS widget_position text NOT NULL DEFAULT 'bottom-right',
  ADD COLUMN IF NOT EXISTS widget_size text NOT NULL DEFAULT 'medium',
  ADD COLUMN IF NOT EXISTS widget_title text NOT NULL DEFAULT 'Chat with us',
  ADD COLUMN IF NOT EXISTS logo_url text,
  ADD COLUMN IF NOT EXISTS launcher_label text NOT NULL DEFAULT '💬',
  ADD COLUMN IF NOT EXISTS contact_name text,
  ADD COLUMN IF NOT EXISTS contact_email text;

CREATE TABLE IF NOT EXISTS public.chatbot_events (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  chatbot_id uuid NOT NULL REFERENCES public.chatbot_configs(id) ON DELETE CASCADE,
  session_id text NOT NULL,
  role text NOT NULL,
  chars integer NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now()
);

GRANT SELECT ON public.chatbot_events TO authenticated;
GRANT ALL ON public.chatbot_events TO service_role;
ALTER TABLE public.chatbot_events ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Owners view their chatbot events" ON public.chatbot_events;
CREATE POLICY "Owners view their chatbot events"
ON public.chatbot_events FOR SELECT TO authenticated
USING (EXISTS (SELECT 1 FROM public.chatbot_configs c WHERE c.id = chatbot_events.chatbot_id AND c.user_id = auth.uid()));

CREATE INDEX IF NOT EXISTS chatbot_events_bot_time_idx ON public.chatbot_events (chatbot_id, created_at DESC);
CREATE INDEX IF NOT EXISTS chatbot_events_session_idx ON public.chatbot_events (chatbot_id, session_id);

CREATE TABLE IF NOT EXISTS public.chatbot_rate_limits (
  bucket_key text PRIMARY KEY,
  window_start timestamptz NOT NULL DEFAULT now(),
  hits integer NOT NULL DEFAULT 0
);

GRANT ALL ON public.chatbot_rate_limits TO service_role;
ALTER TABLE public.chatbot_rate_limits ENABLE ROW LEVEL SECURITY;

CREATE OR REPLACE FUNCTION public.bump_rate_limit(_key text, _limit integer, _window_seconds integer)
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  v_hits integer;
BEGIN
  INSERT INTO public.chatbot_rate_limits AS r (bucket_key, window_start, hits)
  VALUES (_key, now(), 1)
  ON CONFLICT (bucket_key) DO UPDATE
    SET hits = CASE WHEN r.window_start < now() - make_interval(secs => _window_seconds) THEN 1 ELSE r.hits + 1 END,
        window_start = CASE WHEN r.window_start < now() - make_interval(secs => _window_seconds) THEN now() ELSE r.window_start END
  RETURNING hits INTO v_hits;
  RETURN v_hits <= _limit;
END;
$$;

REVOKE ALL ON FUNCTION public.bump_rate_limit(text, integer, integer) FROM PUBLIC, anon, authenticated;
GRANT EXECUTE ON FUNCTION public.bump_rate_limit(text, integer, integer) TO service_role;

CREATE OR REPLACE FUNCTION public.chatbot_analytics(_chatbot_id uuid)
RETURNS jsonb
LANGUAGE plpgsql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  v_owner uuid;
  v_result jsonb;
BEGIN
  SELECT user_id INTO v_owner FROM public.chatbot_configs WHERE id = _chatbot_id;
  IF v_owner IS NULL OR (v_owner IS DISTINCT FROM auth.uid() AND NOT public.has_role(auth.uid(), 'admin')) THEN
    RAISE EXCEPTION 'not authorized';
  END IF;

  SELECT jsonb_build_object(
    'conversations', (SELECT count(DISTINCT session_id) FROM public.chatbot_events WHERE chatbot_id = _chatbot_id),
    'messages', (SELECT count(*) FROM public.chatbot_events WHERE chatbot_id = _chatbot_id),
    'user_messages', (SELECT count(*) FROM public.chatbot_events WHERE chatbot_id = _chatbot_id AND role = 'user'),
    'last_activity', (SELECT max(created_at) FROM public.chatbot_events WHERE chatbot_id = _chatbot_id),
    'daily', COALESCE((
      SELECT jsonb_agg(d ORDER BY d->>'day')
      FROM (
        SELECT jsonb_build_object('day', to_char(date_trunc('day', created_at), 'YYYY-MM-DD'), 'messages', count(*)) AS d
        FROM public.chatbot_events
        WHERE chatbot_id = _chatbot_id AND created_at > now() - interval '30 days'
        GROUP BY 1
      ) s
    ), '[]'::jsonb)
  ) INTO v_result;

  RETURN v_result;
END;
$$;

REVOKE ALL ON FUNCTION public.chatbot_analytics(uuid) FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION public.chatbot_analytics(uuid) TO authenticated, service_role;