alter table coupons
  add column if not exists description text not null default '',
  add column if not exists max_discount_amount integer,
  add column if not exists product_scope jsonb not null default '{"mode":"all","productTypes":[],"productIds":[],"excludeProductIds":[]}'::jsonb,
  add column if not exists user_scope jsonb not null default '{"membershipLevels":[],"passportNos":[],"userIds":[],"firstOrderOnly":false,"newUserOnly":false}'::jsonb,
  add column if not exists usage_limits jsonb not null default '{"perUser":0,"perDay":0}'::jsonb,
  add column if not exists stacking_policy jsonb not null default '{"allowMemberDiscount":true}'::jsonb,
  add column if not exists budget_amount integer,
  add column if not exists used_discount_amount integer not null default 0;

alter table coupon_redemptions
  add column if not exists coupon_code_snapshot text not null default '',
  add column if not exists coupon_name_snapshot text not null default '',
  add column if not exists discount_base_amount integer not null default 0,
  add column if not exists rule_snapshot jsonb not null default '{}'::jsonb;

update coupons
set
  product_scope = coalesce(product_scope, '{"mode":"all","productTypes":[],"productIds":[],"excludeProductIds":[]}'::jsonb),
  user_scope = coalesce(user_scope, '{"membershipLevels":[],"passportNos":[],"userIds":[],"firstOrderOnly":false,"newUserOnly":false}'::jsonb),
  usage_limits = coalesce(usage_limits, '{"perUser":0,"perDay":0}'::jsonb),
  stacking_policy = coalesce(stacking_policy, '{"allowMemberDiscount":true}'::jsonb),
  used_discount_amount = coalesce(used_discount_amount, 0);

create index if not exists idx_coupons_discount_type on coupons(discount_type);
create index if not exists idx_coupon_redemptions_coupon_user on coupon_redemptions(coupon_id, user_id, created_at desc);

