import pandas as pd
import argparse
from sqlalchemy import create_engine, text, inspect, types

# Function to read a parquet file and return a pandas dataframe
def read_parquet(file_path):
    try:
        # Reading the parquet file
        dataframe = pd.read_parquet(file_path, engine='pyarrow')
        return dataframe
    except Exception as e:
        print(f"Error reading parquet file: {e}")
        return None

# Function to convert dataframe to SQL
def dataframe_to_sql(df, table_name):
    # Creating an in-memory MySQL database using SQLAlchemy; adjust the data types if needed
    engine = create_engine('mysql+pymysql://user:password@localhost/db_name', echo=False)
    df.to_sql(table_name, con=engine, if_exists='replace', index=False, dtype={
        # Adjust the types here based on your dataframe's schema and MySQL's data types
        # 'Id': types.INTEGER(),
        # 'TimeSpanType': types.SMALLINT(),
        # 'BuzzCategoryId': types.FLOAT(),
        # 'CountryCode': types.VARCHAR(length=8),
        # 'TrachReleaseType': types.SMALLINT(),
    })

    inspector = inspect(engine)
    columns = inspector.get_columns(table_name)

    # Creating the 'CREATE TABLE' statement
    fields = ',\n'.join([f"`{col['name']}` {col['type']}" for col in columns])
    create_table_statement = f"CREATE TABLE `{table_name}` (\n{fields}\n);"
    print(create_table_statement)

    # Fetching data to create 'INSERT INTO' statement
    with engine.connect() as conn:
        rows = conn.execute(text(f"SELECT * FROM `{table_name}`")).fetchall()

    insert_statement = []
    for row in rows:
        formatted_values = []
        for val in row:
            if val is None:
                formatted_values.append('NULL')
            else:
                formatted_values.append(repr(val))  # repr function handles string quoting
        formatted_row = ', '.join(formatted_values)
        insert_statement.append(f"({formatted_row})")

    insert_query = f"INSERT INTO `{table_name}` VALUES {', '.join(insert_statement)};"
    print(insert_query)

# Main execution
if __name__ == "__main__":
    # Initialize argument parser
    parser = argparse.ArgumentParser(description="Read Apache Parquet file and convert its contents to MySQL SQL syntax.")
    # Add argument for file path
    parser.add_argument('file_path', type=str, help='Path to the parquet file')
    parser.add_argument('table_name', type=str, help='Name of the SQL table')

    # Parse the arguments
    args = parser.parse_args()

    # Read the parquet file
    df = read_parquet(args.file_path)

    # Check if dataframe is not empty
    if df is not None:
        # Convert dataframe to SQL and print
        dataframe_to_sql(df, args.table_name)
    else:
        print("No data to process.")