-- ============================================================
-- JM Tracking v2 — Esquema Completo de Base de Datos
-- Todas las tablas con FOREIGN KEYS, indices y protecciones
-- Fecha: 2026-02-26
-- ============================================================
--
-- ORDEN DE CREACION (respeta dependencias de FK):
--   1. Seguridad y Acceso
--   2. Clientes y Organizacion
--   3. Catalogos Operativos (conductores, vehiculos, etc.)
--   4. Dispositivos (DES / Candados)
--   5. Operaciones / Movimientos
--   6. Monitoreo y Rutas
--   7. Notificaciones, Documentos y Soporte
--
-- NOTA: Usar SET FOREIGN_KEY_CHECKS = 0 si se necesita recrear.
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================
-- GRUPO 1: SEGURIDAD Y ACCESO
-- ============================================================

-- Usuarios del sistema (tabla base — todas las demas referencian aqui)
CREATE TABLE IF NOT EXISTS usuarios (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username        VARCHAR(100)  NOT NULL,
    nombres         VARCHAR(100)  DEFAULT NULL,
    apellidos       VARCHAR(100)  NOT NULL,
    email           VARCHAR(255)  NOT NULL,
    password        VARCHAR(255)  NOT NULL,
    rol             ENUM('root','admin','supervisor','controlador','tecnico','distribuidor','usuario','cliente','dian')
                        NOT NULL DEFAULT 'usuario',
    client_id       INT UNSIGNED  DEFAULT NULL,
    activo          TINYINT(1)    NOT NULL DEFAULT 1,
    telefono        VARCHAR(20)   DEFAULT NULL,
    ultimo_login    DATETIME      DEFAULT NULL,
    creado_en       TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    actualizado_en  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_email    (email),
    INDEX idx_rol     (rol),
    INDEX idx_client  (client_id),
    INDEX idx_activo  (activo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Permisos granulares por rol y modulo
CREATE TABLE IF NOT EXISTS permisos_modulo (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    rol             VARCHAR(30)   NOT NULL,
    modulo          VARCHAR(50)   NOT NULL COMMENT 'dashboard, movimientos, monitoreo, solicitudes, etc.',
    puede_ver       TINYINT(1)    DEFAULT 0,
    puede_crear     TINYINT(1)    DEFAULT 0,
    puede_editar    TINYINT(1)    DEFAULT 0,
    puede_eliminar  TINYINT(1)    DEFAULT 0,
    puede_exportar  TINYINT(1)    DEFAULT 0,
    created_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uk_rol_modulo (rol, modulo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sesiones activas (control de seguridad / sesiones concurrentes)
CREATE TABLE IF NOT EXISTS sesiones_activas (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    usuario_id      INT UNSIGNED  NOT NULL,
    session_id      VARCHAR(128)  NOT NULL,
    ip_address      VARCHAR(45)   DEFAULT NULL,
    user_agent      VARCHAR(255)  DEFAULT NULL,
    ultimo_acceso   DATETIME      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_usuario (usuario_id),
    INDEX idx_session (session_id),
    CONSTRAINT fk_sesiones_usuario
        FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bitacora de auditoria
CREATE TABLE IF NOT EXISTS audit_log (
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    usuario_id       INT UNSIGNED  NOT NULL,
    username         VARCHAR(100)  DEFAULT NULL,
    rol              VARCHAR(50)   DEFAULT NULL,
    accion           VARCHAR(100)  NOT NULL,
    tabla_afectada   VARCHAR(50)   DEFAULT NULL,
    registro_id      INT           DEFAULT NULL,
    datos_anteriores JSON          DEFAULT NULL,
    datos_nuevos     JSON          DEFAULT NULL,
    detalles         TEXT          DEFAULT NULL,
    ip_address       VARCHAR(45)   DEFAULT NULL,
    user_agent       VARCHAR(255)  DEFAULT NULL,
    created_at       TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_usuario (usuario_id),
    INDEX idx_accion  (accion),
    INDEX idx_tabla   (tabla_afectada),
    INDEX idx_fecha   (created_at),
    CONSTRAINT fk_audit_usuario
        FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- GRUPO 2: CLIENTES Y ORGANIZACION
-- ============================================================

-- Clientes principales
CREATE TABLE IF NOT EXISTS clientes (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre              VARCHAR(255)  NOT NULL,
    razon_social        VARCHAR(200)  DEFAULT NULL,
    nit                 VARCHAR(30)   DEFAULT NULL,
    email               VARCHAR(200)  DEFAULT NULL,
    telefono            VARCHAR(30)   DEFAULT NULL,
    direccion           VARCHAR(255)  DEFAULT NULL,
    ciudad              VARCHAR(100)  DEFAULT NULL,
    contacto_nombre     VARCHAR(100)  DEFAULT NULL,
    contacto_telefono   VARCHAR(30)   DEFAULT NULL,
    logo_url            VARCHAR(255)  DEFAULT NULL,
    activo              TINYINT(1)    DEFAULT 1,
    creado_en           TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    actualizado_en      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_nombre (nombre),
    INDEX idx_nit    (nit),
    INDEX idx_activo (activo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- FK diferida: usuarios.client_id → clientes.id
ALTER TABLE usuarios
    ADD CONSTRAINT fk_usuarios_cliente
    FOREIGN KEY (client_id) REFERENCES clientes(id) ON DELETE SET NULL;

-- Mapeo clientes locales → RedGPS
CREATE TABLE IF NOT EXISTS clientes_redgps (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cliente_id          INT UNSIGNED  DEFAULT NULL,
    redgps_idcliente    INT           NOT NULL COMMENT 'ID del cliente en RedGPS',
    nombre              VARCHAR(255)  DEFAULT NULL,
    es_activo           TINYINT(1)    DEFAULT 1,
    razon_social        VARCHAR(200)  DEFAULT NULL,
    email               VARCHAR(200)  DEFAULT NULL,
    synced_at           TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_cliente (cliente_id),
    INDEX idx_redgps  (redgps_idcliente),
    CONSTRAINT fk_clientesredgps_cliente
        FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mapeo usuarios locales → RedGPS
CREATE TABLE IF NOT EXISTS usuarios_redgps (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    usuario_id          INT UNSIGNED  NOT NULL,
    redgps_idusuario    INT           NOT NULL COMMENT 'ID del usuario en RedGPS',
    redgps_idcliente    INT           NOT NULL COMMENT 'ID del cliente en RedGPS',
    user                VARCHAR(100)  DEFAULT NULL,
    email               VARCHAR(200)  DEFAULT NULL,
    creado_at           TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_usuario (usuario_id),
    CONSTRAINT fk_usuariosredgps_usuario
        FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tecnicos (especializacion de usuarios)
CREATE TABLE IF NOT EXISTS tecnicos (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    usuario_id      INT UNSIGNED  NOT NULL,
    zona            VARCHAR(100)  DEFAULT NULL COMMENT 'Zona geografica: Bogota, Cartagena, Buenaventura, etc.',
    especialidad    VARCHAR(100)  DEFAULT NULL,
    disponible      TINYINT(1)    DEFAULT 1,
    creado_at       TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_usuario    (usuario_id),
    INDEX idx_disponible (disponible),
    CONSTRAINT fk_tecnicos_usuario
        FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Distribuidores (API partners)
CREATE TABLE IF NOT EXISTS distribuidores (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    usuario_id      INT UNSIGNED  NOT NULL,
    apikey          VARCHAR(255)  NOT NULL,
    usuario_ws      VARCHAR(100)  NOT NULL,
    clave_ws        VARCHAR(100)  NOT NULL,
    token           VARCHAR(255)  DEFAULT NULL,
    token_obtained  DATETIME      DEFAULT NULL,
    activo          TINYINT(1)    DEFAULT 1,
    created_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_usuario (usuario_id),
    INDEX idx_apikey  (apikey),
    CONSTRAINT fk_distribuidores_usuario
        FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- WebServices (credenciales API por cliente)
CREATE TABLE IF NOT EXISTS webservices (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre              VARCHAR(100)  NOT NULL,
    pais                VARCHAR(50)   NOT NULL DEFAULT 'Colombia',
    tipo_transmision    VARCHAR(50)   NOT NULL DEFAULT 'RESTful',
    tipo_envio          VARCHAR(50)   NOT NULL DEFAULT 'json',
    estado              VARCHAR(20)   NOT NULL DEFAULT 'activo',
    usuario_ws          VARCHAR(100)  NOT NULL,
    clave_ws            VARCHAR(100)  NOT NULL,
    cliente_id          INT UNSIGNED  NOT NULL,
    creado_en           TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_cliente (cliente_id),
    INDEX idx_estado  (estado),
    CONSTRAINT fk_webservices_cliente
        FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- GRUPO 3: CATALOGOS OPERATIVOS
-- ============================================================

-- Conductores (antes embebidos en solicitud_servicio, ahora normalizados)
CREATE TABLE IF NOT EXISTS conductores (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    documento               VARCHAR(50)   NOT NULL,
    nombre_completo         VARCHAR(150)  NOT NULL,
    telefono                VARCHAR(20)   DEFAULT NULL,
    email                   VARCHAR(150)  DEFAULT NULL,
    licencia_numero         VARCHAR(50)   DEFAULT NULL,
    licencia_categoria      VARCHAR(10)   DEFAULT NULL,
    licencia_vencimiento    DATE          DEFAULT NULL,
    cliente_id              INT UNSIGNED  DEFAULT NULL COMMENT 'Cliente al que pertenece',
    activo                  TINYINT(1)    DEFAULT 1,
    created_at              TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at              TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uk_documento (documento),
    INDEX idx_nombre  (nombre_completo),
    INDEX idx_cliente (cliente_id),
    INDEX idx_activo  (activo),
    CONSTRAINT fk_conductores_cliente
        FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Transportistas (empresas de transporte)
CREATE TABLE IF NOT EXISTS transportistas (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre              VARCHAR(150)  NOT NULL,
    nit                 VARCHAR(30)   DEFAULT NULL,
    telefono            VARCHAR(30)   DEFAULT NULL,
    email               VARCHAR(150)  DEFAULT NULL,
    direccion           VARCHAR(255)  DEFAULT NULL,
    ciudad              VARCHAR(100)  DEFAULT NULL,
    contacto_nombre     VARCHAR(100)  DEFAULT NULL,
    contacto_telefono   VARCHAR(30)   DEFAULT NULL,
    cliente_id          INT UNSIGNED  DEFAULT NULL,
    activo              TINYINT(1)    DEFAULT 1,
    created_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_nombre  (nombre),
    INDEX idx_nit     (nit),
    INDEX idx_cliente (cliente_id),
    INDEX idx_activo  (activo),
    CONSTRAINT fk_transportistas_cliente
        FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vehiculos
CREATE TABLE IF NOT EXISTS vehiculos (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    placa               VARCHAR(20)   NOT NULL,
    marca               VARCHAR(50)   DEFAULT NULL,
    modelo              VARCHAR(50)   DEFAULT NULL,
    color               VARCHAR(30)   DEFAULT NULL,
    tipo                ENUM('Camion','Camioneta','Tractomula','Furgon','Otro') DEFAULT 'Camion',
    anio                SMALLINT      DEFAULT NULL,
    capacidad_ton       DECIMAL(6,2)  DEFAULT NULL,
    cliente_id          INT UNSIGNED  DEFAULT NULL COMMENT 'Propietario / cliente',
    transportista_id    INT UNSIGNED  DEFAULT NULL,
    activo              TINYINT(1)    DEFAULT 1,
    created_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uk_placa (placa),
    INDEX idx_cliente       (cliente_id),
    INDEX idx_transportista (transportista_id),
    INDEX idx_activo        (activo),
    CONSTRAINT fk_vehiculos_cliente
        FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE SET NULL,
    CONSTRAINT fk_vehiculos_transportista
        FOREIGN KEY (transportista_id) REFERENCES transportistas(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Contenedores
CREATE TABLE IF NOT EXISTS contenedores (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    numero          VARCHAR(50)   NOT NULL COMMENT 'Numero unico del contenedor (BIC/ISO)',
    tipo            ENUM('Dry Van','High Cube','Metalicos','Open Top','Flat Rack','Refrigerado','Otro')
                        NOT NULL DEFAULT 'Dry Van',
    tamano          ENUM('20ft','40ft','45ft','Otro') DEFAULT '40ft',
    propietario     VARCHAR(150)  DEFAULT NULL,
    cliente_id      INT UNSIGNED  DEFAULT NULL,
    activo          TINYINT(1)    DEFAULT 1,
    created_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uk_numero (numero),
    INDEX idx_cliente (cliente_id),
    INDEX idx_tipo    (tipo),
    CONSTRAINT fk_contenedores_cliente
        FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- GRUPO 4: DISPOSITIVOS (DES / CANDADOS)
-- ============================================================

-- Candados / DES — tabla unificada (antes habia 'devices' y 'candados' por separado)
CREATE TABLE IF NOT EXISTS candados (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    identificador_unico     VARCHAR(50)   NOT NULL,
    marca                   VARCHAR(50)   NOT NULL DEFAULT 'N/A',
    modelo                  VARCHAR(50)   NOT NULL DEFAULT 'N/A',
    nombre                  VARCHAR(100)  DEFAULT NULL,
    placa                   VARCHAR(50)   DEFAULT NULL,
    sim                     VARCHAR(50)   DEFAULT NULL,
    imei                    VARCHAR(50)   DEFAULT NULL,
    client_id               INT UNSIGNED  DEFAULT NULL,
    sensores                TEXT          DEFAULT NULL,
    estado                  ENUM('disponible','asignado','en_movimiento','mantenimiento','baja')
                                NOT NULL DEFAULT 'disponible',
    remote_id               INT           DEFAULT NULL COMMENT 'ID en RedGPS (idvehiculo)',
    -- Campos de tracking en tiempo real
    ultima_lat              DECIMAL(10,7) DEFAULT NULL,
    ultima_lng              DECIMAL(10,7) DEFAULT NULL,
    ultima_velocidad        DECIMAL(6,2)  DEFAULT 0,
    ultima_comunicacion     DATETIME      DEFAULT NULL,
    bateria                 INT           DEFAULT 100,
    puerta_abierta          TINYINT(1)    DEFAULT 0,
    senal                   INT           DEFAULT NULL,
    created_at              TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at              TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uk_identificador (identificador_unico),
    INDEX idx_imei    (imei),
    INDEX idx_client  (client_id),
    INDEX idx_estado  (estado),
    INDEX idx_remote  (remote_id),
    CONSTRAINT fk_candados_cliente
        FOREIGN KEY (client_id) REFERENCES clientes(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Autorizaciones candado → cliente (N:M)
CREATE TABLE IF NOT EXISTS autorizados (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    candado_id      INT UNSIGNED  NOT NULL,
    cliente_id      INT UNSIGNED  NOT NULL,
    autorizado_en   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uk_candado_cliente (candado_id, cliente_id),
    INDEX idx_candado (candado_id),
    INDEX idx_cliente (cliente_id),
    CONSTRAINT fk_autorizados_candado
        FOREIGN KEY (candado_id) REFERENCES candados(id) ON DELETE CASCADE,
    CONSTRAINT fk_autorizados_cliente
        FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Historial de asignacion / desasignacion de candados
CREATE TABLE IF NOT EXISTS historial_candados (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    solicitud_id            INT UNSIGNED  DEFAULT NULL,
    candado_id              INT UNSIGNED  NOT NULL,
    candado_nombre          VARCHAR(100)  DEFAULT NULL,
    candado_modelo          VARCHAR(50)   DEFAULT NULL,
    candado_identificador   VARCHAR(100)  DEFAULT NULL,
    candado_imei            VARCHAR(50)   DEFAULT NULL,
    fecha_asignacion        DATETIME      DEFAULT NULL,
    fecha_desasignacion     DATETIME      DEFAULT NULL,
    created_at              TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_solicitud (solicitud_id),
    INDEX idx_candado   (candado_id),
    CONSTRAINT fk_histcandados_candado
        FOREIGN KEY (candado_id) REFERENCES candados(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mantenimiento de DES (doc. movimientos: "DES que haya debido cambiarse, debera ingresarse en un incidente a Mantenimiento")
CREATE TABLE IF NOT EXISTS mantenimiento_dispositivos (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    candado_id      INT UNSIGNED  NOT NULL,
    tipo            ENUM('preventivo','correctivo','calibracion','carga_bateria','baja') NOT NULL,
    descripcion     TEXT          DEFAULT NULL,
    tecnico_id      INT UNSIGNED  DEFAULT NULL,
    estado          ENUM('pendiente','en_proceso','completado','cancelado') DEFAULT 'pendiente',
    fecha_ingreso   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    fecha_salida    DATETIME      DEFAULT NULL,
    observaciones   TEXT          DEFAULT NULL,
    created_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_candado (candado_id),
    INDEX idx_estado  (estado),
    INDEX idx_tecnico (tecnico_id),
    CONSTRAINT fk_mantenimiento_candado
        FOREIGN KEY (candado_id) REFERENCES candados(id) ON DELETE RESTRICT,
    CONSTRAINT fk_mantenimiento_tecnico
        FOREIGN KEY (tecnico_id) REFERENCES tecnicos(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Eventos GPS reportados por los DES (webhook / polling)
CREATE TABLE IF NOT EXISTS device_events (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_imei     VARCHAR(50)   NOT NULL,
    plate           VARCHAR(20)   DEFAULT NULL,
    latitude        DECIMAL(10,7) NOT NULL,
    longitude       DECIMAL(10,7) NOT NULL,
    report_date     DATETIME      NOT NULL,
    speed           DECIMAL(6,2)  DEFAULT 0,
    ignition        TINYINT(1)    DEFAULT 0,
    address         VARCHAR(255)  DEFAULT NULL,
    sensors_json    TEXT          DEFAULT NULL,
    battery         INT           DEFAULT NULL,
    door_open       TINYINT(1)    DEFAULT 0,
    signal_strength INT           DEFAULT NULL,
    heading         INT           DEFAULT NULL,
    raw_data        TEXT          DEFAULT NULL COMMENT 'JSON crudo del reporte',
    created_at      DATETIME      DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_imei_date   (device_imei, report_date),
    INDEX idx_report_date (report_date),
    INDEX idx_plate       (plate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Alarmas generadas por el motor propio
CREATE TABLE IF NOT EXISTS device_alarms (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_imei     VARCHAR(50)   NOT NULL,
    movimiento_id   INT UNSIGNED  DEFAULT NULL COMMENT 'Movimiento al que pertenece',
    alarm_code      CHAR(1)       NOT NULL COMMENT 'R=Retraso,V=Velocidad,D=Desvio,C=Comunicacion,S=Parada,A=Apertura,N=SinGPS,G=Geocerca,B=Bateria',
    alarm_type      VARCHAR(10)   NOT NULL COMMENT 'DRUT,EVEL,DRUT,NCOM,DETS,APPU,NGPS,GEOC,BBAT',
    message         VARCHAR(255)  DEFAULT NULL,
    latitude        DECIMAL(10,7) DEFAULT NULL,
    longitude       DECIMAL(10,7) DEFAULT NULL,
    status          ENUM('active_unseen','active_seen','justified','resolved') DEFAULT 'active_unseen',
    seen_at         DATETIME      DEFAULT NULL,
    seen_by         INT UNSIGNED  DEFAULT NULL,
    justified_at    DATETIME      DEFAULT NULL,
    justified_by    INT UNSIGNED  DEFAULT NULL,
    resolved_at     DATETIME      DEFAULT NULL,
    resolved_by     INT UNSIGNED  DEFAULT NULL,
    comment         TEXT          DEFAULT NULL,
    created_at      DATETIME      DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_imei_status  (device_imei, status),
    INDEX idx_status_date  (status, created_at),
    INDEX idx_movimiento   (movimiento_id),
    INDEX idx_alarm_type   (alarm_type),
    CONSTRAINT fk_alarms_seen_by
        FOREIGN KEY (seen_by) REFERENCES usuarios(id) ON DELETE SET NULL,
    CONSTRAINT fk_alarms_justified_by
        FOREIGN KEY (justified_by) REFERENCES usuarios(id) ON DELETE SET NULL,
    CONSTRAINT fk_alarms_resolved_by
        FOREIGN KEY (resolved_by) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- GRUPO 5: OPERACIONES / MOVIMIENTOS
-- ============================================================

-- Solicitudes de servicio
CREATE TABLE IF NOT EXISTS solicitud_servicio (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id           INT UNSIGNED  NOT NULL,
    radicado            VARCHAR(20)   NOT NULL,
    fecha_hora          DATETIME      NOT NULL,
    tipo_movimiento     ENUM('Privado','DIAN') NOT NULL,
    dta_cv              VARCHAR(20)   DEFAULT NULL,
    -- Origen / Destino
    origen              VARCHAR(255)  NOT NULL,
    lat_origen          DECIMAL(10,6) DEFAULT NULL,
    lng_origen          DECIMAL(10,6) DEFAULT NULL,
    destino             VARCHAR(255)  NOT NULL,
    lat_destino         DECIMAL(10,6) DEFAULT NULL,
    lng_destino         DECIMAL(10,6) DEFAULT NULL,
    scheduled_at        DATETIME      DEFAULT NULL,
    orden_compra        VARCHAR(100)  DEFAULT NULL,
    -- Conductor (FK + snapshot)
    conductor_id        INT UNSIGNED  DEFAULT NULL,
    conductor           VARCHAR(100)  NOT NULL,
    documento_conductor VARCHAR(50)   NOT NULL,
    telefono_conductor  VARCHAR(20)   NOT NULL,
    -- Vehiculo (FK + snapshot)
    vehiculo_id         INT UNSIGNED  DEFAULT NULL,
    tipo_vehiculo       ENUM('Camion','Camioneta (Carga Suelta)','Tractomula','Furgon') NOT NULL,
    placa_vehiculo      VARCHAR(20)   NOT NULL,
    marca               VARCHAR(50)   NOT NULL,
    color_vehiculo      VARCHAR(30)   NOT NULL,
    -- Contenedor (FK + snapshot)
    contenedor_id       INT UNSIGNED  DEFAULT NULL,
    contenedor_num      VARCHAR(50)   NOT NULL,
    tipo_contenedor     ENUM('Dry Van','High Cube','Metalicos','Open Top','Flat Rack','Refrigerado') NOT NULL,
    -- Transporte
    transportista_id    INT UNSIGNED  DEFAULT NULL,
    transportadora      VARCHAR(100)  DEFAULT NULL,
    -- DES asignado
    device_id           INT UNSIGNED  DEFAULT NULL,
    -- Estado del flujo
    estado              ENUM('solicitado','asignado','en_transito','pendiente_cierre','finalizado','cancelado')
                            NOT NULL DEFAULT 'solicitado',
    created_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uk_radicado (radicado),
    INDEX idx_client        (client_id),
    INDEX idx_estado        (estado),
    INDEX idx_device        (device_id),
    INDEX idx_conductor     (conductor_id),
    INDEX idx_vehiculo      (vehiculo_id),
    INDEX idx_contenedor    (contenedor_id),
    INDEX idx_transportista (transportista_id),
    INDEX idx_fecha         (fecha_hora),
    CONSTRAINT fk_solicitud_cliente
        FOREIGN KEY (client_id) REFERENCES clientes(id) ON DELETE RESTRICT,
    CONSTRAINT fk_solicitud_conductor
        FOREIGN KEY (conductor_id) REFERENCES conductores(id) ON DELETE SET NULL,
    CONSTRAINT fk_solicitud_vehiculo
        FOREIGN KEY (vehiculo_id) REFERENCES vehiculos(id) ON DELETE SET NULL,
    CONSTRAINT fk_solicitud_contenedor
        FOREIGN KEY (contenedor_id) REFERENCES contenedores(id) ON DELETE SET NULL,
    CONSTRAINT fk_solicitud_transportista
        FOREIGN KEY (transportista_id) REFERENCES transportistas(id) ON DELETE SET NULL,
    CONSTRAINT fk_solicitud_device
        FOREIGN KEY (device_id) REFERENCES candados(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- FK diferida: historial_candados.solicitud_id → solicitud_servicio.id
ALTER TABLE historial_candados
    ADD CONSTRAINT fk_histcandados_solicitud
    FOREIGN KEY (solicitud_id) REFERENCES solicitud_servicio(id) ON DELETE SET NULL;

-- Movimientos (tracking activo — evolucion de tracking_registro v1)
CREATE TABLE IF NOT EXISTS movimientos (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    solicitud_id        INT UNSIGNED  NOT NULL,
    radicado            VARCHAR(20)   NOT NULL,
    candado_id          INT UNSIGNED  DEFAULT NULL,
    tecnico_id          INT UNSIGNED  DEFAULT NULL,
    ruta_id             INT UNSIGNED  DEFAULT NULL,
    -- Estado del movimiento
    estado              ENUM('pendiente','activo','en_pausa','pendiente_cierre','finalizado','cancelado')
                            NOT NULL DEFAULT 'pendiente',
    -- Snapshot de datos (consulta rapida sin JOINs)
    conductor           VARCHAR(100)  DEFAULT NULL,
    documento_conductor VARCHAR(50)   DEFAULT NULL,
    telefono_conductor  VARCHAR(20)   DEFAULT NULL,
    transportadora      VARCHAR(255)  DEFAULT NULL,
    contenedor          VARCHAR(100)  DEFAULT NULL,
    tipo_contenedor     VARCHAR(50)   DEFAULT NULL,
    placa_vehiculo      VARCHAR(50)   DEFAULT NULL,
    marca_vehiculo      VARCHAR(100)  DEFAULT NULL,
    color_vehiculo      VARCHAR(50)   DEFAULT NULL,
    tipo_servicio       VARCHAR(50)   DEFAULT NULL,
    dta_cv              VARCHAR(100)  DEFAULT NULL,
    origen              VARCHAR(255)  NOT NULL,
    destino             VARCHAR(255)  NOT NULL,
    -- Ubicacion actual
    latitud             DECIMAL(10,7) DEFAULT NULL,
    longitud            DECIMAL(10,7) DEFAULT NULL,
    -- Fotos evidencia
    foto_frontal        VARCHAR(255)  DEFAULT NULL,
    foto_lateral        VARCHAR(255)  DEFAULT NULL,
    foto_trasera        VARCHAR(255)  DEFAULT NULL,
    foto_interior       VARCHAR(255)  DEFAULT NULL,
    foto_precinto_1     VARCHAR(255)  DEFAULT NULL,
    foto_precinto_2     VARCHAR(255)  DEFAULT NULL,
    foto_precinto_3     VARCHAR(255)  DEFAULT NULL,
    foto_candado_1      VARCHAR(255)  DEFAULT NULL,
    foto_candado_2      VARCHAR(255)  DEFAULT NULL,
    -- Timestamps
    fecha_inicio        DATETIME      DEFAULT NULL,
    fecha_activacion    DATETIME      DEFAULT NULL,
    fecha_fin           DATETIME      DEFAULT NULL,
    created_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_solicitud     (solicitud_id),
    INDEX idx_radicado      (radicado),
    INDEX idx_candado       (candado_id),
    INDEX idx_estado        (estado),
    INDEX idx_tecnico       (tecnico_id),
    INDEX idx_fecha_inicio  (fecha_inicio),
    CONSTRAINT fk_movimientos_solicitud
        FOREIGN KEY (solicitud_id) REFERENCES solicitud_servicio(id) ON DELETE RESTRICT,
    CONSTRAINT fk_movimientos_candado
        FOREIGN KEY (candado_id) REFERENCES candados(id) ON DELETE SET NULL,
    CONSTRAINT fk_movimientos_tecnico
        FOREIGN KEY (tecnico_id) REFERENCES tecnicos(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- FK diferida: device_alarms.movimiento_id → movimientos.id
ALTER TABLE device_alarms
    ADD CONSTRAINT fk_alarms_movimiento
    FOREIGN KEY (movimiento_id) REFERENCES movimientos(id) ON DELETE SET NULL;

-- Timeline de eventos del movimiento
CREATE TABLE IF NOT EXISTS movimiento_eventos (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    movimiento_id   INT UNSIGNED  NOT NULL,
    tipo            ENUM('creacion','activacion','pausa','reanudacion','alerta','nota',
                         'cambio_estado','checkpoint','cierre','cancelacion') NOT NULL,
    descripcion     TEXT          DEFAULT NULL,
    usuario_id      INT UNSIGNED  DEFAULT NULL,
    metadata        JSON          DEFAULT NULL COMMENT 'Datos extra del evento',
    created_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_movimiento (movimiento_id),
    INDEX idx_tipo       (tipo),
    INDEX idx_fecha      (created_at),
    CONSTRAINT fk_moveventos_movimiento
        FOREIGN KEY (movimiento_id) REFERENCES movimientos(id) ON DELETE CASCADE,
    CONSTRAINT fk_moveventos_usuario
        FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Checkpoints del movimiento (puntos de control en ruta)
CREATE TABLE IF NOT EXISTS movimiento_checkpoints (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    movimiento_id   INT UNSIGNED  NOT NULL,
    lugar_id        INT UNSIGNED  DEFAULT NULL,
    nombre          VARCHAR(150)  NOT NULL,
    latitud         DECIMAL(10,7) DEFAULT NULL,
    longitud        DECIMAL(10,7) DEFAULT NULL,
    orden           INT           DEFAULT 0,
    hora_estimada   DATETIME      DEFAULT NULL,
    hora_llegada    DATETIME      DEFAULT NULL,
    hora_salida     DATETIME      DEFAULT NULL,
    estado          ENUM('pendiente','en_camino','alcanzado','omitido') DEFAULT 'pendiente',
    created_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_movimiento (movimiento_id),
    INDEX idx_estado     (estado),
    CONSTRAINT fk_checkpoints_movimiento
        FOREIGN KEY (movimiento_id) REFERENCES movimientos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Instalaciones de DES
CREATE TABLE IF NOT EXISTS instalaciones (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    solicitud_id    INT UNSIGNED  NOT NULL,
    tecnico_id      INT UNSIGNED  NOT NULL,
    candado_id      INT UNSIGNED  NOT NULL,
    estado          ENUM('solicitado','asignado','en_transito','finalizado','cancelado')
                        NOT NULL DEFAULT 'solicitado',
    asignado_en     DATETIME      NOT NULL,
    terminado_en    DATETIME      DEFAULT NULL,
    observaciones   TEXT          DEFAULT NULL,
    created_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_solicitud (solicitud_id),
    INDEX idx_tecnico   (tecnico_id),
    INDEX idx_candado   (candado_id),
    INDEX idx_estado    (estado),
    CONSTRAINT fk_instalaciones_solicitud
        FOREIGN KEY (solicitud_id) REFERENCES solicitud_servicio(id) ON DELETE RESTRICT,
    CONSTRAINT fk_instalaciones_tecnico
        FOREIGN KEY (tecnico_id) REFERENCES tecnicos(id) ON DELETE RESTRICT,
    CONSTRAINT fk_instalaciones_candado
        FOREIGN KEY (candado_id) REFERENCES candados(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Desinstalaciones de DES
CREATE TABLE IF NOT EXISTS desinstalaciones (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    solicitud_id            INT UNSIGNED  NOT NULL,
    tecnico_id              INT UNSIGNED  NOT NULL,
    fecha_llegada           DATETIME      NOT NULL,
    -- Snapshot datos al momento de desinstalar
    conductor               VARCHAR(100)  NOT NULL,
    documento_conductor     VARCHAR(50)   NOT NULL,
    telefono_conductor      VARCHAR(20)   NOT NULL,
    dta_cv                  VARCHAR(20)   DEFAULT NULL,
    contenedor_num          VARCHAR(50)   DEFAULT NULL,
    tipo_contenedor         ENUM('Dry Van','High Cube','Metalicos','Open Top','Flat Rack','Refrigerado') DEFAULT NULL,
    placa_vehiculo          VARCHAR(20)   DEFAULT NULL,
    marca                   VARCHAR(50)   DEFAULT NULL,
    color_vehiculo          VARCHAR(30)   DEFAULT NULL,
    -- Fotos evidencia
    foto_frontal            VARCHAR(255)  DEFAULT NULL,
    foto_lateral            VARCHAR(255)  DEFAULT NULL,
    foto_trasera            VARCHAR(255)  DEFAULT NULL,
    foto_interior           VARCHAR(255)  DEFAULT NULL,
    foto_precinto_1         VARCHAR(255)  DEFAULT NULL,
    foto_precinto_2         VARCHAR(255)  DEFAULT NULL,
    foto_precinto_3         VARCHAR(255)  DEFAULT NULL,
    foto_candado_1          VARCHAR(255)  DEFAULT NULL,
    foto_candado_2          VARCHAR(255)  DEFAULT NULL,
    -- Cierre
    solicitado_cierre_en    DATETIME      DEFAULT CURRENT_TIMESTAMP,
    fecha_cierre            DATETIME      DEFAULT NULL,
    estado                  ENUM('pendiente','cerrado') DEFAULT 'pendiente',
    observaciones           TEXT          DEFAULT NULL,
    created_at              TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_solicitud (solicitud_id),
    INDEX idx_tecnico   (tecnico_id),
    INDEX idx_estado    (estado),
    CONSTRAINT fk_desinstalaciones_solicitud
        FOREIGN KEY (solicitud_id) REFERENCES solicitud_servicio(id) ON DELETE RESTRICT,
    CONSTRAINT fk_desinstalaciones_tecnico
        FOREIGN KEY (tecnico_id) REFERENCES tecnicos(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Servicios genericos
CREATE TABLE IF NOT EXISTS servicios (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cliente_id      INT UNSIGNED  NOT NULL,
    usuario_id      INT UNSIGNED  NOT NULL,
    tipo_servicio   VARCHAR(50)   NOT NULL,
    estado          VARCHAR(20)   NOT NULL DEFAULT 'pendiente',
    created_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    started_at      DATETIME      DEFAULT NULL,
    ended_at        DATETIME      DEFAULT NULL,

    INDEX idx_cliente (cliente_id),
    INDEX idx_usuario (usuario_id),
    INDEX idx_estado  (estado),
    CONSTRAINT fk_servicios_cliente
        FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE RESTRICT,
    CONSTRAINT fk_servicios_usuario
        FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bridge servicios → activos
CREATE TABLE IF NOT EXISTS servicio_activo (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    servicio_id     INT UNSIGNED  NOT NULL,
    activo_id       BIGINT        NOT NULL,
    assigned_at     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_servicio (servicio_id),
    CONSTRAINT fk_servactivo_servicio
        FOREIGN KEY (servicio_id) REFERENCES servicios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- GRUPO 6: MONITOREO Y RUTAS
-- ============================================================

-- Rutas definidas
CREATE TABLE IF NOT EXISTS rutas (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    redgps_id           INT           DEFAULT NULL,
    nombre              VARCHAR(150)  NOT NULL,
    descripcion         TEXT          DEFAULT NULL,
    definicion          LONGTEXT      DEFAULT NULL COMMENT 'JSON con puntos, geocercas lineales, distancia',
    distancia_km        DECIMAL(10,2) DEFAULT NULL,
    tiempo_estimado_min INT           DEFAULT NULL,
    cliente_id          INT UNSIGNED  DEFAULT NULL,
    activa              TINYINT(1)    DEFAULT 1,
    created_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_nombre  (nombre),
    INDEX idx_cliente (cliente_id),
    INDEX idx_activa  (activa),
    CONSTRAINT fk_rutas_cliente
        FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- FK diferida: movimientos.ruta_id → rutas.id
ALTER TABLE movimientos
    ADD CONSTRAINT fk_movimientos_ruta
    FOREIGN KEY (ruta_id) REFERENCES rutas(id) ON DELETE SET NULL;

-- Rutas programadas
CREATE TABLE IF NOT EXISTS rutas_programadas (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ruta_id         INT UNSIGNED  NOT NULL,
    device_id       INT UNSIGNED  DEFAULT NULL,
    nombre          VARCHAR(150)  DEFAULT NULL,
    fecha_inicio    DATE          DEFAULT NULL,
    hora_inicio     TIME          DEFAULT NULL,
    fecha_fin       DATE          DEFAULT NULL,
    hora_fin        TIME          DEFAULT NULL,
    dias            VARCHAR(50)   DEFAULT NULL COMMENT 'Lu-Ma-Mi-Ju-Vi-Sa-Do',
    periodo         TINYINT       DEFAULT 1 COMMENT '1=Diario,2=Semanal,3=Mensual,4=Anual,5=Continuo',
    repetir         INT           DEFAULT 1,
    veces           INT           DEFAULT 0,
    termina         TINYINT       DEFAULT 1 COMMENT '1=Nunca,2=Despues n veces,3=Fecha fin,4=Al completar',
    activa          TINYINT(1)    DEFAULT 1,
    created_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_ruta   (ruta_id),
    INDEX idx_device (device_id),
    INDEX idx_activa (activa),
    CONSTRAINT fk_rutasprog_ruta
        FOREIGN KEY (ruta_id) REFERENCES rutas(id) ON DELETE CASCADE,
    CONSTRAINT fk_rutasprog_device
        FOREIGN KEY (device_id) REFERENCES candados(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Lugares / Puntos de interes
CREATE TABLE IF NOT EXISTS lugares (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre      VARCHAR(150)  NOT NULL,
    direccion   VARCHAR(255)  DEFAULT NULL,
    latitud     DECIMAL(10,7) DEFAULT NULL,
    longitud    DECIMAL(10,7) DEFAULT NULL,
    tipo        VARCHAR(50)   DEFAULT NULL COMMENT 'puerto, aduana, zona_franca, deposito, cliente, etc.',
    cliente_id  INT UNSIGNED  DEFAULT NULL,
    activo      TINYINT(1)    DEFAULT 1,
    created_at  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_tipo    (tipo),
    INDEX idx_cliente (cliente_id),
    CONSTRAINT fk_lugares_cliente
        FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- FK diferida: movimiento_checkpoints.lugar_id → lugares.id
ALTER TABLE movimiento_checkpoints
    ADD CONSTRAINT fk_checkpoints_lugar
    FOREIGN KEY (lugar_id) REFERENCES lugares(id) ON DELETE SET NULL;

-- Geocercas
CREATE TABLE IF NOT EXISTS geocercas (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    idCerca             INT           NOT NULL,
    idtipo_cerca        INT           DEFAULT NULL,
    tipo_cerca          VARCHAR(30)   DEFAULT 'Circular',
    nombre              VARCHAR(150)  NOT NULL,
    color               VARCHAR(10)   DEFAULT '#FF0000',
    radio               DECIMAL(10,2) DEFAULT 500,
    limite_velocidad    INT           DEFAULT 0,
    solo_mi_usuario     TINYINT(1)    DEFAULT 0,
    visible             TINYINT(1)    DEFAULT 1,
    grupo               VARCHAR(100)  DEFAULT NULL,
    compartida          TINYINT(1)    DEFAULT 0,
    cliente_id          INT UNSIGNED  DEFAULT NULL,
    created_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uk_idcerca (idCerca),
    INDEX idx_nombre  (nombre),
    INDEX idx_visible (visible),
    INDEX idx_cliente (cliente_id),
    CONSTRAINT fk_geocercas_cliente
        FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Puntos de geocercas
CREATE TABLE IF NOT EXISTS geocerca_puntos (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    geocerca_id     INT UNSIGNED  NOT NULL,
    lat             DECIMAL(10,7) NOT NULL,
    lng             DECIMAL(10,7) NOT NULL,
    punto_orden     INT           DEFAULT 0,

    INDEX idx_geocerca (geocerca_id),
    CONSTRAINT fk_geopuntos_geocerca
        FOREIGN KEY (geocerca_id) REFERENCES geocercas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Configuracion de alarmas por cliente (umbrales personalizados)
CREATE TABLE IF NOT EXISTS configuracion_alarmas (
    id                          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cliente_id                  INT UNSIGNED  DEFAULT NULL COMMENT 'NULL = configuracion global',
    alarm_code                  CHAR(1)       NOT NULL,
    habilitada                  TINYINT(1)    DEFAULT 1,
    umbral_velocidad_kmh        INT           DEFAULT 120,
    umbral_parada_min           INT           DEFAULT 30,
    umbral_sin_comunicacion_min INT           DEFAULT 60,
    umbral_bateria_pct          INT           DEFAULT 20,
    umbral_desvio_metros        INT           DEFAULT 500,
    notificar_email             TINYINT(1)    DEFAULT 0,
    notificar_sms               TINYINT(1)    DEFAULT 0,
    emails_destino              TEXT          DEFAULT NULL COMMENT 'Emails separados por coma',
    telefonos_destino           TEXT          DEFAULT NULL,
    created_at                  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at                  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uk_cliente_alarm (cliente_id, alarm_code),
    CONSTRAINT fk_configalarmas_cliente
        FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- GRUPO 7: NOTIFICACIONES, DOCUMENTOS Y SOPORTE
-- ============================================================

-- Notificaciones del sistema
CREATE TABLE IF NOT EXISTS notificaciones (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    usuario_id          INT UNSIGNED  DEFAULT NULL,
    cliente_id          INT UNSIGNED  DEFAULT NULL,
    tipo                ENUM('alarma','movimiento','sistema','servicio','mantenimiento') NOT NULL,
    titulo              VARCHAR(200)  NOT NULL,
    mensaje             TEXT          DEFAULT NULL,
    referencia_tipo     VARCHAR(50)   DEFAULT NULL COMMENT 'movimiento, alarma, solicitud, etc.',
    referencia_id       INT UNSIGNED  DEFAULT NULL,
    canal               ENUM('web','email','sms','push') DEFAULT 'web',
    leida               TINYINT(1)    DEFAULT 0,
    leida_en            DATETIME      DEFAULT NULL,
    enviada             TINYINT(1)    DEFAULT 0,
    enviada_en          DATETIME      DEFAULT NULL,
    created_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_usuario   (usuario_id),
    INDEX idx_cliente   (cliente_id),
    INDEX idx_leida     (leida),
    INDEX idx_tipo      (tipo),
    INDEX idx_fecha     (created_at),
    CONSTRAINT fk_notificaciones_usuario
        FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    CONSTRAINT fk_notificaciones_cliente
        FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Documentos / Archivos subidos (gestion centralizada de fotos y docs)
CREATE TABLE IF NOT EXISTS documentos (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    referencia_tipo     VARCHAR(50)   NOT NULL COMMENT 'movimiento, solicitud, desinstalacion, certificacion, etc.',
    referencia_id       INT UNSIGNED  NOT NULL,
    tipo_documento      VARCHAR(50)   DEFAULT NULL COMMENT 'foto_frontal, guia_remision, acta, certificado, etc.',
    nombre_archivo      VARCHAR(255)  NOT NULL,
    ruta_archivo        VARCHAR(500)  NOT NULL,
    mime_type           VARCHAR(100)  DEFAULT NULL,
    tamano_bytes        BIGINT        DEFAULT NULL,
    subido_por          INT UNSIGNED  DEFAULT NULL,
    created_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_referencia (referencia_tipo, referencia_id),
    INDEX idx_tipo       (tipo_documento),
    CONSTRAINT fk_documentos_subidopor
        FOREIGN KEY (subido_por) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Certificaciones GPS
CREATE TABLE IF NOT EXISTS certificaciones_gps (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    radicado            VARCHAR(50)   NOT NULL,
    fecha               DATE          NOT NULL,
    periodo_inicio      DATE          NOT NULL,
    periodo_fin         DATE          NOT NULL,
    dispositivo_avl     VARCHAR(100)  NOT NULL,
    serial_gps          VARCHAR(100)  NOT NULL,
    imei_gps            VARCHAR(100)  NOT NULL,
    tipo_maquinaria     VARCHAR(100)  NOT NULL,
    no_motor            VARCHAR(100)  NOT NULL,
    no_serial_maquina   VARCHAR(100)  NOT NULL,
    empresa             VARCHAR(200)  NOT NULL,
    nit                 VARCHAR(50)   NOT NULL,
    created_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_radicado (radicado),
    INDEX idx_empresa  (empresa)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Incidentes operativos (doc. movimientos: contingencias, cambios DES, etc.)
CREATE TABLE IF NOT EXISTS incidentes (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    movimiento_id   INT UNSIGNED  DEFAULT NULL,
    solicitud_id    INT UNSIGNED  DEFAULT NULL,
    candado_id      INT UNSIGNED  DEFAULT NULL,
    tipo            ENUM('des_sin_comunicacion','des_abierto','bateria_baja','otra_ubicacion',
                         'des_en_uso','conductor_sin_comunicacion','desvio_ruta',
                         'cambio_destino','cambio_vehiculo','otro') NOT NULL,
    descripcion     TEXT          NOT NULL,
    reportado_por   INT UNSIGNED  DEFAULT NULL,
    asignado_a      INT UNSIGNED  DEFAULT NULL,
    estado          ENUM('abierto','en_proceso','resuelto','cerrado') DEFAULT 'abierto',
    prioridad       ENUM('baja','media','alta','critica') DEFAULT 'media',
    resolucion      TEXT          DEFAULT NULL,
    created_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_movimiento (movimiento_id),
    INDEX idx_solicitud  (solicitud_id),
    INDEX idx_estado     (estado),
    INDEX idx_prioridad  (prioridad),
    CONSTRAINT fk_incidentes_movimiento
        FOREIGN KEY (movimiento_id) REFERENCES movimientos(id) ON DELETE SET NULL,
    CONSTRAINT fk_incidentes_solicitud
        FOREIGN KEY (solicitud_id) REFERENCES solicitud_servicio(id) ON DELETE SET NULL,
    CONSTRAINT fk_incidentes_candado
        FOREIGN KEY (candado_id) REFERENCES candados(id) ON DELETE SET NULL,
    CONSTRAINT fk_incidentes_reportadopor
        FOREIGN KEY (reportado_por) REFERENCES usuarios(id) ON DELETE SET NULL,
    CONSTRAINT fk_incidentes_asignadoa
        FOREIGN KEY (asignado_a) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- GRUPO 8: FACTURACION Y CONTROL DE SERVICIOS
-- ============================================================

-- Planes / Tarifas de servicio por cliente
CREATE TABLE IF NOT EXISTS planes_servicio (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre              VARCHAR(100)  NOT NULL,
    descripcion         TEXT          DEFAULT NULL,
    tipo                ENUM('por_movimiento','mensual','por_dispositivo','personalizado') NOT NULL DEFAULT 'por_movimiento',
    precio_unitario     DECIMAL(12,2) NOT NULL DEFAULT 0,
    moneda              CHAR(3)       DEFAULT 'COP',
    incluye_dispositivo TINYINT(1)    DEFAULT 0,
    incluye_monitoreo   TINYINT(1)    DEFAULT 0,
    incluye_reportes    TINYINT(1)    DEFAULT 0,
    max_dispositivos    INT           DEFAULT NULL COMMENT 'NULL = ilimitados',
    max_movimientos_mes INT           DEFAULT NULL COMMENT 'NULL = ilimitados',
    activo              TINYINT(1)    DEFAULT 1,
    created_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_tipo   (tipo),
    INDEX idx_activo (activo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Asignacion de plan a cliente (contrato)
CREATE TABLE IF NOT EXISTS cliente_plan (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cliente_id      INT UNSIGNED  NOT NULL,
    plan_id         INT UNSIGNED  NOT NULL,
    fecha_inicio    DATE          NOT NULL,
    fecha_fin       DATE          DEFAULT NULL COMMENT 'NULL = indefinido',
    precio_acordado DECIMAL(12,2) DEFAULT NULL COMMENT 'NULL = usa precio del plan',
    estado          ENUM('activo','suspendido','cancelado','vencido') DEFAULT 'activo',
    notas           TEXT          DEFAULT NULL,
    created_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_cliente (cliente_id),
    INDEX idx_plan    (plan_id),
    INDEX idx_estado  (estado),
    CONSTRAINT fk_clienteplan_cliente
        FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE RESTRICT,
    CONSTRAINT fk_clienteplan_plan
        FOREIGN KEY (plan_id) REFERENCES planes_servicio(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Facturas emitidas
CREATE TABLE IF NOT EXISTS facturas (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    numero_factura      VARCHAR(30)   NOT NULL,
    cliente_id          INT UNSIGNED  NOT NULL,
    cliente_plan_id     INT UNSIGNED  DEFAULT NULL,
    fecha_emision       DATE          NOT NULL,
    fecha_vencimiento   DATE          NOT NULL,
    subtotal            DECIMAL(12,2) NOT NULL DEFAULT 0,
    iva_pct             DECIMAL(5,2)  DEFAULT 19.00,
    iva_valor           DECIMAL(12,2) NOT NULL DEFAULT 0,
    total               DECIMAL(12,2) NOT NULL DEFAULT 0,
    moneda              CHAR(3)       DEFAULT 'COP',
    estado              ENUM('borrador','emitida','enviada','pagada','vencida','anulada') DEFAULT 'borrador',
    fecha_pago          DATE          DEFAULT NULL,
    metodo_pago         VARCHAR(50)   DEFAULT NULL,
    referencia_pago     VARCHAR(100)  DEFAULT NULL,
    notas               TEXT          DEFAULT NULL,
    creado_por          INT UNSIGNED  DEFAULT NULL,
    created_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uk_numero (numero_factura),
    INDEX idx_cliente  (cliente_id),
    INDEX idx_estado   (estado),
    INDEX idx_fecha    (fecha_emision),
    INDEX idx_vencimiento (fecha_vencimiento),
    CONSTRAINT fk_facturas_cliente
        FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE RESTRICT,
    CONSTRAINT fk_facturas_plan
        FOREIGN KEY (cliente_plan_id) REFERENCES cliente_plan(id) ON DELETE SET NULL,
    CONSTRAINT fk_facturas_creador
        FOREIGN KEY (creado_por) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Detalle / lineas de cada factura
CREATE TABLE IF NOT EXISTS factura_detalle (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    factura_id      INT UNSIGNED  NOT NULL,
    concepto        VARCHAR(255)  NOT NULL,
    cantidad        INT           NOT NULL DEFAULT 1,
    precio_unitario DECIMAL(12,2) NOT NULL,
    subtotal        DECIMAL(12,2) NOT NULL,
    referencia_tipo VARCHAR(50)   DEFAULT NULL COMMENT 'movimiento, servicio, dispositivo, etc.',
    referencia_id   INT UNSIGNED  DEFAULT NULL,
    created_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_factura (factura_id),
    CONSTRAINT fk_detalle_factura
        FOREIGN KEY (factura_id) REFERENCES facturas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Registro de consumo mensual por cliente (para facturacion automatica)
CREATE TABLE IF NOT EXISTS consumo_mensual (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cliente_id          INT UNSIGNED  NOT NULL,
    periodo             CHAR(7)       NOT NULL COMMENT 'YYYY-MM',
    movimientos_count   INT           DEFAULT 0,
    dispositivos_count  INT           DEFAULT 0,
    alertas_count       INT           DEFAULT 0,
    reportes_count      INT           DEFAULT 0,
    certificaciones_count INT         DEFAULT 0,
    facturado           TINYINT(1)    DEFAULT 0,
    factura_id          INT UNSIGNED  DEFAULT NULL,
    created_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uk_cliente_periodo (cliente_id, periodo),
    INDEX idx_periodo  (periodo),
    INDEX idx_facturado (facturado),
    CONSTRAINT fk_consumo_cliente
        FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE RESTRICT,
    CONSTRAINT fk_consumo_factura
        FOREIGN KEY (factura_id) REFERENCES facturas(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Log de webhooks recibidos (debugging y metricas)
CREATE TABLE IF NOT EXISTS webhook_log (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    source_ip       VARCHAR(45)   DEFAULT NULL,
    payload_size    INT           DEFAULT 0,
    events_count    INT           DEFAULT 0,
    alarms_count    INT           DEFAULT 0,
    errors_count    INT           DEFAULT 0,
    processing_ms   INT           DEFAULT 0,
    error_detail    TEXT          DEFAULT NULL,
    created_at      DATETIME      DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- GRUPO 9: CACHE Y CONFIGURACION DEL SISTEMA
-- ============================================================

-- Cache de tokens RedGPS (evitar re-auth en cada request)
CREATE TABLE IF NOT EXISTS redgps_token_cache (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cuenta          VARCHAR(50)   NOT NULL COMMENT 'gestion, gps, historial, distribuidor',
    usuario_ref     VARCHAR(100)  DEFAULT NULL COMMENT 'username de la cuenta RedGPS',
    token           TEXT          NOT NULL,
    expires_at      DATETIME      NOT NULL,
    created_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uk_cuenta_usuario (cuenta, usuario_ref),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- API keys por cliente (credenciales RedGPS asignadas)
CREATE TABLE IF NOT EXISTS cliente_keys (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cliente_id      INT UNSIGNED  NOT NULL,
    api_key         VARCHAR(64)   NOT NULL,
    api_secret      VARCHAR(128)  DEFAULT NULL,
    descripcion     VARCHAR(100)  DEFAULT NULL,
    activo          TINYINT(1)    DEFAULT 1,
    ultimo_uso      DATETIME      DEFAULT NULL,
    created_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_cliente (cliente_id),
    INDEX idx_apikey  (api_key),
    CONSTRAINT fk_clientekeys_cliente
        FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Configuracion global del sistema (key-value)
CREATE TABLE IF NOT EXISTS configuracion (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    clave           VARCHAR(100)  NOT NULL,
    valor           TEXT          DEFAULT NULL,
    tipo            ENUM('string','int','bool','json') DEFAULT 'string',
    grupo           VARCHAR(50)   DEFAULT 'general' COMMENT 'general, notificaciones, facturacion, redgps, etc.',
    descripcion     VARCHAR(255)  DEFAULT NULL,
    editable        TINYINT(1)    DEFAULT 1,
    updated_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uk_clave (clave),
    INDEX idx_grupo (grupo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Datos iniciales de configuracion
INSERT INTO configuracion (clave, valor, tipo, grupo, descripcion) VALUES
('empresa_nombre',      'JM Tracking SAS',              'string', 'general',        'Nombre de la empresa'),
('empresa_nit',         '901234567-1',                   'string', 'general',        'NIT de la empresa'),
('empresa_telefono',    '+57 300 1234567',               'string', 'general',        'Telefono principal'),
('empresa_email',       'info@jmtracking.com',           'string', 'general',        'Email de contacto'),
('empresa_direccion',   'Bogota, Colombia',              'string', 'general',        'Direccion fiscal'),
('factura_prefijo',     'JMT',                           'string', 'facturacion',    'Prefijo para numeros de factura'),
('factura_consecutivo', '1',                             'int',    'facturacion',    'Ultimo consecutivo de factura'),
('iva_porcentaje',      '19',                            'int',    'facturacion',    'Porcentaje de IVA por defecto'),
('moneda_defecto',      'COP',                           'string', 'facturacion',    'Moneda por defecto'),
('notif_email_activo',  '0',                             'bool',   'notificaciones', 'Enviar notificaciones por email'),
('notif_sms_activo',    '0',                             'bool',   'notificaciones', 'Enviar notificaciones por SMS'),
('sync_intervalo_min',  '5',                             'int',    'redgps',         'Intervalo de sincronizacion en minutos'),
('alerta_bateria_baja', '20',                            'int',    'alertas',        'Umbral de bateria baja (%)'),
('alerta_sin_comunic',  '30',                            'int',    'alertas',        'Minutos sin comunicacion para alarma'),
('session_timeout_min', '480',                           'int',    'general',        'Timeout de sesion en minutos')
ON DUPLICATE KEY UPDATE valor = VALUES(valor);


-- ============================================================
-- DATOS INICIALES: Permisos por defecto
-- ============================================================

INSERT INTO permisos_modulo (rol, modulo, puede_ver, puede_crear, puede_editar, puede_eliminar, puede_exportar) VALUES
-- Root: super-admin, acceso absoluto a todo
('root', 'dashboard',        1, 1, 1, 1, 1),
('root', 'movimientos',      1, 1, 1, 1, 1),
('root', 'monitoreo',        1, 1, 1, 1, 1),
('root', 'solicitudes',      1, 1, 1, 1, 1),
('root', 'instalaciones',    1, 1, 1, 1, 1),
('root', 'dispositivos',     1, 1, 1, 1, 1),
('root', 'alertas',          1, 1, 1, 1, 1),
('root', 'conductores',      1, 1, 1, 1, 1),
('root', 'vehiculos',        1, 1, 1, 1, 1),
('root', 'transportistas',   1, 1, 1, 1, 1),
('root', 'contenedores',     1, 1, 1, 1, 1),
('root', 'rutas',            1, 1, 1, 1, 1),
('root', 'reportes',         1, 1, 1, 1, 1),
('root', 'certificaciones',  1, 1, 1, 1, 1),
('root', 'facturacion',      1, 1, 1, 1, 1),
('root', 'clientes',         1, 1, 1, 1, 1),
('root', 'usuarios',         1, 1, 1, 1, 1),
('root', 'configuracion',    1, 1, 1, 1, 1),
-- Admin: acceso total operativo (no puede eliminar root ni config global)
('admin', 'dashboard',        1, 1, 1, 1, 1),
('admin', 'movimientos',      1, 1, 1, 1, 1),
('admin', 'monitoreo',        1, 1, 1, 1, 1),
('admin', 'solicitudes',      1, 1, 1, 1, 1),
('admin', 'instalaciones',    1, 1, 1, 1, 1),
('admin', 'dispositivos',     1, 1, 1, 1, 1),
('admin', 'alertas',          1, 1, 1, 1, 1),
('admin', 'conductores',      1, 1, 1, 1, 1),
('admin', 'vehiculos',        1, 1, 1, 1, 1),
('admin', 'transportistas',   1, 1, 1, 1, 1),
('admin', 'contenedores',     1, 1, 1, 1, 1),
('admin', 'rutas',            1, 1, 1, 1, 1),
('admin', 'reportes',         1, 1, 1, 1, 1),
('admin', 'certificaciones',  1, 1, 1, 1, 1),
('admin', 'facturacion',      1, 1, 1, 0, 1),
('admin', 'clientes',         1, 1, 1, 1, 1),
('admin', 'usuarios',         1, 1, 1, 1, 1),
-- Supervisor: ver casi todo, editar operaciones, exportar
('supervisor', 'dashboard',        1, 0, 0, 0, 1),
('supervisor', 'movimientos',      1, 1, 1, 0, 1),
('supervisor', 'monitoreo',        1, 0, 0, 0, 1),
('supervisor', 'solicitudes',      1, 1, 1, 0, 1),
('supervisor', 'instalaciones',    1, 0, 1, 0, 1),
('supervisor', 'dispositivos',     1, 0, 0, 0, 1),
('supervisor', 'alertas',          1, 0, 1, 0, 1),
('supervisor', 'reportes',         1, 0, 0, 0, 1),
('supervisor', 'certificaciones',  1, 1, 0, 0, 1),
('supervisor', 'facturacion',      1, 0, 0, 0, 1),
('supervisor', 'conductores',      1, 1, 1, 0, 0),
('supervisor', 'vehiculos',        1, 1, 1, 0, 0),
('supervisor', 'contenedores',     1, 1, 1, 0, 0),
-- Controlador (CCT): monitoreo en tiempo real, alertas, movimientos
('controlador', 'dashboard',     1, 0, 0, 0, 0),
('controlador', 'movimientos',   1, 1, 1, 0, 1),
('controlador', 'monitoreo',     1, 0, 1, 0, 0),
('controlador', 'alertas',       1, 0, 1, 0, 1),
('controlador', 'solicitudes',   1, 0, 0, 0, 0),
('controlador', 'dispositivos',  1, 0, 0, 0, 0),
('controlador', 'reportes',      1, 0, 0, 0, 1),
-- Tecnico: instalaciones, desinstalaciones, dispositivos
('tecnico', 'dashboard',       1, 0, 0, 0, 0),
('tecnico', 'instalaciones',   1, 1, 1, 0, 0),
('tecnico', 'dispositivos',    1, 0, 1, 0, 0),
('tecnico', 'movimientos',     1, 1, 1, 0, 0),
('tecnico', 'alertas',         1, 0, 0, 0, 0),
-- Distribuidor: API partner con acceso a sus clientes
('distribuidor', 'dashboard',       1, 0, 0, 0, 1),
('distribuidor', 'movimientos',     1, 1, 1, 0, 1),
('distribuidor', 'monitoreo',       1, 0, 0, 0, 0),
('distribuidor', 'solicitudes',     1, 1, 0, 0, 0),
('distribuidor', 'dispositivos',    1, 0, 0, 0, 0),
('distribuidor', 'reportes',        1, 0, 0, 0, 1),
('distribuidor', 'clientes',        1, 0, 0, 0, 0),
-- Usuario (cliente regular): ver sus datos, crear solicitudes
('usuario', 'dashboard',       1, 0, 0, 0, 0),
('usuario', 'movimientos',     1, 0, 0, 0, 1),
('usuario', 'monitoreo',       1, 0, 0, 0, 0),
('usuario', 'solicitudes',     1, 1, 0, 0, 0),
('usuario', 'reportes',        1, 0, 0, 0, 1),
-- Cliente: ver sus datos, crear solicitudes, monitorear sus dispositivos
('cliente', 'dashboard',       1, 0, 0, 0, 0),
('cliente', 'movimientos',     1, 0, 0, 0, 1),
('cliente', 'monitoreo',       1, 0, 0, 0, 0),
('cliente', 'solicitudes',     1, 1, 0, 0, 0),
('cliente', 'dispositivos',    1, 0, 0, 0, 0),
('cliente', 'alertas',         1, 0, 0, 0, 0),
('cliente', 'reportes',        1, 0, 0, 0, 1),
('cliente', 'certificaciones', 1, 0, 0, 0, 1),
-- DIAN: solo lectura de tracking + reportes especiales (NO crea solicitudes)
('dian', 'dashboard',       1, 0, 0, 0, 0),
('dian', 'movimientos',     1, 0, 0, 0, 0),
('dian', 'monitoreo',       1, 0, 0, 0, 0),
('dian', 'reportes',        1, 0, 0, 0, 1),
('dian', 'certificaciones', 1, 0, 0, 0, 1)
ON DUPLICATE KEY UPDATE puede_ver = VALUES(puede_ver);


SET FOREIGN_KEY_CHECKS = 1;
