import { createRequire } from "node:module";
import type BetterSqlite3 from "better-sqlite3";

const require = createRequire(import.meta.url);
const Database = require("better-sqlite3") as typeof BetterSqlite3;

export type RecentAssetKind = "static_sticker" | "text_template";

export interface RecentAssetItem {
  asset_id: string;
  asset_kind: RecentAssetKind;
  resource_version: string;
}

export class RecentAssetService {
  readonly database: BetterSqlite3.Database;
  private readonly ownsDatabase: boolean;
  private readonly clock: () => number;

  constructor(input: { clock?: () => number; database?: BetterSqlite3.Database; databasePath?: string }) {
    if (!input.database && !input.databasePath) throw new Error("recent_asset_database_required");
    this.database = input.database ?? new Database(input.databasePath!);
    this.ownsDatabase = !input.database;
    this.clock = input.clock ?? Date.now;
    this.database.exec(`
      CREATE TABLE IF NOT EXISTS recent_assets (
        user_id TEXT NOT NULL,
        asset_kind TEXT NOT NULL CHECK (asset_kind IN ('text_template', 'static_sticker')),
        asset_id TEXT NOT NULL,
        resource_version TEXT NOT NULL,
        used_at INTEGER NOT NULL,
        PRIMARY KEY (user_id, asset_kind, asset_id)
      );
      CREATE INDEX IF NOT EXISTS recent_assets_user_kind_used
      ON recent_assets (user_id, asset_kind, used_at DESC, asset_id ASC);
    `);
  }

  close() {
    if (this.ownsDatabase) this.database.close();
  }

  list(userId: string, assetKind: RecentAssetKind, limit = 12): RecentAssetItem[] {
    if (!Number.isInteger(limit) || limit < 1 || limit > 50) throw new Error("recent_asset_limit_invalid");
    return this.database.prepare(`
      SELECT asset_id, asset_kind, resource_version
      FROM recent_assets
      WHERE user_id = ? AND asset_kind = ?
      ORDER BY used_at DESC, asset_id ASC
      LIMIT ?
    `).all(userId, assetKind, limit) as RecentAssetItem[];
  }

  recordSuccessfulUse(input: { assetId: string; assetKind: RecentAssetKind; resourceVersion: string; userId: string }) {
    if (!/^[A-Za-z0-9][A-Za-z0-9_.:-]{0,119}$/.test(input.assetId)
      || !/^[A-Za-z0-9][A-Za-z0-9_.:-]{0,119}$/.test(input.resourceVersion)) {
      throw new Error("recent_asset_reference_invalid");
    }
    this.database.prepare(`
      INSERT INTO recent_assets (user_id, asset_kind, asset_id, resource_version, used_at)
      VALUES (@userId, @assetKind, @assetId, @resourceVersion, @usedAt)
      ON CONFLICT (user_id, asset_kind, asset_id) DO UPDATE SET
        resource_version = excluded.resource_version,
        used_at = excluded.used_at
    `).run({ ...input, usedAt: this.clock() });
  }
}