-- +goose Up

-- The READING half of contract 0.3.0 (pack P7): the chapter tree with its pairs, the bank as the
-- client signs it, the frame history a stream is resumed from, and the vocabularies 0.3.0 renamed.
--
-- The rule this migration follows everywhere: a column nobody writes is a wire LIE, not a
-- placeholder. Three of them are removed below rather than left for a later pack, because each was
-- projected as a zero or as a word the contract now forbids.

-- ── Structure: which cut of the book the client is looking at ───────────────────────────────────
--
-- `revision` moves on every materialization; `structure_version` moves only when the book is cut
-- again, which is what a cursor, a pair anchor and a term's chapter window are bound to (canon
-- §StructureVersion). Binding those to the revision instead would restart pagination constantly.
--
-- `manifest_key` is how the move is DETECTED and it is the engine's own validity key
-- (pipeline/manifest.go): everything whose change moves a chapter boundary is hashed into it, so
-- comparing one string answers "is this still the same book, cut the same way" without the platform
-- re-deriving a rule that lives on the other side of the seam.
alter table books
    add column structure_version integer not null default 0,
    add column manifest_key text not null default '',
    -- The frame counter of this book's event history. Separate from `revision` and it has to be:
    -- one is a position in a stream a client resumes from, the other is the state a read is ordered
    -- against, and a server that merged them would answer `Last-Event-ID` with a number that jumps
    -- whenever anything materializes (canon §EventEnvelope).
    add column event_position bigint not null default 0,
    -- The revision at which each collection was last replaced WHOLESALE. A delta read whose
    -- watermark predates one cannot be answered — a delta cannot express a deletion — and the
    -- contract's answer to that is `400` with `cause.code: version_too_old` rather than a silently
    -- short list (canon §AfterVersion).
    add column structure_reset_revision bigint not null default 0,
    add column bank_reset_revision bigint not null default 0;

-- ── `finalizing` leaves the vocabularies ────────────────────────────────────────────────────────
--
-- The engine has no such phase (`grep -ri finaliz backend/internal` is empty), the seam's event
-- vocabulary has no such value, and nothing on this side ever wrote one. It survived in three
-- checks and two Go switches because a status nobody writes costs nothing to keep — until a client
-- is generated from a contract that no longer has it (0.3.0 removed the value) and the DDL is the
-- only place still promising it.
alter table books drop constraint books_status_check;
alter table books add constraint books_status_check check (status in (
    'uploading', 'parsing', 'not_started', 'translating', 'awaiting_bank',
    'ready', 'stopped', 'rejected', 'failed', 'paused'));

alter table runs drop constraint runs_status_check;
alter table runs add constraint runs_status_check check (status in (
    'translating', 'awaiting_bank', 'ready', 'stopped', 'failed', 'paused'));

-- ── The run: why it failed, and which segment its bar is measuring ──────────────────────────────
alter table runs
    -- The contract's RunFailureReason, and the one state that IS an error carries it or nothing.
    -- Three values, deliberately coarse: what a client decides from this is whether to offer a
    -- retry, and a fourth value that answered that question the same way would only be a fourth
    -- phrase to write.
    add column failure_reason text
        check (failure_reason is null or failure_reason in ('source_unreadable', 'service_error', 'interrupted')),
    -- How many chapters the BOOK had already finished when this run started. It is the baseline the
    -- run's own bar is measured from: `unit_resolutions` persist across runs (a resumed run re-walks
    -- finished chapters at $0 and must not move them), so without it a second run over a half-done
    -- book opens at `done` = everything the FIRST run did, against a `total` of what the second one
    -- bought — a fraction that starts above zero and can exceed one (canon §Progress: "Chapters
    -- finished in this segment", "the fraction always reaches one").
    add column chapters_before integer not null default 0,
    -- Whether this run's signing stop has been cleared. It is what tells the FIRST segment of the
    -- work from the second (canon §Progress: a segment is the work between two stops, and the
    -- counter starts again from zero when a stop is lifted). Phases stay inside the platform — the
    -- wire carries one counter — and this is the one bit of that split the projection needs.
    add column bank_released boolean not null default false;

-- ── Chapters ────────────────────────────────────────────────────────────────────────────────────
--
-- `heading` GOES. The engine's manifest carries one and it is a deterministic render of the pair's
-- heading rule («Глава N»), not a label out of the book's data — its own comment says a consumer
-- must not present it as one (pipeline/manifest.go), and the contract forbids a deployment to put a
-- rendered ordinal in that field (canon §Chapter.heading). A column that may only ever hold the
-- empty string is a trap for the next reader, so the projection answers `null` from nothing at all
-- until a producer of real labels exists (engine backlog row 160).
--
-- `note_count` goes for the same reason wearing the opposite clothes: nothing wrote it, so every
-- read answered zero — a number that is not "unknown" but "there are none", which is a different
-- fact and a false one. Notes are derived from `unit_resolutions` (see below), and so is their
-- count.
alter table chapters drop column heading, drop column note_count;
alter table books drop column note_count;

-- `books.genre` goes with them, and it is the third instance of the same rule rather than a fourth
-- decision: the field left the contract, the intake form and the render in 0.3.0 (Б-23, owner
-- 16.08), so nothing writes it any more. The engine keeps the key in its own `book.yaml` until its
-- removal lands (unified backlog row 184) — and that value is now the OPERATOR's, carried through
-- from their template untouched, which is why the platform has nowhere left to keep one.
alter table books drop column genre;

-- ── Pairs ───────────────────────────────────────────────────────────────────────────────────────
--
-- `units.ordinal` is the unit's LEADER chunk index, which is both its reading order inside the
-- chapter and the join key the engine publishes for it (`first_chunk_idx` in the manifest, and the
-- `unit` of every `unit_done` event). One column serving both is not thrift: a separate dense
-- ordinal would be a second numbering to keep in step with a cut that moves.
comment on column units.ordinal is
    'The engine''s leader chunk index: reading order inside the chapter AND the join key of unit_resolutions.';

-- ── Notes ───────────────────────────────────────────────────────────────────────────────────────
--
-- The `notes` table goes, and this is the pack that would otherwise have given it a writer.
--
-- It cannot have one that is correct. A note is one flagged resolution of one pair, and the
-- durable record of that IS `unit_resolutions` (00015) — keyed by the ENGINE's ordinals, which is
-- what makes it writable while a run is going and before any chapter row exists. The `notes` table
-- is keyed by the platform's opaque chapter and unit ids, which exist only once a manifest has been
-- persisted, so a materializer writing both would either drop notes that arrive early or hold them
-- somewhere until the tree caught up. Two homes for one fact, and the second one lossy.
--
-- So notes are PROJECTED at read time from `unit_resolutions` joined onto the tree, exactly as
-- 00002 said the product phrase would be ("applied at read time from the contract's map"). The
-- identity a note needs on the wire is derived from the resolution's own key, so it survives a
-- re-read of the stream instead of being minted afresh by it.
drop table notes;

-- A resolution needs the one thing the read model orders everything else by: the book revision it
-- was materialized at. Without it a delta read of the notes has no watermark to compare against —
-- the row's own `at` is the ENGINE's clock, which is not the counter a client holds — and the
-- client would have to re-read every note of the book on every reconnect.
alter table unit_resolutions add column revision bigint not null default 0;
create index unit_resolutions_revision_idx on unit_resolutions (book_id, revision);
-- …which makes 00015's `(book_id)` a strict prefix duplicate. Dropped: the table takes one insert
-- per unit per wave, so a second index on the hottest write path buys nothing.
drop index unit_resolutions_book_idx;

-- ── Bank: the contract's vocabulary, not the engine's ───────────────────────────────────────────
--
-- 0.3.0 renamed both axes and the reason is the project's own invariant rather than taste: `ruby`
-- is Japanese furigana — pair-specific data in a shared layer — `mined` is the name of a pipeline
-- stage and `draft` the name of a wave, and none of the three may cross this seam (canon
-- §Boundaries). The mapping engine→contract is done where the sidecar is read; what is STORED is
-- what the client reads, so no path can project a word this deployment would have to translate
-- twice.
--
-- ⚠ The DATA is translated, not just the constraint, and the tables being empty on every deployment
-- today is NOT the reason to skip it: a migration that only holds while its tables are empty stops
-- being re-appliable the moment one row exists — including the re-apply after a rollback, which is
-- the ordinary shape of a failed release. Drop → translate → add, because the constraint standing at
-- each moment is the one being left behind.
alter table bank_terms drop constraint bank_terms_status_check;
alter table bank_terms drop constraint bank_terms_origin_check;
update bank_terms set status = case status when 'auto' then 'proposed'
                                          when 'draft' then 'in_progress'
                                          -- identity, not 'approved': an unexpected value must not
                                          -- become "the user signed this" by falling through.
                                          else status end,
                      origin = case origin when 'seed' then 'given'
                                           when 'ruby' then 'annotated'
                                           else 'found' end;
alter table bank_terms add constraint bank_terms_status_check
    check (status in ('proposed', 'in_progress', 'approved'));
alter table bank_terms add constraint bank_terms_origin_check
    check (origin in ('given', 'annotated', 'found'));

-- `since_chapter`/`until_chapter`: the engine's `0` sentinel becomes NULL, because `0` meant "no
-- boundary" in a column whose other values are chapter numbers starting at 1 — one sentinel with
-- two readings in two fields (canon §BankTerm).
alter table bank_terms
    alter column since_chapter drop not null,
    alter column since_chapter drop default,
    alter column until_chapter drop not null,
    alter column until_chapter drop default;
-- The sentinel is translated before the constraint that forbids it, for the same reason the
-- vocabularies above are: this migration has to be re-appliable over rows written under the old
-- shape, which is exactly what a rollback leaves behind.
update bank_terms set since_chapter = nullif(since_chapter, 0), until_chapter = nullif(until_chapter, 0);
alter table bank_terms
    add constraint bank_terms_window_check
        check ((since_chapter is null or since_chapter >= 1) and (until_chapter is null or until_chapter >= 1));

-- The uniqueness key holds with NULLs only under NULLS NOT DISTINCT: two rows of one surface
-- differing solely by an open boundary are the same term, and the default treatment would let both
-- in and hand the client two identical rows with different ids.
alter table bank_terms drop constraint bank_terms_book_id_src_sense_since_chapter_until_chapter_key;
alter table bank_terms add constraint bank_terms_key
    unique nulls not distinct (book_id, src, sense, since_chapter, until_chapter);

-- `promote` → `approve`: one act had two words, and the one that survives is the one that produces
-- `approved` (canon §BankDecision).
alter table bank_decisions drop constraint bank_decisions_action_check;
alter table bank_decisions drop constraint bank_decisions_promote_has_dst;
update bank_decisions set action = 'approve' where action = 'promote';
alter table bank_decisions add constraint bank_decisions_action_check
    check (action in ('approve', 'decline'));
alter table bank_decisions add constraint bank_decisions_approve_has_dst
    check (action <> 'approve' or dst <> '');

-- ── The frame history a stream resumes from ─────────────────────────────────────────────────────
--
-- The reporting database is not an event store and this does not make it one: what is kept here is
-- a short LIVE BUFFER of the frames already emitted, pruned as it grows, so that two viewers of one
-- book see the same frame under the same id and a client that reconnects can be told from its
-- `Last-Event-ID` whether it can be continued at all (canon §streamBookEvents). Nothing reads it
-- back into a decision; the state is read from the tables the frames describe.
--
-- Only HISTORY frames live here. `hello`, `end` and `resync_required` belong to a connection rather
-- than to the book: they carry the id of the last history frame and consume none of their own, so
-- two simultaneous viewers cannot spend each other's numbers.
-- ⚠ `unique nulls not distinct` below and this table are PostgreSQL 15+, which the ratified floor of
-- 16 (`docs/STACK_DECISIONS.md`) covers. Unasserted — a server older than 15 fails on THIS migration
-- rather than later.
create table book_events (
    book_id  text not null references books (id) on delete cascade,
    position bigint not null,
    event    text not null check (event in ('status', 'progress', 'chapter', 'note', 'bank')),
    -- The frame's `data` exactly as it goes on the wire, built inside the transaction that caused
    -- it: a frame is a snapshot of a moment, so re-deriving its payload at send time would answer a
    -- later state under an earlier id.
    data       jsonb not null,
    created_at timestamptz not null default now(),
    primary key (book_id, position)
);

-- +goose Down
drop table book_events;

comment on column units.ordinal is null;

create index unit_resolutions_book_idx on unit_resolutions (book_id);
drop index unit_resolutions_revision_idx;
alter table unit_resolutions drop column revision;

-- ⚠ The DATA is translated back BEFORE the old vocabularies are restored, and this is not
-- symmetry for its own sake: a rollback runs in one transaction, so the first violated CHECK
-- aborts the WHOLE migration. Without these updates a deployment could not roll back at all the
-- moment one bank row existed — which is the same release that first writes one. (The nullability
-- restore below always had its update; these three did not, and a reviewer reproduced the abort.)
-- The ORDER is drop → translate → add, and it is forced: the constraint standing at each moment is
-- the one being left behind, so an update that runs before the drop violates the vocabulary it is
-- migrating away from.
alter table bank_decisions drop constraint bank_decisions_approve_has_dst;
alter table bank_decisions drop constraint bank_decisions_action_check;
update bank_decisions set action = 'promote' where action = 'approve';
alter table bank_decisions add constraint bank_decisions_promote_has_dst
    check (action <> 'promote' or dst <> '');
alter table bank_decisions add constraint bank_decisions_action_check
    check (action in ('promote', 'decline'));

alter table bank_terms drop constraint bank_terms_key;
alter table bank_terms drop constraint bank_terms_window_check;
update bank_terms set since_chapter = 0 where since_chapter is null;
update bank_terms set until_chapter = 0 where until_chapter is null;
alter table bank_terms
    alter column since_chapter set default 0,
    alter column since_chapter set not null,
    alter column until_chapter set default 0,
    alter column until_chapter set not null;
alter table bank_terms add constraint bank_terms_book_id_src_sense_since_chapter_until_chapter_key
    unique (book_id, src, sense, since_chapter, until_chapter);
alter table bank_terms drop constraint bank_terms_origin_check;
alter table bank_terms drop constraint bank_terms_status_check;
update bank_terms set origin = case origin when 'given' then 'seed'
                                           when 'annotated' then 'ruby'
                                           else 'mined' end,
                      status = case status when 'proposed' then 'auto'
                                           when 'in_progress' then 'draft'
                                           else 'approved' end;
alter table bank_terms add constraint bank_terms_origin_check
    check (origin in ('seed', 'ruby', 'mined'));
alter table bank_terms add constraint bank_terms_status_check
    check (status in ('auto', 'draft', 'approved'));

create table notes (
    id         text primary key,
    book_id    text not null references books (id) on delete cascade,
    chapter_id text references chapters (id) on delete cascade,
    unit_id    text references units (id) on delete cascade,
    reason     text not null,
    created_at timestamptz not null default now(),
    revision   bigint not null default 0
);
create index notes_book_revision_idx on notes (book_id, revision);
create index notes_unit_idx on notes (unit_id);
-- The index 00004 added for the cascading foreign key, restored with the table it belongs to: the
-- down path of THAT migration drops it by name, so a rollback past this one would otherwise fail.
create index notes_chapter_idx on notes (chapter_id);

alter table books add column genre text not null default '';
alter table books add column note_count integer not null default 0;
alter table chapters
    add column heading text not null default '',
    add column note_count integer not null default 0;

alter table runs drop column bank_released, drop column chapters_before, drop column failure_reason;

alter table runs drop constraint runs_status_check;
alter table runs add constraint runs_status_check check (status in (
    'translating', 'awaiting_bank', 'finalizing', 'ready', 'stopped', 'failed', 'paused'));
alter table books drop constraint books_status_check;
alter table books add constraint books_status_check check (status in (
    'uploading', 'parsing', 'not_started', 'translating', 'awaiting_bank', 'finalizing',
    'ready', 'stopped', 'rejected', 'failed', 'paused'));

alter table books
    drop column bank_reset_revision,
    drop column structure_reset_revision,
    drop column event_position,
    drop column manifest_key,
    drop column structure_version;