'use strict';
const assert = require('node:assert');
const fs = require('node:fs');
const os = require('node:os');
const path = require('node:path');

const Database = require('better-sqlite3');

const tmp = fs.mkdtempSync(path.join(os.tmpdir(), 'clip-join-'));
const db = new Database(path.join(tmp, 'h.db'));
db.exec(`
  CREATE TABLE clips (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT, text TEXT, image BLOB, preview TEXT,
    top_at REAL, created_at REAL NOT NULL
  );
  CREATE TABLE favorites (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    source_clip_id INTEGER UNIQUE, favorited_at REAL NOT NULL
  );
`);
const now = Date.now()/1000;
// created_at 必须互不相同，否则 ORDER BY created_at DESC 的次序未定义，断言就是碰运气
db.prepare('INSERT INTO clips(type,text,preview,top_at,created_at) VALUES(?,?,?,?,?)').run('text','a','a',null,now - 2);
db.prepare('INSERT INTO clips(type,text,preview,top_at,created_at) VALUES(?,?,?,?,?)').run('text','b','b',null,now - 1);
db.prepare('INSERT INTO clips(type,text,preview,top_at,created_at) VALUES(?,?,?,?,?)').run('text','c','c',null,now);
const favInfo = db.prepare('INSERT INTO favorites(source_clip_id, favorited_at) VALUES(?,?)').run(2, now);
const favIdOfClip2 = favInfo.lastInsertRowid;   // = favorites.id，不是 clip.id

let passed = 0;
function check(name, fn) {
  try { fn(); console.log('  ok -', name); passed++; }
  catch (e) { console.error('  FAIL -', name, e.message); process.exitCode = 1; }
}

check('LEFT JOIN returns one row per clip with fav_id when matched', () => {
  const rows = db.prepare(`
    SELECT c.id, c.text, f.id AS fav_id
    FROM clips c
    LEFT JOIN favorites f ON f.source_clip_id = c.id
    ORDER BY c.created_at DESC
  `).all();
  assert.strictEqual(rows.length, 3, 'JOIN 不应放大行数');
  // created_at DESC → c(3), b(2), a(1)；被收藏的是 clip 2 = 中间那条
  assert.deepStrictEqual(rows.map(r => r.id), [3, 2, 1]);
  assert.strictEqual(rows[0].fav_id, null);
  // fav_id 是 favorites 表的主键，不是 source_clip_id —— 这两个值以前被搞混了
  assert.strictEqual(rows[1].fav_id, favIdOfClip2);
  assert.notStrictEqual(rows[1].fav_id, rows[1].id, 'favId 与 clipId 是不同的命名空间');
  assert.strictEqual(rows[2].fav_id, null);
});

db.close();