#!/bin/sh

# Account used when logging in to the remote server
server_account=pq

# Name of account to check stats for
account_name='Hugo Hörnquist'

ssh "${server_account}@vastgota.nation.liu.se" <<- OUT
	env LC_ALL=en_US.UTF-8 sqlite3 \$(ls -1tr ~stupan-backup/Stupan-Backup/stupan-*.db | tail -n1) <<- EOF
		.mode columns

		.headers off
		VALUES ('== Köpt från kassan ==');

		.headers on
		SELECT 
		  date(time) AS datum
		, printf('%.2f', sum(price) / 100.0) AS utgift
		FROM log 
		LEFT JOIN money m ON log.account = m.id 
		WHERE name = '${account_name}' 
		AND time > datetime(CURRENT_TIMESTAMP, '-7 days')
		GROUP BY date(time) 
		ORDER BY time ASC 
		;

		.headers off
		VALUES ('== Inköpt till lagret ==');

		.headers on
		SELECT 
		  date(time) AS datum
		, printf('%.2f', sum(item_price * amount) / 100.0) AS inkomst
		FROM acquisitions a 
		LEFT JOIN money m ON a.account = m.id 
		WHERE m.name = '${account_name}' 
		AND time > datetime(CURRENT_TIMESTAMP, '-7 days')
		GROUP BY date(time) 
		ORDER BY time DESC ;

		.headers off
		VALUES ('== Manuella transaktioner ==');

		.headers on
		WITH transfers (change, acc, time, note) AS 
		(
			SELECT change, to_acc, time, note FROM money_transfers 
		UNION ALL 
			SELECT - change, from_acc, time, note FROM money_transfers
		) 
		SELECT 
		  time AS datum
		, printf('%.2f', change / 100.0) AS utgift
		, note AS notering
		FROM transfers t 
		LEFT JOIN money m ON t.acc = m.id 
		WHERE m.name = '${account_name}' 
		AND time > datetime(current_timestamp, '-7 days') 
		ORDER BY time ASC;

		.headers off
		VALUES ('== Saldo ==');
		SELECT amount FROM money_simple WHERE name = '${account_name}';
	EOF
OUT