/* 检录员一检、二检、三检任务分配。SQL Server，可重复执行。 */
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.CheckinTask', N'U') IS NULL
    THROW 50001, N'缺少 dbo.CheckinTask，请先执行前置迁移', 1;

IF COL_LENGTH(N'dbo.CheckinTask', N'CheckinNo') IS NULL
    ALTER TABLE dbo.CheckinTask ADD CheckinNo INT NOT NULL
        CONSTRAINT DF_CheckinTask_CheckinNo DEFAULT (1);
GO

IF EXISTS(SELECT EventId FROM dbo.CheckinTask WHERE DeleteMark=0 AND TaskStatus=1 GROUP BY EventId HAVING COUNT(*)>3)
    THROW 50002, N'存在赛事拥有超过三个有效检录员，请先解除多余检录员', 1;

;WITH RankedTask AS
(
    SELECT Id, ROW_NUMBER() OVER(PARTITION BY EventId ORDER BY CreatedTime,Id) AS NewCheckinNo
    FROM dbo.CheckinTask WHERE DeleteMark=0 AND TaskStatus=1
)
UPDATE task SET CheckinNo=ranked.NewCheckinNo
FROM dbo.CheckinTask task INNER JOIN RankedTask ranked ON ranked.Id=task.Id;

IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE object_id=OBJECT_ID(N'dbo.CheckinTask') AND name=N'UX_CheckinTask_Event_CheckinNo_Active')
    CREATE UNIQUE INDEX UX_CheckinTask_Event_CheckinNo_Active
        ON dbo.CheckinTask(EventId,CheckinNo) WHERE DeleteMark=0 AND TaskStatus=1;
GO

DECLARE @Description NVARCHAR(4000)=N'该检录员负责的检次：1-一检，2-二检，3-三检';
IF EXISTS(SELECT 1 FROM sys.extended_properties WHERE class=1 AND major_id=OBJECT_ID(N'dbo.CheckinTask') AND minor_id=COLUMNPROPERTY(OBJECT_ID(N'dbo.CheckinTask'),N'CheckinNo','ColumnId') AND name=N'MS_Description')
    EXEC sys.sp_updateextendedproperty @name=N'MS_Description',@value=@Description,@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'TABLE',@level1name=N'CheckinTask',@level2type=N'COLUMN',@level2name=N'CheckinNo';
ELSE
    EXEC sys.sp_addextendedproperty @name=N'MS_Description',@value=@Description,@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'TABLE',@level1name=N'CheckinTask',@level2type=N'COLUMN',@level2name=N'CheckinNo';