-- Backfill customer_cost_per_token for all active subscriptions
-- based on their plan tier pricing and credit allocation
UPDATE subscriptions s
SET customer_cost_per_token = (
  SELECT CASE
    WHEN s.billing_cycle = 'monthly' THEN st.monthly_price / st.credit_allocation
    WHEN s.billing_cycle = 'annual' THEN st.annual_price / st.credit_allocation
  END
  FROM subscription_tiers st
  WHERE st.slug = s.plan_tier
    AND st.plugin = 'translate'
    AND st.credit_allocation > 0
)
WHERE s.status = 'active'
  AND s.customer_cost_per_token IS NULL;
