from __future__ import annotations

from datetime import date, datetime

import pymysql
import pymysql.cursors

from scripts.edoxid_calendar_config import DbConfig


class Database:
    """Koneksi READ-ONLY ke doxid2022 (DB aplikasi eDOXID) pakai user
    `calendar_sync_ro` yang cuma punya grant SELECT ke submissions/verificators/users.
    Tidak pernah menulis apa pun ke DB ini."""

    def __init__(self, config: DbConfig):
        self._conn = pymysql.connect(
            host=config.host,
            port=config.port,
            user=config.username,
            password=config.password,
            database=config.database,
            cursorclass=pymysql.cursors.DictCursor,
            autocommit=True,
        )

    def close(self) -> None:
        self._conn.close()

    def get_scheduled_submissions(self, min_date: str = "today") -> list[dict]:
        """Submission yang sudah punya jadwal sidang (schedule IS NOT NULL),
        dibatasi >= min_date supaya tidak menyapu semua histori lama.
        min_date: 'today' atau string 'YYYY-MM-DD'.
        """
        cutoff = date.today() if min_date == "today" else datetime.strptime(min_date, "%Y-%m-%d").date()

        with self._conn.cursor() as cur:
            cur.execute(
                """
                SELECT s.id, s.schedule, s.room, s.title, s.type_id,
                       u.name AS student_name, u.email AS student_email, u.unique_id AS student_nim
                FROM submissions s
                JOIN users u ON u.id = s.student_id
                WHERE s.schedule IS NOT NULL
                  AND s.schedule >= %s
                """,
                (cutoff,),
            )
            submissions = cur.fetchall()

        if not submissions:
            return []

        ids = [s["id"] for s in submissions]
        placeholders = ",".join(["%s"] * len(ids))

        # Diurutkan v.id supaya "Pembimbing 1/2" & "Penguji 1/2" konsisten
        # dengan urutan input aslinya.
        with self._conn.cursor() as cur:
            cur.execute(
                f"""
                SELECT v.submission_id, v.type, u.name, u.email
                FROM verificators v
                JOIN users u ON u.id = v.user_id
                WHERE v.submission_id IN ({placeholders})
                  AND v.type IN ('Pembimbing', 'Penguji')
                  AND u.email IS NOT NULL AND u.email != ''
                ORDER BY v.id
                """,
                ids,
            )
            verificator_rows = cur.fetchall()

        verificators_by_submission: dict[int, list[dict]] = {}
        for row in verificator_rows:
            verificators_by_submission.setdefault(row["submission_id"], []).append(
                {"type": row["type"], "name": row["name"], "email": row["email"]}
            )

        for submission in submissions:
            submission["verificators"] = verificators_by_submission.get(submission["id"], [])

        return submissions

    def get_ids_with_active_schedule(self) -> set[int]:
        """ID semua submission yang MASIH punya schedule (tanpa filter tanggal).

        Dipakai KHUSUS untuk deteksi orphan/cancelled — supaya sidang yang
        tanggalnya sudah lewat (tapi memang belum dibatalkan) tidak salah
        dianggap "cancelled" dan event Calendar-nya ikut terhapus, gara-gara
        filter min_date di get_scheduled_submissions() menyisihkannya.
        """
        with self._conn.cursor() as cur:
            cur.execute("SELECT id FROM submissions WHERE schedule IS NOT NULL")
            return {row["id"] for row in cur.fetchall()}