import aiosqlite

class Database:
    def __init__(self, db_path: str):
        self.db_path = db_path

    async def create_tables(self):
        async with aiosqlite.connect(self.db_path) as db:
            await db.execute("""
                CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY,
                    user_id INTEGER UNIQUE,
                    sphere TEXT,
                    language TEXT,
                    preferences TEXT
                )
            """)
            await db.execute("""
            CREATE TABLE IF NOT EXISTS sent_vacancies (
                user_id INTEGER,
                vacancy_url TEXT,
                PRIMARY KEY (user_id, vacancy_url)
            )
        """)
            await db.commit()

    async def is_vacancy_sent(self, user_id: int, url: str) -> bool:
        async with aiosqlite.connect(self.db_path) as db:
            async with db.execute(
                "SELECT 1 FROM sent_vacancies WHERE user_id = ? AND vacancy_url = ?", 
                (user_id, url)
            ) as cursor:
                return await cursor.fetchone() is not None

    async def mark_vacancy_as_sent(self, user_id: int, url: str):
        async with aiosqlite.connect(self.db_path) as db:
            await db.execute(
                "INSERT OR IGNORE INTO sent_vacancies (user_id, vacancy_url) VALUES (?, ?)",
                (user_id, url)
            )
            await db.commit()

    async def add_user(self, user_id: int):
        async with aiosqlite.connect(self.db_path) as db:
            await db.execute(
                "INSERT OR IGNORE INTO users (user_id) VALUES (?)",
                (user_id,)
            )
            await db.commit()

    async def update_user_data(self, user_id: int, data: dict):
        async with aiosqlite.connect(self.db_path) as db:
            await db.execute(
                "UPDATE users SET sphere = ?, language = ?, preferences = ? WHERE user_id = ?",
                (data.get('sphere'), data.get('language'), data.get('preferences'), user_id)
            )
            await db.commit()

    async def get_all(self):
        async with aiosqlite.connect(self.db_path) as db:
            async with db.execute("SELECT * FROM users") as cursor:
                return await cursor.fetchall()
    async def get_user(self, user_id: int):
        async with aiosqlite.connect(self.db_path) as db:
            async with db.execute(
                "SELECT sphere, language, preferences FROM users WHERE user_id = ?", 
                (user_id,)
            ) as cursor:
                return await cursor.fetchone()

    async def count_users(self) -> int:
        #Возвращает количество зарегистрированных пользователей
        async with aiosqlite.connect(self.db_path) as db:
            async with db.execute("SELECT COUNT(*) FROM users") as cursor:
                result = await cursor.fetchone()
                return result[0] if result else 0
    async def clear_sent_vacancies(self, user_id: int):
            #Очищает историю отправленных вакансий для пользователя
            async with aiosqlite.connect(self.db_path) as db:
                await db.execute(
                        "DELETE FROM sent_vacancies WHERE user_id = ?",
                    (user_id,)
                )
                await db.commit()