
CREATE TABLE public.transaction_categories (
  id uuid NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  name text NOT NULL,
  name_bn text NOT NULL DEFAULT '',
  type text NOT NULL CHECK (type IN ('income','expense')),
  description text NOT NULL DEFAULT '',
  icon text NOT NULL DEFAULT 'Tag',
  color text NOT NULL DEFAULT '#6366f1',
  status text NOT NULL DEFAULT 'active' CHECK (status IN ('active','inactive')),
  is_default boolean NOT NULL DEFAULT false,
  created_by uuid,
  deleted_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (type, name)
);

CREATE INDEX idx_txn_cat_type ON public.transaction_categories(type) WHERE deleted_at IS NULL;

ALTER TABLE public.transaction_categories ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Staff can view transaction categories"
  ON public.transaction_categories FOR SELECT TO authenticated
  USING (is_staff(auth.uid()));

CREATE POLICY "Admins can insert transaction categories"
  ON public.transaction_categories FOR INSERT TO authenticated
  WITH CHECK (has_role(auth.uid(), 'admin'::app_role));

CREATE POLICY "Admins can update transaction categories"
  ON public.transaction_categories FOR UPDATE TO authenticated
  USING (has_role(auth.uid(), 'admin'::app_role));

CREATE POLICY "Admins can delete transaction categories"
  ON public.transaction_categories FOR DELETE TO authenticated
  USING (has_role(auth.uid(), 'admin'::app_role));

-- Prevent deletion/modification of default categories
CREATE OR REPLACE FUNCTION public.protect_default_txn_categories()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  IF TG_OP = 'DELETE' AND OLD.is_default = true THEN
    RAISE EXCEPTION 'Default category cannot be deleted';
  END IF;
  IF TG_OP = 'UPDATE' AND OLD.is_default = true AND NEW.type <> OLD.type THEN
    RAISE EXCEPTION 'Default category type cannot be changed';
  END IF;
  RETURN COALESCE(NEW, OLD);
END;
$$;

CREATE TRIGGER trg_protect_default_txn_categories
BEFORE UPDATE OR DELETE ON public.transaction_categories
FOR EACH ROW EXECUTE FUNCTION public.protect_default_txn_categories();

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

-- Add category_id columns to incomes & expenses
ALTER TABLE public.incomes ADD COLUMN IF NOT EXISTS category_id uuid REFERENCES public.transaction_categories(id);
ALTER TABLE public.expenses ADD COLUMN IF NOT EXISTS category_id uuid REFERENCES public.transaction_categories(id);

CREATE INDEX IF NOT EXISTS idx_incomes_category_id ON public.incomes(category_id);
CREATE INDEX IF NOT EXISTS idx_expenses_category_id ON public.expenses(category_id);

-- Seed default categories
INSERT INTO public.transaction_categories (name, name_bn, type, icon, color, is_default) VALUES
  ('Product Sale','পণ্য বিক্রয়','income','ShoppingCart','#10b981',true),
  ('Service Income','সেবা আয়','income','Briefcase','#0ea5e9',true),
  ('Commission','কমিশন','income','Percent','#f59e0b',true),
  ('Investment','বিনিয়োগ','income','TrendingUp','#8b5cf6',true),
  ('Other Income','অন্যান্য আয়','income','PlusCircle','#14b8a6',true),
  ('Salary','বেতন','expense','Users','#ef4444',true),
  ('Electricity Bill','বিদ্যুৎ বিল','expense','Zap','#f97316',true),
  ('Transport','পরিবহন','expense','Truck','#06b6d4',true),
  ('Office Expense','অফিস খরচ','expense','Building2','#a855f7',true),
  ('Marketing','মার্কেটিং','expense','Megaphone','#ec4899',true),
  ('Maintenance','রক্ষণাবেক্ষণ','expense','Wrench','#64748b',true),
  ('Other Expense','অন্যান্য খরচ','expense','MinusCircle','#dc2626',true)
ON CONFLICT (type, name) DO NOTHING;
