create extension if not exists vector; create table if not exists properties ( id uuid default gen_random_uuid() primary key, name text not null, address text, user_id uuid references auth.users(id) on delete cascade, created_at timestamptz default now() ); create table if not exists calendar_events ( id uuid default gen_random_uuid() primary key, property_id uuid not null references properties(id) on delete cascade, start_date date not null, end_date date not null, event_type text check (event_type in ('booking','blocked','maintenance','hold')), platform text, guest_name text, total_price int, status text default 'confirmed', source_ical_url text, created_at timestamptz default now() ); create table if not exists pricing_rules ( id uuid default gen_random_uuid() primary key, property_id uuid not null references properties(id) on delete cascade, name text not null, start_date date, end_date date, multiplier decimal(3,2) default 1.00, min_nights int default 1, is_active boolean default true, is_ai_generated boolean default false, ai_reasoning text, created_at timestamptz default now() ); create table if not exists pricing_agent_runs ( id uuid default gen_random_uuid() primary key, property_id uuid not null references properties(id) on delete cascade, run_at timestamptz default now(), comps_scraped int, platforms text[], blended_market_rate int, recommended_base int, current_base int, projected_revenue_impact int, reasoning text, was_applied boolean default false, prompt_version int default 1 ); create table if not exists pricing_agent_prompts ( id uuid default gen_random_uuid() primary key, version int not null, prompt_text text not null, created_by uuid references auth.users(id), created_at timestamptz default now(), is_active boolean default false ); create table if not exists property_knowledge ( id uuid default gen_random_uuid() primary key, property_id uuid not null references properties(id) on delete cascade, question text not null, answer text not null, embedding vector(1536), category text default 'general', usage_count int default 0, is_auto_learned boolean default false, created_at timestamptz default now(), updated_at timestamptz default now() ); create table if not exists lookbook_templates ( id uuid default gen_random_uuid() primary key, property_id uuid not null references properties(id) on delete cascade, name text not null default 'Welcome Guide', subject_line text not null default 'Your Welcome Guide', html_content text not null, json_content jsonb, status text default 'draft', send_count int default 0, last_sent_at timestamptz, created_at timestamptz default now() ); create table if not exists lookbook_sends ( id uuid default gen_random_uuid() primary key, lookbook_id uuid references lookbook_templates(id) on delete set null, property_id uuid not null, guest_email text not null, booking_id uuid, sent_at timestamptz default now(), opened_at timestamptz, clicked_at timestamptz, status text default 'sent' ); create table if not exists affiliate_links ( id uuid default gen_random_uuid() primary key, property_id uuid not null references properties(id) on delete cascade, program_name text not null, category text not null, url text not null, commission_rate text, is_active boolean default true, display_order int default 0, created_at timestamptz default now() ); create table if not exists maintenance_tasks ( id uuid default gen_random_uuid() primary key, property_id uuid not null references properties(id) on delete cascade, task_name text not null, category text not null, last_done timestamptz, next_due timestamptz not null, frequency_days int, mileage_logged int default 0, assigned_to text, status text default 'scheduled', notes text, created_at timestamptz default now() ); create table if not exists email_templates ( id uuid default gen_random_uuid() primary key, property_id uuid references properties(id) on delete cascade, step_key text not null check (step_key in ('request','confirmed','pre_arrival','checkin_day','mid_stay','checkout_day','review_request')), subject text not null, body_html text not null, body_text text, delay_hours int default 0, delay_reference text default 'booking_created', is_active boolean default true, created_at timestamptz default now() ); create table if not exists email_logs ( id uuid default gen_random_uuid() primary key, property_id uuid not null, booking_id uuid, guest_email text not null, template_id uuid references email_templates(id), step_key text not null, sent_at timestamptz default now(), opened_at timestamptz, clicked_at timestamptz, status text default 'sent' ); create table if not exists reviews ( id uuid default gen_random_uuid() primary key, property_id uuid not null references properties(id) on delete cascade, booking_id uuid, platform text not null, guest_name text not null, guest_email text, rating int not null check (rating between 1 and 5), comment text, categories jsonb default '[]', host_reply text, replied_at timestamptz, is_featured boolean default false, created_at timestamptz default now() ); create or replace function match_property_knowledge( query_embedding vector(1536), match_property_id uuid, match_threshold float, match_count int ) returns table(id uuid, property_id uuid, question text, answer text, category text, usage_count int, similarity float) language plpgsql as $$ begin return query select pk.id, pk.property_id, pk.question, pk.answer, pk.category, pk.usage_count, 1 - (pk.embedding <=> query_embedding) as similarity from property_knowledge pk where pk.property_id = match_property_id and pk.embedding is not null and 1 - (pk.embedding <=> query_embedding) > match_threshold order by pk.embedding <=> query_embedding limit match_count; end; $$; create index idx_knowledge_embedding on property_knowledge using ivfflat (embedding vector_cosine_ops); create index idx_knowledge_property on property_knowledge(property_id); create index idx_lookbook_property on lookbook_templates(property_id); create index idx_affiliate_property on affiliate_links(property_id); create index idx_maintenance_property on maintenance_tasks(property_id); create index idx_calendar_property on calendar_events(property_id); create index idx_pricing_property on pricing_rules(property_id); create index idx_agent_runs_property on pricing_agent_runs(property_id, run_at desc); create index idx_pricing_ai on pricing_rules(is_ai_generated) where is_ai_generated = true; create index idx_email_template_step on email_templates(property_id, step_key); create index idx_email_logs_booking on email_logs(booking_id); create index idx_reviews_property on reviews(property_id); create index idx_reviews_platform on reviews(platform); create index idx_reviews_featured on reviews(is_featured) where is_featured = true; alter table property_knowledge enable row level security; alter table lookbook_templates enable row level security; alter table lookbook_sends enable row level security; alter table affiliate_links enable row level security; alter table maintenance_tasks enable row level security; alter table calendar_events enable row level security; alter table pricing_rules enable row level security; alter table pricing_agent_runs enable row level security; alter table pricing_agent_prompts enable row level security; alter table email_templates enable row level security; alter table email_logs enable row level security; alter table reviews enable row level security; -- RLS policies (repeat pattern for all tables) create policy "select_own" on property_knowledge for select using (exists(select 1 from properties where id = property_id and user_id = auth.uid())); create policy "insert_own" on property_knowledge for insert with check (exists(select 1 from properties where id = property_id and user_id = auth.uid())); create policy "update_own" on property_knowledge for update using (exists(select 1 from properties where id = property_id and user_id = auth.uid())); create policy "delete_own" on property_knowledge for delete using (exists(select 1 from properties where id = property_id and user_id = auth.uid()));

// app/api/calendar/route.ts // GET /api/calendar?property_id=xxx&month=YYYY-MM // POST /api/calendar {property_id, start_date, end_date, event_type, platform, guest_name, total_price} // PUT /api/calendar {id, ...updates} // DELETE /api/calendar?id=xxx // app/api/pricing/route.ts // GET /api/pricing?property_id=xxx // POST /api/pricing {property_id, name, start_date, end_date, multiplier, min_nights} // PUT /api/pricing {id, ...updates} // DELETE /api/pricing?id=xxx // app/api/pricing/agent/route.ts // POST /api/pricing/agent/run {property_id} // Triggers AI comp analysis: uses approved channel and market data → OpenAI → returns recommendation // Body: { comp_data: [...], property_profile: {...}, current_rate: 342 } // Returns: { recommended_base, weekend_multiplier, last_minute_discount, reasoning, projected_revenue_impact } // app/api/pricing/agent/log/route.ts // GET /api/pricing/agent/log?property_id=xxx&limit=50 // Returns last N agent runs with timestamps, comp counts, and decisions // app/api/pricing/agent/prompt/route.ts // GET /api/pricing/agent/prompt → returns current system prompt // PUT /api/pricing/agent/prompt → updates system prompt (admin only) // app/api/knowledge-base/route.ts // GET /api/knowledge-base?property_id=xxx // POST /api/knowledge-base {property_id, question, answer, category} // PUT /api/knowledge-base {id, question, answer, category} // DELETE /api/knowledge-base?id=xxx // app/api/qa-suggest/route.ts // POST /api/qa-suggest {property_id, question, property_context} // Returns: exactMatches, suggestedMatches, smartDraft // app/api/knowledge-base/track/route.ts // POST /api/knowledge-base/track {id} // increments usage_count // app/api/lookbook/generate/route.ts // POST /api/lookbook/generate {property_id, custom_notes} // Uses OpenAI to generate HTML email with partner links // app/api/lookbook/list/route.ts // GET /api/lookbook/list?property_id=xxx // app/api/lookbook/update/route.ts // PUT /api/lookbook/update {id, ...updates} // app/api/lookbook/delete/route.ts // DELETE /api/lookbook/delete?id=xxx // app/api/lookbook/send/route.ts // POST /api/lookbook/send {lookbook_id, guest_email, booking_id} // Wire Resend / SendGrid / AWS SES here // app/api/affiliate/route.ts // CRUD for partner links // app/api/maintenance/route.ts // GET /api/maintenance?property_id=xxx // POST /api/maintenance {property_id, task_name, category, next_due, frequency_days, assigned_to} // PUT /api/maintenance {id, ...updates} // DELETE /api/maintenance?id=xxx // app/api/automations/route.ts // GET /api/automations?property_id=xxx // POST /api/automations {property_id, step_key, subject, body_html, delay_hours, delay_reference} // PUT /api/automations {id, ...updates} // DELETE /api/automations?id=xxx // app/api/automations/send/route.ts // POST /api/automations/send {booking_id, step_key} // triggers email send // app/api/reviews/route.ts // GET /api/reviews?property_id=xxx&platform=xxx&rating=xxx // POST /api/reviews {property_id, booking_id, platform, guest_name, rating, comment, categories} // PUT /api/reviews {id, host_reply, is_featured} // DELETE /api/reviews?id=xxx

// app/components/CalendarGrid.tsx // Interactive month grid with booking bars, price tags, click handlers. // Props: events, onDateClick, onEventClick, currentMonth // app/components/BookingModal.tsx // Create/edit booking form. Fields: guest, dates, platform, price, status. // app/components/QASuggestPanel.tsx // Inbox smart suggest panel. No external branding. // Uses /api/qa-suggest and /api/knowledge-base/track // app/views/CalendarView.tsx // Full calendar with nav, legend, detail panel, + Add Booking / Block buttons. // app/views/QAView.tsx // Full knowledge base management. Stats, search, CRUD form. // app/components/LookbookPreview.tsx // Email preview with desktop/mobile toggle, HTML export, send button. // app/views/LookbookView.tsx // Generate + Library tabs. Host notes input, pro tip callout. // app/views/PricingView.tsx // Smart pricing dashboard. 30-day forecast chart, rules list, competitor cards, // AI agent panel with comp analysis, prompt editor, and activity log. // app/components/PricingAgentPanel.tsx // AI agent control center. Run button, comp results cards, recommendation banner, // apply/dismiss/adjust actions, log viewer, prompt editor textarea. // app/components/CompResultsCard.tsx // Platform-specific comp card (Airbnb/Vrbo/Manual). Shows listing count, // individual comps, market avg, and your position vs market. // app/views/AutomationsView.tsx // Guest journey timeline. Email preview cards per step, enable toggles, delay settings. // app/views/ReviewsView.tsx // Review center. Stats, filter bar, review cards with star ratings, reply composer, feature toggle. // app/views/MaintenanceView.tsx // Property care tracker. Mileage logs, overdue alerts, task table. // app/views/PhotosView.tsx // Photo SEO manager. Alt text editor, SEO title, optimization score. // app/views/SettingsView.tsx // Pixels & SEO settings. Facebook Pixel, GA4, GTM, meta defaults.

// Add to app/components/Sidebar.tsx tabs: { id: 'dashboard', label: 'Dashboard', icon: LayoutGrid } { id: 'inbox', label: 'Inbox', icon: MessageSquare } { id: 'calendar', label: 'Calendar', icon: Calendar } { id: 'knowledge', label: 'Knowledge Base', icon: BookOpen } { id: 'lookbook', label: 'Lookbook', icon: Mail } { id: 'pricing', label: 'Smart Pricing', icon: DollarSign } { id: 'maintenance', label: 'Maintenance', icon: Cog } { id: 'photos', label: 'Photos & SEO', icon: Image } { id: 'settings', label: 'Pixels & SEO', icon: Settings } { id: 'automations', label: 'Automations', icon: Mail } { id: 'reviews', label: 'Reviews', icon: Star } // Add to app/page.tsx router: {activeTab === 'dashboard' && } {activeTab === 'inbox' && } {activeTab === 'calendar' && } {activeTab === 'knowledge' && } {activeTab === 'lookbook' && } {activeTab === 'pricing' && } {activeTab === 'maintenance' && } {activeTab === 'photos' && } {activeTab === 'settings' && } {activeTab === 'automations' && } {activeTab === 'reviews' && } // Add to app/views/InboxView.tsx above reply composer: setReplyDraft(prev => prev ? prev + '\n\n' + text : text)} /> // Environment variables (.env.local): NEXT_PUBLIC_SUPABASE_URL= NEXT_PUBLIC_SUPABASE_ANON_KEY= SUPABASE_SERVICE_ROLE_KEY= OPENAI_API_KEY= RESEND_API_KEY= // or SENDGRID_API_KEY FACEBOOK_PIXEL_ID= GA4_MEASUREMENT_ID= GTM_CONTAINER_ID=

// Apply to these partner programs: // 1. GetYourGuide -> affiliates.getyourguide.com (~8% tours) // 2. Viator -> viator.com/affiliate (~8% activities) // 3. Discover Cars -> discovercars.com/affiliates (~6% rentals) // 4. Airalo -> airalo.com/affiliates (~10% eSIM) // 5. Welcome Pickups -> welcomepickups.com/affiliates (~10% transfers) // 6. World Nomads -> worldnomads.com/affiliate (~10% insurance) // Store links in affiliate_links table per property. // Lookbook generator reads these and embeds them in recommendations. // Disclosure footer auto included in every email.