alter table membership_subscriptions
  add column if not exists cancel_requested_at timestamptz,
  add column if not exists cancel_requested_reason text not null default '',
  add column if not exists cancelled_by text not null default '',
  add column if not exists credited_to_subscription_id uuid references membership_subscriptions(id) on delete set null,
  add column if not exists paid_amount_snapshot integer not null default 0,
  add column if not exists order_item_snapshot jsonb not null default '{}'::jsonb;

create table if not exists membership_benefit_rules (
  code text primary key,
  label text not null,
  group_key text not null,
  benefit_type text not null,
  target_type text not null default 'global',
  target_ref text not null default '',
  execution_mode text not null default 'display',
  default_value jsonb not null default '{}'::jsonb,
  status text not null default 'active',
  sort_order int not null default 100,
  description text not null default '',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists membership_plan_benefits (
  id uuid primary key default gen_random_uuid(),
  plan_product_id text not null references membership_plans(product_id) on delete cascade,
  benefit_code text not null references membership_benefit_rules(code) on delete cascade,
  enabled boolean not null default true,
  value_json jsonb not null default '{}'::jsonb,
  display_label text not null default '',
  sort_order int not null default 100,
  status text not null default 'active',
  unique(plan_product_id, benefit_code)
);

create table if not exists membership_entitlement_accounts (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references users(id) on delete cascade,
  subscription_id uuid references membership_subscriptions(id) on delete cascade,
  benefit_code text not null references membership_benefit_rules(code),
  period_key text not null,
  quota_total int not null default 0,
  quota_used int not null default 0,
  valid_from timestamptz not null default now(),
  valid_to timestamptz not null,
  status text not null default 'active',
  metadata jsonb not null default '{}'::jsonb,
  unique(user_id, subscription_id, benefit_code, period_key)
);

alter table membership_entitlement_accounts
  add column if not exists created_at timestamptz not null default now(),
  add column if not exists updated_at timestamptz not null default now();

create index if not exists idx_membership_entitlement_accounts_user
  on membership_entitlement_accounts(user_id, status, valid_to desc);

create table if not exists membership_entitlement_ledger (
  id uuid primary key default gen_random_uuid(),
  account_id uuid references membership_entitlement_accounts(id) on delete set null,
  user_id uuid not null references users(id) on delete cascade,
  benefit_code text not null,
  action text not null,
  delta int not null default 0,
  ref_type text not null default '',
  ref_id text not null default '',
  before_value int not null default 0,
  after_value int not null default 0,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create index if not exists idx_membership_entitlement_ledger_user
  on membership_entitlement_ledger(user_id, created_at desc);

create table if not exists membership_service_assignments (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references users(id) on delete cascade,
  subscription_id uuid references membership_subscriptions(id) on delete set null,
  assignment_type text not null,
  assigned_staff_account_id uuid references staff_accounts(id) on delete set null,
  status text not null default 'active',
  note text not null default '',
  assigned_at timestamptz not null default now(),
  ended_at timestamptz
);

create index if not exists idx_membership_service_assignments_user
  on membership_service_assignments(user_id, status, assignment_type);

create table if not exists membership_content_deliveries (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  content_type text not null default 'monthly_report',
  eligible_tiers text[] not null default '{junior,senior}',
  file_id uuid references uploaded_files(id) on delete set null,
  href text not null default '',
  period_key text not null,
  status text not null default 'draft',
  published_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create unique index if not exists idx_membership_content_deliveries_seed
  on membership_content_deliveries(content_type, period_key, title);

alter table data_service_requests
  add column if not exists original_quoted_amount integer not null default 0,
  add column if not exists membership_discount_amount integer not null default 0,
  add column if not exists membership_discount_note text not null default '',
  add column if not exists membership_level_snapshot text not null default '';

alter table consulting_requests
  add column if not exists original_quoted_amount integer not null default 0,
  add column if not exists membership_discount_amount integer not null default 0,
  add column if not exists membership_discount_note text not null default '',
  add column if not exists membership_level_snapshot text not null default '';

insert into membership_benefit_rules (
  code, label, group_key, benefit_type, target_type, execution_mode,
  default_value, sort_order, description
)
values
  ('expert_discount_percent', '专家预约会员折扣', 'pricing', 'discount_percent', 'expert', 'automatic', '{"junior":10,"senior":20}', 10, '专家预约自动按有效会员等级计算折扣。'),
  ('data_service_manual_discount_hint', '数据服务报价会员优惠提示', 'manual_quote', 'manual_discount_hint', 'data_service', 'manual_quote', '{}', 20, '报价时展示会员身份，由报价人员人工输入优惠金额并留痕。'),
  ('consulting_manual_discount_hint', '规划咨询报价会员优惠提示', 'manual_quote', 'manual_discount_hint', 'consulting', 'manual_quote', '{}', 30, '报价时展示会员身份，由报价人员人工输入优惠金额并留痕。'),
  ('agent_monthly_quota', '智能体会员月度额度', 'quota', 'quota', 'agent', 'ledger', '{"period":"month","junior":1,"senior":3}', 40, '可在智能体履约任务中人工消耗，消耗写入额度流水。'),
  ('monthly_ai_report', '会员月度 AI 报告', 'content', 'content_access', 'report', 'display', '{}', 50, '会员可查看后台发布的月度报告。'),
  ('senior_priority_sla', '高级会员优先服务标识', 'priority', 'priority_badge', 'service', 'display', '{"tier":"senior"}', 60, '咨询、数据服务、专家预约列表展示高级会员优先标识。'),
  ('customer_manager', '高级会员客户经理', 'service', 'staff_assignment', 'membership', 'manual_assignment', '{"tier":"senior"}', 70, '高级会员可分配客户经理。')
on conflict (code) do update set
  label = excluded.label,
  group_key = excluded.group_key,
  benefit_type = excluded.benefit_type,
  target_type = excluded.target_type,
  execution_mode = excluded.execution_mode,
  default_value = excluded.default_value,
  sort_order = excluded.sort_order,
  description = excluded.description,
  updated_at = now();

insert into membership_plan_benefits (
  plan_product_id, benefit_code, enabled, value_json, display_label, sort_order, status
)
values
  ('membership-junior', 'expert_discount_percent', true, '{"percent":10}', '专家预约 9 折', 10, 'active'),
  ('membership-junior', 'data_service_manual_discount_hint', true, '{}', '数据服务报价会员提示', 20, 'active'),
  ('membership-junior', 'consulting_manual_discount_hint', true, '{}', '规划咨询报价会员提示', 30, 'active'),
  ('membership-junior', 'agent_monthly_quota', true, '{"quota":1,"period":"month"}', '每月 1 次智能体额度', 40, 'active'),
  ('membership-junior', 'monthly_ai_report', true, '{}', '会员月度 AI 报告', 50, 'active'),
  ('membership-senior', 'expert_discount_percent', true, '{"percent":20}', '专家预约 8 折', 10, 'active'),
  ('membership-senior', 'data_service_manual_discount_hint', true, '{}', '数据服务报价高级会员提示', 20, 'active'),
  ('membership-senior', 'consulting_manual_discount_hint', true, '{}', '规划咨询报价高级会员提示', 30, 'active'),
  ('membership-senior', 'agent_monthly_quota', true, '{"quota":3,"period":"month"}', '每月 3 次智能体额度', 40, 'active'),
  ('membership-senior', 'monthly_ai_report', true, '{}', '会员月度 AI 报告', 50, 'active'),
  ('membership-senior', 'senior_priority_sla', true, '{}', '高级会员优先响应', 60, 'active'),
  ('membership-senior', 'customer_manager', true, '{}', '专属客户经理', 70, 'active')
on conflict (plan_product_id, benefit_code) do update set
  enabled = excluded.enabled,
  value_json = excluded.value_json,
  display_label = excluded.display_label,
  sort_order = excluded.sort_order,
  status = excluded.status;

insert into membership_content_deliveries (title, content_type, eligible_tiers, href, period_key, status, published_at)
values (
  '会员月度 AI 报告样例',
  'monthly_report',
  '{junior,senior}',
  '/membership/reports',
  to_char(now(), 'YYYY-MM'),
  'published',
  now()
)
on conflict do nothing;

update staff_roles
set permissions = (
  select array_agg(distinct p.permission)
  from unnest(permissions || array[
    'membership_plan:read',
    'membership_plan:write',
    'membership_benefit:read',
    'membership_benefit:write',
    'membership_subscription:update',
    'membership_entitlement:update',
    'membership_content:write'
  ]::text[]) as p(permission)
),
updated_at = now()
where code = 'operations_admin';

update staff_roles
set permissions = (
  select array_agg(distinct p.permission)
  from unnest(permissions || array[
    'membership_plan:read',
    'membership_benefit:read',
    'membership_content:write'
  ]::text[]) as p(permission)
),
updated_at = now()
where code = 'content_admin';
