package eu.dnetlib.data.claims.handler;

import eu.dnetlib.data.claims.entity.*;
import eu.dnetlib.data.claims.sql.SQLStoreException;
import eu.dnetlib.data.claims.sql.SqlDAO;
import eu.dnetlib.data.claims.utils.ClaimUtils;
import eu.dnetlib.data.claims.utils.JsonldBuilder;
import eu.dnetlib.data.claims.utils.QueryGenerator;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by argirok on 9/3/2016.
 */
public class FetchClaimHandler {
    private Logger log = LogManager.getLogger(this.getClass());

    SqlDAO sqlDAO = null;
    QueryGenerator queryGenerator = null;

    public FetchClaimHandler(){
//        ApplicationContext context = new ClassPathXmlApplicationContext("../claims/migration/springContext-claims.xml");
//        sqlDAO = context.getBean(SqlDAO.class);

    }

    public String claims2JSON(List<Claim> claims) throws Exception {
        return JsonldBuilder.toJsonld(claims);
    }
    public String claim2JSON(Claim claim) throws Exception {
        return JsonldBuilder.toJsonld(claim);
    }

    /**
     *
     * @param user - user e-mail stored in claims DB
     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)
     * @param offset returns claims after 'offset' position (null for no offset)
     * @return
     * @throws Exception
     */
    public List<Claim> fetchClaimsByUser(String user, Integer limit, Integer offset, List<String> types, boolean addCurationInfo) throws Exception, SQLStoreException {
        return fetchClaimsByUser(user,limit,offset,null,"claim.claim_date",true, types, addCurationInfo);
    }
    /**
     *
     * @param user - user e-mail stored in claims DB
     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)
     * @param offset returns claims after 'offset' position (null for no offset)
     * @param keyword filters in specific fields per type (result: {title,doi},project: {name.acronym. funder_name, funder_acronym}, context: {name})
     * @param orderField
     * @param descending
     * @return
     * @throws Exception
     */
    public List<Claim> fetchClaimsByUser(String user, Integer limit, Integer offset,String keyword,String orderField,boolean descending, List<String> types, boolean addCurationInfo) throws Exception, SQLStoreException {
        log.info("Fetching claims for user"+user);
        ArrayList<Object> params = new ArrayList<>();
        String query = queryGenerator.generateFetchClaimsByUser(user, limit, offset,keyword,orderField,descending, types, params);
        ResultSet rs = sqlDAO.executePreparedQuery(query, params);
        return fetchClaimsByResultSet(rs, addCurationInfo);
    }
    /**
     *
     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)
     * @param offset returns claims after 'offset' position (null for no offset)
     * @return
     * @throws Exception
     */
    public List<Claim> fetchAllClaims( Integer limit, Integer offset, boolean addCurationInfo, String user) throws Exception, SQLStoreException {
        log.info("Fetching all claims");
         return fetchAllClaims(limit,offset,null,"claim.claim_date",true,new ArrayList<String>(), addCurationInfo, user);
    }

    /**
     *
     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)
     * @param offset returns claims after 'offset' position (null for no offset)
     * @param keyword  filters in specific fields per type (result: {title,doi},project: {name.acronym. funder_name, funder_acronym}, context: {name})
     * @param orderField
     * @param descending
     * @return
     * @throws Exception
     */
    public List<Claim> fetchAllClaims( Integer limit, Integer offset, String keyword, String orderField, boolean descending, List<String> types, boolean addCurationInfo, String user) throws Exception, SQLStoreException {
        log.info("Fetching all claims");
        ArrayList<Object> params = new ArrayList<>();
        String query = queryGenerator.generateFetchClaims(limit, offset,keyword,orderField,descending,types, params, user);
        ResultSet rs = sqlDAO.executePreparedQuery(query, params);
        return fetchClaimsByResultSet(rs, addCurationInfo);
    }

    /**
     *
     * @param dateFrom
     * @param dataTo
     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)
     * @param offset returns claims after 'offset' position (null for no offset)
     * @return
     * @throws Exception
     */
    public List<Claim> fetchClaimsByDate(String dateFrom, String dataTo, Integer limit, Integer offset, boolean addCurationInfo) throws Exception, SQLStoreException {
         return fetchClaimsByDate(dateFrom, dataTo, limit, offset, null, "claim.claim_date",true, new ArrayList<String>(),addCurationInfo);
    }
    /**
     *
     * @param dateFrom
     * @param dataTo
     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)
     * @param offset returns claims after 'offset' position (null for no offset)
     * @return
     * @throws Exception
     */
    public List<Claim> fetchClaimsByDate(String dateFrom, String dataTo, Integer limit, Integer offset, String keyword, String orderField, boolean descending, List<String> types, boolean addCurationInfo) throws Exception, SQLStoreException {
        ArrayList<Object> params = new ArrayList<>();
        String query = queryGenerator.generateFetchClaimsByDate(dateFrom, dataTo, limit, offset,keyword, orderField, descending,types, params);
        ResultSet rs = sqlDAO.executePreparedQuery(query, params);
        return fetchClaimsByResultSet(rs, addCurationInfo);
    }

    public List<Claim> fetchClaimsByDateForDashboards(String dateFrom, String dataTo, Integer limit, Integer offset, boolean addCurationInfo, ArrayList<String> dashboards) throws Exception, SQLStoreException {
        ArrayList<Object> params = new ArrayList<>();
        String query = queryGenerator.generateFetchClaimsByDateForDashboards(dateFrom, dataTo, limit, offset,null, "claim.claim_date", true,new ArrayList<String>(), params, dashboards);
        ResultSet rs = sqlDAO.executePreparedQuery(query, params);
        return fetchClaimsByResultSet(rs, addCurationInfo);
    }
    /**
     *
     * @param dateFrom
     * @param dataTo
     * @param openaireId for project
     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)
     * @param offset returns claims after 'offset' position (null for no offset)
     * @return
     * @throws Exception
     */
    public int fetchNumberOfClaimsByDateAndOpenaireId(String dateFrom, String dataTo, String openaireId, Integer limit, Integer offset, String keyword, String orderField, boolean descending, List<String> types, boolean addCurationInfo) throws Exception, SQLStoreException {
        ArrayList<Object> params = new ArrayList<>();
        String query = queryGenerator.generateFetchNumberOfClaimsByDateAndOpenaireId(dateFrom, dataTo, openaireId, limit, offset,keyword, orderField, descending,types, params);
        ResultSet rs = sqlDAO.executePreparedQuery(query, params);
        return countClaimsByResultSet(rs);
    }

    /**
     *
     * @param openaireId for project
     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)
     * @param offset returns claims after 'offset' position (null for no offset)
     * @return
     * @throws Exception
     */
    public List<Claim> fetchClaimsByProject(String openaireId, Integer limit, Integer offset, boolean addCurationInfo) throws Exception, SQLStoreException {

        return fetchClaimsByProject(openaireId,limit, offset, null, "claim.claim_date",true, new ArrayList<String>(), addCurationInfo);
    }
    /**
     *
     * @param openaireId for project
     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)
     * @param offset returns claims after 'offset' position (null for no offset)
     * @param orderField
     * @param descending
     * @return
     * @throws Exception
     */
    public List<Claim> fetchClaimsByProject(String openaireId, Integer limit, Integer offset, String keyword, String orderField, boolean descending, List<String> types, boolean addCurationInfo) throws Exception, SQLStoreException {
        log.info("Fetching claims by project ID:"+openaireId);
        ArrayList<Object> params = new ArrayList<>();
        String query = queryGenerator.generateFetchClaimsByProject(openaireId, limit, offset, keyword, orderField,descending,types, params);
        ResultSet rs = sqlDAO.executePreparedQuery(query, params);
        return fetchClaimsByResultSet(rs, addCurationInfo);
    }
    public List<Claim> fetchClaimsByOrganization(String openaireId, Integer limit, Integer offset, String keyword, String orderField, boolean descending, List<String> types, boolean addCurationInfo, String user) throws Exception, SQLStoreException {
        log.info("Fetching claims by Organization ID:"+openaireId);
        ArrayList<Object> params = new ArrayList<>();
        String query = queryGenerator.generateFetchClaimsByOrganization(openaireId, limit, offset, keyword, orderField,descending,types, params, user);
        ResultSet rs = sqlDAO.executePreparedQuery(query, params);
        return fetchClaimsByResultSet(rs, addCurationInfo);
    }
    /**
     *
     * @param openaireId of Funder
     * @param limit
     * @param offset
     * @return
     * @throws Exception
     */
    public List<Claim> fetchClaimsByFunder(String openaireId, Integer limit, Integer offset, boolean addCurationInfo) throws Exception, SQLStoreException {
         return fetchClaimsByFunder(openaireId,limit,offset,null,"claim.claim_date",true, new ArrayList<String>(), addCurationInfo);
    }
    /**
     *
     * @param openaireId of Funder
     * @param limit
     * @param offset
     * @param orderField
     * @param descending
     * @return
     * @throws Exception
     */
    public List<Claim> fetchClaimsByFunder(String openaireId, Integer limit, Integer offset,String keyword, String orderField, boolean descending, List<String> types, boolean addCurationInfo) throws Exception, SQLStoreException {
        ArrayList<Object> params = new ArrayList<>();
        String query = queryGenerator.generateFetchClaimsByFunder(openaireId, limit, offset,keyword, orderField,descending,types, params);
        ResultSet rs = sqlDAO.executePreparedQuery(query, params);
        return fetchClaimsByResultSet(rs, addCurationInfo);
    }
    /**
     *
     * @param openaireId for result
     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)
     * @param offset returns claims after 'offset' position (null for no offset)
     * @return
     * @throws Exception
     */
    public List<Claim> fetchClaimsByResult(String openaireId, Integer limit, Integer offset, boolean addCurationInfo) throws Exception, SQLStoreException {
         return fetchClaimsByResult(openaireId,limit,offset,null, "claim.claim_date",true, new ArrayList<String>(), addCurationInfo);
    }
    /**
     *
     * @param openaireId for result
     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)
     * @param offset returns claims after 'offset' position (null for no offset)
     * @param orderField
     * @param descending
     * @return
     * @throws Exception
     */
    public List<Claim> fetchClaimsByResult(String openaireId, Integer limit, Integer offset,String keyword, String orderField, boolean descending, List<String> types, boolean addCurationInfo) throws Exception, SQLStoreException {
        log.info("Fetching claims by result ID:"+openaireId);
        ArrayList<Object> params = new ArrayList<>();
        String query = queryGenerator.generateFetchClaimsByResult(openaireId, limit, offset,keyword, orderField, descending,types, params);
        ResultSet rs = sqlDAO.executePreparedQuery(query, params);
        return fetchClaimsByResultSet(rs, addCurationInfo);
    }
    /**
     *
     * @param openaireId
     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)
     * @param offset returns claims after 'offset' position (null for no offset)
     * @return
     * @throws Exception
     */
    public List<Claim> fetchClaimsByContext(String openaireId, Integer limit, Integer offset, boolean addCurationInfo, String user) throws Exception, SQLStoreException {
         return fetchClaimsByContext(openaireId,limit,offset,null,"claim.claim_date",true, new ArrayList<String>(), addCurationInfo, user);
    }
    /**
     *
     * @param openaireId
     * @param limit returns at most 'limit' claims  (use -1 or null for no limit)
     * @param offset returns claims after 'offset' position (null for no offset)
     * @param orderField
     * @param descending
     * @return
     * @throws Exception
     */
    public List<Claim> fetchClaimsByContext(String openaireId, Integer limit, Integer offset, String keyword, String orderField, boolean descending, List<String> types, boolean addCurationInfo, String user) throws Exception, SQLStoreException {
        log.info("Fetching claims by context ID:"+openaireId);
        ArrayList<Object> params = new ArrayList<>();
        String query = queryGenerator.generateFetchClaimsByContext(openaireId, limit, offset,keyword,orderField, descending,types, params, user);
        ResultSet rs = sqlDAO.executePreparedQuery(query, params);
        return fetchClaimsByResultSet(rs, addCurationInfo);
    }
    public Claim fetchClaimById(String claimId, boolean addCurationInfo) throws Exception, SQLStoreException {
        ArrayList<Object> params = new ArrayList<>();
        String query = queryGenerator.generateSelectClaimQuery(claimId, params);
        ResultSet rs = sqlDAO.executePreparedQuery(query, params);
        if(rs.next()) {
            String sourceType =rs.getString(2);
//            String sourceId =rs.getString(3);
            String targetType =rs.getString(4);
//            String targetId =rs.getString(5);
            ArrayList<Object> params2 = new ArrayList<>();
            String query2 = queryGenerator.generateFetchClaimsByClaimId(claimId,sourceType,targetType, params2);
           rs = sqlDAO.executePreparedQuery(query2, params2);
        }else{
            log.info("No claim with id : "+ claimId+"\n");
        }
        Claim claim = null;
        List<Claim> claims=fetchClaimsByResultSet(rs, addCurationInfo);
        if(claims.size()==1){
            claim=claims.get(0);
        }
        return claim;
    }

    /*
    public List<Claim> fetchClaimsByToken(String token, String email, Integer limit, Integer offset, boolean addCurationInfo) throws Exception {
        return fetchClaimsByToken(token, email, limit, offset, null, "claim.claim_date",true, new ArrayList<String>(), addCurationInfo);
    }

    public List<Claim> fetchClaimsByToken(String token, String email, Integer limit, Integer offset, String keyword, String orderField, boolean descending, List<String> types, boolean addCurationInfo) throws Exception {
        ResultSet rs = sqlDAO.executePreparedQuery(queryGenerator.generateFetchClaimsByProjectToken(token, email, limit, offset, keyword, orderField,descending,types));
        return fetchClaimsByResultSet(rs, addCurationInfo);
    }
    */


    public List<Claim> fetchClaimsByResultSet(ResultSet rs, boolean addCurationInfo) throws Exception {
        List<Claim> claims= new ArrayList<Claim>();
        while(rs.next()) {
            Claim claim= new Claim();
            claims.add(claim);
            Integer i = 1;
            claim.setId(rs.getString(i++));
            DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Date date = format.parse(rs.getString(i++));
            claim.setDate(date);
            claim.setUserMail(rs.getString(i++));
            claim.setSourceType(rs.getString(i++));
            claim.setTargetType(rs.getString(i++));
            claim.setSource(buildEntity(rs,i,claim.getSourceType()));
            claim.setTarget(buildEntity(rs,i+3,claim.getTargetType()));

        }
        return claims;
    }
    public Integer countClaimsByResultSet(ResultSet rs) throws Exception {

        while(rs.next()) {
            return rs.getInt(1);
        }
        return null;
    }
    public List<Project> getProjectsByResultSet(ResultSet rs, boolean addCurationInfo) throws Exception {
        List<Project> projects= new ArrayList<Project>();
        while(rs.next()) {
            Project project=(Project)(buildEntity(rs, 1, ClaimUtils.PROJECT));
             projects.add(project);
        }
        return projects;
    }
    public List<Context> getContextsByResultSet(ResultSet rs, boolean addCurationInfo) throws Exception {
        List<Context> contexts= new ArrayList<Context>();
        while(rs.next()) {
            Context context=(Context)(buildEntity(rs, 1, ClaimUtils.CONTEXT));
            contexts.add(context);
        }
        return contexts;
    }
    public Integer countAllClaims(String keyword, List<String> types, String user) throws Exception, SQLStoreException {
        ArrayList<Object> params = new ArrayList<>();
        String query = queryGenerator.generateCountAllClaims(keyword,types, params, user);
        ResultSet rs = sqlDAO.executePreparedQuery(query, params);
        return countClaimsByResultSet(rs);
    }
    public Integer countClaimsByUser(String user,String keyword, List<String> types) throws Exception, SQLStoreException {
        ArrayList<Object> params = new ArrayList<>();
        String query = queryGenerator.generateCountByUser(user,keyword,types, params);
        ResultSet rs = sqlDAO.executePreparedQuery(query, params);
        return countClaimsByResultSet(rs);
    }
    public Integer countClaimsByProject(String projectId,String keyword, List<String> types) throws Exception, SQLStoreException {
        ArrayList<Object> params = new ArrayList<>();
        String query = queryGenerator.generateCountByProject(projectId,keyword,types, params);
        ResultSet rs = sqlDAO.executePreparedQuery(query, params);
        return countClaimsByResultSet(rs);
    }
    public Integer countClaimsByOrganization(String projectId,String keyword, List<String> types, String user) throws Exception, SQLStoreException {
        ArrayList<Object> params = new ArrayList<>();
        String query = queryGenerator.generateCountByOrganization(projectId,keyword,types, params, user);
        ResultSet rs = sqlDAO.executePreparedQuery(query, params);
        return countClaimsByResultSet(rs);
    }
    /*
    public Integer countClaimsByProjectToken(String projectToken,String email,String keyword, List<String> types)throws Exception {
        ResultSet rs = sqlDAO.executePreparedQuery(queryGenerator.generateCountByProjectToken(projectToken,email,keyword,types));
        return countClaimsByResultSet(rs);
    }
    */
    public Integer countClaimsByFunder(String funderId,String keyword, List<String> types) throws Exception, SQLStoreException {
        ArrayList<Object> params = new ArrayList<>();
        String query = queryGenerator.generateCountByFunder(funderId,keyword,types, params);
        ResultSet rs = sqlDAO.executePreparedQuery(query, params);
        return countClaimsByResultSet(rs);
    }
    public Integer countClaimsByResult(String resultId,String keyword, List<String> types) throws Exception, SQLStoreException {
        ArrayList<Object> params = new ArrayList<>();
        String query = queryGenerator.generateCountByResult(resultId,keyword,types, params);
        ResultSet rs = sqlDAO.executePreparedQuery(query, params);
        return countClaimsByResultSet(rs);
    }
    public Integer countClaimsByContext(String contextId,String keyword, List<String> types, String user) throws Exception, SQLStoreException {
        ArrayList<Object> params = new ArrayList<>();
        String query = queryGenerator.generateCountByContext(contextId,keyword,types, params, user);
        ResultSet rs = sqlDAO.executePreparedQuery(query, params);
        return countClaimsByResultSet(rs);
    }
    public Integer countClaimsByDate(String dateFrom,String dateTo,String keyword, List<String> types) throws Exception, SQLStoreException {
        ArrayList<Object> params = new ArrayList<>();
        String query = queryGenerator.generateCountByDate(dateFrom,dateTo,keyword,types, params);
        ResultSet rs = sqlDAO.executePreparedQuery(query, params);
        return countClaimsByResultSet(rs);
    }

    public List<Project> getAllclaimedProjects(boolean addCurationInfo) throws Exception, SQLStoreException {
        ResultSet rs = sqlDAO.executePreparedQuery(queryGenerator.generateFetchAllProjectsQuery());
        return getProjectsByResultSet(rs,addCurationInfo);
    }
    public List<Context> getAllclaimedContexts( boolean addCurationInfo) throws Exception, SQLStoreException {
        ResultSet rs = sqlDAO.executePreparedQuery(queryGenerator.generateFetchAllContextsQuery());
        return getContextsByResultSet(rs,addCurationInfo);
    }
    /**
     *
     * @param rs
     * @param index
     * @return
     * @throws SQLException
     */
    private static Result buildResult(ResultSet rs, Integer index) throws SQLException {
        Result result = new Result();
        result.setOpenaireId(rs.getString(index));
        result.setTitle(rs.getString(index + 1));
        result.setCollectedFrom(rs.getString(index + 2));
        return result;

    }
    private static Project buildProject(ResultSet rs, Integer index) throws SQLException {
        Project project = new Project();
        project.setOpenaireId(rs.getString(index));
        project.setName(rs.getString(index + 1));
        project.setFunderName(rs.getString(index + 2));
        return project;

    }
    private static Organization buildOrganization(ResultSet rs, Integer index) throws SQLException {
        Organization organization = new Organization();
        organization.setOpenaireId(rs.getString(index));
        organization.setName(rs.getString(index + 1));
        organization.setShortName(rs.getString(index + 2));
        return organization;

    }
    private static Context buildContext(ResultSet rs, Integer index) throws SQLException {
        Context context = new Context();
        context.setOpenaireId(rs.getString(index));
        context.setTitle(rs.getString(index + 1));
        return context;

    }
    private static OpenaireEntity buildEntity(ResultSet rs, Integer index, String type) throws SQLException {
        OpenaireEntity openaireEntity = null;
        if(type == null){
            openaireEntity = null;
        }else if(type.equals(ClaimUtils.PUBLICATION)||type.equals(ClaimUtils.DATASET)||type.equals(ClaimUtils.SOFTWARE)||type.equals(ClaimUtils.OTHER)) {
            openaireEntity= buildResult(rs, index);
        }else if(type.equals(ClaimUtils.PROJECT)){
            openaireEntity = buildProject(rs,index);
        }else if(type.equals(ClaimUtils.ORGANIZATION)){
            openaireEntity = buildOrganization(rs,index);
        }else if(type.equals(ClaimUtils.CONTEXT)){
            openaireEntity = buildContext(rs,index);
        }
        return openaireEntity;
    }

    public SqlDAO getSqlDAO() {
        return sqlDAO;
    }

    public void setSqlDAO(SqlDAO sqlDAO) {
        this.sqlDAO = sqlDAO;
    }

    public QueryGenerator getQueryGenerator() {
        return queryGenerator;
    }

    public void setQueryGenerator(QueryGenerator queryGenerator) {
        this.queryGenerator = queryGenerator;
    }
}