-- ============================================
-- Import Script: Old Agents to TrueID V2
-- ============================================
-- This script imports agents (type=2) from the old check_nurrifa database
-- and maps their QR code assignments to the new schema.
--
-- Run this in your new trueidv2 database after importing the raw data
-- into temporary tables or run against a combined database.
-- ============================================

-- ============================================
-- STEP 1: Create temporary mapping table
-- ============================================
-- This helps us track old userId -> new sellers.id mapping

CREATE TEMPORARY TABLE IF NOT EXISTS seller_id_mapping (
    old_user_id INT NOT NULL,
    new_seller_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (old_user_id)
);

-- ============================================
-- STEP 2: Insert agents into sellers table
-- ============================================
-- Only importing type=2 (agents) from old seller table
-- Mapping:
--   old.id (IC number) -> sellers.seller_id
--   old.fname + lname  -> sellers.name
--   old.status 1/0     -> sellers.status active/inactive

INSERT INTO sellers (
    seller_id,
    name,
    email,
    phone,
    extra_data,
    status,
    created_at,
    updated_at
)
SELECT
    TRIM(s.id) as seller_id,
    CONCAT(TRIM(COALESCE(s.fname, '')), ' ', TRIM(COALESCE(s.lname, ''))) as name,
    CASE WHEN s.email != '' THEN s.email ELSE NULL END as email,
    CASE WHEN s.phone != 'NON' AND s.phone != '' THEN s.phone ELSE NULL END as phone,
    JSON_OBJECT(
        'address', CASE WHEN s.address != 'NON' THEN s.address ELSE NULL END,
        'region', CASE WHEN s.region != 'NON' THEN s.region ELSE NULL END,
        'old_user_id', s.userId,
        'agent_type', 'imported_from_old_system'
    ) as extra_data,
    CASE WHEN s.status = 1 THEN 'active' ELSE 'inactive' END as status,
    s.createdOn as created_at,
    s.updatedOn as updated_at
FROM seller s
WHERE s.type = 2  -- Only agents
AND NOT EXISTS (
    -- Skip if seller_id already exists to prevent duplicates
    SELECT 1 FROM sellers WHERE sellers.seller_id = TRIM(s.id)
);

-- ============================================
-- STEP 3: Build the ID mapping table
-- ============================================
-- Maps old userId to new sellers.id for QR assignment

INSERT INTO seller_id_mapping (old_user_id, new_seller_id)
SELECT
    JSON_UNQUOTE(JSON_EXTRACT(extra_data, '$.old_user_id')) as old_user_id,
    id as new_seller_id
FROM sellers
WHERE JSON_EXTRACT(extra_data, '$.old_user_id') IS NOT NULL;

-- ============================================
-- STEP 4: Update QR codes with seller assignment
-- ============================================
-- Match old qr_id to qr_codes.serial and assign to the new seller

UPDATE qr_codes qc
INNER JOIN seller_qr sq ON CAST(sq.qr_id AS CHAR) = qc.serial
INNER JOIN seller_id_mapping m ON sq.userId = m.old_user_id
SET
    qc.assigned_to_id = m.new_seller_id,
    qc.assigned_to_type = 'App\\Models\\Seller'
WHERE qc.assigned_to_id IS NULL;  -- Only update unassigned QR codes

-- ============================================
-- STEP 5: Cleanup
-- ============================================
DROP TEMPORARY TABLE IF EXISTS seller_id_mapping;

-- ============================================
-- VERIFICATION QUERIES (run after import)
-- ============================================
-- Check imported sellers count:
-- SELECT COUNT(*) as imported_agents FROM sellers WHERE JSON_EXTRACT(extra_data, '$.agent_type') = 'imported_from_old_system';

-- Check QR assignments count:
-- SELECT COUNT(*) as assigned_qrs FROM qr_codes WHERE assigned_to_type = 'App\\Models\\Seller';

-- View sellers with their QR counts:
-- SELECT s.id, s.seller_id, s.name, COUNT(qc.id) as qr_count
-- FROM sellers s
-- LEFT JOIN qr_codes qc ON qc.assigned_to_id = s.id AND qc.assigned_to_type = 'App\\Models\\Seller'
-- WHERE JSON_EXTRACT(s.extra_data, '$.agent_type') = 'imported_from_old_system'
-- GROUP BY s.id, s.seller_id, s.name;
