-- Goodens / ens.mx — Esquema completo de la base de datos
-- Base de datos objetivo: ens_good
-- Importar desde phpMyAdmin (HostGator) tal cual.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------------------------------------------
-- Usuarios
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS usuarios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(120) NOT NULL,
    correo VARCHAR(190) NOT NULL UNIQUE,
    contrasena_hash VARCHAR(255) NOT NULL,
    api_token VARCHAR(80) NULL,
    creado_en DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_usuarios_token (api_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------------
-- Noticias
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS noticias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(200) NOT NULL,
    resumen TEXT NULL,
    contenido MEDIUMTEXT NOT NULL,
    imagen_url VARCHAR(500) NULL,
    categoria VARCHAR(80) NULL,
    destacada TINYINT(1) NOT NULL DEFAULT 0,
    publicada_en DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_noticias_pub (publicada_en)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------------
-- Alertas de tráfico
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS alertas_trafico (
    id INT AUTO_INCREMENT PRIMARY KEY,
    titulo VARCHAR(200) NOT NULL,
    descripcion TEXT NULL,
    tipo VARCHAR(30) NOT NULL,           -- obra | accidente | evento | semaforo
    latitud DECIMAL(10,7) NOT NULL,
    longitud DECIMAL(10,7) NOT NULL,
    creada_en DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------------
-- Reportes ciudadanos
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS reportes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    usuario_id INT NULL,
    tipo VARCHAR(40) NOT NULL,           -- choque | incendio | derrame_aguas_negras | bache | alumbrado | basura | fuga_agua | otro
    descripcion TEXT NOT NULL,
    direccion VARCHAR(300) NULL,
    latitud DECIMAL(10,7) NULL,
    longitud DECIMAL(10,7) NULL,
    foto_url VARCHAR(500) NULL,
    estado VARCHAR(20) NOT NULL DEFAULT 'pendiente',   -- pendiente | en_proceso | resuelto
    creado_en DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_reportes_estado (estado),
    CONSTRAINT fk_reportes_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------------
-- Empleos y servicios freelance
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS empleos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    usuario_id INT NULL,
    tipo VARCHAR(20) NOT NULL,           -- empleo | servicio
    titulo VARCHAR(200) NOT NULL,
    descripcion TEXT NOT NULL,
    categoria VARCHAR(100) NULL,
    empresa VARCHAR(150) NULL,
    sueldo VARCHAR(100) NULL,
    contacto VARCHAR(150) NOT NULL,
    ubicacion VARCHAR(200) NULL,
    creado_en DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_empleos_tipo (tipo),
    CONSTRAINT fk_empleos_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ------------------------------------------------------------------
-- Rutas de microbús
-- ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS rutas_microbus (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(150) NOT NULL,
    descripcion TEXT NULL,
    horario VARCHAR(150) NULL,
    tarifa VARCHAR(50) NULL,
    color VARCHAR(20) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- ------------------------------------------------------------------
-- Datos de ejemplo (opcional — bórralos si no los quieres)
-- ------------------------------------------------------------------

INSERT INTO noticias (titulo, resumen, contenido, imagen_url, categoria, destacada) VALUES
('Inicia rehabilitación de la avenida Ryerson',
 'Obras durarán aproximadamente 3 semanas.',
 'El Ayuntamiento informó que iniciarán los trabajos de repavimentación en la avenida Ryerson entre las calles Segunda y Novena. Se recomienda usar rutas alternas.',
 NULL, 'obras', 1),
('Feria del empleo este sábado en el Riviera',
 '80 empresas participarán ofreciendo más de 1,500 vacantes.',
 'Este sábado de 9:00 a 15:00 horas se realizará la feria del empleo en el Centro Social Cívico y Cultural Riviera. Llevar CV impreso.',
 NULL, 'empleos', 0);

INSERT INTO alertas_trafico (titulo, descripcion, tipo, latitud, longitud) VALUES
('Cierre calle Cerro por obras', 'Cierre parcial por reparación de drenaje.', 'obra', 31.86720, -116.60050),
('Choque en Reforma y Ámbar', 'Un carril cerrado, tráfico lento.', 'accidente', 31.85990, -116.60830),
('Semáforo intermitente en Diamante', 'Precaución al cruzar.', 'semaforo', 31.85200, -116.61500);

INSERT INTO rutas_microbus (nombre, descripcion, horario, tarifa, color) VALUES
('Ruta Centro - Chapultepec', 'Recorre desde el centro hasta col. Chapultepec.', '05:30 - 22:30', '$14', 'amarillo'),
('Ruta Maneadero', 'Servicio directo al poblado Maneadero.', '06:00 - 21:00', '$18', 'verde'),
('Ruta Valle Dorado', 'Cubre las colonias del oriente.', '05:00 - 23:00', '$14', 'azul');