package database.api;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import database.model.User;

public class DBApi {

    public static final String DRIVER = "org.sqlite.JDBC";
    public static final String DB_URL = "jdbc:sqlite:users_table.db";

    private Connection conn;
    private Statement stat;


    /*******************
    ***** C-TOR *****
    ******************/
    /* 1) Loading driver to system
     * 2) Creating connection with DB
     * Here is type and name of DB
     * 3) Creating object 'stat' of class Statement
     *  - it allows to execute queries base on
     *    earlier defined Strings
    */
    public DBApi(){
        try {
            Class.forName(DBApi.DRIVER); //1
        } catch (ClassNotFoundException e) {
            System.err.println("Brak sterownika JDBC");
            e.printStackTrace();
        }
        try {
            conn = DriverManager.getConnection(DB_URL); //2
            stat = conn.createStatement(); //3
        } catch (SQLException e) {
            System.err.println("Problem z otwarciem polaczenia");
            e.printStackTrace();
        }

        createTables();
    }

    /*******************
     ** CREATE TABLES **
     ******************/
    ///table name: users_table
    ///fields id, login, password, username, privileges
    public boolean createTables()  {
        String createUsers = "CREATE TABLE IF NOT EXISTS users_table (id INTEGER PRIMARY KEY AUTOINCREMENT, "
                + "login varchar(255), password varchar(255), username varchar(255), privileges INTEGER)";

        try {
            stat.execute(createUsers);
        } catch (SQLException e) {
            System.err.println("Blad przy tworzeniu tabeli");
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /*******************
     ** INSERT USERS **
     ******************/
    public boolean insertUser(String login, String password,
                              String username, int privileges) {
        try {
            //PreparedStatement class allows to create
            //schema of query which need to be execute
            //and matching appropriate values of values
            //using as arguments method
            PreparedStatement prepStmt = conn.prepareStatement(
                    "insert into users_table values (NULL, ?, ?, ?, ?);");
            prepStmt.setString(1, login);
            prepStmt.setString(2, password);
            prepStmt.setString(3, username);
            prepStmt.setInt(4, privileges);
            System.out.println("Wstawiam usera login: " + login +
                    " password: " + password + " username: " + username
                      + "privileges: " + privileges);
            prepStmt.execute();
        } catch (SQLException e) {
            System.err.println("Blad przy wstawianiu uzytkownika!");
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /*******************
     ** SELECT USERS **
     ******************/
    //method return list of all records from table
    public List<User> selectUsers(){
        List<User> users = new LinkedList<User>();
        try{
            ResultSet result = stat.executeQuery("SELECT * FROM users_table");
            int id, privileges;
            String login, password, username;
            while(result.next()){
                id = result.getInt("id");
                login = result.getString("login");
                password = result.getString("password");
                username = result.getString("username");
                privileges = result.getInt("privileges");
                users.add(new User(id, login, password, username, privileges));
            }
        }catch(SQLException e){
            e.printStackTrace();
            return null;
        }
        return users;
    }

    /*******************
     ** DELETE USER **
     ******************/
    public List<User> deletetUser(String l){

        List<User> users = new LinkedList<User>();

        users = selectUsers();
        boolean isUserFound = false;

        //check if choosen user exists
        for(User u:users){
            System.out.println(u);
            if ( l.equals(u.getLogin()) ){
                System.out.println("login kasowanego usera: " + l);
                isUserFound = true;
            }
        }

        if(isUserFound){
            ///user will be deleted from db
            try{

                String deleteUser = "DELETE FROM users_table WHERE login='"+l+"'";
                stat.execute(deleteUser);
                System.out.println("DELETE USER command executed!");
            }catch(SQLException e){
                e.printStackTrace();
                return null;
            }
            ///but it still exist at list, so..
            users = selectUsers();

            ///so now users contains list without deleted user
            return users;
        }
        else{
            System.out.println("Nie znaleziono takiego usera");
            return users;
        }
    }

    /**********************
     **** DELETE TABLE ****
     *********************/
    public boolean deleteTable(){

        String dropTable = "DROP TABLE users_table";
        try {
            stat.execute(dropTable);
            System.out.println("DROP command executed!");
        } catch (SQLException e) {
            System.err.println("Blad przy DROP!");
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /**********************
     ** DELETE ALL USERS **
     *********************/
    public boolean deleteAllUsers(){

        String deleteUsers = "DELETE FROM users_table";

        try {
            stat.execute(deleteUsers);
            System.out.println("DELETE command executed!");
        } catch (SQLException e) {
            System.err.println("Blad przy usuwaniu!");
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /*******************
     ** CLOSE CONNECTION **
     ******************/
    public void closeConnection() {
        try {
            conn.close();
        } catch (SQLException e) {
            System.err.println("Problem z zamknieciem polaczenia");
            e.printStackTrace();
        }
    }
}