-- Crear la base de datos
CREATE DATABASE IF NOT EXISTS simpsons_db;
USE simpsons_db;

-- Tabla de personajes principales
CREATE TABLE personajes (
    id_personaje INT PRIMARY KEY AUTO_INCREMENT,
    nombre VARCHAR(50) NOT NULL,
    apellido VARCHAR(50) NOT NULL,
    edad INT,
    ocupacion VARCHAR(100),
    fecha_nacimiento DATE,
    voz_original VARCHAR(100),
    descripcion TEXT,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabla de familias
CREATE TABLE familias (
    id_familia INT PRIMARY KEY AUTO_INCREMENT,
    nombre_familia VARCHAR(50) NOT NULL,
    direccion VARCHAR(200),
    ciudad VARCHAR(50) DEFAULT 'Springfield',
    estado VARCHAR(50) DEFAULT 'No especificado',
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabla de relación personaje-familia
CREATE TABLE familia_personaje (
    id_relacion INT PRIMARY KEY AUTO_INCREMENT,
    id_familia INT,
    id_personaje INT,
    parentesco VARCHAR(50),
    FOREIGN KEY (id_familia) REFERENCES familias(id_familia),
    FOREIGN KEY (id_personaje) REFERENCES personajes(id_personaje)
);

-- Tabla de episodios
CREATE TABLE episodios (
    id_episodio INT PRIMARY KEY AUTO_INCREMENT,
    titulo VARCHAR(200) NOT NULL,
    temporada INT NOT NULL,
    numero_episodio INT NOT NULL,
    fecha_emision DATE,
    duracion_minutos INT,
    sinopsis TEXT,
    rating DECIMAL(3,1),
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabla de apariciones en episodios
CREATE TABLE apariciones (
    id_aparicion INT PRIMARY KEY AUTO_INCREMENT,
    id_episodio INT,
    id_personaje INT,
    importancia ENUM('Principal', 'Secundario', 'Cameo'),
    lineas_dialogo INT DEFAULT 0,
    FOREIGN KEY (id_episodio) REFERENCES episodios(id_episodio),
    FOREIGN KEY (id_personaje) REFERENCES personajes(id_personaje)
);

-- Tabla de lugares importantes
CREATE TABLE lugares (
    id_lugar INT PRIMARY KEY AUTO_INCREMENT,
    nombre_lugar VARCHAR(100) NOT NULL,
    tipo_lugar VARCHAR(50),
    direccion VARCHAR(200),
    descripcion TEXT,
    creado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabla de trabajos/empleos
CREATE TABLE empleos (
    id_empleo INT PRIMARY KEY AUTO_INCREMENT,
    id_personaje INT,
    puesto VARCHAR(100) NOT NULL,
    empresa VARCHAR(100),
    salario_anual DECIMAL(10,2),
    fecha_inicio DATE,
    fecha_fin DATE,
    estado ENUM('Activo', 'Inactivo', 'Despedido') DEFAULT 'Activo',
    FOREIGN KEY (id_personaje) REFERENCES personajes(id_personaje)
);

-- Tabla de frases célebres
CREATE TABLE frases_celebres (
    id_frase INT PRIMARY KEY AUTO_INCREMENT,
    id_personaje INT,
    frase TEXT NOT NULL,
    id_episodio INT,
    contexto VARCHAR(200),
    popularidad INT DEFAULT 0,
    fecha_frase TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_personaje) REFERENCES personajes(id_personaje),
    FOREIGN KEY (id_episodio) REFERENCES episodios(id_episodio)
);

-- Tabla de relaciones entre personajes
CREATE TABLE relaciones (
    id_relacion INT PRIMARY KEY AUTO_INCREMENT,
    id_personaje1 INT,
    id_personaje2 INT,
    tipo_relacion VARCHAR(50),
    descripcion TEXT,
    estado ENUM('Amistad', 'Familia', 'Romance', 'Rivalidad', 'Neutral'),
    FOREIGN KEY (id_personaje1) REFERENCES personajes(id_personaje),
    FOREIGN KEY (id_personaje2) REFERENCES personajes(id_personaje)
);

-- Insertar datos de ejemplo

-- Insertar familias
INSERT INTO familias (nombre_familia, direccion, ciudad) VALUES
('Simpson', 'Avenida Siempre Viva 742', 'Springfield'),
('Flanders', 'Avenida Siempre Viva 744', 'Springfield'),
('Van Houten', 'Calle Washington 123', 'Springfield'),
('Burns', 'Mansión Burns', 'Springfield'),
('Wiggum', 'Comisaría Central', 'Springfield');

-- Insertar personajes principales
INSERT INTO personajes (nombre, apellido, edad, ocupacion, fecha_nacimiento, voz_original) VALUES
('Homer', 'Simpson', 39, 'Inspector de seguridad en la Planta Nuclear', '1956-05-12', 'Dan Castellaneta'),
('Marge', 'Simpson', 36, 'Ama de casa', '1958-10-01', 'Julie Kavner'),
('Bart', 'Simpson', 10, 'Estudiante en la Escuela Primaria de Springfield', '1980-04-01', 'Nancy Cartwright'),
('Lisa', 'Simpson', 8, 'Estudiante en la Escuela Primaria de Springfield', '1982-05-09', 'Yeardley Smith'),
('Maggie', 'Simpson', 1, 'Bebé', '1989-01-01', 'Varios'),
('Ned', 'Flanders', 60, 'Propietario de la Tienda Leftorium', '1945-09-07', 'Harry Shearer'),
('Montgomery', 'Burns', 104, 'Dueño de la Planta Nuclear', '1919-09-15', 'Harry Shearer'),
('Milhouse', 'Van Houten', 10, 'Estudiante', '1980-07-23', 'Pamela Hayden'),
('Moe', 'Szyslak', 55, 'Dueño de la Taberna de Moe', '1965-05-01', 'Hank Azaria'),
('Chief', 'Wiggum', 45, 'Jefe de policía', '1960-04-01', 'Hank Azaria');

-- Relacionar personajes con familias
INSERT INTO familia_personaje (id_familia, id_personaje, parentesco) VALUES
(1, 1, 'Padre'), (1, 2, 'Madre'), (1, 3, 'Hijo'), (1, 4, 'Hija'), (1, 5, 'Hija'),
(2, 6, 'Padre'),
(3, 8, 'Hijo'),
(4, 7, 'Patriarca'),
(5, 10, 'Jefe de familia');

-- Insertar lugares importantes
INSERT INTO lugares (nombre_lugar, tipo_lugar, direccion, descripcion) VALUES
('Planta Nuclear de Springfield', 'Trabajo', 'Avenida Industrial 1', 'Lugar de trabajo de Homer Simpson'),
('Escuela Primaria de Springfield', 'Educación', 'Calle Educación 456', 'Escuela a la que van Bart y Lisa'),
('Taberna de Moe', 'Entretenimiento', 'Calle Principal 123', 'Bar favorito de Homer'),
('Kwik-E-Mart', 'Comercio', 'Avenida Siempre Viva 741', 'Tienda de conveniencia de Apu'),
('Casa de los Simpson', 'Residencial', 'Avenida Siempre Viva 742', 'Hogar de la familia Simpson');

-- Insertar empleos
INSERT INTO empleos (id_personaje, puesto, empresa, salario_anual, fecha_inicio, estado) VALUES
(1, 'Inspector de Seguridad', 'Planta Nuclear de Springfield', 25000.00, '1980-01-15', 'Activo'),
(6, 'Propietario', 'Leftorium', 40000.00, '1985-03-20', 'Activo'),
(7, 'Dueño', 'Planta Nuclear de Springfield', 10000000.00, '1945-01-01', 'Activo'),
(9, 'Dueño y Barman', 'Taberna de Moe', 35000.00, '1982-06-10', 'Activo'),
(10, 'Jefe de Policía', 'Departamento de Policía de Springfield', 55000.00, '1985-08-15', 'Activo');

-- Insertar algunos episodios
INSERT INTO episodios (titulo, temporada, numero_episodio, fecha_emision, duracion_minutos, sinopsis, rating) VALUES
('Bart el Genio', 1, 2, '1990-01-14', 22, 'Bart obtiene una puntuación perfecta en un test de inteligencia', 8.2),
('Homer\'s Odyssey', 1, 3, '1990-01-21', 22, 'Homer pierde su trabajo y busca un nuevo propósito en la vida', 7.9),
('There\'s No Disgrace Like Home', 1, 4, '1990-01-28', 22, 'La familia Simpson va a terapia familiar', 8.1);

-- Insertar apariciones en episodios
INSERT INTO apariciones (id_episodio, id_personaje, importancia, lineas_dialogo) VALUES
(1, 3, 'Principal', 45), (1, 1, 'Principal', 38), (1, 2, 'Principal', 25),
(1, 4, 'Secundario', 15), (1, 6, 'Secundario', 8);

-- Insertar frases célebres
INSERT INTO frases_celebres (id_personaje, frase, id_episodio, contexto, popularidad) VALUES
(1, '¡D\'oh!', NULL, 'Expresión típica de frustración', 100),
(3, '¡Ay, caramba!', NULL, 'Expresión característica de Bart', 95),
(1, 'Mmm... cerveza', NULL, 'Al ver una cerveza', 85),
(7, 'Excelente...', NULL, 'Expresión característica del Sr. Burns', 80);

-- Insertar relaciones entre personajes
INSERT INTO relaciones (id_personaje1, id_personaje2, tipo_relacion, descripcion, estado) VALUES
(1, 2, 'Matrimonio', 'Homer y Marge están casados', 'Romance'),
(1, 3, 'Paternidad', 'Homer es el padre de Bart', 'Familia'),
(3, 8, 'Amistad', 'Bart y Milhouse son mejores amigos', 'Amistad'),
(1, 6, 'Vecindad', 'Homer y Ned son vecinos', 'Rivalidad'),
(1, 7, 'Empleo', 'Homer trabaja para el Sr. Burns', 'Neutral');

-- Crear vistas útiles
CREATE VIEW vista_familias_completas AS
SELECT f.nombre_familia, p.nombre, p.apellido, fp.parentesco
FROM familias f
JOIN familia_personaje fp ON f.id_familia = fp.id_familia
JOIN personajes p ON fp.id_personaje = p.id_personaje
ORDER BY f.nombre_familia, p.edad DESC;

CREATE VIEW vista_episodios_personajes AS
SELECT e.titulo, e.temporada, p.nombre, p.apellido, a.importancia, a.lineas_dialogo
FROM episodios e
JOIN apariciones a ON e.id_episodio = a.id_episodio
JOIN personajes p ON a.id_personaje = p.id_personaje
ORDER BY e.temporada, e.numero_episodio;

-- Crear índices para mejorar el rendimiento
CREATE INDEX idx_personajes_nombre ON personajes(nombre, apellido);
CREATE INDEX idx_episodios_temporada ON episodios(temporada, numero_episodio);
CREATE INDEX idx_frases_personaje ON frases_celebres(id_personaje);
CREATE INDEX idx_empleos_personaje ON empleos(id_personaje);

-- Procedimiento almacenado para agregar nuevo personaje
DELIMITER //
CREATE PROCEDURE agregar_personaje(
    IN p_nombre VARCHAR(50),
    IN p_apellido VARCHAR(50),
    IN p_edad INT,
    IN p_ocupacion VARCHAR(100),
    IN p_fecha_nacimiento DATE,
    IN p_voz_original VARCHAR(100)
)
BEGIN
    INSERT INTO personajes (nombre, apellido, edad, ocupacion, fecha_nacimiento, voz_original)
    VALUES (p_nombre, p_apellido, p_edad, p_ocupacion, p_fecha_nacimiento, p_voz_original);
END //
DELIMITER ;

-- Función para contar personajes por familia
DELIMITER //
CREATE FUNCTION contar_personajes_familia(familia_id INT) RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE total INT;
    SELECT COUNT(*) INTO total FROM familia_personaje WHERE id_familia = familia_id;
    RETURN total;
END //
DELIMITER ;