# User Pick Lock-Ins

## Project Overview

This project allows users to lock-in a NFL schedule before a given week's games start, and will award a user one-point per game won.

A weekly and seasonal leaderboard will exist for users to show on the site.

## System Enhancements

### User Pick Lock In

#### Requirements

1. User picks become available at the close of the prior week (after Monday Night Football concludes)
2. User picks cannot be submitted after any NFL game transitions to "in progress"
3. User picks can be re-submitted until 1-hour before game start

CREATE SCHEMA resulting


CREATE TABLE [resulting].PrivacyLevels (
    PrivacyId int primary key identity,
    Name varchar(100) not null unique
)

INSERT INTO [resulting].PrivacyLevels VALUES 
    ('Unrestricted'), -- no restriction
    ('Private'), -- users can join with an invite link
    ('Closed') -- users with invite link cannot join

create table resulting.GameResults (
	GameResultId bigint primary key identity,
	GameScheduleNum int not null,
	[Week] int not null,
	Outcome int not null,
	TeamPicked int not null,
	GameClusterId bigint not null FOREIGN KEY REFERENCES Games(GameClusterId),
	LeagueGuid uniqueidentifier not null,
	INDEX IX_GameResults_League_Week NONCLUSTERED(LeagueGuid, [Week]),
	INDEX IX_GameResults_League NONCLUSTERED(LeagueGuid),
    --include is important because it will index scan instead of seek without it
    INDEX IX_GameResults_GameClusterId NONCLUSTERED(GameClusterId) INCLUDE (Outcome)
)

CREATE TABLE [resulting].Groups (
    GroupGuid uniqueidentifier default newid(),
    GroupId bigint identity,
    
    LeagueClusterId bigint not null,    
    GroupAdministratorId nvarchar(128) not null,
    GroupName varchar(100),
    PrivacyLevel int not null default 1,
    CONSTRAINT FK_Groups_PrivacyLevels FOREIGN KEY (PrivacyLevel) REFERENCES [resulting].PrivacyLevels(PrivacyId),
    CONSTRAINT FK_Groups_GroupAdministratorId FOREIGN KEY (GroupAdministratorId) REFERENCES dbo.ASPNETUSERS(Id),
    CONSTRAINT FK_Groups_LeagueClusterId FOREIGN KEY (LeagueClusterId) REFERENCES Leagues(LeagueClusterId),
	CONSTRAINT PK_Groups_GroupGuid PRIMARY KEY NONCLUSTERED (GroupGuid),
	INDEX IX_Groups_GroupId CLUSTERED (GroupId)
)

CREATE TABLE [resulting].UserPicks(
    UserPickId bigint identity primary key,
    LeagueClusterId bigint not null,    
    GameClusterId bigint not null, 
    UserGuid nvarchar(128) not null,
    TeamPicked int not null,
    GameWeek int not null, --will be far easier to query this directly
    PickSubmittedOn DATETIME not null default GETUTCDATE(), 
    
    CONSTRAINT FK_UserPicks_UserGuid FOREIGN KEY (UserGuid) REFERENCES dbo.ASPNETUSERS(Id),
	CONSTRAINT FK_UserPicks_TeamPicked FOREIGN KEY (TeamPicked) REFERENCES dbo.Teams(TeamId),
	CONSTRAINT FK_UserPicks_GameClusterId FOREIGN KEY (GameClusterId) REFERENCES dbo.Games(GameClusterId),
    -- could get here from the GameClusterId table but useful to have easier queries
    CONSTRAINT FK_UserPicks_LeagueClusterId FOREIGN KEY (LeagueClusterId) REFERENCES Leagues(LeagueClusterId)			,

	CONSTRAINT UQ_UserPicks_GameClusterId_UserGuid UNIQUE(GameClusterId, UserGuid)
)

CREATE NONCLUSTERED INDEX IX_UserPicks_Week_League ON
    [resulting].UserPicks ([GameWeek], LeagueClusterId)
INCLUDE (GameClusterId, UserGuid, TeamPicked, PickSubmittedOn)


CREATE NONCLUSTERED INDEX IX_UserPicks_Week ON
	[resulting].UserPicks ([GameWeek])

CREATE NONCLUSTERED INDEX IX_UserPicks_User ON
	[resulting].UserPicks ([GameWeek], [UserGuid])
    
CREATE NONCLUSTERED INDEX IX_League_User_Game ON
	[resulting].UserPicks ([LeagueClusterId], [UserGuid], [GameClusterId])
    
CREATE TABLE [resulting].WeeklyRollups(
    WeeklyRollupId BIGINT PRIMARY KEY IDENTITY,
    UserGuid nvarchar(128) not null,
    LeagueClusterId bigint not null,
    
    PointsScored int not null,
    GameWeek int not null, --will be far easier to query this directly
    
    CONSTRAINT FK_WeeklyRollups_UserGuid FOREIGN KEY (UserGuid) REFERENCES dbo.ASPNETUSERS(Id),
    CONSTRAINT FK_WeeklyRollups_LeagueClusterId FOREIGN KEY (LeagueClusterId) REFERENCES Leagues(LeagueClusterId),

    CONSTRAINT UQ_WeeklyRollups_League_Week_User UNIQUE(LeagueClusterId,GameWeek,UserGuid)
)

CREATE NONCLUSTERED INDEX IX_WeeklyRollups_User ON 
	[resulting].WeeklyRollups (UserGuid)

CREATE NONCLUSTERED INDEX IX_WeeklyRollups_LeagueClusterId ON 
	[resulting].WeeklyRollups (LeagueClusterId)
    
ALTER TABLE resulting.UserPicks 
	ADD ProcessingState INT DEFAULT 0 NOT NULL

CREATE NONCLUSTERED INDEX IX_UserPicks_ProcessingState ON resulting.UserPicks(ProcessingState)

#### Data generation for testing

declare userCursor cursor for
    select top 10 UserId = Id from ASPNETUSERS
open userCursor
declare @UserId varchar(128)
fetch next from userCursor INTO @UserId ;
WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO resulting.UserPicks (UserGuid, LeagueClusterId, GameClusterId, GameWeek, TeamPicked) 
            (SELECT @UserId, LeagueClusterId, GameClusterId, [Week],
            (CASE WHEN ABS(CHECKSUM(NEWID()) % 2)+1 = 1 THEN HomeId ELSE AwayId END) AS TeamPicked FROM Games g
				join leagues l on g.LeagueGUID = l.LeagueGUID
			
			where g.LeagueGuid = '842CB5EC-193A-4D6D-8002-801297F04F53')

    FETCH NEXT FROM userCursor INTO @UserId
    END
CLOSE userCursor
DEALLOCATE userCursor

### Pick Submission

Save Pick
`POST /save-pick`

read userid from header: `x-playoffpredictors-userId`

UserId for `[e-mail]` in sql-dev `6948bcbe-4e70-415d-958e-2b014e56960e`

*extraction of userid is done via api management -- you can code this value into postman yourself*

enum Outcomes{
    NONE = 0,
    Home = 1,
    Away = 2,
    Tie = 3
}

{
    "LeagueClusterId": "842CB5EC-193A-4D6D-8002-801297F04F53",
    "week": 1,
    "results": [
        {
            "GameGuid": "373A1135-AD2A-489E-B3CA-D7780A6FA26D",
            "outcome": 1,
            "teamPicked": 14
        },
        {
            "GameGuid": "6FE193FC-BE81-4C38-BF5D-D9E6C17CD190",
            "outcome": 2,
            "teamPicked": 32
        },
        {
            "GameGuid": "C32BCE53-794C-4164-8168-DA220EF6892C",
            "outcome": 1,
            "teamPicked": 28
        },
        {
            "GameGuid": "79B178C9-1F73-466A-AB7B-DBAD00B1A13A",
            "outcome": 1,
            "teamPicked": 11
        },
        {
            "GameGuid": "D3DA2722-0957-435A-9092-DF4F4A0E3CE5",
            "outcome": 1,
            "teamPicked": 11
        },
        {
            "GameGuid": "4BD3969A-2661-4BCD-BEB3-DFF5F07BFF07",
            "outcome": 2,
            "teamPicked": 19
        },
        {
            "GameGuid": "69BC595D-0D71-4476-9C5E-DFF8E0EF3CAE",
            "outcome": 2,
            "teamPicked": 6
        }
    ]
}

sequenceDiagram
participant UI
participant re as ResultingEngine
participant le as LeagueEngine
participant sb as ServiceBus
participant sql as Sql

alt UserLoggedIn
UI->>le: IsWeekAvailable

alt Submissions Available
UI->>re: save-picks
re->>le: IsSubmissionsAvailable (probably cache)
re->>le: Get LeagueData (cache)
re->>sb: SavePicks
re->>UI: OK
par async pick saves
re->>sb: ServiceBusTrigger
sb->>re: UserGamePick
re->>sql: SavePicks
end
end
end

* to take load off of the system, picks should be enqueued to service bus, and dequeued one-by-one to update the system asyncrhonously
* the updates should never be accepted if the enqueued time occurs after a game start time for that week
* Since ResultingEngine emits messages to itself, the contracts can be entirely defined in the project. We don't need to use the `PlayoffPredictors.Exports` projects for the service bus messages or the internal contracts

### Resulting

1. UpdateEngine can emit a message when a game is resulted
2. (new engine) ScoringEngine takes pick, pulls users that selected that game, awards them one point
1. could use event sourcing? worth a look
4. Users receive weekly email reports of their picks and leader board ranks (can schedule job to run on Wednesdays)

sequenceDiagram
participant le as LeagueEngine
participant sb as ServiceBus
participant se as ResultingEngine
participant sql
le->>sb: WeekConcludedEvent
#durable function to orchestrate maybe?
sb->>se: WeekConcludedHandler
se->>sql: Select games for league+week
se->>se: Select games user got correct outcome of
se->>sql: Insert weekly rollup
# could be a timer trigger to query/update the sql records in-line
se->>sql: Any remaining scores for week?
alt any remaining?
    se->>sb: Scoring Done
else
se->>se: Keep Processing
end

Once there are no records remaining for the week, we need to move on to the next stage, which is to roll off the week's total results. Can be json file to blob or redis.

### User Profile

1. User Profiles need to have an external username entry, configurable from the account page. This will be what's shown on leaderboards
1. disallow entry of usernames with words like "playoffpredictors" in it