package com.ffii.lioner.modules.lioner.service;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;
import java.util.Optional;

import org.springframework.stereotype.Service;

import com.ffii.lioner.modules.lioner.entity.FileRef;
import com.ffii.lioner.modules.lioner.entity.FileRefRepository;
import com.ffii.lioner.modules.lioner.req.UpdateExtRefReq;
import com.ffii.core.support.AbstractBaseEntityService;
import com.ffii.core.support.JdbcDao;
import com.ffii.core.utils.BeanUtils;

@Service
public class FileRefService extends AbstractBaseEntityService<FileRef, Long, FileRefRepository> {

	public FileRefService(JdbcDao jdbcDao, FileRefRepository repository) {
		super(jdbcDao, repository);
	}

	public Long saveOrUpdate(FileRef obj) {
		FileRef instance;
        if(obj.getId()!= null){
			instance = find(obj.getId()).get();
		}
		else{
			instance = new FileRef();
		}
		BeanUtils.copyProperties(obj,instance);
		instance = save(instance);
	
		return instance.getId();
	}

	public Long saveExternal(UpdateExtRefReq req, String refType) {
		return saveExternal(req, refType, null);
	}

	public Long saveExternal(UpdateExtRefReq req, String refType, LocalDateTime importDate) {
		FileRef instance;
		if(req.getId()>0){
			instance = find(req.getId()).get();
		}
		else{
			instance = new FileRef();
		}

		BeanUtils.copyProperties(req,instance);
		instance.setRefType(refType);
		if(importDate != null) instance.setImportDate(importDate);
		instance = save(instance);
		return instance.getId();
	}

	public List<Map<String, Object>> listExternal(Map<String, Object> args) {
		StringBuilder sql = new StringBuilder("SELECT"
				+ " fr.refType, "
				+ " fr.remarks, "
				+ " fr.id "
				+ " FROM file_ref fr "
				+ " LEFT JOIN file f ON f.id = fr.fileId  "
				+ " WHERE fr.deleted = FALSE "
				+ " AND f.id IS NULL");

		if (args != null) {
			if (args.containsKey("refId"))
				sql.append(" AND fr.refId = :refId ");
			if (args.containsKey("refType"))
				sql.append(" AND fr.refType = :refType");
		}

		sql.append(" ORDER BY fr.modified ");

		return jdbcDao.queryForList(sql.toString(), args);
	}

	public Optional<FileRef> findByFileId(Long fileId) {
		Map<String,Object> args = Map.of("fileId", fileId);
		return jdbcDao.queryForEntity("SELECT * "+
			"FROM file_ref fr where fr.fileId = :fileId "
		, args, FileRef.class);
	}

	public BigDecimal getFileStoredSize(Map<String, Object> args) {
		StringBuilder sql = new StringBuilder("SELECT"
			+ " COALESCE(SUM(f.filesize)/1024/1024,0) AS refSize "
			+ " FROM file_ref fr  "
			+ " JOIN file f ON fr.fileId = f.id  "
			+ " WHERE refId = :refId  "
			+ " AND refType = :refType  "
		);

		return jdbcDao.queryForDecimal(sql.toString(), args);
	}

	public Integer getVideoExist(Map<String, Object> args) {
		StringBuilder sql = new StringBuilder("SELECT"
			+ " COALESCE(COUNT(f.id),0) AS videoCount "
			+ " FROM file_ref fr  "
			+ " JOIN file f ON fr.fileId = f.id  "
			+ " WHERE refId = :refId  "
			+ " AND refType = :refType  "
			+ " AND f.mimetype LIKE \"%video%\" "
		);

		return jdbcDao.queryForInt(sql.toString(), args);
	}

}

