import { executeQuery, executeTransaction } from '../config/database.js';

export class Transaction {
  constructor(data) {
    this.id = data.id;
    this.order_id = data.order_id;
    this.rrr = data.rrr;
    this.payer_name = data.payer_name;
    this.payer_email = data.payer_email;
    this.payer_phone = data.payer_phone;
    this.payment_item_id = data.payment_item_id;
    this.payment_item = data.payment_item;
    this.amount = data.amount;
    this.service_type_id = data.service_type_id;
    this.status = data.status;
    this.remita_status = data.remita_status;
    this.remita_message = data.remita_message;
    this.transaction_date = data.transaction_date;
    this.created_at = data.created_at;
    this.updated_at = data.updated_at;
  }

  // Create new transaction
  static async create(data) {
    const query = `
      INSERT INTO transactions 
      (order_id, rrr, payer_name, payer_email, payer_phone, payment_item_id, 
       payment_item, amount, service_type_id, status) 
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;
    const result = await executeQuery(query, [
      data.order_id,
      data.rrr,
      data.payer_name,
      data.payer_email,
      data.payer_phone,
      data.payment_item_id,
      data.payment_item,
      data.amount,
      data.service_type_id,
      data.status || 'pending'
    ]);
    return result.insertId;
  }

  // Get transaction by order ID
  static async getByOrderId(orderId) {
    const query = `
      SELECT * FROM transactions 
      WHERE order_id = ?
    `;
    const rows = await executeQuery(query, [orderId]);
    return rows.length > 0 ? new Transaction(rows[0]) : null;
  }

  // Get transaction by RRR
  static async getByRRR(rrr) {
    const query = `
      SELECT * FROM transactions 
      WHERE rrr = ?
    `;
    const rows = await executeQuery(query, [rrr]);
    return rows.length > 0 ? new Transaction(rows[0]) : null;
  }

  // Update transaction RRR
  static async updateRRR(orderId, rrr) {
    const query = `
      UPDATE transactions 
      SET rrr = ?, updated_at = CURRENT_TIMESTAMP
      WHERE order_id = ?
    `;
    await executeQuery(query, [rrr, orderId]);
  }

  // Update transaction status
  static async updateStatus(orderId, status, remitaData = {}) {
    const query = `
      UPDATE transactions 
      SET status = ?, remita_status = ?, remita_message = ?, 
          transaction_date = ?, updated_at = CURRENT_TIMESTAMP
      WHERE order_id = ?
    `;

    let transactionDateValue = null;
    if (remitaData.transactionDate) {
      const parsedDate = new Date(remitaData.transactionDate);
      transactionDateValue = Number.isNaN(parsedDate.getTime()) ? null : parsedDate;
    } else if (status === 'completed') {
      transactionDateValue = new Date();
    }

    await executeQuery(query, [
      status,
      remitaData.status || null,
      remitaData.message || null,
      transactionDateValue,
      orderId
    ]);
  }

  // Get all transactions with pagination
  static async getAll(page = 1, limit = 50, filters = {}) {
    let query = `
      SELECT t.*, pi.description as payment_description
      FROM transactions t
      LEFT JOIN payment_items pi ON t.payment_item_id COLLATE utf8mb4_general_ci = pi.payment_item_id COLLATE utf8mb4_general_ci
      WHERE 1=1
    `;
    const params = [];

    // Add filters
    if (filters.status) {
      query += ' AND t.status = ?';
      params.push(filters.status);
    }
    if (filters.payment_item_id) {
      query += ' AND t.payment_item_id = ?';
      params.push(filters.payment_item_id);
    }
    if (filters.date_from) {
      query += ' AND DATE(t.created_at) >= ?';
      params.push(filters.date_from);
    }
    if (filters.date_to) {
      query += ' AND DATE(t.created_at) <= ?';
      params.push(filters.date_to);
    }

    // Add pagination
    const safeLimit = Number.isFinite(Number(limit)) && Number(limit) > 0 ? Number(limit) : 50;
    const safePage = Number.isFinite(Number(page)) && Number(page) > 0 ? Number(page) : 1;
    const offset = (safePage - 1) * safeLimit;

    // In some MySQL versions, using placeholders for LIMIT/OFFSET in prepared
    // statements can cause "Incorrect arguments to mysqld_stmt_execute".
    // Since these values are server-side only, safely interpolate them.
    query += ` ORDER BY t.created_at DESC LIMIT ${safeLimit} OFFSET ${offset}`;

    const rows = await executeQuery(query, params);
    return rows.map(row => new Transaction(row));
  }

  // Get transaction statistics
  static async getStatistics(filters = {}) {
    let query = `
      SELECT 
        COUNT(*) as total_transactions,
        SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed_transactions,
        SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_transactions,
        SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed_transactions,
        SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) as total_revenue
      FROM transactions
      WHERE 1=1
    `;
    const params = [];

    // Add filters
    if (filters.date_from) {
      query += ' AND DATE(created_at) >= ?';
      params.push(filters.date_from);
    }
    if (filters.date_to) {
      query += ' AND DATE(created_at) <= ?';
      params.push(filters.date_to);
    }

    const rows = await executeQuery(query, params);
    return rows[0];
  }

  // Log payment action
  static async logAction(transactionId, action, details, req) {
    const query = `
      INSERT INTO payment_logs 
      (transaction_id, action, details, ip_address, user_agent) 
      VALUES (?, ?, ?, ?, ?)
    `;
    await executeQuery(query, [
      transactionId,
      action,
      JSON.stringify(details),
      req?.ip || req?.connection?.remoteAddress,
      req?.get('User-Agent')
    ]);
  }
}
