-- dbo.Transition
CREATE TABLE [dbo].[Transition](
    [transitionID] [int] IDENTITY(1,1) NOT NULL,
    [machineID] [int] NOT NULL,
    [category] [varchar](50) NOT NULL,
    [eventID] [int] NOT NULL,
    [stateID] [int] NOT NULL,
    [nextStateID] [int] NOT NULL,
 CONSTRAINT [PK_Transition] PRIMARY KEY CLUSTERED
(
    [transitionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UK_Transition_machineID_stateID_eventID] UNIQUE NONCLUSTERED
(
    [machineID] ASC,
    [stateID] ASC,
    [eventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [UK_Transition_machineID_nextStateID_eventID] ON [dbo].[Transition]
(
    [machineID] ASC,
    [eventID] ASC,
    [stateID] ASC,
    [nextStateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Transition]  WITH CHECK ADD  CONSTRAINT [FK_Transition_NextState] FOREIGN KEY([nextStateID])
REFERENCES [dbo].[State] ([stateID])
GO

ALTER TABLE [dbo].[Transition] CHECK CONSTRAINT [FK_Transition_NextState]
GO

ALTER TABLE [dbo].[Transition]  WITH CHECK ADD  CONSTRAINT [FK_Transition_State] FOREIGN KEY([stateID])
REFERENCES [dbo].[State] ([stateID])
GO

ALTER TABLE [dbo].[Transition] CHECK CONSTRAINT [FK_Transition_State]
GO

ALTER TABLE [dbo].[Transition]  WITH CHECK ADD  CONSTRAINT [FK_Transition_StateEvent] FOREIGN KEY([eventID])
REFERENCES [dbo].[Event] ([eventID])
GO

ALTER TABLE [dbo].[Transition] CHECK CONSTRAINT [FK_Transition_StateEvent]
GO

ALTER TABLE [dbo].[Transition]  WITH CHECK ADD  CONSTRAINT [FK_Transition_StateMachine] FOREIGN KEY([machineID])
REFERENCES [dbo].[Machine] ([machineID])
GO

ALTER TABLE [dbo].[Transition] CHECK CONSTRAINT [FK_Transition_StateMachine]
GO

-- #Event
CREATE TABLE #Event
(
    assetID INT     ,
    busDate DATETIME,
    eventID INT
)

CREATE CLUSTERED INDEX IX_Ev_assetID ON #Event(assetID)

-- #EFT.AssetState
CREATE TABLE [EFT].[AssetState](
    [assetID] [int] NOT NULL,
    [busDate] [datetime] NOT NULL,
    [machineID] [int] NOT NULL,
    [stateID] [int] NOT NULL,
 CONSTRAINT [PK_AssetState] PRIMARY KEY CLUSTERED
(
    [assetID] ASC,
    [busDate] ASC,
    [machineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_AssetState_assetID] ON [EFT].[AssetState]
(
    [assetID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_AssetState_assetID_stateID] ON [EFT].[AssetState]
(
    [assetID] ASC,
    [stateID] ASC,
    [machineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_AssetState_machineID_stateID_assetID] ON [EFT].[AssetState]
(
    [machineID] ASC,
    [stateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO