#!/opt/ActiveTcl-8.6/bin/tclsh
# Hey Emacs, use -*- Tcl -*- mode

set scriptname stubcalc
set output_directory output

######################## Command line parsing ########################
package require cmdline
set usage "usage: [file tail $argv0] \[options]"
set options {
    {n "Make a new database and exit"}
    {y.arg 2019 "Year to analyze"}
    {w.arg 30 "Previous days to analyze"}
}

############################### SQLite ###############################
package require sqlite3

try {
    array set params [::cmdline::getoptions argv $options $usage]
} trap {CMDLINE USAGE} {message optdict} {
    # Trap the usage signal, print the message, and exit the application.
    # Note: Other errors are not caught and passed through to higher levels!
    puts $message
    exit 1
}

# After cmdline is done, argv will point to the last argument
set input_file_name $argv

# Our window extends this many days back from today
set scan_window_days $params(w)

proc iterint {start points} {
    # Return a list of increasing integers starting with start with
    # length points
    set count 0
    set intlist [list]
    while {$count < $points} {
	lappend intlist [expr $start + $count]
	incr count
    }
    return $intlist
}

proc center_text {text width} {
    # Return text centered in a string of size width
    set centered ""
    set text_width [string length $text]
    set pad [expr int(($width - $text_width)/double(2))]
    foreach padchar [iterint 0 $pad] {
	append centered " "
    }
    append centered $text
    foreach padchar [iterint 0 $pad] {
	append centered " "
    }
    return $centered
}

proc left_justify {text width} {
    # Return text left-justified in a string of size width
    set justified ""
    set text_width [string length $text]
    set pad [expr int(($width - $text_width))]
    append justified $text
    foreach padchar [iterint 0 $pad] {
	append justified " "
    }
    return $justified
}

proc right_justify {text width} {
    # Return text right-justified in a string of size width
    set justified ""
    set text_width [string length $text]
    set pad [expr int(($width - $text_width))]
    foreach padchar [iterint 0 $pad] {
	append justified " "
    }
    append justified $text
    return $justified
}

proc dashline {width} {
    # Return a string of dashes of length width
    set dashline ""
    foreach dashchar [iterint 0 $width] {
	append dashline "-"
    }
    return $dashline
}

proc create_new_db {} {
    set output_file "stubcalc.db"
    # Open a connection to the database file
    sqlite3 db $output_file
    # Single quotes in SQLite are around string literals.  The name of
    # the column should be in single quotes.
    #
    # Name of the company paying you
    lappend column_list "'company' TEXT"

    # Date of the payment
    lappend column_list "'pay date' TEXT"

    # Amount of the payment
    lappend column_list "'earnings' REAL"

    # Extra money paid out because I don't use the company health insurance
    lappend column_list "'health insurance bonus' REAL"

    # Money withheld for federal taxes
    lappend column_list "'federal withholding' REAL"

    # Money withheld for Social Security
    lappend column_list "'social security' REAL"

    # Money withheld for Medicare
    lappend column_list "'medicare' REAL"

    # Money withheld for state taxes
    lappend column_list "'state withholding' REAL"

    # Money withheld for unemployment insurance
    lappend column_list "'state unemployment' REAL"

    # Money withheld for the state transit benefit
    lappend column_list "'transit withholding' REAL"

    # Money withheld for 401-K
    lappend column_list "'401K withholding' REAL"

    # Create the table
    db eval "CREATE TABLE 'paystubs' ([join $column_list ", "])"

    # Write the changes
    db close
}

# If we've gotten the -n parameter, simply make a new database and exit
if $params(n) {
    create_new_db
    exit
}

proc get_unix_time {date_string} {
    # Return the seconds since 1970 given a date string
    #
    # February 5, 1980 is the string "2/5/1980"
    set timestamp [clock scan $date_string -format {%m/%d/%Y}]
}

proc get_year {date_string} {
    # Return the year number from a date string
    #
    # February 5, 1980 is the string "2/5/1980"
    set year [lindex [split $date_string "/"] end]
}

proc get_total_earnings {dbfile} {
    sqlite3 db $dbfile
    db eval {SELECT * FROM paystubs} {
	puts "Payment is $earnings"
	puts "Year is [get_year ${pay date}]"
    }
    db close
}

proc get_category_dict {dbfile window_days} {
    # Return a category dictionary initialized based on the database
    # columns
    #
    # Arguments:
    #   sqlite database filename
    sqlite3 db $dbfile
    # Get the column information
    set column_information [db eval "PRAGMA table_info(paystubs)"]
    # Each column seems to have 6 attributes, and the title is the
    # second.  So titles will be at indexes 1, 7, 13, etc
    set attr_num 0
    set next_title_num 1
    foreach attribute $column_information {
	if {$attr_num == $next_title_num} {
	    lappend column_name_list $attribute
	    incr next_title_num 6
	}
	incr attr_num
    }

    dict set category_dict earnings 0
    dict set category_dict federal_withholding 0
    dict set category_dict state_withholding 0
    dict set category_dict social_security 0
    dict set category_dict 401k 0
    dict set category_dict transit 0
    dict set category_dict health_care 0
    dict set category_dict taxable 0
    
    set seconds_per_day 86400
    set time_now_s [clock seconds]
    set window_s [expr $window_days * $seconds_per_day]
    db eval {SELECT * FROM paystubs} {
	# This set of statements will get executed once for every row
	# in the table.
	set paystub_time_s [get_unix_time ${pay date}]
	if {$paystub_time_s >= [expr $time_now_s - $window_s]} {
	    # The paystub is in our window.  Increment the sum
	    set stub_earnings $earnings
	    set old_earnings [dict get $category_dict earnings]
	    dict set category_dict earnings [expr $old_earnings + $stub_earnings]

	    # Federal withholding
	    set stub_fed_wh ${federal withholding}
	    set old_fed_wh [dict get $category_dict federal_withholding]
	    dict set category_dict federal_withholding [expr $stub_fed_wh + $old_fed_wh]

	    # State withholding
	    set stub_state_wh ${state withholding}
	    set old_state_wh [dict get $category_dict state_withholding]
	    dict set category_dict state_withholding [expr $stub_state_wh + $old_state_wh]

	    # Social security
	    set stub_ssi ${social security}
	    set old_ssi [dict get $category_dict social_security]
	    dict set category_dict social_security [expr $stub_ssi + $old_ssi]

	    # 401-K
	    set stub_401k ${401K withholding}
	    set old_401k [dict get $category_dict 401k]
	    dict set category_dict 401k [expr $stub_401k + $old_401k]

	    # Transit
	    set stub_transit ${transit withholding}
	    set old_transit [dict get $category_dict transit]
	    dict set category_dict transit [expr $stub_transit + $old_transit]

	    # Health care bonus
	    set stub_hcb ${health insurance bonus}
	    set old_hcb [dict get $category_dict health_care]
	    dict set category_dict health_care [expr $stub_hcb + $old_hcb]

	    # Taxable wages
	    set stub_taxable [expr $stub_earnings - $stub_401k - $stub_transit + $stub_hcb]
	    set old_taxable [dict get $category_dict taxable]
	    dict set category_dict taxable [expr $stub_taxable + $old_taxable]
	}
    }
    db close
    return $category_dict
}

set category_dict [get_category_dict $input_file_name $params(w)]


set category_column_width 25
set usd_column_width 10
set percentage_column_width 10

# - -- left justify
# * -- minimum width is specified as argument
# s -- format as a string (no conversion)
set format_string "%-*s %-*s %-*s %-*s"
set header [format $format_string \
		$category_column_width "Category" \
		$usd_column_width [center_text "USD total" $usd_column_width] \
		$usd_column_width [center_text "USD per 30" $usd_column_width] \
		$usd_column_width [center_text "Percent" $percentage_column_width]]

puts ""
puts "Last $scan_window_days days"
puts ""
puts $header
puts [dashline [string length $header]]

foreach category [dict keys $category_dict] {
    set usd [format {%0.2f} [dict get $category_dict $category]]
    set usd_per_30 [format {%0.2f} [expr ($usd / $scan_window_days) * 30]]
    set percent [format {%0.1f} [expr 100 * $usd / [dict get $category_dict earnings]]]
    puts [format $format_string \
	      $category_column_width $category \
	      $usd_column_width [center_text $usd $usd_column_width] \
	      $usd_column_width [center_text $usd_per_30 $usd_column_width] \
	      $percentage_column_width [center_text $percent $usd_column_width]]
}