-- ============================================
-- Import Agents (tbl_agents) to Clients Table
-- ============================================
-- 
-- This script imports data from the old tbl_agents table structure
-- to the new clients table structure.
--
-- Note: Parent client relationships (parentagent) are set to NULL initially
-- as they reference old IDs. You may need to update these after import
-- based on the code field or name matching.
--
-- Note: Country field (country_id) is imported directly from old table.
-- Ensure country_id values exist in countries table before running this import.
-- ============================================

-- Step 1: Create temporary table for old structure
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_tbl_agents (
    Id INT(10) NOT NULL PRIMARY KEY,
    Code VARCHAR(9) DEFAULT NULL,
    Name_AR VARCHAR(100) DEFAULT NULL,
    Name_EN VARCHAR(100) DEFAULT NULL,
    Address VARCHAR(250) DEFAULT NULL,
    Telephone VARCHAR(15) DEFAULT NULL,
    Mobile VARCHAR(15) DEFAULT NULL,
    Email VARCHAR(100) DEFAULT NULL,
    Owner VARCHAR(100) DEFAULT NULL,
    ResPerson VARCHAR(100) DEFAULT NULL,
    ResPersonMob VARCHAR(100) DEFAULT NULL,
    CompanyNameOnVisa VARCHAR(150) DEFAULT NULL,
    prefix VARCHAR(50) DEFAULT NULL,
    is_deleted INT(11) DEFAULT NULL,
    country_id INT(11) DEFAULT NULL,
    connection VARCHAR(100) DEFAULT NULL,
    accountno VARCHAR(25) DEFAULT NULL,
    parentagent INT(11) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- Step 2: Insert data from old structure
INSERT IGNORE INTO tmp_tbl_agents (Id, Code, Name_AR, Name_EN, Address, Telephone, Mobile, Email, Owner, ResPerson, ResPersonMob, CompanyNameOnVisa, prefix, is_deleted, country_id, connection, accountno, parentagent) VALUES
(1, '1001', ' ', 'Default Client', '', '', '', '', '', '', '', '', 'default', 0, 968, NULL, NULL, NULL),
(2, '1002', 'Tasheel Travel', '', '', '', '', '', '', '', '', 'تسهيل الدولية للسفر والسياحة', 'Tasheel', 0, 968, '120363042539673256@g.us', '', 0),
(3, '1003', 'Adam Travel USA', '', '', '', '', '', '', '', '', 'Adam Travel USA', 'ADAM', 0, 1, '963966624766-1547152038@g.us', '', 0),
(4, '1004', 'Qwafil Al Tawheed', '', '', '', '', '', '', '', '', 'قوافل التوحيد', 'QWAFEL', 0, 964, '120363374741558188@g.us', '', 0),
(5, '1005', 'RAKB', '', '', '', '', '', '', '', '', 'RAKB', 'RAKB', 0, 966, '120363042105049378@g.us', '', 0),
(6, '1006', 'MANAZEL', '', '', '', '', '', '', '', '', '', 'MANAZEL', 0, 966, '120363141828621015@g.us', '', 0),
(7, '1007', 'Galata Tours', '', '', '', '', '', '', '', '', 'Galata Tours', 'Galata', 0, 90, '120363047540561795@g.us', '', 0),
(8, '1008', 'Sky Direct', '', '', '', '', '', '', '', '', '', 'SKYDIRECT', 0, 44, '201030034119-1578928308@g.us', '', 0),
(9, '1009', 'LOUVER TRAVEL', '', '', '', '', '', '', '', '', '', 'LOUVER', 0, 90, '', '', 0),
(10, '1010', 'MARAHEB', '', '', '', '', '', '', '', '', '', 'MARAHEB', 0, 962, '120363162521918560@g.us', '', 9),
(11, '1011', 'Prince Visa LTD', '', '', '', '', '', '', '', '', 'Prince Visa LTD', 'PRINCE', 0, 44, '447956460080-1420752954@g.us', '', 12),
(12, '1012', 'Virgo Business Solutions', '', '', '', '', '', '', '', '', 'فيرجو لحلول الأعمال', 'VIRGO', 0, 20, '', '', 0),
(13, '1013', 'Mustafa Travel', '', '', '', '', '', '', '', '', 'Mustafa Travel', 'RAKB', 0, 221, '120363042105049378@g.us', '', 5),
(14, '1014', 'Vataksi Travel', '', '', '', '', '', '', '', '', '', 'Vataksi', 1, 355, '120363029114809528@g.us', '', 14),
(15, '1015', 'Al Masara Haj & Umrah', '', '', '', '', '', '', '', '', 'شركة المسرة للحج والعمرة', 'Massarah', 0, 964, '120363400080531892@g.us', '', 0),
(16, '1016', 'Vataksi Travel', '', ' - فاتاكسي للسياحة', '', '', '', '', '', '', 'Vataksi Travel', 'Vataksi', 0, 355, '120363029114809528@g.us', '', 0),
(17, '1017', 'Beoot Al Shaam', '', 'اصالة للسياحة والسفر', '120363369162214797@g.us', '', '', '', '', '', '', 'BEOOT', 0, 963, '120363419114415993@g.us', '', 0),
(18, '1018', 'Atlas Tourism', '', '', '', '', '', '', '', '', 'Virgo Business Solutions', 'ATLAS', 0, 7001, '201010272322-1607427933@g.us', '', 12),
(19, '1019', 'AL AHEBA', '', '', '', '', '', '', '', '', 'الأحبة', 'LOUVER', 0, 963, '120363196015849155@g.us', '', 9),
(20, '1019', 'Rawdat Al Molaby Group', '', '', '', '', '', '', '', '', 'روضه الملبي', 'Rawdat', 0, 963, '120363417841732299@g.us', '', 0),
(21, '1020', 'ABRAG ALMADINA', '', '', '', '', '', '', '', '', 'أبراج المدينة للسياحة والسفر', 'ABRAG', 0, 964, '120363404082913465@g.us', '', 0),
(22, '1021', 'AMANOUS \\ AWTAD', '', '', '', '', '', '', '', '', 'أمانوس للسياحة والسفر', 'AMANOUS', 0, 963, '120363160226936106@g.us', '', 40),
(23, '1022', 'Mahattat Al Aalam', '', '', '', '', '', '', '', '', '', 'MAHATTAT', 0, 993, '120363027901438947@g.us', '', 0),
(24, '1023', 'مؤسسة حنوش للسياحة و السفر و العمرة', '', '', '', '', '', '', '', '', 'مؤسسة حنوش للسياحة و السفر و العمرة', 'HANNOUSH', 0, 963, '120363417576294925@g.us', '', 0),
(25, '1024', 'Bab Al salaam Travel', '', '', '', '', '', '', '', '', '', 'MANAZEL', 0, 212, '120363141828621015@g.us', '', 6),
(26, '1025', 'Al Hashmy', '', '', '', '', '', '', '', '', '', 'LOUVER', 0, 963, '120363197640456615@g.us', '', 9),
(27, '1026', 'Adel Travel', '', '', '', '', '', '', '', '', 'Adel Travel', 'RAKB', 0, 62, '120363042105049378@g.us', '', 5),
(28, '1027', 'مؤسسة حنوش للسياحة والسفر', '', '', '', '', '', '', '', '', 'مؤسسة حنوش للسياحة والسفر', 'مهرة', 0, 963, '120363417576294925@g.us', '', 24),
(29, '1028', 'Teranga Travel', '', '', '', '', '', '', '', '', 'Teranga Travel', 'RAKB', 0, 221, '120363042105049378@g.us', '', 5),
(30, '1029', 'ASALAH', '', '', '', '', '', '', '', '', '', 'BEOOT', 0, 963, '120363419114415993@g.us', '', 0),
(31, '1030', 'MAHARET MAKKAH', '', '', '', '', '', '', '', '', 'مؤسسة مهرة مكة للسياحة والسفر والعمرة', 'HANNOUSH', 0, 963, '120363417576294925@g.us', '', 24),
(32, '1031', 'نجمة الساحل', '', '', '', '', '', '', '', '', 'نجمة الساحل', 'negma', 0, 964, '120363402052121911@g.us', '', 0),
(33, '1031', 'AL SHAHD', '', '', '', '', '', '', '', '', '', 'SHAHD', 0, 249, '120363154035407435@g.us', '', 0),
(34, '1032', 'SUNNY LAND', '', '', '', '', '', '', '', '', '', 'BEOOT', 0, 963, '120363419114415993@g.us', '', 0),
(35, '1033', 'AZKA Travel', '', '120363141828621015@g.us', '', '', '', '', '', '', 'AZKA Travel', 'AZKA', 0, 355, '120363404130336385@g.us', '', 0),
(36, '1034', 'AMANOUS HORAS \\ AL WISAM', '', '', '', '', '', '', '', '', 'أمانوس للسياحة والسفر', 'AMANOUS', 0, 963, '120363401337288060@g.us', '', 40),
(37, '1035', 'Al Naqqash', '', '', '', '', '', '', '', '', '', 'NAQQASH', 0, 963, '120363051265990774@g.us', '', 9),
(38, '1036', 'Al-Ehsan Trust LTD', '', '', '', '', '', '', '', '', '', 'EHSAN', 0, 44, '201012121146-1562933842@g.us', '', 12),
(39, '1037', 'AMANOUS \\ MAATHER', '', '', '', '', '', '', '', '', 'أمانوس للسياحة والسفر', 'AMANOUS', 0, 963, '120363419522427439@g.us', '', 40),
(40, '1038', 'AMANOUS', '', '', '', '', '', '', '', '', 'أمانوس للسياحة والسفر', 'AMANOUS', 0, 963, '', '', 0),
(41, '1039', 'AL MULLAH', '', '', '', '', '', '', '', '', 'مؤسسة الملا الدولية', 'LOUVER', 0, 963, '120363196015849155@g.us', '', 9),
(42, '1040', 'ZAIN \\ BAKR', '', '', '', '', '', '', '', '', 'أمانوس للسياحة والسفر', 'AMANOUS', 0, 963, '120363403923189314@g.us', '', 40),
(43, '1042', 'IDEALGATE', '', '', '', '', '', '', '', '', 'IDEALGATE TRAVEL • TICKETS • HAJJ & UMRAH', 'IDEALGATE', 0, 49, '', '', 0),
(44, '1043', 'Al Safy Travel', '', '', '', '', '', '', '', '', 'الصافي للسياحة والسفر', 'ALSAFY', 0, 963, '120363419428550647@g.us', '', 0),
(45, '1044', 'Vataksi Travel Arabic', '', '', '', '', '', '', '', '', 'فاتاكسي للسياحة', 'Vataksi', 0, 355, '120363029114809528@g.us', '', 16),
(46, '1045', 'NOOR ALMAARIF', '', '', '', '', '', '', '', '', 'نور المعارف للحج و العمرة', 'NOOR', 0, 964, '120363403363400126@g.us', '', 0),
(47, '1046', 'نور اليقين', '', '', '', '', '', '', '', '', 'نور اليقين', 'Beoot', 0, 963, '120363419114415993@g.us', '', 0),
(48, '1047', 'Caravan Tours', '', '', '', '', '', '', '', '', '', 'caravan', 0, 44, '963966624766-1570887396@g.us', '', 0),
(49, '1048', 'تيماسك', '', '', '', '', '', '', '', '', 'تيماسك للسياحة والسفر', 'تيماسك', 0, 963, '', '', 0),
(50, '1049', 'Mahsuri Travel and Tours', '', '', '', '', '', '', '', '', '', 'Mahsuri', 0, 61, '120363025417494569@g.us', '', 12),
(51, '1050', 'Amanous MNHAJ', '', '', '', '', '', '', '', '', 'أمانوس للسياحة والسفر', 'AMANOUS', 0, 963, '120363403628649400@g.us', '', 40),
(52, '1051', 'AMANOUS AJYAD / QWAFIL', '', '', '', '', '', '', '', '', 'أمانوس للسياحة والسفر', 'AMANOUS', 0, 963, '120363402954057626@g.us', '', 40),
(53, '1052', 'AMANOS ALKHATM HASOOB SYR', '', '', '', '', '', '', '', '', 'أمانوس للسياحة والسفر', 'AMANOUS', 0, 963, '120363420007286497@g.us', '', 40),
(54, '1053', 'AL BAZ AL NAADY', '', '', '', '', '', '', '', '', 'أمانوس للسياحة و السفر', 'AMANOUS', 0, 963, '120363423456853469@g.us', '', 40),
(55, '1041', 'Test', '', '', '', '', '', '', '', '', '', 'Test', 0, 44, '201557356352', '', 20),
(56, '1055', 'AMANOUS WARKAA SHABAA', '', '', '', '', '', '', '', '', 'أمانوس للسياحة والسفر', 'AMANOUS', 0, 963, '120363419641509911@g.us', '', 40),
(57, '1056', 'VATAKSI ARABIC', '', '', '', '', '', '', '', '', 'فاتاكسي للسياحة', 'VATAKSI', 0, 355, '120363029114809528@g.us', '', 16),
(58, '1057', 'Amanos ALBOSTAN TAAEF', '', '', '', '', '', '', '', '', 'أمانوس للسياحة والسفر', 'AMANOUS', 0, 963, '120363419560016168@g.us', '', 40),
(59, '1058', 'IDEALGATE ALMNHAJ', '', '', '', '', '', '', '', '', 'IDEALGATE TRAVEL • TICKETS • HAJJ & UMRAH', 'IDEALGATE', 0, 49, '120363403334587690@g.us', '', 43),
(60, '1059', 'IDEALGATE BAKR', '', '', '', '', '', '', '', '', 'IDEALGATE TRAVEL • TICKETS • HAJJ & UMRAH', 'IDEALGATE', 0, 49, '120363230454824349@g.us', '', 43),
(61, '1060', 'القادري', '', '', '', '', '', '', '', '', 'القادري', 'Kadri', 0, 963, '', '', 17),
(62, '1061', 'AMANOUS MOISHA AL DYAFAH', '', '', '', '', '', '', '', '', 'أمانوس للسياحة والسفر', 'AMANOUS', 0, 963, '120363402945300262@g.us', '', 40),
(63, '1062', 'AMANOUS CHARAF Z', '', '', '', '', '', '', '', '', 'أمانوس للسياحة والسفر', 'AMANOUS', 0, 963, '120363403188089170@g.us', '', 40),
(64, '1063', 'AMANOS AL MASAR', '', '', '', '', '', '', '', '', 'أمانوس للسياحة والسفر', 'AMANOUS', 0, 963, '120363421462079367@g.us', '', 40),
(65, '1064', 'رواند', '', '', '', '', '', '', '', '', 'رواند', 'RAKB', 0, 39, '120363042105049378@g.us', '', 5),
(66, '1065', 'AMANOUS SAHL HORAAN ALDEWANYA', '', '', '', '', '', '', '', '', 'أمانوس للسياحة والسفر', 'AMANOUS', 0, 963, '120363406750249647@g.us', '', 40),
(67, '1066', 'AMANOUS AL SHARI / ALSHARIAH', '', '', '', '', '', '', '', '', 'أمانوس للسياحة والسفر', 'AMANOUS', 0, 963, '120363402959248322@g.us', '', 40),
(68, '1067', 'MARITTA \\ ALEMAN', '', '', '', '', '', '', '', '', '', 'LOUVER', 0, 963, '120363404962272839@g.us', '', 9),
(69, '1068', 'SHAM AL MAJD \\ ROAYA', '', '', '', '', '', '', '', '', 'أمانوس للسياحة والسفر', 'AMANOUS', 0, 963, '120363425096503213@g.us', '', 40),
(70, '1069', 'AMANOS GOLDIN HOLIDAYS', '', '', '', '', '', '', '', '', 'أمانوس للسياحة والسفر', 'AMANOUS', 0, 963, '120363406986593908@g.us', '', 40),
(71, '1070', 'Amanos ITLALT MAKAH NAWA', '', '', '', '', '', '', '', '', 'أمانوس للسياحة والسفر', 'AMANOUS', 0, 963, '120363422667346628@g.us', '', 40),
(72, '1071', 'AMANOUS RAWAFI ALAHLAM ALGIZA', '', '', '', '', '', '', '', '', 'أمانوس للسياحة والسفر', 'AMANOUS', 0, 44, '120363402743665242@g.us', '', 40),
(73, '1072', 'SHAHAD AL HARAMEN', '', '', '', '', '', '', '', '', 'شهد الحرمين للسياحة والسفر', 'SHAHAD AL HARAMEN SYR', 0, 963, '120363422053495324@g.us', '', 0),
(74, '1073', 'RAWAHEL AL MASHAER', '', 'شركة رواحل المشاعر', '', '', '', '', '', '', '', 'RAWAHEL AL MASHAER', 0, 963, '120363421134642852@g.us', '', 0),
(75, '1074', 'LOUVER AHEBBAH RAKB', '', '', '', '', '', '', '', '', 'مؤسسة الملا الدولية', 'LOUVER', 0, 963, '120363145118638217@g.us', '', 9),
(76, '1075', 'ASSALAM REISEN', '', '', '', '', '', '', '', '', 'Assalam Travel Agency  GmbH', 'ASSALAM REISEN', 0, 49, '120363043821635040@g.us', '', 0),
(77, '1076', 'شركة الفرقان', '', '', '', '', '', '', '', '', 'PT ALFURQON BINA ILLAHI', 'Mahtat', 0, 62, '120363027901438947@g.us', '', 23),
(78, '1077', 'Amanos ALMAMORA', '', '', '', '', '', '', '', '', '', 'AMANOUS', 0, 963, '120363425886931429@g.us', '', 40),
(79, '1078', 'Amanos BAWABET MUSAFIR', '', '', '', '', '', '', '', '', '', 'AMANOUS', 0, 963, '120363423407397498@g.us', '', 40),
(80, '1079', 'AMANOUS \\ ALTALBIA \\ RAWABI', '', '', '', '', '', '', '', '', 'أمانوس للسياحة والسفر', 'AMANOUS', 0, 963, '120363425947494674@g.us', '', 40),
(81, '1080', 'AMANOUS / FADI / AWTAD', '', '', '', '', '', '', '', '', '', 'AMANOUS', 0, 963, '120363403561906646@g.us', '', 40),
(82, '1081', 'Kiwan / Nigm Al Asema', '', '', '', '', '', '', '', '', 'أمانوس للسياحة و السفر', 'AMANOUS', 0, 963, '120363424668855284@g.us', '', 40);

-- Step 3: Map and insert into new clients table
INSERT INTO clients (
    id, name, code, country_id, address, email, phone, 
    whatsapp_group_id, account_id, status, main_language,
    created_at, updated_at
)
SELECT 
    o.Id AS id,
    COALESCE(NULLIF(o.Name_EN, ''), NULLIF(o.Name_AR, ''), 'Unnamed Client') AS name,
    NULLIF(o.Code, '') AS code,
    o.country_id AS country_id,
    NULLIF(o.Address, '') AS address,
    NULLIF(o.Email, '') AS email,
    COALESCE(NULLIF(o.Mobile, ''), NULLIF(o.Telephone, '')) AS phone,
    NULLIF(o.connection, '') AS whatsapp_group_id,
    NULLIF(o.accountno, '') AS account_id,
    CASE WHEN o.is_deleted = 1 THEN 'inactive' ELSE 'active' END AS status,
    CASE WHEN o.Name_AR IS NOT NULL AND o.Name_AR != '' THEN 'ar' ELSE 'en' END AS main_language,
    NOW() AS created_at,
    NOW() AS updated_at
FROM tmp_tbl_agents o
WHERE (o.is_deleted = 0 OR o.is_deleted IS NULL)
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    code = VALUES(code),
    country_id = VALUES(country_id),
    address = VALUES(address),
    email = VALUES(email),
    phone = VALUES(phone),
    whatsapp_group_id = VALUES(whatsapp_group_id),
    account_id = VALUES(account_id),
    status = VALUES(status),
    main_language = VALUES(main_language),
    updated_at = NOW();

-- Step 4: Drop temporary table
DROP TEMPORARY TABLE IF EXISTS tmp_tbl_agents;
