-- SPDX-FileCopyrightText: 2026 Paul Schoeneck
--
-- SPDX-License-Identifier: AGPL-3.0-only AND LicenseRef-KIBU-Additional-Terms

CREATE TABLE pickup_slots (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  starts_at time NOT NULL,
  ends_at time NOT NULL,
  active boolean NOT NULL DEFAULT true,
  CHECK (starts_at < ends_at),
  UNIQUE(starts_at, ends_at)
);
INSERT INTO pickup_slots(starts_at, ends_at) VALUES
  ('14:00', '14:15'), ('15:00', '15:15'), ('16:00', '16:15'), ('17:00', '17:15');
ALTER TABLE weekly_schedules ADD COLUMN pickup_end time;
ALTER TABLE weekly_schedules ADD COLUMN pickup_slot_id uuid REFERENCES pickup_slots(id);
ALTER TABLE weekly_schedules ADD CONSTRAINT weekly_pickup_window CHECK(pickup_end IS NULL OR pickup_end > pickup);
ALTER TABLE day_overrides ADD COLUMN pickup_end time;
ALTER TABLE day_overrides ADD COLUMN pickup_slot_id uuid REFERENCES pickup_slots(id);
ALTER TABLE day_overrides ADD CONSTRAINT override_pickup_window CHECK(pickup_end IS NULL OR (attending AND pickup_end > pickup));
UPDATE weekly_schedules w SET pickup_slot_id = s.id, pickup_end = s.ends_at FROM pickup_slots s WHERE w.pickup = s.starts_at;
UPDATE day_overrides o SET pickup_slot_id = s.id, pickup_end = s.ends_at FROM pickup_slots s WHERE o.pickup = s.starts_at;

CREATE TABLE admission_rounds (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  code_hash text NOT NULL UNIQUE,
  expires_on date NOT NULL,
  status text NOT NULL DEFAULT 'open' CHECK(status IN ('open', 'closed', 'drawn')),
  created_by uuid NOT NULL REFERENCES staff(id),
  created_at timestamptz NOT NULL DEFAULT now(),
  drawn_by uuid REFERENCES staff(id),
  drawn_at timestamptz,
  places integer CHECK(places > 0),
  CHECK ((status = 'drawn' AND drawn_at IS NOT NULL AND drawn_by IS NOT NULL AND places IS NOT NULL)
    OR (status <> 'drawn' AND drawn_at IS NULL AND drawn_by IS NULL AND places IS NULL))
);
CREATE TABLE applications (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  round_id uuid NOT NULL REFERENCES admission_rounds(id),
  first_name text NOT NULL,
  last_name text NOT NULL,
  class_name text NOT NULL,
  parent_name text NOT NULL,
  email text NOT NULL,
  notes text NOT NULL DEFAULT '',
  status text NOT NULL DEFAULT 'waiting' CHECK(status IN ('waiting', 'selected', 'waitlisted', 'accepted')),
  child_id uuid UNIQUE REFERENCES children(id),
  submitted_at timestamptz NOT NULL DEFAULT now(),
  accepted_at timestamptz,
  accepted_by uuid REFERENCES staff(id),
  CHECK ((status = 'accepted' AND child_id IS NOT NULL AND accepted_at IS NOT NULL AND accepted_by IS NOT NULL)
    OR (status <> 'accepted' AND child_id IS NULL AND accepted_at IS NULL AND accepted_by IS NULL))
);
CREATE UNIQUE INDEX application_unique_child ON applications(round_id, lower(first_name), lower(last_name), lower(email));
CREATE INDEX applications_round ON applications(round_id, submitted_at, id);
CREATE TABLE application_limits (
  key_hash text PRIMARY KEY,
  attempts integer NOT NULL,
  window_start timestamptz NOT NULL
);