WITH ranked AS (
  SELECT
    id,
    user_id,
    provider,
    status::text AS previous_status,
    current_period_end,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY
        CASE WHEN provider = 'stripe' THEN 0 ELSE 1 END,
        current_period_end DESC,
        updated_at DESC,
        id DESC
    ) AS position
  FROM subscriptions
  WHERE status IN ('active', 'trialing', 'past_due')
), duplicates AS (
  SELECT *
  FROM ranked
  WHERE position > 1
)
INSERT INTO audit_logs (
  user_id, action, resource_type, resource_id, details
)
SELECT
  user_id,
  'migration_subscription_dedup',
  'subscription',
  id,
  jsonb_build_object(
    'migration', '023_cross_provider_subscription_invariant',
    'provider', provider,
    'previous_status', previous_status,
    'current_period_end', current_period_end,
    'reason', 'cross_provider_single_effective_subscription'
  )
FROM duplicates;

WITH ranked AS (
  SELECT
    id,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY
        CASE WHEN provider = 'stripe' THEN 0 ELSE 1 END,
        current_period_end DESC,
        updated_at DESC,
        id DESC
    ) AS position
  FROM subscriptions
  WHERE status IN ('active', 'trialing', 'past_due')
)
UPDATE subscriptions AS subscription
SET status = 'canceled',
    cancel_at_period_end = false,
    canceled_at = COALESCE(subscription.canceled_at, NOW()),
    updated_at = NOW()
FROM ranked
WHERE ranked.position > 1
  AND subscription.id = ranked.id;

CREATE UNIQUE INDEX idx_subscriptions_user_effective_unique
  ON subscriptions(user_id)
  WHERE status IN ('active', 'trialing', 'past_due');