-- Drop kanban template scaffold.
DROP TABLE IF EXISTS kanban_card;

-- Seminarhof Drawehn core schema.
-- Multi-tenant from day one (venue_id on every domain row).
-- All money stored as integer cents (euro_cents) to avoid float drift.
--
-- Enum-valued columns carry a CHECK (col IN (...)) so the DB is the single
-- source of truth for each enum. The pikku CLI introspects these into
-- string-literal unions (db.types.ts + enums.gen.ts).

-- ─────────────────────────────────────────────────────────────────────
-- Tenancy

CREATE TABLE IF NOT EXISTS venue (
  venue_id        TEXT PRIMARY KEY,
  name            TEXT NOT NULL,
  slug            TEXT NOT NULL UNIQUE,
  locale_default  TEXT NOT NULL DEFAULT 'de',
  meal_window_breakfast TEXT NOT NULL DEFAULT '07:00-10:00',
  meal_window_lunch     TEXT NOT NULL DEFAULT '12:00-14:00',
  meal_window_dinner    TEXT NOT NULL DEFAULT '18:00-20:00',
  created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ─────────────────────────────────────────────────────────────────────
-- Identity

CREATE TABLE IF NOT EXISTS app_user (
  user_id         TEXT PRIMARY KEY,
  email           TEXT NOT NULL UNIQUE,
  name            TEXT NOT NULL,
  locale          TEXT NOT NULL DEFAULT 'de',
  role            TEXT NOT NULL CHECK (role IN ('admin', 'client', 'owner')),
  created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS client (
  client_id       TEXT PRIMARY KEY,
  venue_id        TEXT NOT NULL REFERENCES venue(venue_id),
  name            TEXT,
  is_stammgruppe  INTEGER NOT NULL DEFAULT 0,
  billing_address TEXT,
  contact_email   TEXT,
  contact_phone   TEXT,
  website         TEXT,
  notes           TEXT,
  created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS client_member (
  client_id       TEXT NOT NULL REFERENCES client(client_id),
  user_id         TEXT NOT NULL REFERENCES app_user(user_id),
  role            TEXT NOT NULL DEFAULT 'organiser' CHECK (role IN ('organiser', 'colleague')),
  PRIMARY KEY (client_id, user_id)
);

-- ─────────────────────────────────────────────────────────────────────
-- Physical inventory

CREATE TABLE IF NOT EXISTS bathroom (
  bathroom_id     TEXT PRIMARY KEY,
  venue_id        TEXT NOT NULL REFERENCES venue(venue_id),
  label           TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS room (
  room_id           TEXT PRIMARY KEY,
  venue_id          TEXT NOT NULL REFERENCES venue(venue_id),
  number            INTEGER NOT NULL,
  beds              INTEGER NOT NULL,
  room_type         TEXT NOT NULL CHECK (room_type IN ('MBZ', 'DZ', 'EZ_only')),
  building          TEXT NOT NULL CHECK (building IN ('gaestehaus', 'haupthaus')),
  floor             TEXT NOT NULL CHECK (floor IN ('eg', 'og')),
  bathroom_id       TEXT REFERENCES bathroom(bathroom_id),
  wheelchair        INTEGER NOT NULL DEFAULT 0,
  ground_floor      INTEGER NOT NULL DEFAULT 0,
  quiet             INTEGER NOT NULL DEFAULT 0,
  shared_bath       INTEGER NOT NULL DEFAULT 0,
  dogs_allowed      INTEGER NOT NULL DEFAULT 0,
  double_bed_140    INTEGER NOT NULL DEFAULT 0,
  allergy_friendly  INTEGER NOT NULL DEFAULT 0,
  surcharge_eur_cents INTEGER NOT NULL DEFAULT 0,
  UNIQUE (venue_id, number)
);

CREATE TABLE IF NOT EXISTS seminar_room (
  seminar_room_id TEXT PRIMARY KEY,
  venue_id        TEXT NOT NULL REFERENCES venue(venue_id),
  name            TEXT NOT NULL
);

-- ─────────────────────────────────────────────────────────────────────
-- Bookings

CREATE TABLE IF NOT EXISTS booking (
  booking_id      TEXT PRIMARY KEY,
  venue_id        TEXT NOT NULL REFERENCES venue(venue_id),
  client_id       TEXT NOT NULL REFERENCES client(client_id),
  event_name      TEXT NOT NULL,
  start_date      TEXT,
  end_date        TEXT,
  status          TEXT NOT NULL CHECK (status IN ('enquiry', 'reserved', 'confirmed', 'ended', 'cancelled')),
  half_house      INTEGER NOT NULL DEFAULT 0,
  online_ad       INTEGER NOT NULL DEFAULT 0,
  cover_image_url TEXT,
  short_description TEXT,
  organiser_website TEXT,
  expected_persons INTEGER,
  meal_time_breakfast TEXT,
  meal_time_lunch     TEXT,
  meal_time_dinner    TEXT,
  daily_plan      TEXT,
  arrival_time    TEXT,
  departure_time  TEXT,
  payment_method  TEXT NOT NULL DEFAULT 'transfer' CHECK (payment_method IN ('cash', 'transfer')),
  agb_year        INTEGER,
  agb_accepted_at TEXT,
  privacy_accepted_at TEXT,
  notes           TEXT,
  created_at      TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_booking_venue_dates
  ON booking (venue_id, start_date, end_date);
CREATE INDEX IF NOT EXISTS idx_booking_client
  ON booking (client_id);
CREATE INDEX IF NOT EXISTS idx_booking_status
  ON booking (venue_id, status);

CREATE TABLE IF NOT EXISTS booking_extra (
  booking_extra_id TEXT PRIMARY KEY,
  booking_id      TEXT NOT NULL REFERENCES booking(booking_id),
  kind            TEXT NOT NULL CHECK (kind IN ('cake', 'second_seminar_room', 'early_arrival', 'bedlinen', 'massage_bench', 'sauna')),
  qty             INTEGER NOT NULL DEFAULT 1,
  on_dates        TEXT,            -- JSON array of dates for cake-day picker
  unit_price_cents INTEGER NOT NULL DEFAULT 0,
  note            TEXT
);

-- ─────────────────────────────────────────────────────────────────────
-- Participants

CREATE TABLE IF NOT EXISTS participant (
  participant_id  TEXT PRIMARY KEY,
  booking_id      TEXT NOT NULL REFERENCES booking(booking_id),
  name            TEXT NOT NULL,
  email           TEXT,
  age             INTEGER,
  kind            TEXT NOT NULL DEFAULT 'overnight' CHECK (kind IN ('overnight', 'day_guest')),
  dietary_tag     TEXT CHECK (dietary_tag IN ('omnivore', 'vegetarian', 'vegan', 'gluten_free', 'lactose_free', 'pescatarian', 'unknown')),
  free_text       TEXT,
  self_form_token TEXT UNIQUE,
  self_form_submitted_at TEXT,
  notes           TEXT,
  created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_participant_booking
  ON participant (booking_id);

CREATE TABLE IF NOT EXISTS participant_allergy (
  participant_id  TEXT NOT NULL REFERENCES participant(participant_id),
  allergen        TEXT NOT NULL CHECK (allergen IN ('nuts', 'soy', 'gluten_strict', 'dairy', 'egg', 'fish', 'celery', 'mustard', 'sesame', 'shellfish', 'other')),
  severity        TEXT NOT NULL DEFAULT 'standard' CHECK (severity IN ('standard', 'mild', 'moderate', 'severe', 'separate_prep')),
  note            TEXT,
  PRIMARY KEY (participant_id, allergen)
);

CREATE TABLE IF NOT EXISTS room_assignment (
  booking_id      TEXT NOT NULL REFERENCES booking(booking_id),
  participant_id  TEXT NOT NULL REFERENCES participant(participant_id),
  room_id         TEXT NOT NULL REFERENCES room(room_id),
  PRIMARY KEY (booking_id, participant_id)
);

CREATE INDEX IF NOT EXISTS idx_room_assignment_room
  ON room_assignment (room_id);

-- ─────────────────────────────────────────────────────────────────────
-- Invoicing

CREATE TABLE IF NOT EXISTS invoice (
  invoice_id      TEXT PRIMARY KEY,
  booking_id      TEXT NOT NULL REFERENCES booking(booking_id),
  kind            TEXT NOT NULL CHECK (kind IN ('deposit', 'final')),
  invoice_number  TEXT NOT NULL UNIQUE,
  issued_on       TEXT NOT NULL,
  due_on          TEXT,
  paid_on         TEXT,
  amount_cents    INTEGER NOT NULL,
  status          TEXT NOT NULL DEFAULT 'outstanding' CHECK (status IN ('outstanding', 'paid', 'cancelled')),
  pdf_url         TEXT,
  datev_ref       TEXT
);

CREATE INDEX IF NOT EXISTS idx_invoice_booking
  ON invoice (booking_id);

-- ─────────────────────────────────────────────────────────────────────
-- Audit

CREATE TABLE IF NOT EXISTS audit_log (
  audit_id        TEXT PRIMARY KEY,
  venue_id        TEXT NOT NULL REFERENCES venue(venue_id),
  user_id         TEXT REFERENCES app_user(user_id),
  entity          TEXT NOT NULL,
  entity_id       TEXT NOT NULL,
  field           TEXT,
  before_value    TEXT,
  after_value     TEXT,
  action          TEXT NOT NULL CHECK (action IN ('create', 'update', 'delete')),
  at              TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_audit_entity
  ON audit_log (entity, entity_id);