-- ============================================================================
-- RUIRU GYM AND AEROBICS - CLIENT SAMPLE DATA
-- File: 2_client_sample_data.sql
-- Purpose: Insert comprehensive sample data for client management system
-- ============================================================================

USE ruiru_gym;

-- ============================================================================
-- INSERT SAMPLE CLIENTS
-- ============================================================================
INSERT INTO clients (
    first_name, last_name, email, phone, date_of_birth, gender, address,
    emergency_contact, emergency_phone, emergency_relationship,
    medical_conditions, allergies, blood_type, status, referred_by, newsletter_subscription
) VALUES
-- Active Members
('James', 'Kimani', 'james.kimani@email.com', '0712345678', '1990-05-15', 'Male', 'Ruiru, Kiambu County',
 'Mary Kimani', '0712345679', 'Spouse', NULL, NULL, 'O+', 'active', NULL, TRUE),

('Grace', 'Wanjiru', 'grace.wanjiru@email.com', '0723456789', '1988-08-20', 'Female', 'Thika Road, Nairobi',
 'Peter Wanjiru', '0723456790', 'Father', 'Asthma - mild', 'Dust', 'A+', 'active', 'James Kimani', TRUE),

('Peter', 'Otieno', 'peter.otieno@email.com', '0734567890', '1992-03-10', 'Male', 'Kahawa West, Nairobi',
 'Susan Otieno', '0734567891', 'Spouse', NULL, NULL, 'B+', 'active', NULL, TRUE),

('Lucy', 'Njeri', 'lucy.njeri@email.com', '0745678901', '1995-11-25', 'Female', 'Zimmerman, Nairobi',
 'John Njeri', '0745678902', 'Brother', NULL, 'Peanuts', 'AB+', 'active', 'Grace Wanjiru', TRUE),

('Michael', 'Mwangi', 'michael.mwangi@email.com', '0756789012', '1987-07-30', 'Male', 'Kasarani, Nairobi',
 'Ann Mwangi', '0756789013', 'Spouse', 'High blood pressure', NULL, 'O+', 'active', NULL, FALSE),

('Catherine', 'Akinyi', 'catherine.akinyi@email.com', '0767890123', '1993-12-05', 'Female', 'Roysambu, Nairobi',
 'James Akinyi', '0767890124', 'Father', NULL, 'Shellfish', 'A-', 'active', NULL, TRUE),

('Daniel', 'Kipchoge', 'daniel.kipchoge@email.com', '0778901234', '1991-04-18', 'Male', 'Ruiru Town',
 'Rose Kipchoge', '0778901235', 'Mother', NULL, NULL, 'B-', 'active', 'Peter Otieno', TRUE),

('Faith', 'Chebet', 'faith.chebet@email.com', '0789012345', '1994-09-22', 'Female', 'Juja, Kiambu',
 'Samuel Chebet', '0789012346', 'Spouse', NULL, NULL, 'O-', 'active', NULL, TRUE),

('Brian', 'Omondi', 'brian.omondi@email.com', '0790123456', '1989-06-14', 'Male', 'Githurai, Nairobi',
 'Jane Omondi', '0790123457', 'Sister', NULL, NULL, 'AB-', 'active', NULL, FALSE),

('Elizabeth', 'Wambui', 'elizabeth.wambui@email.com', '0701234567', '1996-01-08', 'Female', 'Ruiru',
 'Joseph Wambui', '0701234568', 'Father', 'Diabetes Type 2', 'Penicillin', 'A+', 'active', 'Catherine Akinyi', TRUE),

-- Inactive Members
('David', 'Kariuki', 'david.kariuki@email.com', '0712234567', '1985-03-20', 'Male', 'Thika, Kiambu',
 'Sarah Kariuki', '0712234568', 'Spouse', NULL, NULL, 'O+', 'inactive', NULL, FALSE),

('Monica', 'Mutua', 'monica.mutua@email.com', '0723345678', '1991-09-15', 'Female', 'Nairobi CBD',
 'Peter Mutua', '0723345679', 'Brother', NULL, NULL, 'B+', 'inactive', NULL, TRUE);

-- ============================================================================
-- INSERT MEMBERSHIPS FOR CLIENTS
-- ============================================================================
INSERT INTO memberships (
    client_id, membership_type, membership_plan, start_date, expiry_date,
    status, price, access_level, gym_access, class_access, personal_training_sessions
) VALUES
-- Active Memberships
(1, 'Basic Monthly', 'Basic', '2024-12-01', '2025-01-01', 'active', 2000.00, 'basic', TRUE, FALSE, 0),
(2, 'Standard Quarterly', 'Standard', '2024-11-15', '2025-02-15', 'active', 5500.00, 'standard', TRUE, TRUE, 2),
(3, 'Premium Semi-Annual', 'Premium', '2024-10-01', '2025-04-01', 'active', 10000.00, 'premium', TRUE, TRUE, 6),
(4, 'Basic Monthly', 'Basic', '2024-12-10', '2025-01-10', 'active', 2000.00, 'basic', TRUE, FALSE, 0),
(5, 'Elite Annual', 'Elite', '2024-09-01', '2025-09-01', 'active', 18000.00, 'vip', TRUE, TRUE, 24),
(6, 'Standard Quarterly', 'Standard', '2024-12-05', '2025-03-05', 'active', 5500.00, 'standard', TRUE, TRUE, 2),
(7, 'Premium Semi-Annual', 'Premium', '2024-11-20', '2025-05-20', 'active', 10000.00, 'premium', TRUE, TRUE, 6),
(8, 'Basic Monthly', 'Basic', '2024-12-15', '2025-01-15', 'active', 2000.00, 'basic', TRUE, FALSE, 0),
(9, 'Standard Quarterly', 'Standard', '2024-10-10', '2025-01-10', 'active', 5500.00, 'standard', TRUE, TRUE, 2),
(10, 'Elite Annual', 'Elite', '2024-08-01', '2025-08-01', 'active', 18000.00, 'vip', TRUE, TRUE, 24),

-- Expired Memberships
(11, 'Basic Monthly', 'Basic', '2024-06-01', '2024-07-01', 'expired', 2000.00, 'basic', TRUE, FALSE, 0),
(12, 'Standard Quarterly', 'Standard', '2024-03-01', '2024-06-01', 'expired', 5500.00, 'standard', TRUE, TRUE, 2);

-- ============================================================================
-- INSERT PAYMENT RECORDS
-- ============================================================================
INSERT INTO payments (
    client_id, amount, payment_method, payment_date, description, 
    transaction_reference, receipt_number, status, membership_id
) VALUES
-- Recent Payments
(1, 2000.00, 'M-Pesa', '2024-12-01 10:30:00', 'Basic Monthly Membership (1 month)', 'MPESA001', 'RCP001', 'completed', 1),
(2, 5500.00, 'Cash', '2024-11-15 14:20:00', 'Standard Quarterly Membership (3 months)', NULL, 'RCP002', 'completed', 2),
(3, 10000.00, 'Bank Transfer', '2024-10-01 09:15:00', 'Premium Semi-Annual Membership (6 months)', 'BTR001', 'RCP003', 'completed', 3),
(4, 2000.00, 'M-Pesa', '2024-12-10 11:45:00', 'Basic Monthly Membership (1 month)', 'MPESA002', 'RCP004', 'completed', 4),
(5, 18000.00, 'Card', '2024-09-01 16:00:00', 'Elite Annual Membership (12 months)', 'CRD001', 'RCP005', 'completed', 5),
(6, 5500.00, 'M-Pesa', '2024-12-05 13:30:00', 'Standard Quarterly Membership (3 months)', 'MPESA003', 'RCP006', 'completed', 6),
(7, 10000.00, 'Bank Transfer', '2024-11-20 10:00:00', 'Premium Semi-Annual Membership (6 months)', 'BTR002', 'RCP007', 'completed', 7),
(8, 2000.00, 'Cash', '2024-12-15 15:20:00', 'Basic Monthly Membership (1 month)', NULL, 'RCP008', 'completed', 8),
(9, 5500.00, 'M-Pesa', '2024-10-10 12:10:00', 'Standard Quarterly Membership (3 months)', 'MPESA004', 'RCP009', 'completed', 9),
(10, 18000.00, 'Card', '2024-08-01 09:30:00', 'Elite Annual Membership (12 months)', 'CRD002', 'RCP010', 'completed', 10),

-- Additional service payments
(1, 500.00, 'M-Pesa', '2024-12-20 14:00:00', 'Personal Training Session', 'MPESA005', 'RCP011', 'completed', NULL),
(2, 1000.00, 'Cash', '2024-12-18 11:00:00', 'Nutrition Consultation', NULL, 'RCP012', 'completed', NULL),
(5, 2500.00, 'Card', '2024-12-22 10:30:00', 'Sports Massage Therapy', 'CRD003', 'RCP013', 'completed', NULL),
(3, 750.00, 'M-Pesa', '2024-12-21 16:45:00', 'Locker Rental - 3 months', 'MPESA006', 'RCP014', 'completed', NULL);

-- ============================================================================
-- INSERT ATTENDANCE RECORDS
-- ============================================================================
INSERT INTO attendance (
    client_id, check_in_time, check_out_time, duration_minutes, visit_type
) VALUES
-- December 20, 2024
(1, '2024-12-20 06:00:00', '2024-12-20 07:30:00', 90, 'gym'),
(2, '2024-12-20 07:00:00', '2024-12-20 08:45:00', 105, 'gym'),
(3, '2024-12-20 17:00:00', '2024-12-20 19:00:00', 120, 'gym'),
(4, '2024-12-20 06:30:00', '2024-12-20 08:00:00', 90, 'gym'),
(5, '2024-12-20 18:00:00', '2024-12-20 20:00:00', 120, 'gym'),

-- December 21, 2024
(1, '2024-12-21 06:00:00', '2024-12-21 07:45:00', 105, 'gym'),
(2, '2024-12-21 07:30:00', '2024-12-21 09:00:00', 90, 'class'),
(6, '2024-12-21 17:30:00', '2024-12-21 19:15:00', 105, 'gym'),
(7, '2024-12-21 06:15:00', '2024-12-21 07:45:00', 90, 'gym'),
(8, '2024-12-21 18:00:00', '2024-12-21 19:30:00', 90, 'gym'),

-- December 22, 2024
(1, '2024-12-22 06:00:00', '2024-12-22 07:30:00', 90, 'gym'),
(3, '2024-12-22 17:00:00', '2024-12-22 18:45:00', 105, 'gym'),
(5, '2024-12-22 18:30:00', '2024-12-22 20:15:00', 105, 'personal_training'),
(9, '2024-12-22 07:00:00', '2024-12-22 08:30:00', 90, 'gym'),
(10, '2024-12-22 17:30:00', '2024-12-22 19:00:00', 90, 'gym'),

-- December 23, 2024
(2, '2024-12-23 07:00:00', '2024-12-23 08:30:00', 90, 'class'),
(4, '2024-12-23 18:00:00', '2024-12-23 19:30:00', 90, 'gym'),
(6, '2024-12-23 06:30:00', '2024-12-23 08:00:00', 90, 'gym'),
(7, '2024-12-23 17:45:00', '2024-12-23 19:15:00', 90, 'gym'),
(8, '2024-12-23 06:00:00', '2024-12-23 07:20:00', 80, 'gym'),

-- December 24, 2024
(1, '2024-12-24 06:00:00', '2024-12-24 07:45:00', 105, 'gym'),
(3, '2024-12-24 18:00:00', '2024-12-24 19:45:00', 105, 'gym'),
(5, '2024-12-24 17:30:00', '2024-12-24 19:30:00', 120, 'gym'),

-- December 25, 2024 (Current sessions - some checked in but not out)
(1, '2024-12-25 06:00:00', NULL, NULL, 'gym'),
(4, '2024-12-25 18:00:00', NULL, NULL, 'gym'),
(6, '2024-12-25 07:30:00', NULL, NULL, 'class'),
(9, '2024-12-25 17:00:00', '2024-12-25 18:30:00', 90, 'gym');

-- ============================================================================
-- INSERT CLIENT NOTES
-- ============================================================================
INSERT INTO client_notes (
    client_id, note_type, title, note_text, priority, created_by, is_private
) VALUES
(2, 'medical', 'Asthma Condition', 'Client has mild asthma. Ensure inhaler is accessible. Avoid dusty areas.', 'high', 1, FALSE),
(5, 'medical', 'Blood Pressure Monitoring', 'Client has high blood pressure. Monitor during intense workouts. BP: 140/90 at last check.', 'high', 1, FALSE),
(10, 'medical', 'Diabetes Management', 'Type 2 Diabetes. Check blood sugar before workouts. Keep glucose tablets nearby.', 'high', 1, FALSE),
(1, 'general', 'Progress Check', 'Client showing excellent progress. Increased weights by 10% this month.', 'low', 2, FALSE),
(3, 'compliment', 'Excellent Attendance', 'Perfect attendance for 3 months straight! Recommended for loyalty reward.', 'medium', 1, FALSE),
(7, 'general', 'Form Correction Needed', 'Needs coaching on proper squat form to prevent injury.', 'medium', 2, FALSE),
(8, 'reminder', 'Follow-up Consultation', 'Schedule nutrition consultation next week.', 'medium', 1, FALSE);

-- ============================================================================
-- INSERT CLIENT GOALS
-- ============================================================================
INSERT INTO client_goals (
    client_id, goal_type, title, description, target_value, current_value, unit,
    start_date, target_date, status, progress_percentage
) VALUES
(1, 'weight_loss', 'Lose 10kg', 'Target weight reduction for improved health', 75.0, 80.0, 'kg', '2024-12-01', '2025-06-01', 'active', 50.0),
(2, 'endurance', 'Run 10K', 'Complete 10km run without stopping', 10.0, 5.0, 'km', '2024-11-15', '2025-03-15', 'active', 50.0),
(3, 'muscle_gain', 'Build Muscle Mass', 'Gain 5kg of lean muscle', 75.0, 72.0, 'kg', '2024-10-01', '2025-04-01', 'active', 60.0),
(4, 'general_fitness', 'Improve Overall Fitness', 'General fitness improvement and toning', NULL, NULL, NULL, '2024-12-10', '2025-06-10', 'active', 20.0),
(5, 'flexibility', 'Increase Flexibility', 'Touch toes comfortably and improve range of motion', NULL, NULL, NULL, '2024-09-01', '2025-03-01', 'active', 70.0),
(6, 'weight_loss', 'Lose 8kg', 'Weight loss for wedding preparation', 62.0, 68.0, 'kg', '2024-12-05', '2025-05-05', 'active', 25.0);

-- ============================================================================
-- INSERT BODY MEASUREMENTS
-- ============================================================================
INSERT INTO client_body_measurements (
    client_id, measurement_date, weight_kg, body_fat_percentage, bmi,
    chest_cm, waist_cm, hips_cm, thigh_cm, arm_cm, measured_by
) VALUES
-- Initial measurements
(1, '2024-12-01', 85.0, 28.5, 27.8, 102.0, 95.0, 105.0, 58.0, 35.0, 2),
(2, '2024-11-15', 68.0, 25.2, 23.5, 90.0, 78.0, 95.0, 52.0, 30.0, 2),
(3, '2024-10-01', 72.0, 18.5, 22.1, 98.0, 82.0, 92.0, 55.0, 33.0, 2),
(4, '2024-12-10', 75.0, 30.1, 24.9, 95.0, 88.0, 98.0, 56.0, 32.0, 2),
(5, '2024-09-01', 88.0, 32.5, 28.5, 105.0, 100.0, 110.0, 60.0, 37.0, 2),

-- Progress measurements (1 month later)
(1, '2025-01-01', 80.0, 25.0, 26.1, 99.0, 90.0, 102.0, 56.0, 34.0, 2),
(2, '2024-12-15', 66.5, 23.8, 23.0, 89.0, 76.0, 94.0, 51.0, 29.5, 2),
(3, '2024-11-01', 73.0, 17.2, 22.4, 100.0, 81.0, 93.0, 56.0, 34.0, 2);

-- ============================================================================
-- INSERT CLIENT STATUS HISTORY
-- ============================================================================
INSERT INTO client_status_history (
    client_id, old_status, new_status, reason, changed_by
) VALUES
(11, 'active', 'inactive', 'Membership expired and not renewed', 1),
(12, 'active', 'inactive', 'Relocated to another city', 1),
(1, 'pending', 'active', 'Initial registration completed and payment received', 1),
(2, 'pending', 'active', 'Registration completed', 1);

-- ============================================================================
-- INSERT SAMPLE SEARCH HISTORY
-- ============================================================================
INSERT INTO client_search_history (
    searched_by, search_term, results_count, filters_applied
) VALUES
(1, 'james', 1, '{"status": "active"}'),
(1, 'grace', 1, '{"status": "active", "membership": "Standard"}'),
(2, 'kimani', 1, '{"status": "all"}'),
(1, '0712', 1, '{"status": "active"}'),
(2, 'diabetes', 1, '{"status": "active"}');

-- ============================================================================
-- Summary Information
-- ============================================================================
-- Total Clients: 12 (10 Active, 2 Inactive)
-- Total Memberships: 12 (10 Active, 2 Expired)
-- Total Payments: 14
-- Total Attendance Records: 29
-- Total Notes: 7
-- Total Goals: 6
-- Total Measurements: 8
-- ============================================================================