package com.uca.dao;

import com.uca.entity.UserEntity;
import com.uca.exceptions.ServiceException;

import java.sql.*;
import java.util.ArrayList;

public class UserDAO extends _Generic<UserEntity> {

    public ArrayList<UserEntity> getAll() throws ServiceException {
        try {
            ArrayList<UserEntity> users = new ArrayList<>();
            PreparedStatement stm = this.connect.prepareStatement("SELECT * FROM users ORDER BY id ASC;");

            ResultSet res = stm.executeQuery();
            while (res.next()) {
                users.add(parseFromResult(res));
            }

            return users;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new ServiceException("Could not get all users.");
        }
    }

    @Override
    public UserEntity getById(int id) throws ServiceException {
        try {
            PreparedStatement stm = this.connect.prepareStatement("SELECT * FROM users WHERE id = ? LIMIT 1;");
            stm.setInt(1, id);

            ResultSet res = stm.executeQuery();
            res.next();

            return parseFromResult(res);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new ServiceException("Could not get get user with id " + id);
        }
    }

    public UserEntity getByIdentifier(String identifier) throws ServiceException {
        try {
            PreparedStatement stm = this.connect.prepareStatement("SELECT * FROM users WHERE identifier=? LIMIT 1;");
            stm.setString(1, identifier);

            ResultSet res = stm.executeQuery();
            res.next();

            return  parseFromResult(res);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new ServiceException("Could not get get user with identifier " + identifier);
        }
    }

    @Override
    public UserEntity create(UserEntity obj) throws ServiceException{
        try {
            PreparedStatement stm = this.connect.prepareStatement("INSERT INTO users(firstname, lastname, identifier, password, last_connection, upgrades) VALUES(?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
            stm.setString(1, obj.getFirstName());
            stm.setString(2, obj.getLastName());
            stm.setString(3, obj.getIdentifier());
            stm.setString(4, obj.getPassword());
            stm.setDate(5, Date.valueOf(obj.getLastConnection()));
            stm.setInt(6, obj.getNbUpgrades());
            stm.executeUpdate();

            ResultSet res = stm.getGeneratedKeys();
            res.next();
            obj.setId(res.getInt(1));

            return obj;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new ServiceException("Could not create user.");
        }
    }

    @Override
    public void update(int id, UserEntity obj) throws ServiceException {
        try {
            PreparedStatement stm = this.connect.prepareStatement("UPDATE users SET firstname=?, lastname=?, identifier=?, last_connection=?, upgrades=? WHERE id=?");
            stm.setString(1, obj.getFirstName());
            stm.setString(2, obj.getLastName());
            stm.setString(3, obj.getIdentifier());
            stm.setDate(4, Date.valueOf(obj.getLastConnection()));
            stm.setInt(5, obj.getNbUpgrades());
            stm.setInt(6, id);
            stm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new ServiceException("Could not update user with id " + id);
        }
    }

    public void updatePassword(int id, String password) throws ServiceException {
        try {
            PreparedStatement stm = this.connect.prepareStatement("UPDATE users SET password=? WHERE id=?");
            stm.setString(1, password);
            stm.setInt(2, id);
            stm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new ServiceException("Could not update user with id " + id);
        }
    }

    @Override
    public void delete(int id) throws ServiceException {
        try {
            PreparedStatement stm;

            stm = this.connect.prepareStatement("DELETE FROM exchanges WHERE id_pokemon_exp IN (SELECT id_pokemon_exp FROM exchanges INNER JOIN pokemons ON id_pokemon_exp = id WHERE owner=?);");
            stm.setInt(1, id);
            stm.executeUpdate();

            stm = this.connect.prepareStatement("DELETE FROM exchanges WHERE id_pokemon_dest IN (SELECT id_pokemon_dest FROM exchanges INNER JOIN pokemons ON id_pokemon_dest = id WHERE owner=?);");
            stm.setInt(1, id);
            stm.executeUpdate();

            stm = this.connect.prepareStatement("DELETE FROM pokemons WHERE owner = ?");
            stm.setInt(1, id);
            stm.executeUpdate();

            stm = this.connect.prepareStatement("DELETE FROM users WHERE id = ?");
            stm.setInt(1, id);
            stm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new ServiceException("Could not delete user with id " + id);
        }
    }

    public int getUserCount() throws ServiceException {
        try {
            PreparedStatement stm = this.connect.prepareStatement("SELECT COUNT(*) AS nb FROM users;");

            ResultSet res = stm.executeQuery();
            res.next();

            return res.getInt("nb");
        } catch (SQLException e) {
            e.printStackTrace();
            throw new ServiceException("Could not count users.");
        }
    }

    private UserEntity parseFromResult(ResultSet res) throws SQLException {
        UserEntity user = new UserEntity();
        user.setId(res.getInt("id"));
        user.setFirstName(res.getString("firstname"));
        user.setLastName(res.getString("lastname"));
        user.setIdentifier(res.getString("identifier"));
        user.setPassword(res.getString("password"));
        user.setLastConnection(res.getDate("last_connection").toLocalDate());
        user.setNbUpgrades(res.getInt("upgrades"));
        return user;
    }
}
