import os
from dotenv import load_dotenv
from sqlalchemy import create_engine, Column, Integer, String, Boolean, JSON
from sqlalchemy.orm import sessionmaker, declarative_base

load_dotenv()

SUPABASE_URL = os.environ["SUPABASE_URL"]

if SUPABASE_URL.startswith("postgres://"):
    SUPABASE_URL = SUPABASE_URL.replace("postgres://", "postgresql://", 1)

engine = create_engine(SUPABASE_URL, pool_pre_ping=True)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()


# --- THE USER DATABASE MODEL ---
class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True, index=True)
    hashed_password = Column(String, nullable=True)
    is_admin = Column(Boolean, default=False)

    # User FPL State
    default_team_id = Column(Integer, nullable=True)
    saved_edits = Column(JSON, default={})
    drafts = Column(JSON, default=[])
    solver_settings = Column(JSON, default={"quick": {}, "advanced": {}})


# --- THE NEW JSON VAULT ---
class GlobalConfig(Base):
    __tablename__ = "global_config"
    key = Column(String, primary_key=True, index=True)
    value = Column(JSON, default={})


# Create the tables in the Supabase database
Base.metadata.create_all(bind=engine)


# Dependency to get the DB session in our API routes
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()