-- AI assistant toggle
ALTER TABLE public.business_settings ADD COLUMN IF NOT EXISTS ai_enabled boolean NOT NULL DEFAULT true;

-- Conversations
CREATE TABLE public.ai_conversations (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
  title text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz 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 "Users manage own conversations" ON public.ai_conversations FOR ALL TO authenticated
  USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Admins view all conversations" ON public.ai_conversations FOR SELECT TO authenticated
  USING (public.has_role(auth.uid(), 'admin'));

-- Messages
CREATE TABLE public.ai_messages (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  conversation_id uuid NOT NULL REFERENCES public.ai_conversations(id) ON DELETE CASCADE,
  role text NOT NULL CHECK (role IN ('user','assistant','system')),
  content text NOT NULL,
  rating smallint CHECK (rating IN (-1, 0, 1)),
  flagged_unanswered boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ai_messages_conv_idx ON public.ai_messages(conversation_id, created_at);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.ai_messages TO authenticated;
GRANT ALL ON public.ai_messages TO service_role;
ALTER TABLE public.ai_messages ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users manage own messages" ON public.ai_messages FOR ALL TO authenticated
  USING (EXISTS (SELECT 1 FROM public.ai_conversations c WHERE c.id = conversation_id AND c.user_id = auth.uid()))
  WITH CHECK (EXISTS (SELECT 1 FROM public.ai_conversations c WHERE c.id = conversation_id AND c.user_id = auth.uid()));
CREATE POLICY "Admins view all messages" ON public.ai_messages FOR SELECT TO authenticated
  USING (public.has_role(auth.uid(), 'admin'));

-- Custom FAQs
CREATE TABLE public.ai_faqs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  question text NOT NULL,
  answer text NOT NULL,
  active boolean NOT NULL DEFAULT true,
  sort_order int NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT ON public.ai_faqs TO anon, authenticated;
GRANT ALL ON public.ai_faqs TO service_role;
ALTER TABLE public.ai_faqs ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Public read active faqs" ON public.ai_faqs FOR SELECT TO anon, authenticated USING (active = true);
CREATE POLICY "Admins manage faqs" ON public.ai_faqs FOR ALL TO authenticated
  USING (public.has_role(auth.uid(), 'admin')) WITH CHECK (public.has_role(auth.uid(), 'admin'));

CREATE TRIGGER ai_conversations_updated BEFORE UPDATE ON public.ai_conversations
  FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();
CREATE TRIGGER ai_faqs_updated BEFORE UPDATE ON public.ai_faqs
  FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

-- Seed a few starter FAQs
INSERT INTO public.ai_faqs (question, answer, sort_order) VALUES
  ('What are your business hours?', 'We are open every day (Mon–Sun) from 6:00 AM to 10:00 PM.', 1),
  ('Where are you located?', 'We are based in Homabay, Kenya. Visit our contact page for directions.', 2),
  ('How do I pay for a service?', 'Top up your account via M-PESA Buy Goods Till 3521362 (RISTATECHENTERPRISES), submit the reference for admin approval, then pay from your wallet balance.', 3),
  ('How do I request a service?', 'Log in, open your dashboard, click "Request service", pick the service, fill in the form and attach any documents. We''ll contact you on WhatsApp to confirm.', 4);