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

export interface ServiceRow {
  id: number;
  category_slug: string;
  slug: string;
  icon: string;
  name: string;
  description: string;
  price: string;
  features: string; // JSON string[]
  is_active: number;
  sort_order: number;
}

export function parseFeatures(json: string): string[] {
  try { return JSON.parse(json) || []; } catch { return []; }
}

export function slugify(name: string): string {
  return name.toLowerCase().replace(/[^a-z0-9]+/g, "_").replace(/^_|_$/g, "");
}

export const ServiceModel = {
  getAll(): ServiceRow[] {
    return getDb()
      .prepare("SELECT * FROM services ORDER BY category_slug, sort_order, id")
      .all() as ServiceRow[];
  },

  getByCategory(categorySlug: string): ServiceRow[] {
    return getDb()
      .prepare(
        "SELECT * FROM services WHERE category_slug = ? AND is_active = 1 ORDER BY sort_order, id"
      )
      .all(categorySlug) as ServiceRow[];
  },

  getAllByCategory(categorySlug: string): ServiceRow[] {
    return getDb()
      .prepare("SELECT * FROM services WHERE category_slug = ? ORDER BY sort_order, id")
      .all(categorySlug) as ServiceRow[];
  },

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

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

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

  update(
    id: number,
    fields: {
      icon?: string;
      name?: string;
      description?: string;
      price?: string;
      features?: string;
      isActive?: number;
    }
  ): 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 (fields.price !== undefined) { sets.push("price = ?"); vals.push(fields.price); }
    if (fields.features !== undefined) { sets.push("features = ?"); vals.push(fields.features); }
    if (fields.isActive !== undefined) { sets.push("is_active = ?"); vals.push(fields.isActive); }
    if (!sets.length) return false;
    vals.push(id);
    return getDb().prepare(`UPDATE services SET ${sets.join(", ")} WHERE id = ?`).run(...vals).changes > 0;
  },

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

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