migrate_existing_data.py

"""
Data Migration Script: Convert existing products to product-variant model
Run this after applying migrations 003-006
"""

import sys
import os
sys.path.append(os.path.dirname(os.path.abspath(__file__)))

from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
from app.core.database import get_db_session
from app.models.tenant_database_config import TenantDatabaseConfig
from app.core.central_database import get_central_db
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)


def generate_category_paths():
    """Generate path field for existing categories"""
    logger.info("Starting category path generation...")

    # Get all tenant databases
    central_db = next(get_central_db())
    tenant_configs = central_db.query(TenantDatabaseConfig).all()

    for config in tenant_configs:
        try:
            logger.info(f"Processing tenant: {config.tenant_id}")
            tenant_db = next(get_db_session(config.tenant_id))

            # Get all categories
            categories = tenant_db.execute(text("SELECT id, name, parent_id FROM categories ORDER BY id")).fetchall()

            # Build category hierarchy
            category_map = {cat.id: cat for cat in categories}

            def build_path(category_id, visited=None):
                if visited is None:
                    visited = set()

                if category_id in visited:
                    logger.warning(f"Circular reference detected for category {category_id}")
                    return None

                visited.add(category_id)
                cat = category_map.get(category_id)
                if not cat:
                    return None

                if cat.parent_id is None:
                    return f"/{cat.name}"

                parent_path = build_path(cat.parent_id, visited.copy())
                if parent_path:
                    return f"{parent_path}/{cat.name}"
                else:
                    return f"/{cat.name}"

            # Update paths
            for category in categories:
                path = build_path(category.id)
                if path:
                    tenant_db.execute(
                        text("UPDATE categories SET path = :path WHERE id = :id"),
                        {"path": path, "id": category.id}
                    )

            tenant_db.commit()
            tenant_db.close()
            logger.info(f"Completed category path generation for tenant: {config.tenant_id}")

        except Exception as e:
            logger.error(f"Error processing tenant {config.tenant_id}: {str(e)}")
            if 'tenant_db' in locals():
                tenant_db.rollback()
                tenant_db.close()


def migrate_products_to_variants():
    """Convert existing products to product-variant model"""
    logger.info("Starting product to variant migration...")

    # Get all tenant databases
    central_db = next(get_central_db())
    tenant_configs = central_db.query(TenantDatabaseConfig).all()

    for config in tenant_configs:
        try:
            logger.info(f"Processing tenant: {config.tenant_id}")
            tenant_db = next(get_db_session(config.tenant_id))

            # Get all existing products with variant data
            products = tenant_db.execute(text("""
                SELECT id, sku, price, compare_at_price, cost_price,
                       inventory_quantity, track_inventory, continue_selling_when_out_of_stock,
                       weight, weight_unit, created_at, updated_at
                FROM products
                WHERE sku IS NOT NULL
            """)).fetchall()

            logger.info(f"Found {len(products)} products to migrate")

            for product in products:
                # Create a default variant for each product
                variant_data = {
                    'product_id': product.id,
                    'sku': product.sku,
                    'price': product.price,
                    'compare_at_price': product.compare_at_price,
                    'cost_price': product.cost_price,
                    'inventory_quantity': product.inventory_quantity or 0,
                    'track_inventory': product.track_inventory if product.track_inventory is not None else True,
                    'continue_selling_when_out_of_stock': product.continue_selling_when_out_of_stock if product.continue_selling_when_out_of_stock is not None else False,
                    'weight': product.weight,
                    'weight_unit': product.weight_unit or 'kg',
                    'attributes': '{}',
                    'created_at': product.created_at,
                    'updated_at': product.updated_at
                }

                # Insert variant
                tenant_db.execute(text("""
                    INSERT INTO product_variants
                    (product_id, sku, price, compare_at_price, cost_price,
                     inventory_quantity, track_inventory, continue_selling_when_out_of_stock,
                     weight, weight_unit, attributes, created_at, updated_at, metadata)
                    VALUES
                    (:product_id, :sku, :price, :compare_at_price, :cost_price,
                     :inventory_quantity, :track_inventory, :continue_selling_when_out_of_stock,
                     :weight, :weight_unit, :attributes, :created_at, :updated_at, '{}')
                """), variant_data)

                logger.info(f"Created variant for product {product.id} with SKU {product.sku}")

            tenant_db.commit()
            tenant_db.close()
            logger.info(f"Completed product variant migration for tenant: {config.tenant_id}")

        except Exception as e:
            logger.error(f"Error processing tenant {config.tenant_id}: {str(e)}")
            if 'tenant_db' in locals():
                tenant_db.rollback()
                tenant_db.close()


def remove_variant_fields_from_products():
    """Remove variant-specific fields from products table after migration"""
    logger.info("Removing variant fields from products table...")

    # Get all tenant databases
    central_db = next(get_central_db())
    tenant_configs = central_db.query(TenantDatabaseConfig).all()

    for config in tenant_configs:
        try:
            logger.info(f"Processing tenant: {config.tenant_id}")
            tenant_db = next(get_db_session(config.tenant_id))

            # Drop variant-specific columns from products table
            variant_columns = [
                'sku', 'price', 'compare_at_price', 'cost_price',
                'inventory_quantity', 'track_inventory', 'continue_selling_when_out_of_stock',
                'weight', 'weight_unit'
            ]

            for column in variant_columns:
                try:
                    tenant_db.execute(text(f"ALTER TABLE products DROP COLUMN IF EXISTS {column}"))
                    logger.info(f"Dropped column {column} from products table")
                except Exception as e:
                    logger.warning(f"Could not drop column {column}: {str(e)}")

            tenant_db.commit()
            tenant_db.close()
            logger.info(f"Completed removing variant fields for tenant: {config.tenant_id}")

        except Exception as e:
            logger.error(f"Error processing tenant {config.tenant_id}: {str(e)}")
            if 'tenant_db' in locals():
                tenant_db.rollback()
                tenant_db.close()


def main():
    """Run all migration steps"""
    logger.info("Starting data migration process...")

    try:
        # Step 1: Generate category paths
        generate_category_paths()

        # Step 2: Migrate products to variants
        migrate_products_to_variants()

        # Step 3: Remove variant fields from products (optional - can be done manually)
        # remove_variant_fields_from_products()

        logger.info("Data migration completed successfully!")

    except Exception as e:
        logger.error(f"Migration failed: {str(e)}")
        sys.exit(1)


if __name__ == "__main__":
    main()