import 'package:drift/drift.dart';
import 'package:drift_postgres/drift_postgres.dart';
import '../database.dart';
import '../tables/payments.dart';
import 'mixins/soft_delete_mixin.dart';

part 'payment_dao.g.dart';

@DriftAccessor(tables: [Payments])
class PaymentDao extends DatabaseAccessor<AppDatabase>
    with _$PaymentDaoMixin, SoftDeleteMixin<Payments, Payment> {
  PaymentDao(super.db);

  @override
  TableInfo<Payments, Payment> get table => payments;

  /// Получить платеж по ID (только активные)
  Future<Payment?> getPaymentById(String id) async {
    print('🔍 PaymentDao.getPaymentById: paymentId=$id');
    print('🔍 PaymentDao.getPaymentById: Calling getActiveById');
    final result = await getActiveById(id);
    print(
      '✅ PaymentDao.getPaymentById: Result=${result != null ? "found" : "not found"}',
    );
    return result;
  }

  /// Получить платежи пользователя (только активные)
  Future<List<Payment>> getPaymentsByUserId(
    String userId, {
    int? limit,
    int? offset,
  }) {
    final query = selectActive()
      ..where((p) => p.userId.equals(userId))
      ..orderBy([(p) => OrderingTerm.desc(p.date)]);

    if (limit != null) {
      query.limit(limit, offset: offset);
    }

    return query.get();
  }

  /// Получить платежи по статусу (только активные)
  Future<List<Payment>> getPaymentsByStatus(String status) {
    return (selectActive()
          ..where((p) => p.status.equals(status))
          ..orderBy([(p) => OrderingTerm.desc(p.date)]))
        .get();
  }

  /// Создать платеж
  Future<String> createPayment(PaymentsCompanion payment) async {
    print('🔍 PaymentDao.createPayment: Starting payment creation');
    print(
      '🔍 PaymentDao.createPayment: externalToken=${payment.externalToken.value}',
    );
    print('🔍 PaymentDao.createPayment: userId=${payment.userId.value}');
    print('🔍 PaymentDao.createPayment: amount=${payment.amount.value}');

    final inserted = await into(payments).insertReturning(payment);
    print(
      '✅ PaymentDao.createPayment: Payment inserted, date=${inserted.date.dateTime}',
    );

    // Try to get id from inserted object first
    if (inserted.id.isNotEmpty) {
      print(
        '✅ PaymentDao.createPayment: Got id from inserted object, id=${inserted.id}',
      );
      return inserted.id;
    }

    // Query back to get the id using type-safe queries
    // Use externalToken if available, otherwise query by unique fields
    if (inserted.externalToken != null && inserted.externalToken!.isNotEmpty) {
      print(
        '🔍 PaymentDao.createPayment: Querying by externalToken=${inserted.externalToken}',
      );
      final foundPayment = await getPaymentByExternalToken(
        inserted.externalToken!,
      );
      if (foundPayment != null && foundPayment.id.isNotEmpty) {
        print(
          '✅ PaymentDao.createPayment: Found payment by externalToken, id=${foundPayment.id}',
        );
        return foundPayment.id;
      }
      print('⚠️ PaymentDao.createPayment: No payment found by externalToken');
    }

    // Fallback: query by userId, date, and amount using type-safe queries
    print(
      '🔍 PaymentDao.createPayment: Querying by userId, date, amount (fallback)',
    );
    final query = select(payments)
      ..where(
        (p) =>
            p.userId.equals(payment.userId.value) &
            p.date.equals(inserted.date) &
            p.amount.equals(inserted.amount),
      )
      ..orderBy([(p) => OrderingTerm.desc(p.createdAt)])
      ..limit(1);

    final results = await query.get();
    if (results.isNotEmpty && results.first.id.isNotEmpty) {
      print(
        '✅ PaymentDao.createPayment: Found payment by fallback query, id=${results.first.id}',
      );
      return results.first.id;
    }

    print(
      '❌ PaymentDao.createPayment: Failed to retrieve payment ID after creation',
    );
    throw Exception('Failed to retrieve payment ID after creation');
  }

  /// Обновить платеж
  Future<bool> updatePayment(Payment payment) {
    return update(payments).replace(payment);
  }

  /// Обновить платеж частично
  Future<void> updatePaymentCompanion(
    String paymentId,
    PaymentsCompanion companion,
  ) {
    return (update(
      payments,
    )..where((p) => p.id.equals(paymentId))).write(companion);
  }

  /// Обновить статус платежа
  Future<void> updatePaymentStatus(String externalToken, String status) async {
    print(
      '🔍 PaymentDao.updatePaymentStatus: token=$externalToken, status=$status',
    );
    final result =
        await (update(
          payments,
        )..where((p) => p.externalToken.equals(externalToken))).write(
          PaymentsCompanion(
            status: Value(status),
            updatedAt: Value(PgDateTime(DateTime.now())),
          ),
        );
    print('✅ PaymentDao.updatePaymentStatus: result=$result');
  }

  /// Получить платеж по externalToken (только активные)
  Future<Payment?> getPaymentByExternalToken(String token) async {
    final query = selectActive()
      ..where((p) => p.externalToken.equals(token))
      ..limit(1);

    final results = await query.get();
    return results.isNotEmpty ? results.first : null;
  }

  /// Получить платежи по продукту (store ID) (только активные)
  Future<List<Payment>> getPaymentsByProduct(String productId) async {
    // Поиск по продуктам в JSON массиве
    final query = selectActive()..where((p) => p.products.like('%$productId%'));

    return query.get();
  }

  /// Получить платежи по статусу с пагинацией (только активные)
  Future<List<Payment>> getPaymentsByStatusPaged(
    String status, {
    int? limit,
    int? offset,
  }) {
    final query = selectActive()
      ..where((p) => p.status.equals(status))
      ..orderBy([(p) => OrderingTerm.desc(p.date)]);

    if (limit != null) {
      query.limit(limit, offset: offset);
    }

    return query.get();
  }

  /// Получить все платежи (для админки) (только активные)
  Future<List<Payment>> getAllPayments({int? limit, int? offset}) {
    final query = selectActive()..orderBy([(p) => OrderingTerm.desc(p.date)]);

    if (limit != null) {
      query.limit(limit, offset: offset);
    }

    return query.get();
  }

  /// Подсчитать все платежи (только активные)
  Future<int> countAllPayments() async {
    final countExpr = payments.id.count();
    final query = selectOnly(payments)
      ..addColumns([countExpr])
      ..where(payments.isDeleted.equals(false));

    return await query.map((row) => row.read(countExpr)!).getSingle();
  }

  /// Получить последний платеж пользователя для продукта (пака)
  /// Используется для получения paymentId при возврате с ЮКассы
  Future<Payment?> getLatestPaymentForUserAndPack({
    required String userId,
    required String packId,
  }) async {
    final query = selectActive()
      ..where(
        (p) => p.userId.equals(userId) & p.products.like('%"id":"$packId"%'),
      )
      ..orderBy([(p) => OrderingTerm.desc(p.createdAt)])
      ..limit(1);

    final results = await query.get();
    return results.isNotEmpty ? results.first : null;
  }

  /// Подсчитать платежи пользователя (только активные)
  Future<int> countPaymentsByUserId(String userId) async {
    final countExpr = payments.id.count();
    final query = selectOnly(payments)
      ..addColumns([countExpr])
      ..where(
        payments.userId.equals(userId) & payments.isDeleted.equals(false),
      );

    return await query.map((row) => row.read(countExpr)!).getSingle();
  }
}