package edu.hsog.db;

import javax.swing.*;
import java.lang.reflect.Type;
import java.sql.*;
import java.util.ArrayList;

public class DBQueries {

    // Count ohne try-with-resource und mit statement
    public static int count() {

        Connection con = Globals.getPoolConnection();
        Statement st = null;
        ResultSet rs = null;
        try {
            st = con.createStatement();
            String q = "select count(*)\n" +
                    "from gadgets";
            rs = st.executeQuery(q);
            rs.next();
            return rs.getInt(1);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (rs != null) rs.close();
                if (st != null) st.close();
                if (con != null) con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    // Count mit try-with-resource und mit statement
    /*
    public static int count() {
    String query = "SELECT COUNT(*) FROM gadgets";

    try (Connection con = Globals.getPoolConnection();
         Statement st = con.createStatement();
         ResultSet rs = st.executeQuery(query)) {

        rs.next(); // Kein if, da COUNT(*) immer ein Ergebnis liefert
        return rs.getInt(1);

    } catch (SQLException e) {
        throw new RuntimeException("Error while counting gadgets", e);
    }
    */

    // Count mit try-with-resource und mit prepared-statement
    /*
    public static int count() {
        String query = "SELECT COUNT(*) FROM gadgets";

        try (Connection con = Globals.getPoolConnection();
             PreparedStatement pst = con.prepareStatement(query);
             ResultSet rs = pst.executeQuery()) {

            rs.next(); // Kein if, da COUNT(*) immer ein Ergebnis liefert
            return rs.getInt(1);

        } catch (SQLException e) {
            throw new RuntimeException("Error while counting gadgets", e);
        }
    }
    */

    // Login mit try-with-resource und mit statement
    /*
    public static boolean login(String username, String password) {

        Connection con = Globals.getPoolConnection();
        Statement st = null;
        ResultSet rs = null;
        try {
            st = con.createStatement();

            String q = "select count(*)\n" +
                    "from users\n" +
                    "where email = '" + username + "' and passwd = '" + password + "'";
            rs = st.executeQuery(q);
            rs.next();
            int c = rs.getInt(1);
            return (c == 1);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (rs != null) rs.close();
                if (st != null) st.close();
                if (con != null) con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    */

    // Login mit try-with-resource und mit prepared-statement
    public static boolean login(String username, String password) {
        String query = "SELECT COUNT(*) FROM users WHERE email = ? AND passwd = ?";

        try (Connection con = Globals.getPoolConnection();
             PreparedStatement pst = con.prepareStatement(query)) {

            // Parameter setzen
            pst.setString(1, username);
            pst.setString(2, password);

            try (ResultSet rs = pst.executeQuery()) {
                rs.next(); // Kein if notwendig, COUNT(*) liefert immer eine Zeile
                int count = rs.getInt(1);
                return count == 1;
            }

        } catch (SQLException e) {
            throw new RuntimeException("Error during login attempt", e);
        }
    }

    public static ArrayList showAll() {
        String query = "SELECT  *  FROM gadgets";
        ArrayList icons = new ArrayList<Icon>();
        try (Connection con = Globals.getPoolConnection();
             PreparedStatement pst = con.prepareStatement(query)) {

            // Parameter setzen
            // pst.setString(1, username);
            try (ResultSet rs = pst.executeQuery()) {
                while (rs.next()) {
                    Icon i = Converter.blob2Icon(rs.getBlob("cover"));
                    icons.add(i);
                }
                return icons;
            }
        } catch (SQLException e) {
            throw new RuntimeException("Error during login attempt", e);
        }
    }

    public static ImageIcon getImage(int z) {
        String query = "SELECT * from gadgets order by url asc";

        ImageIcon imIcon;

        try (Connection con = Globals.getPoolConnection();
             PreparedStatement pst = con.prepareStatement(query)){
             ResultSet rs = pst.executeQuery();

             for(int i = 0; i<z;i++) {
                 rs.next();
             }
             Blob b = rs.getBlob("COVER");
             imIcon = Converter.blob2ImageIcon(b);

             }catch (SQLException e) {
            throw new RuntimeException("Error while counting gadgets", e);
        }
        return imIcon;
    }

    public static ImageIcon getImageByUrl(String partialUrl) {
        String query = "SELECT * FROM gadgets WHERE url LIKE ?";

        ImageIcon imIcon = null;

        try (Connection con = Globals.getPoolConnection();
             PreparedStatement pst = con.prepareStatement(query)) {

            pst.setString(1, "%" + partialUrl + "%");  // Teilstring-Suche
            ResultSet rs = pst.executeQuery();

            if (rs.next()) {
                Blob b = rs.getBlob("COVER");
                imIcon = Converter.blob2ImageIcon(b);
            } else {
                System.out.println("Keine passende URL gefunden für: " + partialUrl);
            }

        } catch (SQLException e) {
            throw new RuntimeException("Fehler beim Abrufen des Bildes für URL-Teil: " + partialUrl, e);
        }

        return imIcon;
    }
    public static ImageIcon getImageByDesc(String desc) {
        String query = "SELECT * FROM gadgets WHERE DESCRIPTION = ?";

        ImageIcon imIcon = null;

        try (Connection con = Globals.getPoolConnection();
             PreparedStatement pst = con.prepareStatement(query)) {

            pst.setString(1, desc);
            ResultSet rs = pst.executeQuery();

            if (rs.next()) {
                Blob b = rs.getBlob("COVER");
                imIcon = Converter.blob2ImageIcon(b);
            }

        } catch (SQLException e) {
            throw new RuntimeException("Fehler beim Abrufen des Bildes für URL: " + desc, e);
        }

        return imIcon;
    }

    public static void insertImageUrlEmail(String url, String email) {
        String query = "insert into gadgets(url,email,cover)values(?,?,?)";
        Icon icon = Converter.generatePatternIcon();

        try (Connection con = Globals.getPoolConnection();
             PreparedStatement pst = con.prepareStatement(query)) {
            Blob coverBlob = Converter.icon2Blob(icon,con);
            // Parameter setzen
            pst.setString(1, url);
            pst.setString(2, email);
            pst.setBlob(3,coverBlob);

            pst.executeQuery();

        } catch (SQLException e) {
            throw new RuntimeException("Error during login attempt", e);
        }
    }

    public static void updateItemLikeKeywords(String eingabe) {
        String query = "update gadgets g set g.cover = ? where keywords like ?";
        Icon icon = Converter.generatePatternIcon();

        try (Connection con = Globals.getPoolConnection();
             PreparedStatement pst = con.prepareStatement(query)) {

            // Parameter setzen
            pst.setBlob(1,Converter.icon2Blob(icon,con));
            pst.setString(2, eingabe);

           pst.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("Error during login attempt", e);
        }
    }

    public static ArrayList<Icon> iconListEmail(String username) {
        String query = "SELECT cover from gadgets where email = ?";
        Icon icon = Converter.generatePatternIcon();
        ArrayList<Icon>myList = new ArrayList<>();
        try (Connection con = Globals.getPoolConnection();
             PreparedStatement pst = con.prepareStatement(query)) {

            pst.setString(1,username);
            ResultSet rs = pst.executeQuery();

            while (rs.next()){

                icon = Converter.blob2Icon(rs.getBlob("cover"));
                myList.add(icon);

            }
            return myList;
        } catch (SQLException e) {
            throw new RuntimeException("Error during login attempt", e);
        }
    }


    public static int searchGadgetsUrlIndex(String url) {

        Connection con = Globals.getPoolConnection();
        Statement st = null;
        ResultSet rs = null;
        try {
            st = con.createStatement();
            String q = "SELECT * from gadgets";
            rs = st.executeQuery(q);
            int lineCounter = 1;
            String foundURL = null;

            while (rs.next()){
                if(rs.getString(1).contains(url)){
                    foundURL = url;
                    break;
                }
                lineCounter ++;
            }
            if(foundURL != null){
                return lineCounter;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (rs != null) rs.close();
                if (st != null) st.close();
                if (con != null) con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return -1;
    }

    public static String zeilenSpalten(int z, int col) {
        String query = "select * from bewertung order by url asc";

        try (Connection con = Globals.getPoolConnection();
             Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
             ResultSet rs = st.executeQuery(query )) {

            rs.next();
            // Parameter setzen
            rs.absolute(z);

            if(col== 1){
                return rs.getString("email");
            }
            if(col== 2){
                return rs.getString("url");
            }
            if(col== 3){
                return rs.getString("bewertung");
            }
            if(col== 4){
                return rs.getString("kommentars");
            }
            } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }


        return "..";
    }


    public static DTO emailPassword(String teil) {
        String query = "SELECT EMAIL,PASSWD FROM users WHERE email like ? ";

        try (Connection con = Globals.getPoolConnection();
             PreparedStatement pst = con.prepareStatement(query)) {

            // Parameter setzen
            pst.setString(1,"%"+ teil+"%");


            ResultSet rs = pst.executeQuery();
            rs.next(); // Kein if notwendig, COUNT(*) liefert immer eine Zeile

            String email = rs.getString("EMAIL");
            String passwd = rs.getString("PASSWD");

            return new DTO(email,passwd);

        } catch (SQLException e) {
            throw new RuntimeException("Error during login attempt", e);
        }
    }

    public static int sumGefallenUrl(String eingabe) {

        Connection con = Globals.getPoolConnection();
        Statement st = null;
        ResultSet rs = null;
        try {
            st = con.createStatement();
            String q = "select sum(gefallen) from bewertung where url like '%" + eingabe +"%' ";
            rs = st.executeQuery(q);

            if(rs.next()){
                return rs.getInt(1);
            }else {
                return 0;
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (rs != null) rs.close();
                if (st != null) st.close();
                if (con != null) con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }


    public static String firstKommentar (String eingabe) {
        String query = "SELECT kommentar from bewertung order by kommentar asc";
        int i = Integer.valueOf(eingabe);

        try (Connection con = Globals.getPoolConnection();
             Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);

            ResultSet rs = st.executeQuery(query)) {
            rs.absolute(i);
            String kommentar = rs.getString("kommentar");
            return kommentar;


        } catch (SQLException e) {
            throw new RuntimeException("Error during login attempt", e);
        }
    }

    public static String Kommentar(String eingabe) {
        Connection con = Globals.getPoolConnection();
        Statement st = null;
        ResultSet rs = null;
        StringBuilder result = new StringBuilder();

        try {
            st = con.createStatement();
            String q = "Select Kommentar from Bewertung where email Like '" + eingabe + "%'";
            rs = st.executeQuery(q);

            while (rs.next()) {
                System.out.println("Erfolg");
                result.append(rs.getString("Kommentar")).append("\n");
            }

            if (result.length() != 0)
                return result.toString();
            else
                return "Kein Kommentar gefunden";

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (rs != null) rs.close();
                if (st != null) st.close();
                if (con != null) con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }



}