from sqlalchemy import create_engine, Column, Integer, String, Float, DateTime
from sqlalchemy.orm import declarative_base, sessionmaker
from datetime import datetime
import os
from dotenv import load_dotenv
from urllib.parse import quote_plus

# Load environment variables
load_dotenv()

# Get database credentials from environment variables
DB_HOST = os.getenv('DB_HOST', '13.210.75.82')
DB_PORT = os.getenv('DB_PORT', '3306')
DB_NAME = os.getenv('DB_NAME', 'betting_prizes')
DB_USER = os.getenv('DB_USER', 'patman')
DB_PASSWORD = quote_plus(os.getenv('DB_PASSWORD', 'UPCb024@'))

# Construct database URL
DATABASE_URL = f"mysql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
print("DATABASE_URL:", DATABASE_URL)

Base = declarative_base()
engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(bind=engine)

# Test connection
if __name__ == "__main__":
    try:
        with engine.connect() as connection:
            print("Database connection successful!")
    except Exception as e:
        print(f"Database connection failed: {e}")

class TournamentDeco(Base):
    __tablename__ = 'TournamentDeco'
    Id = Column(Integer, primary_key=True)
    CompetitionId = Column(String(64), nullable=False)
    Sport = Column(String(64), nullable=False)
    InitialPool = Column(Float, nullable=False)
    TicketPrice = Column(Float, nullable=False)
    TicketsSold = Column(Integer, nullable=False)
    PrizeCount = Column(Integer, nullable=False)
    CreatedAt = Column(DateTime, default=datetime.utcnow)
    UpdatedAt = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

class PayoutDeco(Base):
    __tablename__ = 'PayoutDeco'
    Id = Column(Integer, primary_key=True)
    CompetitionId = Column(String(64), nullable=False)
    Rank = Column(Integer, nullable=False)
    Amount = Column(Float, nullable=False)
    Tier = Column(String(32))
    Sport = Column(String(64), nullable=False)
    CreatedAt = Column(DateTime, default=datetime.utcnow)
    UpdatedAt = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)