import csv
import pymysql

def generate_and_execute_queries(csv_path, output_path, db_config):
    """
    Automates SQL query generation, execution, and saving results to a CSV.

    Args:
        csv_path (str): Path to the input CSV file containing table and column names.
        output_path (str): Path to save the query results as a CSV.
        db_config (dict): Database connection configuration.
    """

    connection = pymysql.connect(
        host=db_config['host'],
        user=db_config['user'],
        password=db_config['password'],
        database=db_config['database'],
        port=db_config.get('port', 3306)
    )
    results = []

    try:

        with open(csv_path, mode='r') as file:
            reader = csv.DictReader(file)
            for row in reader:
                table_name = row['table_name']
                column_name = row['column_name']

                if not table_name or not column_name:
                    print(f"Missing data in row: {row}")
                    continue

                query_max_integer_digits = f"""
                SELECT 
                    MAX(LENGTH(FLOOR({column_name}))) AS max_integer_digits
                FROM {table_name};
                """
                query_max_fraction_digits = f"""
                SELECT 
                    MAX(LENGTH(SUBSTRING_INDEX({column_name}, '.', -1))) AS max_fraction_digits
                FROM {table_name}
                WHERE {column_name} LIKE '%.%';
                """

                with connection.cursor() as cursor:
                    cursor.execute(query_max_integer_digits)
                    max_integer_digits = cursor.fetchone()[0] or 0

                    cursor.execute(query_max_fraction_digits)
                    max_fraction_digits = cursor.fetchone()[0] or 0

                total_precision = max_integer_digits + max_fraction_digits
                decimal_format = f"DECIMAL({total_precision},{max_fraction_digits})"
                print(f"Column: {column_name} - {decimal_format}")

                results.append({
                    "table_name": table_name,
                    "column_name": column_name,
                    "decimal_format": decimal_format
                })

    finally:
        connection.close()

    with open(output_path, mode='w', newline='') as output_file:
        writer = csv.DictWriter(output_file, fieldnames=["table_name", "column_name", "decimal_format"])
        writer.writeheader()
        writer.writerows(results)
        print(f"Query results have been written to {output_path}.")

csv_file_path = './inputs.csv'
output_file_path = './query_results.csv'
db_config = {
    "host": "hostname",
    "user": "username",
    "password": "password",
    "database": "dbname"
}

generate_and_execute_queries(csv_file_path, output_file_path, db_config)