mirror of
https://github.com/myronblair/tomsjavajive-app
synced 2026-06-30 17:50:56 -05:00
436 lines
16 KiB
PHP
436 lines
16 KiB
PHP
<?php
|
|
/**
|
|
* Tom's Java Jive - MongoDB to MySQL Migration Script
|
|
*
|
|
* This script migrates data from the existing MongoDB database to MySQL.
|
|
* Run this script once after setting up the MySQL database with schema.sql
|
|
*
|
|
* Usage: php migrate_from_mongodb.php [mongodb_url] [mongodb_dbname]
|
|
*
|
|
* Prerequisites:
|
|
* - PHP MongoDB extension installed
|
|
* - MySQL database created and schema.sql executed
|
|
* - database.php configured with MySQL credentials
|
|
*/
|
|
|
|
require_once __DIR__ . '/../config/database.php';
|
|
require_once __DIR__ . '/../includes/db.php';
|
|
|
|
// Configuration
|
|
$mongoUrl = $argv[1] ?? getenv('MONGO_URL') ?: 'mongodb://localhost:27017';
|
|
$mongoDbName = $argv[2] ?? getenv('MONGO_DB_NAME') ?: 'tomsjavajive';
|
|
|
|
echo "==============================================\n";
|
|
echo "Tom's Java Jive - MongoDB to MySQL Migration\n";
|
|
echo "==============================================\n\n";
|
|
|
|
// Check for MongoDB extension
|
|
if (!extension_loaded('mongodb')) {
|
|
echo "ERROR: MongoDB PHP extension is not installed.\n";
|
|
echo "Install it with: pecl install mongodb\n";
|
|
echo "Or: apt-get install php-mongodb\n\n";
|
|
echo "Alternatively, export data from MongoDB using mongodump/mongoexport\n";
|
|
echo "and import it manually.\n";
|
|
exit(1);
|
|
}
|
|
|
|
try {
|
|
// Connect to MongoDB
|
|
echo "Connecting to MongoDB at: $mongoUrl\n";
|
|
$mongoClient = new MongoDB\Client($mongoUrl);
|
|
$mongodb = $mongoClient->selectDatabase($mongoDbName);
|
|
echo "Connected to MongoDB database: $mongoDbName\n\n";
|
|
|
|
// Test MySQL connection
|
|
$mysql = db();
|
|
echo "Connected to MySQL database: " . DB_NAME . "\n\n";
|
|
|
|
} catch (Exception $e) {
|
|
echo "ERROR: " . $e->getMessage() . "\n";
|
|
exit(1);
|
|
}
|
|
|
|
/**
|
|
* Helper function to convert MongoDB document to array
|
|
*/
|
|
function docToArray($doc) {
|
|
if ($doc instanceof MongoDB\Model\BSONDocument || $doc instanceof MongoDB\Model\BSONArray) {
|
|
return json_decode(json_encode($doc), true);
|
|
}
|
|
return $doc;
|
|
}
|
|
|
|
/**
|
|
* Helper function to safely get nested value
|
|
*/
|
|
function safeGet($array, $key, $default = null) {
|
|
return isset($array[$key]) ? $array[$key] : $default;
|
|
}
|
|
|
|
/**
|
|
* Migrate Admin Users
|
|
*/
|
|
function migrateAdminUsers($mongodb, $mysql) {
|
|
echo "Migrating admin users...\n";
|
|
|
|
$cursor = $mongodb->admin_users->find();
|
|
$count = 0;
|
|
|
|
foreach ($cursor as $doc) {
|
|
$doc = docToArray($doc);
|
|
|
|
try {
|
|
$mysql->insert('admin_users', [
|
|
'user_id' => safeGet($doc, 'user_id') ?: safeGet($doc, 'admin_id'),
|
|
'email' => safeGet($doc, 'email'),
|
|
'password_hash' => safeGet($doc, 'password_hash'),
|
|
'name' => safeGet($doc, 'name'),
|
|
'picture' => safeGet($doc, 'picture'),
|
|
'is_admin' => 1,
|
|
'is_master' => safeGet($doc, 'is_master') ? 1 : 0,
|
|
'permissions' => json_encode(safeGet($doc, 'permissions', [])),
|
|
'created_at' => safeGet($doc, 'created_at') ?: date('Y-m-d H:i:s')
|
|
]);
|
|
$count++;
|
|
} catch (Exception $e) {
|
|
echo " Warning: Could not migrate admin {$doc['email']}: " . $e->getMessage() . "\n";
|
|
}
|
|
}
|
|
|
|
echo " Migrated $count admin users\n\n";
|
|
}
|
|
|
|
/**
|
|
* Migrate Customers
|
|
*/
|
|
function migrateCustomers($mongodb, $mysql) {
|
|
echo "Migrating customers...\n";
|
|
|
|
$cursor = $mongodb->customers->find();
|
|
$count = 0;
|
|
|
|
foreach ($cursor as $doc) {
|
|
$doc = docToArray($doc);
|
|
|
|
try {
|
|
$mysql->insert('customers', [
|
|
'customer_id' => safeGet($doc, 'customer_id'),
|
|
'email' => safeGet($doc, 'email'),
|
|
'password_hash' => safeGet($doc, 'password_hash'),
|
|
'name' => safeGet($doc, 'name'),
|
|
'phone' => safeGet($doc, 'phone'),
|
|
'shipping_address' => json_encode(safeGet($doc, 'shipping_address')),
|
|
'billing_address' => json_encode(safeGet($doc, 'billing_address')),
|
|
'wallet_balance' => safeGet($doc, 'account_balance', 0),
|
|
'reward_points' => safeGet($doc, 'loyalty_points', 0),
|
|
'is_guest' => safeGet($doc, 'is_guest') ? 1 : 0,
|
|
'created_via' => safeGet($doc, 'created_via', 'web'),
|
|
'created_at' => safeGet($doc, 'created_at') ?: date('Y-m-d H:i:s')
|
|
]);
|
|
|
|
// Migrate wallet transactions
|
|
$transactions = safeGet($doc, 'account_transactions', []);
|
|
foreach ($transactions as $txn) {
|
|
try {
|
|
$mysql->insert('wallet_transactions', [
|
|
'transaction_id' => safeGet($txn, 'transaction_id') ?: 'txn_' . bin2hex(random_bytes(6)),
|
|
'customer_id' => $doc['customer_id'],
|
|
'amount' => safeGet($txn, 'amount', 0),
|
|
'balance_after' => safeGet($txn, 'balance_after', 0),
|
|
'type' => mapTransactionType(safeGet($txn, 'type')),
|
|
'description' => safeGet($txn, 'description'),
|
|
'order_id' => safeGet($txn, 'order_id'),
|
|
'created_at' => safeGet($txn, 'date') ?: date('Y-m-d H:i:s')
|
|
]);
|
|
} catch (Exception $e) {
|
|
// Skip transaction errors
|
|
}
|
|
}
|
|
|
|
$count++;
|
|
} catch (Exception $e) {
|
|
echo " Warning: Could not migrate customer {$doc['email']}: " . $e->getMessage() . "\n";
|
|
}
|
|
}
|
|
|
|
echo " Migrated $count customers\n\n";
|
|
}
|
|
|
|
function mapTransactionType($type) {
|
|
$map = [
|
|
'deposit' => 'deposit',
|
|
'payment' => 'withdrawal',
|
|
'gift_card_purchase' => 'purchase',
|
|
'admin_deposit' => 'deposit',
|
|
'pos_payment' => 'withdrawal',
|
|
'refund' => 'refund'
|
|
];
|
|
return isset($map[$type]) ? $map[$type] : 'deposit';
|
|
}
|
|
|
|
/**
|
|
* Migrate Products
|
|
*/
|
|
function migrateProducts($mongodb, $mysql) {
|
|
echo "Migrating products...\n";
|
|
|
|
$cursor = $mongodb->products->find();
|
|
$count = 0;
|
|
|
|
foreach ($cursor as $doc) {
|
|
$doc = docToArray($doc);
|
|
|
|
// Map blend_type to category
|
|
$category = safeGet($doc, 'blend_type') ?: safeGet($doc, 'category') ?: 'coffee';
|
|
|
|
try {
|
|
$mysql->insert('products', [
|
|
'product_id' => safeGet($doc, 'product_id'),
|
|
'name' => safeGet($doc, 'name'),
|
|
'description' => safeGet($doc, 'description'),
|
|
'price' => safeGet($doc, 'price', 0),
|
|
'sale_price' => safeGet($doc, 'sale_price'),
|
|
'sku' => safeGet($doc, 'sku'),
|
|
'barcode' => safeGet($doc, 'barcode'),
|
|
'category' => $category,
|
|
'tags' => json_encode([safeGet($doc, 'roast_level'), safeGet($doc, 'product_type')]),
|
|
'images' => json_encode([safeGet($doc, 'image_url')]),
|
|
'stock' => safeGet($doc, 'stock_quantity', 100),
|
|
'low_stock_threshold' => safeGet($doc, 'low_stock_threshold', 10),
|
|
'weight' => safeGet($doc, 'weight'),
|
|
'is_active' => safeGet($doc, 'in_stock', true) ? 1 : 0,
|
|
'is_featured' => safeGet($doc, 'on_sale', false) ? 1 : 0,
|
|
'created_at' => safeGet($doc, 'created_at') ?: date('Y-m-d H:i:s')
|
|
]);
|
|
$count++;
|
|
} catch (Exception $e) {
|
|
echo " Warning: Could not migrate product {$doc['name']}: " . $e->getMessage() . "\n";
|
|
}
|
|
}
|
|
|
|
echo " Migrated $count products\n\n";
|
|
}
|
|
|
|
/**
|
|
* Migrate Orders
|
|
*/
|
|
function migrateOrders($mongodb, $mysql) {
|
|
echo "Migrating orders...\n";
|
|
|
|
$cursor = $mongodb->orders->find();
|
|
$count = 0;
|
|
|
|
foreach ($cursor as $doc) {
|
|
$doc = docToArray($doc);
|
|
|
|
try {
|
|
$mysql->insert('orders', [
|
|
'order_id' => safeGet($doc, 'order_id'),
|
|
'order_number' => safeGet($doc, 'order_number'),
|
|
'customer_id' => safeGet($doc, 'customer_id'),
|
|
'customer_email' => safeGet($doc, 'customer_email'),
|
|
'customer_name' => safeGet($doc, 'customer_name'),
|
|
'items' => json_encode(safeGet($doc, 'items', [])),
|
|
'subtotal' => safeGet($doc, 'subtotal', 0),
|
|
'shipping_cost' => safeGet($doc, 'shipping_cost', 0),
|
|
'tax' => safeGet($doc, 'tax', 0),
|
|
'discount' => safeGet($doc, 'discount_amount', 0),
|
|
'total' => safeGet($doc, 'total', 0),
|
|
'shipping_address' => json_encode(safeGet($doc, 'shipping_address')),
|
|
'shipping_method' => safeGet($doc, 'shipping_method'),
|
|
'payment_method' => safeGet($doc, 'payment_method'),
|
|
'payment_status' => safeGet($doc, 'payment_status', 'pending'),
|
|
'order_status' => safeGet($doc, 'order_status', 'pending'),
|
|
'stripe_payment_intent' => safeGet($doc, 'checkout_session_id'),
|
|
'tracking_number' => safeGet($doc, 'tracking_number'),
|
|
'notes' => safeGet($doc, 'pos_notes'),
|
|
'is_pos_order' => safeGet($doc, 'pos_order') ? 1 : 0,
|
|
'created_at' => safeGet($doc, 'created_at') ?: date('Y-m-d H:i:s')
|
|
]);
|
|
|
|
// Also insert into order_items for reporting
|
|
$items = safeGet($doc, 'items', []);
|
|
foreach ($items as $item) {
|
|
try {
|
|
$mysql->insert('order_items', [
|
|
'order_id' => $doc['order_id'],
|
|
'product_id' => safeGet($item, 'product_id'),
|
|
'name' => safeGet($item, 'name'),
|
|
'price' => safeGet($item, 'price', 0),
|
|
'quantity' => safeGet($item, 'quantity', 1),
|
|
'total' => safeGet($item, 'total', 0)
|
|
]);
|
|
} catch (Exception $e) {
|
|
// Skip item errors
|
|
}
|
|
}
|
|
|
|
$count++;
|
|
} catch (Exception $e) {
|
|
echo " Warning: Could not migrate order {$doc['order_number']}: " . $e->getMessage() . "\n";
|
|
}
|
|
}
|
|
|
|
echo " Migrated $count orders\n\n";
|
|
}
|
|
|
|
/**
|
|
* Migrate Gift Cards
|
|
*/
|
|
function migrateGiftCards($mongodb, $mysql) {
|
|
echo "Migrating gift cards...\n";
|
|
|
|
$cursor = $mongodb->gift_cards->find();
|
|
$count = 0;
|
|
|
|
foreach ($cursor as $doc) {
|
|
$doc = docToArray($doc);
|
|
|
|
try {
|
|
$mysql->insert('gift_cards', [
|
|
'gift_card_id' => safeGet($doc, 'gift_card_id'),
|
|
'code' => safeGet($doc, 'code'),
|
|
'initial_balance' => safeGet($doc, 'initial_balance', 0),
|
|
'current_balance' => safeGet($doc, 'current_balance', 0),
|
|
'purchaser_email' => safeGet($doc, 'purchased_by_email'),
|
|
'recipient_email' => safeGet($doc, 'recipient_email'),
|
|
'recipient_name' => safeGet($doc, 'recipient_name'),
|
|
'message' => safeGet($doc, 'message'),
|
|
'is_active' => safeGet($doc, 'status') !== 'disabled' ? 1 : 0,
|
|
'created_at' => safeGet($doc, 'created_at') ?: date('Y-m-d H:i:s')
|
|
]);
|
|
$count++;
|
|
} catch (Exception $e) {
|
|
echo " Warning: Could not migrate gift card {$doc['code']}: " . $e->getMessage() . "\n";
|
|
}
|
|
}
|
|
|
|
echo " Migrated $count gift cards\n\n";
|
|
}
|
|
|
|
/**
|
|
* Migrate Reviews
|
|
*/
|
|
function migrateReviews($mongodb, $mysql) {
|
|
echo "Migrating reviews...\n";
|
|
|
|
$cursor = $mongodb->product_reviews->find();
|
|
$count = 0;
|
|
|
|
foreach ($cursor as $doc) {
|
|
$doc = docToArray($doc);
|
|
|
|
try {
|
|
$mysql->insert('reviews', [
|
|
'review_id' => safeGet($doc, 'review_id'),
|
|
'product_id' => safeGet($doc, 'product_id'),
|
|
'customer_id' => safeGet($doc, 'customer_id'),
|
|
'customer_name' => safeGet($doc, 'customer_name'),
|
|
'customer_email' => safeGet($doc, 'customer_email'),
|
|
'rating' => safeGet($doc, 'rating', 5),
|
|
'title' => safeGet($doc, 'title'),
|
|
'comment' => safeGet($doc, 'comment'),
|
|
'is_verified_purchase' => safeGet($doc, 'verified_purchase') ? 1 : 0,
|
|
'is_approved' => safeGet($doc, 'status') === 'approved' ? 1 : 0,
|
|
'created_at' => safeGet($doc, 'created_at') ?: date('Y-m-d H:i:s')
|
|
]);
|
|
$count++;
|
|
} catch (Exception $e) {
|
|
echo " Warning: Could not migrate review: " . $e->getMessage() . "\n";
|
|
}
|
|
}
|
|
|
|
echo " Migrated $count reviews\n\n";
|
|
}
|
|
|
|
/**
|
|
* Migrate Email Subscribers
|
|
*/
|
|
function migrateSubscribers($mongodb, $mysql) {
|
|
echo "Migrating email subscribers...\n";
|
|
|
|
$cursor = $mongodb->email_subscribers->find();
|
|
$count = 0;
|
|
|
|
foreach ($cursor as $doc) {
|
|
$doc = docToArray($doc);
|
|
|
|
try {
|
|
$mysql->insert('email_subscribers', [
|
|
'email' => safeGet($doc, 'email'),
|
|
'name' => safeGet($doc, 'name'),
|
|
'is_active' => safeGet($doc, 'status') === 'active' ? 1 : 0,
|
|
'source' => safeGet($doc, 'source', 'website'),
|
|
'created_at' => safeGet($doc, 'subscribed_at') ?: date('Y-m-d H:i:s')
|
|
]);
|
|
$count++;
|
|
} catch (Exception $e) {
|
|
// Skip duplicate emails
|
|
}
|
|
}
|
|
|
|
echo " Migrated $count subscribers\n\n";
|
|
}
|
|
|
|
/**
|
|
* Migrate Settings
|
|
*/
|
|
function migrateSettings($mongodb, $mysql) {
|
|
echo "Migrating settings...\n";
|
|
|
|
// Shipping settings
|
|
$shipping = $mongodb->shipping_settings->findOne(['settings_id' => 'shipping_settings']);
|
|
if ($shipping) {
|
|
$shipping = docToArray($shipping);
|
|
$mysql->query(
|
|
"UPDATE settings SET setting_value = :val WHERE setting_key = 'shipping'",
|
|
['val' => json_encode([
|
|
'flat_rate_enabled' => safeGet($shipping, 'flat_rate_enabled', true),
|
|
'flat_rate_amount' => safeGet($shipping, 'flat_rate_amount', 5.99),
|
|
'free_shipping_threshold' => safeGet($shipping, 'free_shipping_threshold', 50),
|
|
'weight_based_enabled' => safeGet($shipping, 'weight_based_enabled', false)
|
|
])]
|
|
);
|
|
echo " Migrated shipping settings\n";
|
|
}
|
|
|
|
// Payment settings
|
|
$payment = $mongodb->payment_settings->findOne(['settings_id' => 'payment_settings']);
|
|
if ($payment) {
|
|
$payment = docToArray($payment);
|
|
$mysql->query(
|
|
"UPDATE settings SET setting_value = :val WHERE setting_key = 'payment'",
|
|
['val' => json_encode([
|
|
'stripe_enabled' => safeGet($payment, 'stripe_enabled', true),
|
|
'paypal_enabled' => safeGet($payment, 'paypal_enabled', false),
|
|
'cod_enabled' => false
|
|
])]
|
|
);
|
|
echo " Migrated payment settings\n";
|
|
}
|
|
|
|
echo "\n";
|
|
}
|
|
|
|
// Run migrations
|
|
echo "Starting migration...\n\n";
|
|
|
|
try {
|
|
migrateAdminUsers($mongodb, $mysql);
|
|
migrateCustomers($mongodb, $mysql);
|
|
migrateProducts($mongodb, $mysql);
|
|
migrateOrders($mongodb, $mysql);
|
|
migrateGiftCards($mongodb, $mysql);
|
|
migrateReviews($mongodb, $mysql);
|
|
migrateSubscribers($mongodb, $mysql);
|
|
migrateSettings($mongodb, $mysql);
|
|
|
|
echo "==============================================\n";
|
|
echo "Migration completed successfully!\n";
|
|
echo "==============================================\n";
|
|
|
|
} catch (Exception $e) {
|
|
echo "\nMIGRATION ERROR: " . $e->getMessage() . "\n";
|
|
exit(1);
|
|
}
|