-- EuroQuest Management System V2
-- Additive migration. Existing agents, clients and payments are preserved.

CREATE TYPE "ManagementGroupStatus" AS ENUM ('ACTIVE', 'COMPLETED', 'ARCHIVED');
CREATE TYPE "PaymentScheduleStatus" AS ENUM ('PENDING', 'PAID', 'OVERDUE', 'WAIVED');
CREATE TYPE "RecruitmentInvoiceStatus" AS ENUM ('DRAFT', 'ISSUED', 'SENT', 'PAID', 'CANCELLED');

CREATE TABLE "recruitment_groups" (
  "id" UUID NOT NULL,
  "groupCode" VARCHAR(30) NOT NULL,
  "name" VARCHAR(150) NOT NULL,
  "description" TEXT,
  "startDate" DATE,
  "status" "ManagementGroupStatus" NOT NULL DEFAULT 'ACTIVE',
  "createdById" UUID,
  "updatedById" UUID,
  "createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMPTZ(3) NOT NULL,
  CONSTRAINT "recruitment_groups_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "recruitment_groups_groupCode_key" ON "recruitment_groups"("groupCode");
CREATE INDEX "recruitment_groups_status_createdAt_idx" ON "recruitment_groups"("status", "createdAt");

ALTER TABLE "agent_profiles"
  ADD COLUMN "managementGroupId" UUID,
  ADD COLUMN "commissionPercent" DECIMAL(5,2);

ALTER TABLE "recruitment_clients"
  ADD COLUMN "groupId" UUID,
  ADD COLUMN "workType" VARCHAR(120),
  ADD COLUMN "completionDate" DATE;

ALTER TABLE "recruitment_payments"
  ADD COLUMN "paymentType" VARCHAR(60);

CREATE TABLE "recruitment_payment_schedules" (
  "id" UUID NOT NULL,
  "clientId" UUID NOT NULL,
  "installmentNo" INTEGER NOT NULL,
  "label" VARCHAR(80) NOT NULL,
  "amount" DECIMAL(14,2) NOT NULL,
  "dueDate" DATE,
  "status" "PaymentScheduleStatus" NOT NULL DEFAULT 'PENDING',
  "createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMPTZ(3) NOT NULL,
  CONSTRAINT "recruitment_payment_schedules_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "recruitment_payment_schedules_clientId_installmentNo_key"
  ON "recruitment_payment_schedules"("clientId", "installmentNo");
CREATE INDEX "recruitment_payment_schedules_status_dueDate_idx"
  ON "recruitment_payment_schedules"("status", "dueDate");

CREATE TABLE "recruitment_invoices" (
  "id" UUID NOT NULL,
  "invoiceNumber" VARCHAR(40) NOT NULL,
  "clientId" UUID NOT NULL,
  "issueDate" DATE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "serviceDescription" TEXT,
  "totalAmount" DECIMAL(14,2) NOT NULL,
  "totalPaid" DECIMAL(14,2) NOT NULL,
  "totalDue" DECIMAL(14,2) NOT NULL,
  "currency" VARCHAR(3) NOT NULL DEFAULT 'BDT',
  "status" "RecruitmentInvoiceStatus" NOT NULL DEFAULT 'ISSUED',
  "sentAt" TIMESTAMPTZ(3),
  "sentVia" VARCHAR(40),
  "createdById" UUID,
  "createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMPTZ(3) NOT NULL,
  CONSTRAINT "recruitment_invoices_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "recruitment_invoices_invoiceNumber_key" ON "recruitment_invoices"("invoiceNumber");
CREATE UNIQUE INDEX "recruitment_invoices_clientId_key" ON "recruitment_invoices"("clientId");
CREATE INDEX "recruitment_invoices_status_issueDate_idx" ON "recruitment_invoices"("status", "issueDate");

CREATE INDEX "agent_profiles_managementGroupId_idx" ON "agent_profiles"("managementGroupId");
CREATE INDEX "recruitment_clients_groupId_agentProfileId_status_idx"
  ON "recruitment_clients"("groupId", "agentProfileId", "status");

ALTER TABLE "recruitment_groups"
  ADD CONSTRAINT "recruitment_groups_createdById_fkey"
  FOREIGN KEY ("createdById") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT "recruitment_groups_updatedById_fkey"
  FOREIGN KEY ("updatedById") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE "agent_profiles"
  ADD CONSTRAINT "agent_profiles_managementGroupId_fkey"
  FOREIGN KEY ("managementGroupId") REFERENCES "recruitment_groups"("id") ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE "recruitment_clients"
  ADD CONSTRAINT "recruitment_clients_groupId_fkey"
  FOREIGN KEY ("groupId") REFERENCES "recruitment_groups"("id") ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE "recruitment_payment_schedules"
  ADD CONSTRAINT "recruitment_payment_schedules_clientId_fkey"
  FOREIGN KEY ("clientId") REFERENCES "recruitment_clients"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "recruitment_invoices"
  ADD CONSTRAINT "recruitment_invoices_clientId_fkey"
  FOREIGN KEY ("clientId") REFERENCES "recruitment_clients"("id") ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT "recruitment_invoices_createdById_fkey"
  FOREIGN KEY ("createdById") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- Put existing records into one clearly-labelled group so no historical data becomes orphaned.
INSERT INTO "recruitment_groups" (
  "id", "groupCode", "name", "description", "startDate", "status", "createdAt", "updatedAt"
) VALUES (
  '00000000-0000-4000-8000-000000000101',
  'EQG-LEGACY',
  'Existing / Legacy Group',
  'Automatically created during the Management System V2 upgrade for existing EuroQuest agents and clients.',
  CURRENT_DATE,
  'ACTIVE',
  CURRENT_TIMESTAMP,
  CURRENT_TIMESTAMP
) ON CONFLICT ("groupCode") DO NOTHING;

UPDATE "agent_profiles"
SET "managementGroupId" = '00000000-0000-4000-8000-000000000101'
WHERE "managementGroupId" IS NULL;

UPDATE "recruitment_clients" rc
SET "groupId" = ap."managementGroupId"
FROM "agent_profiles" ap
WHERE rc."agentProfileId" = ap."id"
  AND rc."groupId" IS NULL
  AND ap."managementGroupId" IS NOT NULL;

UPDATE "recruitment_clients"
SET "groupId" = '00000000-0000-4000-8000-000000000101'
WHERE "groupId" IS NULL;

-- Seed a 3-part payment schedule for existing contracts that do not already have one.
INSERT INTO "recruitment_payment_schedules" (
  "id", "clientId", "installmentNo", "label", "amount", "dueDate", "status", "createdAt", "updatedAt"
)
SELECT
  md5(rc."id"::text || ':schedule:1')::uuid,
  rc."id",
  1,
  'First Payment',
  ROUND(rc."contractAmount" / 3, 2),
  COALESCE(rc."fileDate", CURRENT_DATE) + 30,
  'PENDING',
  CURRENT_TIMESTAMP,
  CURRENT_TIMESTAMP
FROM "recruitment_clients" rc
WHERE rc."contractAmount" > 0
  AND NOT EXISTS (SELECT 1 FROM "recruitment_payment_schedules" s WHERE s."clientId" = rc."id");

INSERT INTO "recruitment_payment_schedules" (
  "id", "clientId", "installmentNo", "label", "amount", "dueDate", "status", "createdAt", "updatedAt"
)
SELECT
  md5(rc."id"::text || ':schedule:2')::uuid,
  rc."id",
  2,
  'Second Payment',
  ROUND(rc."contractAmount" / 3, 2),
  COALESCE(rc."fileDate", CURRENT_DATE) + 60,
  'PENDING',
  CURRENT_TIMESTAMP,
  CURRENT_TIMESTAMP
FROM "recruitment_clients" rc
WHERE rc."contractAmount" > 0
  AND EXISTS (SELECT 1 FROM "recruitment_payment_schedules" s WHERE s."clientId" = rc."id" AND s."installmentNo" = 1)
  AND NOT EXISTS (SELECT 1 FROM "recruitment_payment_schedules" s WHERE s."clientId" = rc."id" AND s."installmentNo" = 2);

INSERT INTO "recruitment_payment_schedules" (
  "id", "clientId", "installmentNo", "label", "amount", "dueDate", "status", "createdAt", "updatedAt"
)
SELECT
  md5(rc."id"::text || ':schedule:3')::uuid,
  rc."id",
  3,
  'Third Payment',
  rc."contractAmount" - (ROUND(rc."contractAmount" / 3, 2) * 2),
  COALESCE(rc."fileDate", CURRENT_DATE) + 90,
  'PENDING',
  CURRENT_TIMESTAMP,
  CURRENT_TIMESTAMP
FROM "recruitment_clients" rc
WHERE rc."contractAmount" > 0
  AND EXISTS (SELECT 1 FROM "recruitment_payment_schedules" s WHERE s."clientId" = rc."id" AND s."installmentNo" = 1)
  AND NOT EXISTS (SELECT 1 FROM "recruitment_payment_schedules" s WHERE s."clientId" = rc."id" AND s."installmentNo" = 3);