-- Ticket remarks (multi-attempt thread)
CREATE TABLE public.ticket_remarks (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  ticket_id uuid NOT NULL REFERENCES public.support_tickets(id) ON DELETE CASCADE,
  company_id uuid NOT NULL REFERENCES public.companies(id) ON DELETE CASCADE,
  remark text NOT NULL,
  attempt_no int,
  created_by uuid,
  created_at timestamptz NOT NULL DEFAULT now()
);
ALTER TABLE public.ticket_remarks ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Tenant view remarks" ON public.ticket_remarks FOR SELECT TO authenticated
  USING (company_id = public.get_user_company(auth.uid()) OR public.is_super_admin(auth.uid()));
CREATE POLICY "Tenant insert remarks" ON public.ticket_remarks FOR INSERT TO authenticated
  WITH CHECK (company_id = public.get_user_company(auth.uid()) OR public.is_super_admin(auth.uid()));
CREATE INDEX idx_ticket_remarks_ticket ON public.ticket_remarks(ticket_id);

-- Products (company-level master)
CREATE TABLE public.products (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES public.companies(id) ON DELETE CASCADE,
  name text NOT NULL,
  category_id uuid REFERENCES public.product_categories(id) ON DELETE SET NULL,
  price numeric,
  is_active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
ALTER TABLE public.products ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Tenant view products" ON public.products FOR SELECT TO authenticated
  USING (company_id = public.get_user_company(auth.uid()) OR public.is_super_admin(auth.uid()));
CREATE POLICY "Admins manage products" ON public.products FOR ALL TO authenticated
  USING (public.is_super_admin(auth.uid()) OR (public.has_role(auth.uid(), 'company_admin') AND company_id = public.get_user_company(auth.uid())))
  WITH CHECK (public.is_super_admin(auth.uid()) OR (public.has_role(auth.uid(), 'company_admin') AND company_id = public.get_user_company(auth.uid())));
CREATE TRIGGER products_updated_at BEFORE UPDATE ON public.products FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
CREATE INDEX idx_products_company ON public.products(company_id);

-- Retention calls (outbound retention/upsell queue + log)
CREATE TYPE public.retention_call_status AS ENUM ('pending','contacted','not_reachable','interested','not_interested','converted');
CREATE TABLE public.retention_calls (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES public.companies(id) ON DELETE CASCADE,
  client_id uuid NOT NULL REFERENCES public.clients(id) ON DELETE CASCADE,
  assigned_to uuid,
  status public.retention_call_status NOT NULL DEFAULT 'pending',
  remarks text,
  upsell_interested boolean DEFAULT false,
  pitched_product text,
  follow_up_date date,
  called_at timestamptz,
  created_by uuid,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
ALTER TABLE public.retention_calls ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Tenant view retention" ON public.retention_calls FOR SELECT TO authenticated
  USING (company_id = public.get_user_company(auth.uid()) OR public.is_super_admin(auth.uid()));
CREATE POLICY "Tenant insert retention" ON public.retention_calls FOR INSERT TO authenticated
  WITH CHECK (company_id = public.get_user_company(auth.uid()) OR public.is_super_admin(auth.uid()));
CREATE POLICY "Tenant update retention" ON public.retention_calls FOR UPDATE TO authenticated
  USING (company_id = public.get_user_company(auth.uid()) OR public.is_super_admin(auth.uid()));
CREATE POLICY "Admins delete retention" ON public.retention_calls FOR DELETE TO authenticated
  USING (public.is_super_admin(auth.uid()) OR (public.has_role(auth.uid(), 'company_admin') AND company_id = public.get_user_company(auth.uid())));
CREATE TRIGGER retention_calls_updated_at BEFORE UPDATE ON public.retention_calls FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
CREATE INDEX idx_retention_company ON public.retention_calls(company_id);
CREATE INDEX idx_retention_client ON public.retention_calls(client_id);