package eu.glowacki.utp.assignment10.repositories;

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

import eu.glowacki.utp.assignment10.dtos.GroupDTO;
import eu.glowacki.utp.assignment10.dtos.UserDTO;

public class UserRepository implements IUserRepository {

    private Connection con;
    Savepoint save;

    {
        con = getConnection();
        try {
            con.setAutoCommit(false);
        } catch (SQLException e) {
        }
        beginTransaction();
        System.out.println("user repository has been constructed.");

    }

    @Override
    public void add(UserDTO user) {
        String sql = "insert into users values (?, ?, ?)";
        try {
            PreparedStatement addUser = con.prepareStatement(sql);
            addUser.setInt(1, user.getId());
            addUser.setString(2, user.getLogin());
            addUser.setString(3, user.getPassword());
            addUser.executeUpdate();

            if ((user.getGroups() != null) && !user.getGroups().isEmpty()) {
                for (GroupDTO u : user.getGroups()) {
                    sql = "insert into groups_users values(?, ?)";
                    addUser = con.prepareStatement(sql);
                    addUser.setInt(1, user.getId());
                    addUser.setInt(2, u.getId());
                    try {
                        addUser.executeUpdate();
                    } catch (SQLException e) {
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            rollbackTransaction();
        }
    }

    @Override
    public void update(UserDTO dto) {
        String sql = "update users set user_login=? where user_id=?";
        try {
            PreparedStatement updateUser = con.prepareStatement(sql);
            updateUser.setString(1, dto.getLogin());
            updateUser.setInt(1, dto.getId());
            updateUser.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            System.err.println("Failed to execute the sql update statement");
            rollbackTransaction();
        }
    }

    @Override
    public void addOrUpdate(UserDTO dto) {
        if (exists(dto))
            update(dto);
        else
            add(dto);
    }

    @Override
    public void delete(UserDTO dto) {
        String sql = "delete from users where user_id=?";
        try {
            PreparedStatement delete = con.prepareStatement(sql);
            delete.setInt(1, dto.getId());
            System.out.println("Just before exec in delete");
            delete.executeUpdate();
            if ((dto.getGroups() != null) && !dto.getGroups().isEmpty()) {
                sql = "delete from users_groups where user_id=?";
                delete = con.prepareStatement(sql);
                delete.setInt(1, dto.getId());
                try {
                    delete.executeUpdate();
                } catch (SQLException e) {
                }

            }
        } catch (SQLException e) {
            rollbackTransaction();
            System.err.println("Failed to execute the sql delete statement, cause: " + e.getCause());
        }

    }

    @Override
    public UserDTO findById(int id) {
        String sql = "Select * from users where user_id = ?";
        PreparedStatement find;
        try {
            find = con.prepareStatement(sql);
            find.setInt(1, id);
            ResultSet rs = find.executeQuery();
            return new UserDTO(rs.getInt(1), rs.getString(2), rs.getString(3));
        } catch (SQLException e) {

            rollbackTransaction();
            return null;
        }
    }

    @Override
    public void beginTransaction() {
        try {
            save = con.setSavepoint();
        } catch (SQLException e) {
            System.err.println("Failed to set a savepoint");
            rollbackTransaction();
        }

    }

    @Override
    public void commitTransaction() {
        try {
            con.commit();
        } catch (SQLException e) {
            System.err.println("Failed to commit the transaction");
        }
    }

    @Override
    public void rollbackTransaction() {
        try {
            con.rollback();
        } catch (SQLException e) {
            System.err.println("Failed to rollback");
        }
    }

    @Override
    public int getCount() {
        String sql = "Select count(user_id) from users";
        try {
            PreparedStatement count = con.prepareStatement(sql);
            return count.executeQuery().getInt(1);
        } catch (SQLException e) {
        }
        rollbackTransaction();
        return 0;
    }

    @Override
    public boolean exists(UserDTO dto) {

        String sql = "select * from users where user_id=?";

        try {
            PreparedStatement check = con.prepareStatement(sql);
            return check.execute();
        } catch (SQLException e) {
            System.err.println("Couldn't find out whether or not dto " + dto + " exists");
        }

        return false;
    }

    @Override
    public Connection getConnection() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            return DriverManager.getConnection("jdbc:oracle:thin:@db-oracle.pjwstk.edu.pl:1521:baza", "s16766",
                    "oracle12");
        } catch (ClassNotFoundException e) { // driver class could not be loaded
            System.out.println("Driver could not be loaded");
            System.out.println(e);
            System.exit(1);

        } catch (SQLException e) { // connection could not be established
            System.out.println("Connection could not be established");
            System.out.println(e);
            System.exit(1);
        }
        return null;
    }

    @Override
    public List<UserDTO> findByName(String username) {
        String sql = "Select * from users where user_login=?";
        List<UserDTO> found = new LinkedList<>();
        PreparedStatement find;
        try {
            find = con.prepareStatement(sql);
            find.setString(1, username);
            ResultSet results = find.executeQuery();
            while (results.next()) {
                found.add(new UserDTO(results.getInt(1), results.getString(2), results.getString(3)));
            }
        } catch (SQLException e) {
        }
        return found;
    }

}