package com.uca.dao;

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

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class PokemonDAO extends _Generic<PokemonEntity> {

    @Override
    public ArrayList<PokemonEntity> getAll() throws ServiceException {
        try {
            ArrayList<PokemonEntity> pokemons = new ArrayList<>();
            PreparedStatement stm = this.connect.prepareStatement("SELECT * FROM pokemons INNER JOIN users ON pokemons.owner = users.id ORDER BY pokemons.id;");

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

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

    public ArrayList<PokemonEntity> getAllByOwner(int id) throws ServiceException {
        try {
            ArrayList<PokemonEntity> pokemons = new ArrayList<>();
            PreparedStatement stm = this.connect.prepareStatement("SELECT * FROM pokemons INNER JOIN users ON pokemons.owner = users.id WHERE owner = ?;");
            stm.setInt(1, id);

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

            return pokemons;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new ServiceException("Could not get all pokemons of user " + id);
        }
    }

    @Override
    public PokemonEntity getById(int id) throws ServiceException {
        try {
            PreparedStatement stm = this.connect.prepareStatement("SELECT * FROM pokemons INNER JOIN users ON pokemons.owner = users.id WHERE pokemons.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);
        }
    }

    @Override
    public PokemonEntity create(PokemonEntity obj) throws ServiceException {
        try {
            PreparedStatement stm = this.connect.prepareStatement("INSERT INTO pokemons(id_api, nickname, level, owner) VALUES(?,?,?,?);", Statement.RETURN_GENERATED_KEYS);
            stm.setInt(1, obj.getSpecies().getId());
            stm.setString(2, obj.getNickname());
            stm.setInt(3, obj.getLevel());
            stm.setInt(4, obj.getOwner().getId());
            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 Pokemon.");
        }
    }

    @Override
    public void update(int id, PokemonEntity obj) throws ServiceException {
        try {
            PreparedStatement stm = this.connect.prepareStatement("UPDATE pokemons SET id_api=?, nickname=?, level=?, owner=? WHERE id=?;");
            stm.setInt(1, obj.getSpecies().getId());
            stm.setString(2, obj.getNickname());
            stm.setInt(3, obj.getLevel());
            stm.setInt(4, obj.getOwner().getId());
            stm.setInt(5, id);
            stm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new ServiceException("Could not update pokemon with id " + id);
        }
    }

    @Override
    public void delete(int id) throws ServiceException {
        try {
            PreparedStatement stm = this.connect.prepareStatement("DELETE FROM exchanges WHERE id_pokemon_exp = ?;");
            stm.setInt(1, id);
            stm.executeUpdate();

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

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

    private PokemonEntity parseFromResult(ResultSet res) throws SQLException, ServiceException {
        PokemonEntity pokemon = new PokemonEntity();
        pokemon.setId(res.getInt("pokemons.id"));
        pokemon.setNickname(res.getString("nickname"));
        pokemon.setLevel(res.getInt("level"));

        PokedexEntity species = new PokedexDAO().getById(res.getInt("id_api"));
        pokemon.setSpecies(species);

        UserEntity owner = new UserEntity();
        owner.setId(res.getInt("users.id"));
        owner.setFirstName(res.getString("firstname"));
        owner.setLastName(res.getString("lastname"));
        owner.setIdentifier(res.getString("identifier"));
        owner.setPassword(res.getString("password"));
        pokemon.setOwner(owner);

        return pokemon;
    }
}
