CREATE TABLE "KanbanBoard" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "ownerId" BIGINT NOT NULL,
    "title" TEXT NOT NULL,
    "createdAt" BIGINT NOT NULL,
    "updatedAt" BIGINT NOT NULL,
    CONSTRAINT "KanbanBoard_ownerId_fkey"
        FOREIGN KEY ("ownerId") REFERENCES "User" ("id")
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE "KanbanColumn" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "boardId" INTEGER NOT NULL,
    "title" TEXT NOT NULL,
    "position" INTEGER NOT NULL,
    CONSTRAINT "KanbanColumn_boardId_fkey"
        FOREIGN KEY ("boardId") REFERENCES "KanbanBoard" ("id")
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE "KanbanTask" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "columnId" INTEGER NOT NULL,
    "title" TEXT NOT NULL,
    "description" TEXT,
    "position" INTEGER NOT NULL,
    "createdAt" BIGINT NOT NULL,
    "updatedAt" BIGINT NOT NULL,
    CONSTRAINT "KanbanTask_columnId_fkey"
        FOREIGN KEY ("columnId") REFERENCES "KanbanColumn" ("id")
        ON DELETE CASCADE ON UPDATE CASCADE
);

ALTER TABLE "Message" ADD COLUMN "kanbanBoardId" INTEGER
    REFERENCES "KanbanBoard" ("id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "Message" ADD COLUMN "kanbanTaskId" INTEGER
    REFERENCES "KanbanTask" ("id") ON DELETE SET NULL ON UPDATE CASCADE;

CREATE INDEX "KanbanBoard_ownerId_idx" ON "KanbanBoard" ("ownerId");
CREATE INDEX "KanbanColumn_boardId_position_idx" ON "KanbanColumn" ("boardId", "position");
CREATE INDEX "KanbanTask_columnId_position_idx" ON "KanbanTask" ("columnId", "position");
CREATE INDEX "Message_kanbanBoardId_idx" ON "Message" ("kanbanBoardId");
CREATE INDEX "Message_kanbanTaskId_idx" ON "Message" ("kanbanTaskId");