expense

#! /usr/bin/env ruby
require 'pg'
require 'io/console'

class ExpenseData
  attr_reader :connection

  def initialize
    @connection = PG.connect(dbname: "expenses")
    setup_schema
  end

  def list_expenses
    result = connection.exec("SELECT * FROM expenses ORDER BY created_on;")
    print_count(result)
    print_expenses(result)
    print_total(result)
  end

  def add_expense(amount, memo)
    unless amount && memo
      puts "You must provide an amount and a memo."
      return
    end

    sql = "INSERT INTO expenses (amount, memo) VALUES ($1, $2)"
    connection.exec_params(sql, [amount, memo])
  end

  def search_expenses(query)
    unless query
      puts "You must provide a search query."
      return
    end

    sql = "SELECT * FROM expenses WHERE memo ILIKE $1;"
    result = connection.exec_params(sql, ["%#{query}%"])
    print_count(result)
    print_expenses(result)
    print_total(result)
  end

  def delete_expense(id)
    unless id
      puts "You must specify the id of the expense you'd like to delete."
      return
    end

    sql = "SELECT * FROM expenses where id = $1"
    result = connection.exec_params(sql, [id])

    if result.ntuples == 1
      sql = "DELETE FROM expenses WHERE id = $1;"
      connection.exec_params(sql, [id])
      puts "The following expense has been deleted:"
      print_expenses(result)
    else
      puts "There is no expense with the id '#{id}'."
      return
    end
  end

  def delete_all_expenses
    puts "This will delete all expenses. Are you sure? (Y/n)"
    unless $stdin.getch == 'Y'
      return
    end

    connection.exec("DELETE FROM expenses;")
    puts "All expenses have been deleted."
  end

  private

    def print_expenses(result)
      result.each do |tuple|
        columns = [ tuple['id'].rjust(3),
                    tuple['created_on'].rjust(10),
                    tuple['amount'].rjust(12),
                    tuple['memo'] ]

        puts columns.join(' | ')
      end
    end

    def print_total(result)
      if result.ntuples > 0
        total = result.field_values('amount').map(&:to_f).inject(&:+)
        puts '-' * 50
        puts "Total #{('%.2f' % total).rjust(25)}"
      end
    end

    def print_count(result)
      if result.ntuples == 0
        puts "There are no expenses."
      else
        puts "There are #{result.ntuples} expenses."
      end
    end

    def setup_schema
      result = connection.exec <<~SQL
        SELECT COUNT(*)
        FROM information_schema.tables
        WHERE table_schema = 'public' AND table_name = 'expenses';
      SQL

      if result.field_values('count').first.to_i == 0
        connection.exec <<~SQL
          CREATE TABLE expenses (
            id serial PRIMARY KEY,
            amount numeric (6,2) NOT NULL,
            memo varchar NOT NULL,
            created_on date NOT NULL DEFAULT CURRENT_DATE,
            CHECK (amount > 0)
          );
       SQL
      end
    end
end

class CLI
  attr_reader :command, :arguments, :application

  def initialize
    @command = ARGV[0]
    @arguments = ARGV[1..-1]
    @application = ExpenseData.new
  end

  def run
    case command
    when "list"
      application.list_expenses
    when "add"
      amount = arguments[0]
      memo = arguments[1]
      application.add_expense(amount, memo)
    when "search"
      query = arguments[0]
      application.search_expenses(query)
    when "delete"
      id = arguments[0]
      application.delete_expense(id)
    when "clear"
      application.delete_all_expenses
    else
      display_help
    end
  end

  def display_help
    puts <<~HELP
      An expense recording system

      Commands:

      add AMOUNT MEMO [DATE] - record a new expense
      clear - delete all expenses
      list - list all expenses
      delete NUMBER - remove expense with id NUMBER
      search QUERY - list expenses with a matching memo field
    HELP
  end
end

cli = CLI.new
cli.run