package in.gov.chennaicorporation.gccoffice.cmdashboard.service;

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

import javax.sql.DataSource;
import javax.transaction.Transactional;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

@Service
public class CmDashboardService {
	private JdbcTemplate jdbcTemplate;

	@Autowired
	public void setDataSourcemysql(@Qualifier("mysqlAppDataSource") DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	@Transactional
	public List<Map<String, Object>> getCombinedCmDashboardReports(String zonename) {
		String sqlQuery;
		Object[] params;

		if (zonename != null && !zonename.equalsIgnoreCase("all")) {
			sqlQuery = "SELECT cm.schemename AS name, " + "COUNT(*) AS count, " + "'Scheme' AS type "
					+ "FROM gcc_apps.mytable cm " + "WHERE cm.zonename = ? " + "GROUP BY cm.schemename " + "UNION ALL "
					 + "SELECT cm.typeofwork AS name, " + "COUNT(*) AS count, " + "'TypeOfWork' AS type "
					+ "FROM gcc_apps.mytable cm " + "WHERE cm.zonename = ? " + "GROUP BY cm.typeofwork";
			params = new Object[] { zonename, zonename };
		} else {
			sqlQuery = "SELECT cm.schemename AS name, " + "COUNT(*) AS count, " + "'Scheme' AS type "
					+ "FROM gcc_apps.mytable cm " + "GROUP BY cm.schemename " + "UNION ALL "				
					+ "SELECT cm.typeofwork AS name, " + "COUNT(*) AS count, " + "'TypeOfWork' AS type "
					+ "FROM gcc_apps.mytable cm " + "GROUP BY cm.typeofwork";
			params = new Object[] {};
		}

		return jdbcTemplate.queryForList(sqlQuery, params);
	}
	  
	

	@Transactional
	public List<Map<String, Object>> getRegionCounts() {
		String sqlQuery = "SELECT regionname, COUNT(*) AS region_count FROM gcc_apps.mytable GROUP BY regionname";
		return jdbcTemplate.queryForList(sqlQuery);
	}

	@Transactional
	public List<Map<String, Object>> getZoneCounts() {
		String sqlQuery = "SELECT zonename, COUNT(*) AS zone_count FROM gcc_apps.mytable GROUP BY zonename";
		return jdbcTemplate.queryForList(sqlQuery);
	}
	
	@Transactional
	public Map<String, Object> getRegionDetails(String regionname) {
		
		String sqlQuery = "SELECT SUM(cm.valueofwork) AS totalValueOfWork, COUNT(*) AS totalWorkCounts "
				+ "FROM gcc_apps.mytable cm " + "WHERE cm.regionname = ? ";
		
		Object[] params = new Object[] { regionname };
		
		Map<String, Object> regiondetails = jdbcTemplate.queryForMap(sqlQuery, params);
		
	    String sqlQueryRegionZone = "SELECT zonename AS name, " +
	                      "COUNT(*) AS regionszones_count, " +
	                      "SUM(valueofwork) AS totalValueOfWork, " +
	                      "COUNT(*) AS totalWorkCounts " +
	                      "FROM gcc_apps.mytable " +
	                      "WHERE regionname = ? " +
	                      "GROUP BY zonename;";

	    List<Map<String, Object>> regionzonedetails = jdbcTemplate.queryForList(sqlQueryRegionZone, params);
	    regiondetails.put("regionzonedetails", regionzonedetails);

	    return regiondetails;
	}

	@Transactional
	public Map<String, Object> getSchemeDetails(String schemename, String zonename) {
		String sqlQuery = "SELECT SUM(cm.valueofwork) AS totalValueOfWork, COUNT(*) AS totalWorkCounts "
				+ "FROM gcc_apps.mytable cm " + "WHERE cm.schemename = ? "
				+ (zonename != null && !zonename.equalsIgnoreCase("all") ? " AND cm.zonename = ?" : "");

		Object[] params = zonename != null && !zonename.equalsIgnoreCase("all") ? new Object[] { schemename, zonename }
				: new Object[] { schemename };

		Map<String, Object> schemeDetails = jdbcTemplate.queryForMap(sqlQuery, params);

		String sqlQueryTypeOfWork = "SELECT cm.typeofwork AS name, COUNT(*) AS count, SUM(cm.valueofwork) AS totalValueOfWork "
				+ "FROM gcc_apps.mytable cm " + "WHERE cm.schemename = ? "
				+ (zonename != null && !zonename.equalsIgnoreCase("all") ? " AND cm.zonename = ?" : "")
				+ "GROUP BY cm.typeofwork";

		List<Map<String, Object>> typeOfWorkDetails = jdbcTemplate.queryForList(sqlQueryTypeOfWork, params);

		schemeDetails.put("typeOfWorkDetails", typeOfWorkDetails);

		return schemeDetails;
	}


	@Transactional
	public Map<String, Object> getTypeOfWorkDetails(String typeofwork, String zonename) {
		String sqlQuery = "SELECT SUM(cm.valueofwork) AS totalValueOfWork, COUNT(*) AS totalWorkCounts "
				+ "FROM gcc_apps.mytable cm " + "WHERE cm.typeofwork = ? "
				+ (zonename != null && !zonename.equalsIgnoreCase("all") ? " AND cm.zonename = ?" : "");
		Object[] params = zonename != null && !zonename.equalsIgnoreCase("all") ? new Object[] { typeofwork, zonename }
				: new Object[] { typeofwork };

		Map<String, Object> typeOfWorkDetail = jdbcTemplate.queryForMap(sqlQuery, params);

		String sqlQueryTypeOfWork = "SELECT cm.schemename AS name, COUNT(*) AS count, SUM(cm.valueofwork) AS totalValueOfWork "
				+ "FROM gcc_apps.mytable cm " + "WHERE cm.typeofwork = ? "
				+ (zonename != null && !zonename.equalsIgnoreCase("all") ? " AND cm.zonename = ?" : "")
				+ "GROUP BY cm.schemename";

		List<Map<String, Object>> schemeNameDetails = jdbcTemplate.queryForList(sqlQueryTypeOfWork, params);

		typeOfWorkDetail.put("schemeNameDetails", schemeNameDetails);

		return typeOfWorkDetail;
	}
	
	

	public List<Map<String, Object>> getCmDashboardAllReports() {
		String sqlQuery = "SELECT * FROM gcc_apps.mytable";
		List<Map<String, Object>> result = jdbcTemplate.queryForList(sqlQuery);

		return result;
	}
	
	public Map<String, Object> getCMDetails(int id) {
        String sql = "SELECT * FROM gcc_apps.mytable WHERE data_id = ?";
        return jdbcTemplate.queryForMap(sql, id);
    }
	
	@Transactional
	public List<Map<String, Object>> getCmDashboardAllReportsFiltered(String zonename, String schemename, String typeofwork) {
	    String sqlQuery = "SELECT * FROM gcc_apps.mytable WHERE 1=1";
	    List<Object> params = new ArrayList<>();

	    if (zonename != null && !zonename.equalsIgnoreCase("all")) {
	        sqlQuery += " AND zonename = ?";
	        params.add(zonename);
	    }
	    if (schemename != null && !schemename.equalsIgnoreCase("all")) {
	        sqlQuery += " AND schemename = ?";
	        params.add(schemename);
	    }
	    if (typeofwork != null && !typeofwork.equalsIgnoreCase("all")) {
	        sqlQuery += " AND typeofwork = ?";
	        params.add(typeofwork);
	    }

	    return jdbcTemplate.queryForList(sqlQuery, params.toArray());
	}

}