#!/usr/bin/env python3
# -*- coding: utf-8 -*-

"""
Работа с capture_sessions.

Модуль предоставляет функции для получения
информации о поездках (захватах).

Используется:

    dashboard.py

    session_page.py

    generator.py
"""

from pathlib import Path

from .config import (
    CAPTURES_DIR,
)

from .database import (
    query_all,
    query_one,
    query_value,
)

from datetime import datetime

from .utils import (
    format_duration,
    format_size,
)


# ============================================================
# Список всех поездок
# ============================================================

def get_sessions(conn):

    sql = """

        SELECT

            id,

            start_time,

            end_time,

            capture_file

        FROM capture_sessions

        ORDER BY

            start_time DESC

    """

    return query_all(
        conn,
        sql
    )


# ============================================================
# Получить одну поездку
# ============================================================

def get_session(
    conn,
    session_id
):

    sql = """

        SELECT

            id,

            start_time,

            end_time,

            capture_file

        FROM capture_sessions

        WHERE id=?

    """

    return query_one(
        conn,
        sql,
        (
            session_id,
        )
    )


# ============================================================
# Продолжительность
# ============================================================

def session_duration(
    conn,
    session_id
):

    row = query_one(
        conn,
        """

        SELECT

            start_time,

            end_time

        FROM capture_sessions

        WHERE id=?

        """,
        (
            session_id,
        )
    )

    if row is None:

        return None

    start = row[0]

    end = row[1]

    return format_duration(
        start,
        end
    )

# ============================================================
# Реальное время поездки (GPS)
# ============================================================

def trip_time(
    conn,
    session_id,
):

    row = query_one(
        conn,
        """
        SELECT

            MIN(timestamp),
            MAX(timestamp)

        FROM gps_track

        WHERE session_id=?

        """,
        (
            session_id,
        )
    )

    if row is None:

        return (
            None,
            None,
        )

    return (
        row[0],
        row[1],
    )

# ============================================================
# Количество точек доступа в поездке
# ============================================================

def access_points_count(
    conn,
    session_id
):

    return query_value(
        conn,
        """
        SELECT COUNT(DISTINCT access_point_id)

        FROM access_point_observations

        WHERE session_id=?
        """,
        (
            session_id,
        ),
        default=0
    )


# ============================================================
# Handshake AP (уникальные точки)
# ============================================================

def handshakes_count(
    conn,
    session_id
):

    return query_value(
        conn,
        """
        SELECT COUNT(DISTINCT access_point_id)

        FROM handshakes

        WHERE session_id=?
        """,
        (
            session_id,
        ),
        default=0
    )


# ============================================================
# Handshake Records (все записи)
# ============================================================

def handshake_records_count(
    conn,
    session_id
):

    return query_value(
        conn,
        """
        SELECT COUNT(*)

        FROM handshakes

        WHERE session_id=?
        """,
        (
            session_id,
        ),
        default=0
    )

# ============================================================
# Найденные пароли
# ============================================================

def credentials_count(
    conn,
    session_id
):

    return query_value(
        conn,
        """
        SELECT COUNT(DISTINCT c.id)

        FROM credentials c

        JOIN access_points ap
            ON ap.id = c.access_point_id

        JOIN access_point_observations obs
            ON obs.access_point_id = ap.id

        WHERE obs.session_id = ?
        """,
        (
            session_id,
        ),
        default=0
    )


# ============================================================
# PMKID
# ============================================================

def pmkid_count(
    conn,
    session_id
):

    return query_value(
        conn,
        """
        SELECT COUNT(DISTINCT ap.id)

        FROM access_points ap

        JOIN access_point_observations obs
            ON obs.access_point_id = ap.id

        WHERE
            obs.session_id = ?
            AND ap.has_pmkid = 1
        """,
        (
            session_id,
        ),
        default=0
    )


# ============================================================
# Взломанные сети
# ============================================================

def cracked_count(
    conn,
    session_id
):

    return query_value(
        conn,
        """
        SELECT COUNT(DISTINCT ap.id)

        FROM access_points ap

        JOIN access_point_observations obs
            ON obs.access_point_id = ap.id

        WHERE
            obs.session_id = ?
            AND ap.is_cracked = 1
        """,
        (
            session_id,
        ),
        default=0
    )


def coordinates_restored_count(
    conn,
    session_id
):

    return query_value(
        conn,
        """
        SELECT COUNT(*)
        FROM
        (
            SELECT
                current_obs.access_point_id
            FROM access_point_observations AS current_obs
            WHERE
                current_obs.session_id = ?
                AND current_obs.latitude IS NOT NULL
                AND current_obs.longitude IS NOT NULL
                AND current_obs.latitude != 0
                AND current_obs.longitude != 0
                AND EXISTS
                (
                    SELECT 1
                    FROM access_point_observations AS previous_obs
                    WHERE
                        previous_obs.access_point_id =
                            current_obs.access_point_id
                        AND previous_obs.observed_at <
                            (
                                SELECT start_time
                                FROM capture_sessions
                                WHERE id=?
                            )
                )
                AND NOT EXISTS
                (
                    SELECT 1
                    FROM access_point_observations AS previous_coords
                    WHERE
                        previous_coords.access_point_id =
                            current_obs.access_point_id
                        AND previous_coords.observed_at <
                            (
                                SELECT start_time
                                FROM capture_sessions
                                WHERE id=?
                            )
                        AND previous_coords.latitude IS NOT NULL
                        AND previous_coords.longitude IS NOT NULL
                        AND previous_coords.latitude != 0
                        AND previous_coords.longitude != 0
                )
            GROUP BY current_obs.access_point_id
        )
        """,
        (
            session_id,
            session_id,
            session_id
        ),
        default=0
    )


def ap_without_coordinates_count(
    conn,
    session_id
):

    return query_value(
        conn,
        """
        SELECT COUNT(*)
        FROM
        (
            SELECT
                obs.access_point_id
            FROM access_point_observations AS obs
            WHERE
                obs.session_id = ?
            GROUP BY obs.access_point_id
            HAVING
                SUM(
                    CASE
                        WHEN obs.latitude IS NOT NULL
                        AND obs.longitude IS NOT NULL
                        AND obs.latitude != 0
                        AND obs.longitude != 0
                        THEN 1
                        ELSE 0
                    END
                ) = 0
        )
        """,
        (
            session_id,
        ),
        default=0
    )

# ============================================================
# Полная статистика поездки
# ============================================================

def session_statistics(
    conn,
    session_id
):

    row = get_session(
        conn,
        session_id
    )

    if row is None:

        return None

    (
        ident,
        start_time,
        end_time,
        capture_file,
    ) = row

    return {

        "id": ident,

        "start_time": start_time,

        "end_time": end_time,

        "duration": session_duration(
            conn,
            session_id
        ),

        "access_points": access_points_count(
            conn,
            session_id
        ),

        "coordinates_restored": coordinates_restored_count(
            conn,
            session_id
        ),

        "ap_without_coordinates": ap_without_coordinates_count(
            conn,
            session_id
        ),

        "handshakes": handshakes_count(
            conn,
            session_id
        ),

        "handshake_records": handshake_records_count(
            conn,
            session_id
        ),

        "credentials": credentials_count(
            conn,
            session_id
        ),

        "pmkid": pmkid_count(
            conn,
            session_id
        ),

        "cracked": cracked_count(
            conn,
            session_id
        ),

        "capture_file": capture_file,

    }

# ============================================================
# Каталог поездки
# ============================================================

def session_directory(capture_file):

    if capture_file is None:

        return None

    capture_path = Path(capture_file)

    if capture_path.parent.exists():

        return capture_path.parent

    return CAPTURES_DIR / capture_path.parent.name


# ============================================================
# Проверка существования файла
# ============================================================

def file_exists(path):

    if path is None:

        return False

    return Path(path).exists()


# ============================================================
# Получить все файлы поездки
# ============================================================

def session_files(
    conn,
    session_id
):

    row = get_session(
        conn,
        session_id
    )

    if row is None:

        return None

    directory = session_directory(
        row[3]
    )

    files = {

        "directory": directory,

        "capture": directory / "capture.pcapng",

        "gps_track": directory / "gps_track.nmea",

        "parser_log": directory / "parser.log",

        "access_points": directory / "access_points.csv",

        "handshakes": directory / "handshakes.22000",

        "essid": directory / "essid.txt",

    }

    return files


# ============================================================
# Файлы, реально существующие на диске
# ============================================================

def existing_files(
    conn,
    session_id
):

    files = session_files(
        conn,
        session_id
    )

    if files is None:

        return {}

    result = {}

    for name, path in files.items():

        if name == "directory":

            result[name] = path

            continue

        result[name] = {

            "path": path,

            "exists": file_exists(path),

            "size": path.stat().st_size if file_exists(path) else 0,

        }

    return result


# ============================================================
# Размер каталога поездки
# ============================================================

def session_directory_size(
    conn,
    session_id
):

    files = session_files(
        conn,
        session_id
    )

    if files is None:
        return 0

    directory = files["directory"]

    if not directory.exists():
        return 0

    total = 0

    for file in directory.iterdir():

        if file.is_file():
            total += file.stat().st_size

    return total


# ============================================================
# Последние поездки
# ============================================================

def recent_sessions(
    conn,
    limit=10
):

    sql = """

        SELECT

            id

        FROM capture_sessions

        ORDER BY

            start_time DESC

        LIMIT ?

    """

    rows = conn.execute(
        sql,
        (
            limit,
        )
    ).fetchall()

    result = []

    for row in rows:

        card = session_card_data(

            conn,

            row[0]

        )

        if card:

            result.append(card)

    return result

# ============================================================
# Самая длинная поездка
# ============================================================

def longest_session(conn):

    sql = """

        SELECT

            id,

            start_time,

            end_time

        FROM capture_sessions

        WHERE end_time IS NOT NULL

    """

    rows = conn.execute(sql).fetchall()

    winner = None

    seconds = -1

    for ident, start, end in rows:

        try:

            s = datetime.fromisoformat(start)

            e = datetime.fromisoformat(end)

        except Exception:

            continue

        diff = (e - s).total_seconds()

        if diff > seconds:

            seconds = diff

            winner = ident

    if winner is None:

        return None

    return session_statistics(
        conn,
        winner
    )


# ============================================================
# Самая короткая поездка
# ============================================================

def shortest_session(conn):

    sql = """

        SELECT

            id,

            start_time,

            end_time

        FROM capture_sessions

        WHERE end_time IS NOT NULL

    """

    rows = conn.execute(sql).fetchall()

    winner = None

    seconds = None

    for ident, start, end in rows:

        try:

            s = datetime.fromisoformat(start)

            e = datetime.fromisoformat(end)

        except Exception:

            continue

        diff = (e - s).total_seconds()

        if seconds is None or diff < seconds:

            seconds = diff

            winner = ident

    if winner is None:

        return None

    return session_statistics(
        conn,
        winner
    )


# ============================================================
# Карточка поездки
# ============================================================

def session_card_data(
    conn,
    session_id
):

    stat = session_statistics(
        conn,
        session_id
    )

    if stat is None:

        return None

    files = existing_files(
        conn,
        session_id
    )

    stat["files"] = files

    trip_start, trip_end = trip_time(
        conn,
        session_id
    )

    if trip_start:
        stat["trip_start"] = trip_start

    if trip_end:
        stat["trip_end"] = trip_end

    if trip_start and trip_end:
        stat["duration"] = format_duration(
            trip_start,
            trip_end
        )

    stat["directory_size"] = format_size(

        session_directory_size(
            conn,
            session_id
        )

    )

    return stat


# ============================================================
# Карточки всех поездок
# ============================================================

def all_sessions(
    conn
):

    sql = """

        SELECT

            id

        FROM capture_sessions

        ORDER BY

            start_time DESC

    """

    rows = conn.execute(
        sql
    ).fetchall()

    result = []

    for row in rows:

        card = session_card_data(
            conn,
            row[0]
        )

        if card:

            result.append(card)

    return result

def all_session_cards(conn):

    sql = """

        SELECT

            id

        FROM capture_sessions

        ORDER BY

            start_time DESC

    """

    rows = conn.execute(sql).fetchall()

    result = []

    for row in rows:

        card = session_card_data(

            conn,

            row[0]

        )

        if card:

            result.append(card)

    return result


# ============================================================
# Поиск поездки по ID
# ============================================================

def find_session(
    conn,
    session_id
):

    for session in all_session_cards(conn):

        if session["id"] == session_id:

            return session

    return None


# ============================================================
# Всего поездок
# ============================================================

def sessions_count(conn):

    return query_value(

        conn,

        """

        SELECT COUNT(*)

        FROM capture_sessions

        """,

        default=0

    )