package frames;
import java.awt.Color;
import java.sql.*;

import javax.swing.DefaultComboBoxModel;
import javax.swing.JComboBox;
import javax.swing.JLabel;
import javax.swing.JLayeredPane;
import javax.swing.JPanel;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

import net.proteanit.sql.DbUtils;

public class MainClass
{
    static Connection con = DBConnection.doConnection();
    static Statement stmt = null;
    static ResultSet res = null;

    public static ResultSet select (String query)
    {
        try
        {
            stmt = con.createStatement();
            res = stmt.executeQuery(query);

            return res;
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());

            return null;
        }
    }

    //na vyn String per arsye se clickedID osht string, edhe nuk kemi nevoje me e kompliku shume
    public static String getHeadAdminID ()
    {
        ResultSet test = MainClass.select("select S.id from Staff s where s.priviledge=1");

        try
        {
            while (test.next())
            {
                return test.getString(1);
            }
        }
        catch (Exception ex)
        {
        }

        return null;
    }

    public static boolean exec (String query)
    {
        try
        {
            stmt = con.createStatement();
            stmt.executeUpdate(query);

            return true;
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());

            return false;
        }
    }

    public static String getCIDfromUsername(String username)
    {
        ResultSet queried = MainClass.select("select C.id from Clients C where C.login.username='"+username+"'");

        try
        {
            while (queried.next())
            {
                String id = queried.getString("id");

                return id;
            }
        }
        catch (Exception ex)
        {
            ex.getMessage();
        }

        return null;
    }

    public static int specialSelect (String query)
    {
        try
        {
            PreparedStatement statement = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
                statement.execute();
                PreparedStatement ps = con.prepareStatement("select sells_table_seq.currval from dual");

                ResultSet rs = ps.executeQuery();
                int generatedKey;
                if (rs.next())
                {
                    generatedKey = (int) rs.getLong(1);

                    return generatedKey;
                }
    }
        catch (Exception ex)
        {
            ex.getMessage();
        }

        return -1;
    }

    public static int[] verifyLogin (String query, String username, String password, JLabel label, int isStaff)
    {
        ResultSet partTable = select (query);
        int[] arr = new int[10];
        arr[0] = -1;
        arr[1] = -1;

        try
        {
            while (partTable.next())
            {
                int id = Integer.parseInt(partTable.getString("id"));
                String user = partTable.getString("login.username");
                String pass = partTable.getString("login.password");

                if (username.equals(user) && password.equals(pass))
                {
                    if (isStaff == 1)
                    {
                        int priv = Integer.parseInt(partTable.getString("priviledge"));

                        arr[0] = id;
                        arr[1] = priv;
                    }
                    else
                    {
                        arr[0] = id;
                    }
                }
                else
                {
                    label.setForeground(Color.red);
                    label.setText("Invalid login information");
                }
            }
        }
        catch(Exception ex)
        {
            return arr;
        }

        return arr;
    }

    public static void ClearLayers (JLayeredPane layeredPane, JPanel panel)
    {
        layeredPane.removeAll();
        layeredPane.add(panel);
        layeredPane.repaint();
        layeredPane.revalidate();
    }

    public static void viewIt (String query, JTable table)
    {
        ResultSet tryIt = select (query);

        table.setModel(DbUtils.resultSetToTableModel(tryIt));
    }

    public static void fetchCities (JComboBox box)
    {
        ResultSet query = select ("select * from Cities");

        try
        {
            while (query.next())
            {
                String emri = query.getString("name");

                box.addItem(emri);
            }
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }
    }

    public static void clearModel (DefaultTableModel model)
    {
        for (int i = model.getRowCount() - 1; i >= 0; i--)
        {
            model.removeRow(i);
        }
    }

    public static void fetchReturned (JComboBox box)
    {
        ResultSet query = select ("select * from ReturnedType");

        try
        {
            while (query.next())
            {
                String emri = query.getString("type");

                box.addItem(emri);
            }
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }
    }

    public static void fetchPayments (JComboBox box)
    {
        ResultSet query = select ("select * from PaymentType");

        try
        {
            while (query.next())
            {
                String emri = query.getString("type");

                box.addItem(emri);
            }
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }
    }

    public static int getBookComponent (String component, int id) //amount, price
    {
        try
        {
            CallableStatement cstmt = con.prepareCall("{? = call getBookComponent(?, ?)}");
            cstmt.registerOutParameter(1, Types.INTEGER);
            cstmt.setString(2, component);
            cstmt.setInt(3, id);
            cstmt.executeUpdate();

            return cstmt.getInt(1);
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());

            return -1;
        }

    }

    public static String getBookSells (String id)
    {
        ResultSet queried = MainClass.select("select count(*) \"Nr\" from Sells where bookid='"+id+"'");

        try
        {
            while (queried.next())
            {
                String times = queried.getString("Nr");

                return times;
            }
        }
        catch (Exception ex)
        {
        }

        return null;
    }

    public static String getBookBorrows (String id)
    {
        ResultSet queried = MainClass.select("select count(*) \"Nr\" from forRent where bookid='"+id+"'");

        try
        {
            while (queried.next())
            {
                String times = queried.getString("Nr");

                return times;
            }
        }
        catch (Exception ex)
        {
        }

        return null;
    }

    public static int getPenaltyTimes (int clientID)
    {
        try
        {
            CallableStatement cstmt = con.prepareCall("{? = call getClientPenaltyTimes(?)}");

            cstmt.registerOutParameter(1, Types.INTEGER);
            cstmt.setInt(2, clientID);
            cstmt.executeUpdate();
            int times = cstmt.getInt(1);

            return times;

        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());

            return -1;
        }
    }

    public static int getClientComponent(String component, int id) //currentlyRenting, penaltyTimes
    {
        try
        {
            CallableStatement cstmt = con.prepareCall("{? = call getClientComponent(?, ?)}");
            cstmt.registerOutParameter(1, Types.INTEGER);
            cstmt.setString(2, component);
            cstmt.setInt(3, id);
            cstmt.executeUpdate();

            return cstmt.getInt(1);
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());

            return -1;
        }
    }

    public static String getClientBuyCount(String id)
    {
        ResultSet query = MainClass.select("select count(*) \"Nr\" from Sells S where S.clientid="+id+" and S.approved=1");

        try
        {
            while (query.next())
            {
                String times = query.getString("Nr");

                return times;
            }
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }

        return null;
    }

    public static String getClientRentCount(String id)
    {
        ResultSet query = MainClass.select("select count(*) \"Nr\" from forRent R where R.clientid="+id);

        try
        {
            while (query.next())
            {
                String times = query.getString("Nr");

                return times;
            }
        }
        catch (Exception ex)
        {
            System.out.println(ex.getMessage());
        }

        return null;
    }
}