database/connection.py - master - Shtirlitc/TickBot

database/connection.py
253 строки9 KB
Shtirlyts
Initial commit: TickBot attendance bot with stale-status fixes
31 июл 2026, 23:40
31 июл 2026, 23:4030e955c
# database/connection.py
from asyncio.log import logger

import aiosqlite
from pathlib import Path
from config import settings

DB_PATH = Path(settings.database_path)


async def get_db() -> aiosqlite.Connection:
    """Получает подключение к БД"""
    db = await aiosqlite.connect(DB_PATH)
    db.row_factory = aiosqlite.Row
    return db


async def init_db():
    """Инициализирует таблицы БД"""
    async with aiosqlite.connect(DB_PATH) as db:
        # ===========================
        # ПОЛЬЗОВАТЕЛИ
        # ===========================
        await db.execute("""
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                telegram_id BIGINT UNIQUE NOT NULL,
                username VARCHAR(100),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        
        # ===========================
        # СТУДЕНТЫ
        # ===========================
        await db.execute("""
            CREATE TABLE IF NOT EXISTS students (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                telegram_id BIGINT NOT NULL,
                fio VARCHAR(255) NOT NULL,
                student_number VARCHAR(50),
                subgroup INTEGER,
                group_name VARCHAR(50),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (telegram_id) REFERENCES users(telegram_id)
            )
        """)
        
        # ===========================
        # РАСПИСАНИЕ
        # ===========================
        await db.execute("""
            CREATE TABLE IF NOT EXISTS schedule (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                group_name VARCHAR(50) NOT NULL,
                subgroup INTEGER NOT NULL,
                week_string VARCHAR(100) NOT NULL,
                day VARCHAR(50) NOT NULL,
                subject VARCHAR(255) NOT NULL,
                row_num INTEGER,
                is_cancelled BOOLEAN DEFAULT FALSE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(group_name, subgroup, week_string, day, subject)
            )
        """)
        
        # ===========================
        # ПОСЕЩАЕМОСТЬ
        # ===========================
        await db.execute("""
        CREATE TABLE IF NOT EXISTS attendance (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            student_id INTEGER NOT NULL,
            group_name VARCHAR(50) NOT NULL,
            subgroup INTEGER NOT NULL,
            week_string VARCHAR(100) NOT NULL,
            day VARCHAR(50) NOT NULL,
            subject VARCHAR(255) NOT NULL,
            row_num INTEGER NOT NULL,  -- ← ВАЖНО: NOT NULL
            status VARCHAR(10) NOT NULL,
            synced_to_gs BOOLEAN DEFAULT FALSE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (student_id) REFERENCES students(id),
            UNIQUE(student_id, week_string, day, row_num)
        )
    """)
        
        # ===========================
        # ЧЁРНЫЙ СПИСОК
        # ===========================
        await db.execute("""
            CREATE TABLE IF NOT EXISTS blacklist (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                telegram_id BIGINT UNIQUE NOT NULL,
                reason TEXT,
                added_by BIGINT,
                added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        
        # ===========================
        # УВЕДОМЛЕНИЯ
        # ===========================
        await db.execute("""
            CREATE TABLE IF NOT EXISTS notifications (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                telegram_id BIGINT UNIQUE NOT NULL,
                enabled BOOLEAN DEFAULT FALSE,
                days TEXT,
                time VARCHAR(5)
            )
        """)
        
        # ===========================
        # СТАРОСТЫ
        # ===========================
        await db.execute("""
            CREATE TABLE IF NOT EXISTS wardens (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                telegram_id BIGINT NOT NULL,
                group_name VARCHAR(50) NOT NULL,
                added_by BIGINT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(telegram_id, group_name)
            )
        """)
        
        # ===========================
        # ЛОГ СИНХРОНИЗАЦИИ
        # ===========================
        await db.execute("""
            CREATE TABLE IF NOT EXISTS gs_sync_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                sync_type VARCHAR(50) NOT NULL,
                status VARCHAR(20) NOT NULL,
                records_synced INTEGER DEFAULT 0,
                last_sync TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                error TEXT
            )
        """)
        
        await db.execute("""
            CREATE UNIQUE INDEX IF NOT EXISTS uix_attendance_unique_key 
            ON attendance (student_id, week_string, day, row_num)
        """)
        await db.commit()
        
        # ===========================
        # ИНДЕКСЫ
        # ===========================
        await db.execute("""
            CREATE INDEX IF NOT EXISTS idx_students_telegram 
            ON students(telegram_id)
        """)
        
        await db.execute("""
            CREATE INDEX IF NOT EXISTS idx_attendance_student_week 
            ON attendance(student_id, week_string)
        """)
        
        await db.execute("""
            CREATE INDEX IF NOT EXISTS idx_attendance_synced 
            ON attendance(synced_to_gs)
        """)
        
        await db.execute("""
            CREATE INDEX IF NOT EXISTS idx_schedule_group_week 
            ON schedule(group_name, week_string)
        """)
        
        # ===========================
        # ИСПРАВЛЕНИЕ: Добавляем колонку synced_to_gs если её нет
        # ===========================
        try:
            await db.execute("""
                ALTER TABLE attendance ADD COLUMN synced_to_gs BOOLEAN DEFAULT FALSE
            """)
            await db.commit()
            print("✅ Добавлена колонка synced_to_gs в таблицу attendance")
        except aiosqlite.OperationalError as e:
            if "duplicate column" in str(e).lower():
                pass  # Колонка уже есть
            else:
                raise
        
        await db.commit()
        
        print("✅ Все таблицы SQLite созданы/обновлены")


# ============================================================
# МИГРАЦИЯ СТУДЕНТОВ
# ============================================================
async def migrate_students_from_gs(gs_client, group_name: str) -> int:
    from database.repositories import StudentRepository
    student_repo = StudentRepository()
    migrated_count = 0

    try:
        # Теперь вызываем синхронный метод
        students_data = gs_client.get_students_data(group_name)

        for student in students_data:
            fio = student.get('ФИО', '').strip()
            student_number = str(student.get('№', '')).strip()
            subgroup = int(student.get('Подгруппа', 1))
            telegram_id_str = student.get('Telegram ID', '').strip()

            if not fio or not student_number:
                continue

            if not telegram_id_str or not telegram_id_str.isdigit():
                continue

            telegram_id = int(telegram_id_str)

            existing = await student_repo.get_by_telegram_id(telegram_id)
            if existing:
                if existing.fio != fio or existing.group_name != group_name:
                    existing.fio = fio
                    existing.group_name = group_name
                    existing.subgroup = subgroup
                    await student_repo.update(existing)
                continue

            await student_repo.create(
                telegram_id=telegram_id,
                fio=fio,
                student_number=student_number,
                subgroup=subgroup,
                group_name=group_name
            )
            migrated_count += 1

        logger.info(f"✅ Мигрировано {migrated_count} студентов из группы {group_name}")

    except Exception as e:
        logger.error(f"❌ Ошибка миграции студентов из {group_name}: {e}")

    return migrated_count

async def migrate_all_students_from_gs(gs_client) -> int:
    """Миграция всех студентов из всех групп"""
    from config import GROUPS
    
    total_migrated = 0
    
    for group_name in GROUPS.keys():
        count = await migrate_students_from_gs(gs_client, group_name)
        total_migrated += count
    
    print(f"🎉 Всего мигрировано: {total_migrated} студентов")
    return total_migrated