-- SHARK CRM Multi Language System
-- Run once in phpMyAdmin. Safe to re-run.

CREATE TABLE IF NOT EXISTS languages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(10) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  direction ENUM('ltr','rtl') NOT NULL DEFAULT 'ltr',
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  status TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS translation_keys (
  id INT AUTO_INCREMENT PRIMARY KEY,
  `key` VARCHAR(191) NOT NULL UNIQUE,
  group_name VARCHAR(80) NULL,
  description VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS translations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  language_id INT NOT NULL,
  key_id INT NOT NULL,
  value TEXT NOT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_lang_key (language_id, key_id),
  KEY idx_trans_language (language_id),
  KEY idx_trans_key (key_id),
  CONSTRAINT fk_trans_language FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE,
  CONSTRAINT fk_trans_key FOREIGN KEY (key_id) REFERENCES translation_keys(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET @has_account_lang := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='accounts' AND COLUMN_NAME='default_language_code');
SET @sql := IF(@has_account_lang=0, 'ALTER TABLE accounts ADD COLUMN default_language_code VARCHAR(10) DEFAULT ''en''', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_user_lang := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='users' AND COLUMN_NAME='language_code');
SET @sql := IF(@has_user_lang=0, 'ALTER TABLE users ADD COLUMN language_code VARCHAR(10) DEFAULT NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

INSERT IGNORE INTO languages (code, name, direction, is_default, status) VALUES
('en', 'English', 'ltr', 1, 1),
('ar', 'العربية', 'rtl', 0, 1);

-- Core translation keys used by PHP/JS. Auto captured UI text will be added while browsing pages.
INSERT IGNORE INTO translation_keys (`key`, group_name, description) VALUES
('menu.dashboard','menu','Dashboard'),('menu.leads','menu','Leads'),('menu.pipeline','menu','Pipeline'),('menu.customers','menu','Customers'),('menu.items','menu','Items'),('menu.quotations','menu','Quotations'),('menu.campaigns','menu','Campaigns'),('menu.audience','menu','Audience'),('menu.occasions','menu','Occasions'),('menu.settings','menu','Settings'),('menu.reminders','menu','Reminders'),('menu.activities','menu','Activities'),('menu.message_logs','menu','Message Logs'),('menu.deals','menu','Deals'),('menu.customer_report','menu','Customer Report'),
('buttons.save','buttons','Save'),('buttons.cancel','buttons','Cancel'),('buttons.add','buttons','Add'),('buttons.edit','buttons','Edit'),('buttons.delete','buttons','Delete'),('buttons.update','buttons','Update'),('buttons.search','buttons','Search'),('buttons.filter','buttons','Filter'),('buttons.export','buttons','Export'),('buttons.import','buttons','Import'),('buttons.send','buttons','Send'),('buttons.close','buttons','Close'),
('settings.languages','settings','Languages'),('languages.title','languages','Languages'),('languages.add_language','languages','Add Language'),('languages.translation_manager','languages','Translation Manager'),('languages.code','languages','Code'),('languages.name','languages','Name'),('languages.direction','languages','Direction'),('languages.default','languages','Default'),('languages.status','languages','Status'),('languages.key','languages','Key'),('languages.english_fallback','languages','English / Fallback'),('languages.translation','languages','Translation'),('languages.group','languages','Group'),('languages.save_translations','languages','Save Translations'),('languages.set_default','languages','Set Default'),('languages.active','languages','Active'),('languages.inactive','languages','Inactive'),
('js.request_failed','js','Request failed'),('js.creating','js','Creating...'),('js.adding','js','Adding...'),('js.updating','js','Updating...'),('js.saving','js','Saving...'),('js.sending','js','Sending...'),('js.removing','js','Removing...'),('js.loading','js','Loading...'),('js.working','js','Working...'),
('messages.unauthorized','messages','Unauthorized'),('messages.invalid_action','messages','Invalid action'),('messages.request_failed','messages','Request failed'),('messages.saved_successfully','messages','Saved successfully'),('messages.deleted_successfully','messages','Deleted successfully');

INSERT IGNORE INTO translations (language_id, key_id, value)
SELECT l.id, k.id, k.description FROM languages l JOIN translation_keys k WHERE l.code='en';

INSERT IGNORE INTO translations (language_id, key_id, value)
SELECT l.id, k.id,
CASE k.`key`
WHEN 'menu.dashboard' THEN 'لوحة التحكم' WHEN 'menu.leads' THEN 'العملاء المحتملون' WHEN 'menu.pipeline' THEN 'المراحل' WHEN 'menu.customers' THEN 'الزبائن' WHEN 'menu.items' THEN 'المنتجات' WHEN 'menu.quotations' THEN 'عروض الأسعار' WHEN 'menu.campaigns' THEN 'الحملات' WHEN 'menu.audience' THEN 'الجمهور' WHEN 'menu.occasions' THEN 'المناسبات' WHEN 'menu.settings' THEN 'الإعدادات' WHEN 'menu.reminders' THEN 'التذكيرات' WHEN 'menu.activities' THEN 'النشاطات' WHEN 'menu.message_logs' THEN 'سجل الرسائل' WHEN 'menu.deals' THEN 'الصفقات' WHEN 'menu.customer_report' THEN 'تقرير الزبائن'
WHEN 'buttons.save' THEN 'حفظ' WHEN 'buttons.cancel' THEN 'إلغاء' WHEN 'buttons.add' THEN 'إضافة' WHEN 'buttons.edit' THEN 'تعديل' WHEN 'buttons.delete' THEN 'حذف' WHEN 'buttons.update' THEN 'تحديث' WHEN 'buttons.search' THEN 'بحث' WHEN 'buttons.filter' THEN 'فلترة' WHEN 'buttons.export' THEN 'تصدير' WHEN 'buttons.import' THEN 'استيراد' WHEN 'buttons.send' THEN 'إرسال' WHEN 'buttons.close' THEN 'إغلاق'
WHEN 'settings.languages' THEN 'اللغات' WHEN 'languages.title' THEN 'اللغات' WHEN 'languages.add_language' THEN 'إضافة لغة' WHEN 'languages.translation_manager' THEN 'إدارة الترجمات' WHEN 'languages.code' THEN 'الكود' WHEN 'languages.name' THEN 'الاسم' WHEN 'languages.direction' THEN 'الاتجاه' WHEN 'languages.default' THEN 'الافتراضية' WHEN 'languages.status' THEN 'الحالة' WHEN 'languages.key' THEN 'المفتاح' WHEN 'languages.english_fallback' THEN 'النص الأساسي' WHEN 'languages.translation' THEN 'الترجمة' WHEN 'languages.group' THEN 'المجموعة' WHEN 'languages.save_translations' THEN 'حفظ الترجمات' WHEN 'languages.set_default' THEN 'تعيين كافتراضية' WHEN 'languages.active' THEN 'فعالة' WHEN 'languages.inactive' THEN 'غير فعالة'
WHEN 'js.request_failed' THEN 'فشل الطلب' WHEN 'js.creating' THEN 'جاري الإنشاء...' WHEN 'js.adding' THEN 'جاري الإضافة...' WHEN 'js.updating' THEN 'جاري التحديث...' WHEN 'js.saving' THEN 'جاري الحفظ...' WHEN 'js.sending' THEN 'جاري الإرسال...' WHEN 'js.removing' THEN 'جاري الحذف...' WHEN 'js.loading' THEN 'جاري التحميل...' WHEN 'js.working' THEN 'جاري العمل...'
WHEN 'messages.unauthorized' THEN 'غير مصرح' WHEN 'messages.invalid_action' THEN 'إجراء غير صالح' WHEN 'messages.request_failed' THEN 'فشل الطلب' WHEN 'messages.saved_successfully' THEN 'تم الحفظ بنجاح' WHEN 'messages.deleted_successfully' THEN 'تم الحذف بنجاح'
ELSE k.description END
FROM languages l JOIN translation_keys k WHERE l.code='ar';
