package com.ffii.lioner.modules.master.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.ffii.core.utils.BeanUtils;
import org.springframework.stereotype.Service;

import com.ffii.lioner.modules.master.entity.SubDivision;
import com.ffii.lioner.modules.master.entity.SubDivisionRepository;
import com.ffii.lioner.modules.master.req.UpdateSubDivisionReq;
import com.ffii.core.support.AbstractBaseEntityService;
import com.ffii.core.support.JdbcDao;
import com.ffii.core.utils.Params;

@Service
public class SubDivisionService extends AbstractBaseEntityService<SubDivision, Long, SubDivisionRepository> {

	public SubDivisionService(JdbcDao jdbcDao, SubDivisionRepository repository) {
		super(jdbcDao, repository);
	}

	public List<Long> saveOrUpdateList(List<UpdateSubDivisionReq> subDivisions, Long divisionId) {
		SubDivision instance;
        List<Long> subDivisionIdList = new ArrayList<>();

        for(UpdateSubDivisionReq subDivision: subDivisions){
            if(subDivision.getId()>0){
                instance = find(subDivision.getId()).get();
            }
            else{
                instance = new SubDivision();
            }
		    BeanUtils.copyProperties(subDivision,instance);
            instance.setDivisionId(divisionId);
            instance = save(instance);
            subDivisionIdList.add(instance.getId());
        }

		return subDivisionIdList;
	}
    
    public List<Long> deleteByIdList(List<Long> subDivisionDeleteList) {
		SubDivision instance;
        List<Long> subDivisionIdList = new ArrayList<>();
        for(Long subDivisionId: subDivisionDeleteList){
            instance = find(subDivisionId).get();
            instance.setDeleted(true);
            instance = save(instance);
            subDivisionIdList.add(instance.getId());
        }

		return subDivisionIdList;
	}

    public List<Long> deleteByDivision(Long divisionId) {
        List<SubDivision> subDivisionList = this.listSubdivisionObject(Map.of("id", divisionId));
        //idList

        List<Long> idList = new ArrayList<>();

        for(SubDivision subDivision: subDivisionList){
		    SubDivision instance = this.find(subDivision.getId()).get();
            instance.setDeleted(true);
            instance = save(instance);
            idList.add(instance.getId());
        }

		return idList;
	}

	public List<Map<String,Object>> getNameList(){
		StringBuilder sql = new StringBuilder("SELECT"
			 + " sd.id, "
			 + " sd.name  "
			 + " FROM division d  "
			 + " JOIN sub_division sd on sd.divisionId = d.id    "
			 + " WHERE sd.deleted = FALSE  "
		);

		return jdbcDao.queryForList(sql.toString());
	}

    public List<Map<String,Object>> listSubdivision(Map<String, Object> args){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " sd.id, "
			+ " sd.name "
			+ " FROM division d  "
			+ " JOIN sub_division sd on sd.divisionId = d.id "
			+ " WHERE sd.deleted = FALSE"
		);
		if (args != null) {
			if (args.containsKey(Params.ID)) sql.append(" AND sd.divisionId = :id");
			if (args.containsKey("subDivisionName")) sql.append(" AND sd.name LIKE :subDivisionName");
		}
		sql.append(" ORDER BY sd.id");

		return jdbcDao.queryForList(sql.toString(), args);
	}

	public List<Integer> listSubdivisionId(Map<String, Object> args){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " sd.id "
			+ " FROM division d  "
			+ " JOIN sub_division sd on sd.divisionId = d.id "
			+ " WHERE sd.deleted = FALSE"
		);
		if (args != null) {
			if (args.containsKey(Params.ID)) sql.append(" AND sd.divisionId = :id");
		}
		sql.append(" ORDER BY sd.id");

		return jdbcDao.queryForInts(sql.toString(), args);
	}

	public List<String> listSubdivisionName(Map<String, Object> args){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " sd.name "
			+ " FROM division d  "
			+ " JOIN sub_division sd on sd.divisionId = d.id "
			+ " WHERE sd.deleted = FALSE"
		);
		if (args != null) {
			if (args.containsKey(Params.ID)) sql.append(" AND sd.divisionId = :id");
		}
		sql.append(" ORDER BY sd.id");

		return jdbcDao.queryForStrings(sql.toString(), args);
	}

    public List<Map<String,Object>> listSubdivisionCombo(Map<String, Object> args){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " sd.id, "
			+ " sd.id as `key`, "
			+ " sd.name as label, "
			+ " d.id as divisionId "
			+ " FROM division d  "
			+ " JOIN sub_division sd on sd.divisionId = d.id "
			+ " WHERE sd.deleted = FALSE"
		);

		if (args != null) {
			if (args.containsKey("divisionIds")) 
				sql.append(" AND sd.divisionId IN (:divisionIds)");
		}

		sql.append(" ORDER BY sd.name asc");

		return jdbcDao.queryForList(sql.toString(), args);
	}

	public List<Map<String,Object>> listSubdivisionComboForApplication(Map<String, Object> args){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " sd.id,  "
			+ " sd.id as `key`,  "
			+ " sd.name as label  "
			+ " FROM event_sub_division esd  "
			+ " JOIN sub_division sd on esd.subDivisionId = sd.id  "
			+ " WHERE sd.deleted = FALSE "
			+ " AND esd.eventId = :eventId "
		);

		sql.append(" ORDER BY sd.name asc");

		return jdbcDao.queryForList(sql.toString(), args);
	}

	public List<Map<String,Object>> listSubdivisionComboForAward(Map<String, Object> args){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " sd.id,  "
			+ " sd.id as `key`,  "
			+ " sd.name as label  "
			+ " FROM application_sub_division asd  "
			+ " JOIN sub_division sd on asd.subDivisionId = sd.id  "
			+ " WHERE sd.deleted = FALSE "
			+ " AND asd.applicationId = :applicationId "
		);

		sql.append(" ORDER BY sd.name asc");

		return jdbcDao.queryForList(sql.toString(), args);
	}


	public List<Map<String,Object>> listDivisionCombo(Map<String, Object> args){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " d.id, "
			+ " d.id as `key`, "
			+ " d.name as label "
			+ " FROM division d "
			+ " WHERE d.deleted = FALSE "
		);
		sql.append(" ORDER BY d.name asc");

		return jdbcDao.queryForList(sql.toString(), args);
	}

    public List<SubDivision> listSubdivisionObject(Map<String, Object> args){
		StringBuilder sql = new StringBuilder("SELECT"
			+ " sd.id "
			+ " FROM sub_division sd "
            + " WHERE sd.deleted = FALSE "
			+ " AND sd.divisionId = :id "
		);
		return jdbcDao.queryForList(sql.toString(), args, SubDivision.class);
	}

	public String isNameTaken(Map<String, Object> args) {
		if (args.containsKey("subDivisionList")){
			StringBuilder sql = new StringBuilder("SELECT"
				+ " GROUP_CONCAT(sd.name SEPARATOR ', ') "
				+ " FROM sub_division sd  "
				+ " WHERE sd.deleted = FALSE  "
				+ " AND sd.name in (:subDivisionList) "
				+ " AND sd.divisionId != :divisionId"
        	);
        
        	return jdbcDao.queryForString(sql.toString(), args);
		}
		else{
			return null;
		}

    }

	public Integer getIdByName(String name) {
        StringBuilder sql = new StringBuilder("SELECT"
			+ " sd.id "
			+ " FROM sub_division sd  "
			+ " WHERE sd.deleted = FALSE"
			+ " AND sd.name = :name "
        );
        
        return jdbcDao.queryForInt(sql.toString(), Map.of("name", name));
    }
}
