<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.27.xsd">

  <!--
    Dictionary schema workflow API — backend pending endpoint #3.

    Параллельная архитектура к record_drafts (0019), но для schema-level
    changes: дизайнер edit'ит JSON Schema → создаёт draft → submit'ит на
    review → reviewer approve/reject/request_changes → publish bump'ит
    semver + replace'ит live schema.

    State machine:
      draft → review_pending → {approved, changes_requested, rejected}
                  ↓
              withdrawn  ←  draft (maker discard)
                  ↓
              withdrawn  ←  review_pending (maker recall)
              published  ←  approved (publish bump'ит version)
              review_pending ← changes_requested (maker re-submit)

    Decisions:
      - Pool model для reviewer'ов (D3): любой user с approve role
        может decide; reviewer_assignments JSONB (NULL = open pool).
      - Self-approve forbidden: maker_id != decision_user_id constraint.
      - Только один non-terminal draft per dict (EXCLUDE constraint
        WHERE status NOT IN published/rejected/withdrawn).
      - Optimistic lock на publish: expected_head_version проверяется
        против dictionary_definitions.schema_version в service layer.

    Source: docs/integration/api/pending-endpoints.md §3 (frontend spec).
  -->

  <changeSet id="0022-1-schema-drafts" author="ordinis">
    <comment>dictionary_schema_drafts — schema-level approval workflow</comment>
    <createTable tableName="dictionary_schema_drafts">
      <column name="id" type="UUID" defaultValueComputed="gen_random_uuid()">
        <constraints primaryKey="true" nullable="false"/>
      </column>
      <column name="dictionary_id" type="UUID">
        <constraints nullable="false" foreignKeyName="fk_schema_draft_dictionary"
                     references="dictionary_definitions(id)"/>
      </column>

      <!-- Proposed schema content + base version pointer. -->
      <column name="proposed_schema" type="JSONB">
        <constraints nullable="false"/>
      </column>
      <column name="branched_from_version" type="VARCHAR(32)">
        <constraints nullable="false"/>
      </column>

      <!-- Maker rationale; required чтобы reviewer понимал intent. -->
      <column name="reason" type="TEXT"/>

      <!-- Reviewer pool: NULL = anyone with approve role, JSONB array
           если конкретный set. См. SubmitForReviewRequest.reviewers. -->
      <column name="reviewer_assignments" type="JSONB"/>
      <column name="review_note" type="TEXT"/>

      <!-- Workflow state — enum check ниже. -->
      <column name="status" type="VARCHAR(24)">
        <constraints nullable="false"/>
      </column>

      <column name="maker_id" type="VARCHAR(128)">
        <constraints nullable="false"/>
      </column>
      <column name="created_at" type="TIMESTAMPTZ" defaultValueComputed="NOW()">
        <constraints nullable="false"/>
      </column>
      <column name="submitted_at" type="TIMESTAMPTZ"/>

      <!-- Decision details (single decision — reviewer's verdict). -->
      <column name="decision_user_id" type="VARCHAR(128)"/>
      <column name="decision_at" type="TIMESTAMPTZ"/>
      <column name="decision_comment" type="TEXT"/>

      <!-- Publish metadata — set когда status=published. -->
      <column name="published_at" type="TIMESTAMPTZ"/>
      <column name="published_version" type="VARCHAR(32)"/>
      <column name="publish_note" type="TEXT"/>

      <column name="updated_at" type="TIMESTAMPTZ" defaultValueComputed="NOW()">
        <constraints nullable="false"/>
      </column>
      <column name="version" type="BIGINT" defaultValueNumeric="0">
        <constraints nullable="false"/>
      </column>
    </createTable>

    <sql>
      ALTER TABLE dictionary_schema_drafts
      ADD CONSTRAINT schema_drafts_status_check
      CHECK (status IN ('draft', 'review_pending', 'changes_requested',
                        'approved', 'rejected', 'published', 'withdrawn'));
    </sql>

    <!-- Self-approve forbidden: D3 maker-checker pattern. -->
    <sql>
      ALTER TABLE dictionary_schema_drafts
      ADD CONSTRAINT schema_drafts_no_self_approve
      CHECK (decision_user_id IS NULL OR maker_id != decision_user_id);
    </sql>

    <!-- Один active draft per dict (concurrent editing на schema level
         не имеет смысла — schema changes coarse-grained и serialized).
         Terminal статусы (published/rejected/withdrawn) исключены —
         можно создать новый draft после того как предыдущий closed. -->
    <sql>
      ALTER TABLE dictionary_schema_drafts
      ADD CONSTRAINT schema_drafts_one_active_per_dict
      EXCLUDE USING btree (
        dictionary_id WITH =
      ) WHERE (status IN ('draft', 'review_pending', 'changes_requested', 'approved'));
    </sql>

    <!-- Listing by dict — самое частое чтение, см. SchemaDraftService.listByDictionary. -->
    <createIndex indexName="idx_schema_drafts_by_dict" tableName="dictionary_schema_drafts">
      <column name="dictionary_id"/>
      <column name="created_at" descending="true"/>
    </createIndex>

    <!-- Maker's own drafts (self-tracking). -->
    <createIndex indexName="idx_schema_drafts_by_maker" tableName="dictionary_schema_drafts">
      <column name="maker_id"/>
      <column name="created_at" descending="true"/>
    </createIndex>

    <!-- Global review queue (pool reviewer'ы видят review_pending across all dicts). -->
    <createIndex indexName="idx_schema_drafts_review_queue" tableName="dictionary_schema_drafts">
      <column name="status"/>
      <column name="submitted_at"/>
    </createIndex>
  </changeSet>

</databaseChangeLog>
