USE vistoria;

-- Atualizacao para edicao pelo vistoriador e fotos extras com legenda.
-- A feature usa a tabela inspection_evidences existente:
-- - evidencias normativas continuam vinculadas por evidence_requirement_id
-- - fotos extras ficam sem requirement/item e guardam a legenda em notes

SET @schema_name := DATABASE();

-- Permite evidencias sem item legado, necessario para fotos extras.
SELECT CONSTRAINT_NAME
INTO @fk_evidence_item_id
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'inspection_evidences'
  AND COLUMN_NAME = 'evidence_item_id'
  AND REFERENCED_TABLE_NAME IS NOT NULL
LIMIT 1;

SET @sql := IF(
    @fk_evidence_item_id IS NULL,
    'SELECT 1',
    CONCAT('ALTER TABLE `inspection_evidences` DROP FOREIGN KEY `', @fk_evidence_item_id, '`')
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE;

ALTER TABLE `inspection_evidences`
    MODIFY `evidence_item_id` INT(11) UNSIGNED NULL;

SET @sql := IF(
    (
        SELECT COUNT(*)
        FROM information_schema.KEY_COLUMN_USAGE
        WHERE TABLE_SCHEMA = @schema_name
          AND TABLE_NAME = 'inspection_evidences'
          AND COLUMN_NAME = 'evidence_item_id'
          AND REFERENCED_TABLE_NAME = 'inspection_evidence_items'
    ) = 0,
    'ALTER TABLE `inspection_evidences` ADD CONSTRAINT `inspection_evidences_evidence_item_id_fk` FOREIGN KEY (`evidence_item_id`) REFERENCES `inspection_evidence_items` (`id`) ON DELETE SET NULL ON UPDATE CASCADE',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE;

-- Colunas usadas pelas evidencias normativas e pelas fotos extras.
SET @sql := IF(
    (
        SELECT COUNT(*)
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @schema_name
          AND TABLE_NAME = 'inspection_evidences'
          AND COLUMN_NAME = 'evidence_requirement_id'
    ) = 0,
    'ALTER TABLE `inspection_evidences` ADD COLUMN `evidence_requirement_id` INT(11) UNSIGNED NULL AFTER `inspection_id`',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE;

SET @sql := IF(
    (
        SELECT COUNT(*)
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @schema_name
          AND TABLE_NAME = 'inspection_evidences'
          AND COLUMN_NAME = 'item_id'
    ) = 0,
    'ALTER TABLE `inspection_evidences` ADD COLUMN `item_id` INT(11) UNSIGNED NULL AFTER `evidence_requirement_id`',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE;

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

SET @sql := IF(
    (
        SELECT COUNT(*)
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @schema_name
          AND TABLE_NAME = 'inspection_evidences'
          AND COLUMN_NAME = 'file_type'
    ) = 0,
    'ALTER TABLE `inspection_evidences` ADD COLUMN `file_type` ENUM(''foto'', ''video'', ''documento'') NULL AFTER `file_path`',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE;

SET @sql := IF(
    (
        SELECT COUNT(*)
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @schema_name
          AND TABLE_NAME = 'inspection_evidences'
          AND COLUMN_NAME = 'captured_by'
    ) = 0,
    'ALTER TABLE `inspection_evidences` ADD COLUMN `captured_by` INT(11) UNSIGNED NULL AFTER `longitude`',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE;

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

-- Indices e FKs auxiliares, se ainda nao existirem.
SET @sql := IF(
    (
        SELECT COUNT(*)
        FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA = @schema_name
          AND TABLE_NAME = 'inspection_evidences'
          AND COLUMN_NAME = 'evidence_requirement_id'
    ) = 0,
    'ALTER TABLE `inspection_evidences` ADD INDEX `evidence_requirement_id` (`evidence_requirement_id`)',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE;

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

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

SET @sql := IF(
    (
        SELECT COUNT(*)
        FROM information_schema.KEY_COLUMN_USAGE
        WHERE TABLE_SCHEMA = @schema_name
          AND TABLE_NAME = 'inspection_evidences'
          AND COLUMN_NAME = 'evidence_requirement_id'
          AND REFERENCED_TABLE_NAME = 'inspection_evidence_requirements'
    ) = 0,
    'ALTER TABLE `inspection_evidences` ADD CONSTRAINT `inspection_evidences_evidence_requirement_id_fk` FOREIGN KEY (`evidence_requirement_id`) REFERENCES `inspection_evidence_requirements` (`id`) ON DELETE SET NULL ON UPDATE CASCADE',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE;

SET @sql := IF(
    (
        SELECT COUNT(*)
        FROM information_schema.KEY_COLUMN_USAGE
        WHERE TABLE_SCHEMA = @schema_name
          AND TABLE_NAME = 'inspection_evidences'
          AND COLUMN_NAME = 'item_id'
          AND REFERENCED_TABLE_NAME = 'inspection_items'
    ) = 0,
    'ALTER TABLE `inspection_evidences` ADD CONSTRAINT `inspection_evidences_item_id_fk` FOREIGN KEY (`item_id`) REFERENCES `inspection_items` (`id`) ON DELETE SET NULL ON UPDATE CASCADE',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE;

SET @sql := IF(
    (
        SELECT COUNT(*)
        FROM information_schema.KEY_COLUMN_USAGE
        WHERE TABLE_SCHEMA = @schema_name
          AND TABLE_NAME = 'inspection_evidences'
          AND COLUMN_NAME = 'captured_by'
          AND REFERENCED_TABLE_NAME = 'users'
    ) = 0,
    'ALTER TABLE `inspection_evidences` ADD CONSTRAINT `inspection_evidences_captured_by_fk` FOREIGN KEY (`captured_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE;