-- Enforce is_temporary = false whenever a profile is assigned a paid plan.
--
-- Background
-- ----------
-- Invited users are created with (plan = 'none', is_temporary = true). The Stripe sync
-- trigger clears is_temporary when it detects an active subscription. However, the sync
-- can occasionally miss updates or fire out of order, leaving the inconsistency in place.
--
-- This migration adds a second, independent DB-level enforcement layer:
--   1. A BEFORE INSERT/UPDATE trigger on profiles that auto-clears is_temporary whenever
--      plan is set to solo, team, or annual.
--   2. A CHECK constraint that makes (is_temporary = true AND plan IN ('solo', 'team', 'annual'))
--      outright impossible at the schema level.

-- 1. Fix any existing inconsistent rows
UPDATE public.profiles
SET is_temporary = false
WHERE is_temporary = true
  AND plan IN ('solo', 'team', 'annual');

-- 2. Trigger function: auto-clear is_temporary when plan is set to any paid plan value
CREATE OR REPLACE FUNCTION public.enforce_non_temporary_on_paid_plan()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  -- solo, team, and annual are all paid plan values; a temporary flag must not coexist
  IF NEW.plan IN ('solo', 'team', 'annual') THEN
    NEW.is_temporary := false;
  END IF;
  RETURN NEW;
END;
$$;

ALTER FUNCTION public.enforce_non_temporary_on_paid_plan() OWNER TO postgres;

COMMENT ON FUNCTION public.enforce_non_temporary_on_paid_plan() IS
'Automatically clears is_temporary when a profile is set to a paid plan (solo, team, or annual). '
'Acts as a DB-level safety net independent of the Stripe sync trigger.';

CREATE OR REPLACE TRIGGER enforce_non_temporary_on_paid_plan
BEFORE INSERT OR UPDATE OF plan, is_temporary
ON public.profiles
FOR EACH ROW
EXECUTE FUNCTION public.enforce_non_temporary_on_paid_plan();

-- 3. CHECK constraint: makes the inconsistent state impossible at the schema level
ALTER TABLE public.profiles
  DROP CONSTRAINT IF EXISTS profiles_no_temporary_on_paid_plan;

ALTER TABLE public.profiles
  ADD CONSTRAINT profiles_no_temporary_on_paid_plan
  CHECK (NOT (is_temporary = true AND plan IN ('solo', 'team', 'annual')));
