-- Enums
CREATE TYPE "Role" AS ENUM ('USER', 'ADMIN', 'SUPER_ADMIN');

CREATE TYPE "TaskStatus" AS ENUM ('IN_PROGRESS', 'UNDER_REVIEW', 'COMPLETED', 'REVISION');

CREATE TYPE "MemberStatus" AS ENUM ('PENDING', 'SUBMITTED');

-- Departments
CREATE TABLE "Department" (
    "id"         SERIAL       NOT NULL,
    "name"       TEXT         NOT NULL,
    "createdAt"  TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt"  TIMESTAMP(3) NOT NULL,

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

-- Users
CREATE TABLE "User" (
    "id"                SERIAL       NOT NULL,
    "name"              TEXT         NOT NULL,
    "badgeNo"           TEXT         NOT NULL,
    "passwordHash"      TEXT         NOT NULL,
    "isInitialPassword" BOOLEAN      NOT NULL DEFAULT TRUE,
    "role"              "Role"       NOT NULL DEFAULT 'USER',
    "departmentId"      INTEGER,
    "createdAt"         TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt"         TIMESTAMP(3) NOT NULL,

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

-- Tasks
CREATE TABLE "Task" (
    "id"          SERIAL       NOT NULL,
    "title"       TEXT         NOT NULL,
    "description" TEXT         NOT NULL,
    "status"      "TaskStatus" NOT NULL DEFAULT 'IN_PROGRESS',
    "dueDate"     TIMESTAMP(3),
    "creatorId"   INTEGER      NOT NULL,
    "createdAt"   TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt"   TIMESTAMP(3) NOT NULL,

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

-- Task members
CREATE TABLE "TaskMember" (
    "id"         SERIAL         NOT NULL,
    "taskId"     INTEGER        NOT NULL,
    "userId"     INTEGER        NOT NULL,
    "status"     "MemberStatus" NOT NULL DEFAULT 'PENDING',
    "createdAt"  TIMESTAMP(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt"  TIMESTAMP(3)   NOT NULL,

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

-- Attachments
CREATE TABLE "Attachment" (
    "id"         SERIAL       NOT NULL,
    "taskId"     INTEGER      NOT NULL,
    "uploaderId" INTEGER      NOT NULL,
    "filename"   TEXT         NOT NULL,
    "objectKey"  TEXT         NOT NULL,
    "createdAt"  TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- Comments
CREATE TABLE "Comment" (
    "id"        SERIAL       NOT NULL,
    "taskId"    INTEGER      NOT NULL,
    "authorId"  INTEGER      NOT NULL,
    "content"   TEXT         NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- Task status history
CREATE TABLE "TaskStatusHistory" (
    "id"         SERIAL       NOT NULL,
    "taskId"     INTEGER      NOT NULL,
    "operatorId" INTEGER      NOT NULL,
    "fromStatus" "TaskStatus",
    "toStatus"   "TaskStatus" NOT NULL,
    "operation"  TEXT         NOT NULL,
    "remark"     TEXT,
    "createdAt"  TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- Many-to-many: Task responsible users
CREATE TABLE "_TaskResponsibleUsers" (
    "A" INTEGER NOT NULL,
    "B" INTEGER NOT NULL,

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

-- Many-to-many: Task <-> Department
CREATE TABLE "TaskDepartment" (
    "taskId"       INTEGER NOT NULL,
    "departmentId" INTEGER NOT NULL,

    CONSTRAINT "TaskDepartment_pkey" PRIMARY KEY ("taskId","departmentId")
);

-- Indexes
CREATE UNIQUE INDEX "Department_name_key" ON "Department"("name");

CREATE UNIQUE INDEX "User_badgeNo_key" ON "User"("badgeNo");

CREATE UNIQUE INDEX "TaskMember_taskId_userId_key"
  ON "TaskMember"("taskId","userId");

CREATE INDEX "_TaskResponsibleUsers_B_index"
  ON "_TaskResponsibleUsers"("B");

-- Foreign keys
ALTER TABLE "User"
  ADD CONSTRAINT "User_departmentId_fkey"
  FOREIGN KEY ("departmentId") REFERENCES "Department"("id")
  ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE "Task"
  ADD CONSTRAINT "Task_creatorId_fkey"
  FOREIGN KEY ("creatorId") REFERENCES "User"("id")
  ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE "TaskMember"
  ADD CONSTRAINT "TaskMember_taskId_fkey"
  FOREIGN KEY ("taskId") REFERENCES "Task"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "TaskMember"
  ADD CONSTRAINT "TaskMember_userId_fkey"
  FOREIGN KEY ("userId") REFERENCES "User"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "Attachment"
  ADD CONSTRAINT "Attachment_taskId_fkey"
  FOREIGN KEY ("taskId") REFERENCES "Task"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "Attachment"
  ADD CONSTRAINT "Attachment_uploaderId_fkey"
  FOREIGN KEY ("uploaderId") REFERENCES "User"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "Comment"
  ADD CONSTRAINT "Comment_taskId_fkey"
  FOREIGN KEY ("taskId") REFERENCES "Task"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "Comment"
  ADD CONSTRAINT "Comment_authorId_fkey"
  FOREIGN KEY ("authorId") REFERENCES "User"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "TaskStatusHistory"
  ADD CONSTRAINT "TaskStatusHistory_taskId_fkey"
  FOREIGN KEY ("taskId") REFERENCES "Task"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "TaskStatusHistory"
  ADD CONSTRAINT "TaskStatusHistory_operatorId_fkey"
  FOREIGN KEY ("operatorId") REFERENCES "User"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "_TaskResponsibleUsers"
  ADD CONSTRAINT "_TaskResponsibleUsers_A_fkey"
  FOREIGN KEY ("A") REFERENCES "Task"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "_TaskResponsibleUsers"
  ADD CONSTRAINT "_TaskResponsibleUsers_B_fkey"
  FOREIGN KEY ("B") REFERENCES "User"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "TaskDepartment"
  ADD CONSTRAINT "TaskDepartment_taskId_fkey"
  FOREIGN KEY ("taskId") REFERENCES "Task"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "TaskDepartment"
  ADD CONSTRAINT "TaskDepartment_departmentId_fkey"
  FOREIGN KEY ("departmentId") REFERENCES "Department"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;
