package sac.pessoa.juridica.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;


import sac.exception.ItemNaoCadastradoException;
import sac.persistencia.OID;
import sac.persistencia.OIDFactory;
import sac.persistencia.PersistenceException;
import sac.persistencia.PoolConexoes;
import sac.pessoa.juridica.RepositorioCategoria;
import sac.pessoa.util.jdbc.DBRepository_Abstract;


public class DBCategoria extends DBRepository_Abstract
                 implements RepositorioCategoria {
    // instancia unica do repositorio
    private static DBCategoria instancia;

    //Atributos para controle de acesso ao BD.
    private PoolConexoes pool;
    private OIDFactory oidFactory;

    private final String preparedSelectCategoria =
         "select cd_categoria from categoria where nm_categoria = ? ";
    private final String preparedSelectSubCategoria =
          "select cd_sub_categoria from sub_categoria where nm_sub_categoria = ? ";

    private final String preparedInsertCategoriaSql =
        "insert into categoria (cd_categoria , nm_categoria) " +
        "values (?, ?)";
    private final String preparedInsertSubCategoriaSql =
        "insert into sub_categoria (cd_sub_categoria , nm_sub_categoria) " +
        "values (?, ?)";
    private final String preparedInsertRelCategoriaSql =
        "insert into rel_categoria_sub_categoria (cd_categoria, cd_sub_categoria) " +
        "values (?, ?)";

    private final String preparedRemoverCategoriaSql =
        "delete from categoria where nm_categoria = ? ";
    private final String preparedRemoverRelCategoriaSql =
        "delete from rel_categoria_sub_categoria where cod_categoria in "+
        " (select cod_categoria from categoria where nm_categoria = ? ) ";
    private final String preparedSelectPjCategoria =
        " select cod_pj from rel_pj_categoria, categoria "+
        "where rel_pj_categoria.cod_categoria = categoria.cod_categoria "+
        "and categoria.nm_categoria = ? ";


    public DBCategoria() throws PersistenceException {
      pool = PoolConexoes.getInstancia();
      oidFactory   = OIDFactory.getInstancia();

    }

    public static synchronized DBCategoria getInstancia() throws PersistenceException {
        if (instancia == null) {
            instancia = new DBCategoria();
        }
        return instancia;
    }

   /**
   * Funcao para adicionar uma nova Categoria de Pessoa Juridica
   * executada por um usuário operador que extende de pessoa fisica.
   *
   * @param categoria	String contendo categoria a ser cadastrada
   */
    public OID cadastrarCategoria(String categoria) throws PersistenceException {
        Connection con = getConnection();
        PreparedStatement pre = createPreparedStatement(con, preparedInsertCategoriaSql);
        try {
            OID oid = oidFactory.novoOID();
            //pessoa.setId(oid);
            long idCategoria = oid.getLongValue();
            pre.setLong(1, idCategoria);
            pre.setString(2, categoria);
            pre.executeUpdate();
            con.commit();
            return oid;
        } catch (SQLException sqle){
            try {
                con.rollback();
            } catch (Exception ex) {
                throw new PersistenceException ("Erro no Banco de Dados, (nao foi possivel realizar rollback).", sqle);
            }
            throw new PersistenceException ("Erro no Banco de Dados. ", sqle);
        } finally {
            closeStatement(pre);
            freeConnection(con);
        }
   }

   public void removerCategoria(String categoria) throws PersistenceException {
        Connection con = getConnection();
        PreparedStatement preRemoverCat = createPreparedStatement(con, preparedRemoverCategoriaSql);
        PreparedStatement preRemoverRelCat = createPreparedStatement(con, preparedRemoverRelCategoriaSql);
        PreparedStatement preExistPj = createPreparedStatement(con, preparedSelectPjCategoria);
        try {
            preExistPj.setString(1, categoria);
            ResultSet rs = preExistPj.executeQuery();
            if (!rs.next()){
              preRemoverRelCat.setString(1, categoria);
              preRemoverRelCat.execute();
              preRemoverCat.setString(1, categoria);
              preRemoverCat.execute();
              con.commit();
            } else {
              throw new  PersistenceException ("Existe uma Pessoa Juridica utilizando esta categoria.");
            }
        } catch (SQLException sqle){
            try {
                con.rollback();
            } catch (Exception ex) {
                throw new PersistenceException ("Erro no Banco de Dados, (nao foi possivel realizar rollback).", sqle);
            }
            throw new PersistenceException ("Erro no Banco de Dados. ", sqle);
        } finally {
            closeStatement(preRemoverCat);
            closeStatement(preRemoverRelCat);
            closeStatement(preExistPj);
            freeConnection(con);
        }
   }

   /**
   * Funcao para adicionar uma nova subCategoria de Pessoa Juridica.
   *
   * @param subcategoria	String contendo subcategoria a ser cadastrada
   */
    public OID cadastrarSubCategoria(String subcategoria) throws PersistenceException {
        Connection con = getConnection();
        PreparedStatement pre = createPreparedStatement(con, preparedInsertSubCategoriaSql);
        try {
            OID oid = oidFactory.novoOID();
            long idsubCategoria = oid.getLongValue();
            pre.setLong(1, idsubCategoria);
            pre.setString(2, subcategoria);
            pre.executeUpdate();
            con.commit();
            return oid;
        } catch (SQLException sqle){
            try {
                con.rollback();
            } catch (Exception ex) {
                throw new PersistenceException ("Erro no Banco de Dados, (nao foi possivel realizar rollback).", sqle);
            }
            throw new PersistenceException ("Erro no Banco de Dados. ", sqle);
        } finally {
            closeStatement(pre);
            freeConnection(con);
        }
   }

   /**
   * Funcao para adicionar uma relacao entre uma Categoria e uma subCategoria de Pessoa Juridica.
   *
   * @param categoria	        String contendo categoria a ser cadastrada
   * @param subcategoria	String contendo subcategoria a ser cadastrada
   */
    public void cadastrarRelCategoria(String categoria, String subcategoria) throws PersistenceException{
        Connection con = getConnection();
        PreparedStatement preInsert = createPreparedStatement(con, preparedInsertRelCategoriaSql);
        try {
            OID oidCat, oidSubCat;
            try {
                oidCat = consultarOIDCategoria( categoria, con ) ;
            } catch (ItemNaoCadastradoException iteme){
                oidCat = cadastrarCategoria(categoria);
            }
            try {
              oidSubCat = consultarOIDSubCategoria( subcategoria, con  );
            } catch (ItemNaoCadastradoException iteme){
               oidSubCat = cadastrarSubCategoria(subcategoria);
            }
            preInsert.setLong(1, oidCat.getLongValue());
            preInsert.setLong(2, oidSubCat.getLongValue());
            preInsert.executeUpdate();

            con.commit();
        } catch (SQLException sqle){
            try {
                con.rollback();
            } catch (Exception ex) {
                throw new PersistenceException ("Erro no Banco de Dados, (nao foi possivel realizar rollback).", sqle);
            }
            throw new PersistenceException ("Erro no Banco de Dados. ", sqle);
        } finally {
            closeStatement(preInsert);
            freeConnection(con);
        }
   }


   public OID consultarOIDCategoria(String categoria, Connection con) throws PersistenceException, ItemNaoCadastradoException{
        PreparedStatement preCodigoCategoria = createPreparedStatement(con, preparedSelectCategoria);
        try {
           long codCat;
            preCodigoCategoria.setString(1, categoria);
            ResultSet rsCat = preCodigoCategoria.executeQuery();
            if (rsCat.next()){
               codCat = rsCat.getLong("cd_categoria");
            } else {
                throw new ItemNaoCadastradoException ("Categoria = "+ categoria+ " inexistente.");
            }
            rsCat.close();
            return new OID(codCat);
        } catch (SQLException sqle){

            throw new PersistenceException ("Erro no Banco de Dados. ", sqle);
        } finally {
            closeStatement(preCodigoCategoria);
        }
   }

   public OID consultarOIDSubCategoria(String subcategoria, Connection con) throws PersistenceException, ItemNaoCadastradoException {
        PreparedStatement preCodigoSubCategoria = createPreparedStatement(con, preparedSelectSubCategoria);
        try {
           long codSubCat;
            preCodigoSubCategoria.setString(1, subcategoria);
            ResultSet rsSubCat = preCodigoSubCategoria.executeQuery();
            if (rsSubCat.next()){
               codSubCat = rsSubCat.getLong("cd_sub_categoria");
            } else {
                throw new ItemNaoCadastradoException ("Sub-categoria = "+ subcategoria+ " inexistente.");
            }
            rsSubCat.close();
            return new OID(codSubCat);
        } catch (SQLException sqle){

            throw new PersistenceException ("Erro no Banco de Dados. ", sqle);
        } finally {
            closeStatement(preCodigoSubCategoria);
        }
   }

    public Map listarCategoria () throws PersistenceException{
        Map resposta = new Hashtable();

        Connection con = getConnection();
        PreparedStatement preListCategoria = createPreparedStatement(con,
            "select * from categoria");
        PreparedStatement preListCodSubCategoria = createPreparedStatement(con,
            "select cd_sub_categoria from rel_categoria_sub_categoria " +
            "where cd_categoria = ? ");
        PreparedStatement preSelectSubCategria = createPreparedStatement(con,
            "select nm_sub_categoria from sub_categoria "+
            "where cd_sub_categoria = ? ");
        try {
            ResultSet rs = preListCategoria.executeQuery();
            while ( rs.next() ){
               String categoria = rs.getString("nm_categoria");
               long cd_categoria = rs.getLong("cd_categoria");

               List list_sub_categoria = new ArrayList();

               preListCodSubCategoria.clearParameters();
               preListCodSubCategoria.setLong(1, cd_categoria);
               ResultSet rs_cd_sub = preListCodSubCategoria.executeQuery();
               while ( rs_cd_sub.next() ){
                  long cd_sub_categoria = rs_cd_sub.getLong("cd_sub_categoria");

                  preSelectSubCategria.clearParameters();
                  preSelectSubCategria.setLong(1, cd_sub_categoria);
                  ResultSet rs_sub = preSelectSubCategria.executeQuery();
                  if ( rs_sub.next()) {
                      String sub_categoria = rs_sub.getString("nm_sub_categoria");
                      list_sub_categoria.add(sub_categoria);
                  } else {
                      throw new PersistenceException ("Erro no Banco de Dados, "+
                      "Incistencia entra a tabela rel_categoria_sub_categoria e "+
                      " sub_categoria ");
                  }
               }
               resposta.put(categoria , list_sub_categoria);
            }
            return resposta;
        } catch (SQLException sqle){

            throw new PersistenceException ("Erro no Banco de Dados. ", sqle);
        } finally {
            closeStatement(preListCodSubCategoria);
            closeStatement(preSelectSubCategria);
            closeStatement(preListCategoria);
            freeConnection(con);
        }
   }
}