LvLiangDataUpload/TimedUpload/PushRecord_CreateTable.sql

-- 每月推送记录表（字段对应"每月推送模版表xlsx.xlsx"，列名与数据库视图 V_WaterMetersAll 字段名一致）
-- 数据库：Mul_AMRS_NBManager
--
-- 说明：本表为推送流水(追加写入，不更新/不删除历史)。防重粒度 = 水表编号 ElecAddress（空则 UserNo）
--       + 抄表月份 ReadingMonth + 读数 NowReading + 抄表时间 NowRadingDT（即"读数版本"）。
--   1) 同一读数版本本月已成功推过：重跑直接跳过，不重复上报；
--   2) 月内档案表读数更新(读数或抄表时间变化)：视为新版本，再次推送并新增一条成功流水；
--      即同一块表同一个月允许存在多条成功记录，分别对应每次推送时的读数版本；
--   3) 推送失败：同样新增失败流水，下次执行按版本自动补传；
--   4) 因此表上【不建唯一索引】，仅保留普通查询索引。
IF OBJECT_ID(N'dbo.PushRecord', N'U') IS NOT NULL
    DROP TABLE dbo.PushRecord;
GO

CREATE TABLE dbo.PushRecord
(
    Id              BIGINT IDENTITY(1,1) PRIMARY KEY,   -- 自增主键
    UserNo          NVARCHAR(50)  NULL,                -- 用户编号(对应V_WaterMetersAll.UserNo)
    UserName        NVARCHAR(100) NULL,                -- 用户名称(对应V_WaterMetersAll.UserName)
    totalAddr       NVARCHAR(500) NULL,                -- 用户地址(对应V_WaterMetersAll.totalAddr)
    ElecAddress     NVARCHAR(50)  NULL,                -- 水表编号(对应V_WaterMetersAll.ElecAddress)
    PhoneNumber     NVARCHAR(50)  NULL,                -- 用户手机号(对应V_WaterMetersAll.PhoneNumber)
    NowRadingDT     DATETIME      NULL,                -- 实时推送日期/抄表日期(对应V_WaterMetersAll.NowRadingDT)
    ReadingMonth    NVARCHAR(10)  NULL,                -- 抄表月yyyyMM(无对应数据库字段,由推送时间生成)
    NowReading       INT           NULL,                -- 本地读数(对应V_WaterMetersAll.NowReading)
    UploadResult    NVARCHAR(20)  NULL,                -- 上传结果(成功/失败)
    ResultMsg       NVARCHAR(500) NULL,                -- 接口返回信息
    UploadTime      DATETIME      NULL DEFAULT(GETDATE()), -- 记录写入时间
    Remark          NVARCHAR(500) NULL                 -- 备注
);
GO

-- 普通查询索引（按月统计、按用户查询；流水表不加唯一约束）
-- 建议另建 (ReadingMonth, ElecAddress) 复合索引以加速程序启动时的已推送版本查询：
CREATE INDEX IX_PushRecord_ReadingMonth ON dbo.PushRecord(ReadingMonth);
CREATE INDEX IX_PushRecord_UserNo       ON dbo.PushRecord(UserNo);
CREATE INDEX IX_PushRecord_Month_Meter  ON dbo.PushRecord(ReadingMonth, ElecAddress) INCLUDE (NowReading, NowRadingDT);
GO

-- 表及列注释（SQL Server 扩展属性）
EXEC sp_addextendedproperty @name=N'MS_Description', @value=N'每月推送记录表（字段对应每月推送模版表，列名与数据库视图V_WaterMetersAll一致）', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PushRecord';
GO

EXEC sp_addextendedproperty @name=N'MS_Description', @value=N'自增主键', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PushRecord', @level2type=N'COLUMN', @level2name=N'Id';
GO
EXEC sp_addextendedproperty @name=N'MS_Description', @value=N'用户编号(对应V_WaterMetersAll.UserNo)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PushRecord', @level2type=N'COLUMN', @level2name=N'UserNo';
GO
EXEC sp_addextendedproperty @name=N'MS_Description', @value=N'用户名称(对应V_WaterMetersAll.UserName)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PushRecord', @level2type=N'COLUMN', @level2name=N'UserName';
GO
EXEC sp_addextendedproperty @name=N'MS_Description', @value=N'用户地址(对应V_WaterMetersAll.totalAddr)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PushRecord', @level2type=N'COLUMN', @level2name=N'totalAddr';
GO
EXEC sp_addextendedproperty @name=N'MS_Description', @value=N'水表编号(对应V_WaterMetersAll.ElecAddress)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PushRecord', @level2type=N'COLUMN', @level2name=N'ElecAddress';
GO
EXEC sp_addextendedproperty @name=N'MS_Description', @value=N'用户手机号(对应V_WaterMetersAll.PhoneNumber)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PushRecord', @level2type=N'COLUMN', @level2name=N'PhoneNumber';
GO
EXEC sp_addextendedproperty @name=N'MS_Description', @value=N'实时推送日期/抄表日期(对应V_WaterMetersAll.NowRadingDT)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PushRecord', @level2type=N'COLUMN', @level2name=N'NowRadingDT';
GO
EXEC sp_addextendedproperty @name=N'MS_Description', @value=N'抄表月yyyyMM(无对应数据库字段,由推送时间生成)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PushRecord', @level2type=N'COLUMN', @level2name=N'ReadingMonth';
GO
EXEC sp_addextendedproperty @name=N'MS_Description', @value=N'本地读数(对应V_WaterMetersAll.NowReading)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PushRecord', @level2type=N'COLUMN', @level2name=N'NowReading';
GO
EXEC sp_addextendedproperty @name=N'MS_Description', @value=N'上传结果(成功/失败)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PushRecord', @level2type=N'COLUMN', @level2name=N'UploadResult';
GO
EXEC sp_addextendedproperty @name=N'MS_Description', @value=N'接口返回信息', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PushRecord', @level2type=N'COLUMN', @level2name=N'ResultMsg';
GO
EXEC sp_addextendedproperty @name=N'MS_Description', @value=N'记录写入时间', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PushRecord', @level2type=N'COLUMN', @level2name=N'UploadTime';
GO
EXEC sp_addextendedproperty @name=N'MS_Description', @value=N'备注', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PushRecord', @level2type=N'COLUMN', @level2name=N'Remark';
GO

-- ==========================================================================================
-- 以下为【已按旧版本脚本建过 PushRecord 表】环境的增量升级脚本（幂等，可重复执行）。
-- 全新库执行上面的建表语句即可，无需执行本段。
-- ==========================================================================================

-- 1) 旧表地址列名是 UserAddress，而程序写入列名为 totalAddr，需重命名修正（只改列名，数据保留）
-- IF COL_LENGTH('dbo.PushRecord','totalAddr') IS NULL
--    AND COL_LENGTH('dbo.PushRecord','UserAddress') IS NOT NULL
--     EXEC sp_rename N'dbo.PushRecord.UserAddress', N'totalAddr', N'COLUMN';
-- GO

-- 2) 流水模式不再需要"成功行筛选唯一索引"；若早期版本曾创建过，先删除（幂等）
-- IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'UX_PushRecord_Month_Meter_Success'
--            AND object_id = OBJECT_ID('dbo.PushRecord'))
--     DROP INDEX UX_PushRecord_Month_Meter_Success ON dbo.PushRecord;
-- GO
-- IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'UX_PushRecord_Month_User_Success'
--            AND object_id = OBJECT_ID('dbo.PushRecord'))
--     DROP INDEX UX_PushRecord_Month_User_Success ON dbo.PushRecord;
-- GO

-- 3) 新增 (ReadingMonth, ElecAddress) 复合索引，加速程序启动时查询本月已推送读数版本（幂等）
-- IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_PushRecord_Month_Meter'
--                AND object_id = OBJECT_ID('dbo.PushRecord'))
--     CREATE INDEX IX_PushRecord_Month_Meter
--         ON dbo.PushRecord(ReadingMonth, ElecAddress) INCLUDE (NowReading, NowRadingDT);
-- GO