finnow/finnow-api/source/transaction/data_impl_sqlite.d

module transaction.data_impl_sqlite;

import handy_httpd.components.optional;
import std.datetime;
import d2sqlite3;

import transaction.model;
import transaction.data;
import util.sqlite;
import util.money;

class SqliteTransactionVendorRepository : TransactionVendorRepository {
    private Database db;
    this(Database db) {
        this.db = db;
    }
    
    Optional!TransactionVendor findById(ulong id) {
        return util.sqlite.findById(db, "transaction_vendor", &parseVendor, id);
    }

    TransactionVendor[] findAll() {
        return util.sqlite.findAll(
            db,
            "SELECT * FROM transaction_vendor ORDER BY name ASC",
            &parseVendor
        );
    }

    bool existsByName(string name) {
        return util.sqlite.exists(db, "SELECT id FROM transaction_vendor WHERE name = ?", name);
    }

    TransactionVendor insert(string name, string description) {
        util.sqlite.update(
            db,
            "INSERT INTO transaction_vendor (name, description) VALUES (?, ?)",
            name, description
        );
        ulong id = db.lastInsertRowid();
        return findById(id).orElseThrow();
    }

    void deleteById(ulong id) {
        util.sqlite.deleteById(db, "transaction_vendor", id);
    }

    TransactionVendor updateById(ulong id, string name, string description) {
        util.sqlite.update(
            db,
            "UPDATE transaction_vendor SET name = ?, description = ? WHERE id = ?",
            name, description, id
        );
        return findById(id).orElseThrow();
    }

    private static TransactionVendor parseVendor(Row row) {
        return TransactionVendor(
            row.peek!ulong(0),
            row.peek!string(1),
            row.peek!string(2)
        );
    }
}

class SqliteTransactionCategoryRepository : TransactionCategoryRepository {
    private Database db;
    this(Database db) {
        this.db = db;
    }
    
    Optional!TransactionCategory findById(ulong id) {
        return util.sqlite.findById(db, "transaction_category", &parseCategory, id);
    }

    TransactionCategory[] findAllByParentId(Optional!ulong parentId) {
        if (parentId) {
            return util.sqlite.findAll(
                db,
                "SELECT * FROM transaction_category WHERE parent_id = ? ORDER BY name ASC",
                &parseCategory,
                parentId.value
            );
        }
        return util.sqlite.findAll(
            db,
            "SELECT * FROM transaction_category WHERE parent_id IS NULL ORDER BY name ASC",
            &parseCategory
        );
    }

    TransactionCategory insert(Optional!ulong parentId, string name, string description, string color) {
        util.sqlite.update(
            db,
            "INSERT INTO transaction_category
            (parent_id, name, description, color)
            VALUES (?, ?, ?, ?)",
            parentId.asNullable(), name, description, color
        );
        ulong id = db.lastInsertRowid();
        return findById(id).orElseThrow();
    }

    void deleteById(ulong id) {
        util.sqlite.deleteById(db, "transaction_category", id);
    }

    TransactionCategory updateById(ulong id, string name, string description, string color) {
        util.sqlite.update(
            db,
            "UPDATE transaction_category
            SET name = ?, description = ?, color = ?
            WHERE id = ?",
            name, description, color, id
        );
        return findById(id).orElseThrow();
    }

    private static TransactionCategory parseCategory(Row row) {
        import std.typecons;
        return TransactionCategory(
            row.peek!ulong(0),
            Optional!ulong.of(row.peek!(Nullable!ulong)(1)),
            row.peek!string(2),
            row.peek!string(3),
            row.peek!string(4)
        );
    }
}

class SqliteTransactionTagRepository : TransactionTagRepository {
    private Database db;
    this(Database db) {
        this.db = db;
    }

    Optional!TransactionTag findById(ulong id) {
        return findOne(db, "SELECT * FROM transaction_tag WHERE id = ?", &parseTag, id);
    }

    Optional!TransactionTag findByName(string name) {
        return findOne(db, "SELECT * FROM transaction_tag WHERE name = ?", &parseTag, name);
    }

    TransactionTag[] findAll() {
        return util.sqlite.findAll(
            db,
            "SELECT * FROM transaction_tag ORDER BY name ASC",
            &parseTag
        );
    }

    TransactionTag insert(string name) {
        auto existingTag = findByName(name);
        if (existingTag) {
            return existingTag.value;
        }
        util.sqlite.update(
            db,
            "INSERT INTO transaction_tag (name) VALUES (?)",
            name
        );
        ulong id = db.lastInsertRowid();
        return findById(id).orElseThrow();
    }

    void deleteById(ulong id) {
        util.sqlite.update(
            db,
            "DELETE FROM transaction_tag WHERE id = ?",
            id
        );
    }

    private static TransactionTag parseTag(Row row) {
        return TransactionTag(
            row.peek!ulong(0),
            row.peek!string(1)
        );
    }
}

class SqliteTransactionRepository : TransactionRepository {
    private const TABLE_NAME = "\"transaction\"";
    private Database db;
    this(Database db) {
        this.db = db;
    }

    Optional!Transaction findById(ulong id) {
        return util.sqlite.findById(db, TABLE_NAME, &parseTransaction, id);
    }

    Transaction insert(
        SysTime timestamp,
        SysTime addedAt,
        ulong amount,
        Currency currency,
        string description,
        Optional!ulong vendorId,
        Optional!ulong categoryId
    ) {
        util.sqlite.update(
            db,
            "INSERT INTO " ~ TABLE_NAME ~ "
            (timestamp, added_at, amount, currency, description, vendor_id, category_id)
            VALUES (?, ?, ?, ?, ?, ?, ?)",
            timestamp.toISOExtString(),
            addedAt.toISOExtString(),
            amount,
            currency.code,
            description,
            vendorId.asNullable,
            categoryId.asNullable
        );
        ulong id = db.lastInsertRowid();
        return findById(id).orElseThrow();
    }

    void deleteById(ulong id) {
        util.sqlite.deleteById(db, TABLE_NAME, id);
    }

    static Transaction parseTransaction(Row row) {
        import std.typecons : Nullable;
        return Transaction(
            row.peek!ulong(0),
            SysTime.fromISOExtString(row.peek!string(1)),
            SysTime.fromISOExtString(row.peek!string(2)),
            row.peek!ulong(3),
            Currency.ofCode(row.peek!(string, PeekMode.slice)(4)),
            row.peek!string(5),
            Optional!(ulong).of(row.peek!(Nullable!ulong)(6)),
            Optional!(ulong).of(row.peek!(Nullable!ulong)(7))
        );
    }
}