-- Lead status enum
CREATE TYPE public.lead_status AS ENUM ('new','contacted','interested','not_interested','converted','lost');

-- Leads table
CREATE TABLE public.leads (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES public.companies(id) ON DELETE CASCADE,
  assigned_to uuid,
  created_by uuid,
  name text,
  mobile text NOT NULL,
  alt_mobile text,
  email text,
  city text,
  state text,
  address text,
  gstin text,
  product_interest text,
  ref_by text,
  account_name text,
  notes text,
  status public.lead_status NOT NULL DEFAULT 'new',
  next_followup_date date,
  converted_client_id uuid,
  converted_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX leads_company_mobile_unique ON public.leads(company_id, mobile);
CREATE INDEX leads_assigned_to_idx ON public.leads(assigned_to);
CREATE INDEX leads_status_idx ON public.leads(status);
CREATE INDEX leads_next_followup_idx ON public.leads(next_followup_date);

ALTER TABLE public.leads ENABLE ROW LEVEL SECURITY;

CREATE POLICY "View own leads or admin"
ON public.leads FOR SELECT TO authenticated
USING (
  is_super_admin(auth.uid())
  OR (company_id = get_user_company(auth.uid()) AND has_role(auth.uid(), 'company_admin'::app_role))
  OR (company_id = get_user_company(auth.uid()) AND assigned_to = auth.uid())
);

CREATE POLICY "Insert leads in own company"
ON public.leads FOR INSERT TO authenticated
WITH CHECK (company_id = get_user_company(auth.uid()) OR is_super_admin(auth.uid()));

CREATE POLICY "Update own leads or admin"
ON public.leads FOR UPDATE TO authenticated
USING (
  is_super_admin(auth.uid())
  OR (company_id = get_user_company(auth.uid()) AND has_role(auth.uid(), 'company_admin'::app_role))
  OR (company_id = get_user_company(auth.uid()) AND assigned_to = auth.uid())
);

CREATE POLICY "Admins delete leads"
ON public.leads FOR DELETE TO authenticated
USING (is_super_admin(auth.uid()) OR (company_id = get_user_company(auth.uid()) AND has_role(auth.uid(), 'company_admin'::app_role)));

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

-- Lead calls log
CREATE TABLE public.lead_calls (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  lead_id uuid NOT NULL REFERENCES public.leads(id) ON DELETE CASCADE,
  company_id uuid NOT NULL REFERENCES public.companies(id) ON DELETE CASCADE,
  called_by uuid,
  call_status text NOT NULL,
  pitched_product text,
  remark text,
  next_followup_date date,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX lead_calls_lead_idx ON public.lead_calls(lead_id);

ALTER TABLE public.lead_calls ENABLE ROW LEVEL SECURITY;

CREATE POLICY "View lead calls (assigned or admin)"
ON public.lead_calls FOR SELECT TO authenticated
USING (
  is_super_admin(auth.uid())
  OR (company_id = get_user_company(auth.uid()) AND has_role(auth.uid(), 'company_admin'::app_role))
  OR EXISTS (SELECT 1 FROM public.leads l WHERE l.id = lead_id AND l.assigned_to = auth.uid())
);

CREATE POLICY "Insert lead calls"
ON public.lead_calls FOR INSERT TO authenticated
WITH CHECK (company_id = get_user_company(auth.uid()) OR is_super_admin(auth.uid()));