<commit_before>#!/usr/bin/perl -w

# create media_tag_tag_counts table by querying the database tags / feeds / stories

use strict;
use warnings;

BEGIN
{
    use FindBin;
    use lib "$FindBin::Bin/../lib";
    use lib "$FindBin::Bin/.";
}

use MediaWords::DB;
use DBIx::Simple::MediaWords;
use TableCreationUtils;
use Readonly;
use File::Temp qw/ tempfile tempdir /;
use Archive::Zip qw( :ERROR_CODES :CONSTANTS );
use File::Copy;
use List::Util qw(first max maxstr min minstr reduce shuffle sum);
use MediaWords::Controller::Dashboard;

use Getopt::Long;
use Date::Parse;
use Data::Dumper;
use Carp;
use Dir::Self;
my $_stories_id_window_size = 1000;

# base dir
my $_base_dir = __DIR__ . '/..';

sub get_max_stories_id
{
    my ( $dbh ) = @_;

    my $max_stories_id_row = $dbh->query( "select max(stories_id) as max_id from story_sentence_words" );

    my $max_stories_id = $max_stories_id_row->hash()->{ max_id };

    return $max_stories_id;
}

sub scroll_stories_id_window
{
    my ( $_stories_id_start, $_stories_id_stop, $max_stories_id ) = @_;

    $_stories_id_start = $_stories_id_stop + 1;
    $_stories_id_stop  = $_stories_id_start + $_stories_id_window_size - 1;

    $_stories_id_stop = min( $_stories_id_stop, $max_stories_id );

    return ( $_stories_id_start, $_stories_id_stop );
}

sub isNonnegativeInteger
{
    my ( $val ) = @_;

    return int( $val ) eq $val;
}

sub dump_story_words
{

    my ( $dbh, $dir, $first_dumped_id, $last_dumped_id ) = @_;

    if ( !defined( $first_dumped_id ) )
    {
        $first_dumped_id = 0;
    }

    if ( !defined( $last_dumped_id ) )
    {
        my $max_stories_id = get_max_stories_id( $dbh );
        $last_dumped_id = $max_stories_id;
    }

    my $file_name = "$dir/story_words_" . $first_dumped_id . "_$last_dumped_id" . ".csv";
    open my $output_file, ">", $file_name
      or die "Can't open $file_name $@";

    Readonly my $select_query => "select stories_id, media_id, publish_day, stem, term, sum(stem_count)  as count from  " .
"   story_sentence_words where stories_id >= ? and stories_id <= ? group by stories_id, media_id, publish_day, stem, term"
      . "   order by stories_id, term                  ";

    $dbh->query_csv_dump( $output_file, " $select_query  limit 0 ", [ 0, 0 ], 1 );

    my $_stories_id_start = $first_dumped_id;
    my $_stories_id_stop  = $_stories_id_start + $_stories_id_window_size;

    while ( $_stories_id_start <= $last_dumped_id )
    {
        $dbh->query_csv_dump( $output_file, " $select_query ", [ $_stories_id_start, $_stories_id_stop ], 0 );

        last if ( $_stories_id_stop ) >= $last_dumped_id;

        ( $_stories_id_start, $_stories_id_stop ) =
          scroll_stories_id_window( $_stories_id_start, $_stories_id_stop, $last_dumped_id );
        print STDERR "story_id windows: $_stories_id_start -- $_stories_id_stop   (max_dumped_id: " . $last_dumped_id .
          ")  -- " .
          localtime() . "\n";

    }

    $dbh->disconnect;

    return [ $first_dumped_id, $last_dumped_id ];
}

sub dump_stories
{
    my ( $dbh, $dir, $first_dumped_id, $last_dumped_id ) = @_;

    my $file_name = "$dir/stories_" . $first_dumped_id . "_$last_dumped_id" . ".csv";
    open my $output_file, ">", "$dir/stories.csv"
      or die "Can't open $file_name: $@";

    $dbh->query_csv_dump(
        $output_file,
        " select stories_id, media_id, url, guid, title, publish_date, collect_date, full_text_rss from stories " .
          "   where stories_id >= ? and stories_id <= ? order by stories_id",<commit_msg>Fix story CSV file name.<commit_after>#!/usr/bin/perl -w

# create media_tag_tag_counts table by querying the database tags / feeds / stories

use strict;
use warnings;

BEGIN
{
    use FindBin;
    use lib "$FindBin::Bin/../lib";
    use lib "$FindBin::Bin/.";
}

use MediaWords::DB;
use DBIx::Simple::MediaWords;
use TableCreationUtils;
use Readonly;
use File::Temp qw/ tempfile tempdir /;
use Archive::Zip qw( :ERROR_CODES :CONSTANTS );
use File::Copy;
use List::Util qw(first max maxstr min minstr reduce shuffle sum);
use MediaWords::Controller::Dashboard;

use Getopt::Long;
use Date::Parse;
use Data::Dumper;
use Carp;
use Dir::Self;
my $_stories_id_window_size = 1000;

# base dir
my $_base_dir = __DIR__ . '/..';

sub get_max_stories_id
{
    my ( $dbh ) = @_;

    my $max_stories_id_row = $dbh->query( "select max(stories_id) as max_id from story_sentence_words" );

    my $max_stories_id = $max_stories_id_row->hash()->{ max_id };

    return $max_stories_id;
}

sub scroll_stories_id_window
{
    my ( $_stories_id_start, $_stories_id_stop, $max_stories_id ) = @_;

    $_stories_id_start = $_stories_id_stop + 1;
    $_stories_id_stop  = $_stories_id_start + $_stories_id_window_size - 1;

    $_stories_id_stop = min( $_stories_id_stop, $max_stories_id );

    return ( $_stories_id_start, $_stories_id_stop );
}

sub isNonnegativeInteger
{
    my ( $val ) = @_;

    return int( $val ) eq $val;
}

sub dump_story_words
{

    my ( $dbh, $dir, $first_dumped_id, $last_dumped_id ) = @_;

    if ( !defined( $first_dumped_id ) )
    {
        $first_dumped_id = 0;
    }

    if ( !defined( $last_dumped_id ) )
    {
        my $max_stories_id = get_max_stories_id( $dbh );
        $last_dumped_id = $max_stories_id;
    }

    my $file_name = "$dir/story_words_" . $first_dumped_id . "_$last_dumped_id" . ".csv";
    open my $output_file, ">", $file_name
      or die "Can't open $file_name $@";

    Readonly my $select_query => "select stories_id, media_id, publish_day, stem, term, sum(stem_count)  as count from  " .
"   story_sentence_words where stories_id >= ? and stories_id <= ? group by stories_id, media_id, publish_day, stem, term"
      . "   order by stories_id, term                  ";

    $dbh->query_csv_dump( $output_file, " $select_query  limit 0 ", [ 0, 0 ], 1 );

    my $_stories_id_start = $first_dumped_id;
    my $_stories_id_stop  = $_stories_id_start + $_stories_id_window_size;

    while ( $_stories_id_start <= $last_dumped_id )
    {
        $dbh->query_csv_dump( $output_file, " $select_query ", [ $_stories_id_start, $_stories_id_stop ], 0 );

        last if ( $_stories_id_stop ) >= $last_dumped_id;

        ( $_stories_id_start, $_stories_id_stop ) =
          scroll_stories_id_window( $_stories_id_start, $_stories_id_stop, $last_dumped_id );
        print STDERR "story_id windows: $_stories_id_start -- $_stories_id_stop   (max_dumped_id: " . $last_dumped_id .
          ")  -- " .
          localtime() . "\n";

    }

    $dbh->disconnect;

    return [ $first_dumped_id, $last_dumped_id ];
}

sub dump_stories
{
    my ( $dbh, $dir, $first_dumped_id, $last_dumped_id ) = @_;

    my $file_name = "$dir/stories_" . $first_dumped_id . "_$last_dumped_id" . ".csv";
    open my $output_file, ">", "$file_name"
      or die "Can't open $file_name: $@";

    $dbh->query_csv_dump(
        $output_file,
        " select stories_id, media_id, url, guid, title, publish_date, collect_date, full_text_rss from stories " .
          "   where stories_id >= ? and stories_id <= ? order by stories_id",
{'fraction_non_alphanumeric': 0.13146399887276314, 'fraction_numerical': 0.0033817105819360293, 'mean_word_length': 3.419676214196762, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 29, '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': '7376545', 'n_tokens_mistral': 2777, 'n_tokens_neox': 2669, 'n_words': 559}