-- Adds freeform observations (comments) to MOD reports, each optionally
-- with one or more attached images and per-image captions. Observations
-- can be added at submission time or afterward -- e.g. someone remembers
-- something worth noting the next day, or wants to attach a photo taken
-- during the shift.
--
-- Safe to run more than once.

CREATE TABLE IF NOT EXISTS `mod_report_observations` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `report_id` INT(11) NOT NULL,
    `comment` TEXT DEFAULT NULL,
    `created_by` INT(11) DEFAULT NULL,
    `created_by_name` VARCHAR(150) DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT current_timestamp(),
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_mro_report` FOREIGN KEY (`report_id`) REFERENCES `mod_reports` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `mod_observation_images` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `observation_id` INT(11) NOT NULL,
    `image_path` VARCHAR(255) NOT NULL,
    `caption` VARCHAR(255) DEFAULT NULL,
    `display_order` INT(11) NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_moi_observation` FOREIGN KEY (`observation_id`) REFERENCES `mod_report_observations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;