package com.talentcard.server.service;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.DayOfWeek;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.YearMonth;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import com.talentcard.server.business.PageResult;
import com.talentcard.server.business.merchant.MerchantTypeCatalog;
import com.talentcard.server.common.BusinessException;
import com.talentcard.server.common.ErrorCode;
import com.talentcard.server.mapper.MerchantSettlementStatisticsMapper;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class MerchantSettlementStatisticsService {
    private static final int EXPORT_LIMIT = 200000;

    private final MerchantSettlementStatisticsMapper mapper;

    public MerchantSettlementStatisticsService(MerchantSettlementStatisticsMapper mapper) {
        this.mapper = mapper;
    }

    @Transactional(readOnly = true)
    public PageResult<Map<String, Object>> summaries(
            Long merchantId,
            String merchantType,
            String periodType,
            String startDate,
            String endDate,
            int page,
            int size
    ) {
        requireMerchantIdWhenSpecified(merchantId);
        String normalizedPeriod = normalizePeriod(periodType);
        DateRange range = range(normalizedPeriod, startDate, endDate);
        int safePage = Math.max(page, 1);
        int safeSize = Math.min(Math.max(size, 1), 100);
        List<Map<String, Object>> records = mapper.selectPeriodSummaries(
                        merchantId,
                        merchantTypeCode(merchantType),
                        normalizedPeriod,
                        range.startTime(),
                        range.endTime(),
                        safeSize,
                        (safePage - 1) * safeSize
                ).stream()
                .map(row -> view(row, normalizedPeriod))
                .toList();
        Long total = mapper.countPeriodSummaries(
                merchantId,
                merchantTypeCode(merchantType),
                normalizedPeriod,
                range.startTime(),
                range.endTime()
        );
        return new PageResult<>(records, safePage, safeSize, total == null ? 0 : total);
    }

    @Transactional(readOnly = true)
    public Map<String, Object> overview(Long merchantId, String merchantType, String startDate, String endDate) {
        requireMerchantIdWhenSpecified(merchantId);
        DateRange range = range("DAY", startDate, endDate);
        List<Map<String, Object>> rows = mapper.selectMerchantOverviews(merchantId, merchantTypeCode(merchantType), range.startTime(), range.endTime());
        Map<String, Object> result = new LinkedHashMap<>();
        result.put("startDate", range.startDate() == null ? "" : range.startDate().toString());
        result.put("endDate", range.endDateInclusive() == null ? "" : range.endDateInclusive().toString());
        result.put("records", rows.stream().map(row -> view(row, "TOTAL")).toList());
        result.put("total", total(rows));
        return result;
    }

    public Map<String, Object> currentMonthOverview(Long merchantId) {
        YearMonth currentMonth = YearMonth.now();
        return overview(merchantId, null, currentMonth.atDay(1).toString(), currentMonth.atEndOfMonth().toString());
    }

    private String merchantTypeCode(String value) {
        String cleaned = clean(value);
        return cleaned.isBlank() ? "" : MerchantTypeCatalog.canonicalCode(cleaned);
    }

    @Transactional(readOnly = true)
    public Map<String, Object> dashboardCards(Long merchantId) {
        requireMerchantId(merchantId);
        LocalDate today = LocalDate.now();
        LocalDate weekStartDate = today.with(DayOfWeek.MONDAY);
        Map<String, Object> row = mapper.selectDashboardCards(
                merchantId,
                today.atStartOfDay(),
                today.plusDays(1).atStartOfDay(),
                weekStartDate.atStartOfDay(),
                today.plusDays(1).atStartOfDay()
        );
        long transactionAmount = longValue(row, "transactionAmountCents");
        long todayVerificationCount = longValue(row, "todayVerificationCount");
        long weekVerificationCount = longValue(row, "weekVerificationCount");
        long subsidyAmount = longValue(row, "subsidyAmountCents");

        Map<String, Object> result = new LinkedHashMap<>();
        result.put("merchantId", merchantId);
        result.put("statisticsDate", today.toString());
        result.put("weekStartDate", weekStartDate.toString());
        result.put("transactionTotal", amountCard("交易总额", transactionAmount));
        result.put("todayVerification", countCard("今日核销", todayVerificationCount));
        result.put("weekVerification", countCard("本周核销", weekVerificationCount));
        result.put("subsidyTotal", amountCard("补贴总额", subsidyAmount));
        result.put("transactionAmountCents", transactionAmount);
        result.put("transactionAmount", yuan(transactionAmount));
        result.put("todayVerificationCount", todayVerificationCount);
        result.put("weekVerificationCount", weekVerificationCount);
        result.put("subsidyAmountCents", subsidyAmount);
        result.put("subsidyAmount", yuan(subsidyAmount));
        result.put("hasData", transactionAmount > 0 || todayVerificationCount > 0 || weekVerificationCount > 0 || subsidyAmount > 0);
        result.put("statisticsBasis", "交易总额统计已支付且已核销订单的实付金额；补贴总额统计政府承担的订单补贴、政府补贴券及酒店免费入住线下补贴，商家自行让利不进入政府补贴结算");
        return result;
    }

    @Transactional(readOnly = true)
    public byte[] exportSettlementDetailsExcel(Long merchantId, String status, String startDate, String endDate) {
        requireMerchantId(merchantId);
        DateRange range = range("DAY", startDate, endDate);
        StatusFilter statusFilter = normalizeExportStatus(status);
        List<Map<String, Object>> records = mapper.selectSettlementExportDetails(
                merchantId,
                statusFilter.orderStatus(),
                statusFilter.verifyStatus(),
                range.startTime(),
                range.endTime(),
                EXPORT_LIMIT
        );
        String[] headers = {
                "序号", "订单号", "订单类型", "商户名称", "门店名称", "持卡人", "手机号", "人才卡号", "人才卡等级",
                "商品/服务", "交易金额(元)", "政府补贴(元)", "应结算金额(元)", "优惠券金额(元)", "订单状态", "核销状态", "下单时间", "支付时间", "核销时间", "核销人员"
        };
        try (Workbook workbook = new XSSFWorkbook(); ByteArrayOutputStream output = new ByteArrayOutputStream()) {
            Sheet sheet = workbook.createSheet("结算明细");
            sheet.createFreezePane(0, 1);
            CellStyle headerStyle = headerStyle(workbook);
            Row headerRow = sheet.createRow(0);
            for (int index = 0; index < headers.length; index++) {
                headerRow.createCell(index).setCellValue(headers[index]);
                headerRow.getCell(index).setCellStyle(headerStyle);
            }
            int rowIndex = 1;
            for (Map<String, Object> record : records) {
                Row row = sheet.createRow(rowIndex);
                List<String> values = exportValues(rowIndex, record);
                for (int index = 0; index < values.size(); index++) {
                    row.createCell(index).setCellValue(values.get(index));
                }
                rowIndex++;
            }
            int[] widths = {8, 26, 18, 28, 24, 16, 18, 24, 16, 28, 16, 16, 18, 16, 14, 14, 24, 24, 24, 16};
            for (int index = 0; index < widths.length; index++) {
                sheet.setColumnWidth(index, widths[index] * 256);
            }
            workbook.write(output);
            return output.toByteArray();
        } catch (IOException exception) {
            throw new BusinessException(ErrorCode.INTERNAL_ERROR, "结算明细导出失败");
        }
    }

    private List<String> exportValues(int index, Map<String, Object> row) {
        List<String> values = new ArrayList<>();
        values.add(String.valueOf(index));
        values.add(text(row, "orderNo", "order_no"));
        values.add(orderTypeName(text(row, "orderType", "order_type")));
        values.add(text(row, "merchantName", "merchant_name"));
        values.add(text(row, "storeName", "store_name"));
        values.add(text(row, "talentName", "talent_name"));
        values.add(text(row, "talentMobile", "talent_mobile"));
        values.add(text(row, "cardNo", "card_no"));
        values.add(text(row, "cardLevel", "card_level"));
        values.add(text(row, "productName", "product_name"));
        values.add(string(yuan(longValue(row, "transactionAmountCents"))));
        values.add(string(yuan(longValue(row, "subsidyAmountCents"))));
        values.add(string(yuan(longValue(row, "settlementAmountCents"))));
        values.add(string(yuan(longValue(row, "couponAmountCents"))));
        values.add(orderStatusName(intValue(row, "orderStatus")));
        values.add(verifyStatusName(intValue(row, "verifyStatus")));
        values.add(string(value(row, "createdAt", "created_at")));
        values.add(string(value(row, "paidAt", "paid_at")));
        values.add(string(value(row, "verifyTime", "verify_time")));
        values.add(text(row, "verifyOperator", "verify_operator"));
        return values;
    }

    private Map<String, Object> amountCard(String title, long amountCents) {
        Map<String, Object> card = new LinkedHashMap<>();
        card.put("title", title);
        card.put("amountCents", amountCents);
        card.put("amount", yuan(amountCents));
        card.put("unit", "元");
        return card;
    }

    private Map<String, Object> countCard(String title, long count) {
        Map<String, Object> card = new LinkedHashMap<>();
        card.put("title", title);
        card.put("count", count);
        card.put("unit", "笔");
        return card;
    }

    private Map<String, Object> total(List<Map<String, Object>> rows) {
        long orderCount = 0;
        long transactionAmount = 0;
        long subsidyAmount = 0;
        long settlementAmount = 0;
        long originalAmount = 0;
        long couponAmount = 0;
        long subsidyOrderCount = 0;
        for (Map<String, Object> row : rows) {
            orderCount += longValue(row, "orderCount");
            transactionAmount += longValue(row, "transactionAmountCents");
            subsidyAmount += longValue(row, "subsidyAmountCents");
            settlementAmount += longValue(row, "settlementAmountCents");
            originalAmount += longValue(row, "originalAmountCents");
            couponAmount += longValue(row, "couponAmountCents");
            subsidyOrderCount += longValue(row, "governmentSubsidyOrderCount");
        }
        Map<String, Object> total = new LinkedHashMap<>();
        total.put("orderCount", orderCount);
        total.put("transactionAmountCents", transactionAmount);
        total.put("transactionAmount", yuan(transactionAmount));
        total.put("subsidyAmountCents", subsidyAmount);
        total.put("subsidyAmount", yuan(subsidyAmount));
        total.put("settlementAmountCents", settlementAmount);
        total.put("settlementAmount", yuan(settlementAmount));
        total.put("originalAmountCents", originalAmount);
        total.put("originalAmount", yuan(originalAmount));
        total.put("couponAmountCents", couponAmount);
        total.put("couponAmount", yuan(couponAmount));
        total.put("governmentSubsidyOrderCount", subsidyOrderCount);
        return total;
    }

    private Map<String, Object> view(Map<String, Object> row, String periodType) {
        Map<String, Object> result = new LinkedHashMap<>();
        if (row.containsKey("periodKey")) {
            result.put("periodKey", text(row, "periodKey"));
            result.put("periodType", periodType);
        }
        result.put("merchantId", value(row, "merchantId"));
        result.put("merchantName", text(row, "merchantName"));
        result.put("merchantType", text(row, "merchantType"));
        result.put("orderCount", longValue(row, "orderCount"));
        result.put("transactionAmountCents", longValue(row, "transactionAmountCents"));
        result.put("transactionAmount", yuan(longValue(row, "transactionAmountCents")));
        result.put("subsidyAmountCents", longValue(row, "subsidyAmountCents"));
        result.put("subsidyAmount", yuan(longValue(row, "subsidyAmountCents")));
        result.put("settlementAmountCents", longValue(row, "settlementAmountCents"));
        result.put("settlementAmount", yuan(longValue(row, "settlementAmountCents")));
        result.put("originalAmountCents", longValue(row, "originalAmountCents"));
        result.put("originalAmount", yuan(longValue(row, "originalAmountCents")));
        result.put("couponAmountCents", longValue(row, "couponAmountCents"));
        result.put("couponAmount", yuan(longValue(row, "couponAmountCents")));
        result.put("governmentSubsidyOrderCount", longValue(row, "governmentSubsidyOrderCount"));
        result.put("periodStartTime", value(row, "periodStartTime", "startTime"));
        result.put("periodEndTime", value(row, "periodEndTime", "endTime"));
        result.put("statisticsBasis", "已支付且已核销订单及酒店免费入住核销记录；补贴额统计政府承担的订单补贴、政府补贴券及酒店免费入住线下补贴，商家自行让利不进入政府补贴结算");
        return result;
    }

    private DateRange range(String periodType, String startDate, String endDate) {
        LocalDate start = parseDate(startDate, "开始日期");
        LocalDate end = parseDate(endDate, "结束日期");
        if (start != null && end != null && start.isAfter(end)) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "开始日期不能晚于结束日期");
        }
        if ("MONTH".equals(periodType)) {
            LocalDate normalizedStart = start == null ? null : start.withDayOfMonth(1);
            LocalDate normalizedEnd = end == null ? null : YearMonth.from(end).atEndOfMonth();
            return new DateRange(
                    normalizedStart,
                    normalizedEnd,
                    normalizedStart == null ? null : normalizedStart.atStartOfDay(),
                    normalizedEnd == null ? null : normalizedEnd.plusDays(1).atStartOfDay()
            );
        }
        return new DateRange(
                start,
                end,
                start == null ? null : start.atStartOfDay(),
                end == null ? null : end.plusDays(1).atStartOfDay()
        );
    }

    private LocalDate parseDate(String value, String field) {
        String text = clean(value);
        if (text.isBlank()) {
            return null;
        }
        try {
            if (text.matches("^\\d{4}-\\d{2}$")) {
                return YearMonth.parse(text).atDay(1);
            }
            return LocalDate.parse(text);
        } catch (Exception exception) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, field + "格式不正确，应为yyyy-MM-dd或yyyy-MM");
        }
    }

    private StatusFilter normalizeExportStatus(String raw) {
        String status = clean(raw).toUpperCase(Locale.ROOT).replace("-", "_");
        if (status.isBlank() || "ALL".equals(status) || "全部".equals(status)) {
            return new StatusFilter(null, null);
        }
        return switch (status) {
            case "PAID", "已支付" -> new StatusFilter(1, null);
            case "VERIFIED", "COMPLETED", "已核销", "已完成" -> new StatusFilter(1, 1);
            case "UNVERIFIED", "PENDING_VERIFICATION", "待核销", "未核销" -> new StatusFilter(1, 0);
            case "PENDING_PAYMENT", "UNPAID", "待支付", "未支付", "0" -> new StatusFilter(0, null);
            case "CANCELLED", "REFUNDED", "已取消", "已退款", "2" -> new StatusFilter(2, null);
            case "1" -> new StatusFilter(1, null);
            default -> throw new BusinessException(ErrorCode.BAD_REQUEST, "状态参数不正确");
        };
    }

    private String normalizePeriod(String value) {
        String period = clean(value).toUpperCase(Locale.ROOT);
        if (period.isBlank() || "DAY".equals(period) || "DAILY".equals(period) || "日".equals(period)) {
            return "DAY";
        }
        if ("MONTH".equals(period) || "MONTHLY".equals(period) || "月".equals(period)) {
            return "MONTH";
        }
        throw new BusinessException(ErrorCode.BAD_REQUEST, "统计周期只能为DAY日汇总或MONTH月汇总");
    }

    private void requireMerchantIdWhenSpecified(Long merchantId) {
        if (merchantId != null && merchantId <= 0) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "商户ID不正确");
        }
    }

    private void requireMerchantId(Long merchantId) {
        if (merchantId == null || merchantId <= 0) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "商户ID不正确");
        }
    }

    private CellStyle headerStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return style;
    }

    private Object value(Map<String, Object> row, String... keys) {
        if (row == null) {
            return null;
        }
        for (String key : keys) {
            if (row.containsKey(key)) {
                return row.get(key);
            }
        }
        return null;
    }

    private String text(Map<String, Object> row, String... keys) {
        Object value = value(row, keys);
        return value == null ? "" : String.valueOf(value).trim();
    }

    private long longValue(Map<String, Object> row, String key) {
        Object value = value(row, key);
        return value instanceof Number number ? number.longValue() : 0L;
    }

    private Integer intValue(Map<String, Object> row, String key) {
        Object value = value(row, key);
        return value instanceof Number number ? number.intValue() : null;
    }

    private BigDecimal yuan(long cents) {
        return BigDecimal.valueOf(cents, 2).setScale(2, RoundingMode.HALF_UP);
    }

    private String orderStatusName(Integer status) {
        if (status == null) {
            return "未知";
        }
        return switch (status) {
            case 0 -> "待支付";
            case 1 -> "已支付";
            case 2 -> "已取消/已退款";
            default -> "未知";
        };
    }

    private String verifyStatusName(Integer status) {
        if (status == null) {
            return "未知";
        }
        return status == 1 ? "已核销" : "未核销";
    }

    private String orderTypeName(String orderType) {
        return switch (clean(orderType).toUpperCase(Locale.ROOT)) {
            case "STORE_CONSUME" -> "到店消费";
            case "GROUP_BUY" -> "团购订单";
            case "HOTEL_RESERVATION" -> "酒店预约";
            case "HOTEL_FREE" -> "酒店免费入住";
            default -> clean(orderType);
        };
    }

    private String string(Object value) {
        return value == null ? "" : String.valueOf(value);
    }

    private String clean(String value) {
        return value == null ? "" : value.trim();
    }

    private record DateRange(LocalDate startDate, LocalDate endDateInclusive, LocalDateTime startTime, LocalDateTime endTime) {
    }

    private record StatusFilter(Integer orderStatus, Integer verifyStatus) {
    }
}