CREATE DATABASE bd_blog_viernes;

USE bd_blog_viernes;

/* Tablas */
CREATE TABLE privilegio(
    id INT AUTO_INCREMENT,
    descripcion VARCHAR(100),
    PRIMARY KEY(id)
); -- Administrador, Moderador, Normal

CREATE TABLE etiqueta(
    id INT AUTO_INCREMENT,
    nombre VARCHAR(100),
    PRIMARY KEY(id)
);

CREATE TABLE pais(
    id INT AUTO_INCREMENT,
    nombre VARCHAR(100),
    PRIMARY KEY(id)
);

CREATE TABLE usuario(
    id INT AUTO_INCREMENT,
    nombre VARCHAR(100),
    apellido_paterno VARCHAR(100),
    apellido_materno VARCHAR(100),
    nick VARCHAR(100),
    contrasenia VARCHAR(100),
    correo VARCHAR(100),
    fecha_nacimiento DATE,
    fk_pais INT,
    fk_privilegio INT,
    PRIMARY KEY(id),
    FOREIGN KEY(fk_pais) REFERENCES pais(id),
    FOREIGN KEY(fk_privilegio) REFERENCES privilegio(id),
    UNIQUE(nick),
    UNIQUE(correo)
);

CREATE TABLE publicacion(
    id INT AUTO_INCREMENT,
    titulo VARCHAR(100),
    mensaje VARCHAR(5000),
    fecha DATETIME,
    fk_usuario INT,
    PRIMARY KEY(id),
    FOREIGN KEY(fk_usuario) REFERENCES usuario(id)
);

CREATE TABLE comentario(
    id INT AUTO_INCREMENT,
    texto TEXT,
    fecha DATETIME,
    fk_usuario INT,
    fk_publicacion INT,
    PRIMARY KEY(id),
    FOREIGN KEY(fk_usuario) REFERENCES usuario(id),
    FOREIGN KEY(fk_publicacion) REFERENCES publicacion(id)
);

CREATE TABLE penalizado(
    id INT AUTO_INCREMENT,
    fk_castigado INT,
    fk_verdugo INT,
    fecha DATETIME,
    motivo VARCHAR(1000),
    fecha_alza DATETIME,
    PRIMARY KEY(id),
    FOREIGN KEY(fk_castigado) REFERENCES usuario(id),
    FOREIGN KEY(fk_verdugo) REFERENCES usuario(id)
);

CREATE TABLE publi_etiqueta(
    id INT AUTO_INCREMENT,
    fk_publicacion INT,
    fk_etiqueta INT,
    PRIMARY KEY(id),
    FOREIGN KEY(fk_publicacion) REFERENCES publicacion(id),
    FOREIGN KEY(fk_etiqueta) REFERENCES etiqueta(id)
);
/* Tablas */

/*insert de privilegio*/
INSERT INTO privilegio VALUES(NULL, 'Adminstrador');
INSERT INTO privilegio VALUES(NULL, 'Moderador');
INSERT INTO privilegio VALUES(NULL, 'Normal');

/*insert de 10 etiquetas*/

INSERT INTO etiqueta VALUES(NULL, 'Deporte');
INSERT INTO etiqueta VALUES(NULL, 'Computacion');
INSERT INTO etiqueta VALUES(NULL, 'Rancagua');
INSERT INTO etiqueta VALUES(NULL, 'Rancagua no existe');
INSERT INTO etiqueta VALUES(NULL, 'Comida');
INSERT INTO etiqueta VALUES(NULL, 'Derechos digitales');
INSERT INTO etiqueta VALUES(NULL, 'Reclamos');
INSERT INTO etiqueta VALUES(NULL, 'Importante');
INSERT INTO etiqueta VALUES(NULL, 'Aliens');
INSERT INTO etiqueta VALUES(NULL, 'Estay reptiliano');

INSERT INTO pais VALUES (NULL,'Chile');
INSERT INTO pais VALUES (NULL,'Argentina');
INSERT INTO pais VALUES (NULL,'México');
INSERT INTO pais VALUES (NULL,'España');
INSERT INTO pais VALUES (NULL,'Italia');
INSERT INTO pais VALUES (NULL,'Francia');
INSERT INTO pais VALUES (NULL,'Alemania');
INSERT INTO pais VALUES (NULL,'China');
INSERT INTO pais VALUES (NULL,'Rusia');
INSERT INTO pais VALUES (NULL,'Inglaterra');

-- Tapia
INSERT INTO usuario VALUES (NULL,'Patricio','Peréz','Pinto','pperezp','123456','patricioperez@gmail.com','1993-08-15','2','1');
INSERT INTO usuario VALUES (NULL,'Flavio','Toro','Basso','fulvio','654321','fulvio12@gmail.com','1995-09-30','5','1');
INSERT INTO usuario VALUES (NULL,'Felipe','Tapia','Rojas','footchicken','asd123','felipe.tapia@gmail.com','1947-03-23','7','1');

-- Torres
INSERT INTO usuario VALUES (NULL, 'juancho', 'diaz', 'vergara', 'juanMadafaka', 'juan54321', 'anchooa@gmail.com', '1996-10-02', '3', '2');
INSERT INTO usuario VALUES (NULL, 'lucario', 'soto', 'toro', 'lucasDIE', 'lucas54321', 'lucario123@gmail.com', '1996-08-22', '6', '2');
INSERT INTO usuario VALUES (NULL, 'pomelo', 'schwarzenegger', 'bross', 'malditoschwarzenegger', 'pompom54321', 'pompom@gmail.com', '1995-5-07', '7', '2');

-- ICIN
INSERT INTO usuario VALUES (NULL,'Cesar','Aros','Moraga','kirilink','cesararos','kirilinkpro@gmail.com','1999-09-13','5','3');
INSERT INTO usuario VALUES (NULL,'José','Neo','Torres','Alukar','miakhalifa','joseneotorres@gmail.com','1996-05-19','7','3');
INSERT INTO usuario VALUES (NULL,'Ethan','Izin','Arellano','Hiruma','cocacola','ethanhiruma@gmail.com','1997-03-26','2','3');

INSERT INTO publicacion VALUES (NULL,'Hola','Hola que tal, como andamos compare',now(),4);
INSERT INTO publicacion VALUES (NULL,'El Mundo','El mundo es un lugar muy grande y mistico...eso gracias',now(),1);
INSERT INTO publicacion VALUES (NULL,'Cumputación','La computación nos entrega una herramienta fundamental en la vida y la hace mas facil',NOW(),1);
INSERT INTO publicacion VALUES (NULL,'Chao','Chao, Chavela,Chacabuco',now(),4);

INSERT INTO publicacion VALUES (NULL,'Haga esto 2 dias seguidos y quite las arugas de tu boca','para que quieres saber eso jaja saludo!!',NOW(), 2);
INSERT INTO publicacion VALUES (NULL,'Haga esto para ganar 50 dolares diarios desde su casa ','para que quieres saber eso jaja saludo!!',NOW(), 7);
INSERT INTO publicacion VALUES (NULL,'Haga esto cada mañana y luciras como de 20 años','para que quieres saber eso jaja saludo!!',NOW(),9);

INSERT INTO publicacion VALUES (NULL,'Wena profe','Profe regale unas decimas',now(),9);
INSERT INTO publicacion VALUES (NULL,'Al tiro','no me apure',now(),6);

INSERT INTO publi_etiqueta VALUES (NULL,'1','1');
INSERT INTO publi_etiqueta VALUES (NULL,'1','2');
INSERT INTO publi_etiqueta VALUES (NULL,'1','3');
INSERT INTO publi_etiqueta VALUES (NULL,'2','1');
INSERT INTO publi_etiqueta VALUES (NULL,'2','2');
INSERT INTO publi_etiqueta VALUES (NULL,'2','3');

insert into publi_etiqueta values(NULL, '9','1');
insert into publi_etiqueta values(NULL, '9','2');
insert into publi_etiqueta values(NULL, '9','3');
insert into publi_etiqueta values(NULL, '9','10');

INSERT INTO publi_etiqueta VALUES(NULL, '7', '1');
INSERT INTO publi_etiqueta VALUES(NULL, '7', '2');
INSERT INTO publi_etiqueta VALUES(NULL, '8', '5');
INSERT INTO publi_etiqueta VALUES(NULL, '8', '7');
INSERT INTO publi_etiqueta VALUES(NULL, '8', '9');

INSERT INTO publi_etiqueta VAlUES(NULL , '3','1');
INSERT INTO publi_etiqueta VAlUES(NULL , '3','2');
INSERT INTO publi_etiqueta VAlUES(NULL , '3','3');
INSERT INTO publi_etiqueta VAlUES(NULL , '3','4');
INSERT INTO publi_etiqueta VAlUES(NULL , '4','10');

INSERT INTO publi_etiqueta VALUES (NULL, '5', '1');
INSERT INTO publi_etiqueta VALUES (NULL, '5', '3');
INSERT INTO publi_etiqueta VALUES (NULL, '5', '5');
INSERT INTO publi_etiqueta VALUES (NULL, '5', '6');
INSERT INTO publi_etiqueta VALUES (NULL, '6', '5');
INSERT INTO publi_etiqueta VALUES (NULL, '6', '6');
INSERT INTO publi_etiqueta VALUES (NULL, '6', '7');
INSERT INTO publi_etiqueta VALUES (NULL, '6', '8');

INSERT INTO comentario VALUES(NULL,'Tengo miedo, mi homunculo acaba de decir "ESQUEEE HUMANO"',NOW(),1,3);
INSERT INTO comentario VALUES(NULL,'El profe pato es dios, Salu4',NOW(),1,3);
INSERT INTO comentario VALUES(NULL,'A mi polola aún no le llega la regla:( ESQUE',NOW(),1,3);

INSERT INTO penalizado VALUES(NULL,'2','5','2018-10-27','Tonto','2019-10-27');
INSERT INTO penalizado VALUES(NULL,'3','7','2018-10-27','Necio','2019-10-27');
INSERT INTO penalizado VALUES(NULL,'2','9','2018-10-27','Bobo','2019-10-27');

SELECT * FROM pais;
SELECT * FROM publicacion;
SELECT * FROM usuario;

-- DROP DATABASE bd_blog_viernes;


/*1.- Mostrar id, nombre, nick y publicaciones
del usuario número 3 ordenados por fecha de forma
descendente*/

/*1.- Qué quiero mostrar (SELECT)*/
/* usuario.id, usuario.nombre, usuario.nick, publicacion.id
publicacion.titulo, publicacion.mensaje
*/
/*2.- ¿Dónde estan los datos?
además de los datos de condiciones
 usuario, publicacion*/

/*3.- Unión (pk y fk)
usuario.id = publicacion.fk_usuario
*/
/*4.- Condiciones
usuario.id = 3
publicacion.fecha DESC
*/

SELECT
    usuario.id,
    usuario.nombre,
    usuario.nick,
    publicacion.id,
    publicacion.titulo,
    publicacion.mensaje
FROM
    usuario
INNER JOIN
    publicacion ON usuario.id = publicacion.fk_usuario
WHERE
    usuario.id = 9
ORDER BY
    publicacion.fecha DESC;




SELECT
    usuario.id,
    usuario.nombre AS 'Usuario',
    pais.nombre,
    publicacion.id,
    publicacion.titulo,
    publicacion.mensaje,
    publicacion.fecha,
    etiqueta.nombre AS 'Etiqueta'
FROM
    pais
INNER JOIN
    usuario ON pais.id = usuario.fk_pais
INNER JOIN
    publicacion ON usuario.id = publicacion.fk_usuario
INNER JOIN
    publi_etiqueta ON publicacion.id = publi_etiqueta.fk_publicacion
INNER JOIN
    etiqueta ON etiqueta.id = publi_etiqueta.fk_etiqueta
WHERE
    pais.id = 3
ORDER BY etiqueta.nombre DESC;


SELECT
    comentario.id,
    comentario.texto,
    comentario.fecha,
    usuario.nombre,
    usuario.nick,
    pais.nombre
FROM
    pais
INNER JOIN
    usuario ON pais.id = usuario.fk_pais
INNER JOIN
    comentario ON usuario.id = comentario.fk_usuario;


SELECT * FROM comentario;


SELECT * FROM publicacion;
SELECT * FROM usuario WHERE fk_pais = 3;