perauset-e2e-mrfzot2p/sql/0018-seed-demo-data.sql

-- ============================================================
-- Demo seed data for PerAuset
-- Run after all migrations to populate with realistic test data
-- ============================================================

-- ============================================================
-- Users (diverse roles)
-- ============================================================
INSERT INTO app."user" (email, first_name, last_name, display_name, roles, status, phone, mobile_number, whatsapp_number) VALUES
  ('sarah@perauset.org', 'Sarah', 'El-Masry', 'Sarah', '{coordinator}', 'active', '+20 100 123 4567', '+20 100 123 4567', '+20 100 123 4567'),
  ('omar@perauset.org', 'Omar', 'Hassan', 'Omar', '{facilitator}', 'active', '+20 101 234 5678', '+20 101 234 5678', null),
  ('fatima@perauset.org', 'Fatima', 'Abdel-Rahman', 'Fatima', '{kitchen_lead}', 'active', '+20 102 345 6789', '+20 102 345 6789', '+20 102 345 6789'),
  ('ahmed@perauset.org', 'Ahmed', 'Nour', 'Ahmed', '{boat_coordinator}', 'active', '+20 103 456 7890', '+20 103 456 7890', null),
  ('layla@perauset.org', 'Layla', 'Ibrahim', 'Layla', '{staff}', 'active', null, '+20 104 567 8901', '+20 104 567 8901'),
  ('youssef@perauset.org', 'Youssef', 'Kamal', 'Youssef', '{volunteer}', 'active', null, '+20 105 678 9012', null),
  ('nadia@perauset.org', 'Nadia', 'Farouk', 'Nadia', '{community_member}', 'active', null, null, null),
  ('marco@example.com', 'Marco', 'Rossi', 'Marco', '{guest}', 'active', '+39 333 123 4567', null, '+39 333 123 4567'),
  ('anna@example.com', 'Anna', 'Müller', 'Anna', '{guest}', 'active', '+49 171 234 5678', null, null),
  ('james@example.com', 'James', 'Chen', 'James', '{volunteer}', 'active', null, '+1 555 987 6543', '+1 555 987 6543')
ON CONFLICT (email) DO NOTHING;

-- ============================================================
-- Dietary profiles
-- ============================================================
INSERT INTO app.dietary_profile (user_id, is_vegetarian, is_vegan, is_gluten_free, is_dairy_free, has_nut_allergy, allergy_notes, other_requirements)
SELECT u.user_id, r.veg, r.vgn, r.gf, r.df, r.nut, r.allergy_notes, r.other
FROM (VALUES
  ('fatima@perauset.org', true, false, false, false, false, null, 'No onions please'),
  ('marco@example.com', false, true, true, true, true, 'Severe nut allergy', 'Strict vegan - no honey'),
  ('anna@example.com', false, false, false, true, false, 'Lactose intolerant', null),
  ('omar@perauset.org', false, false, false, false, false, null, 'Halal only'),
  ('nadia@perauset.org', false, false, false, false, false, 'Shellfish allergy', null)
) AS r(email, veg, vgn, gf, df, nut, allergy_notes, other)
JOIN app."user" u ON u.email = r.email
ON CONFLICT (user_id) DO NOTHING;

-- ============================================================
-- Rooms
-- ============================================================
INSERT INTO app.room (code, name, room_type, capacity, price_per_night, notes) VALUES
  ('R-101', 'Sunrise Room', 'private', 2, 45.00, 'East-facing, morning sun'),
  ('R-102', 'Nile View', 'private', 2, 55.00, 'River view, premium room'),
  ('R-103', 'Palm Suite', 'facilitator', 2, 60.00, 'Reserved for retreat facilitators'),
  ('R-201', 'Garden Dorm A', 'dorm', 6, 15.00, 'Ground floor, garden access'),
  ('R-202', 'Garden Dorm B', 'dorm', 6, 15.00, 'Ground floor, garden access'),
  ('R-203', 'Rooftop Dorm', 'dorm', 4, 18.00, 'Rooftop terrace access'),
  ('R-301', 'Staff Room 1', 'staff', 2, null, 'Staff accommodation'),
  ('R-302', 'Staff Room 2', 'staff', 2, null, 'Staff accommodation'),
  ('R-401', 'Shared Room 1', 'shared', 3, 25.00, 'Shared bathroom'),
  ('R-402', 'Shared Room 2', 'shared', 3, 25.00, 'Shared bathroom')
ON CONFLICT (code) DO NOTHING;

-- ============================================================
-- Boat routes and boats
-- ============================================================
INSERT INTO app.boat_route (name, origin, destination) VALUES
  ('Main Ferry', 'Aswan Marina', 'Per Auset Island'),
  ('Express Shuttle', 'Aswan Corniche', 'Per Auset Island'),
  ('Supply Run', 'Aswan Market Dock', 'Per Auset Island')
ON CONFLICT DO NOTHING;

INSERT INTO app.boat (name, passenger_capacity) VALUES
  ('Lotus', 20),
  ('Felucca Star', 12),
  ('Supply Barge', 8)
ON CONFLICT DO NOTHING;

-- ============================================================
-- Boat trips (upcoming)
-- ============================================================
INSERT INTO app.boat_trip (route_id, boat_id, status, scheduled_departure_at, scheduled_arrival_at, notes, created_by_user_id)
SELECT
  r.route_id, b.boat_id, 'open'::app.boat_trip_status,
  (CURRENT_DATE + interval '1 day' + interval '8 hours')::timestamptz,
  (CURRENT_DATE + interval '1 day' + interval '8 hours 30 minutes')::timestamptz,
  'Morning ferry', u.user_id
FROM app.boat_route r, app.boat b, app."user" u
WHERE r.name = 'Main Ferry' AND b.name = 'Lotus' AND u.email = 'ahmed@perauset.org'
ON CONFLICT DO NOTHING;

INSERT INTO app.boat_trip (route_id, boat_id, status, scheduled_departure_at, scheduled_arrival_at, notes, created_by_user_id)
SELECT
  r.route_id, b.boat_id, 'open'::app.boat_trip_status,
  (CURRENT_DATE + interval '1 day' + interval '16 hours')::timestamptz,
  (CURRENT_DATE + interval '1 day' + interval '16 hours 30 minutes')::timestamptz,
  'Evening return', u.user_id
FROM app.boat_route r, app.boat b, app."user" u
WHERE r.name = 'Main Ferry' AND b.name = 'Lotus' AND u.email = 'ahmed@perauset.org'
ON CONFLICT DO NOTHING;

INSERT INTO app.boat_trip (route_id, boat_id, status, scheduled_departure_at, scheduled_arrival_at, notes, created_by_user_id)
SELECT
  r.route_id, b.boat_id, 'planned'::app.boat_trip_status,
  (CURRENT_DATE + interval '3 days' + interval '10 hours')::timestamptz,
  (CURRENT_DATE + interval '3 days' + interval '10 hours 45 minutes')::timestamptz,
  'Weekly supply run', u.user_id
FROM app.boat_route r, app.boat b, app."user" u
WHERE r.name = 'Supply Run' AND b.name = 'Supply Barge' AND u.email = 'ahmed@perauset.org'
ON CONFLICT DO NOTHING;

-- ============================================================
-- Stay requests & stays
-- ============================================================
INSERT INTO app.stay_request (user_id, request_type, requested_start_at, requested_end_at, status, notes)
SELECT u.user_id, r.request_type::app.stay_request_type, r.start_at::date, r.end_at::date, r.status::app.stay_request_status, r.notes
FROM (VALUES
  ('marco@example.com', 'guest', CURRENT_DATE + 2, CURRENT_DATE + 9, 'submitted', 'First visit, excited to learn about permaculture'),
  ('anna@example.com', 'guest', CURRENT_DATE + 5, CURRENT_DATE + 12, 'submitted', 'Interested in the kitchen program'),
  ('james@example.com', 'volunteer', CURRENT_DATE + 7, CURRENT_DATE + 37, 'approved', 'Gardening and construction skills'),
  ('nadia@perauset.org', 'day_visit', CURRENT_DATE + 3, CURRENT_DATE + 3, 'approved', null)
) AS r(email, request_type, start_at, end_at, status, notes)
JOIN app."user" u ON u.email = r.email;

-- Create stays from approved requests
INSERT INTO app.stay (user_id, source_request_id, start_at, end_at, stay_type, status, created_by_user_id)
SELECT sr.user_id, sr.request_id, sr.requested_start_at, sr.requested_end_at,
  (CASE sr.request_type::text
    WHEN 'guest' THEN 'guest'
    WHEN 'volunteer' THEN 'volunteer'
    WHEN 'day_visit' THEN 'day_visitor'
    ELSE 'guest'
  END)::app.stay_type,
  'confirmed'::app.stay_status, (SELECT user_id FROM app."user" WHERE email = 'sarah@perauset.org')
FROM app.stay_request sr
WHERE sr.status = 'approved';

-- ============================================================
-- Room allocations
-- ============================================================
INSERT INTO app.room_allocation (room_id, stay_id, starts_at, ends_at, status, allocation_reason, created_by_user_id)
SELECT r.room_id, s.stay_id, s.start_at, s.end_at, 'active'::app.room_allocation_status, 'Guest stay',
  (SELECT user_id FROM app."user" WHERE email = 'sarah@perauset.org')
FROM app.stay s
JOIN app."user" u ON u.user_id = s.user_id
JOIN app.room r ON r.code = CASE u.email
  WHEN 'james@example.com' THEN 'R-201'
  ELSE 'R-101'
END
WHERE s.status = 'confirmed'
LIMIT 2;

-- ============================================================
-- Inventory items
-- ============================================================
INSERT INTO app.inventory_item (name, unit, current_quantity, minimum_quantity, category, subcategory) VALUES
  ('Rice', 'kg', 50, 20, 'kitchen', 'staples'),
  ('Olive Oil', 'liter', 15, 5, 'kitchen', 'cooking-oils'),
  ('Flour', 'kg', 30, 10, 'kitchen', 'staples'),
  ('Tomatoes', 'kg', 8, 5, 'kitchen', 'fresh-produce'),
  ('Onions', 'kg', 12, 5, 'kitchen', 'fresh-produce'),
  ('Garlic', 'kg', 3, 1, 'kitchen', 'fresh-produce'),
  ('Lentils', 'kg', 25, 10, 'kitchen', 'staples'),
  ('Chickpeas (dried)', 'kg', 15, 5, 'kitchen', 'staples'),
  ('Tahini', 'jar', 6, 3, 'kitchen', 'condiments'),
  ('Soap (handmade)', 'bar', 40, 15, 'rooms', 'toiletries'),
  ('Towels', 'piece', 30, 10, 'rooms', 'linens'),
  ('Bed Sheets', 'set', 20, 8, 'rooms', 'linens'),
  ('Mosquito Nets', 'piece', 15, 5, 'rooms', 'supplies'),
  ('Garden Hose', 'piece', 3, 1, 'garden', 'tools'),
  ('Compost Bags', 'roll', 5, 2, 'garden', 'supplies'),
  ('Garden Shears', 'piece', 4, 2, 'garden', 'tools'),
  ('Solar Panel Cleaner', 'liter', 4, 2, 'equipment', 'cleaning'),
  ('First Aid Kit', 'kit', 3, 2, 'equipment', 'safety'),
  ('Boat Fuel', 'liter', 80, 30, 'equipment', 'fuel'),
  ('Life Jackets', 'piece', 25, 15, 'equipment', 'safety'),
  ('Yoga Mats', 'piece', 20, 10, 'other', 'retreat'),
  ('Whiteboard Markers', 'pack', 5, 2, 'other', 'office')
ON CONFLICT DO NOTHING;

-- ============================================================
-- Task templates
-- ============================================================
INSERT INTO app.task_template (title, description, category, is_claimable, default_location, estimated_minutes, recurrence_cron, created_by_user_id)
SELECT t.title, t.description, t.category::app.task_category, t.is_claimable, t.location, t.minutes, t.cron,
  (SELECT user_id FROM app."user" WHERE email = 'sarah@perauset.org')
FROM (VALUES
  ('Morning kitchen prep', 'Prepare breakfast ingredients and set up kitchen', 'kitchen', true, 'Main Kitchen', 60, 'daily'),
  ('Evening cleanup', 'Clean kitchen and dining area after dinner', 'kitchen', true, 'Main Kitchen', 45, 'daily'),
  ('Garden watering', 'Water all garden beds and check irrigation', 'maintenance', true, 'Garden', 30, 'daily'),
  ('Guest room turnover', 'Clean and prepare rooms for new guests', 'housekeeping', true, null, 45, null),
  ('Compost management', 'Turn compost piles and add new material', 'maintenance', true, 'Compost Area', 30, 'weekly'),
  ('Boat inspection', 'Check fuel, safety equipment, and hull condition', 'transport', false, 'Dock', 30, 'weekly'),
  ('Inventory check', 'Review kitchen inventory and note low-stock items', 'operations', false, 'Storage Room', 45, 'weekly'),
  ('Solar panel cleaning', 'Clean all solar panels', 'maintenance', true, 'Rooftop', 60, 'monthly')
) AS t(title, description, category, is_claimable, location, minutes, cron);

-- ============================================================
-- Task instances (active)
-- ============================================================
INSERT INTO app.task_instance (title, description, category, status, location, scheduled_start_at, scheduled_end_at, created_by_user_id)
SELECT t.title, t.description, t.category::app.task_category, t.status::app.task_status, t.location,
  t.start_at::timestamptz, t.end_at::timestamptz,
  (SELECT user_id FROM app."user" WHERE email = 'sarah@perauset.org')
FROM (VALUES
  ('Morning kitchen prep', 'Prepare breakfast', 'kitchen', 'open', 'Main Kitchen',
    CURRENT_DATE + interval '6 hours', CURRENT_DATE + interval '7 hours'),
  ('Garden watering', 'Water beds and check drip lines', 'maintenance', 'open', 'Garden',
    CURRENT_DATE + interval '7 hours', CURRENT_DATE + interval '7 hours 30 minutes'),
  ('Guest room turnover - R-101', 'Clean Sunrise Room for new arrival', 'housekeeping', 'assigned', null,
    CURRENT_DATE + interval '10 hours', CURRENT_DATE + interval '11 hours'),
  ('Evening cleanup', 'Kitchen and dining cleanup', 'kitchen', 'open', 'Main Kitchen',
    CURRENT_DATE + interval '20 hours', CURRENT_DATE + interval '21 hours'),
  ('Fix leaky faucet', 'Bathroom faucet in R-201 is dripping', 'maintenance', 'in_progress', 'R-201',
    CURRENT_DATE - interval '1 day', null),
  ('Organize storage room', 'Sort supplies and update inventory labels', 'operations', 'open', 'Storage Room',
    CURRENT_DATE + interval '2 days', CURRENT_DATE + interval '2 days 2 hours'),
  ('Prepare yoga space', 'Set up mats and clean the retreat hall', 'retreat', 'open', 'Retreat Hall',
    CURRENT_DATE + interval '3 days' + interval '8 hours', CURRENT_DATE + interval '3 days' + interval '9 hours')
) AS t(title, description, category, status, location, start_at, end_at);

-- Assign some tasks
INSERT INTO app.task_assignment (task_instance_id, assigned_user_id, assigned_by_user_id, status)
SELECT ti.task_id, u.user_id,
  (SELECT user_id FROM app."user" WHERE email = 'sarah@perauset.org'),
  'accepted'::app.task_assignment_status
FROM app.task_instance ti
JOIN app."user" u ON u.email = CASE ti.title
  WHEN 'Guest room turnover - R-101' THEN 'layla@perauset.org'
  WHEN 'Fix leaky faucet' THEN 'youssef@perauset.org'
END
WHERE ti.status IN ('assigned', 'in_progress');

-- ============================================================
-- Meal services (this week)
-- ============================================================
INSERT INTO app.meal_service (service_type, service_date, planned_headcount, menu_notes, created_by_user_id)
SELECT m.service_type::app.meal_type, m.service_date::date, m.headcount, m.notes,
  (SELECT user_id FROM app."user" WHERE email = 'fatima@perauset.org')
FROM (VALUES
  ('breakfast', CURRENT_DATE, 12, 'Ful medames, falafel, bread, tahini, fresh vegetables'),
  ('lunch', CURRENT_DATE, 15, 'Koshari with lentils and pasta, salad, pickles'),
  ('dinner', CURRENT_DATE, 14, 'Grilled vegetables, rice, molokhia soup'),
  ('breakfast', CURRENT_DATE + 1, 12, 'Shakshuka, cheese, olives, fresh bread'),
  ('lunch', CURRENT_DATE + 1, 15, 'Stuffed vine leaves, hummus, tabbouleh'),
  ('dinner', CURRENT_DATE + 1, 14, 'Lentil soup, grilled fish, salad'),
  ('breakfast', CURRENT_DATE + 2, 14, 'Pancakes, fruit, yogurt, honey'),
  ('lunch', CURRENT_DATE + 2, 16, 'Vegetable tagine, couscous, mint tea')
) AS m(service_type, service_date, headcount, notes);

-- ============================================================
-- Retreats
-- ============================================================
INSERT INTO app.retreat (name, slug, description, start_at, end_at, capacity, status, created_by_user_id)
SELECT r.name, r.slug, r.description, r.start_at::date, r.end_at::date, r.capacity, r.status::app.retreat_status,
  (SELECT user_id FROM app."user" WHERE email = 'omar@perauset.org')
FROM (VALUES
  ('Spring Permaculture Intensive', 'spring-permaculture-2026',
    'A 7-day deep dive into permaculture design, soil building, and sustainable food systems on the island.',
    CURRENT_DATE + 14, CURRENT_DATE + 21, 20, 'published'),
  ('Yoga & Meditation Retreat', 'yoga-meditation-april',
    'A 5-day retreat focused on daily yoga, meditation, and mindful living by the Nile.',
    CURRENT_DATE + 30, CURRENT_DATE + 35, 15, 'draft'),
  ('Community Building Workshop', 'community-building-may',
    'Learn participatory decision-making, conflict resolution, and community governance.',
    CURRENT_DATE + 45, CURRENT_DATE + 48, 25, 'draft')
) AS r(name, slug, description, start_at, end_at, capacity, status);

-- Add people to the published retreat
INSERT INTO app.retreat_person (retreat_id, user_id, role_type, attendance_status)
SELECT ret.retreat_id, u.user_id, r.role::app.retreat_person_role, 'confirmed'::app.retreat_attendance_status
FROM (VALUES
  ('omar@perauset.org', 'facilitator'),
  ('sarah@perauset.org', 'organizer'),
  ('marco@example.com', 'participant'),
  ('anna@example.com', 'participant'),
  ('james@example.com', 'participant'),
  ('nadia@perauset.org', 'participant')
) AS r(email, role)
JOIN app."user" u ON u.email = r.email
JOIN app.retreat ret ON ret.slug = 'spring-permaculture-2026';

-- Add schedule to the published retreat
INSERT INTO app.retreat_schedule_item (retreat_id, title, description, starts_at, ends_at, location)
SELECT ret.retreat_id, s.title, s.description,
  (ret.start_at + s.day_offset + s.start_time)::timestamptz,
  (ret.start_at + s.day_offset + s.end_time)::timestamptz,
  s.location
FROM (VALUES
  ('Welcome Circle', 'Introduction and intention setting', interval '0 days', interval '16 hours', interval '18 hours', 'Retreat Hall'),
  ('Permaculture Principles', 'Theory session on 12 permaculture principles', interval '1 day', interval '9 hours', interval '12 hours', 'Classroom'),
  ('Garden Walk', 'Guided tour of existing permaculture gardens', interval '1 day', interval '14 hours', interval '16 hours', 'Garden'),
  ('Soil Building Workshop', 'Hands-on composting and soil preparation', interval '2 days', interval '9 hours', interval '12 hours', 'Compost Area'),
  ('Water Systems Design', 'Designing rainwater harvesting and greywater systems', interval '3 days', interval '9 hours', interval '12 hours', 'Classroom'),
  ('Food Forest Planning', 'Planning and planting a food forest', interval '4 days', interval '9 hours', interval '12 hours', 'Garden'),
  ('Closing Circle', 'Reflection, sharing, and next steps', interval '6 days', interval '16 hours', interval '18 hours', 'Retreat Hall')
) AS s(title, description, day_offset, start_time, end_time, location)
JOIN app.retreat ret ON ret.slug = 'spring-permaculture-2026';

-- ============================================================
-- Finance records
-- ============================================================
INSERT INTO app.finance_record (name, description, amount, currency, record_type, category, purchased_at, created_by_user_id, purchased_by_user_id)
SELECT f.name, f.description, f.amount, 'EUR', f.record_type, f.category,
  (CURRENT_DATE - f.days_ago)::timestamptz,
  (SELECT user_id FROM app."user" WHERE email = 'sarah@perauset.org'),
  (SELECT user_id FROM app."user" WHERE email = f.buyer)
FROM (VALUES
  ('Weekly market groceries', 'Vegetables, fruits, bread from Aswan market', 180.00, 'expense', 'kitchen', 2, 'fatima@perauset.org'),
  ('Boat fuel', 'Diesel for Lotus and Supply Barge', 95.00, 'expense', 'transport', 3, 'ahmed@perauset.org'),
  ('Guest donation - Marco', 'Voluntary contribution for stay', 200.00, 'income', 'operations', 1, 'sarah@perauset.org'),
  ('Solar panel parts', 'Replacement inverter for roof array', 320.00, 'expense', 'maintenance', 7, 'sarah@perauset.org'),
  ('Retreat deposit - Spring Permaculture', 'Deposit for materials and facilitator', 500.00, 'expense', 'retreat', 10, 'omar@perauset.org'),
  ('Soap making supplies', 'Olive oil, lye, essential oils', 45.00, 'expense', 'operations', 5, 'layla@perauset.org'),
  ('Workshop fee income', 'Day workshop attendee fees', 150.00, 'income', 'retreat', 4, 'sarah@perauset.org'),
  ('Plumbing repair parts', 'Faucet and pipe fittings', 35.00, 'expense', 'maintenance', 1, 'youssef@perauset.org'),
  ('Monthly internet', 'Satellite internet subscription', 75.00, 'expense', 'operations', 15, 'sarah@perauset.org'),
  ('Guest donation - Anna', 'Contribution for kitchen program stay', 150.00, 'income', 'kitchen', 3, 'sarah@perauset.org')
) AS f(name, description, amount, record_type, category, days_ago, buyer);

-- ============================================================
-- Notifications
-- ============================================================
INSERT INTO app.notification (user_id, type, title, body)
SELECT u.user_id, 'info', n.title, n.body
FROM (VALUES
  ('sarah@perauset.org', 'New stay request', 'Marco Rossi has submitted a stay request for Mar 22 - Mar 29'),
  ('sarah@perauset.org', 'New stay request', 'Anna Müller has submitted a stay request for Mar 25 - Apr 1'),
  ('fatima@perauset.org', 'Low stock alert', 'Tomatoes are below minimum quantity (8 kg < 5 kg threshold)'),
  ('ahmed@perauset.org', 'Boat trip tomorrow', 'Morning ferry to Per Auset Island departing at 8:00 AM'),
  ('layla@perauset.org', 'Task assigned', 'You have been assigned: Guest room turnover - R-101'),
  ('youssef@perauset.org', 'Task assigned', 'You have been assigned: Fix leaky faucet in R-201'),
  ('omar@perauset.org', 'Retreat published', 'Spring Permaculture Intensive is now published with 6 participants'),
  ('marco@example.com', 'Stay request submitted', 'Your stay request for Mar 22 - Mar 29 has been submitted'),
  ('james@example.com', 'Stay approved', 'Your volunteer stay request has been approved. Welcome!'),
  ('admin@perauset.org', 'Weekly summary', '3 active stays, 2 pending requests, 7 open tasks, 2 boat trips scheduled')
) AS n(email, title, body)
JOIN app."user" u ON u.email = n.email;