#!/usr/bin/env perl
use strict;
use warnings;
use Finance::Tiller2QIF;

# PODNAME: tiller2qif
# ABSTRACT: Convert Tiller CSV exports to QIF format


=pod

=encoding utf8

=head1 NAME

tiller2qif

Finance::Tiller2QIF

=head1 DESCRIPTION

Convert Tiller CSV exports to QIF for import into Financial software like GnuCash, KMyMoney, Quicken, HomeBank, Money Manager Ex and many others.

=head1 SYNOPSIS

  # Command-line
  tiller2qif run --input export.csv --db tiller.sqlite3 \
                 --output import.qif [--mapfile mapping.txt]

  # Programmatic — see PROGRAMMATIC USE below
  use Finance::Tiller2QIF;

  Finance::Tiller2QIF::run(
    input   => 'export.csv',
    db_path => 'tiller.sqlite3',
    mapfile => 'mapping.txt',
    output  => 'import.qif',
  );

=head1 OVERVIEW

Tiller Money (tillerapp.com) aggregates bank and credit-card transactions
into a Google Sheet and lets you export a CSV. This module ingests that CSV
into a SQLite database, optionally applies a category-mapping file to
translate Tiller's auto-assigned categories to match your accounts/categories, then emits a QIF file ready for import.

The three phases can be run individually or together:

=over 4

=item B<ingest> — parse the CSV and load rows into the SQLite database.

=item B<map> — apply a user-supplied mapping file that rewrites categories,
suppresses duplicates (card-payment credits), and assigns destination accounts.

=item B<emit> — read unexported rows from the database and write a QIF file.

=back

=head1 INSTALLATION

=head2 From CPAN

  cpan Finance::Tiller2QIF
  # or
  cpanm Finance::Tiller2QIF

=head2 Perl Dependencies

Runtime: Cpanel::JSON::XS, DateTime::Format::Flexible, Getopt::Long::Descriptive,
Path::Tiny, Mojo::SQLite, Text::CSV

Testing: Capture::Tiny, Test2::V0, Test2::Bundle::More, Test2::Tools::Exception

=head3 On Debian/Ubuntu:

All of Tiller2QIF’s dependencies are available through package management if you need to install to system Perl.

  sudo apt install libpath-tiny-perl libtext-csv-perl libtest2-suite-perl libcapture-tiny-perl \
    libmojo-sqlite-perl libcpanel-json-xs-perl libdatetime-format-flexible-perl
  sudo cpan install Finance::Tiller2QIF

=head3 On Windows

tiller2qif works with Strawberry Perl, after installing Strawberry Perl, install from CPAN.

=head1 CLI COMMANDS

=over 4

=item B<run> -- ingest, map, and emit in one step

  tiller2qif run --input export.csv --db tiller.sqlite3 \
                 --output import.qif [--mapfile mapping.txt]

  run will always create a checkpoint even when the flag is not set.

=item B<ingest> -- load CSV into the database

  tiller2qif ingest --input export.csv --db tiller.sqlite3

=item B<map> -- apply category mapping rules

  tiller2qif map --db tiller.sqlite3 [--mapfile mapping.txt] \
                 [--beforemap before.sql] [--aftermap after.sql]

=item B<emit> -- write QIF from the database

  tiller2qif emit --db tiller.sqlite3 --output import.qif

=item B<newdb> -- initialise a new SQLite database

  tiller2qif newdb --db tiller.sqlite3

=item B<newconfig> -- create a starter config file

  tiller2qif newconfig [--config ~/.config/tiller2qif.conf]

=item B<checkconfig> -- check the merged values of cli arguments and config file

  # The verbose flag will run checkconfig before beginning any operations.
  tiller2qif checkconfig [--config ~/.config/tiller2qif.conf]

=item B<clean> -- remove checkpoint copies of the database

Deletes all timestamped checkpoint files created by C<--checkpoint> or C<run>,
leaving the live database intact.

  tiller2qif clean --db tiller.sqlite3

=item B<version> -- print the installed version number

  tiller2qif version

=back

=head1 OPTIONS

    "input":    "~/Downloads/mytillerdump.csv",
    "output":   "/tmp/tillerout.qif",
    "db":       "~/.data/tiller2qif.sqlite3",
    "mapfile": "~/.config/tiller.mapping"
  }

Pass the config file with C<--config>.  Command-line options override config
file values.

=over 4

=item B<--input> CSV export from Tiller.

=item B<--output> QIF file to create.

=item B<--db> SQLite database file used to store and transform transactions between phases.

=item B<--mapfile> File containing category mapping rules.  Optional; omitting it passes
transactions through with their original Tiller categories.

=item B<--beforemap> Path to a SQL script to execute against the database immediately
before the mapping rules are applied.  Useful for preprocessing transactions — for
example, renaming accounts or correcting data — in ways that affect which map rules
fire.

=item B<--aftermap> Path to a SQL script to execute against the database immediately
after the mapping rules are applied.  Useful for post-processing the mapped results —
for example, marking or transforming rows based on what the map phase produced.

=back

=head1 MAPPING FILE

The mapping file controls how Tiller categories are translated into destination
account or category names and which transactions to suppress.  Each non-comment
line has the form:

  [AccountFilter] field | pattern | destination

Lines beginning with C<#> and blank lines are ignored.  Rules are evaluated in
order; the first matching rule wins and no further rules are checked for that
transaction.

=over 4

=item B<AccountFilter> (optional) — a Perl regex in square brackets that
restricts the rule to transactions on matching accounts.  Alternation works
naturally: C<[Checking|Savings]>. Omit to match all accounts.

=item B<field> — the transaction field to test: C<category>, C<payee>,
C<memo>, C<date>, or C<amount>.

=item B<pattern> — a Perl regex applied case-insensitively to the field value.

For a simple pattern containing no C<|>, write it as-is:

  payee | Starbucks | Expenses:Coffee

To use regex alternation (matching either of several values), enclose the
pattern in forward slashes:

  payee | /Starbucks|Dunkin/ | Expenses:Coffee

To match a literal pipe character in the data, escape it with a backslash:


=item C<source> — keep the original Tiller category unchanged.

=item C<blank> — emit no category field in the QIF output.

=item C<skip> — exclude the transaction from QIF output entirely (useful for
suppressing the credit-side of card payments that appear in both accounts).

=back

For double-entry programs such as GnuCash, destination is a full account name
(e.g. C<Expenses:Groceries>).  For single-entry programs such as Quicken it is
a category name.


The optional C<default> line sets the fallback for transactions that match no
rule.  It must appear as the last non-comment line:

  default | source

If the C<default> line is omitted, unmatched transactions behave as
C<default | source>.

=head2 EXAMPLES

=over 4

=item * Map by category

  category | Groceries | Expenses:Groceries

=item * Map by payee with alternation (slash-delimited pattern)

  payee | /Starbucks|Dunkin/ | Expenses:Coffee

=item * Map by payee, scoped to one account

  [Checking] payee | Payroll | Income:Salary

=item * Scope to multiple accounts using alternation in the account filter

  [Checking|Savings] category | Transfer | skip

=item * Skip card-payment credits on the card account

  [CapitalOne] category | Transfer | skip

=item * Match a literal pipe character in a payee name

  payee | Cash\|App | Expenses:Transfers

=item * Suppress category in QIF (no L field)

  category | Miscellaneous | blank

=item * Default: leave unmatched transactions with their Tiller category

  default | source

=back

=head1 Advanced Use

You can write SQL scripts or use an interactive sqlite3 client to make changes between steps. For example your Tiller sheet might have an account "Checking", while your table of accounts has "Assets::Current Assets::Bank::Checking". Custom SQL you keep the short name in Tiller even though mapping rules can't rename accounts.

The C<--beforemap> and C<--aftermap> options allow SQL scripts to run immediately before
and after the map phase without having to break the workflow into separate commands.
This is the preferred way to preprocess or post-process transactions when using C<run>
or C<map> as a single step.

While other CSV export sources are not directly supported, you can write a script to remap the fields for ingestion or just import into the table, and then use the map and emit stages to complete your export. If translating other CSV sources be aware that Tiller currently only provides it's data in the US 'MM/DD/YYYY' format, this program can also accept dates in ISO 8601 'YYYY-MM-DD'. Data is written into the SQLite database using the ISO 8601 format.

=head1 PROGRAMMATIC USE

C<Finance::Tiller2QIF> is primarily a CLI tool; the public functions exist to
support the command dispatcher. Programmatic users will likely use this module
as a starting point and call the sub-modules directly (C<Finance::Tiller2QIF::ReadCSV>,
C<Finance::Tiller2QIF::Map>, C<Finance::Tiller2QIF::WriteQIF>) for finer control.

Note that all functions accept C<db_path> as the database parameter. The CLI
normalises the C<--db> option to C<db_path> internally; programmatic callers
should use C<db_path> directly.

=head2 run

  Finance::Tiller2QIF::run(
    input     => 'export.csv',
    db_path   => 'tiller.sqlite3',
    mapfile   => 'mapping.txt',     # optional
    beforemap => 'pre.sql',         # optional
    aftermap  => 'post.sql',        # optional
    output    => 'import.qif',
  );

Convenience wrapper that calls C<ingest>, C<apply_map>, and C<emit> in sequence
with the same options hash.

=head2 ingest

  Finance::Tiller2QIF::ingest( input => 'export.csv', db_path => 'tiller.sqlite3' );

Parses the Tiller CSV export and loads rows into the SQLite database.
Returns the number of new rows inserted.

=head2 apply_map

  Finance::Tiller2QIF::apply_map(
    db_path   => 'tiller.sqlite3',
    mapfile   => 'mapping.txt',  # optional
    beforemap => 'pre.sql',      # optional — runs before map rules
    aftermap  => 'post.sql',     # optional — runs after map rules
  );

Applies category mapping rules to unexported transactions. If C<mapfile> is
omitted, transactions pass through unchanged. C<beforemap> and C<aftermap>
are paths to SQL scripts executed immediately before and after the mapping
phase respectively; each may contain multiple semicolon-terminated statements.

=head2 emit

  Finance::Tiller2QIF::emit( db_path => 'tiller.sqlite3', output => 'import.qif' );

Writes unexported transactions from the database to a QIF file and marks
them as exported.

=for Pod::Coverage run_cli

=head1 AUTHOR

John Karr E<lt>brainbuz@cpan.orgE<gt>

=head1 LICENSE

GPL version 3 or later.

=cut

Finance::Tiller2QIF::run_cli();

