package com.comet.search.service;

import com.comet.search.config.DatabaseConfig;
import com.comet.search.model.SearchRequest;
import com.comet.search.model.SearchResult;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.*;

@Service
public class SearchService {

    private String decodeUID(String encodedUid) {
        try {
            byte[] decodedBytes = Base64.getDecoder().decode(encodedUid);
            return new String(decodedBytes);
        } catch (IllegalArgumentException e) {
            return encodedUid; // fallback if not base64
        }
    }

    public List<SearchResult> searchByName(String searchName, int offset) {
        List<SearchResult> results = new ArrayList<>();
        List<DatabaseConfig> databases = DatabaseConfig.getAllDatabases();

        for (DatabaseConfig db : databases) {
            try (Connection conn = DriverManager.getConnection(db.getJdbcUrl(), DatabaseConfig.DB_USER, DatabaseConfig.DB_PASSWORD)) {
                String sql = "SELECT t1.mobile, t1.cname, t1.dob, t1.padd AS permanent_address, " +
                             "COALESCE(t2.fladd, 'N/A') AS local_address, COALESCE(t2.ffname, 'N/A') AS father_name, " +
                             "t1.altno, t1.email, t1.uid, t1.doa " +
                             "FROM " + db.getTable1() + " t1 " +
                             "LEFT JOIN " + db.getTable2() + " t2 ON t1.id1 = t2.id2 " +
                             "WHERE t1.cname = ? " +
                             "LIMIT 10 OFFSET ?";

                try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
                    pstmt.setString(1, searchName);
                    pstmt.setInt(2, offset);

                    try (ResultSet rs = pstmt.executeQuery()) {
                        while (rs.next()) {
                            results.add(mapRow(rs));
                        }
                    }
                }
            } catch (SQLException e) {
                System.err.println("Database error for " + db.getDbName() + ": " + e.getMessage());
            }
        }
        return results;
    }

    public List<SearchResult> performAdvancedSearch(SearchRequest request) {
        if (!request.getSearchName().isEmpty()) {
            return searchByName(request.getSearchName(), request.getOffset());
        }

        List<SearchResult> combinedResults = new ArrayList<>();
        Set<String> secondaryMobile = new HashSet<>();
        Set<String> secondaryAltNo = new HashSet<>();
        Set<String> secondaryUid = new HashSet<>();
        Set<String> secondaryEmail = new HashSet<>();

        List<DatabaseConfig> databases = DatabaseConfig.getAllDatabases();

        // Initial Search
        for (DatabaseConfig db : databases) {
            try (Connection conn = DriverManager.getConnection(db.getJdbcUrl(), DatabaseConfig.DB_USER, DatabaseConfig.DB_PASSWORD)) {
                StringBuilder sql = new StringBuilder("SELECT t1.mobile, t1.cname, t1.dob, t1.padd AS permanent_address, " +
                        "COALESCE(t2.fladd, 'N/A') AS local_address, COALESCE(t2.ffname, 'N/A') AS father_name, " +
                        "t1.altno, t1.email, t1.uid, t1.doa " +
                        "FROM ").append(db.getTable1()).append(" t1 ")
                        .append("LEFT JOIN ").append(db.getTable2()).append(" t2 ON t1.id1 = t2.id2 WHERE 1=1 ");

                List<Object> params = new ArrayList<>();

                if (request.getSearchMobile() != null && !request.getSearchMobile().isEmpty()) {
                    sql.append(" AND (t1.mobile = ? OR t1.altno = ?) ");
                    params.add(request.getSearchMobile());
                    params.add(request.getSearchMobile());
                }
                if (request.getSearchEmail() != null && !request.getSearchEmail().isEmpty()) {
                    sql.append(" AND t1.email = ? ");
                    params.add(request.getSearchEmail());
                }
                if (request.getSearchAltNo() != null && !request.getSearchAltNo().isEmpty()) {
                    sql.append(" AND (t1.mobile = ? OR t1.altno = ?) ");
                    params.add(request.getSearchAltNo());
                    params.add(request.getSearchAltNo());
                }
                if (request.getSearchUid() != null && !request.getSearchUid().isEmpty()) {
                    String decodedUid = decodeUID(request.getSearchUid());
                    sql.append(" AND t1.uid = ? ");
                    params.add(decodedUid);
                }

                sql.append(" LIMIT 10 OFFSET ?");
                params.add(request.getOffset());

                try (PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {
                    for (int i = 0; i < params.size(); i++) {
                        pstmt.setObject(i + 1, params.get(i));
                    }

                    try (ResultSet rs = pstmt.executeQuery()) {
                        while (rs.next()) {
                            SearchResult result = mapRow(rs);
                            combinedResults.add(result);

                            if (result.getMobile() != null && !result.getMobile().isEmpty()) {
                                secondaryMobile.add(result.getMobile());
                            }
                            if (result.getAltno() != null && !result.getAltno().isEmpty()) {
                                secondaryAltNo.add(result.getAltno());
                            }
                            if (result.getUid() != null && result.getUid().length() == 12) {
                                secondaryUid.add(result.getUid());
                            }
                            if (result.getEmail() != null && !result.getEmail().isEmpty()) {
                                secondaryEmail.add(result.getEmail());
                            }
                        }
                    }
                }
            } catch (SQLException e) {
                System.err.println("Database error during initial search for " + db.getDbName() + ": " + e.getMessage());
            }
        }

        // Secondary Search
        if (secondaryMobile.isEmpty() && secondaryAltNo.isEmpty() && secondaryUid.isEmpty() && secondaryEmail.isEmpty()) {
            return combinedResults;
        }

        for (DatabaseConfig db : databases) {
             try (Connection conn = DriverManager.getConnection(db.getJdbcUrl(), DatabaseConfig.DB_USER, DatabaseConfig.DB_PASSWORD)) {
                 performSecondaryQuery(conn, db, "mobile", secondaryMobile, combinedResults);
                 performSecondaryQuery(conn, db, "altno", secondaryAltNo, combinedResults);
                 performSecondaryQuery(conn, db, "uid", secondaryUid, combinedResults);
                 performSecondaryQuery(conn, db, "email", secondaryEmail, combinedResults);
             } catch (SQLException e) {
                 System.err.println("Database error during secondary search for " + db.getDbName() + ": " + e.getMessage());
             }
        }

        return combinedResults;
    }

    private void performSecondaryQuery(Connection conn, DatabaseConfig db, String field, Set<String> values, List<SearchResult> combinedResults) throws SQLException {
        if (values.isEmpty()) return;

        StringBuilder inClause = new StringBuilder();
        for (int i = 0; i < values.size(); i++) {
            inClause.append("?");
            if (i < values.size() - 1) {
                inClause.append(",");
            }
        }

        String sql = "SELECT t1.mobile, t1.cname, t1.dob, t1.padd AS permanent_address, " +
                "COALESCE(t2.fladd, 'N/A') AS local_address, COALESCE(t2.ffname, 'N/A') AS father_name, " +
                "t1.altno, t1.email, t1.uid, t1.doa " +
                "FROM " + db.getTable1() + " t1 " +
                "LEFT JOIN " + db.getTable2() + " t2 ON t1.id1 = t2.id2 ";

        if (field.equals("mobile") || field.equals("altno")) {
             sql += "WHERE t1.mobile IN (" + inClause + ") OR t1.altno IN (" + inClause + ")";
        } else {
             sql += "WHERE t1." + field + " IN (" + inClause + ")";
        }

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            int paramIndex = 1;
            for (String value : values) {
                pstmt.setString(paramIndex++, value);
            }
            if (field.equals("mobile") || field.equals("altno")) {
                for (String value : values) {
                    pstmt.setString(paramIndex++, value);
                }
            }

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    SearchResult result = mapRow(rs);
                    if (!combinedResults.contains(result)) {
                        combinedResults.add(result);
                    }
                }
            }
        }
    }

    private SearchResult mapRow(ResultSet rs) throws SQLException {
        SearchResult r = new SearchResult();
        r.setMobile(rs.getString("mobile"));
        r.setCname(rs.getString("cname"));
        r.setDob(rs.getString("dob"));
        r.setPermanent_address(rs.getString("permanent_address"));
        r.setLocal_address(rs.getString("local_address"));
        r.setFather_name(rs.getString("father_name"));
        r.setAltno(rs.getString("altno"));
        r.setEmail(rs.getString("email"));
        r.setUid(rs.getString("uid"));
        r.setDoa(rs.getString("doa"));
        return r;
    }
}