Import Spliit transactions into Spliit

import_spliit_txns.clj

#!/usr/bin/env bb

(require '[babashka.pods :as pods])
(pods/load-pod 'org.babashka/postgresql "0.1.0")

(require '[cheshire.core :as json]
         '[pod.babashka.postgresql :as pg]
         '[clojure.string :as str])

(def db {:dbtype "postgresql"
         :host "localhost"
         :port 5532
         :dbname "postgres"
         :user "postgres"
         :password "1234"}) ;; this is Spliit's default database password

(defn write-to-database [data]
  (let [sql (str "
DO $$
DECLARE
    json_data jsonb := '" content "'::jsonb;
    new_expense_id uuid;
    temp_expense_data RECORD;
BEGIN
    -- Insert group
    INSERT INTO \"Group\" (id, name, currency, \"createdAt\", information)
    VALUES (
        json_data->>'id',
        json_data->>'name',
        json_data->>'currency',
        CURRENT_TIMESTAMP,
        NULL
    );

    -- Insert participants
    INSERT INTO \"Participant\" (id, name, \"groupId\")
    SELECT
        p->>'id',
        p->>'name',
        json_data->>'id'
    FROM jsonb_array_elements(json_data->'participants') AS p;

    -- Insert expenses and their paid-for entries
    FOR temp_expense_data IN
        SELECT
            e->>'title' as title,
            (e->>'amount')::integer as amount,
            e->>'paidById' as paid_by_id,
            (e->>'isReimbursement')::boolean as is_reimbursement,
            (e->>'splitMode')::\"SplitMode\" as split_mode,
            (e->>'expenseDate')::timestamp as expense_date,
            e->'category'->>'grouping' as category_grouping,
            e->'category'->>'name' as category_name,
            e->'paidFor' as paid_for
        FROM jsonb_array_elements(json_data->'expenses') AS e
    LOOP
        -- Insert expense
        INSERT INTO \"Expense\" (
            id, title, amount, \"paidById\", \"groupId\", \"isReimbursement\",
            \"createdAt\", \"splitMode\", \"expenseDate\", \"categoryId\", notes
        )
        SELECT
            gen_random_uuid(),
            temp_expense_data.title,
            temp_expense_data.amount,
            temp_expense_data.paid_by_id,
            json_data->>'id',
            temp_expense_data.is_reimbursement,
            CURRENT_TIMESTAMP,
            temp_expense_data.split_mode,
            temp_expense_data.expense_date,
            c.id,
            NULL
        FROM \"Category\" c
        WHERE c.grouping = temp_expense_data.category_grouping
        AND c.name = temp_expense_data.category_name
        RETURNING id INTO new_expense_id;

        -- Insert expense paid-for entries
        INSERT INTO \"ExpensePaidFor\" (\"expenseId\", \"participantId\", shares)
        SELECT
            new_expense_id,
            p->>'participantId',
            (p->>'shares')::integer
        FROM jsonb_array_elements(temp_expense_data.paid_for) AS p;
    END LOOP;
END $$;")]
    (pg/with-transaction [tx db]
      (pg/execute! tx [sql]))))

(when (= *file* (System/getProperty "babashka.file"))
  (let [args *command-line-args*]
    (if-let [file-path (first args)]
      (if (not (.exists (java.io.File. file-path)))
        (println "Error: File" file-path "does not exist")
        (do
          (println "Starting to import from " file-path)
          (try
            (write-to-database (-> (slurp file-path)
                                   (str/replace "'" "''")))
            (println "Import completed")
            (catch Exception e
              (println "Error during import:" (ex-message e))
              (System/exit 1)))))
      (println "Usage: bb import_spliit_txns.clj <json_file>"))))