"""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

import csv

from dateutil.relativedelta import relativedelta

import datetime

import decimal

import io

import sys

import tempfile

import textwrap

import typing

from typing import List

import os

from beancount import loader

from beancount.query.query import run_query

def end_of_month(date: datetime.date) -> datetime.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: typing.List, homedir: str) -> typing.List:

    """Return a line in a grep-style.

    This is so the line can be fed into Emacs grep-mode for quickly jumping to

    the relevant lines in the books.

    """

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

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

def max_column_widths(rows: List) -> List[int]:

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

    if not rows:

        return []

    else:

        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: List, headers: List = None) -> str:

    """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()

def reconciliation_report(account, end_date, bank_account_balance, uncleared, prev_end_date, our_account_balance, prev_uncleared):

    *_, account_name = account.rpartition(':')

    # end_date_iso = end_date.isoformat()

    # prev_end_date_iso = prev_end_date.isoformat()

    output = io.StringIO()

    w = csv.writer(output)

    w.writerow([f'title:{end_date}: {account_name}'])

    w.writerow(['BANK RECONCILIATION', 'ENDING', end_date])

    w.writerow([f'  {account}'])

    w.writerow([])

    w.writerow(['DATE', 'CHECK NUM', 'PAYEE', 'AMOUNT'])

    w.writerow([])

    w.writerow([end_date, '', 'BANK ACCOUNT BALANCE', bank_account_balance])

    w.writerow([])

    for trans in uncleared:

        w.writerow(trans)

    w.writerow([])

    w.writerow([end_date, '', 'OUR ACCOUNT BALANCE', our_account_balance])

    if prev_uncleared:

        w.writerow([])

        w.writerow(['Items Still', 'Outstanding On', prev_end_date, 'Appeared By', end_date])

        w.writerow([])

        for trans in prev_uncleared:

            w.writerow(trans)

    return output.getvalue()

def reconciliation_report_path(account, end_date):

    *_, account_name = account.rpartition(':')

    return f'Financial/Controls/Reports-for-Treasurer/{end_date}_{account_name}_bank-reconciliation.csv'

def parse_args(argv):

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

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

    parser.add_argument('--account', help='Full account name, e.g. "Liabilities:CreditCard:AMEX"', 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(args=argv[1:])

    if args.month or args.period:

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

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

    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')

    return args

def beancount_file_exists(path):

    return os.path.isfile(path)

def main(args):

    if not beancount_file_exists(args.beancount_file):

        sys.exit(f'Beancount file does not exist: {args.beancount_file}')

    if args.month or args.period:

        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:

        preDate = args.prev_end_date

        lastDateInPeriod = args.cur_end_date.isoformat()

        month = args.cur_end_date.strftime('%Y-%m')

    grep_output_file: typing.IO

    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/"

[metadata]

name = conservancy_beancount

version = 1.19.8

author = Software Freedom Conservancy

author_email = info@sfconservancy.org

description = Plugin, library, and reports for reading Conservancy’s books

license = AGPLv3-or-later WITH Beancount-Plugin-Additional-Permission-1.0

license_files =

  LICENSE.txt

  AGPLv3.txt

long_description = file: README.rst

long_description_content_type = text/x-rst; charset=UTF-8

project_urls =

  Source = %(url)s

url = https://k.sfconservancy.org/NPO-Accounting/conservancy_beancount

[bdist_wheel]

universal = 1

[mypy]

disallow_any_unimported = False

disallow_untyped_calls = False

disallow_untyped_defs = True

show_error_codes = True

strict_equality = True

warn_redundant_casts = True

warn_return_any = True

warn_unreachable = True

warn_unused_configs = True

[options]

include_package_data = True

install_requires =

  babel>=2.6

  beancount>=2.2

  GitPython>=2.0

  odfpy>=1.4.0,!=1.4.1

  pdfminer.six>=20200101

  python-dateutil>=2.7

  PyYAML>=3.0

  regex

  rt>=2.0,<3.0

  thefuzz

packages = find:

python_requires = >=3.6

[options.extras_require]

test =

  mypy>=0.770

  pytest>=3.0

  pytest-mypy

  types-requests

  types-python-dateutil

  types-setuptools

  types-PyYAML

[options.entry_points]

console_scripts =

  accrual-report = conservancy_beancount.reports.accrual:entry_point

  assemble-audit-reports = conservancy_beancount.tools.audit_report:entry_point

  balance-sheet-report = conservancy_beancount.reports.balance_sheet:entry_point

  budget-report = conservancy_beancount.reports.budget:entry_point

  bean-sort = conservancy_beancount.tools.sort_entries:entry_point

  extract-odf-links = conservancy_beancount.tools.extract_odf_links:entry_point

  fund-report = conservancy_beancount.reports.fund:entry_point

  ledger-report = conservancy_beancount.reports.ledger:entry_point

  opening-balances = conservancy_beancount.tools.opening_balances:entry_point

  pdfform-extract = conservancy_beancount.pdfforms.extract:entry_point

  pdfform-extract-irs990scheduleA = conservancy_beancount.pdfforms.extract.irs990scheduleA:entry_point

  pdfform-fill = conservancy_beancount.pdfforms.fill:entry_point

  query-report = conservancy_beancount.reports.query:entry_point

  reconcile-paypal = conservancy_beancount.reconcile.paypal:entry_point

  reconcile-statement = conservancy_beancount.reconcile.statement:entry_point

  split-ods-links = conservancy_beancount.tools.split_ods_links:entry_point

  statement-reconciler = conservancy_beancount.reconcile.statement_reconciler:entry_point

  reconcile-helper = conservancy_beancount.reconcile.helper:entry_point

[options.package_data]

* = py.typed

[options.packages.find]

exclude =

  tests

[testenv]

deps =

  mypy>=0.770

  pytest>=3.0

  pytest-mypy

  types-requests

  types-python-dateutil

  types-setuptools

  types-PyYAML

# Beancount includes type declarations but not the `py.typed` flag file mypy

# is looking for to know that. Create it ourselves.

commands_pre = python -c 'import beancount, pathlib; pathlib.Path(beancount.__file__).with_name("py.typed").touch()'

commands =

  pytest

  pytest --mypy conservancy_beancount

[tool:pytest]

filterwarnings =

  ignore::DeprecationWarning:^socks$

[tox:tox]

envlist = py36,py37