-- 08: FK ocr_document_creation_log → dimensioner_measurement_log
--
-- errno 150 suele ser: tipos distintos (p. ej. parent id BIGINT sin UNSIGNED y
-- columna hija BIGINT UNSIGNED). Este script alinea dimensioner_measurement_log_id
-- al COLUMN_TYPE de dimensioner_measurement_log.id antes del ALTER ADD CONSTRAINT.
-- Ambas tablas deben ser ENGINE=InnoDB.
SET NAMES utf8mb4;
SET @db := DATABASE();

SET @dim_exists := (
  SELECT COUNT(*) FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'dimensioner_measurement_log'
);
SET @dim_inno := (
  SELECT COUNT(*) FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'dimensioner_measurement_log' AND ENGINE = 'InnoDB'
);
SET @ocr_inno := (
  SELECT COUNT(*) FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'ocr_document_creation_log' AND ENGINE = 'InnoDB'
);
SET @child_col := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'ocr_document_creation_log'
    AND COLUMN_NAME = 'dimensioner_measurement_log_id'
);

SET @ref_type := (
  SELECT COLUMN_TYPE FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'dimensioner_measurement_log' AND COLUMN_NAME = 'id'
  LIMIT 1
);
SET @child_type := (
  SELECT COLUMN_TYPE FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'ocr_document_creation_log'
    AND COLUMN_NAME = 'dimensioner_measurement_log_id'
  LIMIT 1
);

SET @need_align := IF(
  @dim_exists > 0 AND @child_col > 0 AND @ref_type IS NOT NULL
  AND (@child_type IS NULL OR @child_type <> @ref_type),
  1, 0
);
SET @sql_align := IF(@need_align = 1,
  CONCAT(
    'ALTER TABLE `ocr_document_creation_log` MODIFY COLUMN `dimensioner_measurement_log_id` ',
    @ref_type,
    ' NULL'
  ),
  'SELECT ''skip align: column already matches dimensioner_measurement_log.id (or nothing to do)'' AS note'
);
PREPARE stmt_align FROM @sql_align;
EXECUTE stmt_align;
DEALLOCATE PREPARE stmt_align;

SET @fk := (
  SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = @db AND TABLE_NAME = 'ocr_document_creation_log'
    AND CONSTRAINT_NAME = 'FK_1D141786BA7F5D5D' AND CONSTRAINT_TYPE = 'FOREIGN KEY'
);
SET @sql := IF(
  @dim_exists > 0 AND @dim_inno > 0 AND @ocr_inno > 0 AND @child_col > 0 AND @fk = 0 AND @ref_type IS NOT NULL,
  'ALTER TABLE `ocr_document_creation_log` ADD CONSTRAINT `FK_1D141786BA7F5D5D` FOREIGN KEY (`dimensioner_measurement_log_id`) REFERENCES `dimensioner_measurement_log` (`id`) ON DELETE SET NULL',
  IF(@fk > 0,
    'SELECT ''skip FK_1D141786BA7F5D5D: already exists'' AS note',
    IF(@dim_exists = 0,
      'SELECT ''skip FK_1D141786BA7F5D5D: dimensioner_measurement_log missing'' AS note',
      IF(@child_col = 0,
        'SELECT ''skip FK_1D141786BA7F5D5D: column dimensioner_measurement_log_id missing (run 05)'' AS note',
        IF(@ref_type IS NULL,
          'SELECT ''skip FK_1D141786BA7F5D5D: could not read dimensioner_measurement_log.id'' AS note',
          IF(@dim_inno = 0 OR @ocr_inno = 0,
            'SELECT ''skip FK_1D141786BA7F5D5D: both tables must be InnoDB'' AS note',
            'SELECT ''skip FK_1D141786BA7F5D5D: unknown precondition'' AS note'
          )
        )
      )
    )
  )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;