/**
 * Widen plan_tier CHECK constraints to include the canonical per-connection tiers
 * 'professional' and 'managed_services'.
 *
 * The pricing page + config/pricing.js were renamed ('team' → 'professional') and
 * gained 'managed_services', but the DB CHECK constraints on payments / subscriptions /
 * user_credits still only allowed the legacy names. Result: a Starter user upgrading to
 * Professional or Managed Service passed app validation and had a Razorpay order created,
 * then failed at the DB write with a 23514 check-constraint violation — surfaced to the
 * user as "Failed to process plan change".
 *
 * Fix: rebuild each constraint to allow the new tiers while keeping every legacy value
 * (individual/team/growth/scale/custom) for backwards compatibility with existing rows.
 * Idempotent — DROP CONSTRAINT IF EXISTS then ADD.
 */
module.exports = {
  name: 'widen_plan_tier_check_constraints',
  up: async (client) => {
    // payments + subscriptions share the same allowed set (no 'free' — you never pay for free)
    const PAID_TIERS =
      "'starter','growth','scale','custom','individual','team','professional','business','enterprise','managed_services'";
    // user_credits additionally allows 'free'
    const CREDIT_TIERS = "'free'," + PAID_TIERS;

    await client.query(`
      ALTER TABLE payments DROP CONSTRAINT IF EXISTS payments_plan_tier_check;
      ALTER TABLE payments ADD CONSTRAINT payments_plan_tier_check
        CHECK (plan_tier = ANY (ARRAY[${PAID_TIERS}]::text[]));
    `);

    await client.query(`
      ALTER TABLE subscriptions DROP CONSTRAINT IF EXISTS subscriptions_plan_tier_check;
      ALTER TABLE subscriptions ADD CONSTRAINT subscriptions_plan_tier_check
        CHECK (plan_tier = ANY (ARRAY[${PAID_TIERS}]::text[]));
    `);

    await client.query(`
      ALTER TABLE user_credits DROP CONSTRAINT IF EXISTS user_credits_plan_tier_check;
      ALTER TABLE user_credits ADD CONSTRAINT user_credits_plan_tier_check
        CHECK (plan_tier = ANY (ARRAY[${CREDIT_TIERS}]::text[]));
    `);
  },
};
