alter table order_items
  add column if not exists billing_cycle text not null default 'once';

create table if not exists membership_subscriptions (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references users(id) on delete cascade,
  tier text not null check (tier in ('junior', 'senior')),
  product_id text references products(id),
  billing_cycle text not null check (billing_cycle in ('monthly', 'yearly', 'manual_grant')),
  status text not null default 'active' check (status in ('active', 'past_due', 'expired', 'canceled', 'refunded')),
  starts_at timestamptz not null,
  expires_at timestamptz not null,
  ended_at timestamptz,
  auto_renew boolean not null default false,
  source_order_id uuid references orders(id) on delete set null,
  latest_renewal_order_id uuid references orders(id) on delete set null,
  canceled_at timestamptz,
  cancel_reason text not null default '',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (source_order_id, tier)
);

create index if not exists idx_membership_subscriptions_user_status
  on membership_subscriptions(user_id, status, expires_at desc);

create index if not exists idx_membership_subscriptions_expiry
  on membership_subscriptions(status, expires_at);

create table if not exists membership_events (
  id uuid primary key default gen_random_uuid(),
  subscription_id uuid references membership_subscriptions(id) on delete set null,
  user_id uuid not null references users(id) on delete cascade,
  event_type text not null,
  from_tier text,
  to_tier text,
  from_status text,
  to_status text,
  order_id uuid references orders(id) on delete set null,
  payload_json jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create index if not exists idx_membership_events_user_time
  on membership_events(user_id, created_at desc);

create index if not exists idx_membership_events_type_time
  on membership_events(event_type, created_at desc);

create table if not exists notification_messages (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references users(id) on delete cascade,
  scene text not null,
  channel text not null check (channel in ('in_app', 'sms', 'wechat_subscribe', 'email')),
  template_key text not null,
  payload_json jsonb not null default '{}'::jsonb,
  dedupe_key text not null unique,
  status text not null default 'queued' check (status in ('queued', 'sent', 'failed', 'skipped')),
  scheduled_at timestamptz not null default now(),
  sent_at timestamptz,
  read_at timestamptz,
  failed_reason text not null default '',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists idx_notification_messages_dispatch
  on notification_messages(status, scheduled_at);

create index if not exists idx_notification_messages_user_time
  on notification_messages(user_id, created_at desc);

create table if not exists notification_preferences (
  user_id uuid primary key references users(id) on delete cascade,
  in_app_enabled boolean not null default true,
  sms_enabled boolean not null default false,
  wechat_subscribe_enabled boolean not null default false,
  email_enabled boolean not null default false,
  quiet_hours_start text not null default '21:00',
  quiet_hours_end text not null default '09:00',
  updated_at timestamptz not null default now()
);

insert into membership_subscriptions (
  user_id, tier, product_id, billing_cycle, status, starts_at, expires_at, auto_renew
)
select
  u.id,
  u.membership_level,
  case when u.membership_level = 'senior' then 'membership-senior' else 'membership-junior' end,
  'manual_grant',
  'active',
  coalesce(u.created_at, now()),
  now() + interval '1 year',
  false
from users u
where u.membership_level in ('junior', 'senior')
  and not exists (
    select 1
    from membership_subscriptions ms
    where ms.user_id = u.id
      and ms.status in ('active', 'past_due')
      and ms.expires_at > now()
  );
