package system

import (
	"bytes"
	"context"
	"encoding/json"
	"errors"
	"fmt"
	"mime/multipart"
	"net/url"
	"strconv"
	"strings"
	"time"
)

// ExportTemplate 导出模板实体
type ExportTemplate struct {
	ID           uint
	DBName       string
	Name         string
	TableName    string
	TemplateID   string
	TemplateInfo string
	Limit        *int
	Order        string
	Conditions   []*Condition
	JoinTemplate []*JoinTemplate
	CreatedAt    time.Time
	UpdatedAt    time.Time
}

// Condition 条件实体
type Condition struct {
	ID         uint
	TemplateID string
	From       string
	Column     string
	Operator   string
}

// JoinTemplate 关联模板实体
type JoinTemplate struct {
	ID         uint
	TemplateID string
	Joins      string
	Table      string
	On         string
}

// ExportTemplateSearchReq 导出模板搜索请求
type ExportTemplateSearchReq struct {
	Page           int
	PageSize       int
	Name           string
	TableName      string
	TemplateID     string
	StartCreatedAt *time.Time
	EndCreatedAt   *time.Time
}

// ExportTemplateRepo 导出模板仓储接口
type ExportTemplateRepo interface {
	Create(ctx context.Context, template *ExportTemplate) error
	Update(ctx context.Context, template *ExportTemplate) error
	Delete(ctx context.Context, id uint) error
	FindByID(ctx context.Context, id uint) (*ExportTemplate, error)
	FindByTemplateID(ctx context.Context, templateID string) (*ExportTemplate, error)
	GetList(ctx context.Context, req *ExportTemplateSearchReq) ([]*ExportTemplate, int64, error)
	DeleteConditionsByTemplateID(ctx context.Context, templateID string) error
	DeleteJoinsByTemplateID(ctx context.Context, templateID string) error
	CreateConditions(ctx context.Context, conditions []*Condition) error
	CreateJoins(ctx context.Context, joins []*JoinTemplate) error
	// Excel 相关
	GetDB(dbName string) interface{}
	ExecuteQuery(ctx context.Context, dbName, sql string, args ...interface{}) ([]map[string]interface{}, error)
	GetColumnTypes(ctx context.Context, dbName, tableName string) ([]string, error)
	HasDeletedAtColumn(ctx context.Context, tableName string) bool
	ImportData(ctx context.Context, dbName, tableName string, items []map[string]interface{}) error
}

// ExportTemplateUsecase 导出模板用例
type ExportTemplateUsecase struct {
	repo ExportTemplateRepo
}

// NewExportTemplateUsecase 创建导出模板用例
func NewExportTemplateUsecase(repo ExportTemplateRepo) *ExportTemplateUsecase {
	return &ExportTemplateUsecase{repo: repo}
}

// CreateExportTemplate 创建导出模板
func (uc *ExportTemplateUsecase) CreateExportTemplate(ctx context.Context, template *ExportTemplate) error {
	return uc.repo.Create(ctx, template)
}

// DeleteExportTemplate 删除导出模板
func (uc *ExportTemplateUsecase) DeleteExportTemplate(ctx context.Context, id uint) error {
	return uc.repo.Delete(ctx, id)
}

// DeleteExportTemplateByIds 批量删除导出模板
func (uc *ExportTemplateUsecase) DeleteExportTemplateByIds(ctx context.Context, ids []uint) error {
	for _, id := range ids {
		if err := uc.repo.Delete(ctx, id); err != nil {
			return err
		}
	}
	return nil
}

// UpdateExportTemplate 更新导出模板
func (uc *ExportTemplateUsecase) UpdateExportTemplate(ctx context.Context, template *ExportTemplate) error {
	// 删除旧的条件和关联
	if err := uc.repo.DeleteConditionsByTemplateID(ctx, template.TemplateID); err != nil {
		return err
	}
	if err := uc.repo.DeleteJoinsByTemplateID(ctx, template.TemplateID); err != nil {
		return err
	}

	// 保存条件
	conditions := template.Conditions
	template.Conditions = nil

	// 保存关联
	joins := template.JoinTemplate
	template.JoinTemplate = nil

	// 更新模板
	if err := uc.repo.Update(ctx, template); err != nil {
		return err
	}

	// 创建新的条件
	if len(conditions) > 0 {
		for i := range conditions {
			conditions[i].ID = 0
		}
		if err := uc.repo.CreateConditions(ctx, conditions); err != nil {
			return err
		}
	}

	// 创建新的关联
	if len(joins) > 0 {
		for i := range joins {
			joins[i].ID = 0
		}
		if err := uc.repo.CreateJoins(ctx, joins); err != nil {
			return err
		}
	}

	return nil
}

// GetExportTemplate 根据ID获取导出模板
func (uc *ExportTemplateUsecase) GetExportTemplate(ctx context.Context, id uint) (*ExportTemplate, error) {
	return uc.repo.FindByID(ctx, id)
}

// GetExportTemplateList 分页获取导出模板列表
func (uc *ExportTemplateUsecase) GetExportTemplateList(ctx context.Context, req *ExportTemplateSearchReq) ([]*ExportTemplate, int64, error) {
	return uc.repo.GetList(ctx, req)
}

// ExportExcel 导出Excel
func (uc *ExportTemplateUsecase) ExportExcel(ctx context.Context, templateID string, values url.Values) (*bytes.Buffer, string, error) {
	params := values.Get("params")
	paramsValues, err := url.ParseQuery(params)
	if err != nil {
		return nil, "", fmt.Errorf("解析 params 参数失败: %v", err)
	}

	template, err := uc.repo.FindByTemplateID(ctx, templateID)
	if err != nil {
		return nil, "", err
	}

	// 解析模板信息
	var templateInfoMap = make(map[string]string)
	columns, err := getJSONKeys(template.TemplateInfo)
	if err != nil {
		return nil, "", err
	}
	if err := json.Unmarshal([]byte(template.TemplateInfo), &templateInfoMap); err != nil {
		return nil, "", err
	}

	var tableTitle []string
	var selectKeyFmt []string
	for _, key := range columns {
		selectKeyFmt = append(selectKeyFmt, key)
		tableTitle = append(tableTitle, templateInfoMap[key])
	}

	selects := strings.Join(selectKeyFmt, ", ")

	// 构建SQL
	var sb strings.Builder
	sb.WriteString("SELECT ")
	sb.WriteString(selects)
	sb.WriteString(" FROM ")
	sb.WriteString(template.TableName)

	// JOIN
	if len(template.JoinTemplate) > 0 {
		for _, join := range template.JoinTemplate {
			sb.WriteString(" ")
			sb.WriteString(join.Joins)
			sb.WriteString(" ")
			sb.WriteString(join.Table)
			sb.WriteString(" ON ")
			sb.WriteString(join.On)
		}
	}

	// WHERE
	var wheres []string
	var args []interface{}

	// 软删除过滤
	filterDeleted := paramsValues.Get("filterDeleted") == "true"
	if filterDeleted {
		wheres = append(wheres, fmt.Sprintf("%s.deleted_at IS NULL", template.TableName))
		for _, join := range template.JoinTemplate {
			if uc.repo.HasDeletedAtColumn(ctx, join.Table) {
				wheres = append(wheres, fmt.Sprintf("%s.deleted_at IS NULL", join.Table))
			}
		}
	}

	// 条件
	for _, condition := range template.Conditions {
		value := paramsValues.Get(condition.From)
		op := strings.ToUpper(strings.TrimSpace(condition.Operator))

		if op == "BETWEEN" {
			startValue := paramsValues.Get("start" + condition.From)
			endValue := paramsValues.Get("end" + condition.From)
			if startValue != "" && endValue != "" {
				wheres = append(wheres, fmt.Sprintf("%s BETWEEN ? AND ?", condition.Column))
				args = append(args, startValue, endValue)
			}
			continue
		}

		if value == "" {
			continue
		}

		if op == "LIKE" {
			wheres = append(wheres, fmt.Sprintf("%s LIKE ?", condition.Column))
			args = append(args, "%"+value+"%")
		} else if op == "IN" || op == "NOT IN" {
			wheres = append(wheres, fmt.Sprintf("%s %s (?)", condition.Column, op))
			args = append(args, value)
		} else {
			wheres = append(wheres, fmt.Sprintf("%s %s ?", condition.Column, op))
			args = append(args, value)
		}
	}

	if len(wheres) > 0 {
		sb.WriteString(" WHERE ")
		sb.WriteString(strings.Join(wheres, " AND "))
	}

	// ORDER
	order := paramsValues.Get("order")
	if order == "" && template.Order != "" {
		order = template.Order
	}
	if order != "" {
		sb.WriteString(" ORDER BY ")
		sb.WriteString(order)
	}

	// LIMIT
	limit := paramsValues.Get("limit")
	if limit == "" && template.Limit != nil && *template.Limit != 0 {
		limit = strconv.Itoa(*template.Limit)
	}
	if limit != "" {
		sb.WriteString(" LIMIT ")
		sb.WriteString(limit)
	}

	// OFFSET
	offset := paramsValues.Get("offset")
	if offset != "" {
		sb.WriteString(" OFFSET ")
		sb.WriteString(offset)
	}

	// 执行查询
	tableMap, err := uc.repo.ExecuteQuery(ctx, template.DBName, sb.String(), args...)
	if err != nil {
		return nil, "", err
	}

	// 生成Excel
	return uc.generateExcel(tableMap, columns, tableTitle, template)
}

// generateExcel 生成Excel文件
func (uc *ExportTemplateUsecase) generateExcel(tableMap []map[string]interface{}, columns, tableTitle []string, template *ExportTemplate) (*bytes.Buffer, string, error) {
	// 简化实现：生成CSV格式
	var buf bytes.Buffer

	// 写入标题行
	buf.WriteString(strings.Join(tableTitle, ","))
	buf.WriteString("\n")

	// 写入数据行
	for _, row := range tableMap {
		var values []string
		for _, col := range columns {
			col = strings.ReplaceAll(col, "\"", "")
			col = strings.ReplaceAll(col, "`", "")
			if len(template.JoinTemplate) > 0 {
				columnAs := strings.Split(col, " as ")
				if len(columnAs) > 1 {
					col = strings.TrimSpace(columnAs[1])
				} else {
					columnArr := strings.Split(col, ".")
					if len(columnArr) > 1 {
						col = columnArr[1]
					}
				}
			}
			if t, ok := row[col].(time.Time); ok {
				values = append(values, t.Format("2006-01-02 15:04:05"))
			} else {
				values = append(values, fmt.Sprintf("%v", row[col]))
			}
		}
		buf.WriteString(strings.Join(values, ","))
		buf.WriteString("\n")
	}

	return &buf, template.Name, nil
}

// PreviewSQL 预览SQL
func (uc *ExportTemplateUsecase) PreviewSQL(ctx context.Context, templateID string, values url.Values) (string, error) {
	params := values.Get("params")
	paramsValues, _ := url.ParseQuery(params)

	template, err := uc.repo.FindByTemplateID(ctx, templateID)
	if err != nil {
		return "", err
	}

	// 解析模板信息
	var templateInfoMap = make(map[string]string)
	columns, err := getJSONKeys(template.TemplateInfo)
	if err != nil {
		return "", err
	}
	if err := json.Unmarshal([]byte(template.TemplateInfo), &templateInfoMap); err != nil {
		return "", err
	}

	var selectKeyFmt []string
	for _, key := range columns {
		selectKeyFmt = append(selectKeyFmt, key)
	}
	selects := strings.Join(selectKeyFmt, ", ")

	// 构建SQL
	var sb strings.Builder
	sb.WriteString("SELECT ")
	sb.WriteString(selects)
	sb.WriteString(" FROM ")
	sb.WriteString(template.TableName)

	// JOIN
	if len(template.JoinTemplate) > 0 {
		for _, join := range template.JoinTemplate {
			sb.WriteString(" ")
			sb.WriteString(join.Joins)
			sb.WriteString(" ")
			sb.WriteString(join.Table)
			sb.WriteString(" ON ")
			sb.WriteString(join.On)
		}
	}

	// WHERE
	var wheres []string

	filterDeleted := false
	if paramsValues != nil {
		filterDeleted = paramsValues.Get("filterDeleted") == "true"
	}
	if filterDeleted {
		wheres = append(wheres, fmt.Sprintf("%s.deleted_at IS NULL", template.TableName))
		for _, join := range template.JoinTemplate {
			if uc.repo.HasDeletedAtColumn(ctx, join.Table) {
				wheres = append(wheres, fmt.Sprintf("%s.deleted_at IS NULL", join.Table))
			}
		}
	}

	// 条件
	for _, condition := range template.Conditions {
		op := strings.ToUpper(strings.TrimSpace(condition.Operator))
		col := strings.TrimSpace(condition.Column)

		val := ""
		if paramsValues != nil {
			val = paramsValues.Get(condition.From)
		}

		switch op {
		case "BETWEEN":
			startValue := ""
			endValue := ""
			if paramsValues != nil {
				startValue = paramsValues.Get("start" + condition.From)
				endValue = paramsValues.Get("end" + condition.From)
			}
			if startValue != "" && endValue != "" {
				wheres = append(wheres, fmt.Sprintf("%s BETWEEN '%s' AND '%s'", col, startValue, endValue))
			} else {
				wheres = append(wheres, fmt.Sprintf("%s BETWEEN {start%s} AND {end%s}", col, condition.From, condition.From))
			}
		case "IN", "NOT IN":
			if val != "" {
				wheres = append(wheres, fmt.Sprintf("%s %s ('%s')", col, op, val))
			} else {
				wheres = append(wheres, fmt.Sprintf("%s %s ({%s})", col, op, condition.From))
			}
		case "LIKE":
			if val != "" {
				wheres = append(wheres, fmt.Sprintf("%s LIKE '%%%s%%'", col, val))
			} else {
				wheres = append(wheres, fmt.Sprintf("%s LIKE {%%%s%%}", col, condition.From))
			}
		default:
			if val != "" {
				wheres = append(wheres, fmt.Sprintf("%s %s '%s'", col, op, val))
			} else {
				wheres = append(wheres, fmt.Sprintf("%s %s {%s}", col, op, condition.From))
			}
		}
	}

	if len(wheres) > 0 {
		sb.WriteString(" WHERE ")
		sb.WriteString(strings.Join(wheres, " AND "))
	}

	// ORDER
	order := ""
	if paramsValues != nil {
		order = paramsValues.Get("order")
	}
	if order == "" && template.Order != "" {
		order = template.Order
	}
	if order != "" {
		sb.WriteString(" ORDER BY ")
		sb.WriteString(order)
	}

	// LIMIT/OFFSET
	limitStr := ""
	offsetStr := ""
	if paramsValues != nil {
		limitStr = paramsValues.Get("limit")
		offsetStr = paramsValues.Get("offset")
	}
	if limitStr == "" && template.Limit != nil && *template.Limit != 0 {
		limitStr = strconv.Itoa(*template.Limit)
	}

	limitInt := 0
	offsetInt := 0
	if limitStr != "" {
		if v, e := strconv.Atoi(limitStr); e == nil {
			limitInt = v
		}
	}
	if offsetStr != "" {
		if v, e := strconv.Atoi(offsetStr); e == nil {
			offsetInt = v
		}
	}

	if limitInt > 0 {
		sb.WriteString(" LIMIT ")
		sb.WriteString(strconv.Itoa(limitInt))
		if offsetInt > 0 {
			sb.WriteString(" OFFSET ")
			sb.WriteString(strconv.Itoa(offsetInt))
		}
	} else if offsetInt > 0 {
		sb.WriteString(" OFFSET ")
		sb.WriteString(strconv.Itoa(offsetInt))
	}

	return sb.String(), nil
}

// ExportTemplate 导出Excel模板
func (uc *ExportTemplateUsecase) ExportTemplate(ctx context.Context, templateID string) (*bytes.Buffer, string, error) {
	template, err := uc.repo.FindByTemplateID(ctx, templateID)
	if err != nil {
		return nil, "", err
	}

	var templateInfoMap = make(map[string]string)
	columns, err := getJSONKeys(template.TemplateInfo)
	if err != nil {
		return nil, "", err
	}
	if err := json.Unmarshal([]byte(template.TemplateInfo), &templateInfoMap); err != nil {
		return nil, "", err
	}

	var tableTitle []string
	for _, key := range columns {
		tableTitle = append(tableTitle, templateInfoMap[key])
	}

	// 生成CSV模板
	var buf bytes.Buffer
	buf.WriteString(strings.Join(tableTitle, ","))
	buf.WriteString("\n")

	return &buf, template.Name, nil
}

// ImportExcel 导入Excel
func (uc *ExportTemplateUsecase) ImportExcel(ctx context.Context, templateID string, file *multipart.FileHeader) error {
	template, err := uc.repo.FindByTemplateID(ctx, templateID)
	if err != nil {
		return err
	}

	src, err := file.Open()
	if err != nil {
		return err
	}
	defer src.Close()

	// 简化实现：读取CSV格式
	var templateInfoMap = make(map[string]string)
	if err := json.Unmarshal([]byte(template.TemplateInfo), &templateInfoMap); err != nil {
		return err
	}

	var titleKeyMap = make(map[string]string)
	for key, title := range templateInfoMap {
		titleKeyMap[title] = key
	}

	// 这里需要实际的Excel解析逻辑
	return errors.New("Excel导入功能需要excelize库支持")
}

// getJSONKeys 获取JSON对象的键（保持顺序）
func getJSONKeys(jsonStr string) ([]string, error) {
	var result []string
	dec := json.NewDecoder(strings.NewReader(jsonStr))

	// 读取开始的 {
	t, err := dec.Token()
	if err != nil {
		return nil, err
	}
	if t != json.Delim('{') {
		return nil, errors.New("expected {")
	}

	for dec.More() {
		// 读取键
		t, err := dec.Token()
		if err != nil {
			return nil, err
		}
		key, ok := t.(string)
		if !ok {
			return nil, errors.New("expected string key")
		}
		result = append(result, key)

		// 跳过值
		var value interface{}
		if err := dec.Decode(&value); err != nil {
			return nil, err
		}
	}

	return result, nil
}