-- Conversations for the AI coach
CREATE TABLE public.ai_conversations (
  id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id uuid NOT NULL,
  title text NOT NULL DEFAULT 'New conversation',
  pinned boolean NOT NULL DEFAULT false,
  archived boolean NOT NULL DEFAULT false,
  last_message_at timestamp with time zone NOT NULL DEFAULT now(),
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now()
);

GRANT SELECT, INSERT, UPDATE, DELETE ON public.ai_conversations TO authenticated;
GRANT ALL ON public.ai_conversations TO service_role;

ALTER TABLE public.ai_conversations ENABLE ROW LEVEL SECURITY;

CREATE POLICY "own conversations" ON public.ai_conversations
  FOR ALL USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);

CREATE TRIGGER update_ai_conversations_updated_at
  BEFORE UPDATE ON public.ai_conversations
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

CREATE INDEX ai_conversations_user_idx ON public.ai_conversations (user_id, last_message_at DESC);

-- Link messages to conversations
ALTER TABLE public.ai_chats
  ADD COLUMN conversation_id uuid REFERENCES public.ai_conversations(id) ON DELETE CASCADE,
  ADD COLUMN pinned boolean NOT NULL DEFAULT false;

CREATE INDEX ai_chats_conversation_idx ON public.ai_chats (conversation_id, created_at);

-- Migrate existing loose messages into one conversation per user
DO $$
DECLARE r record; conv uuid;
BEGIN
  FOR r IN SELECT DISTINCT user_id FROM public.ai_chats WHERE conversation_id IS NULL LOOP
    INSERT INTO public.ai_conversations (user_id, title, last_message_at)
    VALUES (r.user_id, 'Previous conversation', now())
    RETURNING id INTO conv;
    UPDATE public.ai_chats SET conversation_id = conv
      WHERE user_id = r.user_id AND conversation_id IS NULL;
  END LOOP;
END $$;

-- AI intelligence artifacts: proactive recommendations, financial DNA, stories
CREATE TABLE public.ai_insights (
  id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id uuid NOT NULL,
  kind text NOT NULL,
  title text NOT NULL,
  body text,
  severity text NOT NULL DEFAULT 'info',
  data jsonb NOT NULL DEFAULT '{}'::jsonb,
  dismissed boolean NOT NULL DEFAULT false,
  period_start date,
  period_end date,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now()
);

GRANT SELECT, INSERT, UPDATE, DELETE ON public.ai_insights TO authenticated;
GRANT ALL ON public.ai_insights TO service_role;

ALTER TABLE public.ai_insights ENABLE ROW LEVEL SECURITY;

CREATE POLICY "own insights" ON public.ai_insights
  FOR ALL USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);

CREATE TRIGGER update_ai_insights_updated_at
  BEFORE UPDATE ON public.ai_insights
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

CREATE INDEX ai_insights_user_kind_idx ON public.ai_insights (user_id, kind, created_at DESC);