Misc stuffHEADmaster

5 files changed, 234 insertions, 116 deletions

diff --git a/app/src/lib/server/actual.ts b/app/src/lib/server/actual.ts
index 4cf0262..fb1a4fb 100644
--- a/app/src/lib/server/actual.ts
+++ b/app/src/lib/server/actual.ts
@@ -1,13 +1,13 @@
-import { ACTUAL_BUDGET_ID, ACTUAL_HOST, ACTUAL_PASS } from "$env/static/private";
+import { ACTUAL_FILE_ID, ACTUAL_HOST, ACTUAL_PASS } from "$env/static/private";
 import * as actual from "@actual-app/api"
 import { existsSync, mkdirSync } from "node:fs";
 import path from "node:path"
 import process from "node:process";
-import type { Sb1Transaction } from "./sb1";
 import type { ImportTransactionEntity } from "@actual-app/api/@types/loot-core/src/types/models/import-transaction";
 import { Temporal } from "temporal-polyfill";
+import type { Sb1Transaction } from "$lib/shared";
 
-async function init_actual() {
+export async function init_actual() {
     const dataDir = path.resolve(process.cwd(), "data/actualDataDir")
     if (!existsSync(dataDir)) mkdirSync(dataDir, { recursive: true });
     return actual.init({
@@ -15,33 +15,37 @@ async function init_actual() {
         serverURL: ACTUAL_HOST,
         dataDir: dataDir
     }).then(async () => {
-        await actual.downloadBudget(ACTUAL_BUDGET_ID)
+        await actual.downloadBudget(ACTUAL_FILE_ID)
         await actual.sync()
     })
 }
 
 export async function import_transactions(account: string, transactions: Sb1Transaction[], dryRun: boolean) {
-
+    await init_actual()
     function parsedDate(date: number) {
         const instant = Temporal.Instant.fromEpochMilliseconds(date)
         return instant.toString({ timeZone: "Europe/Oslo" }).split("T")[0]
     }
 
     function notes(transaction: Sb1Transaction) {
-        const {description,cleanedDescription} =transaction
+        const { description, cleanedDescription } = transaction
         if (description.toLowerCase().trim() === cleanedDescription.toLowerCase().trim()) return undefined
         return description
     }
 
+    function amount(amount: number) {
+        return Math.round(amount * 100)
+    }
+
     const actualMappedTransactions: ImportTransactionEntity[] = transactions.filter(c => c.bookingStatus === "BOOKED").map(c => ({
         account,
         date: parsedDate(c.date),
-        amount: c.amount,
+        amount: amount(c.amount),
         notes: notes(c),
         payee_name: c.cleanedDescription
     }))
 
-    return await actual.importTransactions(account, actualMappedTransactions, { dryRun })
+    await actual.importTransactions(account, actualMappedTransactions, { dryRun })
 }
 
 export async function get_budgets() {
diff --git a/app/src/lib/server/db/schema.ts b/app/src/lib/server/db/schema.ts
index 4d13ed6..dbff3a2 100644
--- a/app/src/lib/server/db/schema.ts
+++ b/app/src/lib/server/db/schema.ts
@@ -1,17 +1,17 @@
 import { relations, sql } from 'drizzle-orm';
-import { numeric, text, pgTable, uuid, json } from "drizzle-orm/pg-core";
-import type { Sb1Tokens } from '../sb1';
+import { numeric, text, pgTable, uuid, jsonb } from "drizzle-orm/pg-core";
 import type { SessionLogType } from '../session-log';
+import type { Sb1Tokens, Sb1Transaction, Sb1TransactionDetails } from '$lib/shared';
 
-export const syncSession = pgTable("session", {
+export const SyncSessionTable = pgTable("session", {
 	id: uuid('id').primaryKey().default(sql`uuidv7()`),
 	authzState: text("authzState"),
 	accessTokenCreated: numeric("accessTokenCreated"),
 	refreshTokenCreated: numeric("refreshTokenCreated"),
-	tokens: json("tokens").$type<Sb1Tokens>()
+	tokens: jsonb("tokens").$type<Sb1Tokens>()
 })
 
-export const syncLog = pgTable("session_log", {
+export const SyncLogTable = pgTable("session_log", {
 	id: uuid('id').primaryKey().default(sql`uuidv7()`),
 	sessionId: text("session_id"),
 	dateTime: text("date_time"),
@@ -19,13 +19,18 @@ export const syncLog = pgTable("session_log", {
 	msg: text("msg")
 })
 
-export const syncLogRelation = relations(syncLog, ({ one }) => ({
-	author: one(syncSession, {
-		fields: [syncLog.sessionId],
-		references: [syncSession.id],
+export const TransactionsTable = pgTable("transactions", {
+	transaction: jsonb("transaction").$type<Sb1Transaction>(),
+	details: jsonb("details").$type<Sb1TransactionDetails>()
+})
+
+export const SyncLogRelation = relations(SyncLogTable, ({ one }) => ({
+	author: one(SyncSessionTable, {
+		fields: [SyncLogTable.sessionId],
+		references: [SyncSessionTable.id],
 	})
 }))
 
-export const syncSessionLogRelation = relations(syncSession, ({ many }) => ({
-	logs: many(syncLog)
+export const SyncSessionLogRelation = relations(SyncSessionTable, ({ many }) => ({
+	logs: many(SyncLogTable)
 }))
diff --git a/app/src/lib/server/sb1.ts b/app/src/lib/server/sb1.ts
index f6507ef..0a51649 100644
--- a/app/src/lib/server/sb1.ts
+++ b/app/src/lib/server/sb1.ts
@@ -1,84 +1,11 @@
 import { SB1_FIN_INST, SB1_ID, SB1_REDIRECT_URI, SB1_SECRET } from "$env/static/private";
-import { eq } from "drizzle-orm";
+import { eq, sql } from "drizzle-orm";
 import { Temporal } from "temporal-polyfill";
 import { randomUUID } from "node:crypto";
 import { db } from "./db";
-import { syncSession } from "./db/schema";
+import { SyncSessionTable, TransactionsTable } from "./db/schema";
 import { add_session_log } from "./session-log";
-
-export type Sb1Tokens = {
-    access_token: string
-    expires_in: number
-    refresh_token_expires_in: number
-    refresh_token_absolute_expires_in: number
-    token_type: string
-    refresh_token: string
-}
-
-export type Sb1Transaction = {
-    id: string
-    nonUniqueId: string
-    description: string
-    cleanedDescription: string
-    accountNumber: Sb1AccountNumber
-    amount: number
-    date: number
-    interestDate: number
-    typeCode: string
-    typeText: string
-    currencyCode: string
-    canShowDetails: boolean
-    source: string
-    isConfidential: boolean
-    bookingStatus: string
-    accountName: string
-    accountKey: string
-    accountCurrency: string
-    isFromCurrencyAccount: boolean
-    classificationInput: Sb1ClassificationInput
-}
-
-export type Sb1Account = {
-    key: string;
-    accountNumber: string;
-    iban: string;
-    name: string;
-    description: string;
-    balance: number;
-    availableBalance: number;
-    currencyCode: string;
-    owner: Sb1AccountOwner;
-    productType: string;
-    type: string;
-    productId: string;
-    descriptionCode: string;
-    accountProperties: { [key: string]: boolean };
-}
-
-export type Sb1AccountOwner = {
-    name: string;
-    firstName: string;
-    lastName: string;
-    type: string;
-    age: number;
-    customerKey: string;
-    ssnKey: string;
-}
-
-
-export type Sb1AccountNumber = {
-    value: string
-    formatted: string
-    unformatted: string
-}
-
-export type Sb1ClassificationInput = {
-    id: string
-    amount: number
-    type: string
-    text: string
-    date: string
-}
+import type { Sb1Account, Sb1Tokens, Sb1Transaction } from "$lib/shared";
 
 const auth = {
     async is_ready(): Promise<boolean> {
@@ -87,10 +14,10 @@ const auth = {
     },
     async get_auth_info() {
         const entity = await db.select({
-            refreshTokenCreated: syncSession.refreshTokenCreated,
-            accessTokenCreated: syncSession.accessTokenCreated,
-            tokens: syncSession.tokens
-        }).from(syncSession)
+            refreshTokenCreated: SyncSessionTable.refreshTokenCreated,
+            accessTokenCreated: SyncSessionTable.accessTokenCreated,
+            tokens: SyncSessionTable.tokens
+        }).from(SyncSessionTable)
         if (!entity[0]) return undefined
         const { tokens, accessTokenCreated, refreshTokenCreated } = entity[0]
         if (!tokens) return undefined
@@ -104,7 +31,7 @@ const auth = {
     async init_auth_session(): Promise<string> {
         const state = randomUUID()
 
-        await db.insert(syncSession).values({
+        await db.insert(SyncSessionTable).values({
             authzState: state
         })
 
@@ -118,19 +45,25 @@ const auth = {
     },
     async get_access_token() {
         const result = await db.select({
-            tokens: syncSession.tokens,
-            refreshTokenCreated: syncSession.refreshTokenCreated,
-            accessTokenCreated: syncSession.accessTokenCreated
-        }).from(syncSession)
+            tokens: SyncSessionTable.tokens,
+            refreshTokenCreated: SyncSessionTable.refreshTokenCreated,
+            accessTokenCreated: SyncSessionTable.accessTokenCreated
+        }).from(SyncSessionTable)
+
         if (!result[0]) return undefined
+
         const { tokens, accessTokenCreated, refreshTokenCreated } = result[0]
+
         if (!tokens) return undefined
+
         const nowInstant = Temporal.Now.instant()
+
         const accessTokenExpiredInstant = Temporal.Instant.fromEpochMilliseconds(Number(accessTokenCreated)).add({ seconds: tokens.expires_in })
         if (Temporal.Instant.compare(nowInstant, accessTokenExpiredInstant) >= 0) {
             const refreshedTokens = await this.refresh_token()
             if (refreshedTokens) return refreshedTokens.access_token
         }
+
         const refreshTokenExpiredInstant = Temporal.Instant.fromEpochMilliseconds(Number(refreshTokenCreated)).add({ seconds: tokens.refresh_token_expires_in })
         if (Temporal.Instant.compare(nowInstant, refreshTokenExpiredInstant) >= 0) {
             return undefined
@@ -141,9 +74,9 @@ const auth = {
     async refresh_token(): Promise<Sb1Tokens | null> {
         console.log("Refreshing tokens")
         const entity = await db.select({
-            tokens: syncSession.tokens,
-            id: syncSession.id
-        }).from(syncSession)
+            tokens: SyncSessionTable.tokens,
+            id: SyncSessionTable.id
+        }).from(SyncSessionTable)
 
         const { tokens: currentTokens, id } = entity[0]
 
@@ -167,7 +100,7 @@ const auth = {
         const tokens = await res.json() as Sb1Tokens
         const epoch = Temporal.Now.instant().epochMilliseconds
         if (res.ok) {
-            await db.update(syncSession).set({ tokens, accessTokenCreated: epoch.toString(), refreshTokenCreated: epoch.toString() }).where(eq(syncSession.id, id))
+            await db.update(SyncSessionTable).set({ tokens, accessTokenCreated: epoch.toString(), refreshTokenCreated: epoch.toString() }).where(eq(SyncSessionTable.id, id))
             await add_session_log(id, "REFRESH_SB1_TOKEN", "Done")
             return tokens
         } else {
@@ -195,25 +128,60 @@ const data = {
     },
     async get_transactions(accountKey: string, delta?: Temporal.Instant) {
         const token = await auth.get_access_token()
-
         if (!token) return undefined
+
         const params = new URLSearchParams({
             "accountKey": accountKey,
         });
-        if (delta) params.append("fromDate", formatInstant(delta, "yyyy-MM-dd"))
+
+        if (delta) {
+            params.append("fromDate", formatInstant(delta, "yyyy-MM-dd"))
+            params.append("Transaction source", "ALL")
+        }
+
         const response = await fetch("https://api.sparebank1.no/personal/banking/transactions?" + params, {
             headers: {
                 Authorization: `Bearer ${token}`,
+                Accept: "application/vnd.sparebank1.v1+json;charset=utf-8"
             },
         });
+
         const json = await response.json()
-        return json["transactions"] as Sb1Transaction[];
+        return json["transactions"] as Sb1Transaction[]
     }
 }
 
-export default { auth, data }
+export default { auth, data, init }
 
-export function formatInstant(
+let importInterval: NodeJS.Timeout
+let inited = false
+
+async function init() {
+    if (inited) return
+    if (importInterval) clearInterval(importInterval)
+    await importTransactions()
+    importInterval = setInterval(async () => importTransactions, 60 * 60 * 1000)
+    inited = true
+}
+
+async function importTransactions() {
+    console.log("Creating sb1 transactions indb")
+    const accounts = await data.get_accounts()
+    for (const account of accounts?.accounts ?? []) {
+        const transactions = await data.get_transactions(account.key)
+        for (const transaction of transactions ?? []) {
+            // if (await transactionExists(transaction.id)) continue
+            await db.insert(TransactionsTable).values({ transaction })
+        }
+    }
+}
+
+async function transactionExists(transactionId: string) {
+    const query = sql`select data ->>'id' as id from ${TransactionsTable} where id=${transactionId}`;
+    return (await db.execute(query)).rowCount ?? 0 > 0
+}
+
+function formatInstant(
     instant: Temporal.Instant,
     format: string,
     timeZone: string = "UTC"
@@ -236,4 +204,4 @@ export function formatInstant(
         /yyyy|MM|dd|HH|mm|ss/g,
         (token) => replacements[token]
     );
-}
+}
\ No newline at end of file
diff --git a/app/src/lib/server/session-log.ts b/app/src/lib/server/session-log.ts
index 1195621..54498f3 100644
--- a/app/src/lib/server/session-log.ts
+++ b/app/src/lib/server/session-log.ts
@@ -1,11 +1,11 @@
 import { Temporal } from "temporal-polyfill"
 import { db } from "./db"
-import { syncLog } from "./db/schema"
+import { SyncLogTable } from "./db/schema"
 
 export type SessionLogType = "CREATED" | "SYNC_START" | "REFRESH_SB1_TOKEN"
 
 export async function add_session_log(id: string, type: SessionLogType, msg: string) {
-    db.insert(syncLog).values({
+    db.insert(SyncLogTable).values({
         dateTime: String(Temporal.Now.instant().epochMilliseconds),
         sessionId: id,
         type: type,
diff --git a/app/src/lib/shared.ts b/app/src/lib/shared.ts
index a7cf207..cc4472f 100644
--- a/app/src/lib/shared.ts
+++ b/app/src/lib/shared.ts
@@ -11,3 +11,144 @@ export const ImportForm = v.object({
     ),
     dryRun: v.boolean()
 })
+export type Sb1TransactionDetails = {
+    id: string;
+    date: Date;
+    type: string;
+    amount: number;
+    typeCode: string;
+    typeText: string;
+    valueDate: Date;
+    accountKey: string;
+    bookedDate: Date;
+    accountName: string;
+    description: string;
+    eInvoiceUrl: string;
+    nonUniqueId: string;
+    postingDate: Date;
+    currencyCode: string;
+    exchangeRate: number;
+    kidOrMessage: string;
+    accountNumber: number;
+    currencyAmount: number;
+    paymentDetails: Sb1PaymentDetails;
+    accountCurrency: string;
+    archiveReference: string;
+    paymentReference: string;
+    remoteAccountName: string;
+    cleanedDescription: string;
+    numericalReference: string;
+    classificationInput: Sb1ClassificationInput;
+    originalDescription: string;
+    remoteAccountNumber: string;
+}
+
+export type Sb1PaymentDetails = {
+    amount: number;
+    message: string;
+    paymentCid: string;
+    payeeAddress: Sb1PayeeAddress;
+    payeeBankName: string;
+    payeeBicSwift: string;
+    amountCurrency: string;
+    serviceCharges: Sb1ServiceCharge[];
+    payeeBankAddress: Sb1PayeeAddress;
+    paymentReference: string;
+    payeeEmailAddress: string;
+    internationalDetails: Sb1InternationalDetails;
+}
+
+export type Sb1InternationalDetails = {
+    agreedRate: string;
+    agreedWith: string;
+    authorityReportCode: string;
+    authorityReportText: string;
+}
+
+export type Sb1PayeeAddress = {
+    city: string;
+    line1: string;
+    line2: string;
+    line3: string;
+    zipCode: string;
+    countryCode: string;
+}
+
+export type Sb1ServiceCharge = {
+    paidBy: string;
+    chargedAmount: number;
+    chargedAmountCurrency: string;
+}
+
+export type Sb1Tokens = {
+    access_token: string
+    expires_in: number
+    refresh_token_expires_in: number
+    refresh_token_absolute_expires_in: number
+    token_type: string
+    refresh_token: string
+}
+
+export type Sb1Transaction = {
+    id: string
+    nonUniqueId: string
+    description: string
+    cleanedDescription: string
+    accountNumber: Sb1AccountNumber
+    amount: number
+    date: number
+    interestDate: number
+    typeCode: string
+    typeText: string
+    currencyCode: string
+    canShowDetails: boolean
+    source: string
+    isConfidential: boolean
+    bookingStatus: string
+    accountName: string
+    accountKey: string
+    accountCurrency: string
+    isFromCurrencyAccount: boolean
+    classificationInput: Sb1ClassificationInput
+}
+
+export type Sb1Account = {
+    key: string;
+    accountNumber: string;
+    iban: string;
+    name: string;
+    description: string;
+    balance: number;
+    availableBalance: number;
+    currencyCode: string;
+    owner: Sb1AccountOwner;
+    productType: string;
+    type: string;
+    productId: string;
+    descriptionCode: string;
+    accountProperties: { [key: string]: boolean };
+}
+
+export type Sb1AccountOwner = {
+    name: string;
+    firstName: string;
+    lastName: string;
+    type: string;
+    age: number;
+    customerKey: string;
+    ssnKey: string;
+}
+
+export type Sb1AccountNumber = {
+    value: string
+    formatted: string
+    unformatted: string
+}
+
+export type Sb1ClassificationInput = {
+    id: string
+    amount: number
+    type: string
+    text: string
+    date: string
+}