#!/bin/bash
# Run this from the directory containing your Quicken "data" file
# Usage: bash extract_tpc_2024.sh

# First, dump the category hierarchy so we can reconstruct paths
echo "=== EXTRACTING CATEGORY TREE ==="
sqlite3 -header -separator '|' data "
SELECT Z_PK, ZNAME, ZPARENTCATEGORY, ZTYPE
FROM ZTAG 
WHERE ZDELETIONCOUNT = 0
ORDER BY Z_PK;
" > categories.txt

echo "$(wc -l < categories.txt) categories found"

# Main transaction + line item extract for 2024
# Core Data epoch: 2001-01-01 = 978307200 unix
# 2024-01-01 = 757382400 core data (1704067200 - 978307200)
# 2025-01-01 = 788918400 core data (1767225600 - 978307200)
echo "=== EXTRACTING 2024 TRANSACTIONS ==="
sqlite3 -header -separator '|' data "
SELECT 
    a.ZNAME as account,
    datetime(t.ZPOSTEDDATE + 978307200, 'unixepoch') as posted_date,
    t.Z_PK as tx_id,
    t.ZAMOUNT as tx_amount,
    t.ZRECONCILESTATUS as reconcile_status,
    t.ZCHECKNUMBER as check_num,
    t.ZNOTE as tx_memo,
    e.Z_PK as entry_id,
    e.ZAMOUNT as entry_amount,
    e.ZNOTE as entry_memo,
    e.ZSEQUENCENUMBER as seq_num,
    e.ZTRANSFER as transfer_target,
    e.ZCATEGORYTAG as category_id,
    COALESCE(up.ZNAME, '') as payee
FROM ZTRANSACTION t
JOIN ZACCOUNT a ON t.ZACCOUNT = a.Z_PK
LEFT JOIN ZCASHFLOWTRANSACTIONENTRY e ON e.ZPARENT = t.Z_PK
LEFT JOIN ZUSERPAYEE up ON t.ZUSERPAYEE = up.Z_PK
WHERE a.ZNAME LIKE 'TPC-%'
AND t.ZDELETIONCOUNT = 0
AND t.ZPOSTEDDATE >= 757382400
AND t.ZPOSTEDDATE < 788918400
ORDER BY a.ZNAME, t.ZPOSTEDDATE, t.Z_PK, e.ZSEQUENCENUMBER;
" > transactions_2024.txt

echo "$(wc -l < transactions_2024.txt) rows found"

# User tags (property names) linked to line items
echo "=== EXTRACTING USER TAGS ==="
sqlite3 -header -separator '|' data "
SELECT 
    j.Z_18CASHFLOWTRANSACTIONENTRIES as entry_id,
    ut.ZNAME as tag_name
FROM Z_18USERTAGS j
JOIN ZTAG ut ON j.Z_70USERTAGS = ut.Z_PK
WHERE ut.ZDELETIONCOUNT = 0;
" > user_tags.txt

echo "$(wc -l < user_tags.txt) tag assignments found"

echo "=== DONE ==="
echo "Upload these 3 files: categories.txt, transactions_2024.txt, user_tags.txt"