
-- Create customers table
CREATE TABLE public.customers (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  name_bn TEXT NOT NULL DEFAULT '',
  mobile TEXT NOT NULL,
  email TEXT DEFAULT '',
  address TEXT DEFAULT '',
  customer_type TEXT NOT NULL DEFAULT 'retail',
  opening_balance NUMERIC NOT NULL DEFAULT 0,
  current_balance NUMERIC NOT NULL DEFAULT 0,
  total_due NUMERIC NOT NULL DEFAULT 0,
  referral_code TEXT DEFAULT '',
  status TEXT NOT NULL DEFAULT 'active',
  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(),
  CONSTRAINT customers_mobile_unique UNIQUE (mobile)
);

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

CREATE POLICY "Authenticated users can view customers"
  ON public.customers FOR SELECT TO authenticated USING (true);

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

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

-- Timestamp trigger
CREATE TRIGGER update_customers_updated_at
  BEFORE UPDATE ON public.customers
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

-- Create customer_transactions table
CREATE TABLE public.customer_transactions (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  customer_id UUID NOT NULL REFERENCES public.customers(id) ON DELETE CASCADE,
  type TEXT NOT NULL DEFAULT 'sale',
  amount NUMERIC NOT NULL DEFAULT 0,
  reference_id TEXT DEFAULT '',
  note TEXT DEFAULT '',
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

ALTER TABLE public.customer_transactions ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Authenticated users can view customer transactions"
  ON public.customer_transactions FOR SELECT TO authenticated USING (true);

CREATE POLICY "Authenticated users can insert customer transactions"
  ON public.customer_transactions FOR INSERT TO authenticated WITH CHECK (true);

-- Insert some default customers
INSERT INTO public.customers (name, name_bn, mobile, customer_type, status) VALUES
  ('Walk-in Customer', 'ওয়াক-ইন কাস্টমার', '00000000000', 'retail', 'active'),
  ('Rahim Hardware', 'রহিম হার্ডওয়্যার', '01712345678', 'wholesale', 'active'),
  ('Karim Electric', 'করিম ইলেকট্রিক', '01812345678', 'retail', 'active');
