-- ============================================================================
-- email_proposal_matrix — Matrix-review state for an email_activity_proposal,
-- kept 1:1 with the proposal (proposal_id PK). The CRM runs on the box and has
-- no matrix-nio, so it cannot post to Matrix itself: the intake bot (on the Spark)
-- PULLS pending proposals, posts a review card to the dedicated Matrix review room,
-- and writes the thread-root event_id back here. Persisting it CRM-side (not just in
-- the bot's memory) keeps both surfaces in sync and survives a bot restart.
--
-- A SIDE TABLE rather than new columns on email_activity_proposals because the
-- email-integration migration runner (email_integration/db.py:apply_migrations)
-- re-runs every .sql file on every boot via executescript with no ledger — so
-- CREATE TABLE IF NOT EXISTS is idempotent, whereas ALTER ... ADD COLUMN would throw
-- "duplicate column" on the second boot and abort startup. Reversal: DROP TABLE
-- (this runner has no .down.sql convention; cf. 0001/0002).
--
--   posted_at — set once the bot has posted the review card (event_id = thread root).
--   closed_at — set when the thread is resolved: either the bot decided in-thread, OR
--               the bot announced a web-side decision. A posted+decided proposal with
--               closed_at NULL is exactly the bot's signal to post "decided on the web"
--               into the thread and then close it.
-- ============================================================================
CREATE TABLE IF NOT EXISTS email_proposal_matrix (
    proposal_id  TEXT PRIMARY KEY,
    event_id     TEXT,                  -- Matrix thread-root event id of the posted review card
    posted_at    TEXT,
    closed_at    TEXT,
    created_at   TEXT DEFAULT (datetime('now')),
    FOREIGN KEY(proposal_id) REFERENCES email_activity_proposals(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_email_proposal_matrix_event ON email_proposal_matrix(event_id);