-- Phase 5: Application tracker — event log + indexes for state-machine queries.
-- Existing applications table already has the status field with the right values.
-- This migration adds an immutable event log for audit + follow-up cadence support.

CREATE TABLE IF NOT EXISTS tracker_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    application_id INTEGER NOT NULL,
    from_status TEXT,
    to_status TEXT NOT NULL,
    note TEXT,
    occurred_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (application_id) REFERENCES applications(id)
);

CREATE INDEX IF NOT EXISTS idx_tracker_events_application ON tracker_events(application_id);
CREATE INDEX IF NOT EXISTS idx_tracker_events_occurred ON tracker_events(occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_applications_applied_at ON applications(applied_at);
CREATE INDEX IF NOT EXISTS idx_follow_ups_at ON follow_ups(follow_up_at);