Untitled

mail@pastecode.io avatar
unknown
plain_text
a year ago
39 kB
1
Indexable
Never
-- gigandtake.dbo.Broadcast definition

-- Drop table

-- DROP TABLE gigandtake.dbo.Broadcast;

CREATE TABLE gigandtake.dbo.Broadcast (
	Id int IDENTITY(1,1) NOT NULL,
	ToJobIds varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	Message varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	CreationDate datetime NOT NULL,
	CreatedBy int NOT NULL,
	WorkerCount int DEFAULT 0 NOT NULL,
	CONSTRAINT PK_Broadcast PRIMARY KEY (Id)
);


-- gigandtake.dbo.Company definition

-- Drop table

-- DROP TABLE gigandtake.dbo.Company;

CREATE TABLE gigandtake.dbo.Company (
	Id int IDENTITY(1,1) NOT NULL,
	Name varchar(150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	IsActive bit NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime2(2) NOT NULL,
	LastUpdatedBy int NOT NULL,
	LastUpdatedDate datetime2(2) NOT NULL,
	CONSTRAINT PK_Company PRIMARY KEY (Id)
);


-- gigandtake.dbo.ExpLevel definition

-- Drop table

-- DROP TABLE gigandtake.dbo.ExpLevel;

CREATE TABLE gigandtake.dbo.ExpLevel (
	Id bigint IDENTITY(1,1) NOT NULL,
	Title nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	MinHours int DEFAULT 0 NOT NULL,
	MaxHours int DEFAULT 0 NOT NULL,
	CreationDate datetime2 NOT NULL,
	CreatedBy int NOT NULL,
	LastUpdatedDate datetime2 NOT NULL,
	LastUpdateBy int NOT NULL,
	CONSTRAINT PK_ExpLevel PRIMARY KEY (Id),
	CONSTRAINT UQ__ExpLevel__2CB664DCDCC45181 UNIQUE (Title)
);


-- gigandtake.dbo.FulltimeWorkerAvailabilityType definition

-- Drop table

-- DROP TABLE gigandtake.dbo.FulltimeWorkerAvailabilityType;

CREATE TABLE gigandtake.dbo.FulltimeWorkerAvailabilityType (
	Id int IDENTITY(1,1) NOT NULL,
	Name nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	MaxHours int DEFAULT 0 NOT NULL,
	IsActive bit DEFAULT 1 NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime NULL,
	UpdatedBy int NULL,
	UpdatedDate datetime NULL,
	CONSTRAINT PK__Fulltime__3214EC073BD1B4CB PRIMARY KEY (Id)
);


-- gigandtake.dbo.[Login] definition

-- Drop table

-- DROP TABLE gigandtake.dbo.[Login];

CREATE TABLE gigandtake.dbo.[Login] (
	Id int IDENTITY(1,1) NOT NULL,
	UserName nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	Password nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	SaltValue nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	PasswordUpdateDate date NULL,
	ChangePasswordOnLogin bit NULL,
	FailedAttemptedLogins tinyint NULL,
	IsLocked bit NOT NULL,
	IsActive bit NOT NULL,
	CreationDate datetime2(2) NOT NULL,
	CreatedBy int NOT NULL,
	LastUpdatedDate datetime2(2) NOT NULL,
	LastUpdatedBy int NOT NULL,
	RefreshToken nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	PasswordResetExpiration datetime2 NULL,
	CONSTRAINT Login_UN UNIQUE (UserName),
	CONSTRAINT PK_Login PRIMARY KEY (Id)
);


-- gigandtake.dbo.LuEntity definition

-- Drop table

-- DROP TABLE gigandtake.dbo.LuEntity;

CREATE TABLE gigandtake.dbo.LuEntity (
	Id tinyint NOT NULL,
	Entity varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	IsActive bit NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime2(2) NOT NULL,
	LastUpdatedBy int NOT NULL,
	LastUpdatedDate datetime2(2) NOT NULL,
	CONSTRAINT PK_LuEntity PRIMARY KEY (Id)
);


-- gigandtake.dbo.LuRole definition

-- Drop table

-- DROP TABLE gigandtake.dbo.LuRole;

CREATE TABLE gigandtake.dbo.LuRole (
	Id int IDENTITY(1,1) NOT NULL,
	[Role] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	IsActive bit NOT NULL,
	SortOrder smallint NOT NULL,
	CreationDate datetime2(2) NOT NULL,
	CreatedBy int NOT NULL,
	LastUpdateDate datetime2(2) NOT NULL,
	LastUpdatedBy int NOT NULL,
	CONSTRAINT LuRole_UN UNIQUE ([Role]),
	CONSTRAINT PK_LuRole PRIMARY KEY (Id)
);


-- gigandtake.dbo.LuWorkerType definition

-- Drop table

-- DROP TABLE gigandtake.dbo.LuWorkerType;

CREATE TABLE gigandtake.dbo.LuWorkerType (
	Id tinyint IDENTITY(1,1) NOT NULL,
	[Type] varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	IsActive bit NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime2(2) NOT NULL,
	LastUpdatedBy int NOT NULL,
	LastUpdatedDate datetime2(2) NOT NULL,
	CONSTRAINT PK_LuWorkerType PRIMARY KEY (Id)
);


-- gigandtake.dbo.Settings definition

-- Drop table

-- DROP TABLE gigandtake.dbo.Settings;

CREATE TABLE gigandtake.dbo.Settings (
	Id bigint IDENTITY(1,1) NOT NULL,
	DN nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	Name nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	DataType nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	EntityId bigint DEFAULT 0 NOT NULL,
	Value nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	IsActive bigint DEFAULT 1 NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime2 NOT NULL,
	LastUpdatedBy int NULL,
	LastUpdatedDate datetime2 NULL,
	CONSTRAINT PK_Settings PRIMARY KEY (Id)
);


-- gigandtake.dbo.Settings_ShiftCreation definition

-- Drop table

-- DROP TABLE gigandtake.dbo.Settings_ShiftCreation;

CREATE TABLE gigandtake.dbo.Settings_ShiftCreation (
	Id int IDENTITY(1,1) NOT NULL,
	CompanyId int NOT NULL,
	DefaultShiftDuration tinyint NOT NULL,
	MinShiftDuration tinyint NOT NULL,
	MaxShiftDuration tinyint NOT NULL,
	WorkWeekStartDay varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	WorkWeekStartTime time(0) NOT NULL,
	ShiftMinAdvanceInDays tinyint NOT NULL,
	ShiftMaxAdvanceInDays tinyint NOT NULL,
	MinNumDaysPublished tinyint NOT NULL,
	MaxNumDaysPublished tinyint NOT NULL,
	SignUpCutOffDays tinyint NOT NULL,
	ShiftCancelAllow bit NOT NULL,
	ShiftModAllowTillDays tinyint NOT NULL,
	WorkerShiftCancelDays tinyint NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime2(2) NOT NULL,
	LastUpdatedBy int NOT NULL,
	LastUpdatedDate datetime2(2) NOT NULL,
	ShiftConfirmationLockHours int NULL,
	MaxContiniousSignUpHours float DEFAULT 12 NOT NULL,
	EnableDailyReportInEmail bit DEFAULT 1 NOT NULL,
	EnableDailySMSReminderToWorker bit DEFAULT 0 NOT NULL,
	AllowShiftForceCancel bit DEFAULT 0 NOT NULL,
	EnableShiftAutoAccept bit DEFAULT 0 NOT NULL,
	ShiftSwapEnabled bit DEFAULT 0 NOT NULL,
	ShiftSwapApprovalEnabled bit DEFAULT 0 NOT NULL,
	CONSTRAINT PK_Settings_Shift PRIMARY KEY (Id)
);
ALTER TABLE gigandtake.dbo.Settings_ShiftCreation WITH NOCHECK ADD CONSTRAINT CK_Settings_ShiftCreation_MinShiftDuration_should_be_less_than_MaxShiftDuration CHECK ([MinShiftDuration]<[MaxShiftDuration]);


-- gigandtake.dbo.Settings_WorkerScore definition

-- Drop table

-- DROP TABLE gigandtake.dbo.Settings_WorkerScore;

CREATE TABLE gigandtake.dbo.Settings_WorkerScore (
	Id bigint IDENTITY(1,1) NOT NULL,
	ScorePeriodInDays int DEFAULT 30 NULL,
	CancelLimitWithinPeriod int DEFAULT 5 NULL,
	CancelPenaltyRate float DEFAULT 0.5 NULL,
	AbsentPenaltyRate float DEFAULT 1.0 NULL,
	MinScore float DEFAULT 1 NULL,
	MaxScore float DEFAULT 5 NULL,
	CreationDate datetime2 NOT NULL,
	CreatedBy int NOT NULL,
	LastUpdatedDate datetime2 NULL,
	LastUpdatedBy int NULL,
	SarMinWeeklyHrs int DEFAULT 8 NOT NULL,
	SarWeekCount int DEFAULT 4 NOT NULL,
	CONSTRAINT PK_WorkerScoreSettings PRIMARY KEY (Id)
);


-- gigandtake.dbo.SkillLevel definition

-- Drop table

-- DROP TABLE gigandtake.dbo.SkillLevel;

CREATE TABLE gigandtake.dbo.SkillLevel (
	Id bigint IDENTITY(1,1) NOT NULL,
	Name nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	Seq int DEFAULT 0 NOT NULL,
	IsActive bit DEFAULT 1 NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime2 NOT NULL,
	LastUpdatedBy int NULL,
	LastUpdatedDate datetime2 NULL,
	CONSTRAINT PK_SkillLevel PRIMARY KEY (Id),
	CONSTRAINT SkillLevel_UN UNIQUE (Seq)
);


-- gigandtake.dbo.WorkerSettingType definition

-- Drop table

-- DROP TABLE gigandtake.dbo.WorkerSettingType;

CREATE TABLE gigandtake.dbo.WorkerSettingType (
	Id bigint IDENTITY(1,1) NOT NULL,
	Code nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	Name nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	ValueType nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	DefaultValue nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	CreationDate datetime2 NOT NULL,
	CreatedBy int NOT NULL,
	LastUpdatedDate datetime2 NOT NULL,
	LastUpdatedBy int NOT NULL,
	IsActive bit DEFAULT 1 NOT NULL,
	SettingDN varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	CONSTRAINT PK_WorkerSettingType PRIMARY KEY (Id),
	CONSTRAINT UK_Code UNIQUE (Code),
	CONSTRAINT UK_Name UNIQUE (Name)
);


-- gigandtake.dbo.WorkerTier definition

-- Drop table

-- DROP TABLE gigandtake.dbo.WorkerTier;

CREATE TABLE gigandtake.dbo.WorkerTier (
	Id bigint IDENTITY(1,1) NOT NULL,
	Seq int DEFAULT 0 NOT NULL,
	Title nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	CreationDate datetime2 NULL,
	CreatedBy int NULL,
	LastUpdatedDate datetime2 NULL,
	LastUpdatedBy int NULL,
	CONSTRAINT PK_WorkerTier PRIMARY KEY (Id),
	CONSTRAINT UQ__WorkerTi__CA1E3C89896707B3 UNIQUE (Seq)
);


-- gigandtake.dbo.CompanySso definition

-- Drop table

-- DROP TABLE gigandtake.dbo.CompanySso;

CREATE TABLE gigandtake.dbo.CompanySso (
	Id int IDENTITY(0,1) NOT NULL,
	CompanyId int NOT NULL,
	SsoType varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	SsoProvider varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	SsoRedirectUrl varchar(2048) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	SsoKeysJson text COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	CreatedDate datetime2(0) NOT NULL,
	CreatedBy int NOT NULL,
	UpdatedDate datetime2(0) NULL,
	UpdatedBy int NULL,
	IsActive tinyint DEFAULT 1 NOT NULL,
	CONSTRAINT CompanySso_PK PRIMARY KEY (Id),
	CONSTRAINT CompanySso_FK FOREIGN KEY (CompanyId) REFERENCES gigandtake.dbo.Company(Id)
);


-- gigandtake.dbo.Job definition

-- Drop table

-- DROP TABLE gigandtake.dbo.Job;

CREATE TABLE gigandtake.dbo.Job (
	Id int IDENTITY(1,1) NOT NULL,
	Code varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	Name varchar(150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	Category varchar(75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	SubCategory varchar(75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	HexColor varchar(6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	IsActive bit NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime2(2) NOT NULL,
	LastUpdatedBy int NOT NULL,
	LastUpdatedDate datetime2(2) NOT NULL,
	CompanyId int DEFAULT 1 NOT NULL,
	MinWorkHrs int DEFAULT 0 NOT NULL,
	MinWorkHrsDays int DEFAULT 0 NOT NULL,
	CONSTRAINT PK_Job PRIMARY KEY (Id),
	CONSTRAINT FK__Job__CompanyId__19AACF41 FOREIGN KEY (CompanyId) REFERENCES gigandtake.dbo.Company(Id),
	CONSTRAINT FK__Job__CompanyId__1EC48A19 FOREIGN KEY (CompanyId) REFERENCES gigandtake.dbo.Company(Id),
	CONSTRAINT FK__Job__CompanyId__703EA55A FOREIGN KEY (CompanyId) REFERENCES gigandtake.dbo.Company(Id)
);


-- gigandtake.dbo.JobExpLevel definition

-- Drop table

-- DROP TABLE gigandtake.dbo.JobExpLevel;

CREATE TABLE gigandtake.dbo.JobExpLevel (
	Id bigint IDENTITY(1,1) NOT NULL,
	ExpLevelId bigint NOT NULL,
	JobId int DEFAULT 0 NOT NULL,
	MinHours int DEFAULT 0 NOT NULL,
	MaxHours int DEFAULT 0 NOT NULL,
	CreationDate datetime2 NOT NULL,
	CreatedBy int NOT NULL,
	LastUpdatedDate datetime2 NOT NULL,
	LastUpdateBy int NOT NULL,
	CONSTRAINT PK_JobExpLevel PRIMARY KEY (Id),
	CONSTRAINT FK_ExpLevelId FOREIGN KEY (ExpLevelId) REFERENCES gigandtake.dbo.ExpLevel(Id),
	CONSTRAINT FK_JobId FOREIGN KEY (JobId) REFERENCES gigandtake.dbo.Job(Id)
);


-- gigandtake.dbo.JobSetting definition

-- Drop table

-- DROP TABLE gigandtake.dbo.JobSetting;

CREATE TABLE gigandtake.dbo.JobSetting (
	Id bigint IDENTITY(1,1) NOT NULL,
	JobId int NOT NULL,
	JobHrs int DEFAULT 0 NULL,
	JobPriority int DEFAULT 1 NULL,
	CreatedBy nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	CreationDate datetime NOT NULL,
	UpdatedBy nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	UpdatedDate datetime NULL,
	CONSTRAINT PK__JobSetti__3214EC07480E29EC PRIMARY KEY (Id),
	CONSTRAINT JST_JobId_FK FOREIGN KEY (JobId) REFERENCES gigandtake.dbo.Job(Id)
);


-- gigandtake.dbo.Location definition

-- Drop table

-- DROP TABLE gigandtake.dbo.Location;

CREATE TABLE gigandtake.dbo.Location (
	Id int IDENTITY(1,1) NOT NULL,
	CompanyId int NOT NULL,
	Name varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	BuildingNumber varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	Address1 varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	Address2 varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	City varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	State varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	Zip varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	ZipExt varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	County varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	Country varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	IsActive bit NOT NULL,
	CreatedBy int NOT NULL,
	CreatedDate datetime2(2) NOT NULL,
	LastUpdatedBy int NOT NULL,
	LastUpdatedDate datetime2(2) NOT NULL,
	CompanyContactForWorker varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	DailyReportEmailTime time DEFAULT '23:00' NOT NULL,
	TIME_ZONE varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT 'Eastern Standard Time' NOT NULL,
	DailySMSReminderTime time DEFAULT '18:00' NOT NULL,
	CONSTRAINT PK_Location PRIMARY KEY (Id),
	CONSTRAINT FK_Location_Company FOREIGN KEY (CompanyId) REFERENCES gigandtake.dbo.Company(Id)
);


-- gigandtake.dbo.Skill definition

-- Drop table

-- DROP TABLE gigandtake.dbo.Skill;

CREATE TABLE gigandtake.dbo.Skill (
	Id bigint IDENTITY(1,1) NOT NULL,
	Name nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	IsActive bit DEFAULT 1 NOT NULL,
	CompanyId int NOT NULL,
	CreatedBy int NOT NULL,
	CreatedDate datetime2 NOT NULL,
	LastUpdatedBy int NULL,
	LastUpdatedDate datetime2 NULL,
	CONSTRAINT PK_Skill PRIMARY KEY (Id),
	CONSTRAINT FK_Skill_Company FOREIGN KEY (CompanyId) REFERENCES gigandtake.dbo.Company(Id)
);


-- gigandtake.dbo.[User] definition

-- Drop table

-- DROP TABLE gigandtake.dbo.[User];

CREATE TABLE gigandtake.dbo.[User] (
	Id int IDENTITY(1,1) NOT NULL,
	LoginId int NOT NULL,
	RoleId int NOT NULL,
	CompanyId int NOT NULL,
	FirstName nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	MiddleName nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	LastName nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	Email nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	PhoneNumber varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	IsActive bit NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime2(2) NOT NULL,
	LastUpdatedBy int NOT NULL,
	LastUpdatedDate datetime2(2) NOT NULL,
	CONSTRAINT PK_User PRIMARY KEY (Id),
	CONSTRAINT User_UN UNIQUE (Email),
	CONSTRAINT FK_User_Company FOREIGN KEY (CompanyId) REFERENCES gigandtake.dbo.Company(Id),
	CONSTRAINT FK_User_Login FOREIGN KEY (LoginId) REFERENCES gigandtake.dbo.[Login](Id),
	CONSTRAINT FK_User_Role FOREIGN KEY (RoleId) REFERENCES gigandtake.dbo.LuRole(Id)
);


-- gigandtake.dbo.UserRole definition

-- Drop table

-- DROP TABLE gigandtake.dbo.UserRole;

CREATE TABLE gigandtake.dbo.UserRole (
	Id bigint IDENTITY(1,1) NOT NULL,
	UserId int NOT NULL,
	RoleId int NOT NULL,
	IsActive bit DEFAULT 1 NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime2 NOT NULL,
	LastUpdatedBy int NULL,
	LastUpdatedDate datetime2 NULL,
	CONSTRAINT PK_UserRole PRIMARY KEY (Id),
	CONSTRAINT FK_UserRole_LuRole FOREIGN KEY (RoleId) REFERENCES gigandtake.dbo.LuRole(Id),
	CONSTRAINT FK_UserRole_User FOREIGN KEY (UserId) REFERENCES gigandtake.dbo.[User](Id)
);


-- gigandtake.dbo.WorkCenter definition

-- Drop table

-- DROP TABLE gigandtake.dbo.WorkCenter;

CREATE TABLE gigandtake.dbo.WorkCenter (
	Id int IDENTITY(1,1) NOT NULL,
	Name varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	CostCenter varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	LocationId int NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate date NOT NULL,
	LastUpdatedBy int NOT NULL,
	LastUpdatedDate date NOT NULL,
	CONSTRAINT PK_WorkCenter PRIMARY KEY (Id),
	CONSTRAINT FK_WorkCenter_Location FOREIGN KEY (LocationId) REFERENCES gigandtake.dbo.Location(Id)
);


-- gigandtake.dbo.Worker definition

-- Drop table

-- DROP TABLE gigandtake.dbo.Worker;

CREATE TABLE gigandtake.dbo.Worker (
	Id uniqueidentifier DEFAULT newid() NOT NULL,
	FirstName varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	MiddleName varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	LastName varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	Email nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	MobilePhoneNumber varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	IsActive bit NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime2(2) NOT NULL,
	LastUpdatedBy int NOT NULL,
	LastUpdatedDate datetime2(2) NOT NULL,
	Passcode varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	PasscodeLastUpdated datetime NULL,
	RefreshToken varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	RefreshTokenLastUpdated datetime NULL,
	RScore float DEFAULT 5.0 NOT NULL,
	RScoreLastUpdated datetime2 DEFAULT getdate() NOT NULL,
	TotalCompletedHours float DEFAULT 0.0 NOT NULL,
	ExpLevelIdLastUpdated datetime2 DEFAULT getdate() NOT NULL,
	ExpLevelId bigint DEFAULT 1 NOT NULL,
	SAR int DEFAULT 0 NOT NULL,
	SARLastUpdated datetime2 NULL,
	TierSeq int DEFAULT 1 NOT NULL,
	CONSTRAINT PK_Worker PRIMARY KEY (Id),
	CONSTRAINT FK__Worker__ExpLevel__22951AFD FOREIGN KEY (ExpLevelId) REFERENCES gigandtake.dbo.ExpLevel(Id),
	CONSTRAINT FK__Worker__ExpLevel__2F9A1060 FOREIGN KEY (ExpLevelId) REFERENCES gigandtake.dbo.ExpLevel(Id),
	CONSTRAINT FK__Worker__ExpLevel__740F363E FOREIGN KEY (ExpLevelId) REFERENCES gigandtake.dbo.ExpLevel(Id)
);


-- gigandtake.dbo.WorkerAvailability definition

-- Drop table

-- DROP TABLE gigandtake.dbo.WorkerAvailability;

CREATE TABLE gigandtake.dbo.WorkerAvailability (
	Id bigint IDENTITY(11,1) NOT NULL,
	WorkerId uniqueidentifier NOT NULL,
	StartDateTime datetimeoffset NOT NULL,
	EndDateTime datetimeoffset NOT NULL,
	CreationDate datetime2 NOT NULL,
	CreatedBy int DEFAULT 0 NOT NULL,
	LastUpdatedDate datetime2 NULL,
	LastUpdatedBy int NULL,
	CONSTRAINT PK_WorkerAvailability PRIMARY KEY (Id),
	CONSTRAINT FK_WorkerId FOREIGN KEY (WorkerId) REFERENCES gigandtake.dbo.Worker(Id)
);


-- gigandtake.dbo.WorkerDeviceToken definition

-- Drop table

-- DROP TABLE gigandtake.dbo.WorkerDeviceToken;

CREATE TABLE gigandtake.dbo.WorkerDeviceToken (
	Id bigint IDENTITY(1,1) NOT NULL,
	WorkerId uniqueidentifier NOT NULL,
	DeviceToken nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	Platform nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	IsActive bit DEFAULT 1 NOT NULL,
	LastUpdatedDate datetime NOT NULL,
	creationdate datetime DEFAULT getdate() NOT NULL,
	DeviceId varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	CONSTRAINT PK_WorkerDeviceToken PRIMARY KEY (Id),
	CONSTRAINT FK__WorkerDev__Worke__23893F36 FOREIGN KEY (WorkerId) REFERENCES gigandtake.dbo.Worker(Id),
	CONSTRAINT FK__WorkerDev__Worke__318258D2 FOREIGN KEY (WorkerId) REFERENCES gigandtake.dbo.Worker(Id),
	CONSTRAINT FK__WorkerDev__Worke__75035A77 FOREIGN KEY (WorkerId) REFERENCES gigandtake.dbo.Worker(Id)
);


-- gigandtake.dbo.WorkerJob definition

-- Drop table

-- DROP TABLE gigandtake.dbo.WorkerJob;

CREATE TABLE gigandtake.dbo.WorkerJob (
	Id int IDENTITY(1,1) NOT NULL,
	WorkerId uniqueidentifier NOT NULL,
	JobId int NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime2(2) NOT NULL,
	LastUpdatedBy int NOT NULL,
	LastUpdatedDate datetime2(2) NOT NULL,
	InitExpLevelId bigint DEFAULT 1 NOT NULL,
	CurrExpLevelId bigint DEFAULT 1 NULL,
	IsExpired bit DEFAULT 0 NOT NULL,
	IsExpiredBy int DEFAULT 0 NOT NULL,
	IsExpiredDate datetime2 DEFAULT getdate() NOT NULL,
	CONSTRAINT PK_WorkerJob PRIMARY KEY (Id),
	CONSTRAINT WorkerJob_UK UNIQUE (WorkerId,JobId),
	CONSTRAINT FK_WorkerJob_Job FOREIGN KEY (JobId) REFERENCES gigandtake.dbo.Job(Id),
	CONSTRAINT FK_WorkerJob_Worker FOREIGN KEY (WorkerId) REFERENCES gigandtake.dbo.Worker(Id) ON UPDATE CASCADE
);


-- gigandtake.dbo.WorkerNotification definition

-- Drop table

-- DROP TABLE gigandtake.dbo.WorkerNotification;

CREATE TABLE gigandtake.dbo.WorkerNotification (
	Id bigint IDENTITY(1,1) NOT NULL,
	WorkerId uniqueidentifier NOT NULL,
	[Type] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	ShiftIds nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	Title nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	Message nvarchar(500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	NotificationDate datetime2 NULL,
	CONSTRAINT PK_WorkerNotification PRIMARY KEY (Id),
	CONSTRAINT FK_WorkerNotification_Worker FOREIGN KEY (WorkerId) REFERENCES gigandtake.dbo.Worker(Id)
);


-- gigandtake.dbo.WorkerSARWkHrs definition

-- Drop table

-- DROP TABLE gigandtake.dbo.WorkerSARWkHrs;

CREATE TABLE gigandtake.dbo.WorkerSARWkHrs (
	Id bigint IDENTITY(1,1) NOT NULL,
	WorkerId uniqueidentifier NOT NULL,
	WkStartDateTime datetime2 NOT NULL,
	WkEndDateTime datetime2 NOT NULL,
	WkSeq int NOT NULL,
	CompletedHrs float NOT NULL,
	LastUpdatedDate datetime2 NOT NULL,
	CONSTRAINT PK_WorkerWeeklyHours PRIMARY KEY (Id),
	CONSTRAINT FK_WorkerWeeklyHours_Worker FOREIGN KEY (WorkerId) REFERENCES gigandtake.dbo.Worker(Id)
);


-- gigandtake.dbo.WorkerScoreHistory definition

-- Drop table

-- DROP TABLE gigandtake.dbo.WorkerScoreHistory;

CREATE TABLE gigandtake.dbo.WorkerScoreHistory (
	Id bigint IDENTITY(1,1) NOT NULL,
	WorkerId uniqueidentifier NULL,
	RScore float NULL,
	TotalCompletedHours float NULL,
	ExpLevelId bigint NULL,
	CreationDate datetime2 NULL,
	CreatedBy int NULL,
	SAR int DEFAULT 0 NOT NULL,
	TierSeq int DEFAULT 1 NOT NULL,
	CONSTRAINT PK_WorkerScoreHistory PRIMARY KEY (Id),
	CONSTRAINT FK_WorkerScoreHistory_WorkerId FOREIGN KEY (WorkerId) REFERENCES gigandtake.dbo.Worker(Id),
	CONSTRAINT FK__WorkerSco__ExpLe__247D636F FOREIGN KEY (ExpLevelId) REFERENCES gigandtake.dbo.ExpLevel(Id),
	CONSTRAINT FK__WorkerSco__ExpLe__36470DEF FOREIGN KEY (ExpLevelId) REFERENCES gigandtake.dbo.ExpLevel(Id),
	CONSTRAINT FK__WorkerSco__ExpLe__75F77EB0 FOREIGN KEY (ExpLevelId) REFERENCES gigandtake.dbo.ExpLevel(Id)
);


-- gigandtake.dbo.WorkerScoreItem definition

-- Drop table

-- DROP TABLE gigandtake.dbo.WorkerScoreItem;

CREATE TABLE gigandtake.dbo.WorkerScoreItem (
	Id bigint IDENTITY(1,1) NOT NULL,
	WorkerId uniqueidentifier NOT NULL,
	WorkerShiftId int NULL,
	WorkerShiftStatus char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	WorkerShiftIsAbsent bit NULL,
	InvalidShiftCancelPenalty float NULL,
	ShiftStart datetimeoffset NULL,
	ShiftEnd datetimeoffset NULL,
	ShiftJobId int NULL,
	ShiftJobName nvarchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	WorkerShiftLastUpdated datetime2 NULL,
	LastUpdatedDate datetime2 NULL,
	WorkerShiftIsTardy bit DEFAULT 0 NOT NULL,
	CONSTRAINT PK_WorkerScoreItem PRIMARY KEY (Id),
	CONSTRAINT FK_WorkerScoreItem_Worker FOREIGN KEY (WorkerId) REFERENCES gigandtake.dbo.Worker(Id)
);


-- gigandtake.dbo.WorkerSettings definition

-- Drop table

-- DROP TABLE gigandtake.dbo.WorkerSettings;

CREATE TABLE gigandtake.dbo.WorkerSettings (
	Id bigint IDENTITY(1,1) NOT NULL,
	WorkerId uniqueidentifier NOT NULL,
	WorkerSettingTypeId bigint NOT NULL,
	Value nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime NOT NULL,
	LastUpdatedBy int NOT NULL,
	LastUpdatedDate datetime NOT NULL,
	CONSTRAINT PK_WorkerSettings PRIMARY KEY (Id),
	CONSTRAINT UQ__WorkerSe__2E6A568F26148F0E UNIQUE (WorkerId,WorkerSettingTypeId),
	CONSTRAINT FK_WokerSettings_WorkerId FOREIGN KEY (WorkerId) REFERENCES gigandtake.dbo.Worker(Id),
	CONSTRAINT FK_WokerSettings_WorkerSettingTypeId FOREIGN KEY (WorkerSettingTypeId) REFERENCES gigandtake.dbo.WorkerSettingType(Id)
);


-- gigandtake.dbo.WorkerSkill definition

-- Drop table

-- DROP TABLE gigandtake.dbo.WorkerSkill;

CREATE TABLE gigandtake.dbo.WorkerSkill (
	Id bigint IDENTITY(1,1) NOT NULL,
	WorkerId uniqueidentifier NOT NULL,
	SkillId bigint NOT NULL,
	SkillLevelId bigint DEFAULT 1 NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime2 NOT NULL,
	LastUpdatedBy int NULL,
	LastUpdatedDate datetime2 NULL,
	CONSTRAINT PK_WorkerSkill PRIMARY KEY (Id),
	CONSTRAINT FK_WorkerSkill_Skill FOREIGN KEY (SkillId) REFERENCES gigandtake.dbo.Skill(Id),
	CONSTRAINT FK_WorkerSkill_SkillLevel FOREIGN KEY (SkillLevelId) REFERENCES gigandtake.dbo.SkillLevel(Id),
	CONSTRAINT FK_WorkerSkill_Worker FOREIGN KEY (WorkerId) REFERENCES gigandtake.dbo.Worker(Id)
);


-- gigandtake.dbo.WorkerSkillInterest definition

-- Drop table

-- DROP TABLE gigandtake.dbo.WorkerSkillInterest;

CREATE TABLE gigandtake.dbo.WorkerSkillInterest (
	Id bigint IDENTITY(1,1) NOT NULL,
	WorkerId uniqueidentifier NOT NULL,
	SkillId bigint NOT NULL,
	SkillLevelId bigint DEFAULT 1 NOT NULL,
	JobId bigint NULL,
	Status char(1) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT 'N' NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime2 NOT NULL,
	LastUpdatedBy int NULL,
	LastUpdatedDate datetime2 NULL,
	CONSTRAINT PK_WorkerSkillInterest PRIMARY KEY (Id),
	CONSTRAINT FK_WorkerSkillInterest_Skill FOREIGN KEY (SkillId) REFERENCES gigandtake.dbo.Skill(Id),
	CONSTRAINT FK_WorkerSkillInterest_SkillLevel FOREIGN KEY (SkillLevelId) REFERENCES gigandtake.dbo.SkillLevel(Id),
	CONSTRAINT FK_WorkerSkillInterest_Worker FOREIGN KEY (WorkerId) REFERENCES gigandtake.dbo.Worker(Id)
);


-- gigandtake.dbo.WorkerTypeHours definition

-- Drop table

-- DROP TABLE gigandtake.dbo.WorkerTypeHours;

CREATE TABLE gigandtake.dbo.WorkerTypeHours (
	Id int IDENTITY(1,1) NOT NULL,
	CompanyId int NOT NULL,
	WorkerTypeId tinyint NOT NULL,
	MinHours smallint NOT NULL,
	MaxHours smallint NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime2(2) NOT NULL,
	LastUpdatedBy int NOT NULL,
	LastUpdatedDate datetime2(2) NOT NULL,
	CONSTRAINT PK_WorkerType PRIMARY KEY (Id),
	CONSTRAINT FK_Settings_WorkerType_Company FOREIGN KEY (CompanyId) REFERENCES gigandtake.dbo.Company(Id),
	CONSTRAINT FK_Settings_WorkerType_LuWorkerType FOREIGN KEY (WorkerTypeId) REFERENCES gigandtake.dbo.LuWorkerType(Id)
);
ALTER TABLE gigandtake.dbo.WorkerTypeHours WITH NOCHECK ADD CONSTRAINT CK_Settings_WorkerMinHours_should_be_less_than_WorkerMaxHours CHECK ([MinHours]<[MaxHours]);


-- gigandtake.dbo.CompanySsoException definition

-- Drop table

-- DROP TABLE gigandtake.dbo.CompanySsoException;

CREATE TABLE gigandtake.dbo.CompanySsoException (
	Id bigint IDENTITY(1,1) NOT NULL,
	UserId int NOT NULL,
	CompanyId int NOT NULL,
	CreationDate datetime2(0) NOT NULL,
	CreatedBy int NULL,
	UpdatedDate datetime2(0) NULL,
	UpdatedBy int NULL,
	IsActive bit DEFAULT 1 NOT NULL,
	CONSTRAINT CompanySsoException_PK PRIMARY KEY (Id),
	CONSTRAINT CompanySsoException_FK FOREIGN KEY (UserId) REFERENCES gigandtake.dbo.[User](Id),
	CONSTRAINT CompanySsoException_FK_1 FOREIGN KEY (CompanyId) REFERENCES gigandtake.dbo.Company(Id)
);


-- gigandtake.dbo.CompanyWorker definition

-- Drop table

-- DROP TABLE gigandtake.dbo.CompanyWorker;

CREATE TABLE gigandtake.dbo.CompanyWorker (
	Id int IDENTITY(1,1) NOT NULL,
	CompanyId int NOT NULL,
	WorkerId uniqueidentifier NOT NULL,
	WorkerTypeId tinyint NOT NULL,
	CompanyEmployeeId varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	IsActive bit NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime2(2) NOT NULL,
	LastUpdatedBy int NOT NULL,
	LastUpdatedDate datetime2(2) NOT NULL,
	CONSTRAINT PK_CompanyWorker PRIMARY KEY (Id),
	CONSTRAINT FK_CompanyWorker_Company FOREIGN KEY (CompanyId) REFERENCES gigandtake.dbo.Company(Id),
	CONSTRAINT FK_CompanyWorker_Worker FOREIGN KEY (WorkerId) REFERENCES gigandtake.dbo.Worker(Id) ON UPDATE CASCADE,
	CONSTRAINT FK_CompanyWorker_WorkerType FOREIGN KEY (WorkerTypeId) REFERENCES gigandtake.dbo.LuWorkerType(Id)
);


-- gigandtake.dbo.EntityUser definition

-- Drop table

-- DROP TABLE gigandtake.dbo.EntityUser;

CREATE TABLE gigandtake.dbo.EntityUser (
	Id int IDENTITY(1,1) NOT NULL,
	UserId int NOT NULL,
	EntityId int NOT NULL,
	LuEntityId tinyint NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime2(2) NOT NULL,
	LastUpdatedBy int NOT NULL,
	LastUpdatedDate datetime2(2) NOT NULL,
	CONSTRAINT PK_EntityUser PRIMARY KEY (Id),
	CONSTRAINT FK_EntityUser_Entity FOREIGN KEY (LuEntityId) REFERENCES gigandtake.dbo.LuEntity(Id),
	CONSTRAINT FK_EntityUser_User FOREIGN KEY (UserId) REFERENCES gigandtake.dbo.[User](Id)
);


-- gigandtake.dbo.FTShift definition

-- Drop table

-- DROP TABLE gigandtake.dbo.FTShift;

CREATE TABLE gigandtake.dbo.FTShift (
	Id bigint IDENTITY(1,1) NOT NULL,
	StartDateTime datetimeoffset NOT NULL,
	EndDateTime datetimeoffset NOT NULL,
	JobId int NOT NULL,
	WorkCenterId int NOT NULL,
	NumWorkersNeeded smallint NOT NULL,
	ShiftPremium varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	IsPublished bit NOT NULL,
	IsActive bit NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime2(2) NOT NULL,
	LastUpdatedBy int NOT NULL,
	LastUpdatedDate datetime2(2) NOT NULL,
	CONSTRAINT PK_FTShift PRIMARY KEY (Id),
	CONSTRAINT FK_FTShift_Job FOREIGN KEY (JobId) REFERENCES gigandtake.dbo.Job(Id),
	CONSTRAINT FK_FTShift_WorkCenter FOREIGN KEY (WorkCenterId) REFERENCES gigandtake.dbo.WorkCenter(Id)
);


-- gigandtake.dbo.FTWorkerShift definition

-- Drop table

-- DROP TABLE gigandtake.dbo.FTWorkerShift;

CREATE TABLE gigandtake.dbo.FTWorkerShift (
	Id int IDENTITY(1,1) NOT NULL,
	WorkerId uniqueidentifier NOT NULL,
	ShiftId bigint NOT NULL,
	Status char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	ShiftPremium varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime2(2) NOT NULL,
	LastUpdatedBy int NOT NULL,
	LastUpdatedDate datetime2(2) NOT NULL,
	ATTEND_STATUS char(1) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT 'p' NOT NULL,
	Comment nvarchar(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	CONSTRAINT PK_FTWorkerShift PRIMARY KEY (Id),
	CONSTRAINT FK_FTWorkerShift_FTShift FOREIGN KEY (ShiftId) REFERENCES gigandtake.dbo.FTShift(Id),
	CONSTRAINT FK_FTWorkerShift_Worker FOREIGN KEY (WorkerId) REFERENCES gigandtake.dbo.Worker(Id) ON UPDATE CASCADE
);


-- gigandtake.dbo.FulltimeWorkerAvailability definition

-- Drop table

-- DROP TABLE gigandtake.dbo.FulltimeWorkerAvailability;

CREATE TABLE gigandtake.dbo.FulltimeWorkerAvailability (
	Id bigint IDENTITY(1,1) NOT NULL,
	WorkerId uniqueidentifier NOT NULL,
	AvailabilityDate date NOT NULL,
	TypeId int NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime NULL,
	UpdatedBy int NOT NULL,
	UpdatedDate datetime NULL,
	CONSTRAINT PK__Fulltime__3214EC07EC09ED68 PRIMARY KEY (Id),
	CONSTRAINT FWA_TypeId_FK FOREIGN KEY (TypeId) REFERENCES gigandtake.dbo.FulltimeWorkerAvailabilityType(Id),
	CONSTRAINT FWA_WorkerId_FK FOREIGN KEY (WorkerId) REFERENCES gigandtake.dbo.Worker(Id)
);


-- gigandtake.dbo.FulltimeWorkerRequirement definition

-- Drop table

-- DROP TABLE gigandtake.dbo.FulltimeWorkerRequirement;

CREATE TABLE gigandtake.dbo.FulltimeWorkerRequirement (
	Id bigint IDENTITY(1,1) NOT NULL,
	CompanyId int NOT NULL,
	RDate date NOT NULL,
	JobId int NOT NULL,
	WorkerCount int NOT NULL,
	CreationDate datetime NOT NULL,
	CreatedBy int NOT NULL,
	UpdatedDate datetime NULL,
	UpdatedBy int NULL,
	FtShiftId bigint DEFAULT 0 NOT NULL,
	CONSTRAINT PK__Fulltime__3214EC0731F5BBA6 PRIMARY KEY (Id),
	CONSTRAINT WR_Job_RDate_UK UNIQUE (JobId,RDate),
	CONSTRAINT WR_CompanyId_FK FOREIGN KEY (CompanyId) REFERENCES gigandtake.dbo.Company(Id),
	CONSTRAINT WR_Job_FK FOREIGN KEY (JobId) REFERENCES gigandtake.dbo.Job(Id)
);


-- gigandtake.dbo.JobSkill definition

-- Drop table

-- DROP TABLE gigandtake.dbo.JobSkill;

CREATE TABLE gigandtake.dbo.JobSkill (
	Id bigint IDENTITY(1,1) NOT NULL,
	JobId int NOT NULL,
	SkillId bigint NOT NULL,
	IsActive bit DEFAULT 1 NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime2 NOT NULL,
	LastUpdatedBy int NULL,
	LastUpdatedDate datetime2 NULL,
	CONSTRAINT PK_JobSkill PRIMARY KEY (Id),
	CONSTRAINT FK_JobSkill_Job FOREIGN KEY (JobId) REFERENCES gigandtake.dbo.Job(Id),
	CONSTRAINT FK_JobSkill_Skill FOREIGN KEY (SkillId) REFERENCES gigandtake.dbo.Skill(Id)
);


-- gigandtake.dbo.JobWorkcenter definition

-- Drop table

-- DROP TABLE gigandtake.dbo.JobWorkcenter;

CREATE TABLE gigandtake.dbo.JobWorkcenter (
	Id int IDENTITY(1,1) NOT NULL,
	JobId int NOT NULL,
	WorkcenterId int NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime2(2) NOT NULL,
	LastUpdatedBy int NOT NULL,
	LastUpdatedDate datetime2(2) NOT NULL,
	CONSTRAINT FK_JobWorkcenter_Job FOREIGN KEY (JobId) REFERENCES gigandtake.dbo.Job(Id),
	CONSTRAINT FK_JobWorkcenter_Workcenter FOREIGN KEY (WorkcenterId) REFERENCES gigandtake.dbo.WorkCenter(Id)
);


-- gigandtake.dbo.PushNotificationLog definition

-- Drop table

-- DROP TABLE gigandtake.dbo.PushNotificationLog;

CREATE TABLE gigandtake.dbo.PushNotificationLog (
	Id bigint IDENTITY(1,1) NOT NULL,
	Provider nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Type] nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	Request nvarchar(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	Response nvarchar(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	CreationDate datetime NOT NULL,
	WorkerId uniqueidentifier NULL,
	CONSTRAINT PK_PushNotificationLog PRIMARY KEY (Id),
	CONSTRAINT FK__PushNotif__Worke__1FB8AE52 FOREIGN KEY (WorkerId) REFERENCES gigandtake.dbo.Worker(Id),
	CONSTRAINT FK__PushNotif__Worke__22401542 FOREIGN KEY (WorkerId) REFERENCES gigandtake.dbo.Worker(Id),
	CONSTRAINT FK__PushNotif__Worke__7132C993 FOREIGN KEY (WorkerId) REFERENCES gigandtake.dbo.Worker(Id)
);


-- gigandtake.dbo.Shift definition

-- Drop table

-- DROP TABLE gigandtake.dbo.Shift;

CREATE TABLE gigandtake.dbo.Shift (
	Id bigint IDENTITY(1,1) NOT NULL,
	StartDateTime datetimeoffset NOT NULL,
	EndDateTime datetimeoffset NOT NULL,
	JobId int NOT NULL,
	WorkCenterId int NOT NULL,
	NumWorkersNeeded smallint NOT NULL,
	ShiftPremium varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	IsPublished bit NOT NULL,
	IsActive bit NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime2(2) NOT NULL,
	LastUpdatedBy int NOT NULL,
	LastUpdatedDate datetime2(2) NOT NULL,
	CONSTRAINT PK_Shift PRIMARY KEY (Id),
	CONSTRAINT FK_Shift_Job FOREIGN KEY (JobId) REFERENCES gigandtake.dbo.Job(Id),
	CONSTRAINT FK_Shift_WorkCenter FOREIGN KEY (WorkCenterId) REFERENCES gigandtake.dbo.WorkCenter(Id)
);


-- gigandtake.dbo.ShiftNotification definition

-- Drop table

-- DROP TABLE gigandtake.dbo.ShiftNotification;

CREATE TABLE gigandtake.dbo.ShiftNotification (
	Id int IDENTITY(1,1) NOT NULL,
	WorkerId uniqueidentifier NOT NULL,
	ShiftId bigint NOT NULL,
	Status char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	ShiftPremium varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime2(2) NOT NULL,
	LastUpdatedBy int NOT NULL,
	LastUpdatedDate datetime2(2) NOT NULL,
	CONSTRAINT PK_ShiftNotification PRIMARY KEY (Id),
	CONSTRAINT FK_ShiftNotification_Shift FOREIGN KEY (ShiftId) REFERENCES gigandtake.dbo.Shift(Id),
	CONSTRAINT FK_ShiftNotification_Worker FOREIGN KEY (WorkerId) REFERENCES gigandtake.dbo.Worker(Id) ON UPDATE CASCADE
);


-- gigandtake.dbo.ShiftSwap definition

-- Drop table

-- DROP TABLE gigandtake.dbo.ShiftSwap;

CREATE TABLE gigandtake.dbo.ShiftSwap (
	Id int IDENTITY(1,1) NOT NULL,
	ShiftId bigint NOT NULL,
	WorkerId uniqueidentifier NOT NULL,
	RequestDate datetime NOT NULL,
	Comment varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	ApprovalStatus char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	ApprovalBy int NULL,
	ApprovalDate datetime NULL,
	AcceptedByWorkerId uniqueidentifier NULL,
	AcceptanceDate datetime NULL,
	IsActive bit DEFAULT 1 NOT NULL,
	LastUpdatedDate datetime NULL,
	CONSTRAINT PK__ShiftSwa__3214EC07DABCE78B PRIMARY KEY (Id),
	CONSTRAINT FK__ShiftSwap__Shift__20ACD28B FOREIGN KEY (ShiftId) REFERENCES gigandtake.dbo.Shift(Id),
	CONSTRAINT FK__ShiftSwap__Shift__2704CA5F FOREIGN KEY (ShiftId) REFERENCES gigandtake.dbo.Shift(Id),
	CONSTRAINT FK__ShiftSwap__Shift__7226EDCC FOREIGN KEY (ShiftId) REFERENCES gigandtake.dbo.Shift(Id),
	CONSTRAINT FK__ShiftSwap__Worke__21A0F6C4 FOREIGN KEY (WorkerId) REFERENCES gigandtake.dbo.Worker(Id),
	CONSTRAINT FK__ShiftSwap__Worke__27F8EE98 FOREIGN KEY (WorkerId) REFERENCES gigandtake.dbo.Worker(Id),
	CONSTRAINT FK__ShiftSwap__Worke__731B1205 FOREIGN KEY (WorkerId) REFERENCES gigandtake.dbo.Worker(Id)
);
ALTER TABLE gigandtake.dbo.ShiftSwap WITH NOCHECK ADD CONSTRAINT CHK_SHIFTSWAP_STATUS CHECK ([ApprovalStatus]='na' OR [ApprovalStatus]='r' OR [ApprovalStatus]='a' OR [ApprovalStatus]='n' OR [ApprovalStatus]='p');


-- gigandtake.dbo.WorkerShift definition

-- Drop table

-- DROP TABLE gigandtake.dbo.WorkerShift;

CREATE TABLE gigandtake.dbo.WorkerShift (
	Id int IDENTITY(1,1) NOT NULL,
	WorkerId uniqueidentifier NOT NULL,
	ShiftId bigint NOT NULL,
	Status char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	ShiftPremium varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime2(2) NOT NULL,
	LastUpdatedBy int NOT NULL,
	LastUpdatedDate datetime2(2) NOT NULL,
	ATTEND_STATUS char(1) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT 'p' NOT NULL,
	Comment nvarchar(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	CONSTRAINT PK_WorkerShift PRIMARY KEY (Id),
	CONSTRAINT FK_WorkerShift_Shift FOREIGN KEY (ShiftId) REFERENCES gigandtake.dbo.Shift(Id),
	CONSTRAINT FK_WorkerShift_Worker FOREIGN KEY (WorkerId) REFERENCES gigandtake.dbo.Worker(Id) ON UPDATE CASCADE
);


-- gigandtake.dbo.WorkerShiftStatusLog definition

-- Drop table

-- DROP TABLE gigandtake.dbo.WorkerShiftStatusLog;

CREATE TABLE gigandtake.dbo.WorkerShiftStatusLog (
	Id bigint IDENTITY(1,1) NOT NULL,
	WorkerShiftId int NOT NULL,
	Status char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	LastUpdatedBy bigint NOT NULL,
	LastUpdatedDate datetime2 NOT NULL,
	CONSTRAINT PK_WorkerShiftStatusLog PRIMARY KEY (Id),
	CONSTRAINT FK_WorkerShiftId FOREIGN KEY (WorkerShiftId) REFERENCES gigandtake.dbo.WorkerShift(Id)
);


-- gigandtake.dbo.FTShiftFlexShift definition

-- Drop table

-- DROP TABLE gigandtake.dbo.FTShiftFlexShift;

CREATE TABLE gigandtake.dbo.FTShiftFlexShift (
	Id bigint IDENTITY(1,1) NOT NULL,
	FtShiftId bigint NOT NULL,
	FlexShiftId bigint DEFAULT 0 NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime NOT NULL,
	UpdatedBy int NULL,
	UpdatedDate datetime NULL,
	CONSTRAINT PK__FTShiftF__3214EC079EA26E9F PRIMARY KEY (Id),
	CONSTRAINT FSFS_FlexShift_FK FOREIGN KEY (FlexShiftId) REFERENCES gigandtake.dbo.Shift(Id),
	CONSTRAINT FSFS_FtShift_FK FOREIGN KEY (FtShiftId) REFERENCES gigandtake.dbo.FTShift(Id)
);