<?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">

  <!--
    Notifications v1.2.0 — schema foundation.

    Цель: persistent audit trail доставки + per-user channel preferences.
    Notification dispatcher leverages existing ordinis-outbox infra (OutboxRecorder
    + OutboxPoller + KafkaTopicResolver). Этот migration добавляет ТОЛЬКО consumer-side
    state. События публикуются в outbox_events (existing table).

    Decisions made (per design doc):
    - D2 idempotency: UNIQUE (event_id, channel, recipient) — primary defence от
      consumer crash mid-fan-out duplication. INSERT ON CONFLICT DO NOTHING RETURNING.
    - D11 PendingSweepJob: status='PENDING' rows старше 5 min удаляются → освобождают
      idempotency lock на crashed dispatch attempts.
    - D12 RetentionJob: SENT/RATE_LIMITED/SKIPPED_PREF/SKIPPED_TOO_OLD/UNSUBSCRIBED
      старше 90 дней удаляются ежедневно. FAILED rows preserved bessrochno.
    - D15 status enum UPPERCASE (Hibernate @Enumerated(EnumType.STRING) lesson из
      v1.1.0 P3 — migration 0020).

    Indexes:
    - Partial idx_notif_log_pending_sweep — для @Scheduled sweep job (только PENDING).
    - Partial idx_notif_log_retention — для daily cleanup (terminal status'ы).
    - Composite idx_notif_log_recipient_time — для UI "last sent" lookup.
    - Single column idx_notif_log_draft / event для cross-references.

    Source: ~/.gstack/projects/claude/zimin-main-design-notifications-v1.2.0-20260510-130000.md
  -->

  <changeSet id="0021-1-notification-log" author="ordinis">
    <comment>notification_log — per-recipient delivery audit trail с UNIQUE idempotency guard</comment>
    <createTable tableName="notification_log">
      <column name="id" type="BIGINT" autoIncrement="true">
        <constraints primaryKey="true" nullable="false"/>
      </column>
      <!-- domain event UUID из outbox_events — связывает notification с triggering event -->
      <column name="event_id" type="UUID">
        <constraints nullable="false"/>
      </column>
      <column name="event_type" type="VARCHAR(64)">
        <constraints nullable="false"/>
      </column>
      <!-- nullable: некоторые events без draft контекста (future expansion) -->
      <column name="draft_id" type="UUID"/>
      <column name="channel" type="VARCHAR(16)">
        <constraints nullable="false"/>
      </column>
      <!-- email / express chat_id / telegram chat_id -->
      <column name="recipient" type="VARCHAR(256)">
        <constraints nullable="false"/>
      </column>
      <column name="status" type="VARCHAR(20)">
        <constraints nullable="false"/>
      </column>
      <column name="sent_at" type="TIMESTAMPTZ" defaultValueComputed="NOW()">
        <constraints nullable="false"/>
      </column>
      <column name="error_msg" type="TEXT"/>
    </createTable>

    <!-- D15 status enum UPPERCASE (Hibernate @Enumerated lesson) -->
    <sql>
      ALTER TABLE notification_log
      ADD CONSTRAINT notification_log_status_check
      CHECK (status IN (
        'PENDING', 'SENT', 'FAILED',
        'RATE_LIMITED', 'SKIPPED_PREF', 'SKIPPED_TOO_OLD',
        'UNSUBSCRIBED'
      ));
    </sql>

    <sql>
      ALTER TABLE notification_log
      ADD CONSTRAINT notification_log_channel_check
      CHECK (channel IN ('email', 'express', 'telegram'));
    </sql>

    <!-- D2 idempotency guard — primary defence от consumer crash mid-fan-out duplication.
         (event_id, channel, recipient) UNIQUE: тот же event для того же канала и
         recipient'а никогда не send'ится дважды. INSERT ON CONFLICT DO NOTHING RETURNING
         в IdempotencyGuard.shouldSend(). -->
    <sql>
      ALTER TABLE notification_log
      ADD CONSTRAINT notification_log_idempotency
      UNIQUE (event_id, channel, recipient);
    </sql>

    <!-- Partial index для PendingSweepJob: только PENDING rows нужно scan'ить
         каждые 5 минут. Без partial index full scan на growing table. -->
    <sql>
      CREATE INDEX idx_notif_log_pending_sweep
      ON notification_log (sent_at)
      WHERE status = 'PENDING';
    </sql>

    <!-- Partial index для RetentionJob daily cleanup: только terminal "deletable"
         status'ы. FAILED rows preserved → не в индексе. -->
    <sql>
      CREATE INDEX idx_notif_log_retention
      ON notification_log (sent_at)
      WHERE status IN ('SENT', 'RATE_LIMITED', 'SKIPPED_PREF', 'SKIPPED_TOO_OLD', 'UNSUBSCRIBED');
    </sql>

    <createIndex indexName="idx_notif_log_draft" tableName="notification_log">
      <column name="draft_id"/>
    </createIndex>

    <!-- UI "last sent {N} min ago" lookup — recipient + sent_at DESC. -->
    <createIndex indexName="idx_notif_log_recipient_time" tableName="notification_log">
      <column name="recipient"/>
      <column name="sent_at" descending="true"/>
    </createIndex>

    <createIndex indexName="idx_notif_log_event" tableName="notification_log">
      <column name="event_id"/>
    </createIndex>
  </changeSet>

  <changeSet id="0021-2-user-notification-prefs" author="ordinis">
    <comment>user_notification_prefs — per-user channel opt-in/out</comment>
    <createTable tableName="user_notification_prefs">
      <!-- Keycloak subject (sub claim) — same shape as record_drafts.maker_id (VARCHAR 128) -->
      <column name="user_id" type="VARCHAR(128)">
        <constraints nullable="false"/>
      </column>
      <column name="channel" type="VARCHAR(16)">
        <constraints nullable="false"/>
      </column>
      <!-- D3: email default ON для existing users применяется bootstrap loadData
           (отдельный changeSet опционально per env). Для новых юзеров — explicit
           opt-in через UI. -->
      <column name="enabled" type="BOOLEAN" defaultValueBoolean="false">
        <constraints nullable="false"/>
      </column>
    </createTable>

    <sql>
      ALTER TABLE user_notification_prefs
      ADD CONSTRAINT user_notification_prefs_pkey
      PRIMARY KEY (user_id, channel);
    </sql>

    <sql>
      ALTER TABLE user_notification_prefs
      ADD CONSTRAINT user_notification_prefs_channel_check
      CHECK (channel IN ('email', 'express', 'telegram'));
    </sql>
  </changeSet>

</databaseChangeLog>
