import glob
import logging
import os
import psycopg

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger("migrations")

conn_str = os.getenv("DATABASE_URL", "postgresql://dscons_user:dscons_password@postgres:5432/dscons_operations")
logger.info(f"Connecting to database: {conn_str.split('@')[-1]}")

conn = psycopg.connect(conn_str, autocommit=True)

files = sorted(glob.glob("db/migrations/*.sql"))
for f in files:
    logger.info(f"Applying migration: {f}")
    with open(f, "r", encoding="utf-8") as sql_file:
        sql = sql_file.read()
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
        logger.info(f"[OK] {f}")
    except Exception as e:
        logger.warning(f"[NOTICE] {f}: {e}")

conn.close()
logger.info("All migrations completed successfully!")