if (OBJECT_ID('TrackLog') is not null)
	DROP TABLE TrackLog
GO
if (OBJECT_ID('Track') is not null)
	DROP TABLE Track
GO
if (OBJECT_ID('Evento') is not null)
	DROP TABLE Evento
GO
if (OBJECT_ID('Cuenta') is not null)
	DROP TABLE Cuenta
GO
if (OBJECT_ID('Roles') is not null)
	DROP TABLE Roles
GO
if (OBJECT_ID('Persona') is not null)
	DROP TABLE Persona
GO


CREATE TABLE Persona(
	Id uniqueidentifier NOT NULL,
	Nombre nvarchar(255) NULL,
	Mail nvarchar(255) NULL,
	Twitter nvarchar(255) NULL,
	Blog nvarchar(255) NULL,
	CONSTRAINT PK_Persona PRIMARY KEY (Id),
	CONSTRAINT UK_Persona_Nombre UNIQUE (Nombre),
	CONSTRAINT UK_Persona_Twitter UNIQUE (Twitter))	
GO

CREATE TABLE Roles(
	Persona uniqueidentifier NOT NULL,
	Rol nvarchar(25) NOT NULL,
	CONSTRAINT PK_Roles PRIMARY KEY (Persona, Rol),
	CONSTRAINT FK_Roles_Persona FOREIGN KEY(Persona) REFERENCES Persona (Id) ON DELETE CASCADE
)
GO

CREATE TABLE Evento(
	Id uniqueidentifier NOT NULL,
	Titulo nvarchar(255) NULL,
	FechaInicio datetime NULL,
	FechaTermino datetime NULL,
	UrlInvitacion nvarchar(255) NULL,
	UrlWiki nvarchar(255) NULL,
	NumeroOrden smallint NULL,
	Ponente uniqueidentifier NULL,
	Estado nvarchar(25) NULL,
	Tipo int NULL,
	Duracion bigint NULL,
	CONSTRAINT PK_Evento PRIMARY KEY (Id),
	CONSTRAINT UK_Titulo UNIQUE (Titulo),
	CONSTRAINT FK_Evento_Persona FOREIGN KEY(Ponente) REFERENCES Persona (Id)
)
GO

CREATE TABLE Cuenta(
	Id uniqueidentifier NOT NULL,
	Persona uniqueidentifier NULL,
	LogonName nvarchar(255) NULL,
	IdentityProvider int NULL,
	CONSTRAINT PK_Cuenta PRIMARY KEY (Id),
	CONSTRAINT FK_Cuenta_Persona FOREIGN KEY(Persona) REFERENCES Persona (Id) ON DELETE CASCADE
)
GO

CREATE TABLE Track(
	Id uniqueidentifier NOT NULL,
	Evento uniqueidentifier NULL,
	Accion int NULL,
	Usuario uniqueidentifier NULL,
	CONSTRAINT PK_Track PRIMARY KEY (Id),
	CONSTRAINT FK_Track_Usuario FOREIGN KEY(Usuario)REFERENCES Persona (Id),
	CONSTRAINT FK_Track_Evento FOREIGN KEY(Evento) REFERENCES Evento (Id) ON DELETE CASCADE
)
GO

CREATE TABLE TrackLog(
	Id uniqueidentifier NOT NULL,
	Propietario int NULL,
	Mensaje nvarchar(255) NULL,
	Track uniqueidentifier NULL,
	Usuario uniqueidentifier NULL,
	Successful bit NULL,
	Fecha datetime NULL,
	CONSTRAINT PK_TrackLog PRIMARY KEY (Id),
	CONSTRAINT FK_TrackLog_Usuario FOREIGN KEY(Usuario)REFERENCES Persona (Id),
	CONSTRAINT FK_TrackLog_Track FOREIGN KEY(Track)REFERENCES Track (Id) ON DELETE CASCADE
)
GO