from db import get_connection
from repositories.team_repository import get_team_id_by_external_id


def normalize_birth_date_for_input(value) -> str:
    if not value:
        return ""

    text = str(value).strip()

    if not text:
        return ""

    # Уже нормальный формат для input[type=date]
    if len(text) == 10 and text[4] == "-" and text[7] == "-":
        return text

    # Формат ДД.ММ.ГГГГ -> YYYY-MM-DD
    if len(text) == 10 and text[2] == "." and text[5] == ".":
        dd, mm, yyyy = text.split(".")
        return f"{yyyy}-{mm}-{dd}"

    return ""


def upsert_player(
    external_id: str,
    team_external_id: str,
    player: str,
    lastname: str = "",
    name: str = "",
    number: str = "",
    pos: str = "",
    amplua: str = "",
    born: str = "",
    games: int = 0,
    goals: int = 0,
    penaltys: int = 0,
    assists: int = 0,
    yellows: int = 0,
    reds: int = 0,
    is_active: bool = True,
) -> None:
    team_id = get_team_id_by_external_id(str(team_external_id).strip())
    if team_id is None:
        raise ValueError(f"Team not found by external_id: {team_external_id}")

    query = """
    INSERT INTO players (
        external_id,
        team_id,
        full_name,
        first_name,
        last_name,
        number,
        position,
        is_active,
        pos,
        amplua,
        born,
        games,
        goals,
        penaltys,
        assists,
        yellows,
        reds,
        created_at,
        updated_at
    )
    VALUES (
        %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
        NOW(), NOW()
    )
    ON CONFLICT (external_id)
    DO UPDATE SET
        team_id = EXCLUDED.team_id,
        full_name = EXCLUDED.full_name,
        first_name = EXCLUDED.first_name,
        last_name = EXCLUDED.last_name,
        number = EXCLUDED.number,
        position = EXCLUDED.position,
        is_active = EXCLUDED.is_active,
        pos = EXCLUDED.pos,
        amplua = EXCLUDED.amplua,
        born = EXCLUDED.born,
        games = EXCLUDED.games,
        goals = EXCLUDED.goals,
        penaltys = EXCLUDED.penaltys,
        assists = EXCLUDED.assists,
        yellows = EXCLUDED.yellows,
        reds = EXCLUDED.reds,
        updated_at = NOW();
    """

    conn = get_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                query,
                (
                    str(external_id).strip(),
                    team_id,
                    player.strip(),
                    name.strip(),
                    lastname.strip(),
                    str(number).strip(),
                    amplua.strip(),
                    is_active,
                    pos.strip(),
                    amplua.strip(),
                    born.strip(),
                    games,
                    goals,
                    penaltys,
                    assists,
                    yellows,
                    reds,
                ),
            )
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def get_match_by_external_id(external_id: str):
    query = """
    SELECT id, external_id, home_team_id, away_team_id
    FROM matches
    WHERE external_id = %s
    LIMIT 1;
    """

    conn = get_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(query, (str(external_id).strip(),))
            return cur.fetchone()
    finally:
        conn.close()


def mark_match_parsed(external_id: str) -> None:
    query = """
    UPDATE matches
    SET parsed = TRUE,
        parse_error = NULL,
        updated_at = NOW()
    WHERE external_id = %s;
    """

    conn = get_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(query, (str(external_id).strip(),))
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def mark_match_parse_error(external_id: str, error_text: str) -> None:
    query = """
    UPDATE matches
    SET parsed = FALSE,
        parse_error = %s,
        updated_at = NOW()
    WHERE external_id = %s;
    """

    conn = get_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(query, (str(error_text)[:2000], str(external_id).strip()))
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def get_player_id_by_name_and_team(full_name: str, team_id: int) -> int | None:
    query = """
    SELECT id
    FROM players
    WHERE team_id = %s
      AND LOWER(full_name) = LOWER(%s)
    LIMIT 1;
    """

    conn = get_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(query, (team_id, full_name.strip()))
            row = cur.fetchone()
            return row[0] if row else None
    finally:
        conn.close()


def get_player_id_by_external_id(external_id: str) -> int | None:
    query = """
    SELECT id,
    position
    FROM players
    WHERE external_id = %s
    LIMIT 1;
    """

    conn = get_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(query, (str(external_id).strip(),))
            row = cur.fetchone()
            return row if row else None
    finally:
        conn.close()



def _split_full_name_for_autocreate(full_name: str) -> tuple[str, str]:
    """Аккуратно делит имя с сайта на first_name / last_name для новых записей."""
    parts = [p.strip() for p in str(full_name or "").replace("\xa0", " ").split() if p.strip()]
    if not parts:
        return "", ""
    if len(parts) == 1:
        return "", parts[0]

    # На сайте чаще приходит "Имя Фамилия". Полное имя всё равно сохраняем отдельно,
    # поэтому даже при другом порядке данные можно быстро поправить в справочнике.
    first_name = parts[0]
    last_name = " ".join(parts[1:])
    return first_name, last_name


def create_player_from_lineup(
    team_id: int,
    external_id: str = "",
    full_name: str = "",
    number: str = "",
    position: str = "",
) -> tuple[int | None, str | None]:
    """Создаёт минимальную карточку игрока из протокола матча и возвращает (id, position).

    Используется при загрузке составов с сайта, когда игрока ещё нет в справочнике.
    """
    full_name = str(full_name or "").strip()
    if not full_name:
        return None, None

    first_name, last_name = _split_full_name_for_autocreate(full_name)
    external_id = str(external_id or "").strip()
    number = str(number or "").strip()
    position = str(position or "").strip()

    query = """
    INSERT INTO players (
        external_id,
        team_id,
        full_name,
        first_name,
        last_name,
        number,
        position,
        pos,
        amplua,
        is_active,
        photo_enabled,
        created_at,
        updated_at
    )
    VALUES (
        NULLIF(%s, ''), %s, %s, %s, %s, %s, %s, %s, %s, TRUE, FALSE,
        NOW(), NOW()
    )
    ON CONFLICT (external_id)
    DO UPDATE SET
        team_id = EXCLUDED.team_id,
        full_name = COALESCE(NULLIF(EXCLUDED.full_name, ''), players.full_name),
        first_name = COALESCE(NULLIF(EXCLUDED.first_name, ''), players.first_name),
        last_name = COALESCE(NULLIF(EXCLUDED.last_name, ''), players.last_name),
        number = COALESCE(NULLIF(EXCLUDED.number, ''), players.number),
        position = COALESCE(NULLIF(EXCLUDED.position, ''), players.position),
        pos = COALESCE(NULLIF(EXCLUDED.pos, ''), players.pos),
        amplua = COALESCE(NULLIF(EXCLUDED.amplua, ''), players.amplua),
        is_active = TRUE,
        updated_at = NOW()
    RETURNING id, position;
    """

    conn = get_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                query,
                (
                    external_id,
                    team_id,
                    full_name,
                    first_name,
                    last_name,
                    number,
                    position,
                    position,
                    position,
                ),
            )
            row = cur.fetchone()
        conn.commit()
        return row if row else (None, None)
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


PLAYER_ADMIN_SORT_COLUMNS = {
    "id": "p.id",
    "full_name": "p.full_name",
    "first_name": "p.first_name",
    "last_name": "p.last_name",
    "external_id": "p.external_id",
    "position": "p.position",
    "team_name": "t.name",
    "photo": "p.photo_enabled",
}


def ensure_player_photo_enabled_column() -> None:
    """Добавляет флаг использования фото игрока, если база ещё не обновлена."""
    conn = get_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                ALTER TABLE players
                ADD COLUMN IF NOT EXISTS photo_enabled BOOLEAN NOT NULL DEFAULT FALSE;
                """
            )
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def _normalize_player_sort(sort_by: str = "id", sort_dir: str = "desc") -> tuple[str, str, str, str]:
    sort_by = (sort_by or "id").strip()
    if sort_by not in PLAYER_ADMIN_SORT_COLUMNS:
        sort_by = "id"

    sort_dir = (sort_dir or "desc").strip().lower()
    if sort_dir not in {"asc", "desc"}:
        sort_dir = "desc"

    return sort_by, sort_dir, PLAYER_ADMIN_SORT_COLUMNS[sort_by], sort_dir.upper()


def search_players_for_admin(q: str = "", sort_by: str = "id", sort_dir: str = "desc") -> list[dict]:
    sort_by, sort_dir, order_column, order_direction = _normalize_player_sort(sort_by, sort_dir)
    limit = 200

    conn = get_connection()
    try:
        with conn.cursor() as cur:
            params: list = []
            where_sql = ""

            if q.strip():
                pattern = f"%{q.strip()}%"
                where_sql = """
                    WHERE
                        p.full_name ILIKE %s
                        OR p.first_name ILIKE %s
                        OR p.last_name ILIKE %s
                        OR COALESCE(p.external_id, '') ILIKE %s
                        OR COALESCE(t.name, '') ILIKE %s
                """
                params.extend([pattern, pattern, pattern, pattern, pattern])

            params.append(limit)
            cur.execute(
                f"""
                SELECT
                    p.id,
                    p.full_name,
                    p.first_name,
                    p.last_name,
                    p.external_id,
                    p.position,
                    COALESCE(t.name, '') AS team_name,
                    COALESCE(p.photo_enabled, FALSE) AS photo_enabled
                FROM players p
                LEFT JOIN teams t ON t.id = p.team_id
                {where_sql}
                ORDER BY {order_column} {order_direction} NULLS LAST, p.id ASC
                LIMIT %s
                """,
                tuple(params),
            )

            rows = cur.fetchall()

        return [
            {
                "id": row[0],
                "full_name": row[1] or "",
                "first_name": row[2] or "",
                "last_name": row[3] or "",
                "external_id": row[4] or "",
                "position": row[5] or "",
                "team_name": row[6] or "",
                "photo_enabled": bool(row[7]),
            }
            for row in rows
        ]
    finally:
        conn.close()


def get_player_by_id(player_id: int) -> dict | None:
    conn = get_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                SELECT
                    p.id,
                    p.full_name,
                    p.first_name,
                    p.last_name,
                    p.external_id,
                    p.position,
                    p.born,
                    p.photo,
                    p.video,
                    COALESCE(t.name, '') AS team_name,
                    COALESCE(p.photo_enabled, FALSE) AS photo_enabled
                FROM players p
                LEFT JOIN teams t ON t.id = p.team_id
                WHERE p.id = %s
                LIMIT 1
                """,
                (player_id,),
            )
            row = cur.fetchone()

        if not row:
            return None

        return {
            "id": row[0],
            "full_name": row[1] or "",
            "first_name": row[2] or "",
            "last_name": row[3] or "",
            "external_id": row[4] or "",
            "position": row[5] or "",
            "birth_date": normalize_birth_date_for_input(row[6]),
            "photo": row[7] or "",
            "video": row[8] or "",
            "team_name": row[9] or "",
            "photo_enabled": bool(row[10]),
        }
    finally:
        conn.close()


def update_player_admin(
    player_id: int,
    full_name: str = "",
    first_name: str = "",
    last_name: str = "",
    external_id: str = "",
    position: str = "",
    birth_date: str = "",
    photo: str = "",
    video: str = "",
    photo_enabled: bool = False,
) -> None:
    conn = get_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                UPDATE players
                SET
                    full_name = %s,
                    first_name = %s,
                    last_name = %s,
                    external_id = NULLIF(%s, ''),
                    position = %s,
                    born = NULLIF(%s, '')::date,
                    photo = NULLIF(%s, ''),
                    video = NULLIF(%s, ''),
                    photo_enabled = %s,
                    updated_at = NOW()
                WHERE id = %s
                """,
                (
                    full_name.strip(),
                    first_name.strip(),
                    last_name.strip(),
                    external_id.strip(),
                    position.strip(),
                    birth_date.strip(),
                    photo.strip(),
                    video.strip(),
                    bool(photo_enabled),
                    player_id,
                ),
            )
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def update_player_photo_enabled(player_id: int, photo_enabled: bool = False) -> None:
    conn = get_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                UPDATE players
                SET
                    photo_enabled = %s,
                    updated_at = NOW()
                WHERE id = %s
                """,
                (bool(photo_enabled), player_id),
            )
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def create_player_admin(
    team_id: int,
    full_name: str,
    first_name: str = "",
    last_name: str = "",
    external_id: str = "",
    number: str = "",
    position: str = "",
    birth_date: str = "",
    photo: str = "",
    video: str = "",
    height_cm: int | None = None,
    weight_kg: int | None = None,
    games: int = 0,
    goals: int = 0,
    penaltys: int = 0,
    assists: int = 0,
    yellows: int = 0,
    reds: int = 0,
    is_active: bool = True,
    photo_enabled: bool = False,
) -> int:
    """Создаёт игрока вручную из административного раздела."""
    query = """
    INSERT INTO players (
        external_id,
        team_id,
        full_name,
        first_name,
        last_name,
        number,
        position,
        pos,
        amplua,
        born,
        photo,
        video,
        height_cm,
        weight_kg,
        games,
        goals,
        penaltys,
        assists,
        yellows,
        reds,
        is_active,
        photo_enabled,
        created_at,
        updated_at
    )
    VALUES (
        NULLIF(%s, ''), %s, %s, %s, %s, NULLIF(%s, ''), %s, %s, %s,
        NULLIF(%s, '')::date, NULLIF(%s, ''), NULLIF(%s, ''), %s, %s,
        %s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW()
    )
    RETURNING id;
    """

    conn = get_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                query,
                (
                    external_id.strip(),
                    int(team_id),
                    full_name.strip(),
                    first_name.strip(),
                    last_name.strip(),
                    number.strip(),
                    position.strip(),
                    position.strip(),
                    position.strip(),
                    birth_date.strip(),
                    photo.strip(),
                    video.strip(),
                    height_cm,
                    weight_kg,
                    int(games),
                    int(goals),
                    int(penaltys),
                    int(assists),
                    int(yellows),
                    int(reds),
                    bool(is_active),
                    bool(photo_enabled),
                ),
            )
            row = cur.fetchone()
        conn.commit()
        return int(row[0])
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()