<commit_before>
package VNDB::DB::VN;

use strict;
use warnings;
use Exporter 'import';
use VNDB::Func 'gtintype';

our @EXPORT = qw|dbVNGet dbVNAdd dbVNEdit dbVNImageId dbVNCache dbScreenshotAdd dbScreenshotGet dbScreenshotRandom|;


# Options: id, rev, char, search, cati, cate, lang, platform, results, page, order, what
# What: extended categories anime relations screenshots relgraph ranking changes
sub dbVNGet {
  my($self, %o) = @_;
  $o{results} ||= 10;
  $o{page}    ||= 1;
  $o{order}   ||= 'vr.title ASC';
  $o{what}    ||= '';

  my %where = (
    $o{id} ? (
      'v.id = ?' => $o{id} ) : (),
    $o{rev} ? (
      'c.rev = ?' => $o{rev} ) : (),
    $o{char} ? (
      'LOWER(SUBSTR(vr.title, 1, 1)) = ?' => $o{char} ) : (),
    defined $o{char} && !$o{char} ? (
      '(ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)' => 1 ) : (),
    $o{cati} && @{$o{cati}} ? ( q|
      v.id IN(SELECT iv.id
        FROM vn_categories ivc
        JOIN vn iv ON iv.latest = ivc.vid
        WHERE cat IN(!l)
        GROUP BY iv.id
        HAVING COUNT(cat) = ?)| => [ $o{cati}, $#{$o{cati}}+1 ] ) : (),
    $o{cate} && @{$o{cate}} ? ( q|
      v.id NOT IN(SELECT iv.id
        FROM vn_categories ivc
        JOIN vn iv ON iv.latest = ivc.vid
        WHERE cat IN(!l)
        GROUP BY iv.id)| => [ $o{cate} ] ) : (),
    $o{lang} && @{$o{lang}} ? (
      '('.join(' OR ', map "v.c_languages ILIKE '%%$_%%'", @{$o{lang}}).')' => 1 ) : (),
    $o{platform} && @{$o{platform}} ? (
      '('.join(' OR ', map "v.c_platforms ILIKE '%%$_%%'", @{$o{platform}}).')' => 1 ) : (),
   # don't fetch hidden items unless we ask for an ID
    !$o{id} && !$o{rev} ? (
      'v.hidden = FALSE' => 0 ) : (),
  );

  if($o{search}) {
    my @w = (
      '(irr.id IS NULL OR ir.latest = irr.id)' => 1
    );
    for (split /[ -,._]/, $o{search}) {
      s/%//g;
      next if length($_) < 2;
      if(/^\d+$/ && gtintype($_)) {
        push @w, 'irr.gtin = ?', $_;
      } else {
        $_ = "%$_%";
        push @w, '(ivr.title ILIKE ? OR ivr.original ILIKE ? OR ivr.alias ILIKE ? OR irr.title ILIKE ? OR irr.original ILIKE ?)',
          [ $_, $_, $_, $_, $_ ];
      }
    }
    $where{ q|
      v.id IN(SELECT iv.id
        FROM vn iv
        JOIN vn_rev ivr ON iv.latest = ivr.id
        LEFT JOIN releases_vn irv ON irv.vid = iv.id
        LEFT JOIN releases_rev irr ON irr.id = irv.rid
        LEFT JOIN releases ir ON ir.latest = irr.id
        !W
        GROUP BY iv.id)|
    } = [ \@w ] if @w;
  }

  my @join = (
    $o{rev} ?
      'JOIN vn v ON v.id = vr.vid' :
      'JOIN vn v ON vr.id = v.latest',
    $o{rev} || $o{what} =~ /changes/ ?
      'JOIN changes c ON c.id = vr.id' : (),
    $o{what} =~ /changes/ ?
      'JOIN users u ON u.id = c.requester' : (),
    $o{what} =~ /relgraph/ ?
      'JOIN relgraph rg ON rg.id = v.rgraph' : (),
  );

  my @select = (
    qw|v.id v.locked v.hidden v.c_released v.c_languages v.c_platforms vr.title vr.original v.rgraph v.c_popularity|, 'vr.id AS cid',
    $o{what} =~ /extended/ ? (
      qw|vr.alias vr.image vr.img_nsfw vr.length vr.desc vr.l_wp vr.l_encubed vr.l_renai vr.l_vnn| ) : (),
    $o{what} =~ /changes/ ? (
      qw|c.added c.requester c.comments v.latest u.username c.rev c.causedby|) : (),
    $o{what} =~ /relgraph/ ? 'rg.cmap' : (),
    $o{what} =~ /ranking/ ? '(SELECT COUNT(*) FROM vn iv WHERE iv.c_popularity >= v.c_popularity) AS ranking' : (),
  );

  my($r, $np) = $self->dbPage(\%o, q|
    SELECT !s
      FROM vn_rev vr
      !s
      !W
      ORDER BY !s|,
    join(', ', @select), join(' ', @join), \%where, $o{order},
  );

  if(@$r && $o{what} =~ /(categories|anime|relations|screenshots)/) {
    my %r = map {
      $r->[$_]{categories} = [];
      $r->[$_]{anime} = [];<commit_msg>Don't count hidden VNs in ranking + first rank considered with shared place<commit_after>
package VNDB::DB::VN;

use strict;
use warnings;
use Exporter 'import';
use VNDB::Func 'gtintype';

our @EXPORT = qw|dbVNGet dbVNAdd dbVNEdit dbVNImageId dbVNCache dbScreenshotAdd dbScreenshotGet dbScreenshotRandom|;


# Options: id, rev, char, search, cati, cate, lang, platform, results, page, order, what
# What: extended categories anime relations screenshots relgraph ranking changes
sub dbVNGet {
  my($self, %o) = @_;
  $o{results} ||= 10;
  $o{page}    ||= 1;
  $o{order}   ||= 'vr.title ASC';
  $o{what}    ||= '';

  my %where = (
    $o{id} ? (
      'v.id = ?' => $o{id} ) : (),
    $o{rev} ? (
      'c.rev = ?' => $o{rev} ) : (),
    $o{char} ? (
      'LOWER(SUBSTR(vr.title, 1, 1)) = ?' => $o{char} ) : (),
    defined $o{char} && !$o{char} ? (
      '(ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)' => 1 ) : (),
    $o{cati} && @{$o{cati}} ? ( q|
      v.id IN(SELECT iv.id
        FROM vn_categories ivc
        JOIN vn iv ON iv.latest = ivc.vid
        WHERE cat IN(!l)
        GROUP BY iv.id
        HAVING COUNT(cat) = ?)| => [ $o{cati}, $#{$o{cati}}+1 ] ) : (),
    $o{cate} && @{$o{cate}} ? ( q|
      v.id NOT IN(SELECT iv.id
        FROM vn_categories ivc
        JOIN vn iv ON iv.latest = ivc.vid
        WHERE cat IN(!l)
        GROUP BY iv.id)| => [ $o{cate} ] ) : (),
    $o{lang} && @{$o{lang}} ? (
      '('.join(' OR ', map "v.c_languages ILIKE '%%$_%%'", @{$o{lang}}).')' => 1 ) : (),
    $o{platform} && @{$o{platform}} ? (
      '('.join(' OR ', map "v.c_platforms ILIKE '%%$_%%'", @{$o{platform}}).')' => 1 ) : (),
   # don't fetch hidden items unless we ask for an ID
    !$o{id} && !$o{rev} ? (
      'v.hidden = FALSE' => 0 ) : (),
  );

  if($o{search}) {
    my @w = (
      '(irr.id IS NULL OR ir.latest = irr.id)' => 1
    );
    for (split /[ -,._]/, $o{search}) {
      s/%//g;
      next if length($_) < 2;
      if(/^\d+$/ && gtintype($_)) {
        push @w, 'irr.gtin = ?', $_;
      } else {
        $_ = "%$_%";
        push @w, '(ivr.title ILIKE ? OR ivr.original ILIKE ? OR ivr.alias ILIKE ? OR irr.title ILIKE ? OR irr.original ILIKE ?)',
          [ $_, $_, $_, $_, $_ ];
      }
    }
    $where{ q|
      v.id IN(SELECT iv.id
        FROM vn iv
        JOIN vn_rev ivr ON iv.latest = ivr.id
        LEFT JOIN releases_vn irv ON irv.vid = iv.id
        LEFT JOIN releases_rev irr ON irr.id = irv.rid
        LEFT JOIN releases ir ON ir.latest = irr.id
        !W
        GROUP BY iv.id)|
    } = [ \@w ] if @w;
  }

  my @join = (
    $o{rev} ?
      'JOIN vn v ON v.id = vr.vid' :
      'JOIN vn v ON vr.id = v.latest',
    $o{rev} || $o{what} =~ /changes/ ?
      'JOIN changes c ON c.id = vr.id' : (),
    $o{what} =~ /changes/ ?
      'JOIN users u ON u.id = c.requester' : (),
    $o{what} =~ /relgraph/ ?
      'JOIN relgraph rg ON rg.id = v.rgraph' : (),
  );

  my @select = (
    qw|v.id v.locked v.hidden v.c_released v.c_languages v.c_platforms vr.title vr.original v.rgraph v.c_popularity|, 'vr.id AS cid',
    $o{what} =~ /extended/ ? (
      qw|vr.alias vr.image vr.img_nsfw vr.length vr.desc vr.l_wp vr.l_encubed vr.l_renai vr.l_vnn| ) : (),
    $o{what} =~ /changes/ ? (
      qw|c.added c.requester c.comments v.latest u.username c.rev c.causedby|) : (),
    $o{what} =~ /relgraph/ ? 'rg.cmap' : (),
    $o{what} =~ /ranking/ ? '(SELECT COUNT(*) FROM vn iv WHERE iv.hidden = false AND iv.c_popularity > v.c_popularity) AS ranking' : (),
  );

  my($r, $np) = $self->dbPage(\%o, q|
    SELECT !s
      FROM vn_rev vr
      !s
      !W
      ORDER BY !s|,
    join(', ', @select), join(' ', @join), \%where, $o{order},
  );

  if(@$r && $o{what} =~ /(categories|anime|relations|screenshots)/) {
    my %r = map {
      $r->[$_]{categories} = [];
      $r->[$_]{anime} = [];
{'fraction_non_alphanumeric': 0.21399765838428517, 'fraction_numerical': 0.0054637700013008975, 'mean_word_length': 2.2356902356902357, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 35, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7345315', 'n_tokens_mistral': 3324, 'n_tokens_neox': 3172, 'n_words': 746}