-- Map: an annotatable image (property aerial or per-location interior layout)
CREATE TABLE "Map" (
    "id" TEXT NOT NULL,
    "name" TEXT NOT NULL,
    "imagePath" TEXT NOT NULL,
    "width" INTEGER NOT NULL,
    "height" INTEGER NOT NULL,
    "locationId" TEXT,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,
    CONSTRAINT "Map_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "Map_locationId_key" ON "Map"("locationId");
CREATE INDEX "Map_locationId_idx" ON "Map"("locationId");

ALTER TABLE "Map"
  ADD CONSTRAINT "Map_locationId_fkey"
  FOREIGN KEY ("locationId") REFERENCES "Location"("id")
  ON DELETE SET NULL ON UPDATE CASCADE;

-- LocationPin: a normalized-coordinate pin on a Map pointing at a Location
CREATE TABLE "LocationPin" (
    "id" TEXT NOT NULL,
    "mapId" TEXT NOT NULL,
    "locationId" TEXT NOT NULL,
    "x" DOUBLE PRECISION NOT NULL,
    "y" DOUBLE PRECISION NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,
    CONSTRAINT "LocationPin_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "LocationPin_mapId_locationId_key" ON "LocationPin"("mapId", "locationId");
CREATE INDEX "LocationPin_mapId_idx" ON "LocationPin"("mapId");
CREATE INDEX "LocationPin_locationId_idx" ON "LocationPin"("locationId");

ALTER TABLE "LocationPin"
  ADD CONSTRAINT "LocationPin_mapId_fkey"
  FOREIGN KEY ("mapId") REFERENCES "Map"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "LocationPin"
  ADD CONSTRAINT "LocationPin_locationId_fkey"
  FOREIGN KEY ("locationId") REFERENCES "Location"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;