andyesfly
22d27a940d
- 重构勘察业务：移除SOP相关表，新增勘察明细表支持建筑/楼层/房间/照片管理 - 新增定时任务人性化频率配置，支持分钟/小时/天/周/月/自定义六种模式 - 新增频率预览接口，支持配置转换为cron表达式与执行时间预览 - 更新系统品牌名称为鑫潼智电，修改全局标题与登录页文案
1562 lines
78 KiB
PL/PgSQL
1562 lines
78 KiB
PL/PgSQL
-- ============================================================
|
||
-- 鑫潼智电系统 PostgreSQL 数据库构建脚本 V1.0
|
||
-- 基于《鑫潼智电系统需求分析报告 V1.0》+ 数据库设计文档
|
||
-- 数据库: PostgreSQL 15+
|
||
-- 字符集: UTF8
|
||
-- 表数量: 39 张（含 1 张按月分区表）
|
||
--
|
||
-- 用法:
|
||
-- 1. 先安装 pg_cron 扩展并配置 postgresql.conf（见文件末尾说明）
|
||
-- 2. psql -U postgres -d xintong -f db_init.sql
|
||
--
|
||
-- 特性:
|
||
-- - 幂等执行（DROP IF EXISTS + CREATE）
|
||
-- - updated_at 自动更新触发器
|
||
-- - 全部列 COMMENT 注释
|
||
-- - 逻辑隔离索引（customer_id）
|
||
-- - 用电采集按月 RANGE 分区
|
||
-- - 自动分区管理（纯 PG 函数 + pg_cron 定时调度）
|
||
-- - 月度汇总物化视图（历史数据分析）
|
||
-- - 初始角色/菜单/字典数据
|
||
-- ============================================================
|
||
|
||
BEGIN;
|
||
|
||
-- 扩展
|
||
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
|
||
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
||
|
||
-- ============================================================
|
||
-- updated_at 自动更新触发器函数
|
||
-- ============================================================
|
||
CREATE OR REPLACE FUNCTION trigger_set_updated_at()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
NEW.updated_at = NOW();
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- ============================================================
|
||
-- 一、系统管理域（7 张表）
|
||
-- ============================================================
|
||
|
||
-- 1. 系统用户表
|
||
DROP TABLE IF EXISTS sys_user CASCADE;
|
||
CREATE TABLE sys_user (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
username VARCHAR(64) NOT NULL UNIQUE,
|
||
password_hash VARCHAR(256) NOT NULL,
|
||
real_name VARCHAR(64) NOT NULL,
|
||
phone VARCHAR(20),
|
||
email VARCHAR(128),
|
||
status SMALLINT NOT NULL DEFAULT 1,
|
||
last_login_at TIMESTAMPTZ,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE sys_user IS '系统用户表（鑫潼内部人员：业务员、运营管理员等）';
|
||
COMMENT ON COLUMN sys_user.id IS '自增主键';
|
||
COMMENT ON COLUMN sys_user.username IS '登录用户名';
|
||
COMMENT ON COLUMN sys_user.password_hash IS '密码哈希值';
|
||
COMMENT ON COLUMN sys_user.real_name IS '真实姓名';
|
||
COMMENT ON COLUMN sys_user.phone IS '手机号';
|
||
COMMENT ON COLUMN sys_user.email IS '邮箱';
|
||
COMMENT ON COLUMN sys_user.status IS '状态：1启用 0禁用';
|
||
COMMENT ON COLUMN sys_user.last_login_at IS '最后登录时间';
|
||
COMMENT ON COLUMN sys_user.created_at IS '创建时间';
|
||
COMMENT ON COLUMN sys_user.updated_at IS '更新时间';
|
||
CREATE TRIGGER trg_sys_user_updated_at BEFORE UPDATE ON sys_user
|
||
FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
|
||
|
||
-- 2. 角色表
|
||
DROP TABLE IF EXISTS sys_role CASCADE;
|
||
CREATE TABLE sys_role (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
role_code VARCHAR(32) NOT NULL UNIQUE,
|
||
role_name VARCHAR(64) NOT NULL,
|
||
description TEXT,
|
||
sort_order INT NOT NULL DEFAULT 0,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE sys_role IS '角色表';
|
||
COMMENT ON COLUMN sys_role.id IS '自增主键';
|
||
COMMENT ON COLUMN sys_role.role_code IS '角色编码';
|
||
COMMENT ON COLUMN sys_role.role_name IS '角色名称';
|
||
COMMENT ON COLUMN sys_role.description IS '描述';
|
||
COMMENT ON COLUMN sys_role.sort_order IS '排序号';
|
||
COMMENT ON COLUMN sys_role.created_at IS '创建时间';
|
||
|
||
-- 3. 用户角色关联表
|
||
DROP TABLE IF EXISTS sys_user_role CASCADE;
|
||
CREATE TABLE sys_user_role (
|
||
user_id BIGINT NOT NULL REFERENCES sys_user(id) ON DELETE CASCADE,
|
||
role_id BIGINT NOT NULL REFERENCES sys_role(id) ON DELETE CASCADE,
|
||
PRIMARY KEY (user_id, role_id)
|
||
);
|
||
COMMENT ON TABLE sys_user_role IS '用户角色关联表';
|
||
COMMENT ON COLUMN sys_user_role.user_id IS '用户 ID（→ sys_user）';
|
||
COMMENT ON COLUMN sys_user_role.role_id IS '角色 ID（→ sys_role）';
|
||
|
||
-- 4. 菜单表
|
||
DROP TABLE IF EXISTS sys_menu CASCADE;
|
||
CREATE TABLE sys_menu (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
parent_id BIGINT REFERENCES sys_menu(id) ON DELETE SET NULL,
|
||
menu_name VARCHAR(64) NOT NULL,
|
||
menu_path VARCHAR(256),
|
||
component VARCHAR(256),
|
||
icon VARCHAR(64),
|
||
sort_order INT NOT NULL DEFAULT 0,
|
||
menu_type SMALLINT NOT NULL DEFAULT 1,
|
||
permission VARCHAR(128),
|
||
visible SMALLINT NOT NULL DEFAULT 1,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE sys_menu IS '菜单表（RBAC 菜单级权限）';
|
||
COMMENT ON COLUMN sys_menu.id IS '自增主键';
|
||
COMMENT ON COLUMN sys_menu.parent_id IS '父菜单 ID（自关联）';
|
||
COMMENT ON COLUMN sys_menu.menu_name IS '菜单名称';
|
||
COMMENT ON COLUMN sys_menu.menu_path IS '路由路径';
|
||
COMMENT ON COLUMN sys_menu.component IS '前端组件路径';
|
||
COMMENT ON COLUMN sys_menu.icon IS '图标';
|
||
COMMENT ON COLUMN sys_menu.sort_order IS '排序号';
|
||
COMMENT ON COLUMN sys_menu.menu_type IS '类型：1目录 2菜单 3按钮';
|
||
COMMENT ON COLUMN sys_menu.permission IS '权限标识，如 customer:list';
|
||
COMMENT ON COLUMN sys_menu.visible IS '是否可见：1是 0否';
|
||
COMMENT ON COLUMN sys_menu.created_at IS '创建时间';
|
||
|
||
-- 5. 角色菜单关联表
|
||
DROP TABLE IF EXISTS sys_role_menu CASCADE;
|
||
CREATE TABLE sys_role_menu (
|
||
role_id BIGINT NOT NULL REFERENCES sys_role(id) ON DELETE CASCADE,
|
||
menu_id BIGINT NOT NULL REFERENCES sys_menu(id) ON DELETE CASCADE,
|
||
PRIMARY KEY (role_id, menu_id)
|
||
);
|
||
COMMENT ON TABLE sys_role_menu IS '角色菜单关联表';
|
||
COMMENT ON COLUMN sys_role_menu.role_id IS '角色 ID（→ sys_role）';
|
||
COMMENT ON COLUMN sys_role_menu.menu_id IS '菜单 ID（→ sys_menu）';
|
||
|
||
-- 6. 数据字典表
|
||
DROP TABLE IF EXISTS sys_dict CASCADE;
|
||
CREATE TABLE sys_dict (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
dict_type VARCHAR(64) NOT NULL,
|
||
dict_label VARCHAR(128) NOT NULL,
|
||
dict_value VARCHAR(128) NOT NULL,
|
||
sort_order INT NOT NULL DEFAULT 0,
|
||
status SMALLINT NOT NULL DEFAULT 1,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE sys_dict IS '数据字典表';
|
||
COMMENT ON COLUMN sys_dict.id IS '自增主键';
|
||
COMMENT ON COLUMN sys_dict.dict_type IS '字典类型';
|
||
COMMENT ON COLUMN sys_dict.dict_label IS '字典标签';
|
||
COMMENT ON COLUMN sys_dict.dict_value IS '字典值';
|
||
COMMENT ON COLUMN sys_dict.sort_order IS '排序号';
|
||
COMMENT ON COLUMN sys_dict.status IS '状态：1启用 0禁用';
|
||
COMMENT ON COLUMN sys_dict.created_at IS '创建时间';
|
||
CREATE INDEX idx_sys_dict_type ON sys_dict (dict_type);
|
||
|
||
-- 7. 操作日志表
|
||
DROP TABLE IF EXISTS sys_operation_log CASCADE;
|
||
CREATE TABLE sys_operation_log (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
user_id BIGINT,
|
||
module VARCHAR(64),
|
||
action VARCHAR(64),
|
||
method VARCHAR(256),
|
||
params TEXT,
|
||
ip VARCHAR(64),
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE sys_operation_log IS '操作日志表';
|
||
COMMENT ON COLUMN sys_operation_log.id IS '自增主键';
|
||
COMMENT ON COLUMN sys_operation_log.user_id IS '操作用户 ID';
|
||
COMMENT ON COLUMN sys_operation_log.module IS '操作模块';
|
||
COMMENT ON COLUMN sys_operation_log.action IS '操作动作';
|
||
COMMENT ON COLUMN sys_operation_log.method IS '请求方法';
|
||
COMMENT ON COLUMN sys_operation_log.params IS '请求参数';
|
||
COMMENT ON COLUMN sys_operation_log.ip IS '操作 IP';
|
||
COMMENT ON COLUMN sys_operation_log.created_at IS '操作时间';
|
||
CREATE INDEX idx_oplog_user ON sys_operation_log (user_id);
|
||
CREATE INDEX idx_oplog_time ON sys_operation_log (created_at);
|
||
|
||
-- ============================================================
|
||
-- 二、客户域（7 张表）
|
||
-- ============================================================
|
||
|
||
-- 8. 客户信息表
|
||
DROP TABLE IF EXISTS customer CASCADE;
|
||
CREATE TABLE customer (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
customer_no VARCHAR(64) NOT NULL UNIQUE,
|
||
customer_name VARCHAR(128) NOT NULL,
|
||
province VARCHAR(64),
|
||
city VARCHAR(64),
|
||
district VARCHAR(64),
|
||
address VARCHAR(256),
|
||
contact_person VARCHAR(64),
|
||
contact_phone VARCHAR(20),
|
||
postal_code VARCHAR(10),
|
||
fax VARCHAR(20),
|
||
customer_level VARCHAR(32),
|
||
status SMALLINT NOT NULL DEFAULT 1,
|
||
salesman_id BIGINT REFERENCES sys_user(id),
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE customer IS '客户信息表';
|
||
COMMENT ON COLUMN customer.id IS '自增主键';
|
||
COMMENT ON COLUMN customer.customer_no IS '客户业务编号（KH+YYYYMMDD+3位序号）';
|
||
COMMENT ON COLUMN customer.customer_name IS '客户名称';
|
||
COMMENT ON COLUMN customer.province IS '省';
|
||
COMMENT ON COLUMN customer.city IS '市';
|
||
COMMENT ON COLUMN customer.district IS '区/县';
|
||
COMMENT ON COLUMN customer.address IS '地址';
|
||
COMMENT ON COLUMN customer.contact_person IS '联系人';
|
||
COMMENT ON COLUMN customer.contact_phone IS '电话';
|
||
COMMENT ON COLUMN customer.postal_code IS '邮编';
|
||
COMMENT ON COLUMN customer.fax IS '传真';
|
||
COMMENT ON COLUMN customer.customer_level IS '客户级别（学校/医院等）';
|
||
COMMENT ON COLUMN customer.status IS '状态：1意向 2签约 3运维中 4终止';
|
||
COMMENT ON COLUMN customer.salesman_id IS '负责业务员（→ sys_user.id）';
|
||
COMMENT ON COLUMN customer.created_at IS '创建时间';
|
||
COMMENT ON COLUMN customer.updated_at IS '更新时间';
|
||
CREATE INDEX idx_customer_name ON customer (customer_name);
|
||
CREATE INDEX idx_customer_salesman ON customer (salesman_id);
|
||
CREATE INDEX idx_customer_status ON customer (status);
|
||
CREATE INDEX idx_customer_region ON customer (province, city, district);
|
||
CREATE TRIGGER trg_customer_updated_at BEFORE UPDATE ON customer
|
||
FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
|
||
|
||
-- 9. 客户部门表
|
||
DROP TABLE IF EXISTS customer_department CASCADE;
|
||
CREATE TABLE customer_department (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
dept_name VARCHAR(128) NOT NULL,
|
||
contact_person VARCHAR(64),
|
||
contact_phone VARCHAR(20),
|
||
function_desc TEXT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE customer_department IS '客户部门信息表';
|
||
COMMENT ON COLUMN customer_department.id IS '自增主键';
|
||
COMMENT ON COLUMN customer_department.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN customer_department.dept_name IS '部门名称';
|
||
COMMENT ON COLUMN customer_department.contact_person IS '联系人';
|
||
COMMENT ON COLUMN customer_department.contact_phone IS '电话';
|
||
COMMENT ON COLUMN customer_department.function_desc IS '职能';
|
||
COMMENT ON COLUMN customer_department.created_at IS '创建时间';
|
||
COMMENT ON COLUMN customer_department.updated_at IS '更新时间';
|
||
CREATE INDEX idx_dept_customer ON customer_department (customer_id);
|
||
CREATE TRIGGER trg_customer_department_updated_at BEFORE UPDATE ON customer_department
|
||
FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
|
||
|
||
-- 10. 客户拜访信息表
|
||
DROP TABLE IF EXISTS customer_visit CASCADE;
|
||
CREATE TABLE customer_visit (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
visit_no VARCHAR(64) NOT NULL UNIQUE,
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
visit_person VARCHAR(64) NOT NULL,
|
||
visit_phone VARCHAR(20),
|
||
visit_content TEXT,
|
||
visit_time DATE,
|
||
salesman_id BIGINT,
|
||
will_plan SMALLINT DEFAULT 0,
|
||
visit_type SMALLINT DEFAULT 1,
|
||
visit_mode SMALLINT DEFAULT 1,
|
||
priority SMALLINT DEFAULT 3,
|
||
subject VARCHAR(128),
|
||
plan_time TIMESTAMPTZ,
|
||
plan_end_time TIMESTAMPTZ,
|
||
checkin_time TIMESTAMPTZ,
|
||
checkin_status SMALLINT DEFAULT 0,
|
||
checkin_lng DECIMAL(10,6),
|
||
checkin_lat DECIMAL(10,6),
|
||
space_desc VARCHAR(128),
|
||
intent_level SMALLINT,
|
||
intent_desc VARCHAR(64),
|
||
demands VARCHAR(512),
|
||
companion VARCHAR(128),
|
||
reception_person VARCHAR(64),
|
||
reception_title VARCHAR(64),
|
||
reception_phone VARCHAR(20),
|
||
result_rating VARCHAR(64),
|
||
status SMALLINT DEFAULT 0,
|
||
next_visit_time DATE,
|
||
next_action VARCHAR(128),
|
||
photos VARCHAR(1024),
|
||
survey_order_id BIGINT,
|
||
project_no VARCHAR(64),
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE customer_visit IS '客户拜访信息表';
|
||
COMMENT ON COLUMN customer_visit.id IS '自增主键';
|
||
COMMENT ON COLUMN customer_visit.visit_no IS '拜访单号';
|
||
COMMENT ON COLUMN customer_visit.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN customer_visit.visit_person IS '拜访人';
|
||
COMMENT ON COLUMN customer_visit.visit_phone IS '拜访人电话';
|
||
COMMENT ON COLUMN customer_visit.visit_content IS '拜访内容/沟通纪要';
|
||
COMMENT ON COLUMN customer_visit.visit_time IS '拜访时间';
|
||
COMMENT ON COLUMN customer_visit.salesman_id IS '业务员';
|
||
COMMENT ON COLUMN customer_visit.will_plan IS '是否做方案：0不同意 1同意';
|
||
COMMENT ON COLUMN customer_visit.visit_type IS '拜访类型:1首访摸排 2方案沟通 3商务二访 4维保回访 5应急巡检';
|
||
COMMENT ON COLUMN customer_visit.visit_mode IS '拜访方式:1现场拜访 2商务拜访 3电话沟通 4线上会议';
|
||
COMMENT ON COLUMN customer_visit.priority IS '优先级:1特急P1 2加急P2 3常规P3';
|
||
COMMENT ON COLUMN customer_visit.subject IS '拜访主题/目的';
|
||
COMMENT ON COLUMN customer_visit.plan_time IS '计划拜访开始时间';
|
||
COMMENT ON COLUMN customer_visit.plan_end_time IS '计划拜访结束时间';
|
||
COMMENT ON COLUMN customer_visit.checkin_time IS '到场签到时间';
|
||
COMMENT ON COLUMN customer_visit.checkin_status IS '签到状态:0未签到 1已签到';
|
||
COMMENT ON COLUMN customer_visit.checkin_lng IS '签到经度';
|
||
COMMENT ON COLUMN customer_visit.checkin_lat IS '签到纬度';
|
||
COMMENT ON COLUMN customer_visit.space_desc IS '客户空间性质/场所描述';
|
||
COMMENT ON COLUMN customer_visit.intent_level IS '意向评级:1=A高意向 2=B中意向 3=C低意向';
|
||
COMMENT ON COLUMN customer_visit.intent_desc IS '现场意向说明';
|
||
COMMENT ON COLUMN customer_visit.demands IS '诉求痛点(逗号分隔)';
|
||
COMMENT ON COLUMN customer_visit.companion IS '陪同人员';
|
||
COMMENT ON COLUMN customer_visit.reception_person IS '客户接待人';
|
||
COMMENT ON COLUMN customer_visit.reception_title IS '接待人职务';
|
||
COMMENT ON COLUMN customer_visit.reception_phone IS '接待人联系方式';
|
||
COMMENT ON COLUMN customer_visit.result_rating IS '现场成果评级/结论';
|
||
COMMENT ON COLUMN customer_visit.status IS '状态:0待执行 1已完成待复核 2取证待技术复核 3已立项转勘察 4已归档 5已取消';
|
||
COMMENT ON COLUMN customer_visit.next_visit_time IS '预计下次回访日期';
|
||
COMMENT ON COLUMN customer_visit.next_action IS '下一步行动目标';
|
||
COMMENT ON COLUMN customer_visit.photos IS '现场照片对象key(JSON数组字符串)';
|
||
COMMENT ON COLUMN customer_visit.survey_order_id IS '关联勘察单ID';
|
||
COMMENT ON COLUMN customer_visit.project_no IS '关联工程编号';
|
||
COMMENT ON COLUMN customer_visit.created_at IS '创建时间';
|
||
COMMENT ON COLUMN customer_visit.updated_at IS '更新时间';
|
||
CREATE INDEX idx_visit_customer ON customer_visit (customer_id);
|
||
CREATE INDEX idx_visit_salesman ON customer_visit (salesman_id);
|
||
CREATE INDEX idx_visit_status ON customer_visit (status);
|
||
CREATE INDEX idx_visit_plan_time ON customer_visit (plan_time);
|
||
CREATE TRIGGER trg_customer_visit_updated_at BEFORE UPDATE ON customer_visit
|
||
FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
|
||
|
||
-- 11. 客户智电改造建筑信息表
|
||
DROP TABLE IF EXISTS customer_building CASCADE;
|
||
CREATE TABLE customer_building (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
building_name VARCHAR(128) NOT NULL,
|
||
basic_info TEXT,
|
||
purpose VARCHAR(128),
|
||
floor_count INT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE customer_building IS '客户智电改造建筑信息表';
|
||
COMMENT ON COLUMN customer_building.id IS '自增主键';
|
||
COMMENT ON COLUMN customer_building.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN customer_building.building_name IS '建筑名称';
|
||
COMMENT ON COLUMN customer_building.basic_info IS '基本情况';
|
||
COMMENT ON COLUMN customer_building.purpose IS '用途';
|
||
COMMENT ON COLUMN customer_building.floor_count IS '层数';
|
||
COMMENT ON COLUMN customer_building.created_at IS '创建时间';
|
||
CREATE INDEX idx_building_customer ON customer_building (customer_id);
|
||
|
||
-- 12. 客户建筑房间信息表
|
||
DROP TABLE IF EXISTS customer_room CASCADE;
|
||
CREATE TABLE customer_room (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
room_code VARCHAR(64) NOT NULL,
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
room_name VARCHAR(128) NOT NULL,
|
||
building_id BIGINT NOT NULL REFERENCES customer_building(id),
|
||
floor_no VARCHAR(16),
|
||
room_purpose VARCHAR(128),
|
||
dept_id BIGINT REFERENCES customer_department(id),
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE customer_room IS '客户建筑房间信息表';
|
||
COMMENT ON COLUMN customer_room.id IS '自增主键';
|
||
COMMENT ON COLUMN customer_room.room_code IS '房间编码';
|
||
COMMENT ON COLUMN customer_room.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN customer_room.room_name IS '房间名称';
|
||
COMMENT ON COLUMN customer_room.building_id IS '所属建筑 ID（→ customer_building）';
|
||
COMMENT ON COLUMN customer_room.floor_no IS '所在楼层';
|
||
COMMENT ON COLUMN customer_room.room_purpose IS '房间用途';
|
||
COMMENT ON COLUMN customer_room.dept_id IS '房间所属客户部门（→ customer_department）';
|
||
COMMENT ON COLUMN customer_room.created_at IS '创建时间';
|
||
CREATE INDEX idx_room_customer ON customer_room (customer_id);
|
||
CREATE INDEX idx_room_building ON customer_room (building_id);
|
||
|
||
-- 12.1 客户用电回路表（空间拓扑末端节点：建筑 → 楼层 → 房间 → 回路）
|
||
DROP TABLE IF EXISTS customer_circuit CASCADE;
|
||
CREATE TABLE customer_circuit (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
building_id BIGINT NOT NULL REFERENCES customer_building(id),
|
||
room_id BIGINT REFERENCES customer_room(id),
|
||
floor_no VARCHAR(16),
|
||
circuit_code VARCHAR(64) NOT NULL UNIQUE,
|
||
circuit_name VARCHAR(128) NOT NULL,
|
||
loop_spec VARCHAR(128),
|
||
load_type VARCHAR(32),
|
||
rated_voltage VARCHAR(32),
|
||
rated_current VARCHAR(32),
|
||
rated_power NUMERIC(10,2),
|
||
status SMALLINT NOT NULL DEFAULT 1,
|
||
remark TEXT,
|
||
sort_order INT NOT NULL DEFAULT 0,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE customer_circuit IS '客户用电回路表（空间拓扑末端节点）';
|
||
COMMENT ON COLUMN customer_circuit.id IS '自增主键';
|
||
COMMENT ON COLUMN customer_circuit.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN customer_circuit.building_id IS '所属建筑 ID（→ customer_building）';
|
||
COMMENT ON COLUMN customer_circuit.room_id IS '所属房间 ID（→ customer_room，可空）';
|
||
COMMENT ON COLUMN customer_circuit.floor_no IS '所在楼层';
|
||
COMMENT ON COLUMN customer_circuit.circuit_code IS '回路编号（全局唯一）';
|
||
COMMENT ON COLUMN customer_circuit.circuit_name IS '回路名称';
|
||
COMMENT ON COLUMN customer_circuit.loop_spec IS '回路规格';
|
||
COMMENT ON COLUMN customer_circuit.load_type IS '负荷类型';
|
||
COMMENT ON COLUMN customer_circuit.rated_voltage IS '额定电压（配置值）';
|
||
COMMENT ON COLUMN customer_circuit.rated_current IS '额定电流（配置值）';
|
||
COMMENT ON COLUMN customer_circuit.rated_power IS '额定功率 kW（配置值）';
|
||
COMMENT ON COLUMN customer_circuit.status IS '状态：1 正常 / 0 停用';
|
||
COMMENT ON COLUMN customer_circuit.remark IS '备注';
|
||
COMMENT ON COLUMN customer_circuit.sort_order IS '排序值';
|
||
COMMENT ON COLUMN customer_circuit.created_at IS '创建时间';
|
||
CREATE INDEX idx_circuit_customer ON customer_circuit (customer_id);
|
||
CREATE INDEX idx_circuit_building ON customer_circuit (building_id);
|
||
CREATE INDEX idx_circuit_room ON customer_circuit (room_id);
|
||
|
||
-- 13. 客户设备信息表（配电间、配电井、配电箱）
|
||
DROP TABLE IF EXISTS customer_equipment CASCADE;
|
||
CREATE TABLE customer_equipment (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
equip_code VARCHAR(64) NOT NULL UNIQUE,
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
equip_name VARCHAR(128) NOT NULL,
|
||
equip_type VARCHAR(32) NOT NULL,
|
||
building_id BIGINT REFERENCES customer_building(id),
|
||
floor_no VARCHAR(16),
|
||
location VARCHAR(256),
|
||
description TEXT,
|
||
remark TEXT,
|
||
photo_url VARCHAR(512),
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE customer_equipment IS '客户智电改造设备信息表（配电间/井/箱）';
|
||
COMMENT ON COLUMN customer_equipment.id IS '自增主键';
|
||
COMMENT ON COLUMN customer_equipment.equip_code IS '客户设备编码（唯一）';
|
||
COMMENT ON COLUMN customer_equipment.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN customer_equipment.equip_name IS '客户设备名称';
|
||
COMMENT ON COLUMN customer_equipment.equip_type IS '设备类型：配电间/配电井/配电箱';
|
||
COMMENT ON COLUMN customer_equipment.building_id IS '所属建筑 ID（→ customer_building）';
|
||
COMMENT ON COLUMN customer_equipment.floor_no IS '所在楼层';
|
||
COMMENT ON COLUMN customer_equipment.location IS '所在位置';
|
||
COMMENT ON COLUMN customer_equipment.description IS '设备描述';
|
||
COMMENT ON COLUMN customer_equipment.remark IS '备注';
|
||
COMMENT ON COLUMN customer_equipment.photo_url IS '未改造前设备照片';
|
||
COMMENT ON COLUMN customer_equipment.created_at IS '创建时间';
|
||
CREATE INDEX idx_equip_customer ON customer_equipment (customer_id);
|
||
CREATE INDEX idx_equip_building ON customer_equipment (building_id);
|
||
|
||
-- 14. 客户访问账号表
|
||
DROP TABLE IF EXISTS customer_account CASCADE;
|
||
CREATE TABLE customer_account (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
phone VARCHAR(20) NOT NULL,
|
||
password_hash VARCHAR(256) NOT NULL,
|
||
wx_openid VARCHAR(128),
|
||
status SMALLINT NOT NULL DEFAULT 1,
|
||
last_login_at TIMESTAMPTZ,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE customer_account IS '客户访问账号表（小程序登录）';
|
||
COMMENT ON COLUMN customer_account.id IS '自增主键';
|
||
COMMENT ON COLUMN customer_account.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN customer_account.phone IS '登录电话（唯一）';
|
||
COMMENT ON COLUMN customer_account.password_hash IS '密码哈希';
|
||
COMMENT ON COLUMN customer_account.wx_openid IS '微信 openid';
|
||
COMMENT ON COLUMN customer_account.status IS '状态：1启用 0禁用';
|
||
COMMENT ON COLUMN customer_account.last_login_at IS '最后登录时间';
|
||
COMMENT ON COLUMN customer_account.created_at IS '创建时间';
|
||
COMMENT ON COLUMN customer_account.updated_at IS '更新时间';
|
||
CREATE INDEX idx_account_customer ON customer_account (customer_id);
|
||
CREATE UNIQUE INDEX uk_account_phone ON customer_account (phone);
|
||
CREATE TRIGGER trg_customer_account_updated_at BEFORE UPDATE ON customer_account
|
||
FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
|
||
|
||
-- ============================================================
|
||
-- 三、智电设备域（1 张表）
|
||
-- ============================================================
|
||
|
||
-- 15. 智电设备型号表（产品目录）
|
||
DROP TABLE IF EXISTS smart_device CASCADE;
|
||
CREATE TABLE smart_device (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
device_model VARCHAR(64) NOT NULL,
|
||
device_name VARCHAR(128) NOT NULL,
|
||
device_spec VARCHAR(256),
|
||
device_type VARCHAR(32) NOT NULL,
|
||
device_function TEXT,
|
||
supplier VARCHAR(128),
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE smart_device IS '鑫潼智电设备信息表（产品目录）';
|
||
COMMENT ON COLUMN smart_device.id IS '自增主键';
|
||
COMMENT ON COLUMN smart_device.device_model IS '智电设备型号';
|
||
COMMENT ON COLUMN smart_device.device_name IS '智电设备名称';
|
||
COMMENT ON COLUMN smart_device.device_spec IS '智电设备规格';
|
||
COMMENT ON COLUMN smart_device.device_type IS '设备类型：网关/采集器/探测器等';
|
||
COMMENT ON COLUMN smart_device.device_function IS '智电设备功能';
|
||
COMMENT ON COLUMN smart_device.supplier IS '供应商';
|
||
COMMENT ON COLUMN smart_device.created_at IS '创建时间';
|
||
|
||
-- ============================================================
|
||
-- 四、项目方案与合同域（3 张表）
|
||
-- ============================================================
|
||
|
||
-- 16. 智电改造方案表
|
||
DROP TABLE IF EXISTS project_plan CASCADE;
|
||
CREATE TABLE project_plan (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
plan_no VARCHAR(64) NOT NULL UNIQUE,
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
registrar VARCHAR(64),
|
||
register_time DATE,
|
||
plan_desc TEXT,
|
||
plan_file_url VARCHAR(512),
|
||
status SMALLINT NOT NULL DEFAULT 0,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE project_plan IS '智电改造方案信息表';
|
||
COMMENT ON COLUMN project_plan.id IS '自增主键';
|
||
COMMENT ON COLUMN project_plan.plan_no IS '方案单号（唯一）';
|
||
COMMENT ON COLUMN project_plan.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN project_plan.registrar IS '方案登记人（业务员）';
|
||
COMMENT ON COLUMN project_plan.register_time IS '方案登记时间';
|
||
COMMENT ON COLUMN project_plan.plan_desc IS '方案说明';
|
||
COMMENT ON COLUMN project_plan.plan_file_url IS '方案最终文件';
|
||
COMMENT ON COLUMN project_plan.status IS '状态：0草稿 1已确认';
|
||
COMMENT ON COLUMN project_plan.created_at IS '创建时间';
|
||
CREATE INDEX idx_plan_customer ON project_plan (customer_id);
|
||
|
||
-- 17. 项目方案智电设备明细表
|
||
DROP TABLE IF EXISTS project_plan_detail CASCADE;
|
||
CREATE TABLE project_plan_detail (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
plan_id BIGINT NOT NULL REFERENCES project_plan(id) ON DELETE CASCADE,
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
equip_id BIGINT REFERENCES customer_equipment(id),
|
||
smart_device_id BIGINT REFERENCES smart_device(id),
|
||
quantity NUMERIC(10,2) NOT NULL DEFAULT 1,
|
||
remark TEXT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE project_plan_detail IS '项目方案智电设备明细信息表';
|
||
COMMENT ON COLUMN project_plan_detail.id IS '自增主键';
|
||
COMMENT ON COLUMN project_plan_detail.plan_id IS '方案 ID（→ project_plan）';
|
||
COMMENT ON COLUMN project_plan_detail.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN project_plan_detail.equip_id IS '客户设备 ID（→ customer_equipment）';
|
||
COMMENT ON COLUMN project_plan_detail.smart_device_id IS '智电设备型号 ID（→ smart_device）';
|
||
COMMENT ON COLUMN project_plan_detail.quantity IS '数量';
|
||
COMMENT ON COLUMN project_plan_detail.remark IS '备注';
|
||
COMMENT ON COLUMN project_plan_detail.created_at IS '创建时间';
|
||
CREATE INDEX idx_pd_plan ON project_plan_detail (plan_id);
|
||
CREATE INDEX idx_pd_customer ON project_plan_detail (customer_id);
|
||
|
||
-- 18. 智电项目合同表
|
||
DROP TABLE IF EXISTS contract CASCADE;
|
||
CREATE TABLE contract (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
contract_no VARCHAR(64) NOT NULL UNIQUE,
|
||
xt_contract_no VARCHAR(64),
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
contract_amount NUMERIC(14,2),
|
||
pay_cycle VARCHAR(32),
|
||
salesman_id BIGINT,
|
||
sign_date DATE,
|
||
contract_desc TEXT,
|
||
start_date DATE,
|
||
end_date DATE,
|
||
plan_id BIGINT REFERENCES project_plan(id),
|
||
contract_file_url VARCHAR(512),
|
||
status SMALLINT NOT NULL DEFAULT 1,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE contract IS '智电项目合同信息表';
|
||
COMMENT ON COLUMN contract.id IS '自增主键';
|
||
COMMENT ON COLUMN contract.contract_no IS '合同单号（唯一）';
|
||
COMMENT ON COLUMN contract.xt_contract_no IS '鑫潼合同号（XT+年份+序号）';
|
||
COMMENT ON COLUMN contract.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN contract.contract_amount IS '合同金额';
|
||
COMMENT ON COLUMN contract.pay_cycle IS '支付周期：按月/按年';
|
||
COMMENT ON COLUMN contract.salesman_id IS '业务员';
|
||
COMMENT ON COLUMN contract.sign_date IS '签订日期';
|
||
COMMENT ON COLUMN contract.contract_desc IS '合同说明';
|
||
COMMENT ON COLUMN contract.start_date IS '开始日期';
|
||
COMMENT ON COLUMN contract.end_date IS '结束日期';
|
||
COMMENT ON COLUMN contract.plan_id IS '方案 ID（→ project_plan）';
|
||
COMMENT ON COLUMN contract.contract_file_url IS '签字盖章电子合同';
|
||
COMMENT ON COLUMN contract.status IS '状态：1有效 0终止';
|
||
COMMENT ON COLUMN contract.created_at IS '创建时间';
|
||
CREATE INDEX idx_contract_customer ON contract (customer_id);
|
||
CREATE INDEX idx_contract_xt ON contract (xt_contract_no);
|
||
|
||
-- ============================================================
|
||
-- 五、线路与设备关联域（2 张表，ICC 同步）
|
||
-- ============================================================
|
||
|
||
-- 19. 智电改造线路表（ICC 同步 + 业务分配）
|
||
DROP TABLE IF EXISTS electric_line CASCADE;
|
||
CREATE TABLE electric_line (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
line_code VARCHAR(64) NOT NULL UNIQUE,
|
||
equip_id BIGINT NOT NULL REFERENCES customer_equipment(id),
|
||
icc_device_code VARCHAR(128),
|
||
line_name VARCHAR(128),
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
building_id BIGINT REFERENCES customer_building(id),
|
||
floor_no VARCHAR(16),
|
||
location VARCHAR(256),
|
||
status SMALLINT NOT NULL DEFAULT 1,
|
||
synced_at TIMESTAMPTZ,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE electric_line IS '智电改造线路信息表（ICC 同步）';
|
||
COMMENT ON COLUMN electric_line.id IS '自增主键';
|
||
COMMENT ON COLUMN electric_line.line_code IS '线路编码（唯一）';
|
||
COMMENT ON COLUMN electric_line.equip_id IS '客户设备 ID（→ customer_equipment）';
|
||
COMMENT ON COLUMN electric_line.icc_device_code IS 'ICC 智电设备编号';
|
||
COMMENT ON COLUMN electric_line.line_name IS '线路名称（ICC 同步）';
|
||
COMMENT ON COLUMN electric_line.customer_id IS '客户 ID（逻辑隔离键）';
|
||
COMMENT ON COLUMN electric_line.building_id IS '所属建筑 ID（→ customer_building）';
|
||
COMMENT ON COLUMN electric_line.floor_no IS '所在楼层';
|
||
COMMENT ON COLUMN electric_line.location IS '所在位置';
|
||
COMMENT ON COLUMN electric_line.status IS '状态：1在线 0离线';
|
||
COMMENT ON COLUMN electric_line.synced_at IS 'ICC 同步时间';
|
||
COMMENT ON COLUMN electric_line.created_at IS '创建时间';
|
||
CREATE INDEX idx_line_customer ON electric_line (customer_id);
|
||
CREATE INDEX idx_line_equip ON electric_line (equip_id);
|
||
CREATE INDEX idx_line_icc ON electric_line (icc_device_code);
|
||
|
||
-- 20. 线路所属房间关联表
|
||
DROP TABLE IF EXISTS line_room CASCADE;
|
||
CREATE TABLE line_room (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
line_id BIGINT NOT NULL REFERENCES electric_line(id) ON DELETE CASCADE,
|
||
room_id BIGINT NOT NULL REFERENCES customer_room(id),
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE line_room IS '线路所属房间关联表';
|
||
COMMENT ON COLUMN line_room.id IS '自增主键';
|
||
COMMENT ON COLUMN line_room.line_id IS '线路 ID（→ electric_line）';
|
||
COMMENT ON COLUMN line_room.room_id IS '房间 ID（→ customer_room）';
|
||
COMMENT ON COLUMN line_room.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN line_room.created_at IS '创建时间';
|
||
CREATE INDEX idx_lr_line ON line_room (line_id);
|
||
CREATE INDEX idx_lr_room ON line_room (room_id);
|
||
CREATE INDEX idx_lr_customer ON line_room (customer_id);
|
||
|
||
-- ============================================================
|
||
-- 六、派单域（6 张表）
|
||
-- ============================================================
|
||
|
||
-- 21. 勘察派单表
|
||
DROP TABLE IF EXISTS survey_order CASCADE;
|
||
CREATE TABLE survey_order (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
survey_no VARCHAR(64) NOT NULL UNIQUE,
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
surveyor VARCHAR(64),
|
||
worker_id BIGINT,
|
||
survey_time TIMESTAMPTZ,
|
||
priority SMALLINT DEFAULT 3,
|
||
task_title VARCHAR(128),
|
||
site_location VARCHAR(255),
|
||
source VARCHAR(32) DEFAULT 'ledger',
|
||
loop_spec VARCHAR(128),
|
||
loop_count INTEGER,
|
||
progress_done INTEGER DEFAULT 0,
|
||
progress_total INTEGER,
|
||
drawing_count INTEGER DEFAULT 0,
|
||
salesman_id BIGINT,
|
||
tech_contact VARCHAR(128),
|
||
plan_start TIMESTAMPTZ,
|
||
plan_end TIMESTAMPTZ,
|
||
expect_minutes INTEGER,
|
||
expect_arrival_at TIMESTAMPTZ,
|
||
sla_level VARCHAR(32),
|
||
urge_count INTEGER DEFAULT 0,
|
||
last_urged_at TIMESTAMPTZ,
|
||
is_draft SMALLINT NOT NULL DEFAULT 0,
|
||
estimated_fee NUMERIC(10,2),
|
||
actual_fee NUMERIC(10,2),
|
||
photos VARCHAR(1024),
|
||
review_remark TEXT,
|
||
reviewed_at TIMESTAMPTZ,
|
||
cancel_reason VARCHAR(255),
|
||
remark TEXT,
|
||
status SMALLINT NOT NULL DEFAULT 0,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE survey_order IS '客户勘察派单信息表';
|
||
COMMENT ON COLUMN survey_order.id IS '自增主键';
|
||
COMMENT ON COLUMN survey_order.survey_no IS '勘察单号（唯一）';
|
||
COMMENT ON COLUMN survey_order.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN survey_order.surveyor IS '勘察人（共享电工）';
|
||
COMMENT ON COLUMN survey_order.worker_id IS '共享电工 ID';
|
||
COMMENT ON COLUMN survey_order.survey_time IS '勘察时间';
|
||
COMMENT ON COLUMN survey_order.priority IS '优先级:1特急P1 2加急P2 3常规P3';
|
||
COMMENT ON COLUMN survey_order.task_title IS '勘察任务标题';
|
||
COMMENT ON COLUMN survey_order.site_location IS '具体勘察物理区域';
|
||
COMMENT ON COLUMN survey_order.source IS '工单来源:ledger台账派工 visit拜访转勘察 customer客户发起';
|
||
COMMENT ON COLUMN survey_order.loop_spec IS '主进线规约与断路器容量';
|
||
COMMENT ON COLUMN survey_order.loop_count IS '预估分支测绘回路数';
|
||
COMMENT ON COLUMN survey_order.progress_done IS '已完成测绘回路数';
|
||
COMMENT ON COLUMN survey_order.progress_total IS '应测绘回路总数';
|
||
COMMENT ON COLUMN survey_order.drawing_count IS '待技术复核图纸数';
|
||
COMMENT ON COLUMN survey_order.salesman_id IS '责任业务员ID（→ sys_user）';
|
||
COMMENT ON COLUMN survey_order.tech_contact IS '现场技术联系人';
|
||
COMMENT ON COLUMN survey_order.plan_start IS '计划到场窗口开始';
|
||
COMMENT ON COLUMN survey_order.plan_end IS '计划到场窗口结束';
|
||
COMMENT ON COLUMN survey_order.expect_minutes IS '要求接单后到场时限（分钟）';
|
||
COMMENT ON COLUMN survey_order.expect_arrival_at IS '期望到场时限（指派时按 expect_minutes 推算）';
|
||
COMMENT ON COLUMN survey_order.sla_level IS '履约质保要求';
|
||
COMMENT ON COLUMN survey_order.urge_count IS '催办次数';
|
||
COMMENT ON COLUMN survey_order.last_urged_at IS '最近催办时间';
|
||
COMMENT ON COLUMN survey_order.is_draft IS '是否草稿:0正式工单 1草稿';
|
||
COMMENT ON COLUMN survey_order.estimated_fee IS '预估劳务费（基础出场费+回路补贴）';
|
||
COMMENT ON COLUMN survey_order.actual_fee IS '实际结算劳务费';
|
||
COMMENT ON COLUMN survey_order.photos IS '现场取证照片对象key(JSON数组字符串)';
|
||
COMMENT ON COLUMN survey_order.review_remark IS '技术复核意见';
|
||
COMMENT ON COLUMN survey_order.reviewed_at IS '技术复核时间';
|
||
COMMENT ON COLUMN survey_order.cancel_reason IS '取消原因';
|
||
COMMENT ON COLUMN survey_order.remark IS '备注';
|
||
COMMENT ON COLUMN survey_order.status IS '状态:0待指派 1现场勘察中 2待技术复核 3已归档 4预留 5已取消';
|
||
COMMENT ON COLUMN survey_order.created_at IS '创建时间';
|
||
COMMENT ON COLUMN survey_order.updated_at IS '更新时间';
|
||
CREATE INDEX idx_survey_customer ON survey_order (customer_id);
|
||
CREATE INDEX idx_survey_worker ON survey_order (worker_id);
|
||
CREATE INDEX idx_survey_status ON survey_order (status);
|
||
CREATE INDEX idx_survey_priority ON survey_order (priority);
|
||
CREATE INDEX idx_survey_salesman ON survey_order (salesman_id);
|
||
CREATE INDEX idx_survey_plan_start ON survey_order (plan_start);
|
||
CREATE TRIGGER trg_survey_order_updated_at BEFORE UPDATE ON survey_order
|
||
FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
|
||
|
||
-- 21.1 勘察工单明细表（建筑/楼层/房间 + 现场照片）
|
||
DROP TABLE IF EXISTS survey_detail CASCADE;
|
||
CREATE TABLE survey_detail (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
survey_order_id BIGINT NOT NULL REFERENCES survey_order(id) ON DELETE CASCADE,
|
||
building_id BIGINT,
|
||
building_name VARCHAR(128),
|
||
floor_no VARCHAR(16),
|
||
room_id BIGINT,
|
||
room_name VARCHAR(128),
|
||
photos TEXT,
|
||
remark VARCHAR(255),
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE survey_detail IS '勘察工单明细表（建筑/楼层/房间 + 现场照片）';
|
||
COMMENT ON COLUMN survey_detail.id IS '自增主键';
|
||
COMMENT ON COLUMN survey_detail.survey_order_id IS '勘察工单ID（→ survey_order）';
|
||
COMMENT ON COLUMN survey_detail.building_id IS '建筑ID（→ customer_building）';
|
||
COMMENT ON COLUMN survey_detail.building_name IS '建筑名称（冗余，客户拓扑变更后仍可读）';
|
||
COMMENT ON COLUMN survey_detail.floor_no IS '所在楼层：正数为地上、负数为地下，如 1 / -1';
|
||
COMMENT ON COLUMN survey_detail.room_id IS '房间ID（→ customer_room，可空）';
|
||
COMMENT ON COLUMN survey_detail.room_name IS '房间名称（冗余，可空）';
|
||
COMMENT ON COLUMN survey_detail.photos IS '现场照片对象key(JSON数组字符串)';
|
||
COMMENT ON COLUMN survey_detail.remark IS '备注';
|
||
COMMENT ON COLUMN survey_detail.created_at IS '创建时间';
|
||
COMMENT ON COLUMN survey_detail.updated_at IS '更新时间';
|
||
CREATE INDEX idx_survey_detail_order ON survey_detail (survey_order_id);
|
||
CREATE TRIGGER trg_survey_detail_updated_at BEFORE UPDATE ON survey_detail
|
||
FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
|
||
|
||
-- 22. 安装派单表
|
||
DROP TABLE IF EXISTS install_order CASCADE;
|
||
CREATE TABLE install_order (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
install_no VARCHAR(64) NOT NULL UNIQUE,
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
installer VARCHAR(64),
|
||
worker_id BIGINT,
|
||
dispatch_date DATE,
|
||
start_date DATE,
|
||
end_date DATE,
|
||
plan_id BIGINT REFERENCES project_plan(id),
|
||
remark TEXT,
|
||
status SMALLINT NOT NULL DEFAULT 0,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE install_order IS '项目安装任务派单信息表';
|
||
COMMENT ON COLUMN install_order.id IS '自增主键';
|
||
COMMENT ON COLUMN install_order.install_no IS '安装单号（唯一）';
|
||
COMMENT ON COLUMN install_order.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN install_order.installer IS '安装人（共享电工）';
|
||
COMMENT ON COLUMN install_order.worker_id IS '共享电工 ID';
|
||
COMMENT ON COLUMN install_order.dispatch_date IS '派单日期';
|
||
COMMENT ON COLUMN install_order.start_date IS '开始日期';
|
||
COMMENT ON COLUMN install_order.end_date IS '结束日期';
|
||
COMMENT ON COLUMN install_order.plan_id IS '方案 ID（→ project_plan）';
|
||
COMMENT ON COLUMN install_order.remark IS '备注';
|
||
COMMENT ON COLUMN install_order.status IS '状态：0已派发 1已完成';
|
||
COMMENT ON COLUMN install_order.created_at IS '创建时间';
|
||
CREATE INDEX idx_install_customer ON install_order (customer_id);
|
||
CREATE INDEX idx_install_worker ON install_order (worker_id);
|
||
|
||
-- 23. 项目验收表
|
||
DROP TABLE IF EXISTS acceptance CASCADE;
|
||
CREATE TABLE acceptance (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
accept_no VARCHAR(64) NOT NULL UNIQUE,
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
acceptor VARCHAR(64),
|
||
accept_opinion TEXT,
|
||
accept_date DATE,
|
||
installer VARCHAR(64),
|
||
accept_file_url VARCHAR(512),
|
||
contract_id BIGINT REFERENCES contract(id),
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE acceptance IS '项目验收信息表';
|
||
COMMENT ON COLUMN acceptance.id IS '自增主键';
|
||
COMMENT ON COLUMN acceptance.accept_no IS '验收单号（唯一）';
|
||
COMMENT ON COLUMN acceptance.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN acceptance.acceptor IS '验收人';
|
||
COMMENT ON COLUMN acceptance.accept_opinion IS '验收意见';
|
||
COMMENT ON COLUMN acceptance.accept_date IS '验收日期';
|
||
COMMENT ON COLUMN acceptance.installer IS '安装人';
|
||
COMMENT ON COLUMN acceptance.accept_file_url IS '验收文件电子版';
|
||
COMMENT ON COLUMN acceptance.contract_id IS '合同 ID（→ contract）';
|
||
COMMENT ON COLUMN acceptance.created_at IS '创建时间';
|
||
CREATE INDEX idx_accept_customer ON acceptance (customer_id);
|
||
|
||
-- 24. 客户回访信息表
|
||
DROP TABLE IF EXISTS visit_record CASCADE;
|
||
CREATE TABLE visit_record (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
visit_no VARCHAR(64) NOT NULL UNIQUE,
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
contact_person VARCHAR(64),
|
||
contact_phone VARCHAR(20),
|
||
visitor VARCHAR(64),
|
||
visit_time DATE,
|
||
customer_opinion TEXT,
|
||
photos JSONB,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE visit_record IS '客户回访信息表';
|
||
COMMENT ON COLUMN visit_record.id IS '自增主键';
|
||
COMMENT ON COLUMN visit_record.visit_no IS '回访单号（唯一）';
|
||
COMMENT ON COLUMN visit_record.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN visit_record.contact_person IS '联系人';
|
||
COMMENT ON COLUMN visit_record.contact_phone IS '电话';
|
||
COMMENT ON COLUMN visit_record.visitor IS '回访人';
|
||
COMMENT ON COLUMN visit_record.visit_time IS '回访时间';
|
||
COMMENT ON COLUMN visit_record.customer_opinion IS '客户意见';
|
||
COMMENT ON COLUMN visit_record.photos IS '回访照片（JSONB）';
|
||
COMMENT ON COLUMN visit_record.created_at IS '创建时间';
|
||
CREATE INDEX idx_visit_rec_customer ON visit_record (customer_id);
|
||
|
||
-- ============================================================
|
||
-- 七、报警域（4 张表）
|
||
-- ============================================================
|
||
|
||
-- 25. 报警故障类型表
|
||
DROP TABLE IF EXISTS fault_type CASCADE;
|
||
CREATE TABLE fault_type (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
fault_type_code VARCHAR(64) NOT NULL UNIQUE,
|
||
fault_type_name VARCHAR(128) NOT NULL,
|
||
description TEXT,
|
||
remark TEXT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE fault_type IS '报警故障类型信息表';
|
||
COMMENT ON COLUMN fault_type.id IS '自增主键';
|
||
COMMENT ON COLUMN fault_type.fault_type_code IS '故障类型编码（唯一）';
|
||
COMMENT ON COLUMN fault_type.fault_type_name IS '故障类型名称';
|
||
COMMENT ON COLUMN fault_type.description IS '故障类型说明';
|
||
COMMENT ON COLUMN fault_type.remark IS '备注';
|
||
COMMENT ON COLUMN fault_type.created_at IS '创建时间';
|
||
|
||
-- 26. 报警故障类型明细表
|
||
DROP TABLE IF EXISTS fault_type_detail CASCADE;
|
||
CREATE TABLE fault_type_detail (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
fault_type_id BIGINT NOT NULL REFERENCES fault_type(id) ON DELETE CASCADE,
|
||
detail_category VARCHAR(32) NOT NULL,
|
||
detail_desc TEXT,
|
||
remark TEXT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE fault_type_detail IS '报警故障类型明细信息表';
|
||
COMMENT ON COLUMN fault_type_detail.id IS '自增主键';
|
||
COMMENT ON COLUMN fault_type_detail.fault_type_id IS '故障类型 ID（→ fault_type）';
|
||
COMMENT ON COLUMN fault_type_detail.detail_category IS '明细分类：故障原因/处理流程';
|
||
COMMENT ON COLUMN fault_type_detail.detail_desc IS '明细说明';
|
||
COMMENT ON COLUMN fault_type_detail.remark IS '备注';
|
||
COMMENT ON COLUMN fault_type_detail.created_at IS '创建时间';
|
||
CREATE INDEX idx_ftd_type ON fault_type_detail (fault_type_id);
|
||
|
||
-- 27. 设备报警信息表（ICC 拉取）
|
||
DROP TABLE IF EXISTS device_alarm CASCADE;
|
||
CREATE TABLE device_alarm (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
alarm_code VARCHAR(128) NOT NULL UNIQUE,
|
||
line_id BIGINT,
|
||
equip_id BIGINT,
|
||
icc_device_code VARCHAR(128),
|
||
line_name VARCHAR(128),
|
||
customer_id BIGINT,
|
||
building_id BIGINT,
|
||
floor_no VARCHAR(16),
|
||
location VARCHAR(256),
|
||
alarm_date DATE,
|
||
fault_type VARCHAR(128),
|
||
alarm_value NUMERIC(14,2),
|
||
history_count INT DEFAULT 0,
|
||
synced_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE device_alarm IS '设备报警信息表（ICC 拉取）';
|
||
COMMENT ON COLUMN device_alarm.id IS '自增主键';
|
||
COMMENT ON COLUMN device_alarm.alarm_code IS '报警编码（唯一）';
|
||
COMMENT ON COLUMN device_alarm.line_id IS '线路 ID';
|
||
COMMENT ON COLUMN device_alarm.equip_id IS '客户设备 ID';
|
||
COMMENT ON COLUMN device_alarm.icc_device_code IS 'ICC 设备编号';
|
||
COMMENT ON COLUMN device_alarm.line_name IS '线路名称';
|
||
COMMENT ON COLUMN device_alarm.customer_id IS '客户 ID';
|
||
COMMENT ON COLUMN device_alarm.building_id IS '所属建筑 ID';
|
||
COMMENT ON COLUMN device_alarm.floor_no IS '所在楼层';
|
||
COMMENT ON COLUMN device_alarm.location IS '所在位置';
|
||
COMMENT ON COLUMN device_alarm.alarm_date IS '报警日期';
|
||
COMMENT ON COLUMN device_alarm.fault_type IS 'ICC 预警类型';
|
||
COMMENT ON COLUMN device_alarm.alarm_value IS '报警数值';
|
||
COMMENT ON COLUMN device_alarm.history_count IS '历史出现次数';
|
||
COMMENT ON COLUMN device_alarm.synced_at IS 'ICC 同步时间';
|
||
CREATE INDEX idx_alarm_customer ON device_alarm (customer_id);
|
||
CREATE INDEX idx_alarm_date ON device_alarm (alarm_date);
|
||
CREATE INDEX idx_alarm_line ON device_alarm (line_id);
|
||
|
||
-- 28. 报警派单信息表
|
||
DROP TABLE IF EXISTS alarm_dispatch CASCADE;
|
||
CREATE TABLE alarm_dispatch (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
dispatch_no VARCHAR(64) NOT NULL UNIQUE,
|
||
alarm_id BIGINT,
|
||
line_id BIGINT,
|
||
equip_id BIGINT,
|
||
icc_device_code VARCHAR(128),
|
||
line_name VARCHAR(128),
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
contact_person VARCHAR(64),
|
||
contact_phone VARCHAR(20),
|
||
building_id BIGINT,
|
||
floor_no VARCHAR(16),
|
||
location VARCHAR(256),
|
||
fault_type_id BIGINT REFERENCES fault_type(id),
|
||
fault_desc TEXT,
|
||
dispatcher VARCHAR(64),
|
||
fault_reason TEXT,
|
||
process_flow TEXT,
|
||
worker_id BIGINT,
|
||
status SMALLINT NOT NULL DEFAULT 0,
|
||
process_photos JSONB,
|
||
rating SMALLINT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
completed_at TIMESTAMPTZ
|
||
);
|
||
COMMENT ON TABLE alarm_dispatch IS '报警派单信息表';
|
||
COMMENT ON COLUMN alarm_dispatch.id IS '自增主键';
|
||
COMMENT ON COLUMN alarm_dispatch.dispatch_no IS '报警派单号（唯一）';
|
||
COMMENT ON COLUMN alarm_dispatch.alarm_id IS '报警 ID（→ device_alarm）';
|
||
COMMENT ON COLUMN alarm_dispatch.line_id IS '线路 ID';
|
||
COMMENT ON COLUMN alarm_dispatch.equip_id IS '客户设备 ID';
|
||
COMMENT ON COLUMN alarm_dispatch.icc_device_code IS 'ICC 设备编号';
|
||
COMMENT ON COLUMN alarm_dispatch.line_name IS '线路名称';
|
||
COMMENT ON COLUMN alarm_dispatch.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN alarm_dispatch.contact_person IS '联系人';
|
||
COMMENT ON COLUMN alarm_dispatch.contact_phone IS '电话';
|
||
COMMENT ON COLUMN alarm_dispatch.building_id IS '建筑 ID';
|
||
COMMENT ON COLUMN alarm_dispatch.floor_no IS '所在楼层';
|
||
COMMENT ON COLUMN alarm_dispatch.location IS '所在位置';
|
||
COMMENT ON COLUMN alarm_dispatch.fault_type_id IS '故障类型 ID（→ fault_type）';
|
||
COMMENT ON COLUMN alarm_dispatch.fault_desc IS '故障事项（类型+数值）';
|
||
COMMENT ON COLUMN alarm_dispatch.dispatcher IS '派发人';
|
||
COMMENT ON COLUMN alarm_dispatch.fault_reason IS '故障原因（来自明细）';
|
||
COMMENT ON COLUMN alarm_dispatch.process_flow IS '处理流程（来自明细）';
|
||
COMMENT ON COLUMN alarm_dispatch.worker_id IS '共享电工 ID';
|
||
COMMENT ON COLUMN alarm_dispatch.status IS '状态：0已派发 1已处理';
|
||
COMMENT ON COLUMN alarm_dispatch.process_photos IS '报警处理照片（JSONB 多张）';
|
||
COMMENT ON COLUMN alarm_dispatch.rating IS '派单处理评分';
|
||
COMMENT ON COLUMN alarm_dispatch.created_at IS '创建时间';
|
||
COMMENT ON COLUMN alarm_dispatch.completed_at IS '完成时间';
|
||
CREATE INDEX idx_alarm_disp_customer ON alarm_dispatch (customer_id);
|
||
CREATE INDEX idx_alarm_disp_worker ON alarm_dispatch (worker_id);
|
||
CREATE INDEX idx_alarm_disp_status ON alarm_dispatch (status);
|
||
|
||
-- ============================================================
|
||
-- 八、报修域（2 张表）
|
||
-- ============================================================
|
||
|
||
-- 29. 报修单信息表
|
||
DROP TABLE IF EXISTS repair_request CASCADE;
|
||
CREATE TABLE repair_request (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
repair_no VARCHAR(64) NOT NULL UNIQUE,
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
requester VARCHAR(64),
|
||
requester_phone VARCHAR(20),
|
||
request_time TIMESTAMPTZ,
|
||
building_id BIGINT,
|
||
floor_no VARCHAR(16),
|
||
location VARCHAR(256),
|
||
repair_content TEXT,
|
||
photos JSONB,
|
||
status SMALLINT NOT NULL DEFAULT 0,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE repair_request IS '报修单信息表';
|
||
COMMENT ON COLUMN repair_request.id IS '自增主键';
|
||
COMMENT ON COLUMN repair_request.repair_no IS '报修单号（唯一）';
|
||
COMMENT ON COLUMN repair_request.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN repair_request.requester IS '报修人';
|
||
COMMENT ON COLUMN repair_request.requester_phone IS '报修电话';
|
||
COMMENT ON COLUMN repair_request.request_time IS '报修时间';
|
||
COMMENT ON COLUMN repair_request.building_id IS '所属建筑 ID';
|
||
COMMENT ON COLUMN repair_request.floor_no IS '所在楼层';
|
||
COMMENT ON COLUMN repair_request.location IS '所在位置';
|
||
COMMENT ON COLUMN repair_request.repair_content IS '报修内容';
|
||
COMMENT ON COLUMN repair_request.photos IS '报修照片（JSONB）';
|
||
COMMENT ON COLUMN repair_request.status IS '状态：0待处理 1已派单 2已完成';
|
||
COMMENT ON COLUMN repair_request.created_at IS '创建时间';
|
||
CREATE INDEX idx_repair_customer ON repair_request (customer_id);
|
||
CREATE INDEX idx_repair_status ON repair_request (status);
|
||
|
||
-- 30. 报修派单信息表
|
||
DROP TABLE IF EXISTS repair_dispatch CASCADE;
|
||
CREATE TABLE repair_dispatch (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
dispatch_no VARCHAR(64) NOT NULL UNIQUE,
|
||
repair_id BIGINT NOT NULL REFERENCES repair_request(id),
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
requester VARCHAR(64),
|
||
requester_phone VARCHAR(20),
|
||
request_time TIMESTAMPTZ,
|
||
building_id BIGINT,
|
||
floor_no VARCHAR(16),
|
||
location VARCHAR(256),
|
||
repair_content TEXT,
|
||
dispatcher VARCHAR(64),
|
||
worker_id BIGINT,
|
||
status SMALLINT NOT NULL DEFAULT 0,
|
||
process_photos JSONB,
|
||
rating SMALLINT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
completed_at TIMESTAMPTZ
|
||
);
|
||
COMMENT ON TABLE repair_dispatch IS '报修派单信息表';
|
||
COMMENT ON COLUMN repair_dispatch.id IS '自增主键';
|
||
COMMENT ON COLUMN repair_dispatch.dispatch_no IS '报修派单号（唯一）';
|
||
COMMENT ON COLUMN repair_dispatch.repair_id IS '报修单 ID（→ repair_request）';
|
||
COMMENT ON COLUMN repair_dispatch.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN repair_dispatch.requester IS '报修人';
|
||
COMMENT ON COLUMN repair_dispatch.requester_phone IS '报修电话';
|
||
COMMENT ON COLUMN repair_dispatch.request_time IS '报修时间';
|
||
COMMENT ON COLUMN repair_dispatch.building_id IS '建筑 ID';
|
||
COMMENT ON COLUMN repair_dispatch.floor_no IS '所在楼层';
|
||
COMMENT ON COLUMN repair_dispatch.location IS '所在位置';
|
||
COMMENT ON COLUMN repair_dispatch.repair_content IS '报修内容';
|
||
COMMENT ON COLUMN repair_dispatch.dispatcher IS '派发人';
|
||
COMMENT ON COLUMN repair_dispatch.worker_id IS '共享电工 ID';
|
||
COMMENT ON COLUMN repair_dispatch.status IS '状态：0已派发 1已处理';
|
||
COMMENT ON COLUMN repair_dispatch.process_photos IS '处理完成照片（JSONB）';
|
||
COMMENT ON COLUMN repair_dispatch.rating IS '报修派单处理评分（客户评）';
|
||
COMMENT ON COLUMN repair_dispatch.created_at IS '创建时间';
|
||
COMMENT ON COLUMN repair_dispatch.completed_at IS '完成时间';
|
||
CREATE INDEX idx_repair_disp_customer ON repair_dispatch (customer_id);
|
||
CREATE INDEX idx_repair_disp_worker ON repair_dispatch (worker_id);
|
||
|
||
-- ============================================================
|
||
-- 九、维保域（2 张表）
|
||
-- ============================================================
|
||
|
||
-- 31. 维保单表
|
||
DROP TABLE IF EXISTS maintenance_order CASCADE;
|
||
CREATE TABLE maintenance_order (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
maintenance_no VARCHAR(64) NOT NULL UNIQUE,
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
maintainer VARCHAR(64),
|
||
worker_id BIGINT,
|
||
maintenance_date DATE,
|
||
remark TEXT,
|
||
status SMALLINT NOT NULL DEFAULT 0,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE maintenance_order IS '维保单信息表';
|
||
COMMENT ON COLUMN maintenance_order.id IS '自增主键';
|
||
COMMENT ON COLUMN maintenance_order.maintenance_no IS '维保单号（录入，不自动生成）';
|
||
COMMENT ON COLUMN maintenance_order.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN maintenance_order.maintainer IS '维保人（共享电工）';
|
||
COMMENT ON COLUMN maintenance_order.worker_id IS '共享电工 ID';
|
||
COMMENT ON COLUMN maintenance_order.maintenance_date IS '维保日期';
|
||
COMMENT ON COLUMN maintenance_order.remark IS '备注';
|
||
COMMENT ON COLUMN maintenance_order.status IS '状态：0已派发 1已完成';
|
||
COMMENT ON COLUMN maintenance_order.created_at IS '创建时间';
|
||
CREATE INDEX idx_maint_customer ON maintenance_order (customer_id);
|
||
CREATE INDEX idx_maint_worker ON maintenance_order (worker_id);
|
||
|
||
-- 32. 维保单明细表
|
||
DROP TABLE IF EXISTS maintenance_detail CASCADE;
|
||
CREATE TABLE maintenance_detail (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
maintenance_id BIGINT NOT NULL REFERENCES maintenance_order(id) ON DELETE CASCADE,
|
||
equip_id BIGINT,
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
equip_name VARCHAR(128),
|
||
equip_type VARCHAR(32),
|
||
building_id BIGINT,
|
||
floor_no VARCHAR(16),
|
||
location VARCHAR(256),
|
||
panel_condition TEXT,
|
||
circuit_condition TEXT,
|
||
terminal_condition TEXT,
|
||
maintenance_desc TEXT,
|
||
photos JSONB,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE maintenance_detail IS '维保单明细表';
|
||
COMMENT ON COLUMN maintenance_detail.id IS '自增主键';
|
||
COMMENT ON COLUMN maintenance_detail.maintenance_id IS '维保单 ID（→ maintenance_order）';
|
||
COMMENT ON COLUMN maintenance_detail.equip_id IS '客户设备 ID';
|
||
COMMENT ON COLUMN maintenance_detail.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN maintenance_detail.equip_name IS '客户设备名称';
|
||
COMMENT ON COLUMN maintenance_detail.equip_type IS '设备类型：配电间/井/箱';
|
||
COMMENT ON COLUMN maintenance_detail.building_id IS '建筑 ID';
|
||
COMMENT ON COLUMN maintenance_detail.floor_no IS '所在楼层';
|
||
COMMENT ON COLUMN maintenance_detail.location IS '所在位置';
|
||
COMMENT ON COLUMN maintenance_detail.panel_condition IS '配电间或电箱情况';
|
||
COMMENT ON COLUMN maintenance_detail.circuit_condition IS '回路情况';
|
||
COMMENT ON COLUMN maintenance_detail.terminal_condition IS '终端情况';
|
||
COMMENT ON COLUMN maintenance_detail.maintenance_desc IS '维保情况说明';
|
||
COMMENT ON COLUMN maintenance_detail.photos IS '维保现场照片（JSONB 水印）';
|
||
COMMENT ON COLUMN maintenance_detail.created_at IS '创建时间';
|
||
CREATE INDEX idx_md_maint ON maintenance_detail (maintenance_id);
|
||
CREATE INDEX idx_md_customer ON maintenance_detail (customer_id);
|
||
|
||
-- ============================================================
|
||
-- 十、共享电工域（4 张表）
|
||
-- ============================================================
|
||
|
||
-- 33. 共享电工信息表
|
||
DROP TABLE IF EXISTS worker CASCADE;
|
||
CREATE TABLE worker (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
worker_name VARCHAR(64) NOT NULL,
|
||
phone VARCHAR(20) NOT NULL,
|
||
id_card VARCHAR(18),
|
||
worker_title VARCHAR(32),
|
||
rating NUMERIC(3,2) DEFAULT 5.00,
|
||
cert_info VARCHAR(128),
|
||
cert_verified BOOLEAN DEFAULT TRUE,
|
||
completed_count INTEGER DEFAULT 0,
|
||
status SMALLINT NOT NULL DEFAULT 1,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE worker IS '共享电工信息表';
|
||
COMMENT ON COLUMN worker.id IS '自增主键';
|
||
COMMENT ON COLUMN worker.worker_name IS '电工姓名';
|
||
COMMENT ON COLUMN worker.phone IS '手机号';
|
||
COMMENT ON COLUMN worker.id_card IS '身份证号';
|
||
COMMENT ON COLUMN worker.worker_title IS '职衔:电工/资深班组长/特种高压电工';
|
||
COMMENT ON COLUMN worker.rating IS '服务评分(0-5)';
|
||
COMMENT ON COLUMN worker.cert_info IS '持证资质';
|
||
COMMENT ON COLUMN worker.cert_verified IS '资质是否已核验';
|
||
COMMENT ON COLUMN worker.completed_count IS '历史累计勘察场次';
|
||
COMMENT ON COLUMN worker.status IS '状态：1在岗 0离职';
|
||
COMMENT ON COLUMN worker.created_at IS '创建时间';
|
||
COMMENT ON COLUMN worker.updated_at IS '更新时间';
|
||
CREATE INDEX idx_worker_phone ON worker (phone);
|
||
CREATE TRIGGER trg_worker_updated_at BEFORE UPDATE ON worker
|
||
FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
|
||
|
||
-- 34. 派单价格表
|
||
DROP TABLE IF EXISTS dispatch_price CASCADE;
|
||
CREATE TABLE dispatch_price (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
price_code VARCHAR(64) NOT NULL UNIQUE,
|
||
dispatch_type VARCHAR(32) NOT NULL,
|
||
price_type VARCHAR(32),
|
||
unit_price NUMERIC(10,2),
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE dispatch_price IS '共享电工派单价格信息表';
|
||
COMMENT ON COLUMN dispatch_price.id IS '自增主键';
|
||
COMMENT ON COLUMN dispatch_price.price_code IS '派单价格编码（唯一）';
|
||
COMMENT ON COLUMN dispatch_price.dispatch_type IS '派单类型：勘察/安装/预警/维修/维保';
|
||
COMMENT ON COLUMN dispatch_price.price_type IS '价格类型：单价/总价提成';
|
||
COMMENT ON COLUMN dispatch_price.unit_price IS '服务单价';
|
||
COMMENT ON COLUMN dispatch_price.created_at IS '创建时间';
|
||
|
||
-- 35. 共享电工派单收入表（月度汇总）
|
||
DROP TABLE IF EXISTS worker_income CASCADE;
|
||
CREATE TABLE worker_income (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
income_code VARCHAR(64) NOT NULL UNIQUE,
|
||
worker_id BIGINT NOT NULL REFERENCES worker(id),
|
||
income_month VARCHAR(7) NOT NULL,
|
||
monthly_total NUMERIC(14,2),
|
||
rating_summary NUMERIC(5,2),
|
||
final_total NUMERIC(14,2),
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE worker_income IS '共享电工派单收入信息表（月度汇总）';
|
||
COMMENT ON COLUMN worker_income.id IS '自增主键';
|
||
COMMENT ON COLUMN worker_income.income_code IS '派单收入编码（唯一）';
|
||
COMMENT ON COLUMN worker_income.worker_id IS '共享电工 ID（→ worker）';
|
||
COMMENT ON COLUMN worker_income.income_month IS '收入月份（YYYY-MM）';
|
||
COMMENT ON COLUMN worker_income.monthly_total IS '月度收入合计';
|
||
COMMENT ON COLUMN worker_income.rating_summary IS '评分综合统计';
|
||
COMMENT ON COLUMN worker_income.final_total IS '月度最终收入合计';
|
||
COMMENT ON COLUMN worker_income.created_at IS '创建时间';
|
||
CREATE INDEX idx_wi_worker ON worker_income (worker_id);
|
||
CREATE INDEX idx_wi_month ON worker_income (income_month);
|
||
|
||
-- 36. 共享电工派单收入明细表
|
||
DROP TABLE IF EXISTS worker_income_detail CASCADE;
|
||
CREATE TABLE worker_income_detail (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
income_id BIGINT NOT NULL REFERENCES worker_income(id) ON DELETE CASCADE,
|
||
worker_id BIGINT NOT NULL REFERENCES worker(id),
|
||
income_month VARCHAR(7) NOT NULL,
|
||
dispatch_type VARCHAR(32) NOT NULL,
|
||
dispatch_no VARCHAR(64),
|
||
income_amount NUMERIC(10,2),
|
||
rating_level VARCHAR(16),
|
||
final_income NUMERIC(10,2),
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE worker_income_detail IS '共享电工派单收入明细信息表';
|
||
COMMENT ON COLUMN worker_income_detail.id IS '自增主键';
|
||
COMMENT ON COLUMN worker_income_detail.income_id IS '收入 ID（→ worker_income）';
|
||
COMMENT ON COLUMN worker_income_detail.worker_id IS '共享电工 ID（→ worker）';
|
||
COMMENT ON COLUMN worker_income_detail.income_month IS '收入月份';
|
||
COMMENT ON COLUMN worker_income_detail.dispatch_type IS '派单类型';
|
||
COMMENT ON COLUMN worker_income_detail.dispatch_no IS '各类型派单单号';
|
||
COMMENT ON COLUMN worker_income_detail.income_amount IS '本单收入';
|
||
COMMENT ON COLUMN worker_income_detail.rating_level IS '评价等级';
|
||
COMMENT ON COLUMN worker_income_detail.final_income IS '本单最终收入合计';
|
||
COMMENT ON COLUMN worker_income_detail.created_at IS '创建时间';
|
||
CREATE INDEX idx_wid_income ON worker_income_detail (income_id);
|
||
CREATE INDEX idx_wid_worker ON worker_income_detail (worker_id);
|
||
|
||
-- ============================================================
|
||
-- 十一、用电采集数据（1 张表，按月 RANGE 分区）
|
||
-- ============================================================
|
||
|
||
-- 37. 线路用电信息表（按月分区，时序数据）
|
||
DROP TABLE IF EXISTS electricity_data CASCADE;
|
||
CREATE TABLE electricity_data (
|
||
id BIGSERIAL,
|
||
line_id BIGINT NOT NULL,
|
||
line_code VARCHAR(64) NOT NULL,
|
||
equip_id BIGINT,
|
||
icc_device_code VARCHAR(128),
|
||
line_name VARCHAR(128),
|
||
customer_id BIGINT NOT NULL,
|
||
electricity NUMERIC(14,4),
|
||
collect_time TIMESTAMPTZ NOT NULL,
|
||
PRIMARY KEY (id, collect_time)
|
||
) PARTITION BY RANGE (collect_time);
|
||
|
||
COMMENT ON TABLE electricity_data IS '线路用电信息表（按月分区，时序数据）';
|
||
COMMENT ON COLUMN electricity_data.id IS '自增（复合主键含 collect_time）';
|
||
COMMENT ON COLUMN electricity_data.line_id IS '线路 ID（→ electric_line）';
|
||
COMMENT ON COLUMN electricity_data.line_code IS '线路编码（冗余，加速查询）';
|
||
COMMENT ON COLUMN electricity_data.equip_id IS '客户设备 ID';
|
||
COMMENT ON COLUMN electricity_data.icc_device_code IS 'ICC 设备编号';
|
||
COMMENT ON COLUMN electricity_data.line_name IS '线路名称';
|
||
COMMENT ON COLUMN electricity_data.customer_id IS '客户 ID（逻辑隔离键）';
|
||
COMMENT ON COLUMN electricity_data.electricity IS '用电量';
|
||
COMMENT ON COLUMN electricity_data.collect_time IS '采集时间（分区键）';
|
||
|
||
-- 初始分区（2026 年 8~12 月）
|
||
CREATE TABLE electricity_data_2026_08 PARTITION OF electricity_data
|
||
FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
|
||
CREATE TABLE electricity_data_2026_09 PARTITION OF electricity_data
|
||
FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
|
||
CREATE TABLE electricity_data_2026_10 PARTITION OF electricity_data
|
||
FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
|
||
CREATE TABLE electricity_data_2026_11 PARTITION OF electricity_data
|
||
FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
|
||
CREATE TABLE electricity_data_2026_12 PARTITION OF electricity_data
|
||
FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');
|
||
|
||
-- 分区索引（在父表创建，自动继承到各分区）
|
||
CREATE INDEX idx_ed_customer ON electricity_data (customer_id);
|
||
CREATE INDEX idx_ed_line ON electricity_data (line_id);
|
||
CREATE INDEX idx_ed_time ON electricity_data (collect_time);
|
||
CREATE INDEX idx_ed_customer_time ON electricity_data (customer_id, collect_time);
|
||
|
||
-- ============================================================
|
||
-- 自动分区管理（纯 PostgreSQL，无需 pg_partman 扩展）
|
||
-- ============================================================
|
||
|
||
-- 1) 确保指定月份的分区存在（不存在则创建）
|
||
CREATE OR REPLACE FUNCTION ensure_electricity_partition(p_date DATE DEFAULT CURRENT_DATE)
|
||
RETURNS VOID AS $$
|
||
DECLARE
|
||
v_month_start DATE;
|
||
v_month_end DATE;
|
||
v_part_name TEXT;
|
||
v_exists BOOLEAN;
|
||
BEGIN
|
||
v_month_start := DATE_TRUNC('month', p_date)::DATE;
|
||
v_month_end := (v_month_start + INTERVAL '1 month')::DATE;
|
||
v_part_name := 'electricity_data_' || TO_CHAR(v_month_start, 'YYYY_MM');
|
||
|
||
SELECT EXISTS(
|
||
SELECT 1 FROM pg_class c
|
||
JOIN pg_namespace n ON n.oid = c.relnamespace
|
||
WHERE c.relname = v_part_name AND n.nspname = 'public'
|
||
) INTO v_exists;
|
||
|
||
IF NOT v_exists THEN
|
||
EXECUTE format(
|
||
'CREATE TABLE %I PARTITION OF electricity_data FOR VALUES FROM (%L) TO (%L)',
|
||
v_part_name, v_month_start, v_month_end
|
||
);
|
||
END IF;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
COMMENT ON FUNCTION ensure_electricity_partition IS '确保指定月份的用电数据分区存在（不存在则自动创建）';
|
||
|
||
-- 2) 批量预创建未来分区（p_premake = 预创建未来几个月）
|
||
CREATE OR REPLACE FUNCTION run_electricity_partition_maintenance(p_premake INT DEFAULT 3)
|
||
RETURNS TABLE(created_month TEXT, action TEXT) AS $$
|
||
DECLARE
|
||
v_date DATE := CURRENT_DATE;
|
||
v_i INT;
|
||
v_month_start DATE;
|
||
v_part_name TEXT;
|
||
v_exists BOOLEAN;
|
||
BEGIN
|
||
FOR v_i IN 0..p_premake LOOP
|
||
v_month_start := DATE_TRUNC('month', v_date + (v_i || ' month')::INTERVAL)::DATE;
|
||
v_part_name := 'electricity_data_' || TO_CHAR(v_month_start, 'YYYY_MM');
|
||
|
||
SELECT EXISTS(
|
||
SELECT 1 FROM pg_class c
|
||
JOIN pg_namespace n ON n.oid = c.relnamespace
|
||
WHERE c.relname = v_part_name AND n.nspname = 'public'
|
||
) INTO v_exists;
|
||
|
||
IF NOT v_exists THEN
|
||
EXECUTE format(
|
||
'CREATE TABLE %I PARTITION OF electricity_data FOR VALUES FROM (%L) TO (%L)',
|
||
v_part_name,
|
||
v_month_start,
|
||
(v_month_start + INTERVAL '1 month')::DATE
|
||
);
|
||
created_month := v_part_name;
|
||
action := 'CREATED';
|
||
ELSE
|
||
created_month := v_part_name;
|
||
action := 'EXISTS';
|
||
END IF;
|
||
RETURN NEXT;
|
||
END LOOP;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
COMMENT ON FUNCTION run_electricity_partition_maintenance IS '批量预创建用电数据分区（默认当前月+未来3个月），返回创建结果';
|
||
|
||
-- 3) pg_cron 定时调度（数据库全自动管理，无需后端 cron）
|
||
-- pg_cron 扩展在数据库内部定时执行，后端服务状态不影响分区管理
|
||
-- 注意: PostgreSQL 分区表的 BEFORE INSERT 触发器在分区路由之后才执行，
|
||
-- 无法在写入时动态创建分区。因此通过 pg_cron 定时预创建 + 应用层 ensure 兜底。
|
||
|
||
-- 删除已存在的同名定时任务（幂等）
|
||
DO $$
|
||
BEGIN
|
||
PERFORM cron.unschedule(jobid) FROM cron.job WHERE jobname IN ('electricity_partition_maintenance', 'electricity_monthly_refresh');
|
||
EXCEPTION WHEN OTHERS THEN NULL;
|
||
END $$;
|
||
|
||
-- 每月25号凌晨3点：预创建当前月 + 未来3个月分区
|
||
SELECT cron.schedule(
|
||
'electricity_partition_maintenance',
|
||
'0 3 25 * *',
|
||
'SELECT run_electricity_partition_maintenance(3)'
|
||
);
|
||
|
||
-- 每月1号凌晨2点：刷新月度汇总物化视图
|
||
SELECT cron.schedule(
|
||
'electricity_monthly_refresh',
|
||
'0 2 1 * *',
|
||
'SELECT refresh_electricity_monthly()'
|
||
);
|
||
|
||
-- 4) 立即执行一次维护：确保当前月+未来3个月分区就绪
|
||
SELECT * FROM run_electricity_partition_maintenance(3);
|
||
|
||
-- ============================================================
|
||
-- 用电数据月度汇总物化视图（历史数据分析）
|
||
-- ============================================================
|
||
|
||
CREATE MATERIALIZED VIEW IF NOT EXISTS mv_electricity_monthly AS
|
||
SELECT
|
||
customer_id,
|
||
line_id,
|
||
TO_CHAR(collect_time, 'YYYY-MM') AS month,
|
||
SUM(electricity) AS total_usage,
|
||
AVG(electricity) AS avg_usage,
|
||
MAX(electricity) AS peak_usage,
|
||
MIN(electricity) AS min_usage,
|
||
COUNT(*) AS record_count,
|
||
MIN(collect_time) AS first_collect,
|
||
MAX(collect_time) AS last_collect
|
||
FROM electricity_data
|
||
GROUP BY customer_id, line_id, TO_CHAR(collect_time, 'YYYY-MM')
|
||
WITH NO DATA;
|
||
|
||
COMMENT ON MATERIALIZED VIEW mv_electricity_monthly IS '用电数据月度汇总（历史分析用，每月刷新）';
|
||
|
||
CREATE UNIQUE INDEX IF NOT EXISTS idx_mv_em_key
|
||
ON mv_electricity_monthly (customer_id, line_id, month);
|
||
CREATE INDEX IF NOT EXISTS idx_mv_em_customer
|
||
ON mv_electricity_monthly (customer_id);
|
||
CREATE INDEX IF NOT EXISTS idx_mv_em_month
|
||
ON mv_electricity_monthly (month);
|
||
|
||
-- 刷新函数（应用层每月初调用，或用 pg_cron 定时）
|
||
-- 首次刷新用非并发模式初始化，后续用 CONCURRENTLY 不阻塞查询
|
||
CREATE OR REPLACE FUNCTION refresh_electricity_monthly()
|
||
RETURNS VOID AS $$
|
||
BEGIN
|
||
IF NOT pg_is_in_recovery() AND EXISTS (
|
||
SELECT 1 FROM pg_matviews WHERE matviewname = 'mv_electricity_monthly'
|
||
) THEN
|
||
BEGIN
|
||
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_electricity_monthly;
|
||
EXCEPTION WHEN feature_not_supported THEN
|
||
REFRESH MATERIALIZED VIEW mv_electricity_monthly;
|
||
END;
|
||
ELSE
|
||
REFRESH MATERIALIZED VIEW mv_electricity_monthly;
|
||
END IF;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
COMMENT ON FUNCTION refresh_electricity_monthly IS '刷新用电月度汇总物化视图（自动处理首次初始化，后续用 CONCURRENTLY）';
|
||
|
||
-- 首次初始化物化视图（WITH NO DATA 创建后必须先刷新一次）
|
||
REFRESH MATERIALIZED VIEW mv_electricity_monthly;
|
||
|
||
-- ============================================================
|
||
-- 十二、辅助域（2 张表）
|
||
-- ============================================================
|
||
|
||
-- ICC 同步日志表
|
||
DROP TABLE IF EXISTS icc_sync_log CASCADE;
|
||
CREATE TABLE icc_sync_log (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
sync_type VARCHAR(32) NOT NULL,
|
||
start_time TIMESTAMPTZ,
|
||
end_time TIMESTAMPTZ,
|
||
record_count INT,
|
||
status SMALLINT NOT NULL DEFAULT 1,
|
||
error_message TEXT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE icc_sync_log IS 'ICC 数据同步日志表';
|
||
COMMENT ON COLUMN icc_sync_log.id IS '自增主键';
|
||
COMMENT ON COLUMN icc_sync_log.sync_type IS '同步类型：客户/设备/用电/报警';
|
||
COMMENT ON COLUMN icc_sync_log.start_time IS '开始时间';
|
||
COMMENT ON COLUMN icc_sync_log.end_time IS '结束时间';
|
||
COMMENT ON COLUMN icc_sync_log.record_count IS '同步记录数';
|
||
COMMENT ON COLUMN icc_sync_log.status IS '状态：1成功 0失败';
|
||
COMMENT ON COLUMN icc_sync_log.error_message IS '错误信息';
|
||
COMMENT ON COLUMN icc_sync_log.created_at IS '创建时间';
|
||
CREATE INDEX idx_icc_sync_type ON icc_sync_log (sync_type);
|
||
CREATE INDEX idx_icc_sync_time ON icc_sync_log (created_at);
|
||
|
||
-- 文件存储记录表
|
||
DROP TABLE IF EXISTS file_record CASCADE;
|
||
CREATE TABLE file_record (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
file_name VARCHAR(256) NOT NULL,
|
||
file_url VARCHAR(512) NOT NULL,
|
||
file_type VARCHAR(32),
|
||
file_size BIGINT,
|
||
business_type VARCHAR(64),
|
||
business_id BIGINT,
|
||
uploaded_by BIGINT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE file_record IS '文件存储记录表';
|
||
COMMENT ON COLUMN file_record.id IS '自增主键';
|
||
COMMENT ON COLUMN file_record.file_name IS '文件名';
|
||
COMMENT ON COLUMN file_record.file_url IS '文件 URL';
|
||
COMMENT ON COLUMN file_record.file_type IS '文件类型：取证/合同/验收/方案/月报';
|
||
COMMENT ON COLUMN file_record.file_size IS '文件大小（字节）';
|
||
COMMENT ON COLUMN file_record.business_type IS '关联业务类型';
|
||
COMMENT ON COLUMN file_record.business_id IS '关联业务 ID';
|
||
COMMENT ON COLUMN file_record.uploaded_by IS '上传人 ID';
|
||
COMMENT ON COLUMN file_record.created_at IS '创建时间';
|
||
CREATE INDEX idx_file_business ON file_record (business_type, business_id);
|
||
|
||
-- ============================================================
|
||
-- 十三、初始数据
|
||
-- 已迁移至独立脚本 db_seed.sql（幂等执行，便于单独重置种子数据）
|
||
-- 执行顺序：先 db_init.sql 建表结构 → 再 db_seed.sql 写入种子数据
|
||
-- ============================================================
|
||
|
||
COMMIT;
|
||
|
||
-- ============================================================
|
||
-- 完 · 37 张表 · 50+ 索引 · 3 个触发器 · 3 个分区管理函数 · 1 个物化视图 · 2 个 pg_cron 定时任务（种子数据见 db_seed.sql）
|
||
-- ============================================================
|
||
|
||
-- ============================================================
|
||
-- 附录: pg_cron 安装说明（首次部署时执行一次）
|
||
-- ============================================================
|
||
--
|
||
-- 1. 下载 Windows 预编译包:
|
||
-- https://github.com/hakanrw/pg_cron_windows/releases/download/windows/pg_cron_windows_msvc_x64.zip
|
||
--
|
||
-- 2. 复制文件到 PostgreSQL 目录:
|
||
-- pg_cron.dll → C:\Program Files\PostgreSQL\17\lib\
|
||
-- pg_cron.control → C:\Program Files\PostgreSQL\17\share\extension\
|
||
-- pg_cron--*.sql → C:\Program Files\PostgreSQL\17\share\extension\
|
||
--
|
||
-- 3. 编辑 postgresql.conf 添加以下内容:
|
||
-- shared_preload_libraries = 'pg_cron'
|
||
-- cron.database_name = 'xintong'
|
||
-- cron.host = '127.0.0.1'
|
||
-- cron.use_background_workers = on
|
||
-- cron.timezone = 'Asia/Shanghai'
|
||
--
|
||
-- 4. 编辑 pg_hba.conf，在 IPv4 local connections 之前添加:
|
||
-- host all postgres 127.0.0.1/32 trust
|
||
-- host all postgres ::1/128 trust
|
||
--
|
||
-- 5. 重启 PostgreSQL 服务
|
||
--
|
||
-- 6. 执行本脚本: psql -U postgres -d xintong -f db_init.sql
|
||
-- 脚本会自动创建 pg_cron 扩展并注册定时任务
|
||
--
|
||
-- 注意: cron.use_background_workers = on 是 Windows 上的关键配置，
|
||
-- 使用后台工作进程代替 TCP 连接，避免连接认证超时问题。
|
||
-- ============================================================
|