package com.talentcard.server.business.admin;

import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import com.talentcard.server.business.DatabaseRows;
import com.talentcard.server.business.PageResult;
import com.talentcard.server.common.BusinessException;
import com.talentcard.server.common.ErrorCode;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class ChildInformationAdminService {

    private static final List<String> AUDIT_STATUSES = List.of("待审核", "已通过", "审核不通过");

    private final JdbcTemplate jdbc;

    public ChildInformationAdminService(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    @Transactional(readOnly = true)
    public PageResult<Map<String, Object>> page(
            String childKeyword,
            String talentKeyword,
            String cardLevel,
            String status,
            LocalDate applyStart,
            LocalDate applyEnd,
            int page,
            int size
    ) {
        validateFilter(status, applyStart, applyEnd);
        int safePage = Math.max(page, 1);
        int safeSize = Math.min(Math.max(size, 1), 100);
        List<Object> parameters = new ArrayList<>();
        String where = where(childKeyword, talentKeyword, cardLevel, status, applyStart, applyEnd, parameters);
        Long total = jdbc.queryForObject("select count(*) " + baseFrom() + where, Long.class, parameters.toArray());
        if (total == null || total == 0) {
            return new PageResult<>(List.of(), safePage, safeSize, 0);
        }

        List<Object> pageParameters = new ArrayList<>(parameters);
        pageParameters.add(safeSize);
        pageParameters.add((safePage - 1) * safeSize);
        List<Map<String, Object>> records = DatabaseRows.normalize(jdbc.queryForList(
                selectFields() + baseFrom() + where + " order by ci.submitted_at desc, ci.id desc limit ? offset ?",
                pageParameters.toArray()
        )).stream().map(this::view).toList();
        return new PageResult<>(records, safePage, safeSize, total);
    }

    @Transactional(readOnly = true)
    public Map<String, Object> detail(Long id) {
        List<Map<String, Object>> rows = DatabaseRows.normalize(jdbc.queryForList(
                selectFields() + baseFrom() + " where ci.id = ? and ci.deleted = 0",
                id
        ));
        if (rows.isEmpty()) {
            throw new BusinessException(ErrorCode.NOT_FOUND, "子女信息不存在");
        }
        return view(rows.get(0));
    }

    @Transactional
    public Map<String, Object> audit(Long id, String status, String auditRemark) {
        String targetStatus = clean(status);
        if (!List.of("已通过", "审核不通过").contains(targetStatus)) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "审核状态只能为已通过或审核不通过");
        }
        String remark = clean(auditRemark);
        if ("审核不通过".equals(targetStatus) && remark.isBlank()) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "审核不通过时必须填写原因");
        }
        int updated = jdbc.update("""
                update child_information
                set audit_status = ?, reject_reason = ?, updated_at = now()
                where id = ? and deleted = 0 and audit_status = '待审核'
                """, targetStatus, "审核不通过".equals(targetStatus) ? remark : null, id);
        if (updated != 1) {
            Integer exists = jdbc.queryForObject(
                    "select count(*) from child_information where id = ? and deleted = 0",
                    Integer.class,
                    id
            );
            if (exists == null || exists == 0) {
                throw new BusinessException(ErrorCode.NOT_FOUND, "子女信息不存在");
            }
            throw new BusinessException(ErrorCode.CONFLICT, "该子女信息已审核，请刷新后重试");
        }
        return detail(id);
    }

    private String selectFields() {
        return """
                select ci.id,
                       ci.child_name,
                       ci.gender,
                       ci.birth_date,
                       ci.proof_file_ids,
                       ci.audit_status as status,
                       ci.reject_reason,
                       ci.submitted_at as apply_time,
                       ci.submitted_at as register_time,
                       u.name as talent_name,
                       u.id_card_no as talent_id_card_no,
                       u.gender as talent_gender,
                       u.mobile as talent_mobile,
                       u.contact_address,
                       u.work_address,
                       coalesce(tc.card_no, u.talent_card_no) as talent_card_no,
                       coalesce(tc.card_level, u.talent_level) as card_level
                """;
    }

    private String baseFrom() {
        return """
                 from child_information ci
                 join talent_user u on u.id = ci.talent_id and u.deleted = 0
                 left join talent_card tc on tc.id = (
                     select max(card.id)
                     from talent_card card
                     where card.talent_id = ci.talent_id
                       and card.card_type = 'PERSONAL'
                       and card.deleted = 0
                 )
                """;
    }

    private String where(
            String childKeyword,
            String talentKeyword,
            String cardLevel,
            String status,
            LocalDate applyStart,
            LocalDate applyEnd,
            List<Object> parameters
    ) {
        StringBuilder sql = new StringBuilder(" where ci.deleted = 0");
        String child = clean(childKeyword);
        if (!child.isBlank()) {
            sql.append(" and ci.child_name like ?");
            parameters.add("%" + child + "%");
        }
        String talent = clean(talentKeyword);
        if (!talent.isBlank()) {
            sql.append(" and (u.name like ? or u.mobile like ? or u.talent_card_no like ? or tc.card_no like ?)");
            String like = "%" + talent + "%";
            parameters.addAll(List.of(like, like, like, like));
        }
        String level = clean(cardLevel);
        if (!level.isBlank()) {
            sql.append(" and coalesce(tc.card_level, u.talent_level) = ?");
            parameters.add(level);
        }
        String auditStatus = clean(status);
        if (!auditStatus.isBlank()) {
            sql.append(" and ci.audit_status = ?");
            parameters.add(auditStatus);
        }
        if (applyStart != null) {
            sql.append(" and ci.submitted_at >= ?");
            parameters.add(applyStart.atStartOfDay());
        }
        if (applyEnd != null) {
            sql.append(" and ci.submitted_at < ?");
            parameters.add(applyEnd.plusDays(1).atStartOfDay());
        }
        return sql.toString();
    }

    private Map<String, Object> view(Map<String, Object> row) {
        Map<String, Object> result = new LinkedHashMap<>(row);
        String proofFileIds = clean(row.get("proofFileIds"));
        result.remove("proofFileIds");
        result.put("images", proofFileIds.isBlank()
                ? List.of()
                : Arrays.stream(proofFileIds.split(","))
                        .map(String::trim)
                        .filter(value -> !value.isBlank())
                        .toList());
        return result;
    }

    private void validateFilter(String status, LocalDate applyStart, LocalDate applyEnd) {
        String value = clean(status);
        if (!value.isBlank() && !AUDIT_STATUSES.contains(value)) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "子女信息状态不正确");
        }
        if (applyStart != null && applyEnd != null && applyStart.isAfter(applyEnd)) {
            throw new BusinessException(ErrorCode.BAD_REQUEST, "申请开始时间不能晚于结束时间");
        }
    }

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