import pandas as pd
import os
import psycopg2
from sqlalchemy import create_engine, inspect, text
import re
from datetime import datetime
import logging
from dotenv import load_dotenv

load_dotenv()

# Set up logging
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)

# Database connection parameters - use environment variables with fallbacks
DB_PARAMS = {
    'dbname': os.getenv("DB_NAME"),
    'user': os.getenv("DB_USER"),
    'password': os.getenv("DB_PASSWORD"),
    'host': os.getenv("DB_HOST"),
    'port': os.getenv("DB_PORT")
}

# Create SQLAlchemy engine
def get_engine():
    conn_string = f"postgresql://{DB_PARAMS['user']}:{DB_PARAMS['password']}@{DB_PARAMS['host']}:{DB_PARAMS['port']}/{DB_PARAMS['dbname']}"
    return create_engine(conn_string)

def get_jobs():
    engine = get_engine()
    df = pd.read_sql_table("jobs", con=engine)

def submissions():
    engine = get_engine()
    df = pd.read_sql_table("candidate_submissions", con=engine)