-- Tabla DEPARTAMENTO
CREATE TABLE DEPARTAMENTO (
    id_Depto INT PRIMARY KEY,
    Nombre_Depto VARCHAR(50) NOT NULL
);

-- Tabla EMPLEADO
CREATE TABLE EMPLEADO (
    id_Empleado INT PRIMARY KEY,
    Nombre VARCHAR(50) NOT NULL,
    ApellidoP VARCHAR(50) NOT NULL,
    Sexo CHAR(1) NOT NULL, -- 'M' o 'F'
    Salario DECIMAL(10, 2) NOT NULL,
    id_Depto INT,
    FOREIGN KEY (id_Depto) REFERENCES DEPARTAMENTO(id_Depto)
);

-- Tabla DEPENDIENTE
CREATE TABLE DEPENDIENTE (
    id_Dependiente INT PRIMARY KEY,
    id_Empleado INT,
    Parentesco VARCHAR(30) NOT NULL,
    Nombre_Dependiente VARCHAR(50),
    FOREIGN KEY (id_Empleado) REFERENCES EMPLEADO(id_Empleado)
);

-- Tabla PROYECTO
CREATE TABLE PROYECTO (
    id_Proyecto INT PRIMARY KEY,
    Nombre_Proyecto VARCHAR(50) NOT NULL,
    Estado VARCHAR(30) NOT NULL,
    id_Depto INT,
    FOREIGN KEY (id_Depto) REFERENCES DEPARTAMENTO(id_Depto)
);

-- Insertar Departamentos
INSERT INTO DEPARTAMENTO (id_Depto, Nombre_Depto) VALUES
(1, 'Tecnología'),
(2, 'Recursos Humanos'),
(3, 'Finanzas');

-- Insertar Empleados
INSERT INTO EMPLEADO (id_Empleado, Nombre, ApellidoP, Sexo, Salario, id_Depto) VALUES
(101, 'Carlos', 'Gómez', 'M', 2500.00, 1),
(102, 'Ana', 'Pérez', 'F', 2800.00, 1),
(103, 'Luis', 'Martínez', 'M', 1800.00, 2),
(104, 'María', 'Rodríguez', 'F', 2200.00, 2),
(105, 'Jorge', 'Sánchez', 'M', 3100.00, 1),
(106, 'Elena', 'Torres', 'F', 3000.00, 3);

-- Insertar Dependientes
INSERT INTO DEPENDIENTE (id_Dependiente, id_Empleado, Parentesco, Nombre_Dependiente) VALUES
(1, 101, 'Hijo', 'Mateo Gómez'),
(2, 101, 'Cónyuge', 'Laura Ruiz'),
(3, 102, 'Hijo', 'Sofía Pérez'),
(4, 103, 'Cónyuge', 'Carmen Lopez'),
(5, 104, 'Hijo', 'Lucas Rodríguez'),
(6, 104, 'Hijo', 'Valentina Rodríguez');

-- Insertar Proyectos
INSERT INTO PROYECTO (id_Proyecto, Nombre_Proyecto, Estado, id_Depto) VALUES
(1, 'Sistema Web', 'Activo', 1),
(2, 'App Móvil', 'En espera', 1),
(3, 'Migración Cloud', 'Activo', 1),
(4, 'Reclutamiento 2026', 'Activo', 2),
(5, 'Auditoría Fiscal', 'Finalizado', 3);

-- 5. Mostrar el número de proyectos controlados por cada departamento, según su estado.
SELECT d.Nombre_Depto, p.Estado, COUNT(DISTINCT p.id_Proyecto) AS Total_Proyectos, COUNT(DISTINCT e.id_Empleado) AS Total_Empleados
FROM DEPARTAMENTO d
JOIN PROYECTO p ON d.id_Depto = p.id_Depto
JOIN EMPLEADO e ON d.id_Depto = e.id_Depto
GROUP BY d.Nombre_Depto, p.Estado
ORDER BY d.Nombre_Depto, p.Estado;