package com.napa.pulse.dao.impl;

import com.napa.pulse.dao.interfaces.SiteGroupDAO;
import com.napa.pulse.entity.pulseui.SiteGroup;
import com.napa.pulse.entity.security.User;
import com.napa.pulse.utils.ROLE;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.HashSet;
import java.util.List;
import java.util.Map;

@Repository
public class SiteGroupDAOImpl implements SiteGroupDAO {
    private static final Logger LOGGER = LoggerFactory.getLogger(AdminDAOImpl.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /* (non-Javadoc)
     * @see com.napa.pulse.dao.interfaces.SiteGroupDAO#findUserSiteGroups(com.napa.pulse.entity.security.User)
     */
    @Override
    public List<Map<String, Object>> findUserSiteGroups(User user, String userAccessSites) throws Exception {
        int userId = user.getUserId();
        try {
            if (user.getRoleId() == ROLE.ADMIN.getNumVal() || user.getRoleId() == ROLE.XO.getNumVal()) {
                String sqlAdminList = "select sg.SITE_GROUP_ID as \"siteGroupId\", "
                        + "SITE_GROUP_NAME as \"siteGroupName\", "
                        + "sg.SITE_GROUP_TYPE_ID as \"siteGroupTypeId\", "
                        + "CREATED_USER_ID as \"createdBy\", "
                        + "CREATED_TIME as \"createdTime\", "
                        + "GROUP_OWNER_USER_ID as \"groupOwnerId\", "
                        + "SITE_GROUP_TYPE_CODE as \"siteGroupTypeCode\", "
                        + "GLOBAL_FLAG as \"globalFlag\" "
                        + "from site_group sg, site_group_type sgt "
                        + "where sg.SITE_GROUP_TYPE_ID = sgt.SITE_GROUP_TYPE_ID and "
                        + "(sgt.GLOBAL_FLAG = 'Y' or sg.GROUP_OWNER_USER_ID = ? ) "
                        + "order by lower(sg.SITE_GROUP_NAME) ";

                List<Map<String, Object>> siteGroups = jdbcTemplate.queryForList(sqlAdminList, userId);

                return siteGroups;
            } else {
                String sqlUserSiteGroup = "select sg.SITE_GROUP_ID as \"siteGroupId\", SITE_GROUP_NAME as \"siteGroupName\", sg.SITE_GROUP_TYPE_ID as \"siteGroupTypeId\", "
                        + "CREATED_USER_ID as \"createdBy\", CREATED_TIME as \"createdTime\", GROUP_OWNER_USER_ID as \"groupOwnerId\", "
                        + "SITE_GROUP_TYPE_CODE as \"siteGroupTypeCode\", GLOBAL_FLAG as \"globalFlag\" "
                        + "from site_group sg, site_group_type sgt "
                        + "where sg.SITE_GROUP_TYPE_ID = sgt.SITE_GROUP_TYPE_ID and (sgt.GLOBAL_FLAG = 'Y' or sg.GROUP_OWNER_USER_ID = ? ) and "
                        + "SITE_GROUP_ID in (select ACCESS_ID from USER_SITE_ACCESS where USER_ID=? and SITE_OR_GROUP='G') ";

                List<Map<String, Object>> siteGroups = jdbcTemplate.queryForList(sqlUserSiteGroup, userId, userId);

                return siteGroups;
            }
        } catch (Exception e) {
            LOGGER.error("findUserSiteGroups",e);
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Map<String, Object>> getInventorySnapShotData(User user, String userAccessSites, int groupId) {
        String sql = "select /*+FIRST_ROWS */  SITE_NAME as \"siteName\", to_char(SNAPSHOT_DT, 'MM/DD/YYYY') as \"snapshotDt\", to_char(EPTS_UPDATE_DT, 'MM/DD/YYYY') as \"eptsUpdateDt\", "
                + "(case when SNAPSHOT_DT is null then null "
                + "      when (SYSDATE - SNAPSHOT_DT) > 45 then 1 else 0 end) as \"snapshotWarning\", "
                + "(case when EPTS_UPDATE_DT is null then null "
                + "      when (SYSDATE - EPTS_UPDATE_DT) > 7 then 1 else 0 end) as \"eptsUpdateDtWarning\" "
                + "from SITE_GROUP_MAPPING sgm, SITE s "
                + "where sgm.SITE_ID=s.SITE_ID and sgm.SITE_GROUP_ID=? AND s.active_flag = 'Y'";

        if (user.getRoleId() != ROLE.ADMIN.getNumVal() && user.getRoleId() != ROLE.XO.getNumVal()) {
            if (userAccessSites.length() != 0) {
                sql += " and (s.site_id,0) in (" + userAccessSites + ") ";
            } else {
                sql += " and (s.site_id,0) in ((0,0)) "; // user have no access to any sites
            }
        }

        return jdbcTemplate.queryForList(sql, groupId);
    }

    @Override
    public List<Map<String, Object>> getInventorySnapShotCustomGroupData(Integer[] siteIds) {
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);

        String sql = "select SITE_NAME as \"siteName\", to_char(SNAPSHOT_DT, 'MM/DD/YYYY') as \"snapshotDt\", to_char(EPTS_UPDATE_DT, 'MM/DD/YYYY') as \"eptsUpdateDt\", "
                + "(case when SNAPSHOT_DT is null then null when (SYSDATE - SNAPSHOT_DT) > 45 then 1 else 0 end) as \"snapshotWarning\", "
                + "(case when EPTS_UPDATE_DT is null then null when (SYSDATE - EPTS_UPDATE_DT) > 7 then 1 else 0 end) as \"eptsUpdateDtWarning\" "
                + "from SITE where SITE_ID in (:list)";

        MapSqlParameterSource sessionSitesParameters = new MapSqlParameterSource();
        sessionSitesParameters.addValue("list", new HashSet<>(Arrays.asList(siteIds)));
        return namedParameterJdbcTemplate.queryForList(sql, sessionSitesParameters);
    }

    @Override
    public List<Map<String, Object>> getSiteGroupsInventorySnapShot(User user, String userAccessSites, String sessionType) {

        String sql = "WITH tmpTbl as ( ";
        String jasStoreOnly = "";
        if (("MM").equals(sessionType) || ("minMax").equals(sessionType))
            jasStoreOnly = " and s.JAS_STORE = 'Y' ";
        try {
            if (user.getRoleId() == ROLE.ADMIN.getNumVal() || user.getRoleId() == ROLE.XO.getNumVal()) {
                sql += "select "
                        + "NVL((select count(sgm.SITE_ID) from SITE_GROUP_MAPPING sgm, site s "
                        + "where SITE_GROUP_ID=sg.SITE_GROUP_ID and sgm.site_id=s.site_id and ACTIVE_FLAG='Y'" + jasStoreOnly + "),0) as \"siteCount\", "
                        + "sg.SITE_GROUP_ID as \"siteGroupId\", SITE_GROUP_NAME as \"siteGroupName\", sg.SITE_GROUP_TYPE_ID as \"siteGroupTypeId\", "
                        + "CREATED_USER_ID as \"createdBy\", CREATED_TIME as \"createdTime\", GROUP_OWNER_USER_ID as \"groupOwnerId\", SITE_GROUP_TYPE_CODE as \"siteGroupTypeCode\", "
                        + "GLOBAL_FLAG as \"globalFlag\", "
                        + "(case when (select count(SITE_GROUP_ID) from SITE_GROUP_MAPPING sgm, SITE s where sgm.SITE_ID=s.SITE_ID and sgm.SITE_GROUP_ID=sg.SITE_GROUP_ID "
                        + "and (SNAPSHOT_DT is null or EPTS_UPDATE_DT is null) AND s.active_flag = 'Y') > 0 then 1 "
                        + "when (select count(SITE_GROUP_ID) from SITE_GROUP_MAPPING sgm, SITE s where sgm.SITE_ID=s.SITE_ID and sgm.SITE_GROUP_ID=sg.SITE_GROUP_ID "
                        + "and (((SYSDATE - SNAPSHOT_DT) > 45) or ((SYSDATE - EPTS_UPDATE_DT) > 7)) "
                        + "AND s.active_flag = 'Y') > 0 then 1 else 0 end) as \"warning\" "
                        + "from site_group sg, site_group_type sgt "
                        + "where sg.SITE_GROUP_TYPE_ID = sgt.SITE_GROUP_TYPE_ID   "
                        + "and (sgt.GLOBAL_FLAG = 'Y' or sg.GROUP_OWNER_USER_ID = ? ) "
                        + "order by lower(sg.SITE_GROUP_NAME)  ";
            } else {
                sql += "select "
                        + "NVL((select count(sgm.SITE_ID) from SITE_GROUP_MAPPING sgm, site s "
                        + "where SITE_GROUP_ID=sg.SITE_GROUP_ID and sgm.site_id=s.site_id and ACTIVE_FLAG='Y'" + jasStoreOnly + " and (sgm.site_id,0) in (" + userAccessSites + ")),0) as \"siteCount\", "
                        + "sg.SITE_GROUP_ID as \"siteGroupId\", SITE_GROUP_NAME as \"siteGroupName\", sg.SITE_GROUP_TYPE_ID as \"siteGroupTypeId\", "
                        + "CREATED_USER_ID as \"createdBy\", CREATED_TIME as \"createdTime\", GROUP_OWNER_USER_ID as \"groupOwnerId\", SITE_GROUP_TYPE_CODE as \"siteGroupTypeCode\", "
                        + "GLOBAL_FLAG as \"globalFlag\", "
                        + "(case when (select count(SITE_GROUP_ID) from SITE_GROUP_MAPPING sgm, SITE s where sgm.SITE_ID=s.SITE_ID and sgm.SITE_GROUP_ID=sg.SITE_GROUP_ID "
                        + "and (SNAPSHOT_DT is null or EPTS_UPDATE_DT is null) AND s.active_flag = 'Y') > 0 then 1 "
                        + "when (select count(SITE_GROUP_ID) from SITE_GROUP_MAPPING sgm, SITE s where sgm.SITE_ID=s.SITE_ID and sgm.SITE_GROUP_ID=sg.SITE_GROUP_ID "
                        + "and (((SYSDATE - SNAPSHOT_DT) > 45) or ((SYSDATE - EPTS_UPDATE_DT) > 7)) "
                        + "AND s.active_flag = 'Y') > 0 then 1 else 0 end) as \"warning\" "
                        + "from site_group sg, site_group_type sgt "
                        + "where sg.site_group_type_id = sgt.site_group_type_id "
                        + "and (sg.site_group_id in (select distinct(sg1.site_group_id) "
                        + "from site_group_mapping sgm1, site_group sg1, site_group_type sgt1, site s "
                        + "where s.site_id = sgm1.site_id and sg1.site_group_id = sgm1.site_group_id and sg1.site_group_type_id = sgt1.site_group_type_id "
                        + "and (sgt1.global_flag = 'Y' or sg1.group_owner_user_id = ? ) AND s.active_flag = 'Y' ";
                if (userAccessSites.length() != 0) {
                    sql += "and (sgm1.site_id,0) in (" + userAccessSites + ") ";
                } else {
                    sql += "and (sgm1.site_id,0) in ((0,0)) "; // user have no access to any sites
                }
                sql += ") ) order by lower(sg.SITE_GROUP_NAME)";
            }
            sql += " ) SELECT * FROM tmpTbl where \"siteCount\" <> 0";
        }
        catch (Exception e) {
            LOGGER.error("getSiteGroupsInventorySnapShot",e);
            throw new RuntimeException(e);
        }
        return jdbcTemplate.queryForList(sql, user.getUserId());
    }

    @Override
    public int getSiteCount(int siteGroupId, User user, String userAccessSites, String sessionType) throws Exception {

        String jasStoreOnly = "";
        if (("MM").equals(sessionType) || ("minMax").equals(sessionType))
            jasStoreOnly = " and s.JAS_STORE = 'Y' ";
        if (user.getRoleId() == ROLE.ADMIN.getNumVal() || user.getRoleId() == ROLE.XO.getNumVal()) {
            String sql = "select count(sgm.SITE_ID) from SITE_GROUP_MAPPING sgm, site s "
                    + "where SITE_GROUP_ID=? and sgm.site_id=s.site_id and ACTIVE_FLAG='Y'"+jasStoreOnly;
            return jdbcTemplate.queryForObject(sql, new Object[]{siteGroupId}, Integer.class);
        } else {
            if (userAccessSites.length() != 0) {
                String sql = "select count(sgm.SITE_ID) from SITE_GROUP_MAPPING sgm, site s "
                        + "where SITE_GROUP_ID=? and sgm.site_id=s.site_id and ACTIVE_FLAG='Y'"+jasStoreOnly+" and (sgm.site_id,0) in (" + userAccessSites + ") ";
                return jdbcTemplate.queryForObject(sql, new Object[]{siteGroupId}, Integer.class);
            } else {
                return 0;
            }
        }
    }

    @Override
    public Integer create(SiteGroup siteGroup, String siteGroupTypeCode, List<Integer> siteIdList) {
        int siteGrpId = jdbcTemplate.queryForObject("select SITE_GROUP_SEQ.nextval from dual", Integer.class);
        int siteGrpTypeId = jdbcTemplate.queryForObject("select SITE_GROUP_TYPE_ID from SITE_GROUP_TYPE where SITE_GROUP_TYPE_CODE=?", new Object[]{siteGroupTypeCode}, Integer.class);

        jdbcTemplate.update("insert into SITE_GROUP (SITE_GROUP_ID, SITE_GROUP_NAME, SITE_GROUP_TYPE_ID, CREATED_USER_ID, CREATED_TIME, GROUP_OWNER_USER_ID) "
                        + "values (?,?,?,?,?,?)", siteGrpId, siteGroup.getSiteGroupName(), siteGrpTypeId,
                siteGroup.getCreatedBy(), siteGroup.getCreatedTime(), siteGroup.getGroupOwnerId());

        jdbcTemplate.batchUpdate("insert into SITE_GROUP_MAPPING (SITE_GROUP_ID, SITE_ID) values (?,?)", new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setInt(1, siteGrpId);
                ps.setInt(2, siteIdList.get(i));
            }

            @Override
            public int getBatchSize() {
                return siteIdList.size();
            }
        });

        return siteGrpId;
    }

    @Override
    public void addSitesToGroup(int siteGrpId, List<Integer> siteIdList) {
        jdbcTemplate.batchUpdate("insert into SITE_GROUP_MAPPING (SITE_GROUP_ID, SITE_ID) "
                + "select ? as SITE_GROUP_ID, ? as SITE_ID from dual "
                + "where not exists ( select SITE_GROUP_ID, SITE_ID from SITE_GROUP_MAPPING "
                + "where SITE_GROUP_ID=? and site_id=?)", new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setInt(1, siteGrpId);
                ps.setInt(2, siteIdList.get(i));
                ps.setInt(3, siteGrpId);
                ps.setInt(4, siteIdList.get(i));
            }

            @Override
            public int getBatchSize() {
                return siteIdList.size();
            }
        });
    }

    @Override
    public void replaceGroupSites(int siteGroupId, List<Integer> siteIdList) {
        jdbcTemplate.update("delete from SITE_GROUP_MAPPING where SITE_GROUP_ID=?", siteGroupId);

        jdbcTemplate.batchUpdate("insert into SITE_GROUP_MAPPING (SITE_GROUP_ID, SITE_ID) values (?,?)", new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setInt(1, siteGroupId);
                ps.setInt(2, siteIdList.get(i));
            }

            @Override
            public int getBatchSize() {
                return siteIdList.size();
            }
        });
    }

    @Override
    public void deleteSiteGroup(int siteGroupId) {
        jdbcTemplate.update("delete from SITE_GROUP_MAPPING where SITE_GROUP_ID=?", siteGroupId);
        jdbcTemplate.update("delete from SITE_GROUP where SITE_GROUP_ID=?", siteGroupId);
    }

    @Override
    public List<Map<String, Object>> getSiteGroupsTypes(User user) {
        return jdbcTemplate.queryForList("select SITE_GROUP_TYPE_ID as \"siteGroupTypeId\", SITE_GROUP_TYPE_CODE as \"siteGroupTypeCode\", " +
                (("en".equals(user.getLanguage()) || user.getLanguage() == null) ? "SITE_GROUP_TYPE_DESC" : "SITE_GROUP_TYPE_DESC_FR") + " as \"siteGroupTypeDesc\", GLOBAL_FLAG as \"globalFlag\" "
                + "from SITE_GROUP_TYPE order by SITE_GROUP_TYPE_ID asc");
    }

    @Override
    public List<Map<String, Object>> getSitesByGroup(Integer groupId, User user, String userAccessSites, int userId, String sessionType) throws Exception {

        String jasStoreOnly = "";
        if (("minMax").equals(sessionType) || ("MM").equals(sessionType))
            jasStoreOnly = " and s.JAS_STORE = 'Y' ";

        if (user.getRoleId() == ROLE.ADMIN.getNumVal() || user.getRoleId() == ROLE.XO.getNumVal()) {
            String sql = "select /*+ INDEX_JOIN(ST) */ s.SITE_ID as \"siteId\", ID_NUMBER as \"idNumber\", PRODUCT_AVAILABILITY_ID as \"productAvailabilityId\", "
                    + "SITE_NAME as \"siteName\", ACTIVE_FLAG as \"activeFlag\", s.VIRTUAL_SITE_FLAG as \"virtualSiteFlag\", s.POS_TYPE as \"posType\", s.CREATED_USER_ID as \"createdUserId\", "
                    + "s.SITE_TYPE_CODE as \"siteTypeCode\", " + (("en".equals(user.getLanguage()) || user.getLanguage() == null) ? "SITE_TYPE_DESC" : "SITE_TYPE_DESC_FR") + " as \"siteTypeDesc\", "
                    + "(case when s.MARKET_UPDATE_DT is not null and MARKET_UPDATE_DT > EPTS_UPDATE_DT then 1 else 0 end) as \"brUpdated\", "
                    + "s.LATITUDE as \"latitude\", s.LONGITUDE as \"longitude\", to_char(SNAPSHOT_DT, 'MM/DD/YYYY') as \"snapshotDt\", to_char(STAGED_SNAPSHOT_DT, 'MM/DD/YYYY') as \"stagedSnapshotDt\", "
                    + "to_char(EPTS_UPDATE_DT, 'MM/DD/YYYY') as \"eptsUpdateDt\", to_char(SALES_UPDATE_DT, 'MM/DD/YYYY') as \"salesUpdateDt\", "
                    + "ict.ma_inv_coll_template_id as \"invCollTemplateId\", ma_inv_coll_template_name as \"invCollTemplateName\", store_type_code as \"storeTypeCode\", "
                    + "sum(case when sm.site_id is not null then 1 else 0 end) as \"marketCount\", hsn.hs_network_name  as \"networkName\", hsn.hs_network_id  as \"networkId\",hsn.hs_network_type_id  as \"networkTypeId\", hsnh.network_hub_id  as \"networkHubId\" , "
                    +" hsn.is_active AS \"isActive\",hsnh.hub_site_id AS \"hubSiteId\" "
                    + "from site s "
                    + "inner join site_type st on s.SITE_TYPE_CODE = st.SITE_TYPE_CODE "
                    + "left join site_market sm on s.site_id = sm.site_id "
                    + "left join major_account ma on s.major_account_id = ma.major_account_id "
                    + "left join ma_inv_coll_template ict on ict.ma_inv_coll_template_id = nvl(s.ma_inv_coll_template_id, ma.default_inv_coll_template_id) "
                    + "left join hub_spoke_network_mapping hsnh on hsnh.hsn_site_id = s.site_id and hsnh.is_active = 1"
                    + "left join hub_spoke_network hsn on hsn.hs_network_id = hsnh.hsn_network_id and hsn.is_active = 1"
                    + "left join hs_network_hub  hsnh on hsnh.hub_site_id = s.site_id and hsnh.is_active = 1"
                    + " WHERE s.ACTIVE_FLAG = 'Y' "+jasStoreOnly;

            if (userId == 0) {
                sql += "and s.SITE_ID in (select site_id from SITE_GROUP_MAPPING sgm where sgm.SITE_GROUP_ID = ?)";
            } else {
                sql += "and s.SITE_ID in (select sgm.SITE_ID from SITE_GROUP_MAPPING sgm, SITE s, USER_SITE_TYPE ust "
                        + "where s.SITE_TYPE_CODE=ust.SITE_TYPE_CODE "
                        + "and s.SITE_ID = sgm.SITE_ID and ust.USER_ID=? and sgm.SITE_GROUP_ID = ?)";
            }

            sql += " group by s.SITE_ID, ID_NUMBER, PRODUCT_AVAILABILITY_ID, SITE_NAME, ACTIVE_FLAG, " +
                    "s.VIRTUAL_SITE_FLAG, s.POS_TYPE, s.CREATED_USER_ID, s.SITE_TYPE_CODE, " + (("en".equals(user.getLanguage()) || user.getLanguage() == null) ? "SITE_TYPE_DESC" : "SITE_TYPE_DESC_FR") + ", " +
                    "(case when s.MARKET_UPDATE_DT is not null and MARKET_UPDATE_DT > EPTS_UPDATE_DT then 1 else 0 end), s.LATITUDE, s.LONGITUDE, to_char(SNAPSHOT_DT, 'MM/DD/YYYY'), " +
                    "to_char(STAGED_SNAPSHOT_DT, 'MM/DD/YYYY'), to_char(EPTS_UPDATE_DT, 'MM/DD/YYYY'), " +
                    "to_char(SALES_UPDATE_DT, 'MM/DD/YYYY'), ict.ma_inv_coll_template_id, ma_inv_coll_template_name," +
                    "hsn.hs_network_id,"+
                    "hsn.is_active,"+
                    "hsn.hs_network_name,"+
                    "hsn.hs_network_type_id,"+
                    "hsnh.network_hub_id,  hsn.hs_network_type_id, hsnh.hub_site_id, store_type_code ";

            sql += " order by s.SITE_NAME";

            List<Map<String, Object>> siteList;
            if (userId == 0) {
                siteList = jdbcTemplate.queryForList(sql, groupId);
            } else {
                siteList = jdbcTemplate.queryForList(sql, userId, groupId);
            }

            return siteList;
        } else {
            if (userAccessSites != null && userAccessSites.length() > 0) {
                String sql = "select /*+ INDEX_JOIN(ST) */ s.SITE_ID as \"siteId\", ID_NUMBER as \"idNumber\", PRODUCT_AVAILABILITY_ID as \"productAvailabilityId\", "
                        + "SITE_NAME as \"siteName\", ACTIVE_FLAG as \"activeFlag\", s.VIRTUAL_SITE_FLAG as \"virtualSiteFlag\", s.POS_TYPE as \"posType\", s.CREATED_USER_ID as \"createdUserId\", "
                        + "s.SITE_TYPE_CODE as \"siteTypeCode\", " + (("en".equals(user.getLanguage()) || user.getLanguage() == null) ? "SITE_TYPE_DESC" : "SITE_TYPE_DESC_FR") + " as \"siteTypeDesc\", "
                        + "(case when s.MARKET_UPDATE_DT is not null and s.MARKET_UPDATE_DT > s.EPTS_UPDATE_DT then 1 else 0 end) as \"brUpdated\", "
                        + "s.LATITUDE as \"latitude\", s.LONGITUDE as \"longitude\", to_char(SNAPSHOT_DT, 'MM/DD/YYYY') as \"snapshotDt\", "
                        + "to_char(EPTS_UPDATE_DT, 'MM/DD/YYYY') as \"eptsUpdateDt\", to_char(SALES_UPDATE_DT, 'MM/DD/YYYY') as \"salesUpdateDt\", "
                        + "ict.ma_inv_coll_template_id as \"invCollTemplateId\", ma_inv_coll_template_name as \"invCollTemplateName\", store_type_code as \"storeTypeCode\", "
                        + "sum(case when sm.site_id is not null then 1 else 0 end) as \"marketCount\", hsn.hs_network_name  as \"networkName\", hsn.hs_network_id  as \"networkId\",hsn.hs_network_type_id  as \"networkTypeId\", hsnh.network_hub_id  as \"networkHubId\","
                        +" hsn.is_active AS \"isActive\",hsnh.hub_site_id AS \"hubSiteId\" "
                        + "from site s "
                        + "inner join site_type st on s.SITE_TYPE_CODE = st.SITE_TYPE_CODE "
                        + "left join site_market sm on s.site_id = sm.site_id "
                        + "left join major_account ma on s.major_account_id = ma.major_account_id "
                        + "left join ma_inv_coll_template ict on ict.ma_inv_coll_template_id = nvl(s.ma_inv_coll_template_id, ma.default_inv_coll_template_id) "
                        + "left join hub_spoke_network_mapping hsnh on hsnh.hsn_site_id = s.site_id and hsnh.is_active = 1"
                        + "left join hub_spoke_network hsn on hsn.hs_network_id = hsnh.hsn_network_id and hsn.is_active = 1"
                        + "left join hs_network_hub  hsnh on hsnh.hub_site_id = s.site_id and hsnh.is_active = 1"
                        + " WHERE s.ACTIVE_FLAG = 'Y' "+jasStoreOnly;
                if (userId == 0) {
                    sql += "and s.SITE_ID in (select site_id from SITE_GROUP_MAPPING where SITE_GROUP_ID = ? and (SITE_ID,0) in (" + userAccessSites + ") ) ";
                } else {
                    sql += "and s.SITE_ID in (select sgm.SITE_ID from SITE_GROUP_MAPPING sgm, SITE s, USER_SITE_TYPE ust "
                            + "where s.SITE_TYPE_CODE=ust.SITE_TYPE_CODE "
                            + "and s.SITE_ID = sgm.SITE_ID and ust.USER_ID=? and sgm.SITE_GROUP_ID = ? and (sgm.SITE_ID,0) in (" + userAccessSites + "))";
                }

                sql += " group by s.SITE_ID, ID_NUMBER, PRODUCT_AVAILABILITY_ID, SITE_NAME, ACTIVE_FLAG, " +
                        "s.VIRTUAL_SITE_FLAG, s.POS_TYPE, s.CREATED_USER_ID, s.SITE_TYPE_CODE, " + (("en".equals(user.getLanguage()) || user.getLanguage() == null) ? "SITE_TYPE_DESC" : "SITE_TYPE_DESC_FR") + ", " +
                        "(case when s.MARKET_UPDATE_DT is not null and MARKET_UPDATE_DT > EPTS_UPDATE_DT then 1 else 0 end), s.LATITUDE, s.LONGITUDE, to_char(SNAPSHOT_DT, 'MM/DD/YYYY'), to_char(EPTS_UPDATE_DT, 'MM/DD/YYYY'), " +
                        "to_char(SALES_UPDATE_DT, 'MM/DD/YYYY'), " +
                        "ict.ma_inv_coll_template_id, hsn.hs_network_id,"+
                        "hsn.hs_network_id,"+
                        "hsn.is_active,"+
                        "hsn.hs_network_name,"+
                        "hsn.hs_network_type_id,"+
                        "hsnh.network_hub_id,  hsn.hs_network_type_id,hsnh.hub_site_id, store_type_code ";

                sql += "order by s.SITE_NAME";

                List<Map<String, Object>> siteList;
                if (userId == 0) {
                    siteList = jdbcTemplate.queryForList(sql, groupId);
                } else {
                    siteList = jdbcTemplate.queryForList(sql, userId, groupId);
                }

                return siteList;
            } else {
                return null;
            }
        }
    }

    @Override
    public List<Map<String, Object>> getSitesByType(User user, String siteTypeCode) throws Exception {
        String sql = "select /*+ INDEX_JOIN(ST) */ s.SITE_ID as \"siteId\", ID_NUMBER as \"idNumber\", PRODUCT_AVAILABILITY_ID as \"productAvailabilityId\", "
                + "SITE_NAME as \"siteName\", ACTIVE_FLAG as \"activeFlag\", s.VIRTUAL_SITE_FLAG as \"virtualSiteFlag\", s.POS_TYPE as \"posType\", s.CREATED_USER_ID as \"createdUserId\", "
                + "s.SITE_TYPE_CODE as \"siteTypeCode\", " + (("en".equals(user.getLanguage()) || user.getLanguage() == null) ? "SITE_TYPE_DESC" : "SITE_TYPE_DESC_FR") + " as \"siteTypeDesc\", "
                + "(case when s.MARKET_UPDATE_DT is not null and MARKET_UPDATE_DT > EPTS_UPDATE_DT then 1 else 0 end) as \"brUpdated\", "
                + "s.LATITUDE as \"latitude\", s.LONGITUDE as \"longitude\", to_char(SNAPSHOT_DT, 'MM/DD/YYYY') as \"snapshotDt\", "
                + "to_char(EPTS_UPDATE_DT, 'MM/DD/YYYY') as \"eptsUpdateDt\", to_char(SALES_UPDATE_DT, 'MM/DD/YYYY') as \"salesUpdateDt\", "
                + "ict.ma_inv_coll_template_id as \"invCollTemplateId\", ma_inv_coll_template_name as \"invCollTemplateName\", store_type_code as \"storeTypeCode\", "
                + "sum(case when sm.site_id is not null then 1 else 0 end) as \"marketCount\" "
                + "from site s "
                + "inner join site_type st on s.SITE_TYPE_CODE = st.SITE_TYPE_CODE "
                + "left join site_market sm on s.site_id = sm.site_id "
                + "left join major_account ma on s.major_account_id = ma.major_account_id "
                + "left join ma_inv_coll_template ict on ict.ma_inv_coll_template_id = nvl(s.ma_inv_coll_template_id, ma.default_inv_coll_template_id) "
                + "WHERE s.ACTIVE_FLAG = 'Y' ";

        sql += "and s.SITE_TYPE_CODE  = ?";

        sql += " group by s.SITE_ID, ID_NUMBER, PRODUCT_AVAILABILITY_ID, SITE_NAME, ACTIVE_FLAG, " +
                "s.VIRTUAL_SITE_FLAG, s.POS_TYPE, s.CREATED_USER_ID, s.SITE_TYPE_CODE, " + (("en".equals(user.getLanguage()) || user.getLanguage() == null) ? "SITE_TYPE_DESC" : "SITE_TYPE_DESC_FR") + ", " +
                "(case when s.MARKET_UPDATE_DT is not null and MARKET_UPDATE_DT > EPTS_UPDATE_DT then 1 else 0 end), s.LATITUDE, s.LONGITUDE, to_char(SNAPSHOT_DT, 'MM/DD/YYYY'), to_char(EPTS_UPDATE_DT, 'MM/DD/YYYY'), " +
                "to_char(SALES_UPDATE_DT, 'MM/DD/YYYY'), ict.ma_inv_coll_template_id, ma_inv_coll_template_name, store_type_code ";

        sql += " order by s.SITE_NAME";

        return jdbcTemplate.queryForList(sql, siteTypeCode);
    }

    @Override
    public List<Map<String, Object>> getSiteAddresses(User user, String[] siteIds) {
        StringBuilder builder = new StringBuilder(
                "select SITE_ID \"siteId\", SITE_NAME \"siteName\", SITE_SHORT_NAME \"siteShortName\", ADDRESS1 \"address1\", ADDRESS2 \"address2\", CITY \"city\", STATE \"state\", POSTAL_CODE \"postalCode\", COUNTRY \"country\" from SITE where SITE_ID IN (");
        builder.append(String.join(", ", siteIds));
        builder.append(")");
        return jdbcTemplate.queryForList(builder.toString());
    }

    @Override
    public boolean checkGroupNameUnique(String groupName, User user) {
        try {
            Integer value = jdbcTemplate.queryForObject("select 1 from site_group where SITE_GROUP_NAME=? and CREATED_USER_ID=?", new Object[]{groupName, user.getUserId()}, Integer.class);
            return value != 1;
        } catch (Exception e) {
            return false;
        }
    }

    @Override
    public void updateGroupName(Integer siteGroupId, String newSiteGroupName) {
        jdbcTemplate.update("update site_group set SITE_GROUP_NAME=? where SITE_GROUP_ID=?", newSiteGroupName, siteGroupId);
    }

    @Override
    public boolean getHubSpokeNetworkFlag(Object siteId) {
        try{
            SqlRowSet rs = jdbcTemplate.queryForRowSet("select * from HS_NETWORK_HUB where HUB_SITE_ID = ? and IS_ACTIVE = ?",siteId,1);
            SqlRowSet rs1 = jdbcTemplate.queryForRowSet("select * from HS_NETWORK_SPOKE where SPOKE_SITE_ID = ? and IS_ACTIVE = ?",siteId,1);
            if(rs.next() || rs1.next()){
                return true;
            }
            else
                return false;
        }
        catch (Exception ex){
            LOGGER.error("Exception in getHubSpokeNetworkFlag for siteID: {0}"+siteId);
            LOGGER.error("Exception message is: {0}"+ ex.getMessage());
            LOGGER.error("Exception stacktrace is: {0}"+ ex.getStackTrace());
            return false;
        }
    }
}