water-management-system/sql/V_meter_lifecycle.sql

-- ============================================================
-- 水表全生命周期管理 DDL
-- 包含: rev_water_meter, rev_meter_install_record,
--       rev_meter_replace_record, rev_meter_lifecycle_log
-- ============================================================

-- 1. 水表主表
CREATE TABLE IF NOT EXISTS rev_water_meter (
    id              BIGSERIAL       PRIMARY KEY,
    meter_no        VARCHAR(64)     NOT NULL UNIQUE,
    model           VARCHAR(64),
    diameter        INTEGER,
    manufacturer    VARCHAR(128),
    production_date DATE,
    install_date    DATE,
    install_address VARCHAR(512),
    customer_no     VARCHAR(64),
    status          VARCHAR(32)     NOT NULL DEFAULT 'IN_STOCK',
    in_stock_time   TIMESTAMP,
    out_stock_time  TIMESTAMP,
    initial_reading NUMERIC(14,4)   DEFAULT 0,
    current_reading NUMERIC(14,4)   DEFAULT 0,
    dismantle_date  DATE,
    scrapped_date   DATE,
    remark          VARCHAR(512),
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE  rev_water_meter                     IS '水表主表';
COMMENT ON COLUMN rev_water_meter.meter_no            IS '水表编号';
COMMENT ON COLUMN rev_water_meter.model               IS '水表型号';
COMMENT ON COLUMN rev_water_meter.diameter            IS '口径(mm)';
COMMENT ON COLUMN rev_water_meter.manufacturer        IS '制造商';
COMMENT ON COLUMN rev_water_meter.production_date     IS '生产日期';
COMMENT ON COLUMN rev_water_meter.install_date        IS '安装日期';
COMMENT ON COLUMN rev_water_meter.install_address     IS '安装地址';
COMMENT ON COLUMN rev_water_meter.customer_no         IS '客户编号';
COMMENT ON COLUMN rev_water_meter.status              IS '状态: IN_STOCK/INSTALLED/DISMANTLED/SCRAPPED/REPAIRING';
COMMENT ON COLUMN rev_water_meter.in_stock_time       IS '入库时间';
COMMENT ON COLUMN rev_water_meter.out_stock_time      IS '出库时间';
COMMENT ON COLUMN rev_water_meter.initial_reading     IS '初始读数';
COMMENT ON COLUMN rev_water_meter.current_reading     IS '当前读数';
COMMENT ON COLUMN rev_water_meter.dismantle_date      IS '拆除日期';
COMMENT ON COLUMN rev_water_meter.scrapped_date       IS '报废日期';

-- 2. 水表安装记录
CREATE TABLE IF NOT EXISTS rev_meter_install_record (
    id              BIGSERIAL       PRIMARY KEY,
    meter_no        VARCHAR(64)     NOT NULL,
    customer_no     VARCHAR(64),
    installer       VARCHAR(128),
    install_date    DATE,
    install_address VARCHAR(512),
    old_meter_no    VARCHAR(64),
    initial_reading NUMERIC(14,4)   DEFAULT 0,
    remark          VARCHAR(512),
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE rev_meter_install_record IS '水表安装记录';

-- 3. 水表换表记录
CREATE TABLE IF NOT EXISTS rev_meter_replace_record (
    id               BIGSERIAL       PRIMARY KEY,
    old_meter_no     VARCHAR(64)     NOT NULL,
    new_meter_no     VARCHAR(64)     NOT NULL,
    customer_no      VARCHAR(64),
    replace_type     VARCHAR(32)     NOT NULL,
    reason           VARCHAR(512),
    replacer         VARCHAR(128),
    replace_date     DATE,
    old_reading      NUMERIC(14,4),
    new_reading      NUMERIC(14,4),
    approval_status  VARCHAR(32)     DEFAULT 'APPROVED',
    approver         VARCHAR(128),
    approval_time    TIMESTAMP,
    remark           VARCHAR(512),
    created_at       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE  rev_meter_replace_record                  IS '水表换表记录';
COMMENT ON COLUMN rev_meter_replace_record.replace_type     IS '换表类型: FAULT-故障, EXPIRED-到期';
COMMENT ON COLUMN rev_meter_replace_record.approval_status  IS '审批状态: PENDING/APPROVED/REJECTED';

-- 4. 水表生命周期日志
CREATE TABLE IF NOT EXISTS rev_meter_lifecycle_log (
    id              BIGSERIAL       PRIMARY KEY,
    meter_no        VARCHAR(64)     NOT NULL,
    action_type     VARCHAR(32)     NOT NULL,
    action_time     TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    operator        VARCHAR(128),
    detail          VARCHAR(1024),
    old_meter_no    VARCHAR(64),
    new_meter_no    VARCHAR(64),
    customer_no     VARCHAR(64),
    remark          VARCHAR(512),
    created_at      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE  rev_meter_lifecycle_log                IS '水表生命周期日志';
COMMENT ON COLUMN rev_meter_lifecycle_log.action_type    IS '操作类型: STOCK_IN/INSTALL/DISMANTLE/REPLACE/SCRAP/REPAIR';

-- ============================================================
-- 索引
-- ============================================================

-- rev_water_meter 索引
CREATE INDEX IF NOT EXISTS idx_water_meter_status       ON rev_water_meter (status);
CREATE INDEX IF NOT EXISTS idx_water_meter_customer     ON rev_water_meter (customer_no);
CREATE INDEX IF NOT EXISTS idx_water_meter_diameter     ON rev_water_meter (diameter);
CREATE INDEX IF NOT EXISTS idx_water_meter_manufacturer ON rev_water_meter (manufacturer);
CREATE INDEX IF NOT EXISTS idx_water_meter_created      ON rev_water_meter (created_at);

-- rev_meter_install_record 索引
CREATE INDEX IF NOT EXISTS idx_install_record_meter     ON rev_meter_install_record (meter_no);
CREATE INDEX IF NOT EXISTS idx_install_record_customer  ON rev_meter_install_record (customer_no);
CREATE INDEX IF NOT EXISTS idx_install_record_date      ON rev_meter_install_record (install_date);

-- rev_meter_replace_record 索引
CREATE INDEX IF NOT EXISTS idx_replace_record_old       ON rev_meter_replace_record (old_meter_no);
CREATE INDEX IF NOT EXISTS idx_replace_record_new       ON rev_meter_replace_record (new_meter_no);
CREATE INDEX IF NOT EXISTS idx_replace_record_customer  ON rev_meter_replace_record (customer_no);
CREATE INDEX IF NOT EXISTS idx_replace_record_approval  ON rev_meter_replace_record (approval_status);
CREATE INDEX IF NOT EXISTS idx_replace_record_date      ON rev_meter_replace_record (replace_date);

-- rev_meter_lifecycle_log 索引
CREATE INDEX IF NOT EXISTS idx_lifecycle_log_meter      ON rev_meter_lifecycle_log (meter_no);
CREATE INDEX IF NOT EXISTS idx_lifecycle_log_action     ON rev_meter_lifecycle_log (action_type);
CREATE INDEX IF NOT EXISTS idx_lifecycle_log_time       ON rev_meter_lifecycle_log (action_time);
CREATE INDEX IF NOT EXISTS idx_lifecycle_log_customer   ON rev_meter_lifecycle_log (customer_no);