-- =====================================================================
-- SISTEMA LOTIFICADORA - Esquema de Base de Datos
-- Version: 1 (base)
-- Motor: MySQL 8+ / MariaDB 10.4+
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================================
-- 1. LOTIFICADORAS (empresas cliente) Y SUCURSALES
-- =====================================================================

CREATE TABLE lotificadoras (
    id                          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre_comercial            VARCHAR(150) NOT NULL,
    razon_social                VARCHAR(150) NULL,
    rfc                         VARCHAR(30) NULL,
    direccion                   VARCHAR(255) NULL,
    telefono                    VARCHAR(30) NULL,
    email_contacto              VARCHAR(150) NULL,
    logo_path                   VARCHAR(255) NULL,
    color_primario              VARCHAR(20) NULL DEFAULT '#1a73e8',
    color_secundario            VARCHAR(20) NULL,
    fecha_alta                  DATE NOT NULL,
    fecha_vencimiento_licencia  DATE NOT NULL,
    periodo_licencia_meses      INT NOT NULL DEFAULT 12,
    estatus_licencia            ENUM('activa','por_vencer','bloqueada') NOT NULL DEFAULT 'activa',
    activo                      TINYINT(1) NOT NULL DEFAULT 1,
    created_at                  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at                  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE sucursales (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lotificadora_id     INT UNSIGNED NOT NULL,
    nombre              VARCHAR(150) NOT NULL,
    direccion           VARCHAR(255) NULL,
    telefono            VARCHAR(30) NULL,
    activo              TINYINT(1) NOT NULL DEFAULT 1,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_sucursales_lotificadora FOREIGN KEY (lotificadora_id) REFERENCES lotificadoras(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- 2. CATALOGO DE MODULOS Y ACTIVACION POR LOTIFICADORA (venta de modulos)
-- =====================================================================

CREATE TABLE modulos_catalogo (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    clave           VARCHAR(50) NOT NULL UNIQUE,      -- notificaciones, mapa, contratos, membresias, pagina_publica
    nombre          VARCHAR(150) NOT NULL,
    descripcion     VARCHAR(255) NULL,
    es_basico       TINYINT(1) NOT NULL DEFAULT 0,     -- incluido por defecto al dar de alta
    activo          TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Modulos no vencen: una vez activo, queda activo hasta que soporte lo desactive manualmente
CREATE TABLE lotificadora_modulos (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lotificadora_id     INT UNSIGNED NOT NULL,
    modulo_id           INT UNSIGNED NOT NULL,
    activo              TINYINT(1) NOT NULL DEFAULT 1,
    tipo_cobro          ENUM('unico','suscripcion') NOT NULL DEFAULT 'unico',
    periodicidad        ENUM('mensual','anual') NULL,   -- solo aplica si tipo_cobro = suscripcion
    monto               DECIMAL(12,2) NULL,
    fecha_activacion    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    fecha_desactivacion DATETIME NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_lotmod_lotificadora FOREIGN KEY (lotificadora_id) REFERENCES lotificadoras(id) ON DELETE CASCADE,
    CONSTRAINT fk_lotmod_modulo FOREIGN KEY (modulo_id) REFERENCES modulos_catalogo(id),
    UNIQUE KEY uq_lotificadora_modulo (lotificadora_id, modulo_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- 3. VENDEDORES, VENTAS Y COMISIONES (configurable: unica / recurrente / por modulo)
-- =====================================================================

CREATE TABLE vendedores (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre      VARCHAR(150) NOT NULL,
    email       VARCHAR(150) NULL,
    telefono    VARCHAR(30) NULL,
    activo      TINYINT(1) NOT NULL DEFAULT 1,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE ventas (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lotificadora_id         INT UNSIGNED NOT NULL,
    vendedor_id             INT UNSIGNED NOT NULL,
    fecha_venta             DATE NOT NULL,
    monto_total             DECIMAL(12,2) NOT NULL DEFAULT 0,
    tipo_comision           ENUM('unica','recurrente','por_modulo') NOT NULL DEFAULT 'unica',
    porcentaje_comision     DECIMAL(5,2) NULL,
    monto_comision          DECIMAL(12,2) NULL,
    periodicidad_comision   ENUM('mensual','anual') NULL,   -- solo si tipo_comision = recurrente
    notas                   VARCHAR(255) NULL,
    created_at              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_ventas_lotificadora FOREIGN KEY (lotificadora_id) REFERENCES lotificadoras(id) ON DELETE CASCADE,
    CONSTRAINT fk_ventas_vendedor FOREIGN KEY (vendedor_id) REFERENCES vendedores(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Detalle de comision cuando tipo_comision = por_modulo (cada modulo vendido con su propia regla)
CREATE TABLE venta_modulos (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    venta_id                INT UNSIGNED NOT NULL,
    lotificadora_modulo_id  INT UNSIGNED NOT NULL,
    tipo_comision           ENUM('unica','recurrente') NOT NULL DEFAULT 'unica',
    porcentaje_comision     DECIMAL(5,2) NULL,
    monto_comision          DECIMAL(12,2) NULL,
    periodicidad_comision   ENUM('mensual','anual') NULL,
    CONSTRAINT fk_ventamod_venta FOREIGN KEY (venta_id) REFERENCES ventas(id) ON DELETE CASCADE,
    CONSTRAINT fk_ventamod_lotmod FOREIGN KEY (lotificadora_modulo_id) REFERENCES lotificadora_modulos(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Pago de comisiones (para llevar control de que se le pago al vendedor)
CREATE TABLE pagos_comision (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    venta_id        INT UNSIGNED NOT NULL,
    periodo         VARCHAR(20) NULL,     -- ej '2026-09' cuando es recurrente
    monto           DECIMAL(12,2) NOT NULL,
    fecha_pago      DATE NOT NULL,
    notas           VARCHAR(255) NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_pagocom_venta FOREIGN KEY (venta_id) REFERENCES ventas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- 4. LICENCIA DE LA LOTIFICADORA: RENOVACION Y AVISOS
-- =====================================================================

CREATE TABLE pagos_licencia (
    id                          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lotificadora_id             INT UNSIGNED NOT NULL,
    comprobante_path            VARCHAR(255) NOT NULL,
    fecha_subida                DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    fecha_validacion            DATETIME NULL,
    validado_por_usuario_id     INT UNSIGNED NULL,   -- usuario de soporte que valido
    fecha_vencimiento_anterior  DATE NOT NULL,
    fecha_vencimiento_nueva     DATE NULL,
    estatus                     ENUM('pendiente','validado','rechazado') NOT NULL DEFAULT 'pendiente',
    notas                       VARCHAR(255) NULL,
    created_at                  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_pagolic_lotificadora FOREIGN KEY (lotificadora_id) REFERENCES lotificadoras(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Configuracion de dias/colores del semaforo de vencimiento (editable desde soporte)
CREATE TABLE configuracion_avisos_licencia (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    dias_antes  INT NOT NULL,             -- 25, 20, 15, 10, 5, 1
    color_hex   VARCHAR(20) NOT NULL,
    nivel       ENUM('info','advertencia','urgente','critico') NOT NULL DEFAULT 'info',
    mensaje     VARCHAR(255) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- valores iniciales sugeridos del semaforo (editables despues desde el panel soporte)
INSERT INTO configuracion_avisos_licencia (dias_antes, color_hex, nivel, mensaje) VALUES
(25, '#3B82F6', 'info',        'Tu licencia vence en 25 dias.'),
(20, '#60A5FA', 'info',        'Tu licencia vence en 20 dias.'),
(15, '#FACC15', 'advertencia', 'Tu licencia vence en 15 dias.'),
(10, '#F59E0B', 'advertencia', 'Tu licencia vence en 10 dias.'),
(5,  '#EF4444', 'urgente',     'Tu licencia vence en 5 dias. Renueva para evitar el bloqueo.'),
(1,  '#B91C1C', 'critico',     'Manana se bloqueara el sistema por falta de pago.');

-- =====================================================================
-- 5. USUARIOS, ROLES Y PERMISOS GRANULARES
-- =====================================================================

CREATE TABLE usuarios (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lotificadora_id     INT UNSIGNED NULL,   -- NULL = usuario del modulo Soporte
    nombre              VARCHAR(150) NOT NULL,
    email               VARCHAR(150) NOT NULL UNIQUE,
    password_hash       VARCHAR(255) NOT NULL,
    es_soporte          TINYINT(1) NOT NULL DEFAULT 0,
    activo              TINYINT(1) NOT NULL DEFAULT 1,
    ultimo_acceso       DATETIME NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_usuarios_lotificadora FOREIGN KEY (lotificadora_id) REFERENCES lotificadoras(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Alcance de un usuario: a que sucursales tiene acceso (vacio = toda la lotificadora)
CREATE TABLE usuario_sucursales (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    usuario_id      INT UNSIGNED NOT NULL,
    sucursal_id     INT UNSIGNED NOT NULL,
    CONSTRAINT fk_usersuc_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    CONSTRAINT fk_usersuc_sucursal FOREIGN KEY (sucursal_id) REFERENCES sucursales(id) ON DELETE CASCADE,
    UNIQUE KEY uq_usuario_sucursal (usuario_id, sucursal_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE roles (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lotificadora_id     INT UNSIGNED NULL,   -- NULL = rol del modulo Soporte
    nombre              VARCHAR(100) NOT NULL,
    descripcion         VARCHAR(255) NULL,
    es_predefinido      TINYINT(1) NOT NULL DEFAULT 0,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_roles_lotificadora FOREIGN KEY (lotificadora_id) REFERENCES lotificadoras(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Catalogo maestro de permisos granulares (modulo.accion)
CREATE TABLE permisos (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    modulo_clave    VARCHAR(50) NOT NULL,
    clave           VARCHAR(120) NOT NULL UNIQUE,   -- ej: notificaciones.enviar_manual
    nombre          VARCHAR(150) NOT NULL,
    descripcion     VARCHAR(255) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE rol_permisos (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    rol_id      INT UNSIGNED NOT NULL,
    permiso_id  INT UNSIGNED NOT NULL,
    CONSTRAINT fk_rolperm_rol FOREIGN KEY (rol_id) REFERENCES roles(id) ON DELETE CASCADE,
    CONSTRAINT fk_rolperm_permiso FOREIGN KEY (permiso_id) REFERENCES permisos(id) ON DELETE CASCADE,
    UNIQUE KEY uq_rol_permiso (rol_id, permiso_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE usuario_roles (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    usuario_id  INT UNSIGNED NOT NULL,
    rol_id      INT UNSIGNED NOT NULL,
    CONSTRAINT fk_userrol_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    CONSTRAINT fk_userrol_rol FOREIGN KEY (rol_id) REFERENCES roles(id) ON DELETE CASCADE,
    UNIQUE KEY uq_usuario_rol (usuario_id, rol_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- 6. DESARROLLOS Y LOTES (con datos para el mapa)
-- =====================================================================

CREATE TABLE desarrollos (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lotificadora_id     INT UNSIGNED NOT NULL,
    sucursal_id         INT UNSIGNED NULL,
    nombre              VARCHAR(150) NOT NULL,
    ubicacion           VARCHAR(255) NULL,
    mapa_imagen_path    VARCHAR(255) NULL,
    mapa_geojson_path   VARCHAR(255) NULL,
    activo              TINYINT(1) NOT NULL DEFAULT 1,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_desarrollo_lotificadora FOREIGN KEY (lotificadora_id) REFERENCES lotificadoras(id) ON DELETE CASCADE,
    CONSTRAINT fk_desarrollo_sucursal FOREIGN KEY (sucursal_id) REFERENCES sucursales(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE lotes (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    desarrollo_id       INT UNSIGNED NOT NULL,
    clave_lote          VARCHAR(50) NOT NULL,
    manzana             VARCHAR(20) NULL,
    superficie_m2       DECIMAL(10,2) NULL,
    precio              DECIMAL(12,2) NULL,
    coordenadas_mapa    TEXT NULL,             -- puntos/poligono para dibujar en el mapa
    estatus             ENUM('disponible','apartado','vendido') NOT NULL DEFAULT 'disponible',
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_lotes_desarrollo FOREIGN KEY (desarrollo_id) REFERENCES desarrollos(id) ON DELETE CASCADE,
    UNIQUE KEY uq_lote_desarrollo (desarrollo_id, clave_lote)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- 7. CLIENTES FINALES (compradores)
-- =====================================================================

CREATE TABLE clientes (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lotificadora_id     INT UNSIGNED NOT NULL,
    nombre              VARCHAR(150) NOT NULL,
    email               VARCHAR(150) NULL,
    telefono            VARCHAR(30) NULL,
    direccion           VARCHAR(255) NULL,
    identificacion      VARCHAR(50) NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_clientes_lotificadora FOREIGN KEY (lotificadora_id) REFERENCES lotificadoras(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- 8. CONTRATOS FINANCIEROS Y PAGOS
-- =====================================================================

CREATE TABLE contratos (
    id                          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lote_id                     INT UNSIGNED NOT NULL,
    cliente_id                  INT UNSIGNED NOT NULL,
    sucursal_id                 INT UNSIGNED NULL,
    fecha_contrato              DATE NOT NULL,
    monto_total                 DECIMAL(12,2) NOT NULL,
    enganche                    DECIMAL(12,2) NULL,
    plazo_meses                 INT NULL,
    tasa_interes                DECIMAL(5,2) NULL,
    mensualidad                 DECIMAL(12,2) NULL,
    banco_financiera            VARCHAR(150) NULL,
    meses_incumplimiento_limite INT NOT NULL DEFAULT 3,
    estatus                     ENUM('vigente','en_mora','en_recuperacion','cancelado','liquidado') NOT NULL DEFAULT 'vigente',
    documento_path              VARCHAR(255) NULL,
    created_at                  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at                  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_contrato_lote FOREIGN KEY (lote_id) REFERENCES lotes(id),
    CONSTRAINT fk_contrato_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id),
    CONSTRAINT fk_contrato_sucursal FOREIGN KEY (sucursal_id) REFERENCES sucursales(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE pagos_contrato (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    contrato_id             INT UNSIGNED NOT NULL,
    fecha_pago              DATE NOT NULL,
    monto                   DECIMAL(12,2) NOT NULL,
    mes_correspondiente     VARCHAR(20) NULL,
    comprobante_path        VARCHAR(255) NULL,
    registrado_por_usuario_id INT UNSIGNED NULL,
    created_at              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_pagocontrato_contrato FOREIGN KEY (contrato_id) REFERENCES contratos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- 9. MEMBRESIAS (jardineria, guardias, agua, etc.)
-- =====================================================================

CREATE TABLE membresia_conceptos (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lotificadora_id     INT UNSIGNED NOT NULL,
    nombre              VARCHAR(100) NOT NULL,   -- jardineria, guardias, agua...
    monto_default       DECIMAL(10,2) NOT NULL DEFAULT 0,
    activo              TINYINT(1) NOT NULL DEFAULT 1,
    CONSTRAINT fk_concepto_lotificadora FOREIGN KEY (lotificadora_id) REFERENCES lotificadoras(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE membresias (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lote_id             INT UNSIGNED NOT NULL,
    cliente_id          INT UNSIGNED NOT NULL,
    periodicidad        ENUM('mensual','bimestral','anual') NOT NULL DEFAULT 'mensual',
    monto_total         DECIMAL(10,2) NOT NULL DEFAULT 0,
    recargo_por_atraso  DECIMAL(10,2) NULL,
    estatus             ENUM('al_corriente','en_mora','cancelada') NOT NULL DEFAULT 'al_corriente',
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_membresia_lote FOREIGN KEY (lote_id) REFERENCES lotes(id),
    CONSTRAINT fk_membresia_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE membresia_detalle_conceptos (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    membresia_id    INT UNSIGNED NOT NULL,
    concepto_id     INT UNSIGNED NOT NULL,
    monto           DECIMAL(10,2) NOT NULL DEFAULT 0,
    CONSTRAINT fk_memdet_membresia FOREIGN KEY (membresia_id) REFERENCES membresias(id) ON DELETE CASCADE,
    CONSTRAINT fk_memdet_concepto FOREIGN KEY (concepto_id) REFERENCES membresia_conceptos(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE pagos_membresia (
    id                          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    membresia_id                INT UNSIGNED NOT NULL,
    fecha_pago                  DATE NOT NULL,
    periodo_correspondiente     VARCHAR(20) NULL,
    monto                       DECIMAL(10,2) NOT NULL,
    recargo_aplicado            DECIMAL(10,2) NULL,
    comprobante_path            VARCHAR(255) NULL,
    registrado_por_usuario_id   INT UNSIGNED NULL,
    created_at                  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_pagomem_membresia FOREIGN KEY (membresia_id) REFERENCES membresias(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- 10. NOTIFICACIONES (a clientes finales de la lotificadora)
-- =====================================================================

CREATE TABLE plantillas_notificacion (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lotificadora_id     INT UNSIGNED NOT NULL,
    nombre              VARCHAR(150) NOT NULL,
    tipo                ENUM('pago_vencido','aviso_legal','recordatorio','corte_servicio','otro') NOT NULL DEFAULT 'otro',
    asunto              VARCHAR(200) NULL,
    cuerpo              TEXT NOT NULL,
    canal               ENUM('email','sms','whatsapp','push','impreso') NOT NULL DEFAULT 'email',
    activo              TINYINT(1) NOT NULL DEFAULT 1,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_plantilla_lotificadora FOREIGN KEY (lotificadora_id) REFERENCES lotificadoras(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE reglas_automatizacion_notificacion (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lotificadora_id         INT UNSIGNED NOT NULL,
    plantilla_id            INT UNSIGNED NOT NULL,
    dias_desde_vencimiento  INT NOT NULL,
    orden_escalamiento      INT NOT NULL DEFAULT 1,
    activo                  TINYINT(1) NOT NULL DEFAULT 1,
    CONSTRAINT fk_regla_lotificadora FOREIGN KEY (lotificadora_id) REFERENCES lotificadoras(id) ON DELETE CASCADE,
    CONSTRAINT fk_regla_plantilla FOREIGN KEY (plantilla_id) REFERENCES plantillas_notificacion(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE notificaciones (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lotificadora_id     INT UNSIGNED NOT NULL,
    cliente_id          INT UNSIGNED NULL,
    contrato_id         INT UNSIGNED NULL,
    membresia_id        INT UNSIGNED NULL,
    plantilla_id        INT UNSIGNED NULL,
    canal               ENUM('email','sms','whatsapp','push','impreso') NOT NULL,
    asunto              VARCHAR(200) NULL,
    cuerpo              TEXT NULL,
    estatus             ENUM('pendiente','enviado','entregado','leido','fallido') NOT NULL DEFAULT 'pendiente',
    fecha_envio         DATETIME NULL,
    enviado_por_usuario_id INT UNSIGNED NULL,   -- NULL = enviado automaticamente por regla
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_notif_lotificadora FOREIGN KEY (lotificadora_id) REFERENCES lotificadoras(id) ON DELETE CASCADE,
    CONSTRAINT fk_notif_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE SET NULL,
    CONSTRAINT fk_notif_contrato FOREIGN KEY (contrato_id) REFERENCES contratos(id) ON DELETE SET NULL,
    CONSTRAINT fk_notif_membresia FOREIGN KEY (membresia_id) REFERENCES membresias(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- 11. DOCUMENTOS GENERALES (convenios, cartas, actas) Y BITACORA
-- =====================================================================

CREATE TABLE documentos (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lotificadora_id INT UNSIGNED NOT NULL,
    entidad_tipo    VARCHAR(50) NOT NULL,   -- contrato, membresia, cliente, lote...
    entidad_id      INT UNSIGNED NOT NULL,
    nombre          VARCHAR(150) NOT NULL,
    path            VARCHAR(255) NOT NULL,
    tipo_documento  VARCHAR(100) NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_doc_lotificadora FOREIGN KEY (lotificadora_id) REFERENCES lotificadoras(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE bitacora_acciones (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    usuario_id      INT UNSIGNED NULL,
    lotificadora_id INT UNSIGNED NULL,
    accion          VARCHAR(150) NOT NULL,
    detalle         TEXT NULL,
    ip              VARCHAR(50) NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- 12. DATOS INICIALES: MODULOS Y PERMISOS BASE
-- =====================================================================

INSERT INTO modulos_catalogo (clave, nombre, descripcion, es_basico) VALUES
('usuarios',        'Usuarios y permisos',      'Gestion de usuarios, roles y permisos', 1),
('lotes',           'Lotes y desarrollos',      'Alta de desarrollos y lotes (basico, sin mapa visual)', 1),
('mapa',            'Mapa interactivo',         'Mapa visual de lotes con semaforo de estatus', 0),
('contratos',       'Contratos financieros',    'Contratos, financiamiento y control de incumplimiento', 0),
('membresias',      'Membresias',               'Cuotas de mantenimiento: jardineria, guardias, agua, etc.', 0),
('notificaciones',  'Notificaciones',           'Envio de avisos a clientes por email/sms/whatsapp', 0),
('pagina_publica',  'Pagina publica de venta',  'Sitio publico para mostrar y contactar sobre lotes', 0);

-- Permisos granulares base (modulo.accion) - se puede seguir agregando por modulo
INSERT INTO permisos (modulo_clave, clave, nombre, descripcion) VALUES
-- usuarios
('usuarios', 'usuarios.ver',            'Ver usuarios',              NULL),
('usuarios', 'usuarios.crear',          'Crear usuarios',            NULL),
('usuarios', 'usuarios.editar',         'Editar usuarios',           NULL),
('usuarios', 'usuarios.eliminar',       'Eliminar usuarios',         NULL),
('usuarios', 'usuarios.asignar_rol',    'Asignar roles',             NULL),
('usuarios', 'roles.ver',               'Ver roles y permisos',      NULL),
('usuarios', 'roles.administrar',       'Crear/editar roles',        NULL),
-- lotes / desarrollos
('lotes', 'lotes.ver',                  'Ver lotes',                 NULL),
('lotes', 'lotes.crear',                'Crear lotes/desarrollos',   NULL),
('lotes', 'lotes.editar',               'Editar lotes',              NULL),
('lotes', 'lotes.eliminar',             'Eliminar lotes',            NULL),
-- mapa
('mapa', 'mapa.ver',                    'Ver mapa',                  NULL),
('mapa', 'mapa.editar_lote',            'Editar estatus/ubicacion de lote en mapa', NULL),
('mapa', 'mapa.reasignar',              'Reasignar lote',            NULL),
-- contratos
('contratos', 'contratos.ver',                     'Ver contratos',                  NULL),
('contratos', 'contratos.crear',                   'Crear contratos',                NULL),
('contratos', 'contratos.editar',                  'Editar/modificar monto',         NULL),
('contratos', 'contratos.cancelar',                'Cancelar contrato',              NULL),
('contratos', 'contratos.registrar_pago',          'Registrar pago de contrato',     NULL),
('contratos', 'contratos.autorizar_condonacion',   'Autorizar condonacion de recargos', NULL),
-- membresias
('membresias', 'membresias.ver',              'Ver membresias',            NULL),
('membresias', 'membresias.cobrar',           'Registrar cobro',           NULL),
('membresias', 'membresias.aplicar_descuento','Aplicar descuento',         NULL),
('membresias', 'membresias.cancelar',         'Cancelar membresia',        NULL),
-- notificaciones
('notificaciones', 'notificaciones.ver',                 'Ver notificaciones',                  NULL),
('notificaciones', 'notificaciones.crear_plantilla',     'Crear/editar plantillas',             NULL),
('notificaciones', 'notificaciones.enviar_manual',       'Enviar notificacion manual',          NULL),
('notificaciones', 'notificaciones.enviar_masivo',       'Enviar notificacion masiva',          NULL),
('notificaciones', 'notificaciones.eliminar',            'Eliminar notificacion',               NULL),
('notificaciones', 'notificaciones.ver_bitacora',        'Ver bitacora de envios',              NULL),
('notificaciones', 'notificaciones.configurar_reglas',   'Configurar reglas automaticas',       NULL),
-- reportes
('reportes', 'reportes.ver',       'Ver reportes',      NULL),
('reportes', 'reportes.exportar',  'Exportar reportes',  NULL);

SET FOREIGN_KEY_CHECKS = 1;