# settings = { "prefix": "to", "PK": "IDENTITY", "semantics": "CHAR", "auditCols": true }

-- Turnout: Quick SQL first draft
-- Kathuko APEX 26.2 from Zero, Part 2, Lesson 2.9
--
-- Paste into SQL Workshop > Utilities > Quick SQL and generate.
-- This draft is deliberately "good enough, not finished". In the lesson we
-- compare its output with turnout_schema.sql and list what a generator
-- can't decide for us:
--   * composite unique keys (one membership per person per club)
--   * "only one owner per club" (a function-based unique index)
--   * rules that compare two columns (an event must end after it starts)
--   * a 1:1 table keyed by its parent (event weather)
--   * which foreign keys should cascade on delete

categories
  name vc60 /nn /unique
  icon vc60
  sort_order int /default 10
  is_active vc1 /nn /check Y, N /default Y

users
  email vc255 /nn /unique
  display_name vc100 /nn
  preferred_language vc5 /nn /check en, es /default en
  time_zone vc64
  email_verified_at tstz
  is_active vc1 /nn /check Y, N /default Y
  user_identities
    provider vc20 /nn /check EMAIL, GOOGLE, MICROSOFT, GITHUB
    subject vc255 /nn
    provider_email vc255
    last_login_at tstz

clubs
  category_id /fk categories /nn
  name vc100 /nn
  slug vc100 /nn /unique
  description vc4000
  city vc100
  time_zone vc64 /nn /default America/New_York
  visibility vc20 /nn /check PUBLIC, PRIVATE /default PUBLIC
  join_policy vc20 /nn /check OPEN, APPROVAL, INVITE /default OPEN
  status vc20 /nn /check PENDING, ACTIVE, SUSPENDED /default PENDING
  club_members
    user_id /fk users /nn
    role vc20 /nn /check OWNER, ORGANIZER, MEMBER /default MEMBER
    status vc20 /nn /check PENDING, ACTIVE, REMOVED /default PENDING
    joined_at tstz
  club_invites
    email vc255 /nn
    role vc20 /nn /check ORGANIZER, MEMBER /default MEMBER
    token_hash vc64 /nn /unique
    expires_at tstz /nn
    accepted_at tstz
    invited_by_user_id /fk users
  venues
    name vc100 /nn
    address vc400
    latitude num
    longitude num
    notes vc1000
  events
    venue_id /fk venues
    title vc150 /nn
    slug vc150 /nn
    description vc4000
    starts_at tstz /nn
    ends_at tstz /nn
    capacity int
    max_guests int /nn /default 0
    rsvp_opens_at tstz
    rsvp_closes_at tstz
    status vc20 /nn /check DRAFT, PUBLISHED, CANCELLED, COMPLETED /default DRAFT
    cancel_reason vc400
    rsvps
      user_id /fk users /nn
      status vc20 /nn /check GOING, WAITLISTED, CANCELLED, ATTENDED, NO_SHOW
      guests int /nn /default 0
      waitlist_position int
      checkin_token vc64 /unique
      checked_in_at tstz
      rsvp_log
        old_status vc20
        new_status vc20 /nn
        reason vc400
    event_weather
      fetched_at tstz /nn
      summary vc200
      temp_high_c num
      temp_low_c num
      precip_chance_pct int
      forecast clob

notifications
  user_id /fk users /nn
  template_static_id vc255 /nn
  placeholders clob
  status vc20 /nn /check QUEUED, SENT, FAILED /default QUEUED
  attempts int /nn /default 0
  last_error vc4000
  sent_at tstz