import { getDb, RunResult } from '../db.js';
import type { Product } from '../models/Product.js';
export class ProductRepository {
async create(p: Omit<Product, 'id' | 'created_at' | 'updated_at'>): Promise<Product> {
const db = await getDb();
try {
const result = await db.run(
`INSERT INTO products (sku, name, description, price, quantity) VALUES (?, ?, ?, ?, ?)`,
[p.sku, p.name, p.description ?? null, p.price, p.quantity]
) as RunResult;
const id = result.lastID;
if (!id) throw new Error('Failed to get insert ID');
const created = await this.getById(id);
if (!created) throw new Error('Insert succeeded but could not read back record');
return created;
} catch (error: any) {
if (error.message.includes('UNIQUE constraint failed')) {
throw new Error(`Product with SKU '${p.sku}' already exists`);
}
throw error;
}
}
async getById(id: number): Promise<Product | null> {
const db = await getDb();
const row = await db.get('SELECT * FROM products WHERE id = ?', [id]);
return row as Product | null;
}
async getBySku(sku: string): Promise<Product | null> {
const db = await getDb();
const row = await db.get('SELECT * FROM products WHERE sku = ?', [sku]);
return row as Product | null;
}
async updateById(id: number, patch: Partial<Omit<Product, 'id'>>): Promise<Product | null> {
const db = await getDb();
const fields: string[] = [];
const values: any[] = [];
for (const [key, value] of Object.entries(patch)) {
if (value === undefined) continue;
fields.push(`${key} = ?`);
values.push(value);
}
if (fields.length === 0) return this.getById(id);
values.push(id);
const result = await db.run(
`UPDATE products SET ${fields.join(', ')}, updated_at = CURRENT_TIMESTAMP WHERE id = ?`,
values
) as RunResult;
if (!result.changes || result.changes === 0) {
return null;
}
return this.getById(id);
}
async deleteById(id: number): Promise<boolean> {
const db = await getDb();
const result = await db.run('DELETE FROM products WHERE id = ?', [id]) as RunResult;
return (result.changes || 0) > 0;
}
async list(limit = 50, offset = 0): Promise<Product[]> {
const db = await getDb();
const rows = await db.all('SELECT * FROM products ORDER BY id DESC LIMIT ? OFFSET ?', [limit, offset]);
return rows as Product[];
}
async searchByName(q: string, limit = 50, offset = 0): Promise<Product[]> {
const db = await getDb();
const like = `%${q}%`;
const rows = await db.all('SELECT * FROM products WHERE name LIKE ? ORDER BY id DESC LIMIT ? OFFSET ?', [like, limit, offset]);
return rows as Product[];
}
}
