CREATE TABLE Ubicaciones (
    ID_Ubicacion INT PRIMARY KEY,
    Calle VARCHAR(100),
    Colonia VARCHAR(100),
    Sector VARCHAR(50),
    Coordenadas_GPS VARCHAR(100),
    Tipo_Zona VARCHAR(50)
);

CREATE TABLE Tecnicos (
    ID_Tecnico INT PRIMARY KEY,
    Nombre VARCHAR(50),
    Apellido VARCHAR(50),
    Turno VARCHAR(30),
    Telefono VARCHAR(20),
    Especialidad VARCHAR(100)
);

CREATE TABLE Lamparas (
    ID_Lampara INT PRIMARY KEY,
    Modelo VARCHAR(50),
    Potencia_W INT,
    Capacidad_Bateria_Ah INT,
    Tipo_Panel VARCHAR(50),
    Estado_Operativo VARCHAR(30),
    Fecha_Instalacion DATE,
    ID_Ubicacion INT,
    FOREIGN KEY (ID_Ubicacion)
    REFERENCES Ubicaciones(ID_Ubicacion)
);

CREATE TABLE Mantenimiento (
    ID_Mantenimiento INT PRIMARY KEY,
    ID_Lampara INT,
    Fecha_Servicio DATE,
    Tipo_Falla VARCHAR(100),
    Accion_Realizada VARCHAR(150),
    ID_Tecnico INT,
    Costo DECIMAL(10,2),
    FOREIGN KEY (ID_Lampara)
    REFERENCES Lamparas(ID_Lampara),
    FOREIGN KEY (ID_Tecnico)
    REFERENCES Tecnicos(ID_Tecnico)
);

INSERT INTO Ubicaciones VALUES
    
(1, 'Calle Reforma', 'Centro', 'Sector Norte', '32.5149,-117.0382', 'Urbana'),
(2, 'Calle Juarez', 'Libertad', 'Sector Sur', '32.5100,-117.0500', 'Urbana'),
(3, 'Calle Hidalgo', 'Zona Rio', 'Sector Este', '32.5200,-117.0200', 'Comercial'),
(4, 'Calle Morelos', 'Otay', 'Sector Norte', '32.5400,-116.9500', 'Urbana'),
(5, 'Calle Benito Juarez', 'Playas', 'Sector Oeste', '32.5700,-117.1200', 'Residencial'),
(6, 'Calle Primera', 'La Mesa', 'Sector Sur', '32.4900,-116.9800', 'Urbana'),
(7, 'Calle Segunda', 'Mariano Matamoros', 'Sector Este', '32.4600,-116.9200', 'Residencial'),
(8, 'Calle Tercera', 'Florido', 'Sector Norte', '32.4500,-116.9000', 'Urbana'),
(9, 'Calle Quinta', 'Cacho', 'Sector Oeste', '32.5100,-117.0300', 'Residencial'),
(10, 'Calle Sexta', 'Sanchez Taboada', 'Sector Sur', '32.4700,-117.0000', 'Urbana');


INSERT INTO Tecnicos VALUES
    
(1, 'Gael', 'Acevedo', 'Matutino', '6641000001', 'Electricidad'),
(2, 'Levi', 'Juarez', 'Vespertino', '6641000002', 'Paneles solares'),
(3, 'Janeth', 'Valenzuela', 'Matutino', '6641000003', 'Mantenimiento'),
(4, 'Romina', 'Hernandez', 'Nocturno', '6641000004', 'Electricidad'),
(5, 'Samadhi', 'Juarez', 'Vespertino', '6641000005', 'Baterias'),
(6, 'Katerine', 'Salas', 'Matutino', '6641000006', 'Paneles solares'),
(7, 'Yaitzel', 'Flores', 'Nocturno', '6641000007', 'Mantenimiento'),
(8, 'Danna', 'Flores', 'Vespertino', '6641000008', 'Electricidad'),
(9, 'Nayeli', 'Morales', 'Matutino', '6641000009', 'Baterias'),
(10, 'Diego', 'Cruz', 'Nocturno', '6641000010', 'Paneles solares');

INSERT INTO Lamparas VALUES
    
(1, 'Solar LED 100', 100, 50, 'Monocristalino', 'Operativa', '2026-01-10', 1),
(2, 'Solar LED 150', 150, 60, 'Policristalino', 'Operativa', '2026-01-12', 2),
(3, 'Solar LED 200', 200, 80, 'Monocristalino', 'En Mantenimiento', '2026-01-15', 3),
(4, 'EcoLight 100', 100, 45, 'Policristalino', 'Operativa', '2026-01-18', 4),
(5, 'EcoLight 150', 150, 55, 'Monocristalino', 'Baja', '2026-01-20', 5),
(6, 'SunPower 200', 200, 90, 'Monocristalino', 'Operativa', '2026-01-22', 6),
(7, 'SunPower 150', 150, 70, 'Policristalino', 'Operativa', '2026-01-25', 7),
(8, 'SolarMax 100', 100, 50, 'Monocristalino', 'En Mantenimiento', '2026-01-28', 8),
(9, 'SolarMax 200', 200, 85, 'Policristalino', 'Operativa', '2026-01-30', 9),
(10, 'EcoLight 200', 200, 80, 'Monocristalino', 'Operativa', '2026-02-02', 10);


INSERT INTO Mantenimiento VALUES
    
(1, 1, '2026-10-05', 'Bateria descargada', 'Revision y cambio de bateria', 1, 850.00),
(2, 2, '2026-10-06', 'Panel sucio', 'Limpieza del panel solar', 2, 250.00),
(3, 3, '2026-10-07', 'Falla electrica', 'Reparacion del sistema electrico', 3, 600.00),
(4, 4, '2026-10-08', 'Cable desconectado', 'Conexion de cables', 4, 150.00),
(5, 5, '2026-10-09', 'Bateria dañada', 'Revision del sistema de bateria', 5, 900.00),
(6, 6, '2026-10-10', 'Panel defectuoso', 'Reparacion del panel solar', 6, 700.00),
(7, 7, '2026-10-11', 'Luz intermitente', 'Revision de conexiones', 7, 300.00),
(8, 8, '2026-10-12', 'Falla de iluminacion', 'Cambio de lampara LED', 8, 450.00),
(9, 9, '2026-10-13', 'Bateria baja', 'Mantenimiento de bateria', 9, 500.00),
(10, 10, '2026-10-14', 'Panel sucio', 'Limpieza y mantenimiento general', 10, 200.00);


-- 1. Lamparas que necesitan mantenimiento
SELECT *
FROM Lamparas
WHERE Estado_Operativo = 'En Mantenimiento';

-- 2. Rendimiento por sector
SELECT
    U.Sector,
    COUNT(L.ID_Lampara) AS Total_Lamparas,
    SUM(CASE WHEN L.Estado_Operativo = 'Operativa'
        THEN 1 ELSE 0 END) AS Lamparas_Operativas
FROM Ubicaciones U
INNER JOIN Lamparas L
ON U.ID_Ubicacion = L.ID_Ubicacion
GROUP BY U.Sector;

-- 3. Historial por tecnico
SELECT
    T.Nombre,
    T.Apellido,
    L.Modelo,
    M.Fecha_Servicio,
    M.Tipo_Falla,
    M.Accion_Realizada,
    M.Costo
FROM Mantenimiento M
INNER JOIN Tecnicos T
ON M.ID_Tecnico = T.ID_Tecnico
INNER JOIN Lamparas L
ON M.ID_Lampara = L.ID_Lampara
ORDER BY T.Apellido;

SELECT
    U.Sector,
    U.Colonia,
    L.Modelo,
    L.Estado_Operativo
FROM Lamparas L
INNER JOIN Ubicaciones U
ON L.ID_Ubicacion = U.ID_Ubicacion
WHERE L.Estado_Operativo = 'Operativa'
ORDER BY U.Sector, U.Colonia;

SELECT 'Ubicaciones' AS Tabla, COUNT(*) AS Registros FROM Ubicaciones
UNION ALL
SELECT 'Tecnicos', COUNT(*) FROM Tecnicos
UNION ALL
SELECT 'Lamparas', COUNT(*) FROM Lamparas
UNION ALL
SELECT 'Mantenimiento', COUNT(*) FROM Mantenimiento;

SELECT *
FROM Lamparas
WHERE Fecha_Instalacion > '2026-11-13';