import DataLoader from 'dataloader';
import { Injectable, Scope } from 'graphql-modules';
import { sql } from '@pgtyped/runtime';
import { reassureOwnerIdExists } from '../../../shared/helpers/index.js';
import { AdminContextProvider } from '../../admin-context/providers/admin-context.provider.js';
import { TenantAwareDBClient } from '../../app-providers/tenant-db-client.js';
import type {
  DateOrString,
  IAddBusinessTripAttendeesParams,
  IAddBusinessTripAttendeesQuery,
  IGetBusinessTripsAttendeesByBusinessTripIdsQuery,
  IGetBusinessTripsAttendeesByBusinessTripIdsResult,
  IGetBusinessTripsByAttendeeIdQuery,
  IGetBusinessTripsByChargeIdsResult,
  IGetLastFlightByDateAndAttendeeIdQuery,
  IRemoveBusinessTripAttendeesParams,
  IRemoveBusinessTripAttendeesQuery,
  IUpdateBusinessTripAttendeeParams,
  IUpdateBusinessTripAttendeeQuery,
} from '../types.js';
import { BusinessTripsProvider } from './business-trips.provider.js';

const getBusinessTripsAttendeesByBusinessTripIds = sql<IGetBusinessTripsAttendeesByBusinessTripIdsQuery>`
  SELECT bta.business_trip_id, bta.arrival, bta.departure, fe.*, b.vat_number, b.hebrew_name, b.address, b.address_hebrew, b.email, b.website, b.phone_number, b.country, b.no_invoices_required, b.suggestion_data, b.can_settle_with_receipt, b.exempt_dealer, b.optional_vat, b.pcn874_record_type_override, b.city, b.zip_code
  FROM accounter_schema.financial_entities fe
  LEFT JOIN accounter_schema.business_trips_attendees bta
    ON bta.attendee_business_id = fe.id
  LEFT JOIN accounter_schema.businesses b
    ON bta.attendee_business_id = b.id
  WHERE bta.business_trip_id IN $$businessTripIds;`;

const getBusinessTripsByAttendeeId = sql<IGetBusinessTripsByAttendeeIdQuery>`
  SELECT DISTINCT bt.*
  FROM accounter_schema.business_trips_attendees bta
  LEFT JOIN accounter_schema.business_trips bt
    ON bt.id = bta.business_trip_id
  WHERE bta.attendee_business_id = $attendeeId;`;

const getLastFlightByDateAndAttendeeId = sql<IGetLastFlightByDateAndAttendeeIdQuery>`
  SELECT *
  FROM accounter_schema.business_trips_attendees
  WHERE attendee_business_id = $attendeeBusinessId
    AND arrival < $date
    AND departure <= $date AND departure >= $date - INTERVAL '14 DAY'
  ORDER BY departure DESC
      FETCH FIRST 1 ROWS ONLY;`;

const addBusinessTripAttendees = sql<IAddBusinessTripAttendeesQuery>`
  INSERT INTO accounter_schema.business_trips_attendees (business_trip_id, attendee_business_id, arrival, departure, owner_id)
  VALUES ($businessTripId, $businessId, $arrival, $departure, $ownerId)
  ON CONFLICT DO NOTHING
  RETURNING *;`;

const updateBusinessTripAttendee = sql<IUpdateBusinessTripAttendeeQuery>`
  UPDATE accounter_schema.business_trips_attendees
  SET
  arrival = COALESCE(
    $arrival,
    arrival
  ),
  departure = COALESCE(
    $departure,
    departure
  )
  WHERE
    business_trip_id = $businessTripId
    AND attendee_business_id = $attendeeBusinessId
  RETURNING *;
`;

const removeBusinessTripAttendees = sql<IRemoveBusinessTripAttendeesQuery>`
  DELETE FROM accounter_schema.business_trips_attendees
  WHERE business_trip_id = $businessTripId
    AND attendee_business_id = $businessId
  RETURNING *;`;

@Injectable({
  scope: Scope.Operation,
  global: true,
})
export class BusinessTripAttendeesProvider {
  constructor(
    private db: TenantAwareDBClient,
    private adminContextProvider: AdminContextProvider,
    private businessTripsProvider: BusinessTripsProvider,
  ) {}

  private async batchBusinessTripsAttendeesByChargeIds(chargeIds: readonly string[]) {
    const businessTripsMatches = await this.businessTripsProvider.getBusinessTripsByChargeIdLoader
      .loadMany(chargeIds)
      .then(
        res =>
          res.filter(
            item => item && !(item instanceof Error),
          ) as IGetBusinessTripsByChargeIdsResult[],
      );
    const businessTripsAttendees = await this.getBusinessTripsAttendeesByBusinessTripIdLoader
      .loadMany(businessTripsMatches.filter(record => record.id).map(record => record.id))
      .then(
        records =>
          records
            .flat()
            .filter(
              record => !(record instanceof Error),
            ) as IGetBusinessTripsAttendeesByBusinessTripIdsResult[],
      );
    return chargeIds.map(id =>
      businessTripsMatches
        .filter(record => record.charge_id === id)
        .map(match =>
          businessTripsAttendees.filter(attendee => attendee.business_trip_id === match.id),
        )
        .flat(),
    );
  }

  public getBusinessTripsAttendeesByChargeIdLoader = new DataLoader(
    (chargeIds: readonly string[]) => this.batchBusinessTripsAttendeesByChargeIds(chargeIds),
    {
      cache: false,
    },
  );

  private async batchBusinessTripsAttendeesByBusinessTripIds(businessTripIds: readonly string[]) {
    const businessTrips = await getBusinessTripsAttendeesByBusinessTripIds.run(
      {
        businessTripIds,
      },
      this.db,
    );
    return businessTripIds.map(id =>
      businessTrips.filter(record => record.business_trip_id === id),
    );
  }

  public getBusinessTripsAttendeesByBusinessTripIdLoader = new DataLoader(
    (ids: readonly string[]) => this.batchBusinessTripsAttendeesByBusinessTripIds(ids),
  );

  public async getBusinessTripsByAttendeeId(attendeeId: string) {
    return getBusinessTripsByAttendeeId.run({ attendeeId }, this.db);
  }

  public async getLastFlightByDateAndAttendeeId(params: {
    attendeeBusinessId: string;
    date: DateOrString;
  }) {
    return getLastFlightByDateAndAttendeeId.run(params, this.db);
  }

  public async addBusinessTripAttendees(params: IAddBusinessTripAttendeesParams) {
    if (params.businessTripId) {
      this.getBusinessTripsAttendeesByBusinessTripIdLoader.clear(params.businessTripId);
    }
    const { ownerId } = await this.adminContextProvider.getVerifiedAdminContext();
    return addBusinessTripAttendees.run(reassureOwnerIdExists(params, ownerId), this.db);
  }

  public async updateBusinessTripAttendee(params: IUpdateBusinessTripAttendeeParams) {
    if (params.businessTripId) {
      this.getBusinessTripsAttendeesByBusinessTripIdLoader.clear(params.businessTripId);
    }
    return updateBusinessTripAttendee.run(params, this.db);
  }

  public async removeBusinessTripAttendees(params: IRemoveBusinessTripAttendeesParams) {
    if (params.businessTripId) {
      this.getBusinessTripsAttendeesByBusinessTripIdLoader.clear(params.businessTripId);
    }
    return removeBusinessTripAttendees.run(params, this.db);
  }

  public clearCache() {
    this.getBusinessTripsAttendeesByChargeIdLoader.clearAll();
    this.getBusinessTripsAttendeesByBusinessTripIdLoader.clearAll();
  }
}