setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Database Connected Successfully!
";
} catch (PDOException $e) {
die("Database Connection Failed: " . $e->getMessage());
}
// Ensure old tables exist before proceeding
$tables = $pdo->query("SHOW TABLES LIKE 'old_items'")->fetchAll();
if(empty($tables)) {
die("Error: Please import the old tables (renamed to old_items, old_categories, old_chield_categories) first.");
}
$categoryMap = []; // Will store: ['type_oldId' => new_id] to link products correctly
try {
$pdo->beginTransaction();
// --- 2. MIGRATE TOP-LEVEL CATEGORIES ---
echo "Migrating Top-Level Categories...
";
$stmt = $pdo->query("SELECT * FROM old_categories");
$oldCats = $stmt->fetchAll(PDO::FETCH_ASSOC);
$catInsert = $pdo->prepare("INSERT INTO categories (parent_id, name, slug, image) VALUES (NULL, ?, ?, ?)");
$catCount = 0;
foreach($oldCats as $cat) {
// Skip if already exists by slug (to prevent duplicates if run twice)
$check = $pdo->prepare("SELECT id FROM categories WHERE slug = ?");
$check->execute([$cat['slug']]);
if($existing = $check->fetchColumn()) {
$categoryMap['cat_'.$cat['id']] = $existing;
continue;
}
$catInsert->execute([$cat['name'], $cat['slug'], $cat['photo']]);
$newCatId = $pdo->lastInsertId();
$categoryMap['cat_'.$cat['id']] = $newCatId;
$catCount++;
}
echo "Migrated $catCount top-level categories.
";
// --- 3. MIGRATE CHILD CATEGORIES ---
echo "Migrating Child Categories...
";
$stmt = $pdo->query("SELECT * FROM old_chield_categories");
$oldChildren = $stmt->fetchAll(PDO::FETCH_ASSOC);
$childInsert = $pdo->prepare("INSERT INTO categories (parent_id, name, slug) VALUES (?, ?, ?)");
$childCount = 0;
foreach($oldChildren as $child) {
$check = $pdo->prepare("SELECT id FROM categories WHERE slug = ?");
$check->execute([$child['slug']]);
if($existing = $check->fetchColumn()) {
$categoryMap['child_'.$child['id']] = $existing;
continue;
}
// Link to the newly created parent category ID
$newParentId = isset($categoryMap['cat_'.$child['category_id']]) ? $categoryMap['cat_'.$child['category_id']] : NULL;
$childInsert->execute([$newParentId, $child['name'], $child['slug']]);
$newChildId = $pdo->lastInsertId();
$categoryMap['child_'.$child['id']] = $newChildId;
$childCount++;
}
echo "Migrated $childCount child categories.
";
// --- 4. MIGRATE PRODUCTS ---
echo "Migrating Products...
";
$stmt = $pdo->query("SELECT * FROM old_items");
$oldItems = $stmt->fetchAll(PDO::FETCH_ASSOC);
$prodInsert = $pdo->prepare("
INSERT INTO products
(category_id, name, short_description, brand, slug, sku, price, sale_price, stock_qty, unit, description, meta_description, is_featured, created_at, image)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'pc', ?, ?, ?, ?, ?)
");
$imgInsert = $pdo->prepare("INSERT INTO product_images (product_id, image_path, sort_order) VALUES (?, ?, 0)");
$prodCount = 0;
foreach($oldItems as $item) {
// Find best Category ID (Prefer deepest level child, fallback to parent)
$newCatId = NULL;
if(!empty($item['childcategory_id']) && isset($categoryMap['child_'.$item['childcategory_id']])) {
$newCatId = $categoryMap['child_'.$item['childcategory_id']];
} elseif(!empty($item['category_id']) && isset($categoryMap['cat_'.$item['category_id']])) {
$newCatId = $categoryMap['cat_'.$item['category_id']];
}
// Calculate Pricing
$price = $item['previous_price'] > 0 ? $item['previous_price'] : $item['discount_price'];
$salePrice = ($item['discount_price'] < $item['previous_price']) ? $item['discount_price'] : NULL;
// Auto-extract Brand from Name or Tags (Fallback to 'Generic')
$brand = 'Generic';
$brandList = ['HP', 'Dell', 'Lenovo', 'Apple', 'Samsung', 'Asus', 'Hikvision', 'Paxton'];
foreach($brandList as $b) {
if(stripos($item['name'], $b) !== false || stripos($item['tags'] ?? '', $b) !== false) {
$brand = $b; break;
}
}
// Determine Featured Status
$isFeatured = ($item['is_type'] == 'feature') ? 1 : 0;
// Strip HTML tags for short description if old sort_details is empty
$shortDesc = $item['sort_details'];
if(empty($shortDesc)) {
$shortDesc = substr(strip_tags($item['details']), 0, 140) . '...';
}
// --- FIX: Prevent duplicate SKUs to allow safe resuming ---
$sku = !empty($item['sku']) ? trim($item['sku']) : 'SKU-OLD-' . $item['id'];
$checkSku = $pdo->prepare("SELECT id FROM products WHERE sku = ?");
$checkSku->execute([$sku]);
if ($checkSku->fetchColumn()) {
continue; // Product already migrated, skip to next
}
// --- FIX: Robust While-Loop to guarantee absolutely unique slugs ---
$baseSlug = !empty($item['slug']) ? trim($item['slug']) : 'product-' . $item['id'];
// Truncate to ensure appended numbers don't exceed varchar limits
$baseSlug = substr($baseSlug, 0, 200);
$slug = $baseSlug;
$counter = 1;
$checkSlug = $pdo->prepare("SELECT id FROM products WHERE slug = ?");
while (true) {
$checkSlug->execute([$slug]);
if (!$checkSlug->fetchColumn()) {
break; // We found a truly unique slug! Break the loop.
}
// Slug exists, append a counter and try again
$slug = $baseSlug . '-' . $counter;
$counter++;
}
// Execute Product Insert
$prodInsert->execute([
$newCatId,
$item['name'],
$shortDesc,
$brand,
$slug,
$sku,
$price,
$salePrice,
$item['stock'],
$item['details'],
$item['meta_description'],
$isFeatured,
$item['created_at'] ?: date('Y-m-d H:i:s'),
$item['photo']
]);
$newProductId = $pdo->lastInsertId();
// Also add the image to the 'product_images' table if required by your theme
if(!empty($item['photo'])) {
$imgInsert->execute([$newProductId, $item['photo']]);
}
$prodCount++;
}
echo "Migrated $prodCount products successfully!
";
$pdo->commit();
echo "Migration Complete! 🎉
";
echo "Please delete this file (migrate.php) and drop the old_items, old_categories, and old_chield_categories tables from your database.
";
} catch (Exception $e) {
$pdo->rollBack();
die("Migration Failed:
" . $e->getMessage());
}
?>