USE vistoria;

-- Campos adicionais do veiculo para preenchimento da placa e laudo publico via QR Code.
-- Pode ser executado mais de uma vez: cada coluna so sera criada se ainda nao existir.

SET @schema_name := DATABASE();

SET @sql := IF(
    (
        SELECT COUNT(*)
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @schema_name
          AND TABLE_NAME = 'inspections'
          AND COLUMN_NAME = 'renavam'
    ) = 0,
    'ALTER TABLE `inspections` ADD COLUMN `renavam` VARCHAR(30) NULL AFTER `plate`',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (
        SELECT COUNT(*)
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @schema_name
          AND TABLE_NAME = 'inspections'
          AND COLUMN_NAME = 'exercise_year'
    ) = 0,
    'ALTER TABLE `inspections` ADD COLUMN `exercise_year` VARCHAR(10) NULL AFTER `renavam`',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (
        SELECT COUNT(*)
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @schema_name
          AND TABLE_NAME = 'inspections'
          AND COLUMN_NAME = 'power_displacement'
    ) = 0,
    'ALTER TABLE `inspections` ADD COLUMN `power_displacement` VARCHAR(80) NULL AFTER `manufacture_model_year`',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (
        SELECT COUNT(*)
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @schema_name
          AND TABLE_NAME = 'inspections'
          AND COLUMN_NAME = 'bodywork'
    ) = 0,
    'ALTER TABLE `inspections` ADD COLUMN `bodywork` VARCHAR(80) NULL AFTER `power_displacement`',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (
        SELECT COUNT(*)
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @schema_name
          AND TABLE_NAME = 'inspections'
          AND COLUMN_NAME = 'cmt'
    ) = 0,
    'ALTER TABLE `inspections` ADD COLUMN `cmt` VARCHAR(30) NULL AFTER `bodywork`',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (
        SELECT COUNT(*)
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @schema_name
          AND TABLE_NAME = 'inspections'
          AND COLUMN_NAME = 'pbt'
    ) = 0,
    'ALTER TABLE `inspections` ADD COLUMN `pbt` VARCHAR(30) NULL AFTER `cmt`',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (
        SELECT COUNT(*)
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @schema_name
          AND TABLE_NAME = 'inspections'
          AND COLUMN_NAME = 'axles'
    ) = 0,
    'ALTER TABLE `inspections` ADD COLUMN `axles` VARCHAR(20) NULL AFTER `pbt`',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (
        SELECT COUNT(*)
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @schema_name
          AND TABLE_NAME = 'inspections'
          AND COLUMN_NAME = 'capacity'
    ) = 0,
    'ALTER TABLE `inspections` ADD COLUMN `capacity` VARCHAR(50) NULL AFTER `axles`',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (
        SELECT COUNT(*)
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @schema_name
          AND TABLE_NAME = 'inspections'
          AND COLUMN_NAME = 'engine_number'
    ) = 0,
    'ALTER TABLE `inspections` ADD COLUMN `engine_number` VARCHAR(50) NULL AFTER `capacity`',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;