migrations/009_create_calendar_exceptions.sql

-- up
-- HOLIDAY: full closure (window ignored). REDUCED_PERMIT: only this window open.
-- OVERRIDE_OPEN: opens a normally-closed day with this window.
CREATE TABLE calendar_exceptions (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  calendar_id   INTEGER NOT NULL REFERENCES calendars(id) ON DELETE CASCADE,
  date          TEXT NOT NULL,
  start_minute  INTEGER,
  end_minute    INTEGER,
  kind          TEXT NOT NULL CHECK (kind IN ('HOLIDAY','REDUCED_PERMIT','OVERRIDE_OPEN')),
  reason        TEXT,
  metadata      TEXT NOT NULL DEFAULT '{}',
  created_at    TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now')),
  UNIQUE (calendar_id, date, start_minute, kind)
);
CREATE INDEX idx_calendar_exceptions ON calendar_exceptions (calendar_id, date);

-- down
DROP TABLE calendar_exceptions;