
-- Create product_types table
CREATE TABLE public.product_types (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  name_bn TEXT NOT NULL DEFAULT '',
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive')),
  icon TEXT NOT NULL DEFAULT '',
  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()
);

-- Enable RLS
ALTER TABLE public.product_types ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Authenticated users can view active product types"
ON public.product_types FOR SELECT TO authenticated
USING (true);

CREATE POLICY "Authenticated users can insert product types"
ON public.product_types FOR INSERT TO authenticated
WITH CHECK (true);

CREATE POLICY "Authenticated users can update product types"
ON public.product_types FOR UPDATE TO authenticated
USING (true);

-- Trigger for updated_at
CREATE TRIGGER update_product_types_updated_at
BEFORE UPDATE ON public.product_types
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

-- Insert default types
INSERT INTO public.product_types (name, name_bn, icon, status) VALUES
  ('Electric', 'ইলেকট্রিক', '⚡', 'active'),
  ('Hardware', 'হার্ডওয়্যার', '🔩', 'active');

-- Add foreign key from products to product_types
ALTER TABLE public.products ADD COLUMN product_type_id UUID REFERENCES public.product_types(id);
