
-- =========================================
-- Quotations + Quotation Items
-- =========================================

CREATE TABLE IF NOT EXISTS public.quotations (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  quote_no text UNIQUE,
  customer_id uuid NOT NULL,
  for_self boolean NOT NULL DEFAULT true,
  referral_name text DEFAULT '',
  referral_mobile text DEFAULT '',
  referral_customer_id uuid,
  total_amount numeric NOT NULL DEFAULT 0,
  status text NOT NULL DEFAULT 'draft', -- draft|submitted|approved|rejected
  notes text DEFAULT '',
  reviewed_by uuid,
  reviewed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_quotations_customer ON public.quotations(customer_id);
CREATE INDEX IF NOT EXISTS idx_quotations_status ON public.quotations(status);
CREATE INDEX IF NOT EXISTS idx_quotations_ref_mobile ON public.quotations(referral_mobile);
CREATE INDEX IF NOT EXISTS idx_quotations_ref_cust ON public.quotations(referral_customer_id);

CREATE TABLE IF NOT EXISTS public.quotation_items (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  quotation_id uuid NOT NULL REFERENCES public.quotations(id) ON DELETE CASCADE,
  product_id uuid NOT NULL,
  product_name text NOT NULL DEFAULT '',
  product_name_bn text NOT NULL DEFAULT '',
  unit text NOT NULL DEFAULT '',
  image_url text DEFAULT '',
  quantity numeric NOT NULL DEFAULT 0,
  unit_price numeric NOT NULL DEFAULT 0,
  line_total numeric NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_quotation_items_qid ON public.quotation_items(quotation_id);

-- =========================================
-- Auto-numbering
-- =========================================
CREATE OR REPLACE FUNCTION public.generate_quotation_no()
RETURNS trigger
LANGUAGE plpgsql
SET search_path = public
AS $$
DECLARE n integer;
BEGIN
  IF NEW.quote_no IS NULL OR NEW.quote_no = '' THEN
    SELECT COALESCE(MAX(CAST(NULLIF(regexp_replace(quote_no, '\D', '', 'g'), '') AS integer)), 0) + 1
      INTO n FROM public.quotations;
    NEW.quote_no := 'QT-' || LPAD(n::text, 6, '0');
  END IF;
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_generate_quotation_no ON public.quotations;
CREATE TRIGGER trg_generate_quotation_no
BEFORE INSERT ON public.quotations
FOR EACH ROW EXECUTE FUNCTION public.generate_quotation_no();

-- =========================================
-- Resolve referral_mobile → referral_customer_id (+ pending_referrals)
-- =========================================
CREATE OR REPLACE FUNCTION public.quotations_resolve_referral_fn()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  v_found uuid;
BEGIN
  IF NEW.for_self IS TRUE THEN
    NEW.referral_name := '';
    NEW.referral_mobile := '';
    NEW.referral_customer_id := NULL;
    RETURN NEW;
  END IF;

  IF NEW.referral_mobile IS NULL OR length(trim(NEW.referral_mobile)) = 0 THEN
    NEW.referral_customer_id := NULL;
    RETURN NEW;
  END IF;

  SELECT id INTO v_found
    FROM public.customers
   WHERE mobile = NEW.referral_mobile
     AND deleted_at IS NULL
     AND id <> NEW.customer_id
   LIMIT 1;

  NEW.referral_customer_id := v_found; -- NULL if not yet registered

  -- Track pending referral so existing auto-match trigger picks it up on signup
  IF v_found IS NULL THEN
    INSERT INTO public.pending_referrals(referrer_id, mobile, note, status)
    VALUES (NEW.customer_id, NEW.referral_mobile, 'from_quotation', 'pending')
    ON CONFLICT DO NOTHING;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_quotations_resolve_referral ON public.quotations;
CREATE TRIGGER trg_quotations_resolve_referral
BEFORE INSERT OR UPDATE OF referral_mobile, for_self ON public.quotations
FOR EACH ROW EXECUTE FUNCTION public.quotations_resolve_referral_fn();

-- =========================================
-- Updated_at trigger
-- =========================================
DROP TRIGGER IF EXISTS trg_quotations_updated_at ON public.quotations;
CREATE TRIGGER trg_quotations_updated_at
BEFORE UPDATE ON public.quotations
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

-- =========================================
-- Recalc total when items change
-- =========================================
CREATE OR REPLACE FUNCTION public.quotation_items_recalc_total_fn()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  v_qid uuid;
  v_total numeric;
BEGIN
  v_qid := COALESCE(NEW.quotation_id, OLD.quotation_id);
  SELECT COALESCE(SUM(line_total), 0) INTO v_total
    FROM public.quotation_items WHERE quotation_id = v_qid;
  UPDATE public.quotations SET total_amount = v_total, updated_at = now() WHERE id = v_qid;
  RETURN NULL;
END;
$$;

DROP TRIGGER IF EXISTS trg_quotation_items_recalc ON public.quotation_items;
CREATE TRIGGER trg_quotation_items_recalc
AFTER INSERT OR UPDATE OR DELETE ON public.quotation_items
FOR EACH ROW EXECUTE FUNCTION public.quotation_items_recalc_total_fn();

-- =========================================
-- RLS
-- =========================================
ALTER TABLE public.quotations ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.quotation_items ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Staff full access quotations" ON public.quotations;
CREATE POLICY "Staff full access quotations" ON public.quotations
FOR ALL TO authenticated
USING (is_staff(auth.uid()))
WITH CHECK (is_staff(auth.uid()));

DROP POLICY IF EXISTS "Staff full access quotation_items" ON public.quotation_items;
CREATE POLICY "Staff full access quotation_items" ON public.quotation_items
FOR ALL TO authenticated
USING (is_staff(auth.uid()))
WITH CHECK (is_staff(auth.uid()));
