import { createConnection } from 'mysql2/promise';

export interface CreateVoucherInput {
  productVariantId: number;
  value: number;
  forWho?: string;
  note?: string;
}

export interface CreateVoucherResult {
  voucherId: number;
  code: string;
  expiresAt: Date;
}

// Generate 8-character code with numbers and letters
function generateVoucherCode(): string {
  const chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
  let code = '';
  for (let i = 0; i < 8; i++) {
    code += chars.charAt(Math.floor(Math.random() * chars.length));
  }
  return code;
}

async function createDatabaseConnection() {
  return await createConnection({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
  });
}

export async function createVoucher(input: CreateVoucherInput): Promise<CreateVoucherResult> {
  const { productVariantId, value, forWho, note } = input;

  // Validate input
  if (!productVariantId || !value) {
    throw new Error('productVariantId and value are required');
  }

  if (value <= 0) {
    throw new Error('value must be greater than 0');
  }

  let connection: any = null;

  try {
    connection = await createDatabaseConnection();

    // Generate unique voucher code
    let code = generateVoucherCode();
    let isUnique = false;
    let attempts = 0;
    const maxAttempts = 10;

    while (!isUnique && attempts < maxAttempts) {
      const [existing] = await connection.execute(
        'SELECT id FROM vouchers WHERE code = ?',
        [code]
      );

      if ((existing as any[]).length === 0) {
        isUnique = true;
      } else {
        code = generateVoucherCode();
      }
      attempts++;
    }

    if (!isUnique) {
      throw new Error('Failed to generate unique voucher code after 10 attempts');
    }

    // Calculate expiration date - 1 year from now
    const expiresAt = new Date();
    expiresAt.setFullYear(expiresAt.getFullYear() + 1);

    // Insert voucher into database
    const [result] = await connection.execute(
      `INSERT INTO vouchers (product_variant_id, code, value, for_who, note, expires_at, created_at, updated_at)
       VALUES (?, ?, ?, ?, ?, ?, NOW(), NOW())`,
      [productVariantId, code, value, forWho || null, note || null, expiresAt]
    );

    const voucherId = (result as any).insertId;

    return {
      voucherId,
      code,
      expiresAt,
    };
  } catch (error) {
    throw error;
  } finally {
    if (connection) {
      try {
        await connection.end();
      } catch (e) {
      }
    }
  }
}