import { getDb } from "../utils/db";

export interface ServiceCategoryRow {
  id: number;
  slug: string;
  icon: string;
  name: string;
  description: string;
  sort_order: number;
}

export const ServiceCategoryModel = {
  getAll(): ServiceCategoryRow[] {
    return getDb()
      .prepare("SELECT * FROM service_categories ORDER BY sort_order, id")
      .all() as ServiceCategoryRow[];
  },

  getBySlug(slug: string): ServiceCategoryRow | null {
    return (
      (getDb()
        .prepare("SELECT * FROM service_categories WHERE slug = ?")
        .get(slug) as ServiceCategoryRow) ?? null
    );
  },

  getById(id: number): ServiceCategoryRow | null {
    return (
      (getDb()
        .prepare("SELECT * FROM service_categories WHERE id = ?")
        .get(id) as ServiceCategoryRow) ?? null
    );
  },

  create(data: { slug: string; icon: string; name: string; description: string }): number {
    const n = (getDb().prepare("SELECT COUNT(*) as n FROM service_categories").get() as any).n;
    const r = getDb()
      .prepare(
        "INSERT INTO service_categories (slug, icon, name, description, sort_order) VALUES (?, ?, ?, ?, ?)"
      )
      .run(data.slug, data.icon, data.name, data.description, n);
    return r.lastInsertRowid as number;
  },

  update(
    id: number,
    fields: { icon?: string; name?: string; description?: string }
  ): boolean {
    const sets: string[] = [];
    const vals: any[] = [];
    if (fields.icon !== undefined) { sets.push("icon = ?"); vals.push(fields.icon); }
    if (fields.name !== undefined) { sets.push("name = ?"); vals.push(fields.name); }
    if (fields.description !== undefined) { sets.push("description = ?"); vals.push(fields.description); }
    if (!sets.length) return false;
    vals.push(id);
    return getDb().prepare(`UPDATE service_categories SET ${sets.join(", ")} WHERE id = ?`).run(...vals).changes > 0;
  },

  delete(id: number): boolean {
    return getDb().prepare("DELETE FROM service_categories WHERE id = ?").run(id).changes > 0;
  },

  count(): number {
    return (getDb().prepare("SELECT COUNT(*) as n FROM service_categories").get() as { n: number }).n;
  },
};
