CREATE TABLE public.quotes (
  id uuid primary key default gen_random_uuid(),
  quote_number text not null unique,
  customer_id uuid not null references public.customers(id) on delete restrict,
  quote_date date not null default current_date,
  valid_until date,
  status text not null default 'draft',
  subtotal numeric not null default 0,
  discount_total numeric not null default 0,
  tax_total numeric not null default 0,
  total numeric not null default 0,
  notes text,
  terms text,
  studio_id uuid references public.studios(id) on delete set null,
  room_id uuid references public.rooms(id) on delete set null,
  service_id uuid references public.services(id) on delete set null,
  invoice_id uuid references public.invoices(id) on delete set null,
  approved_by uuid,
  approved_at timestamptz,
  rejection_reason text,
  created_by uuid default auth.uid(),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

CREATE TABLE public.quote_items (
  id uuid primary key default gen_random_uuid(),
  quote_id uuid not null references public.quotes(id) on delete cascade,
  line_no integer not null default 1,
  item_type text not null default 'service',
  service_id uuid references public.services(id) on delete set null,
  product_id uuid references public.products(id) on delete set null,
  description text not null,
  quantity numeric not null default 1,
  unit_price numeric not null default 0,
  discount numeric not null default 0,
  tax_rate numeric not null default 0,
  tax_amount numeric not null default 0,
  line_total numeric not null default 0,
  revenue_account_id uuid references public.accounts(id) on delete set null,
  created_at timestamptz not null default now()
);

CREATE INDEX idx_quote_items_quote ON public.quote_items(quote_id);
CREATE INDEX idx_quotes_customer ON public.quotes(customer_id);

GRANT SELECT, INSERT, UPDATE, DELETE ON public.quotes TO authenticated;
GRANT ALL ON public.quotes TO service_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.quote_items TO authenticated;
GRANT ALL ON public.quote_items TO service_role;

ALTER TABLE public.quotes ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.quote_items ENABLE ROW LEVEL SECURITY;

CREATE POLICY quotes_select ON public.quotes FOR SELECT TO authenticated USING (public.can_access('invoices'));
CREATE POLICY quotes_insert ON public.quotes FOR INSERT TO authenticated WITH CHECK (public.can_edit('invoices'));
CREATE POLICY quotes_update ON public.quotes FOR UPDATE TO authenticated USING (public.can_edit('invoices')) WITH CHECK (public.can_edit('invoices'));
CREATE POLICY quotes_delete ON public.quotes FOR DELETE TO authenticated USING (public.can_edit('invoices'));

CREATE POLICY quote_items_select ON public.quote_items FOR SELECT TO authenticated USING (public.can_access('invoices'));
CREATE POLICY quote_items_insert ON public.quote_items FOR INSERT TO authenticated WITH CHECK (public.can_edit('invoices'));
CREATE POLICY quote_items_update ON public.quote_items FOR UPDATE TO authenticated USING (public.can_edit('invoices')) WITH CHECK (public.can_edit('invoices'));
CREATE POLICY quote_items_delete ON public.quote_items FOR DELETE TO authenticated USING (public.can_edit('invoices'));

CREATE TRIGGER quotes_set_updated_at BEFORE UPDATE ON public.quotes
FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

INSERT INTO public.number_sequences(key, prefix) VALUES ('quote', 'QT-')
ON CONFLICT (key) DO NOTHING;

CREATE OR REPLACE FUNCTION public.approve_quote(_quote_id uuid)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path TO 'public' AS $$
DECLARE _q public.quotes;
BEGIN
  IF NOT public.can_edit('invoices') THEN RAISE EXCEPTION 'You do not have permission to approve quotes'; END IF;
  SELECT * INTO _q FROM public.quotes WHERE id = _quote_id FOR UPDATE;
  IF _q.id IS NULL THEN RAISE EXCEPTION 'Quote not found'; END IF;
  IF _q.status IN ('invoiced') THEN RAISE EXCEPTION 'This quote has already been converted to an invoice'; END IF;
  UPDATE public.quotes SET status = 'approved', approved_by = auth.uid(), approved_at = now(),
    rejection_reason = NULL WHERE id = _quote_id;
END; $$;

CREATE OR REPLACE FUNCTION public.reject_quote(_quote_id uuid, _reason text)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path TO 'public' AS $$
DECLARE _q public.quotes;
BEGIN
  IF NOT public.can_edit('invoices') THEN RAISE EXCEPTION 'You do not have permission to reject quotes'; END IF;
  SELECT * INTO _q FROM public.quotes WHERE id = _quote_id FOR UPDATE;
  IF _q.id IS NULL THEN RAISE EXCEPTION 'Quote not found'; END IF;
  IF _q.status = 'invoiced' THEN RAISE EXCEPTION 'This quote has already been converted to an invoice'; END IF;
  UPDATE public.quotes SET status = 'rejected', rejection_reason = _reason,
    approved_by = NULL, approved_at = NULL WHERE id = _quote_id;
END; $$;

CREATE OR REPLACE FUNCTION public.convert_quote_to_invoice(_quote_id uuid)
RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER SET search_path TO 'public' AS $$
DECLARE _q public.quotes; _inv uuid; _num text; _fallback uuid;
BEGIN
  IF NOT public.can_edit('invoices') THEN RAISE EXCEPTION 'You do not have permission to create invoices'; END IF;
  SELECT * INTO _q FROM public.quotes WHERE id = _quote_id FOR UPDATE;
  IF _q.id IS NULL THEN RAISE EXCEPTION 'Quote not found'; END IF;
  IF _q.status <> 'approved' THEN RAISE EXCEPTION 'Only approved quotes can be converted to an invoice'; END IF;
  IF _q.invoice_id IS NOT NULL THEN RETURN _q.invoice_id; END IF;
  IF NOT EXISTS (SELECT 1 FROM public.quote_items WHERE quote_id = _quote_id) THEN
    RAISE EXCEPTION 'This quote has no line items';
  END IF;

  _fallback := public.account_id_by_code('4020');
  _num := public.next_number('invoice');

  INSERT INTO public.invoices (invoice_number, customer_id, invoice_date, due_date, payment_terms,
    subtotal, discount_total, tax_total, total, balance, status, notes, terms)
  VALUES (_num, _q.customer_id, current_date, coalesce(_q.valid_until, current_date + 15), 'net_15',
    _q.subtotal, _q.discount_total, _q.tax_total, _q.total, _q.total, 'draft',
    coalesce(_q.notes, '') || CASE WHEN coalesce(_q.notes,'') = '' THEN '' ELSE ' ' END || '(From quote ' || _q.quote_number || ')',
    _q.terms)
  RETURNING id INTO _inv;

  INSERT INTO public.invoice_items (invoice_id, item_type, service_id, product_id, description, quantity,
    unit_price, discount, tax_rate, tax_amount, line_total, revenue_account_id, line_no)
  SELECT _inv, qi.item_type, qi.service_id, qi.product_id, qi.description, qi.quantity, qi.unit_price,
    qi.discount, qi.tax_rate, qi.tax_amount, qi.line_total,
    coalesce(qi.revenue_account_id, (SELECT s.revenue_account_id FROM public.services s WHERE s.id = qi.service_id), _fallback),
    qi.line_no
  FROM public.quote_items qi WHERE qi.quote_id = _quote_id ORDER BY qi.line_no;

  UPDATE public.quotes SET status = 'invoiced', invoice_id = _inv WHERE id = _quote_id;
  RETURN _inv;
END; $$;

REVOKE ALL ON FUNCTION public.approve_quote(uuid) FROM PUBLIC;
REVOKE ALL ON FUNCTION public.reject_quote(uuid, text) FROM PUBLIC;
REVOKE ALL ON FUNCTION public.convert_quote_to_invoice(uuid) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.approve_quote(uuid) TO authenticated;
GRANT EXECUTE ON FUNCTION public.reject_quote(uuid, text) TO authenticated;
GRANT EXECUTE ON FUNCTION public.convert_quote_to_invoice(uuid) TO authenticated;