-- CreateTable
CREATE TABLE "user" (
    "id" TEXT NOT NULL,
    "name" TEXT,
    "status" TEXT,
    "dept_code" TEXT,
    "created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ NOT NULL,
    "password" TEXT NOT NULL,
    "is_super_admin" BOOLEAN NOT NULL DEFAULT false,
    "last_login_at" TIMESTAMPTZ,

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

-- CreateTable
CREATE TABLE "dept" (
    "code" TEXT NOT NULL,
    "name" TEXT NOT NULL DEFAULT '',
    "full_name" TEXT NOT NULL DEFAULT '',

    CONSTRAINT "dept_pkey" PRIMARY KEY ("code")
);

-- CreateTable
CREATE TABLE "role" (
    "id" SERIAL NOT NULL,
    "name" TEXT NOT NULL,

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

-- CreateTable
CREATE TABLE "permission" (
    "id" SERIAL NOT NULL,
    "name" TEXT NOT NULL,

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

-- CreateTable
CREATE TABLE "selection_log" (
    "id" SERIAL NOT NULL,
    "userId" TEXT NOT NULL,
    "context" TEXT NOT NULL,
    "option_id" TEXT NOT NULL,
    "created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "dev_file_type" (
    "id" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "description" TEXT NOT NULL,
    "order" INTEGER NOT NULL,

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

-- CreateTable
CREATE TABLE "dev_pkg_type" (
    "id" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "description" TEXT NOT NULL,
    "order" INTEGER NOT NULL,

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

-- CreateTable
CREATE TABLE "dev_analyzed_file" (
    "id" SERIAL NOT NULL,
    "path" TEXT NOT NULL,
    "fileName" TEXT NOT NULL,
    "commit_id" TEXT NOT NULL DEFAULT '',
    "content" TEXT,
    "file_type_id" TEXT NOT NULL,
    "summary" TEXT NOT NULL,
    "description" TEXT NOT NULL,
    "exportedMembers" JSONB,
    "tags" TEXT[],
    "lastAnalyzedAt" TIMESTAMPTZ NOT NULL,
    "createdAt" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "dev_analyzed_pkg" (
    "name" TEXT NOT NULL,
    "version" TEXT NOT NULL,
    "modifiedAt" TIMESTAMP(3) NOT NULL,
    "description" TEXT NOT NULL,
    "homepage" TEXT,
    "repository_url" TEXT,
    "pkg_type_id" TEXT NOT NULL,
    "projectRoleSummary" TEXT NOT NULL,
    "primaryUsagePattern" TEXT NOT NULL,
    "relatedFiles" TEXT[],
    "relatedFileCount" INTEGER NOT NULL,
    "last_analyzed_at" TIMESTAMPTZ NOT NULL,
    "created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "dev_analyzed_pkg_pkey" PRIMARY KEY ("name")
);

-- CreateTable
CREATE TABLE "dev_file_dependency" (
    "id" SERIAL NOT NULL,
    "source_file_id" INTEGER NOT NULL,
    "target_file_path" TEXT NOT NULL,
    "usage_description" TEXT,
    "created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "dev_file_pkg_dependency" (
    "id" SERIAL NOT NULL,
    "source_file_id" INTEGER NOT NULL,
    "package_name" TEXT NOT NULL,
    "usage_description" TEXT,
    "created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "dev_analyzed_folder" (
    "path" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "summary" TEXT NOT NULL,
    "description" TEXT NOT NULL,
    "last_analyzed_at" TIMESTAMPTZ NOT NULL,
    "created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "dev_analyzed_folder_pkey" PRIMARY KEY ("path")
);

-- CreateTable
CREATE TABLE "_RoleToUser" (
    "A" INTEGER NOT NULL,
    "B" TEXT NOT NULL,

    CONSTRAINT "_RoleToUser_AB_pkey" PRIMARY KEY ("A","B")
);

-- CreateTable
CREATE TABLE "_PermissionToRole" (
    "A" INTEGER NOT NULL,
    "B" INTEGER NOT NULL,

    CONSTRAINT "_PermissionToRole_AB_pkey" PRIMARY KEY ("A","B")
);

-- CreateIndex
CREATE UNIQUE INDEX "role_name_key" ON "role"("name");

-- CreateIndex
CREATE UNIQUE INDEX "permission_name_key" ON "permission"("name");

-- CreateIndex
CREATE INDEX "selection_log_userId_context_idx" ON "selection_log"("userId", "context");

-- CreateIndex
CREATE INDEX "selection_log_context_option_id_idx" ON "selection_log"("context", "option_id");

-- CreateIndex
CREATE UNIQUE INDEX "dev_analyzed_file_path_commit_id_key" ON "dev_analyzed_file"("path", "commit_id");

-- CreateIndex
CREATE INDEX "dev_analyzed_pkg_pkg_type_id_idx" ON "dev_analyzed_pkg"("pkg_type_id");

-- CreateIndex
CREATE INDEX "dev_file_dependency_target_file_path_idx" ON "dev_file_dependency"("target_file_path");

-- CreateIndex
CREATE UNIQUE INDEX "dev_file_dependency_source_file_id_target_file_path_key" ON "dev_file_dependency"("source_file_id", "target_file_path");

-- CreateIndex
CREATE INDEX "dev_file_pkg_dependency_package_name_idx" ON "dev_file_pkg_dependency"("package_name");

-- CreateIndex
CREATE UNIQUE INDEX "dev_file_pkg_dependency_source_file_id_package_name_key" ON "dev_file_pkg_dependency"("source_file_id", "package_name");

-- CreateIndex
CREATE INDEX "_RoleToUser_B_index" ON "_RoleToUser"("B");

-- CreateIndex
CREATE INDEX "_PermissionToRole_B_index" ON "_PermissionToRole"("B");

-- AddForeignKey
ALTER TABLE "user" ADD CONSTRAINT "user_dept_code_fkey" FOREIGN KEY ("dept_code") REFERENCES "dept"("code") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "selection_log" ADD CONSTRAINT "selection_log_userId_fkey" FOREIGN KEY ("userId") REFERENCES "user"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "dev_analyzed_file" ADD CONSTRAINT "dev_analyzed_file_file_type_id_fkey" FOREIGN KEY ("file_type_id") REFERENCES "dev_file_type"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "dev_analyzed_pkg" ADD CONSTRAINT "dev_analyzed_pkg_pkg_type_id_fkey" FOREIGN KEY ("pkg_type_id") REFERENCES "dev_pkg_type"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "dev_file_dependency" ADD CONSTRAINT "dev_file_dependency_source_file_id_fkey" FOREIGN KEY ("source_file_id") REFERENCES "dev_analyzed_file"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "dev_file_pkg_dependency" ADD CONSTRAINT "dev_file_pkg_dependency_source_file_id_fkey" FOREIGN KEY ("source_file_id") REFERENCES "dev_analyzed_file"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "_RoleToUser" ADD CONSTRAINT "_RoleToUser_A_fkey" FOREIGN KEY ("A") REFERENCES "role"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "_RoleToUser" ADD CONSTRAINT "_RoleToUser_B_fkey" FOREIGN KEY ("B") REFERENCES "user"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "_PermissionToRole" ADD CONSTRAINT "_PermissionToRole_A_fkey" FOREIGN KEY ("A") REFERENCES "permission"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "_PermissionToRole" ADD CONSTRAINT "_PermissionToRole_B_fkey" FOREIGN KEY ("B") REFERENCES "role"("id") ON DELETE CASCADE ON UPDATE CASCADE;