<?php
namespace factory;

class Projects
{
  public static $limit = 25;
  
  public static function clear_project( $project_id )
  {
    global $mdb;

    $mdb -> delete( 'project_links_external', [ 'project_id' => $project_id ] );
    $mdb -> delete( 'project_links_internal', [ 'project_id' => $project_id ] );
    return $mdb -> update( 'project_links_internal', [ 'visited' => 0 ], [ 'project_id' => $project_id ] );
  }
  
  public static function refresh( $project_id )
  {
    global $mdb;
    
    $mdb -> delete( 'project_links_internal', [ 'AND' => [ 'project_id' => $project_id, 'parent_id[!]' => null ] ] );
    $mdb -> delete( 'project_links_external', [ 'project_id' => $project_id ] );
    $mdb -> update( 'project_links_internal', [ 'visited' => 0 ], [ 'project_id' => $project_id ] );
    
    return true;
  }
  
  public static function count_links_external( $project_id )
  {
    global $mdb;
    return $mdb -> query( 'SELECT '
                                  . 'COUNT( DISTINCT(url) ) AS c '
                                . 'FROM '
                                  . 'project_links_external '
                                . 'WHERE '
                                  . 'project_id = ' . $project_id ) -> fetchAll();
  }
  
  public static function project_details( $project_id )
  {
    global $mdb;
    return $mdb -> get( 'projects', '*', [ 'id' => $project_id ] );
  }
  
  public static function links_external( $bs, $project_id )
  {
    global $mdb;
    
    $results = $mdb -> query( 'SELECT '
                                  . 'DISTINCT(url) AS url '
                                . 'FROM '
                                  . 'project_links_external '
                                . 'WHERE '
                                  . 'project_id = ' . $project_id . ' '
                                . "ORDER BY "
                                  . "url ASC "
                                . "LIMIT  " . ( $bs - 1 ) * self::$limit . "," . self::$limit ) -> fetchAll();
    if ( is_array( $results ) and !empty( $results ) ) foreach ( $results as $row )
    {
      $urls = '';
      
      $results2 = $mdb -> query( 'SELECT '
                                    . 'pli.url AS url '
                                  . 'FROM '
                                    . 'project_links_external AS ple '
                                      . 'INNER JOIN project_links_internal AS pli ON pli.id = ple.link_id '
                                  . 'WHERE '
                                      . 'ple.url = \'' . $row['url'] . '\'' ) -> fetchAll();
      if ( is_array( $results2 ) and !empty( $results2 ) ) foreach ( $results2 as $row2 )
        $urls[] = $row2['url'];
      
      $row['urls'] = $urls;
      
      $links[] = $row;
    }
    return $links;
  }
  
  public static function delete_project( $project_id )
  {
    global $mdb;
    
    $mdb -> delete( 'project_links_internal', [ 'project_id' => $project_id ] );
    $mdb -> delete( 'project_links_external', [ 'project_id' => $project_id ] );
    $mdb -> delete( 'projects', [ 'id' => $project_id ] );
    return true;
  }
  
  public static function hide_url( $links )
  {
    global $mdb;
    
    $links = explode( ',', $links );
    
    if ( is_array( $links ) ) foreach ( $links as $link )    
      $mdb -> update( 'project_links_internal', [ 'hide' => 1 ], [ 'id' => $link ] );    
    
    return true;
  }
  
  public static function links_visited( $project_id )
  {
    global $mdb;    
    return $mdb -> count( 'project_links_internal', [ 'AND' => [ 'project_id' => $project_id, 'deleted' => 0, 'visited' => 1 ] ] );
  }
  
  public static function count_links_internal_all( $project_id )
  {
    global $mdb;    
    return $mdb -> count( 'project_links_internal', [ 'AND' => [ 'project_id' => $project_id, 'deleted' => 0 ] ] );
  }
  
  public static function count_links_internal( 
    $project_id, $filtr_mke, $filtr_mde, $filtr_mte, $filtr_irb, $filtr_grb, $filtr_url, $filtr_wor, $filtr_wir, $filtr_hid, $filtr_edf, $filtr_edfrops, $filtr_404, $filtr_tables, $filtr_iframe, $filtr_h1,
    $filtr_images_alt
  )
  {
    global $mdb;
        
    if ( $filtr_mke )
      $sql .= " AND ( meta_keywords = ''";
    
    if ( $filtr_mde )
      $sql ? $sql .= " OR meta_description = ''" : $sql .= " AND ( meta_description = ''";
    
    if ( $filtr_mte )
      $sql ? $sql .= " OR title = ''" : $sql .= " AND ( title = ''";
    
    if ( $filtr_irb )
      $sql ? $sql .= " OR meta_robots LIKE '%noindex%'" : $sql .= " AND ( meta_robots LIKE '%noindex%'";
    
    if ( $filtr_grb )
      $sql ? $sql .= " OR meta_robots LIKE '%noindex%' OR meta_googlebot LIKE '%noindex%'" : $sql .= " AND ( ( meta_robots LIKE '%noindex%' OR meta_googlebot LIKE '%noindex%' )";
    
    if ( $filtr_url )
      $sql ? $sql .= " OR pli.url LIKE '%" . addslashes( $filtr_url ) . "%'" : $sql .= " AND ( pli.url LIKE '%" . addslashes( $filtr_url ) . "%'";
    
    if ( $filtr_wor )
      $sql ? $sql .= " OR response_code = '200'" : $sql .= " AND ( response_code = '200'";
    
    if ( $filtr_wir )
      $sql ? $sql .= " OR response_code != '200'" : $sql .= " AND ( response_code != '200'";
    
    if ( $filtr_404 )
      $sql ? $sql .= " OR response_code = '404'" : $sql .= " AND ( response_code = '404'";
    
    $filtr_hid ? $hidden = '( hide = 1 OR hide = 0 )' : $hidden = 'hide = 0';
    
    if ( $filtr_edf )
      $sql2 = ' AND id IN ( SELECT DISTINCT( link_id ) FROM project_links_external WHERE project_id = ' . $project_id . ' AND nofollow = 0 ) ';
    
    if ( $filtr_edfrops )
      $sql2 = ' AND id IN ( SELECT DISTINCT( link_id ) FROM project_links_external WHERE project_id = ' . $project_id . ' AND title NOT LIKE \'%(Link prowadzi do nowej strony)%\' ) ';
    
    if ( $filtr_tables )
      $sql ? $sql .= " OR have_table = 1" : $sql .= " AND ( have_table = '1'";

    if ( $filtr_iframe )
      $sql ? $sql .= " OR have_iframe = 1" : $sql .= " AND ( have_iframe = '1'";

    if ( $filtr_h1 )
      $sql ? $sql .= " OR have_h1 = 0" : $sql .= " AND ( have_h1 = '0'";

    if ( $filtr_images_alt )
      $sql ? $sql .= " OR have_images_without_alt = 1" : $sql .= " AND ( have_images_without_alt = 1";
    
    if ( $sql )
      $sql .= " )";
    
    return $mdb -> query( "SELECT "
                                . "COUNT(pli.id) AS c "
                            . "FROM "
                                . "project_links_internal AS pli "
                            . "WHERE "
                              . "pli.project_id = " . $project_id . $sql . " AND " . $hidden . " AND deleted = 0 " . $sql2 ) -> fetchAll();
  }
  
  public static function links_internal_list( 
    $bs, $project_id, $filtr_mke, $filtr_mde, $filtr_mte, $filtr_irb, $filtr_grb, $filtr_url, $filtr_wor, $filtr_wir, $filtr_hid, $filtr_edf, $filtr_edfrops, $filtr_404, $filtr_tables, $filtr_iframe, $filtr_h1,
    $filtr_images_alt )
  {
    global $mdb;
        
    if ( $filtr_mke )
      $sql .= " AND ( meta_keywords = ''";
    
    if ( $filtr_mde )
      $sql ? $sql .= " OR meta_description = ''" : $sql .= " AND ( meta_description = ''";
    
    if ( $filtr_mte )
      $sql ? $sql .= " OR pli.title = ''" : $sql .= " AND ( pli.title = ''";
    
    if ( $filtr_irb )
      $sql ? $sql .= " OR meta_robots LIKE '%noindex%'" : $sql .= " AND ( meta_robots LIKE '%noindex%'";
    
    if ( $filtr_grb )
      $sql ? $sql .= " OR meta_robots LIKE '%noindex%' OR meta_googlebot LIKE '%noindex%'" : $sql .= " AND ( ( meta_robots LIKE '%noindex%' OR meta_googlebot LIKE '%noindex%' )";
    
    if ( $filtr_url )
      $sql ? $sql .= " OR pli.url LIKE '%" . addslashes( $filtr_url ) . "%'" : $sql .= " AND ( pli.url LIKE '%" . addslashes( $filtr_url ) . "%'";
    
    if ( $filtr_wor )
      $sql ? $sql .= " OR response_code = '200'" : $sql .= " AND ( response_code = '200'";
    
    if ( $filtr_wir )
      $sql ? $sql .= " OR response_code != '200'" : $sql .= " AND ( response_code != '200'";  
    
    if ( $filtr_404 )
      $sql ? $sql .= " OR response_code = '404'" : $sql .= " AND ( response_code = '404'";  
    
    if ( $filtr_tables )
      $sql ? $sql .= " OR have_table = 1" : $sql .= " AND ( have_table = 1";  
    
    if ( $filtr_iframe )
      $sql ? $sql .= " OR have_iframe = 1" : $sql .= " AND ( have_iframe = 1";  
    
    if ( $filtr_h1 )
      $sql ? $sql .= " OR have_h1 = 0" : $sql .= " AND ( have_h1 = 0";  
    
    if ( $filtr_images_alt )
      $sql ? $sql .= " OR have_images_without_alt = 1" : $sql .= " AND ( have_images_without_alt = 1";  
    
    $filtr_hid ? $hidden = '( hide = 1 OR hide = 0 )' : $hidden = 'hide = 0';
    
    if ( $filtr_edf )
    {
      $join = 'INNER JOIN ';
      $sql ? $sql .= " AND nofollow = 0" : $sql .= " AND ( nofollow = 0";
    }
    else
      $join = 'LEFT JOIN ';
    
    if ( $filtr_edfrops )
    {
      $join = 'INNER JOIN ';
      $sql ? $sql .= " AND ple.title NOT LIKE '%(Link prowadzi do nowej strony)%'" : $sql .= " AND ( ple.title NOT LIKE '%(Link prowadzi do nowej strony)%'";
      // $sql = ' AND id IN ( SELECT DISTINCT( link_id ) FROM project_links_external WHERE project_id = ' . $project_id . ' AND title NOT LIKE \'%(Link prowadzi do nowej strony)%\' ) ';
    }
    else
      $join = 'LEFT JOIN ';
    
    if ( $sql )
      $sql .= " )";
  //     echo "SELECT "
  //     . "pli.id, pli.url, pli.title, ple.title as etitle, meta_keywords, meta_description, meta_robots, meta_googlebot, code_lenght, text_lenght, response_code, parent_id, canonical "
  // . "FROM "
  //     . "project_links_internal AS pli "
  //       . $join . " project_links_external AS ple ON pli.id = ple.link_id "
  // . "WHERE "
  //   . "pli.project_id = " . $project_id . $sql . " AND " . $hidden . " AND deleted = 0 "
  // . "GROUP BY "
  //     . "pli.id "
  // . "ORDER BY "
  //   . "url ASC "
  // . "LIMIT  " . ( $bs - 1 ) * self::$limit . "," . self::$limit; exit;
    $results = $mdb -> query( "SELECT "
                                . "pli.id, pli.url, pli.title, ple.title as etitle, meta_keywords, meta_description, meta_robots, meta_googlebot, code_lenght, text_lenght, response_code, parent_id, canonical "
                            . "FROM "
                                . "project_links_internal AS pli "
                                  . $join . " project_links_external AS ple ON pli.id = ple.link_id "
                            . "WHERE "
                              . "pli.project_id = " . $project_id . $sql . " AND " . $hidden . " AND deleted = 0 "
                            . "GROUP BY "
                                . "pli.id "
                            . "ORDER BY "
                              . "url ASC "
                            . "LIMIT  " . ( $bs - 1 ) * self::$limit . "," . self::$limit ) -> fetchAll();
    if ( is_array( $results ) ) foreach ( $results as $row )
    {
      $row['external_links'] = self::links_external_list( $row['id'] );
      $links[] = $row;
    }
    return $links;
  }
  
  public static function links_external_list( $link_id )
  {
    global $mdb;
    return $mdb -> select( 'project_links_external', [ 'url', 'nofollow', 'title' ], [ 'link_id' => $link_id ] );
  }
}