create extension btree_gist;

CREATE TABLE coffees (
  id INTEGER NOT NULL PRIMARY KEY generated always as identity,
  what VARCHAR(32) NOT NULL,
  price DECIMAL(5, 2) NOT NULL
);

CREATE TABLE status_updates (
  id int GENERATED ALWAYS AS IDENTITY primary key,
  created_at timestamp NOT NULL,
  is_coffee_free boolean not null,
  coffee_id integer null references coffees (id),
  CHECK (is_coffee_free OR coffee_id is not null),
  EXCLUDE USING gist (date(created_at) WITH =, int4(is_coffee_free) WITH <>)
);

-- partial unique index
create unique index only_one_coffee_free on status_updates (date(created_at)) where (is_coffee_free);

insert into coffees (what, price) values ('latte', 3.2);
insert into status_updates (created_at, is_coffee_free, coffee_id) values ('2025-02-01', true, null);
-- fails due to unique: insert into status_updates (created_at, is_coffee_free, coffee_id) values ('2025-02-01', true, null);
-- fails due to exclusion: insert into status_updates (created_at, is_coffee_free, coffee_id) values ('2025-02-01', false, 1);

-- fails due to null coffee_id: insert into status_updates values ('2025-02-02', null, false, null);
insert into status_updates (created_at, is_coffee_free, coffee_id) values ('2025-02-02', false, 1);
insert into status_updates (created_at, is_coffee_free, coffee_id) values ('2025-02-02', false, 1);
-- fails due to exclusion: insert into status_updates (created_at, is_coffee_free, coffee_id) values ('2025-02-02', true, null);