using InvercargillSql.Migrations;
    using Sqlite;

    namespace ClankerSaddle.Data.Migrations {

        /**
         * Moves Web Push subscriptions from our hand-rolled
         * `push_subscriptions` table (plan 11 task E) into Spry's
         * `spry_push_subscriptions` store (goal 47: the app adopted
         * `Spry.Push` for storage, crypto and delivery — our own RFC
         * 8291/8292 implementation never worked and is gone).
         *
         * Column mapping is one-to-one (`created_at` → `created`); the
         * copy is INSERT OR IGNORE because the Spry table enforces
         * UNIQUE(endpoint) — a database that already carried both
         * shapes keeps its newer rows. The copy is ONE-WAY: `down()`
         * recreates the old table per M0001's shape, empty, for
         * operators who must roll back (the subscriptions themselves
         * are re-created by browsers on their next visit — pushing to
         * stale endpoints never worked anyway).
         *
         * ORDERING: the copy may only run after Spry's own
         * M0003_PushSubscriptions ("spry-authentication" namespace)
         * has created `spry_push_subscriptions`. The runner's Kahn
         * queue is namespace-first, so a bare clanker-saddle migration
         * lands BEFORE the spry namespace on a fresh database
         * (empirically: clanker-saddle:1, spry:1, clanker-saddle:12,
         * spry:2..5). The `dependencies` declaration below pins this
         * migration after the WHOLE spry-authentication namespace —
         * verified empirically on a scratch database: with it, the
         * order becomes clanker-saddle:1, spry:1..5,
         * clanker-saddle:12. On an already-migrated database both
         * namespaces are applied, the copy finds both tables and the
         * dependency costs nothing.
         *
         * IDEMPOTENCE: same contract as M0002–M0011 — up() runs before
         * the apply transaction opens (the builder only queues), so it
         * introspects the schema through a separate read-only handle
         * and copies only when the old table still exists. The DROP is
         * likewise conditional (guarded in the SQL itself, since the
         * builder has no drop-if-exists).
         */
        public class M0012_ProviderPushSubscriptions : Migration {

            public override string name { get { return "Move push subscriptions to Spry"; } }
            public override uint64 serial { get { return 12; } }
            public override string migration_namespace { get { return "clanker-saddle"; } }
            public override owned string[] dependencies {
                owned get { return { "spry-authentication" }; }
            }

            // The runner never hands up() a connection, so the schema is
            // read through a read-only handle on the SPRY_DB_PATH main.vala
            // connects to.
            private string db_path() {
                var path = Environment.get_variable("SPRY_DB_PATH") ?? "";
                if (path.strip().length == 0) {
                    return "./clanker-saddle.sqlite";
                }
                return path;
            }

            private bool table_exists(string table) {
                Database db;
                int rc = Database.open_v2(db_path(), out db, Sqlite.OPEN_READONLY);
                if (rc != Sqlite.OK) {
                    return false;
                }
                Statement statement;
                rc = db.prepare_v2(
                    @"select 1 from sqlite_master where type = 'table' and name = '$table'",
                    -1, out statement);
                if (rc != Sqlite.OK) {
                    return false;
                }
                return statement.step() == Sqlite.ROW;
            }

            public override void up(MigrationBuilder b) {
                if (!table_exists("push_subscriptions")) {
                    // Fresh database (or already moved): nothing to copy.
                    return;
                }
                b.execute_sql(@"
                    insert or ignore into spry_push_subscriptions
                        (user_id, endpoint, p256dh, auth, created)
                    select user_id, endpoint, p256dh, auth, created_at
                        from push_subscriptions");
                b.execute_sql("drop table if exists push_subscriptions");
            }

            public override void down(MigrationBuilder b) {
                b.create_table("push_subscriptions", (t) => {
                    t.column<int64?>("id")
                        .auto_increment()
                        .primary_key();
                    t.column<int64?>("user_id")
                        .not_null()
                        .indexed();
                    t.column<string>("endpoint")
                        .not_null()
                        .unique();
                    t.column<string>("p256dh")
                        .not_null();
                    t.column<string>("auth")
                        .not_null();
                    t.column<DateTime?>("created_at");
                });
            }
        }
    }