MYDrivingDB.sql

/****** Object:  Table [dbo].[Devices]    Script Date: 3/24/2016 7:53:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Devices]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Devices](
	[Id] [nvarchar](128) NOT NULL,
	[Name] [nvarchar](max) NULL,
	[Version] [timestamp] NOT NULL,
	[CreatedAt] [datetimeoffset](7) NOT NULL,
	[UpdatedAt] [datetimeoffset](7) NULL,
	[Deleted] [bit] NOT NULL,
	[UserProfile_Id] [nvarchar](128) NULL,
 CONSTRAINT [PK_dbo.Devices] PRIMARY KEY NONCLUSTERED
(
	[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO
/****** Object:  Index [IX_CreatedAt]    Script Date: 3/24/2016 7:53:08 PM ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Devices]') AND name = N'IX_CreatedAt')
CREATE CLUSTERED INDEX [IX_CreatedAt] ON [dbo].[Devices]
(
	[CreatedAt] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO
/****** Object:  Table [dbo].[factMLOutputData]    Script Date: 3/24/2016 7:53:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[factMLOutputData]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[factMLOutputData](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[tripId] [nvarchar](50) NULL,
	[userId] [nvarchar](50) NULL,
	[tripstarttime] [nvarchar](50) NULL,
	[driverType] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED
(
	[id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO
/****** Object:  Table [dbo].[IOTHubDatas]    Script Date: 3/24/2016 7:53:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IOTHubDatas]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[IOTHubDatas](
	[Id] [nvarchar](128) NOT NULL,
	[Version] [timestamp] NOT NULL,
	[CreatedAt] [datetimeoffset](7) NOT NULL,
	[UpdatedAt] [datetimeoffset](7) NULL,
	[Deleted] [bit] NOT NULL,
 CONSTRAINT [PK_dbo.IOTHubDatas] PRIMARY KEY NONCLUSTERED
(
	[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO
/****** Object:  Index [IX_CreatedAt]    Script Date: 3/24/2016 7:53:09 PM ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[IOTHubDatas]') AND name = N'IX_CreatedAt')
CREATE CLUSTERED INDEX [IX_CreatedAt] ON [dbo].[IOTHubDatas]
(
	[CreatedAt] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO
/****** Object:  Table [dbo].[POIs]    Script Date: 3/24/2016 7:53:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[POIs]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[POIs](
	[Id] [nvarchar](128) NOT NULL,
	[TripId] [nvarchar](128) NULL,
	[Latitude] [float] NOT NULL,
	[Longitude] [float] NOT NULL,
	[POIType] [int] NOT NULL,
	[RecordedTimeStamp] [nvarchar](50) NULL,
	[Version] [timestamp] NOT NULL,
	[CreatedAt] [datetimeoffset](7) NOT NULL,
	[UpdatedAt] [datetimeoffset](7) NULL,
	[Deleted] [bit] NOT NULL,
	[Timestamp] [datetime] NOT NULL,
 CONSTRAINT [PK_dbo.POIs] PRIMARY KEY NONCLUSTERED
(
	[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO
/****** Object:  Table [dbo].[TripPoints]    Script Date: 3/24/2016 7:53:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TripPoints]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TripPoints](
	[Id] [nvarchar](128) NOT NULL,
	[TripId] [nvarchar](128) NULL,
	[Latitude] [float] NOT NULL,
	[Longitude] [float] NOT NULL,
	[Speed] [float] NOT NULL,
	[RecordedTimeStamp] [datetime] NOT NULL,
	[Sequence] [int] NOT NULL,
	[RPM] [float] NOT NULL,
	[ShortTermFuelBank] [float] NOT NULL,
	[LongTermFuelBank] [float] NOT NULL,
	[ThrottlePosition] [float] NOT NULL,
	[RelativeThrottlePosition] [float] NOT NULL,
	[Runtime] [float] NOT NULL,
	[DistanceWithMalfunctionLight] [float] NOT NULL,
	[EngineLoad] [float] NOT NULL,
	[MassFlowRate] [float] NOT NULL,
	[EngineFuelRate] [float] NOT NULL,
	[VIN] [nvarchar](max) NULL,
	[HasOBDData] [bit] NOT NULL,
	[HasSimulatedOBDData] [bit] NOT NULL,
	[Version] [timestamp] NOT NULL,
	[CreatedAt] [datetimeoffset](7) NOT NULL,
	[UpdatedAt] [datetimeoffset](7) NULL,
	[Deleted] [bit] NOT NULL,
 CONSTRAINT [PK_dbo.TripPoints] PRIMARY KEY NONCLUSTERED
(
	[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO
/****** Object:  Index [IX_CreatedAt]    Script Date: 3/24/2016 7:53:13 PM ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TripPoints]') AND name = N'IX_CreatedAt')
CREATE CLUSTERED INDEX [IX_CreatedAt] ON [dbo].[TripPoints]
(
	[CreatedAt] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO
/****** Object:  Table [dbo].[Trips]    Script Date: 3/24/2016 7:53:13 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Trips]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Trips](
	[Id] [nvarchar](128) NOT NULL,
	[Name] [nvarchar](max) NULL,
	[UserId] [nvarchar](128) NULL,
	[RecordedTimeStamp] [datetime] NOT NULL,
	[EndTimeStamp] [datetime] NOT NULL,
	[Rating] [int] NOT NULL,
	[IsComplete] [bit] NOT NULL,
	[HasSimulatedOBDData] [bit] NOT NULL,
	[AverageSpeed] [float] NOT NULL,
	[FuelUsed] [float] NOT NULL,
	[HardStops] [bigint] NOT NULL,
	[HardAccelerations] [bigint] NOT NULL,
	[MainPhotoUrl] [nvarchar](max) NULL,
	[Distance] [float] NOT NULL,
	[Version] [timestamp] NOT NULL,
	[CreatedAt] [datetimeoffset](7) NOT NULL,
	[UpdatedAt] [datetimeoffset](7) NULL,
	[Deleted] [bit] NOT NULL,
 CONSTRAINT [PK_dbo.Trips] PRIMARY KEY NONCLUSTERED
(
	[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO
/****** Object:  Index [IX_CreatedAt]    Script Date: 3/24/2016 7:53:15 PM ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Trips]') AND name = N'IX_CreatedAt')
CREATE CLUSTERED INDEX [IX_CreatedAt] ON [dbo].[Trips]
(
	[CreatedAt] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO
/****** Object:  Table [dbo].[UserProfiles]    Script Date: 3/24/2016 7:53:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserProfiles]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[UserProfiles](
	[Id] [nvarchar](128) NOT NULL,
	[FirstName] [nvarchar](max) NULL,
	[LastName] [nvarchar](max) NULL,
	[UserId] [nvarchar](max) NULL,
	[ProfilePictureUri] [nvarchar](max) NULL,
	[Rating] [int] NOT NULL,
	[Ranking] [int] NOT NULL,
	[TotalDistance] [float] NOT NULL,
	[TotalTrips] [bigint] NOT NULL,
	[TotalTime] [bigint] NOT NULL,
	[HardStops] [bigint] NOT NULL,
	[HardAccelerations] [bigint] NOT NULL,
	[FuelConsumption] [float] NOT NULL,
	[MaxSpeed] [float] NOT NULL,
	[Version] [timestamp] NOT NULL,
	[CreatedAt] [datetimeoffset](7) NOT NULL,
	[UpdatedAt] [datetimeoffset](7) NULL,
	[Deleted] [bit] NOT NULL,
 CONSTRAINT [PK_dbo.UserProfiles] PRIMARY KEY NONCLUSTERED
(
	[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO
/****** Object:  Index [IX_CreatedAt]    Script Date: 3/24/2016 7:53:17 PM ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[UserProfiles]') AND name = N'IX_CreatedAt')
CREATE CLUSTERED INDEX [IX_CreatedAt] ON [dbo].[UserProfiles]
(
	[CreatedAt] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [IX_UserProfile_Id]    Script Date: 3/24/2016 7:53:17 PM ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Devices]') AND name = N'IX_UserProfile_Id')
CREATE NONCLUSTERED INDEX [IX_UserProfile_Id] ON [dbo].[Devices]
(
	[UserProfile_Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [IX_TripId]    Script Date: 3/24/2016 7:53:17 PM ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TripPoints]') AND name = N'IX_TripId')
CREATE NONCLUSTERED INDEX [IX_TripId] ON [dbo].[TripPoints]
(
	[TripId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO
--IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DF__Devices__Id__4E88ABD4]') AND type = 'D')
IF (SELECT object_definition(default_object_id) AS definition FROM sys.columns WHERE name ='Id' AND object_id = object_id('dbo.Devices')) IS NULL
BEGIN
ALTER TABLE [dbo].[Devices] ADD  DEFAULT (newid()) FOR [Id]
END

GO
--IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DF__Devices__Created__4F7CD00D]') AND type = 'D')
IF (SELECT object_definition(default_object_id) AS definition FROM sys.columns WHERE name ='CreatedAt' AND object_id = object_id('dbo.Devices')) IS NULL
BEGIN
ALTER TABLE [dbo].[Devices] ADD  DEFAULT (sysutcdatetime()) FOR [CreatedAt]
END

GO
--IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DF__IOTHubDatas__Id__36B12243]') AND type = 'D')
IF (SELECT object_definition(default_object_id) AS definition FROM sys.columns WHERE name ='Id' AND object_id = object_id('dbo.IOTHubDatas')) IS NULL
BEGIN
ALTER TABLE [dbo].[IOTHubDatas] ADD  DEFAULT (newid()) FOR [Id]
END

GO
--IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DF__IOTHubDat__Creat__37A5467C]') AND type = 'D')
IF (SELECT object_definition(default_object_id) AS definition FROM sys.columns WHERE name ='CreatedAt' AND object_id = object_id('dbo.IOTHubDatas')) IS NULL
BEGIN
ALTER TABLE [dbo].[IOTHubDatas] ADD  DEFAULT (sysutcdatetime()) FOR [CreatedAt]
END

GO
--IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DF__POIs__Id__3B75D760]') AND type = 'D')
IF (SELECT object_definition(default_object_id) AS definition FROM sys.columns WHERE name ='Id' AND object_id = object_id('dbo.POIs')) IS NULL
BEGIN
ALTER TABLE [dbo].[POIs] ADD  DEFAULT (newid()) FOR [Id]
END

GO
--IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DF__POIs__CreatedAt__3C69FB99]') AND type = 'D')
IF (SELECT object_definition(default_object_id) AS definition FROM sys.columns WHERE name ='CreatedAt' AND object_id = object_id('dbo.POIs')) IS NULL
BEGIN
ALTER TABLE [dbo].[POIs] ADD  DEFAULT (sysutcdatetime()) FOR [CreatedAt]
END

GO
--IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DF__POIs__Timestamp__5535A963]') AND type = 'D')
IF (SELECT object_definition(default_object_id) AS definition FROM sys.columns WHERE name ='Timestamp' AND object_id = object_id('dbo.POIs')) IS NULL
BEGIN
ALTER TABLE [dbo].[POIs] ADD  DEFAULT ('1900-01-01T00:00:00.000') FOR [Timestamp]
END

GO
--IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DF__TripPoints__Id__403A8C7D]') AND type = 'D')
IF (SELECT object_definition(default_object_id) AS definition FROM sys.columns WHERE name ='Id' AND object_id = object_id('dbo.TripPoints')) IS NULL
BEGIN
ALTER TABLE [dbo].[TripPoints] ADD  DEFAULT (newid()) FOR [Id]
END

GO
--IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DF__TripPoint__Creat__412EB0B6]') AND type = 'D')
IF (SELECT object_definition(default_object_id) AS definition FROM sys.columns WHERE name ='CreatedAt' AND object_id = object_id('dbo.TripPoints')) IS NULL
BEGIN
ALTER TABLE [dbo].[TripPoints] ADD  DEFAULT (sysutcdatetime()) FOR [CreatedAt]
END

GO
--IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DF__Trips__Id__44FF419A]') AND type = 'D')
IF (SELECT object_definition(default_object_id) AS definition FROM sys.columns WHERE name ='Id' AND object_id = object_id('dbo.Trips')) IS NULL
BEGIN
ALTER TABLE [dbo].[Trips] ADD  DEFAULT (newid()) FOR [Id]
END

GO
--IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DF__Trips__CreatedAt__45F365D3]') AND type = 'D')
IF (SELECT object_definition(default_object_id) AS definition FROM sys.columns WHERE name ='CreatedAt' AND object_id = object_id('dbo.Trips')) IS NULL
BEGIN
ALTER TABLE [dbo].[Trips] ADD  DEFAULT (sysutcdatetime()) FOR [CreatedAt]
END

GO
--IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DF__UserProfiles__Id__49C3F6B7]') AND type = 'D')
IF (SELECT object_definition(default_object_id) AS definition FROM sys.columns WHERE name ='Id' AND object_id = object_id('dbo.UserProfiles')) IS NULL
BEGIN
ALTER TABLE [dbo].[UserProfiles] ADD  DEFAULT (newid()) FOR [Id]
END

GO
--IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DF__UserProfi__Creat__4AB81AF0]') AND type = 'D')
IF (SELECT object_definition(default_object_id) AS definition FROM sys.columns WHERE name ='CreatedAt' AND object_id = object_id('dbo.UserProfiles')) IS NULL
BEGIN
ALTER TABLE [dbo].[UserProfiles] ADD  DEFAULT (sysutcdatetime()) FOR [CreatedAt]
END

GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_dbo.Devices_dbo.UserProfiles_UserProfile_Id]') AND parent_object_id = OBJECT_ID(N'[dbo].[Devices]'))
ALTER TABLE [dbo].[Devices]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Devices_dbo.UserProfiles_UserProfile_Id] FOREIGN KEY([UserProfile_Id])
REFERENCES [dbo].[UserProfiles] ([Id])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_dbo.Devices_dbo.UserProfiles_UserProfile_Id]') AND parent_object_id = OBJECT_ID(N'[dbo].[Devices]'))
ALTER TABLE [dbo].[Devices] CHECK CONSTRAINT [FK_dbo.Devices_dbo.UserProfiles_UserProfile_Id]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_dbo.TripPoints_dbo.Trips_TripId]') AND parent_object_id = OBJECT_ID(N'[dbo].[TripPoints]'))
ALTER TABLE [dbo].[TripPoints]  WITH CHECK ADD  CONSTRAINT [FK_dbo.TripPoints_dbo.Trips_TripId] FOREIGN KEY([TripId])
REFERENCES [dbo].[Trips] ([Id])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_dbo.TripPoints_dbo.Trips_TripId]') AND parent_object_id = OBJECT_ID(N'[dbo].[TripPoints]'))
ALTER TABLE [dbo].[TripPoints] CHECK CONSTRAINT [FK_dbo.TripPoints_dbo.Trips_TripId]
GO
/****** Object:  Trigger [dbo].[TR_dbo_Devices_InsertUpdateDelete]    Script Date: 3/24/2016 7:53:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TR_dbo_Devices_InsertUpdateDelete]'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER [dbo].[TR_dbo_Devices_InsertUpdateDelete] ON [dbo].[Devices] AFTER INSERT, UPDATE, DELETE AS BEGIN UPDATE [dbo].[Devices] SET [dbo].[Devices].[UpdatedAt] = CONVERT(DATETIMEOFFSET, SYSUTCDATETIME()) FROM INSERTED WHERE inserted.[Id] = [dbo].[Devices].[Id] END'
GO
ALTER TABLE [dbo].[Devices] ENABLE TRIGGER [TR_dbo_Devices_InsertUpdateDelete]
GO
/****** Object:  Trigger [dbo].[UpdateRatings]    Script Date: 3/24/2016 7:53:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[UpdateRatings]'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER [dbo].[UpdateRatings] ON [dbo].[factMLOutputData]
FOR INSERT
AS
        -- Do it for all rows (maybe bulk insert) inserted
        DECLARE crs CURSOR FOR
                SELECT userId FROM inserted

        DECLARE @UId varchar(100)

        OPEN crs
        FETCH NEXT FROM crs INTO @UId
        WHILE @@FETCH_STATUS = 0
        BEGIN

				WITH all_scores AS (
						SELECT userId, COUNT(driverType) as a_s FROM dbo.factMLOutputData GROUP BY userId),
					good_scores AS (
						SELECT userId, COUNT(driverType) as g_s FROM dbo.factMLOutputData WHERE driverType = ''Good'' GROUP BY userId),
					ratings AS (
						SELECT all_scores.userId as ui, CAST( (100 * g_s)/a_s AS INT) as r FROM all_scores, good_scores WHERE all_scores.userId = good_scores.userId)

				UPDATE UserProfiles SET UserProfiles.Rating = ratings.r FROM ratings WHERE UserProfiles.UserId = ratings.ui AND UserProfiles.UserId = @UId;

               FETCH NEXT FROM crs INTO @UId
        END
        CLOSE crs
        DEALLOCATE crs'
GO
ALTER TABLE [dbo].[factMLOutputData] ENABLE TRIGGER [UpdateRatings]
GO
/****** Object:  Trigger [dbo].[TR_dbo_IOTHubDatas_InsertUpdateDelete]    Script Date: 3/24/2016 7:53:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TR_dbo_IOTHubDatas_InsertUpdateDelete]'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER [dbo].[TR_dbo_IOTHubDatas_InsertUpdateDelete] ON [dbo].[IOTHubDatas] AFTER INSERT, UPDATE, DELETE AS BEGIN UPDATE [dbo].[IOTHubDatas] SET [dbo].[IOTHubDatas].[UpdatedAt] = CONVERT(DATETIMEOFFSET, SYSUTCDATETIME()) FROM INSERTED WHERE inserted.[Id] = [dbo].[IOTHubDatas].[Id] END'
GO
ALTER TABLE [dbo].[IOTHubDatas] ENABLE TRIGGER [TR_dbo_IOTHubDatas_InsertUpdateDelete]
GO
/****** Object:  Trigger [dbo].[UpdateUserProfilesOnInsert]    Script Date: 3/24/2016 7:53:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object:  Trigger [dbo].[TR_dbo_TripPoints_InsertUpdateDelete]    Script Date: 3/24/2016 7:53:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TR_dbo_TripPoints_InsertUpdateDelete]'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER [dbo].[TR_dbo_TripPoints_InsertUpdateDelete] ON [dbo].[TripPoints] AFTER INSERT, UPDATE, DELETE AS BEGIN UPDATE [dbo].[TripPoints] SET [dbo].[TripPoints].[UpdatedAt] = CONVERT(DATETIMEOFFSET, SYSUTCDATETIME()) FROM INSERTED WHERE inserted.[Id] = [dbo].[TripPoints].[Id] END'
GO
ALTER TABLE [dbo].[TripPoints] ENABLE TRIGGER [TR_dbo_TripPoints_InsertUpdateDelete]
GO
/****** Object:  Trigger [dbo].[TR_dbo_Trips_InsertUpdateDelete]    Script Date: 3/24/2016 7:53:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TR_dbo_Trips_InsertUpdateDelete]'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER [dbo].[TR_dbo_Trips_InsertUpdateDelete] ON [dbo].[Trips] AFTER INSERT, UPDATE, DELETE AS BEGIN UPDATE [dbo].[Trips] SET [dbo].[Trips].[UpdatedAt] = CONVERT(DATETIMEOFFSET, SYSUTCDATETIME()) FROM INSERTED WHERE inserted.[Id] = [dbo].[Trips].[Id] END'
GO
ALTER TABLE [dbo].[Trips] ENABLE TRIGGER [TR_dbo_Trips_InsertUpdateDelete]
GO
/****** Object:  Trigger [dbo].[TR_dbo_UserProfiles_InsertUpdateDelete]    Script Date: 3/24/2016 7:53:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TR_dbo_UserProfiles_InsertUpdateDelete]'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER [dbo].[TR_dbo_UserProfiles_InsertUpdateDelete] ON [dbo].[UserProfiles] AFTER INSERT, UPDATE, DELETE AS BEGIN UPDATE [dbo].[UserProfiles] SET [dbo].[UserProfiles].[UpdatedAt] = CONVERT(DATETIMEOFFSET, SYSUTCDATETIME()) FROM INSERTED WHERE inserted.[Id] = [dbo].[UserProfiles].[Id] END'
GO
ALTER TABLE [dbo].[UserProfiles] ENABLE TRIGGER [TR_dbo_UserProfiles_InsertUpdateDelete]
GO


/******* Adding Additional Tables Needed for Simulator *********************/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;



GO
PRINT N'Creating [dbo].[POISource]...';


GO
CREATE TABLE [dbo].[POISource] (
    [Id]                NVARCHAR (128) NOT NULL,
    [TripId]            NVARCHAR (MAX) NULL,
    [Latitude]          FLOAT (53)     NOT NULL,
    [Longitude]         FLOAT (53)     NOT NULL,
    [POIType]           INT            NOT NULL,
    [RecordedTimeStamp] NVARCHAR (50)  NULL,
    CONSTRAINT [PK_POISource_ID] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [dbo].[TripPointSource]...';


GO
CREATE TABLE [dbo].[TripPointSource] (
    [tripid]                   VARCHAR (36)     NOT NULL,
    [userid]                   VARCHAR (33)     NOT NULL,
    [name]                     VARCHAR (30)     NULL,
    [trippointid]              VARCHAR (36)     NOT NULL,
    [lat]                      NUMERIC (18, 15) NOT NULL,
    [lon]                      NUMERIC (19, 14) NOT NULL,
    [speed]                    INT              NOT NULL,
    [recordedtimestamp]        VARCHAR (28)     NOT NULL,
    [sequence]                 INT              NOT NULL,
    [enginerpm]                INT              NOT NULL,
    [shorttermfuelbank]        INT              NOT NULL,
    [longtermfuelbank]         INT              NOT NULL,
    [throttleposition]         INT              NOT NULL,
    [relativethrottleposition] INT              NOT NULL,
    [runtime]                  INT              NOT NULL,
    [distancewithmil]          INT              NOT NULL,
    [engineload]               INT              NOT NULL,
    [mafflowrate]              INT              NOT NULL,
    [outsidetemperature]       VARCHAR (30)     NULL,
    [enginefuelrate]           INT              NOT NULL,
    [FIELD21]                  INT              NULL,
    PRIMARY KEY CLUSTERED ([trippointid] ASC)
);


GO
PRINT N'Creating [dbo].[POIs].[nci_wi_POIs_55307BFB0FFEEFC3E0238C0CC3B42651]...';


GO
CREATE NONCLUSTERED INDEX [nci_wi_POIs_55307BFB0FFEEFC3E0238C0CC3B42651]
    ON [dbo].[POIs]([POIType] ASC)
    INCLUDE([TripId]);


GO
PRINT N'Creating unnamed constraint on [dbo].[IOTHubDatas]...';


GO
ALTER TABLE [dbo].[IOTHubDatas]
    ADD DEFAULT ((0)) FOR [Deleted];


GO
PRINT N'Creating unnamed constraint on [dbo].[POIs]...';


GO
ALTER TABLE [dbo].[POIs]
    ADD DEFAULT ((0)) FOR [Deleted];


GO
PRINT N'Altering [dbo].[UpdateUserProfilesOnInsert]...';


GO
ALTER TRIGGER [dbo].[UpdateUserProfilesOnInsert] ON [dbo].[POIs]
FOR INSERT
AS
        -- Do it for all rows inserted (maybe bulk insert)
        DECLARE crs CURSOR FOR
                SELECT TripId FROM inserted

        DECLARE @TId nvarchar(100)

        OPEN crs
        FETCH NEXT FROM crs INTO @TId
        WHILE @@FETCH_STATUS = 0
        BEGIN

               -- Update Accelerations
               WITH ascnt1 AS (
                                  SELECT        p.TripId, up.UserId, COUNT(p.POIType) AS cnt1
                                  FROM            dbo.POIs AS p INNER JOIN
                                                                           dbo.Trips AS t ON p.TripId = t.Id INNER JOIN
                                                                           dbo.UserProfiles AS up ON t.UserId = up.UserId
                                  WHERE        (p.POIType = 1)
                                  GROUP BY p.TripId, up.UserId
               )
               UPDATE dbo.UserProfiles SET dbo.UserProfiles.HardAccelerations = ascnt1.cnt1 FROM ascnt1
               WHERE ascnt1.TripId = @TId AND dbo.UserProfiles.UserId = ascnt1.UserId;

               -- Update Hard Stops
               WITH ascnt2 AS (
                                  SELECT        p.TripId, up.UserId, COUNT(p.POIType) AS cnt2
                                  FROM            dbo.POIs AS p INNER JOIN
                                                                           dbo.Trips AS t ON p.TripId = t.Id INNER JOIN
                                                                           dbo.UserProfiles AS up ON t.UserId = up.UserId
                                  WHERE        (p.POIType = 2)
                                  GROUP BY p.TripId, up.UserId
               )
               UPDATE dbo.UserProfiles SET dbo.UserProfiles.HardStops = ascnt2.cnt2 FROM ascnt2
               WHERE ascnt2.TripId = @TId AND dbo.UserProfiles.UserId = ascnt2.UserId;


               FETCH NEXT FROM crs INTO @TId
        END
        CLOSE crs
        DEALLOCATE crs
GO
PRINT N'Creating [dbo].[GetPoisForTrip]...';


GO

PRINT N'Update complete.';


GO