package com.reyun.repository;

import com.reyun.model.SubCampaign;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigInteger;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

/**
 * Created by sunhao on 17/5/22.
 * desc:子活动DAO
 */
public interface SubCampaignRepository extends JpaRepository<SubCampaign,Long>{

    @Query(value = "select count(*) from sub_campaign where campaign_id = ?1 and del_flag is false ",nativeQuery = true)
    BigInteger countSubCampaignByCampaign(Long campaignId);

    @Query(value = "select count(*) from sub_campaign where id in ?1 and del_flag is false ", nativeQuery = true)
    BigInteger countSubCampaignById(List<Long> ids);

    @Query(value = "select * from sub_campaign where campaign_id = ?1 ", nativeQuery = true)
    List<SubCampaign> listAllSubCampaignByCampaign(Long campaignId);

    @Query(value = "select * from sub_campaign where campaign_id = ?1 and del_flag is false ", nativeQuery = true)
    List<SubCampaign> listSubCampaignByCampaign(Long campaignId);
    @Query(value = "select * from sub_campaign where campaign_id in ?1 and del_flag is false ", nativeQuery = true)
    List<SubCampaign> listSubCampaignInCampaign(List<Long> campids);

    @Query(value = "select * from sub_campaign where campaign_id = ?1 and del_flag is true ", nativeQuery = true)
    List<SubCampaign> listDisableByCampaign(Long campaignId);

    @Query(value = "select * from sub_campaign where campaign_id = ?1 and keyword = ?2 and creative = ?3 and del_flag is false limit 1 ", nativeQuery = true)
    SubCampaign findSubCampaignByKeyword(Long campaignId, String keyword, String creative);

    @Query(value = "select * from sub_campaign where campaign_id = ?1 and keyword = ?2 and (creative is null or creative = '') and del_flag is false limit 1 ", nativeQuery = true)
    SubCampaign findSubCampaignByKeyword(Long campaignId, String keyword);

    @Query(value = "select * from sub_campaign c where c.app = ?1 and del_flag is not true", nativeQuery = true)
    List<SubCampaign> findAllByApp(Long app);

    @Query(value = "select * from sub_campaign where app=?1 and campaign_id=?2", nativeQuery = true)
    List<SubCampaign> findByAppAndCampaignId(Long app,Long campaignId);

    @Query(value = "select * from sub_campaign c where c.app = ?1 and url = ?2 and del_flag is not true", nativeQuery = true)
    List<SubCampaign> findAllByAppAndUrl(Long app, String url);

    @Transactional
    @Modifying
    @Query(value = "update sub_campaign set url = ?2, modify_account = ?3, modify_time = ?4  where id = ?1 ", nativeQuery = true)
    int updateSubCampaignUrl(Long subCampaignId, String url, Long accountId, Date modifyDate);

    @Transactional
    @Modifying
    @Query(value = "update sub_campaign set del_flag = true, modify_account = ?2, modify_time = ?3 where id in ?1 and del_flag is false ", nativeQuery = true)
    int deleteSubCampaign(List<Long> subCampaignIdList, Long modifyAccount, Date modifyDate);

    @Transactional
    @Modifying
    @Query(value = "update sub_campaign set del_flag = false, modify_account = ?2, modify_time = ?3 where id in ?1 and del_flag is true ", nativeQuery = true)
    int enableSubCampaign(List<Long> subCampaignIdList, Long modifyAccount, Date modifyDate);

    @Transactional
    @Modifying
    @Query(value = "update sub_campaign c set c.url=?2 where c.app = ?1", nativeQuery = true)
    int updateDownloadUrl(Long app, String url);

    @Query(value = "SELECT campaign_id,COUNT(*) AS COUNT FROM sub_campaign WHERE campaign_id IN ?1 AND del_flag IS FALSE GROUP BY campaign_id",nativeQuery = true)
    List<Object[]> countSubCampaignByCampaignByIds(List<Long> ids);

    @Transactional
    @Modifying
    @Query(value = "update sub_campaign set url_name = ?2 where id = ?1",nativeQuery = true)
    int modifyUrlName(Long campaignId, String name);

    @Transactional
    @Modifying
    @Query(value = "update sub_campaign set url = ?2,url_name=?3 where id in ?1",nativeQuery = true)
    int updataUlrWithIds(List<Long> campids, String new_url, String urlName);


    @Transactional
    @Modifying
    @Query(value = "update sub_campaign set url_name=?2 where app = ?3 and  url = ?1",nativeQuery = true)
    int updateUrlName(String new_url, String urlName, Long appid);
}