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<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);
  }

  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);
  }

  Future<bool> updateOrderLine(RigaOrdine line) {
    return db.update(db.righeOrdine).replace(line);
  }

  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));
  }
}