package com.uca.dao;

import com.uca.entity.ExchangeEntity;
import com.uca.entity.PokemonEntity;
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 ExchangeDAO extends _Generic<ExchangeEntity> {
    @Override
    public ArrayList<ExchangeEntity> getAll() throws ServiceException {
        try {
            ArrayList<ExchangeEntity> exchanges = new ArrayList<>();
            PreparedStatement stm = this.connect.prepareStatement("SELECT * FROM exchanges INNER JOIN pokemons AS p1 ON exchanges.id_pokemon_exp = p1.id INNER JOIN pokemons AS p2 ON exchanges.id_pokemon_dest = p2.id ORDER BY id_exch;");

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

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

    public ArrayList<ExchangeEntity> getAllByUserExp(int id) throws ServiceException {
        try {
            ArrayList<ExchangeEntity> exchanges = new ArrayList<>();
            PreparedStatement stm = this.connect.prepareStatement("SELECT * FROM exchanges INNER JOIN pokemons AS p1 ON exchanges.id_pokemon_exp = p1.id INNER JOIN pokemons AS p2 ON exchanges.id_pokemon_dest = p2.id WHERE p1.owner = ? ORDER BY id_exch;");
            stm.setInt(1, id);

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

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

    public ArrayList<ExchangeEntity> getAllByUserDest(int id) throws ServiceException {
        try {
            ArrayList<ExchangeEntity> exchanges = new ArrayList<>();
            PreparedStatement stm = this.connect.prepareStatement("SELECT * FROM exchanges INNER JOIN pokemons AS p1 ON exchanges.id_pokemon_exp = p1.id INNER JOIN pokemons AS p2 ON exchanges.id_pokemon_dest = p2.id WHERE p2.owner = ? ORDER BY id_exch;");
            stm.setInt(1, id);

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

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

    @Override
    public ExchangeEntity getById(int id) throws ServiceException {
        try {
            PreparedStatement stm = this.connect.prepareStatement("SELECT * FROM exchanges INNER JOIN pokemons AS p1 ON exchanges.id_pokemon_exp = p1.id INNER JOIN pokemons AS p2 ON exchanges.id_pokemon_dest = p2.id WHERE id_exch = ? 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 exchange with id " + id);
        }
    }

    @Override
    public ExchangeEntity create(ExchangeEntity obj) throws ServiceException {
        try {
            PreparedStatement stm = this.connect.prepareStatement("INSERT INTO exchanges(id_pokemon_exp, id_pokemon_dest, status) VALUES(?,?,?);", Statement.RETURN_GENERATED_KEYS);
            stm.setInt(1, obj.getPokemonExp().getId());
            stm.setInt(2, obj.getPokemonDest().getId());
            stm.setString(3, obj.getStatus());
            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 exchange.");
        }
    }

    @Override
    public void update(int id, ExchangeEntity obj) throws ServiceException {
        try {
            PreparedStatement stm = this.connect.prepareStatement("UPDATE exchanges SET id_pokemon_exp=?, id_pokemon_dest=?, status=? WHERE id_exch=?;");
            stm.setInt(1, obj.getPokemonExp().getId());
            stm.setInt(2, obj.getPokemonDest().getId());
            stm.setString(3, obj.getStatus());
            stm.setInt(4, id);
            stm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new ServiceException("Could not update exchange with id " + id);
        }
    }

    @Override
    public void delete(int id) throws ServiceException {
        try {
            PreparedStatement stm = this.connect.prepareStatement("DELETE FROM exchanges WHERE id = ?;");
            stm.setInt(1, id);
            stm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new ServiceException("Could not delete pokemon with id " + id);
        }
    }

    private ExchangeEntity parseFromResult(ResultSet res) throws SQLException, ServiceException {
        ExchangeEntity exchange = new ExchangeEntity();
        exchange.setId(res.getInt("id_exch"));

        PokemonEntity exp = new PokemonDAO().getById(res.getInt("id_pokemon_exp"));
        exchange.setPokemonExp(exp);

        PokemonEntity dest = new PokemonDAO().getById(res.getInt("id_pokemon_dest"));
        exchange.setPokemonDest(dest);

        exchange.setStatus(res.getString("status"));

        return exchange;
    }
}
