package com.songshu.mongo.service.impl;

import com.songshu.mongo.model.dto.BookDTO;
import com.songshu.mongo.model.dto.Room;
import com.songshu.mongo.service.BookMongoService;
import com.songshu.mongo.tools.JDBCUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.data.mongodb.core.query.Update;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.List;
import java.util.regex.Pattern;

/**
 * cw
 * 案例
 */
@Service
public class BookMongoServiceImpl implements BookMongoService {

    @Autowired
    private MongoTemplate mongoTemplate;

    /**
     * 新增
     * @param bookDTO
     * @return
     */
    @Override
    public Boolean inserBookInfo(BookDTO bookDTO) {
        mongoTemplate.insert(bookDTO);
        return true;
    }

    /**
     * 批量新增
     * @param bookDTOList
     * @return
     */
    @Override
    public Boolean inserBatchBookInfo(List<BookDTO> bookDTOList) {
        mongoTemplate.insert(bookDTOList,BookDTO.class);
        return  true;
    }

    /**
     * 修改
     * @param query
     * @param update
     * @return
     */
    @Override
    public Boolean updateBookInfo(Query query, Update update) {
        mongoTemplate.updateMulti(query,update,BookDTO.class);
        return true;
    }

    /**
     * 查所有
     * @return
     */
    @Override
    public  List<BookDTO> findAllBookInfo() {
        List<BookDTO> bookDTOList = mongoTemplate.findAll(BookDTO.class);
        return bookDTOList;
    }

    /**
     * 根据书名查询: 精准
     * @param name
     * @return
     */
    @Override
    public List<BookDTO> findBookInfoByName(String name) {
        Query query = Query.query(Criteria.where("name").is(name));
        return mongoTemplate.find(query,BookDTO.class);
    }

    /**
     * 根据书名查询: 模糊
     * @param name
     * @return
     */
    @Override
    public List<BookDTO> findBookInfoReName(String name) {
        // ^.*表示字符前面、*$表示字符后面
        Pattern compile = Pattern.compile("^.*" + name + ".*$", Pattern.CASE_INSENSITIVE);
        Query query= Query.query(Criteria.where("name").regex(compile));
        return mongoTemplate.find(query,BookDTO.class);
    }

    /**
     * 根据阅读量：范围查询并排序
     * @param minReadedNum
     * @param maxReadedNum
     * @return
     */
    @Override
    public List<BookDTO> findBookInfoReRange(Double minReadedNum, Double maxReadedNum) {
        // query两种方式
        Query query = Query.query(Criteria.where("readedNum").gte(minReadedNum).lte(maxReadedNum))
                .with(new Sort(new Sort.Order(Sort.Direction.ASC,"readedNum")));
        return  mongoTemplate.find(query,BookDTO.class);
    }

    /**
     * 排序-分页查询
     * @param minReadedNum
     * @param maxReadedNum
     * @param pageNum
     * @param pageSize
     * @return
     */
    @Override
    public List<BookDTO> findBookInfoPage(Double minReadedNum, Double maxReadedNum, Integer pageNum, Integer pageSize) {
        Query query = Query.query(Criteria.where("readedNum").gte(minReadedNum).lte(maxReadedNum))
                .with(new Sort(new Sort.Order(Sort.Direction.ASC,"readedNum")))
                .with(new PageRequest(pageNum-1,pageSize));
        return mongoTemplate.find(query, BookDTO.class);
    }

    @Override
    public List<Room> findAllBookInfo3() {
        List<Room> all = mongoTemplate.findAll(Room.class);
        long start = System.currentTimeMillis();
        try {
            Connection conn = JDBCUtil.getConnection();
            Statement stat = JDBCUtil.getStatement();
            JDBCUtil.executeUpdate("DELETE FROM room_company_ids");
            JDBCUtil.executeUpdate("DELETE FROM room");
            String sql = "insert into room(id, _id, building, name, area, rentalstatus, space, owner, created, modified, created_by, modified_by, company_id) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            PreparedStatement ps = null;
            ps = conn.prepareStatement(sql);
            conn.setAutoCommit(false);//取消自动提交
            int size = all.size();
            int group = 500;
            for (int i = 1; i < size; i++) {
                Room room = all.get(i);
                ps.setObject(1, i);
                ps.setObject(2,room.get_id() );
                ps.setObject(3, room.getBuilding());
                ps.setObject(4, room.getName());
                ps.setObject(5, room.getArea());
                ps.setObject(6, room.getRentalstatus());
                ps.setObject(7, room.getSpace());
                ps.setObject(8, room.getOwner());
                ps.setTimestamp(9, new Timestamp(room.getCreated().getTime()));
                ps.setTimestamp(10, new Timestamp(room.getModified().getTime()));
                ps.setObject(11, null);
                ps.setObject(12, null);
                ps.setObject(13, null);
                ps.addBatch();          // 将sql语句打包到一个容器中
                if (i % group == 0 || i == size-1) {
                    ps.executeBatch();          // 将容器中的sql语句提交
                    ps.clearBatch();            // 清空容器，为下一次打包做准备
                }
            }
            ps.executeBatch();
            ps.clearBatch();
            conn.commit();//所有语句都执行完毕后才手动提交sql语句
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.getClose();
        }
        return all;
    }

}