package eu.dnetlib.data.claimsDemo;

import eu.dnetlib.data.claims.migration.entity.*;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import org.apache.log4j.Logger;


public class QueryGenerator {
    boolean updateOnInsert;
    String migrationTable ="claims"; //a table or a view containing old schema claims - But only user claims, not those where agent='inference'
    private static final Logger logger = Logger.getLogger(QueryGenerator.class);

    public boolean isUpdateOnInsert() {
        return updateOnInsert;
    }

    public void setUpdateOnInsert(boolean updateOnInsert) {
        this.updateOnInsert = updateOnInsert;
    }

    /**
  ** Insert Queries **
  **/
    private String generateInsertResultQuery(String openaire_id, String result_type, String title, String collected_from , String external_url, String doi, String orcidworkid, String access_rights , String embargo_end_date, String best_license, String record_path, String record_format) {
        if(title!=null&&title.contains("'")){
            title=title.replace("'","''");
        }
        String fields="openaire_id";
        if(result_type!=null){
            fields+=", result_type";
        }
        if(title!=null){
            fields+=", title";
        }
        if(collected_from!=null){
            fields+=", collected_from";
        }
        if(external_url!=null){
            fields+=", external_url";
        }
        if(doi!=null){
            fields+=", doi";
        }
        if(orcidworkid!=null){
            fields+=", orcidworkid";
        }
        if(access_rights!=null){
            fields+=", access_rights";
        }
        if(embargo_end_date!=null){
            fields+=", embargo_end_date";
        }
        if(best_license!=null){
            fields+=", best_license";
        }
        if(record_path!=null){
            fields+=", record_path";
        }
        if(record_format!=null){
            fields+=", record_format";
        }
        String values="'"+openaire_id+"'";
        if(result_type!=null){
            values+=",'"+result_type+"'";
        }
        if(title!=null){
            values+=",'"+title+"'";
        }
        if(collected_from!=null){
            values+=",'"+collected_from+"'";
        }
        if(external_url!=null){
            values+=",'"+external_url+"'";
        }
        if(doi!=null){
            values+=",'"+doi+"'";
        }
        if(orcidworkid!=null){
            values+=",'"+orcidworkid+"'";
        }
        if(access_rights!=null){
            values+=",'"+access_rights+"'";
        }
        if(embargo_end_date!=null){
            values+=",'"+embargo_end_date+"'";
        }
        if(best_license!=null){
            values+=",'"+best_license+"'";
        }
        if(record_path!=null){
            values+=",'"+record_path+"'";
        }
        if(record_format!=null){
            values+=",'"+record_format+"'";
        }
        return "INSERT INTO result("+fields+")\n" +
                "    Select "+values+
                "    where not exists (select openaire_id from result where openaire_id='"+openaire_id+"')\n" +
                "    RETURNING openaire_id";
        //TODO update on insert when exists
    }
    public String generateInsertProjectQuery(String openaire_id, String name, String acronym, String funder_id, String funder_name, String funder_acronym, List<String> contactEmails) {
        if(name!=null) {
            name = name.replace("'", "''");
        }
        String fields="openaire_id";
        if(name!=null){
            fields+=",name";
        }
        if(acronym!=null){
            fields+=",acronym";
        }
        if(funder_id!=null){
            fields+=",funder_id";
        }
        if(funder_name!=null){
            fields+=",funder_name";
        }
        if(funder_acronym!=null){
            fields+=",funder_acronym";
        }
        if(contactEmails != null && contactEmails.size() > 0){
            fields+=",contact_person";
        }
        String values="'"+openaire_id+"'";
        if(name!=null){
            values+=",'"+name+"'";
        }
        if(acronym!=null){
            values+=",'"+acronym+"'";
        }
        if(funder_id!=null){
            values+=",'"+funder_id+"'";
        }
        if(funder_name!=null){
            values+=",'"+funder_name+"'";
        }
        if(funder_acronym!=null){
            values+=",'"+funder_acronym+"'";
        }
        if(contactEmails != null && contactEmails.size() > 0){
            values+=",'{";
            for(int i = 0; i< contactEmails.size(); i++){

                values+="\""+contactEmails.get(i)+"\""+((i < contactEmails.size() -1 )?",":"");
            }
            values+="}'";
        }
        return "INSERT INTO project("+fields+")\n" +
                "    Select "+values +
                "    where not exists (select openaire_id from project where openaire_id='"+openaire_id+"')\n" +
                "    RETURNING openaire_id";
        //TODO update on insert when exists
    }
    private String generateInsertContextQuery(String openaire_id, String name) {
        String fields="openaire_id";
        if(name!=null){
            fields+=",name";
        }
        String values="'"+openaire_id+"'";
        if(name!=null){
            values+=",'"+name+"'";
        }
        return "INSERT INTO context("+fields+")\n" +
                "    Select "+values+
                "    where not exists (select openaire_id from context where openaire_id='"+openaire_id+"')\n" +
                "    RETURNING openaire_id";
        //TODO update on insert when exists
    }
    private String generateInsertClaimQuery(Date date, String claimedBy, String source_type, String target_type , String source_id , String target_id, String semantics) {

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String dateStr=null;
        dateStr=(format.format(date));
        String fields="claim_date,claimedBy,source_type,target_type, source_id, target_id";
        if(semantics!=null){
            fields+=",semantics";
        }
        String values="'"+dateStr+"','"+claimedBy+"','"+source_type+"', '"+target_type+"','"+source_id+"','"+target_id+"'";
        if(semantics!=null){
            values+=",'"+semantics+"'";
        }

        return "     INSERT INTO claim( "+fields+")\n" +
                "    VALUES ( "+values+")\n" +
                "    RETURNING id, source_id, target_id\n";
    }

    private  String generateInsertFullClaimQuery(String targetQuery, String sourceQuery, Claim claim) {
        if (claim ==null || targetQuery ==null || sourceQuery == null){
            return null;
        }
        String sourceTableName= getSourceTableName(claim.getSourceType());
        String targetTableName= getTargetTableName(claim.getTargetType());
        if (sourceTableName ==null || targetTableName ==null ){
            return null;
        }
        return " WITH target AS (\n" +
                targetQuery+
                "    ),\n source AS (\n" +
                sourceQuery+
                "   ),\n myclaim AS (\n" +
                generateInsertClaimQuery(claim.getDate(), claim.getUserMail(), claim.getSourceType(), claim.getTargetType(), claim.getSource().getOpenaireId(), claim.getTarget().getOpenaireId(),this.createSemanticsType(claim.getSourceType(),claim.getTargetType())) +
                "   ),\n ins4 AS (\n" +
                "    INSERT INTO "+sourceTableName+" (claim_id, openaire_id)\n" +
                "    SELECT id, source_id\n" +
                "    FROM   myclaim) \n"+
                "    INSERT INTO "+targetTableName+"(claim_id, openaire_id)\n" +
                "    SELECT id, target_id\n" +
                "    FROM   myclaim "+
                " RETURNING claim_id;";
    }
    public  String generateInsertFullClaimQuery( Claim claim) {
        String targetQuery =null;
        String sourceQuery=null;
        targetQuery=generateInsertEntityQuery(claim.getTarget(),claim.getTargetType());
        sourceQuery=generateInsertEntityQuery(claim.getSource(),claim.getSourceType());
        return generateInsertFullClaimQuery(targetQuery,sourceQuery,claim);

    }
    private String generateInsertEntityQuery(OpenaireEntity openaireEntity, String type){
        String query=null;
        if(type==null){
            return null;
        }
        if(type.equals(ClaimUtils.DATASET)||type.equals(ClaimUtils.PUBLICATION)){
            Result result=(Result)openaireEntity;
            query=generateInsertResultQuery(result.getOpenaireId(),result.getResultType(),result.getTitle(),result.getCollectedFrom(),result.getExternalUrl(),result.getDoi(), result.getOrcidworkid(), result.getAccessRights(),result.getEmbargoEndDate(),result.getBestLicense(),result.getRecordPath(), result.getRecordFormat());
        }else if(type.equals(ClaimUtils.PROJECT)){
            Project project=(Project)openaireEntity;
            query=generateInsertProjectQuery(project.getOpenaireId(),project.getName(),project.getAcronym(),project.getFunderId(),project.getFunderName(),project.getFunderShortName(),project.getContactEmails());
        }else if(type.equals(ClaimUtils.CONTEXT)){
            Context context= (Context)openaireEntity;
            query=generateInsertContextQuery(context.getOpenaireId(),context.getTitle());
        }

        return query;
    }

    private String getSourceTableName(String type){
        String tablename="has_source_";
        if(type==null){
            return null;
        }else{
            tablename+=getTypeForTable(type);
        }
        return tablename;
    }
    private String getTargetTableName(String type){
        String tablename="has_target_";
        if(type==null){
            return null;
        }else{
            tablename+=getTypeForTable(type);
        }
        return tablename;
    }
    public  String generateInsertClaimResultQuery(String targetQuery, String sourceQuery, Claim claim) {

        return " WITH ins1 AS (\n" +
                targetQuery+
                "    ),\n ins2 AS (\n" +
                sourceQuery+
                "   ),\n ins3 AS (\n" +
               generateInsertClaimQuery(claim.getDate(), claim.getUserMail(), claim.getSourceType(), claim.getTargetType(), claim.getSource().getOpenaireId(), claim.getTarget().getOpenaireId(),this.createSemanticsType(claim.getSourceType(),claim.getTargetType())) +
                "   ),\n ins4 AS (\n" +
                "    INSERT INTO has_source_result (claim_id, openaire_id)\n" +
                "    SELECT id, source_id\n" +
                "    FROM   ins3) \n"+
                "    INSERT INTO has_target_result(claim_id, openaire_id)\n" +
                "    SELECT id, target_id\n" +
                "    FROM   ins3;";
    }

    /**
     *
     * @param sourceType
     * @param targetType
     * @return "resultResult_"+targetType+sourceType+"_isRelatedTo"
     */
    private String createSemanticsType(String sourceType,String targetType){
        String semantics=null;
        if((targetType.equals(ClaimUtils.PUBLICATION)||targetType.equals(ClaimUtils.DATASET))&&(sourceType.equals(ClaimUtils.PUBLICATION)||sourceType.equals(ClaimUtils.DATASET))){
            semantics="resultResult_"+targetType+sourceType+"_isRelatedTo";
        }
        else if((targetType.equals(ClaimUtils.PUBLICATION)||targetType.equals(ClaimUtils.DATASET))&&(sourceType.equals(ClaimUtils.PROJECT))){
            semantics= "resultProject_outcome_produces";  //"resultProject_outcome_isProducedBy";
        }
        else if((targetType.equals(ClaimUtils.PUBLICATION)||targetType.equals(ClaimUtils.DATASET))&&(sourceType.equals(ClaimUtils.CONTEXT))){
            semantics= "isRelevantTo";
        }

        return semantics;
    }
   /* public  String generateInsertClaimResultQuery(Result targetResult, Result sourceResult, Claim claim) {

        return generateInsertClaimResultQuery(generateInsertResultQuery(targetResult.getOpenaireId(), targetResult.getResultType(), targetResult.getTitle(), targetResult.getCollectedFrom(), targetResult.getExternalUrl(), targetResult.getDOI(),targetResult.getOrcidworkid(), targetResult.getAccessRights(), targetResult.getEmbargoEndDate(), targetResult.getBestLicense(), targetResult.getRecordPath(), targetResult.getRecordFormat()),
                generateInsertResultQuery(sourceResult.getOpenaireId(), sourceResult.getResultType(), sourceResult.getTitle(), sourceResult.getCollectedFrom(), sourceResult.getExternalUrl(), sourceResult.getDOI(), sourceResult.getOrcidworkid(), sourceResult.getAccessRights(), targetResult.getEmbargoEndDate(), sourceResult.getBestLicense(), sourceResult.getRecordPath(),sourceResult.getRecordFormat()),claim);
    }
    public  String generateInsertClaimProjectQuery(String   targetQuery, String projectQuery, Claim claim) {

        return " WITH ins1 AS (\n" +
                targetQuery+
                "    ),\n ins2 AS (\n" +
                projectQuery+
                "    ),\n ins3 AS (\n" +
                generateInsertClaimQuery(claim.getDate(), claim.getUserMail(), claim.getSourceType(), claim.getTargetType(), claim.getSource().getOpenaireId(), claim.getTarget().getOpenaireId(),"resultProject_outcome_isProducedBy") +
                "    ),\n ins4 AS (\n" +
                "    INSERT INTO has_source_project (claim_id, openaire_id)\n" +
                "    SELECT id, source_id\n" +
                "    FROM   ins3) \n"+
                "    INSERT INTO has_target_result(claim_id, openaire_id)\n" +
                "    SELECT id, target_id\n" +
                "    FROM   ins3;";
    }
    public  String generateInsertClaimProjectQuery(Result targetResult, Project project, Claim claim) {

        return
                generateInsertClaimProjectQuery(generateInsertResultQuery(targetResult.getOpenaireId(), targetResult.getResultType(), targetResult.getTitle(), targetResult.getCollectedFrom(), targetResult.getExternalUrl(), targetResult.getDOI(), targetResult.getOrcidworkid(), targetResult.getAccessRights(), targetResult.getEmbargoEndDate(), targetResult.getBestLicense(), targetResult.getRecordPath(),targetResult.getRecordFormat()),
                        generateInsertProjectQuery(project.getOpenaireId(), project.getName(), project.getAcronym(), project.getFunderId(), project.getFunderName(), project.getFunderShortName()), claim);
    }
    public  String generateInsertClaimContextQuery(Result targetResult, Context context, Claim claim) {

        return generateInsertClaimContextQuery
                (generateInsertResultQuery(targetResult.getOpenaireId(), targetResult.getResultType(), targetResult.getTitle(), targetResult.getCollectedFrom(), targetResult.getExternalUrl(), targetResult.getDOI(), targetResult.getOrcidworkid(), targetResult.getAccessRights(), targetResult.getEmbargoEndDate(), targetResult.getBestLicense(), targetResult.getRecordPath(),targetResult.getRecordFormat())
                        ,generateInsertContextQuery(context.getOpenaireId(), context.getTitle()),claim);
    }
    public  String generateInsertClaimContextQuery(String targetQuery,String sourceQuery, Claim claim) {

        return " WITH ins1 AS (\n" +targetQuery+
                "    ),\n ins2 AS (\n" +
                sourceQuery+
                "   ),\n ins3 AS (\n" +
                generateInsertClaimQuery(claim.getDate(), claim.getUserMail(), claim.getSourceType(), claim.getTargetType(), claim.getSource().getOpenaireId(), claim.getTarget().getOpenaireId(),"") +
                "    ), ins4 AS (\n" +
                "    INSERT INTO has_source_context(claim_id, openaire_id)\n" +
                "    SELECT id, source_id\n" +
                "    FROM   ins3) \n"+
                "    INSERT INTO has_target_result(claim_id, openaire_id)\n" +
                "    SELECT id, target_id\n" +
                "    FROM   ins3;";
    }
*/
    /**
     ** Delete Tables Queries **
     **/

    public String generateDeleteClaimTablesQuery() {
        return "drop table  if exists has_source_context;\n" +
                "drop table if exists has_source_project;\n" +
                "drop table if exists has_source_result;\n" +
                "drop table if exists has_target_project;\n" +
                "drop table if exists has_target_result;\n" +
                "drop table if exists claim;\n" +
                "drop table if exists project;\n" +
                "drop table if exists result;\n" +
                "drop table if exists context;\n" +
                "\n ";

    }

    /**
     ** Create Tables Queries **
     **/

    public String generateCreateClaimTablesQuery() {
        return "CREATE TABLE project (\n" +
                "        openaire_id varchar(60) primary key NOT NULL,\n" +
                "        name text NOT NULL,\n" +
                "        acronym text,\n" +
                "        funder_id varchar(60) NOT NULL,\n" +
                "        funder_name text NOT NULL,\n" +
                "        funder_acronym text NOT NULL,\n" +
                "        notify boolean DEFAULT TRUE,\n" +
                "        contact_person text[],\n" +
                "        token varchar(60)\n" +
                ");\n" +
                "\n" +
                "CREATE TABLE result (\n" +
                " id  serial  NOT NULL,\n" +
                " openaire_id varchar(60) primary key NOT NULL,\n" +
                "\t\tresult_type varchar(30) NOT NULL,\n" +
                "\t\tdoi text,\n" +
                "\t\torcidworkid text,\n" +
                "\t\ttitle text,\n" +
                "\t\taccess_rights varchar(30),\n" +
                "\t\tembargo_end_date date,\n" +
                "\t\tbest_license varchar(30),\t\t\n" +
                "\t\texternal_url text,\n" +
                "\t\tcollected_from varchar(60) ,\n" +
                "\t\trecord_path text , \n" +
                "\t\trecord_format varchar(10)\n" +
                ");\n" +
                "\n" +
                "CREATE TABLE context (\n" +
                "        openaire_id varchar(60) primary key,\n" +
                "        name text\n" +
                ");\n" +
                "CREATE TABLE claim (\n" +
                "        id serial primary key NOT NULL,\n" +
                "        claim_date timestamp without time zone NOT NULL,\n" +
                "        claimedBy text NOT NULL,\n" +
                "        source_type varchar(30) NOT NULL,\n" +
                "        target_type varchar(30) NOT NULL,\n" +
                "        source_id varchar(60) NOT NULL,\n" +
                "        target_id varchar(60) references result(openaire_id) NOT NULL,\n" +
                "        curation_date  timestamp without time zone,\n" +
                "        curated_by text,\n" +
                "        approved boolean DEFAULT TRUE,\n" +
                "        claim_status varchar(30),\n" +
                "        semantics varchar(60)\n" +
                ");\n" +
                "\n" +
                "CREATE TABLE has_source_context(\n" +
                "\tclaim_id int references claim(id) NOT NULL,\n" +
                "\topenaire_id varchar(60) references context(openaire_id) NOT NULL\n" +
                ");\n" +
                "CREATE TABLE has_source_project(\n" +
                "\tclaim_id int references claim(id) NOT NULL,\n" +
                "\topenaire_id varchar(60) references project(openaire_id) NOT NULL\n" +
                ");\n" +
                "CREATE TABLE has_source_result(\n" +
                "\tclaim_id int references claim(id) NOT NULL,\n" +
                "\topenaire_id varchar(60) references result(openaire_id) NOT NULL\n" +
                ");\n"+
                "CREATE TABLE has_target_project(\n" +
                "\tclaim_id int references claim(id) NOT NULL,\n" +
                "\topenaire_id varchar(60) references project(openaire_id) NOT NULL\n" +
                ");\n" +
                "CREATE TABLE has_target_result(\n" +
                "\tclaim_id int references claim(id) NOT NULL,\n" +
                "\topenaire_id varchar(60) references result(openaire_id) NOT NULL\n" +
                ");\n"

                ;

    }

    /**
     ** Select Queries *Old* Database **
     **/


    public String generateSelectDMFByIdQuery(String id) {
        return " Select * from "+this.migrationTable+"  where type='dmf2actions' and xml NOT ILIKE '%<oaf:concept%' and  resultid='"+id+"'  order by date desc limit 1";
    }

    public String generateSelectConceptDMFClaimsQuery(Integer limit) {
        return " Select * from "+this.migrationTable+"  where ( type='dmf2actions' or type='updates2actions' ) and xml LIKE '%<oaf:concept%' "+(limit>0?" limit "+limit:"");
    }
    /**
     *
     * @param limit : for limit > 0 the query adds limit clause, for limit < 0 query skips limit clause
     * @return
     */
    public String generateSelectRelationEnrichedWithDMFClaimsQuery(Integer limit) {
        return " select rel.*, dmf.dmf from (Select * from "+this.migrationTable+"  where type='rels2actions' order by id) as rel left outer join \n" +
                        "(Select DISTINCT ON (resultid) resultid,date, xml as dmf from "+this.migrationTable+"  where type='dmf2actions' and xml NOT ILIKE '%<oaf:concept%'   order by resultid,date desc , xml) as dmf on rel.resultid=dmf.resultid "+
                " order by rel.id "
                +(limit>0?" limit "+limit:"");

    }

    /**
     *
     * @param limit : for limit > 0 the query adds limit clause, for limit < 0 query skips limit clause
     * @return
     */
    public String generateSelectConceptClaimsEnrichedWithDMFClaimsQuery(Integer limit) {
        return " select rel.*, dmf.dmf from (Select * from "+this.migrationTable+"  where  ( type='dmf2actions' or type='updates2actions' ) and xml LIKE '%<oaf:concept%' order by id) as rel left outer join \n" +
                "(Select DISTINCT ON (resultid) resultid,date, xml as dmf from "+this.migrationTable+"  where type='dmf2actions' and xml NOT ILIKE '%<oaf:concept%'   order by resultid,date desc , xml) as dmf on rel.resultid=dmf.resultid"
                +" order by rel.id "
                +(limit>0?" limit "+limit:"");

    }
    
    public String generateSelectConceptClaimByIDEnrichedWithDMFClaimsQuery(Integer limit, String claimID) {
        return " select rel.*, dmf.dmf from (Select * from "+this.migrationTable+"  where id='"+claimID+"' and  ( type='dmf2actions' or type='updates2actions' ) and xml LIKE '%<oaf:concept%') as rel left outer join \n" +
                "(Select DISTINCT ON (resultid) resultid,date, xml as dmf from "+this.migrationTable+"  where type='dmf2actions' and xml NOT ILIKE '%<oaf:concept%'   order by resultid,date desc , xml)  as dmf on rel.resultid=dmf.resultid"
                +(limit>0?" limit "+limit:"");
    }
    public String generateSelectRelationClaimByIDEnrichedWithDMFClaimsQuery(Integer limit, String claimID) {
        return " select rel.*, dmf.dmf from (Select * from "+this.migrationTable+"   where id='"+claimID+"' and type='rels2actions' ) as rel left outer join \n" +
                "(Select DISTINCT ON (resultid) resultid,date, xml as dmf from "+this.migrationTable+"  where type='dmf2actions' and xml NOT ILIKE '%<oaf:concept%'   order by resultid,date desc , xml) as dmf on rel.resultid=dmf.resultid"
                +(limit>0?" limit "+limit:"");
    }


    public  String generateSelectConceptClaimByIdQuery(String id) {
        return " Select * from "+this.migrationTable+"  where ( type='dmf2actions' or type='updates2actions' ) and xml LIKE '%<oaf:concept%' and id='"+id+"'";

    }
    public  String generateSelectRelationClaimByIdQuery(String id) {
        return " Select * from "+this.migrationTable+"  where type='rels2actions' and id='"+id+"'";

    }


    /**
     ** Select - Fetch Queries *New* Database **
     **/


    private  String addPagingClause(Integer limit, Integer offset) {
        if(limit==null && offset==null){
            return "";
        }
        else if((limit==null || offset==null || offset <0 || limit<=0)){
            limit=10;
            offset=0;
        }
        return  " limit  "+limit+ "  offset "+offset;

    }
    private  String addOrderByClause( String orderBy, boolean desc) {
        if(orderBy==null){
            return null;
        }else if(orderBy.equals("date")){
            return  " order by claim.claim_date "+((desc)?"desc":"asc");
        }else if(orderBy.equals("user")){
            return  " order by claim.claimedBy "+((desc)?"desc":"asc");

        }else if(orderBy.equals("source")){
            return  " order by claim.source_title "+((desc)?"desc":"asc");

        }else if(orderBy.equals("target")){
            return  " order by claim.target_title "+((desc)?"desc":"asc");

        }else{
            return  " order by claim.claim_date  desc";
        }
 
    }

    private  String addFilterByType( List<String> types) {
        if(types == null || types.isEmpty()){
            return null;
        }else if (types.contains(ClaimUtils.PUBLICATION) && types.contains(ClaimUtils.DATASET) && types.contains(ClaimUtils.PROJECT) && types.contains(ClaimUtils.CONTEXT) ){
            //it's all types - no need to filter
            return null;
        }
        else{
            String filter = "";
            for(String type: types){
                   filter+=((filter.length()>0)?" or  ":"")+" claim.source_type = '"+type+"' or claim.target_type = '"+type+"' ";
            }
            return filter;
        }
    }

    public  String generateCountByUser(String user ,String  keyword,List<String> types ) {
        if((keyword == null || keyword.equals(""))&&types.isEmpty()){
            return " select count(*) from claim where "+ " claim.claimedBy='" + user + "'";
        }
        return " select count(*) from claim where  claim.id in ( select claim.id from ("+generateFetchClaimsByUser(user, null,null,keyword,null,false,types)+")as claim )";
    }
    public  String generateCountByDate(String dateFrom, String dateTo ,String  keyword,List<String> types) {
        if((keyword == null || keyword.equals(""))&&types.isEmpty()){
            return " select count(*) from claim where "+ " claim.claim_date >= '" + dateFrom + "' and claim.claim_date <= '" + dateTo + "' ";
        }
        return " select count(*) from claim where  claim.id in ( select claim.id from ("+generateFetchClaimsByDate(dateFrom,dateTo, null,null,keyword,null,false, types)+")as claim )";
    }
    public  String generateCountByProject(String projectId ,String  keyword, List<String> types ) {
        if((keyword == null || keyword.equals(""))&&types.isEmpty()){
            return " select count(*) from claim where "+"claim.source_id ='" + projectId + "'";
        }
        return " select count(*) from claim where  claim.id in ( select claim.id from ("+generateFetchClaimsByProject(projectId, null,null,keyword,null,false, types)+")as claim )";
    }
    /*
    public  String generateCountByProjectToken(String projectToken,String email ,String  keyword, List<String> types ) {
        return " select count(*) from claim where  claim.id in ( select claim.id from ("+generateFetchClaimsByProjectToken(projectToken, email,null,null,keyword,null,false, types)+")as claim )";
    }
    */
    public  String generateCountByFunder(String funderId ,String  keyword, List<String> types ) {
        if((keyword == null || keyword.equals(""))&&types.isEmpty()){
            return " select count(*) from claim,project as source where claim.source_id = source.openaire_id and source.funder_id ='" + funderId + "'";
        }
        return " select count(*) from claim where  claim.id in ( select claim.id from ("+generateFetchClaimsByFunder(funderId, null,null,keyword,null,false, types)+")as claim )";
    }
    public  String generateCountByContext(String contextId ,String  keyword, List<String> types ) {
        if((keyword == null || keyword.equals(""))&&types.isEmpty()){
             return " select count(*) from claim where "+" claim.source_id ='" + contextId + "'";
        }
        return " select count(*) from claim where  claim.id in ( select claim.id from ("+generateFetchClaimsByContext(contextId, null,null,keyword,null,false, types)+")as claim )";

    }
    public  String generateCountByResult(String resultId ,String  keyword, List<String> types) {
        if((keyword == null || keyword.equals(""))&&types.isEmpty()){
            return " select count(*) from claim where "+" (  claim.source_id= '"+resultId+"'  or  claim.target_id = '"+resultId+"' )";
        }
        return " select count(*) from claim where  claim.id in ( select claim.id from ("+generateFetchClaimsByResult(resultId, null,null,keyword,null,false,types)+")as claim )";

    }

    public  String generateCountAllClaims(String keyword,List<String> types) {
        String filterByType = addFilterByType(types);
        if((keyword == null || keyword.equals(""))&&types.isEmpty()){
            return "select count(*) from claim "+((filterByType==null)?"":" where "+filterByType+" ");
        }
        return " select count(*) from claim where claim.id in ( select claim.id from ("+generateFetchClaims(null,null,keyword,null,false,types)+" ) as claim )";

    }
    public  String generateFetchAllProjectsQuery() {
        return " select "+getProjectFields("source")+" from project as source";

    }
    public  String generateFetchAllContextsQuery() {
        return " select "+getContextFields("source")+" from context as source";
    }
    public  String generateFetchClaimsByUser(String user, Integer limit, Integer offset,String keyword,String orderBy, boolean desc,List<String> types) {
        // "claim.claim_date", true
        String clause=  " claim.claimedBy='" + user + "'";
        return generateSelectClaimQueryAsUnionOfAllRelations(limit, offset, orderBy,desc,clause,keyword, types);

    }
    /**
     *
     * @param limit how many results to return
     * @param offset starting from
     * @param keyword containing keyword
     * @return
     */

    public  String generateFetchClaims(Integer limit, Integer offset,String keyword,String orderBy, boolean desc,List<String> types) {
        return generateSelectClaimQueryAsUnionOfAllRelations(limit, offset, orderBy,desc,null,keyword, types)+"";
    }
    /**
     *
     * @param limit how many results to return
     * @param offset starting from
     * @return
     */
     public  String generateFetchClaims(Integer limit, Integer offset, String orderBy, boolean desc,List<String> types) {
         String keyword = null;
        return generateSelectClaimQueryAsUnionOfAllRelations(limit, offset, orderBy,desc,null,keyword,types)+"";

    }
     public  String generateFetchClaimsByDate(String dateFrom, String dateTo, Integer limit, Integer offset,String keyword, String orderBy, boolean desc,List<String> types) {
         String clause=  " claim.claim_date >= '" + dateFrom + "' and claim.claim_date <= '" + dateTo + "' ";
          return generateSelectClaimQueryAsUnionOfAllRelations(limit, offset, orderBy,desc,clause,keyword,types);
    }
    public  String generateFetchClaimsByProject(String projectId, Integer limit, Integer offset,String keyword, String orderBy, boolean desc, List<String> types) {
        String clause = "source.openaire_id ='" + projectId + "'";
        String filterByType = addFilterByType(types);
        return " select * from ("+" select * from ( " + generateSelectclaimQuery(ClaimUtils.PROJECT, ClaimUtils.PUBLICATION, limit, offset, orderBy, desc, clause, keyword) + " ) as claim  "+
                ") as claim "+
                ((filterByType==null)?"":" where "+filterByType+" ");
    }
/*
    public  String generateFetchClaimsByProjectToken(String token, String email, Integer limit, Integer offset,String keyword, String orderBy, boolean desc, List<String> types) {
        String clause = "source.token = '" + token + "' and '" + email + "' = ANY(source.contact_person)";
        String filterByType = addFilterByType(types);
        return " select * from ("+" select * from ( " + generateSelectclaimQuery(ClaimUtils.PROJECT, ClaimUtils.PUBLICATION, limit, offset, orderBy, desc, clause, keyword) + " ) as claim  "+
                ") as claim "+
                ((filterByType==null)?"":" where "+filterByType+" ");
    }
*/
    public String generateSelectProjectIdByTokenQuery(String token, String email) {
        return "SELECT openaire_id FROM project WHERE token='"+token+"' AND '"+email+"'= ANY(contact_person);";
    }

    public  String generateFetchClaimsByContext(String contextId,  Integer limit, Integer offset,String keyword, String orderBy, boolean desc, List<String> types) {
        String clause=  "claim.source_id ='" + contextId + "'";
        String filterByType = addFilterByType(types);
        return " select * from ("+" ( " +generateSelectclaimQuery(ClaimUtils.CONTEXT,ClaimUtils.PUBLICATION,limit, offset, orderBy,desc,clause,keyword)+")"+
                ") as claim "+
                ((filterByType==null)?"":" where "+filterByType+" ");
    }
    public  String generateFetchClaimsByFunder(String funderId, Integer limit, Integer offset,String keyword, String orderBy, boolean desc, List<String> types) {
        String whereClause = "source_id =source.openaire_id ";
        String filterByType = addFilterByType(types);
        return " select * from ("+" ( " +generateSelectclaimQuery(ClaimUtils.PROJECT,ClaimUtils.PUBLICATION, limit, offset, orderBy,desc,whereClause,keyword)+" )"+
                ") as claim "+
                ((filterByType==null)?"":" where "+filterByType+" ");


    }
    public  String generateFetchClaimsByResult(String resultId,  Integer limit, Integer offset, String keyword, String orderBy, boolean desc, List<String> types) {
         String queryWhereClause=" ( source.openaire_id = '"+resultId+"'  or target.openaire_id = '"+resultId+"' )";
        String filterByType = addFilterByType(types);
        return " select * from ("+" ( " +generateSelectclaimQuery(ClaimUtils.PUBLICATION,ClaimUtils.PUBLICATION, limit, offset, orderBy,desc,queryWhereClause,keyword)+" ) \nunion "+
                " ( " +generateSelectclaimQuery(ClaimUtils.PROJECT,ClaimUtils.PUBLICATION, limit, offset, orderBy,desc,queryWhereClause,keyword)+" ) \nunion "+
                " ( " +generateSelectclaimQuery(ClaimUtils.CONTEXT,ClaimUtils.PUBLICATION, limit, offset, orderBy,desc,queryWhereClause,keyword)+" ) "+
                ") as claim "+
                ((filterByType==null)?"":" where "+filterByType+" ");

    }

    public  String generateFetchClaimsByClaimId(String claimId, String sourceType, String targetType) {
        String keyword = null;
        String clause=  " claim.id = '" +claimId + "' ";
        String orderbyLimitClause= null;
        if(sourceType.equals(ClaimUtils.PUBLICATION)||sourceType.equals(ClaimUtils.DATASET)){
            return generateSelectclaimQuery(sourceType,targetType,null,null,null,false, clause,keyword);
        }else if(sourceType.equals(ClaimUtils.PROJECT)){
            return generateSelectclaimQuery(sourceType,targetType,null,null,null,false, clause,keyword);
        }else if(sourceType.equals(ClaimUtils.CONTEXT)){
            return generateSelectclaimQuery(sourceType,targetType,null,null,null,false, clause,keyword);
        }
        return null;
    }

    private  String getClaimFields() {
        return " claim.id, claim.claim_date, claim.claimedBy, claim.source_type, claim.target_type, claim.semantics, claim.curation_date, claim.curated_by, claim.approved ";
    }
    private  String getResultFields(String tableAlias) {
        return " "+tableAlias+".openaire_id, "+tableAlias+".title  "+tableAlias+"_title, "+tableAlias+".result_type, "+tableAlias+".doi, "+tableAlias+".orcidworkid, "+tableAlias+".access_rights, "+tableAlias+".embargo_end_date, "+tableAlias+".best_license, "+tableAlias+".external_url,  "+tableAlias+".collected_from  ";
    }
    private  String getProjectFields(String tableAlias) {
        return " "+tableAlias+".openaire_id, "+tableAlias+".name  as "+tableAlias+"_title, "+tableAlias+".acronym, "+tableAlias+".funder_id, "+tableAlias+".funder_name, null as field6, null as field7, null as field8, null as field9, null as field10 ";
    }
    private  String getContextFields(String tableAlias) {
        return " "+tableAlias+".openaire_id, "+tableAlias+".name  "+tableAlias+"_title, "+" null as field3, null as field4, null as field5, null as field6, null as field7, null as field8, null as field9, null as field10 ";
    }

    private  String getFieldsPerType(String type, String tableAlias){
        String fields= null;

        if (type != null) {
            if (type.equals(ClaimUtils.PUBLICATION) || type.equals(ClaimUtils.DATASET)) {
                fields = getResultFields(tableAlias);

            } else if (type.equals(ClaimUtils.PROJECT)) {
                fields = getProjectFields(tableAlias);

            } else if (type.equals(ClaimUtils.CONTEXT)) {
                fields = getContextFields(tableAlias);
            }
        }

        return fields;
    }
    private  String generateSelectclaimQuery(String sourceType, String targetType, Integer limit,Integer offset,String orderBy, boolean desc,String whereClause, String keyword) {
        if(sourceType == null || targetType == null){
            return null;
        }
        /*
        " select "+getClaimFields()+" ,"+getFieldsPerType(sourceType, "source")+" , "+getFieldsPerType(targetType,"target")+
                " \nfrom claim, "+getTypeForTable(sourceType)+" as source, "+getTypeForTable(targetType)+" as target \n" +
                "where claim.source_id = source.openaire_id and claim.target_id = target.openaire_id "+((specificWhereClause==null)?"":" and "+specificWhereClause+" ");
         */
        String orderByClause= addOrderByClause(orderBy,desc);
        String pagingClause= addPagingClause(limit,offset);
        return " select "+getClaimFields()+" ,"+getFieldsPerType(sourceType, "source")+" , "+getFieldsPerType(targetType,"target")+
                " \nfrom claim, "+getTypeForTable(sourceType)+" as source, has_source_"+getTypeForTable(sourceType)+", has_target_"+getTypeForTable(targetType)+", "+getTypeForTable(targetType)+" as target \n" +
                "where claim.id =  has_source_"+getTypeForTable(sourceType)+".claim_id   and   has_source_"+getTypeForTable(sourceType)+".openaire_id = source.openaire_id " +
                "and claim.id =  has_target_"+getTypeForTable(targetType)+".claim_id   and   has_target_"+getTypeForTable(targetType)+".openaire_id  = target.openaire_id "+
                ((keyword==null)?"":(" and ("+getKeywordClauseForType(sourceType, "source", keyword)+" or "+getKeywordClauseForType(targetType, "target", keyword)+" ")+" or "+getKeywordClauseForClaim( keyword)+" )")+
                ((whereClause==null)?"":" and "+whereClause+" ")+
                ((orderByClause==null)?"":" "+orderByClause+" ")+
                pagingClause;
    }
    private String getKeywordClauseForType(String type,String tableAlias,String keyword){
        if (type == null ||keyword == null){
            return "";
        }
        keyword=keyword.toLowerCase();
        if (type.equals(ClaimUtils.PUBLICATION)||type.equals(ClaimUtils.DATASET)){
            return "  (lower("+tableAlias+".title) like '%"+keyword+"%' or lower("+tableAlias+".doi) like '%"+keyword+"%'"+")";
        }else if (type.equals(ClaimUtils.PROJECT)){
            return "  (lower("+tableAlias+".name) like '%"+keyword+"%' or lower("+tableAlias+".acronym) like '%"+keyword+"%' or lower("+tableAlias+".funder_name) like '%"+keyword+"%' or lower("+tableAlias+".funder_acronym) like '%"+keyword+"%' "+")";
        }else if (type.equals(ClaimUtils.CONTEXT)){
            return "  (lower("+tableAlias+".name) like '%"+keyword+"%' )";
        }
        return "";
    }
    private String getKeywordClauseForClaim(String keyword){
        if (keyword == null){
            return "";
        }else {
            return " (lower(claim.claimedby) like '%"+keyword.toLowerCase()+"%')";
        }
     }
    private  String generateSelectClaimQueryAsUnionOfAllRelations(Integer limit,Integer offset,String orderBy, boolean desc,String specificWhereClause,String keyword,List<String> types) {
        //TODO eliminate unions  based on the to
        // ClaimUtils.PUBLICATION or ClaimUtils.DATASET  it
        String orderByClause= addOrderByClause(orderBy,desc);
        String pagingClause= addPagingClause(limit,offset);
        String filterByType = addFilterByType(types);
         return "select * from ("+

                " ( " +generateSelectclaimQuery(ClaimUtils.PUBLICATION,ClaimUtils.PUBLICATION,null,null,null,false, specificWhereClause,keyword)+" ) \nunion "+
                " ( " +generateSelectclaimQuery(ClaimUtils.DATASET,ClaimUtils.DATASET,null,null,null,false, specificWhereClause,keyword)+" ) \nunion "+
                " ( " +generateSelectclaimQuery(ClaimUtils.PROJECT,ClaimUtils.PUBLICATION, null,null,null,false, specificWhereClause,keyword)+" ) \nunion "+
                " ( " +generateSelectclaimQuery(ClaimUtils.CONTEXT,ClaimUtils.PUBLICATION,null,null,null,false, specificWhereClause,keyword)+" )" +

                ") as claim   "+
                ((filterByType==null)?"":" where "+filterByType+" ")+
                ((orderByClause==null)?"":" "+orderByClause+" ")+
                pagingClause;


    }
    private  String getTypeForTable(String type){
        if(type == null){
            return null;
        }
        if(type.equals(ClaimUtils.PUBLICATION)||type.equals(ClaimUtils.DATASET)){
            return "result";
        }else{
            return type;
        }
    }
    /**
     ** Update - Queries *New* Database **
     **/

    private  void updateResult(){

    }
    private  void updateProject(){

    }
    /**
     ** Delete - Queries *New* Database **
     **/

    private String generateDeleteEntityQuery(String resultid, String claimId, String type){
        if(type == null||resultid == null||claimId ==  null ){
            return null;
        }
        String query = null;
        if(type.equals(ClaimUtils.PUBLICATION)||type.equals(ClaimUtils.DATASET)){
            query = generateDeleteResultQuery(resultid,claimId);
        }else if(type.equals(ClaimUtils.PROJECT)){
            query = generateDeleteProjectQuery(resultid,claimId);
        }else if (type.equals(ClaimUtils.CONTEXT)){
            query = generateDeleteContextQuery(resultid,claimId);
        }
        return query;
    }
/*
    Before delete check a Result, Project, Context
    check if there is another claim in  has_source_* or has_target_* relation
    for the entity
 */
    private String generateDeleteResultQuery(String resultid, String claimId ){
        String query = " DELETE FROM "+getTypeForTable(ClaimUtils.PUBLICATION)+" WHERE  openaire_id = '"+resultid+"' and NOT EXISTS \n" +
                "   (   " +generateSelectOthersFromRelationTableQuery(resultid,claimId,getSourceTableName(ClaimUtils.PUBLICATION)) + " union " +
                generateSelectOthersFromRelationTableQuery(resultid,claimId,getTargetTableName(ClaimUtils.PUBLICATION))+"  ) ;\n" ;
        return query;
    }
    private String generateDeleteProjectQuery(String resultid, String claimId ){
        String query = " DELETE FROM "+getTypeForTable(ClaimUtils.PROJECT)+" WHERE openaire_id = '"+resultid+"' and NOT EXISTS \n" +
                "  (    " +generateSelectOthersFromRelationTableQuery(resultid,claimId,getSourceTableName(ClaimUtils.PROJECT)) + " union " +
                generateSelectOthersFromRelationTableQuery(resultid,claimId,getTargetTableName(ClaimUtils.PROJECT))+"  ) ;\n" ;
        return query;
    }
    private String generateDeleteContextQuery(String resultid, String claimId ){
        String query = " DELETE FROM "+getTypeForTable(ClaimUtils.CONTEXT)+" WHERE openaire_id = '"+resultid+"' and NOT EXISTS \n" +
                " (  " +generateSelectOthersFromRelationTableQuery(resultid,claimId,getSourceTableName(ClaimUtils.CONTEXT)) +"  ) ;\n" ;
        return query;
    }

    /**
     * Select form a has_source_* or has_target_* table
     * ...
     * @param resultid
     * @param claimId
     * @param tableName
     * @return
     */
    private String  generateSelectOthersFromRelationTableQuery(String resultid, String claimId , String  tableName){
        String query =
                " SELECT 1 FROM "+ tableName+"  WHERE openaire_id = '"+resultid+"' and claim_id != " +claimId + "  " ;
        return query;
    }
    public String  generateDeleteFullClaimQuery(String id, String user, String sourceType, String sourceId, String targetType, String targetId){
        //TODO generate delete query
        String query =
                "BEGIN;\n" +
                //delete has_source_*
                "DELETE FROM "+ getSourceTableName(sourceType)+" WHERE   claim_id = "+id+" ;\n" +

                //delete has_target_*
                "DELETE FROM "+ getTargetTableName(targetType)+" WHERE  claim_id = "+id+" ;\n" +

                //delete claim
                "DELETE FROM claim where id = "+id+";\n" +

                        //delete source
                generateDeleteEntityQuery(sourceId,id,sourceType)+

                        //delete target
                generateDeleteEntityQuery(targetId,id,targetType)+
                "COMMIT";
        return query;
    }
    public String  generateSelectClaimQuery(String id, String user){

        String query =" ";
        return " Select id, source_type, source_id, target_type, target_id from claim where id = "+id +" and claimedBy = '"+user+"' ";
    }
    public String  generateSelectClaimQuery(String id){

        String query =" ";
        return " Select id, source_type, source_id, target_type, target_id,claimedBy  from claim where id = "+id ;
    }
    /**
     * used to fetch the information from DB
     * @param ids
     * @param users
     * @return
     */
    public String  generateSelectClaimQuery(List<String> ids, List<String> users){

        String query =" Select id, source_type, source_id, target_type, target_id from claim where ";
        if(ids.size()>0 && users.size() > 0 && ids.size() == users.size()) {
            for (int i = 0; i < ids.size(); i++) {
                query+="  ( id = "+ids.get(i) +" and claimedBy = '"+users.get(i)+"' ) or";
            }
            query = query.substring(0, query.length()-2);
            return query;
        }
        return null;
    }

    public  String generateSelectProjectByIdQuery(String projectId) {
        return " Select " + getProjectFields("project") + " from project where openaire_id = '"+projectId +"'";
    }

    public  String generateSelectContactEmailsByProjectIdQuery(String projectId) {
        return " Select contact_person from project where openaire_id = '" + projectId + "'";
    }

    public String generateUpdateTokenByProjectId(String projectdId, String newToken) {
        return "UPDATE project SET token = '" + newToken + "' WHERE openaire_id = '" + projectdId + "'";
    }
    public String generateUpdateContactEmailsByProjectIdByProjectId(String projectdId, List<String> contactEmails) {
        String emails = "";
        if(contactEmails != null && contactEmails.size() > 0){
            emails+="'{";
            for(int i = 0; i< contactEmails.size(); i++){

                emails+="\""+contactEmails.get(i)+"\""+((i < contactEmails.size() -1 )?",":"");
            }
            emails+="}'";
        }

        return "UPDATE project SET contact_person = " + emails + " WHERE openaire_id = '" + projectdId + "';";
    }

    public String generateUpdateClaimCuration(String curatedBy, String claimId, boolean approved) {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = new Date();
        String dateStr=null;
        dateStr=(format.format(date));

        String query = "UPDATE claim "
                + "SET curation_date='" + dateStr + "', curated_by='" + curatedBy + "', approved='" + approved +"' "
                + "WHERE id = '" + claimId + "' RETURNING curation_date, curated_by, approved";

        logger.debug("Query to execute: "+query);

        return query;
    }

    public String getMigrationTable() {
        return migrationTable;
    }

    public void setMigrationTable(String migrationTable) {
        this.migrationTable = migrationTable;
    }
}