package customerDataFetchExcel;

import java.io.Serializable;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;
import java.util.stream.Collectors;

import javax.faces.bean.ManagedBean;
import javax.faces.bean.ViewScoped;
import javax.faces.context.FacesContext;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import main_package.DataBaseConnection;

@ManagedBean(name = "shareholderBean")
@ViewScoped
public class CustomerDataFetchExcelBean implements Serializable {

    private static final long serialVersionUID = 1L;

    private List<ShareholderModel> shareholderList = new ArrayList<>();

    private Date fromDate;
    private Date toDate;
    private int totalMembers;

    public CustomerDataFetchExcelBean() 
    {
    	
    	
    }

    // ========================= LOAD DATA =========================

    public void loadList() 
    {

        shareholderList.clear();

        try (Connection conn = DataBaseConnection.JAVAConnection()) {

            StringBuilder query = new StringBuilder(
                "SELECT DISTINCT  s.branch_id, f.branch_name, s.shareholderno, s.office_date, s.address, " +
                "s.first_applicantName, s.father_husband_name, s.unique_id, s.PAN, s.gender, s.pin_code " +
                "FROM shareholder_info s " +
                "LEFT JOIN firm_detail f ON s.branch_id = f.id " +
                "WHERE (s.PAN IS NULL OR TRIM(s.PAN) = '' " +
               "OR s.gender IS NULL OR TRIM(s.gender) = '' " +
                "OR s.pin_code IS NULL OR TRIM(s.pin_code) = '') "
            );

            if (fromDate != null) {
                query.append(" AND s.office_date >= ? ");
            }

            if (toDate != null) {
                query.append(" AND s.office_date <= ? ");
            }

            PreparedStatement ps = conn.prepareStatement(query.toString());

            int paramIndex = 1;

            if (fromDate != null) {
                ps.setDate(paramIndex++, new java.sql.Date(fromDate.getTime()));
            }

            if (toDate != null) {
                ps.setDate(paramIndex++, new java.sql.Date(toDate.getTime()));
            }

            ResultSet rs = ps.executeQuery();

            while (rs.next()) 
            {
                ShareholderModel s = new ShareholderModel();
                
                s.setBranchId(rs.getString("branch_id"));
                s.setBranchName(rs.getString("branch_name"));
                s.setShareholderno(rs.getString("shareholderno"));
                s.setApplicationDate(rs.getString("office_date"));
                s.setFirstName(rs.getString("first_applicantName"));
                s.setFatherName(rs.getString("father_husband_name"));
                s.setUniqueId(rs.getString("unique_id"));
                s.setPan(rs.getString("PAN"));
                s.setGender(rs.getString("gender"));
                s.setPinCode(rs.getString("pin_code"));
                s.setAddress(rs.getString("address"));
                
                shareholderList.add(s);
            }
            rs.close();
            ps.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        
       totalMembers=shareholderList.size();
       System.out.println("totalMembers"+totalMembers);
    }

    // ========================= DOWNLOAD EXCEL =========================
    
    public void downloadExcelBranchWise() {

        loadList(); // latest data load

        try (Workbook workbook = new XSSFWorkbook())
        {
            // Group data by branch
            Map<String, List<ShareholderModel>> branchMap =
                    shareholderList.stream()
                            .collect(Collectors.groupingBy(
                                    s -> s.getBranchName() == null ? "Unknown" : s.getBranchName()
                            ));

            // ================================
            // 1️⃣ CREATE SUMMARY SHEET
            // ================================

            Sheet summarySheet = workbook.createSheet("Branch Summary");

            CellStyle headerStyle = workbook.createCellStyle();

            Font font = workbook.createFont();
            font.setBoldweight(Font.BOLDWEIGHT_BOLD);   // ✅ Old version compatible
            headerStyle.setFont(font);  int summaryRowNum = 0;

            Row summaryHeader = summarySheet.createRow(summaryRowNum++);
            Cell h1 = summaryHeader.createCell(0);
            h1.setCellValue("Branch Name");
            h1.setCellStyle(headerStyle);

            Cell h2 = summaryHeader.createCell(1);
            h2.setCellValue("Total Members");
            h2.setCellStyle(headerStyle);

            int grandTotal = 0;

            for (String branch : branchMap.keySet()) {

                int branchTotal = branchMap.get(branch).size();
                grandTotal += branchTotal;

                Row row = summarySheet.createRow(summaryRowNum++);
                row.createCell(0).setCellValue(branch);
                row.createCell(1).setCellValue(branchTotal);
            }
            
            // Grand Total Row
            Row grandRow = summarySheet.createRow(summaryRowNum++);
            Cell g1 = grandRow.createCell(0);
            g1.setCellValue("Grand Total");
            g1.setCellStyle(headerStyle);

            Cell g2 = grandRow.createCell(1);
            g2.setCellValue(grandTotal);
            g2.setCellStyle(headerStyle);

            summarySheet.autoSizeColumn(0);
            summarySheet.autoSizeColumn(1);

            // ================================
            // 2️⃣ CREATE BRANCH WISE SHEETS
            // ================================

            for (String branch : branchMap.keySet()) {

                Sheet sheet = workbook.createSheet(branch);
                int rowNum = 0;

                // Branch Title
                Row branchTitle = sheet.createRow(rowNum++);
                Cell titleCell = branchTitle.createCell(0);
                titleCell.setCellValue("Branch : " + branch);
                titleCell.setCellStyle(headerStyle);

                // Total Members Row
                Row totalRow = sheet.createRow(rowNum++);
                totalRow.createCell(0).setCellValue("Total Members");
                totalRow.createCell(1).setCellValue(branchMap.get(branch).size());

                rowNum++; // blank row

                // Table Header
                Row header = sheet.createRow(rowNum++);
                String[] columns = {
                        "Shareholder No", "Application Date", "First Name",
                        "Father Name", "Unique ID", "PAN",
                        "Gender", "Pin Code", "Address"
                };

                for (int i = 0; i < columns.length; i++) {
                    Cell cell = header.createCell(i);
                    cell.setCellValue(columns[i]);
                    cell.setCellStyle(headerStyle);
                }

                // Data Rows
                for (ShareholderModel s : branchMap.get(branch)) {

                    Row row = sheet.createRow(rowNum++);

                    row.createCell(0).setCellValue(s.getShareholderno());
                    row.createCell(1).setCellValue(s.getApplicationDate());
                    row.createCell(2).setCellValue(s.getFirstName());
                    row.createCell(3).setCellValue(s.getFatherName());
                    row.createCell(4).setCellValue(s.getUniqueId());
                    row.createCell(5).setCellValue(s.getPan());
                    row.createCell(6).setCellValue(s.getGender());
                    row.createCell(7).setCellValue(s.getPinCode());
                    row.createCell(8).setCellValue(s.getAddress());
                }

                for (int i = 0; i < columns.length; i++) {
                    sheet.autoSizeColumn(i);
                }
            }

            // ================================
            // 3️⃣ DOWNLOAD FILE
            // ================================

            FacesContext context = FacesContext.getCurrentInstance();
            HttpServletResponse response =
                    (HttpServletResponse) context.getExternalContext().getResponse();

            response.reset();
            response.setContentType(
                    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition",
                    "attachment; filename=BranchWiseShareholderReport.xlsx");

            OutputStream out = response.getOutputStream();
            workbook.write(out);
            out.flush();
            out.close();

            context.responseComplete();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    // ========================= GETTERS & SETTERS =========================

    public List<ShareholderModel> getShareholderList() {
        return shareholderList;
    }

    public Date getFromDate() {
        return fromDate;
    }

    public void setFromDate(Date fromDate) {
        this.fromDate = fromDate;
    }

    public Date getToDate() {
        return toDate;
    }

    public void setToDate(Date toDate) {
        this.toDate = toDate;
    }

	public int getTotalMembers()
	{
		return totalMembers= shareholderList.size();
	}

	public void setTotalMembers(int totalMembers) {
		this.totalMembers = totalMembers;
	}

	public void setShareholderList(List<ShareholderModel> shareholderList) {
		this.shareholderList = shareholderList;
	}

	
}