#!/usr/bin/env ruby
require 'mysql2'
require 'net/telnet'


client = Mysql2::Client.new(:host => "localhost", :username => "root", :password => "xxxx", :database => "xtvd")

def programs_query(mysqlClient)

  program_ids = Array[]

  teams = Hash.new
  teams = {"bulls" => "SELECT id FROM program WHERE title like \"%NBA%\" and subtitle like \"%Bulls%\"",
           "blackhawks" => "SELECT id FROM program WHERE title like \"%NHL%\" and subtitle like \"%Blackhawks%\"",
           "cubs" => "SELECT id FROM program WHERE title like \"%MLB%\" and subtitle like \"%Cubs%\"",
           "bears" => "SELECT id FROM program WHERE title like \"%NFL%\" and subtitle like \"%Bears%\""}

  results = mysqlClient.query(teams[ARGV[0]])

  results.each do |row|
    program_ids.push(row["id"])
  end
  return program_ids

end

def stations_query(mysqlClient, program_ids)

  date = Time.new
  statement_ids = ""
  station_ids = Array[]

  i = 1
  for id in program_ids
    if i == program_ids.length
      statement_ids << "'"
      statement_ids << id
      statement_ids << "'"
      break
    end
    statement_ids << "'"
    statement_ids << id
    statement_ids << "', "
    i += 1
  end

  results = mysqlClient.query("SELECT station FROM schedule WHERE startDate = " + date.strftime("%-m%e") + " and program IN (" + statement_ids + ")")

  results.each do |row|
    station_ids.push(row["station"])
  end
  return station_ids
end

def callsign_query(mysqlClient, station_ids)

  statement_ids = ""
  callsigns = Array[]

  i = 1
    for id in station_ids
      if i == station_ids.length
        statement_ids << id.to_s
        break
      end
      statement_ids << id.to_s
      statement_ids << ", "
      i += 1
    end

  results = mysqlClient.query("SELECT callSign from station where id in (" + statement_ids + ")")

  results.each do |row|
    callsigns.push(row["callSign"])
  end
return callsigns
end

def assign_channel(callsigns)

  call_sign_data = {"CSN" => 685, "WLS" => 607, "WGN" => 609, "WCIU" => 606, "CLTV" => 616, "WPWR" => 608, "TNT" => 623, "TBS" => 622,
                    "ESPN" => 681, "NBCSN" => 691, "WFLD" => 612, "WMAQ" => 605, "WBBM" => 602, "FS1" => 689}
  priority_stack = ["CLTV", "CSN", "WLS", "WGN", "WCIU"]

  for call_sign in priority_stack
    if callsigns.to_s.include? call_sign
      return call_sign_data[call_sign]
    end
  end
  return call_sign_data[callsigns[0]]
end

def send_command(channel)

  command = "SETCH " + channel.to_s + " \r\n"

  if channel.nil?
    quit()
  else
    telnet = Net::Telnet::new("Host" => "192.168.1.72", "Port" => 31339)
    telnet.puts(command)
    telnet.close
    exec "irsend SEND_ONCE receiver KEY_1"

  end

end

programs = programs_query(client)
stations = stations_query(client, programs)
callsigns = callsign_query(client, stations)
channel = assign_channel(callsigns)
send_command(channel)