create table if not exists staff_accounts (
  id uuid primary key default gen_random_uuid(),
  email text not null unique,
  phone text not null default '',
  display_name text not null,
  role text not null,
  status text not null default 'active',
  password_salt text not null,
  password_hash text not null,
  last_login_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists staff_sessions (
  token text primary key,
  account_id uuid not null references staff_accounts(id) on delete cascade,
  expires_at timestamptz not null,
  created_at timestamptz not null default now()
);

create index if not exists idx_staff_accounts_role on staff_accounts(role, status);
create index if not exists idx_staff_sessions_account on staff_sessions(account_id, expires_at desc);

insert into staff_accounts (
  email, phone, display_name, role, status, password_salt, password_hash
)
values
  (
    'admin@dlbdi.local',
    '13900000001',
    '超级管理员',
    'super_admin',
    'active',
    'dlbdi-admin',
    encode(digest('Admin@123456' || 'dlbdi-admin', 'sha256'), 'hex')
  ),
  (
    'ops@dlbdi.local',
    '13900000002',
    '运营管理员',
    'operations_admin',
    'active',
    'dlbdi-ops',
    encode(digest('Ops@123456' || 'dlbdi-ops', 'sha256'), 'hex')
  ),
  (
    'staff@dlbdi.local',
    '13900000003',
    '智能体员工',
    'agent_staff',
    'active',
    'dlbdi-staff',
    encode(digest('Staff@123456' || 'dlbdi-staff', 'sha256'), 'hex')
  ),
  (
    'consultant@dlbdi.local',
    '13900000004',
    '咨询顾问',
    'consultant',
    'active',
    'dlbdi-consultant',
    encode(digest('Consultant@123456' || 'dlbdi-consultant', 'sha256'), 'hex')
  ),
  (
    'expert@dlbdi.local',
    '13900000005',
    '专家账号',
    'expert',
    'active',
    'dlbdi-expert',
    encode(digest('Expert@123456' || 'dlbdi-expert', 'sha256'), 'hex')
  )
on conflict (email) do update set
  phone = excluded.phone,
  display_name = excluded.display_name,
  role = excluded.role,
  status = excluded.status,
  password_salt = excluded.password_salt,
  password_hash = excluded.password_hash,
  updated_at = now();
