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

CREATE TABLE IF NOT EXISTS staff (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email text NOT NULL UNIQUE,
  name text NOT NULL,
  password_hash text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS sessions (
  token_hash text PRIMARY KEY,
  staff_id uuid NOT NULL REFERENCES staff(id) ON DELETE CASCADE,
  expires_at timestamptz NOT NULL
);
CREATE TABLE IF NOT EXISTS login_attempts (
  email text PRIMARY KEY,
  attempts integer NOT NULL,
  window_start timestamptz NOT NULL
);
CREATE TABLE IF NOT EXISTS children (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  first_name text NOT NULL,
  last_name text NOT NULL,
  class_name text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS weekly_schedules (
  child_id uuid NOT NULL REFERENCES children(id) ON DELETE CASCADE,
  weekday integer NOT NULL CHECK (weekday BETWEEN 1 AND 7),
  arrival time NOT NULL,
  pickup time NOT NULL,
  PRIMARY KEY (child_id, weekday),
  CHECK (arrival < pickup)
);
CREATE TABLE IF NOT EXISTS day_overrides (
  child_id uuid NOT NULL REFERENCES children(id) ON DELETE CASCADE,
  day date NOT NULL,
  attending boolean NOT NULL,
  arrival time,
  pickup time,
  PRIMARY KEY (child_id, day),
  CHECK ((attending AND arrival IS NOT NULL AND pickup IS NOT NULL AND arrival < pickup)
    OR (NOT attending AND arrival IS NULL AND pickup IS NULL))
);
CREATE TABLE IF NOT EXISTS attendance (
  child_id uuid NOT NULL REFERENCES children(id) ON DELETE CASCADE,
  day date NOT NULL,
  status text NOT NULL CHECK (status IN ('arrived', 'sick', 'picked_up')),
  arrived_at timestamptz,
  picked_up_at timestamptz,
  pickup_kind text CHECK (pickup_kind IN ('planned', 'parents')),
  updated_by uuid NOT NULL REFERENCES staff(id),
  PRIMARY KEY (child_id, day),
  CHECK (
    (status = 'sick' AND arrived_at IS NULL AND picked_up_at IS NULL AND pickup_kind IS NULL)
    OR (status = 'arrived' AND arrived_at IS NOT NULL AND picked_up_at IS NULL AND pickup_kind IS NULL)
    OR (status = 'picked_up' AND arrived_at IS NOT NULL AND picked_up_at IS NOT NULL AND pickup_kind IS NOT NULL)
  )
);
CREATE INDEX IF NOT EXISTS sessions_expiry ON sessions(expires_at);