Part 5: EAV Schema Migration — Magento's Biggest Trap

-- ❌ WRONG: Hardcoded attribute IDs break in production
SELECT
    e.entity_id,
    e.sku,
    v_name.value AS name,
    d_price.value AS price
FROM catalog_product_entity e
LEFT JOIN catalog_product_entity_varchar v_name
    ON v_name.entity_id = e.entity_id AND v_name.attribute_id = 75  -- "name" hardcoded!
LEFT JOIN catalog_product_entity_decimal d_price
    ON d_price.entity_id = e.entity_id AND d_price.attribute_id = 77  -- "price" hardcoded!
WHERE v_name.store_id = 0

-- ✅ CORRECT: Resolve attribute IDs dynamically from eav_attribute
SELECT attr.attribute_id
FROM eav_attribute attr
JOIN eav_entity_type et ON et.entity_type_id = attr.entity_type_id
WHERE et.entity_type_code = 'catalog_product'
  AND attr.attribute_code = 'name';      -- 'name' is stable, 'attribute_id' is not

-- Migration step 1: Create the identity map
CREATE TABLE magento_id_map (
    id              BIGSERIAL PRIMARY KEY,
    entity_type     VARCHAR(64) NOT NULL,    -- 'product', 'customer', 'order'
    magento_id      BIGINT NOT NULL,         -- Original Magento AUTO_INCREMENT ID
    platform_uuid   UUID NOT NULL DEFAULT gen_random_uuid(),
    migrated_at     TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(entity_type, magento_id)
);

-- Populate for all products
INSERT INTO magento_id_map (entity_type, magento_id)
SELECT 'product', entity_id
FROM catalog_product_entity
ON CONFLICT (entity_type, magento_id) DO NOTHING;

-- Verify: every product has a UUID
SELECT COUNT(*) AS total_products,
       COUNT(platform_uuid) AS mapped_products
FROM magento_id_map
WHERE entity_type = 'product';
-- Expected: total_products = mapped_products

-- EAV extraction with dynamic attribute ID resolution
WITH attr_ids AS (
    -- Resolve all attribute codes to IDs for this Magento instance
    SELECT
        attribute_id,
        attribute_code,
        backend_type                -- 'varchar', 'int', 'decimal', 'datetime', 'text'
    FROM eav_attribute
    WHERE entity_type_id = (
        SELECT entity_type_id FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'
    )
    AND attribute_code IN ('name', 'price', 'special_price', 'status',
                           'visibility', 'description', 'short_description',
                           'tax_class_id', 'weight', 'manufacturer')
)
SELECT
    e.entity_id                                             AS magento_id,
    idmap.platform_uuid                                     AS id,
    e.sku,
    e.attribute_set_id,
    MAX(CASE WHEN a.attribute_code = 'name'
        THEN v.value END)                                   AS name,
    MAX(CASE WHEN a.attribute_code = 'description'
        THEN t.value END)                                   AS description,
    MAX(CASE WHEN a.attribute_code = 'status'
        THEN i.value END)                                   AS status,
    MAX(CASE WHEN a.attribute_code = 'price'
        THEN d.value END)                                   AS price,
    MAX(CASE WHEN a.attribute_code = 'special_price'
        THEN d2.value END)                                  AS special_price,
    MAX(CASE WHEN a.attribute_code = 'weight'
        THEN d3.value END)                                  AS weight,
    e.created_at,
    e.updated_at

FROM catalog_product_entity e
JOIN magento_id_map idmap
    ON idmap.entity_type = 'product' AND idmap.magento_id = e.entity_id

-- Varchar attributes (name, manufacturer, etc.)
LEFT JOIN catalog_product_entity_varchar v
    ON v.entity_id = e.entity_id AND v.store_id = 0
    AND v.attribute_id IN (SELECT attribute_id FROM attr_ids WHERE backend_type = 'varchar')
LEFT JOIN attr_ids a_v ON a_v.attribute_id = v.attribute_id

-- Text attributes (description, short_description)
LEFT JOIN catalog_product_entity_text t
    ON t.entity_id = e.entity_id AND t.store_id = 0
    AND t.attribute_id IN (SELECT attribute_id FROM attr_ids WHERE backend_type = 'text')
LEFT JOIN attr_ids a_t ON a_t.attribute_id = t.attribute_id AND a_t = a_v

-- Integer attributes (status, visibility, tax_class_id)
LEFT JOIN catalog_product_entity_int i
    ON i.entity_id = e.entity_id AND i.store_id = 0
    AND i.attribute_id IN (SELECT attribute_id FROM attr_ids WHERE backend_type = 'int')

-- Decimal attributes (price, special_price, weight)
LEFT JOIN catalog_product_entity_decimal d
    ON d.entity_id = e.entity_id AND d.store_id = 0
    AND d.attribute_id = (SELECT attribute_id FROM attr_ids WHERE attribute_code = 'price')
LEFT JOIN catalog_product_entity_decimal d2
    ON d2.entity_id = e.entity_id AND d2.store_id = 0
    AND d2.attribute_id = (SELECT attribute_id FROM attr_ids WHERE attribute_code = 'special_price')
LEFT JOIN catalog_product_entity_decimal d3
    ON d3.entity_id = e.entity_id AND d3.store_id = 0
    AND d3.attribute_id = (SELECT attribute_id FROM attr_ids WHERE attribute_code = 'weight')

GROUP BY e.entity_id, e.sku, e.attribute_set_id, idmap.platform_uuid, e.created_at, e.updated_at
ORDER BY e.entity_id;

// migration/transformer/product_transformer.go

type MagentoProduct struct {
    MagentoID   int64
    UUID        string
    SKU         string
    Name        string
    Description string
    Status      int   // 1=Enabled, 2=Disabled
    Price       float64
    SpecialPrice *float64
    Weight      float64
    CreatedAt   time.Time
    UpdatedAt   time.Time
}

func TransformProduct(mp *MagentoProduct) *catalog.Product {
    p := &catalog.Product{
        ID:          mp.UUID,
        SKU:         mp.SKU,
        Name:        mp.Name,
        Description: mp.Description,
        Status:      transformStatus(mp.Status),
        // Convert float64 price to Money type (no floating point in API layer)
        Price: money.FromFloat("VND", mp.Price),
        Weight: mp.Weight,
        CreatedAt: mp.CreatedAt,
        UpdatedAt: mp.UpdatedAt,
    }

    if mp.SpecialPrice != nil {
        p.SpecialPrice = money.FromFloat("VND", *mp.SpecialPrice)
    }

    return p
}

func transformStatus(magentoStatus int) catalog.ProductStatus {
    switch magentoStatus {
    case 1: return catalog.ProductStatus_ENABLED
    case 2: return catalog.ProductStatus_DISABLED
    default: return catalog.ProductStatus_UNSPECIFIED
    }
}

-- Customer extraction with dynamic attribute resolution
WITH customer_attrs AS (
    SELECT attribute_id, attribute_code, backend_type
    FROM eav_attribute
    WHERE entity_type_id = (
        SELECT entity_type_id FROM eav_entity_type
        WHERE entity_type_code = 'customer'
    )
    AND attribute_code IN ('firstname', 'lastname', 'dob', 'gender', 'taxvat')
)
SELECT
    ce.entity_id                    AS magento_id,
    idmap.platform_uuid             AS id,
    ce.email,
    ce.group_id                     AS customer_group,
    ce.is_active,
    ce.created_at,
    MAX(CASE WHEN ca.attribute_code = 'firstname'
        THEN v.value END)           AS first_name,
    MAX(CASE WHEN ca.attribute_code = 'lastname'
        THEN v.value END)           AS last_name
FROM customer_entity ce
JOIN magento_id_map idmap
    ON idmap.entity_type = 'customer' AND idmap.magento_id = ce.entity_id
LEFT JOIN customer_entity_varchar v
    ON v.entity_id = ce.entity_id
    AND v.attribute_id IN (SELECT attribute_id FROM customer_attrs WHERE backend_type = 'varchar')
JOIN customer_attrs ca ON ca.attribute_id = v.attribute_id
GROUP BY ce.entity_id, ce.email, ce.group_id, ce.is_active, ce.created_at, idmap.platform_uuid;

-- Delta sync: products changed since last extraction
SELECT e.entity_id
FROM catalog_product_entity e
WHERE e.updated_at > :last_sync_timestamp
ORDER BY e.updated_at ASC
LIMIT 1000;

#!/bin/bash
# validate-product-extraction.sh

MAGENTO_COUNT=$(mysql -h $MAGENTO_DB -e "
    SELECT COUNT(*) FROM catalog_product_entity
    WHERE status = 1  -- Enabled products only
" | tail -1)

PLATFORM_COUNT=$(psql $PLATFORM_DB -t -c "
    SELECT COUNT(*) FROM products WHERE status = 'ENABLED'
")

echo "Magento enabled products: $MAGENTO_COUNT"
echo "Platform products: $PLATFORM_COUNT"

# Count must match before proceeding to Phase 1
if [ "$MAGENTO_COUNT" -ne "$PLATFORM_COUNT" ]; then
    echo "❌ Count mismatch — extraction incomplete"
    exit 1
fi

# Sample validation: spot-check 100 products
psql $PLATFORM_DB -c "
    SELECT p.sku, p.name, p.price
    FROM products p
    ORDER BY RANDOM()
    LIMIT 100
" > /tmp/platform_sample.csv

echo "✅ Count matches. Review sample in /tmp/platform_sample.csv"