#!/usr/bin/env python3
"""
Convert INSERT statements to use ON CONFLICT DO NOTHING for duplicate prevention
"""
import re
import sys

def convert_insert_sql(input_file, output_file):
    """Convert INSERT statements to safe INSERT with ON CONFLICT clause"""

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

    # Pattern to match INSERT INTO statements
    # This pattern captures: table name and column names, and the VALUES
    insert_pattern = r'INSERT INTO public\.(\w+) \(([^)]+)\) VALUES \(([^)]+)\);'

    def replace_insert(match):
        table = match.group(1)
        columns = match.group(2)
        values = match.group(3)

        # Special handling for tables without explicit 'id' column
        # Most tables have 'id' as first column
        if table in ['platform_settings']:
            # These might not have id or use different constraint
            return f"INSERT INTO public.{table} ({columns}) VALUES ({values}) ON CONFLICT DO NOTHING;"

        return f"INSERT INTO public.{table} ({columns}) VALUES ({values}) ON CONFLICT (id) DO NOTHING;"

    # Apply the replacement
    converted_content = re.sub(insert_pattern, replace_insert, content, flags=re.MULTILINE)

    # Write the converted content
    with open(output_file, 'w', encoding='utf-8') as f:
        f.write(converted_content)

    return True

if __name__ == "__main__":
    input_file = "/home/ashraffarid2010/munafasaai.com/insert_data.sql"
    output_file = "/tmp/insert_data_safe.sql"

    print(f"Converting {input_file} to {output_file}...")

    if convert_insert_sql(input_file, output_file):
        print("Conversion complete!")

        # Count original INSERT statements
        with open(input_file, 'r') as f:
            original_count = len(re.findall(r'INSERT INTO public\.', f.read()))

        # Count converted INSERT statements
        with open(output_file, 'r') as f:
            converted_count = len(re.findall(r'ON CONFLICT', f.read()))

        print(f"Original INSERT statements: {original_count}")
        print(f"Converted INSERT statements: {converted_count}")
    else:
        print("Conversion failed!")
        sys.exit(1)