"""SQLite persistence layer for the election results app."""

import sqlite3
from pathlib import Path
from contextlib import contextmanager

DB_PATH = Path(__file__).resolve().parent.parent / "election_data.db"

DEFAULT_DISTRICTS = ["دائرة القنيطرة", "دائرة الغرب"]


@contextmanager
def get_conn():
    conn = sqlite3.connect(DB_PATH)
    conn.execute("PRAGMA foreign_keys = ON")
    conn.row_factory = sqlite3.Row
    try:
        yield conn
        conn.commit()
    finally:
        conn.close()


def init_db():
    with get_conn() as conn:
        conn.executescript(
            """
            CREATE TABLE IF NOT EXISTS districts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT UNIQUE NOT NULL
            );
            CREATE TABLE IF NOT EXISTS candidates (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                district_id INTEGER NOT NULL REFERENCES districts(id) ON DELETE CASCADE,
                name TEXT NOT NULL,
                party TEXT DEFAULT ''
            );
            CREATE TABLE IF NOT EXISTS stations (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                district_id INTEGER NOT NULL REFERENCES districts(id) ON DELETE CASCADE,
                commune TEXT DEFAULT '',
                name TEXT NOT NULL,
                institution TEXT DEFAULT '',
                official_registered INTEGER DEFAULT 0,
                UNIQUE (district_id, commune, name)
            );
            CREATE TABLE IF NOT EXISTS participation (
                station_id INTEGER PRIMARY KEY REFERENCES stations(id) ON DELETE CASCADE,
                registered INTEGER DEFAULT 0,
                voters INTEGER DEFAULT 0,
                blank INTEGER DEFAULT 0,
                null_votes INTEGER DEFAULT 0,
                updated_at TEXT
            );
            CREATE TABLE IF NOT EXISTS votes (
                station_id INTEGER NOT NULL REFERENCES stations(id) ON DELETE CASCADE,
                candidate_id INTEGER NOT NULL REFERENCES candidates(id) ON DELETE CASCADE,
                votes INTEGER DEFAULT 0,
                PRIMARY KEY (station_id, candidate_id)
            );
            """
        )
        for name in DEFAULT_DISTRICTS:
            conn.execute(
                "INSERT OR IGNORE INTO districts (name) VALUES (?)", (name,)
            )

        existing_columns = {row["name"] for row in conn.execute("PRAGMA table_info(stations)")}
        if "institution" not in existing_columns:
            conn.execute("ALTER TABLE stations ADD COLUMN institution TEXT DEFAULT ''")
        if "official_registered" not in existing_columns:
            conn.execute("ALTER TABLE stations ADD COLUMN official_registered INTEGER DEFAULT 0")


# ---------- districts ----------

def list_districts():
    with get_conn() as conn:
        return conn.execute("SELECT * FROM districts ORDER BY id").fetchall()


def add_district(name):
    with get_conn() as conn:
        conn.execute("INSERT OR IGNORE INTO districts (name) VALUES (?)", (name,))


# ---------- candidates ----------

def list_candidates(district_id):
    with get_conn() as conn:
        return conn.execute(
            "SELECT * FROM candidates WHERE district_id = ? ORDER BY id",
            (district_id,),
        ).fetchall()


def add_candidate(district_id, name, party):
    with get_conn() as conn:
        conn.execute(
            "INSERT INTO candidates (district_id, name, party) VALUES (?, ?, ?)",
            (district_id, name.strip(), party.strip()),
        )


def delete_candidate(candidate_id):
    with get_conn() as conn:
        conn.execute("DELETE FROM candidates WHERE id = ?", (candidate_id,))


def update_candidate(candidate_id, name, party):
    with get_conn() as conn:
        conn.execute(
            "UPDATE candidates SET name = ?, party = ? WHERE id = ?",
            (name.strip(), party.strip(), candidate_id),
        )


# ---------- stations ----------

def list_stations(district_id):
    with get_conn() as conn:
        return conn.execute(
            "SELECT * FROM stations WHERE district_id = ? ORDER BY commune, name",
            (district_id,),
        ).fetchall()


def list_communes(district_id):
    """Distinct commune names for a district with their station counts."""
    with get_conn() as conn:
        return conn.execute(
            """
            SELECT commune, COUNT(*) AS station_count
            FROM stations
            WHERE district_id = ?
            GROUP BY commune
            ORDER BY commune
            """,
            (district_id,),
        ).fetchall()


def list_stations_by_commune(district_id, commune):
    with get_conn() as conn:
        return conn.execute(
            "SELECT * FROM stations WHERE district_id = ? AND commune = ? ORDER BY name",
            (district_id, commune),
        ).fetchall()


def rename_commune(district_id, old_commune, new_commune):
    new_commune = new_commune.strip()
    with get_conn() as conn:
        conn.execute(
            "UPDATE stations SET commune = ? WHERE district_id = ? AND commune = ?",
            (new_commune, district_id, old_commune),
        )


def update_station(station_id, commune, name, institution, official_registered):
    with get_conn() as conn:
        conn.execute(
            """
            UPDATE stations
            SET commune = ?, name = ?, institution = ?, official_registered = ?
            WHERE id = ?
            """,
            (commune.strip(), name.strip(), institution.strip(), official_registered, station_id),
        )


def add_station(district_id, commune, name, institution="", official_registered=0):
    with get_conn() as conn:
        conn.execute(
            """
            INSERT INTO stations (district_id, commune, name, institution, official_registered)
            VALUES (?, ?, ?, ?, ?)
            """,
            (district_id, commune.strip(), name.strip(), institution.strip(), official_registered),
        )


def delete_station(station_id):
    with get_conn() as conn:
        conn.execute("DELETE FROM stations WHERE id = ?", (station_id,))


def delete_stations_by_commune(district_id, commune):
    with get_conn() as conn:
        conn.execute(
            "DELETE FROM stations WHERE district_id = ? AND commune = ?",
            (district_id, commune),
        )


def import_stations(district_id, commune, rows):
    """Bulk import stations for a commune from parsed spreadsheet rows.

    Each row is a dict with keys: name, institution, official_registered.
    Existing stations (matched by district + commune + name) are updated
    in place instead of duplicated.
    """
    commune = commune.strip()
    added, updated = 0, 0
    with get_conn() as conn:
        for row in rows:
            name = str(row["name"]).strip()
            institution = str(row.get("institution") or "").strip()
            official_registered = int(row.get("official_registered") or 0)

            existing = conn.execute(
                "SELECT id FROM stations WHERE district_id = ? AND commune = ? AND name = ?",
                (district_id, commune, name),
            ).fetchone()

            if existing:
                conn.execute(
                    "UPDATE stations SET institution = ?, official_registered = ? WHERE id = ?",
                    (institution, official_registered, existing["id"]),
                )
                updated += 1
            else:
                conn.execute(
                    """
                    INSERT INTO stations (district_id, commune, name, institution, official_registered)
                    VALUES (?, ?, ?, ?, ?)
                    """,
                    (district_id, commune, name, institution, official_registered),
                )
                added += 1
    return added, updated


# ---------- participation & votes ----------

def get_participation(station_id):
    with get_conn() as conn:
        row = conn.execute(
            "SELECT * FROM participation WHERE station_id = ?", (station_id,)
        ).fetchone()
        return row


def get_votes(station_id):
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT candidate_id, votes FROM votes WHERE station_id = ?",
            (station_id,),
        ).fetchall()
        return {r["candidate_id"]: r["votes"] for r in rows}


def save_station_result(station_id, registered, voters, blank, null_votes, votes_by_candidate):
    import datetime

    with get_conn() as conn:
        conn.execute(
            """
            INSERT INTO participation (station_id, registered, voters, blank, null_votes, updated_at)
            VALUES (?, ?, ?, ?, ?, ?)
            ON CONFLICT(station_id) DO UPDATE SET
                registered = excluded.registered,
                voters = excluded.voters,
                blank = excluded.blank,
                null_votes = excluded.null_votes,
                updated_at = excluded.updated_at
            """,
            (station_id, registered, voters, blank, null_votes, datetime.datetime.now().isoformat(timespec="seconds")),
        )
        for candidate_id, v in votes_by_candidate.items():
            conn.execute(
                """
                INSERT INTO votes (station_id, candidate_id, votes)
                VALUES (?, ?, ?)
                ON CONFLICT(station_id, candidate_id) DO UPDATE SET votes = excluded.votes
                """,
                (station_id, candidate_id, v),
            )


def district_summary(district_id, commune=None):
    """Returns (participation_totals_dict, candidates_votes_rows, reported_dict).

    Pass `commune` to restrict all three to stations in that commune only.
    """
    commune_filter = "AND s.commune = ?" if commune is not None else ""
    commune_args = (commune,) if commune is not None else ()

    with get_conn() as conn:
        totals = conn.execute(
            f"""
            SELECT
                COALESCE(SUM(p.registered), 0) AS registered,
                COALESCE(SUM(p.voters), 0) AS voters,
                COALESCE(SUM(p.blank), 0) AS blank,
                COALESCE(SUM(p.null_votes), 0) AS null_votes
            FROM participation p
            JOIN stations s ON s.id = p.station_id
            WHERE s.district_id = ? {commune_filter}
            """,
            (district_id, *commune_args),
        ).fetchone()

        candidate_votes = conn.execute(
            f"""
            SELECT c.id, c.name, c.party,
                   COALESCE(SUM(CASE WHEN s.id IS NOT NULL THEN v.votes END), 0) AS total_votes
            FROM candidates c
            LEFT JOIN votes v ON v.candidate_id = c.id
            LEFT JOIN stations s ON s.id = v.station_id AND s.district_id = c.district_id {commune_filter}
            WHERE c.district_id = ?
            GROUP BY c.id
            ORDER BY total_votes DESC
            """,
            (*commune_args, district_id),
        ).fetchall()

        reported = conn.execute(
            f"""
            SELECT COUNT(*) AS reported_count,
                   (SELECT COUNT(*) FROM stations s WHERE district_id = ? {commune_filter}) AS total_count
            FROM participation p
            JOIN stations s ON s.id = p.station_id
            WHERE s.district_id = ? {commune_filter}
            """,
            (district_id, *commune_args, district_id, *commune_args),
        ).fetchone()

        return dict(totals), [dict(r) for r in candidate_votes], dict(reported)


def station_level_results(district_id):
    """Per-station breakdown for a full export: one row per station with its
    participation numbers and votes for every candidate in the district."""
    with get_conn() as conn:
        candidates = conn.execute(
            "SELECT id, name, party FROM candidates WHERE district_id = ? ORDER BY id",
            (district_id,),
        ).fetchall()

        stations = conn.execute(
            """
            SELECT s.id, s.commune, s.name, s.institution,
                   p.registered AS entered_registered, s.official_registered,
                   p.voters, p.blank, p.null_votes, p.updated_at
            FROM stations s
            LEFT JOIN participation p ON p.station_id = s.id
            WHERE s.district_id = ?
            ORDER BY s.commune, s.name
            """,
            (district_id,),
        ).fetchall()

        votes = conn.execute(
            """
            SELECT v.station_id, v.candidate_id, v.votes
            FROM votes v
            JOIN stations s ON s.id = v.station_id
            WHERE s.district_id = ?
            """,
            (district_id,),
        ).fetchall()

        votes_by_station = {}
        for v in votes:
            votes_by_station.setdefault(v["station_id"], {})[v["candidate_id"]] = v["votes"]

        rows = []
        for s in stations:
            row = {
                "الجماعة": s["commune"] or "",
                "رقم المكتب": s["name"],
                "المؤسسة": s["institution"] or "",
                "عدد الناخبين المسجلين": (
                    s["entered_registered"] if s["entered_registered"] is not None else s["official_registered"]
                ),
                "عدد المصوتين": s["voters"] or 0,
                "الأوراق البيضاء": s["blank"] or 0,
                "الأوراق الملغاة": s["null_votes"] or 0,
            }
            station_votes = votes_by_station.get(s["id"], {})
            for c in candidates:
                label = f"{c['name']} ({c['party']})" if c["party"] else c["name"]
                row[label] = station_votes.get(c["id"], 0)
            row["آخر تحديث"] = s["updated_at"] or ""
            rows.append(row)

        return rows