smtp2rss/store.ts

import { DB } from "https://deno.land/x/sqlite@v3.1.1/mod.ts";

function dateToEpoch(dateStr: string | Date) {
  const date = typeof dateStr === "string" ? new Date(dateStr) : dateStr;
  return Math.floor(date.getTime() / 1000);
}

export class SqliteStore {
  private db: DB;

  constructor(path: string) {
    this.db = new DB(path);
    this.db.query(`
      CREATE TABLE IF NOT EXISTS emails (
        id INTEGER PRIMARY KEY,
        recipient TEXT NOT NULL,
        sender TEXT NOT NULL,
        data TEXT NOT NULL,
        received_at INTEGER NOT NULL
      );
    `);
  }

  saveEmail(recipient: string, sender: string, data: string) {
    this.db.query("INSERT INTO emails VALUES (?, ?, ?, ?, ?)", [
      null,
      recipient,
      sender,
      data,
      dateToEpoch(new Date()),
    ]);
  }

  getLatestEmails(
    recipient: string,
    newerThan: Date,
  ): Array<{ id: string; receivedAt: Date; data: string }> {
    const result = this.db.query<[number, string, number]>(
      `
      SELECT id, data, received_at FROM emails
      WHERE recipient = ?
        AND received_at > ?
      ORDER BY id DESC
    `,
      [recipient, dateToEpoch(newerThan)],
    );

    return result.map(([id, data, receivedAt]) => {
      return { id: `${id}`, data, receivedAt: new Date(receivedAt * 1000) };
    });
  }
}