import sqlite3
import re
import sys

print("Initializing SQLite target database...")
conn = sqlite3.connect("db.sqlite3")
cursor = conn.cursor()

# Enable pragmas for speed
cursor.execute("PRAGMA synchronous = OFF")
cursor.execute("PRAGMA journal_mode = MEMORY")

sql_file = "xygameco_xygaming.sql"

current_table = None
columns = []
records_inserted = 0

with open(sql_file, "r", encoding="utf-8", errors="ignore") as f:
    in_insert = False
    insert_prefix = ""
    
    for line in f:
        line_str = line.strip()
        if not line_str or line_str.startswith("--") or line_str.startswith("/*") or line_str.startswith("LOCK TABLES") or line_str.startswith("UNLOCK TABLES"):
            continue
            
        if line_str.startswith("INSERT INTO"):
            m = re.match(r'INSERT INTO [`"]?([a-zA-Z0-9_]+)[`"]?\s*\(([^)]+)\)\s*VALUES', line_str, re.IGNORECASE)
            if m:
                current_table = m.group(1)
                cols_str = m.group(2)
                cols = [c.strip(" `\"") for c in cols_str.split(",")]
                
                # Check target sqlite table info
                try:
                    d_cols = [col[1] for col in cursor.execute(f'PRAGMA table_info("{current_table}")').fetchall()]
                    if not d_cols:
                        current_table = None
                        continue
                    
                    # Filter columns that exist in SQLite schema
                    valid_indices = [i for i, c in enumerate(cols) if c in d_cols]
                    valid_cols = [cols[i] for i in valid_indices]
                    
                    if not valid_cols:
                        current_table = None
                        continue
                        
                    col_names = ", ".join([f'"{c}"' for c in valid_cols])
                    placeholders = ", ".join(["?" for _ in valid_cols])
                    insert_sql = f'INSERT OR REPLACE INTO "{current_table}" ({col_names}) VALUES ({placeholders})'
                    in_insert = True
                except Exception as e:
                    current_table = None
                    continue

        if in_insert and line_str.startswith("("):
            # Parse values row
            # Remove trailing comma or semicolon
            val_tuple_str = line_str.rstrip(",;")
            if val_tuple_str.startswith("(") and val_tuple_str.endswith(")"):
                # Use python eval / literal evaluation with safe context
                val_tuple_str_clean = val_tuple_str[1:-1]
                # Parse CSV with SQL escape handling
                try:
                    # Replace b'...' byte literals if any, handle NULLs
                    # We can use simple parser or re
                    tokens = []
                    curr = []
                    in_quotes = False
                    quote_char = None
                    escape = False
                    
                    for char in val_tuple_str_clean:
                        if escape:
                            curr.append(char)
                            escape = False
                        elif char == '\\':
                            escape = True
                        elif char in ("'", '"'):
                            if not in_quotes:
                                in_quotes = True
                                quote_char = char
                            elif quote_char == char:
                                in_quotes = False
                                quote_char = None
                            else:
                                curr.append(char)
                        elif char == ',' and not in_quotes:
                            tokens.append("".join(curr).strip())
                            curr = []
                        else:
                            curr.append(char)
                    if curr:
                        tokens.append("".join(curr).strip())
                        
                    # Filter tokens based on valid_indices
                    filtered_vals = []
                    for idx in valid_indices:
                        if idx < len(tokens):
                            raw = tokens[idx]
                            if raw.upper() == 'NULL':
                                filtered_vals.append(None)
                            elif (raw.startswith("'") and raw.endswith("'")) or (raw.startswith('"') and raw.endswith('"')):
                                filtered_vals.append(raw[1:-1].replace("\\'", "'").replace('\\"', '"').replace('\\\\', '\\').replace('\\n', '\n').replace('\\r', '\r'))
                            else:
                                try:
                                    if '.' in raw:
                                        filtered_vals.append(float(raw))
                                    else:
                                        filtered_vals.append(int(raw))
                                except ValueError:
                                    filtered_vals.append(raw)
                        else:
                            filtered_vals.append(None)
                    
                    cursor.execute(insert_sql, filtered_vals)
                    records_inserted += 1
                    if records_inserted % 50000 == 0:
                        conn.commit()
                        print(f"Inserted {records_inserted:,} records so far...")
                except Exception as e:
                    pass
            
            if line_str.endswith(";"):
                in_insert = False
                current_table = None

conn.commit()
conn.close()
print(f"CONVERSION COMPLETE! Total records inserted into SQLite db.sqlite3: {records_inserted:,}")