create or replace function generate_passport_no()
returns text
language plpgsql
volatile
as $$
declare
  letters constant text := 'ABCDEFGHJKLMNPQRSTUVWXYZ';
  candidate text;
  i integer;
begin
  loop
    candidate := '';
    for i in 1..5 loop
      candidate := candidate || substr(letters, 1 + floor(random() * length(letters))::integer, 1);
    end loop;
    candidate := candidate || '-' || lpad(floor(random() * 100000)::integer::text, 5, '0');
    exit when not exists (select 1 from users where passport_no = candidate);
  end loop;
  return candidate;
end;
$$;

alter table users
  add column if not exists passport_no text;

alter table users
  alter column passport_no set default generate_passport_no();

with numbered_users as (
  select id, row_number() over (order by created_at, id) as seq
  from users
  where passport_no is null or passport_no = ''
)
update users u
set passport_no = generate_passport_no()
from numbered_users
where u.id = numbered_users.id;

alter table users
  alter column passport_no set not null;

create unique index if not exists idx_users_passport_no
  on users(passport_no);

alter table uploaded_files
  alter column file_size type bigint;

create table if not exists expert_directions (
  id text primary key,
  group_name text not null,
  name text not null unique,
  sort_order integer not null default 0,
  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()
);

alter table expert_profiles
  add column if not exists passport_no text,
  add column if not exists contact_phone text not null default '',
  add column if not exists directions text[] not null default '{}',
  add column if not exists project_experience text not null default '',
  add column if not exists representative_achievements text not null default '',
  add column if not exists homepage_intro text not null default '',
  add column if not exists share_ratio integer not null default 80 check (share_ratio >= 0 and share_ratio <= 100),
  add column if not exists accepting_orders boolean not null default true,
  add column if not exists deleted_at timestamptz;

update expert_profiles
set directions = specialties
where cardinality(directions) = 0
  and cardinality(specialties) > 0;

create unique index if not exists idx_expert_profiles_user_active
  on expert_profiles(user_id)
  where user_id is not null and deleted_at is null;

create index if not exists idx_expert_profiles_directions
  on expert_profiles using gin(directions);

create table if not exists expert_applications (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references users(id) on delete cascade,
  passport_no text not null,
  name text not null,
  contact_phone text not null,
  organization text not null default '',
  title text not null default '',
  directions text[] not null default '{}',
  photo_file_id uuid references uploaded_files(id) on delete set null,
  photo_url text not null default '',
  project_experience text not null default '',
  representative_achievements text not null default '',
  homepage_intro text not null default '',
  attachment_file_ids uuid[] not null default '{}',
  status text not null default 'pending' check (status in ('pending', 'approved', 'rejected', 'withdrawn')),
  reject_reason text not null default '',
  review_price_amount integer not null default 0 check (review_price_amount >= 0),
  review_share_ratio integer not null default 80 check (review_share_ratio >= 0 and review_share_ratio <= 100),
  review_publish boolean not null default false,
  expert_profile_id uuid references expert_profiles(id) on delete set null,
  reviewed_by text not null default '',
  reviewed_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists idx_expert_applications_user_created
  on expert_applications(user_id, created_at desc);

create index if not exists idx_expert_applications_status_created
  on expert_applications(status, created_at desc);

create table if not exists expert_attachments (
  id uuid primary key default gen_random_uuid(),
  application_id uuid references expert_applications(id) on delete cascade,
  expert_id uuid references expert_profiles(id) on delete cascade,
  file_id uuid not null references uploaded_files(id) on delete restrict,
  kind text not null default 'material' check (kind in ('photo', 'resume', 'achievement', 'material')),
  sort_order integer not null default 0,
  created_at timestamptz not null default now()
);

create index if not exists idx_expert_attachments_application
  on expert_attachments(application_id, sort_order);

create index if not exists idx_expert_attachments_expert
  on expert_attachments(expert_id, sort_order);

create table if not exists expert_schedule_slots (
  id uuid primary key default gen_random_uuid(),
  expert_id uuid not null references expert_profiles(id) on delete cascade,
  starts_at timestamptz not null,
  ends_at timestamptz not null,
  status text not null default 'open' check (status in ('open', 'booked', 'closed')),
  note text not null default '',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  check (ends_at > starts_at),
  unique (expert_id, starts_at)
);

create index if not exists idx_expert_schedule_slots_expert_time
  on expert_schedule_slots(expert_id, starts_at);

create table if not exists expert_bookings (
  id uuid primary key default gen_random_uuid(),
  booking_no text not null unique,
  expert_id uuid not null references expert_profiles(id) on delete restrict,
  user_id uuid not null references users(id) on delete cascade,
  passport_no text not null,
  order_id uuid references orders(id) on delete set null,
  slot_id uuid references expert_schedule_slots(id) on delete set null,
  starts_at timestamptz not null,
  ends_at timestamptz not null,
  contact_name text not null default '',
  contact_phone text not null default '',
  background text not null default '',
  question text not null default '',
  material_file_ids uuid[] not null default '{}',
  status text not null default 'pending_expert' check (status in ('pending_payment', 'pending_expert', 'confirmed', 'rejected', 'cancelled', 'completed')),
  status_reason text not null default '',
  meeting_url text not null default '',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  completed_at timestamptz
);

create index if not exists idx_expert_bookings_expert_time
  on expert_bookings(expert_id, starts_at desc);

create index if not exists idx_expert_bookings_user_created
  on expert_bookings(user_id, created_at desc);

create index if not exists idx_expert_bookings_status_created
  on expert_bookings(status, created_at desc);

create table if not exists expert_booking_changes (
  id uuid primary key default gen_random_uuid(),
  booking_id uuid not null references expert_bookings(id) on delete cascade,
  requester_type text not null check (requester_type in ('user', 'expert', 'admin')),
  change_type text not null check (change_type in ('reschedule', 'cancel')),
  requested_slot_id uuid references expert_schedule_slots(id) on delete set null,
  requested_starts_at timestamptz,
  requested_ends_at timestamptz,
  reason text not null default '',
  status text not null default 'pending' check (status in ('pending', 'approved', 'rejected')),
  handled_by text not null default '',
  handled_note text not null default '',
  handled_at timestamptz,
  created_at timestamptz not null default now()
);

create index if not exists idx_expert_booking_changes_booking
  on expert_booking_changes(booking_id, created_at desc);

create table if not exists expert_withdrawals (
  id uuid primary key default gen_random_uuid(),
  expert_id uuid not null references expert_profiles(id) on delete restrict,
  user_id uuid not null references users(id) on delete cascade,
  passport_no text not null,
  amount integer not null check (amount > 0),
  payee_name text not null default '',
  bank_account text not null default '',
  bank_name text not null default '',
  status text not null default 'pending' check (status in ('pending', 'completed', 'rejected')),
  admin_note text not null default '',
  handled_by text not null default '',
  handled_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists idx_expert_withdrawals_status_created
  on expert_withdrawals(status, created_at desc);

create index if not exists idx_expert_withdrawals_expert_created
  on expert_withdrawals(expert_id, created_at desc);

create table if not exists site_agreements (
  id text primary key,
  title text not null,
  summary text not null default '',
  content text not null default '',
  status text not null default 'published' check (status in ('draft', 'published')),
  updated_by text not null default '',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

alter table settlement_records
  add column if not exists expert_id uuid references expert_profiles(id) on delete set null,
  add column if not exists beneficiary_user_id uuid references users(id) on delete set null,
  add column if not exists passport_no text not null default '';

create index if not exists idx_settlement_records_expert_status
  on settlement_records(expert_id, status, created_at desc);

insert into expert_directions (id, group_name, name, sort_order, status)
values
  ('policy-digital-economy', '政策与规划', '数字经济政策', 10, 'active'),
  ('policy-data-element', '政策与规划', '数据要素政策', 20, 'active'),
  ('policy-smart-city', '政策与规划', '智慧城市规划', 30, 'active'),
  ('policy-city-top-design', '政策与规划', '城市数字化顶层设计', 40, 'active'),
  ('policy-industry-planning', '政策与规划', '产业规划', 50, 'active'),
  ('policy-park-planning', '政策与规划', '园区规划', 60, 'active'),
  ('data-governance-system', '数据治理', '数据治理体系', 110, 'active'),
  ('data-asset-management', '数据治理', '数据资产管理', 120, 'active'),
  ('data-standards', '数据治理', '数据标准', 130, 'active'),
  ('data-quality', '数据治理', '数据质量', 140, 'active'),
  ('data-catalog-sharing', '数据治理', '数据目录与共享', 150, 'active'),
  ('data-public-operation', '数据治理', '公共数据授权运营', 160, 'active'),
  ('data-resource-accounting', '数据治理', '数据资源入表', 170, 'active'),
  ('ai-llm-application', 'AI 与智能化', '大模型应用', 210, 'active'),
  ('ai-government', 'AI 与智能化', '政务 AI', 220, 'active'),
  ('ai-enterprise', 'AI 与智能化', '企业 AI 落地', 230, 'active'),
  ('ai-agent', 'AI 与智能化', '智能体应用', 240, 'active'),
  ('ai-algorithm-governance', 'AI 与智能化', '算法治理', 250, 'active'),
  ('ai-product-planning', 'AI 与智能化', 'AI 产品规划', 260, 'active'),
  ('ai-vision', 'AI 与智能化', '计算机视觉应用', 270, 'active'),
  ('industry-internet', '产业数字化', '工业互联网', 310, 'active'),
  ('industry-smart-manufacturing', '产业数字化', '智能制造', 320, 'active'),
  ('industry-enterprise-transform', '产业数字化', '企业数字化转型', 330, 'active'),
  ('industry-park-operation', '产业数字化', '园区数字化运营', 340, 'active'),
  ('industry-supply-chain', '产业数字化', '供应链数字化', 350, 'active'),
  ('industry-culture-tourism', '产业数字化', '文旅数字化', 360, 'active'),
  ('industry-agriculture', '产业数字化', '农业数字化', 370, 'active'),
  ('security-data-compliance', '安全合规', '数据安全合规', 410, 'active'),
  ('security-network', '安全合规', '网络安全', 420, 'active'),
  ('security-privacy', '安全合规', '隐私保护', 430, 'active'),
  ('security-classified-protection', '安全合规', '等保合规', 440, 'active'),
  ('security-cross-border', '安全合规', '数据跨境', 450, 'active'),
  ('tech-data-middle-platform', '技术架构', '数据中台', 510, 'active'),
  ('tech-cloud-architecture', '技术架构', '云架构', 520, 'active'),
  ('tech-lakehouse', '技术架构', '湖仓一体', 530, 'active'),
  ('tech-iot-platform', '技术架构', '物联网平台', 540, 'active'),
  ('tech-system-architecture', '技术架构', '系统架构', 550, 'active'),
  ('tech-data-platform-ops', '技术架构', '数据平台运维', 560, 'active'),
  ('review-project-argument', '项目评审', '项目论证', 610, 'active'),
  ('review-solution', '项目评审', '方案评审', 620, 'active'),
  ('review-feasibility', '项目评审', '可研评审', 630, 'active'),
  ('review-acceptance', '项目评审', '验收评估', 640, 'active'),
  ('review-performance', '项目评审', '绩效评价', 650, 'active'),
  ('review-procurement-tech', '项目评审', '招采技术评审', 660, 'active'),
  ('training-topic-course', '培训研究', '专题授课', 710, 'active'),
  ('training-research', '培训研究', '课题研究', 720, 'active'),
  ('training-standard', '培训研究', '标准制定', 730, 'active'),
  ('training-case-study', '培训研究', '案例研究', 740, 'active'),
  ('training-achievement-transfer', '培训研究', '成果转化', 750, 'active'),
  ('training-resource-match', '培训研究', '产业资源对接', 760, 'active')
on conflict (id) do update set
  group_name = excluded.group_name,
  name = excluded.name,
  sort_order = excluded.sort_order,
  status = excluded.status,
  updated_at = now();

insert into site_agreements (id, title, summary, content, status)
values (
  'expert-cooperation-rules',
  '大连市大数据研究院专家合作规则',
  '专家申请、主页展示、预约履约、收益和合作管理规则。',
  '一、申请人应保证提交的身份、机构、职务、项目经验、代表成果和附件材料真实、准确、可核验。
二、平台会根据专家方向、项目经验、代表成果和合作匹配度进行审核，审核结果以平台通知和个人中心状态为准。
三、通过审核后，专家主页展示内容需符合真实、专业、审慎原则，不得夸大资质、承诺结果或发布误导性信息。
四、专家应按确认的服务时间履约。如需调整，应提前通过平台发起改期或取消申请。
五、专家在咨询、评审、课程或顾问服务中接触到的客户资料、项目资料和业务信息，应承担保密义务。
六、平台可根据服务质量、用户反馈、合规要求和合作情况调整展示、预约状态或暂停合作。
七、专家服务收益、提现和结算以平台记录和双方确认的结算规则为准。',
  'published'
)
on conflict (id) do update set
  title = excluded.title,
  summary = excluded.summary,
  content = excluded.content,
  status = excluded.status,
  updated_at = now();

insert into expert_schedule_slots (expert_id, starts_at, ends_at, status, note)
select
  e.id,
  date_trunc('day', now()) + make_interval(days => day_offset) + start_offset,
  date_trunc('day', now()) + make_interval(days => day_offset) + start_offset + interval '1 hour',
  'open',
  '系统初始化时段'
from expert_profiles e
cross join generate_series(0, 6) as day_offset
cross join (values (interval '10 hours'), (interval '14 hours')) as slots(start_offset)
where e.status = 'published'
  and e.deleted_at is null
on conflict (expert_id, starts_at) do nothing;
