backend/database.py

# from sqlalchemy import create_engine
# import os
# import sys
# from pathlib import Path
 
# # ensure project root (agent/) is on sys.path so sibling packages like "s3" can be imported
# project_root = Path(__file__).resolve().parents[2]  # -> ...\openai_agents\agent
# if str(project_root) not in sys.path:
#     sys.path.insert(0, str(project_root))
# from sqlalchemy.ext.declarative import declarative_base
# from sqlalchemy.orm import sessionmaker
# import urllib.parse
# import psycopg2
# # from retrieve_secret import CONNECTIONS_HOST,CONNECTIONS_DB,CONNECTIONS_USER,CONNECTIONS_PASS
# # from retrieve_secret import *
# from retrieve_secret import *
from sqlalchemy import create_engine
import os
import sys
from pathlib import Path
 
# ensure project root (mvp_2.0_deploy_all_apis/) is on sys.path so sibling packages like "retrieve_secret" can be imported
project_root = Path(__file__).resolve().parents[1]  # -> ...\mvp_2.0_deploy_all_apis (inner)
if str(project_root) not in sys.path:
    sys.path.insert(0, str(project_root))
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import urllib.parse
import psycopg2
from dotenv import load_dotenv
load_dotenv()
# from retrieve_secret import CONNECTIONS_HOST,CONNECTIONS_DB,CONNECTIONS_USER,CONNECTIONS_PASS
# from retrieve_secret import *
from retrieve_secret import * 


# --- Neon PostgreSQL credentials ---
# PGHOST = 'ep-steep-dream-adqtvjel-pooler.c-2.us-east-1.aws.neon.tech'
# PGDATABASE = 'neondb'
# PGUSER = 'neondb_owner'
# PGPASSWORD = 'npg_Qq0B1uWRXavx'


# print(CONNECTIONS_HOST,"kkkk")


# print("one")

PGHOST =CONNECTIONS_HOST
# print(PGHOST,"PGHOST")

PGUSER = CONNECTIONS_USER
PGPASSWORD = CONNECTIONS_PASS
PGDATABASE = CONNECTIONS_DB


# print(PGHOST,"PGHOST")
# print(PGUSER,"PGUSER")



# --- encode password ---
encoded_password = urllib.parse.quote_plus(PGPASSWORD)

# --- ensure target database exists (connects to default 'postgres') ---
def ensure_database_exists():
    try:
        conn = psycopg2.connect(
            host=PGHOST,
            user=PGUSER,
            password=PGPASSWORD,
            dbname="postgres",
            sslmode="require",
        )
        conn.autocommit = True
        cur = conn.cursor()
        cur.execute("SELECT 1 FROM pg_database WHERE datname = %s", (PGDATABASE,))
        if not cur.fetchone():
            cur.execute(f'CREATE DATABASE "{PGDATABASE}" WITH OWNER {PGUSER} ENCODING \"UTF8\";')
        cur.close()
        conn.close()
    except Exception as e:
        # non-fatal: if creation fails (permissions), fallback to normal connect which will raise
        print(f"Warning: could not ensure database exists: {e}")

ensure_database_exists()

# ---connection URL ---
DATABASE_URL = (
    f"postgresql+psycopg2://{PGUSER}:{encoded_password}@{PGHOST}/{PGDATABASE}?sslmode=require"
)

engine = create_engine(
    DATABASE_URL,
    pool_pre_ping=True,      
    pool_recycle=300,        
    echo=False               
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

# --- Dependency for FastAPI routes ---
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

# --- Optional: create tables on startup ---
def create_tables():
    from . import models
    Base.metadata.create_all(bind=engine)