import 'package:drift/drift.dart';
import '../database.dart';

class OrdiniDao {
  final AppDatabase db;
  OrdiniDao(this.db);

  Future<Ordine?> getById(int id) {
    return (db.select(
      db.ordini,
    )..where((t) => t.idOrdine.equals(id)))
        .getSingleOrNull();
  }

  Future<Ordine?> getByNumber(int numeroOrdine) {
    return (db.select(
      db.ordini,
    )..where((t) => t.numeroOrdine.equals(numeroOrdine)))
        .getSingleOrNull();
  }

  Future<List<Ordine>> customerOrders(int idCliente, bool includeReferences) {
    return (db.select(db.ordini)
          ..where((t) => t.idCliente.equals(idCliente))
          ..orderBy([
            (t) =>
                OrderingTerm(expression: t.dataOrdine, mode: OrderingMode.desc),
          ])
          ..limit(200))
        .get();
  }

  Future<List<Ordine>> getLastOrders(int idAgente, int max) {
    return (db.select(db.ordini)
          ..where((t) => t.idAgente.equals(idAgente))
          ..orderBy([
            (t) =>
                OrderingTerm(expression: t.dataOrdine, mode: OrderingMode.desc),
          ])
          ..limit(max))
        .get();
  }

  Future<List<VOrdiniData>> getOrdersView(int idAgente, {int limit = 200}) {
    return (db.select(db.vOrdini)
          ..where((t) => t.idAgente.equals(idAgente))
          ..orderBy([
            (t) =>
                OrderingTerm(expression: t.dataOrdine, mode: OrderingMode.desc),
          ])
          ..limit(limit))
        .get();
  }

  Future<List<VOrdiniData>> searchOrdersView(
    int idAgente,
    String filter,
  ) async {
    final words = filter.split(' ').where((w) => w.isNotEmpty).toList();
    final patterns = words.map((w) => '%$w%').toList();

    final query = db.select(db.vOrdini)
      ..where((t) => t.idAgente.equals(idAgente))
      ..orderBy([
        (t) => OrderingTerm(expression: t.dataOrdine, mode: OrderingMode.desc),
      ]);

    for (final p in patterns) {
      query.where(
        (t) =>
            t.ragioneSociale.like(p) |
            t.filiale.like(p) |
            t.indirizzo.like(p) |
            t.citta.like(p) |
            CustomExpression<String>('CAST(numeroOrdine AS TEXT)').like(p),
      );
    }

    return query.get();
  }

  Future<int> countNewOrdersForAgent(int idAgente) async {
    final rows = await (db.select(
      db.vOrdini,
    )..where((t) => t.idAgente.equals(idAgente) & t.stato.equals('N')))
        .get();
    return rows.length;
  }

  Future<int> countOrdersForAgent(int idAgente) async {
    final rows = await (db.select(
      db.vOrdini,
    )..where((t) => t.idAgente.equals(idAgente)))
        .get();
    return rows.length;
  }

  Future<double> getMonthlyVolume(int idAgente) async {
    final now = DateTime.now();
    final firstOfMonth = DateTime(now.year, now.month, 1);
    final dateStr = '${firstOfMonth.year.toString().padLeft(4, '0')}-'
        '${firstOfMonth.month.toString().padLeft(2, '0')}-01';

    final rows = await (db.select(db.vOrdini)
          ..where(
            (t) =>
                t.idAgente.equals(idAgente) &
                t.dataOrdine.isNotNull() &
                t.dataOrdine.isBiggerOrEqualValue(dateStr),
          ))
        .get();
    return rows.fold<double>(0, (sum, r) => sum + (r.totale ?? 0));
  }

  Future<int> countNewOrders() async {
    final rows = await (db.select(
      db.ordini,
    )..where((t) => t.stato.equals('N')))
        .get();
    return rows.length;
  }

  Future<int> countOrdersOnHold() async {
    final rows = await (db.select(
      db.ordini,
    )..where((t) => t.stato.equals('H')))
        .get();
    return rows.length;
  }

  Future<List<Ordine>> getNewOrders() {
    return (db.select(db.ordini)..where((t) => t.stato.equals('N'))).get();
  }

  Future<List<Ordine>> getUnsentOrders(int max) {
    return (db.select(db.ordini)
          ..where((t) => t.stato.equals('N') | t.stato.equals('H'))
          ..limit(max))
        .get();
  }

  Future<int> generaNumeroOrdine(int idAgente) async {
    final result = await (db.select(db.ordini)
          ..where((t) => t.idAgente.equals(idAgente))
          ..orderBy([
            (t) => OrderingTerm(
                  expression: t.numeroOrdine,
                  mode: OrderingMode.desc,
                ),
          ])
          ..limit(1))
        .getSingleOrNull();
    return (result?.numeroOrdine ?? 0) + 1;
  }

  Future<void> markAsSent(int idOrdine, int idAssegnato) {
    return (db.update(db.ordini)..where((t) => t.idOrdine.equals(idOrdine)))
        .write(OrdiniCompanion(stato: const Value('T')));
  }

  Future<void> markAsSentStatus(int idOrdine) {
    return (db.update(db.ordini)..where((t) => t.idOrdine.equals(idOrdine)))
        .write(OrdiniCompanion(stato: const Value('T')));
  }

  Future<int> insertOrder(Ordine order) {
    return db.into(db.ordini).insert(
        order.toCompanion(true).copyWith(idOrdine: const Value.absent()));
  }

  Future<bool> updateOrder(Ordine order) {
    return db.update(db.ordini).replace(order);
  }

  Future<void> deleteOrder(int idOrdine) {
    return (db.delete(
      db.ordini,
    )..where((t) => t.idOrdine.equals(idOrdine)))
        .go();
  }

  Future<int> insertOrderLine(RigaOrdine line) {
    return db.into(db.righeOrdine).insert(line.toCompanion(true).copyWith(
          idRigaOrdine: const Value.absent(),
        ));
  }

  Future<bool> updateOrderLine(RigaOrdine line) {
    return db.update(db.righeOrdine).replace(line.toCompanion(true).copyWith(
          idRigaOrdine: const Value.absent(),
        ));
  }

  Future<List<RigaOrdine>> getOrderLines(int idOrdine) {
    return (db.select(
      db.righeOrdine,
    )..where((t) => t.idOrdine.equals(idOrdine)))
        .get();
  }

  Future<void> deleteOrderLine(int idRigaOrdine) {
    return (db.delete(
      db.righeOrdine,
    )..where((t) => t.idRigaOrdine.equals(idRigaOrdine)))
        .go();
  }

  Future<void> zapOrderLines(int idOrdine) {
    return (db.delete(
      db.righeOrdine,
    )..where((t) => t.idOrdine.equals(idOrdine)))
        .go();
  }

  Future<Ordine?> findByNumeroOrdineSede(String numSede) {
    return (db.select(
      db.ordini,
    )..where((t) => t.numeroOrdineSede.equals(numSede)))
        .getSingleOrNull();
  }

  Future<Ordine?> findByIdOrdineSede(int idSede) {
    return (db.select(
      db.ordini,
    )..where((t) => t.idOrdineSede.equals(idSede)))
        .getSingleOrNull();
  }

  Future<double> totalAmountItems(int idOrdine) async {
    final lines = await (db.select(db.righeOrdine)
          ..where(
            (t) =>
                t.idOrdine.equals(idOrdine) &
                (t.rigaPromozione.equals(0) | t.rigaPromozione.isNull()),
          ))
        .get();
    return lines.fold<double>(0.0, (sum, l) => sum + (l.totale ?? 0.0));
  }

  Future<double> totalQtyItems(int idOrdine) async {
    final lines = await (db.select(db.righeOrdine)
          ..where(
            (t) =>
                t.idOrdine.equals(idOrdine) &
                (t.rigaPromozione.equals(0) | t.rigaPromozione.isNull()),
          ))
        .get();
    return lines.fold<double>(0.0, (sum, l) => sum + (l.quantita ?? 0.0));
  }
}