import json
import os

import mysql.connector
from mysql.connector import Error


# ============================================================
# KONFIGURASI DATABASE MYSQL
# ============================================================

DB_CONFIG = {
    "host": os.getenv("DB_HOST", "127.0.0.1"),
    "port": int(os.getenv("DB_PORT", "3306")),
    "user": os.getenv("DB_USER", "root"),
    "password": os.getenv("DB_PASSWORD", ""),
    "database": os.getenv("DB_NAME", "slearn_web_db"),
    "charset": "utf8mb4"
}


# ============================================================
# HELPER JSON
# ============================================================

def _to_json(data):
    """
    Mengubah list/dict menjadi JSON string
    sebelum disimpan ke database.
    """

    if data is None:
        return None

    if isinstance(data, str):
        return data

    return json.dumps(
        data,
        ensure_ascii=False
    )


def _from_json(data, default=None):
    """
    Membaca JSON string dari database.
    """

    if default is None:
        default = []

    if data is None:
        return default

    if isinstance(data, (list, dict)):
        return data

    try:
        return json.loads(data)

    except (TypeError, json.JSONDecodeError):
        return default


def _format_datetime(value):
    """
    Mengubah datetime MySQL menjadi string ISO.
    """

    if value is None:
        return None

    return value.isoformat(
        timespec="seconds"
    )


# ============================================================
# MEMBUAT KONEKSI DATABASE
# ============================================================

def get_db_connection():

    try:
        connection = mysql.connector.connect(
            **DB_CONFIG
        )

        if not connection.is_connected():
            raise ConnectionError(
                "Koneksi ke database MySQL tidak aktif."
            )

        return connection

    except Error as error:
        raise ConnectionError(
            f"Gagal terhubung ke MySQL: {error}"
        ) from error


# ============================================================
# TEST KONEKSI DATABASE
# ============================================================

def test_db_connection():

    connection = None
    cursor = None

    try:
        connection = get_db_connection()
        cursor = connection.cursor()

        cursor.execute(
            "SELECT DATABASE();"
        )

        database_name = (
            cursor.fetchone()[0]
        )

        cursor.execute(
            "SHOW TABLES;"
        )

        tables = [
            row[0]
            for row in cursor.fetchall()
        ]

        return {
            "status": "success",
            "database": database_name,
            "tables": tables
        }

    finally:
        if cursor is not None:
            cursor.close()

        if (
            connection is not None
            and connection.is_connected()
        ):
            connection.close()


# ============================================================
# RINGKASAN DATABASE
# ============================================================

def get_database_summary():

    connection = None
    cursor = None

    try:
        connection = get_db_connection()
        cursor = connection.cursor()

        cursor.execute(
            "SELECT COUNT(*) FROM analysis_batches"
        )

        total_batches = (
            cursor.fetchone()[0]
        )

        cursor.execute(
            "SELECT COUNT(*) FROM analysis_results"
        )

        total_results = (
            cursor.fetchone()[0]
        )

        cursor.execute(
            """
            SELECT COUNT(*)
            FROM single_prediction_history
            """
        )

        total_single_predictions = (
            cursor.fetchone()[0]
        )

        return {
            "total_batches": int(
                total_batches
            ),
            "total_results": int(
                total_results
            ),
            "total_single_predictions": int(
                total_single_predictions
            )
        }

    finally:
        if cursor is not None:
            cursor.close()

        if (
            connection is not None
            and connection.is_connected()
        ):
            connection.close()


# ============================================================
# SIMPAN SATU BATCH ANALISIS CSV
# ============================================================

def save_analysis_batch(
    batch_id,
    file_name,
    selected_column,
    results
):

    connection = None
    cursor = None

    versions = {item.get("model_version") for item in results}
    if len(versions) != 1 or None in versions:
        raise ValueError("Satu batch harus menggunakan satu versi model yang tercatat.")
    model_version = versions.pop()
    total_data = len(results)

    total_positive = sum(
        1
        for item in results
        if item["label"] == 1
    )

    total_negative = sum(
        1
        for item in results
        if item["label"] == 0
    )

    try:
        connection = get_db_connection()
        cursor = connection.cursor()

        connection.start_transaction()

        query_batch = """
            INSERT INTO analysis_batches (
                batch_id,
                file_name,
                selected_column,
                total_data,
                total_positive,
                total_negative, model_version
            )
            VALUES (
                %s, %s, %s,
                %s, %s, %s, %s
            )
        """

        cursor.execute(
            query_batch,
            (
                batch_id,
                file_name,
                selected_column,
                total_data,
                total_positive,
                total_negative, model_version
            )
        )

        query_result = """
            INSERT INTO analysis_results (
                batch_id,
                source_row_number,
                original_text,
                cleaning,
                case_folding,
                normalization,
                tokenizing,
                stopword_removal,
                stemming,
                label,
                sentiment,
                tfidf_top
            )
            VALUES (
                %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s, %s
            )
        """

        data_results = []

        for item in results:

            preprocessing = (
                item["preprocessing"]
            )

            data_results.append(
                (
                    batch_id,
                    item["source_row_number"],
                    item["teks"],
                    preprocessing["cleaning"],
                    preprocessing["case_folding"],
                    preprocessing["normalization"],
                    _to_json(
                        preprocessing["tokenizing"]
                    ),
                    preprocessing["stopword_removal"],
                    preprocessing["stemming"],
                    item["label"],
                    item["sentimen"],
                    _to_json(
                        item["tfidf_top"]
                    )
                )
            )

        cursor.executemany(
            query_result,
            data_results
        )

        connection.commit()

        return {
            "batch_id": batch_id,
            "file_name": file_name,
            "selected_column": selected_column,
            "total_data": total_data,
            "total_positive": total_positive,
            "total_negative": total_negative, "model_version": model_version
        }

    except Exception:

        if connection is not None:
            connection.rollback()

        raise

    finally:
        if cursor is not None:
            cursor.close()

        if (
            connection is not None
            and connection.is_connected()
        ):
            connection.close()


# ============================================================
# MEMBACA DETAIL SATU BATCH CSV
# ============================================================

def get_analysis_batch(
    batch_id
):

    connection = None
    cursor = None

    try:
        connection = get_db_connection()

        cursor = connection.cursor(
            dictionary=True
        )

        cursor.execute(
            """
            SELECT
                batch_id,
                file_name,
                selected_column,
                total_data,
                total_positive,
                total_negative,
                model_version,
                created_at
            FROM analysis_batches
            WHERE batch_id = %s
            LIMIT 1
            """,
            (
                batch_id,
            )
        )

        batch = cursor.fetchone()

        if batch is None:
            return None

        cursor.execute(
            """
            SELECT
                id,
                source_row_number,
                original_text,
                cleaning,
                case_folding,
                normalization,
                tokenizing,
                stopword_removal,
                stemming,
                label,
                sentiment,
                tfidf_top,
                created_at
            FROM analysis_results
            WHERE batch_id = %s
            ORDER BY
                source_row_number ASC,
                id ASC
            """,
            (
                batch_id,
            )
        )

        rows = cursor.fetchall()

        results = []

        for row in rows:

            tokenizing = _from_json(
                row["tokenizing"],
                []
            )

            tfidf_top = _from_json(
                row["tfidf_top"],
                []
            )

            results.append(
                {
                    "id": int(
                        row["id"]
                    ),

                    "source_row_number": int(
                        row["source_row_number"]
                    ),

                    "teks": (
                        row["original_text"]
                    ),

                    "label": int(
                        row["label"]
                    ),

                    "sentimen": (
                        row["sentiment"]
                    ),

                    "preprocessing": {
                        "cleaning":
                            row["cleaning"]
                            or "",

                        "case_folding":
                            row["case_folding"]
                            or "",

                        "normalization":
                            row["normalization"]
                            or "",

                        "tokenizing":
                            tokenizing,

                        "stopword_removal":
                            row["stopword_removal"]
                            or "",

                        "stemming":
                            row["stemming"]
                            or ""
                    },

                    "tfidf_top":
                        tfidf_top
                }
            )

        return {
            "batch_id":
                batch["batch_id"],
            "model_version": batch.get("model_version"),

            "file_name":
                batch["file_name"],

            "selected_column":
                batch["selected_column"],

            "total_data": int(
                batch["total_data"]
            ),

            "total_positive": int(
                batch["total_positive"]
            ),

            "total_negative": int(
                batch["total_negative"]
            ),

            "created_at":
                _format_datetime(
                    batch["created_at"]
                ),

            "results":
                results
        }

    finally:
        if cursor is not None:
            cursor.close()

        if (
            connection is not None
            and connection.is_connected()
        ):
            connection.close()


# ============================================================
# MEMBACA SEMUA RIWAYAT BATCH CSV
# ============================================================

def get_analysis_history():

    connection = None
    cursor = None

    try:
        connection = get_db_connection()

        cursor = connection.cursor(
            dictionary=True
        )

        cursor.execute(
            """
            SELECT
                batch_id,
                file_name,
                selected_column,
                total_data,
                total_positive,
                total_negative,
                model_version,
                created_at
            FROM analysis_batches
            ORDER BY
                created_at DESC,
                id DESC
            """
        )

        rows = cursor.fetchall()

        history = []

        for row in rows:

            history.append(
                {
                    "batch_id":
                        row["batch_id"],
                    "model_version": row.get("model_version"),

                    "file_name":
                        row["file_name"],

                    "selected_column":
                        row["selected_column"],

                    "total_data": int(
                        row["total_data"]
                    ),

                    "total_positive": int(
                        row["total_positive"]
                    ),

                    "total_negative": int(
                        row["total_negative"]
                    ),

                    "created_at":
                        _format_datetime(
                            row["created_at"]
                        )
                }
            )

        return history

    finally:
        if cursor is not None:
            cursor.close()

        if (
            connection is not None
            and connection.is_connected()
        ):
            connection.close()


# ============================================================
# SIMPAN PREDIKSI SATU ULASAN
# ============================================================

def save_single_prediction(
    result
):
    """
    Menyimpan satu hasil dari fungsi analisis_teks()
    ke tabel single_prediction_history.
    """

    connection = None
    cursor = None

    preprocessing = (
        result.get(
            "preprocessing",
            {}
        )
    )

    try:
        connection = get_db_connection()
        cursor = connection.cursor()

        query = """
            INSERT INTO single_prediction_history (
                original_text,
                cleaning,
                case_folding,
                normalization,
                tokenizing,
                stopword_removal,
                stemming,
                jumlah_fitur_dikenali,
                label,
                sentiment,
                tfidf_top, model_version
            )
            VALUES (
                %s, %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s, %s
            )
        """

        values = (
            result.get(
                "teks",
                ""
            ),

            preprocessing.get(
                "cleaning",
                ""
            ),

            preprocessing.get(
                "case_folding",
                ""
            ),

            preprocessing.get(
                "normalization",
                ""
            ),

            _to_json(
                preprocessing.get(
                    "tokenizing",
                    []
                )
            ),

            preprocessing.get(
                "stopword_removal",
                ""
            ),

            preprocessing.get(
                "stemming",
                ""
            ),

            int(
                result.get(
                    "jumlah_fitur_dikenali",
                    0
                )
            ),

            int(
                result["label"]
            ),

            result["sentimen"],

            _to_json(
                result.get(
                    "tfidf_top",
                    []
                )
            )
        )

        values = (*values, result["model_version"])

        cursor.execute(
            query,
            values
        )

        prediction_id = (
            cursor.lastrowid
        )

        connection.commit()

        return int(
            prediction_id
        )

    except Exception:

        if connection is not None:
            connection.rollback()

        raise

    finally:
        if cursor is not None:
            cursor.close()

        if (
            connection is not None
            and connection.is_connected()
        ):
            connection.close()


# ============================================================
# MEMBACA SEMUA RIWAYAT PREDIKSI SATU ULASAN
# ============================================================

def get_single_prediction_history():

    connection = None
    cursor = None

    try:
        connection = get_db_connection()

        cursor = connection.cursor(
            dictionary=True
        )

        cursor.execute(
            """
            SELECT
                id,
                original_text,
                jumlah_fitur_dikenali,
                label,
                sentiment,
                model_version,
                created_at
            FROM single_prediction_history
            ORDER BY
                created_at DESC,
                id DESC
            """
        )

        rows = cursor.fetchall()

        history = []

        for row in rows:

            history.append(
                {
                    "id": int(
                        row["id"]
                    ),

                    "teks":
                        row["original_text"],
                    "model_version": row.get("model_version"),

                    "jumlah_fitur_dikenali":
                        int(
                            row[
                                "jumlah_fitur_dikenali"
                            ]
                            or 0
                        ),

                    "label": int(
                        row["label"]
                    ),

                    "sentimen":
                        row["sentiment"],

                    "created_at":
                        _format_datetime(
                            row["created_at"]
                        )
                }
            )

        return history

    finally:
        if cursor is not None:
            cursor.close()

        if (
            connection is not None
            and connection.is_connected()
        ):
            connection.close()


# ============================================================
# MEMBACA DETAIL SATU PREDIKSI
# ============================================================

def get_single_prediction(
    prediction_id
):

    connection = None
    cursor = None

    try:
        connection = get_db_connection()

        cursor = connection.cursor(
            dictionary=True
        )

        cursor.execute(
            """
            SELECT
                id,
                original_text,
                cleaning,
                case_folding,
                normalization,
                tokenizing,
                stopword_removal,
                stemming,
                jumlah_fitur_dikenali,
                label,
                sentiment,
                tfidf_top,
                model_version,
                created_at
            FROM single_prediction_history
            WHERE id = %s
            LIMIT 1
            """,
            (
                prediction_id,
            )
        )

        row = cursor.fetchone()

        if row is None:
            return None

        tokenizing = _from_json(
            row["tokenizing"],
            []
        )

        tfidf_top = _from_json(
            row["tfidf_top"],
            []
        )

        return {
            "id": int(
                row["id"]
            ),

            "teks":
                row["original_text"],
            "model_version": row.get("model_version"),

            "label": int(
                row["label"]
            ),

            "sentimen":
                row["sentiment"],

            "jumlah_fitur_dikenali":
                int(
                    row[
                        "jumlah_fitur_dikenali"
                    ]
                    or 0
                ),

            "preprocessing": {
                "cleaning":
                    row["cleaning"]
                    or "",

                "case_folding":
                    row["case_folding"]
                    or "",

                "normalization":
                    row["normalization"]
                    or "",

                "tokenizing":
                    tokenizing,

                "stopword_removal":
                    row["stopword_removal"]
                    or "",

                "stemming":
                    row["stemming"]
                    or ""
            },

            "tfidf_top":
                tfidf_top,

            "created_at":
                _format_datetime(
                    row["created_at"]
                )
        }

    finally:
        if cursor is not None:
            cursor.close()

        if (
            connection is not None
            and connection.is_connected()
        ):
            connection.close()


# ============================================================
# TEST LANGSUNG DARI TERMINAL
# ============================================================

if __name__ == "__main__":

    hasil = test_db_connection()

    summary = (
        get_database_summary()
    )

    print(
        "KONEKSI MYSQL BERHASIL"
    )

    print(
        "Database :",
        hasil["database"]
    )

    print(
        "Tabel    :",
        hasil["tables"]
    )

    print(
        "Batch    :",
        summary["total_batches"]
    )

    print(
        "Hasil CSV:",
        summary["total_results"]
    )

    print(
        "Prediksi :",
        summary[
            "total_single_predictions"
        ]
    )