export interface AnalysisHistory {
  id?: number
  analysisDate: Date
  periodStart: Date
  periodEnd: Date
  totalRevenue: number
  totalOrders: number
  uniqueCustomers: number
  createdAt?: Date
}

export function createAnalysisHistoryTable(db: any) {
  db.exec(`
    CREATE TABLE IF NOT EXISTS analysis_history (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      analysis_date TEXT NOT NULL,
      period_start TEXT NOT NULL,
      period_end TEXT NOT NULL,
      total_revenue REAL,
      total_orders INTEGER,
      unique_customers INTEGER,
      created_at TEXT DEFAULT CURRENT_TIMESTAMP
    );
  `)
}

export function saveAnalysisHistory(
  db: any,
  history: Omit<AnalysisHistory, 'id' | 'createdAt'>
): AnalysisHistory {
  const result = db
    .prepare(
      'INSERT INTO analysis_history (analysis_date, period_start, period_end, total_revenue, total_orders, unique_customers) VALUES (?, ?, ?, ?, ?, ?)'
    )
    .run(
      history.analysisDate.toISOString(),
      history.periodStart.toISOString(),
      history.periodEnd.toISOString(),
      history.totalRevenue,
      history.totalOrders,
      history.uniqueCustomers
    )

  return {
    ...history,
    id: result.lastInsertRowid as number,
    createdAt: new Date(),
  }
}

export function getAnalysisHistory(db: any, limit: number = 50): AnalysisHistory[] {
  return db
    .prepare('SELECT * FROM analysis_history ORDER BY created_at DESC LIMIT ?')
    .all(limit)
    .map((row: any) => ({
      id: row.id,
      analysisDate: new Date(row.analysis_date),
      periodStart: new Date(row.period_start),
      periodEnd: new Date(row.period_end),
      totalRevenue: row.total_revenue,
      totalOrders: row.total_orders,
      uniqueCustomers: row.unique_customers,
      createdAt: new Date(row.created_at),
    }))
}