-- Project hour rate and acceptance timestamps
ALTER TABLE "Project" ADD COLUMN "hourRate" DECIMAL(18,2) NOT NULL DEFAULT 200;
ALTER TABLE "Project" ADD COLUMN "internalAcceptedAt" TIMESTAMP(3);
ALTER TABLE "Project" ADD COLUMN "customerAcceptedAt" TIMESTAMP(3);

-- ProjectTask WBS
ALTER TABLE "ProjectTask" ADD COLUMN "departmentId" TEXT;
ALTER TABLE "ProjectTask" ADD COLUMN "assigneeId" TEXT;
ALTER TABLE "ProjectTask" ADD COLUMN "sortNo" INTEGER NOT NULL DEFAULT 0;
ALTER TABLE "ProjectTask" ADD COLUMN "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP;

CREATE INDEX "ProjectTask_projectId_idx" ON "ProjectTask"("projectId");
ALTER TABLE "ProjectTask" ADD CONSTRAINT "ProjectTask_departmentId_fkey" FOREIGN KEY ("departmentId") REFERENCES "Department"("id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "ProjectTask" ADD CONSTRAINT "ProjectTask_assigneeId_fkey" FOREIGN KEY ("assigneeId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- Timesheet
ALTER TABLE "Timesheet" ADD COLUMN "taskId" TEXT;
ALTER TABLE "Timesheet" ADD COLUMN "costAmount" DECIMAL(18,2) NOT NULL DEFAULT 0;
ALTER TABLE "Timesheet" ADD COLUMN "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP;

CREATE INDEX "Timesheet_projectId_idx" ON "Timesheet"("projectId");
CREATE INDEX "Timesheet_userId_idx" ON "Timesheet"("userId");
ALTER TABLE "Timesheet" ADD CONSTRAINT "Timesheet_taskId_fkey" FOREIGN KEY ("taskId") REFERENCES "ProjectTask"("id") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "Timesheet" ADD CONSTRAINT "Timesheet_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- Expense budget category (travel / purchase)
ALTER TABLE "ExpenseClaim" ADD COLUMN "budgetCategory" TEXT;

-- ProjectBudget
CREATE TABLE "ProjectBudget" (
    "id" TEXT NOT NULL,
    "projectId" TEXT NOT NULL,
    "category" TEXT NOT NULL,
    "amount" DECIMAL(18,2) NOT NULL,

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

CREATE UNIQUE INDEX "ProjectBudget_projectId_category_key" ON "ProjectBudget"("projectId", "category");
ALTER TABLE "ProjectBudget" ADD CONSTRAINT "ProjectBudget_projectId_fkey" FOREIGN KEY ("projectId") REFERENCES "Project"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- ProjectAcceptance
CREATE TABLE "ProjectAcceptance" (
    "id" TEXT NOT NULL,
    "projectId" TEXT NOT NULL,
    "kind" TEXT NOT NULL,
    "result" TEXT NOT NULL,
    "comment" TEXT,
    "actorId" TEXT NOT NULL,
    "decidedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

CREATE INDEX "ProjectAcceptance_projectId_idx" ON "ProjectAcceptance"("projectId");
ALTER TABLE "ProjectAcceptance" ADD CONSTRAINT "ProjectAcceptance_projectId_fkey" FOREIGN KEY ("projectId") REFERENCES "Project"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "ProjectAcceptance" ADD CONSTRAINT "ProjectAcceptance_actorId_fkey" FOREIGN KEY ("actorId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;