-- Update plan column to allow all 4 plan values
ALTER TABLE public.profiles
  DROP CONSTRAINT IF EXISTS profiles_plan_check;

ALTER TABLE public.profiles
  ADD CONSTRAINT profiles_plan_check
  CHECK (plan IN ('free', 'premium', 'extra_premium', 'platinum'));

-- Ensure stripe_customer_id and stripe_subscription_id columns exist
ALTER TABLE public.profiles
  ADD COLUMN IF NOT EXISTS stripe_customer_id TEXT,
  ADD COLUMN IF NOT EXISTS stripe_subscription_id TEXT,
  ADD COLUMN IF NOT EXISTS stripe_price_id TEXT,
  ADD COLUMN IF NOT EXISTS subscription_status TEXT,
  ADD COLUMN IF NOT EXISTS analyses_today INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS last_run_date DATE;

-- Index for webhook lookups
CREATE INDEX IF NOT EXISTS profiles_stripe_sub_idx ON public.profiles (stripe_subscription_id);
CREATE INDEX IF NOT EXISTS profiles_stripe_cust_idx ON public.profiles (stripe_customer_id);
