-- ============================================================
-- MIGRACION CONSOLIDADA - Modulo Balance de Monedas + campos compras
-- BD base: cambio2026 (o equivalente en VPS)
-- Ejecutar con el cliente mysql (respeta DELIMITER):
--   mysql -u<usuario> -p <DB> < migracion_balance_monedas.sql
--
-- PRE-CHEQUEOS (ejecutar aparte antes de correr este script):
--   SELECT collation_name FROM information_schema.columns
--     WHERE table_schema=DATABASE() AND table_name='login' AND column_name='nombre';
--   -- Debe ser utf8mb3_spanish2_ci. Si difiere, reemplaza todas las
--   -- apariciones de utf8mb3_spanish2_ci por la collation real de login.nombre.
--   SHOW VARIABLES LIKE 'log_bin';
--   -- Si log_bin=ON:  SET GLOBAL log_bin_trust_function_creators=1;
-- ============================================================


-- ============================================================
-- PARTE 1: Campos de compras (Depositado / Balance a favor)
-- ============================================================
ALTER TABLE compras ADD COLUMN IF NOT EXISTS depositado float(19,2) NOT NULL DEFAULT 0 AFTER total;
ALTER TABLE compras ADD COLUMN IF NOT EXISTS balance_favor float(19,2) NOT NULL DEFAULT 0 AFTER depositado;


-- ============================================================
-- PARTE 2: Tablas, funcion, procedimiento, vista y triggers
-- ============================================================

-- 2.1 Tabla de saldo actual por usuario + moneda
CREATE TABLE IF NOT EXISTS `balance_monedas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `usuario` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_spanish2_ci NOT NULL,
  `moneda` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_spanish2_ci NOT NULL,
  `balance` decimal(19,2) NOT NULL DEFAULT 0.00,
  `fecha_actualizacion` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_usuario_moneda` (`usuario`,`moneda`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- 2.2 Libro mayor con saldo corrido (auditoria)
CREATE TABLE IF NOT EXISTS `movimiento_moneda` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fecha` date NOT NULL,
  `hora` time NOT NULL,
  `usuario` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_spanish2_ci NOT NULL,
  `moneda` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_spanish2_ci NOT NULL,
  `operacion` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_spanish2_ci NOT NULL,
  `tabla_origen` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_spanish2_ci DEFAULT NULL,
  `referencia_id` int(11) DEFAULT NULL,
  `debito` decimal(19,2) NOT NULL DEFAULT 0.00,
  `credito` decimal(19,2) NOT NULL DEFAULT 0.00,
  `balance_anterior` decimal(19,2) NOT NULL DEFAULT 0.00,
  `balance_actual` decimal(19,2) NOT NULL DEFAULT 0.00,
  `nota` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_spanish2_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_usuario` (`usuario`),
  KEY `idx_moneda` (`moneda`),
  KEY `idx_fecha` (`fecha`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- 2.3 Funcion de normalizacion de nombre de moneda
DROP FUNCTION IF EXISTS `fn_norm_moneda`;
DELIMITER $$
CREATE FUNCTION `fn_norm_moneda`(m VARCHAR(50)) RETURNS VARCHAR(10)
  DETERMINISTIC
  NO SQL
BEGIN
  DECLARE u VARCHAR(50);
  SET u = UPPER(TRIM(m));
  IF u IN ('USD','DOLAR','DOLARES','DOLLAR') THEN RETURN 'USD';
  ELSEIF u IN ('DOP','PESOS','PESO','RD','RD$') THEN RETURN 'DOP';
  ELSEIF u IN ('GOURDE','GOURDES','HTG') THEN RETURN 'GOURDE';
  ELSEIF u IN ('EURO','EUROS','EUR') THEN RETURN 'EURO';
  ELSE RETURN u;
  END IF;
END$$
DELIMITER ;

-- 2.4 Vista global consolidada por moneda
CREATE OR REPLACE VIEW `v_balance_monedas_global` AS
  SELECT moneda, SUM(balance) AS balance FROM balance_monedas GROUP BY moneda;

-- 2.5 Procedimiento auxiliar: aplica un movimiento y actualiza saldo
DROP PROCEDURE IF EXISTS `sp_aplicar_movimiento_moneda`;
DELIMITER $$
CREATE PROCEDURE `sp_aplicar_movimiento_moneda`(
  IN p_usuario VARCHAR(100), IN p_moneda_raw VARCHAR(50),
  IN p_debito DECIMAL(19,2), IN p_credito DECIMAL(19,2),
  IN p_operacion VARCHAR(20), IN p_tabla VARCHAR(40),
  IN p_ref INT, IN p_nota VARCHAR(255)
)
proc: BEGIN
  DECLARE v_usuario VARCHAR(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_spanish2_ci;
  DECLARE v_mon VARCHAR(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_spanish2_ci;
  DECLARE v_ant DECIMAL(19,2) DEFAULT 0.00;
  DECLARE v_new DECIMAL(19,2) DEFAULT 0.00;
  SET v_usuario = p_usuario;
  SET v_mon = fn_norm_moneda(p_moneda_raw);
  IF v_mon IS NULL OR v_mon = '' OR v_usuario IS NULL OR v_usuario = '' THEN
    LEAVE proc;
  END IF;
  SELECT balance INTO v_ant FROM balance_monedas
    WHERE usuario = v_usuario AND moneda = v_mon LIMIT 1;
  IF v_ant IS NULL THEN SET v_ant = 0.00; END IF;
  SET v_new = v_ant + p_credito - p_debito;
  INSERT INTO balance_monedas (usuario, moneda, balance)
    VALUES (v_usuario, v_mon, v_new)
    ON DUPLICATE KEY UPDATE balance = v_new;
  INSERT INTO movimiento_moneda
    (fecha, hora, usuario, moneda, operacion, tabla_origen, referencia_id,
     debito, credito, balance_anterior, balance_actual, nota)
    VALUES
    (CURDATE(), CURTIME(), v_usuario, v_mon, p_operacion, p_tabla, p_ref,
     p_debito, p_credito, v_ant, v_new, p_nota);
END$$
DELIMITER ;

-- 2.6 Triggers: compra_venta_divisa
DROP TRIGGER IF EXISTS `trg_cvd_ai`;
DELIMITER $$
CREATE TRIGGER `trg_cvd_ai` AFTER INSERT ON `compra_venta_divisa`
FOR EACH ROW
BEGIN
  IF LOWER(NEW.tipo) = 'compra' THEN
    CALL sp_aplicar_movimiento_moneda(NEW.usuario, NEW.moneda, 0, NEW.monto, 'compra', 'compra_venta_divisa', NEW.id, 'Compra divisa');
    CALL sp_aplicar_movimiento_moneda(NEW.usuario, NEW.moneda_destino, NEW.pago, 0, 'compra', 'compra_venta_divisa', NEW.id, 'Pago compra divisa');
  ELSE
    CALL sp_aplicar_movimiento_moneda(NEW.usuario, NEW.moneda, NEW.monto, 0, 'venta', 'compra_venta_divisa', NEW.id, 'Venta divisa');
    CALL sp_aplicar_movimiento_moneda(NEW.usuario, NEW.moneda_destino, 0, NEW.pago, 'venta', 'compra_venta_divisa', NEW.id, 'Cobro venta divisa');
  END IF;
END$$
DELIMITER ;

DROP TRIGGER IF EXISTS `trg_cvd_ad`;
DELIMITER $$
CREATE TRIGGER `trg_cvd_ad` AFTER DELETE ON `compra_venta_divisa`
FOR EACH ROW
BEGIN
  IF LOWER(OLD.tipo) = 'compra' THEN
    CALL sp_aplicar_movimiento_moneda(OLD.usuario, OLD.moneda, OLD.monto, 0, 'anulacion', 'compra_venta_divisa', OLD.id, 'Anula compra divisa');
    CALL sp_aplicar_movimiento_moneda(OLD.usuario, OLD.moneda_destino, 0, OLD.pago, 'anulacion', 'compra_venta_divisa', OLD.id, 'Anula pago compra');
  ELSE
    CALL sp_aplicar_movimiento_moneda(OLD.usuario, OLD.moneda, 0, OLD.monto, 'anulacion', 'compra_venta_divisa', OLD.id, 'Anula venta divisa');
    CALL sp_aplicar_movimiento_moneda(OLD.usuario, OLD.moneda_destino, OLD.pago, 0, 'anulacion', 'compra_venta_divisa', OLD.id, 'Anula cobro venta');
  END IF;
END$$
DELIMITER ;

-- 2.7 Triggers: ingresos (solo modulo distinto de remesa)
DROP TRIGGER IF EXISTS `trg_ing_ai`;
DELIMITER $$
CREATE TRIGGER `trg_ing_ai` AFTER INSERT ON `ingresos`
FOR EACH ROW
BEGIN
  IF IFNULL(NEW.modulo,'cambio') <> 'remesa' THEN
    CALL sp_aplicar_movimiento_moneda(NEW.usuario, NEW.moneda, 0, NEW.monto, 'ingreso', 'ingresos', NEW.id, NEW.concepto);
  END IF;
END$$
DELIMITER ;

DROP TRIGGER IF EXISTS `trg_ing_ad`;
DELIMITER $$
CREATE TRIGGER `trg_ing_ad` AFTER DELETE ON `ingresos`
FOR EACH ROW
BEGIN
  IF IFNULL(OLD.modulo,'cambio') <> 'remesa' THEN
    CALL sp_aplicar_movimiento_moneda(OLD.usuario, OLD.moneda, OLD.monto, 0, 'anulacion', 'ingresos', OLD.id, 'Anula ingreso');
  END IF;
END$$
DELIMITER ;

-- 2.8 Triggers: egresos (solo modulo distinto de remesa)
DROP TRIGGER IF EXISTS `trg_egr_ai`;
DELIMITER $$
CREATE TRIGGER `trg_egr_ai` AFTER INSERT ON `egresos`
FOR EACH ROW
BEGIN
  IF IFNULL(NEW.modulo,'cambio') <> 'remesa' THEN
    CALL sp_aplicar_movimiento_moneda(NEW.usuario, NEW.moneda, NEW.monto, 0, 'egreso', 'egresos', NEW.id, NEW.concepto);
  END IF;
END$$
DELIMITER ;

DROP TRIGGER IF EXISTS `trg_egr_ad`;
DELIMITER $$
CREATE TRIGGER `trg_egr_ad` AFTER DELETE ON `egresos`
FOR EACH ROW
BEGIN
  IF IFNULL(OLD.modulo,'cambio') <> 'remesa' THEN
    CALL sp_aplicar_movimiento_moneda(OLD.usuario, OLD.moneda, 0, OLD.monto, 'anulacion', 'egresos', OLD.id, 'Anula egreso');
  END IF;
END$$
DELIMITER ;

-- 2.9 Trigger: apertura de caja (login) fija el balance base del usuario
DROP TRIGGER IF EXISTS `trg_login_apertura`;
DELIMITER $$
CREATE TRIGGER `trg_login_apertura` AFTER UPDATE ON `login`
FOR EACH ROW
BEGIN
  IF NEW.fecha_apertura IS NOT NULL AND (
       NOT (NEW.apertura_peso   <=> OLD.apertura_peso)
    OR NOT (NEW.apertura_dolar  <=> OLD.apertura_dolar)
    OR NOT (NEW.apertura_gourde <=> OLD.apertura_gourde)
    OR NOT (NEW.apertura_euro   <=> OLD.apertura_euro)
    OR NOT (NEW.fecha_apertura  <=> OLD.fecha_apertura)
  ) THEN
    UPDATE balance_monedas SET balance = IFNULL(NEW.apertura_peso,0)   WHERE usuario = NEW.nombre COLLATE utf8mb3_spanish2_ci AND moneda = 'DOP';
    INSERT INTO balance_monedas (usuario, moneda, balance) SELECT NEW.nombre,'DOP',IFNULL(NEW.apertura_peso,0)
      WHERE NOT EXISTS (SELECT 1 FROM balance_monedas WHERE usuario=NEW.nombre COLLATE utf8mb3_spanish2_ci AND moneda='DOP');
    UPDATE balance_monedas SET balance = IFNULL(NEW.apertura_dolar,0)  WHERE usuario = NEW.nombre COLLATE utf8mb3_spanish2_ci AND moneda = 'USD';
    INSERT INTO balance_monedas (usuario, moneda, balance) SELECT NEW.nombre,'USD',IFNULL(NEW.apertura_dolar,0)
      WHERE NOT EXISTS (SELECT 1 FROM balance_monedas WHERE usuario=NEW.nombre COLLATE utf8mb3_spanish2_ci AND moneda='USD');
    UPDATE balance_monedas SET balance = IFNULL(NEW.apertura_gourde,0) WHERE usuario = NEW.nombre COLLATE utf8mb3_spanish2_ci AND moneda = 'GOURDE';
    INSERT INTO balance_monedas (usuario, moneda, balance) SELECT NEW.nombre,'GOURDE',IFNULL(NEW.apertura_gourde,0)
      WHERE NOT EXISTS (SELECT 1 FROM balance_monedas WHERE usuario=NEW.nombre COLLATE utf8mb3_spanish2_ci AND moneda='GOURDE');
    UPDATE balance_monedas SET balance = IFNULL(NEW.apertura_euro,0)   WHERE usuario = NEW.nombre COLLATE utf8mb3_spanish2_ci AND moneda = 'EURO';
    INSERT INTO balance_monedas (usuario, moneda, balance) SELECT NEW.nombre,'EURO',IFNULL(NEW.apertura_euro,0)
      WHERE NOT EXISTS (SELECT 1 FROM balance_monedas WHERE usuario=NEW.nombre COLLATE utf8mb3_spanish2_ci AND moneda='EURO');

    INSERT INTO movimiento_moneda (fecha,hora,usuario,moneda,operacion,tabla_origen,referencia_id,debito,credito,balance_anterior,balance_actual,nota)
      VALUES (CURDATE(),CURTIME(),NEW.nombre,'DOP','apertura','login',NULL,0,0,0,IFNULL(NEW.apertura_peso,0),'Apertura de caja'),
             (CURDATE(),CURTIME(),NEW.nombre,'USD','apertura','login',NULL,0,0,0,IFNULL(NEW.apertura_dolar,0),'Apertura de caja'),
             (CURDATE(),CURTIME(),NEW.nombre,'GOURDE','apertura','login',NULL,0,0,0,IFNULL(NEW.apertura_gourde,0),'Apertura de caja'),
             (CURDATE(),CURTIME(),NEW.nombre,'EURO','apertura','login',NULL,0,0,0,IFNULL(NEW.apertura_euro,0),'Apertura de caja');
  END IF;
END$$
DELIMITER ;

-- 2.10 Trigger: cierre de turno resetea a 0 el balance del usuario
DROP TRIGGER IF EXISTS `trg_cierre_reset`;
DELIMITER $$
CREATE TRIGGER `trg_cierre_reset` AFTER INSERT ON `cierre_turno_cambio`
FOR EACH ROW
BEGIN
  INSERT INTO movimiento_moneda (fecha,hora,usuario,moneda,operacion,tabla_origen,referencia_id,debito,credito,balance_anterior,balance_actual,nota)
    SELECT CURDATE(),CURTIME(),usuario,moneda,'cierre','cierre_turno_cambio',NEW.id,balance,0,balance,0,'Cierre de turno (reset)'
    FROM balance_monedas WHERE usuario = NEW.usuario COLLATE utf8mb3_spanish2_ci;
  UPDATE balance_monedas SET balance = 0 WHERE usuario = NEW.usuario COLLATE utf8mb3_spanish2_ci;
END$$
DELIMITER ;


-- ============================================================
-- PARTE 3: BACKFILL (carga inicial) - EJECUTAR AL FINAL
-- Solo si el VPS ya tiene operaciones/aperturas activas.
-- ATENCION: hace DELETE FROM balance_monedas (recalcula desde cero).
-- Si NO deseas recalcular saldos existentes, OMITE esta parte.
-- ============================================================
DELETE FROM balance_monedas;

INSERT INTO balance_monedas (usuario, moneda, balance)
SELECT nombre,'DOP',IFNULL(apertura_peso,0)   FROM login WHERE fecha_apertura IS NOT NULL
ON DUPLICATE KEY UPDATE balance = balance + VALUES(balance);
INSERT INTO balance_monedas (usuario, moneda, balance)
SELECT nombre,'USD',IFNULL(apertura_dolar,0)  FROM login WHERE fecha_apertura IS NOT NULL
ON DUPLICATE KEY UPDATE balance = balance + VALUES(balance);
INSERT INTO balance_monedas (usuario, moneda, balance)
SELECT nombre,'GOURDE',IFNULL(apertura_gourde,0) FROM login WHERE fecha_apertura IS NOT NULL
ON DUPLICATE KEY UPDATE balance = balance + VALUES(balance);
INSERT INTO balance_monedas (usuario, moneda, balance)
SELECT nombre,'EURO',IFNULL(apertura_euro,0)  FROM login WHERE fecha_apertura IS NOT NULL
ON DUPLICATE KEY UPDATE balance = balance + VALUES(balance);

INSERT INTO balance_monedas (usuario, moneda, balance)
SELECT usuario, fn_norm_moneda(moneda),
       SUM(CASE WHEN LOWER(tipo)='compra' THEN monto ELSE -monto END)
FROM compra_venta_divisa WHERE IFNULL(estado_caja,'')=''
GROUP BY usuario, fn_norm_moneda(moneda)
ON DUPLICATE KEY UPDATE balance = balance + VALUES(balance);

INSERT INTO balance_monedas (usuario, moneda, balance)
SELECT usuario, fn_norm_moneda(moneda_destino),
       SUM(CASE WHEN LOWER(tipo)='compra' THEN -pago ELSE pago END)
FROM compra_venta_divisa WHERE IFNULL(estado_caja,'')=''
GROUP BY usuario, fn_norm_moneda(moneda_destino)
ON DUPLICATE KEY UPDATE balance = balance + VALUES(balance);

INSERT INTO balance_monedas (usuario, moneda, balance)
SELECT usuario, fn_norm_moneda(moneda), SUM(monto)
FROM ingresos WHERE IFNULL(estado_caja,'')='' AND IFNULL(modulo,'cambio')<>'remesa'
GROUP BY usuario, fn_norm_moneda(moneda)
ON DUPLICATE KEY UPDATE balance = balance + VALUES(balance);

INSERT INTO balance_monedas (usuario, moneda, balance)
SELECT usuario, fn_norm_moneda(moneda), -SUM(monto)
FROM egresos WHERE IFNULL(estado_caja,'')='' AND IFNULL(modulo,'cambio')<>'remesa'
GROUP BY usuario, fn_norm_moneda(moneda)
ON DUPLICATE KEY UPDATE balance = balance + VALUES(balance);

-- ============================================================
-- FIN. Verificacion:
--   SELECT trigger_name FROM information_schema.triggers WHERE trigger_schema=DATABASE();
--   SELECT routine_name FROM information_schema.routines WHERE routine_schema=DATABASE();
--   SELECT * FROM v_balance_monedas_global;
-- ============================================================
