trimape_app_api/update_sp.sql

DELIMITER $$
DROP PROCEDURE IF EXISTS `sp_upsert_vehicle_movement_data`$$

CREATE DEFINER=`trimape_admin`@`%` PROCEDURE `sp_upsert_vehicle_movement_data`(
        IN `p_movement_id` INT,
        IN `p_event_type` VARCHAR(20),
        IN `p_checks_snapshot_json` JSON,
        IN `p_contract_ref` VARCHAR(100),
        IN `p_time_out` TIME,
        IN `p_time_in` TIME,
        IN `p_observations` TEXT,
        IN `p_route_summary` TEXT,
        IN `p_authorized_drivers_json` LONGTEXT,
        IN `p_user_id` INT
)
BEGIN
  DECLARE v_item_id INT;
  DECLARE v_engineer_id INT;
  DECLARE v_item_type VARCHAR(50);

  DECLARE v_prev_drivers LONGTEXT;
  DECLARE v_prev_contract VARCHAR(100);

  -- ===============================
  -- Validaciones básicas
  -- ===============================
  IF p_movement_id IS NULL OR p_movement_id <= 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'movement_id inválido';
  END IF;

  IF p_checks_snapshot_json IS NULL OR JSON_VALID(p_checks_snapshot_json) = 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'checks_snapshot_json es requerido y debe ser JSON válido';
  END IF;

  IF p_event_type NOT IN ('SALIDA','DEVOLUCION') THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'event_type inválido';
  END IF;

  -- ELIMINADA: Validación estricta de contrato en SALIDA
  -- A partir de ahora es opcional.

  -- Drivers solo pueden venir en SALIDA
  IF p_event_type = 'SALIDA' THEN
    IF p_authorized_drivers_json IS NOT NULL AND JSON_VALID(p_authorized_drivers_json) = 0 THEN
      SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'authorized_drivers_json debe ser JSON válido';
    END IF;
  END IF;

  -- ===============================
  -- Resolver movimiento → vehículo
  -- ===============================
  SELECT m.item_id, m.engineer_id, i.type
    INTO v_item_id, v_engineer_id, v_item_type
  FROM inventory_movements m
  JOIN inventory_items i ON i.id = m.item_id
  WHERE m.id = p_movement_id
  LIMIT 1;

  IF v_item_id IS NULL THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Movimiento no existe';
  END IF;

  IF v_item_type <> 'vehículo' THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Este movimiento no corresponde a un vehículo';
  END IF;

  -- ===============================
  -- Resolver heredados en DEVOLUCION
  -- ===============================
  SET v_prev_drivers = NULL;
  SET v_prev_contract = NULL;

  IF p_event_type = 'DEVOLUCION' THEN
    -- Heredar drivers de la última SALIDA del mismo vehículo
    SELECT vmd.authorized_drivers_json
      INTO v_prev_drivers
    FROM vehicle_movement_data vmd
    WHERE vmd.inventory_item_id = v_item_id
      AND vmd.event_type = 'SALIDA'
      AND vmd.authorized_drivers_json IS NOT NULL
    ORDER BY vmd.created_at DESC
    LIMIT 1;

    -- Heredar contrato de la última SALIDA del mismo vehículo (ya no validamos que no sea N/A)
    SELECT vmd.contract_ref
      INTO v_prev_contract
    FROM vehicle_movement_data vmd
    WHERE vmd.inventory_item_id = v_item_id
      AND vmd.event_type = 'SALIDA'
    ORDER BY vmd.created_at DESC
    LIMIT 1;
    
    -- Si no hay SALIDA previa, fallar
    IF v_prev_contract IS NULL AND NOT EXISTS (
       SELECT 1 FROM vehicle_movement_data 
       WHERE inventory_item_id = v_item_id AND event_type = 'SALIDA'
    ) THEN
      SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'No existe salida previa para esta DEVOLUCION';
    END IF;
  END IF;

  -- ===============================
  -- Bitácora (histórico)
  -- ===============================
  INSERT INTO vehicle_movement_data (
    movement_id,
    inventory_item_id,
    engineer_id,
    event_type,
    checks_snapshot_json,
    contract_ref,
    time_out,
    time_in,
    observations,
    route_summary,
    authorized_drivers_json,
    created_by
  )
  VALUES (
    p_movement_id,
    v_item_id,
    v_engineer_id,
    p_event_type,
    p_checks_snapshot_json,

    CASE
      WHEN p_event_type = 'SALIDA' THEN p_contract_ref
      ELSE v_prev_contract
    END,

    p_time_out,
    p_time_in,
    p_observations,
    p_route_summary,

    CASE
      WHEN p_event_type = 'SALIDA' THEN p_authorized_drivers_json
      ELSE v_prev_drivers
    END,

    p_user_id
  )
  ON DUPLICATE KEY UPDATE
    event_type = VALUES(event_type),
    checks_snapshot_json = VALUES(checks_snapshot_json),
    contract_ref = VALUES(contract_ref),
    time_out = VALUES(time_out),
    time_in = VALUES(time_in),
    observations = VALUES(observations),
    route_summary = VALUES(route_summary),
    authorized_drivers_json = VALUES(authorized_drivers_json),
    updated_at = CURRENT_TIMESTAMP;

  -- ===============================
  -- Estado vivo del vehículo (SIN contract_ref)
  -- ===============================
  INSERT INTO vehicle_state (
    inventory_item_id,
    checks_json,
    notes,
    updated_by
  )
  VALUES (
    v_item_id,
    p_checks_snapshot_json,
    p_observations,
    p_user_id
  )
  ON DUPLICATE KEY UPDATE
    checks_json = VALUES(checks_json),
    notes = VALUES(notes),
    updated_by = VALUES(updated_by),
    updated_at = CURRENT_TIMESTAMP;

  SELECT p_movement_id AS movement_id;
END$$
DELIMITER ;