-- One-time backfill
UPDATE public.clients
SET amc_status = CASE
  WHEN next_renewal_date IS NULL THEN amc_status
  WHEN next_renewal_date < CURRENT_DATE THEN 'expired'::amc_status
  ELSE 'active'::amc_status
END
WHERE next_renewal_date IS NOT NULL;

-- Trigger function to keep amc_status in sync with next_renewal_date
CREATE OR REPLACE FUNCTION public.sync_amc_status()
RETURNS trigger
LANGUAGE plpgsql
SET search_path = public
AS $$
BEGIN
  IF NEW.next_renewal_date IS NOT NULL THEN
    IF NEW.next_renewal_date < CURRENT_DATE THEN
      NEW.amc_status := 'expired'::amc_status;
    ELSE
      NEW.amc_status := 'active'::amc_status;
    END IF;
  END IF;
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS clients_sync_amc_status ON public.clients;
CREATE TRIGGER clients_sync_amc_status
BEFORE INSERT OR UPDATE OF next_renewal_date ON public.clients
FOR EACH ROW
EXECUTE FUNCTION public.sync_amc_status();