下载中心/表结构设计稿(不直接执行)

表结构设计稿(不直接执行)

下载原文件
-- P41 PCB 计价引擎表结构设计稿(PostgreSQL 方言,由 MySQL 原文改写,仅评审不执行)

create table if not exists pcb_quote_rule (
  id bigint generated by default as identity primary key,
  rule_name varchar(128) not null,
  material_type varchar(64) not null,
  layer_min smallint not null,
  layer_max smallint not null,
  thickness numeric(4,2) not null,
  copper_thick varchar(64) not null,
  surface_finish varchar(64) not null,
  min_line_width numeric(6,3),
  min_line_space numeric(6,3),
  is_impedance boolean not null default false,
  base_price_per_sqm numeric(18,4) not null default 0,
  min_order_price numeric(18,4) not null default 0,
  valid_start timestamptz,
  valid_end timestamptz,
  status smallint not null default 1 check (status in (0,1)),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create index if not exists idx_pcb_rule_material on pcb_quote_rule (material_type, layer_min, layer_max);

create table if not exists pcb_quote_surcharge (
  id bigint generated by default as identity primary key,
  surcharge_code varchar(64) not null unique,
  surcharge_name varchar(128) not null,
  calc_type smallint not null check (calc_type between 1 and 4),
  price_value numeric(18,4) not null default 0,
  condition_json jsonb not null default '{}'::jsonb,
  remark varchar(512) not null default '',
  status smallint not null default 1 check (status in (0,1)),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists pcb_quote_express (
  id bigint generated by default as identity primary key,
  layer_count smallint not null,
  base_hours integer not null,
  express_hours integer not null,
  extra_rate numeric(6,2) not null default 0,
  max_area numeric(12,4),
  status smallint not null default 1 check (status in (0,1)),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create index if not exists idx_pcb_express on pcb_quote_express (layer_count, express_hours);

create table if not exists pcb_quote_record (
  id bigint generated by default as identity primary key,
  quote_no varchar(64) not null unique,
  user_id uuid,
  gerber_file varchar(512) not null default '',
  gerber_parse_result jsonb,
  width numeric(8,3) not null,
  height numeric(8,3) not null,
  pnl_qty integer not null default 1,
  pcs_qty integer not null default 0,
  layer smallint not null,
  material_type varchar(64) not null,
  thickness numeric(4,2) not null,
  copper_thick varchar(64) not null,
  surface_finish varchar(64) not null,
  is_impedance boolean not null default false,
  is_flyprobe boolean not null default false,
  is_vcut boolean not null default false,
  is_goldfinger boolean not null default false,
  express_level varchar(32) not null default 'standard',
  base_amount numeric(18,4) not null default 0,
  surcharge_amount numeric(18,4) not null default 0,
  express_amount numeric(18,4) not null default 0,
  shipping_fee numeric(18,4) not null default 0,
  discount_amount numeric(18,4) not null default 0,
  total_amount numeric(18,4) not null default 0,
  lead_time_hour integer not null default 0,
  status smallint not null default 0 check (status between 0 and 3),
  expires_at timestamptz,
  remark text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
create index if not exists idx_pcb_quote_user on pcb_quote_record (user_id, status);

-- 说明:
-- 1. MySQL 的 tinyint 布尔语义统一改为 boolean,状态位改为 smallint + check。
-- 2. datetime 统一 timestamptz;ON UPDATE CURRENT_TIMESTAMP 由平台迁移中的本地 before update 触发器实现。
-- 3. 报价快照新增 expires_at,用于 7 天有效期控制。
-- 4. 本文件仅作结构评审依据,实际建表由平台内置能力完成。