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

  <!--
    Webhook deliveries — tracking каждой попытки доставки event'а
    подписчику. Один (subscription_id, outbox_event_id) row на match,
    с retry counter и DLQ flag.

    Lifecycle:
    1. Dispatcher видит unprocessed event matching subscription → INSERT row
       (status='pending', attempt_count=0).
    2. POST URL. Success (2xx) → status='delivered', delivered_at=now().
    3. Failure → status='retrying', attempt_count++, last_error=...,
       next_attempt_at = now() + backoff(attempt_count).
    4. После attempt_count >= 1000 → status='dlq', исключается из poller.

    Backoff: 1m, 5m, 15m, 1h, 6h, 24h (exponential), capped 24h.
  -->

  <changeSet id="0014-1-webhook-deliveries" author="ordinis">
    <createTable tableName="webhook_deliveries">
      <column name="id" type="BIGINT" autoIncrement="true">
        <constraints primaryKey="true" nullable="false"/>
      </column>
      <column name="subscription_id" type="UUID">
        <constraints nullable="false"
                     foreignKeyName="fk_webhook_deliveries_subscription"
                     references="webhook_subscriptions(id)"
                     deleteCascade="true"/>
      </column>
      <column name="outbox_event_id" type="BIGINT">
        <constraints nullable="false"
                     foreignKeyName="fk_webhook_deliveries_event"
                     references="outbox_events(id)"
                     deleteCascade="true"/>
      </column>
      <!-- pending / retrying / delivered / dlq -->
      <column name="status" type="VARCHAR(16)" defaultValue="pending">
        <constraints nullable="false"/>
      </column>
      <column name="attempt_count" type="INTEGER" defaultValueNumeric="0">
        <constraints nullable="false"/>
      </column>
      <column name="created_at" type="TIMESTAMPTZ" defaultValueComputed="now()">
        <constraints nullable="false"/>
      </column>
      <column name="next_attempt_at" type="TIMESTAMPTZ" defaultValueComputed="now()">
        <constraints nullable="false"/>
      </column>
      <column name="last_attempt_at" type="TIMESTAMPTZ"/>
      <column name="delivered_at" type="TIMESTAMPTZ"/>
      <column name="last_error" type="TEXT"/>
      <column name="last_status_code" type="INTEGER"/>
      <column name="dlq_at" type="TIMESTAMPTZ"/>
    </createTable>
  </changeSet>

  <changeSet id="0014-2-webhook-deliveries-status-check" author="ordinis">
    <sql>
      ALTER TABLE webhook_deliveries
      ADD CONSTRAINT chk_webhook_delivery_status
      CHECK (status IN ('pending','retrying','delivered','dlq'));
    </sql>
    <rollback>
      <sql>
        ALTER TABLE webhook_deliveries DROP CONSTRAINT IF EXISTS chk_webhook_delivery_status;
      </sql>
    </rollback>
  </changeSet>

  <changeSet id="0014-3-webhook-deliveries-poller-index" author="ordinis">
    <comment>
      Hot poller path: WHERE status IN ('pending','retrying') AND
      next_attempt_at &lt;= now(). Partial index покрывает обе колонки.
    </comment>
    <sql>
      CREATE INDEX idx_webhook_deliveries_due
      ON webhook_deliveries (next_attempt_at)
      WHERE status IN ('pending', 'retrying');
    </sql>
    <rollback>
      <sql>DROP INDEX IF EXISTS idx_webhook_deliveries_due;</sql>
    </rollback>
  </changeSet>

  <changeSet id="0014-4-webhook-deliveries-dlq-index" author="ordinis">
    <comment>Для UI listing DLQ + reset операций.</comment>
    <sql>
      CREATE INDEX idx_webhook_deliveries_dlq
      ON webhook_deliveries (dlq_at DESC)
      WHERE status = 'dlq';
    </sql>
    <rollback>
      <sql>DROP INDEX IF EXISTS idx_webhook_deliveries_dlq;</sql>
    </rollback>
  </changeSet>

  <changeSet id="0014-5-webhook-deliveries-subscription-event-uniq" author="ordinis">
    <comment>
      Idempotency: один subscription × event = один row. Если dispatcher
      ловит retry где event/subscription уже создавал delivery — INSERT
      ON CONFLICT DO NOTHING.
    </comment>
    <sql>
      CREATE UNIQUE INDEX uniq_webhook_deliveries_subscription_event
      ON webhook_deliveries (subscription_id, outbox_event_id);
    </sql>
    <rollback>
      <sql>DROP INDEX IF EXISTS uniq_webhook_deliveries_subscription_event;</sql>
    </rollback>
  </changeSet>

</databaseChangeLog>
