package com.newvideo.service.imp;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.springframework.orm.hibernate4.HibernateCallback;
import org.springframework.stereotype.Service;

import com.newvideo.dao.VideoInfoDao;
import com.newvideo.domain.VideoDetailInfo;
import com.newvideo.service.imp.juhe.IqiyiService;
import com.newvideo.util.BeanUtil;
import com.newvideo.util.StringUtil;
import com.newvideo.util.TimeUtil;

import net.sf.json.JSONObject;

//清除数据库中的死数据
@Service
public class ClearService {
	@Resource
	private VideoInfoDao videoInfoDao;

	@SuppressWarnings("rawtypes")
	public void clearVideos(long[] ls, String date) {
		final List<JSONObject> list = new ArrayList<JSONObject>();

		for (long id : ls) {
			JSONObject json = new JSONObject();
			json.put("videotype", id);
			json.put("time", TimeUtil.convertGernalTime(date, "yyyy-MM-dd"));
			list.add(json);
		}

		videoInfoDao.excute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				for (JSONObject item : list) {
					List list = session
							.createSQLQuery(String.format(
									"SELECT cv.`videoid` FROM wk_category_video cv LEFT JOIN wk_video_video v ON v.`id`=cv.`videoid` WHERE cv.`videotypeid`=%s  AND v.`createtime`<%s",
									item.getLong("videotype"), item.getLong("time")))
							.setFirstResult(0).setMaxResults(1000).list();
					session.getTransaction().begin();
					if (list != null) {
						int pagecount = 50;
						int pages = list.size() % pagecount == 0 ? list.size() / pagecount
								: list.size() / pagecount + 1;
						for (int i = 0; i < pages; i++) {
							String where = "";
							for (int p = 0; p < pagecount; p++) {
								int pos = i * pagecount + p;
								pos = pos >= list.size() ? list.size() - 1 : pos;
								where += String.format("id=%s or ", list.get(pos) + "");
							}
							if (where.endsWith("or "))
								where = where.substring(0, where.length() - 3);

							session.createSQLQuery("delete from wk_video_video where " + where).executeUpdate();
						}
					}
					session.flush();
					session.getTransaction().commit();
				}
				return null;
			}
		});

	}

	// 清楚依赖VideoInfo的数据
	@SuppressWarnings("rawtypes")
	public void clearDependVideo() {
		String[] tables = new String[] { "wk_category_video", "wk_resource_video", "wk_video_attention",
				"wk_video_banquan_video", "wk_video_banquan_webvideo", "wk_video_collection", "wk_video_comment",
				"wk_video_comment2", "wk_video_getscore_collect", "wk_video_homead", "wk_video_homevideo",
				"wk_video_hotstar_video", "wk_video_iqiyi", "wk_video_letv", "wk_video_play_statistics",
				"wk_video_pptv", "wk_video_sohu", "wk_video_special_video", "wk_video_youku" };

		for (String table : tables) {
			final String sql = String.format(
					"SELECT vv.id FROM `%s` vv LEFT JOIN wk_video_video v ON v.`id`=vv.`videoid` WHERE v.`id` IS NULL",
					table);
			Object obj = videoInfoDao.excute(new HibernateCallback() {
				public Object doInHibernate(Session session) throws HibernateException {
					return session.createSQLQuery(sql).setFirstResult(0).setMaxResults(1000).list();
				}
			});

			final List list = (List) obj;

			if (list != null && list.size() > 0) {
				String idsStr = "";
				for (int i = 0; i < list.size(); i++) {
					idsStr += " or id=" + list.get(i);
				}
				if (idsStr.startsWith(" or"))
					idsStr = idsStr.substring(3);
				final String tempTable = table;
				final String tempIds = idsStr;
				try {
					videoInfoDao.excute(new HibernateCallback() {
						public Object doInHibernate(Session session) throws HibernateException {
							session.getTransaction().begin();
							if (!StringUtil.isNullOrEmpty(tempIds.trim())) {
								session.createSQLQuery(
										String.format("delete from %s where %s", tempTable, tempIds + ""))
										.executeUpdate();
							}
							session.flush();
							session.getTransaction().commit();
							return null;
						}
					});
				} catch (Exception e) {

				}

			}

		}
	}

	/**
	 * 清除无效的爱奇艺视频
	 */
	@SuppressWarnings("unchecked")
	public void clearInvalidIqiyiVideo() {
		IqiyiService iqiyiService = BeanUtil.getBean(IqiyiService.class);

		// 查询出
		// SELECT DISTINCT(i.videoid) FROM `wk_video_iqiyi` i LEFT JOIN
		// wk_video_video v ON v.id=i.videoid WHERE v.show=1;
		// 查询出所有的iqiyi视频

		for (int i = 1; i < 100; i++) {
			final int p = i;
			List<BigInteger> videoList = (List<BigInteger>) videoInfoDao
					.excute(new HibernateCallback<List<BigInteger>>() {

						@Override
						public List<BigInteger> doInHibernate(Session session) throws HibernateException {
							return session
									.createSQLQuery(
											"SELECT  DISTINCT(i.videoid) FROM `wk_video_iqiyi` i LEFT JOIN wk_video_video v ON v.id=i.videoid WHERE v.show=1")
									.setFirstResult(p * 10000).setMaxResults(10000).list();
						}
					});
			for (BigInteger videoId : videoList) {
				List<VideoDetailInfo> list = iqiyiService.getVideoDetailList(videoId.toString());
				if (list == null) {
					iqiyiService.deleteIqiyiVideoByVideoId(videoId.toString());
				}
			}
		}
	}
}