-- Drop old foreign keys linking components and pieces directly to machines
ALTER TABLE "composants" DROP CONSTRAINT IF EXISTS "composants_machineId_fkey";
ALTER TABLE "composants" DROP CONSTRAINT IF EXISTS "composants_parentComposantId_fkey";
ALTER TABLE "composants" DROP CONSTRAINT IF EXISTS "composants_typeMachineComponentRequirementId_fkey";
ALTER TABLE "pieces" DROP CONSTRAINT IF EXISTS "pieces_machineId_fkey";
ALTER TABLE "pieces" DROP CONSTRAINT IF EXISTS "pieces_composantId_fkey";
ALTER TABLE "pieces" DROP CONSTRAINT IF EXISTS "pieces_typeMachinePieceRequirementId_fkey";

-- Create new link tables to associate machines with components and pieces
CREATE TABLE "machine_component_links" (
    "id" TEXT NOT NULL,
    "machineId" TEXT NOT NULL,
    "composantId" TEXT NOT NULL,
    "parentLinkId" TEXT,
    "typeMachineComponentRequirementId" TEXT,
    "nameOverride" TEXT,
    "referenceOverride" TEXT,
    "prixOverride" DECIMAL(10,2),
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,
    CONSTRAINT "machine_component_links_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "machine_piece_links" (
    "id" TEXT NOT NULL,
    "machineId" TEXT NOT NULL,
    "pieceId" TEXT NOT NULL,
    "parentLinkId" TEXT,
    "typeMachinePieceRequirementId" TEXT,
    "nameOverride" TEXT,
    "referenceOverride" TEXT,
    "prixOverride" DECIMAL(10,2),
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,
    CONSTRAINT "machine_piece_links_pkey" PRIMARY KEY ("id")
);

-- Seed the new link tables using the existing component and piece assignments
INSERT INTO "machine_component_links" (
    "id",
    "machineId",
    "composantId",
    "typeMachineComponentRequirementId",
    "createdAt",
    "updatedAt"
)
SELECT
    "machineId" || '_' || "id" AS "id",
    "machineId",
    "id" AS "composantId",
    "typeMachineComponentRequirementId",
    "createdAt",
    "updatedAt"
FROM "composants"
WHERE "machineId" IS NOT NULL;

UPDATE "machine_component_links" AS link
SET "parentLinkId" = link."machineId" || '_' || c."parentComposantId"
FROM "composants" AS c
WHERE link."composantId" = c."id"
  AND c."parentComposantId" IS NOT NULL;

INSERT INTO "machine_piece_links" (
    "id",
    "machineId",
    "pieceId",
    "parentLinkId",
    "typeMachinePieceRequirementId",
    "createdAt",
    "updatedAt"
)
SELECT
    "machineId" || '_' || "id" AS "id",
    "machineId",
    "id" AS "pieceId",
    CASE WHEN "composantId" IS NOT NULL THEN "machineId" || '_' || "composantId" ELSE NULL END,
    "typeMachinePieceRequirementId",
    "createdAt",
    "updatedAt"
FROM "pieces"
WHERE "machineId" IS NOT NULL;

-- Remove the obsolete columns now that the data has been migrated
ALTER TABLE "composants" DROP COLUMN IF EXISTS "machineId";
ALTER TABLE "composants" DROP COLUMN IF EXISTS "parentComposantId";
ALTER TABLE "composants" DROP COLUMN IF EXISTS "typeMachineComponentRequirementId";

ALTER TABLE "pieces" DROP COLUMN IF EXISTS "machineId";
ALTER TABLE "pieces" DROP COLUMN IF EXISTS "composantId";
ALTER TABLE "pieces" DROP COLUMN IF EXISTS "typeMachinePieceRequirementId";

-- Add the new foreign key constraints for the link tables
ALTER TABLE "machine_component_links"
    ADD CONSTRAINT "machine_component_links_machineId_fkey"
        FOREIGN KEY ("machineId") REFERENCES "machines"("id") ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT "machine_component_links_composantId_fkey"
        FOREIGN KEY ("composantId") REFERENCES "composants"("id") ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT "machine_component_links_parentLinkId_fkey"
        FOREIGN KEY ("parentLinkId") REFERENCES "machine_component_links"("id") ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT "machine_component_links_typeMachineComponentRequirementId_fkey"
        FOREIGN KEY ("typeMachineComponentRequirementId") REFERENCES "type_machine_component_requirements"("id") ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE "machine_piece_links"
    ADD CONSTRAINT "machine_piece_links_machineId_fkey"
        FOREIGN KEY ("machineId") REFERENCES "machines"("id") ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT "machine_piece_links_pieceId_fkey"
        FOREIGN KEY ("pieceId") REFERENCES "pieces"("id") ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT "machine_piece_links_parentLinkId_fkey"
        FOREIGN KEY ("parentLinkId") REFERENCES "machine_component_links"("id") ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT "machine_piece_links_typeMachinePieceRequirementId_fkey"
        FOREIGN KEY ("typeMachinePieceRequirementId") REFERENCES "type_machine_piece_requirements"("id") ON DELETE SET NULL ON UPDATE CASCADE;