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

export type ServiceType =
  | "web"
  | "apk"
  | "bot"
  | "landing"
  | "company"
  | "ecommerce"
  | "mobile"
  | "automation"
  | "custom"
  | "other";

export interface Lead {
  id?: number;
  telegram_id: number;
  username?: string;
  name: string;
  phone_number?: string;
  service_type: ServiceType;
  project_name: string;
  features: string;
  deadline: string;
  budget: string;
  status: string;
  is_hot: number;
  created_at: string;
  admin_note?: string;
}

export const LeadModel = {
  create(data: {
    telegramId: number;
    username?: string;
    name: string;
    phoneNumber?: string;
    serviceType: ServiceType;
    projectName: string;
    features: string;
    deadline: string;
    budget: string;
  }) {
    const db = getDb();
    db.prepare(`
      INSERT INTO leads
        (telegram_id, username, name, phone_number, service_type, project_name, features, deadline, budget)
      VALUES
        (@telegramId, @username, @name, @phoneNumber, @serviceType, @projectName, @features, @deadline, @budget)
    `).run(data);
  },

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

  countByStatus(status: string): number {
    const db = getDb();
    const row = db
      .prepare("SELECT COUNT(*) as n FROM leads WHERE status = ?")
      .get(status) as { n: number };
    return row.n;
  },

  countHot(): number {
    const db = getDb();
    const row = db
      .prepare("SELECT COUNT(*) as n FROM leads WHERE is_hot = 1")
      .get() as { n: number };
    return row.n;
  },

  countToday(): number {
    const db = getDb();
    const row = db
      .prepare("SELECT COUNT(*) as n FROM leads WHERE date(created_at) = date('now')")
      .get() as { n: number };
    return row.n;
  },

  getNewLeads(limit = 10): Lead[] {
    const db = getDb();
    return db
      .prepare(
        "SELECT * FROM leads WHERE status IN ('new','hot') ORDER BY is_hot DESC, created_at DESC LIMIT ?"
      )
      .all(limit) as Lead[];
  },
};
