import os
import psycopg2
from psycopg2 import pool
from sshtunnel import SSHTunnelForwarder
from dotenv import load_dotenv
from flask import Flask, render_template, request, redirect, url_for, session

app = Flask(__name__)
app.secret_key = "bibliopremium-secret-key"

load_dotenv()

DB_NAME = os.getenv("DB_NAME")
DB_USER = os.getenv("DB_USER")
DB_PASSWORD = os.getenv("DB_PASSWORD")

SSH_HOST = os.getenv("SSH_HOST")
SSH_USER = os.getenv("SSH_USER")
SSH_PASSWORD = os.getenv("SSH_PASSWORD")


# SSH поврзување
tunnel = SSHTunnelForwarder(
    (SSH_HOST, 22),
    ssh_username=SSH_USER,
    ssh_password=SSH_PASSWORD,
    remote_bind_address=("localhost", 5432)
)

tunnel.start()


# --------------------------------------------------
# DATABASE CONNECTION POOL
# --------------------------------------------------

db_pool = pool.SimpleConnectionPool(
    1,      # минимален број конекции
    10,     # максимален број конекции
    host="localhost",
    port=tunnel.local_bind_port,
    database=DB_NAME,
    user=DB_USER,
    password=DB_PASSWORD
)

if db_pool:
    print("Успешно креиран connection pool за BiblioPremium базата!")

# --------------------------------------------------
# ЗЕМАЊЕ И ВРАЌАЊЕ КОНЕКЦИЈА ОД POOL
# --------------------------------------------------

def get_db_connection():
    return db_pool.getconn()


def release_db_connection(conn):
    db_pool.putconn(conn)   


# --------------------------------------------------
# ПОЧЕТНА СТРАНИЦА + ПРЕБАРУВАЊЕ
# --------------------------------------------------

@app.route("/")
def home():
    search = request.args.get("search", "")

    conn = get_db_connection()
    cursor = conn.cursor()

    if search:
        cursor.execute("""
            SELECT kniga_id, naslov, cena, opis, godina_izdavanje
            FROM project.knigi
            WHERE naslov ILIKE %s
            ORDER BY kniga_id;
        """, (f"%{search}%",))
    else:
        cursor.execute("""
            SELECT kniga_id, naslov, cena, opis, godina_izdavanje
            FROM project.knigi
            ORDER BY kniga_id;
        """)

    knigi = cursor.fetchall()
    cursor.close()
    release_db_connection(conn)

    return render_template(
        "index.html",
        knigi=knigi,
        search=search
    )


# --------------------------------------------------
# ДЕТАЛИ ЗА КНИГА
# --------------------------------------------------

@app.route("/book/<int:kniga_id>")
def book_details(kniga_id):
    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute("""
        SELECT kniga_id, naslov, cena, opis, godina_izdavanje
        FROM project.knigi
        WHERE kniga_id = %s;
    """, (kniga_id,))

    kniga = cursor.fetchone()

    is_favorite = False

    if "korisnik_id" in session:
        cursor.execute("""
            SELECT 1
            FROM project.omileni
            WHERE korisnik_id = %s
            AND kniga_id = %s;
        """, (session["korisnik_id"], kniga_id))

        is_favorite = cursor.fetchone() is not None

    cursor.close()
    release_db_connection(conn)

    return render_template(
        "book_details.html",
        kniga=kniga,
        is_favorite=is_favorite
    )


# --------------------------------------------------
# ДОДАВАЊЕ КНИГА ВО ОМИЛЕНИ
# --------------------------------------------------

@app.route("/favorite/<int:kniga_id>", methods=["POST"])
def add_favorite(kniga_id):

    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute("""
        INSERT INTO project.omileni
            (korisnik_id, kniga_id, datum_dodavanje)
        VALUES (%s, %s, CURRENT_DATE)
        ON CONFLICT (korisnik_id, kniga_id) DO NOTHING;
    """, (session["korisnik_id"], kniga_id))

    conn.commit()
    cursor.close()
    release_db_connection(conn)

    return redirect(
        url_for("book_details", kniga_id=kniga_id)
    )


# --------------------------------------------------
# ОМИЛЕНИ КНИГИ
# --------------------------------------------------

@app.route("/favorites")
def favorites():

    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute("""
        SELECT
            k.kniga_id,
            k.naslov,
            k.cena,
            k.opis,
            k.godina_izdavanje
        FROM project.omileni o
        JOIN project.knigi k
            ON o.kniga_id = k.kniga_id
        WHERE o.korisnik_id = %s
        ORDER BY o.datum_dodavanje DESC;
    """, (session["korisnik_id"],))

    knigi = cursor.fetchall()
    cursor.close()
    release_db_connection(conn)

    return render_template(
        "favorites.html",
        knigi=knigi
    )


# --------------------------------------------------
# ОТСТРАНУВАЊЕ КНИГА ОД ОМИЛЕНИ
# --------------------------------------------------

@app.route("/favorite/remove/<int:kniga_id>", methods=["POST"])
def remove_favorite(kniga_id):

    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    conn = get_db_connection()
    cursor = conn.cursor() 

    cursor.execute("""
        DELETE FROM project.omileni
        WHERE korisnik_id = %s
        AND kniga_id = %s;
    """, (session["korisnik_id"], kniga_id))

    conn.commit()
    cursor.close()
    release_db_connection(conn)

    return redirect(
        url_for("favorites")
    )


# --------------------------------------------------
# ПЕРСОНАЛИЗИРАНИ ПРЕПОРАКИ
# --------------------------------------------------

@app.route("/recommendations")
def recommendations():

    mood_id = request.args.get("mood", type=int)

    conn = get_db_connection()
    cursor = conn.cursor()

    # --------------------------------------------------
    # ГИ ЗЕМАМЕ СИТЕ РАСПОЛОЖЕНИЈА
    # --------------------------------------------------

    cursor.execute("""
        SELECT raspolozenie_id, naziv, opis
        FROM project.raspolozenija
        ORDER BY raspolozenie_id;
    """)

    raspolozenija = cursor.fetchall()

    knigi = []
    selected_mood = None
    personalized_books = []


    # --------------------------------------------------
    # ПЕРСОНАЛИЗИРАНИ ПРЕПОРАКИ
    # СПОРЕД ЖАНРОВИ И КАТЕГОРИИ
    # --------------------------------------------------

    if "korisnik_id" in session:

        korisnik_id = session["korisnik_id"]

        cursor.execute("""
            SELECT DISTINCT
                k.kniga_id,
                k.naslov,
                k.cena,
                k.opis,
                k.godina_izdavanje
            FROM project.knigi k
            WHERE

                EXISTS (
                    SELECT 1
                    FROM project.ima_zhanr iz
                    JOIN project.preferira_zhanr pz
                        ON iz.zhanr_id = pz.zhanr_id
                    WHERE iz.kniga_id = k.kniga_id
                      AND pz.korisnik_id = %s
                )

                OR

                EXISTS (
                    SELECT 1
                    FROM project.ima_kategorija ik
                    JOIN project.preferira_kategorija pk
                        ON ik.kategorija_id = pk.kategorija_id
                    WHERE ik.kniga_id = k.kniga_id
                      AND pk.korisnik_id = %s
                )

            ORDER BY k.kniga_id;
        """, (korisnik_id, korisnik_id))

        personalized_books = cursor.fetchall()


    # --------------------------------------------------
    # ПРЕПОРАКИ СПОРЕД РАСПОЛОЖЕНИЕ
    # --------------------------------------------------

    if mood_id:

        cursor.execute("""
            SELECT raspolozenie_id, naziv, opis
            FROM project.raspolozenija
            WHERE raspolozenie_id = %s;
        """, (mood_id,))

        selected_mood = cursor.fetchone()


        cursor.execute("""
            SELECT
                k.kniga_id,
                k.naslov,
                k.cena,
                k.opis,
                k.godina_izdavanje
            FROM project.knigi k
            JOIN project.povrzana_so p
                ON k.kniga_id = p.kniga_id
            WHERE p.raspolozenie_id = %s
            ORDER BY k.kniga_id;
        """, (mood_id,))

        knigi = cursor.fetchall()


    cursor.close()
    release_db_connection(conn)


    return render_template(
        "recommendations.html",
        raspolozenija=raspolozenija,
        knigi=knigi,
        selected_mood=selected_mood,
        personalized_books=personalized_books
    )
# --------------------------------------------------
# КУПУВАЊЕ КНИГА
# --------------------------------------------------

@app.route("/buy/<int:kniga_id>", methods=["POST"])
def buy_book(kniga_id):

    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    # Ја земаме количината што корисникот ја избрал
    try:
        kolicina = int(request.form.get("kolicina", 1))
    except (TypeError, ValueError):
        kolicina = 1

    # Дозволуваме количина од 1 до 5
    if kolicina < 1 or kolicina > 5:
        return "Невалидна количина.", 400

    conn = get_db_connection()
    cursor = conn.cursor()

    try:

        # 1. Ја земаме цената и моменталната залиха
        cursor.execute("""
            SELECT cena, kolicina_na_zaliha
            FROM project.knigi
            WHERE kniga_id = %s;
        """, (kniga_id,))

        kniga = cursor.fetchone()

        if kniga is None:
            cursor.close()
            release_db_connection(conn)
            return "Книгата не постои.", 404

        cena = kniga[0]
        kolicina_na_zaliha = kniga[1]

        # Проверуваме дали има доволно примероци
        if kolicina_na_zaliha < kolicina:
            cursor.close()
            release_db_connection(conn)
            return (
                f"Нема доволно примероци на залиха. "
                f"Моментално достапни: {kolicina_na_zaliha}.",
                400
            )

        # Ја пресметуваме вкупната цена
        vkupna_cena = cena * kolicina


        # 2. Креираме нова нарачка
        cursor.execute("""
            INSERT INTO project.naracki
                (datum, status, vkupna_cena, korisnik_id)
            VALUES
                (CURRENT_TIMESTAMP, %s, %s, %s)
            RETURNING naracka_id;
        """, (
            "ZAVRSENA",
            vkupna_cena,
            session["korisnik_id"]
        ))

        naracka_id = cursor.fetchone()[0]


        # 3. Ја додаваме книгата и избраната количина
        cursor.execute("""
            INSERT INTO project.sodrzi
                (naracka_id, kniga_id, kolicina, edinechna_cena)
            VALUES
                (%s, %s, %s, %s);
        """, (
            naracka_id,
            kniga_id,
            kolicina,
            cena
        ))


        # 4. Го евидентираме плаќањето
        cursor.execute("""
            INSERT INTO project.plakanja
                (datum, iznos, nacin_na_plakjanje, status, naracka_id)
            VALUES
                (CURRENT_TIMESTAMP, %s, %s, %s, %s);
        """, (
            vkupna_cena,
            "KARTICKA",
            "USPESNO",
            naracka_id
        ))


        # 5. Ја намалуваме залихата за избраната количина
        cursor.execute("""
            UPDATE project.knigi
            SET kolicina_na_zaliha = kolicina_na_zaliha - %s
            WHERE kniga_id = %s;
        """, (
            kolicina,
            kniga_id
        ))

        conn.commit()

    except Exception as e:

        conn.rollback()
        cursor.close()
        release_db_connection(conn)

        return f"Грешка при купувањето: {e}", 500


    cursor.close()
    release_db_connection(conn)

    return redirect(
        url_for(
            "purchase_success",
            naracka_id=naracka_id
        )
    )
# --------------------------------------------------
# УСПЕШНО КУПУВАЊЕ
# --------------------------------------------------

@app.route("/purchase-success/<int:naracka_id>")
def purchase_success(naracka_id):

    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute("""
        SELECT
            n.naracka_id,
            n.datum,
            n.vkupna_cena,
            n.status,
            k.naslov,
            s.kolicina,
            p.nacin_na_plakjanje,
            p.status
        FROM project.naracki n
        JOIN project.sodrzi s
            ON n.naracka_id = s.naracka_id
        JOIN project.knigi k
            ON s.kniga_id = k.kniga_id
        JOIN project.plakanja p
            ON n.naracka_id = p.naracka_id
        WHERE n.naracka_id = %s
          AND n.korisnik_id = %s;
    """, (
        naracka_id,
        session["korisnik_id"]
    ))

    naracka = cursor.fetchone()

    cursor.close()
    release_db_connection(conn)

    return render_template(
        "purchase_success.html",
        naracka=naracka
    )


# --------------------------------------------------
# ПРЕГЛЕД НА ПРЕТХОДНИ КУПУВАЊА
# --------------------------------------------------

@app.route("/orders")
def orders():

    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute("""
        SELECT
            n.naracka_id,
            n.datum,
            n.status,
            n.vkupna_cena,
            k.naslov,
            s.kolicina,
            s.edinechna_cena
        FROM project.naracki n
        JOIN project.sodrzi s
            ON n.naracka_id = s.naracka_id
        JOIN project.knigi k
            ON s.kniga_id = k.kniga_id
        WHERE n.korisnik_id = %s
        ORDER BY n.datum DESC;
    """, (session["korisnik_id"],))

    naracki = cursor.fetchall()
    cursor.close()
    release_db_connection(conn)

    return render_template(
        "orders.html",
        naracki=naracki
    )


# --------------------------------------------------
# РЕГИСТРАЦИЈА
# --------------------------------------------------

@app.route("/register", methods=["GET", "POST"])
def register():

    if request.method == "POST":
        ime = request.form["ime"]
        prezime = request.form["prezime"]
        email = request.form["email"]
        lozinka = request.form["lozinka"]

        conn = get_db_connection()
        cursor = conn.cursor()

        cursor.execute("""
            INSERT INTO project.korisnici
                (ime, prezime, email, lozinka, datum_registracija, uloga)
            VALUES
                (%s, %s, %s, %s, CURRENT_DATE, %s)
            RETURNING korisnik_id;
        """, (
            ime,
            prezime,
            email,
            lozinka,
            "KUPUVAC"
        ))

        korisnik_id = cursor.fetchone()[0]

        conn.commit()
        cursor.close()
        release_db_connection(conn)

        session["korisnik_id"] = korisnik_id
        session["ime"] = ime
        session["prezime"] = prezime
        session["uloga"] = "KUPUVAC"

        return redirect(url_for("home"))

    return render_template("register.html")


# --------------------------------------------------
# НАЈАВА
# --------------------------------------------------

@app.route("/login", methods=["GET", "POST"])
def login():

    if request.method == "POST":
        email = request.form["email"]
        lozinka = request.form["lozinka"]

        conn = get_db_connection()
        cursor = conn.cursor()

        cursor.execute("""
            SELECT korisnik_id, ime, prezime, uloga
            FROM project.korisnici
            WHERE email = %s
              AND lozinka = %s;
        """, (email, lozinka))

        korisnik = cursor.fetchone()
        cursor.close()
        release_db_connection(conn)

        if korisnik:
            session["korisnik_id"] = korisnik[0]
            session["ime"] = korisnik[1]
            session["prezime"] = korisnik[2]
            session["uloga"] = korisnik[3]

            return redirect(url_for("home"))

        return render_template(
            "login.html",
            error="Погрешна е-пошта или лозинка."
        )

    return render_template("login.html")


# --------------------------------------------------
# ОДЈАВА
# --------------------------------------------------

@app.route("/logout")
def logout():
    session.clear()
    return redirect(url_for("home"))

# --------------------------------------------------
# МОИ ПРЕФЕРЕНЦИИ
# --------------------------------------------------

@app.route("/preferences", methods=["GET", "POST"])
def preferences():

    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    korisnik_id = session["korisnik_id"]
    conn = get_db_connection()
    cursor = conn.cursor()

    # --------------------------------------------------
    # ЗАЧУВУВАЊЕ НА ПРЕФЕРЕНЦИИТЕ
    # --------------------------------------------------

    if request.method == "POST":

        selected_zhanrovi = request.form.getlist("zhanrovi")
        selected_kategorii = request.form.getlist("kategorii")

        try:

            # ------------------------------------------
            # ЖАНРОВИ
            # ------------------------------------------

            # Ги бришеме старите жанровски преференции
            cursor.execute("""
                DELETE FROM project.preferira_zhanr
                WHERE korisnik_id = %s;
            """, (korisnik_id,))

            # Ги внесуваме новоизбраните жанрови
            for zhanr_id in selected_zhanrovi:

                cursor.execute("""
                    INSERT INTO project.preferira_zhanr
                        (korisnik_id, zhanr_id, datum_dodavanje)
                    VALUES
                        (%s, %s, CURRENT_DATE);
                """, (korisnik_id, zhanr_id))


            # ------------------------------------------
            # КАТЕГОРИИ
            # ------------------------------------------

            # Ги бришеме старите преференции за категории
            cursor.execute("""
                DELETE FROM project.preferira_kategorija
                WHERE korisnik_id = %s;
            """, (korisnik_id,))

            # Ги внесуваме новоизбраните категории
            for kategorija_id in selected_kategorii:

                cursor.execute("""
                    INSERT INTO project.preferira_kategorija
                        (korisnik_id, kategorija_id, datum_dodavanje)
                    VALUES
                        (%s, %s, CURRENT_DATE);
                """, (korisnik_id, kategorija_id))


            conn.commit()

        except Exception as e:

            conn.rollback()
            cursor.close()
            release_db_connection(conn)

            return (
                f"Грешка при зачувување на преференциите: {e}",
                500
            )

        cursor.close()
        release_db_connection(conn)

        return redirect(url_for("recommendations"))


    # --------------------------------------------------
    # ГИ ЗЕМАМЕ СИТЕ ЖАНРОВИ
    # --------------------------------------------------

    cursor.execute("""
        SELECT zhanr_id, naziv, opis
        FROM project.zhanrovi
        ORDER BY zhanr_id;
    """)

    zhanrovi = cursor.fetchall()


    # --------------------------------------------------
    # ГИ ЗЕМАМЕ ИЗБРАНИТЕ ЖАНРОВИ
    # --------------------------------------------------

    cursor.execute("""
        SELECT zhanr_id
        FROM project.preferira_zhanr
        WHERE korisnik_id = %s;
    """, (korisnik_id,))

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


    # --------------------------------------------------
    # ГИ ЗЕМАМЕ СИТЕ КАТЕГОРИИ
    # --------------------------------------------------

    cursor.execute("""
        SELECT kategorija_id, naziv, opis
        FROM project.kategorii
        ORDER BY kategorija_id;
    """)

    kategorii = cursor.fetchall()


    # --------------------------------------------------
    # ГИ ЗЕМАМЕ ИЗБРАНИТЕ КАТЕГОРИИ
    # --------------------------------------------------

    cursor.execute("""
        SELECT kategorija_id
        FROM project.preferira_kategorija
        WHERE korisnik_id = %s;
    """, (korisnik_id,))

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


    cursor.close()
    release_db_connection(conn)


    # --------------------------------------------------
    # ЈА ПРИКАЖУВАМЕ СТРАНИЦАТА
    # --------------------------------------------------

    return render_template(
        "preferences.html",
        zhanrovi=zhanrovi,
        selected_zhanrovi=selected_zhanrovi,
        kategorii=kategorii,
        selected_kategorii=selected_kategorii
    )

# --------------------------------------------------
# ADMIN
# --------------------------------------------------

@app.route("/admin")
def admin_dashboard():
    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    if session.get("uloga") != "ADMIN":
        return redirect(url_for("home"))

    return render_template("admin.html")
# --------------------------------------------------
# ADMIN - КНИГИ
# --------------------------------------------------

@app.route("/admin/books")
def admin_books():
    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    if session.get("uloga") != "ADMIN":
        return redirect(url_for("home"))

    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute("""
        SELECT
            kniga_id,
            naslov,
            isbn,
            cena,
            kolicina_na_zaliha
        FROM project.knigi
        ORDER BY kniga_id;
    """)

    knigi = cursor.fetchall()
    cursor.close()
    release_db_connection(conn)

    return render_template(
        "admin_books.html",
        knigi=knigi
    )
# --------------------------------------------------
# ADMIN - ДОДАВАЊЕ НОВА КНИГА
# --------------------------------------------------

@app.route("/admin/books/add", methods=["GET", "POST"])
def admin_add_book():
    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    if session.get("uloga") != "ADMIN":
        return redirect(url_for("home"))

    if request.method == "POST":
        naslov = request.form.get("naslov")
        isbn = request.form.get("isbn")
        opis = request.form.get("opis")
        godina_izdavanje = request.form.get("godina_izdavanje")
        kolicina_na_zaliha = request.form.get("kolicina_na_zaliha")
        cena = request.form.get("cena")

        conn = get_db_connection()
        cursor = conn.cursor()

        try:
            cursor.execute("""
                INSERT INTO project.knigi
                    (naslov, isbn, opis, godina_izdavanje,
                     kolicina_na_zaliha, cena)
                VALUES (%s, %s, %s, %s, %s, %s);
            """, (
                naslov,
                isbn,
                opis,
                godina_izdavanje,
                kolicina_na_zaliha,
                cena
            ))

            conn.commit()

        except Exception as e:
            conn.rollback()
            cursor.close()
            release_db_connection(conn)
            return f"Грешка при додавање на книгата: {e}"

        cursor.close()
        release_db_connection(conn)

        return redirect(url_for("admin_books"))

    return render_template("admin_add_book.html")
# --------------------------------------------------
# ADMIN - ИЗМЕНА НА КНИГА
# --------------------------------------------------

@app.route("/admin/books/edit/<int:kniga_id>", methods=["GET", "POST"])
def admin_edit_book(kniga_id):
    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    if session.get("uloga") != "ADMIN":
        return redirect(url_for("home"))

    conn = get_db_connection()
    cursor = conn.cursor()

    if request.method == "POST":
        naslov = request.form.get("naslov")
        isbn = request.form.get("isbn")
        opis = request.form.get("opis")
        godina_izdavanje = request.form.get("godina_izdavanje")
        kolicina_na_zaliha = request.form.get("kolicina_na_zaliha")
        cena = request.form.get("cena")

        try:
            cursor.execute("""
                UPDATE project.knigi
                SET naslov = %s,
                    isbn = %s,
                    opis = %s,
                    godina_izdavanje = %s,
                    kolicina_na_zaliha = %s,
                    cena = %s
                WHERE kniga_id = %s;
            """, (
                naslov,
                isbn,
                opis,
                godina_izdavanje,
                kolicina_na_zaliha,
                cena,
                kniga_id
            ))

            conn.commit()

        except Exception as e:
            conn.rollback()
            cursor.close()
            release_db_connection(conn)
            return f"Грешка при изменување на книгата: {e}"

        cursor.close()
        release_db_connection(conn)

        return redirect(url_for("admin_books"))

    cursor.execute("""
        SELECT
            kniga_id,
            naslov,
            isbn,
            opis,
            godina_izdavanje,
            kolicina_na_zaliha,
            cena
        FROM project.knigi
        WHERE kniga_id = %s;
    """, (kniga_id,))

    kniga = cursor.fetchone()
    cursor.close()
    release_db_connection(conn)

    if not kniga:
        return redirect(url_for("admin_books"))

    return render_template(
        "admin_edit_book.html",
        kniga=kniga
    )

# --------------------------------------------------
# ADMIN - ЖАНРОВИ
# --------------------------------------------------

@app.route("/admin/genres")
def admin_genres():
    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    if session.get("uloga") != "ADMIN":
        return redirect(url_for("home"))

    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute("""
        SELECT
            zhanr_id,
            naziv,
            opis
        FROM project.zhanrovi
        ORDER BY zhanr_id;
    """)

    zhanrovi = cursor.fetchall()
    cursor.close()
    release_db_connection(conn)

    return render_template(
        "admin_genres.html",
        zhanrovi=zhanrovi
    )

# --------------------------------------------------
# ADMIN - ДОДАВАЊЕ НОВ ЖАНР
# --------------------------------------------------

@app.route("/admin/genres/add", methods=["GET", "POST"])
def admin_add_genre():
    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    if session.get("uloga") != "ADMIN":
        return redirect(url_for("home"))

    if request.method == "POST":
        naziv = request.form.get("naziv")
        opis = request.form.get("opis")

        conn = get_db_connection()
        cursor = conn.cursor()

        try:
            cursor.execute("""
                INSERT INTO project.zhanrovi
                    (naziv, opis)
                VALUES (%s, %s);
            """, (
                naziv,
                opis
            ))

            conn.commit()

        except Exception as e:
            conn.rollback()
            cursor.close()
            release_db_connection(conn)
            return f"Грешка при додавање на жанрот: {e}"

        cursor.close()
        release_db_connection(conn)

        return redirect(url_for("admin_genres"))

    return render_template("admin_add_genre.html")

# --------------------------------------------------
# ADMIN - ИЗМЕНА НА ЖАНР
# --------------------------------------------------

@app.route("/admin/genres/edit/<int:zhanr_id>", methods=["GET", "POST"])
def admin_edit_genre(zhanr_id):
    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    if session.get("uloga") != "ADMIN":
        return redirect(url_for("home"))

    conn = get_db_connection()
    cursor = conn.cursor()

    if request.method == "POST":
        naziv = request.form.get("naziv")
        opis = request.form.get("opis")

        try:
            cursor.execute("""
                UPDATE project.zhanrovi
                SET naziv = %s,
                    opis = %s
                WHERE zhanr_id = %s;
            """, (
                naziv,
                opis,
                zhanr_id
            ))

            conn.commit()

        except Exception as e:
            conn.rollback()
            cursor.close()
            release_db_connection(conn)
            return f"Грешка при изменување на жанрот: {e}"

        cursor.close()
        release_db_connection(conn)

        return redirect(url_for("admin_genres"))

    cursor.execute("""
        SELECT
            zhanr_id,
            naziv,
            opis
        FROM project.zhanrovi
        WHERE zhanr_id = %s;
    """, (zhanr_id,))

    zhanr = cursor.fetchone()
    cursor.close()
    release_db_connection(conn)

    if not zhanr:
        return redirect(url_for("admin_genres"))

    return render_template(
        "admin_edit_genre.html",
        zhanr=zhanr
    )
# --------------------------------------------------
# ADMIN - БРИШЕЊЕ ЖАНР
# --------------------------------------------------

@app.route("/admin/genres/delete/<int:zhanr_id>", methods=["POST"])
def admin_delete_genre(zhanr_id):
    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    if session.get("uloga") != "ADMIN":
        return redirect(url_for("home"))

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        # Проверка дали жанрот е поврзан со некоја книга
        cursor.execute("""
            SELECT COUNT(*)
            FROM project.ima_zhanr
            WHERE zhanr_id = %s;
        """, (zhanr_id,))

        broj_knigi = cursor.fetchone()[0]

        if broj_knigi > 0:
            cursor.close()
            release_db_connection(conn)

            return """
                Жанрот не може да се избрише бидејќи е поврзан со една или повеќе книги.
                <br><br>
                <a href="/admin/genres">Назад кон жанровите</a>
            """

        # Бришење на жанрот
        cursor.execute("""
            DELETE FROM project.zhanrovi
            WHERE zhanr_id = %s;
        """, (zhanr_id,))

        conn.commit()

    except Exception as e:
        conn.rollback()
        cursor.close()
        release_db_connection(conn)

        return f"Грешка при бришење на жанрот: {e}"

    cursor.close()
    release_db_connection(conn)

    return redirect(url_for("admin_genres"))
# --------------------------------------------------
# ADMIN - КАТЕГОРИИ
# --------------------------------------------------

@app.route("/admin/categories")
def admin_categories():
    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    if session.get("uloga") != "ADMIN":
        return redirect(url_for("home"))

    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute("""
        SELECT
            kategorija_id,
            naziv,
            opis
        FROM project.kategorii
        ORDER BY kategorija_id;
    """)

    kategorii = cursor.fetchall()
    cursor.close()
    release_db_connection(conn)

    return render_template(
        "admin_categories.html",
        kategorii=kategorii
    )

# --------------------------------------------------
# ADMIN - ДОДАВАЊЕ НОВА КАТЕГОРИЈА
# --------------------------------------------------

@app.route("/admin/categories/add", methods=["GET", "POST"])
def admin_add_category():
    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    if session.get("uloga") != "ADMIN":
        return redirect(url_for("home"))

    if request.method == "POST":
        naziv = request.form.get("naziv")
        opis = request.form.get("opis")

        conn = get_db_connection()
        cursor = conn.cursor()

        try:
            cursor.execute("""
                INSERT INTO project.kategorii
                    (naziv, opis)
                VALUES (%s, %s);
            """, (
                naziv,
                opis
            ))

            conn.commit()

        except Exception as e:
            conn.rollback()
            cursor.close()
            release_db_connection(conn)
            return f"Грешка при додавање на категоријата: {e}"

        cursor.close()
        release_db_connection(conn)

        return redirect(url_for("admin_categories"))

    return render_template("admin_add_category.html")

# --------------------------------------------------
# ADMIN - ИЗМЕНА НА КАТЕГОРИЈА
# --------------------------------------------------

@app.route("/admin/categories/edit/<int:kategorija_id>", methods=["GET", "POST"])
def admin_edit_category(kategorija_id):
    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    if session.get("uloga") != "ADMIN":
        return redirect(url_for("home"))

    conn = get_db_connection()
    cursor = conn.cursor()

    if request.method == "POST":
        naziv = request.form.get("naziv")
        opis = request.form.get("opis")

        try:
            cursor.execute("""
                UPDATE project.kategorii
                SET naziv = %s,
                    opis = %s
                WHERE kategorija_id = %s;
            """, (
                naziv,
                opis,
                kategorija_id
            ))

            conn.commit()

        except Exception as e:
            conn.rollback()
            cursor.close()
            release_db_connection(conn)
            return f"Грешка при изменување на категоријата: {e}"

        cursor.close()
        release_db_connection(conn)

        return redirect(url_for("admin_categories"))

    cursor.execute("""
        SELECT
            kategorija_id,
            naziv,
            opis
        FROM project.kategorii
        WHERE kategorija_id = %s;
    """, (kategorija_id,))

    kategorija = cursor.fetchone()
    cursor.close()
    release_db_connection(conn)

    if not kategorija:
        return redirect(url_for("admin_categories"))

    return render_template(
        "admin_edit_category.html",
        kategorija=kategorija
    )

# --------------------------------------------------
# ADMIN - БРИШЕЊЕ КАТЕГОРИЈА
# --------------------------------------------------

@app.route("/admin/categories/delete/<int:kategorija_id>", methods=["POST"])
def admin_delete_category(kategorija_id):
    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    if session.get("uloga") != "ADMIN":
        return redirect(url_for("home"))

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        # Проверка дали категоријата е поврзана со книги
        cursor.execute("""
            SELECT COUNT(*)
            FROM project.ima_kategorija
            WHERE kategorija_id = %s;
        """, (kategorija_id,))

        broj_knigi = cursor.fetchone()[0]

        # Проверка дали категоријата е поврзана со кориснички преференции
        cursor.execute("""
            SELECT COUNT(*)
            FROM project.preferira_kategorija
            WHERE kategorija_id = %s;
        """, (kategorija_id,))

        broj_preferencii = cursor.fetchone()[0]

        if broj_knigi > 0 or broj_preferencii > 0:
            cursor.close()
            release_db_connection(conn)

            return """
                Категоријата не може да се избрише бидејќи е поврзана со книги или со кориснички преференции.
                <br><br>
                <a href="/admin/categories">Назад кон категориите</a>
            """

        # Бришење на категоријата
        cursor.execute("""
            DELETE FROM project.kategorii
            WHERE kategorija_id = %s;
        """, (kategorija_id,))

        conn.commit()

    except Exception as e:
        conn.rollback()
        cursor.close()
        release_db_connection(conn)

        return f"Грешка при бришење на категоријата: {e}"

    cursor.close()
    release_db_connection(conn)

    return redirect(url_for("admin_categories"))

# --------------------------------------------------
# ADMIN - РАСПОЛОЖЕНИЈА
# --------------------------------------------------

@app.route("/admin/moods")
def admin_moods():
    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    if session.get("uloga") != "ADMIN":
        return redirect(url_for("home"))

    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute("""
        SELECT
            raspolozenie_id,
            naziv,
            opis
        FROM project.raspolozenija
        ORDER BY raspolozenie_id;
    """)

    raspolozenija = cursor.fetchall()
    cursor.close()
    release_db_connection(conn)

    return render_template(
        "admin_moods.html",
        raspolozenija=raspolozenija
    )

# --------------------------------------------------
# ADMIN - ДОДАВАЊЕ НОВО РАСПОЛОЖЕНИЕ
# --------------------------------------------------

@app.route("/admin/moods/add", methods=["GET", "POST"])
def admin_add_mood():
    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    if session.get("uloga") != "ADMIN":
        return redirect(url_for("home"))

    if request.method == "POST":
        naziv = request.form.get("naziv")
        opis = request.form.get("opis")

        conn = get_db_connection()
        cursor = conn.cursor()

        try:
            cursor.execute("""
                INSERT INTO project.raspolozenija
                    (naziv, opis)
                VALUES (%s, %s);
            """, (
                naziv,
                opis
            ))

            conn.commit()

        except Exception as e:
            conn.rollback()
            cursor.close()
            release_db_connection(conn)
            return f"Грешка при додавање на расположението: {e}"

        cursor.close()
        release_db_connection(conn)

        return redirect(url_for("admin_moods"))

    return render_template("admin_add_mood.html")

# --------------------------------------------------
# ADMIN - ИЗМЕНА НА РАСПОЛОЖЕНИЕ
# --------------------------------------------------

@app.route("/admin/moods/edit/<int:raspolozenie_id>", methods=["GET", "POST"])
def admin_edit_mood(raspolozenie_id):
    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    if session.get("uloga") != "ADMIN":
        return redirect(url_for("home"))

    conn = get_db_connection()
    cursor = conn.cursor()

    if request.method == "POST":
        naziv = request.form.get("naziv")
        opis = request.form.get("opis")

        try:
            cursor.execute("""
                UPDATE project.raspolozenija
                SET naziv = %s,
                    opis = %s
                WHERE raspolozenie_id = %s;
            """, (
                naziv,
                opis,
                raspolozenie_id
            ))

            conn.commit()

        except Exception as e:
            conn.rollback()
            cursor.close()
            release_db_connection(conn)
            return f"Грешка при изменување на расположението: {e}"

        cursor.close()
        release_db_connection(conn)

        return redirect(url_for("admin_moods"))

    cursor.execute("""
        SELECT
            raspolozenie_id,
            naziv,
            opis
        FROM project.raspolozenija
        WHERE raspolozenie_id = %s;
    """, (raspolozenie_id,))

    raspolozenie = cursor.fetchone()
    cursor.close()
    release_db_connection(conn)

    if not raspolozenie:
        return redirect(url_for("admin_moods"))

    return render_template(
        "admin_edit_mood.html",
        raspolozenie=raspolozenie
    )

    # --------------------------------------------------
# ADMIN - БРИШЕЊЕ РАСПОЛОЖЕНИЕ
# --------------------------------------------------

@app.route("/admin/moods/delete/<int:raspolozenie_id>", methods=["POST"])
def admin_delete_mood(raspolozenie_id):
    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    if session.get("uloga") != "ADMIN":
        return redirect(url_for("home"))

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        # Проверка дали расположението е поврзано
        # со книга/податоци во izbira_raspolozenie
        cursor.execute("""
            SELECT COUNT(*)
            FROM project.izbira_raspolozenie
            WHERE raspolozenie_id = %s;
        """, (raspolozenie_id,))

        broj_izbira = cursor.fetchone()[0]

        # Проверка дали расположението е поврзано
        # во povrzana_so
        cursor.execute("""
            SELECT COUNT(*)
            FROM project.povrzana_so
            WHERE raspolozenie_id = %s;
        """, (raspolozenie_id,))

        broj_povrzuvanja = cursor.fetchone()[0]

        if broj_izbira > 0 or broj_povrzuvanja > 0:
            cursor.close()
            release_db_connection(conn)

            return """
                Расположението не може да се избрише бидејќи е поврзано со други податоци.
                <br><br>
                <a href="/admin/moods">Назад кон расположенијата</a>
            """

        # Бришење на расположението
        cursor.execute("""
            DELETE FROM project.raspolozenija
            WHERE raspolozenie_id = %s;
        """, (raspolozenie_id,))

        conn.commit()

    except Exception as e:
        conn.rollback()
        cursor.close()
        release_db_connection(conn)

        return f"Грешка при бришење на расположението: {e}"

    cursor.close()
    release_db_connection(conn)

    return redirect(url_for("admin_moods"))

# --------------------------------------------------
# ADMIN - ПРЕГЛЕД НА СИТЕ НАРАЧКИ
# --------------------------------------------------

@app.route("/admin/orders")
def admin_orders():
    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    if session.get("uloga") != "ADMIN":
        return redirect(url_for("home"))

    # Која страница ја гледаме
    page = request.args.get("page", 1, type=int)

    # По 10 нарачки на една страница
    per_page = 10

    # Од кој запис да почнеме
    offset = (page - 1) * per_page

    conn = get_db_connection()
    cursor = conn.cursor()

    # Вкупен број на нарачки
    cursor.execute("""
        SELECT COUNT(*)
        FROM project.naracki;
    """)

    total_orders = cursor.fetchone()[0]

    # Ги земаме само нарачките за тековната страница
    cursor.execute("""
        SELECT
            n.naracka_id,
            k.ime,
            k.prezime,
            k.email,
            n.datum,
            n.status,
            n.vkupna_cena
        FROM project.naracki n
        JOIN project.korisnici k
            ON n.korisnik_id = k.korisnik_id
        ORDER BY n.datum DESC, n.naracka_id DESC
        LIMIT %s OFFSET %s;
    """, (per_page, offset))

    naracki = cursor.fetchall()

    cursor.close()
    release_db_connection(conn)

    # Колку страници вкупно имаме
    total_pages = (total_orders + per_page - 1) // per_page

    return render_template(
        "admin_orders.html",
        naracki=naracki,
        page=page,
        total_pages=total_pages
    )

# --------------------------------------------------
# ADMIN - ПРЕГЛЕД НА СИТЕ ПЛАЌАЊА
# --------------------------------------------------

@app.route("/admin/payments")
def admin_payments():
    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    if session.get("uloga") != "ADMIN":
        return redirect(url_for("home"))

    # Која страница ја гледаме
    page = request.args.get("page", 1, type=int)

    # По 10 плаќања на една страница
    per_page = 10

    # Од кој запис да почнеме
    offset = (page - 1) * per_page

    conn = get_db_connection()
    cursor = conn.cursor()

    # Вкупен број на плаќања
    cursor.execute("""
        SELECT COUNT(*)
        FROM project.plakanja;
    """)

    total_payments = cursor.fetchone()[0]

    # Ги земаме само плаќањата за тековната страница
    cursor.execute("""
        SELECT
            p.plakjanje_id,
            p.naracka_id,
            k.ime,
            k.prezime,
            k.email,
            p.datum,
            p.iznos,
            p.nacin_na_plakjanje,
            p.status
        FROM project.plakanja p
        JOIN project.naracki n
            ON p.naracka_id = n.naracka_id
        JOIN project.korisnici k
            ON n.korisnik_id = k.korisnik_id
        ORDER BY p.datum DESC, p.plakjanje_id DESC
        LIMIT %s OFFSET %s;
    """, (per_page, offset))

    plakanja = cursor.fetchall()

    cursor.close()
    release_db_connection(conn)

    # Колку страници вкупно имаме
    total_pages = (total_payments + per_page - 1) // per_page

    return render_template(
        "admin_payments.html",
        plakanja=plakanja,
        page=page,
        total_pages=total_pages
    )
# --------------------------------------------------
# ADMIN - БРИШЕЊЕ КНИГА
# --------------------------------------------------

@app.route("/admin/books/delete/<int:kniga_id>", methods=["POST"])
def admin_delete_book(kniga_id):
    if "korisnik_id" not in session:
        return redirect(url_for("login"))

    if session.get("uloga") != "ADMIN":
        return redirect(url_for("home"))

    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        # Проверка дали книгата постои во некоја нарачка
        cursor.execute("""
            SELECT COUNT(*)
            FROM project.sodrzi
            WHERE kniga_id = %s;
        """, (kniga_id,))

        broj_naracki = cursor.fetchone()[0]

        if broj_naracki > 0:
            cursor.close()
            release_db_connection(conn)
            return """
                Книгата не може да се избрише бидејќи
                е дел од една или повеќе нарачки.
                <br><br>
                <a href="/admin/books">Назад кон книгите</a>
            """

        # Бришење на врските со книгата
        cursor.execute("""
            DELETE FROM project.omileni
            WHERE kniga_id = %s;
        """, (kniga_id,))

        cursor.execute("""
            DELETE FROM project.napisana_od
            WHERE kniga_id = %s;
        """, (kniga_id,))

        cursor.execute("""
            DELETE FROM project.ima_zhanr
            WHERE kniga_id = %s;
        """, (kniga_id,))

        cursor.execute("""
            DELETE FROM project.ima_kategorija
            WHERE kniga_id = %s;
        """, (kniga_id,))

        cursor.execute("""
            DELETE FROM project.povrzana_so
            WHERE kniga_id = %s;
        """, (kniga_id,))

        # Бришење на самата книга
        cursor.execute("""
            DELETE FROM project.knigi
            WHERE kniga_id = %s;
        """, (kniga_id,))

        conn.commit()

    except Exception as e:
        conn.rollback()
        cursor.close()
        release_db_connection(conn)
        return f"Грешка при бришење на книгата: {e}"

    cursor.close()
    release_db_connection(conn)

    return redirect(url_for("admin_books"))

# --------------------------------------------------
# СТАРТУВАЊЕ НА FLASK
# --------------------------------------------------

if __name__ == "__main__":
    app.run(debug=True, use_reloader=False)