-- 每月推送记录表（字段对应"每月推送模版表xlsx.xlsx"，列名与数据库视图 V_WaterMetersAll 字段名一致）
-- 数据库：Mul_AMRS_NBManager
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)
    UserAddress     NVARCHAR(500) NULL,                -- 用户地址(对应V_WaterMetersAll.UserAddress)
    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

CREATE INDEX IX_PushRecord_ReadingMonth ON dbo.PushRecord(ReadingMonth);
CREATE INDEX IX_PushRecord_UserNo       ON dbo.PushRecord(UserNo);
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.UserAddress)', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'PushRecord', @level2type=N'COLUMN', @level2name=N'UserAddress';
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