94 lines
7.4 KiB
Go
94 lines
7.4 KiB
Go
// 功能描述：仅新增押金规则、押金记录及完整中文数据库注释；版本：1.0.0。
|
||
package main
|
||
|
||
// migrateDeposit 创建首期押金事实表，不写入规则、金额或用户业务数据。
|
||
func migrateDeposit() error {
|
||
return migrateClientColumns([]string{
|
||
`CREATE TABLE IF NOT EXISTS deposit_policy (
|
||
id bigserial PRIMARY KEY, identity varchar(36) NOT NULL UNIQUE,
|
||
created_at timestamptz NOT NULL, updated_at timestamptz NOT NULL, deleted_at timestamptz,
|
||
status integer NOT NULL DEFAULT 1 CHECK (status IN (1,2,3)),
|
||
product_type_id bigint NOT NULL REFERENCES product_type(id), name varchar(128) NOT NULL,
|
||
amount bigint NOT NULL CHECK (amount >= 0), rule_text text NOT NULL DEFAULT '',
|
||
CONSTRAINT ux_deposit_policy_product_type UNIQUE (product_type_id)
|
||
)`,
|
||
`CREATE TABLE IF NOT EXISTS deposit_record (
|
||
id bigserial PRIMARY KEY, identity varchar(36) NOT NULL UNIQUE,
|
||
created_at timestamptz NOT NULL, updated_at timestamptz NOT NULL, deleted_at timestamptz,
|
||
status integer NOT NULL DEFAULT 1 CHECK (status IN (1,3)),
|
||
deposit_status integer NOT NULL DEFAULT 10 CHECK (deposit_status IN (10,20,23)),
|
||
deposit_no varchar(64) NOT NULL UNIQUE, user_account_id bigint NOT NULL REFERENCES user_account(id),
|
||
product_info_id bigint NOT NULL REFERENCES product_info(id), policy_id bigint NOT NULL REFERENCES deposit_policy(id),
|
||
gasorder_id bigint NOT NULL DEFAULT 0, amount bigint NOT NULL CHECK (amount >= 0),
|
||
paid_at timestamptz NOT NULL, refunded_at timestamptz
|
||
)`,
|
||
`CREATE TABLE IF NOT EXISTS deposit_return_request (
|
||
id bigserial PRIMARY KEY, identity varchar(36) NOT NULL UNIQUE,
|
||
created_at timestamptz NOT NULL, updated_at timestamptz NOT NULL, deleted_at timestamptz,
|
||
status integer NOT NULL DEFAULT 1 CHECK (status IN (1,3)),
|
||
return_status integer NOT NULL DEFAULT 10 CHECK (return_status IN (10,20,30,40,50)),
|
||
request_no varchar(128) NOT NULL UNIQUE, deposit_record_id bigint NOT NULL UNIQUE REFERENCES deposit_record(id),
|
||
user_account_id bigint NOT NULL REFERENCES user_account(id), user_address_id bigint NOT NULL REFERENCES user_address(id),
|
||
address_snapshot varchar(255) NOT NULL, contact_name varchar(64) NOT NULL, contact_phone varchar(32) NOT NULL,
|
||
appointment_start timestamptz NOT NULL, appointment_end timestamptz NOT NULL,
|
||
estimated_amount bigint NOT NULL CHECK (estimated_amount >= 0),
|
||
deduction_amount bigint NOT NULL DEFAULT 0 CHECK (deduction_amount >= 0),
|
||
refund_amount bigint NOT NULL DEFAULT 0 CHECK (refund_amount >= 0),
|
||
inspection_remark text NOT NULL DEFAULT '', operator_identity varchar(36) NOT NULL DEFAULT '',
|
||
picked_up_at timestamptz, inspected_at timestamptz, completed_at timestamptz
|
||
)`,
|
||
`COMMENT ON TABLE deposit_policy IS '气瓶产品类型押金规则；金额与退还说明由平台运营配置'`,
|
||
`COMMENT ON COLUMN deposit_policy.id IS '押金规则内部自增主键，不向客户端暴露'`,
|
||
`COMMENT ON COLUMN deposit_policy.identity IS '押金规则公开UUID V7标识'`,
|
||
`COMMENT ON COLUMN deposit_policy.created_at IS '规则创建时间'`,
|
||
`COMMENT ON COLUMN deposit_policy.updated_at IS '规则最后更新时间'`,
|
||
`COMMENT ON COLUMN deposit_policy.deleted_at IS '软删除时间'`,
|
||
`COMMENT ON COLUMN deposit_policy.status IS '通用状态：1=启用，2=停用，3=归档'`,
|
||
`COMMENT ON COLUMN deposit_policy.product_type_id IS '适用气瓶产品类型内部主键'`,
|
||
`COMMENT ON COLUMN deposit_policy.name IS '押金规则名称'`,
|
||
`COMMENT ON COLUMN deposit_policy.amount IS '单只气瓶押金金额，单位分'`,
|
||
`COMMENT ON COLUMN deposit_policy.rule_text IS '用户端展示的押金退还规则正文'`,
|
||
`COMMENT ON TABLE deposit_record IS '用户每只实体气瓶的押金收取及退回事实'`,
|
||
`COMMENT ON COLUMN deposit_record.id IS '押金记录内部自增主键，不向客户端暴露'`,
|
||
`COMMENT ON COLUMN deposit_record.identity IS '押金记录公开UUID V7标识'`,
|
||
`COMMENT ON COLUMN deposit_record.created_at IS '押金记录创建时间'`,
|
||
`COMMENT ON COLUMN deposit_record.updated_at IS '押金状态最后更新时间'`,
|
||
`COMMENT ON COLUMN deposit_record.deleted_at IS '软删除时间；资金事实通常不删除'`,
|
||
`COMMENT ON COLUMN deposit_record.status IS '通用状态：1=有效，3=归档'`,
|
||
`COMMENT ON COLUMN deposit_record.deposit_status IS '押金状态：10=使用中，20=退款中，23=已退回'`,
|
||
`COMMENT ON COLUMN deposit_record.deposit_no IS '押金业务编号'`,
|
||
`COMMENT ON COLUMN deposit_record.user_account_id IS '押金所属用户内部主键'`,
|
||
`COMMENT ON COLUMN deposit_record.product_info_id IS '绑定实体气瓶内部主键'`,
|
||
`COMMENT ON COLUMN deposit_record.policy_id IS '计费时采用的押金规则内部主键'`,
|
||
`COMMENT ON COLUMN deposit_record.gasorder_id IS '来源供气订单内部主键，0表示历史导入'`,
|
||
`COMMENT ON COLUMN deposit_record.amount IS '实收押金金额，单位分'`,
|
||
`COMMENT ON COLUMN deposit_record.paid_at IS '押金缴纳时间'`,
|
||
`COMMENT ON COLUMN deposit_record.refunded_at IS '押金实际退回时间'`,
|
||
`COMMENT ON TABLE deposit_return_request IS '退瓶上门回收、验收扣减及押金入账申请表'`,
|
||
`COMMENT ON COLUMN deposit_return_request.id IS '退瓶申请内部自增主键'`,
|
||
`COMMENT ON COLUMN deposit_return_request.identity IS '退瓶申请公开UUID V7标识'`,
|
||
`COMMENT ON COLUMN deposit_return_request.created_at IS '申请创建时间'`,
|
||
`COMMENT ON COLUMN deposit_return_request.updated_at IS '状态最后更新时间'`,
|
||
`COMMENT ON COLUMN deposit_return_request.deleted_at IS '软删除时间；完成资金事实不删除'`,
|
||
`COMMENT ON COLUMN deposit_return_request.status IS '通用状态：1=有效，3=归档'`,
|
||
`COMMENT ON COLUMN deposit_return_request.return_status IS '退瓶状态：10=待上门，20=已回收待验收，30=已验收待退款，40=已完成，50=已取消'`,
|
||
`COMMENT ON COLUMN deposit_return_request.request_no IS '用户端幂等请求号'`,
|
||
`COMMENT ON COLUMN deposit_return_request.deposit_record_id IS '对应押金记录内部主键'`,
|
||
`COMMENT ON COLUMN deposit_return_request.user_account_id IS '申请用户内部主键'`,
|
||
`COMMENT ON COLUMN deposit_return_request.user_address_id IS '上门回收地址内部主键'`,
|
||
`COMMENT ON COLUMN deposit_return_request.address_snapshot IS '申请时上门地址快照'`,
|
||
`COMMENT ON COLUMN deposit_return_request.contact_name IS '申请时联系人快照'`,
|
||
`COMMENT ON COLUMN deposit_return_request.contact_phone IS '申请时联系手机快照'`,
|
||
`COMMENT ON COLUMN deposit_return_request.appointment_start IS '预约上门时段开始时间'`,
|
||
`COMMENT ON COLUMN deposit_return_request.appointment_end IS '预约上门时段结束时间'`,
|
||
`COMMENT ON COLUMN deposit_return_request.estimated_amount IS '申请时预计可退押金，单位分'`,
|
||
`COMMENT ON COLUMN deposit_return_request.deduction_amount IS '验收后损坏或缺件扣减，单位分'`,
|
||
`COMMENT ON COLUMN deposit_return_request.refund_amount IS '最终退入余额账户金额，单位分'`,
|
||
`COMMENT ON COLUMN deposit_return_request.inspection_remark IS '验收结果及扣减理由'`,
|
||
`COMMENT ON COLUMN deposit_return_request.operator_identity IS '最后操作的平台账号标识'`,
|
||
`COMMENT ON COLUMN deposit_return_request.picked_up_at IS '实际回收时间'`,
|
||
`COMMENT ON COLUMN deposit_return_request.inspected_at IS '完成验收时间'`,
|
||
`COMMENT ON COLUMN deposit_return_request.completed_at IS '押金退回完成时间'`,
|
||
})
|
||
}
|