water-management-system/db/dma_ddl.sql

-- =====================================================
-- DMA分区计量与漏损分析 DDL
-- 数据库: PostgreSQL
-- =====================================================

-- DMA分区表
CREATE TABLE IF NOT EXISTS dma_zone (
    id              BIGSERIAL PRIMARY KEY,
    zone_name       VARCHAR(100) NOT NULL,
    zone_code       VARCHAR(50) NOT NULL UNIQUE,
    parent_id       BIGINT REFERENCES dma_zone(id),
    zone_level      INTEGER NOT NULL DEFAULT 1,
    area            VARCHAR(100),
    area_size       NUMERIC(10, 2),
    population      INTEGER,
    pipe_length     NUMERIC(10, 2),
    status          VARCHAR(20) DEFAULT 'active',
    remark          VARCHAR(500),
    deleted         INTEGER DEFAULT 0,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE dma_zone IS 'DMA分区表';
COMMENT ON COLUMN dma_zone.zone_level IS '分区层级: 1=一级/2=二级/3=三级';
COMMENT ON COLUMN dma_zone.status IS '状态: active/inactive';

-- DMA计量表
CREATE TABLE IF NOT EXISTS dma_meter (
    id              BIGSERIAL PRIMARY KEY,
    zone_id         BIGINT REFERENCES dma_zone(id),
    meter_code      VARCHAR(50) NOT NULL UNIQUE,
    meter_name      VARCHAR(100),
    meter_type      VARCHAR(20) NOT NULL,
    location        VARCHAR(200),
    longitude       NUMERIC(12, 8),
    latitude        NUMERIC(12, 8),
    caliber         INTEGER,
    brand           VARCHAR(100),
    status          VARCHAR(20) DEFAULT 'online',
    remark          VARCHAR(500),
    deleted         INTEGER DEFAULT 0,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE dma_meter IS 'DMA计量表';
COMMENT ON COLUMN dma_meter.meter_type IS '表计类型: inlet=进水表/outlet=出水表/boundary=边界表';
COMMENT ON COLUMN dma_meter.status IS '状态: online/offline/fault';

CREATE INDEX IF NOT EXISTS idx_meter_zone ON dma_meter(zone_id);

-- DMA流量记录表
CREATE TABLE IF NOT EXISTS dma_flow_record (
    id              BIGSERIAL PRIMARY KEY,
    zone_id         BIGINT NOT NULL REFERENCES dma_zone(id),
    meter_id        BIGINT NOT NULL REFERENCES dma_meter(id),
    instant_flow    NUMERIC(12, 4),
    total_flow      NUMERIC(14, 4),
    pressure        NUMERIC(8, 4),
    collect_time    TIMESTAMP NOT NULL,
    data_quality    VARCHAR(20) DEFAULT 'good',
    deleted         INTEGER DEFAULT 0,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE dma_flow_record IS 'DMA流量记录表';
COMMENT ON COLUMN dma_flow_record.instant_flow IS '瞬时流量(m³/h)';
COMMENT ON COLUMN dma_flow_record.total_flow IS '累计流量(m³)';
COMMENT ON COLUMN dma_flow_record.pressure IS '压力(MPa)';
COMMENT ON COLUMN dma_flow_record.data_quality IS '数据质量: good/bad/missing';

CREATE INDEX IF NOT EXISTS idx_flow_zone_time ON dma_flow_record(zone_id, collect_time);
CREATE INDEX IF NOT EXISTS idx_flow_meter_time ON dma_flow_record(meter_id, collect_time);

-- DMA漏损分析表
CREATE TABLE IF NOT EXISTS dma_leakage_analysis (
    id                  BIGSERIAL PRIMARY KEY,
    zone_id             BIGINT NOT NULL REFERENCES dma_zone(id),
    analysis_date       DATE NOT NULL,
    supply_volume       NUMERIC(14, 4),
    sale_volume         NUMERIC(14, 4),
    leakage_volume      NUMERIC(14, 4),
    nrw_rate            NUMERIC(8, 2),
    leakage_rate        NUMERIC(8, 2),
    mnf                 NUMERIC(10, 4),
    mnf_time            VARCHAR(20),
    background_leakage  NUMERIC(10, 4),
    burst_leakage       NUMERIC(10, 4),
    alarm_level         VARCHAR(20) DEFAULT 'normal',
    remark              VARCHAR(500),
    deleted             INTEGER DEFAULT 0,
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE dma_leakage_analysis IS 'DMA漏损分析表';
COMMENT ON COLUMN dma_leakage_analysis.nrw_rate IS '产销差率(%)';
COMMENT ON COLUMN dma_leakage_analysis.leakage_rate IS '漏损率(%)';
COMMENT ON COLUMN dma_leakage_analysis.mnf IS '最小夜间流量(m³/h)';
COMMENT ON COLUMN dma_leakage_analysis.alarm_level IS '报警级别: normal/warning/critical';

CREATE INDEX IF NOT EXISTS idx_leakage_zone_date ON dma_leakage_analysis(zone_id, analysis_date);
CREATE UNIQUE INDEX IF NOT EXISTS uk_leakage_zone_date ON dma_leakage_analysis(zone_id, analysis_date) WHERE deleted = 0;

-- 水平衡表
CREATE TABLE IF NOT EXISTS dma_water_balance (
    id                  BIGSERIAL PRIMARY KEY,
    zone_id             BIGINT NOT NULL REFERENCES dma_zone(id),
    period              VARCHAR(20) NOT NULL,
    stat_date           DATE NOT NULL,
    total_supply        NUMERIC(14, 4),
    total_sale          NUMERIC(14, 4),
    billing_sale        NUMERIC(14, 4),
    free_supply         NUMERIC(14, 4),
    apparent_loss       NUMERIC(14, 4),
    real_loss           NUMERIC(14, 4),
    background_loss     NUMERIC(14, 4),
    burst_loss          NUMERIC(14, 4),
    total_loss          NUMERIC(14, 4),
    nrw_rate            NUMERIC(8, 2),
    leakage_rate        NUMERIC(8, 2),
    remark              VARCHAR(500),
    deleted             INTEGER DEFAULT 0,
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE dma_water_balance IS '水平衡表';
COMMENT ON COLUMN dma_water_balance.period IS '统计周期: daily/monthly/yearly';
COMMENT ON COLUMN dma_water_balance.apparent_loss IS '表观漏损(m³) - 计量误差+偷水';
COMMENT ON COLUMN dma_water_balance.real_loss IS '实际漏损(m³) - 物理漏损';

CREATE INDEX IF NOT EXISTS idx_balance_zone_date ON dma_water_balance(zone_id, stat_date);
CREATE INDEX IF NOT EXISTS idx_balance_period ON dma_water_balance(period);