create table if not exists data_product_details (
  product_id text primary key references products(id) on delete cascade,
  category text not null default '研究报告',
  file_format text not null default 'PDF',
  cover_url text not null default '',
  featured_position integer,
  sort_order integer not null default 10,
  status text not null default 'draft',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

insert into data_product_details (
  product_id, category, file_format, cover_url, featured_position, sort_order, status
)
select
  p.id,
  case
    when p.title like '%图谱%' then '资料包'
    when p.title like '%数据库%' or p.title like '%月报%' then '表格数据'
    else '研究报告'
  end,
  case
    when p.title like '%图谱%' then 'ZIP'
    when p.title like '%数据库%' or p.title like '%月报%' then 'Excel'
    else 'PDF'
  end,
  '',
  case when row_number() over (order by p.created_at) <= 3 then row_number() over (order by p.created_at) else null end,
  row_number() over (order by p.created_at) * 10,
  p.status
from products p
where p.type = 'data'
on conflict (product_id) do nothing;

create unique index if not exists idx_data_product_featured_position
  on data_product_details (featured_position)
  where featured_position is not null and status <> 'deleted';

alter table data_product_details
  drop constraint if exists data_product_details_category_check,
  drop constraint if exists data_product_details_file_format_check,
  drop constraint if exists data_product_details_featured_position_check,
  drop constraint if exists data_product_details_status_check;

alter table data_product_details
  add constraint data_product_details_category_check check (category in ('研究报告', '表格数据', '政策数据库', '资料包')),
  add constraint data_product_details_file_format_check check (file_format in ('PDF', 'Excel', 'CSV', 'ZIP')),
  add constraint data_product_details_featured_position_check check (featured_position is null or featured_position between 1 and 3),
  add constraint data_product_details_status_check check (status in ('draft', 'published', 'offline', 'deleted'));
