import os
import csv
import datetime
from pathlib import Path

def csv_to_sql_insert(csv_file, table_name, output_dir):
    """
    Convert a CSV file to SQL INSERT statements.
    
    Args:
        csv_file (str): Path to the CSV file
        table_name (str): Name of the table for the INSERT statement
        output_dir (str): Directory to save the SQL file
    """
    sql_file = os.path.join(output_dir, f"{table_name}.sql")
    
    try:
        with open(csv_file, 'r', encoding='utf-8') as csv_input, open(sql_file, 'w', encoding='utf-8') as sql_output:
            csv_reader = csv.reader(csv_input)
            headers = next(csv_reader)  # Get column names from first row
            
            # Write a comment header in the SQL file
            sql_output.write(f"-- SQL Insert script for {table_name}\n")
            sql_output.write(f"-- Generated on {datetime.datetime.now()}\n")
            sql_output.write(f"-- Source file: {csv_file}\n\n")
            
            # Process data rows
            row_count = 0
            batch_size = 1000  # Number of rows per batch insert
            batch_rows = []
            
            for row in csv_reader:
                # Process each row
                row_count += 1
                values = []
                
                for value in row:
                    if value == "NOW()":  # Handle special case for NOW() function
                        values.append(value)
                    elif not value:  # Handle empty values
                        values.append("NULL")
                    elif value.isdigit():  # Handle numeric values
                        values.append(value)
                    else:  # Handle string values with proper escaping
                        # Fix: Use string replace method outside of f-string
                        escaped_value = value.replace("'", "''")
                        values.append(f"'{escaped_value}'")
                
                value_str = ','.join(values)
                batch_rows.append(f"    ({value_str})")
                
                # Write batch if reaching batch size
                if len(batch_rows) >= batch_size:
                    write_batch_insert(sql_output, table_name, headers, batch_rows)
                    batch_rows = []
            
            # Write any remaining rows
            if batch_rows:
                write_batch_insert(sql_output, table_name, headers, batch_rows)
            
            print(f"Created {sql_file} with {row_count} rows from {csv_file}")
    
    except Exception as e:
        print(f"Error processing {csv_file}: {str(e)}")

def write_batch_insert(file_handle, table_name, headers, value_rows):
    """
    Write a batch INSERT statement to the SQL file.
    
    Args:
        file_handle: Open file handle for the SQL file
        table_name (str): Name of the table
        headers (list): Column names
        value_rows (list): List of formatted value strings
    """
    columns = ','.join(headers)
    file_handle.write(f"INSERT INTO royalty_accounting.{table_name} ({columns})\nVALUES\n")
    file_handle.write(',\n'.join(value_rows))
    file_handle.write("\n;\n\n")

def main():
    # Define file names to process
    table_names = [
        "account",
        "account_payment_term",
        "account_payee",
        "account_tax_info",
        "contract",
        "run_controller_contract",
        "legacy_contract",
        "account_contract",
        "contract_term",
        "contract_term_condition"
    ]
    
    # Get directory paths
    script_dir = os.path.dirname(os.path.abspath(__file__))
    csv_dir = input(f"Enter path to CSV files directory [default: {script_dir}]: ").strip() or script_dir
    output_dir = input(f"Enter path to save SQL files [default: {script_dir}]: ").strip() or script_dir
    
    # Create output directory if it doesn't exist
    Path(output_dir).mkdir(parents=True, exist_ok=True)
    
    # Process each file
    for table_name in table_names:
        csv_file = os.path.join(csv_dir, f"{table_name}.csv")
        
        if os.path.exists(csv_file):
            csv_to_sql_insert(csv_file, table_name, output_dir)
        else:
            print(f"Warning: CSV file not found: {csv_file}")
    
    print(f"\nSQL generation complete. Files saved to: {output_dir}")

if __name__ == "__main__":
    main()