find_sync_differences.py

#!/usr/bin/env python3
"""
Find Sync Differences Tool
==========================

This tool identifies exactly which rows are causing sync mismatches
by comparing all rows in a table and showing specific differences.

Usage:
    python find_sync_differences.py [table_name] [--limit N]
"""

import sys
import os
import sqlite3
import pymysql
import hashlib
import json
from getpass import getpass
from datetime import datetime, date
from decimal import Decimal

# Configuration
SQLITE_DB_PATH = os.path.expanduser("~/Github/Cruise_Logs/Cruise_Logs.db")
MARIADB_CONFIG = {
    'host': 'cutter.pmel.noaa.gov',
    'user': 'atlas_adm',
    'database': 'Cruise_Logs',
    'charset': 'utf8mb4'
}

def normalize_value_with_type(value, column_type, is_sqlite=True):
    """Normalize value based on destination column type"""
    if value is None:
        return 'NULL'

    # Handle datetime/date objects
    if isinstance(value, datetime):
        return value.strftime('%Y-%m-%d %H:%M:%S')
    elif isinstance(value, date):
        return value.strftime('%Y-%m-%d')

    column_type_upper = column_type.upper()

    # Handle boolean/tinyint columns specially
    if 'TINYINT' in column_type_upper or 'BOOLEAN' in column_type_upper:
        str_val = str(value).strip().lower()
        if str_val == '' or str_val == 'none' or str_val == 'null':
            return '0'
        elif str_val in ('yes', 'true', '1'):
            return '1'
        elif str_val in ('no', 'false', '0'):
            return '0'
        elif isinstance(value, (int, bool)):
            return '1' if bool(value) else '0'
        else:
            return '0'

    # Handle numeric types
    if any(t in column_type_upper for t in ['INT', 'DECIMAL', 'FLOAT', 'DOUBLE', 'NUMERIC']):
        if isinstance(value, (float, Decimal)):
            normalized = round(float(value), 6)
            if normalized == int(normalized):
                return str(int(normalized))
            return f"{normalized:.6f}".rstrip('0').rstrip('.')
        elif isinstance(value, (int, bool)):
            return str(int(value))
        else:
            str_val = str(value).strip()
            if str_val == '' or str_val.lower() in ('none', 'null'):
                return '0'
            try:
                return str(int(float(str_val)))
            except:
                return '0'

    # Handle JSON columns
    if 'JSON' in column_type_upper:
        str_val = str(value).strip()
        if str_val.startswith(('{', '[')) and len(str_val) > 1:
            try:
                parsed = json.loads(str_val)
                return json.dumps(parsed, sort_keys=True, separators=(',', ':'))
            except json.JSONDecodeError:
                pass

    # Handle text/varchar columns
    str_val = str(value).strip()
    if str_val.lower() in ('none', 'null'):
        return ''

    # Normalize whitespace
    return ' '.join(str_val.split())

def get_all_checksums(conn, table, is_sqlite=True, mariadb_schema=None, limit=None):
    """Get checksums for all rows in a table"""
    cursor = conn.cursor()
    checksums = {}

    try:
        # Get column information
        if is_sqlite:
            cursor.execute(f"PRAGMA table_info([{table}])")
            columns = [row[1] for row in cursor.fetchall()]
            # Use MariaDB schema for column types if provided
            column_types = {}
            for col in columns:
                if mariadb_schema and col in mariadb_schema:
                    column_types[col] = mariadb_schema[col]
                else:
                    column_types[col] = 'TEXT'
        else:
            cursor.execute(f"DESCRIBE `{table}`")
            column_info = cursor.fetchall()
            columns = [row[0] for row in column_info]
            column_types = {row[0]: row[1] for row in column_info}

        # Get all data
        column_list = ', '.join([f'`{col}`' for col in columns])
        query = f"SELECT {column_list} FROM `{table}` ORDER BY `{columns[0]}`"
        if limit:
            query += f" LIMIT {limit}"

        cursor.execute(query)
        rows = cursor.fetchall()

        for row in rows:
            row_key = str(row[0])

            # Create normalized values
            normalized_values = []
            for i, val in enumerate(row):
                col_name = columns[i]
                col_type = column_types[col_name]
                normalized_val = normalize_value_with_type(val, col_type, is_sqlite)
                normalized_values.append(normalized_val)

            row_string = '|'.join(normalized_values)
            checksum = hashlib.md5(row_string.encode('utf-8')).hexdigest()

            checksums[row_key] = {
                'checksum': checksum,
                'row_string': row_string,
                'raw_row': row
            }

    finally:
        cursor.close()

    return checksums, columns, column_types

def find_differences(table, limit=None):
    """Find all sync differences for a table"""
    print(f"🔍 FINDING SYNC DIFFERENCES FOR: {table}")
    print("=" * 60)

    # Get MariaDB password
    mariadb_config = MARIADB_CONFIG.copy()
    mariadb_config['password'] = getpass("Enter MariaDB password: ")

    # Connect to databases
    print("\n📡 Connecting to databases...")
    sqlite_conn = sqlite3.connect(SQLITE_DB_PATH)
    mariadb_conn = pymysql.connect(**mariadb_config)
    print("✅ Connected!")

    # Get MariaDB schema
    mariadb_cursor = mariadb_conn.cursor()
    mariadb_cursor.execute(f"DESCRIBE `{table}`")
    mariadb_schema = {row[0]: row[1] for row in mariadb_cursor.fetchall()}
    mariadb_cursor.close()

    # Get checksums from both databases
    print(f"\n🔄 Analyzing {'all' if not limit else limit} rows...")
    sqlite_data, columns, column_types = get_all_checksums(
        sqlite_conn, table, True, mariadb_schema, limit
    )
    mariadb_data, _, _ = get_all_checksums(
        mariadb_conn, table, False, mariadb_schema, limit
    )

    print(f"✅ SQLite: {len(sqlite_data)} rows")
    print(f"✅ MariaDB: {len(mariadb_data)} rows")

    # Find differences
    all_keys = set(sqlite_data.keys()) | set(mariadb_data.keys())
    matches = []
    mismatches = []
    only_sqlite = []
    only_mariadb = []

    for key in all_keys:
        if key in sqlite_data and key in mariadb_data:
            if sqlite_data[key]['checksum'] == mariadb_data[key]['checksum']:
                matches.append(key)
            else:
                mismatches.append(key)
        elif key in sqlite_data:
            only_sqlite.append(key)
        else:
            only_mariadb.append(key)

    # Report summary
    print(f"\n📊 ANALYSIS RESULTS:")
    print("-" * 40)
    print(f"✅ Matching rows: {len(matches)}")
    print(f"❌ Mismatched rows: {len(mismatches)}")
    print(f"📥 Only in SQLite: {len(only_sqlite)}")
    print(f"📤 Only in MariaDB: {len(only_mariadb)}")

    total_differences = len(mismatches) + len(only_sqlite) + len(only_mariadb)
    print(f"🔄 Total sync changes needed: {total_differences}")

    # Show sample mismatches
    if mismatches:
        print(f"\n🔍 SAMPLE MISMATCHES (showing first 5):")
        print("=" * 60)

        for i, key in enumerate(mismatches[:5]):
            print(f"\n📋 ROW {key} - MISMATCH {i+1}:")
            print("-" * 40)

            sqlite_row = sqlite_data[key]
            mariadb_row = mariadb_data[key]

            print(f"SQLite checksum:  {sqlite_row['checksum'][:16]}...")
            print(f"MariaDB checksum: {mariadb_row['checksum'][:16]}...")

            # Compare values column by column
            sqlite_values = sqlite_row['row_string'].split('|')
            mariadb_values = mariadb_row['row_string'].split('|')

            print(f"\nColumn differences:")
            for j, col in enumerate(columns):
                if j < len(sqlite_values) and j < len(mariadb_values):
                    s_val = sqlite_values[j]
                    m_val = mariadb_values[j]

                    if s_val != m_val:
                        col_type = column_types[col]
                        s_raw = sqlite_row['raw_row'][j]
                        m_raw = mariadb_row['raw_row'][j]

                        print(f"  🔸 {col} ({col_type}):")
                        print(f"     SQLite:  {repr(s_raw)} → '{s_val}'")
                        print(f"     MariaDB: {repr(m_raw)} → '{m_val}'")

    # Show rows only in one database
    if only_sqlite:
        print(f"\n📥 ROWS ONLY IN SQLITE (first 3): {only_sqlite[:3]}")
    if only_mariadb:
        print(f"📤 ROWS ONLY IN MARIADB (first 3): {only_mariadb[:3]}")

    # Show some matching rows for verification
    if matches:
        print(f"\n✅ SAMPLE MATCHING ROWS: {matches[:5]}")

    # Test sync behavior prediction
    print(f"\n🎯 SYNC PREDICTION:")
    print("-" * 30)
    if total_differences == 0:
        print("✅ No differences found - sync should report 'No changes needed'")
    else:
        print(f"🔄 Sync should report {total_differences} changes needed:")
        if mismatches:
            print(f"   • {len(mismatches)} rows to update")
        if only_sqlite:
            print(f"   • {len(only_sqlite)} rows to insert")
        if only_mariadb:
            print(f"   • {len(only_mariadb)} rows to delete")

    # Cleanup
    sqlite_conn.close()
    mariadb_conn.close()

    return {
        'total_rows_sqlite': len(sqlite_data),
        'total_rows_mariadb': len(mariadb_data),
        'matches': len(matches),
        'mismatches': len(mismatches),
        'only_sqlite': len(only_sqlite),
        'only_mariadb': len(only_mariadb),
        'total_differences': total_differences,
        'sample_mismatches': mismatches[:5]
    }

def main():
    print("🔍 SYNC DIFFERENCE FINDER")
    print("=" * 40)

    # Parse arguments
    table = 'spool_inventory'  # Default
    limit = None

    if len(sys.argv) > 1:
        table = sys.argv[1]
    if len(sys.argv) > 2 and sys.argv[2] == '--limit':
        limit = int(sys.argv[3]) if len(sys.argv) > 3 else 100

    print(f"🎯 Target table: {table}")
    if limit:
        print(f"📝 Limit: {limit} rows")
    else:
        print("📝 Analyzing ALL rows")

    # Check SQLite database
    if not os.path.exists(SQLITE_DB_PATH):
        print(f"❌ SQLite database not found: {SQLITE_DB_PATH}")
        return 1

    try:
        results = find_differences(table, limit)

        print(f"\n" + "=" * 60)
        print("🎉 ANALYSIS COMPLETE")

        print(f"\n💡 FINDINGS:")
        if results['total_differences'] == 0:
            print("✅ No sync differences found!")
            print("   The sync tool should report 'No changes needed'")
            print("   If it doesn't, there may be a bug in the sync logic")
        else:
            print(f"🔄 Found {results['total_differences']} differences")
            print("   The sync tool behavior matches this analysis")

        print(f"\n🔧 RECOMMENDATIONS:")
        if results['mismatches'] > 0:
            print(f"   • Run sync to update {results['mismatches']} mismatched rows")
        if results['only_sqlite'] > 0:
            print(f"   • {results['only_sqlite']} rows will be inserted to MariaDB")
        if results['only_mariadb'] > 0:
            print(f"   • {results['only_mariadb']} rows will be deleted from MariaDB")

        if results['total_differences'] == 0:
            print("   • No sync needed - databases are identical")

    except Exception as e:
        print(f"❌ Error: {e}")
        import traceback
        traceback.print_exc()
        return 1

    return 0

if __name__ == "__main__":
    sys.exit(main())