import pool from "./db/db";

export interface Transaction {
  id: string;
  amount: number;
  currency: string;
  status: string;
  stripe_payment_intent_id: string;
  customer_email: string;
  customer_name: string;
  consultation_type: string;
  metadata: any;
  created_at: string;
  updated_at: string;
}

const connection = await pool.getConnection();

export async function createTransaction(transactionData: Omit<Transaction, 'id' | 'created_at' | 'updated_at'>) {

  const transactionId = `txn_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`;
  
  const result = await connection.query(
    `INSERT INTO transactions (id, amount, currency, status, stripe_payment_intent_id, customer_email, customer_name, consultation_type, metadata) 
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [
      transactionId,
      transactionData.amount,
      transactionData.currency,
      transactionData.status,
      transactionData.stripe_payment_intent_id,
      transactionData.customer_email,
      transactionData.customer_name,
      transactionData.consultation_type,
      JSON.stringify(transactionData.metadata)
    ]
  );

  return transactionId;
}

export async function getTransactionByStripeId(stripePaymentIntentId: string): Promise<Transaction | null> {
  const transactions: any = await connection.query(
    'SELECT * FROM transactions WHERE stripe_payment_intent_id = ?',
    [stripePaymentIntentId]
  );

  return transactions.length > 0 ? transactions[0] : null;
}

export async function updateTransactionStatus(stripePaymentIntentId: string, status: string, metadata?: any) {
  let sql = `UPDATE transactions SET status = ?, updated_at = CURRENT_TIMESTAMP`;
  const params: any[] = [status];

  if (metadata) {
    sql += `, metadata = ?`;
    params.push(JSON.stringify(metadata));
  }

  sql += ` WHERE stripe_payment_intent_id = ?`;
  params.push(stripePaymentIntentId);

  await connection.query(sql, params);
}

export async function getCustomerTransactions(customerEmail: string, limit = 10): Promise<Transaction[]> {
  const transactions: any = await connection.query(
    `SELECT * FROM transactions 
     WHERE customer_email = ? 
     ORDER BY created_at DESC 
     LIMIT ?`,
    [customerEmail, limit]
  );

  return transactions;
}