#!/usr/bin/gawk -f 
# Script to update channel and transport definitions on mythtv 
# with dvb-utils's scan utility (which stores data in channels.conf)
# Written by Mario Chisari, v1.1 2008-08-12
# Supports mythtv 0.21
# This software is public domain
#
# HOWTO
# scan -5 -n -v <init-location-DVB-stations> > ~/.tzap/channels.conf
# mysqldump -u mythtv -p --opt mythconverg dtv_multiplex > dtv_multiplex_export.sql
# mysqldump -u mythtv -p --opt mythconverg channel > channel_export.sql
# <this_script> ~/.tzap/channels.conf
# mysql -u mythtv -p mythconverg < dtv_multiplex_import.sql
# mysql -u mythtv -p mythconverg < channel_import.sql
#  
# Set debug level with '-v debug=<n>' from command 
# line (<this_script> -v debug=<n> ~/.tzap/channels.conf)
# n=1-4 user level debug; n=5-9 developer level debug

# Function to strip quote characters around a string
function dequote(s,   tmps) {
  tmps = s 
  sub(/^'/, "", tmps)
  sub(/'$/, "", tmps)
  if (debug>8) print "dequote: " s "->" tmps
  return tmps
}

# This function returns a network id number for a transport, given its frequency
# You can modify values according to your location, or simply return 0 (unharmful)
function defaultnid(freq,   retval) {
  # Zona Roma
  if (debug>8) print "Defaultnid: called with freq=" freq
  retval=0
  if (freq==186000000) retval=318
  if (freq==626000000) retval=272
  if (freq==698000000) retval=318
  if (freq==730000000) retval=29
  if (freq==762000000) retval=272
  if (freq==810000000) retval=272
  if (debug>8) print "Defaultnid: returning " retval
  return retval
}

# Ditto, for transport id
function defaulttid(freq,  retval) {
  if (debug>8) print "Defaulttid: called with freq=" freq
  retval=0
  if (freq=762000000) retval=901
  if (debug>8) print "Defaulttid: returning " retval
  return 0
}

BEGIN {
  # Modify here your file names
  CHAN_SQL_OUT="channel_import.sql"     # Output file; will be imported in mysql
  CHAN_SQL_IN="channel_export.sql"      # Input file, used to get old data
  CHAN_SQL_CMD="INSERT INTO `channel` VALUES " # SQL command to insert channel data
  MPX_SQL_OUT="dtv_multiplex_import.sql" # Output file; will be imported in mysql
  MPX_SQL_IN="dtv_multiplex_export.sql"  # Input file, used to get old data
  MPX_SQL_CMD="INSERT INTO `dtv_multiplex` VALUES " # SQL command to insert mpx data
  
  # Field separator for input files
  FS=","
  # Read dtv_multiplex export file, and copy it to output line by line 
  # until an "insert" statement found
  r=getline <MPX_SQL_IN
  print > MPX_SQL_OUT
  do {
    r=getline <MPX_SQL_IN
    # Get rid of AUTO_INCREMENT in output
    sub(/AUTO_INCREMENT=[0-9]* /,"")
    if (r>0 && $0 !~ "^" MPX_SQL_CMD) \
      print >> MPX_SQL_OUT
  }  while (r>0 && $0 !~ "^" MPX_SQL_CMD)
  if (r==0) { print "No insert clause found in " MPX_SQL_IN ; exit }
  # Strip leading insert clause from input, to parse each record in it
  sub(MPX_SQL_CMD, "") ; if (debug>7) print 
  # Strip trailing semicolon from input
  if (sub( /;$/ , "")==0) {
    print "Not a whole insert line in " MPX_SQL_IN ; exit}
  # Prepare a variable where we will build a new insert command. We will write
  # it all at once at the end
  sql_insert_mpx = MPX_SQL_CMD

  # Split old transport individual records 
  print "Previous transports read: " split ($0, prevmpx, /\),\(/)
  for (r in prevmpx) {
    $0 = prevmpx[r]
    # Strip leading and trailing brackets
    sub(/^\(|\)$/,"") ; gsub(/\\'/,"'")
    if (debug>6) print
    if (debug>4) print "Storing old trsp id " $1 " freq " $5 " nid/tid " $4 "/" $3
    # Store references: old transportid->frequency, frequency->nid, frequency->tid
    oldtr_freq[$1]=$5
    oldtr_nid[$5]=$4
    oldtr_tid[$5]=$3
  }

  # Like above, read channel export file, and copy it to output line by line
  # until an "insert" statement found
  r=getline <CHAN_SQL_IN
  print > CHAN_SQL_OUT
  do {
    r=getline <CHAN_SQL_IN
    sub(/AUTO_INCREMENT=[0-9]* /,"")
    if (r>0 && $0 !~ CHAN_SQL_CMD) \
      print >> CHAN_SQL_OUT
  } while (r>0 && $0 !~ CHAN_SQL_CMD)
  if (r==0) { print "No insert clause found in " CHAN_SQL_IN ; exit}
  # Strip leading insert clause from input, to parse each record in it
  sub( CHAN_SQL_CMD, "") ; if (debug>7) print
  # Strip trailing semicolon from input
  if (sub( /;$/ , "")==0) { 
    print "Not a whole insert line in " CHAN_SQL_IN ; exit}
  # Prepare a variable where we will build a new insert command. We will write
  # it all at once at the end
  sql_insert_chan = CHAN_SQL_CMD 
 
  print "Previous channels read:" split ($0, prevchan, /\),\(/)
  for (r in prevchan) {
    # Split individual records from old channel data
    $0=prevchan[r]
    # Strip leading/trailing brackets, replace "\\'" with "'"
    sub(/^\(|\)$/,"") ; gsub(/\\'/,"'")
    if (debug>6) print 
    if (debug>4) print "Storing old chan " $5 " with key " $22, oldtr_freq[$21]
    # Store old channels data, indexed with transport frequency and program id
    oldch_channelid[$22,oldtr_freq[$21]]=$1
    oldch_channum[$22,oldtr_freq[$21]]=dequote($2)
    oldch_sourceid[$22,oldtr_freq[$21]]=$4
    oldch_callsign[$22,oldtr_freq[$21]]=dequote($5)
    oldch_name[$22,oldtr_freq[$21]]=dequote($6)
    oldch_icon[$22,oldtr_freq[$21]]=dequote($7)
    oldch_videofilters[$22,oldtr_freq[$21]]=dequote($9)
    oldch_xmltvid[$22,oldtr_freq[$21]]=dequote($10)
    oldch_recpriority[$22,oldtr_freq[$21]]=$11
    oldch_contrast[$22,oldtr_freq[$21]]=$12
    oldch_brightness[$22,oldtr_freq[$21]]=$13
    oldch_colour[$22,oldtr_freq[$21]]=$14
    oldch_hue[$22,oldtr_freq[$21]]=$15
    oldch_commfree[$22,oldtr_freq[$21]]=$17
    oldch_visible[$22,oldtr_freq[$21]]=$18
    oldch_outputfilters[$22,oldtr_freq[$21]]=dequote($19)
    oldch_useonairguide[$22,oldtr_freq[$21]]=$20
    oldch_tmoffset[$22,oldtr_freq[$21]]=$24
    oldch_lastrecord[$22,oldtr_freq[$21]]=$25
    oldch_authority[$22,oldtr_freq[$21]]=$26
    oldch_commmethod[$22,oldtr_freq[$21]]=$27
    # "chans" stores assigned channel numbers, preventing duplicates
    # Will specify which program id and frequency got it
    chans[$1]=$22 " " oldtr_freq[$21]
  }
  # Now stop processing and begin reading channels.conf
  FS=":"; OFS="|"
}

{ 
  if (debug>6) print
  if ($12 == 0) {
    # Field 12 is audio id; if null, it's not an audio or video channel
    if (debug>0) print "      Skipping " $1 " for not's a TV/Radio channel"
    next
  }
  if (mplexid[$2] == 0) { # First occurence of transport (frequency)
    bw = ( $4=="BANDWIDTH_8_MHZ" ? 8 : 7)
    timestamp = strftime("%Y-%m-%d %H:%M:%S")
    nid = oldtr_nid[$2];
    if (nid==0) nid=defaultnid($2)
    tid = oldtr_tid[$2]
    if (tid==0) tid=defaulttid($2);
    # Append comma to being build insert clause, as a delimiter for new record
    if (transportid++ > 0) sql_insert_mpx = sql_insert_mpx ","
    # Append insert data to work variable
    sql_insert_mpx = sql_insert_mpx "(" transportid ",1," tid "," nid "," $2 ",'a',NULL,'auto',NULL,'auto','" bw "','auto','a','auto',0,'auto','n','auto','dvb',33,'" timestamp "')"
    # Store reference frequency->transportid; will be used by channel definition
    mplexid[$2] = transportid
    printf "Adding transport %d: %d nid/tid (%d/%d)\n", transportid, $2, nid, tid
  }
  if (debug>4) print "Retrieving old channel info for " $1 " with key " $13 " " $2
  # Append comma to being build insert clause, as a delimiter for new record
  if (channelcount++ > 0) sql_insert_chan = sql_insert_chan ","
  channelid     = oldch_channelid[$13,$2]
  # We must avoid that two channels are assigned the same channelid.
  # First we get the old assignment; if it is 0, then it has never 
  # been assigned to this channel before.
  # if id is already in "chans" list, and freq/pid do not match, then this
  # it is already taken by another, so we have to search for a new one 
  if (channelid==0 || (channelid in chans && chans[channelid] != $13 " " $2)) {
    if (debug>1) print " ---  Generating new channelid for " $1
    if (debug>5) print "(I'm doing this because channelid=" channelid " and chans[" channelid "]=" chans[channelid]
    # First try: add 1000 to program id
    channelid = 1000+$13
    # If it's already taken, increment until a free number is found
    while (channelid in chans && chans[channelid] != $13 " " $2) {
      if (debug>5) print "Trying with " channelid "... -> chans[" channelid "]=" chans[channelid]
      channelid++
      }
    }
  # Now store new channelid assignment, to prevent subsequent channels to get it
  chans[channelid]=$13 " " $2
  # Get old other channel properties. If no one is found, assign sensible defaults
  channum       = oldch_channum[$13,$2]       ; if (channum==0) channum=channelid
  sourceid      = oldch_sourceid[$13,$2]      ; if (sourceid==0) sourceid=1
  callsign      = oldch_callsign[$13,$2]      ; if (callsign==0) callsign=$1
  name          = oldch_name[$13,$2]          ; if (name==0) name=$1
  icon          = oldch_icon[$13,$2]
  videofilters  = oldch_videofilters[$13,$2]
  xmltvid       = oldch_xmltvid[$13,$2]
  recpriority   = oldch_recpriority[$13,$2]   ; if (recpriority=="") recpriority=0
  contrast      = oldch_contrast[$13,$2]      ; if (contrast=="") contrast=32768
  brightness    = oldch_brightness[$13,$2]    ; if (brightness=="") brightness=32768
  colour        = oldch_colour[$13,$2]        ; if (colour=="") colour=32768
  hue           = oldch_hue[$13,$2]           ; if (hue=="") hue=32768
  commfree      = oldch_commfree[$13,$2]      ; if (commfree=="") commfree=0
  visible       = oldch_visible[$13,$2]       ; if (visible=="") visible=1
  outputfilters = oldch_outputfilters[$13,$2]
  useonairguide = oldch_useonairguide[$13,$2] ; if (useonairguide=="") useonairguide=1
  tmoffset      = oldch_tmoffset[$13,$2]      ; if (tmoffset=="") tmoffset=0
  lastrecord	= oldch_lastrecord[$13,$2]    ; if (lastrecord=="") lastrecord="0000-00-00 00:00:00"
  authority	= oldch_authority[$13,$2]
  commmethod	= oldch_commmethod[$13,$2]    ; if (commmethod=="") commmethod=-1
  
  printf "%6s %4d - Channel %4s: %s\n", (oldch_callsign[$13,$2]!=""?"":"(new)"), channelid, channum, $1
  if (debug>5) print "old: " oldch_channelid[$13,$2], oldch_channum[$13,$2], oldch_sourceid[$13,$2], oldch_callsign[$13,$2], oldch_name[$13,$2], oldch_icon[$13,$2], oldch_videofilters[$13,$2], oldch_xmltvid[$13,$2], oldch_recpriority[$13,$2], oldch_contrast[$13,$2], oldch_brightness[$13,$2], oldch_colour[$13,$2], oldch_hue[$13,$2], oldch_commfree[$13,$2], oldch_visible[$13,$2], oldch_outputfilters[$13,$2], oldch_useonairguide[$13,$2], oldch_tmoffset[$13,$2], oldch_lastrecord[$13,$2], oldch_authority[$13,$2], oldch_commmethod[$13,$2]
  if (debug>5) print "new: " channelid, channum, sourceid, callsign, name, icon, videofilters, xmltvid, recpriority, contrast, brightness, colour, hue, commfree, visible, outputfilters, useonairguide, tmoffset, lastrecord, authority, commmethod
  # Add escape character "\" before single quotes; mysql will rant otherwise
  gsub(/'/,"\\'",name) ; gsub(/'/,"\\'",callsign)
  # Finally, append record data to being build insert clause
  sql_insert_chan = sql_insert_chan "(" channelid "," channum ",NULL," sourceid ",'" callsign "','" name "','" icon "',NULL,'" videofilters "','" xmltvid "'," recpriority "," contrast "," brightness "," colour "," hue ",'Default'," commfree "," visible ",'" outputfilters "'," useonairguide "," mplexid[$2] "," $13 ",NULL," tmoffset ",0,0,'" lastrecord "','" authority "'," commmethod ")" 
}

END {
  # Finally, write contructed both sql insert clauses to respective output files
  print sql_insert_chan ";" >> CHAN_SQL_OUT
  print sql_insert_mpx ";" >> MPX_SQL_OUT
  print channelcount " channels and " transportid " transport written"
  # Finalize by appending the rest of original sql export files
  do {
    r=getline <CHAN_SQL_IN
    if (r>0) print >> CHAN_SQL_OUT
  } while (r>0)
  do {
    r=getline <MPX_SQL_IN
    if (r>0) print >> MPX_SQL_OUT
  } while (r>0)
}

# END OF PROGRAM