dofus-manager/prisma/migrations/20260119121104_init/migration.sql

-- CreateEnum
CREATE TYPE "TeamType" AS ENUM ('MAIN', 'SECONDARY', 'CUSTOM');

-- CreateEnum
CREATE TYPE "ProgressionType" AS ENUM ('QUEST', 'DUNGEON', 'ACHIEVEMENT', 'DOFUS');

-- CreateTable
CREATE TABLE "users" (
    "id" TEXT NOT NULL,
    "email" TEXT NOT NULL,
    "password_hash" TEXT NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "users_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "sessions" (
    "id" TEXT NOT NULL,
    "user_id" TEXT NOT NULL,
    "expires_at" TIMESTAMP(3) NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "sessions_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "accounts" (
    "id" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "user_id" TEXT NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "accounts_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "characters" (
    "id" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "level" INTEGER NOT NULL DEFAULT 1,
    "class_id" INTEGER NOT NULL,
    "class_name" TEXT NOT NULL,
    "server_id" INTEGER NOT NULL,
    "server_name" TEXT NOT NULL,
    "account_id" TEXT NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "characters_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "teams" (
    "id" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "type" "TeamType" NOT NULL DEFAULT 'CUSTOM',
    "user_id" TEXT NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "teams_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "team_members" (
    "id" TEXT NOT NULL,
    "team_id" TEXT NOT NULL,
    "character_id" TEXT NOT NULL,
    "joined_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "team_members_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "progressions" (
    "id" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "type" "ProgressionType" NOT NULL,
    "category" TEXT NOT NULL,
    "dofusdb_id" INTEGER,

    CONSTRAINT "progressions_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "character_progressions" (
    "id" TEXT NOT NULL,
    "character_id" TEXT NOT NULL,
    "progression_id" TEXT NOT NULL,
    "completed" BOOLEAN NOT NULL DEFAULT false,
    "completed_at" TIMESTAMP(3),

    CONSTRAINT "character_progressions_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "users_email_key" ON "users"("email");

-- CreateIndex
CREATE INDEX "sessions_user_id_idx" ON "sessions"("user_id");

-- CreateIndex
CREATE INDEX "sessions_expires_at_idx" ON "sessions"("expires_at");

-- CreateIndex
CREATE INDEX "accounts_user_id_idx" ON "accounts"("user_id");

-- CreateIndex
CREATE UNIQUE INDEX "accounts_user_id_name_key" ON "accounts"("user_id", "name");

-- CreateIndex
CREATE INDEX "characters_account_id_idx" ON "characters"("account_id");

-- CreateIndex
CREATE INDEX "characters_class_id_idx" ON "characters"("class_id");

-- CreateIndex
CREATE INDEX "characters_server_id_idx" ON "characters"("server_id");

-- CreateIndex
CREATE INDEX "characters_level_idx" ON "characters"("level");

-- CreateIndex
CREATE UNIQUE INDEX "characters_account_id_name_key" ON "characters"("account_id", "name");

-- CreateIndex
CREATE INDEX "teams_user_id_idx" ON "teams"("user_id");

-- CreateIndex
CREATE UNIQUE INDEX "teams_user_id_name_key" ON "teams"("user_id", "name");

-- CreateIndex
CREATE INDEX "team_members_team_id_idx" ON "team_members"("team_id");

-- CreateIndex
CREATE INDEX "team_members_character_id_idx" ON "team_members"("character_id");

-- CreateIndex
CREATE UNIQUE INDEX "team_members_team_id_character_id_key" ON "team_members"("team_id", "character_id");

-- CreateIndex
CREATE UNIQUE INDEX "progressions_dofusdb_id_key" ON "progressions"("dofusdb_id");

-- CreateIndex
CREATE INDEX "progressions_type_idx" ON "progressions"("type");

-- CreateIndex
CREATE INDEX "progressions_category_idx" ON "progressions"("category");

-- CreateIndex
CREATE INDEX "character_progressions_character_id_idx" ON "character_progressions"("character_id");

-- CreateIndex
CREATE INDEX "character_progressions_progression_id_idx" ON "character_progressions"("progression_id");

-- CreateIndex
CREATE INDEX "character_progressions_completed_idx" ON "character_progressions"("completed");

-- CreateIndex
CREATE UNIQUE INDEX "character_progressions_character_id_progression_id_key" ON "character_progressions"("character_id", "progression_id");

-- AddForeignKey
ALTER TABLE "sessions" ADD CONSTRAINT "sessions_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "accounts" ADD CONSTRAINT "accounts_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "characters" ADD CONSTRAINT "characters_account_id_fkey" FOREIGN KEY ("account_id") REFERENCES "accounts"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "teams" ADD CONSTRAINT "teams_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "team_members" ADD CONSTRAINT "team_members_team_id_fkey" FOREIGN KEY ("team_id") REFERENCES "teams"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "team_members" ADD CONSTRAINT "team_members_character_id_fkey" FOREIGN KEY ("character_id") REFERENCES "characters"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "character_progressions" ADD CONSTRAINT "character_progressions_character_id_fkey" FOREIGN KEY ("character_id") REFERENCES "characters"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "character_progressions" ADD CONSTRAINT "character_progressions_progression_id_fkey" FOREIGN KEY ("progression_id") REFERENCES "progressions"("id") ON DELETE CASCADE ON UPDATE CASCADE;