#!/usr/bin/env python3
from beancount import loader
from beancount.query import query
from beancount.parser import printer
import argparse
from tabulate import tabulate
from decimal import Decimal
from beancount.core.amount import Amount, add, sub, mul
from math import floor
from datetime import datetime, timedelta

class bcolors:
    HEADER = '\033[95m'
    OKBLUE = '\033[94m'
    OKCYAN = '\033[96m'
    OKGREEN = '\033[92m'
    WARNING = '\033[93m'
    FAIL = '\033[91m'
    ENDC = '\033[0m'
    BOLD = '\033[1m'
    UNDERLINE = '\033[4m'

def draw_line():
  print('─' * 30)

def get_last_month_timestamps(date):
  month = int(date.split("-")[1])
  year = int(date.split("-")[0])
  d = datetime(year, month, 1)
  end_date = d - timedelta(days=1)
  start_date = f"{end_date.year}-{end_date.month}-01"
  return start_date, end_date.strftime("%Y-%m-%d")

def get_sum_balances(balances, account_prefix):
  sum = 0
  for account, balance in balances.items():
    if account.startswith(account_prefix):
      sum = balance if sum == 0 else sum + balance
  if sum == 0 or sum.get_only_position() == None:
    return Amount(Decimal(0), "EUR").to_string()
  result = sum.get_only_position().units
  return Amount(Decimal(round(result.number, 2)), result.currency).to_string()

def get_net_worth(balances):
  total_assets = Amount.from_string(get_sum_balances(balances, "Assets:"))
  total_liabilities = Amount.from_string(get_sum_balances(balances, "Liabilities:"))
  return add(total_assets, total_liabilities)

def get_debt_to_assets_ratio(balances, max):
  total_assets = Amount.from_string(get_sum_balances(balances, "Assets:"))
  total_liabilities = Amount.from_string(get_sum_balances(balances, "Liabilities:"))
  result = round(((total_liabilities.number * -1) / total_assets.number) * 100, 2)
  return f"{bcolors.FAIL if result >= max else bcolors.OKGREEN}{result} %{bcolors.ENDC}"

def get_emergency_fund_ratio(balances, expenses, low, mid):
  liquid = 0
  living_expenses = expenses[0].position.get_only_position().units.number
  for account, balance in balances.items():
    if account.startswith("Assets:Liquid"):
      liquid = balance if liquid == 0 else liquid + balance
  total_liquid = Amount(Decimal(0), "EUR") if liquid.get_only_position() == None else liquid.get_only_position().units
  result = round(total_liquid.number / living_expenses, 2)
  color = bcolors.FAIL if result < low else bcolors.OKGREEN if result > mid else bcolors.WARNING
  return f"{color}{result}{bcolors.ENDC}"

def get_investment_assets_to_net_worth_ratio(balances, min):
  total_investment = Amount.from_string(get_sum_balances(balances, "Assets:Invest:"))
  result = round((total_investment.number / get_net_worth(balances).number) * 100, 2)
  return f"{bcolors.FAIL if result < min else bcolors.OKGREEN}{result} %{bcolors.ENDC}"

def get_liquid_assets_to_net_worth_ratio(balances, min):
  liquid = 0
  for account, balance in balances.items():
    if account.startswith("Assets:Liquid") or account.startswith("Assets:Invest"):
      liquid = balance if liquid == 0 else liquid + balance
  total_liquid = Amount(Decimal(0), "EUR") if liquid.get_only_position() == None else liquid.get_only_position().units
  result = round((total_liquid.number / get_net_worth(balances).number) * 100, 2)
  return f"{bcolors.FAIL if result < min else bcolors.OKGREEN}{result} %{bcolors.ENDC}"

def get_savings_ratio(balances, gross_monthly_income, monthly_savings, min):
  result = round((monthly_savings.number / gross_monthly_income) * 100, 2)
  return f"{bcolors.FAIL if result < min else bcolors.OKGREEN}{result} %{bcolors.ENDC}"

def get_debt_service_ratio(balances, gross_monthly_income, debt_payments, max):
  result = round((debt_payments.number / gross_monthly_income) * 100, 2)
  return f"{bcolors.FAIL if result >= max else bcolors.OKGREEN}{result} %{bcolors.ENDC}"

def get_non_mortgage_debt_service_ratio(balances, gross_monthly_income, mortgage_payments, max):
  result = round((mortgage_payments.number / gross_monthly_income) * 100, 2)
  return f"{bcolors.FAIL if result >= max else bcolors.OKGREEN}{result} %{bcolors.ENDC}"

def get_solvency_ratio(balances, min):
  total_assets = Amount.from_string(get_sum_balances(balances, "Assets:"))
  result = round((get_net_worth(balances).number / total_assets.number) * 100, 2)
  return f"{bcolors.FAIL if result < min else bcolors.OKGREEN}{result} %{bcolors.ENDC}"

def get_max_leveraged_investment(balances):
  total_assets = Amount.from_string(get_sum_balances(balances, "Assets:"))
  return Amount(round(total_assets.number * Decimal(0.9), 2), total_assets.currency).to_string()

def get_position_as_str(inventory):
  position = inventory.get_only_position()
  if position is None:
    return position
  else:
    return Amount(Decimal(round(position.units.number, 2)), position.units.currency).to_string()

def print_report(date, balances, expenses, income, debt_payments, mortgage_payments, savings):
  print(f"{bcolors.BOLD}Balance Sheet (date={date}){bcolors.ENDC}")
  draw_line()
  print(f"{bcolors.BOLD}Assets{bcolors.ENDC}")
  print(f"\t{bcolors.BOLD}Liquids{bcolors.ENDC}")
  print(tabulate([
    ["Corrent", get_sum_balances(balances, "Assets:Liquid:Caixabank:Corrent")],
    ["Estalvi", get_sum_balances(balances, "Assets:Liquid:Estalvi")],
    ["Compte d'inversió", get_sum_balances(balances, "Assets:Liquid:R4:EUR")],
    ["Total líquids", get_sum_balances(balances, "Assets:Liquid:")],
  ]))
  print(f"\t{bcolors.BOLD}Inversions{bcolors.ENDC}")
  print(tabulate([
    ["Fons d'inversió", get_sum_balances(balances, "Assets:Invest:Fund:")],
    ["ETFs", get_sum_balances(balances, "Assets:Invest:ETF:")],
    ["Accions", get_sum_balances(balances, "Assets:Invest:Stock:")],
    ["Renta fixa", get_sum_balances(balances, "Assets:Invest:Fixed:")],
    ["Total inversions", get_sum_balances(balances, "Assets:Invest:")],
  ]))
  print(f"\t{bcolors.BOLD}Propietat personal{bcolors.ENDC}")
  print(tabulate([
    ["Vivenda principal", get_sum_balances(balances, "Assets:PersonalProperty:VivendaPrincipal")],
    ["Cotxes", get_sum_balances(balances, "Assets:PersonalProperty:Cotxe")],
    ["Joies, Art, Col·leccionables", get_sum_balances(balances, "Assets:PersonalProperty:JoiesArtCollecionables")],
    ["Metalls preciosos", get_sum_balances(balances, "Assets:PersonalProperty:MetallsPreciosos")],
    ["Altres propietats", get_sum_balances(balances, "Assets:PersonalProperty:AltresPropietats")],
    ["Total propietats", get_sum_balances(balances, "Assets:PersonalProperty:")],
  ]))
  print(f"\t{bcolors.BOLD}Deutes{bcolors.ENDC}")
  print(tabulate([
    ["Deutes per cobrar", get_position_as_str(balances["Assets:Debt:DeutesPerCobrar"])],
    ["Total deutes", get_sum_balances(balances, "Assets:Debt:")],
  ]))
  print(f"\t{bcolors.BOLD}Beneficis laborals{bcolors.ENDC}")
  print(tabulate([
    ["Tickets Restaurant", get_position_as_str(balances["Assets:Benefits:Edenred:TicketsRestaurant"])],
    ["Targeta Transport", get_position_as_str(balances["Assets:Benefits:Edenred:TargetaTransport"])],
    ["Pla Pensions Empleados Zurich", get_position_as_str(balances["Assets:Benefits:DZP:PPEZurich"]) if "Assets:Benefits:DZP:PPEZurich" in balances else "-"],
    ["Total beneficis", get_sum_balances(balances, "Assets:Benefits:")],
  ]))
  print(tabulate([
    [f"\t{bcolors.BOLD}Total Assets", get_sum_balances(balances, "Assets:")]
  ]))

  draw_line()
  print(f"{bcolors.BOLD}Liabilites{bcolors.ENDC}")
  print(tabulate([
    ["Hipoteques en vivenda principal", get_position_as_str(balances["Liabilities:Hipoteca:VivendaPrincipal"] * Decimal(-1))],
    ["Hipoteques en vivenda d'inversió", Amount(Decimal(0), "EUR").to_string()],
    ["Crèdit", get_position_as_str(balances["Liabilities:Credit:Caixabank:TargetaCredit"] * Decimal(-1))],
    ["Factures impagades", get_position_as_str(balances["Liabilities:Factures:FacturesPendents"] * Decimal(-1))],
    ["Préstecs personals", Amount(Decimal(0), "EUR").to_string()],
    ["Impostos no pagats", get_position_as_str(balances["Liabilities:Taxes:IRPF"] * Decimal(-1))],
    ["Altres passius", Amount(Decimal(0), "EUR").to_string()]
  ]))
  print(tabulate([
    [f"{bcolors.BOLD}Total passius{bcolors.ENDC}", f"{bcolors.BOLD}{get_sum_balances(balances, "Liabilities:")}{bcolors.ENDC}"],
  ]))

  draw_line()
  print(f"{bcolors.BOLD}Net Worth\t{get_net_worth(balances)}{bcolors.ENDC}")

  draw_line()
  print(f"{bcolors.BOLD}Financial Ratios{bcolors.ENDC}")
  print(tabulate([
    ["Debt-to-Assets Ratio", get_debt_to_assets_ratio(balances, 50), "50 %"],
    ["Emergency Fund", get_emergency_fund_ratio(balances, expenses, 3, 6), "3-6"],
    ["Investment Assets to Net Worth Ratio", get_investment_assets_to_net_worth_ratio(balances, 50), "50 %"],
    ["Liquid Assets to Net Worth Ratio", get_liquid_assets_to_net_worth_ratio(balances, 15), "15 %"],
    ["Savings Ratio", get_savings_ratio(balances, income, savings, 20), "20 %"],
    ["Debt-Service Ratio", get_debt_service_ratio(balances, income, debt_payments, 35), "35 %"],
    ["Non-Mortgage Debt-Service Ratio", get_non_mortgage_debt_service_ratio(balances, income, mortgage_payments, 15), "15 %"],
    ["Solvency Ratio", get_solvency_ratio(balances, 50), "50 %"]
  ]))

def get_balances(entries, options, date):
  balance_query = f"SELECT account, convert(sum(position), \"EUR\") as position FROM date <= {date} WHERE account ~ '^(Liabilities|Assets)'"
  rtypes, rrows = query.run_query(
    entries, options, balance_query)
  balances = {}
  for row in rrows:
    balances[row.account] = row.position
  return balances

def get_expenses(entries, options, date):
  start_date, end_date = get_last_month_timestamps(date)
  expenses_query = f"SELECT convert(sum(position), \"EUR\") as position FROM date <= {end_date} WHERE account ~ 'Expenses:' AND date >= {start_date}"
  rtypes, rrows = query.run_query(
    entries, options, expenses_query)
  return rrows

def get_income(entries, options, date):
  start_date, end_date = get_last_month_timestamps(date)
  income_query = f"SELECT convert(sum(position), \"EUR\") as position FROM date <= {end_date} WHERE account ~ '^(Income:Work|Income:Savings|Income:Invest)' AND date >= {start_date}"
  rtypes, rrows = query.run_query(
    entries, options, income_query)
  return rrows

def get_debt_payments(entries, options, date):
  start_date, end_date = get_last_month_timestamps(date)
  debt_payments_query = f"SELECT convert(sum(position), \"EUR\") as position FROM date <= {end_date} WHERE account ~ 'Liabilities:' AND date >= {start_date}"
  mortgage_payments_query = f"SELECT convert(sum(position), \"EUR\") as position FROM date <= {end_date} WHERE account ~ 'Liabilities:Hipoteca:' AND date >= {start_date}"
  rtypes, rrows_debt = query.run_query(
    entries, options, debt_payments_query)
  rtypes, rrows_mortgage = query.run_query(
    entries, options, mortgage_payments_query)
  debt_payments = rrows_debt[0].position.get_only_position().units if len(rrows_debt) > 0 else Amount(Decimal(0), "EUR")
  mortgage_payments = rrows_mortgage[0].position.get_only_position().units if len(rrows_mortgage) > 0 else Amount(Decimal(0), "EUR")
  return debt_payments, mortgage_payments

def get_savings(entries, options, date):
  start_date, end_date = get_last_month_timestamps(date)
  savings_query = f"SELECT convert(sum(position), \"EUR\") as position FROM date <= {end_date} WHERE account ~ '^Assets:Invest:' AND date >= {start_date}"
  rtypes, rrows = query.run_query(
    entries, options, savings_query)
  result = rrows[0].position.get_only_position().units if len(rrows) > 0 else Amount(Decimal(0), "EUR")
  return result

def main():
  parser = argparse.ArgumentParser(description='Generate balance sheet report')
  parser.add_argument('date', metavar='date', type=str, nargs=1,
                      help='Report date in ISO format (e.g. 1970-01-01)')

  args = parser.parse_args()
  date = args.date[0]

  filename = "ledger/main.beancount"
  entries, errors, options = loader.load_file(filename)

  if errors:
    printer.print_errors(errors)

  balances = get_balances(entries, options, date)
  expenses = get_expenses(entries, options, date)
  income = get_income(entries, options, date)
  gross_monthly_income = income[0].position.get_only_position().units.number * -1
  debt_payments, mortgage_payments = get_debt_payments(entries, options, date)
  savings = get_savings(entries, options, date)
  print_report(date, balances, expenses, gross_monthly_income, debt_payments, mortgage_payments, savings)

main()