<?php
namespace factory;
class Cron 
{
  public static function remove_points_history()
  {
    global $mdb;
    if ( $results = $mdb -> delete( 'users_points_history', [ 'date[<]' => date( 'Y-m-d H:i:s', strtotime( '-30 days', time() ) ) ] ) )
    {
      return [ 
                'status' => 'ok', 
                'msg' => 'Usuwam historię punktów.'
              ];
    }
    return [ 'status' => 'bad' ];
  }
  
  public static function update_points()
  {
    global $mdb;
    if ( $mdb -> update( 'users', [ 'points[+]' => 100, 'points_update' => date( 'Y-m-d' ) ], [ 'OR' => [ 'points_update[!]' => date( 'Y-m-d' ), 'points_update' => null ] ] ) )
    {
      return [ 
                'status' => 'ok', 
                'msg' => 'Dodaję punkty.'
              ];
    }
    return [ 'status' => 'bad' ];
  }
  
  public static function send_push()
  {
    global $mdb;
    
    $sent_mail = false;
    
    /* powiadomienia natychmiastowe */
    $users = $mdb -> query( 'SELECT '
                                . 'id, email, pushover_api, pushover_user '
                              . 'FROM '
                                . 'users '
                              . 'WHERE '
                                . 'id IN ('
                                  . 'SELECT user_id FROM posters WHERE id IN ( '
                                    . 'SELECT poster_id FROM posters_history WHERE send_pushover = 0 AND pushover_settings = 1 GROUP BY poster_id ) ) '
                                . 'AND '
                                  . 'pushover_api IS NOT NULL '
                                . 'AND '
                                  . 'pushover_user IS NOT NULL' ) -> fetchAll( \PDO::FETCH_ASSOC );
    if ( is_array( $users ) and count( $users ) ) 
    {
      foreach ( $users as $user )
      {
        $posters = $mdb -> query( 'SELECT '
                                      . 'pr.id, url, title, price, comment '
                                    . 'FROM '
                                      . 'posters_history AS pr '
                                      . 'INNER JOIN posters AS p ON p.id = pr.poster_id '
                                    . 'WHERE '
                                        . 'send_pushover = 0 '
                                      . 'AND '
                                        . 'pushover_settings = 1 '
                                      . 'AND '
                                        . 'user_id = ' . $user['id'] . ' LIMIT 1' ) -> fetchAll( \PDO::FETCH_ASSOC );
        if ( is_array( $posters ) and count( $posters ) ) 
        {
          foreach ( $posters as $poster )
          {
            $posters_id[]   = $poster['id'];
            $auctions[]     = $poster;
          }
          
          \factory\Posters::send_push( $user['email'], $auctions, $user['pushover_api'], $user['pushover_user'] );
          $mdb -> query( 'UPDATE posters_history SET send_pushover = 1 WHERE id IN ( ' . implode( ',', $posters_id ) . ')' );
        }
      }

      return [ 
                'status' => 'ok', 
                'msg' => 'Wysyłam powiadomienia push o znalezionych ogłoszeniach.'
              ];
    }
    
    /* powiadomienia zbiorcze co godzinę */
    $users = $mdb -> query( 'SELECT '
                                . 'id, email, pushover_api, pushover_user '
                              . 'FROM '
                                . 'users '
                              . 'WHERE '
                                . 'id IN ('
                                  . 'SELECT user_id FROM posters WHERE id IN ( '
                                    . 'SELECT poster_id FROM posters_history AS pr WHERE send_pushover = 0 AND pushover_settings = 2 AND DATE_FORMAT( pr.date, \'%Y-%m-%d %H\' ) = \'' . date( 'Y-m-d H', strtotime( '-1 hours', time() ) ) . '\' GROUP BY poster_id ) ) '
                                . 'AND '
                                  . 'pushover_api IS NOT NULL '
                                . 'AND '
                                  . 'pushover_user IS NOT NULL' ) -> fetchAll( \PDO::FETCH_ASSOC );
    if ( is_array( $users ) and count( $users ) ) 
    {
      foreach ( $users as $user )
      {
        $posters = $mdb -> query( 'SELECT '
                                      . 'pr.id, url, title, price, comment '
                                    . 'FROM '
                                      . 'posters_history AS pr '
                                      . 'INNER JOIN posters AS p ON p.id = pr.poster_id '
                                    . 'WHERE '
                                        . 'send_pushover = 0 '
                                      . 'AND '
                                        . 'pushover_settings = 2 '
                                      . 'AND '
                                        . 'DATE_FORMAT( pr.date, \'%Y-%m-%d %H\' ) = \'' . date( 'Y-m-d H', strtotime( '-1 hours', time() ) ) . '\' '
                                      . 'AND '
                                        . 'user_id = ' . $user['id'] . ' LIMIT 1' ) -> fetchAll( \PDO::FETCH_ASSOC );
        if ( is_array( $posters ) and count( $posters ) ) 
        {
          foreach ( $posters as $poster )
          {
            $posters_id[]   = $poster['id'];
            $auctions[]     = $poster;
          }
          
          \factory\Posters::send_push( $user['email'], $auctions, $user['pushover_api'], $user['pushover_user'] );
          $mdb -> query( 'UPDATE posters_history SET send_pushover = 1 WHERE id IN ( ' . implode( ',', $posters_id ) . ')' );
        }
      }

      return [ 
                'status' => 'ok', 
                'msg' => 'Wysyłam powiadomienia push o znalezionych ogłoszeniach.'
              ];
    }
    
    if ( date( 'Y-m-d H:i:s' ) > date( 'Y-m-d 08:00:00' ) )
    {
      /* powiadomienia zbiorcze dwa razy dziennie - o godzinie 8 */
      $users = $mdb -> query( 'SELECT '
                                  . 'id, email, pushover_api, pushover_user '
                                . 'FROM '
                                  . 'users '
                                . 'WHERE '
                                  . 'id IN ('
                                    . 'SELECT user_id FROM posters WHERE id IN ( '
                                      . 'SELECT poster_id FROM posters_history AS pr WHERE send_pushover = 0 AND pushover_settings = 3 AND DATE_FORMAT( pr.date, \'%Y-%m-%d %H\' ) < \'' . date( 'Y-m-d 08' ) . '\' GROUP BY poster_id ) ) '
                                  . 'AND '
                                    . 'pushover_api IS NOT NULL '
                                  . 'AND '
                                    . 'pushover_user IS NOT NULL' ) -> fetchAll( \PDO::FETCH_ASSOC );
      if ( is_array( $users ) and count( $users ) ) 
      {
        foreach ( $users as $user )
        {
          $posters = $mdb -> query( 'SELECT '
                                        . 'pr.id, url, title, price, comment '
                                      . 'FROM '
                                        . 'posters_history AS pr '
                                        . 'INNER JOIN posters AS p ON p.id = pr.poster_id '
                                      . 'WHERE '
                                          . 'send_pushover = 0 '
                                        . 'AND '
                                          . 'pushover_settings = 3 '
                                        . 'AND '
                                          . 'DATE_FORMAT( pr.date, \'%Y-%m-%d %H\' ) < \'' . date( 'Y-m-d 08' ) . '\' '
                                        . 'AND '
                                          . 'user_id = ' . $user['id'] . ' LIMIT 1' ) -> fetchAll( \PDO::FETCH_ASSOC );
          if ( is_array( $posters ) and count( $posters ) ) 
          {
            foreach ( $posters as $poster )
            {
              $posters_id[]   = $poster['id'];
              $auctions[]     = $poster;
            }

            \factory\Posters::send_push( $user['email'], $auctions, $user['pushover_api'], $user['pushover_user'] );
            $mdb -> query( 'UPDATE posters_history SET send_pushover = 1 WHERE id IN ( ' . implode( ',', $posters_id ) . ')' );
          }
        }

        return [ 
                  'status' => 'ok', 
                  'msg' => 'Wysyłam powiadomienia push o znalezionych ogłoszeniach.'
                ];
      }
    }
    
    if ( date( 'Y-m-d H:i:s' ) > date( 'Y-m-d 20:00:00' ) )
    {
      /* powiadomienia zbiorcze dwa razy dziennie - o godzinie 20 */
      $users = $mdb -> query( 'SELECT '
                                  . 'id, email, pushover_api, pushover_user '
                                . 'FROM '
                                  . 'users '
                                . 'WHERE '
                                  . 'id IN ('
                                    . 'SELECT user_id FROM posters WHERE id IN ( '
                                      . 'SELECT poster_id FROM posters_history AS pr WHERE send_pushover = 0 AND pushover_settings = 3 AND DATE_FORMAT( pr.date, \'%Y-%m-%d %H\' ) < \'' . date( 'Y-m-d 20' ) . '\' GROUP BY poster_id ) ) '
                                  . 'AND '
                                    . 'pushover_api IS NOT NULL '
                                  . 'AND '
                                    . 'pushover_user IS NOT NULL' ) -> fetchAll( \PDO::FETCH_ASSOC );
      if ( is_array( $users ) and count( $users ) ) 
      {
        foreach ( $users as $user )
        {
          $posters = $mdb -> query( 'SELECT '
                                        . 'pr.id, url, title, price, comment '
                                      . 'FROM '
                                        . 'posters_history AS pr '
                                        . 'INNER JOIN posters AS p ON p.id = pr.poster_id '
                                      . 'WHERE '
                                          . 'send_pushover = 0 '
                                        . 'AND '
                                          . 'pushover_settings = 3 '
                                        . 'AND '
                                          . 'DATE_FORMAT( pr.date, \'%Y-%m-%d %H\' ) < \'' . date( 'Y-m-d 20' ) . '\' '
                                        . 'AND '
                                          . 'user_id = ' . $user['id'] . ' LIMIT 1' ) -> fetchAll( \PDO::FETCH_ASSOC );
          if ( is_array( $posters ) and count( $posters ) ) 
          {
            foreach ( $posters as $poster )
            {
              $posters_id[]   = $poster['id'];
              $auctions[]     = $poster;
            }

            \factory\Posters::send_push( $user['email'], $auctions, $user['pushover_api'], $user['pushover_user'] );
            $mdb -> query( 'UPDATE posters_history SET send_pushover = 1 WHERE id IN ( ' . implode( ',', $posters_id ) . ')' );
          }
        }

        return [ 
                  'status' => 'ok', 
                  'msg' => 'Wysyłam powiadomienia push o znalezionych ogłoszeniach.'
                ];
      }
    }
    
    if ( date( 'Y-m-d H:i:s' ) > date( 'Y-m-d 20:00:00' ) )
    {    
      /* powiadomienia zbiorcze raz dziennie - o godzinie 20 */
      $users = $mdb -> query( 'SELECT '
                                  . 'id, email, pushover_api, pushover_user '
                                . 'FROM '
                                  . 'users '
                                . 'WHERE '
                                  . 'id IN ('
                                    . 'SELECT user_id FROM posters WHERE id IN ( '
                                      . 'SELECT poster_id FROM posters_history AS pr WHERE send_pushover = 0 AND pushover_settings = 4 AND DATE_FORMAT( pr.date, \'%Y-%m-%d %H\' ) < \'' . date( 'Y-m-d 20' ) . '\' GROUP BY poster_id ) ) '
                                  . 'AND '
                                    . 'pushover_api IS NOT NULL '
                                  . 'AND '
                                    . 'pushover_user IS NOT NULL' ) -> fetchAll( \PDO::FETCH_ASSOC );
      if ( is_array( $users ) and count( $users ) ) 
      {
        foreach ( $users as $user )
        {        
          $posters = $mdb -> query( 'SELECT '
                                        . 'pr.id, url, title, price, comment '
                                      . 'FROM '
                                        . 'posters_history AS pr '
                                        . 'INNER JOIN posters AS p ON p.id = pr.poster_id '
                                      . 'WHERE '
                                          . 'send_pushover = 0 '
                                        . 'AND '
                                          . 'pushover_settings = 4 '
                                        . 'AND '
                                          . 'DATE_FORMAT( pr.date, \'%Y-%m-%d %H\' ) < \'' . date( 'Y-m-d 20' ) . '\' '
                                        . 'AND '
                                          . 'user_id = ' . $user['id'] . ' LIMIT 1' ) -> fetchAll( \PDO::FETCH_ASSOC );
          if ( is_array( $posters ) and count( $posters ) ) 
          {
            foreach ( $posters as $poster )
            {
              $posters_id[]   = $poster['id'];
              $auctions[]     = $poster;
            }

            \factory\Posters::send_push( $user['email'], $auctions, $user['pushover_api'], $user['pushover_user'] );
            $mdb -> query( 'UPDATE posters_history SET send_pushover = 1 WHERE id IN ( ' . implode( ',', $posters_id ) . ')' );
          }
        }

        return [ 
                  'status' => 'ok', 
                  'msg' => 'Wysyłam powiadomienia push o znalezionych ogłoszeniach.'
                ];
      }
    }
    return [ 'status' => 'bad' ];
  }
  
  public static function send_emails()
  {
    global $mdb;
    
    $sent_mail = false;
    
    /* powiadomienia natychmiastowe */
    $users = $mdb -> query( 'SELECT '
                                . 'id, email '
                              . 'FROM '
                                . 'users '
                              . 'WHERE '
                                . 'id IN ('
                                  . 'SELECT user_id FROM posters WHERE id IN ( '
                                    . 'SELECT poster_id FROM posters_history WHERE send_email = 0 AND mail_settings = 1 GROUP BY poster_id ) )' ) -> fetchAll( \PDO::FETCH_ASSOC );
    if ( is_array( $users ) and count( $users ) ) 
    {
      foreach ( $users as $user )
      {
        $posters = $mdb -> query( 'SELECT '
                                      . 'pr.id, url, title, price, comment, mail_settings '
                                    . 'FROM '
                                      . 'posters_history AS pr '
                                      . 'INNER JOIN posters AS p ON p.id = pr.poster_id '
                                    . 'WHERE '
                                        . 'send_email = 0 '
                                      . 'AND '
                                        . 'mail_settings = 1 '
                                      . 'AND '
                                        . 'user_id = ' . $user['id'] ) -> fetchAll( \PDO::FETCH_ASSOC );
        if ( is_array( $posters ) and count( $posters ) ) 
        {
          foreach ( $posters as $poster )
          {
            $posters_id[]   = $poster['id'];
            $auctions[]     = $poster;
          }
          
          \factory\Posters::send_emails( $user['email'], $auctions );
          $mdb -> query( 'UPDATE posters_history SET send_email = 1 WHERE id IN ( ' . implode( ',', $posters_id ) . ')' );
        }
      }

      return [ 
                'status' => 'ok', 
                'msg' => 'Wysyłam powiadomienia mailowe o znalezionych ogłoszeniach.'
              ];
    }
    
    /* powiadomienia zbiorcze co godzinę */
    $users = $mdb -> query( 'SELECT '
                                . 'id, email '
                              . 'FROM '
                                . 'users '
                              . 'WHERE '
                                . 'id IN ('
                                  . 'SELECT user_id FROM posters WHERE id IN ( '
                                    . 'SELECT poster_id FROM posters_history AS pr WHERE send_email = 0 AND mail_settings = 2 AND DATE_FORMAT( pr.date, \'%Y-%m-%d %H\' ) = \'' . date( 'Y-m-d H', strtotime( '-1 hours', time() ) ) . '\' GROUP BY poster_id ) )' ) -> fetchAll( \PDO::FETCH_ASSOC );
    if ( is_array( $users ) and count( $users ) ) 
    {
      foreach ( $users as $user )
      {
        $posters = $mdb -> query( 'SELECT '
                                      . 'pr.id, url, title, price, comment, mail_settings '
                                    . 'FROM '
                                      . 'posters_history AS pr '
                                      . 'INNER JOIN posters AS p ON p.id = pr.poster_id '
                                    . 'WHERE '
                                        . 'send_email = 0 '
                                      . 'AND '
                                        . 'mail_settings = 2 '
                                      . 'AND '
                                        . 'DATE_FORMAT( pr.date, \'%Y-%m-%d %H\' ) = \'' . date( 'Y-m-d H', strtotime( '-1 hours', time() ) ) . '\' '
                                      . 'AND '
                                        . 'user_id = ' . $user['id'] ) -> fetchAll( \PDO::FETCH_ASSOC );
        if ( is_array( $posters ) and count( $posters ) ) 
        {
          foreach ( $posters as $poster )
          {
            $posters_id[]   = $poster['id'];
            $auctions[]     = $poster;
          }
          
          \factory\Posters::send_emails( $user['email'], $auctions, ' | mail zbiorczy co 1h' );
          $mdb -> query( 'UPDATE posters_history SET send_email = 1 WHERE id IN ( ' . implode( ',', $posters_id ) . ')' );
        }
      }

      return [ 
                'status' => 'ok', 
                'msg' => 'Wysyłam powiadomienia mailowe o znalezionych ogłoszeniach.'
              ];
    }
    
    if ( date( 'Y-m-d H:i:s' ) > date( 'Y-m-d 08:00:00' ) )
    {
      /* powiadomienia zbiorcze dwa razy dziennie - o godzinie 8 */
      $users = $mdb -> query( 'SELECT '
                                  . 'id, email '
                                . 'FROM '
                                  . 'users '
                                . 'WHERE '
                                  . 'id IN ('
                                    . 'SELECT user_id FROM posters WHERE id IN ( '
                                      . 'SELECT poster_id FROM posters_history AS pr WHERE send_email = 0 AND mail_settings = 3 AND DATE_FORMAT( pr.date, \'%Y-%m-%d %H\' ) < \'' . date( 'Y-m-d 08' ) . '\' GROUP BY poster_id ) )' ) -> fetchAll( \PDO::FETCH_ASSOC );
      if ( is_array( $users ) and count( $users ) ) 
      {
        foreach ( $users as $user )
        {
          $posters = $mdb -> query( 'SELECT '
                                        . 'pr.id, url, title, price, comment, mail_settings '
                                      . 'FROM '
                                        . 'posters_history AS pr '
                                        . 'INNER JOIN posters AS p ON p.id = pr.poster_id '
                                      . 'WHERE '
                                          . 'send_email = 0 '
                                        . 'AND '
                                          . 'mail_settings = 3 '
                                        . 'AND '
                                          . 'DATE_FORMAT( pr.date, \'%Y-%m-%d %H\' ) < \'' . date( 'Y-m-d 08' ) . '\' '
                                        . 'AND '
                                          . 'user_id = ' . $user['id'] ) -> fetchAll( \PDO::FETCH_ASSOC );
          if ( is_array( $posters ) and count( $posters ) ) 
          {
            foreach ( $posters as $poster )
            {
              $posters_id[]   = $poster['id'];
              $auctions[]     = $poster;
            }

            \factory\Posters::send_emails( $user['email'], $auctions, ' | mail zbiorczy 2 razy dziennie (08:00)' );
            $mdb -> query( 'UPDATE posters_history SET send_email = 1 WHERE id IN ( ' . implode( ',', $posters_id ) . ')' );
          }
        }

        return [ 
                  'status' => 'ok', 
                  'msg' => 'Wysyłam powiadomienia mailowe o znalezionych ogłoszeniach.'
                ];
      }
    }
    
    if ( date( 'Y-m-d H:i:s' ) > date( 'Y-m-d 20:00:00' ) )
    {
      /* powiadomienia zbiorcze dwa razy dziennie - o godzinie 20 */
      $users = $mdb -> query( 'SELECT '
                                  . 'id, email '
                                . 'FROM '
                                  . 'users '
                                . 'WHERE '
                                  . 'id IN ('
                                    . 'SELECT user_id FROM posters WHERE id IN ( '
                                      . 'SELECT poster_id FROM posters_history AS pr WHERE send_email = 0 AND mail_settings = 3 AND DATE_FORMAT( pr.date, \'%Y-%m-%d %H\' ) < \'' . date( 'Y-m-d 20' ) . '\' GROUP BY poster_id ) )' ) -> fetchAll( \PDO::FETCH_ASSOC );
      if ( is_array( $users ) and count( $users ) ) 
      {
        foreach ( $users as $user )
        {
          $posters = $mdb -> query( 'SELECT '
                                        . 'pr.id, url, title, price, comment, mail_settings '
                                      . 'FROM '
                                        . 'posters_history AS pr '
                                        . 'INNER JOIN posters AS p ON p.id = pr.poster_id '
                                      . 'WHERE '
                                          . 'send_email = 0 '
                                        . 'AND '
                                          . 'mail_settings = 3 '
                                        . 'AND '
                                          . 'DATE_FORMAT( pr.date, \'%Y-%m-%d %H\' ) < \'' . date( 'Y-m-d 20' ) . '\' '
                                        . 'AND '
                                          . 'user_id = ' . $user['id'] ) -> fetchAll( \PDO::FETCH_ASSOC );
          if ( is_array( $posters ) and count( $posters ) ) 
          {
            foreach ( $posters as $poster )
            {
              $posters_id[]   = $poster['id'];
              $auctions[]     = $poster;
            }

            \factory\Posters::send_emails( $user['email'], $auctions, ' | mail zbiorczy 2 razy dziennie (20:00)' );
            $mdb -> query( 'UPDATE posters_history SET send_email = 1 WHERE id IN ( ' . implode( ',', $posters_id ) . ')' );
          }
        }

        return [ 
                  'status' => 'ok', 
                  'msg' => 'Wysyłam powiadomienia mailowe o znalezionych ogłoszeniach.'
                ];
      }
    }
    
    if ( date( 'Y-m-d H:i:s' ) > date( 'Y-m-d 20:00:00' ) )
    {
      /* powiadomienia zbiorcze raz dziennie - o godzinie 20 */
      $users = $mdb -> query( 'SELECT '
                                  . 'id, email '
                                . 'FROM '
                                  . 'users '
                                . 'WHERE '
                                  . 'id IN ('
                                    . 'SELECT user_id FROM posters WHERE id IN ( '
                                      . 'SELECT poster_id FROM posters_history AS pr WHERE send_email = 0 AND mail_settings = 4 AND DATE_FORMAT( pr.date, \'%Y-%m-%d %H\' ) < \'' . date( 'Y-m-d 20' ) . '\' GROUP BY poster_id ) )' ) -> fetchAll( \PDO::FETCH_ASSOC );
      if ( is_array( $users ) and count( $users ) ) 
      {
        foreach ( $users as $user )
        {        
          $posters = $mdb -> query( 'SELECT '
                                        . 'pr.id, url, title, price, comment, mail_settings '
                                      . 'FROM '
                                        . 'posters_history AS pr '
                                        . 'INNER JOIN posters AS p ON p.id = pr.poster_id '
                                      . 'WHERE '
                                          . 'send_email = 0 '
                                        . 'AND '
                                          . 'mail_settings = 4 '
                                        . 'AND '
                                          . 'DATE_FORMAT( pr.date, \'%Y-%m-%d %H\' ) < \'' . date( 'Y-m-d 20' ) . '\' '
                                        . 'AND '
                                          . 'user_id = ' . $user['id'] ) -> fetchAll( \PDO::FETCH_ASSOC );
          if ( is_array( $posters ) and count( $posters ) ) 
          {
            foreach ( $posters as $poster )
            {
              $posters_id[]   = $poster['id'];
              $auctions[]     = $poster;
            }

            \factory\Posters::send_emails( $user['email'], $auctions, ' | mail zbiorczy raz dziennie (20:00)' );
            $mdb -> query( 'UPDATE posters_history SET send_email = 1 WHERE id IN ( ' . implode( ',', $posters_id ) . ')' );
          }
        }

        return [ 
                  'status' => 'ok', 
                  'msg' => 'Wysyłam powiadomienia mailowe o znalezionych ogłoszeniach.'
                ];
      }
    }
    return [ 'status' => 'bad' ];
  }
}