package sample;

import java.beans.Statement;
import java.sql.*;

public class DatabaseHandler extends Configs {
    Connection dbConnection;

    public Connection getDbConnection() throws ClassNotFoundException, SQLException{
        String connectionString = "jdbc:mysql://" + dbHost + ":" + dbPort + "/" + dbName;

        Class.forName("com.mysql.cj.jdbc.Driver");

        dbConnection = DriverManager.getConnection(connectionString, dbUser, dbPass);
        return dbConnection;
    }

    public void signUpUser(String firstname, String lastname, String username, String password, String usertype){
        String insert = "INSERT INTO " + Const.USERS_TABLE + "("
                + Const.USERS_FIRSTNAME + "," + Const.USERS_LASTNAME + ","
                + Const.USERS_USERNAME + "," + Const.USERS_PASSWORD + "," + Const.USERS_TYPE + ")"
                + "VALUES(?,?,?,?,?)";
        try {
            PreparedStatement prSt = getDbConnection().prepareStatement(insert);
            prSt.setString(1, firstname);
            prSt.setString(2, lastname);
            prSt.setString(3, username);
            prSt.setString(4, password);
            prSt.setString(5, usertype);

            System.out.println(insert);

            prSt.executeUpdate();
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }
    }

    public String[][] marketingCategory(){

        String count = "SELECT count(name) FROM course.marketing_category";
        int len = 0;

        try {
            PreparedStatement ps = getDbConnection().prepareStatement(count);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                len = rs.getInt(1);
            }
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }

        System.out.println(len);

        String select = "SELECT name, amountUsers FROM course.marketing_category\n" +
                        "ORDER BY amountUsers DESC;";

        String[][] rows = new String[len][2];
        int i = 0;

        try {
            PreparedStatement ps = getDbConnection().prepareStatement(select);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                rows[i][0] = rs.getString(1);
                rows[i++][1] = rs.getString(2);
            }
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }

        for (String[] row: rows){
            for (String el: row){
                System.out.print(el + "      ");
            }
            System.out.println();
        }

        return rows;
    }

    public boolean checkAuth(String login, String password){
        String select = "SELECT * FROM " + Const.USERS_TABLE + " WHERE "
                + Const.USERS_USERNAME + " LIKE \"" + login + "\" AND "
                + Const.USERS_PASSWORD + " = \"" + password + "\"";
        System.out.println(select);

        int res = 0;

        try {
            PreparedStatement ps = getDbConnection().prepareStatement(select);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                System.out.println(rs.getString(Const.USERS_ID));
                res += 1;
                User.user_id = rs.getString(Const.USERS_ID);
                User.user_firstname = rs.getString(Const.USERS_FIRSTNAME);
                User.user_lastname = rs.getString(Const.USERS_LASTNAME);
                User.user_username = rs.getString(Const.USERS_USERNAME);
                User.user_password = rs.getString(Const.USERS_PASSWORD);
                User.user_type = rs.getString(Const.USERS_TYPE);
            }
        } catch (SQLException | ClassNotFoundException throwables) {
            throwables.printStackTrace();
        }

        return res == 1;
    }
}