// Blob listing and facet SQL shared by the SQLite-backed storage adapters
// (better-sqlite3 and Durable Object SQLite speak the same dialect). The
// schema is each adapter's own; these builders only read `blobs` and
// `record_blobs`.

/** @typedef {import('./ports.js').BlobListOptions} BlobListOptions */

// The four named media kinds; anything else buckets as `other`.
const NAMED_KINDS = ['image', 'video', 'audio', 'text'];

const KIND_CASE = `CASE
  WHEN mime_type LIKE 'image/%' THEN 'image'
  WHEN mime_type LIKE 'video/%' THEN 'video'
  WHEN mime_type LIKE 'audio/%' THEN 'audio'
  WHEN mime_type LIKE 'text/%' THEN 'text'
  ELSE 'other' END`;

/**
 * The collection-NSID authority (`app.bsky.feed.post` -> `app.bsky`) of each
 * blob's referencing records, one row per distinct blob/authority pair, so a
 * blob two apps reference counts once in each and repeat references within an
 * app count once. Derived by string position: a record URI is always
 * `at://<did>/<collection>/<rkey>`, and an NSID's authority is its first two
 * dot-segments.
 */
const BLOB_AUTHORITIES = `SELECT DISTINCT blob_cid, CASE
    WHEN instr(coll, '.') = 0 THEN coll
    WHEN instr(substr(coll, instr(coll, '.') + 1), '.') = 0 THEN coll
    ELSE substr(coll, 1, instr(coll, '.') + instr(substr(coll, instr(coll, '.') + 1), '.') - 1)
  END AS authority
  FROM (
    SELECT blob_cid, substr(c1, 1, instr(c1, '/') - 1) AS coll FROM (
      SELECT blob_cid, substr(substr(record_uri, 6), instr(substr(record_uri, 6), '/') + 1) AS c1
      FROM record_blobs
    )
  )`;

export const BLOB_FACETS_TOTAL_SQL =
  'SELECT COUNT(*) AS count, COALESCE(SUM(size), 0) AS bytes FROM blobs';

export const BLOB_FACETS_UNUSED_SQL = `SELECT COUNT(*) AS count, COALESCE(SUM(size), 0) AS bytes FROM blobs b
  WHERE NOT EXISTS (SELECT 1 FROM record_blobs rb WHERE rb.blob_cid = b.cid)`;

export const BLOB_FACETS_KINDS_SQL = `SELECT ${KIND_CASE} AS kind, COUNT(*) AS count, COALESCE(SUM(size), 0) AS bytes
  FROM blobs GROUP BY kind ORDER BY bytes DESC`;

export const BLOB_FACETS_APPS_SQL = `SELECT authority, COUNT(*) AS count, COALESCE(SUM(size), 0) AS bytes
  FROM (${BLOB_AUTHORITIES}) x JOIN blobs b ON b.cid = x.blob_cid
  GROUP BY authority ORDER BY bytes DESC`;

// A record minted by a normal write carries a TID rkey: 13 base32-sortable
// chars encoding (creation micros << 10 | clock id). The rkey is the URI's
// last path segment, so a TID rkey is exactly the URI's last 13 chars.
const TID_CHARS = '234567abcdefghijklmnopqrstuvwxyz';
// Shape check as instr() per char, not GLOB: Durable Object SQLite rejects a
// 13-class GLOB pattern as "too complex", though better-sqlite3 accepts it.
const TID_SHAPE = [
  `substr(rb.record_uri, -14, 1) = '/'`,
  ...Array.from(
    { length: 13 },
    (_, i) => `instr('${TID_CHARS}', substr(rb.record_uri, ${i - 13}, 1)) > 0`,
  ),
].join(' AND ');
const TID_MS = `(${Array.from(
  { length: 13 },
  (_, i) =>
    `(instr('${TID_CHARS}', substr(rb.record_uri, ${i - 13}, 1)) - 1) * ${(32n ** BigInt(12 - i)).toString()}`,
).join(' + ')}) / 1024000`;

/**
 * When a blob's content entered the account, as epoch ms: the earliest
 * referencing record's own stored timestamp, else that record's TID time,
 * else when the blob was stored here. The distinction matters for migrated
 * repos, where every blob's stored-here time is the migration date but the
 * records still carry the original timestamps.
 */
const LINK_TIME = `COALESCE(rb.record_time, CASE WHEN ${TID_SHAPE} THEN ${TID_MS} END)`;
const BLOB_TIME = `COALESCE(MIN(${LINK_TIME}), b.created_at)`;
const BLOB_TIME_KNOWN = `(MIN(${LINK_TIME}) IS NOT NULL)`;

/**
 * The filtered, ordered blob listing with each blob's referencing record URIs
 * concatenated (comma-separated; neither DIDs, NSIDs, nor rkeys may contain a
 * comma). Keyset pagination: the cursor is `<sortValue>:<cid>`, matching the
 * chosen order, so a page boundary holds still while blobs come and go.
 * @param {string|null} cursor
 * @param {number} limit
 * @param {BlobListOptions} [options]
 * @returns {{sql: string, params: (string|number)[]}}
 */
export function buildBlobListQuery(cursor, limit, options = {}) {
  const { kind, appAuthority, unused, sort } = options;
  const conds = [];
  /** @type {(string|number)[]} */
  const params = [];

  if (kind && NAMED_KINDS.includes(kind)) {
    conds.push('b.mime_type LIKE ?');
    params.push(`${kind}/%`);
  } else if (kind === 'other') {
    conds.push(
      `(b.mime_type IS NULL OR (${NAMED_KINDS.map(() => 'b.mime_type NOT LIKE ?').join(' AND ')}))`,
    );
    params.push(...NAMED_KINDS.map((named) => `${named}/%`));
  }

  if (appAuthority) {
    conds.push(
      'EXISTS (SELECT 1 FROM record_blobs rb2 WHERE rb2.blob_cid = b.cid AND rb2.record_uri LIKE ?)',
    );
    params.push(`at://%/${appAuthority}.%`);
  }

  if (unused) {
    conds.push(
      'NOT EXISTS (SELECT 1 FROM record_blobs rb2 WHERE rb2.blob_cid = b.cid)',
    );
  }

  // The sort key is an aggregate for `newest`, so the cursor bound lives in
  // HAVING (evaluated after grouping) rather than WHERE.
  const having = [];
  if (cursor) {
    const split = cursor.indexOf(':');
    const key = Number(cursor.slice(0, split));
    const cid = cursor.slice(split + 1);
    having.push('(sort_key < ? OR (sort_key = ? AND b.cid < ?))');
    params.push(key, key, cid);
  }

  const keyExpr = sort === 'largest' ? 'b.size' : BLOB_TIME;
  const where = conds.length ? `WHERE ${conds.join(' AND ')}` : '';
  params.push(limit);
  return {
    sql: `SELECT b.cid, b.mime_type, b.size, b.created_at, GROUP_CONCAT(rb.record_uri) AS uris, ${BLOB_TIME} AS time_ms, ${BLOB_TIME_KNOWN} AS time_known, ${keyExpr} AS sort_key
      FROM blobs b LEFT JOIN record_blobs rb ON b.cid = rb.blob_cid
      ${where}
      GROUP BY b.cid ${having.length ? `HAVING ${having.join(' AND ')}` : ''}
      ORDER BY sort_key DESC, b.cid DESC LIMIT ?`,
    params,
  };
}

/**
 * The cursor naming the last blob of a page, under the same options the page
 * was listed with.
 * @param {import('./ports.js').BlobDetails} blob
 * @param {BlobListOptions} [options]
 * @returns {string}
 */
export function blobListCursor(blob, options = {}) {
  const key = options.sort === 'largest' ? blob.size : blob.time;
  return `${key}:${blob.cid}`;
}