Skip to content

Privacy-first password manager with built-in email aliasing. Fully encrypted and self-hostable.

74.8 KB
Raw Blame
-- <auto-generated />
-- Generated from core/vault/src/sql/SqlConstants.ts by core/vault/scripts/generate-vault-sql.cjs. Do not edit manually.
CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
    "MigrationId" TEXT NOT NULL CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY,
    "ProductVersion" TEXT NOT NULL
);

BEGIN TRANSACTION;
CREATE TABLE "Aliases" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_Aliases" PRIMARY KEY,
    "Gender" VARCHAR NULL,
    "FirstName" VARCHAR NULL,
    "LastName" VARCHAR NULL,
    "NickName" VARCHAR NULL,
    "BirthDate" TEXT NOT NULL,
    "AddressStreet" VARCHAR NULL,
    "AddressCity" VARCHAR NULL,
    "AddressState" VARCHAR NULL,
    "AddressZipCode" VARCHAR NULL,
    "AddressCountry" VARCHAR NULL,
    "Hobbies" TEXT NULL,
    "EmailPrefix" TEXT NULL,
    "PhoneMobile" TEXT NULL,
    "BankAccountIBAN" TEXT NULL,
    "CreatedAt" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL
);

CREATE TABLE "Services" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_Services" PRIMARY KEY,
    "Name" TEXT NULL,
    "Url" TEXT NULL,
    "Logo" BLOB NULL,
    "CreatedAt" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL
);

CREATE TABLE "Credentials" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_Credentials" PRIMARY KEY,
    "AliasId" TEXT NOT NULL,
    "Notes" TEXT NULL,
    "Username" TEXT NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "ServiceId" TEXT NOT NULL,
    CONSTRAINT "FK_Credentials_Aliases_AliasId" FOREIGN KEY ("AliasId") REFERENCES "Aliases" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_Credentials_Services_ServiceId" FOREIGN KEY ("ServiceId") REFERENCES "Services" ("Id") ON DELETE CASCADE
);

CREATE TABLE "Attachment" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_Attachment" PRIMARY KEY,
    "Filename" TEXT NOT NULL,
    "Blob" BLOB NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "CredentialId" TEXT NOT NULL,
    CONSTRAINT "FK_Attachment_Credentials_CredentialId" FOREIGN KEY ("CredentialId") REFERENCES "Credentials" ("Id") ON DELETE CASCADE
);

CREATE TABLE "Passwords" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_Passwords" PRIMARY KEY,
    "Value" TEXT NULL,
    "CreatedAt" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "CredentialId" TEXT NOT NULL,
    CONSTRAINT "FK_Passwords_Credentials_CredentialId" FOREIGN KEY ("CredentialId") REFERENCES "Credentials" ("Id") ON DELETE CASCADE
);

CREATE INDEX "IX_Attachment_CredentialId" ON "Attachment" ("CredentialId");

CREATE INDEX "IX_Credentials_AliasId" ON "Credentials" ("AliasId");

CREATE INDEX "IX_Credentials_ServiceId" ON "Credentials" ("ServiceId");

CREATE INDEX "IX_Passwords_CredentialId" ON "Passwords" ("CredentialId");

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20240708094944_1.0.0-InitialMigration', '10.0.12');

COMMIT;

BEGIN TRANSACTION;
INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20240708224522_1.0.1-EmptyTestMigration', '10.0.12');

COMMIT;

BEGIN TRANSACTION;
ALTER TABLE "Aliases" RENAME COLUMN "EmailPrefix" TO "Email";

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20240711204207_1.0.2-ChangeEmailColumn', '10.0.12');

COMMIT;

BEGIN TRANSACTION;
CREATE TABLE "EncryptionKeys" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_EncryptionKeys" PRIMARY KEY,
    "PublicKey" TEXT NOT NULL,
    "PrivateKey" TEXT NOT NULL,
    "IsPrimary" INTEGER NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL
);

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20240729105618_1.1.0-AddPkiTables', '10.0.12');

COMMIT;

BEGIN TRANSACTION;
CREATE TABLE "Settings" (
    "Key" TEXT NOT NULL CONSTRAINT "PK_Settings" PRIMARY KEY,
    "Value" TEXT NULL,
    "CreatedAt" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL
);

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20240805073413_1.2.0-AddSettingsTable', '10.0.12');

COMMIT;

BEGIN TRANSACTION;
CREATE TABLE "ef_temp_Aliases" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_Aliases" PRIMARY KEY,
    "BirthDate" TEXT NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "Email" TEXT NULL,
    "FirstName" VARCHAR NULL,
    "Gender" VARCHAR NULL,
    "LastName" VARCHAR NULL,
    "NickName" VARCHAR NULL,
    "UpdatedAt" TEXT NOT NULL
);

INSERT INTO "ef_temp_Aliases" ("Id", "BirthDate", "CreatedAt", "Email", "FirstName", "Gender", "LastName", "NickName", "UpdatedAt")
SELECT "Id", "BirthDate", "CreatedAt", "Email", "FirstName", "Gender", "LastName", "NickName", "UpdatedAt"
FROM "Aliases";

COMMIT;

PRAGMA foreign_keys = 0;

BEGIN TRANSACTION;
DROP TABLE "Aliases";

ALTER TABLE "ef_temp_Aliases" RENAME TO "Aliases";

COMMIT;

PRAGMA foreign_keys = 1;

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20240805122422_1.3.0-UpdateIdentityStructure', '10.0.12');

BEGIN TRANSACTION;
CREATE TABLE "ef_temp_Credentials" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_Credentials" PRIMARY KEY,
    "AliasId" TEXT NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "Notes" TEXT NULL,
    "ServiceId" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "Username" TEXT NULL,
    CONSTRAINT "FK_Credentials_Aliases_AliasId" FOREIGN KEY ("AliasId") REFERENCES "Aliases" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_Credentials_Services_ServiceId" FOREIGN KEY ("ServiceId") REFERENCES "Services" ("Id") ON DELETE CASCADE
);

INSERT INTO "ef_temp_Credentials" ("Id", "AliasId", "CreatedAt", "Notes", "ServiceId", "UpdatedAt", "Username")
SELECT "Id", "AliasId", "CreatedAt", "Notes", "ServiceId", "UpdatedAt", "Username"
FROM "Credentials";

COMMIT;

PRAGMA foreign_keys = 0;

BEGIN TRANSACTION;
DROP TABLE "Credentials";

ALTER TABLE "ef_temp_Credentials" RENAME TO "Credentials";

COMMIT;

PRAGMA foreign_keys = 1;

BEGIN TRANSACTION;
CREATE INDEX "IX_Credentials_AliasId" ON "Credentials" ("AliasId");

CREATE INDEX "IX_Credentials_ServiceId" ON "Credentials" ("ServiceId");

COMMIT;

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20240812141727_1.3.1-MakeUsernameOptional', '10.0.12');

BEGIN TRANSACTION;
ALTER TABLE "Settings" ADD "IsDeleted" INTEGER NOT NULL DEFAULT 0;

ALTER TABLE "Services" ADD "IsDeleted" INTEGER NOT NULL DEFAULT 0;

ALTER TABLE "Passwords" ADD "IsDeleted" INTEGER NOT NULL DEFAULT 0;

ALTER TABLE "EncryptionKeys" ADD "IsDeleted" INTEGER NOT NULL DEFAULT 0;

ALTER TABLE "Credentials" ADD "IsDeleted" INTEGER NOT NULL DEFAULT 0;

ALTER TABLE "Attachment" ADD "IsDeleted" INTEGER NOT NULL DEFAULT 0;

ALTER TABLE "Aliases" ADD "IsDeleted" INTEGER NOT NULL DEFAULT 0;

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20240916105320_1.4.0-AddSyncSupport', '10.0.12');

COMMIT;

BEGIN TRANSACTION;
ALTER TABLE "Attachment" RENAME TO "Attachments";

CREATE TABLE "ef_temp_Attachments" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_Attachments" PRIMARY KEY,
    "Blob" BLOB NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "CredentialId" TEXT NOT NULL,
    "Filename" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    CONSTRAINT "FK_Attachments_Credentials_CredentialId" FOREIGN KEY ("CredentialId") REFERENCES "Credentials" ("Id") ON DELETE CASCADE
);

INSERT INTO "ef_temp_Attachments" ("Id", "Blob", "CreatedAt", "CredentialId", "Filename", "IsDeleted", "UpdatedAt")
SELECT "Id", "Blob", "CreatedAt", "CredentialId", "Filename", "IsDeleted", "UpdatedAt"
FROM "Attachments";

COMMIT;

PRAGMA foreign_keys = 0;

BEGIN TRANSACTION;
DROP TABLE "Attachments";

ALTER TABLE "ef_temp_Attachments" RENAME TO "Attachments";

COMMIT;

PRAGMA foreign_keys = 1;

BEGIN TRANSACTION;
CREATE INDEX "IX_Attachments_CredentialId" ON "Attachments" ("CredentialId");

COMMIT;

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20240917191243_1.4.1-RenameAttachmentsPlural', '10.0.12');

BEGIN TRANSACTION;
CREATE TABLE "TotpCodes" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_TotpCodes" PRIMARY KEY,
    "Name" TEXT NOT NULL,
    "SecretKey" TEXT NOT NULL,
    "CredentialId" TEXT NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL,
    CONSTRAINT "FK_TotpCodes_Credentials_CredentialId" FOREIGN KEY ("CredentialId") REFERENCES "Credentials" ("Id") ON DELETE CASCADE
);

CREATE INDEX "IX_TotpCodes_CredentialId" ON "TotpCodes" ("CredentialId");

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20250310131554_1.5.0-AddTotpCodes', '10.0.12');

COMMIT;

BEGIN TRANSACTION;

PRAGMA foreign_keys = OFF;

-- Clean up any existing temp tables first
DROP TABLE IF EXISTS "__EFMigrationsHistory_temp";
DROP TABLE IF EXISTS "Aliases_temp";
DROP TABLE IF EXISTS "Services_temp";
DROP TABLE IF EXISTS "EncryptionKeys_temp";
DROP TABLE IF EXISTS "Settings_temp";
DROP TABLE IF EXISTS "Credentials_temp";
DROP TABLE IF EXISTS "Attachments_temp";
DROP TABLE IF EXISTS "Passwords_temp";
DROP TABLE IF EXISTS "TotpCodes_temp";

-- Create backup tables for all data
CREATE TABLE "__EFMigrationsHistory_temp" AS SELECT * FROM "__EFMigrationsHistory";
CREATE TABLE "Aliases_temp" AS SELECT * FROM "Aliases";
CREATE TABLE "Services_temp" AS SELECT * FROM "Services";
CREATE TABLE "EncryptionKeys_temp" AS SELECT * FROM "EncryptionKeys";
CREATE TABLE "Settings_temp" AS SELECT * FROM "Settings";
CREATE TABLE "Credentials_temp" AS SELECT * FROM "Credentials";
CREATE TABLE "Attachments_temp" AS SELECT * FROM "Attachments";
CREATE TABLE "Passwords_temp" AS SELECT * FROM "Passwords";
CREATE TABLE "TotpCodes_temp" AS SELECT * FROM "TotpCodes";

-- Delete orphaned records that do not have a valid FK to the credential object
DELETE FROM "Attachments_temp" WHERE "CredentialId" NOT IN (SELECT "Id" FROM "Credentials_temp");
DELETE FROM "Passwords_temp" WHERE "CredentialId" NOT IN (SELECT "Id" FROM "Credentials_temp");
DELETE FROM "TotpCodes_temp" WHERE "CredentialId" NOT IN (SELECT "Id" FROM "Credentials_temp");

-- Delete orphaned credentials that do not have valid FKs to alias or service objects
DELETE FROM "Credentials_temp" WHERE "AliasId" NOT IN (SELECT "Id" FROM "Aliases_temp");
DELETE FROM "Credentials_temp" WHERE "ServiceId" NOT IN (SELECT "Id" FROM "Services_temp");

-- After cleaning credentials, clean dependent tables again in case we removed credentials
DELETE FROM "Attachments_temp" WHERE "CredentialId" NOT IN (SELECT "Id" FROM "Credentials_temp");
DELETE FROM "Passwords_temp" WHERE "CredentialId" NOT IN (SELECT "Id" FROM "Credentials_temp");
DELETE FROM "TotpCodes_temp" WHERE "CredentialId" NOT IN (SELECT "Id" FROM "Credentials_temp");

-- Drop all existing tables
DROP TABLE "TotpCodes";
DROP TABLE "Passwords";
DROP TABLE "Attachments";
DROP TABLE "Credentials";
DROP TABLE "Settings";
DROP TABLE "EncryptionKeys";
DROP TABLE "Services";
DROP TABLE "Aliases";
DROP TABLE "__EFMigrationsHistory";

-- Recreate tables with proper constraints (no dependencies first)
CREATE TABLE "__EFMigrationsHistory" (
    "MigrationId" TEXT NOT NULL CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY,
    "ProductVersion" TEXT NOT NULL
);

CREATE TABLE "Aliases" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_Aliases" PRIMARY KEY,
    "BirthDate" TEXT NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "Email" TEXT NULL,
    "FirstName" VARCHAR NULL,
    "Gender" VARCHAR NULL,
    "LastName" VARCHAR NULL,
    "NickName" VARCHAR NULL,
    "UpdatedAt" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE "Services" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_Services" PRIMARY KEY,
    "Name" TEXT NULL,
    "Url" TEXT NULL,
    "Logo" BLOB NULL,
    "CreatedAt" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE "EncryptionKeys" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_EncryptionKeys" PRIMARY KEY,
    "PublicKey" TEXT NOT NULL,
    "PrivateKey" TEXT NOT NULL,
    "IsPrimary" INTEGER NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE "Settings" (
    "Key" TEXT NOT NULL CONSTRAINT "PK_Settings" PRIMARY KEY,
    "Value" TEXT NULL,
    "CreatedAt" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL DEFAULT 0
);

-- Tables with foreign keys
CREATE TABLE "Credentials" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_Credentials" PRIMARY KEY,
    "AliasId" TEXT NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "Notes" TEXT NULL,
    "ServiceId" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "Username" TEXT NULL,
    "IsDeleted" INTEGER NOT NULL DEFAULT 0,
    CONSTRAINT "FK_Credentials_Aliases_AliasId" FOREIGN KEY ("AliasId") REFERENCES "Aliases" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_Credentials_Services_ServiceId" FOREIGN KEY ("ServiceId") REFERENCES "Services" ("Id") ON DELETE CASCADE
);

CREATE TABLE "Attachments" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_Attachments" PRIMARY KEY,
    "Blob" BLOB NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "CredentialId" TEXT NOT NULL,
    "Filename" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL DEFAULT 0,
    "UpdatedAt" TEXT NOT NULL,
    CONSTRAINT "FK_Attachments_Credentials_CredentialId" FOREIGN KEY ("CredentialId") REFERENCES "Credentials" ("Id") ON DELETE CASCADE
);

CREATE TABLE "Passwords" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_Passwords" PRIMARY KEY,
    "Value" TEXT NULL,
    "CreatedAt" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "CredentialId" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL DEFAULT 0,
    CONSTRAINT "FK_Passwords_Credentials_CredentialId" FOREIGN KEY ("CredentialId") REFERENCES "Credentials" ("Id") ON DELETE CASCADE
);

CREATE TABLE "TotpCodes" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_TotpCodes" PRIMARY KEY,
    "Name" TEXT NOT NULL,
    "SecretKey" TEXT NOT NULL,
    "CredentialId" TEXT NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL DEFAULT 0,
    CONSTRAINT "FK_TotpCodes_Credentials_CredentialId" FOREIGN KEY ("CredentialId") REFERENCES "Credentials" ("Id") ON DELETE CASCADE
);


-- Restore data from temp tables
INSERT INTO "__EFMigrationsHistory" SELECT * FROM "__EFMigrationsHistory_temp";
INSERT INTO "Aliases" SELECT * FROM "Aliases_temp";
INSERT INTO "Services" SELECT * FROM "Services_temp";
INSERT INTO "EncryptionKeys" SELECT * FROM "EncryptionKeys_temp";
INSERT INTO "Settings" SELECT * FROM "Settings_temp";
INSERT INTO "Credentials" SELECT * FROM "Credentials_temp";
INSERT INTO "Attachments" SELECT * FROM "Attachments_temp";
INSERT INTO "Passwords" SELECT * FROM "Passwords_temp";
INSERT INTO "TotpCodes" SELECT * FROM "TotpCodes_temp";

-- =====================================================================================
-- Date Format Normalization Migration
-- =====================================================================================
-- This migration normalizes ALL date fields to the standard format: 'yyyy-MM-dd HH:mm:ss.fff'
-- Previously the different clients used different date formats which complicate date parsing.
-- From version 0.24.0 onwards, all new dates are stored in this standard format.

-- Update Aliases table (CreatedAt, UpdatedAt, BirthDate)
UPDATE "Aliases" SET "CreatedAt" =
    CASE
        -- Already in correct format (yyyy-MM-dd HH:mm:ss.fff) - no change
        WHEN "CreatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]'
            THEN "CreatedAt"

        -- ISO 8601 with milliseconds (yyyy-MM-ddTHH:mm:ss.fffZ) -> Replace T with space, remove Z and everything after .fff
        WHEN "CreatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9]*'
            THEN substr("CreatedAt", 1, 10) || ' ' || substr("CreatedAt", 12, 12)

        -- Without milliseconds (yyyy-MM-dd HH:mm:ss or yyyy-MM-ddTHH:mm:ssZ) -> Add .000
        WHEN "CreatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][T ][0-9][0-9]:[0-9][0-9]:[0-9][0-9]*'
            THEN substr("CreatedAt", 1, 10) || ' ' || substr(replace("CreatedAt", 'T', ' '), 12, 8) || '.000'

        -- Fallback: if none match, keep as-is (edge case)
        ELSE "CreatedAt"
    END;

UPDATE "Aliases" SET "UpdatedAt" =
    CASE
        WHEN "UpdatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]'
            THEN "UpdatedAt"
        WHEN "UpdatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9]*'
            THEN substr("UpdatedAt", 1, 10) || ' ' || substr("UpdatedAt", 12, 12)
        WHEN "UpdatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][T ][0-9][0-9]:[0-9][0-9]:[0-9][0-9]*'
            THEN substr("UpdatedAt", 1, 10) || ' ' || substr(replace("UpdatedAt", 'T', ' '), 12, 8) || '.000'
        ELSE "UpdatedAt"
    END;

-- BirthDate: Always set time to 00:00:00 (no milliseconds for birth dates)
UPDATE "Aliases" SET "BirthDate" =
    CASE
        -- If empty or already '0001-01-01 00:00:00', keep as-is
        WHEN "BirthDate" = '' OR "BirthDate" = '0001-01-01 00:00:00'
            THEN "BirthDate"

        -- If already in correct format (yyyy-MM-dd 00:00:00), keep as-is
        WHEN "BirthDate" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] 00:00:00'
            THEN "BirthDate"

        -- Extract date part and set time to 00:00:00
        WHEN "BirthDate" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]*'
            THEN substr("BirthDate", 1, 10) || ' 00:00:00'

        -- Fallback
        ELSE "BirthDate"
    END;

-- Update Services table (CreatedAt, UpdatedAt)
UPDATE "Services" SET "CreatedAt" =
    CASE
        WHEN "CreatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]'
            THEN "CreatedAt"
        WHEN "CreatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9]*'
            THEN substr("CreatedAt", 1, 10) || ' ' || substr("CreatedAt", 12, 12)
        WHEN "CreatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][T ][0-9][0-9]:[0-9][0-9]:[0-9][0-9]*'
            THEN substr("CreatedAt", 1, 10) || ' ' || substr(replace("CreatedAt", 'T', ' '), 12, 8) || '.000'
        ELSE "CreatedAt"
    END;

UPDATE "Services" SET "UpdatedAt" =
    CASE
        WHEN "UpdatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]'
            THEN "UpdatedAt"
        WHEN "UpdatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9]*'
            THEN substr("UpdatedAt", 1, 10) || ' ' || substr("UpdatedAt", 12, 12)
        WHEN "UpdatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][T ][0-9][0-9]:[0-9][0-9]:[0-9][0-9]*'
            THEN substr("UpdatedAt", 1, 10) || ' ' || substr(replace("UpdatedAt", 'T', ' '), 12, 8) || '.000'
        ELSE "UpdatedAt"
    END;

-- Update EncryptionKeys table (CreatedAt, UpdatedAt)
UPDATE "EncryptionKeys" SET "CreatedAt" =
    CASE
        WHEN "CreatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]'
            THEN "CreatedAt"
        WHEN "CreatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9]*'
            THEN substr("CreatedAt", 1, 10) || ' ' || substr("CreatedAt", 12, 12)
        WHEN "CreatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][T ][0-9][0-9]:[0-9][0-9]:[0-9][0-9]*'
            THEN substr("CreatedAt", 1, 10) || ' ' || substr(replace("CreatedAt", 'T', ' '), 12, 8) || '.000'
        ELSE "CreatedAt"
    END;

UPDATE "EncryptionKeys" SET "UpdatedAt" =
    CASE
        WHEN "UpdatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]'
            THEN "UpdatedAt"
        WHEN "UpdatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9]*'
            THEN substr("UpdatedAt", 1, 10) || ' ' || substr("UpdatedAt", 12, 12)
        WHEN "UpdatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][T ][0-9][0-9]:[0-9][0-9]:[0-9][0-9]*'
            THEN substr("UpdatedAt", 1, 10) || ' ' || substr(replace("UpdatedAt", 'T', ' '), 12, 8) || '.000'
        ELSE "UpdatedAt"
    END;

-- Update Settings table (CreatedAt, UpdatedAt)
UPDATE "Settings" SET "CreatedAt" =
    CASE
        WHEN "CreatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]'
            THEN "CreatedAt"
        WHEN "CreatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9]*'
            THEN substr("CreatedAt", 1, 10) || ' ' || substr("CreatedAt", 12, 12)
        WHEN "CreatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][T ][0-9][0-9]:[0-9][0-9]:[0-9][0-9]*'
            THEN substr("CreatedAt", 1, 10) || ' ' || substr(replace("CreatedAt", 'T', ' '), 12, 8) || '.000'
        ELSE "CreatedAt"
    END;

UPDATE "Settings" SET "UpdatedAt" =
    CASE
        WHEN "UpdatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]'
            THEN "UpdatedAt"
        WHEN "UpdatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9]*'
            THEN substr("UpdatedAt", 1, 10) || ' ' || substr("UpdatedAt", 12, 12)
        WHEN "UpdatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][T ][0-9][0-9]:[0-9][0-9]:[0-9][0-9]*'
            THEN substr("UpdatedAt", 1, 10) || ' ' || substr(replace("UpdatedAt", 'T', ' '), 12, 8) || '.000'
        ELSE "UpdatedAt"
    END;

-- Update Credentials table (CreatedAt, UpdatedAt)
UPDATE "Credentials" SET "CreatedAt" =
    CASE
        WHEN "CreatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]'
            THEN "CreatedAt"
        WHEN "CreatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9]*'
            THEN substr("CreatedAt", 1, 10) || ' ' || substr("CreatedAt", 12, 12)
        WHEN "CreatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][T ][0-9][0-9]:[0-9][0-9]:[0-9][0-9]*'
            THEN substr("CreatedAt", 1, 10) || ' ' || substr(replace("CreatedAt", 'T', ' '), 12, 8) || '.000'
        ELSE "CreatedAt"
    END;

UPDATE "Credentials" SET "UpdatedAt" =
    CASE
        WHEN "UpdatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]'
            THEN "UpdatedAt"
        WHEN "UpdatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9]*'
            THEN substr("UpdatedAt", 1, 10) || ' ' || substr("UpdatedAt", 12, 12)
        WHEN "UpdatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][T ][0-9][0-9]:[0-9][0-9]:[0-9][0-9]*'
            THEN substr("UpdatedAt", 1, 10) || ' ' || substr(replace("UpdatedAt", 'T', ' '), 12, 8) || '.000'
        ELSE "UpdatedAt"
    END;

-- Update Attachments table (CreatedAt, UpdatedAt)
UPDATE "Attachments" SET "CreatedAt" =
    CASE
        WHEN "CreatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]'
            THEN "CreatedAt"
        WHEN "CreatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9]*'
            THEN substr("CreatedAt", 1, 10) || ' ' || substr("CreatedAt", 12, 12)
        WHEN "CreatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][T ][0-9][0-9]:[0-9][0-9]:[0-9][0-9]*'
            THEN substr("CreatedAt", 1, 10) || ' ' || substr(replace("CreatedAt", 'T', ' '), 12, 8) || '.000'
        ELSE "CreatedAt"
    END;

UPDATE "Attachments" SET "UpdatedAt" =
    CASE
        WHEN "UpdatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]'
            THEN "UpdatedAt"
        WHEN "UpdatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9]*'
            THEN substr("UpdatedAt", 1, 10) || ' ' || substr("UpdatedAt", 12, 12)
        WHEN "UpdatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][T ][0-9][0-9]:[0-9][0-9]:[0-9][0-9]*'
            THEN substr("UpdatedAt", 1, 10) || ' ' || substr(replace("UpdatedAt", 'T', ' '), 12, 8) || '.000'
        ELSE "UpdatedAt"
    END;

-- Update Passwords table (CreatedAt, UpdatedAt)
UPDATE "Passwords" SET "CreatedAt" =
    CASE
        WHEN "CreatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]'
            THEN "CreatedAt"
        WHEN "CreatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9]*'
            THEN substr("CreatedAt", 1, 10) || ' ' || substr("CreatedAt", 12, 12)
        WHEN "CreatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][T ][0-9][0-9]:[0-9][0-9]:[0-9][0-9]*'
            THEN substr("CreatedAt", 1, 10) || ' ' || substr(replace("CreatedAt", 'T', ' '), 12, 8) || '.000'
        ELSE "CreatedAt"
    END;

UPDATE "Passwords" SET "UpdatedAt" =
    CASE
        WHEN "UpdatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]'
            THEN "UpdatedAt"
        WHEN "UpdatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9]*'
            THEN substr("UpdatedAt", 1, 10) || ' ' || substr("UpdatedAt", 12, 12)
        WHEN "UpdatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][T ][0-9][0-9]:[0-9][0-9]:[0-9][0-9]*'
            THEN substr("UpdatedAt", 1, 10) || ' ' || substr(replace("UpdatedAt", 'T', ' '), 12, 8) || '.000'
        ELSE "UpdatedAt"
    END;

-- Update TotpCodes table (CreatedAt, UpdatedAt)
UPDATE "TotpCodes" SET "CreatedAt" =
    CASE
        WHEN "CreatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]'
            THEN "CreatedAt"
        WHEN "CreatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9]*'
            THEN substr("CreatedAt", 1, 10) || ' ' || substr("CreatedAt", 12, 12)
        WHEN "CreatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][T ][0-9][0-9]:[0-9][0-9]:[0-9][0-9]*'
            THEN substr("CreatedAt", 1, 10) || ' ' || substr(replace("CreatedAt", 'T', ' '), 12, 8) || '.000'
        ELSE "CreatedAt"
    END;

UPDATE "TotpCodes" SET "UpdatedAt" =
    CASE
        WHEN "UpdatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9][0-9][0-9]'
            THEN "UpdatedAt"
        WHEN "UpdatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]T[0-9][0-9]:[0-9][0-9]:[0-9][0-9].[0-9]*'
            THEN substr("UpdatedAt", 1, 10) || ' ' || substr("UpdatedAt", 12, 12)
        WHEN "UpdatedAt" GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][T ][0-9][0-9]:[0-9][0-9]:[0-9][0-9]*'
            THEN substr("UpdatedAt", 1, 10) || ' ' || substr(replace("UpdatedAt", 'T', ' '), 12, 8) || '.000'
        ELSE "UpdatedAt"
    END;

-- =====================================================================================
-- End of Date Format Normalization Migration
-- =====================================================================================

-- Recreate indexes
CREATE INDEX "IX_Credentials_AliasId" ON "Credentials" ("AliasId");
CREATE INDEX "IX_Credentials_ServiceId" ON "Credentials" ("ServiceId");
CREATE INDEX "IX_Attachments_CredentialId" ON "Attachments" ("CredentialId");
CREATE INDEX "IX_Passwords_CredentialId" ON "Passwords" ("CredentialId");
CREATE INDEX "IX_TotpCodes_CredentialId" ON "TotpCodes" ("CredentialId");

-- Clean up temp tables
DROP TABLE "__EFMigrationsHistory_temp";
DROP TABLE "Aliases_temp";
DROP TABLE "Services_temp";
DROP TABLE "EncryptionKeys_temp";
DROP TABLE "Settings_temp";
DROP TABLE "Credentials_temp";
DROP TABLE "Attachments_temp";
DROP TABLE "Passwords_temp";
DROP TABLE "TotpCodes_temp";

PRAGMA foreign_keys = ON;


CREATE TABLE "Passkeys" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_Passkeys" PRIMARY KEY,
    "RpId" TEXT COLLATE NOCASE NOT NULL,
    "UserHandle" BLOB NOT NULL,
    "PublicKey" TEXT NOT NULL,
    "PrivateKey" TEXT NOT NULL,
    "PrfKey" BLOB NULL,
    "DisplayName" TEXT NOT NULL,
    "AdditionalData" BLOB NULL,
    "CredentialId" TEXT NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL,
    CONSTRAINT "FK_Passkeys_Credentials_CredentialId" FOREIGN KEY ("CredentialId") REFERENCES "Credentials" ("Id") ON DELETE CASCADE
);

CREATE INDEX "IX_Passkeys_CredentialId" ON "Passkeys" ("CredentialId");

CREATE INDEX "IX_Passkeys_RpId" ON "Passkeys" ("RpId");

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20251014122838_1.6.0-AddPasskeys', '10.0.12');

COMMIT;

PRAGMA foreign_keys = OFF;

BEGIN TRANSACTION;
ALTER TABLE "TotpCodes" RENAME COLUMN "CredentialId" TO "ItemId";

DROP INDEX IF EXISTS "IX_TotpCodes_CredentialId";

CREATE INDEX IF NOT EXISTS "IX_TotpCodes_ItemId" ON "TotpCodes" ("ItemId");

ALTER TABLE "Passkeys" RENAME COLUMN "CredentialId" TO "ItemId";

DROP INDEX IF EXISTS "IX_Passkeys_CredentialId";

CREATE INDEX IF NOT EXISTS "IX_Passkeys_ItemId" ON "Passkeys" ("ItemId");

ALTER TABLE "Attachments" RENAME COLUMN "CredentialId" TO "ItemId";

DROP INDEX IF EXISTS "IX_Attachments_CredentialId";

CREATE INDEX IF NOT EXISTS "IX_Attachments_ItemId" ON "Attachments" ("ItemId");

CREATE TABLE "FieldDefinitions" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_FieldDefinitions" PRIMARY KEY,
    "FieldType" TEXT NOT NULL,
    "Label" TEXT NOT NULL,
    "IsMultiValue" INTEGER NOT NULL,
    "IsHidden" INTEGER NOT NULL,
    "EnableHistory" INTEGER NOT NULL,
    "Weight" INTEGER NOT NULL,
    "ApplicableToTypes" TEXT NULL,
    "CreatedAt" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL
);

CREATE TABLE "Folders" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_Folders" PRIMARY KEY,
    "Name" TEXT NOT NULL,
    "ParentFolderId" TEXT NULL,
    "Weight" INTEGER NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL,
    CONSTRAINT "FK_Folders_Folders_ParentFolderId" FOREIGN KEY ("ParentFolderId") REFERENCES "Folders" ("Id") ON DELETE CASCADE
);

CREATE TABLE "Logos" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_Logos" PRIMARY KEY,
    "Source" TEXT NOT NULL,
    "FileData" BLOB NULL,
    "MimeType" TEXT NULL,
    "FetchedAt" TEXT NULL,
    "CreatedAt" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL
);

CREATE TABLE "Tags" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_Tags" PRIMARY KEY,
    "Name" TEXT NOT NULL,
    "Color" TEXT NULL,
    "DisplayOrder" INTEGER NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL
);

CREATE TABLE "Items" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_Items" PRIMARY KEY,
    "Name" TEXT NULL,
    "ItemType" TEXT NOT NULL,
    "LogoId" TEXT NULL,
    "DeletedAt" TEXT NULL,
    "FolderId" TEXT NULL,
    "CreatedAt" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL,
    CONSTRAINT "FK_Items_Folders_FolderId" FOREIGN KEY ("FolderId") REFERENCES "Folders" ("Id") ON DELETE SET NULL,
    CONSTRAINT "FK_Items_Logos_LogoId" FOREIGN KEY ("LogoId") REFERENCES "Logos" ("Id") ON DELETE SET NULL
);

CREATE TABLE "FieldHistories" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_FieldHistories" PRIMARY KEY,
    "ItemId" TEXT NOT NULL,
    "FieldDefinitionId" TEXT NULL,
    "FieldKey" TEXT NULL,
    "ValueSnapshot" TEXT NOT NULL,
    "ChangedAt" TEXT NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL,
    CONSTRAINT "FK_FieldHistories_FieldDefinitions_FieldDefinitionId" FOREIGN KEY ("FieldDefinitionId") REFERENCES "FieldDefinitions" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_FieldHistories_Items_ItemId" FOREIGN KEY ("ItemId") REFERENCES "Items" ("Id") ON DELETE CASCADE
);

CREATE TABLE "FieldValues" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_FieldValues" PRIMARY KEY,
    "ItemId" TEXT NOT NULL,
    "FieldDefinitionId" TEXT NULL,
    "FieldKey" TEXT NULL,
    "Value" TEXT NULL,
    "Weight" INTEGER NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL,
    CONSTRAINT "FK_FieldValues_FieldDefinitions_FieldDefinitionId" FOREIGN KEY ("FieldDefinitionId") REFERENCES "FieldDefinitions" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_FieldValues_Items_ItemId" FOREIGN KEY ("ItemId") REFERENCES "Items" ("Id") ON DELETE CASCADE
);

CREATE TABLE "ItemTags" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_ItemTags" PRIMARY KEY,
    "ItemId" TEXT NOT NULL,
    "TagId" TEXT NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL,
    CONSTRAINT "FK_ItemTags_Items_ItemId" FOREIGN KEY ("ItemId") REFERENCES "Items" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_ItemTags_Tags_TagId" FOREIGN KEY ("TagId") REFERENCES "Tags" ("Id") ON DELETE CASCADE
);

CREATE INDEX "IX_FieldHistories_FieldDefinitionId" ON "FieldHistories" ("FieldDefinitionId");

CREATE INDEX "IX_FieldHistories_ItemId" ON "FieldHistories" ("ItemId");

CREATE INDEX "IX_FieldValues_FieldDefinitionId" ON "FieldValues" ("FieldDefinitionId");

CREATE INDEX "IX_FieldValues_FieldKey" ON "FieldValues" ("FieldKey");

CREATE INDEX "IX_FieldValues_ItemId" ON "FieldValues" ("ItemId");

CREATE INDEX "IX_FieldValues_ItemId_FieldDefinitionId_Weight" ON "FieldValues" ("ItemId", "FieldDefinitionId", "Weight");

CREATE INDEX "IX_FieldValues_ItemId_FieldKey" ON "FieldValues" ("ItemId", "FieldKey");

CREATE INDEX "IX_Folders_ParentFolderId" ON "Folders" ("ParentFolderId");

CREATE INDEX "IX_Items_FolderId" ON "Items" ("FolderId");

CREATE INDEX "IX_Items_LogoId" ON "Items" ("LogoId");

CREATE INDEX "IX_ItemTags_ItemId" ON "ItemTags" ("ItemId");

CREATE UNIQUE INDEX "IX_ItemTags_ItemId_TagId" ON "ItemTags" ("ItemId", "TagId");

CREATE INDEX "IX_ItemTags_TagId" ON "ItemTags" ("TagId");

CREATE UNIQUE INDEX "IX_Logos_Source" ON "Logos" ("Source");

CREATE INDEX "IX_Tags_Name" ON "Tags" ("Name");


                INSERT INTO Items (Id, Name, ItemType, LogoId, FolderId, CreatedAt, UpdatedAt, IsDeleted)
                SELECT
                  c.Id,
                  s.Name AS Name,
                  CASE
                    WHEN a.Id IS NOT NULL AND (
                      (a.FirstName IS NOT NULL AND a.FirstName != '') OR
                      (a.LastName IS NOT NULL AND a.LastName != '') OR
                      (a.Gender IS NOT NULL AND a.Gender != '') OR
                      (a.BirthDate IS NOT NULL AND a.BirthDate != '' AND a.BirthDate NOT LIKE '0001-%')
                    ) THEN 'Alias'
                    ELSE 'Login'
                  END AS ItemType,
                  NULL AS LogoId,
                  NULL AS FolderId,
                  c.CreatedAt,
                  c.UpdatedAt,
                  c.IsDeleted
                FROM Credentials c
                LEFT JOIN Services s ON s.Id = c.ServiceId
                LEFT JOIN Aliases a ON a.Id = c.AliasId;
            


                INSERT INTO Logos (Id, Source, FileData, MimeType, FetchedAt, CreatedAt, UpdatedAt, IsDeleted)
                SELECT
                  UPPER(SUBSTR(hex(randomblob(4)), 1, 8) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(6)), 1, 12)) AS Id,
                  -- Extract and normalize hostname: remove protocol, path, lowercase, and www. prefix
                  REPLACE(
                    LOWER(
                      SUBSTR(
                        CASE
                          WHEN s.Url LIKE 'https://%' THEN SUBSTR(s.Url, 9)
                          WHEN s.Url LIKE 'http://%' THEN SUBSTR(s.Url, 8)
                          ELSE s.Url
                        END,
                        1,
                        CASE
                          WHEN INSTR(
                            CASE
                              WHEN s.Url LIKE 'https://%' THEN SUBSTR(s.Url, 9)
                              WHEN s.Url LIKE 'http://%' THEN SUBSTR(s.Url, 8)
                              ELSE s.Url
                            END, '/') > 0
                          THEN INSTR(
                            CASE
                              WHEN s.Url LIKE 'https://%' THEN SUBSTR(s.Url, 9)
                              WHEN s.Url LIKE 'http://%' THEN SUBSTR(s.Url, 8)
                              ELSE s.Url
                            END, '/') - 1
                          ELSE LENGTH(
                            CASE
                              WHEN s.Url LIKE 'https://%' THEN SUBSTR(s.Url, 9)
                              WHEN s.Url LIKE 'http://%' THEN SUBSTR(s.Url, 8)
                              ELSE s.Url
                            END)
                        END
                      )
                    ),
                    'www.', ''
                  ) AS Source,
                  s.Logo AS FileData,
                  'image/png' AS MimeType,
                  NULL AS FetchedAt,
                  MIN(s.CreatedAt) AS CreatedAt,
                  MAX(s.UpdatedAt) AS UpdatedAt,
                  0 AS IsDeleted
                FROM Services s
                WHERE s.Logo IS NOT NULL AND s.Url IS NOT NULL AND s.Url != ''
                GROUP BY REPLACE(
                    LOWER(
                      SUBSTR(
                        CASE
                          WHEN s.Url LIKE 'https://%' THEN SUBSTR(s.Url, 9)
                          WHEN s.Url LIKE 'http://%' THEN SUBSTR(s.Url, 8)
                          ELSE s.Url
                        END,
                        1,
                        CASE
                          WHEN INSTR(
                            CASE
                              WHEN s.Url LIKE 'https://%' THEN SUBSTR(s.Url, 9)
                              WHEN s.Url LIKE 'http://%' THEN SUBSTR(s.Url, 8)
                              ELSE s.Url
                            END, '/') > 0
                          THEN INSTR(
                            CASE
                              WHEN s.Url LIKE 'https://%' THEN SUBSTR(s.Url, 9)
                              WHEN s.Url LIKE 'http://%' THEN SUBSTR(s.Url, 8)
                              ELSE s.Url
                            END, '/') - 1
                          ELSE LENGTH(
                            CASE
                              WHEN s.Url LIKE 'https://%' THEN SUBSTR(s.Url, 9)
                              WHEN s.Url LIKE 'http://%' THEN SUBSTR(s.Url, 8)
                              ELSE s.Url
                            END)
                        END
                      )
                    ),
                    'www.', ''
                  );
            


                UPDATE Items
                SET LogoId = (
                  SELECT l.Id FROM Logos l
                  INNER JOIN Services s ON REPLACE(
                    LOWER(
                      SUBSTR(
                        CASE
                          WHEN s.Url LIKE 'https://%' THEN SUBSTR(s.Url, 9)
                          WHEN s.Url LIKE 'http://%' THEN SUBSTR(s.Url, 8)
                          ELSE s.Url
                        END,
                        1,
                        CASE
                          WHEN INSTR(
                            CASE
                              WHEN s.Url LIKE 'https://%' THEN SUBSTR(s.Url, 9)
                              WHEN s.Url LIKE 'http://%' THEN SUBSTR(s.Url, 8)
                              ELSE s.Url
                            END, '/') > 0
                          THEN INSTR(
                            CASE
                              WHEN s.Url LIKE 'https://%' THEN SUBSTR(s.Url, 9)
                              WHEN s.Url LIKE 'http://%' THEN SUBSTR(s.Url, 8)
                              ELSE s.Url
                            END, '/') - 1
                          ELSE LENGTH(
                            CASE
                              WHEN s.Url LIKE 'https://%' THEN SUBSTR(s.Url, 9)
                              WHEN s.Url LIKE 'http://%' THEN SUBSTR(s.Url, 8)
                              ELSE s.Url
                            END)
                        END
                      )
                    ),
                    'www.', ''
                  ) = l.Source
                  INNER JOIN Credentials c ON c.ServiceId = s.Id
                  WHERE c.Id = Items.Id
                  LIMIT 1
                )
                WHERE EXISTS (
                  SELECT 1 FROM Credentials c
                  INNER JOIN Services s ON s.Id = c.ServiceId
                  WHERE c.Id = Items.Id AND s.Logo IS NOT NULL
                );
            


                INSERT INTO FieldValues (Id, ItemId, FieldDefinitionId, FieldKey, Value, Weight, CreatedAt, UpdatedAt, IsDeleted)
                SELECT
                  UPPER(SUBSTR(hex(randomblob(4)), 1, 8) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(6)), 1, 12)) AS Id,
                  c.Id AS ItemId,
                  NULL AS FieldDefinitionId,
                  'login.url' AS FieldKey,
                  s.Url AS Value,
                  0 AS Weight,
                  s.UpdatedAt AS CreatedAt,
                  s.UpdatedAt AS UpdatedAt,
                  0 AS IsDeleted
                FROM Credentials c
                INNER JOIN Services s ON s.Id = c.ServiceId
                WHERE s.Url IS NOT NULL AND s.Url != '';
            


                INSERT INTO FieldValues (Id, ItemId, FieldDefinitionId, FieldKey, Value, Weight, CreatedAt, UpdatedAt, IsDeleted)
                SELECT
                  UPPER(SUBSTR(hex(randomblob(4)), 1, 8) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(6)), 1, 12)) AS Id,
                  c.Id AS ItemId,
                  NULL AS FieldDefinitionId,
                  'login.username' AS FieldKey,
                  c.Username AS Value,
                  0 AS Weight,
                  c.UpdatedAt AS CreatedAt,
                  c.UpdatedAt AS UpdatedAt,
                  0 AS IsDeleted
                FROM Credentials c
                WHERE c.Username IS NOT NULL AND c.Username != '';
            


                INSERT INTO FieldValues (Id, ItemId, FieldDefinitionId, FieldKey, Value, Weight, CreatedAt, UpdatedAt, IsDeleted)
                SELECT
                  UPPER(SUBSTR(hex(randomblob(4)), 1, 8) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(6)), 1, 12)) AS Id,
                  c.Id AS ItemId,
                  NULL AS FieldDefinitionId,
                  'notes.content' AS FieldKey,
                  c.Notes AS Value,
                  0 AS Weight,
                  c.UpdatedAt AS CreatedAt,
                  c.UpdatedAt AS UpdatedAt,
                  0 AS IsDeleted
                FROM Credentials c
                WHERE c.Notes IS NOT NULL AND c.Notes != '';
            


                INSERT INTO FieldValues (Id, ItemId, FieldDefinitionId, FieldKey, Value, Weight, CreatedAt, UpdatedAt, IsDeleted)
                SELECT
                  UPPER(SUBSTR(hex(randomblob(4)), 1, 8) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(6)), 1, 12)) AS Id,
                  p.CredentialId AS ItemId,
                  NULL AS FieldDefinitionId,
                  'login.password' AS FieldKey,
                  p.Value AS Value,
                  0 AS Weight,
                  p.UpdatedAt AS CreatedAt,
                  p.UpdatedAt AS UpdatedAt,
                  0 AS IsDeleted
                FROM Passwords p
                INNER JOIN (
                  SELECT CredentialId, MAX(UpdatedAt) AS MaxUpdated, MAX(Id) AS MaxId
                  FROM Passwords
                  WHERE IsDeleted = 0
                  GROUP BY CredentialId
                ) pm ON p.CredentialId = pm.CredentialId AND p.UpdatedAt = pm.MaxUpdated AND p.Id = pm.MaxId
                WHERE p.IsDeleted = 0;
            


                INSERT INTO FieldValues (Id, ItemId, FieldDefinitionId, FieldKey, Value, Weight, CreatedAt, UpdatedAt, IsDeleted)
                SELECT
                  UPPER(SUBSTR(hex(randomblob(4)), 1, 8) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(6)), 1, 12)) AS Id,
                  c.Id AS ItemId,
                  NULL AS FieldDefinitionId,
                  'login.email' AS FieldKey,
                  a.Email AS Value,
                  0 AS Weight,
                  a.UpdatedAt AS CreatedAt,
                  a.UpdatedAt AS UpdatedAt,
                  0 AS IsDeleted
                FROM Credentials c
                INNER JOIN Aliases a ON a.Id = c.AliasId
                WHERE a.Email IS NOT NULL AND a.Email != '';
            


                INSERT INTO FieldValues (Id, ItemId, FieldDefinitionId, FieldKey, Value, Weight, CreatedAt, UpdatedAt, IsDeleted)
                SELECT
                  UPPER(SUBSTR(hex(randomblob(4)), 1, 8) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(6)), 1, 12)) AS Id,
                  c.Id AS ItemId,
                  NULL AS FieldDefinitionId,
                  'alias.first_name' AS FieldKey,
                  a.FirstName AS Value,
                  0 AS Weight,
                  a.UpdatedAt AS CreatedAt,
                  a.UpdatedAt AS UpdatedAt,
                  0 AS IsDeleted
                FROM Credentials c
                INNER JOIN Aliases a ON a.Id = c.AliasId
                WHERE a.FirstName IS NOT NULL AND a.FirstName != '';
            


                INSERT INTO FieldValues (Id, ItemId, FieldDefinitionId, FieldKey, Value, Weight, CreatedAt, UpdatedAt, IsDeleted)
                SELECT
                  UPPER(SUBSTR(hex(randomblob(4)), 1, 8) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(6)), 1, 12)) AS Id,
                  c.Id AS ItemId,
                  NULL AS FieldDefinitionId,
                  'alias.last_name' AS FieldKey,
                  a.LastName AS Value,
                  0 AS Weight,
                  a.UpdatedAt AS CreatedAt,
                  a.UpdatedAt AS UpdatedAt,
                  0 AS IsDeleted
                FROM Credentials c
                INNER JOIN Aliases a ON a.Id = c.AliasId
                WHERE a.LastName IS NOT NULL AND a.LastName != '';
            


                INSERT INTO FieldValues (Id, ItemId, FieldDefinitionId, FieldKey, Value, Weight, CreatedAt, UpdatedAt, IsDeleted)
                SELECT
                  UPPER(SUBSTR(hex(randomblob(4)), 1, 8) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(6)), 1, 12)) AS Id,
                  c.Id AS ItemId,
                  NULL AS FieldDefinitionId,
                  'alias.gender' AS FieldKey,
                  a.Gender AS Value,
                  0 AS Weight,
                  a.UpdatedAt AS CreatedAt,
                  a.UpdatedAt AS UpdatedAt,
                  0 AS IsDeleted
                FROM Credentials c
                INNER JOIN Aliases a ON a.Id = c.AliasId
                WHERE a.Gender IS NOT NULL AND a.Gender != '';
            


                INSERT INTO FieldValues (Id, ItemId, FieldDefinitionId, FieldKey, Value, Weight, CreatedAt, UpdatedAt, IsDeleted)
                SELECT
                  UPPER(SUBSTR(hex(randomblob(4)), 1, 8) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(2)), 1, 4) || '-' ||
                        SUBSTR(hex(randomblob(6)), 1, 12)) AS Id,
                  c.Id AS ItemId,
                  NULL AS FieldDefinitionId,
                  'alias.birthdate' AS FieldKey,
                  SUBSTR(a.BirthDate, 1, 10) AS Value,
                  0 AS Weight,
                  a.UpdatedAt AS CreatedAt,
                  a.UpdatedAt AS UpdatedAt,
                  0 AS IsDeleted
                FROM Credentials c
                INNER JOIN Aliases a ON a.Id = c.AliasId
                WHERE a.BirthDate IS NOT NULL AND a.BirthDate != '' AND a.BirthDate NOT LIKE '0001-%';
            

DROP TABLE "Passwords";

DROP TABLE "Credentials";

DROP TABLE "Aliases";

DROP TABLE "Services";

COMMIT;

PRAGMA foreign_keys = ON;

BEGIN TRANSACTION;
CREATE TABLE "ef_temp_Attachments" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_Attachments" PRIMARY KEY,
    "Blob" BLOB NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "Filename" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL,
    "ItemId" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    CONSTRAINT "FK_Attachments_Items_ItemId" FOREIGN KEY ("ItemId") REFERENCES "Items" ("Id") ON DELETE CASCADE
);

INSERT INTO "ef_temp_Attachments" ("Id", "Blob", "CreatedAt", "Filename", "IsDeleted", "ItemId", "UpdatedAt")
SELECT "Id", "Blob", "CreatedAt", "Filename", "IsDeleted", "ItemId", "UpdatedAt"
FROM "Attachments";

CREATE TABLE "ef_temp_Passkeys" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_Passkeys" PRIMARY KEY,
    "AdditionalData" BLOB NULL,
    "CreatedAt" TEXT NOT NULL,
    "DisplayName" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL,
    "ItemId" TEXT NOT NULL,
    "PrfKey" BLOB NULL,
    "PrivateKey" TEXT NOT NULL,
    "PublicKey" TEXT NOT NULL,
    "RpId" TEXT COLLATE NOCASE NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "UserHandle" BLOB NOT NULL,
    CONSTRAINT "FK_Passkeys_Items_ItemId" FOREIGN KEY ("ItemId") REFERENCES "Items" ("Id") ON DELETE CASCADE
);

INSERT INTO "ef_temp_Passkeys" ("Id", "AdditionalData", "CreatedAt", "DisplayName", "IsDeleted", "ItemId", "PrfKey", "PrivateKey", "PublicKey", "RpId", "UpdatedAt", "UserHandle")
SELECT "Id", "AdditionalData", "CreatedAt", "DisplayName", "IsDeleted", "ItemId", "PrfKey", "PrivateKey", "PublicKey", "RpId", "UpdatedAt", "UserHandle"
FROM "Passkeys";

CREATE TABLE "ef_temp_TotpCodes" (
    "Id" TEXT NOT NULL CONSTRAINT "PK_TotpCodes" PRIMARY KEY,
    "CreatedAt" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL,
    "ItemId" TEXT NOT NULL,
    "Name" TEXT NOT NULL,
    "SecretKey" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    CONSTRAINT "FK_TotpCodes_Items_ItemId" FOREIGN KEY ("ItemId") REFERENCES "Items" ("Id") ON DELETE CASCADE
);

INSERT INTO "ef_temp_TotpCodes" ("Id", "CreatedAt", "IsDeleted", "ItemId", "Name", "SecretKey", "UpdatedAt")
SELECT "Id", "CreatedAt", "IsDeleted", "ItemId", "Name", "SecretKey", "UpdatedAt"
FROM "TotpCodes";

COMMIT;

PRAGMA foreign_keys = 0;

BEGIN TRANSACTION;
DROP TABLE "Attachments";

ALTER TABLE "ef_temp_Attachments" RENAME TO "Attachments";

DROP TABLE "Passkeys";

ALTER TABLE "ef_temp_Passkeys" RENAME TO "Passkeys";

DROP TABLE "TotpCodes";

ALTER TABLE "ef_temp_TotpCodes" RENAME TO "TotpCodes";

COMMIT;

PRAGMA foreign_keys = 1;

BEGIN TRANSACTION;
CREATE INDEX "IX_Attachments_ItemId" ON "Attachments" ("ItemId");

CREATE INDEX "IX_Passkeys_ItemId" ON "Passkeys" ("ItemId");

CREATE INDEX "IX_Passkeys_RpId" ON "Passkeys" ("RpId");

CREATE INDEX "IX_TotpCodes_ItemId" ON "TotpCodes" ("ItemId");

COMMIT;

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20251213111207_1.7.0-FieldBasedDataModelUpdate', '10.0.12');

BEGIN TRANSACTION;
INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20260130221620_2.0.0-MajorVersionBump', '10.0.12');

COMMIT;

PRAGMA foreign_keys = OFF;

BEGIN TRANSACTION;
DROP INDEX "IX_TotpCodes_ItemId";

DROP INDEX "IX_Tags_Name";

DROP INDEX "IX_Passkeys_ItemId";

DROP INDEX "IX_Logos_Source";

DROP INDEX "IX_ItemTags_ItemId_TagId";

DROP INDEX "IX_Items_FolderId";

DROP INDEX "IX_Items_LogoId";

DROP INDEX "IX_Attachments_ItemId";

ALTER TABLE "TotpCodes" ADD "ManifestId" TEXT COLLATE NOCASE NOT NULL DEFAULT '';

ALTER TABLE "TotpCodes" ADD "Algorithm" TEXT NOT NULL DEFAULT 'SHA1';

ALTER TABLE "TotpCodes" ADD "Digits" INTEGER NOT NULL DEFAULT 6;

ALTER TABLE "TotpCodes" ADD "Period" INTEGER NOT NULL DEFAULT 30;

ALTER TABLE "Tags" ADD "ManifestId" TEXT COLLATE NOCASE NOT NULL DEFAULT '';

ALTER TABLE "Settings" ADD "ManifestId" TEXT COLLATE NOCASE NOT NULL DEFAULT '';

ALTER TABLE "Passkeys" ADD "ManifestId" TEXT COLLATE NOCASE NOT NULL DEFAULT '';

ALTER TABLE "Passkeys" ADD "CredentialId" BLOB NULL;

ALTER TABLE "Logos" ADD "ManifestId" TEXT COLLATE NOCASE NOT NULL DEFAULT '';

ALTER TABLE "Logos" ADD "Kind" TEXT NOT NULL DEFAULT 'favicon';

ALTER TABLE "Logos" ADD "Name" TEXT NULL;

ALTER TABLE "ItemTags" ADD "ManifestId" TEXT COLLATE NOCASE NOT NULL DEFAULT '';

ALTER TABLE "Items" ADD "ManifestId" TEXT COLLATE NOCASE NOT NULL DEFAULT '';

ALTER TABLE "Items" ADD "ArchivedAt" TEXT NULL;

ALTER TABLE "Folders" ADD "ManifestId" TEXT COLLATE NOCASE NOT NULL DEFAULT '';

ALTER TABLE "FieldValues" ADD "ManifestId" TEXT COLLATE NOCASE NOT NULL DEFAULT '';

ALTER TABLE "FieldValues" ADD "IsDisabled" INTEGER NOT NULL DEFAULT 0;

ALTER TABLE "FieldValues" ADD "ValueIndex" INTEGER NOT NULL DEFAULT 0;

ALTER TABLE "FieldHistories" ADD "ManifestId" TEXT COLLATE NOCASE NOT NULL DEFAULT '';

ALTER TABLE "FieldDefinitions" ADD "ManifestId" TEXT COLLATE NOCASE NOT NULL DEFAULT '';

ALTER TABLE "EncryptionKeys" ADD "ManifestId" TEXT COLLATE NOCASE NOT NULL DEFAULT '';

ALTER TABLE "Attachments" ADD "ManifestId" TEXT COLLATE NOCASE NOT NULL DEFAULT '';

ALTER TABLE "Logos" ADD "FileDataHash" TEXT NULL;

ALTER TABLE "Attachments" ADD "BlobHash" TEXT NULL;

CREATE TABLE "CodecOverflows" (
    "Id" TEXT COLLATE NOCASE NOT NULL CONSTRAINT "PK_CodecOverflows" PRIMARY KEY,
    "Data" TEXT NOT NULL
);

CREATE TABLE "ItemStats" (
    "ManifestId" TEXT COLLATE NOCASE NOT NULL,
    "Id" TEXT COLLATE NOCASE NOT NULL,
    "DeviceId" TEXT COLLATE NOCASE NOT NULL DEFAULT '',
    "LastUsedAt" TEXT NULL,
    "UseCount" INTEGER NOT NULL,
    "LastAutofilledAt" TEXT NULL,
    "AutofillCount" INTEGER NOT NULL,
    "LastCopiedAt" TEXT NULL,
    "CopyCount" INTEGER NOT NULL,
    "LastPasskeyAuthAt" TEXT NULL,
    "PasskeyAuthCount" INTEGER NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL,
    CONSTRAINT "PK_ItemStats" PRIMARY KEY ("ManifestId", "Id", "DeviceId")
);

CREATE TABLE "Manifests" (
    "Id" TEXT COLLATE NOCASE NOT NULL CONSTRAINT "PK_Manifests" PRIMARY KEY,
    "Name" TEXT NULL
);

CREATE INDEX "IX_TotpCodes_ManifestId_ItemId" ON "TotpCodes" ("ManifestId", "ItemId");

CREATE INDEX "IX_Tags_ManifestId_Name" ON "Tags" ("ManifestId", "Name");

CREATE INDEX "IX_Passkeys_ManifestId_ItemId" ON "Passkeys" ("ManifestId", "ItemId");

CREATE UNIQUE INDEX "IX_Logos_ManifestId_Kind_Source" ON "Logos" ("ManifestId", "Kind", "Source");

CREATE INDEX "IX_ItemTags_ManifestId_TagId" ON "ItemTags" ("ManifestId", "TagId");

CREATE INDEX "IX_Items_ManifestId_FolderId" ON "Items" ("ManifestId", "FolderId");

CREATE INDEX "IX_Items_ManifestId_LogoId" ON "Items" ("ManifestId", "LogoId");

CREATE INDEX "IX_Folders_ManifestId_ParentFolderId" ON "Folders" ("ManifestId", "ParentFolderId");

CREATE INDEX "IX_FieldValues_ManifestId_FieldDefinitionId" ON "FieldValues" ("ManifestId", "FieldDefinitionId");

CREATE INDEX "IX_FieldValues_ManifestId_ItemId" ON "FieldValues" ("ManifestId", "ItemId");

CREATE INDEX "IX_FieldHistories_ManifestId_FieldDefinitionId" ON "FieldHistories" ("ManifestId", "FieldDefinitionId");

CREATE INDEX "IX_FieldHistories_ManifestId_ItemId" ON "FieldHistories" ("ManifestId", "ItemId");

CREATE INDEX "IX_EncryptionKeys_ManifestId_IsPrimary" ON "EncryptionKeys" ("ManifestId", "IsPrimary");

CREATE INDEX "IX_Attachments_ManifestId_ItemId" ON "Attachments" ("ManifestId", "ItemId");

CREATE TABLE "ef_temp_Attachments" (
    "ManifestId" TEXT COLLATE NOCASE NOT NULL,
    "Id" TEXT COLLATE NOCASE NOT NULL,
    "Blob" BLOB NULL,
    "BlobHash" TEXT NULL,
    "CreatedAt" TEXT NOT NULL,
    "Filename" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL,
    "ItemId" TEXT COLLATE NOCASE NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    CONSTRAINT "PK_Attachments" PRIMARY KEY ("ManifestId", "Id"),
    CONSTRAINT "FK_Attachments_Items_ManifestId_ItemId" FOREIGN KEY ("ManifestId", "ItemId") REFERENCES "Items" ("ManifestId", "Id") ON DELETE CASCADE
);

INSERT INTO "ef_temp_Attachments" ("ManifestId", "Id", "Blob", "BlobHash", "CreatedAt", "Filename", "IsDeleted", "ItemId", "UpdatedAt")
SELECT "ManifestId", "Id", "Blob", "BlobHash", "CreatedAt", "Filename", "IsDeleted", "ItemId", "UpdatedAt"
FROM "Attachments";

CREATE TABLE "ef_temp_FieldHistories" (
    "ManifestId" TEXT COLLATE NOCASE NOT NULL,
    "Id" TEXT COLLATE NOCASE NOT NULL,
    "ChangedAt" TEXT NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "FieldDefinitionId" TEXT COLLATE NOCASE NULL,
    "FieldKey" TEXT NULL,
    "IsDeleted" INTEGER NOT NULL,
    "ItemId" TEXT COLLATE NOCASE NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "ValueSnapshot" TEXT NOT NULL,
    CONSTRAINT "PK_FieldHistories" PRIMARY KEY ("ManifestId", "Id"),
    CONSTRAINT "FK_FieldHistories_FieldDefinitions_ManifestId_FieldDefinitionId" FOREIGN KEY ("ManifestId", "FieldDefinitionId") REFERENCES "FieldDefinitions" ("ManifestId", "Id") ON DELETE CASCADE,
    CONSTRAINT "FK_FieldHistories_Items_ManifestId_ItemId" FOREIGN KEY ("ManifestId", "ItemId") REFERENCES "Items" ("ManifestId", "Id") ON DELETE CASCADE
);

INSERT INTO "ef_temp_FieldHistories" ("ManifestId", "Id", "ChangedAt", "CreatedAt", "FieldDefinitionId", "FieldKey", "IsDeleted", "ItemId", "UpdatedAt", "ValueSnapshot")
SELECT "ManifestId", "Id", "ChangedAt", "CreatedAt", "FieldDefinitionId", "FieldKey", "IsDeleted", "ItemId", "UpdatedAt", "ValueSnapshot"
FROM "FieldHistories";

CREATE TABLE "ef_temp_FieldValues" (
    "ManifestId" TEXT COLLATE NOCASE NOT NULL,
    "Id" TEXT COLLATE NOCASE NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "FieldDefinitionId" TEXT COLLATE NOCASE NULL,
    "FieldKey" TEXT NULL,
    "IsDeleted" INTEGER NOT NULL,
    "IsDisabled" INTEGER NOT NULL DEFAULT 0,
    "ItemId" TEXT COLLATE NOCASE NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "Value" TEXT NULL,
    "ValueIndex" INTEGER NOT NULL DEFAULT 0,
    "Weight" INTEGER NOT NULL,
    CONSTRAINT "PK_FieldValues" PRIMARY KEY ("ManifestId", "Id"),
    CONSTRAINT "FK_FieldValues_FieldDefinitions_ManifestId_FieldDefinitionId" FOREIGN KEY ("ManifestId", "FieldDefinitionId") REFERENCES "FieldDefinitions" ("ManifestId", "Id") ON DELETE CASCADE,
    CONSTRAINT "FK_FieldValues_Items_ManifestId_ItemId" FOREIGN KEY ("ManifestId", "ItemId") REFERENCES "Items" ("ManifestId", "Id") ON DELETE CASCADE
);

INSERT INTO "ef_temp_FieldValues" ("ManifestId", "Id", "CreatedAt", "FieldDefinitionId", "FieldKey", "IsDeleted", "IsDisabled", "ItemId", "UpdatedAt", "Value", "ValueIndex", "Weight")
SELECT "ManifestId", "Id", "CreatedAt", "FieldDefinitionId", "FieldKey", "IsDeleted", "IsDisabled", "ItemId", "UpdatedAt", "Value", "ValueIndex", "Weight"
FROM "FieldValues";

CREATE TABLE "ef_temp_Folders" (
    "ManifestId" TEXT COLLATE NOCASE NOT NULL,
    "Id" TEXT COLLATE NOCASE NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL,
    "Name" TEXT NOT NULL,
    "ParentFolderId" TEXT COLLATE NOCASE NULL,
    "UpdatedAt" TEXT NOT NULL,
    "Weight" INTEGER NOT NULL,
    CONSTRAINT "PK_Folders" PRIMARY KEY ("ManifestId", "Id"),
    CONSTRAINT "FK_Folders_Folders_ManifestId_ParentFolderId" FOREIGN KEY ("ManifestId", "ParentFolderId") REFERENCES "Folders" ("ManifestId", "Id") ON DELETE CASCADE
);

INSERT INTO "ef_temp_Folders" ("ManifestId", "Id", "CreatedAt", "IsDeleted", "Name", "ParentFolderId", "UpdatedAt", "Weight")
SELECT "ManifestId", "Id", "CreatedAt", "IsDeleted", "Name", "ParentFolderId", "UpdatedAt", "Weight"
FROM "Folders";

CREATE TABLE "ef_temp_Items" (
    "ManifestId" TEXT COLLATE NOCASE NOT NULL,
    "Id" TEXT COLLATE NOCASE NOT NULL,
    "ArchivedAt" TEXT NULL,
    "CreatedAt" TEXT NOT NULL,
    "DeletedAt" TEXT NULL,
    "FolderId" TEXT COLLATE NOCASE NULL,
    "IsDeleted" INTEGER NOT NULL,
    "ItemType" TEXT NOT NULL,
    "LogoId" TEXT COLLATE NOCASE NULL,
    "Name" TEXT NULL,
    "UpdatedAt" TEXT NOT NULL,
    CONSTRAINT "PK_Items" PRIMARY KEY ("ManifestId", "Id"),
    CONSTRAINT "FK_Items_Folders_ManifestId_FolderId" FOREIGN KEY ("ManifestId", "FolderId") REFERENCES "Folders" ("ManifestId", "Id"),
    CONSTRAINT "FK_Items_Logos_ManifestId_LogoId" FOREIGN KEY ("ManifestId", "LogoId") REFERENCES "Logos" ("ManifestId", "Id")
);

INSERT INTO "ef_temp_Items" ("ManifestId", "Id", "ArchivedAt", "CreatedAt", "DeletedAt", "FolderId", "IsDeleted", "ItemType", "LogoId", "Name", "UpdatedAt")
SELECT "ManifestId", "Id", "ArchivedAt", "CreatedAt", "DeletedAt", "FolderId", "IsDeleted", "ItemType", "LogoId", "Name", "UpdatedAt"
FROM "Items";

CREATE TABLE "ef_temp_ItemTags" (
    "ManifestId" TEXT COLLATE NOCASE NOT NULL,
    "ItemId" TEXT COLLATE NOCASE NOT NULL,
    "TagId" TEXT COLLATE NOCASE NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    CONSTRAINT "PK_ItemTags" PRIMARY KEY ("ManifestId", "ItemId", "TagId"),
    CONSTRAINT "FK_ItemTags_Items_ManifestId_ItemId" FOREIGN KEY ("ManifestId", "ItemId") REFERENCES "Items" ("ManifestId", "Id") ON DELETE CASCADE,
    CONSTRAINT "FK_ItemTags_Tags_ManifestId_TagId" FOREIGN KEY ("ManifestId", "TagId") REFERENCES "Tags" ("ManifestId", "Id") ON DELETE CASCADE
);

INSERT INTO "ef_temp_ItemTags" ("ManifestId", "ItemId", "TagId", "CreatedAt", "IsDeleted", "UpdatedAt")
SELECT "ManifestId", "ItemId", "TagId", "CreatedAt", "IsDeleted", "UpdatedAt"
FROM "ItemTags";

CREATE TABLE "ef_temp_Passkeys" (
    "ManifestId" TEXT COLLATE NOCASE NOT NULL,
    "Id" TEXT COLLATE NOCASE NOT NULL,
    "AdditionalData" BLOB NULL,
    "CreatedAt" TEXT NOT NULL,
    "CredentialId" BLOB NULL,
    "DisplayName" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL,
    "ItemId" TEXT COLLATE NOCASE NOT NULL,
    "PrfKey" BLOB NULL,
    "PrivateKey" TEXT NOT NULL,
    "PublicKey" TEXT NOT NULL,
    "RpId" TEXT COLLATE NOCASE NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "UserHandle" BLOB NOT NULL,
    CONSTRAINT "PK_Passkeys" PRIMARY KEY ("ManifestId", "Id"),
    CONSTRAINT "FK_Passkeys_Items_ManifestId_ItemId" FOREIGN KEY ("ManifestId", "ItemId") REFERENCES "Items" ("ManifestId", "Id") ON DELETE CASCADE
);

INSERT INTO "ef_temp_Passkeys" ("ManifestId", "Id", "AdditionalData", "CreatedAt", "CredentialId", "DisplayName", "IsDeleted", "ItemId", "PrfKey", "PrivateKey", "PublicKey", "RpId", "UpdatedAt", "UserHandle")
SELECT "ManifestId", "Id", "AdditionalData", "CreatedAt", "CredentialId", "DisplayName", "IsDeleted", "ItemId", "PrfKey", "PrivateKey", "PublicKey", "RpId", "UpdatedAt", "UserHandle"
FROM "Passkeys";

CREATE TABLE "ef_temp_TotpCodes" (
    "ManifestId" TEXT COLLATE NOCASE NOT NULL,
    "Id" TEXT COLLATE NOCASE NOT NULL,
    "Algorithm" TEXT NOT NULL DEFAULT 'SHA1',
    "CreatedAt" TEXT NOT NULL,
    "Digits" INTEGER NOT NULL DEFAULT 6,
    "IsDeleted" INTEGER NOT NULL,
    "ItemId" TEXT COLLATE NOCASE NOT NULL,
    "Name" TEXT NOT NULL,
    "Period" INTEGER NOT NULL DEFAULT 30,
    "SecretKey" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    CONSTRAINT "PK_TotpCodes" PRIMARY KEY ("ManifestId", "Id"),
    CONSTRAINT "FK_TotpCodes_Items_ManifestId_ItemId" FOREIGN KEY ("ManifestId", "ItemId") REFERENCES "Items" ("ManifestId", "Id") ON DELETE CASCADE
);

INSERT INTO "ef_temp_TotpCodes" ("ManifestId", "Id", "Algorithm", "CreatedAt", "Digits", "IsDeleted", "ItemId", "Name", "Period", "SecretKey", "UpdatedAt")
SELECT "ManifestId", "Id", "Algorithm", "CreatedAt", "Digits", "IsDeleted", "ItemId", "Name", "Period", "SecretKey", "UpdatedAt"
FROM "TotpCodes";

CREATE TABLE "ef_temp_Tags" (
    "ManifestId" TEXT COLLATE NOCASE NOT NULL,
    "Id" TEXT COLLATE NOCASE NOT NULL,
    "Color" TEXT NULL,
    "CreatedAt" TEXT NOT NULL,
    "DisplayOrder" INTEGER NOT NULL,
    "IsDeleted" INTEGER NOT NULL,
    "Name" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    CONSTRAINT "PK_Tags" PRIMARY KEY ("ManifestId", "Id")
);

INSERT INTO "ef_temp_Tags" ("ManifestId", "Id", "Color", "CreatedAt", "DisplayOrder", "IsDeleted", "Name", "UpdatedAt")
SELECT "ManifestId", "Id", "Color", "CreatedAt", "DisplayOrder", "IsDeleted", "Name", "UpdatedAt"
FROM "Tags";

CREATE TABLE "ef_temp_Settings" (
    "ManifestId" TEXT COLLATE NOCASE NOT NULL,
    "Key" TEXT NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "Value" TEXT NULL,
    CONSTRAINT "PK_Settings" PRIMARY KEY ("ManifestId", "Key")
);

INSERT INTO "ef_temp_Settings" ("ManifestId", "Key", "CreatedAt", "IsDeleted", "UpdatedAt", "Value")
SELECT "ManifestId", "Key", "CreatedAt", "IsDeleted", "UpdatedAt", "Value"
FROM "Settings";

CREATE TABLE "ef_temp_Logos" (
    "ManifestId" TEXT COLLATE NOCASE NOT NULL,
    "Id" TEXT COLLATE NOCASE NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "FetchedAt" TEXT NULL,
    "FileData" BLOB NULL,
    "FileDataHash" TEXT NULL,
    "IsDeleted" INTEGER NOT NULL,
    "Kind" TEXT NOT NULL DEFAULT 'favicon',
    "MimeType" TEXT NULL,
    "Name" TEXT NULL,
    "Source" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    CONSTRAINT "PK_Logos" PRIMARY KEY ("ManifestId", "Id")
);

INSERT INTO "ef_temp_Logos" ("ManifestId", "Id", "CreatedAt", "FetchedAt", "FileData", "FileDataHash", "IsDeleted", "Kind", "MimeType", "Name", "Source", "UpdatedAt")
SELECT "ManifestId", "Id", "CreatedAt", "FetchedAt", "FileData", "FileDataHash", "IsDeleted", "Kind", "MimeType", "Name", "Source", "UpdatedAt"
FROM "Logos";

CREATE TABLE "ef_temp_FieldDefinitions" (
    "ManifestId" TEXT COLLATE NOCASE NOT NULL,
    "Id" TEXT COLLATE NOCASE NOT NULL,
    "ApplicableToTypes" TEXT NULL,
    "CreatedAt" TEXT NOT NULL,
    "EnableHistory" INTEGER NOT NULL,
    "FieldType" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL,
    "IsHidden" INTEGER NOT NULL,
    "IsMultiValue" INTEGER NOT NULL,
    "Label" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    "Weight" INTEGER NOT NULL,
    CONSTRAINT "PK_FieldDefinitions" PRIMARY KEY ("ManifestId", "Id")
);

INSERT INTO "ef_temp_FieldDefinitions" ("ManifestId", "Id", "ApplicableToTypes", "CreatedAt", "EnableHistory", "FieldType", "IsDeleted", "IsHidden", "IsMultiValue", "Label", "UpdatedAt", "Weight")
SELECT "ManifestId", "Id", "ApplicableToTypes", "CreatedAt", "EnableHistory", "FieldType", "IsDeleted", "IsHidden", "IsMultiValue", "Label", "UpdatedAt", "Weight"
FROM "FieldDefinitions";

CREATE TABLE "ef_temp_EncryptionKeys" (
    "ManifestId" TEXT COLLATE NOCASE NOT NULL,
    "Id" TEXT COLLATE NOCASE NOT NULL,
    "CreatedAt" TEXT NOT NULL,
    "IsDeleted" INTEGER NOT NULL,
    "IsPrimary" INTEGER NOT NULL,
    "PrivateKey" TEXT NOT NULL,
    "PublicKey" TEXT NOT NULL,
    "UpdatedAt" TEXT NOT NULL,
    CONSTRAINT "PK_EncryptionKeys" PRIMARY KEY ("ManifestId", "Id")
);

INSERT INTO "ef_temp_EncryptionKeys" ("ManifestId", "Id", "CreatedAt", "IsDeleted", "IsPrimary", "PrivateKey", "PublicKey", "UpdatedAt")
SELECT "ManifestId", "Id", "CreatedAt", "IsDeleted", "IsPrimary", "PrivateKey", "PublicKey", "UpdatedAt"
FROM "EncryptionKeys";

COMMIT;

PRAGMA foreign_keys = 0;

BEGIN TRANSACTION;
DROP TABLE "Attachments";

ALTER TABLE "ef_temp_Attachments" RENAME TO "Attachments";

DROP TABLE "FieldHistories";

ALTER TABLE "ef_temp_FieldHistories" RENAME TO "FieldHistories";

DROP TABLE "FieldValues";

ALTER TABLE "ef_temp_FieldValues" RENAME TO "FieldValues";

DROP TABLE "Folders";

ALTER TABLE "ef_temp_Folders" RENAME TO "Folders";

DROP TABLE "Items";

ALTER TABLE "ef_temp_Items" RENAME TO "Items";

DROP TABLE "ItemTags";

ALTER TABLE "ef_temp_ItemTags" RENAME TO "ItemTags";

DROP TABLE "Passkeys";

ALTER TABLE "ef_temp_Passkeys" RENAME TO "Passkeys";

DROP TABLE "TotpCodes";

ALTER TABLE "ef_temp_TotpCodes" RENAME TO "TotpCodes";

DROP TABLE "Tags";

ALTER TABLE "ef_temp_Tags" RENAME TO "Tags";

DROP TABLE "Settings";

ALTER TABLE "ef_temp_Settings" RENAME TO "Settings";

DROP TABLE "Logos";

ALTER TABLE "ef_temp_Logos" RENAME TO "Logos";

DROP TABLE "FieldDefinitions";

ALTER TABLE "ef_temp_FieldDefinitions" RENAME TO "FieldDefinitions";

DROP TABLE "EncryptionKeys";

ALTER TABLE "ef_temp_EncryptionKeys" RENAME TO "EncryptionKeys";

COMMIT;

PRAGMA foreign_keys = 1;

BEGIN TRANSACTION;
CREATE INDEX "IX_Attachments_ManifestId_ItemId" ON "Attachments" ("ManifestId", "ItemId");

CREATE INDEX "IX_FieldHistories_FieldDefinitionId" ON "FieldHistories" ("FieldDefinitionId");

CREATE INDEX "IX_FieldHistories_ItemId" ON "FieldHistories" ("ItemId");

CREATE INDEX "IX_FieldHistories_ManifestId_FieldDefinitionId" ON "FieldHistories" ("ManifestId", "FieldDefinitionId");

CREATE INDEX "IX_FieldHistories_ManifestId_ItemId" ON "FieldHistories" ("ManifestId", "ItemId");

CREATE INDEX "IX_FieldValues_FieldDefinitionId" ON "FieldValues" ("FieldDefinitionId");

CREATE INDEX "IX_FieldValues_FieldKey" ON "FieldValues" ("FieldKey");

CREATE INDEX "IX_FieldValues_ItemId" ON "FieldValues" ("ItemId");

CREATE INDEX "IX_FieldValues_ItemId_FieldDefinitionId_Weight" ON "FieldValues" ("ItemId", "FieldDefinitionId", "Weight");

CREATE INDEX "IX_FieldValues_ItemId_FieldKey" ON "FieldValues" ("ItemId", "FieldKey");

CREATE INDEX "IX_FieldValues_ManifestId_FieldDefinitionId" ON "FieldValues" ("ManifestId", "FieldDefinitionId");

CREATE INDEX "IX_FieldValues_ManifestId_ItemId" ON "FieldValues" ("ManifestId", "ItemId");

CREATE INDEX "IX_Folders_ManifestId_ParentFolderId" ON "Folders" ("ManifestId", "ParentFolderId");

CREATE INDEX "IX_Folders_ParentFolderId" ON "Folders" ("ParentFolderId");

CREATE INDEX "IX_Items_ManifestId_FolderId" ON "Items" ("ManifestId", "FolderId");

CREATE INDEX "IX_Items_ManifestId_LogoId" ON "Items" ("ManifestId", "LogoId");

CREATE INDEX "IX_ItemTags_ItemId" ON "ItemTags" ("ItemId");

CREATE INDEX "IX_ItemTags_ManifestId_TagId" ON "ItemTags" ("ManifestId", "TagId");

CREATE INDEX "IX_ItemTags_TagId" ON "ItemTags" ("TagId");

CREATE INDEX "IX_Passkeys_ManifestId_ItemId" ON "Passkeys" ("ManifestId", "ItemId");

CREATE INDEX "IX_Passkeys_RpId" ON "Passkeys" ("RpId");

CREATE INDEX "IX_TotpCodes_ManifestId_ItemId" ON "TotpCodes" ("ManifestId", "ItemId");

CREATE INDEX "IX_Tags_ManifestId_Name" ON "Tags" ("ManifestId", "Name");

CREATE UNIQUE INDEX "IX_Logos_ManifestId_Kind_Source" ON "Logos" ("ManifestId", "Kind", "Source");

CREATE INDEX "IX_EncryptionKeys_ManifestId_IsPrimary" ON "EncryptionKeys" ("ManifestId", "IsPrimary");

COMMIT;

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20261009150000_2.1.0-ManifestScopedStorage', '10.0.12');

BEGIN TRANSACTION;
CREATE TRIGGER IF NOT EXISTS "TR_Items_ClearTombstoneBeforeReturn"
BEFORE UPDATE OF "ManifestId" ON "Items"
FOR EACH ROW WHEN OLD."ManifestId" <> NEW."ManifestId"
    AND EXISTS (SELECT 1 FROM "Items" WHERE "ManifestId" = NEW."ManifestId" AND "Id" = NEW."Id" AND "IsDeleted" = 1)
BEGIN
    DELETE FROM "FieldValues" WHERE "ItemId" = NEW."Id" AND "ManifestId" = NEW."ManifestId";
    DELETE FROM "FieldHistories" WHERE "ItemId" = NEW."Id" AND "ManifestId" = NEW."ManifestId";
    DELETE FROM "ItemTags" WHERE "ItemId" = NEW."Id" AND "ManifestId" = NEW."ManifestId";
    DELETE FROM "Attachments" WHERE "ItemId" = NEW."Id" AND "ManifestId" = NEW."ManifestId";
    DELETE FROM "Passkeys" WHERE "ItemId" = NEW."Id" AND "ManifestId" = NEW."ManifestId";
    DELETE FROM "TotpCodes" WHERE "ItemId" = NEW."Id" AND "ManifestId" = NEW."ManifestId";
    DELETE FROM "ItemStats" WHERE "Id" = NEW."Id" AND "ManifestId" = NEW."ManifestId";
    DELETE FROM "Items" WHERE "ManifestId" = NEW."ManifestId" AND "Id" = NEW."Id" AND "IsDeleted" = 1;
END;

CREATE TRIGGER IF NOT EXISTS "TR_Items_ResyncChildManifestIds"
AFTER UPDATE OF "ManifestId" ON "Items"
FOR EACH ROW WHEN OLD."ManifestId" <> NEW."ManifestId"
BEGIN
    INSERT INTO "Tags" ("ManifestId", "Id", "Name", "Color", "DisplayOrder", "CreatedAt", "UpdatedAt", "IsDeleted")
    SELECT NEW."ManifestId", "Id", "Name", "Color", "DisplayOrder", "CreatedAt", NEW."UpdatedAt", "IsDeleted" FROM "Tags"
    WHERE "ManifestId" = OLD."ManifestId" AND "Id" IN (SELECT "TagId" FROM "ItemTags" WHERE "ManifestId" = OLD."ManifestId" AND "ItemId" = NEW."Id")
    ON CONFLICT ("ManifestId", "Id") DO UPDATE SET "Name" = excluded."Name", "Color" = excluded."Color", "DisplayOrder" = excluded."DisplayOrder", "UpdatedAt" = excluded."UpdatedAt", "IsDeleted" = 0 WHERE "IsDeleted" = 1 AND excluded."IsDeleted" = 0;
    INSERT INTO "FieldDefinitions" ("ManifestId", "Id", "FieldType", "Label", "IsMultiValue", "IsHidden", "EnableHistory", "Weight", "ApplicableToTypes", "CreatedAt", "UpdatedAt", "IsDeleted")
    SELECT NEW."ManifestId", "Id", "FieldType", "Label", "IsMultiValue", "IsHidden", "EnableHistory", "Weight", "ApplicableToTypes", "CreatedAt", NEW."UpdatedAt", "IsDeleted" FROM "FieldDefinitions"
    WHERE "ManifestId" = OLD."ManifestId" AND "Id" IN (SELECT "FieldDefinitionId" FROM "FieldValues" WHERE "ManifestId" = OLD."ManifestId" AND "ItemId" = NEW."Id" UNION SELECT "FieldDefinitionId" FROM "FieldHistories" WHERE "ManifestId" = OLD."ManifestId" AND "ItemId" = NEW."Id")
    ON CONFLICT ("ManifestId", "Id") DO UPDATE SET "FieldType" = excluded."FieldType", "Label" = excluded."Label", "IsMultiValue" = excluded."IsMultiValue", "IsHidden" = excluded."IsHidden", "EnableHistory" = excluded."EnableHistory", "Weight" = excluded."Weight", "ApplicableToTypes" = excluded."ApplicableToTypes", "UpdatedAt" = excluded."UpdatedAt", "IsDeleted" = 0 WHERE "IsDeleted" = 1 AND excluded."IsDeleted" = 0;
    UPDATE "FieldValues" SET "ManifestId" = NEW."ManifestId" WHERE "ItemId" = NEW."Id" AND "ManifestId" = OLD."ManifestId";
    UPDATE "FieldHistories" SET "ManifestId" = NEW."ManifestId" WHERE "ItemId" = NEW."Id" AND "ManifestId" = OLD."ManifestId";
    UPDATE "ItemTags" SET "ManifestId" = NEW."ManifestId" WHERE "ItemId" = NEW."Id" AND "ManifestId" = OLD."ManifestId";
    UPDATE "Attachments" SET "ManifestId" = NEW."ManifestId" WHERE "ItemId" = NEW."Id" AND "ManifestId" = OLD."ManifestId";
    UPDATE "Passkeys" SET "ManifestId" = NEW."ManifestId" WHERE "ItemId" = NEW."Id" AND "ManifestId" = OLD."ManifestId";
    UPDATE "TotpCodes" SET "ManifestId" = NEW."ManifestId" WHERE "ItemId" = NEW."Id" AND "ManifestId" = OLD."ManifestId";
    UPDATE "ItemStats" SET "ManifestId" = NEW."ManifestId" WHERE "Id" = NEW."Id" AND "ManifestId" = OLD."ManifestId";
    INSERT OR IGNORE INTO "Items" ("ManifestId", "Id", "ItemType", "CreatedAt", "UpdatedAt", "IsDeleted")
    SELECT OLD."ManifestId", OLD."Id", OLD."ItemType", OLD."CreatedAt", NEW."UpdatedAt", 1 WHERE OLD."IsDeleted" = 0;
END;

COMMIT;

PRAGMA foreign_keys = ON;

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20261009150100_2.1.1-ItemChildManifestTrigger', '10.0.12');