-- contextual_likes captures a per-session-context snapshot at the time of
-- each like. The recommendation engine in M3 sub-plan #3 uses these rows
-- to compute contextual_match_score per (current session, candidate track).
--
-- Multiple rows per (user, track) are allowed and expected — each row
-- represents a like in a specific session context. Soft-deleted rows
-- remain in the table (deleted_at populated) but are filtered out of the
-- engine's queries via the partial index. Re-liking a previously-unliked
-- track adds a NEW row (does not undelete the old one).
--
-- Note: the M2 events migration (0005) commented that contextual_likes
-- "ships nullable now" but the actual CREATE TABLE was missed. This slice
-- ships the table for the first time.

CREATE TABLE contextual_likes (
    id              uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         uuid        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    track_id        uuid        NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,
    liked_at        timestamptz NOT NULL DEFAULT now(),
    deleted_at      timestamptz,
    session_vector  jsonb,
    session_id      uuid REFERENCES play_sessions(id) ON DELETE SET NULL
);

-- Hot path for both the engine's lookups and the soft-delete UPDATE.
CREATE INDEX contextual_likes_active_idx
    ON contextual_likes (user_id, track_id)
    WHERE deleted_at IS NULL;

-- Vector similarity queries from M3 sub-plan #3 will use this.
CREATE INDEX contextual_likes_vector_idx
    ON contextual_likes USING gin (session_vector);