表结构设计稿(不直接执行)
下载原文件-- 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. 本文件仅作结构评审依据,实际建表由平台内置能力完成。
