PRAGMA encoding = 'UTF-8';
PRAGMA journal_mode = WAL;
PRAGMA synchronous = 1;
PRAGMA application_id = 618087585;
PRAGMA user_version = -477629973;
PRAGMA foreign_keys = ON;
BEGIN IMMEDIATE TRANSACTION;
CREATE TABLE IF NOT EXISTS "Names"(
    "ROWID" INTEGER PRIMARY KEY,
    "name"  TEXT NOT NULL UNIQUE
) STRICT;

CREATE UNIQUE INDEX IF NOT EXISTS i_names ON Names (name);

CREATE TABLE IF NOT EXISTS "Keys"(
    "ROWID"   INTEGER PRIMARY KEY,
    "pubkey"  BLOB    NOT NULL UNIQUE,
    "id"      INTEGER NOT NULL,
    "use"     INTEGER NOT NULL,
    FOREIGN KEY ("id") REFERENCES "Names"("ROWID")
) STRICT;

CREATE UNIQUE INDEX IF NOT EXISTS i_pubkey ON Keys (pubkey);
CREATE UNIQUE INDEX IF NOT EXISTS i_id ON Keys (use, id);

CREATE TABLE IF NOT EXISTS "Misc"(
    "ROWID"  INTEGER PRIMARY KEY,
    "m" ANY
) STRICT;

CREATE VIEW IF NOT EXISTS UserZookoIDs AS
SELECT
    "Names".name AS name,
    "Keys".pubkey AS pubkey
FROM "Names" INNER JOIN "Keys"
ON "Names"."ROWID"="Keys"."id" AND "Keys"."use"=1
ORDER BY LOWER("name"), "name" COLLATE BINARY;

CREATE TRIGGER IF NOT EXISTS InsertUserZookoID INSTEAD OF INSERT ON UserZookoIDs FOR EACH ROW BEGIN
    INSERT OR ROLLBACK INTO "Names" VALUES(
        NULL,
        NEW."name"
    );
    INSERT OR ROLLBACK INTO "Keys" VALUES(
        NULL,
        NEW."pubkey",
        last_insert_rowid(),
        1
    );
END;

CREATE TRIGGER IF NOT EXISTS DeleteUserZookoID INSTEAD OF DELETE ON UserZookoIDs FOR EACH ROW BEGIN
    DELETE FROM "Keys" WHERE "Keys"."pubkey" = OLD."pubkey";
    DELETE FROM "Names" WHERE "Names"."name" = OLD."name";
END;
COMMIT;

/*
 The "id" column of keys

 Currently broken, ill defined, and incoherent -- part of the planned future petname architecture.   Clean up this comment section when petnames work as intended.

    Meaning: "id" is a foreign key to Names.ROWID, declared with FOREIGN KEY ("id") REFERENCES "Names"("ROWID") in the Keys table (WALLET_SCHEMA_SQL, db_accessors.h) and
    enforced by sqlite itself on every connection. It answers "which nickname row is this public key bound to?" Despite the generic name it is not the key's own
    identity — that is the "ROWID" column; id is really name_id (the planned v0_2 schema in docs/design/data_structure.md renames it exactly that).

 Effects:

 1. Join column of the virtual table. UserZookoIDs is defined as ... ON "Names"."ROWID"="Keys"."id" AND "Keys"."use"=1 (frame.cpp:758). It is the only link between the two
    base tables.
 2. Populated by the trigger via last_insert_rowid(). InsertUserZookoID first does INSERT OR ROLLBACK INTO "Names", then INSERT ... INTO "Keys" VALUES(NULL, NEW."pubkey",
    last_insert_rowid(), 1) (frame.cpp:763-774) — so id receives the ROWID of the Names row created one statement earlier on the same connection. OR ROLLBACK plus the
    enclosing transaction is what keeps a failed name insert from leaving an orphan key.
 3. Uniqueness scoping. CREATE UNIQUE INDEX i_id ON Keys (use, id) — at most one key per (relationship-kind, name) pair. Since pubkey is itself UNIQUE, the result today is
    a strict 1:1 between a nickname and a key.
 4. NOT NULL — in this schema every key row must reference a name; a nameless key is unrepresentable.
 5. Integrity verification path. The unit test reads it back (auto id = read_keys.column<int>(2), unit_test.cpp:1556), fetches SELECT * FROM "Names" WHERE ROWID = ?1
    (sql_read_name, db_accessors.h:249), then checks MasterExtendedSecretKey(name).timesBase() == pubkey.
 6. Real FK enforcement. The Keys table declares FOREIGN KEY ("id") REFERENCES "Names"("ROWID"), and PRAGMA foreign_keys = ON is issued in the ISqlite3Impl constructor
    (ISqlit3Impl.cpp) -- the pragma is per connection and not stored in the file, so it must be set on every open, which the single constructor of the single implementation
    class guarantees. Wallet files created before the constraint existed carry no FOREIGN KEY clause in their stored schema; migrate_wallet_keys_foreign_key
    (db_accessors.cpp) rebuilds their Keys table in place on open, and is called right after check_wallet_file_pragmas everywhere an existing wallet is opened
    (dbconnect, Frame::PromptForWalletPassphraseIfNeeded, unit test). An orphan key row, or the deletion of a Names row a key still references, is now rejected by sqlite
    (SQLITE_CONSTRAINT_FOREIGNKEY) instead of merely being prevented by the triggers' transactional discipline -- the triggers still do the paired insert and delete, but
    they are no longer the only thing standing between the schema and an orphan.

 The "use" column  of keys.

Currently broken, ill defined, and incoherent -- part of the planned future petname architecture.  Clean up this comment section when petnames work as intended.

    Meaning: the enumerator of the relationship between the nickname and the key — specifically, what the wallet knows about the secret behind pubkey and how it got it.
    Exactly one value is ever written: use = 1 = "the secret key is derived from this name using the master extended secret key" (child = master.privatekey × H(chain ‖
    normalizeWhitespace(name)), secrets.h:147-152). The unit test's error string for any other value, sz_unknown_secret_key_algorithm, shows the column was conceived from the
    start as tagging a derivation method, not a boolean. The planned value use = 2 ("we know only the public key; the name is under someone else's control") exists only in
    docs/design/data_structure.md.

 Effects:

 1. Hardcoded on write. The insert trigger writes literal 1 (frame.cpp:771) — nothing in the code can produce any other value.
 2. Filters the view. "Keys"."use"=1 in UserZookoIDs makes the view mean "identities I own"; a future use=2 population would be invisible to it, which is why the planned
    schema adds the symmetric UserPetZookoIDs view filtered on use=2.
 3. Scopes uniqueness. As part of i_id (use, id), it makes "one name, one key" hold per relationship kind — structurally permitting the same Names row to later be bound to
    keys under different use values without an index change.
 4. Gates validation. unit_test.cpp:1557-1558 throws on use != 1, and display_wallet::refresh_from_database's re-derivation check
    (MasterExtendedSecretKey(name).timesBase() != pubkey → throw) is only semantically valid for use=1 rows — externally controlled keys have no secret to re-derive from,
    so when use=2 arrives, both checks must become use-aware.

 CREATE TABLE HiddenNicks(
   ROWID INTEGER PRIMARY KEY REFERENCES keys(ROWID ),
   nick  INTEGER NOT NULL REFERENCES Names(ROWID),
  );

CREATE VIEW ZookoIDs AS
SELECT
    "Names".name AS name,
    "Keys".pubkey AS pubkey,
    "Keys.use AS use
FROM "Names" INNER JOIN "Keys"
ON "Names"."ROWID"="Keys"."id"
ORDER BY LOWER("name"), "name" COLLATE BINARY;
COMMIT;

CREATE TABLE "Titles"(
    "ROWID"     INTEGER PRIMARY KEY,
    "title"     BLOB    NOT NULL UNIQUE,
) STRICT;

CREATE UNIQUE INDEX i_titles ON Titles (title);

CREATE TABLE "Messages"(
    "ROWID"     INTEGER PRIMARY KEY,
    "title"     INTEGER NOT NULL REFERENCES Titles(ROWID),
    "time"      INTEGER,
    "body"      BLOB    NOT NULL,
    "signer"    INTEGER NOT NULL REFERENCES Keys(ROWID),
    "pow"       INTEGER,
) STRICT;

CREATE TABLE "Ids"(
    "ROWID"     INTEGER PRIMARY KEY,
    "id"  BLOB  NOT NULL UNIQUE,
    "Preimage"  INTEGER NOT NULL,
    "use"       INTEGER NOT NULL
) STRICT;