schema_creator.py

import psycopg2
from app.core.config import settings

ALLOWED_TYPES = {
    "text": "TEXT",
    "string": "TEXT",
    "int": "INTEGER",
    "integer": "INTEGER",
    "number": "REAL",
    "float": "REAL",
    "timestamp": "TIMESTAMP",
    "date": "TIMESTAMP",
}


def create_table(table_name: str, columns: list[dict]):
    cols_sql = []

    for col in columns:
        if col["type"] not in ALLOWED_TYPES:
            raise ValueError(f"Unsupported column type: {col['type']}")

        cols_sql.append(
            f"{col['name']} {ALLOWED_TYPES[col['type']]}"
        )

    sql = f"""
    CREATE TABLE IF NOT EXISTS {table_name} (
        id SERIAL PRIMARY KEY,
        {", ".join(cols_sql)}
    );
    """

    conn = psycopg2.connect(
        host=settings.DB_HOST,
        port=settings.DB_PORT,
        dbname=settings.DB_NAME,
        user=settings.DB_USER,
        password=settings.DB_PASSWORD,
    )

    try:
        with conn.cursor() as cursor:
            cursor.execute(sql)
            conn.commit()
    finally:
        conn.close()