-- =============================================================
-- product_sync_log: tabla de seguimiento de cambios de productos
-- para habilitar el endpoint GET /api/v1/products/changes?since=
-- =============================================================

USE clientes_app;

-- -------------------------------------
-- 1. Tabla
-- -------------------------------------
CREATE TABLE IF NOT EXISTS product_sync_log (
    sku         VARCHAR(45)               NOT NULL,
    change_type ENUM('updated','deleted') NOT NULL DEFAULT 'updated',
    updated_at  DATETIME                  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (sku),
    INDEX idx_updated_at (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- -------------------------------------
-- 2. Triggers sobre estadodeposicion
-- -------------------------------------

-- Producto nuevo
DROP TRIGGER IF EXISTS trg_edp_insert;
DELIMITER $$
CREATE TRIGGER trg_edp_insert
AFTER INSERT ON estadodeposicion
FOR EACH ROW
BEGIN
    INSERT INTO product_sync_log (sku, change_type, updated_at)
    VALUES (TRIM(NEW.Articulo), 'updated', NOW())
    ON DUPLICATE KEY UPDATE
        change_type = 'updated',
        updated_at  = NOW();
END$$
DELIMITER ;

-- Stock modificado
DROP TRIGGER IF EXISTS trg_edp_update;
DELIMITER $$
CREATE TRIGGER trg_edp_update
AFTER UPDATE ON estadodeposicion
FOR EACH ROW
BEGIN
    IF NEW.Burghi <> OLD.Burghi OR NEW.ar_nombre <> OLD.ar_nombre THEN
        INSERT INTO product_sync_log (sku, change_type, updated_at)
        VALUES (TRIM(NEW.Articulo), 'updated', NOW())
        ON DUPLICATE KEY UPDATE
            change_type = 'updated',
            updated_at  = NOW();
    END IF;
END$$
DELIMITER ;

-- Producto eliminado
DROP TRIGGER IF EXISTS trg_edp_delete;
DELIMITER $$
CREATE TRIGGER trg_edp_delete
AFTER DELETE ON estadodeposicion
FOR EACH ROW
BEGIN
    INSERT INTO product_sync_log (sku, change_type, updated_at)
    VALUES (TRIM(OLD.Articulo), 'deleted', NOW())
    ON DUPLICATE KEY UPDATE
        change_type = 'deleted',
        updated_at  = NOW();
END$$
DELIMITER ;


-- -------------------------------------
-- 3. Triggers sobre precioarticulo
-- -------------------------------------

-- Precio modificado
DROP TRIGGER IF EXISTS trg_precio_update;
DELIMITER $$
CREATE TRIGGER trg_precio_update
AFTER UPDATE ON precioarticulo
FOR EACH ROW
BEGIN
    IF NEW.Publico       <> OLD.Publico       OR
       NEW.Distribuidor  <> OLD.Distribuidor  OR
       NEW.Identificado  <> OLD.Identificado  OR
       NEW.Flota         <> OLD.Flota
    THEN
        INSERT INTO product_sync_log (sku, change_type, updated_at)
        VALUES (TRIM(NEW.ar_codigo), 'updated', NOW())
        ON DUPLICATE KEY UPDATE
            change_type = 'updated',
            updated_at  = NOW();
    END IF;
END$$
DELIMITER ;

-- Precio nuevo (artículo que no tenía precio)
DROP TRIGGER IF EXISTS trg_precio_insert;
DELIMITER $$
CREATE TRIGGER trg_precio_insert
AFTER INSERT ON precioarticulo
FOR EACH ROW
BEGIN
    INSERT INTO product_sync_log (sku, change_type, updated_at)
    VALUES (TRIM(NEW.ar_codigo), 'updated', NOW())
    ON DUPLICATE KEY UPDATE
        change_type = 'updated',
        updated_at  = NOW();
END$$
DELIMITER ;


-- -------------------------------------
-- 4. Poblar la tabla con el estado actual
--    (carga inicial — ejecutar una sola vez)
-- -------------------------------------
INSERT INTO product_sync_log (sku, change_type, updated_at)
SELECT TRIM(Articulo), 'updated', NOW()
FROM estadodeposicion
ON DUPLICATE KEY UPDATE updated_at = updated_at;
