"""Tool to help identify unreconciled postings.

Run like this:

  python conservancy_beancount/reconcile/helper.py \

    --beancount-file=$HOME/conservancy/beancount/books/2021.beancount \

    --prev-end-date=2021-05-13 --cur-end-date=2021-12-31 \

    --account="Liabilities:CreditCard:AMEX"

In the spirit of bc-reconcile-helper.plx (the original Perl code)

Not implemented:

 - --report-group-regex

 - git branch selection from bean-query-goofy-daemon.plx

"""

import argparse

from dateutil.relativedelta import relativedelta

import datetime

import io

import tempfile

import textwrap

import os

from beancount import loader

from beancount.query.query import run_query

def end_of_month(date):

    """Given a date, return the last day of the month."""

    # Using 'day' replaces, rather than adds.

    return date + relativedelta(day=31)

def format_record_for_grep(row, homedir):

    file = row[0].replace(homedir, '~')

    return [f'{file}:{row[1]}:'] + row[2:]

def max_column_widths(rows):

    """Return the max width for each column in a table of data."""

    maxes = [0] * len(rows[0])

    for row in rows:

        for i, val in enumerate(row):

            length = len(str(val))

            maxes[i] = max(maxes[i], length)

    return maxes

def tabulate(rows, headers=None):

    """Format a table of data as a string.

    Implemented here to avoid adding dependency on "tabulate" package.

    """

    output = io.StringIO()

    if headers:

        rows = [headers] + rows

    widths = max_column_widths(rows)

    for row in rows:

        for i, col in enumerate(row):

            width = widths[i]

            if col is None:

                print(' ' * width, end=' ', file=output)

            elif isinstance(col, str):

                print((str(col)).ljust(width), end=' ', file=output)

            else:

                print((str(col)).rjust(width), end=' ', file=output)

        print('', file=output)

    return output.getvalue().strip()

# Parse all the arguments

parser = argparse.ArgumentParser(description='Reconciliation helper')

parser.add_argument('--beancount-file', required=True)

parser.add_argument('--account', help='Account regexp', required=True)

parser.add_argument('--prev-end-date', type=datetime.date.fromisoformat)

parser.add_argument('--cur-end-date', type=datetime.date.fromisoformat)

parser.add_argument('--month', help='YYYY-MM of ending month. Use with --period.')

parser.add_argument('--period', help='Months in the past to consider. Use with --month.', type=int, choices=[1, 3, 12])

parser.add_argument('--statement-match')

parser.add_argument('--cost-function', default='COST')

parser.add_argument('--grep-output-filename')

# parser.add_argument('--report-group-regex')

args = parser.parse_args()

if args.month or args.period:

    if not (args.month and args.period):

        parser.error('--month and --period must be used together')

    parsed_date = datetime.datetime.strptime(args.month, '%Y-%m').date()

    preDate = end_of_month(parsed_date - relativedelta(months=args.period)).isoformat()

    lastDateInPeriod = end_of_month(parsed_date).isoformat()

    month = args.month

else:

    if not (args.cur_end_date and args.prev_end_date):

        parser.error(' --prev-end-date and --cur-end-date must be used together')

    preDate = args.prev_end_date

    lastDateInPeriod = args.cur_end_date

    month = lastDateInPeriod.strftime('%Y-%m')

if args.grep_output_filename:

    grep_output_file = open(args.grep_output_filename, 'w')

else:

    grep_output_file = tempfile.NamedTemporaryFile(prefix='bc-reconcile-grep-output_', mode='w', delete=False)

beancount_file = args.beancount_file

account = args.account

cost_function = args.cost_function

statement_match = args.statement_match if args.statement_match else month

QUERIES = {

    f"00: CLEARED BAL ENDING DAY BEFORE {preDate}":

    # $CONLEDGER -V -C -e "$preDate" bal "/$acct/"

    f"""SELECT sum({cost_function}(position)) AS aa WHERE account = "{account}"

        AND date < {preDate} AND META('bank-statement') != NULL""",

    f"01: ALL TRANSACTION BAL ENDING DAY BEFORE {preDate}":

    # $CONLEDGER -V -e "$preDate" bal "/$acct/"

    f"""SELECT sum({cost_function}(position)) AS aa WHERE account = "{account}"

        AND date < {preDate}""",

    f"02: ALL TRANSACTION BAL, ending {lastDateInPeriod}":

    # $CONLEDGER -V -e "$date" bal "/$acct/"

    f"""SELECT sum({cost_function}(position)) AS aa WHERE account = "{account}"

    AND date <= {lastDateInPeriod}""",

    f"03: UNCLEARED TRANSACTIONS, ending {lastDateInPeriod}":

    f"""SELECT date, {cost_function}(position) as amt, ANY_META('check-id') as chknum, narration, payee, ENTRY_META('code') as code

    WHERE account = "{account}"

    AND date <= {lastDateInPeriod} AND META('bank-statement') = NULL

    ORDER BY date, payee, narration""",

    "04: UNCLEARED TRANSACTION FILE, SUITABLE FOR GREP":

    # $CONLEDGER -w -F "%(filename):%(beg_line): %(date) %(code) %(payee) %(amount)\n" --sort d -U -e "$date" reg "/$acct/" > "$TMPDIR/unreconciled-lines"

    f"""SELECT  ENTRY_META('filename') as file, META('lineno') as line, date,

    {cost_function}(position) as amt, ANY_META('check-id') as chknum, narration, payee, ANY_META("entity") as entity, ENTRY_META('code') as c

    WHERE account = "{account}"

    AND date <= {lastDateInPeriod} AND META('bank-statement') = NULL

    ORDER BY date, payee, narration""",

    f"05: CLEARED BALANCE ending {lastDateInPeriod}":

    # $CONLEDGER -V -C -e "$date" bal "/$acct/"

    f"""SELECT sum({cost_function}(position)) AS aa WHERE account = "{account}"

    AND date <= {lastDateInPeriod} AND META('bank-statement') != NULL""",

    f"06: CLEARED SUBTRACTIONS on {month}'s statement":

    # $CONLEDGER -V -C --limit "a > 0 and tag(\"Statement\") =~ /$statementSearchString/" bal "/$acct/"

    f"""SELECT  sum(number({cost_function}(position))) AS aa

    WHERE account = "{account}"

    and META("bank-statement") ~  "{statement_match}" and number({cost_function}(position)) < 0""",

    f"07: CLEARED ADDITIONS on {month}'s statement":

    # $CONLEDGER -V -C --limit "a < 0 and tag(\"Statement\") =~ /$statementSearchString/" bal "/$acct/"

    f"""SELECT  sum(number({cost_function}(position))) AS aa

    WHERE account = "{account}"

    and META("bank-statement") ~  "{statement_match}" and number({cost_function}(position)) > 0""",

}

# Run Beancount queries.

print(f"START RECONCILIATION FOR {account} ENDING {lastDateInPeriod} (previous end date {preDate})")

entries, _, options = loader.load_file(beancount_file)

for desc, query in QUERIES.items():

    rtypes, rrows = run_query(entries, options, query, numberify=True)

    if desc.startswith('04'):

        homedir = os.getenv('HOME')

        print(f'{desc}\n   See {grep_output_file.name}')

        grep_rows = [format_record_for_grep(row, homedir) for row in rrows]

        print(tabulate(grep_rows), file=grep_output_file)

    elif len(rrows) == 1:

        result = rrows[0][0]

        print(f'{desc:<55} {result:>11,.2f}')

    else:

        headers = [c[0].capitalize() for c in rtypes]

        print(desc)

        print(textwrap.indent(tabulate(rrows, headers=headers), '    '))