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

export interface Portfolio {
  id: number;
  title: string;
  category: "web" | "bot" | "mobile";
  description: string;
  tech: string;
  demo_url?: string;
  media_file_id?: string;
  media_type?: "photo" | "video";
  created_at: string;
}

export const PortfolioModel = {
  getAll(): Portfolio[] {
    return getDb()
      .prepare("SELECT * FROM portfolios ORDER BY created_at DESC")
      .all() as Portfolio[];
  },

  getByCategory(category: string): Portfolio[] {
    return getDb()
      .prepare("SELECT * FROM portfolios WHERE category = ? ORDER BY created_at DESC")
      .all(category) as Portfolio[];
  },

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

  create(data: {
    title: string;
    category: string;
    description: string;
    tech: string;
    demoUrl?: string;
    mediaFileId?: string;
    mediaType?: "photo" | "video";
  }) {
    getDb()
      .prepare(`
        INSERT INTO portfolios (title, category, description, tech, demo_url, media_file_id, media_type)
        VALUES (@title, @category, @description, @tech, @demoUrl, @mediaFileId, @mediaType)
      `)
      .run({
        title: data.title,
        category: data.category,
        description: data.description,
        tech: data.tech,
        demoUrl: data.demoUrl ?? null,
        mediaFileId: data.mediaFileId ?? null,
        mediaType: data.mediaType ?? "photo",
      });
  },

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

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