package gerenciadorempresa.contatos.gui;

import gerenciadorempresa.base.JPAUtil;
import gerenciadorempresa.contatos.model.entity.Assunto;
import gerenciadorempresa.contatos.model.entity.Conversa;
import gerenciadorempresa.contatos.model.persistence.ConversaDAO;
import gerenciadorempresa.contatos.model.persistence.DAOFactory;
import gerenciadorempresa.contatos.model.persistence.IConversaDAO;

import java.awt.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.swing.BoxLayout;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JScrollPane;
import javax.swing.JTextField;
import javax.swing.SwingUtilities;

import org.swingBean.actions.ApplicationAction;
import org.swingBean.descriptor.BeanTableModel;
import org.swingBean.descriptor.TableFieldDescriptor;
import org.swingBean.descriptor.XMLDescriptorFactory;
import org.swingBean.gui.JActButton;
import org.swingBean.gui.JBeanTable;

public class TelaConsulta {


    public static void main(String[] arg) throws SQLException, ClassNotFoundException  {

        EntityManager em = JPAUtil.getEntityManager();
        EntityTransaction tx = em.getTransaction();

        final IConversaDAO db = DAOFactory.getConversaDAO(em);





        final JComboBox ComboData = new JComboBox(Conversa.getDatas(db.findAll()));
        final JComboBox ComboObjetivos = new JComboBox(Conversa.getObjetivoss(db.findAll()));
        final JComboBox ComboResultados = new JComboBox(Conversa.getResultadoss(db.findAll()));
        final JComboBox ComboStatus = new JComboBox(Conversa.getStatuss(db.findAll()));
        final JComboBox ComboAssunto = new JComboBox(Conversa.getAssuntoss(db.findAll()));
        final JComboBox ComboUsuario = new JComboBox(Conversa.getUsuarioss(db.findAll()));
        final JComboBox ComboFormaContato = new JComboBox(Conversa.getFormaContatoss(db.findAll()));

        JFrame tela = new JFrame();



        //Cria??o do frame que conter? o campo gen?rico
        JFrame BoxSupSup = new JFrame();
        Container paneSupSup = BoxSupSup.getContentPane();
        paneSupSup.setLayout(new BoxLayout(paneSupSup, BoxLayout.X_AXIS));
        //BoxSupSup.add(Geral);

        //Cria??o do frame que conter? o primeiro conjunto de Combos
        JFrame BoxMiddle = new JFrame();
        Container paneMiddle = BoxMiddle.getContentPane();
        paneMiddle.add(ComboData);
        paneMiddle.add(ComboObjetivos);
        paneMiddle.add(ComboResultados);
        paneMiddle.setLayout(new BoxLayout(paneMiddle, BoxLayout.X_AXIS));

        //Cria??o do frame que conter? o segundo conjunto de combos
        JFrame BoxInf = new JFrame();
        Container paneInf = BoxInf.getContentPane();
        paneInf.add(ComboStatus);
        paneInf.add(ComboAssunto);
        paneInf.add(ComboUsuario);
        paneInf.add(ComboFormaContato);
        paneInf.setLayout(new BoxLayout(paneInf, BoxLayout.X_AXIS));




        //Cria??o do componente da tabela SwingBean
        TableFieldDescriptor tabledescriptor = XMLDescriptorFactory
        .getTableFieldDescriptor(Conversa.class,
                "gerenciadorempresa/contatos/gui/tabelaConsulta.xml","ConsultaTable");
        final BeanTableModel<Conversa> model = new BeanTableModel<Conversa>(
                tabledescriptor);
        final JBeanTable table = new JBeanTable(model);

        table.addDoubleClickAction(new ApplicationAction() {

            public void execute() {

                // ABRIR TELA DO KAMI AKI!

            }

        });

        JActButton pesquisa = new JActButton("Pesquisar",
                new ApplicationAction(){
                    public void execute(){
                        String qry = "SELECT DISTINCT CV.id_conversa, ASS.assunto, PR.nome, CV.data, CV.objetivos, CV.texto, CV.resultados, FC.valor, ST.status, US.login " +
                                "FROM projeto PR, conversa CV,  assunto ASS, usuario US, status ST, forma_contato FC WHERE ";

                        qry=qry+" CV.id_assunto=ASS.id_assunto " +
                        " AND CV.id_forma_contato = FC.id_forma_contato " +
                        " AND CV.id_status = ST.id_status " +
                        " AND CV.id_usuario = US.id_usuario ";

                        if (ComboData.getSelectedItem()!=null) {
                            qry=qry+" AND CV.data LIKE '%"+ComboData.getSelectedItem()+"%'";
                        }

                        if (ComboObjetivos.getSelectedItem()!=null) {
                            qry=qry+" AND CV.objetivos LIKE '%"+ComboObjetivos.getSelectedItem()+"%'";
                        }

                        if (ComboResultados.getSelectedItem()!=null) {
                            qry=qry+" AND CV.resultados LIKE '%"+ComboResultados.getSelectedItem()+"%'";
                        }

                        if (ComboStatus.getSelectedItem()!=null) {
                            qry=qry+" AND ST.status LIKE '%"+ComboStatus.getSelectedItem()+"%'";
                        }

                        if (ComboAssunto.getSelectedItem()!=null) {
                            qry=qry+" AND ASS.assunto LIKE '%"+ComboAssunto.getSelectedItem()+"'";
                        }

                        if (ComboUsuario.getSelectedItem()!=null) {
                            qry=qry+" AND US.login LIKE '%"+ComboUsuario.getSelectedItem()+"'";
                        }

                        if (ComboFormaContato.getSelectedItem()!=null) {
                            qry=qry+" AND FC.valor LIKE '%"+ComboFormaContato.getSelectedItem()+"'";
                        }

                        Connection jdbcConnection;
                        try {
                            jdbcConnection = DriverManager.getConnection("jdbc:postgresql://comp12.redecasd.ita.br/gerenciadorempresa", "postgres", "itacomp12");
                            Statement stmt = jdbcConnection.createStatement();
                            ResultSet rs = stmt.executeQuery(qry);
                            Set<Integer> ids = new HashSet<Integer>();
                            List<Conversa> conversas = new ArrayList<Conversa>();
                            while(rs.next()) {
                            //  System.out.println(rs.getString(1) +" " + rs.getString(2) + " " + rs.getString(3));
                                ids.add(rs.getInt(1));
                            }
                            EntityManager em = JPAUtil.getEntityManager();
                            IConversaDAO daoConversa = DAOFactory.getConversaDAO(em);
                            for( Integer id : ids ) {
                                conversas.add(daoConversa.findById(id));
                            }
                            em.close();
                            //faz alguma coisa com conversas
                            for( Conversa c : conversas)
                                System.out.println(c.getIdConversa());
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }





                    }
        });

        Container paneTela = tela.getContentPane();
        tela.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        paneTela.add(paneSupSup);
        paneTela.add(paneMiddle);
        paneTela.add(paneInf);
        paneTela.add(new JScrollPane(table));
        paneTela.add(pesquisa);
        tela.setVisible(true);
        paneTela.setLayout(new BoxLayout(paneTela, BoxLayout.Y_AXIS));
        tela.setSize(600, 400);
    }

}