salix/db/versions/.archive/10624-blueAnthurium/00-firstScript.sql

ALTER TABLE vn.workerLog
	MODIFY COLUMN changedModel enum('Worker','workerDocument','Calendar','WorkerTimeControlMail','Business')
		CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT 'Worker' NOT NULL,
	MODIFY COLUMN description text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NULL;

-- Fix description, model and id

UPDATE vn.workerLog SET description = NULL WHERE description = '';

UPDATE vn.workerLog
	SET changedModel = 'Business'
	WHERE description LIKE '%del contrato con id%'
		AND changedModel = '';

UPDATE vn.workerLog
	SET changedModelId = REGEXP_REPLACE(description, '.*del contrato con id ([0-9]*)$', '\\1')
	WHERE description LIKE '%del contrato con id%'
		AND changedModel = 'Business'
		AND changedModelId = 0;

-- Various fixes

DELETE FROM vn.workerLog
	WHERE `action` = 'update' AND oldInstance = newInstance;

-- Set instance from description

UPDATE vn.workerLog
	SET newInstance = JSON_OBJECT('departmentFk',
			IF((@val := REGEXP_REPLACE(description, 'Cambiado el departamento a (.*) del contrato con id ?[0-9]*', '\\1')) = '',
				NULL, CAST(@val AS SIGNED)
			)
		),
		description = NULL
	WHERE description LIKE 'Cambiado el departamento a %del contrato con id%'
		AND newInstance IS NULL
		AND changedModel = 'Business';

UPDATE vn.workerLog
	SET newInstance = JSON_OBJECT('ended',
			IF((@val := REGEXP_REPLACE(description, 'Cambiado el Fecha finalización a (.*) del contrato con id ?[0-9]*', '\\1')) = '',
				NULL, CAST(@val AS SIGNED)
			)
		),
		description = NULL
	WHERE description LIKE 'Cambiado el Fecha finalización a %del contrato con id%'
		AND newInstance IS NULL
		AND changedModel = 'Business';

UPDATE vn.workerLog
	SET newInstance = JSON_OBJECT('workcenterFk',
			IF((@val := REGEXP_REPLACE(description, 'Cambiado el Centro de trabajo a (.*) del contrato con id ?[0-9]*', '\\1')) = '',
				NULL, CAST(@val AS SIGNED)
			)
		),
		description = NULL
	WHERE description LIKE 'Cambiado el Centro de trabajo a %del contrato con id%'
		AND newInstance IS NULL
		AND changedModel = 'Business';

UPDATE vn.workerLog
	SET newInstance = JSON_OBJECT('calendarTypeFk',
			IF((@val := REGEXP_REPLACE(description, 'Cambiado el Calendario a (.*) del contrato con id ?[0-9]*', '\\1')) = '',
				NULL, CAST(@val AS SIGNED)
			)
		),
		description = NULL
	WHERE description LIKE 'Cambiado el Calendario a %del contrato con id%'
		AND newInstance IS NULL
		AND changedModel = 'Business';

UPDATE vn.workerLog
	SET newInstance = JSON_OBJECT('amount',
			IF((@val := REGEXP_REPLACE(description, 'Cambiado el importe Pactado a (.*) del contrato con id ?[0-9]*', '\\1')) <> '',
				CAST(REPLACE(@val, ',', '.') AS DECIMAL(12,2)),
				NULL
			)
		),
		description = NULL
	WHERE description LIKE 'Cambiado el importe Pactado a %del contrato con id%'
		AND newInstance IS NULL
		AND changedModel = 'Business';

UPDATE vn.workerLog
	SET newInstance = JSON_OBJECT('rate',
			IF((@val := REGEXP_REPLACE(description, 'Cambiado el tarifa a (.*) del contrato con id ?[0-9]*', '\\1')) <> '',
				CAST(@val AS SIGNED),
				NULL
			)
		),
		description = NULL
	WHERE description LIKE 'Cambiado el tarifa a %del contrato con id%'
		AND newInstance IS NULL
		AND changedModel = 'Business';

UPDATE vn.workerLog
	SET newInstance = JSON_OBJECT('departmentFk',
			IF((@val := REGEXP_REPLACE(description, 'Ha cambiado el departamento  a (.*) del contrato con id ?[0-9]*', '\\1')) <> '',
				@val,
				NULL
			)
		),
		description = NULL
	WHERE description LIKE 'Ha cambiado el departamento  a %del contrato con id%'
		AND newInstance IS NULL
		AND changedModel = 'Business';

UPDATE vn.workerLog
	SET newInstance = JSON_OBJECT('departmentFk',
			IF((@val := REGEXP_REPLACE(description, 'Ha cambiado el departamento .* a (.*) del contrato con id ?[0-9]*', '\\1')) <> '',
				@val,
				NULL
			)
		),
		oldInstance = JSON_OBJECT('departmentFk',
			IF((@val := REGEXP_REPLACE(description, 'Ha cambiado el departamento (.*) a .* del contrato con id ?[0-9]*', '\\1')) <> '',
				@val,
				NULL
			)
		),
		description = NULL
	WHERE description LIKE 'Ha cambiado el departamento % a % del contrato con id%'
		AND newInstance IS NULL
		AND changedModel = 'Business';

UPDATE vn.workerLog
	SET newInstance = JSON_OBJECT('amount',
			IF((@val := REGEXP_REPLACE(description, 'Ha cambiado el salario de [0-9,]* a ([0-9,]*) del contrato con id ?[0-9]*', '\\1')) <> '',
				CAST(REPLACE(@val, ',', '.') AS DECIMAL(12,2)),
				NULL
			)
		),
		oldInstance = JSON_OBJECT('amount',
			IF((@val := REGEXP_REPLACE(description, 'Ha cambiado el salario de ([0-9,]*) a [0-9,]* del contrato con id ?[0-9]*', '\\1')) <> '',
				CAST(REPLACE(@val, ',', '.') AS DECIMAL(12,2)),
				NULL
			)
		),
		description = NULL
	WHERE description LIKE 'Ha cambiado el salario de % a % del contrato con id%'
		AND newInstance IS NULL
		AND changedModel = 'Business';