import json
import sqlite3
import time

from web.config import Config


class RosterVersionService:
    @staticmethod
    def _connect_web(web_db_path=None):
        db = sqlite3.connect(web_db_path or Config.DB_WEB_PATH, timeout=30)
        db.row_factory = sqlite3.Row
        db.execute('PRAGMA foreign_keys = ON')
        return db

    @staticmethod
    def _normalize_ids(player_ids):
        seen = set()
        result = []
        for value in player_ids or []:
            steam_id = str(value).strip()
            if steam_id and steam_id not in seen:
                seen.add(steam_id)
                result.append(steam_id)
        return result

    @staticmethod
    def ensure_initial_version(web_db_path=None, l2_db_path=None):
        web = RosterVersionService._connect_web(web_db_path)
        try:
            current = web.execute(
                """
                SELECT id
                FROM team_roster_versions
                WHERE is_current = 1
                LIMIT 1
                """
            ).fetchone()
            if current:
                return int(current['id'])

            lineup = web.execute(
                """
                SELECT player_ids_json
                FROM team_lineups
                WHERE is_active = 1
                ORDER BY created_at DESC, id DESC
                LIMIT 1
                """
            ).fetchone()
            if not lineup:
                return None
            try:
                player_ids = RosterVersionService._normalize_ids(
                    json.loads(lineup['player_ids_json'] or '[]')
                )
            except (TypeError, json.JSONDecodeError):
                return None
            if not player_ids:
                return None

            effective_from = int(time.time())
            l2_path = l2_db_path or Config.DB_L2_PATH
            l2 = sqlite3.connect(l2_path)
            try:
                placeholders = ','.join('?' for _ in player_ids)
                row = l2.execute(
                    f"""
                    SELECT MIN(m.start_time)
                    FROM fact_match_players p
                    JOIN fact_matches m ON m.match_id = p.match_id
                    WHERE p.steam_id_64 IN ({placeholders})
                    """,
                    player_ids,
                ).fetchone()
                if row and row[0]:
                    effective_from = int(row[0])
            finally:
                l2.close()

            cursor = web.execute(
                """
                INSERT INTO team_roster_versions (
                    name, effective_from, is_current, notes
                ) VALUES (?, ?, 1, ?)
                """,
                ['2.0.0 Beta Initial Roster', effective_from, 'Bootstrapped from active lineup'],
            )
            version_id = int(cursor.lastrowid)
            web.executemany(
                """
                INSERT INTO team_roster_members (
                    roster_version_id, steam_id_64, member_role, position_order
                ) VALUES (?, ?, 'member', ?)
                """,
                [
                    (version_id, steam_id, index)
                    for index, steam_id in enumerate(player_ids)
                ],
            )
            web.commit()
            return version_id
        finally:
            web.close()

    @staticmethod
    def snapshot_roster(
        player_ids,
        name=None,
        effective_from=None,
        roles=None,
        web_db_path=None,
    ):
        normalized_ids = RosterVersionService._normalize_ids(player_ids)
        if not normalized_ids:
            raise ValueError('Roster version requires at least one player')

        roles = roles or {}
        effective_from = int(effective_from or time.time())
        web = RosterVersionService._connect_web(web_db_path)
        try:
            current = web.execute(
                """
                SELECT *
                FROM team_roster_versions
                WHERE is_current = 1
                LIMIT 1
                """
            ).fetchone()
            if current:
                current_ids = [
                    str(row[0]) for row in web.execute(
                        """
                        SELECT steam_id_64
                        FROM team_roster_members
                        WHERE roster_version_id = ?
                        ORDER BY position_order, steam_id_64
                        """,
                        [current['id']],
                    )
                ]
                if current_ids == normalized_ids:
                    return int(current['id']), False
                web.execute(
                    """
                    UPDATE team_roster_versions
                    SET is_current = 0, effective_to = ?
                    WHERE id = ?
                    """,
                    [effective_from - 1, current['id']],
                )

            cursor = web.execute(
                """
                INSERT INTO team_roster_versions (
                    name, effective_from, is_current
                ) VALUES (?, ?, 1)
                """,
                [
                    name or f'Roster {time.strftime("%Y-%m-%d")}',
                    effective_from,
                ],
            )
            version_id = int(cursor.lastrowid)
            values = []
            for index, steam_id in enumerate(normalized_ids):
                role = roles.get(steam_id, 'member')
                if role not in {'starter', 'substitute', 'member'}:
                    role = 'member'
                values.append((version_id, steam_id, role, index))
            web.executemany(
                """
                INSERT INTO team_roster_members (
                    roster_version_id, steam_id_64, member_role, position_order
                ) VALUES (?, ?, ?, ?)
                """,
                values,
            )
            web.commit()
            return version_id, True
        except Exception:
            web.rollback()
            raise
        finally:
            web.close()

    @staticmethod
    def list_versions(web_db_path=None):
        web = RosterVersionService._connect_web(web_db_path)
        try:
            rows = web.execute(
                """
                SELECT
                    version.*,
                    COUNT(member.steam_id_64) AS member_count
                FROM team_roster_versions version
                LEFT JOIN team_roster_members member
                  ON member.roster_version_id = version.id
                GROUP BY version.id
                ORDER BY version.effective_from DESC, version.id DESC
                """
            ).fetchall()
            return [dict(row) for row in rows]
        finally:
            web.close()

    @staticmethod
    def get_current_members(web_db_path=None):
        web = RosterVersionService._connect_web(web_db_path)
        try:
            rows = web.execute(
                """
                SELECT member.*, version.name AS version_name
                FROM team_roster_members member
                JOIN team_roster_versions version
                  ON version.id = member.roster_version_id
                WHERE version.is_current = 1
                ORDER BY member.position_order, member.steam_id_64
                """
            ).fetchall()
            return [dict(row) for row in rows]
        finally:
            web.close()

    @staticmethod
    def update_current_member_role(steam_id, member_role, web_db_path=None):
        if member_role not in {'starter', 'substitute', 'member'}:
            raise ValueError('Invalid roster member role')
        web = RosterVersionService._connect_web(web_db_path)
        try:
            cursor = web.execute(
                """
                UPDATE team_roster_members
                SET member_role = ?
                WHERE steam_id_64 = ?
                  AND roster_version_id = (
                      SELECT id
                      FROM team_roster_versions
                      WHERE is_current = 1
                      LIMIT 1
                  )
                """,
                [member_role, str(steam_id)],
            )
            if cursor.rowcount != 1:
                raise ValueError('Player is not in the current roster version')
            web.commit()
        finally:
            web.close()