package br.com.elotech.websaude.odontograma.backend.model;

import br.com.elotech.websaude.odontograma.ui.Application;
import br.com.elotech.websaude.odontograma.util.Cor;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

import static br.com.elotech.websaude.odontograma.backend.model.dao.Conexao.CONN;

public class ProcedimentoModel {

    static String SQL = "";
    static PreparedStatement pstmt = null;

    public ResultSet buscaProcedimentoOdontologico() throws SQLException {

        SQL = "SELECT proc.proc_codigo,proc.proc_nome,proc.proc_codigo_sus FROM procedimento proc " +
                "INNER JOIN rl_procedimento_ocupacao rl ON rl.co_procedimento=proc.proc_codigo_sus " +
                "INNER JOIN especialidade esp ON esp.cod_cbo=rl.co_ocupacao " +
                "WHERE esp.esp_codigo=? " +
                "ORDER BY proc.proc_nome";
        pstmt = CONN.prepareStatement(SQL);
        pstmt.setInt(1, Integer.valueOf(Application.args[6]));

        return pstmt.executeQuery();
    }

    public String getCorProcedimento(Integer procCodigo) throws SQLException {

        SQL = "SELECT proc_cor_hexa FROM procedimento WHERE proc_codigo=?";
        pstmt = CONN.prepareStatement(SQL);
        pstmt.setInt(1, procCodigo);

        ResultSet rs = pstmt.executeQuery();

        if(rs.next()){
            if(rs.getString("proc_cor_hexa")!=null)
                if(rs.getString("proc_cor_hexa").length()>0)
                    return rs.getString("proc_cor_hexa");
        }

        List<String> cores = new ArrayList<>();
        SQL = "SELECT distinct proc_cor_hexa FROM procedimento WHERE proc_cor_hexa is not null";
        pstmt = CONN.prepareStatement(SQL);

        rs = pstmt.executeQuery();

        while(rs.next()){
            cores.add(rs.getString("proc_cor_hexa"));
        }

        String corGerada = Cor.gerarCor(cores);

        SQL = "UPDATE procedimento SET proc_cor_hexa=? WHERE proc_codigo=?";
        pstmt = CONN.prepareStatement(SQL);
        pstmt.setString(1, corGerada);
        pstmt.setInt(2, procCodigo);

        pstmt.executeUpdate();
        CONN.commit();

        return corGerada;
    }

    public List<String> getCoresExistentes() throws SQLException {
        List<String> cores = new ArrayList<>();
        SQL = "SELECT distinct proc_cor_hexa FROM procedimento WHERE proc_cor_hexa is not null";
        pstmt = CONN.prepareStatement(SQL);

        ResultSet rs = pstmt.executeQuery();

        while(rs.next()){
            cores.add(rs.getString("proc_cor_hexa"));
        }
        return cores;
    }

    public void alterarCor(String antiga, String nova) throws SQLException {

        SQL = "UPDATE procedimento SET proc_cor_hexa=? WHERE proc_cor_hexa=?";
        pstmt = CONN.prepareStatement(SQL);
        pstmt.setString(1,nova);
        pstmt.setString(2,antiga);
        pstmt.executeUpdate();
        CONN.commit();
    }

    public Long getProcedureByColor(String color) {
        SQL = "SELECT proc_codigo FROM procedimento WHERE proc_cor_hexa=?";
        try {
            pstmt = CONN.prepareStatement(SQL);
            pstmt.setString(1,color);
            ResultSet rs = pstmt.executeQuery();
            if(rs.next())
                return rs.getLong("proc_codigo");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0L;
    }

    public String getNomeProcedimento(Long odoProcCodigo) {
        SQL = "SELECT proc_nome FROM procedimento WHERE proc_codigo=?";
        try {
            pstmt = CONN.prepareStatement(SQL);
            pstmt.setLong(1,odoProcCodigo);
            ResultSet rs = pstmt.executeQuery();
            if(rs.next())
                return rs.getString("proc_nome");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return "";
    }

    public ResultSet getMarcacoesPossiveis() {
        SQL = "SELECT proc_codigo as codigo,upper(proc_nome) as marcacao,proc_odonto_marcacao  FROM procedimento WHERE proc_odonto_marcacao is not null " +
                "ORDER BY proc_codigo";
        try {
            pstmt = CONN.prepareStatement(SQL);
            System.out.println(pstmt);
            return pstmt.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
}