alter table if exists data_product_details
  add column if not exists tags text[] not null default '{}',
  add column if not exists case_goal text not null default '',
  add column if not exists request_outline text not null default '',
  add column if not exists delivery_outline text not null default '',
  add column if not exists service_note text not null default '';

update products
set purchase_flow = 'application',
    fulfillment_flow = 'data_service',
    updated_at = now()
where type = 'data';

alter table if exists staff_roles
  add column if not exists role_family text not null default 'operations',
  add column if not exists role_level int not null default 50,
  add column if not exists sort_order int not null default 100,
  add column if not exists is_system boolean not null default false;

create table if not exists staff_org_units (
  id uuid primary key default gen_random_uuid(),
  parent_id uuid references staff_org_units(id) on delete restrict,
  code text not null unique,
  name text not null,
  unit_type text not null default 'department',
  path text not null default '',
  depth int not null default 0,
  sort_order int not null default 100,
  status text not null default 'active',
  manager_account_id uuid references staff_accounts(id) on delete set null,
  description text not null default '',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists staff_positions (
  id uuid primary key default gen_random_uuid(),
  org_unit_id uuid not null references staff_org_units(id) on delete restrict,
  parent_position_id uuid references staff_positions(id) on delete set null,
  code text not null unique,
  name text not null,
  role_code text not null references staff_roles(code),
  position_level int not null default 50,
  sort_order int not null default 100,
  status text not null default 'active',
  description text not null default '',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists staff_account_assignments (
  id uuid primary key default gen_random_uuid(),
  account_id uuid not null references staff_accounts(id) on delete cascade,
  org_unit_id uuid not null references staff_org_units(id) on delete restrict,
  position_id uuid references staff_positions(id) on delete set null,
  role_code text not null references staff_roles(code),
  is_primary boolean not null default true,
  status text not null default 'active',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists staff_permission_grants (
  id uuid primary key default gen_random_uuid(),
  subject_type text not null,
  subject_id uuid not null,
  permission_code text not null,
  created_at timestamptz not null default now(),
  unique(subject_type, subject_id, permission_code)
);

alter table if exists staff_accounts
  add column if not exists org_unit_id uuid references staff_org_units(id) on delete set null,
  add column if not exists position_id uuid references staff_positions(id) on delete set null;

create index if not exists idx_staff_org_units_parent_sort
  on staff_org_units(parent_id, sort_order, name);

create index if not exists idx_staff_positions_org_sort
  on staff_positions(org_unit_id, sort_order, name);

create index if not exists idx_staff_account_assignments_account
  on staff_account_assignments(account_id, status, is_primary desc);

create index if not exists idx_staff_account_assignments_org
  on staff_account_assignments(org_unit_id, position_id, status);

create unique index if not exists idx_staff_account_assignments_unique_active
  on staff_account_assignments(account_id, org_unit_id, coalesce(position_id, '00000000-0000-0000-0000-000000000000'::uuid), role_code)
  where status = 'active';

insert into staff_roles (code, name, description, default_entry, status, permissions, role_family, role_level, sort_order, is_system)
values
  (
    'data_service_staff',
    '数据服务专员',
    '负责后台数据服务需求受理、报价、推进和交付。',
    '/admin/data-service',
    'active',
    array['data_service:read', 'data_service:update', 'user:read', 'data_page:read']::text[],
    'fulfillment',
    50,
    55,
    true
  )
on conflict (code) do update set
  name = excluded.name,
  description = excluded.description,
  default_entry = excluded.default_entry,
  status = excluded.status,
  permissions = excluded.permissions,
  role_family = excluded.role_family,
  role_level = excluded.role_level,
  sort_order = excluded.sort_order,
  is_system = excluded.is_system,
  updated_at = now();

update staff_roles
set role_family = 'system', role_level = 0, sort_order = 0, is_system = true
where code = 'super_admin';

update staff_roles
set role_family = 'operations',
    role_level = 40,
    sort_order = 10,
    is_system = true,
    description = '负责商品、数据页面、优惠码、专家审核、活动报名、合作咨询和订单只读。',
    permissions = (
      select array_agg(distinct permission order by permission)
      from unnest(permissions || array['data_page:read', 'data_page:write', 'data_service:read']::text[]) as permission
    ),
    updated_at = now()
where code = 'operations_admin';

update staff_roles
set role_family = 'finance', role_level = 50, sort_order = 60, is_system = true
where code = 'finance_admin';

update staff_roles
set role_family = 'content',
    role_level = 50,
    sort_order = 40,
    is_system = true,
    description = '负责资讯、首页、关于我们、规划咨询、数据页面、协议等站点内容配置。',
    permissions = (
      select array_agg(distinct permission order by permission)
      from unnest(permissions || array['data_page:read', 'data_page:write']::text[]) as permission
    ),
    updated_at = now()
where code = 'content_admin';

update staff_roles
set role_family = 'fulfillment', role_level = 60, sort_order = 70, is_system = true
where code = 'agent_staff';

update staff_roles
set role_family = 'fulfillment', role_level = 50, sort_order = 50, is_system = true
where code = 'consultant';

update staff_roles
set role_family = 'fulfillment', role_level = 70, sort_order = 80, is_system = true
where code = 'checkin_staff';

insert into staff_org_units (code, name, unit_type, path, depth, sort_order, status, description)
values
  ('platform', '平台管理', 'root', '/platform', 0, 0, 'active', '平台最高管理组织'),
  ('operations', '运营中心', 'department', '/platform/operations', 1, 10, 'active', '商品、活动、合作和平台运营'),
  ('fulfillment', '履约中心', 'department', '/platform/fulfillment', 1, 20, 'active', '咨询、数据服务和交付任务'),
  ('data_service', '数据服务组', 'team', '/platform/fulfillment/data_service', 2, 21, 'active', '数据服务需求、报价和交付'),
  ('content', '内容中心', 'department', '/platform/content', 1, 30, 'active', '站点内容和资讯维护'),
  ('finance', '财务中心', 'department', '/platform/finance', 1, 40, 'active', '订单、支付、发票和对账'),
  ('expert_center', '专家中心', 'department', '/platform/expert_center', 1, 50, 'active', '专家入驻、预约和排期'),
  ('system_ops', '系统运维', 'department', '/platform/system_ops', 1, 60, 'active', '系统状态、异常和组织权限')
on conflict (code) do update set
  name = excluded.name,
  unit_type = excluded.unit_type,
  path = excluded.path,
  depth = excluded.depth,
  sort_order = excluded.sort_order,
  status = excluded.status,
  description = excluded.description,
  updated_at = now();

update staff_org_units child
set parent_id = parent.id,
    updated_at = now()
from staff_org_units parent
where child.code in ('operations', 'fulfillment', 'content', 'finance', 'expert_center', 'system_ops')
  and parent.code = 'platform';

update staff_org_units child
set parent_id = parent.id,
    updated_at = now()
from staff_org_units parent
where child.code = 'data_service'
  and parent.code = 'fulfillment';

insert into staff_positions (org_unit_id, code, name, role_code, position_level, sort_order, status, description)
select ou.id, seed.code, seed.name, seed.role_code, seed.position_level, seed.sort_order, 'active', seed.description
from (
  values
    ('platform', 'super_admin', '超级管理员', 'super_admin', 0, 0, '平台最高权限岗位'),
    ('operations', 'operations_admin', '运营管理员', 'operations_admin', 40, 10, '运营中心管理岗位'),
    ('data_service', 'data_service_staff', '数据服务专员', 'data_service_staff', 50, 20, '数据服务组专职岗位'),
    ('fulfillment', 'consultant', '咨询顾问', 'consultant', 50, 30, '咨询需求和方案跟进岗位'),
    ('fulfillment', 'agent_staff', '履约员工', 'agent_staff', 60, 40, '订单交付任务岗位'),
    ('content', 'content_admin', '内容管理员', 'content_admin', 50, 50, '内容配置岗位'),
    ('finance', 'finance_admin', '财务管理员', 'finance_admin', 50, 60, '财务结算岗位'),
    ('operations', 'checkin_staff', '活动核销员', 'checkin_staff', 70, 70, '课程和活动核销岗位')
) as seed(org_code, code, name, role_code, position_level, sort_order, description)
join staff_org_units ou on ou.code = seed.org_code
on conflict (code) do update set
  org_unit_id = excluded.org_unit_id,
  name = excluded.name,
  role_code = excluded.role_code,
  position_level = excluded.position_level,
  sort_order = excluded.sort_order,
  status = excluded.status,
  description = excluded.description,
  updated_at = now();

update staff_accounts sa
set org_unit_id = sp.org_unit_id,
    position_id = sp.id,
    updated_at = now()
from staff_positions sp
where sp.role_code = sa.role
  and sa.role <> 'expert'
  and (sa.org_unit_id is null or sa.position_id is null);

insert into staff_account_assignments (account_id, org_unit_id, position_id, role_code, is_primary, status)
select sa.id, sp.org_unit_id, sp.id, sa.role, true, 'active'
from staff_accounts sa
join staff_positions sp on sp.role_code = sa.role
where sa.role <> 'expert'
on conflict do nothing;
