#!/usr/bin/perl -w

######################################################################
# Copyright (C) 2004  Martijn van Oosterhout <kleptog@svana.org>     #
#                                                                    #
#      This program is free software; you can redistribute it and/or #
#      modify it under the terms of the GNU General Public License   #
#      as published by the Free Software Foundation; either version  #
#      2 of the License, or (at your option) any later version.      #
#                                                                    #
###################################################################### 

use strict;
use DBI;
use Data::Dumper;

my $db = DBI->connect( "dbi:Pg:dbname=kleptog", "kleptog", "", { AutoCommit => 1 } ) or die $DBI::errstr;

my $channels = $db->selectall_arrayref( "select channel_id, afid, name from channels" );

my $startday = int( time / 86400 );
my $endday = $startday + 10;

foreach my $channel (@$channels)
{
  my( $channel_id, $affiliation, $name ) = @$channel;

  print "Checking channel $name ($channel_id)...\n";

  # First, find the gaps...

  my $res = $db->selectall_arrayref( "select daynum * 86400 + time, duration, gdid from guide_data 
                                      where channel_id = ? and daynum between ? and ?
                                      order by daynum, time", undef,
                                   $channel_id, $startday, $endday );

  my $lasttime = 0;

  my @gaps = ();
  foreach my $show (@$res)
  {
    my($time,$duration,$gdid) = @$show;

    if( $lasttime == 0 ) { $lasttime = $time }

    if( $time > $lasttime )
    {
      push @gaps, [ $lasttime, $time-$lasttime ];
    }
    elsif( $time < $lasttime )
    {
      printf STDERR "*** ERROR ***: Overlap channel $channel_id, time $time (%d, %d:%02d), lasttime=$lasttime\n",
             int( $time / 86400 ), int( ($time % 86400) / 3600 ), int( ($time % 3600) / 60 );
      $db->do( "delete from guide_data where gdid = ?", undef, $gdid );
      next;
    }

    $lasttime = $time + $duration;
  }
  if( $lasttime == 0 )  # No programs yet? Try affiliations...
  {
    $lasttime = $startday * 86400;
  }

  if( $lasttime < $endday*86400 )
  {
    my $space = 86400 - ($lasttime % 86400);
    if( $space < 3600*8 ) { $space += 86400 }  # Always at least 8 hours...

    push @gaps, [ $lasttime, $space ];  # Fill the last day...
    $lasttime += $space;
  }

  while( $lasttime < ($startday+7)*86400 )   # Less then one week? Drastic measures...
  {
    push @gaps, [ $lasttime, 86400 ];
    $lasttime += 86400;
  }

  # @gaps is now a list of gaps

  print "Gaps are:\n";
  foreach my $gap (@gaps)
  {
    my( $time, $duration ) = @$gap;

    my( $daynum, $tod ) = (int($time/86400), $time % 86400);

    print "  [$time, $duration] ($daynum,$tod)\n";
  }
  print "===\n";
#  next;
  foreach my $gap (@gaps)
  {
    my( $gaptime, $gapduration ) = @$gap;

    printf "Filling gap [$gaptime,$gapduration] (%d,%d:%02d)\n", int( $gaptime / 86400 ), int( ($gaptime % 86400) / 3600 ), int( ($gaptime % 3600) / 60 );

    my $res;
    # Episodic is only used when copying a previous weeks data. We can copy non-episodic if its at the same time...
    if( not defined $affiliation or $affiliation eq "" )
    {
      $res = $db->selectall_arrayref( "select daynum * 86400 + time as start, duration, gdid, title,
                                           case when confirmed then 2 else 0 end + 1 as score
                                       from guide_data g
                                       where g.channel_id = ?
                                       and (daynum+7) * 86400 + time >= ? 
                                       and ? >= (daynum+7) * 86400 + time + duration 
                                       and daynum >= ?   -- optimisation
                                       and episodic
                                       order by start, score desc", undef,
                                       $channel_id, $gaptime, $gaptime + $gapduration, $startday - 10 );
    }
    else
    {
      # First query does affiliated channels, Seconds does this channel last week
      $res = $db->selectall_arrayref( "select daynum * 86400 + time as start, duration, gdid, title,
                                           case when g.confirmed then 2 else 0 end as score
                                       from guide_data g, channels a
                                       where g.channel_id = a.channel_id and a.afid = ?
                                       and daynum * 86400 + time >= ? and ? >=  daynum * 86400 + time + duration
                                       and daynum >= ?   -- optimisation

                                       union all

                                       select (daynum+7) * 86400 + time as start, duration, gdid, title,
                                           case when confirmed then 2 else 0 end + 1 as score
                                       from guide_data g
                                       where g.channel_id = ?
                                       and (daynum+7) * 86400 + time >= ? and ? >= (daynum+7) * 86400 + time + duration 
                                       and episodic
                                       and daynum >= ?   -- optimisation
                                       order by start, score desc", undef,
                                       $affiliation, $gaptime, $gaptime + $gapduration, $startday - 10,
                                       $channel_id, $gaptime, $gaptime + $gapduration, $startday - 10 );
    }
    my $lasttime = $gaptime;
    $db->begin_work;
    foreach my $show (@$res)
    {
      my( $time, $duration, $gdid, $title, $score ) = @$show;

      my $diff = ($time % 86400) - ($lasttime % 86400);  # Time-of-day differential

      next if( $diff > 18*3600 );  # Happens when we get two shows same time

      if( $diff < -86400/2 )
      { $diff += 86400 }

      next if( $diff < 0 );   # Overlaps, skip...

      $lasttime += $diff;
      printf "%d,%d,%d,%s,%d  diff=%d\n", @$show, $diff;

      if( $lasttime + $duration > $gaptime + $gapduration )
      {
        print "*** Eeek! Exceeded boundary. Last.\n";
      }
      printf "Inserting at %d (%d:%02d)\n", $lasttime%86400, int( ($lasttime % 86400) /3600 ), int( $lasttime % 3600 ) /60;
      InsertShow( $gdid, $channel_id, $lasttime );
      
      $lasttime += $duration;
    }
    $db->commit;
  }
#  exit;
}

# Eventually this will have to take into account episodes...
sub InsertShow
{
  my( $gdid, $channel_id, $time ) = @_;

  print "Inserting $gdid on $channel_id at $time\n";
  my $sth = $db->prepare( "select * from guide_data where gdid = ?");

  $sth->execute( $gdid );

  my $data = $sth->fetchrow_hashref;

  if( not defined $data->{gdid} )  # Error
  { die "Error\n"; }

#  print "Data=",Dumper( $data );

  # If it's a series
  if( defined $data->{series_id} )
  {
    my $sth2 = $db->prepare( "select * from series where series_id = ?" );
    $sth2->execute( $data->{series_id} );

    my $series = $sth2->fetchrow_hashref;

    if( not defined $series->{series_id} )
    { die "Error2\n" }

#    print "Series=",Dumper( $series );
    my $type = $series->{series_type};

    # Note, type 0, 1 and 4 won't ever really come here currently...
    # 0 = program
    # 1 = irregular, non-episodic
    # 2 = episodic, no episode title
    # 3 = episodic with title
    # 4 = movie

    $data->{genres} = $series->{genres};
    $data->{bits}   = $series->{bits};
    $data->{actor}  = $series->{actor};
    $data->{title}  = $series->{title};
    $data->{episodic} = ($type == 2 or $type == 3) ? 1 : 0;
    $data->{advisory} = $series->{advisory};

    if( $type == 2 )
    {
      $data->{episode} = '';
      $data->{description} = $series->{description};
    }
    elsif( $type == 3 )
    {
      $data->{episode} = '';
      $data->{description} = '';
    }
  }

#    print "New Data=",Dumper( $data );
  $db->do("insert into guide_data (channel_id,daynum,time,duration,title,episode,episodic,description,genres,bits,advisory,actor,
                                   series_id,confirmed) 
           values (?,?,?,?, ?,?,?,?, ?,?,?,?, ?,false)", undef,
           $channel_id, int($time/86400), $time % 86400, $data->{duration}, $data->{title}, $data->{episode}, 
           $data->{episodic}?'t':'f',
           $data->{description}, $data->{genres}, $data->{bits}, $data->{advisory}, $data->{actor}, $data->{series_id} );
}