PRAGMA foreign_keys=OFF;

CREATE TABLE "new_AppAccount" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "username" TEXT NOT NULL,
    "passwordHash" TEXT,
    "passwordSetAt" DATETIME,
    "role" TEXT NOT NULL DEFAULT 'super_admin',
    "ownerId" TEXT,
    "status" TEXT NOT NULL DEFAULT 'active',
    "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" DATETIME NOT NULL,
    CONSTRAINT "AppAccount_ownerId_fkey" FOREIGN KEY ("ownerId") REFERENCES "AppAccount" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO "new_AppAccount" ("id", "username", "passwordHash", "passwordSetAt", "role", "ownerId", "status", "createdAt", "updatedAt")
SELECT "id", "username", "passwordHash", "passwordSetAt", 'super_admin', NULL, 'active', "createdAt", "updatedAt" FROM "AppAccount";

DROP TABLE "AppAccount";
ALTER TABLE "new_AppAccount" RENAME TO "AppAccount";

CREATE UNIQUE INDEX "AppAccount_username_key" ON "AppAccount"("username");
CREATE INDEX "AppAccount_role_idx" ON "AppAccount"("role");
CREATE INDEX "AppAccount_ownerId_idx" ON "AppAccount"("ownerId");
CREATE INDEX "AppAccount_status_idx" ON "AppAccount"("status");

CREATE TABLE "AppSession" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "accountId" TEXT NOT NULL,
    "expiresAt" DATETIME NOT NULL,
    "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastSeenAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "AppSession_accountId_fkey" FOREIGN KEY ("accountId") REFERENCES "AppAccount" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX "AppSession_accountId_idx" ON "AppSession"("accountId");
CREATE INDEX "AppSession_expiresAt_idx" ON "AppSession"("expiresAt");

PRAGMA foreign_keys=ON;