---- 通信模块表
DROP TABLE IF EXISTS config_transmit;

CREATE TABLE config_transmit
(
    id             bigserial    NOT NULL,
    name           varchar(50)  NOT NULL,
    transmit_class varchar(100) NOT NULL DEFAULT '',
    config_example varchar(500) NOT NULL DEFAULT '',
    PRIMARY KEY (id),
    CONSTRAINT uq_config_transmit_1 UNIQUE (name)
);

COMMENT
ON TABLE config_transmit IS '通信模块';
COMMENT
ON COLUMN config_transmit.id IS '主键';
COMMENT
ON COLUMN config_transmit.name IS '名称';
COMMENT
ON COLUMN config_transmit.transmit_class IS '类全名';
COMMENT
ON COLUMN config_transmit.config_example IS '配置示例';

---- 协议模块表
DROP TABLE IF EXISTS config_protocol;

CREATE TABLE config_protocol
(
    id             bigserial    NOT NULL,
    name           varchar(50)  NOT NULL,
    protocol_class varchar(100) NOT NULL,
    config_example varchar(500) NOT NULL DEFAULT '',
    PRIMARY KEY (id),
    CONSTRAINT uq_config_protocol_1 UNIQUE (name)
);

COMMENT
ON TABLE config_protocol IS '协议模块';
COMMENT
ON COLUMN config_protocol.id IS '主键';
COMMENT
ON COLUMN config_protocol.name IS '名称';
COMMENT
ON COLUMN config_protocol.protocol_class IS '全类名';
COMMENT
ON COLUMN config_protocol.config_example IS '配置示例';

---- 设备通道表
DROP TABLE IF EXISTS config_channel;

CREATE TABLE config_channel
(
    id                 bigserial    NOT NULL,
    code               varchar(15)  NOT NULL DEFAULT '',
    name               varchar(50)  NOT NULL,
    disabled           char(1)      NOT NULL DEFAULT '0',
    config_transmit_id bigint       NOT NULL,
    transmit_config    varchar(500) NOT NULL DEFAULT '',
    config_protocol_id bigint       NOT NULL,
    protocol_config    varchar(500) NOT NULL DEFAULT '',
    remark             varchar(200) NOT NULL DEFAULT '',
    create_time        timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time        timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    create_by          varchar(25)  NOT NULL DEFAULT '',
    update_by          varchar(25)  NOT NULL DEFAULT '',
    PRIMARY KEY (id)
);
COMMENT
ON TABLE config_channel IS '设备通道表';
COMMENT
ON COLUMN config_channel.id IS '主键';
COMMENT
ON COLUMN config_channel.code IS '编号';
COMMENT
ON COLUMN config_channel.name IS '名称';
COMMENT
ON COLUMN config_channel.disabled IS '停用状态';
COMMENT
ON COLUMN config_channel.config_transmit_id IS '通信模块';
COMMENT
ON COLUMN config_channel.transmit_config IS '通信配置';
COMMENT
ON COLUMN config_channel.config_protocol_id IS '协议模块';
COMMENT
ON COLUMN config_channel.protocol_config IS '协议配置';
COMMENT
ON COLUMN config_channel.remark IS '备注';
COMMENT
ON COLUMN config_channel.create_time IS '创建时间';
COMMENT
ON COLUMN config_channel.update_time IS '修改时间';
COMMENT
ON COLUMN config_channel.create_by IS '创建者';
COMMENT
ON COLUMN config_channel.update_by IS '更新者';

---- 设备表
DROP TABLE IF EXISTS config_device;

CREATE TABLE config_device
(
    id                BIGSERIAL PRIMARY KEY,
    name              VARCHAR(100) NOT NULL,
    config_channel_id BIGINT                DEFAULT NULL,
    config_station_id VARCHAR(32)           DEFAULT NULL,
    tp VARCHAR(10),
    brand VARCHAR(100),
    model VARCHAR(100),
    serial_number VARCHAR(100),
    status VARCHAR(50),
    install_date date,
    config_device_id  BIGINT                DEFAULT NULL,
    remark            VARCHAR(200) NOT NULL DEFAULT '',
    create_time        timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time        timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    create_by          varchar(25)  NOT NULL DEFAULT '',
    update_by          varchar(25)  NOT NULL DEFAULT ''
);

-- 添加注释
COMMENT
ON TABLE config_device IS '设备信息';

COMMENT ON COLUMN config_device.id IS '主键';
COMMENT ON COLUMN config_device.name IS '名称';
COMMENT ON COLUMN config_device.config_channel_id IS '通道';
COMMENT ON COLUMN config_device.config_station_id IS '场站';
COMMENT ON COLUMN config_device.tp IS '设备类型,WEATHER=气象设备, AGVC=agvc设备';
COMMENT ON COLUMN config_device.brand IS '品牌';
COMMENT ON COLUMN config_device.model IS '型号';
COMMENT ON COLUMN config_device.serial_number IS '序列号';
COMMENT ON COLUMN config_device.status IS '状态';
COMMENT ON COLUMN config_device.install_date IS '投运日期';
COMMENT ON COLUMN config_device.config_device_id IS '父级设备';
COMMENT ON COLUMN config_device.remark IS '备注';

-- 创建唯一索引
CREATE UNIQUE INDEX uq_config_device_1 ON config_device (name);

---- 测点表
DROP TABLE IF EXISTS config_point;

CREATE TABLE config_point
(
    id                       BIGSERIAL PRIMARY KEY,
    name                     VARCHAR(100)     NOT NULL,
    code                     VARCHAR(25)      NOT NULL DEFAULT '',
    addr                     INT              NOT NULL DEFAULT 0,
    category                 CHAR(2)          NOT NULL DEFAULT '0',
    config_device_id         BIGINT           NOT NULL,
    init_value               VARCHAR(10)      NOT NULL DEFAULT '0',
    will_inverse             CHAR(1)          NOT NULL,
    kb_composite             CHAR(1)          NOT NULL,
    k                        DOUBLE PRECISION NOT NULL DEFAULT 1,
    b                        DOUBLE PRECISION NOT NULL,
    zero_filter              DOUBLE PRECISION NOT NULL DEFAULT 0,
    will_check_ud            CHAR(1)          NOT NULL DEFAULT '0',
    up1                      DOUBLE PRECISION NOT NULL DEFAULT 0,
    up2                      DOUBLE PRECISION NOT NULL DEFAULT 0,
    down1                    DOUBLE PRECISION NOT NULL DEFAULT 0,
    down2                    DOUBLE PRECISION NOT NULL DEFAULT 0,
    hop_filter               INT              NOT NULL DEFAULT 0,
    lazy_tm                  INT              NOT NULL DEFAULT 0,
    config_alarm_template_id BIGINT                    DEFAULT NULL,
    create_time              TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time              TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    create_by                VARCHAR(25)               DEFAULT '',
    update_by                VARCHAR(25)               DEFAULT ''
);

-- 添加注释
COMMENT
ON TABLE config_point IS '测点表';

COMMENT
ON COLUMN config_point.id IS '主键';
COMMENT
ON COLUMN config_point.name IS '名称';
COMMENT
ON COLUMN config_point.code IS '编码';
COMMENT
ON COLUMN config_point.addr IS '地址';
COMMENT
ON COLUMN config_point.category IS '测点类型';
COMMENT
ON COLUMN config_point.config_device_id IS '设备号';
COMMENT
ON COLUMN config_point.init_value IS '初始值';
COMMENT
ON COLUMN config_point.will_inverse IS '是否取反';
COMMENT
ON COLUMN config_point.kb_composite IS '计算方式';
COMMENT
ON COLUMN config_point.k IS 'K';
COMMENT
ON COLUMN config_point.b IS 'B';
COMMENT
ON COLUMN config_point.zero_filter IS '零漂';
COMMENT
ON COLUMN config_point.will_check_ud IS '越限检查';
COMMENT
ON COLUMN config_point.up1 IS '上限1';
COMMENT
ON COLUMN config_point.up2 IS '上限2';
COMMENT
ON COLUMN config_point.down1 IS '下限1';
COMMENT
ON COLUMN config_point.down2 IS '下限2';
COMMENT
ON COLUMN config_point.hop_filter IS '滤波';
COMMENT
ON COLUMN config_point.lazy_tm IS '呆值时间';
COMMENT
ON COLUMN config_point.config_alarm_template_id IS '报警模板id';
COMMENT
ON COLUMN config_point.create_time IS '创建时间';
COMMENT
ON COLUMN config_point.update_time IS '修改时间';
COMMENT
ON COLUMN config_point.create_by IS '创建者';
COMMENT
ON COLUMN config_point.update_by IS '更新者';

-- 创建唯一索引
CREATE UNIQUE INDEX uq_configdeviceid_addr ON config_point (addr, config_device_id);


---- 本地气象数据
drop table if exists data_weather_local;
CREATE TABLE data_weather_local
(
    tm                    TIMESTAMPTZ NOT NULL,
    config_station_id     bigint      NOT NULL,
    irradiance DOUBLE PRECISION,
    inclined_irradiance   DOUBLE PRECISION,
    daily_irradiance      DOUBLE PRECISION,
    temperature           DOUBLE PRECISION,
    board_temperature     DOUBLE PRECISION,
    pressure              DOUBLE PRECISION,
    humidity              DOUBLE PRECISION,
    wind_speed            DOUBLE PRECISION,
    wind_direction        DOUBLE PRECISION
) WITH (
      tsdb.hypertable,
      tsdb.partition_column = 'tm',
      tsdb.segmentby = 'config_station_id',
      tsdb.chunk_interval = '1 month',
      tsdb.orderby = 'tm DESC'
      );

COMMENT
ON COLUMN data_weather_local.tm          IS '时间戳（带时区）';
COMMENT
ON COLUMN data_weather_local.config_station_id           IS '场站ID';
COMMENT
ON COLUMN data_weather_local.irradiance  IS '水平面总辐照度，单位 W/m²';
COMMENT
ON COLUMN data_weather_local.inclined_irradiance    IS '斜面辐照度，单位 W/m²';
COMMENT
ON COLUMN data_weather_local.daily_irradiance    IS '日累计辐照度，单位 W/m²';
COMMENT
ON COLUMN data_weather_local.temperature         IS '温度，单位 ℃';
COMMENT
ON COLUMN data_weather_local.board_temperature         IS '组件温度，单位 ℃';
COMMENT
ON COLUMN data_weather_local.pressure            IS '气压，单位 hPa';
COMMENT
ON COLUMN data_weather_local.humidity            IS '湿度，单位 %';
COMMENT
ON COLUMN data_weather_local.wind_speed          IS '风速，单位 m/s';
COMMENT
ON COLUMN data_weather_local.wind_direction      IS '风向，北向为0，顺时针，单位 °（0~360）';

---- 配置data_weather_local中的气象信息来源
DROP TABLE IF EXISTS config_weather_local;
CREATE TABLE IF NOT EXISTS config_weather_local
(
    id BIGSERIAL PRIMARY KEY,
    config_station_id bigint NOT NULL,
    irradiance json,
    inclined_irradiance json,
    daily_irradiance json,
    temperature json,
    board_temperature json,
    pressure json,
    humidity json,
    wind_speed json,
    wind_direction json,
    create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    create_by varchar(25) NOT NULL DEFAULT '',
    update_by varchar(25) NOT NULL DEFAULT ''
    );
COMMENT
ON TABLE config_weather_local IS '配置data_electricity_local中的电气量来源';
COMMENT
ON COLUMN config_weather_local.id IS '主键';
COMMENT
ON COLUMN config_weather_local.config_station_id IS '场站id';
COMMENT
ON COLUMN config_weather_local.irradiance IS '总辐照度测点配置
{
deviceId:xxx
pointType:YC|YX|YM
pointCode:xxx
}';
COMMENT
ON COLUMN config_weather_local.inclined_irradiance IS '斜面辐照度测点配置';
COMMENT
ON COLUMN config_weather_local.daily_irradiance IS '日辐射度累计值';
COMMENT
ON COLUMN config_weather_local.temperature IS '温度测点配置';
COMMENT
ON COLUMN config_weather_local.board_temperature IS '组件温度测点配置';
COMMENT
ON COLUMN config_weather_local.pressure IS '气压测点配置';
COMMENT
ON COLUMN config_weather_local.humidity IS '湿度测点配置';
COMMENT
ON COLUMN config_weather_local.wind_speed IS '风速测点配置';
COMMENT
ON COLUMN config_weather_local.wind_direction IS '风向测点配置';
COMMENT
ON COLUMN config_weather_local.create_time IS '创建时间';
COMMENT
ON COLUMN config_weather_local.update_time IS '修改时间';
COMMENT
ON COLUMN config_weather_local.create_by IS '创建者';
COMMENT
ON COLUMN config_weather_local.update_by IS '更新者';

---- 场站电气采样数据
DROP TABLE IF EXISTS data_electricity_local;
CREATE TABLE data_electricity_local
(
    tm                TIMESTAMPTZ NOT NULL,
    config_station_id bigint      NOT NULL,
    actual_power      DOUBLE PRECISION,
    theoretical_power DOUBLE PRECISION,
    curtailment_power DOUBLE PRECISION,
    availability      DOUBLE PRECISION,
    device_status     SMALLINT,
    grid_status       SMALLINT
) with (
      tsdb.hypertable,
      tsdb.partition_column = 'tm',
      tsdb.chunk_interval = '1 month',
      tsdb.orderby = 'tm DESC'
      );
COMMENT
ON COLUMN data_electricity_local.config_station_id IS '光伏场站ID';
COMMENT
ON COLUMN data_electricity_local.tm IS '时间戳（带时区）';
COMMENT
ON COLUMN data_electricity_local.actual_power IS '实际发电功率（kW）';
COMMENT
ON COLUMN data_electricity_local.theoretical_power IS '理论发电功率（kW）';
COMMENT
ON COLUMN data_electricity_local.curtailment_power IS '限电功率（kW）';
COMMENT
ON COLUMN data_electricity_local.device_status IS '设备状态（0=正常，1=停机，2=限电，3=检修，4=故障,只要一个处于异常就设为某个异常）';
COMMENT
ON COLUMN data_electricity_local.grid_status IS '并网状态（1=并网，0=离网）';
COMMENT
ON COLUMN data_electricity_local.availability IS '可用率（%）';

---- 配置data_electricity_local中的电气量来源
DROP TABLE IF EXISTS config_electricity_local;
CREATE TABLE IF NOT EXISTS config_electricity_local
(
    id BIGSERIAL PRIMARY KEY,
    config_station_id bigint NOT NULL,
    actual_power json,
    theoretical_power json,
    curtailment_power json,
    device_status json,
    grid_status json,
    availability json,
    create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    create_by varchar(25) NOT NULL DEFAULT '',
    update_by varchar(25) NOT NULL DEFAULT ''
);
COMMENT
ON TABLE config_electricity_local IS '配置data_electricity_local中的电气量来源';
COMMENT
ON COLUMN config_electricity_local.id IS '主键';
COMMENT
ON COLUMN config_electricity_local.config_station_id IS '场站id';
COMMENT
ON COLUMN config_electricity_local.actual_power IS '实际发电功率测点配置
{
device_id:1
point_category:yc
point_code:xxx
}';
COMMENT
ON COLUMN config_electricity_local.theoretical_power IS '理论发电功率测点配置';
COMMENT
ON COLUMN config_electricity_local.curtailment_power IS '限电功率测点配置';
COMMENT
ON COLUMN config_electricity_local.device_status IS '设备状态测点配置';
COMMENT
ON COLUMN config_electricity_local.grid_status IS '并网状态测点配置';
COMMENT
ON COLUMN config_electricity_local.availability IS '可用率测点配置';
COMMENT
ON COLUMN config_electricity_local.create_time IS '创建时间';
COMMENT
ON COLUMN config_electricity_local.update_time IS '修改时间';
COMMENT
ON COLUMN config_electricity_local.create_by IS '创建者';
COMMENT
ON COLUMN config_electricity_local.update_by IS '更新者';

---- 遥调
DROP TABLE IF EXISTS config_yt;
CREATE TABLE config_yt
(
    id               BIGSERIAL PRIMARY KEY,
    name             VARCHAR(50)  NOT NULL,
    config_device_id BIGINT       NOT NULL,
    addr_code        VARCHAR(25)  NOT NULL,
    fun              VARCHAR(20)  NOT NULL DEFAULT '',
    control_val      VARCHAR(25)  NOT NULL,
    tm_out           INTEGER      NOT NULL,
    param            VARCHAR(50)  NOT NULL DEFAULT '',
    remark           VARCHAR(200) NOT NULL DEFAULT '',
    create_time      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    create_by        VARCHAR(25)           DEFAULT '',
    update_by        VARCHAR(25)           DEFAULT ''
);
-- 添加表注释
COMMENT
ON TABLE config_yt IS '遥调';
-- 添加列注释
COMMENT
ON COLUMN config_yt.id IS '主键';
COMMENT
ON COLUMN config_yt.name IS '名称';
COMMENT
ON COLUMN config_yt.config_device_id IS '设备';
COMMENT
ON COLUMN config_yt.addr_code IS '地址编号';
COMMENT
ON COLUMN config_yt.fun IS '功能码';
COMMENT
ON COLUMN config_yt.control_val IS '初始值';
COMMENT
ON COLUMN config_yt.tm_out IS '超时时间';
COMMENT
ON COLUMN config_yt.param IS '参数';
COMMENT
ON COLUMN config_yt.remark IS '备注';
COMMENT
ON COLUMN config_yt.create_time IS '创建时间';
COMMENT
ON COLUMN config_yt.update_time IS '修改时间';
COMMENT
ON COLUMN config_yt.create_by IS '创建者';
COMMENT
ON COLUMN config_yt.update_by IS '更新者';

---- 遥调日志表
DROP TABLE IF EXISTS biz_yt_log;

CREATE TABLE biz_yt_log
(
    id               BIGSERIAL PRIMARY KEY,
    config_yt_id     BIGINT       NOT NULL,
    config_device_id BIGINT       NOT NULL,
    addr_code        VARCHAR(25)  NOT NULL,
    control_val      VARCHAR(25)  NOT NULL,
    tm_out           INTEGER      NOT NULL,
    fun              VARCHAR(20)  NOT NULL DEFAULT '',
    param            VARCHAR(50)  NOT NULL DEFAULT '',
    tm_operate       TIMESTAMP    NOT NULL,
    sys_user_id      BIGINT       NOT NULL,
    state            CHAR(2)      NOT NULL,
    remark           VARCHAR(200) NOT NULL DEFAULT '',
    create_time      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    create_by        VARCHAR(25)           DEFAULT '',
    update_by        VARCHAR(25)           DEFAULT ''
);
-- 添加表注释
COMMENT
ON TABLE biz_yt_log IS '遥调日志';
-- 添加列注释
COMMENT
ON COLUMN biz_yt_log.id IS '主键';
COMMENT
ON COLUMN biz_yt_log.config_yt_id IS '遥调';
COMMENT
ON COLUMN biz_yt_log.config_device_id IS '设备';
COMMENT
ON COLUMN biz_yt_log.addr_code IS '地址编号';
COMMENT
ON COLUMN biz_yt_log.control_val IS '值';
COMMENT
ON COLUMN biz_yt_log.tm_out IS '超时时间ms';
COMMENT
ON COLUMN biz_yt_log.fun IS '参数';
COMMENT
ON COLUMN biz_yt_log.param IS '参数';
COMMENT
ON COLUMN biz_yt_log.tm_operate IS '操作时间';
COMMENT
ON COLUMN biz_yt_log.sys_user_id IS '操作人';
COMMENT
ON COLUMN biz_yt_log.state IS '状态';
COMMENT
ON COLUMN biz_yt_log.remark IS '备注';
COMMENT
ON COLUMN biz_yt_log.create_time IS '创建时间';
COMMENT
ON COLUMN biz_yt_log.update_time IS '修改时间';
COMMENT
ON COLUMN biz_yt_log.create_by IS '创建者';
COMMENT
ON COLUMN biz_yt_log.update_by IS '更新者';

---- 遥控表
DROP TABLE IF EXISTS config_yk;

CREATE TABLE config_yk
(
    id                BIGSERIAL PRIMARY KEY,
    name              VARCHAR(50)  NOT NULL,
    config_device_id  BIGINT       NOT NULL,
    addr_code         VARCHAR(25)  NOT NULL,
    control_step      CHAR(1)      NOT NULL,
    will_wardship     CHAR(1)      NOT NULL,
    control_type      CHAR(2)      NOT NULL,
    fun               VARCHAR(20)  NOT NULL DEFAULT '',
    param             VARCHAR(50)  NOT NULL DEFAULT '',
    tm_out            INTEGER      NOT NULL,
    remark            VARCHAR(200) NOT NULL DEFAULT '',
    prec_check        CHAR(1)      NOT NULL,
    prec_dev_point_id BIGINT NULL DEFAULT NULL,
    prec_value        INTEGER      NOT NULL DEFAULT 0,
    postc_check       CHAR(1)      NOT NULL,
    preo_check        CHAR(1)      NOT NULL,
    preo_dev_point_id BIGINT NULL DEFAULT NULL,
    preo_value        INTEGER      NOT NULL DEFAULT 0,
    posto_check       CHAR(1)      NOT NULL,
    create_time       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    create_by         VARCHAR(25)           DEFAULT '',
    update_by         VARCHAR(25)           DEFAULT ''
);
-- 添加表注释
COMMENT
ON TABLE config_yk IS '遥控';
-- 添加列注释
COMMENT
ON COLUMN config_yk.id IS '主键';
COMMENT
ON COLUMN config_yk.name IS '名称';
COMMENT
ON COLUMN config_yk.config_device_id IS '设备';
COMMENT
ON COLUMN config_yk.addr_code IS '地址编号';
COMMENT
ON COLUMN config_yk.control_step IS '单双步';
COMMENT
ON COLUMN config_yk.will_wardship IS '是否监护';
COMMENT
ON COLUMN config_yk.control_type IS '控开合';
COMMENT
ON COLUMN config_yk.fun IS '功能码';
COMMENT
ON COLUMN config_yk.param IS '附带参数';
COMMENT
ON COLUMN config_yk.tm_out IS '超时时间';
COMMENT
ON COLUMN config_yk.remark IS '备注';
COMMENT
ON COLUMN config_yk.prec_check IS '合前校';
COMMENT
ON COLUMN config_yk.prec_dev_point_id IS '合点号';
COMMENT
ON COLUMN config_yk.prec_value IS '合预期';
COMMENT
ON COLUMN config_yk.postc_check IS '合反校';
COMMENT
ON COLUMN config_yk.preo_check IS '开前校';
COMMENT
ON COLUMN config_yk.preo_dev_point_id IS '开点号';
COMMENT
ON COLUMN config_yk.preo_value IS '开预期';
COMMENT
ON COLUMN config_yk.posto_check IS '开反校';
COMMENT
ON COLUMN config_yk.create_time IS '创建时间';
COMMENT
ON COLUMN config_yk.update_time IS '修改时间';
COMMENT
ON COLUMN config_yk.create_by IS '创建者';
COMMENT
ON COLUMN config_yk.update_by IS '更新者';

---- 遥控日志
DROP TABLE IF EXISTS biz_yk_log;

CREATE TABLE biz_yk_log
(
    id               BIGSERIAL PRIMARY KEY,
    config_yk_id     BIGINT       NOT NULL,
    config_device_id BIGINT       NOT NULL,
    addr_code        VARCHAR(25)  NOT NULL,
    control_val      VARCHAR(25)  NOT NULL,
    tm_out           INTEGER      NOT NULL,
    fun              VARCHAR(20)  NOT NULL DEFAULT '',
    param            VARCHAR(50)  NOT NULL DEFAULT '',
    control_step     CHAR(1)               DEFAULT 'Y',
    tm_operate       TIMESTAMP    NOT NULL,
    sys_user_id      BIGINT       NOT NULL,
    state            CHAR(2)      NOT NULL,
    remark           VARCHAR(200) NOT NULL DEFAULT '',
    create_time      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    create_by        VARCHAR(25)           DEFAULT '',
    update_by        VARCHAR(25)           DEFAULT ''
);

-- 添加表注释
COMMENT
ON TABLE biz_yk_log IS '遥控日志';

-- 添加列注释
COMMENT
ON COLUMN biz_yk_log.id IS '主键';
COMMENT
ON COLUMN biz_yk_log.config_yk_id IS '遥调';
COMMENT
ON COLUMN biz_yk_log.config_device_id IS '设备';
COMMENT
ON COLUMN biz_yk_log.addr_code IS '地址编号';
COMMENT
ON COLUMN biz_yk_log.control_val IS '值';
COMMENT
ON COLUMN biz_yk_log.tm_out IS '超时时间';
COMMENT
ON COLUMN biz_yk_log.fun IS '功能码';
COMMENT
ON COLUMN biz_yk_log.param IS '参数';
COMMENT
ON COLUMN biz_yk_log.control_step IS '单双步';
COMMENT
ON COLUMN biz_yk_log.tm_operate IS '操作时间';
COMMENT
ON COLUMN biz_yk_log.sys_user_id IS '操作人';
COMMENT
ON COLUMN biz_yk_log.state IS '状态';
COMMENT
ON COLUMN biz_yk_log.remark IS '备注';
COMMENT
ON COLUMN biz_yk_log.create_time IS '创建时间';
COMMENT
ON COLUMN biz_yk_log.update_time IS '修改时间';
COMMENT
ON COLUMN biz_yk_log.create_by IS '创建者';
COMMENT
ON COLUMN biz_yk_log.update_by IS '更新者';

-- 上报日志表 data_report_log
drop table if exists data_report_log;
CREATE TABLE data_report_log (
     id BIGSERIAL PRIMARY KEY,
     curve_id BIGINT NOT NULL,
     report_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
     target_dispatch VARCHAR(64) NOT NULL,
     channel SMALLINT NOT NULL,
     message_no VARCHAR(128) NOT NULL,
     content_path VARCHAR(256),
     status SMALLINT NOT NULL,
     response_msg TEXT,
     remark varchar(200),
     create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
     update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
     create_by varchar(25) NOT NULL DEFAULT '',
     update_by varchar(25) NOT NULL DEFAULT '',
    -- 索引：按曲线查上报记录、按上报时间检索
     CONSTRAINT uk_report_log_msg_no UNIQUE(message_no)
);

-- 注释（PG专用COMMENT）
COMMENT ON TABLE data_report_log IS '功率预测上报日志表，记录每次向电网调度上报预测曲线';
COMMENT ON COLUMN data_report_log.id IS '日志主键ID';
COMMENT ON COLUMN data_report_log.curve_id IS '关联预测曲线ID(forecast_curve)';
COMMENT ON COLUMN data_report_log.report_time IS '上报发起时间';
COMMENT ON COLUMN data_report_log.target_dispatch IS '目标调度机构：省调/地调名称';
COMMENT ON COLUMN data_report_log.channel IS '上报通道：1=IEC104，2=XML文件，3=FTP，4=MQTT';
COMMENT ON COLUMN data_report_log.message_no IS '报文流水号，对外唯一';
COMMENT ON COLUMN data_report_log.content_path IS '报文原文存储路径';
COMMENT ON COLUMN data_report_log.status IS '上报状态：0待发送，1上报成功，2上报失败，3超时';
COMMENT ON COLUMN data_report_log.response_msg IS '调度侧返回报文/反馈信息';
COMMENT ON COLUMN data_report_log.remark IS '备注，失败原因描述';
COMMENT ON COLUMN data_report_log.create_time IS '记录创建时间';
COMMENT ON COLUMN data_report_log.update_time IS '记录更新时间';

-- 调度回执确认表 data_dispatch_confirm
DROP TABLE IF EXISTS data_dispatch_confirm;
CREATE TABLE data_dispatch_confirm (
   id BIGSERIAL PRIMARY KEY,
   curve_id BIGINT NOT NULL,
   data_report_log_id BIGINT,
   confirm_time TIMESTAMPTZ NOT NULL,
   confirm_result SMALLINT NOT NULL,
   feedback_info TEXT,
   dispatch_msg_no VARCHAR(128),
   create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   create_by varchar(25) NOT NULL DEFAULT '',
   update_by varchar(25) NOT NULL DEFAULT ''
);

-- 字段注释
COMMENT ON TABLE data_dispatch_confirm IS '调度系统回执确认表，存储调度对预测曲线上报的接收/拒收反馈';
COMMENT ON COLUMN data_dispatch_confirm.id IS '回执主键ID';
COMMENT ON COLUMN data_dispatch_confirm.curve_id IS '关联预测曲线ID(forecast_curve)';
COMMENT ON COLUMN data_dispatch_confirm.data_report_log_id IS '关联上报日志ID(report_log)，可选';
COMMENT ON COLUMN data_dispatch_confirm.confirm_time IS '调度回执接收时间';
COMMENT ON COLUMN data_dispatch_confirm.confirm_result IS '回执结果：0=待回执，1=接收成功，2=拒收，3=数据修正';
COMMENT ON COLUMN data_dispatch_confirm.feedback_info IS '调度反馈详细说明、拒绝原因';
COMMENT ON COLUMN data_dispatch_confirm.dispatch_msg_no IS '调度侧报文流水号';