-- ============================================================
-- Perauset schema — converted from Postgres to SQLite
-- UUIDs → TEXT, BOOLEAN → INTEGER (0/1), JSON columns → TEXT,
-- TIMESTAMPTZ/DATE → TEXT, enums → TEXT CHECK constraints,
-- arrays → TEXT (JSON), no schemas, no triggers, no PL/pgSQL
-- ============================================================

-- ============================================================
-- Users (replaced by better-auth 0002 — defined here as
-- a placeholder so FKs can be deferred until 0002 runs)
-- ============================================================

CREATE TABLE IF NOT EXISTS user (
  id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  email TEXT NOT NULL UNIQUE,
  name TEXT,
  email_verified INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

-- ============================================================
-- User profile
-- ============================================================

CREATE TABLE user_profile (
  profile_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  user_id TEXT NOT NULL UNIQUE REFERENCES user(id) ON DELETE CASCADE,
  date_of_birth TEXT,
  nationality TEXT,
  notes TEXT,
  emergency_contact_name TEXT,
  emergency_contact_phone TEXT,
  mobile_number TEXT,
  whatsapp_number TEXT,
  avatar_url TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ============================================================
-- Audit log (no triggers in SQLite — app writes rows directly)
-- ============================================================

CREATE TABLE audit_log (
  audit_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  table_name TEXT NOT NULL,
  record_id TEXT NOT NULL,
  action TEXT NOT NULL,
  user_id TEXT REFERENCES user(id),
  changed_fields TEXT,
  occurred_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_audit_log_table_record ON audit_log(table_name, record_id);
CREATE INDEX idx_audit_log_user ON audit_log(user_id);
CREATE INDEX idx_audit_log_occurred ON audit_log(occurred_at);

-- ============================================================
-- Stay requests
-- ============================================================

CREATE TABLE stay_request (
  request_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  user_id TEXT NOT NULL REFERENCES user(id),
  request_type TEXT NOT NULL DEFAULT 'guest'
    CHECK (request_type IN ('guest','volunteer','staff','facilitator','day_visit')),
  requested_start_at TEXT NOT NULL,
  requested_end_at TEXT NOT NULL,
  retreat_id TEXT,
  notes TEXT,
  status TEXT NOT NULL DEFAULT 'submitted'
    CHECK (status IN ('submitted','under_review','approved','rejected','cancelled')),
  reviewed_by_user_id TEXT REFERENCES user(id),
  reviewed_at TEXT,
  review_notes TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_stay_request_user ON stay_request(user_id);
CREATE INDEX idx_stay_request_status ON stay_request(status);

-- ============================================================
-- Stays
-- ============================================================

CREATE TABLE stay (
  stay_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  user_id TEXT NOT NULL REFERENCES user(id),
  stay_type TEXT NOT NULL DEFAULT 'guest'
    CHECK (stay_type IN ('guest','volunteer','staff','facilitator','day_visitor')),
  source_request_id TEXT REFERENCES stay_request(request_id),
  retreat_id TEXT,
  start_at TEXT NOT NULL,
  end_at TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending','confirmed','checked_in','checked_out','cancelled')),
  checked_in_at TEXT,
  checked_out_at TEXT,
  created_by_user_id TEXT NOT NULL REFERENCES user(id),
  updated_by_user_id TEXT REFERENCES user(id),
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_stay_user ON stay(user_id);
CREATE INDEX idx_stay_status ON stay(status);
CREATE INDEX idx_stay_dates ON stay(start_at, end_at);

-- ============================================================
-- Rooms
-- ============================================================

CREATE TABLE room (
  room_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  code TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  room_type TEXT NOT NULL DEFAULT 'private'
    CHECK (room_type IN ('private','shared','dorm','facilitator','staff')),
  capacity INTEGER NOT NULL DEFAULT 1,
  is_active INTEGER NOT NULL DEFAULT 1,
  notes TEXT,
  price_per_night REAL,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE room_block (
  block_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  room_id TEXT NOT NULL REFERENCES room(room_id),
  block_type TEXT NOT NULL DEFAULT 'maintenance'
    CHECK (block_type IN ('maintenance','retreat_reserved','admin_hold','other')),
  retreat_id TEXT,
  starts_at TEXT NOT NULL,
  ends_at TEXT NOT NULL,
  reason TEXT,
  created_by_user_id TEXT NOT NULL REFERENCES user(id),
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_room_block_room ON room_block(room_id);
CREATE INDEX idx_room_block_dates ON room_block(starts_at, ends_at);

CREATE TABLE room_request (
  request_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  stay_id TEXT NOT NULL REFERENCES stay(stay_id),
  requested_room_type TEXT
    CHECK (requested_room_type IN ('private','shared','dorm','facilitator','staff')),
  preference_notes TEXT,
  status TEXT NOT NULL DEFAULT 'submitted'
    CHECK (status IN ('submitted','reviewed','satisfied','unsatisfied','cancelled')),
  reviewed_by_user_id TEXT REFERENCES user(id),
  reviewed_at TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE room_allocation (
  allocation_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  stay_id TEXT NOT NULL REFERENCES stay(stay_id),
  room_id TEXT NOT NULL REFERENCES room(room_id),
  starts_at TEXT NOT NULL,
  ends_at TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'planned'
    CHECK (status IN ('planned','active','completed','cancelled')),
  allocation_reason TEXT,
  created_by_user_id TEXT NOT NULL REFERENCES user(id),
  updated_by_user_id TEXT REFERENCES user(id),
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_room_allocation_room ON room_allocation(room_id);
CREATE INDEX idx_room_allocation_stay ON room_allocation(stay_id);
CREATE INDEX idx_room_allocation_dates ON room_allocation(starts_at, ends_at);

-- ============================================================
-- Boats
-- ============================================================

CREATE TABLE boat_route (
  route_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  name TEXT NOT NULL,
  origin TEXT NOT NULL,
  destination TEXT NOT NULL,
  is_active INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE boat (
  boat_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  name TEXT NOT NULL,
  passenger_capacity INTEGER NOT NULL,
  cargo_capacity_kg REAL,
  is_active INTEGER NOT NULL DEFAULT 1,
  notes TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE boat_trip (
  trip_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  boat_id TEXT REFERENCES boat(boat_id),
  route_id TEXT NOT NULL REFERENCES boat_route(route_id),
  scheduled_departure_at TEXT NOT NULL,
  scheduled_arrival_at TEXT,
  status TEXT NOT NULL DEFAULT 'planned'
    CHECK (status IN ('planned','open','full','closed','cancelled','completed')),
  notes TEXT,
  created_by_user_id TEXT NOT NULL REFERENCES user(id),
  updated_by_user_id TEXT REFERENCES user(id),
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_boat_trip_status ON boat_trip(status);
CREATE INDEX idx_boat_trip_departure ON boat_trip(scheduled_departure_at);

CREATE TABLE boat_reservation_request (
  request_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  trip_id TEXT NOT NULL REFERENCES boat_trip(trip_id),
  user_id TEXT NOT NULL REFERENCES user(id),
  stay_id TEXT REFERENCES stay(stay_id),
  retreat_id TEXT,
  requested_seats INTEGER NOT NULL DEFAULT 1,
  cargo_notes TEXT,
  special_requirements TEXT,
  priority_reason TEXT,
  status TEXT NOT NULL DEFAULT 'submitted'
    CHECK (status IN ('submitted','under_review','confirmed','waitlisted','declined','cancelled')),
  reviewed_by_user_id TEXT REFERENCES user(id),
  reviewed_at TEXT,
  review_notes TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_boat_reservation_trip ON boat_reservation_request(trip_id);
CREATE INDEX idx_boat_reservation_user ON boat_reservation_request(user_id);
CREATE INDEX idx_boat_reservation_status ON boat_reservation_request(status);

CREATE TABLE boat_manifest_entry (
  entry_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  trip_id TEXT NOT NULL REFERENCES boat_trip(trip_id),
  reservation_request_id TEXT REFERENCES boat_reservation_request(request_id),
  user_id TEXT NOT NULL REFERENCES user(id),
  stay_id TEXT REFERENCES stay(stay_id),
  status TEXT NOT NULL DEFAULT 'confirmed'
    CHECK (status IN ('confirmed','boarded','no_show','cancelled')),
  confirmed_by_user_id TEXT NOT NULL REFERENCES user(id),
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_boat_manifest_trip ON boat_manifest_entry(trip_id);

-- ============================================================
-- Tasks
-- ============================================================

CREATE TABLE task_template (
  template_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  title TEXT NOT NULL,
  description TEXT,
  category TEXT NOT NULL DEFAULT 'operations'
    CHECK (category IN ('operations','maintenance','kitchen','retreat','transport','housekeeping')),
  is_claimable INTEGER NOT NULL DEFAULT 0,
  default_location TEXT,
  estimated_minutes INTEGER,
  recurrence_cron TEXT,
  is_active INTEGER NOT NULL DEFAULT 1,
  created_by_user_id TEXT NOT NULL REFERENCES user(id),
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE task_instance (
  task_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  template_id TEXT REFERENCES task_template(template_id),
  title TEXT NOT NULL,
  description TEXT,
  category TEXT NOT NULL DEFAULT 'operations'
    CHECK (category IN ('operations','maintenance','kitchen','retreat','transport','housekeeping')),
  location TEXT,
  assigned_role TEXT,
  linked_retreat_id TEXT,
  linked_stay_id TEXT REFERENCES stay(stay_id),
  scheduled_start_at TEXT,
  scheduled_end_at TEXT,
  status TEXT NOT NULL DEFAULT 'planned'
    CHECK (status IN ('planned','open','assigned','in_progress','blocked','completed','cancelled')),
  created_by_user_id TEXT NOT NULL REFERENCES user(id),
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_task_instance_status ON task_instance(status);
CREATE INDEX idx_task_instance_category ON task_instance(category);
CREATE INDEX idx_task_instance_template ON task_instance(template_id);

CREATE TABLE task_assignment (
  assignment_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  task_instance_id TEXT NOT NULL REFERENCES task_instance(task_id),
  assigned_user_id TEXT NOT NULL REFERENCES user(id),
  assigned_by_user_id TEXT NOT NULL REFERENCES user(id),
  status TEXT NOT NULL DEFAULT 'proposed'
    CHECK (status IN ('proposed','accepted','declined','reassigned','completed')),
  notes TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_task_assignment_task ON task_assignment(task_instance_id);
CREATE INDEX idx_task_assignment_user ON task_assignment(assigned_user_id);

-- ============================================================
-- Notifications
-- ============================================================

CREATE TABLE notification (
  notification_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  user_id TEXT NOT NULL REFERENCES user(id),
  type TEXT NOT NULL,
  title TEXT NOT NULL,
  body TEXT,
  entity_type TEXT,
  entity_id TEXT,
  read_at TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_notification_user ON notification(user_id, read_at, created_at);

-- ============================================================
-- Retreats
-- ============================================================

CREATE TABLE retreat (
  retreat_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  slug TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  description TEXT,
  start_at TEXT NOT NULL,
  end_at TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'draft'
    CHECK (status IN ('draft','published','active','completed','cancelled')),
  capacity INTEGER,
  default_check_in_at TEXT,
  default_check_out_at TEXT,
  created_by_user_id TEXT NOT NULL REFERENCES user(id),
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_retreat_status ON retreat(status);
CREATE INDEX idx_retreat_dates ON retreat(start_at, end_at);

CREATE TABLE retreat_person (
  retreat_person_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  retreat_id TEXT NOT NULL REFERENCES retreat(retreat_id) ON DELETE CASCADE,
  user_id TEXT REFERENCES user(id),
  stay_id TEXT REFERENCES stay(stay_id),
  full_name TEXT,
  email TEXT,
  phone TEXT,
  role_type TEXT NOT NULL DEFAULT 'participant'
    CHECK (role_type IN ('participant','facilitator','assistant_facilitator','organizer','support_staff')),
  attendance_status TEXT NOT NULL DEFAULT 'registered'
    CHECK (attendance_status IN ('invited','registered','confirmed','checked_in','cancelled')),
  arrival_override_at TEXT,
  departure_override_at TEXT,
  notes TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE(retreat_id, user_id)
);

CREATE INDEX idx_retreat_person_retreat ON retreat_person(retreat_id);
CREATE INDEX idx_retreat_person_user ON retreat_person(user_id);

CREATE TABLE retreat_schedule_item (
  item_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  retreat_id TEXT NOT NULL REFERENCES retreat(retreat_id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  description TEXT,
  starts_at TEXT NOT NULL,
  ends_at TEXT NOT NULL,
  location TEXT,
  lead_retreat_person_id TEXT REFERENCES retreat_person(retreat_person_id),
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_retreat_schedule_retreat ON retreat_schedule_item(retreat_id);

-- ============================================================
-- Kitchen / Dietary
-- ============================================================

CREATE TABLE dietary_profile (
  profile_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  user_id TEXT NOT NULL UNIQUE REFERENCES user(id) ON DELETE CASCADE,
  is_vegan INTEGER NOT NULL DEFAULT 0,
  is_vegetarian INTEGER NOT NULL DEFAULT 0,
  is_gluten_free INTEGER NOT NULL DEFAULT 0,
  is_dairy_free INTEGER NOT NULL DEFAULT 0,
  has_nut_allergy INTEGER NOT NULL DEFAULT 0,
  allergy_notes TEXT,
  other_requirements TEXT,
  dislikes TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE meal_service (
  service_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  service_type TEXT NOT NULL
    CHECK (service_type IN ('breakfast','lunch','dinner','snack')),
  service_date TEXT NOT NULL,
  retreat_id TEXT REFERENCES retreat(retreat_id),
  planned_headcount INTEGER,
  menu_notes TEXT,
  status TEXT NOT NULL DEFAULT 'planned'
    CHECK (status IN ('planned','ready','served','completed')),
  created_by_user_id TEXT NOT NULL REFERENCES user(id),
  updated_by_user_id TEXT REFERENCES user(id),
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_meal_service_date ON meal_service(service_date);
CREATE INDEX idx_meal_service_type ON meal_service(service_type);
CREATE INDEX idx_meal_service_retreat ON meal_service(retreat_id);

CREATE TABLE meal_attendance (
  attendance_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  meal_service_id TEXT NOT NULL REFERENCES meal_service(service_id) ON DELETE CASCADE,
  user_id TEXT NOT NULL REFERENCES user(id),
  stay_id TEXT REFERENCES stay(stay_id),
  source_type TEXT NOT NULL DEFAULT 'manual'
    CHECK (source_type IN ('stay','retreat','manual')),
  status TEXT NOT NULL DEFAULT 'expected'
    CHECK (status IN ('expected','confirmed','cancelled','served')),
  notes TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE(meal_service_id, user_id)
);

CREATE INDEX idx_meal_attendance_service ON meal_attendance(meal_service_id);
CREATE INDEX idx_meal_attendance_user ON meal_attendance(user_id);

-- ============================================================
-- Inventory
-- ============================================================

CREATE TABLE inventory_item (
  item_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  name TEXT NOT NULL,
  unit TEXT NOT NULL DEFAULT 'unit',
  current_quantity REAL NOT NULL DEFAULT 0,
  minimum_quantity REAL NOT NULL DEFAULT 0,
  is_active INTEGER NOT NULL DEFAULT 1,
  category TEXT NOT NULL DEFAULT 'operations',
  subcategory TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_inventory_item_active ON inventory_item(is_active);
CREATE INDEX idx_inventory_item_category ON inventory_item(category);

CREATE TABLE inventory_request (
  request_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  requested_by_user_id TEXT NOT NULL REFERENCES user(id),
  item_id TEXT REFERENCES inventory_item(item_id),
  item_name TEXT NOT NULL,
  quantity REAL NOT NULL,
  unit TEXT NOT NULL,
  purpose TEXT,
  status TEXT NOT NULL DEFAULT 'submitted'
    CHECK (status IN ('submitted','approved','rejected','fulfilled','cancelled')),
  reviewed_by_user_id TEXT REFERENCES user(id),
  reviewed_at TEXT,
  review_notes TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_inventory_request_status ON inventory_request(status);
CREATE INDEX idx_inventory_request_user ON inventory_request(requested_by_user_id);

CREATE TABLE inventory_transaction (
  transaction_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  item_id TEXT NOT NULL REFERENCES inventory_item(item_id),
  quantity_change REAL NOT NULL,
  reason TEXT NOT NULL,
  notes TEXT,
  created_by_user_id TEXT NOT NULL REFERENCES user(id),
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_inventory_transaction_item ON inventory_transaction(item_id);
CREATE INDEX idx_inventory_transaction_created ON inventory_transaction(created_at);

-- ============================================================
-- Finance
-- ============================================================

CREATE TABLE finance_record (
  finance_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  name TEXT NOT NULL,
  description TEXT,
  amount REAL NOT NULL,
  currency TEXT NOT NULL DEFAULT 'EUR',
  record_type TEXT NOT NULL DEFAULT 'expense',
  amount_egp REAL,
  purchased_by_user_id TEXT REFERENCES user(id),
  purchased_at TEXT NOT NULL DEFAULT (datetime('now')),
  category TEXT NOT NULL DEFAULT 'operations',
  created_by_user_id TEXT NOT NULL REFERENCES user(id),
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_finance_record_category ON finance_record(category);
CREATE INDEX idx_finance_record_purchased_at ON finance_record(purchased_at);
CREATE INDEX idx_finance_record_purchased_by ON finance_record(purchased_by_user_id);

CREATE TABLE finance_link (
  link_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  finance_id TEXT NOT NULL REFERENCES finance_record(finance_id) ON DELETE CASCADE,
  entity_type TEXT NOT NULL,
  entity_id TEXT NOT NULL,
  notes TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_finance_link_finance ON finance_link(finance_id);
CREATE INDEX idx_finance_link_entity ON finance_link(entity_type, entity_id);

-- ============================================================
-- Exchange rates
-- ============================================================

CREATE TABLE exchange_rate (
  rate_id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(4)) || '-' || hex(randomblob(2)) || '-4' || substr(hex(randomblob(2)),2) || '-' || substr('AB89',1+(abs(random())%4),1) || substr(hex(randomblob(2)),2) || '-' || hex(randomblob(6)))),
  base_currency TEXT NOT NULL DEFAULT 'EGP',
  target_currency TEXT NOT NULL,
  rate REAL NOT NULL,
  fetched_at TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE UNIQUE INDEX idx_exchange_rate_unique ON exchange_rate(base_currency, target_currency, fetched_at);
CREATE INDEX idx_exchange_rate_date ON exchange_rate(fetched_at);