#!/usr/bin/env python3
  """
  Import test data into MySQL Shoplazza tables.
  
  Reads SQL file generated by generate_test_data.py and imports into MySQL.
  """
  
  import sys
  import os
  import argparse
  from pathlib import Path
  
  # Add parent directory to path
  sys.path.insert(0, str(Path(__file__).parent.parent))
  
  from utils.db_connector import create_db_connection, test_connection
  
  
  def import_sql_file(db_engine, sql_file: str):
      """

      Import SQL file into database using MySQL client (more reliable for large files).

  
      Args:

          db_engine: SQLAlchemy database engine (used to get connection info)

          sql_file: Path to SQL file
      """

      import subprocess
      import os
      from pathlib import Path
      
      # Get connection info from engine URL
      engine_url = str(db_engine.url)
      # Parse: mysql+pymysql://user:pass@host:port/database
      import re
      match = re.match(r'mysql\+pymysql://([^:]+):([^@]+)@([^:]+):(\d+)/(.+)', engine_url)
      if not match:
          raise ValueError(f"Cannot parse database URL: {engine_url}")
      
      username, password, host, port, database = match.groups()
      
      # Use MySQL client to execute SQL file (more reliable)
      sql_file_path = Path(sql_file).absolute()
      
      # Build mysql command
      mysql_cmd = [
          'mysql',
          f'-h{host}',
          f'-P{port}',
          f'-u{username}',
          f'-p{password}',
          database
      ]
      
      print(f"Executing SQL file using MySQL client...")
      print(f"  File: {sql_file_path}")
      print(f"  Database: {host}:{port}/{database}")
      
      try:
          with open(sql_file_path, 'r', encoding='utf-8') as f:
              result = subprocess.run(
                  mysql_cmd,
                  stdin=f,
                  capture_output=True,
                  text=True,
                  timeout=300  # 5 minute timeout
              )
          
          if result.returncode != 0:
              error_msg = result.stderr or result.stdout
              print(f"ERROR: MySQL execution failed")
              print(f"Error output: {error_msg[:500]}")
              raise Exception(f"MySQL execution failed: {error_msg[:200]}")
          
          print("SQL file executed successfully")
          return True
          
      except FileNotFoundError:
          # Fallback to SQLAlchemy if mysql client not available
          print("MySQL client not found, falling back to SQLAlchemy...")
          return import_sql_file_sqlalchemy(db_engine, sql_file)
      except subprocess.TimeoutExpired:
          raise Exception("SQL execution timed out after 5 minutes")
      except Exception as e:
          print(f"Error using MySQL client: {e}")
          print("Falling back to SQLAlchemy...")
          return import_sql_file_sqlalchemy(db_engine, sql_file)
  
  
  def import_sql_file_sqlalchemy(db_engine, sql_file: str):
      """
      Fallback method: Import SQL file using SQLAlchemy (for when mysql client unavailable).
      """

      from sqlalchemy import text
      

      with open(sql_file, 'r', encoding='utf-8') as f:
          sql_content = f.read()
      

      # Remove comment lines
      lines = sql_content.split('\n')
      cleaned_lines = []
      for line in lines:
          stripped = line.lstrip()
          if stripped.startswith('--'):
              continue
          cleaned_lines.append(line)
      
      sql_content = '\n'.join(cleaned_lines)
      
      # Split by semicolon - but we need to handle strings properly
      # Use a state machine to track string boundaries

      statements = []

      current = []

      in_string = False

      i = 0

      while i < len(sql_content):
          char = sql_content[i]
          

          if char == "'":
              # Check for escaped quote (two single quotes)
              if i + 1 < len(sql_content) and sql_content[i+1] == "'":
                  current.append("''")
                  i += 1  # Skip next quote
              elif not in_string:

                  in_string = True

                  current.append(char)
              else:

                  in_string = False

                  current.append(char)
          else:
              current.append(char)

          # Split on semicolon only if not in string

          if char == ';' and not in_string:

              stmt = ''.join(current).strip()
              if stmt and stmt.upper().startswith('INSERT INTO'):

                  statements.append(stmt)

              current = []

          
          i += 1
      

      # Handle last statement
      if current:
          stmt = ''.join(current).strip()
          if stmt and stmt.upper().startswith('INSERT INTO'):

              statements.append(stmt)

      print(f"Parsed {len(statements)} SQL statements")

      print(f"Executing {len(statements)} SQL statements...")
      

      # Use raw connection to avoid SQLAlchemy parameter parsing
      raw_conn = db_engine.raw_connection()
      try:
          cursor = raw_conn.cursor()
          try:

              for i, statement in enumerate(statements, 1):

                  try:

                      # Execute raw SQL directly using pymysql cursor
                      cursor.execute(statement)
                      raw_conn.commit()
                      if i % 1000 == 0 or i == len(statements):

                          print(f"  [{i}/{len(statements)}] Executed successfully")

                  except Exception as e:
                      print(f"  [{i}/{len(statements)}] ERROR: {e}")

                      error_start = max(0, statement.find('VALUES') - 100)
                      error_end = min(len(statement), error_start + 500)
                      print(f"  Statement context: ...{statement[error_start:error_end]}...")

                      raise

          finally:
              cursor.close()
      finally:
          raw_conn.close()
      
      return True

  
  
  def verify_import(db_engine, tenant_id: str):
      """
      Verify imported data.
  
      Args:
          db_engine: SQLAlchemy database engine
          tenant_id: Tenant ID to verify
      """
      from sqlalchemy import text
      
      with db_engine.connect() as conn:
          # Count SPUs
          result = conn.execute(text("SELECT COUNT(*) FROM shoplazza_product_spu WHERE tenant_id = :tenant_id"), {"tenant_id": tenant_id})
          spu_count = result.scalar()
          
          # Count SKUs
          result = conn.execute(text("SELECT COUNT(*) FROM shoplazza_product_sku WHERE tenant_id = :tenant_id"), {"tenant_id": tenant_id})
          sku_count = result.scalar()
          
          print(f"\nVerification:")
          print(f"  SPUs: {spu_count}")
          print(f"  SKUs: {sku_count}")
          
          return spu_count, sku_count
  
  
  def main():
      parser = argparse.ArgumentParser(description='Import test data into MySQL')
      
      # Database connection
      parser.add_argument('--db-host', required=True, help='MySQL host')
      parser.add_argument('--db-port', type=int, default=3306, help='MySQL port (default: 3306)')
      parser.add_argument('--db-database', required=True, help='MySQL database name')
      parser.add_argument('--db-username', required=True, help='MySQL username')
      parser.add_argument('--db-password', required=True, help='MySQL password')
      
      # Import options
      parser.add_argument('--sql-file', required=True, help='SQL file to import')
      parser.add_argument('--tenant-id', help='Tenant ID to verify (optional)')
      
      args = parser.parse_args()
  
      print(f"Connecting to MySQL: {args.db_host}:{args.db_port}/{args.db_database}")
      
      # Connect to database
      try:
          db_engine = create_db_connection(
              host=args.db_host,
              port=args.db_port,
              database=args.db_database,
              username=args.db_username,
              password=args.db_password
          )
      except Exception as e:
          print(f"ERROR: Failed to connect to MySQL: {e}")
          return 1
  
      # Test connection
      if not test_connection(db_engine):
          print("ERROR: Database connection test failed")
          return 1
  
      print("Database connection successful")
  

      # Clean existing data if tenant_id provided
      if args.tenant_id:
          print(f"\nCleaning existing data for tenant_id: {args.tenant_id}")
          from sqlalchemy import text
          try:
              with db_engine.connect() as conn:
                  # Delete SKUs first (foreign key constraint)
                  conn.execute(text(f"DELETE FROM shoplazza_product_sku WHERE tenant_id = '{args.tenant_id}'"))
                  # Delete SPUs
                  conn.execute(text(f"DELETE FROM shoplazza_product_spu WHERE tenant_id = '{args.tenant_id}'"))
                  conn.commit()
                  print("✓ Existing data cleaned")
          except Exception as e:
              print(f"⚠ Warning: Failed to clean existing data: {e}")
              # Continue anyway
  

      # Import SQL file
      print(f"\nImporting SQL file: {args.sql_file}")
      try:
          import_sql_file(db_engine, args.sql_file)
          print("Import completed successfully")
      except Exception as e:
          print(f"ERROR: Failed to import SQL file: {e}")
          import traceback
          traceback.print_exc()
          return 1
  
      # Verify import if tenant_id provided
      if args.tenant_id:
          verify_import(db_engine, args.tenant_id)
  
      return 0
  
  
  if __name__ == '__main__':
      sys.exit(main())