from sqlalchemy import (
    create_engine,
    MetaData,
    Table,
    Column,
    String,
    Integer,
    Float,
    insert,
    text,
)

# Use a persistent SQLite database file
engine = create_engine("sqlite:///database.db")
metadata_obj = MetaData()

def create_dynamic_table(df):
    """
    Creates a table dynamically based on DataFrame schema.
    
    Args:
        df: pandas DataFrame containing the data
        
    Returns:
        SQLAlchemy Table object
    """
    # Drop existing table if it exists
    if 'data_table' in metadata_obj.tables:
        metadata_obj.remove(metadata_obj.tables['data_table'])
    
    # Create columns based on DataFrame dtypes
    columns = []
    for col_name, dtype in df.dtypes.items():
        if 'int' in str(dtype):
            col_type = Integer
        elif 'float' in str(dtype):
            col_type = Float
        else:
            col_type = String(255)  # Using a generous length for string columns
        
        # First column becomes primary key
        if len(columns) == 0:
            columns.append(Column(col_name, col_type, primary_key=True))
        else:
            columns.append(Column(col_name, col_type))
    
    # Create new table
    table = Table('data_table', metadata_obj, *columns)
    
    # Create table in database
    metadata_obj.create_all(engine, tables=[table])
    
    return table

def clear_database():
    """
    Removes all tables from the database.
    """
    metadata_obj.drop_all(engine)
    metadata_obj.clear()

def insert_rows_into_table(rows, table):
    """
    Inserts rows into the specified table.
    
    Args:
        rows: List of dictionaries containing the row data
        table: SQLAlchemy Table object
    """
    with engine.begin() as connection:
        connection.execute(insert(table), rows)

def get_table_schema():
    """
    Returns the current table schema as a string.
    """
    if 'data_table' not in metadata_obj.tables:
        return None
        
    table = metadata_obj.tables['data_table']
    schema = []
    for column in table.columns:
        col_type = str(column.type).upper()
        is_primary = "primary key" if column.primary_key else ""
        schema.append(f"- {column.name} ({col_type}) {is_primary}".strip())
    
    return "\n".join(schema)