-- 001 · Esquema inicial · Integración Familiar (FEVAL)
-- Motor InnoDB, charset utf8mb4. Modelo por edición (aIF 2026, aIF 2027, ...).

CREATE TABLE IF NOT EXISTS ediciones (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(20) NOT NULL,
    nombre VARCHAR(120) NOT NULL,
    fecha_ini_inscripcion DATE NOT NULL,
    fecha_fin_inscripcion DATE NOT NULL,
    fecha_fin_actividad DATE NOT NULL,
    estado ENUM('borrador', 'activa', 'cerrada') NOT NULL DEFAULT 'borrador',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_ediciones_codigo (codigo)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ciudades (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    UNIQUE KEY uq_ciudades_nombre (nombre)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bonos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    UNIQUE KEY uq_bonos_nombre (nombre)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rangos_antiguedad (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    edicion_id INT UNSIGNED NOT NULL,
    nombre VARCHAR(50) NOT NULL,
    meses_min INT UNSIGNED NOT NULL,
    meses_max INT UNSIGNED NULL,
    valor DECIMAL(12, 2) NOT NULL,
    KEY ix_rangos_edicion_meses (edicion_id, meses_min),
    CONSTRAINT fk_rangos_edicion FOREIGN KEY (edicion_id) REFERENCES ediciones (id) ON DELETE RESTRICT
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS oferta (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    edicion_id INT UNSIGNED NOT NULL,
    ciudad_id INT UNSIGNED NOT NULL,
    bono_id INT UNSIGNED NOT NULL,
    pieza_grafica VARCHAR(255) NULL,
    condiciones TEXT NULL,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    UNIQUE KEY uq_oferta (edicion_id, ciudad_id, bono_id),
    CONSTRAINT fk_oferta_edicion FOREIGN KEY (edicion_id) REFERENCES ediciones (id) ON DELETE RESTRICT,
    CONSTRAINT fk_oferta_ciudad FOREIGN KEY (ciudad_id) REFERENCES ciudades (id) ON DELETE RESTRICT,
    CONSTRAINT fk_oferta_bono FOREIGN KEY (bono_id) REFERENCES bonos (id) ON DELETE RESTRICT
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS asociados (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    edicion_id INT UNSIGNED NOT NULL,
    cedula VARCHAR(20) NOT NULL,
    nombre VARCHAR(150) NOT NULL,
    celular VARCHAR(20) NOT NULL,
    correo VARCHAR(150) NOT NULL,
    ciudad_id INT UNSIGNED NOT NULL,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    tiene_curso TINYINT(1) NOT NULL DEFAULT 0,
    antiguedad_meses INT UNSIGNED NOT NULL,
    rango_id INT UNSIGNED NULL,
    elegible TINYINT(1) NOT NULL DEFAULT 0,
    motivo_no_elegible VARCHAR(50) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_asociados_edicion_cedula (edicion_id, cedula),
    KEY ix_asociados_cedula (cedula),
    KEY ix_asociados_ciudad (ciudad_id),
    KEY ix_asociados_elegible (elegible),
    CONSTRAINT fk_asociados_edicion FOREIGN KEY (edicion_id) REFERENCES ediciones (id) ON DELETE RESTRICT,
    CONSTRAINT fk_asociados_ciudad FOREIGN KEY (ciudad_id) REFERENCES ciudades (id) ON DELETE RESTRICT,
    CONSTRAINT fk_asociados_rango FOREIGN KEY (rango_id) REFERENCES rangos_antiguedad (id) ON DELETE SET NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS usuarios_internos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(120) NOT NULL,
    email VARCHAR(150) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    rol ENUM('admin', 'asesor') NOT NULL,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_usuarios_email (email)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS selecciones (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    edicion_id INT UNSIGNED NOT NULL,
    asociado_id INT UNSIGNED NOT NULL,
    bono_id INT UNSIGNED NOT NULL,
    valor DECIMAL(12, 2) NOT NULL,
    estado ENUM('registrada', 'cambiada') NOT NULL DEFAULT 'registrada',
    origen ENUM('asociado', 'asesor') NOT NULL DEFAULT 'asociado',
    usuario_interno_id INT UNSIGNED NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_selecciones_edicion_asociado (edicion_id, asociado_id),
    KEY ix_selecciones_bono (bono_id),
    CONSTRAINT fk_selecciones_edicion FOREIGN KEY (edicion_id) REFERENCES ediciones (id) ON DELETE RESTRICT,
    CONSTRAINT fk_selecciones_asociado FOREIGN KEY (asociado_id) REFERENCES asociados (id) ON DELETE RESTRICT,
    CONSTRAINT fk_selecciones_bono FOREIGN KEY (bono_id) REFERENCES bonos (id) ON DELETE RESTRICT,
    CONSTRAINT fk_selecciones_usuario FOREIGN KEY (usuario_interno_id) REFERENCES usuarios_internos (id) ON DELETE RESTRICT
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS auditoria_cambios (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    seleccion_id INT UNSIGNED NOT NULL,
    asociado_id INT UNSIGNED NOT NULL,
    usuario_interno_id INT UNSIGNED NOT NULL,
    bono_antes INT UNSIGNED NULL,
    bono_despues INT UNSIGNED NOT NULL,
    motivo VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY ix_auditoria_seleccion (seleccion_id),
    KEY ix_auditoria_asociado (asociado_id),
    CONSTRAINT fk_auditoria_seleccion FOREIGN KEY (seleccion_id) REFERENCES selecciones (id) ON DELETE RESTRICT,
    CONSTRAINT fk_auditoria_asociado FOREIGN KEY (asociado_id) REFERENCES asociados (id) ON DELETE RESTRICT,
    CONSTRAINT fk_auditoria_usuario FOREIGN KEY (usuario_interno_id) REFERENCES usuarios_internos (id) ON DELETE RESTRICT,
    CONSTRAINT fk_auditoria_bono_antes FOREIGN KEY (bono_antes) REFERENCES bonos (id) ON DELETE RESTRICT,
    CONSTRAINT fk_auditoria_bono_despues FOREIGN KEY (bono_despues) REFERENCES bonos (id) ON DELETE RESTRICT
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS otp_codigos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    asociado_id INT UNSIGNED NOT NULL,
    codigo_hash CHAR(64) NOT NULL,
    expira_en DATETIME NOT NULL,
    intentos TINYINT UNSIGNED NOT NULL DEFAULT 0,
    usado TINYINT(1) NOT NULL DEFAULT 0,
    ip VARCHAR(45) NOT NULL DEFAULT '',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY ix_otp_asociado_expira (asociado_id, expira_en),
    CONSTRAINT fk_otp_asociado FOREIGN KEY (asociado_id) REFERENCES asociados (id) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS envios (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    asociado_id INT UNSIGNED NOT NULL,
    tipo ENUM('otp_sms', 'confirmacion', 'recordatorio') NOT NULL,
    destino VARCHAR(150) NOT NULL,
    estado ENUM('enviado', 'fallido', 'rebote') NOT NULL,
    proveedor_ref VARCHAR(100) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY ix_envios_asociado (asociado_id),
    KEY ix_envios_tipo (tipo),
    CONSTRAINT fk_envios_asociado FOREIGN KEY (asociado_id) REFERENCES asociados (id) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cargas_base (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    edicion_id INT UNSIGNED NOT NULL,
    usuario_interno_id INT UNSIGNED NOT NULL,
    archivo VARCHAR(255) NOT NULL,
    total INT UNSIGNED NOT NULL DEFAULT 0,
    validas INT UNSIGNED NOT NULL DEFAULT 0,
    rechazadas INT UNSIGNED NOT NULL DEFAULT 0,
    detalle JSON NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY ix_cargas_edicion (edicion_id),
    CONSTRAINT fk_cargas_edicion FOREIGN KEY (edicion_id) REFERENCES ediciones (id) ON DELETE RESTRICT,
    CONSTRAINT fk_cargas_usuario FOREIGN KEY (usuario_interno_id) REFERENCES usuarios_internos (id) ON DELETE RESTRICT
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS avisos (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    asociado_id INT UNSIGNED NOT NULL,
    tipo VARCHAR(40) NOT NULL,
    estado ENUM('abierto', 'resuelto') NOT NULL DEFAULT 'abierto',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY ix_avisos_asociado (asociado_id),
    CONSTRAINT fk_avisos_asociado FOREIGN KEY (asociado_id) REFERENCES asociados (id) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rate_limits (
    clave VARCHAR(120) NOT NULL PRIMARY KEY,
    intentos INT UNSIGNED NOT NULL DEFAULT 1,
    ventana_expira DATETIME NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;