alter table staff_accounts
  add column if not exists position_title text not null default '',
  add column if not exists remark text not null default '';

create unique index if not exists idx_staff_accounts_phone_unique
  on staff_accounts(phone)
  where phone <> '';

create table if not exists staff_roles (
  code text primary key,
  name text not null,
  description text not null default '',
  default_entry text not null default '/staff',
  status text not null default 'active',
  permissions text[] not null default '{}',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists staff_audit_logs (
  id uuid primary key default gen_random_uuid(),
  actor_account_id uuid,
  actor_email text not null default '',
  action text not null,
  target_type text not null default 'staff_account',
  target_id text not null default '',
  summary text not null default '',
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create index if not exists idx_staff_audit_logs_created
  on staff_audit_logs(created_at desc);

create index if not exists idx_staff_audit_logs_target
  on staff_audit_logs(target_type, target_id, created_at desc);

insert into staff_roles (code, name, description, default_entry, status, permissions)
values
  (
    'super_admin',
    '超级管理员',
    '平台初始化生成的最高权限岗位，负责员工、角色、财务、内容和全部业务配置。',
    '/admin',
    'active',
    array['*']::text[]
  ),
  (
    'operations_admin',
    '运营管理员',
    '负责商品、优惠码、专家审核、活动报名、合作咨询和订单只读。',
    '/admin',
    'active',
    array[
      'product:read', 'product:write', 'coupon:read', 'coupon:write',
      'order:read', 'event:read',
      'cooperation:read', 'cooperation:update',
      'expert_profile:read', 'expert_profile:write',
      'expert_application:read', 'expert_application:review',
      'expert_booking:read', 'expert_booking:update',
      'expert_schedule:read', 'expert_schedule:write',
      'user:read'
    ]::text[]
  ),
  (
    'finance_admin',
    '财务管理员',
    '负责订单、支付、平台收入、专家分账、提现和对账导出。',
    '/admin',
    'active',
    array['order:read', 'payment:read', 'settlement:read', 'reconcile:export', 'coupon:read']::text[]
  ),
  (
    'content_admin',
    '内容管理员',
    '负责资讯、首页、关于我们、规划咨询、协议等站点内容配置。',
    '/admin',
    'active',
    array[
      'news:read', 'news:write',
      'expert_profile:read', 'expert_profile:write',
      'site:read', 'site:write'
    ]::text[]
  ),
  (
    'agent_staff',
    '履约员工',
    '负责员工工作台内的交付任务和数据服务。',
    '/staff',
    'active',
    array['task:read', 'task:update', 'data_service:read', 'data_service:update']::text[]
  ),
  (
    'consultant',
    '咨询顾问',
    '负责咨询需求、交付任务和数据服务跟进。',
    '/staff?mode=consulting',
    'active',
    array['task:read', 'task:update', 'consulting:read', 'consulting:update', 'data_service:read', 'data_service:update']::text[]
  ),
  (
    'checkin_staff',
    '活动核销员',
    '负责课程和活动报名名单查看与核销。',
    '/admin/events/checkin',
    'active',
    array['event:read', 'event_registration:read', 'event_checkin:update', 'course_registration:read', 'course_checkin:update']::text[]
  )
on conflict (code) do update set
  name = excluded.name,
  description = excluded.description,
  default_entry = excluded.default_entry,
  status = excluded.status,
  permissions = excluded.permissions,
  updated_at = now();

insert into staff_accounts (
  email, phone, display_name, role, status, position_title, remark, password_salt, password_hash
)
values
  (
    'finance@dlbdi.local',
    '13900000006',
    '财务管理员',
    'finance_admin',
    'disabled',
    '财务管理员',
    '历史初始化账号已停用；内部账号仅使用手机号验证码登录。',
    '',
    ''
  ),
  (
    'content@dlbdi.local',
    '13900000007',
    '内容管理员',
    'content_admin',
    'disabled',
    '内容管理员',
    '历史初始化账号已停用；内部账号仅使用手机号验证码登录。',
    '',
    ''
  ),
  (
    'checkin@dlbdi.local',
    '13900000008',
    '活动核销员',
    'checkin_staff',
    'disabled',
    '活动核销员',
    '历史初始化账号已停用；内部账号仅使用手机号验证码登录。',
    '',
    ''
  )
on conflict (email) do update set
  phone = excluded.phone,
  display_name = excluded.display_name,
  role = excluded.role,
  status = excluded.status,
  position_title = excluded.position_title,
  remark = excluded.remark,
  password_salt = excluded.password_salt,
  password_hash = excluded.password_hash,
  updated_at = now();

update staff_accounts
set status = 'disabled',
    remark = trim(both from concat_ws(' ', nullif(remark, ''), '专家不属于内部员工角色，已停用该员工身份。')),
    updated_at = now()
where role = 'expert';
