package uk.gov.migration;

import org.flywaydb.core.api.migration.MigrationChecksumProvider;
import org.flywaydb.core.api.migration.jdbc.BaseJdbcMigration;

import java.sql.Connection;
import java.sql.Statement;

public class V__18_ResetSequences extends BaseJdbcMigration implements MigrationChecksumProvider {
    @Override
    public void migrate(Connection connection) throws Exception {
        Statement stmt = connection.createStatement();

        try {
            stmt.execute("SELECT setval('item_item_order_seq', coalesce((select max(blob_order)+1 from item), 1), false)");
        } finally {
            stmt.close();
        }
    }


    @Override
    public Integer getChecksum() {
        return 1;
    }
}

Reset the blob order sequence to be in sync with the data
Reset the blob order sequence to be in sync with the data

In a previous migration we added this field, and backpopulated
the data so that existing blobs had an order value.

I assumed when we updated the data in this way, the sequence
would automatically advance its value, but this does not seem
to be true in all cases.

On production, we ended up with a register where the latest
`blob_order` was 167, but the sequence thought the next value
was 164:

```
openregister_java=# select max(blob_order) from item;
 max
-----
 167
(1 row)

openregister_java=# select nextval('item_item_order_seq');
 nextval
---------
     164
(1 row)
```

This caused inserts of new blobs to silently fail, because the actual
insert query ends with "on conflict do nothing". This meant that
load-rsf returned a 200 response even though the blob data wasn't added
to the DB.

The postgres wiki says:

> A common problem when copying or recreating a database is that
> database sequences are not updated just by inserting records in the
> table that sequence is used in.
https://wiki.postgresql.org/wiki/Fixing_Sequences

So it seems that we need to tell postgres what the next value of
the sequence should be after populating the historical data.

This can be done with the setval function, documented here:
https://www.postgresql.org/docs/10/functions-sequence.html
