package com.zhyc.module.base.mapper;

import com.zhyc.module.base.domain.SheepFile;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.util.StringUtils;

import java.util.Map;

/**
 * MyBatis SQL 提供者类
 */
public class SheepFileSqlProvider {

    /**
     * 生成动态查询的 SQL
     */
    public String selectByCondition(Map<String, Object> params) {
        SheepFile sheepFile = (SheepFile) params.get("sheepFile");
        Map<String, Object> conditions = (Map<String, Object>) params.get("conditions");

        // 使用 SQL 构建器
        SQL sql = new SQL();

        // 重要：使用与 XML 中完全相同的字段列表
        selectAllColumns(sql);

        sql.FROM("sheep_file");
        sql.WHERE("is_delete = 0");

        // 添加 SheepFile 条件
        addSheepFileConditions(sql, sheepFile);

        // 添加动态条件
        addDynamicConditions(sql, conditions);

        sql.ORDER_BY("id ASC");

        return sql.toString();
    }

    /**
     * 选择所有列（与 XML 中的 selectSheepFileVo 保持一致）
     */
    private void selectAllColumns(SQL sql) {
        sql.SELECT(
                "id, bs_manage_tags, ranch_id, dr_ranch, sheepfold_id, sheepfold_name, " +
                        "electronic_tags, variety_id, variety, family, name, gender, birthday, " +
                        "day_age, month_age, parity, birth_weight, weaning_date, status_id, " +
                        "weaning_weight, current_weight, weaning_day_age, weaning_daily_gain, " +
                        "breed_status_id, breed, bs_father_id, father_manage_tags, bs_mother_id, " +
                        "mother_manage_tags, receptor_id, receptor_manage_tags, father_father_id, " +
                        "grandfather_manage_tags, father_mother_id, grandmother_manage_tags, " +
                        "father_id, maternal_grandfather_manage_tags, mother_id, " +
                        "maternal_grandmother_manage_tags, mating_date, mating_type_id, " +
                        "preg_date, lambing_date, lambing_day, mating_day, gestation_day, " +
                        "expected_date, post_lambing_day, lactation_day, anestrous_day, " +
                        "mating_counts, mating_total, miscarriage_counts, comment, " +
                        "controlled, body, breast, source, source_date, source_ranch_id, " +
                        "source_ranch, update_by, update_time, create_by, create_time, " +
                        "is_delete"
        );
    }

    /**
     * 添加 SheepFile 对象中的条件
     */
    private void addSheepFileConditions(SQL sql, SheepFile sheepFile) {
        if (sheepFile == null) {
            return;
        }

        if (StringUtils.hasText(sheepFile.getBsManageTags())) {
            sql.WHERE("bs_manage_tags LIKE CONCAT('%', #{sheepFile.bsManageTags}, '%')");
        }
        if (StringUtils.hasText(sheepFile.getElectronicTags())) {
            sql.WHERE("electronic_tags LIKE CONCAT('%', #{sheepFile.electronicTags}, '%')");
        }
        if (StringUtils.hasText(sheepFile.getDrRanch())) {
            sql.WHERE("dr_ranch LIKE CONCAT('%', #{sheepFile.drRanch}, '%')");
        }
        if (StringUtils.hasText(sheepFile.getVariety())) {
            sql.WHERE("variety LIKE CONCAT('%', #{sheepFile.variety}, '%')");
        }
        if (StringUtils.hasText(sheepFile.getName())) {
            sql.WHERE("name LIKE CONCAT('%', #{sheepFile.name}, '%')");
        }
        if (sheepFile.getGender() != null) {
            sql.WHERE("gender = #{sheepFile.gender}");
        }
        if (sheepFile.getStatusId() != null) {
            sql.WHERE("status_id = #{sheepFile.statusId}");
        }
        if (StringUtils.hasText(sheepFile.getBreed())) {
            sql.WHERE("breed LIKE CONCAT('%', #{sheepFile.breed}, '%')");
        }
    }

    /**
     * 简化的动态条件处理
     */
    private void addDynamicConditions(SQL sql, Map<String, Object> conditions) {
        if (conditions == null || conditions.isEmpty()) {
            return;
        }

        for (Map.Entry<String, Object> entry : conditions.entrySet()) {
            String field = entry.getKey();
            Object value = entry.getValue();

            if (value == null) {
                continue;
            }

            String strValue = value.toString();

            // 处理空值条件
            if ("IS_NULL".equals(strValue)) {
                sql.WHERE(field + " IS NULL");
            } else if ("NOT_NULL".equals(strValue)) {
                sql.WHERE(field + " IS NOT NULL");
            }
            // 处理范围条件
            else if (strValue.startsWith("GT:") || strValue.startsWith("LT:") ||
                    strValue.startsWith("GE:") || strValue.startsWith("LE:")) {
                String operator = strValue.substring(0, 2);
                String numValue = strValue.substring(3);
                if (isNumeric(numValue)) {
                    sql.WHERE(field + " " + operator + " " + numValue);
                }
            }
            // 处理IN条件
            else if (strValue.contains(",")) {
                String[] values = strValue.split(",");
                StringBuilder inClause = new StringBuilder(field + " IN (");
                for (int i = 0; i < values.length; i++) {
                    inClause.append("'").append(values[i].trim()).append("'");
                    if (i < values.length - 1) {
                        inClause.append(",");
                    }
                }
                inClause.append(")");
                sql.WHERE(inClause.toString());
            }
            // 默认条件：使用等值查询
            else {
                sql.WHERE(field + " = #{conditions." + field + "}");
            }
        }
    }

    /**
     * 判断字符串是否为数字
     */
    private boolean isNumeric(String str) {
        if (str == null || str.trim().isEmpty()) {
            return false;
        }
        try {
            Double.parseDouble(str.trim());
            return true;
        } catch (NumberFormatException e) {
            return false;
        }
    }

    /**
     * 生成获取字段值的 SQL
     */
    public String selectFieldValues(Map<String, Object> params) {
        String fieldName = (String) params.get("fieldName");

        SQL sql = new SQL();
        sql.SELECT("DISTINCT " + fieldName + " AS field_value");
        sql.FROM("sheep_file");
        sql.WHERE(fieldName + " IS NOT NULL");
        sql.WHERE(fieldName + " != ''");
        sql.WHERE(fieldName + " != 'null'");
        sql.ORDER_BY(fieldName + " ASC");

        return sql.toString();
    }
}