-- ============================================================================
-- Migration Script: IA Segments from v1 (whizzmailer_swissbell2) to v2 (whizzmailer_swissbelhotelv2)
--
-- Migrates: 12 IA segments, ~2033 contacts, segment-contact mappings, DNC records
-- Safe to run multiple times (idempotent)
-- ============================================================================

-- Use the target (v2) database
USE whizzmailer_swissbelhotelv2;

-- ============================================================================
-- STEP 0: Create temporary mapping tables
-- ============================================================================

DROP TEMPORARY TABLE IF EXISTS tmp_segment_map;
CREATE TEMPORARY TABLE tmp_segment_map (
    old_id INT UNSIGNED NOT NULL,
    new_id INT UNSIGNED DEFAULT NULL,
    seg_name VARCHAR(191) NOT NULL,
    PRIMARY KEY (old_id)
) ENGINE=InnoDB;

DROP TEMPORARY TABLE IF EXISTS tmp_contact_map;
CREATE TEMPORARY TABLE tmp_contact_map (
    old_id BIGINT UNSIGNED NOT NULL,
    new_id BIGINT UNSIGNED DEFAULT NULL,
    email VARCHAR(191) DEFAULT NULL,
    is_new TINYINT(1) DEFAULT 0,
    PRIMARY KEY (old_id),
    INDEX idx_email (email)
) ENGINE=InnoDB;

-- ============================================================================
-- STEP 1: Create 12 IA Segments in v2
-- ============================================================================

-- Insert segments that don't already exist (check by alias for idempotency)
INSERT INTO lead_lists (is_published, date_added, created_by, created_by_user, name, description, alias, public_name, filters, is_global, is_preference_center)
SELECT 1, NOW(), 1, 'admin', 'IA - ANZAC', NULL, 'ia-anzac', 'IA - ANZAC', 'a:0:{}', 1, 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM lead_lists WHERE alias = 'ia-anzac');

INSERT INTO lead_lists (is_published, date_added, created_by, created_by_user, name, description, alias, public_name, filters, is_global, is_preference_center)
SELECT 1, NOW(), 1, 'admin', 'IA - Central Asia', NULL, 'ia-central-asia', 'IA - Central Asia', 'a:0:{}', 1, 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM lead_lists WHERE alias = 'ia-central-asia');

INSERT INTO lead_lists (is_published, date_added, created_by, created_by_user, name, description, alias, public_name, filters, is_global, is_preference_center)
SELECT 1, NOW(), 1, 'admin', 'IA - General Managers', NULL, 'ia-general-managers', 'IA - General Managers', 'a:0:{}', 1, 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM lead_lists WHERE alias = 'ia-general-managers');

INSERT INTO lead_lists (is_published, date_added, created_by, created_by_user, name, description, alias, public_name, filters, is_global, is_preference_center)
SELECT 1, NOW(), 1, 'admin', 'IA - GM & Ex', 'Internal Announcement - GM & Ex\n\nfrom folder ID', 'ia-gm-ex', 'IA - GM & Ex', 'a:0:{}', 1, 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM lead_lists WHERE alias = 'ia-gm-ex');

INSERT INTO lead_lists (is_published, date_added, created_by, created_by_user, name, description, alias, public_name, filters, is_global, is_preference_center)
SELECT 1, NOW(), 1, 'admin', 'IA - HOD Indonesia', 'Internal Announcement - HOD from folder ID', 'ia-hod', 'IA - HOD Indonesia', 'a:0:{}', 1, 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM lead_lists WHERE alias = 'ia-hod');

INSERT INTO lead_lists (is_published, date_added, created_by, created_by_user, name, description, alias, public_name, filters, is_global, is_preference_center)
SELECT 1, NOW(), 1, 'admin', 'IA - Hotel HOD', NULL, 'ia-hotel-hod', 'IA-Hotel-HOD', 'a:0:{}', 1, 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM lead_lists WHERE alias = 'ia-hotel-hod');

INSERT INTO lead_lists (is_published, date_added, created_by, created_by_user, name, description, alias, public_name, filters, is_global, is_preference_center)
SELECT 1, NOW(), 1, 'admin', 'IA - Indies', 'Bill and Harold from Indies', 'ia-indies', 'IA - Indies', 'a:0:{}', 1, 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM lead_lists WHERE alias = 'ia-indies');

INSERT INTO lead_lists (is_published, date_added, created_by, created_by_user, name, description, alias, public_name, filters, is_global, is_preference_center)
SELECT 1, NOW(), 1, 'admin', 'IA - List PR Global', NULL, 'ia-list-pr-global', 'IA - List PR Global', 'a:0:{}', 1, 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM lead_lists WHERE alias = 'ia-list-pr-global');

INSERT INTO lead_lists (is_published, date_added, created_by, created_by_user, name, description, alias, public_name, filters, is_global, is_preference_center)
SELECT 1, NOW(), 1, 'admin', 'IA - ME', NULL, 'ia-me', 'IA - ME', 'a:0:{}', 1, 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM lead_lists WHERE alias = 'ia-me');

INSERT INTO lead_lists (is_published, date_added, created_by, created_by_user, name, description, alias, public_name, filters, is_global, is_preference_center)
SELECT 1, NOW(), 1, 'admin', 'IA - PHP', 'IA - PHP VGSB SBBL', 'ia-php', 'IA - PHP', 'a:0:{}', 1, 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM lead_lists WHERE alias = 'ia-php');

INSERT INTO lead_lists (is_published, date_added, created_by, created_by_user, name, description, alias, public_name, filters, is_global, is_preference_center)
SELECT 1, NOW(), 1, 'admin', 'IA - Regional Executives', 'Internal Announcement - Regional Executives folder ID', 'ia-reg-ex', 'IA - Regional Executives', 'a:0:{}', 1, 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM lead_lists WHERE alias = 'ia-reg-ex');

INSERT INTO lead_lists (is_published, date_added, created_by, created_by_user, name, description, alias, public_name, filters, is_global, is_preference_center)
SELECT 1, NOW(), 1, 'admin', 'IA - TTL', NULL, 'ia-ttl', 'IA - TTL', 'a:0:{}', 1, 0
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM lead_lists WHERE alias = 'ia-ttl');

-- Build segment ID mapping (old v1 ID -> new v2 ID)
INSERT INTO tmp_segment_map (old_id, seg_name) VALUES
    (91, 'IA - ANZAC'),
    (92, 'IA - Central Asia'),
    (93, 'IA - General Managers'),
    (79, 'IA - GM & Ex'),
    (80, 'IA - HOD Indonesia'),
    (94, 'IA - Hotel HOD'),
    (321, 'IA - Indies'),
    (95, 'IA - List PR Global'),
    (97, 'IA - ME'),
    (98, 'IA - PHP'),
    (81, 'IA - Regional Executives'),
    (96, 'IA - TTL');

-- Map old_id -> new v2 ID using segment name
UPDATE tmp_segment_map sm
    INNER JOIN lead_lists ll ON ll.name = sm.seg_name
SET sm.new_id = ll.id;

-- Verify all 12 segments were mapped
SELECT 'STEP 1 - Segment mapping' AS step, COUNT(*) AS mapped, SUM(CASE WHEN new_id IS NULL THEN 1 ELSE 0 END) AS unmapped FROM tmp_segment_map;

-- ============================================================================
-- STEP 2: Migrate Contacts (dedup by email)
-- ============================================================================

-- Populate tmp_contact_map with all unique contacts from v1 IA segments
INSERT INTO tmp_contact_map (old_id, email)
SELECT DISTINCT l.id, l.email
FROM whizzmailer_swissbell2.leads l
INNER JOIN whizzmailer_swissbell2.lead_lists_leads lll ON l.id = lll.lead_id AND lll.manually_removed = 0
INNER JOIN whizzmailer_swissbell2.lead_lists ll ON lll.leadlist_id = ll.id
WHERE ll.name LIKE 'IA -%';

-- Map contacts that already exist in v2 by email
UPDATE tmp_contact_map cm
    INNER JOIN leads v2l ON v2l.email = cm.email AND cm.email IS NOT NULL AND cm.email != ''
SET cm.new_id = v2l.id, cm.is_new = 0;

-- Report how many need to be created vs already exist
SELECT 'STEP 2 - Contact mapping (before insert)' AS step,
    COUNT(*) AS total,
    SUM(CASE WHEN new_id IS NOT NULL THEN 1 ELSE 0 END) AS existing_in_v2,
    SUM(CASE WHEN new_id IS NULL THEN 1 ELSE 0 END) AS to_create
FROM tmp_contact_map;

-- Insert contacts that don't exist in v2
-- Use a cursor-free approach: insert one by one from the unmapped set
-- We insert them and then update the map with their new IDs

INSERT INTO leads (
    is_published, date_added, created_by, created_by_user, points,
    title, firstname, lastname, company, position,
    email, phone, mobile, address1, address2,
    city, state, zipcode, timezone, country,
    fax, preferred_locale, website,
    facebook, foursquare, instagram, linkedin, skype, twitter,
    reasonsfortravel, fullname, birthday, region, hotel, f_member, memberid,
    date_identified
)
SELECT
    v1l.is_published, v1l.date_added, 1, 'admin', v1l.points,
    v1l.title, v1l.firstname, v1l.lastname, v1l.company, v1l.position,
    v1l.email, v1l.phone, v1l.mobile, v1l.address1, v1l.address2,
    v1l.city, v1l.state, v1l.zipcode, v1l.timezone, v1l.country,
    v1l.fax, v1l.preferred_locale, v1l.website,
    v1l.facebook, v1l.foursquare, v1l.instagram, v1l.linkedin, v1l.skype, v1l.twitter,
    v1l.reasonsfortravel, v1l.fullname, v1l.birthday, v1l.region, v1l.hotel, v1l.f_member, v1l.memberid,
    v1l.date_identified
FROM tmp_contact_map cm
INNER JOIN whizzmailer_swissbell2.leads v1l ON v1l.id = cm.old_id
WHERE cm.new_id IS NULL
    AND cm.email IS NOT NULL
    AND cm.email != ''
    AND NOT EXISTS (
        SELECT 1 FROM leads v2l WHERE v2l.email = v1l.email
    );

-- Now map the newly inserted contacts by email
UPDATE tmp_contact_map cm
    INNER JOIN leads v2l ON v2l.email = cm.email AND cm.email IS NOT NULL AND cm.email != ''
SET cm.new_id = v2l.id, cm.is_new = 1
WHERE cm.new_id IS NULL;

-- Handle contacts with NULL or empty email (insert them always as they can't be deduped)
INSERT INTO leads (
    is_published, date_added, created_by, created_by_user, points,
    title, firstname, lastname, company, position,
    email, phone, mobile, address1, address2,
    city, state, zipcode, timezone, country,
    fax, preferred_locale, website,
    facebook, foursquare, instagram, linkedin, skype, twitter,
    reasonsfortravel, fullname, birthday, region, hotel, f_member, memberid,
    date_identified
)
SELECT
    v1l.is_published, v1l.date_added, 1, 'admin', v1l.points,
    v1l.title, v1l.firstname, v1l.lastname, v1l.company, v1l.position,
    v1l.email, v1l.phone, v1l.mobile, v1l.address1, v1l.address2,
    v1l.city, v1l.state, v1l.zipcode, v1l.timezone, v1l.country,
    v1l.fax, v1l.preferred_locale, v1l.website,
    v1l.facebook, v1l.foursquare, v1l.instagram, v1l.linkedin, v1l.skype, v1l.twitter,
    v1l.reasonsfortravel, v1l.fullname, v1l.birthday, v1l.region, v1l.hotel, v1l.f_member, v1l.memberid,
    v1l.date_identified
FROM tmp_contact_map cm
INNER JOIN whizzmailer_swissbell2.leads v1l ON v1l.id = cm.old_id
WHERE cm.new_id IS NULL
    AND (cm.email IS NULL OR cm.email = '');

-- For null-email contacts, we need to map by matching on other fields
-- Use LAST_INSERT_ID trick: since we inserted them sequentially, map by firstname+lastname+company
UPDATE tmp_contact_map cm
    INNER JOIN whizzmailer_swissbell2.leads v1l ON v1l.id = cm.old_id
    INNER JOIN leads v2l ON (
        v2l.firstname = v1l.firstname
        AND v2l.lastname = v1l.lastname
        AND IFNULL(v2l.company,'') = IFNULL(v1l.company,'')
        AND (v2l.email IS NULL OR v2l.email = '')
        AND v2l.date_added >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
    )
SET cm.new_id = v2l.id, cm.is_new = 1
WHERE cm.new_id IS NULL;

-- Report final mapping status
SELECT 'STEP 2 - Contact mapping (after insert)' AS step,
    COUNT(*) AS total,
    SUM(CASE WHEN new_id IS NOT NULL THEN 1 ELSE 0 END) AS mapped,
    SUM(CASE WHEN new_id IS NULL THEN 1 ELSE 0 END) AS unmapped
FROM tmp_contact_map;

-- ============================================================================
-- STEP 3: Create Segment-Contact Mappings
-- ============================================================================

INSERT IGNORE INTO lead_lists_leads (leadlist_id, lead_id, date_added, manually_removed, manually_added)
SELECT
    sm.new_id,
    cm.new_id,
    v1lll.date_added,
    0,
    1
FROM whizzmailer_swissbell2.lead_lists_leads v1lll
INNER JOIN tmp_segment_map sm ON sm.old_id = v1lll.leadlist_id
INNER JOIN tmp_contact_map cm ON cm.old_id = v1lll.lead_id
WHERE v1lll.manually_removed = 0
    AND sm.new_id IS NOT NULL
    AND cm.new_id IS NOT NULL;

-- Verify segment-contact counts
SELECT 'STEP 3 - Segment contact counts in v2' AS step;
SELECT ll.name, COUNT(lll.lead_id) AS contact_count
FROM lead_lists ll
INNER JOIN lead_lists_leads lll ON ll.id = lll.leadlist_id AND lll.manually_removed = 0
WHERE ll.name LIKE 'IA -%'
GROUP BY ll.id, ll.name
ORDER BY ll.name;

-- ============================================================================
-- STEP 4: Migrate DNC Records
-- ============================================================================

INSERT INTO lead_donotcontact (lead_id, date_added, reason, channel, channel_id, comments)
SELECT
    cm.new_id,
    v1dnc.date_added,
    v1dnc.reason,
    v1dnc.channel,
    v1dnc.channel_id,
    v1dnc.comments
FROM whizzmailer_swissbell2.lead_donotcontact v1dnc
INNER JOIN tmp_contact_map cm ON cm.old_id = v1dnc.lead_id
WHERE cm.new_id IS NOT NULL
    AND NOT EXISTS (
        SELECT 1 FROM lead_donotcontact v2dnc
        WHERE v2dnc.lead_id = cm.new_id
            AND v2dnc.channel = v1dnc.channel
    );

-- Verify DNC count
SELECT 'STEP 4 - DNC records migrated' AS step, COUNT(*) AS dnc_count
FROM lead_donotcontact dnc
INNER JOIN tmp_contact_map cm ON cm.new_id = dnc.lead_id;

-- ============================================================================
-- VERIFICATION
-- ============================================================================

SELECT 'VERIFICATION - Segment count' AS check_name, COUNT(*) AS result FROM lead_lists WHERE name LIKE 'IA -%';

SELECT 'VERIFICATION - Segment details' AS check_name;
SELECT ll.name, COUNT(lll.lead_id) AS v2_count
FROM lead_lists ll
LEFT JOIN lead_lists_leads lll ON ll.id = lll.leadlist_id AND lll.manually_removed = 0
WHERE ll.name LIKE 'IA -%'
GROUP BY ll.id, ll.name
ORDER BY ll.name;

-- Cleanup
DROP TEMPORARY TABLE IF EXISTS tmp_segment_map;
DROP TEMPORARY TABLE IF EXISTS tmp_contact_map;

SELECT 'MIGRATION COMPLETE' AS status;