import { Database } from 'bun:sqlite';
import type { InboxStore } from '@shade/inbox-server';
import { toBase64, fromBase64 } from '@shade/core';

/**
 * SQLite-backed InboxStore for the Shade Inbox Server (V3.6).
 *
 * Stores ciphertext blobs keyed by (address, msgId) with an
 * (address, expires_at) index so prune scans don't need a full table walk.
 *
 * Docker usage:
 *   Volume mount /data, set SHADE_INBOX_DB_PATH=/data/shade-inbox.db
 */
export class SqliteInboxStore implements InboxStore {
  private db: Database;

  private stmts!: {
    saveOwner: ReturnType<Database['prepare']>;
    getOwner: ReturnType<Database['prepare']>;
    deleteOwner: ReturnType<Database['prepare']>;
    deleteOwnerBlobs: ReturnType<Database['prepare']>;
    insertBlob: ReturnType<Database['prepare']>;
    findBlob: ReturnType<Database['prepare']>;
    fetchSince: ReturnType<Database['prepare']>;
    deleteBlob: ReturnType<Database['prepare']>;
    countBlobs: ReturnType<Database['prepare']>;
    purgeExpired: ReturnType<Database['prepare']>;
    nextSeq: ReturnType<Database['prepare']>;
  };

  // Monotonic in-process sequence for receivedAt — guarantees a strict
  // ordering even when many writes land in the same millisecond.
  private seq = 0;

  constructor(dbPath?: string) {
    const path = dbPath ?? process.env.SHADE_INBOX_DB_PATH ?? '/data/shade-inbox.db';
    this.db = new Database(path, { create: true });
    this.db.exec('PRAGMA journal_mode=WAL');
    this.ensureTables();
    this.prepareStatements();
    this.bootstrapSeq();
  }

  private ensureTables() {
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS inbox_owners (
        address TEXT PRIMARY KEY,
        signing_key TEXT NOT NULL
      );
      CREATE TABLE IF NOT EXISTS inbox_blobs (
        address     TEXT NOT NULL,
        msg_id      TEXT NOT NULL,
        ciphertext  TEXT NOT NULL,
        received_at INTEGER NOT NULL,
        expires_at  INTEGER NOT NULL,
        sender_fp   TEXT,
        PRIMARY KEY (address, msg_id)
      );
      CREATE INDEX IF NOT EXISTS idx_inbox_addr_expires
        ON inbox_blobs(address, expires_at);
      CREATE INDEX IF NOT EXISTS idx_inbox_addr_received
        ON inbox_blobs(address, received_at);
      CREATE INDEX IF NOT EXISTS idx_inbox_expires
        ON inbox_blobs(expires_at);
    `);
    // V4.8 — sender fingerprint column. Idempotent ALTER for live
    // databases that came up under a 4.7-or-earlier schema.
    const cols = this.db
      .prepare(`PRAGMA table_info(inbox_blobs)`)
      .all() as Array<{ name: string }>;
    if (!cols.some((c) => c.name === 'sender_fp')) {
      this.db.exec(`ALTER TABLE inbox_blobs ADD COLUMN sender_fp TEXT`);
    }
  }

  private prepareStatements() {
    this.stmts = {
      saveOwner: this.db.prepare(
        'INSERT OR REPLACE INTO inbox_owners (address, signing_key) VALUES (?, ?)',
      ),
      getOwner: this.db.prepare('SELECT signing_key FROM inbox_owners WHERE address = ?'),
      deleteOwner: this.db.prepare('DELETE FROM inbox_owners WHERE address = ?'),
      deleteOwnerBlobs: this.db.prepare('DELETE FROM inbox_blobs WHERE address = ?'),
      insertBlob: this.db.prepare(
        'INSERT INTO inbox_blobs (address, msg_id, ciphertext, received_at, expires_at, sender_fp) VALUES (?, ?, ?, ?, ?, ?)',
      ),
      findBlob: this.db.prepare(
        'SELECT received_at FROM inbox_blobs WHERE address = ? AND msg_id = ?',
      ),
      fetchSince: this.db.prepare(
        `SELECT msg_id, ciphertext, received_at, expires_at, sender_fp
         FROM inbox_blobs
         WHERE address = ? AND received_at > ? AND expires_at > ?
         ORDER BY received_at ASC
         LIMIT ?`,
      ),
      deleteBlob: this.db.prepare(
        'DELETE FROM inbox_blobs WHERE address = ? AND msg_id = ?',
      ),
      countBlobs: this.db.prepare(
        'SELECT COUNT(*) AS count FROM inbox_blobs WHERE address = ? AND expires_at > ?',
      ),
      purgeExpired: this.db.prepare(
        'DELETE FROM inbox_blobs WHERE expires_at <= ?',
      ),
      nextSeq: this.db.prepare(
        'SELECT MAX(received_at) AS max FROM inbox_blobs',
      ),
    };
  }

  private bootstrapSeq() {
    const row = this.stmts.nextSeq.get() as { max: number | null };
    this.seq = Math.max(row?.max ?? 0, Date.now());
  }

  close() {
    this.db.close();
  }

  async saveAddressOwner(address: string, signingKey: Uint8Array): Promise<void> {
    this.stmts.saveOwner.run(address, toBase64(signingKey));
  }

  async getAddressOwner(address: string): Promise<Uint8Array | null> {
    const row = this.stmts.getOwner.get(address) as { signing_key: string } | undefined;
    if (!row) return null;
    return fromBase64(row.signing_key);
  }

  async putBlob(args: {
    address: string;
    msgId: string;
    ciphertext: Uint8Array;
    expiresAt: number;
    senderFp?: string;
  }): Promise<{ created: boolean; receivedAt: number }> {
    const existing = this.stmts.findBlob.get(args.address, args.msgId) as
      | { received_at: number }
      | undefined;
    if (existing) {
      return { created: false, receivedAt: existing.received_at };
    }
    this.seq = Math.max(this.seq + 1, Date.now());
    const receivedAt = this.seq;
    this.stmts.insertBlob.run(
      args.address,
      args.msgId,
      toBase64(args.ciphertext),
      receivedAt,
      args.expiresAt,
      args.senderFp ?? null,
    );
    return { created: true, receivedAt };
  }

  async fetchBlobs(args: {
    address: string;
    sinceCursor: number;
    now: number;
    limit: number;
  }): Promise<
    Array<{
      msgId: string;
      ciphertext: Uint8Array;
      receivedAt: number;
      expiresAt: number;
      senderFp?: string;
    }>
  > {
    const rows = this.stmts.fetchSince.all(
      args.address,
      args.sinceCursor,
      args.now,
      args.limit,
    ) as Array<{
      msg_id: string;
      ciphertext: string;
      received_at: number;
      expires_at: number;
      sender_fp: string | null;
    }>;
    return rows.map((r) => {
      const out: {
        msgId: string;
        ciphertext: Uint8Array;
        receivedAt: number;
        expiresAt: number;
        senderFp?: string;
      } = {
        msgId: r.msg_id,
        ciphertext: fromBase64(r.ciphertext),
        receivedAt: r.received_at,
        expiresAt: r.expires_at,
      };
      if (r.sender_fp) out.senderFp = r.sender_fp;
      return out;
    });
  }

  async deleteBlob(address: string, msgId: string): Promise<boolean> {
    const result = this.stmts.deleteBlob.run(address, msgId);
    return result.changes > 0;
  }

  async countBlobs(address: string, now: number): Promise<number> {
    const row = this.stmts.countBlobs.get(address, now) as { count: number };
    return row.count;
  }

  async purgeExpired(now: number): Promise<number> {
    const result = this.stmts.purgeExpired.run(now);
    return result.changes;
  }

  async deleteAddress(address: string): Promise<void> {
    const tx = this.db.transaction(() => {
      this.stmts.deleteOwner.run(address);
      this.stmts.deleteOwnerBlobs.run(address);
    });
    tx();
  }
}
