Social Network

CREATE DATABASE RedSocial
GO
USE RedSocial
GO
--------------------------------------------TABLAS / REGISTROS-------------------------------------------------
CREATE TABLE Idioma (
    ID          INT IDENTITY(1,1) PRIMARY KEY,
    Nombre      VARCHAR(50)
)
GO
INSERT INTO Idioma VALUES ('Español'),('Ingles'),('Aleman'),('Frances')
GO
CREATE TABLE Pais(
    ID          INT IDENTITY(1,1) PRIMARY KEY,
    Nombre      VARCHAR(50)
)
INSERT INTO Pais VALUES ('Rep. Dom.'),('España'), ('Alemania'),('Francia')
GO
CREATE TABLE Usuarios(
    ID                  INT IDENTITY(1,1) PRIMARY KEY,
    ID_Idioma           INT NOT NULL      FOREIGN KEY REFERENCES Idioma,
    Usuario             VARCHAR(50)       NOT NULL,
    Contraseña         VARCHAR(50)       NOT NULL,
    Nombre              VARCHAR(50)       NOT NULL,
    Apellido            VARCHAR(50)       NOT NULL,
    Correo              VARCHAR(100)      NOT NULL,
    Foto                VARCHAR(100)      NOT NULL,
    Fecha_Registro      DATE DEFAULT      GETDATE() NOT NULL,
    Conectado           BIT DEFAULT 0     NOT NULL,
    Estado              BIT DEFAULT 1     NOT NULL,
)
GO
INSERT INTO  Usuarios (Usuario,      ID_Idioma,   Contraseña, Nombre,     Apellido, Correo, Foto) VALUES
('beni',    '2', '123', 'Bernard',  'Wiesner',   'link@yahoo.com',       'files/perfil_beni.jpg'),
('felix',   '1', '123', 'Felix',    'Manuel',    'felix@yahoo.com',      'files/perfil_felix.jpg'),
('max',     '1', '123', 'Maxwell',  'Fereirras', 'maxwell@yahoo.com',    'files/perfil_max.jpg'),
('jenny',   '3', '123', 'Jennifer', 'Fernandez', 'jennifer@hotmail.com', 'files/perfil_jennifer.jpg'),
('caludia', '4', '123', 'Claudia',  'Leduc',     'claudia@yahoo.com',    'files/perfil_claudia.jpg')
GO
CREATE TABLE Perfil(
    ID                  INT IDENTITY(1,1) Primary Key,
    Sobre_Mi            VARCHAR(200)      NOT NULL,
    Genero              VARCHAR(20)       NOT NULL,
    Relacion            VARCHAR(50),
    Telefono            VARCHAR(30),
    Intereses           VARCHAR(200),
    Educacion           VARCHAR(200),
    Hobbies             VARCHAR(200),
    Direccion           VARCHAR(100),
    Fecha_Nacimiento    DATE NOT NULL,
    ID_Pais             INT  NOT NULL     FOREIGN KEY REFERENCES Pais(ID),
    ID_Usuario          INT               FOREIGN KEY REFERENCES Usuarios(ID)
)
GO
INSERT INTO Perfil (Sobre_Mi, Genero, Relacion, Telefono, Intereses, Educacion, Hobbies, Direccion, Fecha_Nacimiento, ID_Pais, ID_Usuario) VALUES
('Mi nombre es Bernard, soy un estudiante del itla...',  'Masculino', 'Casado',   '809-374-2541',   'IT',         'Desarrollo de Software',  'Football',         'calle 12, #5',   '1989-10-16', '3', '1'),
('Soy Felix, soy un estudiante del itla...',             'Masculino', 'Soltero',  '809-222-3333',   'IT',         'Desarrollo de Software',  'Video Juegos',     'calle 2,  #10',  '1997-01-10', '1', '2'),
('Me llamo Mawwell, soy un estudiante del itla...',      'Masculino', 'Soltero',  '809-123-1234',   'IT',         'Desarrollo de Software',  'Programacion',     'calle 28, #16',  '1998-03-12', '1', '3'),
('Soy Jennifer, soy un estudiante de la UASD...',        'Femenino',  'Soltera',  '809-852,9874',   'Medicina',   'Enfermera',               'Peliculas',        'calle 15, #1',   '1995-05-03', '2', '4'),
('Soy Claudia, soy francesa...',                         'Femenino',  'Casada',   '809-789-3214',   'Fotografia', 'Fotografa',               'Fotos, Editacion', 'calle 3,  #16',  '1960-03-01', '4', '5')
GO
CREATE TABLE Contacto(
    ID                  INT IDENTITY(1,1) PRIMARY KEY,
    ID_Usuario          INT FOREIGN KEY   REFERENCES Usuarios(ID),
    ID_Otro_Uuario      INT,
    Estado              INT
)
GO
INSERT INTO Contacto (ID_Usuario, ID_Otro_Uuario) VALUES (1, 2), (1, 3), (4, 5)
GO
CREATE TABLE Publicaciones(
    ID                  INT IDENTITY(1,1) PRIMARY KEY,
    ID_Usuario          INT FOREIGN KEY   REFERENCES Usuarios(ID),
    Mensaje             VARCHAR(200)      NOT NULL,
    Fecha               DATE DEFAULT      GETDATE() NOT NULL,
    Estado              BIT  DEFAULT 1
)
GO
INSERT INTO Publicaciones (ID_Usuario, Mensaje) VALUES (1, 'Me gusta el footbal'), (2, 'Me gusta los video juegos'),
(1, 'Me gusta Neuer, el jugador de football'),
(3, 'Hoy asisti a una operacion')
GO
CREATE TABLE Fotos(
    ID                  INT IDENTITY(1,1) PRIMARY KEY,
    ID_Publicacion      INT FOREIGN KEY   REFERENCES Publicaciones(ID),
    Url_Foto            VARCHAR(100)      NOT NULL
)
GO
CREATE TABLE Comentario_Foto(
    ID                  INT IDENTITY (1,1) PRIMARY KEY,
    Comentario          VARCHAR(200)       NOT NULL,
    Fecha               DATE DEFAULT       GETDATE() NOT NULL,
    ID_Foto             INT FOREIGN KEY    REFERENCES Fotos(ID),
    ID_Usuario          INT FOREIGN KEY    REFERENCES Usuarios(ID),
    Estado              BIT DEFAULT 1      NOT NULL
)
GO
CREATE TABLE Comentario_Publicacion(
    ID                  INT IDENTITY (1,1) PRIMARY KEY,
    Comentario          VARCHAR(200)       NOT NULL,
    Fecha               DATE DEFAULT       GETDATE() NOT NULL,
    ID_Publicacion      INT FOREIGN KEY    REFERENCES Publicaciones(ID),
    ID_Usuario          INT FOREIGN KEY    REFERENCES Usuarios(ID),
    Estado              BIT DEFAULT 1      NOT NULL
)
GO
CREATE TABLE Notificaciones(
    ID                  INT IDENTITY (1,1) PRIMARY KEY,
    Mensaje             VARCHAR(100)       NOT NULL,
    Fecha               DATE DEFAULT       GETDATE() NOT NULL,
    ID_Usuario          INT FOREIGN KEY    REFERENCES Usuarios(ID)
)
GO
CREATE TABLE Monitoreo_Faltas(
    ID                  INT IDENTITY (1,1) PRIMARY KEY,
    ID_Usuario          INT FOREIGN KEY    REFERENCES Usuarios(ID)
    Fecha               DATE DEFAULT       GETDATE() NOT NULL,
    Nombre              VARCHAR(50)        NOT NULL,
    Apellido            VARCHAR(50)        NOT NULL,
    Correo              VARCHAR(100)       NOT NULL,
    Mensaje             VARCHAR(200)       NOT NULL
)
GO
CREATE TABLE Chat(
    ID                  INT IDENTITY (1,1) PRIMARY KEY,
    Fecha               DATE DEFAULT       GETDATE() NOT NULL,
    Mensaje             VARCHAR(100)       NOT NULL,
    ID_Usuario          INT FOREIGN KEY    REFERENCES Usuarios(ID)
    ID_Otro_Usuario     INT,
    Estado              BIT DEFAULT 1
)
GO
INSERT INTO Chat (Mensaje, ID_Usuario, ID_Otro_Usuario) VALUES('Hola Bernard',2,1),('Como estas Felix',1,2),
('Espero que estes bien',1,2), ('Hola Maxwell',1,3)
GO
CREATE TABLE  Historico(
    ID                  INT IDENTITY (1,1) PRIMARY KEY,
    Mensaje             VARCHAR(100)       NOT NULL,
    Fecha               DATE               NOT NULL,
    ID_Usuario          INT FOREIGN KEY    REFERENCES Usuarios(ID)
)
GO
-------------------------------------CONSULTAS-----------------------------------------

---1. Buscar los contactos de usuarios e indicar si está conectado o no.

SELECT Usuario, (SELECT Usuario FROM Usuarios WHERE Usuarios.ID = Contacto.ID_Otro_Uuario) AS Amigo, Conectado from Usuarios INNER JOIN Contacto ON (Usuarios.ID = Contacto.ID_Usuario)

---2. Buscar los usuarios registrados en los ultimos 2 meses
select Usuario FROM Usuarios WHERE MONTH(Fecha_Registro) > MONTH(getdate())-2

---3. Usuarios activos que no son tus contactos.
SELECT
    a.id,
    a.usuario,
    a.estado
FROM
    Usuarios a
LEFT JOIN
    Contacto b ON
        a.id IN (b.id_usuario, b.ID_Otro_Uuario) AND
        1 IN (b.id_usuario, b.ID_Otro_Uuario)
WHERE
    (b.ID_Otro_Uuario IS NULL OR b.estado <> 2) AND
    a.id <> 1 AND
    a.estado IS NOT NULL


---4. Listar los mensajes enviados a un determinado usuario.
SELECT Mensaje FROM Chat INNER JOIN Usuarios ON (Chat.ID_Usuario = Usuarios.ID) AND (Chat.ID_Otro_Usuario = 2)


----------------------------CASOS PROGRAMACION-----------------------------------

--Usted debe crear un procedimiento almacenado que genere un reporte  con la cantidad de conexiones por año.
CREATE PROCEDURE SP1_Conexiones
AS
SELECT DB_NAME(dbid) AS DBName, COUNT(dbid) AS NumeroDeConexiones, YEAR(login_time) AS Año
FROM sys.sysprocesses
WHERE dbid = DB_ID('RedSocial')
GROUP BY dbid, loginame, YEAR(login_time)
GO
Exec SP1_Conexiones

--Los mensajes escritos por un usuario pueden ser borrados por esta persona, pero el sistema debe mantenerlos en sus datos históricos, para esto usted debe crear un "Trigger" que permita esta funcionalidad.-

ALTER TRIGGER T1_MSG ON Chat
AFTER DELETE
AS
BEGIN
SET IDENTITY_INSERT Historico ON
declare @id int
declare @fecha date
declare @mensaje varchar(1000)
declare @id_usuario int
Select @id = id From deleted
Select @fecha = fecha From deleted
Select @mensaje = mensaje From deleted
Select @id_usuario = id_usuario from deleted
INSERT INTO Historico (id , mensaje, fecha, id_usuario) values (@id, @mensaje, @fecha, @id_usuario)
SET IDENTITY_INSERT Historico OFF
END