CREATE DATABASE DevicesOnMapBackground

CREATE TABLE Users
(
	ID int IDENTITY(1,1) PRIMARY KEY,
	FirstName VARCHAR(50) NOT NULL,
	Surname VARCHAR(50) NOT NULL,
	PasswordHash VARCHAR(50) NOT NULL,
	Email VARCHAR(50) NOT NULL,
	PhoneNumber varchar(50) NOT NULL,
	CreationDate DateTime, 
)

CREATE TABLE Zones
(
	ID int IDENTITY(1,1) PRIMARY KEY,
	Title VARCHAR(50),
	OwnerID int FOREIGN KEY (OwnerID) REFERENCES Users (ID)
)

CREATE TABLE Devices
(
	ID int IDENTITY(1,1) PRIMARY KEY,
	Title VARCHAR(50) NOT NULL,
	Identifier VARCHAR(50) NOT NULL,
	ZoneEmailAlert bit,
	ZoneSMSAlert bit,
	AlertSended bit,
	OwnerID INT FOREIGN KEY (OwnerID) REFERENCES Users (ID)
)

CREATE TABLE DeviceZone
(
	ID int IDENTITY(1,1) PRIMARY KEY,
	IdDevice INT FOREIGN KEY (IdDevice) References Devices (ID),
	IdZone INT FOREIGN KEY (IdZone) References Zones (ID)
)

CREATE TABLE Points
(
	ID int IDENTITY(1,1) PRIMARY KEY,
	Longitude FLOAT NOT NULL,
	Latitude FLOAT NOT NULL,
	CreatedDateTime DateTime,
	DeviceID INT FOREIGN KEY (DeviceID) REFERENCES Devices (ID),
	ZoneID INT FOREIGN KEY (ZoneID) REFERENCES Zones(ID),
	NameLocation varchar(100)
)

CREATE TABLE Groups
(
	ID int IDENTITY(1,1) PRIMARY KEY,
	NameGroup VARCHAR(50) not null,
	OwnerID INT FOREIGN KEY (OwnerID) REFERENCES Users (ID)
)

CREATE TABLE GroupDevice
(
	ID int IDENTITY(1,1) PRIMARY KEY,
	IDGroup INT FOREIGN KEY (IDGroup) REFERENCES Groups (ID),
	IDDevice INT FOREIGN KEY (IDDevice) REFERENCES Devices (ID)
)

CREATE TABLE GroupUsers
(
	ID int IDENTITY(1,1) PRIMARY KEY,
	IDGroup INT FOREIGN KEY (IDGroup) REFERENCES Groups (ID),
	UserID INT FOREIGN KEY (UserID) REFERENCES Users (ID)
)

CREATE TABLE LogBook 
(
	ID int IDENTITY(1,1) PRIMARY KEY,
	Departure DATETIME,
	Arrival DATETIME,
	Purpose VARCHAR(100),
	IdDevice INT FOREIGN KEY (IdDevice) REFERENCES Devices (ID)
)
