-- Migration 011: applicant-tracking notes and status history.
-- One table records both freehand admin notes (kind 'note') and automatic
-- status-change entries (kind 'status'), forming the application's timeline
-- on the admin's application profile page. MySQL syntax (not MariaDB).

CREATE TABLE application_notes (
    id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
    application_id INT UNSIGNED NOT NULL,
    admin_id       INT UNSIGNED NULL,
    kind           VARCHAR(20) NOT NULL DEFAULT 'note',
    body           TEXT NOT NULL,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_app_notes_application (application_id, created_at),
    KEY idx_app_notes_kind (kind),
    CONSTRAINT fk_app_notes_application FOREIGN KEY (application_id)
        REFERENCES applications (id) ON DELETE CASCADE,
    CONSTRAINT fk_app_notes_admin FOREIGN KEY (admin_id)
        REFERENCES admins (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;