CREATE OR REPLACE FUNCTION public.is_finance_member(_user_id uuid)
RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  SELECT EXISTS (SELECT 1 FROM public.user_roles WHERE user_id = _user_id AND role IN ('admin','finance'));
$$;

CREATE TABLE public.finance_transactions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  direction text NOT NULL CHECK (direction IN ('in','out')),
  amount numeric(14,2) NOT NULL CHECK (amount > 0),
  currency text NOT NULL DEFAULT 'UGX',
  category text NOT NULL DEFAULT 'general',
  description text NOT NULL,
  counterparty text,
  method text NOT NULL DEFAULT 'cash',
  reference text,
  occurred_on date NOT NULL DEFAULT current_date,
  created_by uuid,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.finance_transactions TO authenticated;
GRANT ALL ON public.finance_transactions TO service_role;
ALTER TABLE public.finance_transactions ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Finance members read transactions" ON public.finance_transactions FOR SELECT TO authenticated USING (public.is_finance_member(auth.uid()));
CREATE POLICY "Finance members insert transactions" ON public.finance_transactions FOR INSERT TO authenticated WITH CHECK (public.is_finance_member(auth.uid()));
CREATE POLICY "Finance members update transactions" ON public.finance_transactions FOR UPDATE TO authenticated USING (public.is_finance_member(auth.uid())) WITH CHECK (public.is_finance_member(auth.uid()));
CREATE POLICY "Admins delete transactions" ON public.finance_transactions FOR DELETE TO authenticated USING (public.has_role(auth.uid(), 'admin'));
CREATE TRIGGER update_finance_transactions_updated_at BEFORE UPDATE ON public.finance_transactions FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

CREATE SEQUENCE public.finance_receipt_seq START 1001;
GRANT USAGE, SELECT ON SEQUENCE public.finance_receipt_seq TO authenticated;
GRANT ALL ON SEQUENCE public.finance_receipt_seq TO service_role;

CREATE TABLE public.finance_receipts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  receipt_no text NOT NULL UNIQUE DEFAULT ('GAT-' || to_char(now(),'YYYY') || '-' || lpad(nextval('public.finance_receipt_seq')::text, 5, '0')),
  transaction_id uuid REFERENCES public.finance_transactions(id) ON DELETE SET NULL,
  issued_to text NOT NULL,
  issued_to_email text,
  items jsonb NOT NULL DEFAULT '[]'::jsonb,
  amount numeric(14,2) NOT NULL CHECK (amount >= 0),
  currency text NOT NULL DEFAULT 'UGX',
  method text NOT NULL DEFAULT 'cash',
  notes text,
  issued_by uuid,
  issued_on date NOT NULL DEFAULT current_date,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.finance_receipts TO authenticated;
GRANT ALL ON public.finance_receipts TO service_role;
ALTER TABLE public.finance_receipts ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Finance members read receipts" ON public.finance_receipts FOR SELECT TO authenticated USING (public.is_finance_member(auth.uid()));
CREATE POLICY "Finance members insert receipts" ON public.finance_receipts FOR INSERT TO authenticated WITH CHECK (public.is_finance_member(auth.uid()));
CREATE POLICY "Finance members update receipts" ON public.finance_receipts FOR UPDATE TO authenticated USING (public.is_finance_member(auth.uid())) WITH CHECK (public.is_finance_member(auth.uid()));
CREATE POLICY "Admins delete receipts" ON public.finance_receipts FOR DELETE TO authenticated USING (public.has_role(auth.uid(), 'admin'));
CREATE TRIGGER update_finance_receipts_updated_at BEFORE UPDATE ON public.finance_receipts FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

CREATE TABLE public.finance_audit_log (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  actor_id uuid,
  actor_email text,
  action text NOT NULL,
  entity text NOT NULL,
  entity_id text,
  details jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT ON public.finance_audit_log TO authenticated;
GRANT ALL ON public.finance_audit_log TO service_role;
ALTER TABLE public.finance_audit_log ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Finance members read audit" ON public.finance_audit_log FOR SELECT TO authenticated USING (public.is_finance_member(auth.uid()));
CREATE POLICY "Finance members write audit" ON public.finance_audit_log FOR INSERT TO authenticated WITH CHECK (public.is_finance_member(auth.uid()) AND actor_id = auth.uid());

CREATE TABLE public.chatbot_knowledge (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  kind text NOT NULL DEFAULT 'faq' CHECK (kind IN ('faq','snippet')),
  title text NOT NULL,
  content text NOT NULL,
  category text NOT NULL DEFAULT 'general',
  published boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT ON public.chatbot_knowledge TO anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.chatbot_knowledge TO authenticated;
GRANT ALL ON public.chatbot_knowledge TO service_role;
ALTER TABLE public.chatbot_knowledge ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Published knowledge is public" ON public.chatbot_knowledge FOR SELECT TO anon, authenticated USING (published = true);
CREATE POLICY "Admins read all knowledge" ON public.chatbot_knowledge FOR SELECT TO authenticated USING (public.has_role(auth.uid(), 'admin'));
CREATE POLICY "Admins insert knowledge" ON public.chatbot_knowledge FOR INSERT TO authenticated WITH CHECK (public.has_role(auth.uid(), 'admin'));
CREATE POLICY "Admins update knowledge" ON public.chatbot_knowledge FOR UPDATE TO authenticated USING (public.has_role(auth.uid(), 'admin')) WITH CHECK (public.has_role(auth.uid(), 'admin'));
CREATE POLICY "Admins delete knowledge" ON public.chatbot_knowledge FOR DELETE TO authenticated USING (public.has_role(auth.uid(), 'admin'));
CREATE TRIGGER update_chatbot_knowledge_updated_at BEFORE UPDATE ON public.chatbot_knowledge FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

CREATE TABLE public.chatbot_feedback (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  rating text NOT NULL CHECK (rating IN ('up','down')),
  question text,
  answer text,
  message_ref text,
  created_at timestamptz NOT NULL DEFAULT now()
);
GRANT INSERT ON public.chatbot_feedback TO anon;
GRANT SELECT, INSERT ON public.chatbot_feedback TO authenticated;
GRANT ALL ON public.chatbot_feedback TO service_role;
ALTER TABLE public.chatbot_feedback ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Anyone can rate an answer" ON public.chatbot_feedback FOR INSERT TO anon, authenticated WITH CHECK (rating IN ('up','down'));
CREATE POLICY "Admins read feedback" ON public.chatbot_feedback FOR SELECT TO authenticated USING (public.has_role(auth.uid(), 'admin'));