-- Normalized per-module detection output for one verification result. Enables
-- analytics like "which checks fail most often" without parsing JSON.
CREATE TABLE IF NOT EXISTS `detection_logs` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `result_id`  BIGINT UNSIGNED NOT NULL,
    `module`     VARCHAR(40)     NOT NULL,
    `raw_score`  DECIMAL(5,4)    NOT NULL DEFAULT 0,   -- 0..1 normalized module output
    `weight`     DECIMAL(6,2)    NOT NULL DEFAULT 0,
    `weighted`   DECIMAL(7,2)    NOT NULL DEFAULT 0,   -- raw_score * weight
    `passed`     JSON            NULL,
    `failed`     JSON            NULL,
    `detail`     JSON            NULL,
    `created_at` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_dl_result` (`result_id`),
    KEY `idx_dl_module` (`module`),
    CONSTRAINT `fk_dl_result` FOREIGN KEY (`result_id`) REFERENCES `verification_results` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;