-- One cover per position per day.
--
-- Written by hand because Drizzle 1.0 has no builder for EXCLUDE: `pgTable`
-- offers check, index, uniqueIndex, foreignKey and primaryKey, and nothing that
-- emits an exclusion constraint. `bun run db:generate` produced the table in the
-- migration before this one and was correct about everything it can express;
-- this is the part it cannot.
--
-- btree_gist is what lets `=` on a uuid sit in the same GiST index as `&&` on a
-- range. It is available here (1.8) and is created rather than assumed, so a
-- fresh database built from these migrations gets it too.
CREATE EXTENSION IF NOT EXISTS btree_gist;
--> statement-breakpoint
-- '[]' because both ends of the window are inclusive, and `daterange` is
-- half-open by default. The default would treat two covers that meet on the same
-- day as not overlapping, and that day is the one on which the same task would
-- be sent to two people and neither would know who owed it.
ALTER TABLE "position_covers"
  ADD CONSTRAINT "position_covers_no_overlap"
  EXCLUDE USING gist (
    "position_id" WITH =,
    daterange("from_date", "until_date", '[]') WITH &&
  );