-- ============================================
-- Import Script for Live Site
-- Run this AFTER importing seller and seller_qr tables from old database
-- ============================================

-- Step 1: Insert agents (type=2) into sellers table
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
AND NOT EXISTS (
    SELECT 1 FROM sellers WHERE sellers.seller_id = TRIM(s.id)
);

-- Step 2: Update QR codes with seller assignment
-- This maps old qr_id to qr_codes.serial
UPDATE qr_codes qc
INNER JOIN seller_qr sq ON CAST(sq.qr_id AS CHAR) = qc.serial
INNER JOIN seller s ON s.userId = sq.userId AND s.type = 2
INNER JOIN sellers sel ON sel.seller_id = TRIM(s.id)
SET
    qc.assigned_to_id = sel.id,
    qc.assigned_to_type = 'App\\Models\\Seller'
WHERE qc.assigned_to_id IS NULL;

-- Step 3: Verify import
SELECT 'Imported Sellers' as metric, COUNT(*) as count FROM sellers WHERE JSON_EXTRACT(extra_data, '$.agent_type') = 'imported_from_old_system'
UNION ALL
SELECT 'QR Codes Assigned', COUNT(*) FROM qr_codes WHERE assigned_to_type = 'App\\Models\\Seller';
