# Written by Emily Price <http://duien.com> Feb 4, 2009
# Feel free to use and modify

# The code makes a few assumptions about how you use org-mode:
# Assumes you're using org-odd-levels-only and that you have the TODO keywords 'TODO', 'DONE', and 'CANCEL' defined

#!/usr/bin/ruby
require 'rubygems'
require 'sqlite3'

def orgify_group(pk, numstars)
  node = $db.execute("select ZTITLE from ZGROUP where Z_PK = #{pk}")[0]
  result = ('*'*numstars) + " #{node['ZTITLE']}\n"
  $db.execute("select Z_PK from ZGROUP where ZPARENTGROUP = #{pk} order by ZDISPLAYORDER") do |row|
    result << orgify_group(row['Z_PK'], numstars + 2)
  end
  $db.execute("select Z_PK from ZTASK where ZPARENTLIST = #{pk} order by ZDISPLAYORDER") do |row|
    result << orgify_task(row['Z_PK'], numstars + 2)
  end
  result
end

def orgify_task(pk, numstars)
  node = $db.execute("select ZTITLE, strftime('%s', ZCOMPLETEDDATE, 'unixepoch', '+31 years') as ZCOMPLETEDDATE, strftime('%s', ZCANCELEDDATE, 'unixepoch', '+31 years') as ZCANCELEDDATE, strftime('%s', ZDUEDATE, 'unixepoch', '+31 years') as ZDUEDATE, strftime('%s', ZSTARTDATE, 'unixepoch', '+31 years') as ZSTARTDATE, ZNOTES from ZTASK where Z_PK = #{pk}")[0]
  status = 'TODO'
  property_line = ''
  if node['ZCANCELEDDATE']
    status = 'CANCEL'
    date = Time.at(node['ZCANCELEDDATE'].to_i).strftime('%Y-%m-%d %a %H:%M')
    property_line = (' '*numstars) + " CLOSED: [#{date}]\n"
  end
  if node['ZCOMPLETEDDATE']
    status = 'DONE'
    date = Time.at(node['ZCOMPLETEDDATE'].to_i).strftime('%Y-%m-%d %a %H:%M')
    property_line = (' '*numstars) + " CLOSED: [#{date}]\n"
  end
  if node['ZDUEDATE']
    date = Time.at(node['ZDUEDATE'].to_i).strftime('%Y-%m-%d %a')
    property_line = (' '*numstars) + " DEADLINE: <#{date}>\n"
  end
  if node['ZSTARTDATE'] and status == 'TODO'
    date = Time.at(node['ZSTARTDATE'].to_i).strftime('%Y-%m-%d %a')
    property_line = (' '*numstars) + " SCHEDULED: <#{date}>\n"
  end
  result = ('*'*numstars) + " #{status} #{node['ZTITLE']}\n#{property_line}"
  if node['ZNOTES']
    notes_lines = node['ZNOTES'].split("\n")
    notes_lines.each do |line|
      result << (' '*numstars) + " #{line}\n"
    end
  end
  $db.execute("select Z_PK from ZTASK where ZPARENTTASK = #{pk} order by ZDISPLAYORDER") do |row|
    result << orgify_task(row['Z_PK'], numstars + 2)
  end
  result
end

thl_library = File.expand_path("~/Library/Application Support/The Hit List/Library.thllibrary/library.sqlite3")

$db = SQLite3::Database.new( thl_library )

tables = %w{ZGROUP ZTASK Z_4TASKS Z_METADATA ZLIBRARY ZTIMELOGENTRY Z_5TASKS Z_PRIMARYKEY}

$db.results_as_hash = true

start_node = $db.execute("select Z_PK from ZGROUP where ZPARENTGROUP is null")[0]['Z_PK']

puts orgify_group(start_node, 1)