/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package cl.puc.ing.kegan.grupo9.controller;

import cl.puc.ing.kegan.grupo9.Database;
import cl.puc.ing.kegan.grupo9.Question;
import cl.puc.ing.kegan.grupo9.controller.actionforms.addQuestionFormBean;
import cl.puc.ing.kegan.grupo9.controller.actions.addQuestionAction;
import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.*;
import java.util.*;
import java.util.logging.*;
import javax.xml.crypto.Data;

/**
 *
 * @author crandrad
 */
public class QuestionDataAccessObject {
    
    public Collection getQuestionsByTags(String tagsStr) throws SQLException {
        Statement st = Database.getInstance().createStatement();
        
        tagsStr = addSlashes(tagsStr);
        
        System.err.println("getQuestionsByTags("+tagsStr+")::");
        
        String[] tagsAr = tagsStr.trim().split("\\s+");
        LinkedList<Integer> preguntas = null;
        
        for(String tag : tagsAr) {
            System.err.println("getQuestionsByTags("+tagsStr+"):: tag "+tag);
            LinkedList<Integer> preguntas2 = new LinkedList<Integer>();
            ResultSet rs = st.executeQuery("SELECT id_pregunta FROM tag_pregunta WHERE tag LIKE '%"+tag+"%'");
            while(rs.next()) {
                System.err.println("getQuestionsByTags("+tagsStr+"):: tag "+tag+" pregunta "+rs.getInt(1));
                if(!preguntas2.contains(rs.getInt(1)))
                    preguntas2.add(rs.getInt(1));
            }
            
            if(preguntas == null) {
                preguntas = new LinkedList<Integer>(preguntas2);
            } else {
                LinkedList<Integer> preguntas3 = new LinkedList<Integer>();
                for(Integer i : preguntas) {
                    if(preguntas2.contains(i))
                        preguntas3.add(i);
                }
                preguntas = preguntas3;
            }
        }
        
        LinkedList<Question> questions = new LinkedList<Question>();
        
        for (Integer i : preguntas) {
            System.err.println("getQuestionsByTags("+tagsStr+"):: agregando pregunta "+i);
            ResultSet rs = st.executeQuery("SELECT * FROM pregunta WHERE visible=1 AND id=" + i);
            while (rs.next()) {

                Question c = new Question();
                c.setID(rs.getInt("id"));
                c.setCorrecta(rs.getString("correcta").charAt(0) - 'a');
                c.setEnunciado(rs.getString("enunciado"));
                c.setId_creador(rs.getInt("id_creador"));
                c.setCalidad(rs.getDouble("calidad"));
                c.setDificultad(rs.getDouble("dificultad"));
                c.setImagen(rs.getString("imagen"));

                ArrayList<String> alts = new ArrayList<String>(5);
                alts.add("a) "+rs.getString("alta"));
                alts.add("b) "+rs.getString("altb"));
                alts.add("c) "+rs.getString("altc"));
                alts.add("d) "+rs.getString("altd"));
                alts.add("e) "+rs.getString("alte"));
                c.setAlternativas(alts);
                
                ArrayList<String> mistags = new ArrayList<String>();
                mistags.addAll(getQuestionTags(c.getID()));
                c.setTags(mistags);

                questions.add(c);



            }
        }




      return questions;
    }
    
    public Collection getQuestionsByTags(Collection<String> tags, int max) throws SQLException {
        LinkedList<Question> c = getQuestionsByTags(tags);
        Collections.shuffle(c);
        while(c.size() > max) {
            c.removeLast();
        }
        return c;
    }
    
    public LinkedList<Question> getQuestionsByTags(Collection<String> tags) throws SQLException {
        Statement st = Database.getInstance().createStatement();
        
        LinkedList<Integer> preguntas = null;
        
        for(String tag : tags) {
            System.err.println("getQuestionsByTags(Collection):: tag "+tag);
            LinkedList<Integer> preguntas2 = new LinkedList<Integer>();
            ResultSet rs = st.executeQuery("SELECT id_pregunta FROM tag_pregunta WHERE tag LIKE '%"+tag+"%'");
            while(rs.next()) {
                System.err.println("getQuestionsByTags(Collection):: tag "+tag+" pregunta "+rs.getInt(1));
                if(!preguntas2.contains(rs.getInt(1)))
                    preguntas2.add(rs.getInt(1));
            }
            
            if(preguntas == null) {
                preguntas = new LinkedList<Integer>(preguntas2);
            } else {
                LinkedList<Integer> preguntas3 = new LinkedList<Integer>();
                for(Integer i : preguntas) {
                    if(preguntas2.contains(i))
                        preguntas3.add(i);
                }
                preguntas = preguntas3;
            }
        }
        
        LinkedList<Question> questions = new LinkedList<Question>();
        if(preguntas != null) {
            for (Integer i : preguntas) {
                System.err.println("getQuestionsByTags(Collection):: agregando pregunta "+i);
                ResultSet rs = st.executeQuery("SELECT * FROM pregunta WHERE visible=1 AND id=" + i);
                while (rs.next()) {

                    Question c = new Question();
                    c.setID(rs.getInt("id"));
                    c.setCorrecta(rs.getString("correcta").charAt(0) - 'a');
                    c.setEnunciado(rs.getString("enunciado"));
                    c.setId_creador(rs.getInt("id_creador"));
                    c.setCalidad(rs.getDouble("calidad"));
                    c.setDificultad(rs.getDouble("dificultad"));
                    c.setImagen(rs.getString("imagen"));

                    ArrayList<String> alts = new ArrayList<String>(5);
                    alts.add("a) "+rs.getString("alta"));
                    alts.add("b) "+rs.getString("altb"));
                    alts.add("c) "+rs.getString("altc"));
                    alts.add("d) "+rs.getString("altd"));
                    alts.add("e) "+rs.getString("alte"));
                    c.setAlternativas(alts);


                    ArrayList<String> mistags = new ArrayList<String>();
                    mistags.addAll(getQuestionTags(c.getID()));
                    c.setTags(mistags);

                    questions.add(c);



                }
            }
        }




      return questions;
    }
    
    public Collection getQuestionTags(int question) throws  SQLException{
         Statement st = Database.getInstance().createStatement();
         ResultSet rs = st.executeQuery("SELECT tag FROM tag_pregunta " +    
                    "WHERE id_pregunta=" + question);
          ArrayList<String> mistags = new ArrayList<String>();
          while (rs.next()) {
              mistags.add(rs.getString("tag"));
          }
        return mistags;
    }
    
    public Collection getQuestionByUserID(int user_ID) throws  SQLException 
    {
        Statement st = Database.getInstance().createStatement();
        LinkedList<Question> questions = new LinkedList<Question>();
        ResultSet rs = st.executeQuery("SELECT * FROM pregunta " +    
                    "WHERE visible=1 AND id_creador=" + user_ID);
            while (rs.next()) {

                Question c = new Question();
                c.setID(rs.getInt("id"));
                c.setCorrecta(rs.getString("correcta").charAt(0) - 'a');
                c.setEnunciado(rs.getString("enunciado"));
                c.setId_creador(rs.getInt("id_creador"));
                c.setCalidad(rs.getDouble("calidad"));
                c.setDificultad(rs.getDouble("dificultad"));
                  c.setImagen(rs.getString("imagen"));

                ArrayList<String> alts = new ArrayList<String>(5);
                  alts.add("a) "+rs.getString("alta"));
                alts.add("b) "+rs.getString("altb"));
                alts.add("c) "+rs.getString("altc"));
                alts.add("d) "+rs.getString("altd"));
                alts.add("e) "+rs.getString("alte"));
                c.setAlternativas(alts);
                ArrayList<String> mistags = new ArrayList<String>();
                mistags.addAll(getQuestionTags(c.getID()));
                c.setTags(mistags);

                questions.add(c);
        }
        return questions;
    }
     public Collection getQuestionByDif(int dificultad) throws  SQLException 
    {
        Statement st = Database.getInstance().createStatement();
        LinkedList<Question> questions = new LinkedList<Question>();
        ResultSet rs = st.executeQuery("SELECT * FROM pregunta " +    
                    "WHERE visible=1 AND dificultad <= "+dificultad);
            while (rs.next()) {

                Question c = new Question();
                c.setID(rs.getInt("id"));
                c.setCorrecta(rs.getString("correcta").charAt(0) - 'a');
                c.setEnunciado(rs.getString("enunciado"));
                c.setId_creador(rs.getInt("id_creador"));
                c.setCalidad(rs.getDouble("calidad"));
                c.setDificultad(rs.getDouble("dificultad"));
                  c.setImagen(rs.getString("imagen"));

                ArrayList<String> alts = new ArrayList<String>(5);
                  alts.add("a) "+rs.getString("alta"));
                alts.add("b) "+rs.getString("altb"));
                alts.add("c) "+rs.getString("altc"));
                alts.add("d) "+rs.getString("altd"));
                alts.add("e) "+rs.getString("alte"));
                c.setAlternativas(alts);
                ArrayList<String> mistags = new ArrayList<String>();
                mistags.addAll(getQuestionTags(c.getID()));
                c.setTags(mistags);

                questions.add(c);
        }
        return questions;
    }
    public boolean deletQuestion(int id_question,int id_user) throws SQLException {
        /*borrar la pregunta, chequear que el id_creador de la pregunta sea = a user*/
        
        System.err.println("QDAO.deleteQ("+id_question+","+id_user+")");
        
        Statement st = Database.getInstance().createStatement();
        int res = st.executeUpdate("UPDATE pregunta SET visible=0 WHERE id="+id_question+" AND id_creador="+id_user);

        System.err.println("QDAO.deleteQ:: OK query, deleting");
       
        if(res > 0) {
            System.err.println("QDAO.deleteQ:: OK delete, returning");
            return true;
        }
        System.err.println("QDAO.deleteQ:: ERROR deleting, returning");
        return false;
    }

    public Question getQuestion(int id_question) throws  SQLException 
    {
        Statement st = Database.getInstance().createStatement();
        
        ResultSet rs = st.executeQuery("SELECT * FROM pregunta WHERE visible=1 AND id=" + id_question);

        if(rs.next())
        {
            Question c = new Question();
            c.setID(rs.getInt("id"));
            c.setCorrecta(rs.getString("correcta").charAt(0) - 'a');
            c.setEnunciado(rs.getString("enunciado"));
            c.setId_creador(rs.getInt("id_creador"));
            c.setCalidad(rs.getDouble("calidad"));
            c.setDificultad(rs.getDouble("dificultad"));
              c.setImagen(rs.getString("imagen"));;
            
            c.setJustificacion(rs.getString("justificacion"));
          
            ArrayList<String> alts = new ArrayList<String>(5);
                alts.add("a) "+rs.getString("alta"));
                alts.add("b) "+rs.getString("altb"));
                alts.add("c) "+rs.getString("altc"));
                alts.add("d) "+rs.getString("altd"));
                alts.add("e) "+rs.getString("alte"));
            c.setAlternativas(alts);
            ArrayList<String> mistags = new ArrayList<String>();
            mistags.addAll(getQuestionTags(c.getID()));
            c.setTags(mistags);
                        
            return c;
        }
        
     return null;   
    }
    
    public void addQuestion(addQuestionFormBean bean, int id_creador, String path,Collection<String> tags) throws SQLException {
        
        String imagen,ext=null;
        if(bean.getImagen() == null || bean.getImagen().getFileName().equals(""))
            imagen = "NULL";
        else {
            int x = bean.getImagen().getFileName().lastIndexOf('.');
            ext = bean.getImagen().getFileName().substring(x+1);
            if(!(ext.equalsIgnoreCase("png") || ext.equalsIgnoreCase("jpg") || ext.equalsIgnoreCase("gif")))
                imagen = "NULL";
            else {
                imagen = "'"+ext+"'";
            }
        }
        
        Statement st = Database.getInstance().createStatement();
        
        String sql = "INSERT INTO pregunta (enunciado, alta, altb, altc, altd, alte, correcta, justificacion, imagen, id_creador) VALUES "+
                "('"+addSlashes(bean.getEnunciado())+"','"+addSlashes(bean.getAlternativaa_texto())+"','"+addSlashes(bean.getAlternativab_texto())+"',"+
                "'"+addSlashes(bean.getAlternativac_texto())+"','"+addSlashes(bean.getAlternativad_texto())+"','"+addSlashes(bean.getAlternativae_texto())+"','"+
                +(bean.getCorrecta().charAt(0))+"','"+addSlashes(bean.getJustificacion())+"',"+imagen+","+id_creador+")";
        
        System.err.println("QDAO.addQ::SQL = "+sql);
        st.executeUpdate(sql);

        
        //ahora necesito el ID para agregar la imagen ;)
        ResultSet rs = st.getGeneratedKeys();
        if(!rs.last())
            throw new SQLException("No pude obtener el ID de la pregunta insertada! ERROR!!!");
        int id = rs.getInt(1);
        
        
        //ahora los tags
        
        
        st.execute("INSERT INTO tag_pregunta (tag, id_pregunta) VALUES ('"+bean.getTagPadre()+"',"+id+")");

        
        if(bean.getTagHijos() != null) {
            for(String t : bean.getTagHijos()) {
                st.execute("INSERT INTO tag_pregunta (tag, id_pregunta) VALUES ('"+t+"',"+id+")");
            }
        }

        
        
        System.err.println("QDAO.addQ::ID insertado = "+id);
        
        if(ext!=null) {
            try {
                FileOutputStream out = new FileOutputStream(new File(path+id+"."+ext));
                InputStream upl = bean.getImagen().getInputStream();
                int esteByte;
                while((esteByte = upl.read())>=0)
                    out.write((byte)esteByte);
                upl.close();
                out.close();
            } catch (Exception e) {
                System.err.println("QDAO.addQ::Error al escribir archivo!");
            }
        }
        
        
    }
    
    private String addSlashes(String orig) {
        return orig==null?"":orig.replaceAll("'", "\\'").replaceAll("\\\\","\\\\");
    }
    public int UpdateDificultad(int idQ,int dificultad)
    {
        try {
        
            Statement st = Database.getInstance().createStatement();
            ResultSet rs = st.executeQuery("SELECT dificultad, ndificultad FROM pregunta WHERE id="+idQ);
            
            int n;
            double d;
            
            if(rs.next()) {
                n = rs.getInt("ndificultad");
                d = rs.getDouble("dificultad");
            } else
                return dificultad;
        
            if(n==0) {
                d = dificultad;
                n++;
            } else {
                d = (d*n+dificultad);
                n++;
                d /= n;
            }
            
            rs.close();
            
            st.executeUpdate("UPDATE pregunta SET dificultad="+d+", ndificultad="+n+" WHERE id="+idQ);
            
            return (int)(d+0.5);
        } catch(SQLException e) {
            return dificultad;
        }
    }
     public int UpdateCalidad(int idQ,int calidad)
    {
        try {
        
            Statement st = Database.getInstance().createStatement();
            ResultSet rs = st.executeQuery("SELECT calidad, ncalidad FROM pregunta WHERE id="+idQ);
            
            int n;
            double c;
            
            if(rs.next()) {
                n = rs.getInt("ncalidad");
                c = rs.getDouble("calidad");
            } else
                return calidad;
        
            if(n==0) {
                c = calidad;
                n++;
            } else {
                c = (c*n+calidad);
                n++;
                c /= n;
            }
            
            rs.close();
            
            st.executeUpdate("UPDATE pregunta SET calidad="+c+", ncalidad="+n+" WHERE id="+idQ);
            
            return (int)(c+0.5);
        } catch(SQLException e) {
            return calidad;
        }
    }
}