boc/backend/cmd/sie4-import/main.go

package main

import (
	"bufio"
	"database/sql"
	"fmt"
	"log"
	"os"
	"strconv"
	"strings"
	"time"

	"github.com/google/uuid"
	_ "github.com/lib/pq"
)

type SIE4Parser struct {
	accounts    map[string]string // account_number -> name
	ib          map[string]float64 // account_number -> opening balance
	ub          map[string]float64 // account_number -> closing balance
	vouchers    []Voucher
	companyID   uuid.UUID
	tenantID    uuid.UUID
	db          *sql.DB
}

type Voucher struct {
	Series      string
	Number      int
	Date        time.Time
	Description string
	Transactions []Transaction
}

type Transaction struct {
	Account string
	Amount  float64
}

func main() {
	if len(os.Args) < 2 {
		log.Fatal("Usage: sie4-import <sie-file>")
	}

	db, err := sql.Open("postgres", os.Getenv("DB_URL"))
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	parser := &SIE4Parser{
		accounts: make(map[string]string),
		ib:       make(map[string]float64),
		ub:       make(map[string]float64),
		db:       db,
	}

	// Get LandveX AB company ID
	var companyIDStr string
	err = db.QueryRow("SELECT id FROM boc_companies WHERE org_number = $1", "559141-7042").Scan(&companyIDStr)
	if err != nil {
		log.Fatal("LandveX AB not found:", err)
	}
	parser.companyID = uuid.MustParse(companyIDStr)

	var tenantIDStr string
	err = db.QueryRow("SELECT tenant_id FROM boc_companies WHERE id = $1", companyIDStr).Scan(&tenantIDStr)
	if err != nil {
		log.Fatal("Tenant not found:", err)
	}
	parser.tenantID = uuid.MustParse(tenantIDStr)

	// Parse SIE4 file
	file, err := os.Open(os.Args[1])
	if err != nil {
		log.Fatal(err)
	}
	defer file.Close()

	scanner := bufio.NewScanner(file)
	var currentVoucher *Voucher

	for scanner.Scan() {
		line := scanner.Text()
		line = strings.TrimSpace(line)

		if strings.HasPrefix(line, "#KONTO") {
			parts := strings.Fields(line)
			if len(parts) >= 3 {
				accNum := parts[1]
				name := strings.Trim(strings.Join(parts[2:], " "), "\"")
				parser.accounts[accNum] = name
			}
		} else if strings.HasPrefix(line, "#IB") {
			parts := strings.Fields(line)
			if len(parts) >= 4 {
				accNum := parts[2]
				amount, _ := strconv.ParseFloat(parts[3], 64)
				parser.ib[accNum] = amount
			}
		} else if strings.HasPrefix(line, "#UB") {
			parts := strings.Fields(line)
			if len(parts) >= 4 {
				accNum := parts[2]
				amount, _ := strconv.ParseFloat(parts[3], 64)
				parser.ub[accNum] = amount
			}
		} else if strings.HasPrefix(line, "#VER") {
			if currentVoucher != nil && len(currentVoucher.Transactions) > 0 {
				parser.vouchers = append(parser.vouchers, *currentVoucher)
			}
			parts := strings.Fields(line)
			if len(parts) >= 5 {
				series := parts[1]
				number, _ := strconv.Atoi(parts[2])
				dateStr := parts[3]
				date, _ := time.Parse("20060102", dateStr)
				desc := strings.Trim(strings.Join(parts[4:], " "), "\"")
				currentVoucher = &Voucher{
					Series:      series,
					Number:      number,
					Date:        date,
					Description: desc,
				}
			}
		} else if strings.HasPrefix(line, "#TRANS") {
			if currentVoucher != nil {
				parts := strings.Fields(line)
				if len(parts) >= 3 {
					accNum := parts[1]
					amount, _ := strconv.ParseFloat(parts[3], 64)
					currentVoucher.Transactions = append(currentVoucher.Transactions, Transaction{
						Account: accNum,
						Amount:  amount,
					})
				}
			}
		}
	}

	if currentVoucher != nil && len(currentVoucher.Transactions) > 0 {
		parser.vouchers = append(parser.vouchers, *currentVoucher)
	}

	fmt.Printf("Parsed %d accounts, %d vouchers\n", len(parser.accounts), len(parser.vouchers))

	// Import to database
	parser.importAccounts()
	parser.importVouchers()
	parser.importBalances()

	fmt.Println("Import complete")
}

func (p *SIE4Parser) importAccounts() {
	for accNum, name := range p.accounts {
		_, err := p.db.Exec(`
			INSERT INTO boc_chart_of_accounts (company_id, account_code, name, account_type, is_active)
			VALUES ($1, $2, $3, 'asset', true)
			ON CONFLICT (company_id, account_code) DO UPDATE SET name = $3
		`, p.companyID, accNum, name)
		if err != nil {
			log.Printf("Error importing account %s: %v", accNum, err)
		}
	}
	fmt.Printf("Imported %d accounts\n", len(p.accounts))
}

func (p *SIE4Parser) importVouchers() {
	for _, v := range p.vouchers {
		var entryID uuid.UUID
		err := p.db.QueryRow(`
			INSERT INTO boc_journal_entries (company_id, entry_number, entry_date, description, source, status, posted_at)
			VALUES ($1, $2, $3, $4, 'import', 'posted', NOW())
			RETURNING id
		`, p.companyID, fmt.Sprintf("%s%d", v.Series, v.Number), v.Date, v.Description).Scan(&entryID)
		if err != nil {
			log.Printf("Error importing voucher %s%d: %v", v.Series, v.Number, err)
			continue
		}

		for _, t := range v.Transactions {
			// Get account ID
			var accountID uuid.UUID
			err := p.db.QueryRow(`
				SELECT id FROM boc_chart_of_accounts 
				WHERE company_id = $1 AND account_code = $2
			`, p.companyID, t.Account).Scan(&accountID)
			if err != nil {
				log.Printf("Account not found: %s", t.Account)
				continue
			}

			var debit, credit float64
			if t.Amount > 0 {
				debit = t.Amount
			} else {
				credit = -t.Amount
			}

			_, err = p.db.Exec(`
				INSERT INTO boc_journal_lines (company_id, entry_id, account_id, debit, credit, description)
				VALUES ($1, $2, $3, $4, $5, $6)
			`, p.companyID, entryID, accountID, debit, credit, v.Description)
			if err != nil {
				log.Printf("Error importing line: %v", err)
			}
		}
	}
	fmt.Printf("Imported %d vouchers\n", len(p.vouchers))
}

func (p *SIE4Parser) importBalances() {
	fiscalYear := 2026
	for accNum, amount := range p.ub {
		var accountID uuid.UUID
		err := p.db.QueryRow(`
			SELECT id FROM boc_chart_of_accounts 
			WHERE company_id = $1 AND account_code = $2
		`, p.companyID, accNum).Scan(&accountID)
		if err != nil {
			continue
		}

		_, err = p.db.Exec(`
			INSERT INTO boc_period_balances (company_id, account_id, fiscal_year, period, closing_balance)
			VALUES ($1, $2, $3, 0, $4)
			ON CONFLICT (company_id, account_id, fiscal_year, period) 
			DO UPDATE SET closing_balance = $4
		`, p.companyID, accountID, fiscalYear, amount)
		if err != nil {
			log.Printf("Error importing balance for %s: %v", accNum, err)
		}
	}
	fmt.Println("Imported balances")
}