package com.mini.capi.sys.App;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.mini.capi.biz.domain.DbConfig;
import com.mini.capi.biz.domain.SyncTablesView;
import com.mini.capi.biz.domain.SyncTask;
import com.mini.capi.biz.service.DbConfigService;
import com.mini.capi.biz.service.SyncTablesViewService;
import com.mini.capi.biz.service.SyncTaskService;
import com.mini.capi.config.DataSourceConfig;
import com.mini.capi.model.ApiResult;
import com.mini.capi.sys.domain.SelectOption;
import jakarta.annotation.Resource;
import lombok.Data;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.stream.Collectors;

@RestController
@RequestMapping("/Sys/data")
public class dataController {


    @Resource
    private SyncTaskService syncTaskService;


    @Resource
    private DbConfigService configService;


    @Resource
    private SyncTablesViewService tablesViewService;


    @Data
    static class TabDetail implements Serializable {
        private String createTime;
        private String dbName;
        private String tableName;
        private String tableDesc;
        private String dataLength;
        private String updateTime;

        public TabDetail(String createTime, String dbName, String tableName, String tableDesc, String dataLength, String updateTime) {
            this.createTime = createTime;
            this.dbName = dbName;
            this.tableName = tableName;
            this.tableDesc = tableDesc;
            this.dataLength = dataLength;
            this.updateTime = updateTime;
        }
    }

    @Data
    static class TabColumns implements Serializable {
        private String tableName;
        private Integer sort;
        private String colName;
        private String colType;
        private String colDesc;
        private String keyType;

        public TabColumns(String tableName, Integer sort, String colName, String colType, String colDesc, String keyType) {
            this.tableName = tableName;
            this.sort = sort;
            this.colName = colName;
            this.colType = colType;
            this.colDesc = colDesc;
            this.keyType = keyType;
        }
    }


    @Data
    static class TableInfo implements Serializable {
        private TabDetail tabDetail;
        private List<TabColumns> tabColumns;
        private long pkCnt;
        private long idxCnt;
        private long colCnt;
        private String ddlSql;
        private String selectSql;

        public TableInfo(TabDetail tabDetail, List<TabColumns> tabColumns, long pkCnt, long idxCnt, long colCnt, String ddlSql, String selectSql) {
            this.tabDetail = tabDetail;
            this.tabColumns = tabColumns;
            this.pkCnt = pkCnt;
            this.idxCnt = idxCnt;
            this.colCnt = colCnt;
            this.ddlSql = ddlSql;
            this.selectSql = selectSql;
        }
    }


    public static String buildSelect(List<TabColumns> columns, TabDetail table) {
        if (columns == null || columns.isEmpty() || table == null) {
            return "-- no columns or table info";
        }

        StringBuilder sb = new StringBuilder("SELECT\n");
        for (int i = 0; i < columns.size(); i++) {
            TabColumns c = columns.get(i);
            sb.append("    ");
            if (i > 0) sb.append(",");
            sb.append(quote(c.getColName()))
                    .append(" -- ")
                    .append(nullToEmpty(c.getColDesc()))
                    .append('\n');
        }
        sb.append("FROM ")
                .append(quote(table.getDbName()))
                .append('.')
                .append(quote(table.getTableName()))
                .append(" -- ")
                .append(nullToEmpty(table.getTableDesc()))
                .append("\n;");
        return sb.toString();
    }

    /* ---------- 生成 CREATE TABLE ---------- */
    public static String buildDDL(List<TabColumns> columns, TabDetail table) {
        if (columns == null || columns.isEmpty() || table == null) {
            return "-- no columns or table info";
        }

        StringBuilder sb = new StringBuilder("CREATE TABLE ")
                .append(quote(table.getDbName()))
                .append('.')
                .append(quote(table.getTableName()))
                .append(" (\n");

        for (TabColumns c : columns) {
            sb.append("    ")
                    .append(leftPad(quote(c.getColName()), 20)) // 左对齐宽度可自行调整
                    .append(leftPad(mapType(c.getColType()), 18))
                    .append(" NOT NULL");

            String comment = nullToEmpty(c.getColDesc());
            if (!comment.isEmpty()) {
                sb.append(" COMMENT '").append(comment).append('\'');
            }
            sb.append(",\n");
        }

        // 主键：约定 id 字段
        boolean hasId = columns.stream().anyMatch(c -> "id".equalsIgnoreCase(c.getColName()));
        if (hasId) {
            sb.append("    PRIMARY KEY (").append(quote("id")).append(")\n");
        } else {
            sb.setLength(sb.length() - 2); // 去掉最后一个逗号
            sb.append("\n");
        }

        sb.append(")\nCOMMENT='")
                .append(nullToEmpty(table.getTableDesc()))
                .append("';\n");
        return sb.toString();
    }

    /* ---------- 工具 ---------- */
    private static String nullToEmpty(String s) {
        return s == null ? "" : s;
    }

    private static String quote(String name) {
        return "`" + name + "`";
    }

    private static String leftPad(String val, int len) {
        return String.format("%-" + len + "s", val);
    }

    /**
     * 简单类型映射，可根据需要扩充
     **/
    private static String mapType(String colType) {
        if (colType == null) return "varchar(50)";
        return colType.trim();
    }


    /**
     * 获取数据配置表
     *
     * @return
     */
    @GetMapping("/getDbList")
    public ApiResult<List<SelectOption>> getDbList() {
        try {
            List<DbConfig> configs = configService.list();
            List<SelectOption> selectOptions = configs.stream()
                    .map(config -> new SelectOption(
                            config.getDbId(),    // 下拉框value（绑定值）
                            config.getDbName(),  // 下拉框label（显示文本）
                            false                 // 【关键注释】说明禁用原因（如：所有数据库默认禁用选择，需申请后启用）
                    ))
                    .collect(Collectors.toList());
            return ApiResult.success(selectOptions);
        } catch (Exception e) {
            return ApiResult.error(401, e.getMessage());
        }
    }

    /**
     * 获取数据字段表
     *
     * @return
     */
    @GetMapping("/getTableList")
    public ApiResult<List<SyncTablesView>> getTableList(String dbId, String targetTable) {
        try {
            List<SyncTablesView> tablesViews = tablesViewService.list(
                    new QueryWrapper<SyncTablesView>()
                            .eq(StringUtils.hasText(dbId), "db_id", dbId)
                            .and(StringUtils.hasText(targetTable),
                                    w -> w.like("target_table", targetTable)
                                            .or()
                                            .like("task_name", targetTable))
                            .orderByDesc("create_time")
            );
            return ApiResult.success(tablesViews);
        } catch (Exception e) {
            return ApiResult.error(401, e.getMessage());
        }
    }

    /**
     * 获取数据表属性
     *
     * @param taskId
     * @return
     */
    @GetMapping("/Sys/data/getTableDetail")
    public ApiResult<TableInfo> getTableDetail(String taskId) {
        // 获取基础数据
        SyncTask task = syncTaskService.getById(taskId);
        DbConfig dbConfig = configService.getById(task.getSourceDbId());
        JdbcTemplate jdbcTemplate;
        try {
            // 统一创建JdbcTemplate，避免重复初始化
            jdbcTemplate = DataSourceConfig.createJdbcTemplate(dbConfig);
        } catch (Exception e) {
            return ApiResult.error(401, e.getMessage());
        }

        // 查询表基本信息
        List<TabDetail> data;
        try {
            String tableQuerySql = "SELECT CREATE_TIME,TABLE_NAME,TABLE_COMMENT,MAX_DATA_LENGTH,UPDATE_TIME " +
                    "FROM information_schema.TABLES " +
                    "WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
            data = jdbcTemplate.query(
                    tableQuerySql,
                    (rs, rowNum) -> new TabDetail(
                            rs.getString("CREATE_TIME"),
                            dbConfig.getDbName(),
                            rs.getString("TABLE_NAME"),
                            rs.getString("TABLE_COMMENT"),
                            rs.getString("MAX_DATA_LENGTH"),
                            rs.getString("UPDATE_TIME")
                    ),
                    dbConfig.getDbName(),
                    task.getSourceTable()
            );
            data.sort(Comparator.comparing(TabDetail::getTableName));
        } catch (Exception e) {

            return ApiResult.error(401, e.getMessage());
        }
        // 查询表列信息
        List<TabColumns> columns;
        try {
            String columnQuerySql = "SELECT TABLE_NAME,ORDINAL_POSITION,COLUMN_NAME,COLUMN_TYPE,COLUMN_COMMENT,COLUMN_KEY " +
                    "FROM information_schema.COLUMNS " +
                    "WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
            columns = jdbcTemplate.query(
                    columnQuerySql,
                    (rs, rowNum) -> new TabColumns(
                            rs.getString("TABLE_NAME"),
                            rs.getInt("ORDINAL_POSITION"),
                            rs.getString("COLUMN_NAME"),
                            rs.getString("COLUMN_TYPE"),
                            rs.getString("COLUMN_COMMENT"),
                            rs.getString("COLUMN_KEY")
                    ),
                    dbConfig.getDbName(),
                    task.getSourceTable()
            );
            columns.sort(Comparator.comparing(TabColumns::getSort));
        } catch (Exception e) {
            return ApiResult.error(401, e.getMessage());
        }

        // 计算表结构统计信息
        long primaryKeyCnt = columns.stream()
                .filter(c -> "PRI".equalsIgnoreCase(c.getKeyType()))
                .count();
        long indexCnt = columns.stream()
                .filter(c -> !"PRI".equalsIgnoreCase(c.getKeyType()) && !c.getKeyType().isBlank())
                .count() + 1;  // 包含主键索引
        long colCnt = columns.size();

        // 构建返回结果
        TableInfo tableInfo = new TableInfo(
                data.get(0),
                columns,
                primaryKeyCnt,
                indexCnt,
                colCnt,
                buildDDL(columns, data.get(0)),
                buildSelect(columns, data.get(0))
        );
        return ApiResult.success(tableInfo);
    }


}
