Stage 4: FORGE — The Chronicle Club Architecture

Roles:
  - host: Mohamed (admin, matchmaker, event curator, content producer)
  - patron: $349/mo (matchmaking + events + vetting committee)
  - inner_circle: $149/mo (unlimited events + featured chronicle)
  - member: $59/mo (chronicle + resonance + 2 events/mo)
  - probation: $59/mo first 30 days (building chronicle, no matching)
  - free: Browse published content only

-- Memberships (universal across all tiers)
CREATE TABLE fd_memberships (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    profile_id UUID REFERENCES fd_profiles(id) UNIQUE,
    tier TEXT NOT NULL DEFAULT 'free' CHECK (tier IN ('free','probation','member','inner_circle','patron')),
    status TEXT DEFAULT 'active' CHECK (status IN ('active','cancelled','expired','suspended')),
    started_at TIMESTAMPTZ DEFAULT now(),
    expires_at TIMESTAMPTZ,
    storekit_transaction_id TEXT,
    storekit_product_id TEXT,
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

-- Chronicles (one per member)
CREATE TABLE fd_chronicles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    profile_id UUID REFERENCES fd_profiles(id) UNIQUE,
    status TEXT DEFAULT 'probation' CHECK (status IN ('probation','active','featured','published')),
    probation_start TIMESTAMPTZ DEFAULT now(),
    probation_passed BOOLEAN DEFAULT false,
    entry_count INTEGER DEFAULT 0,
    featured_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Chronicle entries (journal posts)
CREATE TABLE fd_chronicle_entries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    chronicle_id UUID REFERENCES fd_chronicles(id),
    entry_type TEXT DEFAULT 'text' CHECK (entry_type IN ('text','photo','video','audio','milestone')),
    title TEXT,
    body TEXT,
    media_urls TEXT[],
    theme TEXT CHECK (theme IN ('what_i_built','what_i_learned','what_im_looking_for','chapter','reflection')),
    is_chapter BOOLEAN DEFAULT false,
    chapter_partner_id UUID REFERENCES fd_profiles(id),
    published BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Resonances (connection signals on specific entries)
CREATE TABLE fd_resonances (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    entry_id UUID REFERENCES fd_chronicle_entries(id),
    resonator_profile_id UUID REFERENCES fd_profiles(id),
    message TEXT,
    is_mutual BOOLEAN DEFAULT false,
    created_at TIMESTAMPTZ DEFAULT now(),
    UNIQUE(entry_id, resonator_profile_id)
);

-- Events (community gatherings)
CREATE TABLE fd_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title TEXT NOT NULL,
    description TEXT,
    event_type TEXT CHECK (event_type IN ('dinner','intimate_dinner','activity','salon','mixer')),
    venue_restaurant_id UUID REFERENCES fd_restaurants(id),
    venue_name TEXT,
    venue_address TEXT,
    capacity INTEGER DEFAULT 16,
    attendee_count INTEGER DEFAULT 0,
    min_tier TEXT DEFAULT 'member',
    starts_at TIMESTAMPTZ NOT NULL,
    ends_at TIMESTAMPTZ,
    host_profile_id UUID REFERENCES fd_profiles(id),
    status TEXT DEFAULT 'upcoming' CHECK (status IN ('upcoming','open','full','active','completed','cancelled')),
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Event RSVPs
CREATE TABLE fd_event_rsvps (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    event_id UUID REFERENCES fd_events(id),
    profile_id UUID REFERENCES fd_profiles(id),
    status TEXT DEFAULT 'confirmed' CHECK (status IN ('confirmed','waitlisted','cancelled','attended')),
    checked_in_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT now(),
    UNIQUE(event_id, profile_id)
);

-- Introductions (matchmaker-created for Patron tier)
CREATE TABLE fd_introductions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    member_a_id UUID REFERENCES fd_profiles(id),
    member_b_id UUID REFERENCES fd_profiles(id),
    matchmaker_id UUID REFERENCES fd_profiles(id),
    matchmaker_note TEXT,
    compatibility_thesis TEXT,
    member_a_response TEXT CHECK (member_a_response IN ('pending','accepted','declined')),
    member_b_response TEXT CHECK (member_b_response IN ('pending','accepted','declined')),
    status TEXT DEFAULT 'pending' CHECK (status IN ('pending','mutual_accept','declined','expired')),
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Reflection prompts (weekly AI-assisted journaling)
CREATE TABLE fd_reflection_prompts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    prompt_text TEXT NOT NULL,
    theme TEXT,
    week_number INTEGER,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- fd_profiles: add membership + chronicle fields
ALTER TABLE fd_profiles ADD COLUMN IF NOT EXISTS membership_tier TEXT DEFAULT 'free';
ALTER TABLE fd_profiles ADD COLUMN IF NOT EXISTS chronicle_id UUID;
ALTER TABLE fd_profiles ADD COLUMN IF NOT EXISTS relationship_history TEXT DEFAULT 'never';
ALTER TABLE fd_profiles ADD COLUMN IF NOT EXISTS events_attended INTEGER DEFAULT 0;

-- fd_restaurants: add event venue fields
ALTER TABLE fd_restaurants ADD COLUMN IF NOT EXISTS max_event_capacity INTEGER;
ALTER TABLE fd_restaurants ADD COLUMN IF NOT EXISTS event_contact TEXT;
ALTER TABLE fd_restaurants ADD COLUMN IF NOT EXISTS event_pricing TEXT;

com.mohameddiomande.FirstDate.member.monthly      — $59/mo
com.mohameddiomande.FirstDate.innercircle.monthly  — $149/mo
com.mohameddiomande.FirstDate.patron.monthly       — $349/mo