from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import sessionmaker, declarative_base
from sqlalchemy import Column, Integer, String, Text, DateTime, Float, Boolean
from sqlalchemy.dialects.mysql import JSON
from sqlalchemy.sql import func
from dotenv import load_dotenv
import os

# Загрузка переменных окружения
load_dotenv()

DATABASE_URL = os.getenv('DATABASE_URL')  # Убедитесь, что URL настроен на асинхронный движок
# Например: "mysql+aiomysql://user:password@host:port/database"

# Создание базы данных
Base = declarative_base()
engine = create_async_engine(DATABASE_URL, echo=True)
async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

# Profile model
class Profile(Base):
    __tablename__ = "profile"

    id = Column(Integer, primary_key=True, index=True)
    link = Column(String(255), unique=True, nullable=True)
    first_name = Column(String(255), nullable=False)
    last_name = Column(String(255), nullable=False)
    pronouns = Column(String(50), nullable=True)
    email = Column(String(255), nullable=True)
    
    telephone = Column(String(20), nullable=True)
    location = Column(String(255), nullable=True)
    statement = Column(Text, nullable=True)
    skills = Column(JSON, nullable=True)  # List of skills
    websites = Column(JSON, nullable=True)  # List of website links
    languages = Column(JSON, nullable=True)  # List of languages with proficiency
    date = Column(DateTime, server_default=func.now())

# Jobs model
class Job(Base):
    __tablename__ = "jobs"

    job_id = Column(Integer, primary_key=True, index=True)
    job_title = Column(String(255), nullable=False)
    job_company = Column(String(255), nullable=False)
    minimum_annual_salary = Column(Float, nullable=True)
    salary_currency = Column(String(10), nullable=True)
    location_type = Column(String(50), nullable=False)  # Remote, On-site, Hybrid
    location = Column(String(255), nullable=False)
    job_level = Column(String(50), nullable=False)  # Entry-level, Junior, Mid, etc.
    job_type = Column(String(50), nullable=False)  # Full-time, Part-time, etc.
    days_posted = Column(Integer, nullable=False)
    hourly_rate = Column(Float, nullable=True)
    link = Column(String(2083), nullable=False)  # URL for the job posting
    link_company = Column(String(2083), nullable=False)  # URL for the job posting
    active = Column(Boolean, default=True)  # Indicates if the job is active

# Create database tables
async def init_db():
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)

# Example for dependency injection in FastAPI
async def get_session():
    async with async_session() as session:
        yield session