package com.britton.services;

import com.britton.models.MemberCardDataBean;
import com.britton.models.CardLogoBean;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;
import java.util.Comparator;
import java.util.stream.Collectors;


public class IdCardsReportService {

    public static List<MemberCardDataBean> getMemberCardData(Connection conn, String jasperBatchId) {
        List<MemberCardDataBean> cardData = new ArrayList<>();
        try {
            System.out.println(jasperBatchId);
            String orderClause = "";
            if (!jasperBatchId.contains("Samples-")) {
                orderClause = " ORDER BY employer_name, full_name_last_name_first";
            }
            String sql = "SELECT * FROM id_card_print_data WHERE jasper_batch_id = ?" + orderClause;
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, jasperBatchId);
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {

                System.out.println("-start : " + rs.getString("primary_mb_member_key"));
                MemberCardDataBean mcdb = new MemberCardDataBean(
                    rs.getString("employer_name"),
                    rs.getString("group_number"),
                    rs.getString("primary_mb_member_key"),
                    rs.getString("family_id"),
                    rs.getString("full_name"),
                    rs.getString("full_name_last_name_first"),
                    rs.getString("rx_group"),
                    rs.getString("medical_eff_date"),
                    rs.getString("dental_coverage"),
                    rs.getString("network_provider"),
                    rs.getString("provider_line_1"),
                    rs.getString("provider_line_2"),
                    rs.getString("provider_line_3"),
                    rs.getString("provider_line_4"),
                    rs.getString("provider_line_5"),
                    rs.getString("provider_line_6"),
                    rs.getString("provider_line_7"),
                    rs.getString("provider_line_8"),
                    rs.getString("provider_line_9"),
                    rs.getString("provider_line_10"),
                    rs.getString("provider_line_11"),
                    rs.getString("provider_line_12"),
                    rs.getString("claim_to_1"),
                    rs.getString("claim_to_2"),
                    rs.getString("claim_to_3"),
                    rs.getString("claim_to_4"),
                    rs.getString("claim_to_5"),
                    rs.getString("claim_to_6"),
                    rs.getString("claim_to_7"),
                    rs.getString("claim_to_8"),
                    rs.getString("claim_to_9"),
                    rs.getString("claim_to_10"),
                    rs.getString("claim_to_11"),
                    rs.getString("claim_to_12"),
                    rs.getString("customer_service"),
                    rs.getString("web_url"),
                    rs.getString("dependent_1"),
                    rs.getString("dependent_2"),
                    rs.getString("dependent_3"),
                    rs.getString("dependent_4"),
                    rs.getString("dependent_5"),
                    rs.getString("dependent_6"),
                    rs.getString("dependent_7"),
                    rs.getString("dependent_8"),
                    rs.getString("benefit_desc_1"),
                    rs.getString("benefit_1"),
                    rs.getString("benefit_desc_2"),
                    rs.getString("benefit_2"),
                    rs.getString("benefit_desc_3"),
                    rs.getString("benefit_3"),
                    rs.getString("benefit_desc_4"),
                    rs.getString("benefit_4"),
                    rs.getString("benefit_desc_5"),
                    rs.getString("benefit_5"),
                    rs.getString("benefit_desc_6"),
                    rs.getString("benefit_6"),
                    rs.getString("benefit_desc_7"),
                    rs.getString("benefit_7"),
                    rs.getString("benefit_desc_8"),
                    rs.getString("benefit_8"),
                    rs.getString("benefit_desc_9"),
                    rs.getString("benefit_9"),
                    rs.getString("benefit_desc_10"),
                    rs.getString("benefit_10"),
                    rs.getString("benefit_desc_11"),
                    rs.getString("benefit_11"),
                    rs.getString("benefit_desc_12"),
                    rs.getString("benefit_12"),
                    rs.getString("benefit_desc_13"),
                    rs.getString("benefit_13"),
                    rs.getString("benefit_desc_14"),
                    rs.getString("benefit_14"),
                    rs.getString("precert_1"),
                    rs.getString("precert_2"),
                    rs.getString("precert_3"),
                    rs.getString("precert_4"),
                    rs.getString("jasper_batch_id"),
                    rs.getString("sample_plan_title"),
                    rs.getString("employer_logo_filename"),
                    rs.getString("network_logo_filename")
                );
                System.out.println("-card : " + mcdb.getPrimary_mb_member_key());
                cardData.add(mcdb);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("-number of member cards: " + cardData.size());
        // cardData.sort(Comparator.comparing(MemberCardDataBean::getFull_name_last_name_first)); 
        return cardData;
    }

    public static List<CardLogoBean> getEmployerLogoImages(Connection conn, List<MemberCardDataBean> memberCardsDataList) {
        List<CardLogoBean> logoData = new ArrayList<>();

        Set<String> employerLogoFilenames = memberCardsDataList.stream()
            .map(MemberCardDataBean::getEmployer_logo_filename)
            .collect(Collectors.toSet());

        try {
            // System.out.println(jasperBatchId);
            String sqlWhereIn = buildDynamicWhereIn(employerLogoFilenames.size());
            String sql = "SELECT * FROM id_card_employer_logos WHERE filename IN (" + sqlWhereIn.toString() + ")";
            PreparedStatement pstmt = conn.prepareStatement(sql);

            int index = 1;
            for (String filename : employerLogoFilenames) {
                pstmt.setString(index++, filename);
            }
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {

                System.out.println("-start : " + rs.getString("filename"));
                CardLogoBean clb = new CardLogoBean(
                    rs.getString("filename"),
                    rs.getBytes("image_data"),
                    rs.getString("content_type"),
                    rs.getFloat("aspect_ratio")
                );
                System.out.println("-logo : " + clb.getFilename());
                logoData.add(clb);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("-number of logos: " + logoData.size());
        return logoData;
    }

    public static List<CardLogoBean> getNetworkLogoImages(Connection conn, List<MemberCardDataBean> memberCardsDataList) {
        List<CardLogoBean> logoData = new ArrayList<>();

        Set<String> networkLogoFilenames = memberCardsDataList.stream()
            .map(MemberCardDataBean::getNetwork_logo_filename)
            .collect(Collectors.toSet());

        try {
            // System.out.println(jasperBatchId);
            String sqlWhereIn = buildDynamicWhereIn(networkLogoFilenames.size());
            String sql = "SELECT * FROM id_card_network_logos WHERE filename IN (" + sqlWhereIn.toString() + ")";
            PreparedStatement pstmt = conn.prepareStatement(sql);

            int index = 1;
            for (String filename : networkLogoFilenames) {
                pstmt.setString(index++, filename);
            }
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {

                System.out.println("-start : " + rs.getString("filename"));
                CardLogoBean clb = new CardLogoBean(
                    rs.getString("filename"),
                    rs.getBytes("image_data"),
                    rs.getString("content_type"),
                    rs.getFloat("aspect_ratio")
                );
                System.out.println("-logo : " + clb.getFilename());
                logoData.add(clb);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("-number of logos: " + logoData.size());
        return logoData;
    }

    private static String buildDynamicWhereIn(int size) {
        StringBuilder builder = new StringBuilder();
        for (int i = 0; i < size; i++) {
            builder.append("?");
            if (i < size - 1) {
                builder.append(", ");
            }
        }
        return builder.toString();
    }
    
}
