/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package com.youchain.rest;

import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.youchain.annotation.AnonymousAccess;
import com.youchain.config.FileProperties;
import com.youchain.domain.ExcelConfig;
import com.youchain.domain.ExcelConfigKey;
import com.youchain.exception.BadRequestException;
import com.youchain.exception.handler.ApiError;
import com.youchain.exception.handler.ApiResult;
import com.youchain.repository.ExcelConfigKeyRepository;
import com.youchain.repository.ExcelConfigRepository;
import com.youchain.service.ExcelConfigService;
import com.youchain.service.dto.ExcelConfigDto;
import com.youchain.service.dto.ExcelConfigQueryCriteria;
import com.youchain.utils.FileUtil;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.data.domain.Pageable;
import lombok.RequiredArgsConstructor;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;
import io.swagger.annotations.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import javax.persistence.*;
import javax.servlet.http.HttpServletResponse;

/**
* @website https://eladmin.vip
* @author baobl
* @date 2024-09-14
**/
@RestController
@RequiredArgsConstructor
@Api(tags = "excel_config管理")
@RequestMapping("/api/excelConfig")
public class ExcelConfigController {
    private final FileProperties properties;
    private final ExcelConfigService excelConfigService;
    private final ExcelConfigKeyRepository excelConfigKeyRepository;
    @PersistenceContext
    private EntityManager entityManager;
    private final ExcelConfigRepository excelConfigRepository;
    @ApiOperation("导出数据")
    @GetMapping(value = "/download")
    @PreAuthorize("@el.check('excelConfig:list')")
    public void exportExcelConfig(HttpServletResponse response, ExcelConfigQueryCriteria criteria) throws Exception {
        excelConfigService.download(excelConfigService.queryAll(criteria), response);
    }

    @GetMapping
    @ApiOperation("查询excel_config")
    @PreAuthorize("@el.check('excelConfig:list')")
    public ResponseEntity<Object> queryExcelConfig(ExcelConfigQueryCriteria criteria, Pageable pageable){
        return new ResponseEntity<>(excelConfigService.queryAll(criteria,pageable),HttpStatus.OK);
    }

    @PostMapping
    @ApiOperation("新增excel_config")
    @PreAuthorize("@el.check('excelConfig:add')")
    public ResponseEntity<Object> createExcelConfig(@Validated @RequestBody ExcelConfigDto resources){
        return new ResponseEntity<>(excelConfigService.create(resources),HttpStatus.CREATED);
    }

    @PutMapping
    @ApiOperation("修改excel_config")
    @PreAuthorize("@el.check('excelConfig:edit')")
    public ResponseEntity<Object> updateExcelConfig(@Validated @RequestBody ExcelConfigDto resources){
        excelConfigService.create(resources);
        return new ResponseEntity<>(HttpStatus.NO_CONTENT);
    }

    @DeleteMapping
    @ApiOperation("删除excel_config")
    @PreAuthorize("@el.check('excelConfig:del')")
    public ResponseEntity<Object> deleteExcelConfig(@RequestBody Long[] ids) {
        excelConfigService.deleteAll(ids);
        return new ResponseEntity<>(HttpStatus.OK);
    }
    @PostMapping(value = "/import")
    @ApiOperation("导入点位")
    @AnonymousAccess
    @Transactional
    public ResponseEntity<Object> importPoint(@RequestParam("file") MultipartFile multipartFile,@RequestParam("tableName") String tableNames) {
        /*通过权限匹配对应的数据库表*/
        String tableName = getTableName(tableNames);
        FileUtil.checkSize(properties.getMaxSize(), multipartFile.getSize());
        String suffix = FileUtil.getExtensionName(multipartFile.getOriginalFilename());
        String type = FileUtil.getFileType(suffix);

        File file = FileUtil.upload(multipartFile, properties.getPath().getPath() + type + File.separator);

//        Dept dept = UserUtils.getDept();
        // 编码、描述、库区、存储类型
        ExcelReader reader = ExcelUtil.getReader(file);
        int edit_len = 0;
        int new_len = 0;
        int i = 0;
        StringBuffer sqlBf=new StringBuffer();
        /* 获取导入模板*/
        List<ExcelConfig> excelConfigList=excelConfigRepository.findByTableName(tableName);
        List<Map<String, Object>> readAll = reader.readAll();
        for (i = 0; i < readAll.size(); i++) {
            ExcelConfig editEI=null;//0 新增 1 修改
            StringBuffer columns=new StringBuffer();/* 字段集*/
            StringBuffer values=new StringBuffer();/* 数据集*/
            StringBuffer editValues=new StringBuffer();/* 修改*/

            Map<String, Object> readMap=readAll.get(i);
            for (int j = 0; j < excelConfigList.size(); j++) {
                ExcelConfig EI=excelConfigList.get(j);
                /* 唯一判定*/
                if (EI.getOnlyFlag()==true){
                    if (readMap.get(EI.getExcelColumnName())==null){
                        throw new BadRequestException(EI.getExcelColumnName()+"本字段不能为空");
                    }else {
                        Object value=readMap.get(EI.getExcelColumnName());

                        String hql ="select * from "+tableName+" where "+EI.getTableColumnName()+"="+ EI.getFormat(value,EI.getTableColumnType());
//                        System.out.println(hql);
                        Query query = entityManager.createNativeQuery(hql);
                        List<T> qL= query.getResultList();
                        if (qL.size()<1){
                            //新增
                        }else {
                            //修改
                            editEI=EI;
                        }
                    }
                }

                if (readMap.get(EI.getExcelColumnName())!=null||(EI.getDefaultValue()!=null&&EI.getDefaultValue().length()>0)){
                    /* 获取导入值*/
                    Object value;
                    if (readMap.get(EI.getExcelColumnName())!=null){
                        value = readMap.get(EI.getExcelColumnName());
                    }else {
                        value=EI.getDefaultValue();
                    }
                    /*添加到sql字段集*/
                    if (EI.getGlbType()==1){
                        /* 外键关联*/
                        ExcelConfigKey excelConfigKey=EI.getEcKey();
                        if (excelConfigKey!=null){
                            /*获取外键id*/
                            String hql ="select "+excelConfigKey.getKeyName()+" from "+excelConfigKey.getTableName()+" where "+excelConfigKey.getTableColumnName()+"="+ EI.getFormat(value,excelConfigKey.getTableColumnType());
//                                System.out.println(hql);
                            Query query = entityManager.createNativeQuery(hql);
                            List<T> qL= query.getResultList();
                            if (qL.size()>0){
                                value=qL.get(0);
                            }else {
                                value=null;
                            }
                        }
                    }else
                    if (EI.getGlbType()==2){
                        /*关联字典*/
                        String hql =" select dd.`value` from sys_dict_detail dd  "
                                +" left join sys_dict d on dd.dict_id=d.dict_id "
                                +" where d.`name`='"+EI.getDictName()+"' and dd.label='"+value+"' ";
                        Query query = entityManager.createNativeQuery(hql);
                        List<T> qL= query.getResultList();
                        if (qL.size()<1){
                            throw new BadRequestException("未找到字典:"+EI.getExcelColumnName()+","+value);
                        }
                        value=qL.get(0);
                    }
                    if (columns.toString().length() < 1) {
                        columns.append(EI.getTableColumnName());
                    } else {
                        columns.append("," + EI.getTableColumnName());
                    }
                    if (values.toString().length() < 1) {
                        values.append(EI.getFormat(value,EI.getTableColumnType()));
                    } else {
                        values.append("," + EI.getFormat(value,EI.getTableColumnType()));
                    }
                }
            }
            String sql;
            if (editEI==null){
                sql = "INSERT INTO "+tableName+" ("+columns+") VALUES ("+values+")"+";";
            }else {
                String[] columnArr=columns.toString().split(",");
                String[] valuesArr=values.toString().split(",");
                for (int j = 0; j < columnArr.length; j++) {
                    if (editValues.length()<1){
                        editValues.append(columnArr[j]+"="+valuesArr[j]);
                    }else {
                        editValues.append(","+columnArr[j]+"="+valuesArr[j]);
                    }
                }
                sql = "UPDATE "+tableName+" SET "+editValues+" WHERE "+editEI.getTableColumnName()+"="+editEI.getFormat(readMap.get(editEI.getExcelColumnName()),editEI.getTableColumnType())+";";
            }
            sqlBf.append(sql);
            System.out.println(sql);
            entityManager.createNativeQuery(sql)
                    .executeUpdate();
        }
        /*System.out.println(sqlBf);
         *//* 执行sql语句*//*
        entityManager.createNativeQuery(sqlBf.toString())
                .executeUpdate();*/
        ApiError apiError = ApiError.errorJosn(HttpStatus.OK.value(), "导入成功：" + (i) + "行 新增（" + new_len + ")修改（" + edit_len + ")");
        return new ResponseEntity(apiError, HttpStatus.valueOf(apiError.getStatus()));
    }

    private String getTableName(String tableNames) {
        String tableName="";
        String[] tableNameList= tableNames.split(",");
        StringBuilder tableNameSql = new StringBuilder();
        tableNameSql.append("(");
        for (int i = 0; i < tableNameList.length; i++) {
            tableNameSql.append("'").append(tableNameList[i]).append("'");
            if (i < tableNameList.length - 1) {
                tableNameSql.append(",");
            }
        }
        tableNameSql.append(")");

        String hql =" select dd.`value` from sys_dict_detail dd  "
                +" left join sys_dict d on dd.dict_id=d.dict_id "
                +" where d.`name`='per_table_config' and dd.label in "+tableNameSql+" ";
        Query query = entityManager.createNativeQuery(hql);
        List<T> qL= query.getResultList();
        if (qL.size()<1){
            throw new BadRequestException("未匹配到权限表");
        }else {
            tableName=qL.get(0)+"";
        }
        return tableName;
    }
}