-- Step 1: Create temporary table with old structure
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_tbl_contract LIKE tbl_contract;

-- If the old table doesn't exist, create the structure
CREATE TABLE IF NOT EXISTS tmp_tbl_contract (
    Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(50) DEFAULT NULL,
    GroupPrefix VARCHAR(50) DEFAULT NULL,
    account_type INT(10) DEFAULT 0,
    country_id INT(10) DEFAULT 0,
    embassy_id INT(10) DEFAULT 0,
    address_city VARCHAR(30) DEFAULT NULL,
    username VARCHAR(50) DEFAULT NULL,
    tasheel_email VARCHAR(150) DEFAULT NULL,
    tasheel_phone VARCHAR(25) DEFAULT NULL,
    res_phone_country INT(11) DEFAULT 0,
    OnlineVisaPhone VARCHAR(100) DEFAULT NULL,
    connection VARCHAR(100) DEFAULT NULL,
    is_active INT(11) DEFAULT NULL,
    is_deleted INT(11) DEFAULT 0,
    INDEX idx_is_deleted (is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- Step 2: Insert your data here (paste your REPLACE INTO statements)
-- For example:
-- REPLACE INTO tmp_tbl_contract (Id, Name, GroupPrefix, account_type, ...) VALUES
-- (1, 'عقد العمرة', '', 1, ...),
-- ...

-- Step 3: Map and insert into new contracts table
INSERT INTO contracts (
    id, contract_name, username, contract_type, is_active, status,
    embassy_id, group_name_prefix, whatsapp_group_id, email,
    country_code, phone_number, city, primary_client_id,
    umrah_company_id, tourism_company_id, created_at, updated_at
)
SELECT 
    o.Id,
    COALESCE(NULLIF(o.Name, ''), 'Unnamed Contract') AS contract_name,
    COALESCE(NULLIF(o.username, ''), CONCAT('user_', o.Id)) AS username,
    CASE o.account_type
        WHEN 1 THEN 'umrah'
        WHEN 2 THEN 'hajj'
        WHEN 3 THEN 'external'
        WHEN 4 THEN 'tourism'
        ELSE 'umrah'
    END AS contract_type,
    COALESCE(o.is_active, 1) = 1 AS is_active,
    'pending' AS status,
    NULLIF(o.embassy_id, 0) AS embassy_id,
    NULLIF(o.GroupPrefix, '') AS group_name_prefix,
    COALESCE(
        (SELECT id FROM whatsapp_groups WHERE group_id = o.connection LIMIT 1),
        1
    ) AS whatsapp_group_id,
    COALESCE(
        NULLIF(o.tasheel_email, ''),
        NULLIF(o.username, ''),
        CONCAT('contract', o.Id, '@example.com')
    ) AS email,
    CASE
        WHEN o.res_phone_country > 0 THEN CONCAT('+', o.res_phone_country)
        ELSE '+966'
    END AS country_code,
    COALESCE(
        NULLIF(o.tasheel_phone, ''),
        NULLIF(o.OnlineVisaPhone, ''),
        '0'
    ) AS phone_number,
    NULLIF(o.address_city, '') AS city,
    1 AS primary_client_id,
    NULL AS umrah_company_id,
    NULL AS tourism_company_id,
    NOW() AS created_at,
    NOW() AS updated_at
FROM tmp_tbl_contract o
WHERE (o.is_deleted = 0 OR o.is_deleted IS NULL)
ON DUPLICATE KEY UPDATE
    contract_name = VALUES(contract_name),
    username = VALUES(username),
    contract_type = VALUES(contract_type),
    is_active = VALUES(is_active),
    embassy_id = VALUES(embassy_id),
    group_name_prefix = VALUES(group_name_prefix),
    whatsapp_group_id = VALUES(whatsapp_group_id),
    email = VALUES(email),
    country_code = VALUES(country_code),
    phone_number = VALUES(phone_number),
    city = VALUES(city),
    updated_at = NOW();

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