package Datos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import Modelo.CodClub;
import Modelo.Usuario;
import java.util.ArrayList;

public class CodClubDB {

    public static CodClub getCodClub(String nombre) {
        ConnectionPool cp = ConnectionPool.getInstance();
        Connection c = cp.getConnection();
        PreparedStatement ps;
        ResultSet rs;
        CodClub cod_club = null;
        String query = "SELECT * FROM COD_CLUB cc "
                + "WHERE cc.nombre = ?";
        try {
            ps = c.prepareStatement(query);
            ps.setString(1, nombre);
            rs = ps.executeQuery();
            if(!rs.next()){
                cod_club = new CodClub("","");
            }else{
                String id_club = rs.getString("ID_CLUB");
                String nombre_club = rs.getString("NOMBRE");
                cod_club = new CodClub(id_club,nombre_club);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        cp.freeConnection(c);
        return cod_club;
    }
    
    public static CodClub getCodClubId(String id_club) {
        ConnectionPool cp = ConnectionPool.getInstance();
        Connection c = cp.getConnection();
        PreparedStatement ps;
        ResultSet rs;
        CodClub cod_club = null;
        String query = "SELECT * FROM COD_CLUB cc "
                + "WHERE cc.id_club = ?";
        try {
            ps = c.prepareStatement(query);
            ps.setString(1, id_club);
            rs = ps.executeQuery();
            if(!rs.next()){
                cod_club = new CodClub("","");
            }else{
                String nombre_club = rs.getString("NOMBRE");
                cod_club = new CodClub(id_club,nombre_club);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        cp.freeConnection(c);
        return cod_club;
    }
    
    public static ArrayList <CodClub> getClubsUusario(Usuario u){
        ConnectionPool cp = ConnectionPool.getInstance();
        Connection c = cp.getConnection();
        PreparedStatement ps;
        ResultSet rs;
        ArrayList <CodClub> lista_clubs = new ArrayList<>();
        @SuppressWarnings("UnusedAssignment")
        CodClub club = null;
        String query = "SELECT cc.* FROM COD_CLUB cc, MIEMBROS_CLUB mc "
                + " WHERE cc.id_club = mc.id_club "
                + " AND mc.id_usuario = ? ";
        try {
            ps = c.prepareStatement(query);
            ps.setString(1, u.getId_usuario());
            rs = ps.executeQuery();
            while(rs.next()){
                String id_club = rs.getString("id_club");
                String nombre_club = rs.getString("nombre");
                club = new CodClub(id_club, nombre_club);
                lista_clubs.add(club);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        cp.freeConnection(c);
        return lista_clubs;
    }

    public static boolean addCodClub(CodClub cod_club) {
        int resultado = 0;
        ConnectionPool cp = ConnectionPool.getInstance();
        Connection c = cp.getConnection();
        PreparedStatement ps;
        String query = "INSERT INTO COD_CLUB VALUES(?,?)";
        try {
            ps = c.prepareStatement(query);
            ps.setString(1, cod_club.getId_club());
            ps.setString(2, cod_club.getNombre_club());
            resultado = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        cp.freeConnection(c);
        return resultado == 1;
    }
    
    public static boolean updateCodClub(CodClub cod_club) {
        int resultado = 0;
        ConnectionPool cp = ConnectionPool.getInstance();
        Connection c = cp.getConnection();
        PreparedStatement ps;
        String query = "UPDATE COD_CLUB "+   
                       "SET id_club = ? ,"+
                             " nombre = ? "+
                       "WHERE id_club = ? ";
        try {
            ps = c.prepareStatement(query);
            ps.setString(1, cod_club.getId_club());
            ps.setString(2, cod_club.getNombre_club());
            ps.setString(3, cod_club.getId_club());
            resultado = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        cp.freeConnection(c);
        return resultado == 1;
    }
}
