// server/test/db.softWhere.test.js
// 测试 softWhere() helper 在所有 SQL 形态下的行为
import { describe, it, expect } from 'vitest';
import { softWhere } from '../src/db.js';

describe('softWhere()', () => {
    it('纯 SELECT 无 WHERE → 末尾追加 WHERE is_deleted = 0', () => {
        expect(softWhere('vehicles', 'SELECT * FROM vehicles')).toBe(
            'SELECT * FROM vehicles WHERE vehicles.is_deleted = 0'
        );
    });

    it('SELECT ... WHERE id = ? → 注入到 WHERE 之后', () => {
        const r = softWhere('vehicles', 'SELECT * FROM vehicles WHERE id = ?');
        expect(r).toBe('SELECT * FROM vehicles WHERE vehicles.is_deleted = 0 AND id = ?');
    });

    it('WHERE 子句前替换', () => {
        const r = softWhere('vehicles', 'SELECT * FROM vehicles WHERE plate LIKE ?', 'v');
        expect(r).toBe('SELECT * FROM vehicles WHERE v.is_deleted = 0 AND plate LIKE ?');
    });

    it('已有 is_deleted 条件 → 不重复', () => {
        const sql = 'SELECT * FROM vehicles WHERE is_deleted = 0 AND id = ?';
        expect(softWhere('vehicles', sql)).toBe(sql);
    });

    it('表带别名 → 使用别名', () => {
        const r = softWhere('vehicles', 'SELECT v.* FROM vehicles v WHERE v.id = ?', 'v');
        expect(r).toBe('SELECT v.* FROM vehicles v WHERE v.is_deleted = 0 AND v.id = ?');
    });

    it('ORDER BY 在末尾 → WHERE 插在 ORDER 之前', () => {
        const r = softWhere('vehicles', 'SELECT * FROM vehicles ORDER BY id DESC');
        expect(r).toBe('SELECT * FROM vehicles WHERE vehicles.is_deleted = 0 ORDER BY id DESC');
    });

    it('GROUP BY 在末尾 → WHERE 插在 GROUP 之前', () => {
        const r = softWhere('vehicles', 'SELECT COUNT(*) FROM vehicles GROUP BY type');
        expect(r).toBe(
            'SELECT COUNT(*) FROM vehicles WHERE vehicles.is_deleted = 0 GROUP BY type'
        );
    });

    it('LIMIT 在末尾 → WHERE 插在 LIMIT 之前', () => {
        const r = softWhere('vehicles', 'SELECT * FROM vehicles LIMIT 10');
        expect(r).toBe('SELECT * FROM vehicles WHERE vehicles.is_deleted = 0 LIMIT 10');
    });

    it('末尾分号 → 去掉再追加', () => {
        const r = softWhere('vehicles', 'SELECT * FROM vehicles;');
        expect(r).toBe('SELECT * FROM vehicles WHERE vehicles.is_deleted = 0');
    });

    it('is_deleted 写为大写 IS_DELETED → 跳过', () => {
        const sql = 'SELECT * FROM vehicles WHERE IS_DELETED = 0';
        expect(softWhere('vehicles', sql)).toBe(sql);
    });

    it('不区分表名大小写', () => {
        const r = softWhere('VEHICLES', 'SELECT * FROM vehicles');
        expect(r).toBe('SELECT * FROM vehicles WHERE VEHICLES.is_deleted = 0');
    });

    it('UPDATE/DELETE 也支持', () => {
        expect(softWhere('vehicles', 'DELETE FROM vehicles WHERE id = ?')).toBe(
            'DELETE FROM vehicles WHERE vehicles.is_deleted = 0 AND id = ?'
        );
    });
});