<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class SplitTestStat extends Model
{
    use HasFactory;
  protected $fillable = ['split_test_id', 'split_test_name', 'winning_criteria', 'decision_percentage', 'action', 'action_data',
    'schedule_by', 'total', 'scheduled', 'sent', 'sending_speed', 'threads', 'sending_speed', 'scheduled_detail', 'start_datetime',
    'app_id', 'user_id', 'opens_clicks', 'decision_datetime'];

  /**
   * Retrun query assoicate with APP-ID
  */
  public function scopeApp($query)
  {
    return $query->where('app_id', \Auth::user()->app_id);
  }

  /**
   * Return query that helps to find unique data like lists, boradcasts, and sending_serers for schedule
   * @param int split_test_stat_id
   * @param strin list, broadcast, sending_server
  */
  public static function statLogData($split_test_stat_id, $value='list')
  {
    $query = SplitTestStat::join('split_test_stat_logs', 'split_test_stats.id', '=', 'split_test_stat_logs.split_test_stat_id')
      ->where('split_test_stats.id', $split_test_stat_id)
      ->where('split_test_stats.app_id', \Auth::user()->app_id)
      ->select("split_test_stat_logs.{$value}")
      ->groupBy("split_test_stat_logs.{$value}")
      ->select("split_test_stat_logs.{$value}", \DB::raw('count(*) as total'));
    return $query;
  }

  /**
   * Return query that helps to find the unique opens for winning campaign
   * @param int split_test_stat_id
  */
  public static function statLogOpensWinningDecision($split_test_stat_id)
  {
    $query = SplitTestStat::join('split_test_stat_logs', 'split_test_stats.id', '=', 'split_test_stat_logs.split_test_stat_id')
      ->join('split_test_stat_log_opens', 'split_test_stat_logs.id', '=', 'split_test_stat_log_opens.split_test_stat_log_id')
      ->where('split_test_stats.id', $split_test_stat_id);

      $query = $query->select('split_test_stat_logs.broadcast', \DB::raw('count(*) as maxi'))
        ->groupBy('split_test_stat_logs.broadcast');

    return $query;
  }

  /**
   * Return query that helps to find the unique opens for winning campaign
   * @param int split_test_stat_id
  */
  public static function statLogClicksWinningDecision($split_test_stat_id)
  {
    $query = SplitTestStat::join('split_test_stat_logs', 'split_test_stats.id', '=', 'split_test_stat_logs.split_test_stat_id')
      ->join('split_test_stat_log_clicks', 'split_test_stat_logs.id', '=', 'split_test_stat_log_clicks.split_test_stat_log_id')
      ->where('split_test_stats.id', $split_test_stat_id);

      $query = $query->select('split_test_stat_logs.broadcast', \DB::raw('count(*) as maxi'))
        ->groupBy('split_test_stat_logs.broadcast');

    return $query;
  }

  /**
   * Return query that helps to find opens for a scheduled
   * @param int split_test_stat_id
   * @param string all / unique
  */
  public static function statLogOpens($split_test_stat_id, $filter='all')
  {
    $query = SplitTestStat::join('split_test_stat_logs', 'split_test_stats.id', '=', 'split_test_stat_logs.split_test_stat_id')
      ->join('split_test_stat_log_opens', 'split_test_stat_logs.id', '=', 'split_test_stat_log_opens.split_test_stat_log_id')
      ->where('split_test_stats.id', $split_test_stat_id);

    if($filter == 'unique') {
      $query = $query->select('split_test_stat_log_opens.split_test_stat_log_id')
        ->groupBy('split_test_stat_log_opens.split_test_stat_log_id');
    } else {
      $query = $query->select('split_test_stats.id as id', 'split_test_stats.split_test_name as name', 'split_test_stat_logs.id as stat_log_id', 'split_test_stat_logs.message_id', 'split_test_stat_logs.email', 'split_test_stat_logs.list', 'split_test_stat_logs.broadcast', 'split_test_stat_logs.sending_server', 'split_test_stat_logs.status', 'split_test_stat_log_opens.*');
    }
    return $query;
  }

  /**
   * Return query that helps to find clicks for a scheduled
   * @param int split_test_stat_id
   * @param string all / unique
  */
  public static function statLogClicks($split_test_stat_id, $filter='all')
  {
    $query = SplitTestStat::join('split_test_stat_logs', 'split_test_stats.id', '=', 'split_test_stat_logs.split_test_stat_id')
      ->join('split_test_stat_log_clicks', 'split_test_stat_logs.id', '=', 'split_test_stat_log_clicks.split_test_stat_log_id')
      ->where('split_test_stats.id', $split_test_stat_id);

    if($filter == 'unique') {
      $query = $query->select('split_test_stat_log_clicks.split_test_stat_log_id')
        ->groupBy('split_test_stat_log_clicks.split_test_stat_log_id');
    } else {
      $query = $query->select('split_test_stats.id as id', 'split_test_stats.split_test_name as name', 'split_test_stat_logs.id as stat_log_id', 'split_test_stat_logs.message_id', 'split_test_stat_logs.email', 'split_test_stat_logs.list', 'split_test_stat_logs.broadcast', 'split_test_stat_logs.sending_server', 'split_test_stat_logs.status', 'split_test_stat_log_clicks.*');
    }
    return $query;
  }

  /**
   * Return query that helps to find bounces for a scheduled
   * @param int split_test_stat_id
  */
  public static function statLogBounces($scheduel_campaign_stat_id)
  {
    $query = SplitTestStat::join('split_test_stat_logs', 'split_test_stats.id', '=', 'split_test_stat_logs.split_test_stat_id')
      ->join('global_bounces', 'split_test_stat_logs.id', '=', 'global_bounces.schedule_campaign_stat_log_id')
      ->where('global_bounces.stat_id', $scheduel_campaign_stat_id)
      ->where('global_bounces.section', 'SplitTest')
      ->where('split_test_stats.id', $scheduel_campaign_stat_id)
      ->where('split_test_stats.app_id', \Auth::user()->app_id)
      ->select('split_test_stats.id as id', 'split_test_stats.split_test_name as name', 'split_test_stat_logs.id as stat_log_id', 'split_test_stat_logs.message_id', 'split_test_stat_logs.email', 'split_test_stat_logs.list', 'split_test_stat_logs.broadcast', 'split_test_stat_logs.sending_server', 'split_test_stat_logs.status', 'global_bounces.*');
    return $query;
  }

  /**
   * Return query that helps to find spam for a scheduled
   * @param int split_test_stat_id
  */
  public static function statLogSpam($scheduel_campaign_stat_id)
  {
    $query = SplitTestStat::join('split_test_stat_logs', 'split_test_stats.id', '=', 'split_test_stat_logs.split_test_stat_id')
      ->join('global_spams', 'split_test_stat_logs.id', '=', 'global_spams.schedule_campaign_stat_log_id')
      ->where('split_test_stats.id', $scheduel_campaign_stat_id)
      ->where('split_test_stats.app_id', \Auth::user()->app_id)
      ->where('global_spams.section', 'SplitTest')
      ->select('split_test_stats.id as id', 'split_test_stats.split_test_name as name', 'split_test_stat_logs.id as stat_log_id', 'split_test_stat_logs.message_id', 'split_test_stat_logs.email', 'split_test_stat_logs.list', 'split_test_stat_logs.broadcast', 'split_test_stat_logs.sending_server', 'split_test_stat_logs.status', 'global_spams.*');;
    return $query;
  }

  /**
   * Return query that helps to find replies for a scheduled
   * @param int split_test_stat_id
  */
  public static function statLogReplies($scheduel_campaign_stat_id)
  {
    $query = SplitTestStat::join('split_test_stat_logs', 'split_test_stats.id', '=', 'split_test_stat_logs.split_test_stat_id')
      ->join('replies', 'split_test_stat_logs.id', '=', 'replies.schedule_campaign_stat_log_id')
      ->where('split_test_stats.id', $scheduel_campaign_stat_id)
      ->where('split_test_stats.app_id', \Auth::user()->app_id)
      ->where('replies.section', 'SplitTest')
      ->select('split_test_stats.id as id', 'split_test_stats.split_test_name as name', 'split_test_stat_logs.id as stat_log_id', 'split_test_stat_logs.message_id', 'split_test_stat_logs.email', 'split_test_stat_logs.list', 'split_test_stat_logs.broadcast', 'split_test_stat_logs.sending_server', 'split_test_stat_logs.status', 'replies.*');;
    return $query;
  }

  /**
   * Return query that helps to find unique data like lists, boradcasts, and sending_serers for schedule
   * @param int split_test_stat_id
   * @param strin list, broadcast, sending_server
  */
  public static function getUniqueCountries($split_test_stat_id=null, $table='opens')
  {
    $table = $table == 'opens' ? 'split_test_stat_log_opens' : 'split_test_stat_log_clicks';

    $query = SplitTestStat::join('split_test_stat_logs', 'split_test_stats.id', '=', 'split_test_stat_logs.split_test_stat_id')
      ->join($table, 'split_test_stat_logs.id', '=', "$table.split_test_stat_log_id");
      
    if(!empty($split_test_stat_id)) {
      $query = $query->where('split_test_stats.id', $split_test_stat_id);
    }

    $query = $query->select(\DB::raw('count(country_code) as cnt, country_code'))
      ->groupBy("$table.country_code");

    return $query;
  }
}
