-- CreateEnum
CREATE TYPE "EarnedWageAccessIssueModelName" AS ENUM ('EarnedWageAccessIssue');

-- CreateEnum
CREATE TYPE "EarnedWageAccessRepaymentModelName" AS ENUM ('EarnedWageAccessRepayment');

-- CreateTable
CREATE TABLE "EarnedWageAccessIssue" (
    "modelName" "EarnedWageAccessIssueModelName" NOT NULL DEFAULT 'EarnedWageAccessIssue',
    "id" UUID NOT NULL DEFAULT gen_random_uuid(),
    "perkInstanceId" UUID NOT NULL,
    "transferId" UUID NOT NULL,
    "amount" INTEGER NOT NULL,
    "userId" UUID NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "EarnedWageAccessRepayment" (
    "modelName" "EarnedWageAccessRepaymentModelName" NOT NULL DEFAULT 'EarnedWageAccessRepayment',
    "id" UUID NOT NULL DEFAULT gen_random_uuid(),
    "earnedWageAccessIssueId" UUID NOT NULL,
    "routedTransferId" UUID NOT NULL,
    "userId" UUID NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

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

-- AddForeignKey
ALTER TABLE "EarnedWageAccessIssue" ADD CONSTRAINT "EarnedWageAccessIssue_perkInstanceId_fkey" FOREIGN KEY ("perkInstanceId") REFERENCES "PerkInstance"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "EarnedWageAccessIssue" ADD CONSTRAINT "EarnedWageAccessIssue_transferId_fkey" FOREIGN KEY ("transferId") REFERENCES "Transfer"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "EarnedWageAccessIssue" ADD CONSTRAINT "EarnedWageAccessIssue_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "EarnedWageAccessRepayment" ADD CONSTRAINT "EarnedWageAccessRepayment_earnedWageAccessIssueId_fkey" FOREIGN KEY ("earnedWageAccessIssueId") REFERENCES "EarnedWageAccessIssue"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "EarnedWageAccessRepayment" ADD CONSTRAINT "EarnedWageAccessRepayment_routedTransferId_fkey" FOREIGN KEY ("routedTransferId") REFERENCES "RoutedTransfer"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "EarnedWageAccessRepayment" ADD CONSTRAINT "EarnedWageAccessRepayment_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- Migrate all PerkEvent issues
insert into "EarnedWageAccessIssue" (id, "userId", "perkInstanceId", "amount", "transferId", "createdAt", "updatedAt")
select
	id,
	"userId",
	"perkInstanceId",
	(pe."data" -> 'earnedWageAccess' ->> 'amount')::INTEGER as "amount",
	(pe."data" -> 'earnedWageAccess' ->> 'transferId')::uuid as "transferId",
	pe."createdAt",
	pe."updatedAt"
from "PerkEvent" pe
where pe."data" ->> 'type' = 'EarnedWageAccess'
and pe."data" -> 'earnedWageAccess' ->> 'type' = 'Issued';

-- Migrate all PerkEvent repayments
insert into "EarnedWageAccessRepayment"  (id, "userId", "routedTransferId" , "earnedWageAccessIssueId", "createdAt", "updatedAt")
select
	id,
	"userId",
	(pe."data" -> 'earnedWageAccess' ->> 'routedTransferId')::uuid as "routedTransferId",
	(pe."data" -> 'earnedWageAccess' ->> 'issueId')::uuid as "earnedWageAccessIssueId",
	pe."createdAt",
	pe."updatedAt"
from "PerkEvent" pe
where pe."data" ->> 'type' = 'EarnedWageAccess'
and pe."data" -> 'earnedWageAccess' ->> 'type' = 'Repaid';

-- Replace all old perk events for issues, we keep the rest of the old json
update "PerkEvent" pe
set "data" = jsonb_set(
	"data",
	'{earnedWageAccess,earnedWageAccessIssueId}',
	to_jsonb(pe.id),
	true
)
where pe."data" ->> 'type' = 'EarnedWageAccess'
and pe."data" -> 'earnedWageAccess' ->> 'type' = 'Issued';

-- Replace all old perk events for repayments, we keep the rest of the old json
update "PerkEvent" pe
set "data" = jsonb_set(
	"data",
	'{earnedWageAccess,earnedWageAccessRepaymentId}',
	to_jsonb(pe.id),
	true
)
where pe."data" ->> 'type' = 'EarnedWageAccess'
and pe."data" -> 'earnedWageAccess' ->> 'type' = 'Repaid';
