USE `minasvale_producao`;

DELIMITER $$

DROP PROCEDURE IF EXISTS `add_column_if_missing`$$
CREATE PROCEDURE `add_column_if_missing`(
    IN table_name_param VARCHAR(64),
    IN column_name_param VARCHAR(64),
    IN column_definition_param TEXT
)
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = table_name_param
          AND COLUMN_NAME = column_name_param
    ) THEN
        SET @ddl = CONCAT(
            'ALTER TABLE `',
            REPLACE(table_name_param, '`', '``'),
            '` ADD COLUMN `',
            REPLACE(column_name_param, '`', '``'),
            '` ',
            column_definition_param
        );
        PREPARE stmt FROM @ddl;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END$$

DELIMITER ;

CREATE TABLE IF NOT EXISTS `inspection_standards` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(80) NOT NULL,
    `name` VARCHAR(180) NOT NULL,
    `version` VARCHAR(40) NOT NULL,
    `description` TEXT NULL,
    `active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NULL,
    `updated_at` DATETIME NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `inspection_standards_code_unique` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `inspection_groups` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `standard_id` INT(11) UNSIGNED NOT NULL,
    `code` VARCHAR(80) NOT NULL,
    `name` VARCHAR(180) NOT NULL,
    `description` TEXT NULL,
    `sort_order` INT(11) NOT NULL DEFAULT 0,
    `active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NULL,
    `updated_at` DATETIME NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `inspection_groups_standard_code_unique` (`standard_id`, `code`),
    KEY `inspection_groups_standard_id` (`standard_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `inspection_items` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `group_id` INT(11) UNSIGNED NOT NULL,
    `code` VARCHAR(80) NOT NULL,
    `title` VARCHAR(180) NOT NULL,
    `description` TEXT NULL,
    `required` TINYINT(1) NOT NULL DEFAULT 1,
    `allow_not_applicable` TINYINT(1) NOT NULL DEFAULT 1,
    `sort_order` INT(11) NOT NULL DEFAULT 0,
    `active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NULL,
    `updated_at` DATETIME NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `inspection_items_group_code_unique` (`group_id`, `code`),
    KEY `inspection_items_group_id` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `inspection_failure_criteria` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `item_id` INT(11) UNSIGNED NOT NULL,
    `code` VARCHAR(80) NOT NULL,
    `description` TEXT NOT NULL,
    `severity` ENUM('leve', 'grave', 'critica') NOT NULL,
    `causes_rejection` TINYINT(1) NOT NULL DEFAULT 1,
    `active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NULL,
    `updated_at` DATETIME NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `inspection_failure_criteria_item_code_unique` (`item_id`, `code`),
    KEY `inspection_failure_criteria_item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `inspection_evidence_requirements` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `standard_id` INT(11) UNSIGNED NOT NULL,
    `group_id` INT(11) UNSIGNED NULL,
    `item_id` INT(11) UNSIGNED NULL,
    `title` VARCHAR(180) NOT NULL,
    `description` TEXT NULL,
    `evidence_type` ENUM('foto', 'video', 'documento') NOT NULL,
    `required` TINYINT(1) NOT NULL DEFAULT 1,
    `min_quantity` INT(11) NOT NULL DEFAULT 1,
    `sort_order` INT(11) NOT NULL DEFAULT 0,
    `active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NULL,
    `updated_at` DATETIME NULL,
    PRIMARY KEY (`id`),
    KEY `inspection_evidence_requirements_standard_id` (`standard_id`),
    KEY `inspection_evidence_requirements_group_id` (`group_id`),
    KEY `inspection_evidence_requirements_item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `inspection_answers` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `inspection_id` INT(11) UNSIGNED NOT NULL,
    `item_id` INT(11) UNSIGNED NOT NULL,
    `result` ENUM('conforme', 'nao_conforme', 'nao_aplicavel') NOT NULL,
    `notes` TEXT NULL,
    `answered_by` INT(11) UNSIGNED NULL,
    `answered_at` DATETIME NULL,
    `created_at` DATETIME NULL,
    `updated_at` DATETIME NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `inspection_answers_inspection_item_unique` (`inspection_id`, `item_id`),
    KEY `inspection_answers_item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `inspection_answer_failures` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `inspection_answer_id` INT(11) UNSIGNED NOT NULL,
    `failure_criteria_id` INT(11) UNSIGNED NOT NULL,
    `notes` TEXT NULL,
    `created_at` DATETIME NULL,
    `updated_at` DATETIME NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `inspection_answer_failures_answer_criteria_unique` (`inspection_answer_id`, `failure_criteria_id`),
    KEY `inspection_answer_failures_failure_criteria_id` (`failure_criteria_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CALL `add_column_if_missing`('inspection_evidences', 'evidence_requirement_id', 'INT(11) UNSIGNED NULL AFTER `inspection_id`');
CALL `add_column_if_missing`('inspection_evidences', 'item_id', 'INT(11) UNSIGNED NULL AFTER `evidence_requirement_id`');
CALL `add_column_if_missing`('inspection_evidences', 'file_path', 'TEXT NULL AFTER `item_id`');
CALL `add_column_if_missing`('inspection_evidences', 'file_type', 'ENUM(''foto'', ''video'', ''documento'') NULL AFTER `file_path`');
CALL `add_column_if_missing`('inspection_evidences', 'captured_by', 'INT(11) UNSIGNED NULL AFTER `longitude`');
CALL `add_column_if_missing`('inspection_evidences', 'notes', 'TEXT NULL AFTER `captured_at`');
CALL `add_column_if_missing`('inspection_evidences', 'rotation_degrees', 'SMALLINT(4) NOT NULL DEFAULT 0 AFTER `file_size`');

CALL `add_column_if_missing`('inspections', 'inspection_type', 'ENUM(''DER'', ''Escolar'') NULL AFTER `plate`');

DROP PROCEDURE IF EXISTS `add_column_if_missing`;

INSERT INTO `inspection_standards` (`code`, `name`, `version`, `description`, `active`, `created_at`, `updated_at`)
VALUES
    ('ABNT-NBR-17075-2022', 'ABNT NBR 17075', '2022', 'Inspeção de veículos escolares, registros, evidências e documentação da inspeção.', 1, NOW(), NOW()),
    ('SENATRAN-967-2022', 'Portaria SENATRAN nº 967', '2022', 'Grupos e critérios técnicos para inspeção de segurança veicular.', 1, NOW(), NOW())
ON DUPLICATE KEY UPDATE
    `name` = VALUES(`name`),
    `version` = VALUES(`version`),
    `description` = VALUES(`description`),
    `active` = 1,
    `updated_at` = NOW();

SET @senatran_id := (SELECT `id` FROM `inspection_standards` WHERE `code` = 'SENATRAN-967-2022' LIMIT 1);
SET @abnt_id := (SELECT `id` FROM `inspection_standards` WHERE `code` = 'ABNT-NBR-17075-2022' LIMIT 1);

INSERT INTO `inspection_groups` (`standard_id`, `code`, `name`, `description`, `sort_order`, `active`, `created_at`, `updated_at`)
VALUES
    (@senatran_id, 'G01', 'Identificação e condições externas do veículo', 'Identificação, carroceria, placas, marcações e condições externas.', 10, 1, NOW(), NOW()),
    (@senatran_id, 'G02', 'Equipamentos obrigatórios e proibidos', 'Itens obrigatórios, equipamentos vedados e disponibilidade operacional.', 20, 1, NOW(), NOW()),
    (@senatran_id, 'G03', 'Sinalização', 'Sinalização veicular, retrorrefletivos, faixas e dispositivos de advertência.', 30, 1, NOW(), NOW()),
    (@senatran_id, 'G04', 'Iluminação', 'Sistema de iluminação, faróis, lanternas e indicadores.', 40, 1, NOW(), NOW()),
    (@senatran_id, 'G05', 'Freios', 'Sistema de freios de serviço, estacionamento e ensaios aplicáveis.', 50, 1, NOW(), NOW()),
    (@senatran_id, 'G06', 'Direção', 'Sistema de direção, folgas, funcionamento e ensaios aplicáveis.', 60, 1, NOW(), NOW()),
    (@senatran_id, 'G07', 'Eixos e suspensão', 'Componentes de eixo, suspensão, fixações, molas e amortecedores.', 70, 1, NOW(), NOW()),
    (@senatran_id, 'G08', 'Pneus e rodas', 'Pneus, rodas, estepe, dimensões e estado geral.', 80, 1, NOW(), NOW()),
    (@senatran_id, 'G09', 'Sistemas e componentes complementares', 'Cintos, tacógrafo, portas, bancos e demais componentes complementares.', 90, 1, NOW(), NOW()),
    (@senatran_id, 'G10', 'Emissão de poluentes e ruído', 'Condição de escapamento, ruído e emissões aparentes.', 100, 1, NOW(), NOW())
ON DUPLICATE KEY UPDATE
    `name` = VALUES(`name`),
    `description` = VALUES(`description`),
    `sort_order` = VALUES(`sort_order`),
    `active` = 1,
    `updated_at` = NOW();

INSERT INTO `inspection_items` (`group_id`, `code`, `title`, `description`, `required`, `allow_not_applicable`, `sort_order`, `active`, `created_at`, `updated_at`)
VALUES
    ((SELECT id FROM inspection_groups WHERE standard_id = @senatran_id AND code = 'G01'), 'ID-001', 'Conferência de placa e identificação', 'Verificar placa, identificação visual e correspondência com documentos.', 1, 0, 10, 1, NOW(), NOW()),
    ((SELECT id FROM inspection_groups WHERE standard_id = @senatran_id AND code = 'G01'), 'ID-002', 'Marcação do chassi', 'Verificar existência, legibilidade e integridade da marcação do chassi.', 1, 0, 20, 1, NOW(), NOW()),
    ((SELECT id FROM inspection_groups WHERE standard_id = @senatran_id AND code = 'G01'), 'ID-003', 'Marcação do motor', 'Verificar existência, legibilidade e compatibilidade da marcação do motor.', 1, 1, 30, 1, NOW(), NOW()),
    ((SELECT id FROM inspection_groups WHERE standard_id = @senatran_id AND code = 'G01'), 'ID-004', 'Condição externa da carroceria', 'Verificar danos, corrosão, saliências e estado geral externo.', 1, 1, 40, 1, NOW(), NOW()),
    ((SELECT id FROM inspection_groups WHERE standard_id = @senatran_id AND code = 'G02'), 'EQ-001', 'Equipamentos obrigatórios', 'Verificar presença e condição de equipamentos obrigatórios aplicáveis.', 1, 1, 10, 1, NOW(), NOW()),
    ((SELECT id FROM inspection_groups WHERE standard_id = @senatran_id AND code = 'G02'), 'EQ-002', 'Equipamentos proibidos', 'Verificar inexistência de equipamentos ou adaptações proibidas.', 1, 1, 20, 1, NOW(), NOW()),
    ((SELECT id FROM inspection_groups WHERE standard_id = @senatran_id AND code = 'G03'), 'SI-001', 'Sinalização de veículo escolar', 'Verificar faixas, inscrições e identificação externa de transporte escolar.', 1, 0, 10, 1, NOW(), NOW()),
    ((SELECT id FROM inspection_groups WHERE standard_id = @senatran_id AND code = 'G03'), 'SI-002', 'Dispositivos retrorrefletivos', 'Verificar presença, fixação e conservação dos retrorrefletivos.', 1, 1, 20, 1, NOW(), NOW()),
    ((SELECT id FROM inspection_groups WHERE standard_id = @senatran_id AND code = 'G04'), 'IL-001', 'Faróis baixos', 'Verificar funcionamento e condição dos faróis baixos.', 1, 1, 10, 1, NOW(), NOW()),
    ((SELECT id FROM inspection_groups WHERE standard_id = @senatran_id AND code = 'G04'), 'IL-002', 'Lanternas e indicadores', 'Verificar lanternas, luzes de freio, ré, posição e indicadores de direção.', 1, 1, 20, 1, NOW(), NOW()),
    ((SELECT id FROM inspection_groups WHERE standard_id = @senatran_id AND code = 'G05'), 'FR-001', 'Freio de serviço', 'Verificar funcionamento e desempenho do freio de serviço.', 1, 0, 10, 1, NOW(), NOW()),
    ((SELECT id FROM inspection_groups WHERE standard_id = @senatran_id AND code = 'G05'), 'FR-002', 'Freio de estacionamento', 'Verificar funcionamento e retenção do freio de estacionamento.', 1, 0, 20, 1, NOW(), NOW()),
    ((SELECT id FROM inspection_groups WHERE standard_id = @senatran_id AND code = 'G06'), 'DR-001', 'Sistema de direção', 'Verificar folgas, ruídos, fixações e funcionamento do sistema de direção.', 1, 0, 10, 1, NOW(), NOW()),
    ((SELECT id FROM inspection_groups WHERE standard_id = @senatran_id AND code = 'G06'), 'DR-002', 'Ensaio de direção em pista', 'Registrar comportamento do sistema de direção em pista quando aplicável.', 0, 1, 20, 1, NOW(), NOW()),
    ((SELECT id FROM inspection_groups WHERE standard_id = @senatran_id AND code = 'G07'), 'ES-001', 'Suspensão', 'Verificar molas, amortecedores, buchas, batentes e fixações.', 1, 1, 10, 1, NOW(), NOW()),
    ((SELECT id FROM inspection_groups WHERE standard_id = @senatran_id AND code = 'G07'), 'ES-002', 'Eixos e fixações', 'Verificar eixos, suportes, trincas, deformações e fixações.', 1, 1, 20, 1, NOW(), NOW()),
    ((SELECT id FROM inspection_groups WHERE standard_id = @senatran_id AND code = 'G08'), 'PR-001', 'Pneus por eixo', 'Verificar estado, desgaste, dimensões e compatibilidade dos pneus por eixo.', 1, 0, 10, 1, NOW(), NOW()),
    ((SELECT id FROM inspection_groups WHERE standard_id = @senatran_id AND code = 'G08'), 'PR-002', 'Rodas e estepe', 'Verificar rodas, fixadores e pneu estepe.', 1, 1, 20, 1, NOW(), NOW()),
    ((SELECT id FROM inspection_groups WHERE standard_id = @senatran_id AND code = 'G09'), 'SC-001', 'Cronotacógrafo', 'Verificar certificado e condição do cronotacógrafo quando aplicável.', 1, 1, 10, 1, NOW(), NOW()),
    ((SELECT id FROM inspection_groups WHERE standard_id = @senatran_id AND code = 'G09'), 'SC-002', 'Interior, bancos e cintos', 'Verificar bancos, cintos, corredores, portas e condição interna.', 1, 1, 20, 1, NOW(), NOW()),
    ((SELECT id FROM inspection_groups WHERE standard_id = @senatran_id AND code = 'G10'), 'ER-001', 'Sistema de escapamento', 'Verificar fixação, vazamentos, fumaça aparente e condição do escapamento.', 1, 1, 10, 1, NOW(), NOW()),
    ((SELECT id FROM inspection_groups WHERE standard_id = @senatran_id AND code = 'G10'), 'ER-002', 'Ruído aparente', 'Verificar ruído anormal ou alteração aparente no sistema.', 1, 1, 20, 1, NOW(), NOW())
ON DUPLICATE KEY UPDATE
    `title` = VALUES(`title`),
    `description` = VALUES(`description`),
    `required` = VALUES(`required`),
    `allow_not_applicable` = VALUES(`allow_not_applicable`),
    `sort_order` = VALUES(`sort_order`),
    `active` = 1,
    `updated_at` = NOW();

INSERT INTO `inspection_evidence_requirements` (`standard_id`, `group_id`, `item_id`, `title`, `description`, `evidence_type`, `required`, `min_quantity`, `sort_order`, `active`, `created_at`, `updated_at`)
SELECT @abnt_id, g.id, i.id, v.title, v.description, v.evidence_type, v.required, v.min_quantity, v.sort_order, 1, NOW(), NOW()
FROM (
    SELECT NULL group_code, 'ID-002' item_code, 'Chassi' title, 'Registre a marcação do chassi com nitidez suficiente para conferência. (EV-001)' description, 'foto' evidence_type, 1 required, 1 min_quantity, 10 sort_order
    UNION ALL SELECT 'G01', 'ID-001', 'Frente', 'Registre a frente do veículo de forma ampla e legível. (EV-002)', 'foto', 1, 1, 20
    UNION ALL SELECT 'G01', 'ID-001', 'Traseira', 'Registre a traseira do veículo de forma ampla e legível. (EV-003)', 'foto', 1, 1, 30
    UNION ALL SELECT 'G08', 'PR-001', 'Pneu dianteiro', 'Registre o pneu dianteiro, incluindo banda de rodagem e lateral. (EV-004)', 'foto', 1, 1, 40
    UNION ALL SELECT 'G08', 'PR-001', 'Pneu traseiro', 'Registre o pneu traseiro, incluindo banda de rodagem e lateral. (EV-005)', 'foto', 1, 1, 50
    UNION ALL SELECT 'G09', 'SC-001', 'Cronotacógrafo', 'Registre o equipamento cronotacógrafo instalado no veículo. (EV-006)', 'foto', 1, 1, 60
    UNION ALL SELECT 'G09', 'SC-002', 'Interior veículo', 'Registre bancos, cintos, corredor e área interna. (EV-007)', 'foto', 1, 1, 70
    UNION ALL SELECT 'G09', 'SC-002', 'Janela emergência', 'Registre a janela de emergência e suas condições de identificação/acesso. (EV-008)', 'foto', 1, 1, 80
    UNION ALL SELECT 'G01', 'ID-004', '45 graus frente', 'Registre uma visão diagonal dianteira do veículo. (EV-009)', 'foto', 1, 1, 90
    UNION ALL SELECT 'G01', 'ID-004', '45 graus traseira', 'Registre uma visão diagonal traseira do veículo. (EV-010)', 'foto', 1, 1, 100
    UNION ALL SELECT NULL, NULL, 'CRLV', 'Anexe CRLV legível, sem cortes nas bordas do documento. (EV-011)', 'documento', 1, 1, 110
    UNION ALL SELECT 'G09', 'SC-001', 'Certificado Cronotacógrafo', 'Anexe o certificado do cronotacógrafo vigente quando aplicável. (EV-012)', 'documento', 1, 1, 120
    UNION ALL SELECT 'G01', 'ID-004', 'Panorâmica lateral', 'Registre a lateral inteira do veículo. (EV-013)', 'foto', 1, 1, 130
    UNION ALL SELECT 'G02', 'EQ-001', 'Equipamento obrigatório', 'Registre os equipamentos obrigatórios disponíveis para conferência. (EV-014)', 'foto', 1, 1, 140
    UNION ALL SELECT 'G09', 'SC-002', 'Lotação passageiros', 'Registre a lotação/assentos de passageiros. (EV-015)', 'foto', 1, 1, 150
    UNION ALL SELECT 'G09', 'SC-002', 'Cinto segurança', 'Registre os cintos de segurança e seus pontos de fixação. (EV-016)', 'foto', 1, 1, 160
    UNION ALL SELECT NULL, NULL, 'Lista check list', 'Anexe a lista de checklist da vistoria. (EV-017)', 'documento', 1, 1, 170
    UNION ALL SELECT 'G04', 'IL-001', 'Filmagem farol frenometro', 'Registre a filmagem do farol/frenômetro quando aplicável. (EV-018)', 'video', 0, 1, 180
    UNION ALL SELECT 'G05', 'FR-001', 'Filmagem frenagem', 'Registre a filmagem do ensaio de frenagem em pista quando aplicável. (EV-019)', 'video', 0, 1, 190
    UNION ALL SELECT 'G06', 'DR-002', 'Filmagem direção', 'Registre a filmagem do ensaio do sistema de direção em pista quando aplicável. (EV-020)', 'video', 0, 1, 200
) v
LEFT JOIN inspection_groups g ON g.standard_id = @senatran_id AND g.code = v.group_code
LEFT JOIN inspection_items i ON i.group_id = COALESCE(g.id, (SELECT id FROM inspection_groups WHERE standard_id = @senatran_id AND code = 'G01' LIMIT 1)) AND i.code = v.item_code
WHERE NOT EXISTS (
    SELECT 1
    FROM inspection_evidence_requirements r
    WHERE r.standard_id = @abnt_id
      AND r.sort_order = v.sort_order
);

UPDATE `inspection_evidence_requirements`
SET `active` = 1
WHERE `standard_id` = @abnt_id
  AND `sort_order` < 900;