-- SQL OPCIONAL v7.2 - Importador XLS BCV de tasas de interés para prestaciones
-- Compatible MariaDB/MySQL. No borra datos.

CREATE TABLE IF NOT EXISTS nomina_tasas_interes_bcv (
    id INT AUTO_INCREMENT PRIMARY KEY,
    periodo_ym CHAR(7) NOT NULL,
    anio SMALLINT NOT NULL,
    mes TINYINT NOT NULL,
    tasa_activa_anual DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
    tasa_pasiva_anual DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
    tasa_promedio_anual DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
    gaceta_numero VARCHAR(30) DEFAULT NULL,
    gaceta_fecha DATE DEFAULT NULL,
    fecha_publicacion DATE DEFAULT NULL,
    fuente_url VARCHAR(255) DEFAULT NULL,
    fuente_archivo VARCHAR(180) DEFAULT NULL,
    aplica_prestaciones TINYINT(1) NOT NULL DEFAULT 1,
    observacion VARCHAR(255) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    imported_at DATETIME DEFAULT NULL,
    imported_by VARCHAR(80) DEFAULT NULL,
    UNIQUE KEY uniq_periodo_ym (periodo_ym),
    KEY idx_anio_mes (anio, mes),
    KEY idx_aplica (aplica_prestaciones)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET @db := DATABASE();

SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='nomina_tasas_interes_bcv' AND COLUMN_NAME='gaceta_numero')=0,
'ALTER TABLE nomina_tasas_interes_bcv ADD COLUMN gaceta_numero VARCHAR(30) DEFAULT NULL AFTER tasa_promedio_anual', 'SELECT "gaceta_numero OK"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='nomina_tasas_interes_bcv' AND COLUMN_NAME='gaceta_fecha')=0,
'ALTER TABLE nomina_tasas_interes_bcv ADD COLUMN gaceta_fecha DATE DEFAULT NULL AFTER gaceta_numero', 'SELECT "gaceta_fecha OK"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='nomina_tasas_interes_bcv' AND COLUMN_NAME='fuente_archivo')=0,
'ALTER TABLE nomina_tasas_interes_bcv ADD COLUMN fuente_archivo VARCHAR(180) DEFAULT NULL AFTER fuente_url', 'SELECT "fuente_archivo OK"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='nomina_tasas_interes_bcv' AND COLUMN_NAME='imported_at')=0,
'ALTER TABLE nomina_tasas_interes_bcv ADD COLUMN imported_at DATETIME DEFAULT NULL AFTER updated_at', 'SELECT "imported_at OK"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=@db AND TABLE_NAME='nomina_tasas_interes_bcv' AND COLUMN_NAME='imported_by')=0,
'ALTER TABLE nomina_tasas_interes_bcv ADD COLUMN imported_by VARCHAR(80) DEFAULT NULL AFTER imported_at', 'SELECT "imported_by OK"');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SELECT COUNT(*) AS tasas_cargadas, MIN(periodo_ym) AS desde, MAX(periodo_ym) AS hasta
FROM nomina_tasas_interes_bcv;
