SET NAMES utf8mb4;

INSERT INTO settings (k, v) VALUES
('site_name', 'Global Health CIO Club'),
('site_tagline', 'The private club for healthcare technology leaders'),
('operator_name', 'Caresoft Foundation'),
('contact_email', 'hello@example.org'),
('free_until', '2027-12-31'),
('founding_target', '50'),
('require_vouches', '0'),
('feed_ad_every', '8');

INSERT INTO languages (code, name, native_name, dir, active, sort_order) VALUES
('en','English','English','ltr',1,1),
('ar','Arabic','العربية','rtl',1,2),
('fr','French','Français','ltr',1,3),
('es','Spanish','Español','ltr',1,4),
('pt','Portuguese','Português','ltr',0,5),
('zh','Chinese (Simplified)','简体中文','ltr',0,6),
('hi','Hindi','हिन्दी','ltr',0,7),
('de','German','Deutsch','ltr',0,8);

INSERT INTO currencies (code, name, symbol, decimals, active, sort_order) VALUES
('USD','US Dollar','$',2,1,1),('EUR','Euro','€',2,1,2),('GBP','Pound Sterling','£',2,1,3),
('AED','UAE Dirham','AED',2,1,4),('SAR','Saudi Riyal','SAR',2,1,5),('INR','Indian Rupee','₹',2,1,6),
('KES','Kenyan Shilling','KSh',2,1,7),('NGN','Nigerian Naira','₦',2,1,8),('ZAR','South African Rand','R',2,1,9),
('EGP','Egyptian Pound','E£',2,1,10),('MXN','Mexican Peso','MX$',2,1,11),('BRL','Brazilian Real','R$',2,1,12),
('SGD','Singapore Dollar','S$',2,1,13),('AUD','Australian Dollar','A$',2,1,14),('CAD','Canadian Dollar','C$',2,1,15);

INSERT INTO regions (id, name, sort_order) VALUES
(1,'Asia Pacific',1),(2,'Middle East',2),(3,'Africa',3),(4,'Europe',4),(5,'Americas',5);

INSERT INTO countries (iso2, name, region_id, currency, default_lang, timezone) VALUES
('IN','India',1,'INR','en','Asia/Kolkata'),
('SG','Singapore',1,'SGD','en','Asia/Singapore'),
('MY','Malaysia',1,'USD','en','Asia/Kuala_Lumpur'),
('ID','Indonesia',1,'USD','en','Asia/Jakarta'),
('PH','Philippines',1,'USD','en','Asia/Manila'),
('BD','Bangladesh',1,'USD','en','Asia/Dhaka'),
('LK','Sri Lanka',1,'USD','en','Asia/Colombo'),
('NP','Nepal',1,'USD','en','Asia/Kathmandu'),
('AU','Australia',1,'AUD','en','Australia/Sydney'),
('AE','United Arab Emirates',2,'AED','ar','Asia/Dubai'),
('SA','Saudi Arabia',2,'SAR','ar','Asia/Riyadh'),
('QA','Qatar',2,'USD','ar','Asia/Qatar'),
('OM','Oman',2,'USD','ar','Asia/Muscat'),
('KW','Kuwait',2,'USD','ar','Asia/Kuwait'),
('BH','Bahrain',2,'USD','ar','Asia/Bahrain'),
('JO','Jordan',2,'USD','ar','Asia/Amman'),
('EG','Egypt',3,'EGP','ar','Africa/Cairo'),
('MA','Morocco',3,'USD','fr','Africa/Casablanca'),
('DZ','Algeria',3,'USD','fr','Africa/Algiers'),
('TN','Tunisia',3,'USD','fr','Africa/Tunis'),
('SN','Senegal',3,'USD','fr','Africa/Dakar'),
('CI','Côte d''Ivoire',3,'USD','fr','Africa/Abidjan'),
('CM','Cameroon',3,'USD','fr','Africa/Douala'),
('KE','Kenya',3,'KES','en','Africa/Nairobi'),
('UG','Uganda',3,'USD','en','Africa/Kampala'),
('TZ','Tanzania',3,'USD','en','Africa/Dar_es_Salaam'),
('RW','Rwanda',3,'USD','en','Africa/Kigali'),
('ET','Ethiopia',3,'USD','en','Africa/Addis_Ababa'),
('NG','Nigeria',3,'NGN','en','Africa/Lagos'),
('GH','Ghana',3,'USD','en','Africa/Accra'),
('ZA','South Africa',3,'ZAR','en','Africa/Johannesburg'),
('GB','United Kingdom',4,'GBP','en','Europe/London'),
('IE','Ireland',4,'EUR','en','Europe/Dublin'),
('FR','France',4,'EUR','fr','Europe/Paris'),
('BE','Belgium',4,'EUR','fr','Europe/Brussels'),
('CH','Switzerland',4,'EUR','fr','Europe/Zurich'),
('ES','Spain',4,'EUR','es','Europe/Madrid'),
('DE','Germany',4,'EUR','en','Europe/Berlin'),
('NL','Netherlands',4,'EUR','en','Europe/Amsterdam'),
('IT','Italy',4,'EUR','en','Europe/Rome'),
('US','United States',5,'USD','en','America/New_York'),
('CA','Canada',5,'CAD','en','America/Toronto'),
('MX','Mexico',5,'MXN','es','America/Mexico_City'),
('CO','Colombia',5,'USD','es','America/Bogota'),
('AR','Argentina',5,'USD','es','America/Argentina/Buenos_Aires'),
('CL','Chile',5,'USD','es','America/Santiago'),
('PE','Peru',5,'USD','es','America/Lima'),
('BR','Brazil',5,'BRL','en','America/Sao_Paulo');

INSERT INTO chapters (country_iso2, city, name, slug, description, status) VALUES
('IN','Mumbai','Mumbai','india-mumbai','Founding chapter for hospital and health-system technology leaders across Mumbai and Maharashtra.','active'),
('AE','Dubai','Dubai','uae-dubai','Healthcare CIOs and digital health leaders across the Emirates.','forming'),
('SA','Riyadh','Riyadh','saudi-arabia-riyadh','Health-system technology leaders across the Kingdom.','forming'),
('KE','Nairobi','Nairobi','kenya-nairobi','Hospital and public-health technology leaders in East Africa.','forming'),
('NG','Lagos','Lagos','nigeria-lagos','Healthcare technology leaders across West Africa.','forming'),
('GB','London','London','uk-london','NHS trusts, private providers and health-tech leaders in the UK.','forming'),
('FR','Paris','Paris','france-paris','Directeurs des systèmes d''information hospitaliers en France.','forming'),
('ES','Madrid','Madrid','spain-madrid','Responsables de tecnología sanitaria en España.','forming'),
('MX','Mexico City','Mexico City','mexico-mexico-city','Healthcare technology leaders across Mexico and Central America.','forming'),
('SG','Singapore','Singapore','singapore','Healthcare technology leaders across Southeast Asia.','forming');

INSERT INTO circles (name, slug, kind, description) VALUES
('Hospitals & health systems','hospitals-health-systems','segment','Provider-side technology leaders: EHR/HIS, infrastructure, clinical systems.'),
('Public health & government','public-health','segment','Ministries, national programmes and public hospital networks.'),
('Payers & insurers','payers','segment','Technology leaders at health insurers and TPAs.'),
('Diagnostics & labs','diagnostics','segment','Lab, imaging and diagnostic-chain technology leaders.'),
('Pharma & life sciences','pharma-life-sciences','segment','IT and digital leaders in pharma, medtech and life sciences.'),
('CISOs & security','cisos','role','Security leaders. Threat sharing, audits, ransomware readiness.'),
('Clinical informatics (CMIO/CNIO)','clinical-informatics','role','Physician and nurse informatics leaders.'),
('AI in healthcare','ai-in-healthcare','topic','Practical AI: what is working in production, governance and evaluation.'),
('Interoperability & FHIR','interoperability','topic','HL7 FHIR, national health-data exchanges, integration engines.');

INSERT INTO ad_slots (code, name, width, height, placement) VALUES
('header_leaderboard','Header leaderboard',728,90,'Top of public pages and articles hub (320x50 on mobile)'),
('sidebar_rect','Sidebar rectangle',300,250,'Article pages, chapter pages, events'),
('sidebar_tall','Sidebar half-page',300,600,'Article pages (desktop only)'),
('feed_native','In-feed native',0,0,'Member feed, max 1 per N posts'),
('in_article','In-article',300,250,'Between article paragraphs'),
('footer_banner','Footer banner',728,90,'All public pages');

INSERT INTO ad_campaigns (advertiser, name, slot_code, headline, body, target_url, starts_on, ends_on, is_house, status) VALUES
('House','Founding members','sidebar_rect','Become a founding member','Free membership for verified healthcare technology leaders.','/en/join','2026-01-01','2030-12-31',1,'active'),
('House','Advertise','header_leaderboard','Reach healthcare CIOs worldwide','Sponsor the club. Aggregated reporting, no member data shared.','/en/advertise','2026-01-01','2030-12-31',1,'active'),
('House','Founding feed','feed_native','Invite a peer','Know a healthcare CIO who should be here? Send them to the join page.','/en/join','2026-01-01','2030-12-31',1,'active'),
('House','Footer','footer_banner','Start a chapter in your city','Lead the club in your country. Apply as a chapter lead.','/en/contact?kind=chapter_lead','2026-01-01','2030-12-31',1,'active');

-- Email templates. Placeholders: {{name}} {{site}} {{link}} {{ref}} {{title}} {{when}} {{note}} {{message}}
INSERT INTO comm_templates (code, channel, lang, subject, body) VALUES
('verify_email','email','en','Confirm your email for {{site}}','Hello {{name}},\n\nPlease confirm your email address to complete your application:\n{{link}}\n\nThis link expires in 48 hours.\n\n{{site}}'),
('verify_email','email','ar','تأكيد بريدك الإلكتروني في {{site}}','مرحباً {{name}}،\n\nيرجى تأكيد بريدك الإلكتروني لإكمال طلب العضوية:\n{{link}}\n\nتنتهي صلاحية الرابط خلال 48 ساعة.\n\n{{site}}'),
('verify_email','email','fr','Confirmez votre e-mail pour {{site}}','Bonjour {{name}},\n\nMerci de confirmer votre adresse e-mail pour finaliser votre candidature :\n{{link}}\n\nCe lien expire dans 48 heures.\n\n{{site}}'),
('verify_email','email','es','Confirme su correo para {{site}}','Hola {{name}}:\n\nConfirme su dirección de correo para completar su solicitud:\n{{link}}\n\nEste enlace caduca en 48 horas.\n\n{{site}}'),
('application_received','email','en','We received your application','Hello {{name}},\n\nThank you for applying to {{site}}. A chapter admin will review your application, usually within 3 working days. We will email you when it is decided.\n\n{{site}}'),
('application_received','email','ar','استلمنا طلبك','مرحباً {{name}}،\n\nشكراً لتقديمك طلب الانضمام إلى {{site}}. سيراجع مسؤول الفرع طلبك خلال 3 أيام عمل عادةً، وسنراسلك عند اتخاذ القرار.\n\n{{site}}'),
('application_received','email','fr','Nous avons reçu votre candidature','Bonjour {{name}},\n\nMerci pour votre candidature à {{site}}. Un administrateur de chapitre l''examinera, généralement sous 3 jours ouvrés. Nous vous écrirons dès qu''une décision sera prise.\n\n{{site}}'),
('application_received','email','es','Hemos recibido su solicitud','Hola {{name}}:\n\nGracias por solicitar su ingreso en {{site}}. Un administrador del capítulo revisará su solicitud, normalmente en 3 días hábiles. Le escribiremos cuando haya una decisión.\n\n{{site}}'),
('application_approved','email','en','Welcome to {{site}}','Hello {{name}},\n\nYour membership is approved. Sign in here:\n{{link}}\n\nA good first step: complete your profile and answer one question in your circle.\n\n{{site}}'),
('application_approved','email','ar','مرحباً بك في {{site}}','مرحباً {{name}}،\n\nتمت الموافقة على عضويتك. سجّل الدخول من هنا:\n{{link}}\n\nخطوة أولى جيدة: أكمل ملفك الشخصي وأجب عن سؤال واحد في دائرتك.\n\n{{site}}'),
('application_approved','email','fr','Bienvenue au {{site}}','Bonjour {{name}},\n\nVotre adhésion est approuvée. Connectez-vous ici :\n{{link}}\n\nPremière étape conseillée : complétez votre profil et répondez à une question dans votre cercle.\n\n{{site}}'),
('application_approved','email','es','Bienvenido a {{site}}','Hola {{name}}:\n\nSu membresía ha sido aprobada. Inicie sesión aquí:\n{{link}}\n\nUn buen primer paso: complete su perfil y responda una pregunta en su círculo.\n\n{{site}}'),
('application_rejected','email','en','About your application to {{site}}','Hello {{name}},\n\nThank you for your interest. We could not approve your application at this time.\n\n{{note}}\n\nMembership is limited to healthcare technology leaders with strategic responsibility. You may reply to this email if your role has changed.\n\n{{site}}'),
('application_rejected','email','ar','بخصوص طلبك إلى {{site}}','مرحباً {{name}}،\n\nشكراً لاهتمامك. لم نتمكن من الموافقة على طلبك في الوقت الحالي.\n\n{{note}}\n\nالعضوية مقتصرة على قادة تقنية الرعاية الصحية ذوي المسؤولية الاستراتيجية.\n\n{{site}}'),
('application_rejected','email','fr','Votre candidature au {{site}}','Bonjour {{name}},\n\nMerci de votre intérêt. Nous ne pouvons pas approuver votre candidature pour le moment.\n\n{{note}}\n\nL''adhésion est réservée aux dirigeants technologiques de la santé ayant une responsabilité stratégique.\n\n{{site}}'),
('application_rejected','email','es','Sobre su solicitud a {{site}}','Hola {{name}}:\n\nGracias por su interés. No hemos podido aprobar su solicitud en este momento.\n\n{{note}}\n\nLa membresía está reservada a líderes de tecnología sanitaria con responsabilidad estratégica.\n\n{{site}}'),
('reset_password','email','en','Reset your password','Hello {{name}},\n\nUse this link to set a new password. It expires in 1 hour and works once:\n{{link}}\n\nIf you did not ask for this, ignore this email.\n\n{{site}}'),
('reset_password','email','ar','إعادة تعيين كلمة المرور','مرحباً {{name}}،\n\nاستخدم هذا الرابط لتعيين كلمة مرور جديدة. صالح لمدة ساعة واحدة ولمرة واحدة:\n{{link}}\n\nإذا لم تطلب ذلك، تجاهل هذه الرسالة.\n\n{{site}}'),
('reset_password','email','fr','Réinitialisez votre mot de passe','Bonjour {{name}},\n\nUtilisez ce lien pour définir un nouveau mot de passe. Il expire dans 1 heure et ne fonctionne qu''une fois :\n{{link}}\n\nSi vous n''êtes pas à l''origine de cette demande, ignorez cet e-mail.\n\n{{site}}'),
('reset_password','email','es','Restablezca su contraseña','Hola {{name}}:\n\nUse este enlace para establecer una nueva contraseña. Caduca en 1 hora y funciona una sola vez:\n{{link}}\n\nSi no lo solicitó, ignore este correo.\n\n{{site}}'),
('query_received','email','en','We received your message ({{ref}})','Hello {{name}},\n\nThank you for contacting {{site}}. Your reference is {{ref}}. Our team will reply within 2 working days.\n\n{{site}}'),
('query_received','email','ar','استلمنا رسالتك ({{ref}})','مرحباً {{name}}،\n\nشكراً لتواصلك مع {{site}}. رقمك المرجعي {{ref}}. سيرد فريقنا خلال يومي عمل.\n\n{{site}}'),
('query_received','email','fr','Nous avons reçu votre message ({{ref}})','Bonjour {{name}},\n\nMerci d''avoir contacté {{site}}. Votre référence est {{ref}}. Notre équipe vous répondra sous 2 jours ouvrés.\n\n{{site}}'),
('query_received','email','es','Hemos recibido su mensaje ({{ref}})','Hola {{name}}:\n\nGracias por contactar con {{site}}. Su referencia es {{ref}}. Nuestro equipo responderá en 2 días hábiles.\n\n{{site}}'),
('query_reply','email','en','Re: your message ({{ref}})','Hello {{name}},\n\n{{message}}\n\n{{site}}'),
('query_reply','email','ar','رد: رسالتك ({{ref}})','مرحباً {{name}}،\n\n{{message}}\n\n{{site}}'),
('query_reply','email','fr','Re : votre message ({{ref}})','Bonjour {{name}},\n\n{{message}}\n\n{{site}}'),
('query_reply','email','es','Re: su mensaje ({{ref}})','Hola {{name}}:\n\n{{message}}\n\n{{site}}'),
('event_rsvp','email','en','You are registered: {{title}}','Hello {{name}},\n\nYou are registered for {{title}} on {{when}}.\nYour check-in code: {{ref}}\nEvent page: {{link}}\n\n{{site}}'),
('event_rsvp','email','ar','تم تسجيلك: {{title}}','مرحباً {{name}}،\n\nتم تسجيلك في {{title}} بتاريخ {{when}}.\nرمز الحضور: {{ref}}\nصفحة الفعالية: {{link}}\n\n{{site}}'),
('event_rsvp','email','fr','Inscription confirmée : {{title}}','Bonjour {{name}},\n\nVous êtes inscrit(e) à {{title}} le {{when}}.\nCode d''enregistrement : {{ref}}\nPage de l''événement : {{link}}\n\n{{site}}'),
('event_rsvp','email','es','Inscripción confirmada: {{title}}','Hola {{name}}:\n\nEstá inscrito en {{title}} el {{when}}.\nCódigo de registro: {{ref}}\nPágina del evento: {{link}}\n\n{{site}}'),
('new_application','email','en','New membership application: {{name}}','A new application is waiting for review.\n\n{{message}}\n\nReview: {{link}}');

-- WhatsApp: Meta-approved template names must be created in WhatsApp Manager with matching parameters.
INSERT INTO comm_templates (code, channel, lang, subject, body, wa_template_name) VALUES
('application_approved','whatsapp','en','','Hello {{1}}, your {{2}} membership is approved. Sign in: {{3}}','membership_approved'),
('event_rsvp','whatsapp','en','','Hello {{1}}, you are registered for {{2}} on {{3}}. Check-in code: {{4}}','event_registered'),
('query_received','whatsapp','en','','Hello {{1}}, we received your message. Reference {{2}}. We reply within 2 working days.','query_received');