#!/usr/bin/env python
# coding: utf-8
# # Bibliothek
#!pip install PyMySQL


import pprint
import json
import pymysql.cursors
from fastapi import FastAPI
from fastapi.responses import JSONResponse
import os

# Connect to the database
connection = pymysql.connect(host='134.0.14.99',
                            user='marcadm',
                            password=os.environ['MURMEL_DB_PASSWORD'],
                            database='murmel',
                            cursorclass=pymysql.cursors.DictCursor)

app = FastAPI()


@app.get("/groups")
def get_groups():
    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT Gruppe, idGruppe FROM `gruppe` WHERE aktuell is True ORDER BY idGruppe ASC;"
        cursor.execute(sql, ())
        result = cursor.fetchall()
        #pprint.pprint(result)
    return result

@app.get("/students/{idGruppe}")
def get_students(idGruppe):
    with connection.cursor() as cursor:
        #sql = "SELECT Gruppe, idGruppe FROM `gruppe` WHERE idGruppe=%s ORDER BY name ASC;"
        sql = "select concat(`ki`.`Vorname`,' ',`ki`.`Nachnamen`) AS `Kind`, `ki`.`idKind` AS `idKind` from (((`kind` `ki` join `gruppe` `gr`) join `kind_x_gruppe_x_schuljahr` `kgs`) join `schuljahr` `sch`) where `ki`.`idKind` = `kgs`.`x_kind` and `sch`.`idschuljahr` = `kgs`.`x_schuljahr` and `gr`.`idGruppe` =  `kgs`.`x_gruppe` and `sch`.`aktuell` = 1 and (`gr`.`Gruppe` = %s or `gr`.`idGruppe` =  %s) order by 1"
        cursor.execute(sql, (idGruppe,idGruppe))
        result = cursor.fetchall()
        #pprint.pprint(result)
    return result

@app.get("/return/{idBuch}")
def rueckgabe(idBuch, grund='rueckgabe'):
    """
    Updates the database to mark a book as returned.

    Parameters:
    - idBuch (int): The ID of the book to be returned.
    - grund (str): The reason for the return (default: 'rueckgabe').

    Returns:
    - int: 0 if the book was not found or already returned.

    """
    with connection.cursor() as cursor:
        # check if the book is already returned
        sql = "SELECT `idBuch`, `idKind`, `ausleihe`, `rueckgabe`, `rueckGrund` FROM `ausleihe` WHERE `idBuch` = %s AND `rueckgabe` is NULL;"
        cursor.execute(sql, (idBuch,))
        result = cursor.fetchall()
        if len(result) == 0:
            return 0
        # return the book
        sql = "UPDATE `ausleihe` SET `rueckgabe` = NOW(), `rueckGrund` = %s WHERE `idBuch` = %s AND `rueckgabe` is NULL;"
        cursor.execute(sql, (grund, idBuch))
        connection.commit()
        #pprint.pprint(result)
        # return if the book was returned or not and who had it before
        return result

@app.get("/borrow/{idBuch}/{idKind}")
def ausleihe(idBuch, idKind):
    """
    Performs a book loan operation by inserting a new record into the 'ausleihe' table.

    Parameters:
    - idBuch (int): The ID of the book being loaned.
    - idKind (int): The ID of the child borrowing the book.

    Returns:
    - rueckgabe_result (str): The result of the book return operation, indicating if the book was returned or not and who had it before, or if there was an error.
    """
    rueckgabe_result = rueckgabe(idBuch, grund="neu-ausleihe") # Buch kann nicht durch andere ausgeliehen sein        
    sql = "INSERT INTO `ausleihe` (`idBuch`, `idKind`, `ausleihe`) VALUES (%s, %s, NOW());"
    with connection.cursor() as cursor:
        cursor.execute(sql, (idBuch, idKind))
        connection.commit()
        #pprint.pprint(result)
    # return if the book was returned or not and who had it before or if there was an error
    return rueckgabe_result
    
@app.get("/borrowed/{idKind}")
def ausgeliehen(idKind):
    """
    Retrieves the books that are currently borrowed by a specific child.

    Args:
        idKind (int): The ID of the child.

    Returns:
        list: A list of tuples containing the book ID and the borrowing date for each book that is currently borrowed by the child.
    """
    with connection.cursor() as cursor:
        sql = "SELECT `idBuch`, `ausleihe` FROM `ausleihe` WHERE `idKind` = %s AND `rueckgabe` IS NULL;"
        cursor.execute(sql, (idKind,))
        result = cursor.fetchall()
        #pprint.pprint(result)
    return result


# run the app
if __name__ == '__main__':
    app.run(host='localhost', port=5000)

# %%