"""
sqlite_mcp_server.py
────────────────────────────
FastMCP server exposing two tools backed by a local SQLite DB of students.

Tools
-----
• insert_student_record(sql_insert_query: str) -> bool
• fetch_student_records(sql_select_query: str = "SELECT * FROM students") -> list[dict]

Run
---

$ python sqlite_student_mcp_server.py
"""

import pathlib
import sqlite3
from dotenv import load_dotenv
from mcp.server.fastmcp import FastMCP


# ----------------------------------------------------------------------------------------------------------
# Environment / constants
# ----------------------------------------------------------------------------------------------------------
load_dotenv()
DB_PATH = pathlib.Path("Students.db")  # auto‑created if absent
# ----------------------------------------------------------------------------------------------------------


# ----------------------------------------------------------------------------------------------------------
# FastMCP server & tools
# ----------------------------------------------------------------------------------------------------------
mcp = FastMCP("SQLite‑Students‑DB")
# ----------------------------------------------------------------------------------------------------------

# ----------------------------------------------------------------------------------------------------------
# Connection helper
# ----------------------------------------------------------------------------------------------------------
def get_sqlite_connection(db_path):
    """
    Open (or create) a SQLite database file and return a (connection, cursor).

    Parameters
    ----------
    db_path : pathlib.Path
        Path to the SQLite file.  If it does not exist, SQLite will create it.

    Returns
    -------
    tuple
        (sqlite3.Connection, sqlite3.Cursor)

    Raises
    ------
    sqlite3.Error
        Propagates any low‑level SQLite error after logging a message.
    """
    try:
        # Establish a connection to the DB file (creates the file if absent)
        connection = sqlite3.connect(db_path)

        # Configure the connection so each result row behaves like a mapping
        # (row["col_name"]) instead of a plain tuple.
        connection.row_factory = sqlite3.Row

        # Obtain a cursor object for executing SQL statements.
        cursor = connection.cursor()

        return connection, cursor

    except sqlite3.Error as exc:
        # Print diagnostic information and re‑raise so callers can handle it.
        print(f"[DB] Connection error: {exc}")
        return None, None
# ----------------------------------------------------------------------------------------------------------


# ----------------------------------------------------------------------------------------------------------
# Ensure table exists
# ----------------------------------------------------------------------------------------------------------
def ensure_student_table():
    """
    Ensure the `students` table exists, then return an open (connection, cursor).

    The table schema is:

        id     INTEGER PRIMARY KEY AUTOINCREMENT
        name   TEXT    NOT NULL
        gender TEXT    NOT NULL
        class  TEXT    NOT NULL

    Returns
    -------
    tuple
        (sqlite3.Connection, sqlite3.Cursor) with an open transaction.

    Raises
    ------
    sqlite3.Error
        Propagates any SQL/DDL error after logging.
    """
    # Step 1: open (or create) the database file
    connection, cursor = get_sqlite_connection(DB_PATH)

    try:
        if connection is None or cursor is None:
            return None, None
        
        # Step 2: execute DDL to create the table if it doesn't already exist
        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS students (
                id     INTEGER PRIMARY KEY AUTOINCREMENT,
                name   TEXT    NOT NULL,
                gender TEXT    NOT NULL,
                class  TEXT    NOT NULL
            )
            """
        )

        # Step 3: commit DDL changes so the schema is persisted
        connection.commit()

        # Step 4: return the connection/cursor so callers can keep using them
        return connection, cursor

    except sqlite3.Error as exc:
        # Roll back any partial transaction to keep DB consistent
        if connection is not None:
            connection.rollback()
        print(f"[DB] Table‑creation error: {exc}")
        return None, None
# ----------------------------------------------------------------------------------------------------------


# ----------------------------------------------------------------------------------------------------------
# Insert a new row into the students table.
# ----------------------------------------------------------------------------------------------------------
@mcp.tool()
def insert_student_record(sql_insert_query):
    """
        Insert a new row into the students table.

        Args
        ----
        sql_insert_query : str
            SQL INSERT statement, e.g.:
            INSERT INTO students (name, gender, class)
            VALUES ('John Doe', 'Male', '10A')

        Returns
        -------
        bool
            True if the insertion succeeds, False otherwise.
    """

    # Get a database connection and cursor, ensuring the 'students' table exists
    connection, cursor = ensure_student_table()
    try:
        # Check if connection or cursor creation failed
        if connection is None or cursor is None:
            # Log the failure to create connection or cursor
            print("[DB] No connection or cursor")
            return False
        
        # Log the INSERT query to be executed
        print(f"[DB] INSERT: {sql_insert_query}")

        # Execute the user-provided SQL INSERT query
        cursor.execute(sql_insert_query)

        # Commit the transaction to persist the changes
        connection.commit()

        # Return True to indicate successful insertion
        return True

    # Handle any SQLite errors that occur during the insertion
    except sqlite3.Error as exc:
        # Log any SQLite error that occurs during insertion
        print(f"[DB] Insert error: {exc}")
        return False

    finally:
        # Close the database connection to release resources
        if connection is not None:
            connection.close()
# ----------------------------------------------------------------------------------------------------------


# ----------------------------------------------------------------------------------------------------------
# Fetch rows from the students table via a SELECT query.
# ----------------------------------------------------------------------------------------------------------
@mcp.tool()
def fetch_student_records(sql_select_query):
    """
    Fetch rows from the students table via a SELECT query.

    Args
    ----
    sql_select_query : str, default "SELECT * FROM students"
        A valid SQL SELECT statement to retrieve student records.

    Returns
    -------
    list of dict
        A list where each item is a dictionary representing one student row.
        Example: [{'id': 1, 'name': 'Alice', 'gender': 'Female', 'class': '10A'}]
    """

    # Get a connection and cursor to the students database
    connection, cursor = ensure_student_table()
    try:
        # Check if connection or cursor failed to initialize
        if connection is None or cursor is None:
            print("[DB] No connection or cursor")
            return []
        
        # Log the SELECT query to be executed
        print(f"[DB] SELECT: {sql_select_query}")

        # Execute the user-provided SQL SELECT query
        cursor.execute(sql_select_query)

        # Extract column names from the cursor metadata
        columns = [d[0] for d in cursor.description]

        # Fetch all result rows from the query
        rows = cursor.fetchall()

        # Convert each row to a dictionary using column names
        student_records = [dict(zip(columns, row)) for row in rows]

        # Return the list of student records
        return student_records

    # Handle any SQLite errors that occur during the query
    except sqlite3.Error as exc:
        # Log any SQLite error that occurs during query execution
        print(f"[DB] Select error: {exc}")
        return []

    finally:
        # Close the database connection to release resources
        if connection is not None:
            connection.close()      
# ----------------------------------------------------------------------------------------------------------


# ----------------------------------------------------------------------------------------------------------
# Run server over stdio
# ----------------------------------------------------------------------------------------------------------
if __name__ == "__main__":
    mcp.run(transport="stdio")
# ----------------------------------------------------------------------------------------------------------