CREATE TABLE UBICACIONES (
ID_Ubicacion INT PRIMARY KEY AUTO_INCREMENT,
Calle VARCHAR(50) NOT NULL,
Colonia VARCHAR(50) NOT NULL,
Sector VARCHAR(20),
Tipo_Zona VARCHAR(20),
Coordenadas_GPS VARCHAR(50)
);
CREATE TABLE LAMPARAS (
ID_Lampara INT PRIMARY KEY AUTO_INCREMENT,
Modelo VARCHAR(50) NOT NULL,
Potencia_W INT CHECK (Potencia_W > 0),
Capacidad_Bateria_Ah INT CHECK (Capacidad_Bateria_Ah > 0),
Tipo_Panel VARCHAR(30),
Estado_Operativo VARCHAR(30) CHECK (Estado_Operativo IN ('Operativa','En Mantenimiento','Baja')),
Fecha_Instalacion DATE,
Horas_Encendido INT DEFAULT 8,
ID_Ubicacion INT,
FOREIGN KEY (ID_Ubicacion) REFERENCES UBICACIONES(ID_Ubicacion) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE TECNICOS (
ID_Tecnico INT PRIMARY KEY AUTO_INCREMENT,
Nombre VARCHAR(50) NOT NULL,
Apellido VARCHAR(50),
Telefono VARCHAR(20),
Turno VARCHAR(20) CHECK (Turno IN ('Matutino','Vespertino','Nocturno')),
Especialidad VARCHAR(30)
);
CREATE TABLE MANTENIMIENTO (
ID_Mantenimiento INT PRIMARY KEY AUTO_INCREMENT,
Fecha_Servicio DATE NOT NULL,
Tipo_Falla VARCHAR(30),
Accion_Realizada VARCHAR(100),
Costo DECIMAL(10,2) CHECK (Costo >= 0),
ID_Lampara INT,
ID_Tecnico INT,
FOREIGN KEY (ID_Lampara) REFERENCES LAMPARAS(ID_Lampara) ON DELETE CASCADE,
FOREIGN KEY (ID_Tecnico) REFERENCES TECNICOS(ID_Tecnico) ON DELETE CASCADE
);
CREATE TABLE REPORTES (
ID_Reporte INT PRIMARY KEY AUTO_INCREMENT,
Fecha_Reporte DATE,
Total_Lamparas INT,
Potencia_Total INT,
Gasto_Total DECIMAL(10,2)
);
INSERT INTO UBICACIONES (Calle, Colonia, Sector, Tipo_Zona, Coordenadas_GPS) VALUES
('Calle Principal','Centro','Urbano','Urbana','32.5149,-117.0382'),
('Calle Flores','La Mesa','Urbano','Urbana','32.5150,-117.0385'),
('Av. Las Torres','Otay','Urbano','Urbana','32.5200,-117.0400'),
('Calle Pinos','Centro','Urbano','Urbana','32.5145,-117.0380'),
('Calle Rosas','La Mesa','Urbano','Urbana','32.5155,-117.0390'),
('Blvd. 2000','Otay','Rural','Rural','32.5300,-117.0500'),
('Calle Olivos','Centro','Urbano','Urbana','32.5140,-117.0375'),
('Calle Palmas','La Mesa','Rural','Rural','32.5160,-117.0395'),
('Calle Nubes','Otay','Urbano','Urbana','32.5210,-117.0410'),
('Calle Sol','Centro','Urbano','Urbana','32.5135,-117.0370');
INSERT INTO LAMPARAS (Modelo, Potencia_W, Capacidad_Bateria_Ah, Tipo_Panel, Estado_Operativo, Fecha_Instalacion, Horas_Encendido, ID_Ubicacion) VALUES
('SolarLED 100',100,50,'Monocristalino','Operativa','2025-03-10',8,1),
('SolarLED 80',80,40,'Policristalino','Operativa','2025-03-12',8,2),
('SolarLED 100',100,50,'Monocristalino','En Mantenimiento','2025-04-01',8,3),
('EcoSun 60',60,30,'Monocristalino','Operativa','2025-04-05',10,4),
('SolarLED 80',80,40,'Policristalino','Operativa','2025-04-08',8,5),
('EcoSun 120',120,60,'Monocristalino','Operativa','2025-04-10',12,6),
('SolarLED 100',100,50,'Monocristalino','Baja','2025-02-15',0,7),
('EcoSun 60',60,30,'Policristalino','Operativa','2025-05-01',8,8),
('SolarLED 80',80,40,'Monocristalino','En Mantenimiento','2025-05-03',0,9),
('SolarLED 100',100,50,'Monocristalino','Operativa','2025-05-10',8,10);
INSERT INTO TECNICOS (Nombre, Apellido, Telefono, Turno, Especialidad) VALUES
('Juan','Perez','664-111-1111','Matutino','Electrico'),
('Ana','Lopez','664-222-2222','Vespertino','Paneles'),
('Carlos','Ruiz','664-333-3333','Matutino','Baterias'),
('Maria','Gomez','664-444-4444','Vespertino','LED'),
('Pedro','Sanchez','664-555-5555','Nocturno','Electrico'),
('Laura','Torres','664-666-6666','Matutino','Paneles'),
('Diego','Ramirez','664-777-7777','Vespertino','Baterias'),
('Sofia','Diaz','664-888-8888','Matutino','LED'),
('Luis','Mendoza','664-999-9999','Nocturno','Electrico'),
('Elena','Castro','664-000-0000','Matutino','Paneles');
INSERT INTO MANTENIMIENTO (Fecha_Servicio, Tipo_Falla, Accion_Realizada, Costo, ID_Lampara, ID_Tecnico) VALUES
('2026-01-15','Bateria','Cambio de bateria',450,3,1),
('2026-02-10','Panel','Limpieza de panel',200,9,2),
('2026-01-20','LED','Cambio de foco LED',300,7,3),
('2026-02-01','Cable','Reparacion de cableado',150,3,4),
('2026-02-15','Bateria','Revision de bateria',100,1,5),
('2026-03-01','Panel','Cambio de panel',600,6,6),
('2026-03-05','LED','Ajuste de LED',120,2,7),
('2026-03-10','Bateria','Carga de bateria',80,5,8),
('2026-03-12','Panel','Limpieza general',200,4,9),
('2026-03-15','Cable','Cambio de cable',250,10,10);
SELECT * FROM UBICACIONES;
SELECT * FROM LAMPARAS;
SELECT * FROM TECNICOS;
SELECT * FROM MANTENIMIENTO;
SELECT ID_Lampara, Modelo, Estado_Operativo FROM LAMPARAS WHERE Estado_Operativo = 'En Mantenimiento';
SELECT * FROM LAMPARAS WHERE Estado_Operativo = 'Operativa' ORDER BY Potencia_W DESC;
SELECT Colonia, COUNT(*) AS Total_Lamparas, SUM(Potencia_W) AS Potencia_Total, AVG(Potencia_W) AS Promedio FROM LAMPARAS JOIN UBICACIONES ON LAMPARAS.ID_Ubicacion = UBICACIONES.ID_Ubicacion GROUP BY Colonia;
SELECT TECNICOS.Nombre, TECNICOS.Apellido, MANTENIMIENTO.Fecha_Servicio, MANTENIMIENTO.Tipo_Falla, MANTENIMIENTO.Costo FROM MANTENIMIENTO JOIN TECNICOS ON MANTENIMIENTO.ID_Tecnico = TECNICOS.ID_Tecnico WHERE TECNICOS.Nombre = 'Juan';
SELECT LAMPARAS.ID_Lampara, LAMPARAS.Modelo, LAMPARAS.Potencia_W, LAMPARAS.Estado_Operativo, UBICACIONES.Calle, UBICACIONES.Colonia FROM LAMPARAS JOIN UBICACIONES ON LAMPARAS.ID_Ubicacion = UBICACIONES.ID_Ubicacion;
SELECT SUM(Costo) AS Gasto_Total, AVG(Costo) AS Gasto_Promedio, MAX(Costo) AS Mas_Caro, MIN(Costo) AS Mas_Barato, COUNT(*) AS Total_Servicios FROM MANTENIMIENTO;
SELECT * FROM LAMPARAS WHERE Modelo LIKE 'Solar%';
SELECT * FROM LAMPARAS WHERE Potencia_W BETWEEN 60 AND 100;
SELECT Modelo, Potencia_W, Horas_Encendido, (Potencia_W * Horas_Encendido) AS Consumo_Diario_Wh FROM LAMPARAS;
SELECT Colonia, COUNT(*) AS Total FROM UBICACIONES GROUP BY Colonia HAVING COUNT(*) >= 3;
SELECT Tipo_Falla, COUNT(*) AS Veces, SUM(Costo) AS Total_Gastado FROM MANTENIMIENTO GROUP BY Tipo_Falla HAVING SUM(Costo) > 300;
INSERT INTO LAMPARAS (Modelo, Potencia_W, Capacidad_Bateria_Ah, Tipo_Panel, Estado_Operativo, Fecha_Instalacion, Horas_Encendido, ID_Ubicacion) VALUES ('SolarLED 150',150,70,'Monocristalino','Operativa','2026-11-13',8,1);
UPDATE LAMPARAS SET Estado_Operativo = 'Operativa' WHERE ID_Lampara = 3;
CREATE VIEW Vista_Inventario AS SELECT LAMPARAS.ID_Lampara, LAMPARAS.Modelo, LAMPARAS.Potencia_W, LAMPARAS.Estado_Operativo, UBICACIONES.Colonia FROM LAMPARAS JOIN UBICACIONES ON LAMPARAS.ID_Ubicacion = UBICACIONES.ID_Ubicacion;
SELECT * FROM Vista_Inventario;
CREATE VIEW Vista_Gastos AS SELECT Tipo_Falla, SUM(Costo) AS Total FROM MANTENIMIENTO GROUP BY Tipo_Falla;
SELECT * FROM Vista_Gastos;
SELECT 'Total Lamparas' AS Concepto, COUNT(*) AS Valor FROM LAMPARAS UNION ALL SELECT 'Operativas', COUNT(*) FROM LAMPARAS WHERE Estado_Operativo = 'Operativa' UNION ALL SELECT 'En Mantenimiento', COUNT(*) FROM LAMPARAS WHERE Estado_Operativo = 'En Mantenimiento' UNION ALL SELECT 'Potencia Total W', SUM(Potencia_W) FROM LAMPARAS;