import aiosqlite
import os
from pathlib import Path

DATABASE_PATH = Path(__file__).parent.parent / "database" / "submission.db"
DATABASE_PATH.parent.mkdir(exist_ok=True)


async def get_db():
    """获取数据库连接"""
    db = await aiosqlite.connect(str(DATABASE_PATH))
    db.row_factory = aiosqlite.Row
    return db


async def init_db():
    """初始化数据库"""
    db = await aiosqlite.connect(str(DATABASE_PATH))
    with open(Path(__file__).parent / "schema.sql", "r") as f:
        await db.executescript(f.read())
    await db.commit()
    await db.close()


async def get_or_create_team(db, team_name: str) -> int:
    """获取或创建队伍，返回 team_id"""
    cursor = await db.execute(
        "SELECT id FROM teams WHERE name = ?", (team_name,)
    )
    row = await cursor.fetchone()
    if row:
        return row["id"]
    await db.execute(
        "INSERT INTO teams (name) VALUES (?)", (team_name,)
    )
    await db.commit()
    cursor = await db.execute(
        "SELECT id FROM teams WHERE name = ?", (team_name,)
    )
    row = await cursor.fetchone()
    return row["id"]


async def check_daily_limit(db, team_id: int) -> tuple[bool, int]:
    """
    检查每日限制，返回 (是否超限, 当日提交数)
    """
    today = date.today().isoformat()
    cursor = await db.execute(
        "SELECT count FROM daily_limits WHERE team_id = ? AND date = ?",
        (team_id, today)
    )
    row = await cursor.fetchone()
    if row:
        return row["count"] >= 1, row["count"]
    return False, 0


async def increment_daily_limit(db, team_id: int):
    """增加当日提交计数"""
    today = date.today().isoformat()
    await db.execute(
        """INSERT INTO daily_limits (team_id, date, count)
           VALUES (?, ?, 1)
           ON CONFLICT(team_id, date) DO UPDATE SET count = count + 1""",
        (team_id, today)
    )
    await db.commit()


async def create_submission(db, team_id: int, file_path: str) -> int:
    """创建提交记录，返回 submission_id"""
    cursor = await db.execute(
        """INSERT INTO submissions (team_id, file_path, status)
           VALUES (?, ?, 'pending')""",
        (team_id, file_path)
    )
    await db.commit()
    return cursor.lastrowid


async def get_submission(submission_id: int):
    """获取单个提交记录"""
    db = await get_db()
    cursor = await db.execute(
        """SELECT s.*, t.name as team_name
           FROM submissions s
           JOIN teams t ON s.team_id = t.id
           WHERE s.id = ?""",
        (submission_id,)
    )
    row = await cursor.fetchone()
    await db.close()
    return dict(row) if row else None


async def update_submission_status(
    submission_id: int,
    status: str,
    score: float = None,
    error_message: str = None
):
    """更新提交状态"""
    db = await get_db()
    if score is not None:
        await db.execute(
            "UPDATE submissions SET status = ?, score = ?, error_message = ? WHERE id = ?",
            (status, score, error_message, submission_id)
        )
    else:
        await db.execute(
            "UPDATE submissions SET status = ?, error_message = ? WHERE id = ?",
            (status, error_message, submission_id)
        )
    await db.commit()
    await db.close()


async def get_oldest_pending_submission():
    """获取最老的 pending 提交"""
    db = await get_db()
    cursor = await db.execute(
        """SELECT s.*, t.name as team_name
           FROM submissions s
           JOIN teams t ON s.team_id = t.id
           WHERE s.status = 'pending'
           ORDER BY s.submit_time ASC
           LIMIT 1"""
    )
    row = await cursor.fetchone()
    await db.close()
    return dict(row) if row else None


async def get_team_submissions(team_id: int):
    """获取队伍的所有提交记录"""
    db = await get_db()
    cursor = await db.execute(
        """SELECT s.*, t.name as team_name
           FROM submissions s
           JOIN teams t ON s.team_id = t.id
           WHERE s.team_id = ?
           ORDER BY s.submit_time DESC""",
        (team_id,)
    )
    rows = await cursor.fetchall()
    await db.close()
    return [dict(row) for row in rows]


async def get_leaderboard():
    """获取排行榜数据（每队最优成绩）"""
    db = await get_db()
    cursor = await db.execute(
        """SELECT t.name as team_name,
                  MAX(s.score) as best_score,
                  MAX(s.submit_time) as last_submit_time
           FROM teams t
           LEFT JOIN submissions s ON t.id = s.team_id AND s.status = 'completed'
           GROUP BY t.id
           ORDER BY best_score DESC NULLS LAST,
                    last_submit_time ASC"""
    )
    rows = await cursor.fetchall()
    await db.close()
    return [dict(row) for row in rows]


async def get_team_by_name(team_name: str):
    """根据队名获取队伍信息"""
    db = await get_db()
    cursor = await db.execute(
        "SELECT * FROM teams WHERE name = ?", (team_name,)
    )
    row = await cursor.fetchone()
    await db.close()
    return dict(row) if row else None


from datetime import date