create table if not exists consulting_option_sets (
  code text primary key,
  name text not null,
  description text not null default '',
  sort_order integer not null default 100,
  status text not null default 'active' check (status in ('active', 'disabled')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists consulting_options (
  id uuid primary key default gen_random_uuid(),
  set_code text not null references consulting_option_sets(code) on delete cascade,
  code text not null,
  label text not null,
  description text not null default '',
  sort_order integer not null default 100,
  status text not null default 'active' check (status in ('active', 'disabled')),
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (set_code, code)
);

create index if not exists idx_consulting_options_set
  on consulting_options(set_code, status, sort_order);

alter table if exists site_consulting_directions
  add column if not exists owner_department text not null default '',
  add column if not exists owner_org_unit_id uuid references staff_org_units(id) on delete set null,
  add column if not exists default_assignee_id uuid references staff_accounts(id) on delete set null;

alter table if exists cooperation_requests
  add column if not exists assigned_account_id uuid references staff_accounts(id) on delete set null,
  add column if not exists process_note text not null default '',
  add column if not exists converted_type text not null default '',
  add column if not exists converted_ref_id uuid,
  add column if not exists converted_at timestamptz;

insert into consulting_option_sets (code, name, description, sort_order, status)
values
  ('organization_type', '组织类型', '规划咨询申请表的组织类型选项。', 10, 'active'),
  ('project_stage', '项目阶段', '规划咨询申请表的项目阶段选项。', 20, 'active'),
  ('urgency', '紧急度', '规划咨询申请表的紧急程度选项。', 30, 'active'),
  ('budget_range', '预算范围', '规划咨询申请表的预算范围选项。', 40, 'active'),
  ('expected_start', '启动时间', '规划咨询申请表的期望启动时间选项。', 50, 'active')
on conflict (code) do update set
  name = excluded.name,
  description = excluded.description,
  sort_order = excluded.sort_order,
  status = excluded.status,
  updated_at = now();

insert into consulting_options (set_code, code, label, sort_order, status)
values
  ('organization_type', 'government', '政府部门 / 事业单位', 10, 'active'),
  ('organization_type', 'park', '园区 / 平台公司', 20, 'active'),
  ('organization_type', 'state_enterprise', '国有企业', 30, 'active'),
  ('organization_type', 'private_enterprise', '民营企业', 40, 'active'),
  ('organization_type', 'research_institute', '高校 / 科研机构', 50, 'active'),
  ('organization_type', 'other', '其他组织', 60, 'active'),
  ('project_stage', 'idea', '只有初步想法', 10, 'active'),
  ('project_stage', 'researching', '正在调研可行性', 20, 'active'),
  ('project_stage', 'proposal', '已有方案需要优化', 30, 'active'),
  ('project_stage', 'implementation', '准备立项 / 实施', 40, 'active'),
  ('project_stage', 'review', '项目复盘 / 评估', 50, 'active'),
  ('urgency', 'normal', '常规沟通', 10, 'active'),
  ('urgency', 'within_month', '一个月内需要方案', 20, 'active'),
  ('urgency', 'within_week', '一周内需要反馈', 30, 'active'),
  ('urgency', 'urgent', '紧急事项', 40, 'active'),
  ('budget_range', 'unknown', '暂不确定', 10, 'active'),
  ('budget_range', 'under_50k', '5 万以下', 20, 'active'),
  ('budget_range', '50k_200k', '5-20 万', 30, 'active'),
  ('budget_range', '200k_500k', '20-50 万', 40, 'active'),
  ('budget_range', 'over_500k', '50 万以上', 50, 'active'),
  ('expected_start', 'asap', '尽快启动', 10, 'active'),
  ('expected_start', 'within_month', '一个月内', 20, 'active'),
  ('expected_start', 'this_quarter', '本季度', 30, 'active'),
  ('expected_start', 'next_quarter', '下季度', 40, 'active'),
  ('expected_start', 'not_fixed', '时间待定', 50, 'active')
on conflict (set_code, code) do update set
  label = excluded.label,
  sort_order = excluded.sort_order,
  status = excluded.status,
  updated_at = now();

update site_consulting_directions
set owner_department = case
  when title like '%数据%' then '数据治理研究部'
  when title like '%城市%' then '智慧城市研究部'
  when title like '%产业%' then '产业数字化研究部'
  else '规划咨询部'
end
where owner_department = '';

update staff_roles
set permissions = array(
  select distinct unnest(permissions || array[
    'consulting_option:read',
    'consulting_option:write',
    'cooperation:read',
    'cooperation:update'
  ])
)
where code in ('super_admin', 'operations_admin');
