USE vistoria;

-- Atualizacao para informacoes adicionais do veiculo e relatorio publico via QR Code.
-- Pode ser executado mais de uma vez: as colunas/indice so sao criados se ainda nao existirem.

SET @schema_name := DATABASE();

-- Dados adicionais do solicitante e do veiculo.
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 = 'requester_document'
    ) = 0,
    'ALTER TABLE `inspections` ADD COLUMN `requester_document` VARCHAR(30) NULL AFTER `client_name`',
    '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 `exercise_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;

SET @sql := IF(
    (
        SELECT COUNT(*)
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @schema_name
          AND TABLE_NAME = 'inspections'
          AND COLUMN_NAME = 'chassis'
    ) = 0,
    'ALTER TABLE `inspections` ADD COLUMN `chassis` VARCHAR(50) 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 = 'species_type'
    ) = 0,
    'ALTER TABLE `inspections` ADD COLUMN `species_type` VARCHAR(80) NULL AFTER `chassis`',
    '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 = 'city_uf'
    ) = 0,
    'ALTER TABLE `inspections` ADD COLUMN `city_uf` VARCHAR(120) NULL AFTER `species_type`',
    '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 = 'manufacture_model_year'
    ) = 0,
    'ALTER TABLE `inspections` ADD COLUMN `manufacture_model_year` VARCHAR(20) NULL AFTER `city_uf`',
    '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 = 'fuel'
    ) = 0,
    'ALTER TABLE `inspections` ADD COLUMN `fuel` VARCHAR(50) 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 = 'color'
    ) = 0,
    'ALTER TABLE `inspections` ADD COLUMN `color` VARCHAR(50) NULL AFTER `fuel`',
    '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 = 'category'
    ) = 0,
    'ALTER TABLE `inspections` ADD COLUMN `category` VARCHAR(50) NULL AFTER `color`',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Token publico usado no link do relatorio aprovado/QR Code.
SET @sql := IF(
    (
        SELECT COUNT(*)
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @schema_name
          AND TABLE_NAME = 'inspections'
          AND COLUMN_NAME = 'public_report_token'
    ) = 0,
    'ALTER TABLE `inspections` ADD COLUMN `public_report_token` VARCHAR(96) NULL AFTER `status`',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
    (
        SELECT COUNT(*)
        FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA = @schema_name
          AND TABLE_NAME = 'inspections'
          AND INDEX_NAME = 'public_report_token'
    ) = 0,
    'ALTER TABLE `inspections` ADD UNIQUE INDEX `public_report_token` (`public_report_token`)',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Opcional: ja libera link publico para vistorias aprovadas existentes.
UPDATE `inspections`
SET `public_report_token` = REPLACE(UUID(), '-', '')
WHERE `status` = 'Aprovada'
  AND (`public_report_token` IS NULL OR `public_report_token` = '');