-- Add optional estate scoping to visitors so future reads can be tenant-isolated.
ALTER TABLE "Visitor" ADD COLUMN "estateId" TEXT;

UPDATE "Visitor" AS visitor
SET "estateId" = pass."estateId"
FROM (
    SELECT DISTINCT ON ("visitorId") "visitorId", "estateId"
    FROM "VisitorPass"
    ORDER BY "visitorId", "createdAt" DESC
) AS pass
WHERE visitor."id" = pass."visitorId"
  AND visitor."estateId" IS NULL;

CREATE INDEX "Visitor_estateId_idx" ON "Visitor"("estateId");

ALTER TABLE "Visitor" ADD CONSTRAINT "Visitor_estateId_fkey"
FOREIGN KEY ("estateId") REFERENCES "Estate"("id") ON DELETE SET NULL ON UPDATE CASCADE;
