package com.napa.pulse.dao.impl;

import com.napa.pulse.dao.interfaces.SiteDAO;
import com.napa.pulse.entity.pulseui.Site;
import com.napa.pulse.entity.pulseui.SiteType;
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.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author Yong Wang
 * <p>
 * Created on 12/15/2014
 */

@Repository
public class SiteDAOImpl implements SiteDAO {

    private static final Logger LOGGER = LoggerFactory.getLogger(SiteDAOImpl.class);
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<Site> getUserSites(User user, String userAccessSites, int userId, String sessionType) {
        List<Site> sites;
        String jasStoreOnly = "";
        if (("minMax").equals(sessionType) || ("MM").equals(sessionType))
            jasStoreOnly = " and s.JAS_STORE = 'Y' ";
        try {
            if ((user.getRoleId() == ROLE.ADMIN.getNumVal() || user.getRoleId() == ROLE.XO.getNumVal()) && userId == 0) // Admin user
            {
                String sql = "select /*+ INDEX_JOIN(ST) */ s.SITE_ID, s.ID_NUMBER, s.PRODUCT_AVAILABILITY_ID, s.SITE_NAME, s.ACTIVE_FLAG, "
                        + "s.VIRTUAL_SITE_FLAG, POS_TYPE, s.CREATED_USER_ID, st.SITE_TYPE_CODE, st." + (("en".equals(user.getLanguage()) || user.getLanguage() == null) ? "SITE_TYPE_DESC" : "SITE_TYPE_DESC_FR") + ", st.UI_ORDER, "
                        + "(case when s.MARKET_UPDATE_DT is not null and s.MARKET_UPDATE_DT > s.EPTS_UPDATE_DT then 1 else 0 end) as BR_UPDATED, "
                        + "s.LATITUDE, s.LONGITUDE, to_char(s.SNAPSHOT_DT, 'MM/DD/YYYY') as SNAPSHOT_DT, to_char(STAGED_SNAPSHOT_DT, 'MM/DD/YYYY') as STAGED_SNAPSHOT_DT, "
                        + "to_char(s.EPTS_UPDATE_DT, 'MM/DD/YYYY') as EPTS_UPDATE_DT, to_char(s.SALES_UPDATE_DT, 'MM/DD/YYYY') as SALES_UPDATE_DT, "
                        + "sum(case when sm.site_id is not null then 1 else 0 end) as MARKET_COUNT, "
                        + "ICT.MA_INV_COLL_TEMPLATE_ID, MA_INV_COLL_TEMPLATE_NAME, STORE_TYPE_CODE "
                        + "from site s "
                        + "inner join site_type st on st.SITE_TYPE_CODE = s.SITE_TYPE_CODE "
                        + "left join site_market sm on sm.SITE_ID = s.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' " + jasStoreOnly
                        + "group by s.SITE_ID, s.ID_NUMBER, s.PRODUCT_AVAILABILITY_ID, s.SITE_NAME, s.ACTIVE_FLAG,  "
                        + "s.VIRTUAL_SITE_FLAG, POS_TYPE, s.CREATED_USER_ID, st.SITE_TYPE_CODE, st." + (("en".equals(user.getLanguage()) || user.getLanguage() == null) ? "SITE_TYPE_DESC" : "SITE_TYPE_DESC_FR") + ", st.UI_ORDER, "
                        + "(case when s.MARKET_UPDATE_DT is not null and s.MARKET_UPDATE_DT > s.EPTS_UPDATE_DT then 1 else 0 end), s.LATITUDE, s.LONGITUDE, to_char(s.SNAPSHOT_DT, 'MM/DD/YYYY'), to_char(STAGED_SNAPSHOT_DT, 'MM/DD/YYYY'), "
                        + "to_char(s.EPTS_UPDATE_DT, 'MM/DD/YYYY'), to_char(s.SALES_UPDATE_DT, 'MM/DD/YYYY'), ICT.MA_INV_COLL_TEMPLATE_ID, MA_INV_COLL_TEMPLATE_NAME, STORE_TYPE_CODE order by s.SITE_NAME asc ";

                sites = jdbcTemplate.query(sql, (rs, rowNum) -> {
                    Site site = new Site();
                    SiteType siteType = new SiteType();

                    site.setSiteId(rs.getInt("SITE_ID"));
                    site.setIdNumber(rs.getString("ID_NUMBER"));
                    site.setProductAvailabilityId(rs.getInt("PRODUCT_AVAILABILITY_ID"));
                    site.setSiteName(rs.getString("SITE_NAME"));
                    site.setActiveFlag(rs.getString("ACTIVE_FLAG"));
                    site.setVirtualSiteFlag(rs.getString("VIRTUAL_SITE_FLAG"));
                    site.setPosType(rs.getString("POS_TYPE"));
                    site.setCreatedUserId(rs.getInt("CREATED_USER_ID"));
                    site.setBrUpdated(rs.getInt("BR_UPDATED"));
                    site.setLongitude(rs.getDouble("LONGITUDE"));
                    site.setLatitude(rs.getDouble("LATITUDE"));
                    site.setSnapshotDt(rs.getString("SNAPSHOT_DT"));
                    site.setStagedSnapshotDt(rs.getString("STAGED_SNAPSHOT_DT"));
                    site.setEptsUpdateDt(rs.getString("EPTS_UPDATE_DT"));
                    site.setSalesUpdateDt(rs.getString("SALES_UPDATE_DT"));
                    site.setMarketCount(rs.getInt("MARKET_COUNT"));
                    site.setInvCollTemplateId(rs.getInt("MA_INV_COLL_TEMPLATE_ID"));
                    site.setInvCollTemplateName(rs.getString("MA_INV_COLL_TEMPLATE_NAME"));
                    siteType.setSiteTypeCode(rs.getString("SITE_TYPE_CODE"));
                    siteType.setSiteTypeDesc(rs.getString((("en".equals(user.getLanguage()) || user.getLanguage() == null) ? "SITE_TYPE_DESC" : "SITE_TYPE_DESC_FR")));
                    siteType.setUiOrder(rs.getInt("UI_ORDER"));
                    site.setSiteType(siteType);
                    site.setStoreTypeCode(rs.getString("STORE_TYPE_CODE"));

                    return site;
                });
            } else if ((user.getRoleId() == ROLE.ADMIN.getNumVal()) && userId > 0) {
                String sql = "select /*+ INDEX_JOIN(ST) */ s.SITE_ID, s.ID_NUMBER, s.PRODUCT_AVAILABILITY_ID, s.SITE_NAME, s.ACTIVE_FLAG, "
                        + "s.VIRTUAL_SITE_FLAG, POS_TYPE, s.CREATED_USER_ID, st.SITE_TYPE_CODE, st." + (("en".equals(user.getLanguage()) || user.getLanguage() == null) ? "SITE_TYPE_DESC" : "SITE_TYPE_DESC_FR") + ", st.UI_ORDER, "
                        + "(case when s.MARKET_UPDATE_DT is not null and s.MARKET_UPDATE_DT > s.EPTS_UPDATE_DT then 1 else 0 end) as BR_UPDATED, "
                        + "s.LATITUDE, s.LONGITUDE, to_char(s.SNAPSHOT_DT, 'MM/DD/YYYY') as SNAPSHOT_DT, to_char(STAGED_SNAPSHOT_DT, 'MM/DD/YYYY') as STAGED_SNAPSHOT_DT, to_char(s.EPTS_UPDATE_DT, 'MM/DD/YYYY') as EPTS_UPDATE_DT, to_char(s.SALES_UPDATE_DT, 'MM/DD/YYYY') as SALES_UPDATE_DT, "
                        + "sum(case when sm.site_id is not null then 1 else 0 end) as MARKET_COUNT, "
                        + "ICT.MA_INV_COLL_TEMPLATE_ID, MA_INV_COLL_TEMPLATE_NAME, STORE_TYPE_CODE "
                        + "from site s "
                        + "inner join site_type st on s.SITE_TYPE_CODE=st.SITE_TYPE_CODE "
                        + "inner join user_site_type ust on st.SITE_TYPE_CODE=ust.SITE_TYPE_CODE "
                        + "left join site_market sm on sm.SITE_ID = s.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' and ust.USER_ID=? " + jasStoreOnly
                        + "group by s.SITE_ID, s.ID_NUMBER, s.PRODUCT_AVAILABILITY_ID, s.SITE_NAME, s.ACTIVE_FLAG,  "
                        + "s.VIRTUAL_SITE_FLAG, POS_TYPE, s.CREATED_USER_ID, st.SITE_TYPE_CODE, st." + (("en".equals(user.getLanguage()) || user.getLanguage() == null) ? "SITE_TYPE_DESC" : "SITE_TYPE_DESC_FR") + ", "
                        + "st.UI_ORDER, (case when s.MARKET_UPDATE_DT is not null and s.MARKET_UPDATE_DT > s.EPTS_UPDATE_DT then 1 else 0 end), s.LATITUDE, s.LONGITUDE, to_char(s.SNAPSHOT_DT, 'MM/DD/YYYY'), to_char(STAGED_SNAPSHOT_DT, 'MM/DD/YYYY'), "
                        + "to_char(s.EPTS_UPDATE_DT, 'MM/DD/YYYY'), to_char(s.SALES_UPDATE_DT, 'MM/DD/YYYY'), ICT.MA_INV_COLL_TEMPLATE_ID, MA_INV_COLL_TEMPLATE_NAME, STORE_TYPE_CODE order by s.SITE_NAME asc ";

                sites = jdbcTemplate.query(sql, new Object[]{userId}, (rs, rowNum) -> {
                    Site site = new Site();
                    SiteType siteType = new SiteType();

                    site.setSiteId(rs.getInt("SITE_ID"));
                    site.setIdNumber(rs.getString("ID_NUMBER"));
                    site.setProductAvailabilityId(rs.getInt("PRODUCT_AVAILABILITY_ID"));
                    site.setSiteName(rs.getString("SITE_NAME"));
                    site.setActiveFlag(rs.getString("ACTIVE_FLAG"));
                    site.setVirtualSiteFlag(rs.getString("VIRTUAL_SITE_FLAG"));
                    site.setPosType(rs.getString("POS_TYPE"));
                    site.setCreatedUserId(rs.getInt("CREATED_USER_ID"));
                    site.setBrUpdated(rs.getInt("BR_UPDATED"));
                    site.setLongitude(rs.getDouble("LONGITUDE"));
                    site.setLatitude(rs.getDouble("LATITUDE"));
                    site.setSnapshotDt(rs.getString("SNAPSHOT_DT"));
                    site.setStagedSnapshotDt(rs.getString("STAGED_SNAPSHOT_DT"));
                    site.setEptsUpdateDt(rs.getString("EPTS_UPDATE_DT"));
                    site.setSalesUpdateDt(rs.getString("SALES_UPDATE_DT"));
                    site.setMarketCount(rs.getInt("MARKET_COUNT"));
                    site.setInvCollTemplateId(rs.getInt("MA_INV_COLL_TEMPLATE_ID"));
                    site.setInvCollTemplateName(rs.getString("MA_INV_COLL_TEMPLATE_NAME"));
                    siteType.setSiteTypeCode(rs.getString("SITE_TYPE_CODE"));
                    siteType.setSiteTypeDesc(rs.getString((("en".equals(user.getLanguage()) || user.getLanguage() == null) ? "SITE_TYPE_DESC" : "SITE_TYPE_DESC_FR")));
                    siteType.setUiOrder(rs.getInt("UI_ORDER"));
                    site.setSiteType(siteType);
                    site.setStoreTypeCode(rs.getString("STORE_TYPE_CODE"));

                    return site;
                });
            } else {
                if (userAccessSites != null && userAccessSites.length() > 0) {
                    if (userId == 0) {
                        String sql = "select /*+ INDEX_JOIN(ST) */ s.SITE_ID, s.ID_NUMBER, s.PRODUCT_AVAILABILITY_ID, s.SITE_NAME, s.ACTIVE_FLAG, "
                                + "s.VIRTUAL_SITE_FLAG, POS_TYPE, s.CREATED_USER_ID, st.SITE_TYPE_CODE, st." + (("en".equals(user.getLanguage()) || user.getLanguage() == null) ? "SITE_TYPE_DESC" : "SITE_TYPE_DESC_FR") + ", st.UI_ORDER, "
                                + "(case when s.MARKET_UPDATE_DT is not null and s.MARKET_UPDATE_DT > s.EPTS_UPDATE_DT then 1 else 0 end) as BR_UPDATED, "
                                + "s.LATITUDE, s.LONGITUDE, to_char(s.SNAPSHOT_DT, 'MM/DD/YYYY') as SNAPSHOT_DT, to_char(STAGED_SNAPSHOT_DT, 'MM/DD/YYYY') as STAGED_SNAPSHOT_DT, to_char(s.EPTS_UPDATE_DT, 'MM/DD/YYYY') as EPTS_UPDATE_DT, to_char(s.SALES_UPDATE_DT, 'MM/DD/YYYY') as SALES_UPDATE_DT, "
                                + "sum(case when sm.site_id is not null then 1 else 0 end) as MARKET_COUNT, "
                                + "ICT.MA_INV_COLL_TEMPLATE_ID, MA_INV_COLL_TEMPLATE_NAME, STORE_TYPE_CODE "
                                + "from site s "
                                + "inner join site_type st on s.SITE_TYPE_CODE=st.SITE_TYPE_CODE "
                                + "left join site_market sm on sm.SITE_ID = s.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' " + jasStoreOnly
                                + "and (s.SITE_ID, 0) in (" + userAccessSites + ") "
                                + "group by s.SITE_ID, s.ID_NUMBER, s.PRODUCT_AVAILABILITY_ID, s.SITE_NAME, s.ACTIVE_FLAG,  "
                                + "s.VIRTUAL_SITE_FLAG, POS_TYPE, s.CREATED_USER_ID, st.SITE_TYPE_CODE, st." + (("en".equals(user.getLanguage()) || user.getLanguage() == null) ? "SITE_TYPE_DESC" : "SITE_TYPE_DESC_FR") + ", "
                                + "st.UI_ORDER, (case when s.MARKET_UPDATE_DT is not null and s.MARKET_UPDATE_DT > s.EPTS_UPDATE_DT then 1 else 0 end), s.LATITUDE, s.LONGITUDE, to_char(s.SNAPSHOT_DT, 'MM/DD/YYYY'), to_char(STAGED_SNAPSHOT_DT, 'MM/DD/YYYY'), "
                                + "to_char(s.EPTS_UPDATE_DT, 'MM/DD/YYYY'), to_char(s.SALES_UPDATE_DT, 'MM/DD/YYYY'), ICT.MA_INV_COLL_TEMPLATE_ID, MA_INV_COLL_TEMPLATE_NAME, STORE_TYPE_CODE order by s.SITE_NAME asc ";

                        sites = jdbcTemplate.query(sql, (rs, rowNum) -> {
                            Site site = new Site();
                            SiteType siteType = new SiteType();
                            site.setSiteId(rs.getInt("SITE_ID"));
                            site.setIdNumber(rs.getString("ID_NUMBER"));
                            site.setProductAvailabilityId(rs.getInt("PRODUCT_AVAILABILITY_ID"));
                            site.setSiteName(rs.getString("SITE_NAME"));
                            site.setActiveFlag(rs.getString("ACTIVE_FLAG"));
                            site.setVirtualSiteFlag(rs.getString("VIRTUAL_SITE_FLAG"));
                            site.setPosType(rs.getString("POS_TYPE"));
                            site.setCreatedUserId(rs.getInt("CREATED_USER_ID"));
                            site.setBrUpdated(rs.getInt("BR_UPDATED"));
                            site.setLongitude(rs.getDouble("LONGITUDE"));
                            site.setLatitude(rs.getDouble("LATITUDE"));
                            site.setSnapshotDt(rs.getString("SNAPSHOT_DT"));
                            site.setStagedSnapshotDt(rs.getString("STAGED_SNAPSHOT_DT"));
                            site.setEptsUpdateDt(rs.getString("EPTS_UPDATE_DT"));
                            site.setSalesUpdateDt(rs.getString("SALES_UPDATE_DT"));
                            site.setMarketCount(rs.getInt("MARKET_COUNT"));
                            site.setInvCollTemplateId(rs.getInt("MA_INV_COLL_TEMPLATE_ID"));
                            site.setInvCollTemplateName(rs.getString("MA_INV_COLL_TEMPLATE_NAME"));
                            siteType.setSiteTypeCode(rs.getString("SITE_TYPE_CODE"));
                            siteType.setSiteTypeDesc(rs.getString((("en".equals(user.getLanguage()) || user.getLanguage() == null) ? "SITE_TYPE_DESC" : "SITE_TYPE_DESC_FR")));
                            siteType.setUiOrder(rs.getInt("UI_ORDER"));
                            site.setSiteType(siteType);
                            site.setStoreTypeCode(rs.getString("STORE_TYPE_CODE"));

                            return site;
                        });
                    } else {
                        String sql = "select /*+ parallel (s 10) */  s.SITE_ID, s.ID_NUMBER, s.PRODUCT_AVAILABILITY_ID, s.SITE_NAME, s.ACTIVE_FLAG, "
                                + "s.VIRTUAL_SITE_FLAG, POS_TYPE, s.CREATED_USER_ID, st.SITE_TYPE_CODE, st." + (("en".equals(user.getLanguage()) || user.getLanguage() == null) ? "SITE_TYPE_DESC" : "SITE_TYPE_DESC_FR") + ", st.UI_ORDER, "
                                + "(case when s.MARKET_UPDATE_DT is not null and s.MARKET_UPDATE_DT > s.EPTS_UPDATE_DT then 1 else 0 end) as BR_UPDATED, "
                                + "s.LATITUDE, s.LONGITUDE, to_char(s.SNAPSHOT_DT, 'MM/DD/YYYY') as SNAPSHOT_DT, to_char(STAGED_SNAPSHOT_DT, 'MM/DD/YYYY') as STAGED_SNAPSHOT_DT, to_char(s.EPTS_UPDATE_DT, 'MM/DD/YYYY') as EPTS_UPDATE_DT, to_char(s.SALES_UPDATE_DT, 'MM/DD/YYYY') as SALES_UPDATE_DT, "
                                + "sum(case when sm.site_id is not null then 1 else 0 end) as MARKET_COUNT, "
                                + "ICT.MA_INV_COLL_TEMPLATE_ID, MA_INV_COLL_TEMPLATE_NAME, STORE_TYPE_CODE "
                                + "from site s "
                                + "inner join site_type st on s.SITE_TYPE_CODE=st.SITE_TYPE_CODE "
                                + "inner join user_site_type ust on st.SITE_TYPE_CODE=ust.SITE_TYPE_CODE "
                                + "left join site_market sm on sm.SITE_ID = s.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' and ust.USER_ID=? and (s.SITE_ID, 0) in (" + userAccessSites + ") " + jasStoreOnly
                                + "group by s.SITE_ID, s.ID_NUMBER, s.PRODUCT_AVAILABILITY_ID, s.SITE_NAME, s.ACTIVE_FLAG,  "
                                + "s.VIRTUAL_SITE_FLAG, POS_TYPE, s.CREATED_USER_ID, st.SITE_TYPE_CODE, st." + (("en".equals(user.getLanguage()) || user.getLanguage() == null) ? "SITE_TYPE_DESC" : "SITE_TYPE_DESC_FR") + ", "
                                + "st.UI_ORDER, (case when s.MARKET_UPDATE_DT is not null and s.MARKET_UPDATE_DT > s.EPTS_UPDATE_DT then 1 else 0 end), s.LATITUDE, s.LONGITUDE, to_char(s.SNAPSHOT_DT, 'MM/DD/YYYY'), to_char(STAGED_SNAPSHOT_DT, 'MM/DD/YYYY'), "
                                + "to_char(s.EPTS_UPDATE_DT, 'MM/DD/YYYY'), to_char(s.SALES_UPDATE_DT, 'MM/DD/YYYY'), ICT.MA_INV_COLL_TEMPLATE_ID, MA_INV_COLL_TEMPLATE_NAME, STORE_TYPE_CODE order by s.SITE_NAME asc ";

                        sites = jdbcTemplate.query(sql, new Object[]{userId}, (rs, rowNum) -> {
                            Site site = new Site();
                            SiteType siteType = new SiteType();

                            site.setSiteId(rs.getInt("SITE_ID"));
                            site.setIdNumber(rs.getString("ID_NUMBER"));
                            site.setProductAvailabilityId(rs.getInt("PRODUCT_AVAILABILITY_ID"));
                            site.setSiteName(rs.getString("SITE_NAME"));
                            site.setActiveFlag(rs.getString("ACTIVE_FLAG"));
                            site.setVirtualSiteFlag(rs.getString("VIRTUAL_SITE_FLAG"));
                            site.setPosType(rs.getString("POS_TYPE"));
                            site.setCreatedUserId(rs.getInt("CREATED_USER_ID"));
                            site.setBrUpdated(rs.getInt("BR_UPDATED"));
                            site.setLongitude(rs.getDouble("LONGITUDE"));
                            site.setLatitude(rs.getDouble("LATITUDE"));
                            site.setSnapshotDt(rs.getString("SNAPSHOT_DT"));
                            site.setStagedSnapshotDt(rs.getString("STAGED_SNAPSHOT_DT"));
                            site.setEptsUpdateDt(rs.getString("EPTS_UPDATE_DT"));
                            site.setSalesUpdateDt(rs.getString("SALES_UPDATE_DT"));
                            site.setMarketCount(rs.getInt("MARKET_COUNT"));
                            site.setInvCollTemplateId(rs.getInt("MA_INV_COLL_TEMPLATE_ID"));
                            site.setInvCollTemplateName(rs.getString("MA_INV_COLL_TEMPLATE_NAME"));
                            siteType.setSiteTypeCode(rs.getString("SITE_TYPE_CODE"));
                            siteType.setSiteTypeDesc(rs.getString((("en".equals(user.getLanguage()) || user.getLanguage() == null) ? "SITE_TYPE_DESC" : "SITE_TYPE_DESC_FR")));
                            siteType.setUiOrder(rs.getInt("UI_ORDER"));
                            site.setSiteType(siteType);
                            site.setStoreTypeCode(rs.getString("STORE_TYPE_CODE"));

                            return site;
                        });
                    }
                } else {
                    return null;
                }
            }
        } catch (Exception e) {
            LOGGER.error("getUserSites",e);
            throw new RuntimeException(e);
        }

        return sites;
    }

}