
CREATE TABLE public.units (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  name_bn TEXT NOT NULL DEFAULT '',
  short_code TEXT DEFAULT '',
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive')),
  deleted_at TIMESTAMP WITH TIME ZONE DEFAULT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

ALTER TABLE public.units ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Authenticated users can view units" ON public.units FOR SELECT TO authenticated USING (true);
CREATE POLICY "Authenticated users can insert units" ON public.units FOR INSERT TO authenticated WITH CHECK (true);
CREATE POLICY "Authenticated users can update units" ON public.units FOR UPDATE TO authenticated USING (true);

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

INSERT INTO public.units (name, name_bn, short_code) VALUES
  ('pcs', 'পিস', 'pc'),
  ('box', 'বক্স', 'bx'),
  ('meter', 'মিটার', 'm'),
  ('kg', 'কেজি', 'kg'),
  ('liter', 'লিটার', 'ltr'),
  ('set', 'সেট', 'set'),
  ('roll', 'রোল', 'rl'),
  ('pair', 'জোড়া', 'pr'),
  ('dozen', 'ডজন', 'dz'),
  ('feet', 'ফিট', 'ft');
