#!/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;
my $self = "oztivo.pl";

use DBI;
use CGI;

sub ESC ($);

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

$db->do( "set client_encoding = latin9;" );

my $cgi = new CGI;

my $page = $cgi->param("page") || "channels";
my $type = $cgi->param("type") || "view";
my $data = $cgi->param("data") || '';

my %Setup = (
  "channels_view"     => \&channels_view,   # View all channels
  "channel_view"      => \&channel_view,    # View all days, one channel
  "channel_update"    => \&channel_update,    # View all days, one channel
  "channelday_update" => \&channelday_update,
  "channelday_view"   => \&channelday_view,  # View one day, one channel
  "show_view"         => \&show_view,        # Add/Edit show
  "show_update"       => \&show_update,    # Update a single show
  "rawdump_view"      => \&rawdump_view,   # Dump raw
  "rawdumpseries_view" => \&rawdumpseries_view,
  "rawdumpepisodes_view" => \&rawdumpepisodes_view,
  "series_view"       => \&series_view,    # Series editor
  "series_update"     => \&series_update,  # Series save

  "seriesshowings_view" => \&seriesshowings_view, 
  "seriesepisodes_view" => \&seriesepisodes_view, 
  "seriesepisodes_update" => \&seriesepisodes_update,

  "convertshow_view"  => \&convertshow_view,  # Converting a show...
  "convertshow_update" => \&convertshow_update,  # Adding new series...

  "convertquick_view" => \&convertquick_view,

  "searchseries_view" => \&searchseries_view, 
  "newseries_view" => \&newseries_view, 
);

if( $page =~ /^raw/ )
{
  print "Content-Type: text/plain\n\n";
}
else
{
  print "Content-Type: text/html\n\n";
  print "<title>OzTiVo TV Guide</title>\n";
}

if( defined $Setup{ $page."_update" } )
{
#  print "<!-- ${page}_update start data=[$data]-->\n";
  $Setup{ $page."_update" }->();
#  print "<!-- ${page}_update stop -->\n";
}
if( defined $Setup{ $page."_view" } )
{
#  print "<!-- ${page}_view start data=[$data]-->\n";
  $Setup{ $page."_view" }->();
#  print "<!-- ${page}_view stop -->\n";
}
else
{
  print "Sorry, not done yet ($page:$type:$data)\n";
  print $cgi->Dump;
}
$db->disconnect;
exit 0;

sub channels_view
{
  my %targets = map { $_ => 1 } qw(ACT NSW SA QLD VIC WA TAS);

  my $c = $db->selectall_arrayref( "select channel_id, state, city, code, name, serverid, filled, confirmed from channels where not hidden order by state, city, code", undef );

  print "<font size=+2>[channels] [<a href=$self?page=searchseries>series</a>]</font><br>\n";

  print "<h1>Australian TV Guide</h1>\n";

  print "<h2>Currently defined channels</h2>\n";
  print "Quick links: <a href=#Top>Top</a> ".join(" ", map { "<a href=#$_>$_</a>" } sort keys %targets)."<br>\n";
  print "<a name=Top></a>\n";
  print "<table>\n<tr><th>State<th>City<th>Code</th><th>Name<th>Server ID<th>Action</tr>\n";

  foreach my $chan (@$c)
  {
    my $fill = $chan->[6] || 0;
    my $conf = $chan->[7] || 0;

    # Colour is as follows (conf < fill):
    # fill=0, conf=0 => #ff0000
    # fill=1, conf=0 => #0000ff
    # fill=1, conf=1 => #00ff00

    my $r = 255 * (1-$fill);
    my $g = 255 * $conf;
    my $b = 255 * ($fill - $conf);

#    $r = ($r + 0x80) / 2;
#    $g = ($g + 0x80) / 2;
#    $b = ($b + 0x80) / 2;

    my $colour = sprintf "#%02X%02X%02X", $r, $g, $b;

    if( defined $targets{$chan->[1]} )
    {
      print "<a name=$chan->[1]></a>\n";
    }
    print "<tr><td>$chan->[1]<td>$chan->[2]<td><a href=$self?page=channel&type=view&data=$chan->[0]>$chan->[3]</a><td>$chan->[4]<td>$chan->[5]<td>[<a href=$self?page=channel&type=view&data=$chan->[0]>guide</a>] [edit]\n";
    printf "<td bgcolor=$colour><font color=white>%.0f%% filled, %.0f%% confirmed</font>\n", $fill*100, $conf*100;
  }
  print "</table>\n";
  print "[new]\n";
}

sub channel_view
{
  my $chan = $data;

  my $res = $db->selectall_arrayref( "select code from channels where channel_id = ?", undef, $chan );
  my $channame = $res->[0][0];

  my $condition = "channel_id = $chan";

  # Work out percentage convered
  my $query = "select to_char('1970-01-01'::date + daynum, 'Day, dd Mon YYYY') as d, daynum, sum(d) from 
                (select daynum, int4smaller( 86400 - time, duration ) as d 
                 from guide_data where %COND%
                union all 
                 select daynum+1, time+duration-86400 
                 from guide_data where time+duration > 86400 and %COND%) as x 
               where ('1970-01-01'::date + daynum) > 'now'::date group by daynum order by daynum;";

  my $q = $query;
  $q =~ s/%COND%/$condition/g;

  $res = $db->selectall_arrayref( $q, undef );   # Get full
  my %totalhash = map { $_->[1] => [ $_->[0], $_->[2] ] } @$res;

  $condition .= " and confirmed";

  $q = $query;
  $q =~ s/%COND%/$condition/g;

  $res = $db->selectall_arrayref( $q, undef );   # Get full

  my %confirmhash = map { $_->[1] => [ $_->[0], $_->[2] ] } @$res;

  print "<h1><a href=$self>Australian TV Guide</a> &gt; $channame</h1>\n";
  print "<h2>Data for $channame</h2>\n";
  print "<p>\n";
  print "<table><tr><th>Day<th>Filled<th>Confirmed</tr>\n";

  foreach my $day (sort keys %totalhash)
  {
    printf "<tr><td><a href=$self?page=channelday&type=view&data=$chan,$day>%s</a><td align=right>%.0f%%<td align=right>%.0f%%</tr>\n", $totalhash{$day}[0], $totalhash{$day}[1]/864, ($confirmhash{$day}[1]||0)/864;
  }
  print "</table>\n";
  print "<hr>\n";
  print "<form action=$self method=post enctype=",&CGI::MULTIPART,">\n";
  print "<input type=hidden name=page value=channel>\n";
  print "<input type=hidden name=type value=view>\n";
  print "<input type=hidden name=data value=$chan>\n";
  print "Upload datafile (Warren's format): <input type=file name=datafile size=50><br>\n<input type=submit name=submit value=Upload>\n";
  print "<i><b>Note: The file must be for this channel ($channame). The actual filename is ignored.</b></i><br>\n";
  print "<i>Under beta testing, tread carefully</i><br>\n";
  print "</form>\n";
}

sub searchseries_view
{
  print "<font size=+2>[<a href=$self?page=channels>channels</a>] [series]</font><br>\n";

  print "<h1><a href=$self>Australian TV Guide</a> &gt; Series Search</h1>\n";
  print "Here you can search for currently defined series in the system.<p>\n";

  my $term = $data || '';

  $term =~ s/[^a-zA-Z0-9]//g;

  {
    my $res = $db->selectall_arrayref( "select count(*) from series" );

    my $count = $res->[0][0];

    print "There are $count series currently in the database<p>\n";
  }
  print "<form method=post>\n";
  print "<input type=hidden name=page value=searchseries>\n";
  print "Enter search term: <input type=text name=data value=$term> <input type=submit value=Submit>\n";
  print "</form>\n";

  if( $term ne "" )
  {
    my $res = $db->selectall_arrayref( "select series_id, title, description, series_type from series where program_canonical(title) like '%$term%' order by title limit 50" );

    if( scalar( @$res ) == 50 )
    {
      print "Number of matches limited to 50\n";
    }
    else
    {
      print "Number of matches: ", scalar( @$res ), "\n";
    }

    my @types = ( "Program", "Irregular / Non-episodic", "Regular, no episode names", "Regular, Episodic", "Movie" );

    print "<table border=1>\n";
    print "<tr><th>Title<th>Type<th>Description</tr>\n";
    for my $series (@$res)
    {
      my( $series_id, $title, $descr, $series_type ) = @$series;

      $descr ||= '';
      if( $descr =~ /^\s*$/ )
      {
        $descr = "<i>None</i>";
      }
      print "<tr><td><a href=$self?page=series&data=$series_id,searchseries,$term>$title</a><td>$types[$series_type]<td>$descr</tr>\n";
    }
    print "</table>\n";
  }
  print "<hr>\n";
  print "<a href=$self?page=rawdumpseries>Raw Series Dump</a>\n";
}

sub newseries_view
{
  my($chan,$daynum,$time) = split /,/,$data;
  my ($channame,$dayname) = GetChannelNameDate( $chan, $daynum );

  print "<h1><a href=$self>Australian TV Guide</a> &gt; Series Search</h1>\n";
  print "<h2>Looking for series to insert on channel $channame, $dayname</h2>\n";

  my $term = $cgi->param("search") || '';

  $term =~ s/[^a-zA-Z0-9]//g;

  {
    my $res = $db->selectall_arrayref( "select count(*) from series" );

    my $count = $res->[0][0];

    print "There are $count series currently in the database<p>\n";
  }
  print "<form method=post>\n";
  print "<input type=hidden name=page value=newseries>\n";
  print "<input type=hidden name=data value=$data>\n";
  print "Enter search term: <input type=text name=search value=$term> <input type=submit value=Submit>\n";
  print "</form>\n";

  print "If you can't find the series you're looking for <a href=$self?page=channelday&data=$chan,$daynum>return to the guide</a><p>\n";
  if( $term ne "" )
  {
    my $res = $db->selectall_arrayref( "select series_id, title, description, series_type, normal_duration from series where program_canonical(title) like '%$term%' order by title limit 50" );

    if( scalar( @$res ) == 50 )
    {
      print "Number of matches limited to 50\n";
    }
    else
    {
      print "Number of matches: ", scalar( @$res ), "\n";
    }

    my @types = ( "Program", "Irregular / Non-episodic", "Regular, no episode names", "Regular, Episodic", "Movie" );

    print "<table border=1>\n";
    print "<tr><th>Title<th>Type<th>Description</tr>\n";
    for my $series (@$res)
    {
      my( $series_id, $title, $descr, $series_type, $duration ) = @$series;

      $descr ||= '';
      if( $descr =~ /^\s*$/ )
      {
        $descr = "<i>None</i>";
      }
      print "<tr><td><a href=$self?page=show&data=$chan,$daynum,$time,$duration,series,$series_id>$title</a><td>$types[$series_type]<td>$descr</tr>\n";
    }
    print "</table>\n";
  }
  print "<hr>\n";
  print "<a href=$self?page=rawdumpseries>Raw Series Dump</a>\n";
}

sub channel_update
{
  $page = "channel";
  my $chan = $cgi->param("data");

  return if not defined $cgi->param("submit");

  my $fh = $cgi->upload('datafile');
  if( not defined $fh ) 
  {
    print "File upload didn't work\n[".$cgi->param("datafile")."]\n";
#    print $cgi->uploadInfo('datafile');
    exit;
  }
#  print "<pre>\n";
  my %hash;
  $db->begin_work;

  my $error = 0;

  my $daynum;

  while( <$fh> )
  {
    print "<tt>$_</tt><br>\n";
    chomp;
    s/[\r\n]//g;
    if( $_ eq "" )
    {
#      print "</pre>\n";
      if( defined $hash{description} )
      {
        $hash{description} =~ s/&nbsp;/ /g;
        $hash{description} =~ s/&lt;/</g;
        $hash{description} =~ s/&gt;/>/g;
        $hash{description} =~ s/&quot;/'/g;
        $hash{description} =~ s/&amp;/&/g;
        $hash{description} =~ s/ +/ /g;
      }

      $daynum = $hash{daynum};
      if( InsertProgram( $chan, \%hash ) )
      { print "Quitting...<br>\n"; $error = 1; last }
      %hash = ();
#      print "<pre>\n";
    }
    elsif( /^(\w+): +(.*)/ )  # Line
    {
      my( $field, $value ) = ($1,$2);
      $field = lc $field;
      $value =~ s/\s+/ /g;
      $value =~ s/ +$//;
      $hash{$field} = $value;
    }
    else
    {
      print "<font color=red>Unrecognisable line [$_]</font>\n";
      exit;
    }
  }
  $db->commit;
  if( $error )
  {
    print "<h1>Failed... Go back and retry</h1>\n";
  }
  else
  {
    print "<h1>End... <a href=$self?page=channelday&data=$chan,$daynum>Next</a></h1>\n";
  }
#  print "</pre>\n";
  exit;
}

sub InsertProgram
{
  my $chan = shift;
  my $hash = shift;

  if( not defined $hash->{title} or
      not defined $hash->{daynum} or
      not defined $hash->{time} or
      not defined $hash->{duration} )
  {
    print "<font color=red>Missing mandatory field (title,daynum,time,duration)</font>\n";
    return 1;
  }

  my( $channame, $dayname ) = GetChannelNameDate( $chan, $hash->{daynum} );
  my $timestr = GetTimeString( $hash->{time} );

  print "<p><b>$channame, $dayname, $timestr: $hash->{title}: $hash->{episode}</b><br>\n";

  if( $hash->{duration} > 8*3600 )
  {
    print "<font color=red>Show too long > 8 hours</font>\n";
    return 0;
  }

  my $time = $hash->{daynum} * 86400 + $hash->{time};

  my $optimise = $hash->{daynum}-2;  # For use to trick the optimiser...

  # Look for overlapping shows
  my $res = $db->prepare( "select * from guide_data where daynum*86400 + time + duration > $time and $time+$hash->{duration} > daynum*86400 + time and channel_id = $chan and daynum >= $optimise" );
  $res->execute();

  my $matched_program;
  my $series;

  while( my $match = $res->fetchrow_hashref )
  {
    print "Matching [$match->{title}] @ ($match->{daynum},$match->{time}) for $match->{duration}<br>\n";
    # Also includes partial matches, if $hash is a substr of $match
    my $matchlevel = MatchProgram( $match->{title}, $hash->{title} );
    if( $matchlevel > 0 and not defined $matched_program )   # Sorta match
    {
      print "Program Name matches<br>\n";
#      if( defined $match->{series_id} )
#      {
#        $series = GetSeries( $match->{series_id} );
#      }
      # Match is in database, Hash is the imported one
      for my $field (qw(genres episode actor description))
      {
        $hash->{$field} ||= "";
        $match->{$field} ||= "";
        print "Checking $field: [$hash->{$field}] [$match->{$field}]<br>\n";
        if( $hash->{$field} !~ /^\s*$/ )
        {
          print "Updating $field...<br>\n";
          $match->{$field} = $hash->{$field};
        }
      }
      if( $match->{daynum}   == $hash->{daynum} and
          $match->{time}     == $hash->{time}   and
          $match->{duration} == $hash->{duration} and
          $matchlevel > 1 )  # Only on full match
      {
        $match->{confirmed} = 1;
        print "All details match, marking confirmed<br>\n";
      }
      else
      {
        $match->{daynum}   = $hash->{daynum};
        $match->{time}     = $hash->{time};
        $match->{duration} = $hash->{duration};
      }
      $matched_program = { %$match };   # Take copy
    }
    else
    {
      print "Will replace this program<br>\n";
      if( $match->{confirmed} )
      {
        print "<font color=red>*** Sorry, is confirmed... next...</font><br>\n";
        return 0;
      }
      $db->do( "delete from guide_data where gdid = ?", undef, $match->{gdid} );
    }
  }
  my @fields = qw(daynum time duration title episodic episode description genres bits advisory actor series_id confirmed);
  if( defined $matched_program )   # Do update
  {
    print "Updating program<br>\n";
    $matched_program->{episodic} = $matched_program->{series_id} ? 't' : 'f';
    $matched_program->{confirmed} = $matched_program->{confirmed} ? 't' : 'f';
    my $query = "update guide_data set ".join(", ", map { "$_ = ?" } @fields)." where gdid = ?";
    $db->do( $query, undef, (map { $matched_program->{$_} } @fields), $matched_program->{gdid} ) or return 1;
  }
  else
  {
    print "Inserting program<br>\n";
    $hash->{confirmed} = 'f';
    $hash->{episodic} ||= 'f';
    my $query = "insert into guide_data (channel_id, ".join(",",@fields).") values (?,".join(",", map { "?" } @fields).")";
    if( $db->do( $query, undef, $chan, (map { $hash->{$_} } @fields) ) != 1 )
    {
      print "<font color=red>Database error: ",$db->errstr,"<br>Contact administrator</font><br>\n";
      return 1;
    }
  }
  return 0;
}

# Matches two program
# 2 = exact match
# 1 = partial match (B is in A)
# 0 = no match
sub MatchProgram
{
  my( $a, $b ) = @_;

  $a =~ s/[^\w]//g;
  $b =~ s/[^\w]//g;

  if( $a eq $b ) { return 2 }

  if( index( $a, $b ) >= 0 )
  { return 1; }

  return 0;
}

sub channelday_update
{
  my ($chan,$daynum) = split /,/,$data;

  ######### BEGIN UPDATE ##########
  if( defined $cgi->param("deleteid") and $cgi->param("deleteid") ne "" )
  {
    my $gdid = $cgi->param("deleteid");

    $db->do( "delete from guide_data where gdid = ?", undef, $gdid );
  }
  if( defined $cgi->param("suggest") and $cgi->param("suggest") ne "" )
  {
    $data = $cgi->param("suggest");

    $page = "show";
  }
  {
    my @confirmed = $cgi->param("confirm");
    print "<!-- ", join(",",@confirmed), " -->\n";
    if( scalar(@confirmed) )
    {
      my $res = GetChannelDay($chan,$daynum);
      $db->begin_work;
      foreach my $show (@$res)
      {
        my $gdid = $show->[0];
        my $c = scalar( grep { $_ == $gdid } @confirmed );
        if( $c != $show->[7] )
        {
          $show->[7] = $c;
          $db->do( "update guide_data set confirmed = ? where gdid = ?", undef, $c?'t':'f', $gdid );
        }
      }
      $db->commit;
    }
  }
  my $goto = $cgi->param("gotopage");

  if( $goto ne "" )
  {
    ($page,$data) = split /,/,$goto,2;
  }
}

sub channelday_view
{
  my ($chan,$daynum) = split /,/,$data;

  my( $channame, $dayname ) = GetChannelNameDate( $chan,$daynum );

  print "<h1><a href=$self>Australian TV Guide</a> &gt; <a href=$self?page=channel&type=view&data=$chan>$channame</a> &gt; $dayname</h1><p>\n";
  print <<EOF;
<i>Tip: To avoid your guide changes being overwritten by some jerk uploading a file, mark a show confirmed...</i>
<form action=$self method=post>
<input type=hidden name=page value=channelday>
<input type=hidden name=type value=edit>
<input type=hidden name=data value=$chan,$daynum>
<input type=hidden name=gotopage value="">
<input type=hidden name=deleteid value="">
<input type=hidden name=suggest value="">
<script language=javascript>
function DeleteShow(gdid)
{
  if( confirm( "Are you sure?" ) )
  {
    document.forms[0].deleteid.value = gdid;
    document.forms[0].submit();
  }
}
function Suggest(str)
{
  document.forms[0].suggest.value = str;
  document.forms[0].submit();
}
function GotoPage(page,type,data)
{
  document.forms[0].gotopage.value = page + "," + data;
  document.forms[0].submit();
}
function GotoDay(daynum)
{
  document.forms[0].data.value = "$chan," + daynum;
  document.forms[0].submit();
}
</script>
<input type=submit value="Update">
EOF

  my $res = GetChannelDay( $chan, $daynum );

  ######### BEGIN DISPLAY #########
  print "<table>\n";
  printf "<tr><td colspan=3 align=left><a href=javascript:GotoDay(%d)>&lt;&lt; Previous Day</a><td colspan=2 align=right><a href=javascript:GotoDay(%d)>Next Day &gt;&gt;</a></tr>\n",
            $daynum-1, $daynum+1;
  print "<tr bgcolor=#e0e0e0><th>Time<th>Duration<th>Title / Episode<th>Confirm<th>Action<th>Description</tr>\n";

  my( $lastday, $lasttime ) = ($daynum,0);
  foreach my $show (@$res)
  {
    my( $gdid, $dynum,$time,$dur,$title,$episodic,$episode,$confirmed,$series_id,$series_type,$descr,$genres,$season,$epnum ) = @$show;

    if( defined ($series_id) and not $episodic and ($series_type == 2 or $series_type == 3) )
    {
      $db->do( "update guide_data set episodic = 't' where gdid = ?", undef, $gdid );
      $episodic = 1;
    }
    if( $dynum == $lastday and $lasttime < $time )
    {
      my $suggests = $db->selectall_arrayref( "select distinct on (title) gdid, title from guide_data 
                                              where channel_id = ?
                                              and (daynum = ?-1 or daynum = ?-7) 
                                              and ? < time+duration and time < ?
                                              order by title", undef,
                                              $chan, $lastday, $lastday, $lasttime, $time );

      printf "<tr bgcolor=#ff8080><td>%02d:%02d<td align=right>%d:%02d<td colspan=2>Add [<a href=javascript:Suggest('$chan,$lastday,$lasttime,%d')>show</a>] ".
                "[<a href=$self?page=newseries&type=new&data=$chan,$lastday,$lasttime>series</a>] [<a href=$self?page=newmovie&type=new&data=$chan,$lastday,$lasttime>movie</a>]\n<td colspan=2>\n",
            int($lasttime / 3600), int($lasttime / 60) % 60,
            int( ($time-$lasttime)/3600 ), int( ($time-$lasttime)/60 ) % 60,
            $time-$lasttime;

      foreach my $suggest (@$suggests)
      {
        my( $gdid, $title ) = @$suggest;

        my $duration = $time - $lasttime;
        print "Suggest: <a href=javascript:Suggest('$chan,$dynum,$lasttime,$duration,show,$gdid')>$title</a><br>\n";
      }
    }

    printf "<tr bgcolor=#%s><td>%s%02d:%02d<td align=right>%d:%02d<td>%s %s %s %s<td><input type=checkbox name=confirm value=%d %s><td><nobr>[<a href=javascript:DeleteShow($gdid)>del</a>] [<a href=javascript:Suggest('$chan,$dynum,$time,$dur,show,$gdid')>edit</a>]</nobr>\n",
            $confirmed ? "bbffbb" : "bbbbff",
            ($dynum < $daynum ? "Prev Day<br>" : ""),
            int($time / 3600), int( $time / 60 ) % 60,
            int($dur / 3600), int( $dur / 60 ) % 60,
            $title,
            defined($series_id) ? "[<a href=javascript:GotoPage('series','view','$series_id,channelday,$chan,$daynum')>series</a>]" : "",
            defined($season) ? "#$season.$epnum" : "",
            (defined($series_type) and ($series_type==1 or $series_type==3))? $episode || '<i>Unknown episode</i>' : '',
            $gdid,
            $confirmed ? "checked" : "";

    if( not defined($series_id) )
    {
      my $series = $db->selectall_arrayref( "select series_id from series where title = ? and series_type in (2,3)", undef, $title );
      if( scalar( @$series) )
      {
        print "[<a href=javascript:GotoPage('convertquick','view','$gdid,$series->[0][0],channelday,$chan,$daynum')>Make part of matching series</a>]\n";
      }
    }

    print "<td>";
    if( not defined ($descr) or $descr eq "" )
    { print "<i>No description</i>" }
    else
    { print substr($descr,0,40), length($descr)>40 ? "..." : "" }


    $lastday = $dynum;
    $lasttime = $time+$dur;

    if( $lasttime > 86400 )
    {
      $lastday++;
      $lasttime -= 86400;
    }
  }
  if( $lastday == $daynum and $lasttime < 86400 )
  {
    my $remain = 86400 - $lasttime;
    if( $remain > 4*3600 ) { $remain = 4*3600 }

    {
      my $suggests = $db->selectall_arrayref( "select distinct on (title) gdid, title from guide_data 
                                              where channel_id = ?
                                              and (daynum = ?-1 or daynum = ?-7) 
                                              and ? < time+duration and time < ?
                                              order by title", undef,
                                              $chan, $lastday, $lastday, $lasttime, $lasttime+$remain );

      printf "<tr bgcolor=#ff8080><td>%02d:%02d<td align=right>%d:%02d<td colspan=2>Add [<a href=javascript:Suggest('$chan,$lastday,$lasttime,%d')>show</a>] ".
                "[<a href=$self?page=newseries&type=new&data=$chan,$lastday,$lasttime>series</a>] [<a href=$self?page=newmovie&type=new&data=$chan,$lastday,$lasttime>movie</a>]\n<td colspan=2>\n",
            int($lasttime / 3600), int($lasttime / 60) % 60,
            int( ($remain)/3600 ), int( ($remain)/60 ) % 60,
            $remain;

      foreach my $suggest (@$suggests)
      {
        my( $gdid, $title ) = @$suggest;

        my $duration = $remain;
        print "Suggest: <a href=javascript:Suggest('$chan,$daynum,$lasttime,$duration,show,$gdid')>$title</a><br>\n";
      }
    }
#    printf "<tr><td>%02d:%02d<td align=right>%d:%02d<td colspan=3>Add [<a href=javascript:Suggest('$chan,$daynum,$lasttime,1800')>show</a>] ".
#                "[<a href=$self?page=newseries&type=new&data=$chan,$lasttime>series</a>] [<a href=$self?page=newmovie&type=new&data=$chan,$lasttime>movie</a>] </tr>\n",
#          int($lasttime / 3600), int($lasttime / 60) % 60,
#          int( (86400-$lasttime)/3600 ), int( (86400-$lasttime)/60 ) % 60;
  }

  printf "<tr><td colspan=3 align=left><a href=javascript:GotoDay(%d)>&lt;&lt; Previous Day</a><td colspan=2 align=right><a href=javascript:GotoDay(%d)>Next Day &gt;&gt;</a></tr>\n",
            $daynum-1, $daynum+1;
  print "</table>\n<input type=submit value='Update'>\n</form>\n";
  print "<hr>\n<a href=$self?page=rawdump&type=view&data=$chan,$daynum>Raw Data Dump</a>\n";
}

sub show_view
{
  my( $chan, $daynum, $time, $duration, $source, $gdid ) = split /,/,$data;

  my ($channame,$dayname) = GetChannelNameDate( $chan, $daynum );

  my $showtime = sprintf "%02d:%02d", int( $time / 3600 ), int( ($time % 3600)/60 );
  my (%data,%seriesdata,$episodedata);
  my $origgdid = '';

  if( defined $source )
  {
    if( $source eq "show" )
    {
      $origgdid = $gdid;
      %data = %seriesdata = GetProgram( $gdid );

      if( defined $data{series_id} )  # If the show is based on a series, go there...
      {
#        $source = "series";
#        $gdid = $data{series_id};
        %seriesdata = GetSeries( $data{series_id} );
        $episodedata = GetEpisodes( $data{series_id} );
      }
    }
    if( $source eq "series" )
    {
      %seriesdata = GetSeries( $gdid );
      $episodedata = GetEpisodes( $seriesdata{series_id} );
    }
  }

  if( scalar( keys %data ) == 0 )
  {
    %data = %seriesdata;
  }

  if( defined($data{duration}) and $duration > $data{duration} )
  { $duration = $data{duration} }
  my $showduration = int( $duration / 60 );
  # fields are (from db): title, episodic, episode, description, genres, bits, advisory, actor

  print <<EOF;
<body onload=SetEpisode()>
<h1><a href=$self>Australian TV Guide</a> &gt; <a href=$self?page=channel&type=view&data=$chan>$channame</a> &gt; <a href=$self?page=channelday&type=view&data=$chan,$daynum>$dayname</a> &gt; Edit</h1>
<h2>Editing show</h2>
<form action=$self method=post onsubmit=EnableControl()>
<script language=javascript>
function SetEpisode()
{
  var obj = document.forms[0].episode_id;
  var t = obj.options[obj.selectedIndex].text;
  if( obj.value == 0 )
  {
    // If name of unnumbered episode in string...
    if( t.indexOf("(") > 1 )
    {
      t = t.substr( t.indexOf("(") + 1 );
      document.forms[0].episode.value = t.substr( 0, t.length-1 );
    }
    document.forms[0].episode.disabled = false;
  }
  else
  {
    document.forms[0].episode.disabled = true;
    document.forms[0].episode.value = t.substr( t.indexOf(" ") + 1 );
  }
//  confirm( obj.value );
}
// Enable control before submit to actually save episode...
function EnableControl()
{
  document.forms[0].episode.disabled = false;
}

</script>
<input type=hidden name=origgdid value=$origgdid>
<input type=hidden name=page value=show>
<input type=hidden name=type value=view>
<table>
<tr><td>Channel:<td>$channame <input type=hidden name=channel_id value=$chan>
<tr><td>Date:<td>$dayname <input type=hidden name=daynum value=$daynum>
<tr><td>Time:<td><input size=5 width=5 name=time value=$showtime>
<tr><td>Duration:<td><input size=5 width=5 name=duration value=$showduration> minutes
EOF

  if( defined $data{series_id} )   # If series, can't edit...
  {
    print "<input type=hidden name=series_id value=$data{series_id}>\n";
    print "<tr><td>Title<td>$data{title} <input type=hidden name=title size=50 value=\"@{[ESC $data{title}]}\">\n";
    if( $seriesdata{series_type} == 1 or $seriesdata{series_type} == 3 )
    {
      print "<tr><td>Episode<td><nobr><input type=text name=episode value=\"@{[ESC $data{episode}]}\" size=35>\n";
      if( scalar( keys %$episodedata ) > 0 )
      {
        $data{episode_id} ||= 0;

        my @list = map { [ $_->{episode_id}, "#".$_->{season}.".".$_->{episodenum}."  ".$_->{episode} ] }
                   sort { $a->{season} <=> $b->{season}  or  $a->{episodenum} <=> $b->{episodenum} }
                   values %$episodedata;
        print "<select name=episode_id onchange=SetEpisode()>\n<option value=0>Unnumbered episode ";
        if( $data{episode} ne "" )
        { print "($data{episode})" };

        print "\n";
        print map { "<option value=$_->[0]".($_->[0] == $data{episode_id}?" selected":"").">$_->[1]\n" } @list;
        print "</select>\n";
      }
      print "</nobr>\n";
      print "<tr><td colspan=2><i>Tip: If this series doesn't have episode titles, edit the series type</i>\n";
    }
    else
    {
      print "<tr><td>Episode<td><i>None</i>\n";
      print "<tr><td colspan=2><i>Tip: If this series has episode titles, edit the series type</i>\n";
    }
    print "<tr><td colspan=2><i>Tip: Editing these values only affects this showing. To change all future showings you need to edit the series.</i>\n";
  }
  else
  {
    print "<tr><td>Title<td><input type=text name=title size=50 value=\"@{[ESC $data{title}]}\">\n";
    print "<tr><td>Episode<td><i>N/A (not a series)</i> <a href=$self?page=convertshow&data=$origgdid,show,$data>Wait, this is a series...</a>\n";
  }
  print "<tr><td>Description<td><textarea name=description cols=35 rows=4>$data{description}</textarea>\n";

  foreach my $field ( qw( episodic genres advisory actor ) )
  {
    print "<tr><td>$field<td>$data{$field} <input type=hidden name=$field value=$data{$field}>\n";
  }
  print "</table>\n";

  my @bits = qw( 
                        CloseCaption Stereo Subtitle JoinedInProgress
                        CableInTheClassroom Sap Blackout Intercast ThreeD
                        Repeat Letterbox SexRating ViolenceRating
                        LanguageRating DialogRating FvRating
  );
  print "<table>\n";
  $data{bits} ||= 0;
  foreach my $i (0..15)
  {
    if( ($i%4) == 0 ) { print "<tr>\n" }
    print "<td><input type=checkbox name=bits value=$i ",($data{bits} & (1<<$i))?"checked":"", ">$bits[$i]\n";
  }
  print "</table>\n";
  print "<h3>Update type</h3>\n";
  print "<input type=radio name=updatetype value=2>This is correct, update conflicting shows<br>\n";
  print "<input type=radio name=updatetype value=1 checked>This is correct, update conflicting shows as long as they're not confirmed<br>\n";
  print "<input type=radio name=updatetype value=0>I'm not sure, show any conflicting shows<br>\n";
  print "<input type=submit value=Update> <input type=submit name=cancel value=Cancel>\n";
  print "</form>\n";
}

sub show_update
{
  return if not defined $cgi->param("updatetype");
  my $chan = $cgi->param('channel_id');
  my $daynum = $cgi->param('daynum');

  if( defined $cgi->param("cancel") )
  {
    $page = "channelday";
    $data = "$chan,$daynum";
    return;
  }

  my $time = $cgi->param('time');
  {
    my($h,$m) = split /:/,$time;
    $time = $h*3600 + $m*60;
  }
  my $duration = $cgi->param('duration') * 60;
  {
    my $bits = 0;
    map { $bits += 1 << $_ } $cgi->param("bits");

    $cgi->param( "bits", $bits );
  }
  my $update = 0;
  my $change = 0;
  my $changeconfirmed = 0;

  # Look for overlapping shows
  my $overlaps = $db->selectall_arrayref( "select gdid, title, daynum, time, duration, confirmed from guide_data 
                                           where channel_id = ?
                                           and ( (daynum = ? and ? < time+duration and time < ?)
                                              or (daynum = ?-1 and 86400+? < time+duration)
                                              or (daynum = ?+1 and 86400+time < ? ) )
                                           order by gdid", undef,
                                           $chan, $daynum, $time, $time+$duration,
                                                  $daynum, $time,
                                                  $daynum, $time+$duration );

  my $fulltime = $daynum * 86400 + $time;
  print "<table border=1>\n<tr><th>Show<th colspan=3>From<th colspan=3>To\n";

  my @queries;
  foreach my $over (@$overlaps)
  {
    if( $over->[0] == $cgi->param("origgdid") )
    { $update = 1 }
    elsif( $over->[5] )
    { $changeconfirmed = 1 }
    else
    { $change = 1 }

    my $overfulltime = $over->[2] * 86400 + $over->[3];

    my $newfulltime = $overfulltime;
    my $newduration = $over->[4];

    if( $overfulltime < $fulltime )
    { $newduration = $fulltime - $overfulltime }
    else
    {
      $newfulltime = $fulltime + $duration;
      $newduration = $overfulltime + $newduration - $newfulltime;
    }
    if( $newduration <= 0 )
    {
      $newfulltime = $newduration = 0;
    }
    push @$over, $newfulltime, $newduration;
    if( $over->[0] == $cgi->param("origgdid") )   # replacing itself
    {
      print "<tr><td>$over->[1]<td>$over->[2]<td>$over->[3]<td>$over->[4]".
              "<td span=3>Updated\n";

      push @queries, [ "update guide_data set confirmed = false, daynum = ?, time = ?, duration = ?, 
                                              title = ?, episode = ?, description = ?, bits = ?, episode_id = ? where gdid = ?",
                                              int( $fulltime / 86400 ), $fulltime % 86400,
                                              $duration, $cgi->param("title"), $cgi->param("episode")||'', $cgi->param("description"),
                                              $cgi->param("bits"), $cgi->param("episode_id"),
                                              $over->[0] ] ;
    }
    elsif( $newduration == 0 )   # Completely covers show
    {
      push @queries, [ "delete from guide_data where gdid = ?", $over->[0] ];
      print "<tr><td>$over->[1]<td>$over->[2]<td>$over->[3]<td>$over->[4]".
              "<td span=3>Replaced\n";
    }
    else
    {
      push @queries, [ "update guide_data set confirmed = false, daynum = ?, time = ?, duration = ? where gdid = ?", 
                                              int( $newfulltime / 86400 ), $newfulltime % 86400,
                                              $newduration, $over->[0] ] ;
      print "<tr><td>$over->[1]<td>$over->[2]<td>$over->[3]<td>$over->[4]".
                "<td>",int($newfulltime/86400),"<td>",$newfulltime%86400,"<td>",$newduration,"\n";
    }
  }

  if( not $update )
  {
    push @queries, [ "insert into guide_data (channel_id,confirmed,daynum,time,duration,title,episode,description,series_id,episode_id,episodic,bits)
                                  values (?,?,?,?,?,?,?,?,?,?,?,?)",
                     $chan, 'f', int($fulltime / 86400), $fulltime % 86400, $duration,
                     $cgi->param("title"), $cgi->param("episode")||undef, $cgi->param("description"), $cgi->param("series_id")||undef,
                     $cgi->param("episode_id")||undef,
                     defined( $cgi->param("series_id") ) ? 't' : 'f', $cgi->param("bits") ];
  }

  if( not $update and not $change and not $changeconfirmed )
  {
    print "<tr><td colspan=7>No other programs affected\n";
  }
  print "</table>\n";
  if( $cgi->param( "updatetype" ) == 1 )
  {
    if( $changeconfirmed )
    {
      print "<h1>Overlap detected on confirmed show</h1>\n";
      exit;
    }
  }

  if( $cgi->param( "updatetype" ) == 0 )
  {
    if( $changeconfirmed || $change )
    {
      print "<h1>Overlap detected</h1>\n";
      exit;
    }
  }

  # Type 2 is do anyway, so just go ahead

  $db->begin_work;
  foreach my $query (@queries)
  {
    print join(" | ", @$query),"<br>\n";
    splice @$query, 1, 0, undef;
    $db->do( @$query );
  }
#  my @keys = $cgi->param();
#  print "<ul>\n";
#  foreach my $key (@keys)
#  {
#    print "<li>$key => ",$cgi->param($key),"\n";
#  } 
#  print map { "<li>".join( " | ", @$_ ) } @queries;
#  print "</ul>\n";

  print "<a href=$self?page=channelday&type=view&data=$chan,$daynum>Back to schedule listing</a>\n";
  $db->commit;
  exit;
}

sub convertquick_view
{
  my( $gdid, $series_id, $olddata ) = split /,/,$data,3;

  my $force = 0;
  if( $series_id =~ /^F(\d+)/ )
  {
    $force = 1;
    $series_id = $1;
  }

  my %data = GetProgram( $gdid ); 

  my $res = $db->selectall_arrayref( "select title, series_type, series_id from series where series_id = ?", undef, $series_id );

  if( scalar(@$res) == 0 )
  { print "Internal error: Invalid convert\n"; exit }
  if( $res->[0][0] ne $data{title} and $force == 0 )
  { print "Internal error: Bad title convert\n"; exit }

  # Well, do the conversion
  $db->do( "update guide_data set series_id = ?, title = ?, episodic = ? where gdid = ?", undef,
               $res->[0][2], $res->[0][0], ($res->[0][1] == 2 || $res->[0][1] == 3)?'t':'f', $gdid );

  ( $page, $data ) = split /,/,$olddata,2;
  return channelday_view();   #  Hack...
}

sub convertshow_view
{
  my ($gdid, $olddata) = split /,/, $data, 2;

  my %data = GetProgram( $gdid );  # Alot of the fields are the same...

  print "<h1>Converting program to series</h1>\n";

  print <<EOF;
First I will present a list of possibly matching series already in the
database. If it is one of these, just select it and the conversion will
occur. Otherwise you have the option of creating the series below. Please
avoid making series where not necessary, it's a great way to break Season
Passes.
<p>
If it is one of these series, just click it:
<table>
<tr><th>Title<th>Description
EOF

  {
    my $title = $data{title};
    my $match = 0;

    my @a = ($title);
    while( $title =~ s/[^a-zA-Z0-9]+[0-9a-zA-Z]+$//g )
    {
      push @a, $title;
    }

    my %unique;

    for my $a (@a)
    {
      $a = lc $a;
      $a =~ s/([^0-9a-z])//g;   # Canonicalise it

      my $res = $db->selectall_arrayref( "select series_id, title, description from series where program_canonical(title) = ?", undef, $a );

      foreach my $show (@$res)
      {
        next if defined $unique{$show->[0]};
        $unique{$show->[0]} = 1;
        print "<tr><td><a href=$self?page=convertquick&data=$gdid,F$show->[0],channelday,$data{channel_id},$data{daynum}>$show->[1]</a><td>$show->[2]\n";
        $match = 1;
      }

      $res = $db->selectall_arrayref( "select series_id, title, description from series where program_canonical(title) like ?", undef, $a."%" );

      foreach my $show (@$res)
      {
        next if defined $unique{$show->[0]};
        $unique{$show->[0]} = 1;
        print "<tr><td><a href=$self?page=convertquick&data=$gdid,F$show->[0],channelday,$data{channel_id},$data{daynum}>$show->[1]</a><td>$show->[2]\n";
        $match = 1;
      }
    }

    if( not $match )
    {
      print "<tr><td colspan=2><i>No matches found</i>\n";
    }
  }
print <<EOF;
</table>
<p>
Otherwise, fill in the values below...
<p>
EOF

  # Note, this bit is copied quite a bit from the series_view function.
  # Maybe I should look at templates, no? :)
  
  $data{series_type} = 2;
  $data{episode} ||= $data{title};   # Default to write back

  if( $data{title} =~ /^([^:]+):\s+(.*)$/ )
  {
    $data{title} = $1;
    $data{episode} = $2;
    $data{series_type} = 3;
  }

  my $showduration = int( $data{duration} / 60 );

  print "<form action=$self method=post>\n";
  print "<input type=hidden name=page value=convertshow>\n";
  print "<input type=hidden name=type value=view>\n";
  print "<input type=hidden name=data value=$data>\n";  # Where to go after submit
  print "<table>\n";
  print "<tr><td>Title<td><input type=text name=title size=50 value=\"@{[ESC $data{title}]}\">\n";
  print "<input type=hidden name=episode value=\"@{[ESC $data{episode}]}\">\n";
  print "<tr><td>Series Type<td><select name=series_type>\n";
  print "  <option value=1 ",($data{series_type} == 1 ? 'selected' : ''),">Irregular / Non-episodic\n";
  print "  <option value=2 ",($data{series_type} == 2 ? 'selected' : ''),">Regular, no episode names (News,etc)\n";
  print "  <option value=3 ",($data{series_type} == 3 ? 'selected' : ''),">Regular, Episodic\n";
  print "</select><br>\n";

  print "<tr><td>Usual Duration:<td><input size=5 width=5 name=duration value=$showduration> minutes\n";
  print "<tr><td>Description<td><textarea name=description cols=35 rows=4>$data{description}</textarea>\n";

  foreach my $field ( qw( advisory actor ) )
  {
    print "<tr><td>$field<td>$data{$field} <input type=hidden name=$field value='$data{$field}'>\n";
  }
  
  print "<tr><td>Genres<td><input type=text size=50 disabled name=genres2 value='$data{genres}'><input type=hidden name=genres value='$data{genres}'><br>\n";
  GenreBoxes( $data{genres} );
  
  print "</table>\n";

  my @bits = qw( 
                        CloseCaption Stereo Subtitle JoinedInProgress
                        CableInTheClassroom Sap Blackout Intercast ThreeD
                        Repeat Letterbox SexRating ViolenceRating
                        LanguageRating DialogRating FvRating
  );
  print "<table><!-- bits=$data{bits} -->\n";
  $data{bits} ||= 0;
  foreach my $i (0..15)
  {
    if( ($i%4) == 0 ) { print "<tr>\n" }
    print "<td><input type=checkbox name=bits value=$i ",($data{bits} & (1<<$i))?"checked":"", ">$bits[$i]\n";
  }
  print "</table>\n";
  print "<input type=submit name=add value='Add series'><input type=submit name=cancel value=Cancel>\n";
  print "</form>\n";

}

sub convertshow_update
{
  my ($gdid, $oldpage, $olddata) = split /,/,$data,3;
  print "convertshow_update..[$data].\n";

  if( defined $cgi->param("cancel") )
  {
    # Should go back to old page...
    $page = $oldpage;
    $data = $olddata;
    return;
  }
  if( not defined $cgi->param("add") )
  {
    # Continue editing i guess...
    return;
  }

  $cgi->param("normal_duration", $cgi->param("duration") * 60);

  {
    my $bits = 0;
    map { $bits += 1 << $_ } $cgi->param("bits");

    $cgi->param( "bits", $bits );
  }

  my @fields = qw(title series_type normal_duration genres bits description advisory actor);

  $db->begin_work;

  $db->do( "insert into series (".join(",",@fields).") values (".join(",", map { "?" } @fields).")", undef,
           map { $cgi->param($_)||undef } @fields );
  $db->do( "update guide_data set series_id = currval('series_series_id_seq'), title = ?, episode = ? where gdid = ?", undef,
            $cgi->param('title'),$cgi->param('episode'), $gdid );
  $db->commit;

  $page = $oldpage;
  $data = $olddata;
}

sub series_view
{
  my( $series_id,$data ) = split /,/,$data,2;

  my %data = GetSeries( $series_id );

  my $typename = $data{series_type} == 0 ? 'Program' : $data{series_type} == 4 ? 'Movie' : 'Series';

  my $showduration = int($data{normal_duration} / 60);

  print <<EOF;
<h1><a href=$self>Australian TV Guide</a> &gt; <a href=$self?page=searchseries>Series</a> &gt; Edit</h1>
<h2>[edit] [<a href=$self?page=seriesshowings&data=$series_id,$data>showings</a>] [<a href=$self?page=seriesepisodes&data=$series_id,$data>episodes</a>]</h2>
<h2>Editing $typename</h2>
<script language=javascript>
function ChangeTitle()
{
  if( confirm( "Changing Titles: Note that making changes to a title is tricky\\nand should never be done lightly. It may be propegated to all\\nunconfirmed showings and also affect automated data entry.\\nPlease limit any changes to punctuation and case-changes only.\\n\\nContinue?" ) )
  {
    var text = prompt( "Enter updated title", document.forms[0].title.value );

    if( text == null || text == "" )
      return;

    document.forms[0].title.value = text;

    alert( "Title update accepted. Press Update to submit change" );
  }
}
</script>
<form action=$self method=post>
<input type=hidden name=series_id value=$series_id>
<input type=hidden name=page value=series>
<input type=hidden name=type value=view>
<input type=hidden name=data value=$data>  <!-- Where to go after submit -->
<table>
<tr><td>Title<td><b>$data{title}</b> <input type=hidden name=title value=\"@{[ESC $data{title}]}\"> <input type=button value="Change Title" onclick=javascript:ChangeTitle()>
EOF

  if( $data{series_type} == 0 or $data{series_type} == 4 )
  {
    print "<input type=hidden name=series_type value=$data{series_type}>\n";
  }
  else
  {
    print "<tr><td>Series Type<td><select name=series_type>\n";
    print "  <option value=1 ",($data{series_type} == 1 ? 'selected' : ''),">Irregular / Non-episodic\n";
    print "  <option value=2 ",($data{series_type} == 2 ? 'selected' : ''),">Regular, no episode names (News,etc)\n";
    print "  <option value=3 ",($data{series_type} == 3 ? 'selected' : ''),">Regular, Episodic\n";
    print "</select><br>\n";
  }

  print "<tr><td>Usual Duration:<td><input size=5 width=5 name=duration value=$showduration> minutes\n";
  print "<tr><td>Description<td><textarea name=description cols=35 rows=4>@{[ESC $data{description}]}</textarea>\n";

  foreach my $field ( qw( advisory actor ) )
  {
    print "<tr><td>$field<td>$data{$field} <input type=hidden name=$field value='$data{$field}'>\n";
  }
  print "<tr><td>TiVo Series ID<td>".(defined($data{tivo_series})?$data{tivo_series}:"<i>Unknown</i>")."\n";
  {
    my %selected = map { $_ => 1 } split /,/,$data{genres};

    print "<tr><td>Genres<td><input type=text size=50 disabled name=genres2 value='$data{genres}'><input type=hidden name=genres value='$data{genres}'><br>\n";
    my $sth = $db->prepare("select isgroup, name from genres");
    $sth->execute();
    my $data = $sth->fetchall_arrayref;

    my %data2 = ( '1' => [], '0' => [] );

    foreach my $row (@$data)
    {
      push @{$data2{$row->[0]}}, $row->[1];
    }
    print <<EOF;
<script language=javascript> <!--
function UpdateGenres()
{
  var str = "";
  var i;

  var list = document.forms[0].genrelist;
  for( i=0 ; i < list.length ; i++ )
  {
    if( list.options[i].selected )
    {
      str = str + "," + list.options[i].value;
    }
  }

  var list = document.forms[0].genregrouplist;
  for( i=0 ; i < list.options.length ; i++ )
  {
    if( list.options[i].selected )
    {
      str = str + "," + list.options[i].value;
    }
  }

  document.forms[0].genres.value = str.substring(1);
  document.forms[0].genres2.value = str.substring(1);
}
//--> </script>
EOF
    print "<select name=genrelist onchange=javascript:UpdateGenres() multiple size=5>\n";
    print map { "  <option value='$_'".($selected{$_}?" selected":"").">$_\n" } @{ $data2{0} };
    print "</select>\n";
    
    print "<select name=genregrouplist onchange=javascript:UpdateGenres() multiple size=5>\n";
    print map { "  <option value='$_'".($selected{$_}?" selected":"").">$_\n" } @{ $data2{1} };
    print "</select>\n";
  }
  
  print "</table>\n";

  my @bits = qw( 
                        CloseCaption Stereo Subtitle JoinedInProgress
                        CableInTheClassroom Sap Blackout Intercast ThreeD
                        Repeat Letterbox SexRating ViolenceRating
                        LanguageRating DialogRating FvRating
  );
  print "<table><!-- bits=$data{bits} -->\n";
  $data{bits} ||= 0;
  foreach my $i (0..15)
  {
    if( ($i%4) == 0 ) { print "<tr>\n" }
    print "<td><input type=checkbox name=bits value=$i ",($data{bits} & (1<<$i))?"checked":"", ">$bits[$i]\n";
  }
  print "</table>\n";
  print "<input type=submit value=Update> <input type=submit name=cancel value=Cancel>\n";
  print "</form>\n";

}

sub series_update
{
  my $series_id = $cgi->param('series_id');

  return if not defined $series_id;
  if( defined $cgi->param("cancel") )
  {
    ($page,$data) = split /,/, $cgi->param("data"), 2;
    return;
  }

  {
    my $bits = 0;
    map { $bits += 1 << $_ } $cgi->param("bits");

    $cgi->param( "bits", $bits );
  }

  $cgi->param("normal_duration", $cgi->param("duration") * 60 );

  print "<!-- ".$cgi->param("genres")."-->\n";
  $db->do( "update series set title = ?, series_type = ?, normal_duration = ?, description = ?, genres = ?, advisory = ?, actor = ?, bits = ?
            where series_id = ?", undef,
            (map { $cgi->param($_)||'' } qw(title series_type normal_duration description genres advisory actor bits)),
            $series_id );

#  print "[",$cgi->param("data"),"]\n";
  ($page,$data) = split /,/, $cgi->param("data"), 2;
}

sub seriesshowings_view
{
  my( $series_id,$data ) = split /,/,$data,2;

  my %data = GetSeries( $series_id );

  print <<EOF;
<h1><a href=$self>Australian TV Guide</a> &gt; <a href=$self?page=searchseries>Series</a> &gt; Showings</h1>
<h2>[<a href=$self?page=series&data=$series_id,$data>edit</a>] [showings] [<a href=$self?page=seriesepisodes&data=$series_id,$data>episodes</a>]</h2>
<h2>Currently available showings for $data{title}</h2>
EOF

  {
    my $list = $db->selectall_arrayref( 
         "select channels.code, daynum, time, episode, guide_data.confirmed 
          from guide_data where channels.channel_id = guide_data.channel_id 
          and series_id = ?", undef, $series_id );

    if( scalar( @$list ) == 0 )
    {
      print "No showings on file...\n";
    }
    else
    {
      my %hash;

      print "<h2>Showings currently on file...</h2>\n";
      print "<table border=1>\n<tr><th>Date<th>Time<th>Channels</tr>\n";
      for my $showing ( @$list )
      {
        my( $code, $daynum, $time, $episode, $confirmed ) = @$showing;

        $hash{$daynum}{$time}{$code} = $confirmed;
      }
      foreach my $daynum ( sort { $a <=> $b } keys %hash )
      {
        my( $chan, $date ) = GetChannelNameDate( 1, $daynum );
        foreach my $time ( sort { $a <=> $b } keys %{ $hash{$daynum} } )
        {
          print "<tr><td>$date<td>".GetTimeString( $time )."<td>";
          foreach my $code ( sort keys %{ $hash{$daynum}{$time} } )
          {
            print "$code, ";
          }
        }
      }
      print "</table>\n";
    }
  }
}

sub seriesepisodes_view
{
  my( $series_id,$data ) = split /,/,$data,2;

  my %data = GetSeries( $series_id );

  print <<EOF;
<h1><a href=$self>Australian TV Guide</a> &gt; <a href=$self?page=searchseries>Series</a> &gt; Showings</h1>
<h2>[<a href=$self?page=series&data=$series_id,$data>edit</a>] [<a href=$self?page=seriesshowings&data=$series_id,$data>showings</a>] [episodes]</h2>
<h2>Currently available episodes for $data{title}</h2>
<form method=post>
<input type=hidden name=page value=seriesepisodes>
<input type=hidden name=data value=$series_id,$data>
EOF

  {
    my $list = $db->selectall_arrayref( 
         "select episode_id, series_id, episode, season, episodenum, firstairdate, description
          from episodes where series_id = ? order by season, episodenum", undef, $series_id );

    if( scalar( @$list ) == 0 )
    {
      print "No episodes on file...\n";
    }
    else
    {
      my %hash;

      print "<h2>Episodes currently on file...</h2>\n";
      print "<table border=1>\n<tr><th>Episode<th>Name<th>First Aired<th>Description</tr>\n";
      for my $showing ( @$list )
      {
        my( $episode_id, $series_id, $episode, $season, $epnum, $firstairdate, $description ) = @$showing;

        $description ||= "<i>None</i>";
        $firstairdate ||= "<i>None</i>";
        print "<tr><td>#$season.$epnum<td>$episode<td>$firstairdate<td>$description</tr>\n";
      }
      print "</table>\n";
    }
  }
  {
    print "<h1>Current unnumbered episodes on file...</h1>\n";
    my $list = $db->selectall_arrayref( "select distinct on (program_canonical(episode)) gdid, episode, description from guide_data where series_id = ? and episode_id is null and episode <> '' and not confirmed order by program_canonical(episode)", undef, $series_id );

    if( scalar( @$list ) == 0 )
    {
      print "No unnumbered episodes on file...\n";
    }
    else
    {
      print "<table border=1>\n<tr><th>Series<th>Ep Num<th>Episode<th>Description</tr>\n";
      for my $showing ( @$list )
      {
        my( $gdid, $title, $description ) = @$showing;

        $description ||= "<i>None</i>";
        print "<tr><td><input type=input name=season_$gdid size=4><td><input type=input name=epnum_$gdid size=4><td>$title<td>$description</tr>\n";
      }
      print "</table>\n";
      print "<input type=submit name=assign value='Assign season/episode numbers'>\n";
    }
  }
  print "<input type=hidden name=page value=seriesepisodes>\n";
  print "<input type=hidden name=data value=$data>\n";
  print "<h2>Enter a new episode</h2>\n";
  print "<table>\n";
  print "<tr><td>Season Number:<td><input type=text name=season size=5> Series can be any number, including a year number\n";
  print "<tr><td>Episode Number:<td><input type=text name=episodenum size=5>\n";
  print "<tr><td>Episode Name:<td><input type=text name=name>\n";
  print "<tr><td colspan=2><input type=submit name=add value='Add episode'>\n";
  print "</table>\n";
  print "</form>\n";
  print "<hr>\n";
  print "<a href=$self?page=rawdumpepisodes&data=$series_id>Raw Dump of Episodes</a>\n";
}

sub seriesepisodes_update
{
  my($series_id) = split /,/,$data;

  if( $cgi->param("season") =~ /^\d+$/ and
      $cgi->param("episodenum") =~ /^\d+$/ )
  {
    my $sth = $db->prepare( "insert into episodes (series_id,season,episodenum,episode) values (?,?,?,?)" );
    $sth->execute( $series_id, $cgi->param("season"), $cgi->param("episodenum"), $cgi->param("name") );
  }

  my @p = grep { $cgi->param($_) =~ /^\d+$/ }
          grep { /^season_\d+/ } $cgi->param();

  foreach my $p (@p)
  {
    $p =~ /^season_(\d+)/;
    my $q = "epnum_$1";
    my $gdid = $1;

    next unless $cgi->param($q) =~ /^\d+$/;

    my $season = $cgi->param($p);
    my $epnum = $cgi->param($q);

    my $a = $db->selectall_arrayref( "select episode_id from episodes where series_id = ? and season = ? and episodenum = ?", undef, 
                                    $series_id, $season, $epnum );

    if( scalar( @$a ) == 0 )   # No current episode with that number...
    {
      $db->do( "insert into episodes (series_id,season,episodenum,episode) select ?,?,?,episode from guide_data where gdid = ?", undef,
               $series_id, $season, $epnum, $gdid );
      $db->do( "update guide_data set episode_id = currval('episodes_episode_id_seq') from guide_data g ".
                  "where guide_data.series_id = ? and guide_data.episode = g.episode and g.gdid = ? and not guide_data.confirmed", undef,
               $series_id, $gdid );
    }
    else                       # Currently episode with this number
    {
      my $episode_id = $a->[0][0];

      $db->do( "update guide_data set episode_id = ? from guide_data g ".
                  "where guide_data.series_id = ? and guide_data.episode = g.episode and g.gdid = ? and not guide_data.confirmed", undef,
               $episode_id, $series_id, $gdid );
    }
  }
}

sub rawdump_view
{
  my ($chan,$daynum) = split /,/,$data;

  my( $channame, $dayname ) = GetChannelNameDate( $chan,$daynum );

  print "# NowShowing Day Dump\n";
  print "# Channel: $channame\n";
  print "# Date: $dayname\n";
  print "#\n";

  my $res = GetChannelDay2( $chan, $daynum );

  my @fields = qw(Title Daynum Time Duration Episode Description Year Genres
                  Episodic Advisory Actor Director Premiere Live Bits
                  TvRating);

  ######### BEGIN DISPLAY #########
  foreach my $show (@$res)
  {
#    my( $gdid, $dynum,$time,$dur,$title,$episodic,$episode,$confirmed,$series_id ) = @$show;

    foreach my $field (@fields)
    {
      if( defined $show->{lc $field} )
      {
        $show->{lc $field} =~ s/\s+/ /g;
        print "$field: ",$show->{lc $field},"\n";
      }
    }
    print "\n";
  }
}

sub rawdumpseries_view
{
  my %fields = (series_id => "SeriesID",
                title     => "Title",
                normal_duration => "NormalDuration",
                description => "Description",
                year        => "Year",
                genres      => "Genres",
                bits        => "Bits",
                advisory    => "Advisory",
                actor       => "Actor",
                series_type => "SeriesType",
                tivo_series => "TiVoSeriesID" );

  print "# NowShowing Series Dump\n";
  print "#\n";

  my $sth = $db->prepare( "select * from series" );
  $sth->execute();

  while( my $hash = $sth->fetchrow_hashref )
  {
    print "Title: $hash->{title}\n";
    delete $hash->{title};
    foreach my $field (keys %$hash)
    {
      if( defined $hash->{$field} )
      {
        $hash->{$field} =~ s/\s+/ /g;
        print "$fields{$field}: ",$hash->{$field},"\n";
      }
    }
    print "\n";
  }
}

sub rawdumpepisodes_view
{
  my $series_id = $data;
  my %fields = (series_id    => "SeriesID",
                episode_id   => "EpisodeID",
                season       => "Season",
                episodenum   => "EpisodeNumber",
                title        => "SeriesTitle",
                episode      => "EpisodeTitle",
                description  => "Description",
                genres       => "Genres",
                actor        => "Actor",
                firstairdate => "FirstAirDate" );

  print "# NowShowing Episode Dump\n";
  print "#\n";

  my $sth = $db->prepare( "select s.series_id, e.episode_id, season, episodenum, title, episode, e.description, e.genres, e.actor, e.firstairdate from series s, episodes e where s.series_id = e.series_id and s.series_id = ? order by season, episodenum" );
  $sth->execute($series_id);

  while( my $hash = $sth->fetchrow_hashref )
  {
    print "SeriesTitle: $hash->{title}\n";
    delete $hash->{title};
    print "Season: $hash->{season}\n";
    delete $hash->{season};
    print "EpisodeNumber: $hash->{episodenum}\n";
    delete $hash->{episodenum};
    print "EpisodeTitle: $hash->{episode}\n";
    delete $hash->{episode};
    foreach my $field (keys %$hash)
    {
      if( defined $hash->{$field} )
      {
        $hash->{$field} =~ s/\s+/ /g;
        print "$fields{$field}: ",$hash->{$field},"\n";
      }
    }
    print "\n";
  }
}

sub GetProgram
{
  my $gdid = shift;

  my $sth = $db->prepare( "select * from guide_data where gdid = ?" );

  $sth->execute( $gdid );

  my $hash = $sth->fetchrow_hashref;

  return %$hash;
}

sub GetSeries
{
  my $series_id = shift;

  my $sth = $db->prepare( "select * from series where series_id = ?" );

  $sth->execute( $series_id );

  my $hash = $sth->fetchrow_hashref;

  return %$hash;
}

sub GetEpisodes
{
  my $series_id = shift;

  my $sth = $db->prepare( "select * from episodes where series_id = ?" );

  $sth->execute( $series_id );

  my %hash;

  while( my $h = $sth->fetchrow_hashref )
  {
    $hash{ $h->{episode_id} } = $h;
  }

  return \%hash;
}

sub GetProgramTemplate
{
  # Get the fields: title, episodic, duration, description, genres, bits, advisory, series_id
  my $gdid = shift;

  my %hash = GetProgram( $gdid );

  delete $hash{'episode'};
  delete $hash{'gdid'};

  return %hash;
}

sub GetSeriesTemplate
{
  # Get the fields: title, episodic, duration, description, genres, bits, advisory, series_id
  my $series_id = shift;

  my $res = $db->selectall_arrayref( "select title, true as episodic, normal_duration as duration, description, genres, 0 as bits, '' as advisory, series_id from series where series_id = ?", undef, $series_id );

  my %hash;

  @hash{ qw(title episodic duration description genres bits advisory series_id series_type) } = @{$res->[0]};

  return %hash;
}

my %ChannelCache;
my %DayCache;

sub GetChannelNameDate
{
  my( $chan, $daynum ) = @_;
  if( defined $ChannelCache{$chan} and defined $DayCache{$daynum} )
  {
    return ($ChannelCache{$chan}, $DayCache{$daynum});
  }
  my $res = $db->selectall_arrayref( "select code, to_char('1970-01-01'::date + ?::int4, 'Day, dd Mon YYYY') from channels where channel_id = ?", undef, $daynum, $chan );

  $ChannelCache{$chan} = $res->[0][0];
  $DayCache{$daynum}   = $res->[0][1];

  return @{$res->[0]};
}

sub GetChannelDay
{
  my( $chan, $daynum ) = @_;

  my $query = "select gdid, daynum, time, duration, guide_data.title, episodic, guide_data.episode, confirmed, 
                      guide_data.series_id, series_type, 
                      guide_data.description, guide_data.genres, episodes.season, episodes.episodenum 
               from guide_data left outer join series using (series_id)
               left outer join episodes using (episode_id)
               where channel_id = ? and (daynum = ? or (daynum+1 = ? and time+duration > 86400))
               order by daynum, time";

  return $db->selectall_arrayref( $query, undef, $chan, $daynum, $daynum );
}

sub GetChannelDay2
{
  my( $chan, $daynum ) = @_;

  my $query = "select * from guide_data
               where channel_id = ? and (daynum = ? or (daynum+1 = ? and time+duration > 86400))
               order by daynum, time";

  my $sth = $db->prepare( $query );
  $sth->execute( $chan, $daynum, $daynum );
  my $res = [];

  my $d;
  while( $d = $sth->fetchrow_hashref )
  {
    push @$res, $d;
  }

  return $res;
}

sub GetTimeString
{
  my $time = shift;
  return sprintf "%d:%02d", int($time/3600), int( ($time % 3600) / 60 );
}

# Create the genre list boxes and the javascript to store the result in two text field, genre and genre2
sub GenreBoxes
{
  my $genres = shift;

  {
    my %selected = map { $_ => 1 } split /,/,$genres;

    my $sth = $db->prepare("select isgroup, name from genres");
    $sth->execute();
    my $data = $sth->fetchall_arrayref;

    my %data2 = ( '1' => [], '0' => [] );

    foreach my $row (@$data)
    {
      push @{$data2{$row->[0]}}, $row->[1];
    }
    print <<EOF;
<script language=javascript> <!--
function UpdateGenres()
{
  var str = "";
  var i;

  var list = document.forms[0].genrelist;
  for( i=0 ; i < list.length ; i++ )
  {
    if( list.options[i].selected )
    {
      str = str + "," + list.options[i].value;
    }
  }

  var list = document.forms[0].genregrouplist;
  for( i=0 ; i < list.options.length ; i++ )
  {
    if( list.options[i].selected )
    {
      str = str + "," + list.options[i].value;
    }
  }

  document.forms[0].genres.value = str.substring(1);
  document.forms[0].genres2.value = str.substring(1);
}
//--> </script>
EOF
    print "<select name=genrelist onchange=javascript:UpdateGenres() multiple size=5>\n";
    print map { "  <option value='$_'".($selected{$_}?" selected":"").">$_\n" } @{ $data2{0} };
    print "</select>\n";
    
    print "<select name=genregrouplist onchange=javascript:UpdateGenres() multiple size=5>\n";
    print map { "  <option value='$_'".($selected{$_}?" selected":"").">$_\n" } @{ $data2{1} };
    print "</select>\n";
    print "<br><i>Tip: You can select multiple of each by using the control key</i>\n";
  }
}

sub ESC ($)
{
  return CGI::escapeHTML( shift );
}