-- Initial Data for BookBeach Database

-- Insert default currencies
INSERT INTO currencies (currency_code, currency_name, currency_symbol, exchange_rate_to_eur) VALUES
('EUR', 'Euro', '€', 1.0),
('USD', 'US Dollar', '$', 1.1),
('GBP', 'British Pound', '£', 0.85),
('JPY', 'Japanese Yen', '¥', 130.0),
('CNY', 'Chinese Yuan', '¥', 7.8),
('CAD', 'Canadian Dollar', 'C$', 1.35),
('AUD', 'Australian Dollar', 'A$', 1.55),
('CHF', 'Swiss Franc', 'CHF', 0.95),
('SEK', 'Swedish Krona', 'kr', 11.0),
('NOK', 'Norwegian Krone', 'kr', 11.5),
('DKK', 'Danish Krone', 'kr', 7.45),
('PLN', 'Polish Zloty', 'zł', 4.5),
('CZK', 'Czech Koruna', 'Kč', 24.5),
('HUF', 'Hungarian Forint', 'Ft', 380.0),
('RUB', 'Russian Ruble', '₽', 85.0),
('TRY', 'Turkish Lira', '₺', 18.5),
('BRL', 'Brazilian Real', 'R$', 5.8),
('MXN', 'Mexican Peso', '$', 19.5),
('INR', 'Indian Rupee', '₹', 82.0),
('KRW', 'South Korean Won', '₩', 1350.0),
('SGD', 'Singapore Dollar', 'S$', 1.45),
('HKD', 'Hong Kong Dollar', 'HK$', 8.8),
('THB', 'Thai Baht', '฿', 37.0),
('MYR', 'Malaysian Ringgit', 'RM', 4.8),
('PHP', 'Philippine Peso', '₱', 58.0);

-- Insert common languages
INSERT INTO languages (language_code, language_name, flag_url) VALUES
('en', 'English', '/assets/flags/en.png'),
('es', 'Español', '/assets/flags/es.png'),
('fr', 'Français', '/assets/flags/fr.png'),
('de', 'Deutsch', '/assets/flags/de.png'),
('it', 'Italiano', '/assets/flags/it.png'),
('pt', 'Português', '/assets/flags/pt.png'),
('ru', 'Русский', '/assets/flags/ru.png'),
('zh', '中文', '/assets/flags/zh.png'),
('ja', '日本語', '/assets/flags/ja.png'),
('ko', '한국어', '/assets/flags/ko.png'),
('ar', 'العربية', '/assets/flags/ar.png'),
('tr', 'Türkçe', '/assets/flags/tr.png'),
('pl', 'Polski', '/assets/flags/pl.png'),
('nl', 'Nederlands', '/assets/flags/nl.png'),
('sv', 'Svenska', '/assets/flags/sv.png'),
('da', 'Dansk', '/assets/flags/da.png'),
('no', 'Norsk', '/assets/flags/no.png'),
('fi', 'Suomi', '/assets/flags/fi.png'),
('el', 'Ελληνικά', '/assets/flags/el.png'),
('he', 'עברית', '/assets/flags/he.png'),
('th', 'ไทย', '/assets/flags/th.png'),
('vi', 'Tiếng Việt', '/assets/flags/vi.png'),
('hi', 'हिन्दी', '/assets/flags/hi.png'),
('cs', 'Čeština', '/assets/flags/cs.png'),
('hu', 'Magyar', '/assets/flags/hu.png');

-- Insert common countries
INSERT INTO countries (country_name, country_code, phone_code, flag_url) VALUES
('United States', 'USA', '+1', '/assets/flags/us.png'),
('United Kingdom', 'GBR', '+44', '/assets/flags/gb.png'),
('Germany', 'DEU', '+49', '/assets/flags/de.png'),
('France', 'FRA', '+33', '/assets/flags/fr.png'),
('Italy', 'ITA', '+39', '/assets/flags/it.png'),
('Spain', 'ESP', '+34', '/assets/flags/es.png'),
('Greece', 'GRC', '+30', '/assets/flags/gr.png'),
('Portugal', 'PRT', '+351', '/assets/flags/pt.png'),
('Netherlands', 'NLD', '+31', '/assets/flags/nl.png'),
('Belgium', 'BEL', '+32', '/assets/flags/be.png'),
('Switzerland', 'CHE', '+41', '/assets/flags/ch.png'),
('Austria', 'AUT', '+43', '/assets/flags/at.png'),
('Sweden', 'SWE', '+46', '/assets/flags/se.png'),
('Norway', 'NOR', '+47', '/assets/flags/no.png'),
('Denmark', 'DNK', '+45', '/assets/flags/dk.png'),
('Finland', 'FIN', '+358', '/assets/flags/fi.png'),
('Poland', 'POL', '+48', '/assets/flags/pl.png'),
('Czech Republic', 'CZE', '+420', '/assets/flags/cz.png'),
('Hungary', 'HUN', '+36', '/assets/flags/hu.png'),
('Croatia', 'HRV', '+385', '/assets/flags/hr.png'),
('Slovenia', 'SVN', '+386', '/assets/flags/si.png'),
('Bulgaria', 'BGR', '+359', '/assets/flags/bg.png'),
('Romania', 'ROU', '+40', '/assets/flags/ro.png'),
('Turkey', 'TUR', '+90', '/assets/flags/tr.png'),
('Cyprus', 'CYP', '+357', '/assets/flags/cy.png'),
('Malta', 'MLT', '+356', '/assets/flags/mt.png'),
('Canada', 'CAN', '+1', '/assets/flags/ca.png'),
('Australia', 'AUS', '+61', '/assets/flags/au.png'),
('New Zealand', 'NZL', '+64', '/assets/flags/nz.png'),
('Japan', 'JPN', '+81', '/assets/flags/jp.png'),
('South Korea', 'KOR', '+82', '/assets/flags/kr.png'),
('China', 'CHN', '+86', '/assets/flags/cn.png'),
('Singapore', 'SGP', '+65', '/assets/flags/sg.png'),
('Thailand', 'THA', '+66', '/assets/flags/th.png'),
('Malaysia', 'MYS', '+60', '/assets/flags/my.png'),
('Philippines', 'PHL', '+63', '/assets/flags/ph.png'),
('Indonesia', 'IDN', '+62', '/assets/flags/id.png'),
('Vietnam', 'VNM', '+84', '/assets/flags/vn.png'),
('India', 'IND', '+91', '/assets/flags/in.png'),
('Brazil', 'BRA', '+55', '/assets/flags/br.png'),
('Mexico', 'MEX', '+52', '/assets/flags/mx.png'),
('Argentina', 'ARG', '+54', '/assets/flags/ar.png'),
('Chile', 'CHL', '+56', '/assets/flags/cl.png'),
('South Africa', 'ZAF', '+27', '/assets/flags/za.png'),
('Egypt', 'EGY', '+20', '/assets/flags/eg.png'),
('Morocco', 'MAR', '+212', '/assets/flags/ma.png'),
('Tunisia', 'TUN', '+216', '/assets/flags/tn.png'),
('Israel', 'ISR', '+972', '/assets/flags/il.png'),
('United Arab Emirates', 'ARE', '+971', '/assets/flags/ae.png'),
('Saudi Arabia', 'SAU', '+966', '/assets/flags/sa.png'),
('Qatar', 'QAT', '+974', '/assets/flags/qa.png'),
('Kuwait', 'KWT', '+965', '/assets/flags/kw.png'),
('Bahrain', 'BHR', '+973', '/assets/flags/bh.png'),
('Oman', 'OMN', '+968', '/assets/flags/om.png');

-- Insert user roles
INSERT INTO user_roles (role_name, description) VALUES
('admin', 'System administrator with full access'),
('company_owner', 'Company owner with management privileges'),
('company_manager', 'Company manager with limited management access'),
('company_employee', 'Company employee with basic access'),
('customer', 'Regular customer user');

-- Insert beach terrain types
INSERT INTO beach_terrain_types (terrain_name, photo_path, description) VALUES
('Sand', '/assets/terrain/sand.jpg', 'Fine sandy beach'),
('Pebbles', '/assets/terrain/pebbles.jpg', 'Pebble beach'),
('Rocky', '/assets/terrain/rocky.jpg', 'Rocky coastline'),
('Mixed Sand-Pebbles', '/assets/terrain/mixed.jpg', 'Mixed sand and pebbles'),
('Black Sand', '/assets/terrain/black_sand.jpg', 'Volcanic black sand'),
('White Sand', '/assets/terrain/white_sand.jpg', 'Pure white sand'),
('Coral', '/assets/terrain/coral.jpg', 'Coral beach'),
('Shingle', '/assets/terrain/shingle.jpg', 'Shingle beach');

-- Insert beach bed types
INSERT INTO beach_bed_types (name, description, photo_path, uid, is_layout, has_background_color) VALUES
('Standard Sunbed', 'Basic sunbed with cushion', '/assets/beds/standard.jpg', 1, false, false),
('VIP Sunbed', 'Luxury sunbed with extra comfort', '/assets/beds/vip.jpg', 2, false, true),
('Double Sunbed', 'Double-wide sunbed for couples', '/assets/beds/double.jpg', 3, false, false),
('Cabana', 'Private beach cabana', '/assets/beds/cabana.jpg', 4, true, true),
('Beach Chair', 'Simple beach chair', '/assets/beds/chair.jpg', 5, false, false),
('Daybed', 'Circular daybed', '/assets/beds/daybed.jpg', 6, false, true),
('Umbrella Spot', 'Umbrella with no bed', '/assets/beds/umbrella.jpg', 7, true, false),
('Beach Bar Stool', 'Bar seating area', '/assets/beds/bar_stool.jpg', 8, true, false),
('Lounge Area', 'Group seating area', '/assets/beds/lounge.jpg', 9, true, true),
('Kids Play Area', 'Children play zone', '/assets/beds/kids_area.jpg', 10, true, true);

-- Insert default email templates
INSERT INTO email_templates (template_name, subject, html_content, text_content, variables, language_id) VALUES
('email_verification', 'Verify Your BookBeach Account', 
'<!DOCTYPE html><html><head><style>body{font-family:Arial,sans-serif;margin:0;padding:20px;background-color:#f4f4f4;}.container{max-width:600px;margin:0 auto;background-color:white;padding:20px;border-radius:10px;}.header{text-align:center;margin-bottom:30px;}.logo{max-width:200px;}.content{line-height:1.6;}.verification-code{background-color:#007bff;color:white;padding:15px;text-align:center;font-size:24px;font-weight:bold;border-radius:5px;margin:20px 0;}.footer{margin-top:30px;padding-top:20px;border-top:1px solid #eee;text-align:center;color:#666;}</style></head><body><div class="container"><div class="header"><img src="{{logo_url}}" alt="BookBeach" class="logo"></div><div class="content"><h2>Welcome to BookBeach!</h2><p>Hello {{first_name}},</p><p>Thank you for registering with BookBeach. To complete your registration, please use the verification code below:</p><div class="verification-code">{{verification_code}}</div><p>This code will expire in 15 minutes.</p><p>If you did not create this account, please ignore this email.</p></div><div class="footer"><p>Best regards,<br>The BookBeach Team</p><p><a href="{{website_url}}">www.bookbeach.app</a></p></div></div></body></html>',
'Welcome to BookBeach! Your verification code is: {{verification_code}}. This code will expire in 15 minutes.',
'{"first_name": "User first name", "verification_code": "6-digit code", "logo_url": "Logo URL", "website_url": "Website URL"}',
(SELECT language_id FROM languages WHERE language_code = 'en')),

('password_reset', 'Reset Your BookBeach Password',
'<!DOCTYPE html><html><head><style>body{font-family:Arial,sans-serif;margin:0;padding:20px;background-color:#f4f4f4;}.container{max-width:600px;margin:0 auto;background-color:white;padding:20px;border-radius:10px;}.header{text-align:center;margin-bottom:30px;}.logo{max-width:200px;}.content{line-height:1.6;}.verification-code{background-color:#dc3545;color:white;padding:15px;text-align:center;font-size:24px;font-weight:bold;border-radius:5px;margin:20px 0;}.footer{margin-top:30px;padding-top:20px;border-top:1px solid #eee;text-align:center;color:#666;}</style></head><body><div class="container"><div class="header"><img src="{{logo_url}}" alt="BookBeach" class="logo"></div><div class="content"><h2>Password Reset Request</h2><p>Hello {{first_name}},</p><p>We received a request to reset your password. Use the code below to reset your password:</p><div class="verification-code">{{verification_code}}</div><p>This code will expire in 15 minutes.</p><p>If you did not request this password reset, please ignore this email.</p></div><div class="footer"><p>Best regards,<br>The BookBeach Team</p><p><a href="{{website_url}}">www.bookbeach.app</a></p></div></div></body></html>',
'Password reset code for BookBeach: {{verification_code}}. This code will expire in 15 minutes.',
'{"first_name": "User first name", "verification_code": "6-digit code", "logo_url": "Logo URL", "website_url": "Website URL"}',
(SELECT language_id FROM languages WHERE language_code = 'en')),

('booking_confirmation', 'Your BookBeach Reservation Confirmed',
'<!DOCTYPE html><html><head><style>body{font-family:Arial,sans-serif;margin:0;padding:20px;background-color:#f4f4f4;}.container{max-width:600px;margin:0 auto;background-color:white;padding:20px;border-radius:10px;}.header{text-align:center;margin-bottom:30px;}.logo{max-width:200px;}.content{line-height:1.6;}.booking-details{background-color:#f8f9fa;padding:15px;border-radius:5px;margin:20px 0;}.detail-row{display:flex;justify-content:space-between;margin:5px 0;}.footer{margin-top:30px;padding-top:20px;border-top:1px solid #eee;text-align:center;color:#666;}</style></head><body><div class="container"><div class="header"><img src="{{logo_url}}" alt="BookBeach" class="logo"></div><div class="content"><h2>Booking Confirmed!</h2><p>Hello {{first_name}},</p><p>Your beach reservation has been confirmed. Here are your booking details:</p><div class="booking-details"><div class="detail-row"><strong>Beach:</strong> {{beach_name}}</div><div class="detail-row"><strong>Date:</strong> {{booking_date}}</div><div class="detail-row"><strong>Time:</strong> {{start_time}} - {{end_time}}</div><div class="detail-row"><strong>Sunbed:</strong> {{sunbed_reference}}</div><div class="detail-row"><strong>Total Price:</strong> {{total_price}} {{currency}}</div><div class="detail-row"><strong>Booking ID:</strong> {{booking_id}}</div></div><p>Please arrive 15 minutes before your booking time. Bring this email as confirmation.</p></div><div class="footer"><p>Best regards,<br>The BookBeach Team</p><p><a href="{{website_url}}">www.bookbeach.app</a></p></div></div></body></html>',
'Booking confirmed for {{beach_name}} on {{booking_date}} from {{start_time}} to {{end_time}}. Booking ID: {{booking_id}}',
'{"first_name": "User first name", "beach_name": "Beach name", "booking_date": "Booking date", "start_time": "Start time", "end_time": "End time", "sunbed_reference": "Sunbed reference", "total_price": "Total price", "currency": "Currency", "booking_id": "Booking ID", "logo_url": "Logo URL", "website_url": "Website URL"}',
(SELECT language_id FROM languages WHERE language_code = 'en'));

-- Insert system settings
INSERT INTO system_settings (setting_key, setting_value, description) VALUES
('site_name', 'BookBeach', 'Website name'),
('site_url', 'https://www.bookbeach.app', 'Website URL'),
('admin_email', 'admin@bookbeach.app', 'Administrator email'),
('support_email', 'support@bookbeach.app', 'Support email'),
('noreply_email', 'noreply@bookbeach.app', 'No-reply email'),
('evaluation_email', 'evaluation@bookbeach.app', 'Evaluation email'),
('default_currency', 'EUR', 'Default currency code'),
('default_language', 'en', 'Default language code'),
('email_verification_expire_minutes', '15', 'Email verification token expiry in minutes'),
('password_reset_expire_minutes', '15', 'Password reset token expiry in minutes'),
('booking_advance_days', '30', 'Maximum days in advance for booking'),
('refund_policy_hours', '24', 'Default refund policy hours before booking'),
('company_approval_required', 'true', 'Whether company approval is required'),
('maintenance_mode', 'false', 'Website maintenance mode'),
('google_maps_api_key', '', 'Google Maps API key for location services'),
('exchange_rate_api_key', '', 'Exchange rate API key'),
('min_booking_hours', '1', 'Minimum booking duration in hours'),
('max_booking_hours', '12', 'Maximum booking duration in hours'),
('platform_commission_percentage', '5.0', 'Platform commission percentage'),
('vat_percentage', '24.0', 'VAT percentage for Greece');

-- Create indexes for better performance
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_role ON users(role_id);
CREATE INDEX idx_companies_status ON companies(company_status);
CREATE INDEX idx_beach_places_company ON beach_places(company_id);
CREATE INDEX idx_beach_places_location ON beach_places(latitude, longitude);
CREATE INDEX idx_bookings_user ON bookings(user_id);
CREATE INDEX idx_bookings_beach ON bookings(beach_place_id);
CREATE INDEX idx_bookings_date ON bookings(booking_date);
CREATE INDEX idx_reviews_beach ON reviews(beach_place_id);
CREATE INDEX idx_email_verification_token ON email_verification_tokens(token);
CREATE INDEX idx_wallet_transactions_wallet ON wallet_transactions(wallet_id);
CREATE INDEX idx_beach_terrains_beach ON beach_place_terrains(beach_place_id);