module attachment.data_impl_sqlite;

import handy_http_primitives : Optional;
import d2sqlite3;

import attachment.model;
import attachment.data;
import util.sqlite;

import std.datetime;
import std.format;

class SqliteAttachmentRepository : AttachmentRepository {
    private Database db;
    this(Database db) {
        this.db = db;
    }

    Optional!Attachment findById(ulong id) {
        return findOne(
            db,
            "SELECT id, uploaded_at, filename, content_type, size FROM attachment WHERE id = ?",
            &parseAttachment,
            id
        );
    }

    Attachment[] findAllByLinkedEntity(string subquery, ulong entityId) {
        const query = format!("SELECT id, uploaded_at, filename, content_type, size " ~
            "FROM attachment WHERE id IN (%s) " ~
            "ORDER BY filename ASC, uploaded_at DESC")(subquery);
        return findAll(db, query, &parseAttachment, entityId);
    }

    Attachment[] findAllByTransactionId(ulong transactionId) {
        return findAllByLinkedEntity(
            "SELECT attachment_id FROM transaction_attachment WHERE transaction_id = ?",
            transactionId
        );
    }

    Attachment[] findAllByValueRecordId(ulong valueRecordId) {
        return findAllByLinkedEntity(
            "SELECT attachment_id FROM account_value_record_attachment WHERE value_record_id = ?",
            valueRecordId
        );
    }

    Attachment[] findAllByTransactionDraftId(ulong draftId) {
        return findAllByLinkedEntity(
            "SELECT attachment_id FROM transaction_draft_attachment WHERE draft_id = ?",
            draftId
        );
    }
    
    ulong save(SysTime uploadedAt, string filename, string contentType, in ubyte[] content) {
        util.sqlite.update(
            db,
            q"SQL
                INSERT INTO attachment
                (uploaded_at, filename, content_type, size, content)
                VALUES (?, ?, ?, ?, ?)
SQL",
            uploadedAt.toISOExtString(),
            filename,
            contentType,
            cast(ulong) content.length,
            content
        );
        return db.lastInsertRowid();
    }
    
    void remove(ulong id) {
        util.sqlite.update(
            db,
            "DELETE FROM attachment WHERE id = ?",
            id
        );
    }

    Optional!(ubyte[]) getContent(ulong id) {
        return util.sqlite.findOne(
            db,
            "SELECT content FROM attachment WHERE id = ?",
            (row) => row.peek!(ubyte[])(0),
            id
        );
    }

    static Attachment parseAttachment(Row row) {
        return Attachment(
            row.peek!ulong(0),
            parseISOTimestamp(row, 1),
            row.peek!string(2),
            row.peek!string(3),
            row.peek!ulong(4)
        );
    }
}
