Files
bobbanandClaude Sonnet 5.5 8a0e93f559 Automatiska ID, nya registertyper och ID-register
- Löpande ID per bok och typ enligt idöversikten (KI, FI, BI, DI, LI, UI, FD, RI,
  RD, SI, REI, PI, SCI, KD-R_/KD-E_), prefix samlade i src/lib/ids.ts
- Nya typer: Fordon, Skiss, Research, Referens, Appar, Hemsidor (grupp Register)
- Sida med ID-register med sök och filter
- Migration 0003_auto_ids med backfill av befintliga poster
- ID visas i listor, postsidor, kartpanel och scheman
- README och Register/-mallar

Co-Authored-By: Claude Sonnet 5.5 <noreply@anthropic.com>
2026-10-22 22:31:50 +02:00

107 lines
4.4 KiB
SQL

-- Automatiska ID: nya posttyper, kodkolumner och löpande räknare per bok.
-- Befintliga poster får ID efter skapandetid. Tidigare handskrivna koder ersätts.
-- AlterEnum (de nya värdena används inte i den här migrationen)
ALTER TYPE "EntityKind" ADD VALUE 'VEHICLE';
ALTER TYPE "EntityKind" ADD VALUE 'RESEARCH';
ALTER TYPE "EntityKind" ADD VALUE 'REFERENCE';
ALTER TYPE "EntityKind" ADD VALUE 'SKETCH';
ALTER TYPE "EntityKind" ADD VALUE 'APP';
ALTER TYPE "EntityKind" ADD VALUE 'WEBSITE';
-- DropIndex
DROP INDEX "Entity_bookId_code_idx";
-- AlterTable
ALTER TABLE "Relation" ADD COLUMN "code" TEXT;
ALTER TABLE "Shift" ADD COLUMN "scheduleCode" TEXT;
ALTER TABLE "BookMap" ADD COLUMN "code" TEXT;
ALTER TABLE "Marker" ADD COLUMN "code" TEXT;
-- CreateTable
CREATE TABLE "IdCounter" (
"bookId" TEXT NOT NULL,
"key" TEXT NOT NULL,
"next" INTEGER NOT NULL DEFAULT 0,
CONSTRAINT "IdCounter_pkey" PRIMARY KEY ("bookId","key")
);
-- AddForeignKey
ALTER TABLE "IdCounter" ADD CONSTRAINT "IdCounter_bookId_fkey" FOREIGN KEY ("bookId") REFERENCES "Book"("id") ON DELETE CASCADE ON UPDATE CASCADE;
-- Backfill: poster (prefix enligt src/lib/ids.ts)
UPDATE "Entity" SET "code" = NULL;
WITH numbered AS (
SELECT id,
CASE kind::text
WHEN 'CHARACTER' THEN 'KI' WHEN 'COMPANY' THEN 'FI' WHEN 'HOME' THEN 'BI'
WHEN 'PET' THEN 'DI' WHEN 'PREMISES' THEN 'LI' WHEN 'EDUCATION' THEN 'UI'
WHEN 'PLACE' THEN 'PLI' WHEN 'SCENARIO' THEN 'SNI'
END AS prefix,
row_number() OVER (PARTITION BY "bookId", kind ORDER BY "createdAt", id) AS n
FROM "Entity"
)
UPDATE "Entity" e
SET "code" = numbered.prefix || '-' || lpad(numbered.n::text, 4, '0')
FROM numbered
WHERE e.id = numbered.id AND numbered.prefix IS NOT NULL;
INSERT INTO "IdCounter" ("bookId", "key", "next")
SELECT "bookId",
split_part("code", '-', 1),
count(*)
FROM "Entity"
WHERE "code" IS NOT NULL
GROUP BY "bookId", split_part("code", '-', 1);
-- Backfill: relationer (REI)
WITH numbered AS (
SELECT id, row_number() OVER (PARTITION BY "bookId" ORDER BY "createdAt", id) AS n FROM "Relation"
)
UPDATE "Relation" r SET "code" = 'REI-' || lpad(numbered.n::text, 4, '0') FROM numbered WHERE r.id = numbered.id;
INSERT INTO "IdCounter" ("bookId", "key", "next")
SELECT "bookId", 'REI', count(*) FROM "Relation" GROUP BY "bookId";
-- Backfill: kartor (KD-R_0001 för riktig karta, KD-E_0001 för egen kartbild)
WITH numbered AS (
SELECT id,
CASE kind::text WHEN 'GEO' THEN 'R' ELSE 'E' END AS letter,
row_number() OVER (PARTITION BY "bookId", kind ORDER BY "createdAt", id) AS n
FROM "BookMap"
)
UPDATE "BookMap" m SET "code" = 'KD-' || numbered.letter || '_' || lpad(numbered.n::text, 4, '0') FROM numbered WHERE m.id = numbered.id;
INSERT INTO "IdCounter" ("bookId", "key", "next")
SELECT "bookId", split_part("code", '_', 1), count(*) FROM "BookMap" GROUP BY "bookId", split_part("code", '_', 1);
-- Backfill: placeringar (PI)
WITH numbered AS (
SELECT mk.id, row_number() OVER (PARTITION BY bm."bookId" ORDER BY mk."createdAt", mk.id) AS n
FROM "Marker" mk JOIN "BookMap" bm ON bm.id = mk."mapId"
)
UPDATE "Marker" mk SET "code" = 'PI-' || lpad(numbered.n::text, 4, '0') FROM numbered WHERE mk.id = numbered.id;
INSERT INTO "IdCounter" ("bookId", "key", "next")
SELECT bm."bookId", 'PI', count(*) FROM "Marker" mk JOIN "BookMap" bm ON bm.id = mk."mapId" GROUP BY bm."bookId";
-- Backfill: scheman (SCI), ett per person och arbetsplats
WITH groups AS (
SELECT "bookId", "personId", "workplaceId", min("createdAt") AS first_at FROM "Shift" GROUP BY "bookId", "personId", "workplaceId"
), numbered AS (
SELECT *, row_number() OVER (PARTITION BY "bookId" ORDER BY first_at, "personId", COALESCE("workplaceId", '')) AS n FROM groups
)
UPDATE "Shift" s SET "scheduleCode" = 'SCI-' || lpad(numbered.n::text, 4, '0')
FROM numbered
WHERE s."bookId" = numbered."bookId" AND s."personId" = numbered."personId" AND s."workplaceId" IS NOT DISTINCT FROM numbered."workplaceId";
INSERT INTO "IdCounter" ("bookId", "key", "next")
SELECT "bookId", 'SCI', count(DISTINCT "scheduleCode") FROM "Shift" GROUP BY "bookId";
-- CreateIndex (efter backfill)
CREATE UNIQUE INDEX "Entity_bookId_code_key" ON "Entity"("bookId", "code");
CREATE UNIQUE INDEX "Relation_bookId_code_key" ON "Relation"("bookId", "code");
CREATE UNIQUE INDEX "BookMap_bookId_code_key" ON "BookMap"("bookId", "code");