package br.com.senacagil.springmvc.dao;

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


import br.com.senacagil.springmvc.business.Pessoa;
import br.com.senacagil.springmvc.business.Usuario;
import br.com.senacagil.springmvc.util.DBConexao;

public class UsuarioDAO {
	private DBConexao db = null;

	public UsuarioDAO(DBConexao dbConexao) {
		super();
		this.db = dbConexao;
	}

	private final String sqlSelect = "select id, userid, senha, perfil, id_pessoa from usuario";
	private final String sqlSelectAssociados = "select id, userid, senha, perfil from usuario where id_pessoa=? order by userid";
	private final String sqlSelectNaoAssociados = "select id, userid, senha from usuario where id_pessoa is null order by userid";
	private final String sqlInsert = "insert into usuario (userid, senha, perfil) values(?, ?, ?)";
	private final String sqlDelete = "delete from usuario where id=?";
	private final String sqlUpdate = "update usuario set perfil=?, id_pessoa=? where id=?";

	public void inserir(Usuario usuario) throws ClassNotFoundException,
			SQLException {
		Connection connection = db.getConnection();
		PreparedStatement pstmt = connection.prepareStatement(sqlInsert);
		pstmt.setString(1, usuario.getUserId());
		pstmt.setString(2, usuario.getSenha());
		pstmt.setString(3, usuario.getPerfil());
		pstmt.executeUpdate();
		pstmt.close();
	}

	public void associar(Usuario usuario, Pessoa pessoa)
			throws ClassNotFoundException, SQLException {
		Connection connection = db.getConnection();
		PreparedStatement pstmt = connection.prepareStatement(sqlUpdate);
		pstmt.setString(1, pessoa.getPerfil());
		pstmt.setInt(2, pessoa.getId());
		pstmt.setInt(3, usuario.getId());
		pstmt.executeUpdate();
		pstmt.close();
	}

	public List<Usuario> listar() throws ClassNotFoundException, SQLException {
		List<Usuario> lista = new ArrayList<Usuario>();
		Connection connection = db.getConnection();
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt.executeQuery(sqlSelect);
		while (rs.next()) {
			Usuario usuario = new Usuario();
			usuario.setId(rs.getInt(1));
			usuario.setUserId(rs.getString(2));
			usuario.setSenha(rs.getString(3));
			usuario.setPerfil(rs.getString(4));
			usuario.setIdPessoa(rs.getInt(5));
			lista.add(usuario);
		}
		rs.close();
		stmt.close();
		return lista;
	}

	public List<Usuario> listarAssociados(Pessoa pessoa)
			throws ClassNotFoundException, SQLException {
		List<Usuario> lista = new ArrayList<Usuario>();
		Connection connection = db.getConnection();
		PreparedStatement pstmt = connection
				.prepareStatement(sqlSelectAssociados);
		pstmt.setInt(1, pessoa.getId());
		ResultSet rs = pstmt.executeQuery();
		while (rs.next()) {
			Usuario usuario = new Usuario();
			usuario.setId(rs.getInt(1));
			usuario.setUserId(rs.getString(2));
			usuario.setSenha(rs.getString(3));
			usuario.setPerfil(rs.getString(4));
			usuario.setIdPessoa(pessoa.getId());
			lista.add(usuario);
		}
		rs.close();
		pstmt.close();
		return lista;
	}

	public List<Usuario> listarNaoAssociados() throws ClassNotFoundException,
			SQLException {
		List<Usuario> lista = new ArrayList<Usuario>();
		Connection connection = db.getConnection();
		Statement stmt = connection.createStatement();
		ResultSet rs = stmt.executeQuery(sqlSelectNaoAssociados);
		while (rs.next()) {
			Usuario usuario = new Usuario();
			usuario.setId(rs.getInt(1));
			usuario.setUserId(rs.getString(2));
			usuario.setSenha(rs.getString(3));
			lista.add(usuario);
		}
		rs.close();
		stmt.close();
		return lista;
	}

	public void excluir(Usuario usuario) throws ClassNotFoundException,
			SQLException {
		Connection connection = db.getConnection();
		PreparedStatement pstmt = connection.prepareStatement(sqlDelete);
		pstmt.setInt(1, usuario.getId());
		pstmt.executeUpdate();
		pstmt.close();
	}
}
