#!/bin/sh -e
# pem -- GNU Personal Expenses Manager inspired simpler script
# Copyright (C) 2015-2021 Sergey Matveev (stargrave@stargrave.org)
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.

# This script is simpler version of GNU PEM software. It uses sqlite3 as
# a database storage.
#
# Schema that is used for it:
#
#     CREATE TABLE expenses (
#         id INTEGER PRIMARY KEY,
#         dt DATETIME NOT NULL,
#         earned NUMERIC NOT NULL,
#         spent NUMERIC NOT NULL,
#         descr TEXT NOT NULL
#     );
#     CREATE TABLE tags (
#         id INTEGER PRIMARY KEY,
#         name TEXT NOT NULL
#     );
#     CREATE TABLE m2m (
#         id INTEGER PRIMARY KEY,
#         expense_id INTEGER,
#         tag_id INTEGER,
#         FOREIGN KEY(expense_id) REFERENCES expenses(id),
#         FOREIGN KEY(tag_id) REFERENCES tags(id)
#     );

SQLITE=sqlite3
DB=$HOME/secure/money.db

case "$1" in
s)
    dt="$2"
    [ -n "$dt" ] || dt="-1 day"
    $SQLITE -column -header $DB "
        SELECT
            date(expenses.dt) AS \"when\",
            expenses.earned - expenses.spent AS amount,
            group_concat(tags.name, ',') AS tags,
            expenses.descr AS \"what\"
        FROM expenses
        LEFT JOIN m2m ON m2m.expense_id=expenses.id
        LEFT JOIN tags ON m2m.tag_id=tags.id
        GROUP BY expenses.id
        ORDER BY dt DESC"
    ;;
t)
    $SQLITE -column -header $DB "
        SELECT
            SUM(earned) AS earned,
            SUM(spent) AS spent,
            SUM(earned) - SUM(spent) AS balance
        FROM expenses"
    ;;
*)
    spent=0
    earned=0
    descr="$1"
    money="$2"
    tags="$3"
    [ -n "$descr" ]
    [ -n "$money" ]
    if echo $money | grep -q "^-"; then
        earned=$(echo $money | sed 's/^-//')
    else
        spent=$money
    fi
    $SQLITE $DB "INSERT INTO expenses
        (dt, earned, spent, descr) VALUES
        (datetime('now'), $earned, $spent, '$descr')"
    last_id=$($SQLITE $DB "SELECT id FROM expenses ORDER BY id DESC LIMIT 1")
    for tag in $(echo $tags | sed 's/,/ /g'); do
        tag_id=$($SQLITE $DB "SELECT id FROM tags WHERE name='$tag'")
        if [ "$tag_id" = "" ]; then
            $SQLITE $DB "INSERT INTO tags (name) VALUES ('$tag')"
            tag_id=$($SQLITE $DB "SELECT id FROM tags WHERE name='$tag'")
        fi
        $SQLITE $DB "INSERT INTO m2m (expense_id, tag_id) VALUES ($last_id, $tag_id)"
    done
    ;;
esac