import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { ClsService } from 'nestjs-cls';
import { TenantAwareRepository } from '../../../common/repositories/tenant-aware.repository';
import { BookingCashCollectionEntity } from '../../../entities/booking-cash-collection.entity';

type CollectionKind = 'CASH' | 'EXPENSE';

type SupplierRow = {
  collection_kind: CollectionKind;
  collected_by_type: 'OWN' | 'SUPPLIER';
  collector_name: string | null;
  supplier_id: string | null;
  supplier_name: string;
  currency_code: string;
  collections_count: number;
  pending_count: number;
  collected_count: number;
  total_collection: number;
  total_collected: number;
  total_pending: number;
};

type DriverRow = {
  driver_id: string | null;
  driver_name: string;
  driver_phone: string | null;
  service_type: string | null;
  currency_code: string;
  collections_count: number;
  pending_count: number;
  collected_count: number;
  total_collection: number;
  total_collected: number;
  total_pending: number;
  notes: string | null;
  routes: string | null;
};

/**
 * Analytical aggregations over `booking_cash_collections`.
 *
 * Each live row can contribute two lines:
 *   expense_*  — Movement Dashboard Expense (off-invoice)
 *   cash_*     — Collect Payment → Cash collection for MD
 *
 * Both Own and Supplier / Driver collectors are included. Expense and
 * cash stay on separate collector rows so the list can show
 * `KT ( Expense )` vs `KT`.
 *
 * Amount logic matches the MD chips:
 *   PENDING, no actual → pending = expected, collected = 0
 *   PENDING, after add  → collected = actual, pending = expected − actual
 *   COLLECTED           → collected = actual (fallback expected), pending = 0
 *
 * Date window filters on Date of Collection (`collection_date`).
 *
 * Soft-deleted bookings are excluded via an inner join. Booking delete
 * deliberately keeps `booking_cash_collections` for audit; this report
 * must not surface them (same pattern as settlement analytics).
 */
@Injectable()
export class CollectionAnalyticsRepository extends TenantAwareRepository<BookingCashCollectionEntity> {
  constructor(
    @InjectRepository(BookingCashCollectionEntity)
    repo: Repository<BookingCashCollectionEntity>,
    cls: ClsService,
  ) {
    super(repo, cls);
  }

  private readonly collectionLinesCte = `
    WITH collection_lines AS (
      SELECT
        m.id                                                           AS collection_id,
        'CASH'::text                                                   AS collection_kind,
        m.status                                                       AS status,
        m.amount::numeric                                              AS expected_amount,
        NULL::text                                                     AS service_type,
        (m.created_at AT TIME ZONE 'UTC')::date                        AS service_date,
        m.collected_by_type                                            AS collected_by_type,
        COALESCE(NULLIF(TRIM(m.collector_name), ''), '')               AS collector_name,
        m.supplier_id                                                  AS supplier_id,
        CASE
          WHEN m.collected_by_type = 'OWN'
            THEN COALESCE(NULLIF(TRIM(m.collector_name), ''), 'Own')
          ELSE COALESCE(s.name, 'Unassigned')
        END                                                            AS supplier_name,
        lower(trim(
          CASE
            WHEN m.collected_by_type = 'OWN'
              THEN COALESCE(NULLIF(TRIM(m.collector_name), ''), 'Own')
            ELSE COALESCE(s.name, '')
          END
        ))                                                             AS supplier_name_norm,
        m.driver_id                                                    AS driver_id,
        CASE
          WHEN m.collected_by_type = 'OWN'
            THEN COALESCE(NULLIF(TRIM(m.collector_name), ''), 'Own')
          ELSE COALESCE(d.name, 'Unassigned')
        END                                                            AS driver_name,
        d.phone                                                        AS driver_phone,
        COALESCE(cur.code, '')                                         AS currency_code,
        CASE WHEN m.status = 'COLLECTED' THEN m.amount::numeric ELSE NULL END AS actual_amount,
        NULLIF(TRIM(m.notes), '')                                      AS requirement_notes,
        NULL::text                                                     AS route_label,
        CASE WHEN m.status = 'COLLECTED' THEN m.amount::numeric ELSE 0 END AS collected_amount,
        CASE WHEN m.status = 'PENDING' THEN m.amount::numeric ELSE 0 END AS pending_amount
      FROM md_cash_collections m
      INNER JOIN bookings b
              ON b.id = m.booking_id
             AND b.tenant_id = m.tenant_id
             AND b.is_deleted = false
      LEFT JOIN suppliers s ON s.id = m.supplier_id
      LEFT JOIN drivers d ON d.id = m.driver_id
      LEFT JOIN currencies cur ON cur.id = m.currency_id
      WHERE m.tenant_id = $1
        AND m.is_deleted = false
        AND m.amount::numeric > 0

      UNION ALL

      SELECT
        pc.id                                                          AS collection_id,
        'EXPENSE'::text                                                AS collection_kind,
        pc.expense_status                                              AS status,
        pc.expense_expected_amount::numeric                            AS expected_amount,
        CASE
          WHEN pc.transport_item_id IS NOT NULL THEN 'TRANSPORT'
          WHEN pc.activity_item_id IS NOT NULL THEN 'ACTIVITY'
          WHEN pc.special_item_id IS NOT NULL THEN 'SPECIAL'
          ELSE pc.expense_source_kind
        END                                                            AS service_type,
        COALESCE(ti.date, ai.date, si.date, pc.collection_date)        AS service_date,
        pc.expense_collected_by_type                                   AS collected_by_type,
        COALESCE(NULLIF(TRIM(pc.expense_collector_name), ''), '')      AS collector_name,
        pc.expense_supplier_id                                         AS supplier_id,
        CASE
          WHEN pc.expense_collected_by_type = 'OWN'
            THEN COALESCE(NULLIF(TRIM(pc.expense_collector_name), ''), 'Own')
          ELSE COALESCE(s.name, 'Unassigned')
        END                                                            AS supplier_name,
        lower(trim(
          CASE
            WHEN pc.expense_collected_by_type = 'OWN'
              THEN COALESCE(NULLIF(TRIM(pc.expense_collector_name), ''), 'Own')
            ELSE COALESCE(s.name, '')
          END
        ))                                                             AS supplier_name_norm,
        pc.expense_driver_id                                           AS driver_id,
        CASE
          WHEN pc.expense_collected_by_type = 'OWN'
            THEN COALESCE(NULLIF(TRIM(pc.expense_collector_name), ''), 'Own')
          ELSE COALESCE(d.name, 'Unassigned')
        END                                                            AS driver_name,
        d.phone                                                        AS driver_phone,
        COALESCE(cur.code, '')                                         AS currency_code,
        pc.expense_actual_amount::numeric                              AS actual_amount,
        NULLIF(TRIM(pc.expense_notes), '')                             AS requirement_notes,
        CASE
          WHEN ti.id IS NOT NULL
            THEN CONCAT(COALESCE(ts.from_city, '?'), ' → ', COALESCE(ts.to_city, '?'))
          WHEN ai.id IS NOT NULL
            THEN COALESCE(act.name, 'Activity')
          WHEN si.id IS NOT NULL
            THEN COALESCE(si.service_name, 'Special')
          ELSE NULL
        END                                                            AS route_label,
        CASE
          WHEN pc.expense_status = 'COLLECTED'
            THEN COALESCE(pc.expense_actual_amount::numeric, pc.expense_expected_amount::numeric)
          ELSE COALESCE(pc.expense_actual_amount::numeric, 0)
        END                                                            AS collected_amount,
        CASE
          WHEN pc.expense_status = 'PENDING'
            THEN GREATEST(
              pc.expense_expected_amount::numeric
                - COALESCE(pc.expense_actual_amount::numeric, 0),
              0
            )
          ELSE 0
        END                                                            AS pending_amount
      FROM booking_cash_collections pc
      INNER JOIN bookings b
              ON b.id = pc.booking_id
             AND b.tenant_id = pc.tenant_id
             AND b.is_deleted = false
      LEFT JOIN suppliers s ON s.id = pc.expense_supplier_id
      LEFT JOIN drivers d ON d.id = pc.expense_driver_id
      LEFT JOIN currencies cur ON cur.id = pc.currency_id
      LEFT JOIN booking_transport_items ti
             ON ti.id = pc.transport_item_id AND ti.is_deleted = false
      LEFT JOIN transport_services ts
             ON ts.id = ti.transport_service_id AND ts.is_deleted = false
      LEFT JOIN booking_activity_items ai
             ON ai.id = pc.activity_item_id AND ai.is_deleted = false
      LEFT JOIN activities act
             ON act.id = ai.activity_id AND act.is_deleted = false
      LEFT JOIN booking_special_items si
             ON si.id = pc.special_item_id AND si.is_deleted = false
      WHERE pc.tenant_id = $1
        AND pc.is_deleted = false
        AND pc.expense_status IS NOT NULL
        AND pc.expense_status <> 'CANCELLED'
        AND pc.expense_expected_amount::numeric > 0
        AND pc.expense_collected_by_type IN ('OWN', 'SUPPLIER')
        AND (
          (pc.expense_collected_by_type = 'SUPPLIER' AND pc.expense_supplier_id IS NOT NULL)
          OR (
            pc.expense_collected_by_type = 'OWN'
            AND NULLIF(TRIM(pc.expense_collector_name), '') IS NOT NULL
          )
        )
    )`;

  async bySupplier(opts: {
    collection_kind?: CollectionKind;
    search?: string;
    date_from?: string;
    date_to?: string;
    page?: number;
    limit?: number;
  }): Promise<{
    items: SupplierRow[];
    meta: { total: number; page: number; limit: number; totalPages: number };
  }> {
    const tenantId = this.currentTenantId();
    const page = Math.max(1, Number(opts.page) || 1);
    const limit = Math.min(100, Math.max(1, Number(opts.limit) || 20));
    const offset = (page - 1) * limit;

    const filters: string[] = [];
    const params: unknown[] = [tenantId];
    if (opts.collection_kind === 'CASH' || opts.collection_kind === 'EXPENSE') {
      params.push(opts.collection_kind);
      filters.push(`collection_kind = $${params.length}`);
    }
    if (opts.search) {
      params.push(`%${opts.search}%`);
      filters.push(
        `(supplier_name ILIKE $${params.length} OR (collection_kind = 'EXPENSE' AND 'expense' ILIKE $${params.length}))`,
      );
    }
    if (opts.date_from) {
      params.push(opts.date_from);
      filters.push(`service_date >= $${params.length}`);
    }
    if (opts.date_to) {
      params.push(opts.date_to);
      filters.push(`service_date <= $${params.length}`);
    }
    const where =
      filters.length > 0 ? `WHERE ${filters.join(' AND ')}` : '';

    const countRows = (await this.manager.query(
      `${this.collectionLinesCte}
       SELECT COUNT(*)::int AS c
         FROM (
           SELECT DISTINCT
             collection_kind || '|' ||
             collected_by_type || '|' ||
             COALESCE(supplier_id::text, '') || '|' ||
             COALESCE(collector_name, '') || '|' ||
             currency_code AS grp
             FROM collection_lines
            ${where}
         ) sub`,
      params,
    )) as Array<{ c: number }>;
    const total = countRows[0]?.c ?? 0;

    const dataParams = [...params, limit, offset];
    const rows = (await this.manager.query(
      `${this.collectionLinesCte}
       SELECT
         collection_kind,
         collected_by_type,
         NULLIF(MAX(collector_name), '')                                   AS collector_name,
         supplier_id,
         MAX(supplier_name)                                                AS supplier_name,
         currency_code,
         COUNT(*)::int                                                     AS collections_count,
         COUNT(*) FILTER (WHERE status = 'PENDING')::int                   AS pending_count,
         COUNT(*) FILTER (WHERE status = 'COLLECTED')::int                 AS collected_count,
         COALESCE(SUM(collected_amount + pending_amount), 0)             AS total_collection,
         COALESCE(SUM(collected_amount), 0)                                AS total_collected,
         COALESCE(SUM(pending_amount), 0)                                  AS total_pending
       FROM collection_lines
       ${where}
       GROUP BY
         collection_kind,
         collected_by_type,
         supplier_id,
         collector_name,
         currency_code
       ORDER BY total_collection DESC, supplier_name ASC
       LIMIT $${params.length + 1} OFFSET $${params.length + 2}`,
      dataParams,
    )) as Array<{
      collection_kind: CollectionKind;
      collected_by_type: 'OWN' | 'SUPPLIER';
      collector_name: string | null;
      supplier_id: string | null;
      supplier_name: string;
      currency_code: string;
      collections_count: number;
      pending_count: number;
      collected_count: number;
      total_collection: string;
      total_collected: string;
      total_pending: string;
    }>;

    return {
      items: rows.map((r) => ({
        collection_kind: r.collection_kind === 'EXPENSE' ? 'EXPENSE' : 'CASH',
        collected_by_type: r.collected_by_type === 'OWN' ? 'OWN' : 'SUPPLIER',
        collector_name: r.collector_name,
        supplier_id: r.supplier_id,
        supplier_name: r.supplier_name,
        currency_code: r.currency_code || '',
        collections_count: r.collections_count,
        pending_count: r.pending_count,
        collected_count: r.collected_count,
        total_collection: Number(r.total_collection),
        total_collected: Number(r.total_collected),
        total_pending: Number(r.total_pending),
      })),
      meta: {
        total,
        page,
        limit,
        totalPages: Math.ceil(total / limit) || 1,
      },
    };
  }

  async supplierCollectionDrivers(opts: {
    collection_kind?: CollectionKind;
    collected_by_type?: 'OWN' | 'SUPPLIER';
    collector_name?: string;
    supplier_id?: string;
    supplier_name?: string;
    search?: string;
    date_from?: string;
    date_to?: string;
    page?: number;
    limit?: number;
  }): Promise<{
    items: DriverRow[];
    meta: { total: number; page: number; limit: number; totalPages: number };
  }> {
    const tenantId = this.currentTenantId();
    const page = Math.max(1, Number(opts.page) || 1);
    const limit = Math.min(100, Math.max(1, Number(opts.limit) || 20));
    const offset = (page - 1) * limit;

    const filters: string[] = [];
    const params: unknown[] = [tenantId];

    if (opts.collection_kind === 'CASH' || opts.collection_kind === 'EXPENSE') {
      params.push(opts.collection_kind);
      filters.push(`collection_kind = $${params.length}`);
    }

    if (opts.collected_by_type === 'OWN') {
      params.push('OWN');
      filters.push(`collected_by_type = $${params.length}`);
      const ownName = (opts.collector_name || opts.supplier_name || '').toLowerCase().trim();
      if (ownName) {
        params.push(ownName);
        filters.push(`supplier_name_norm = $${params.length}`);
      }
    } else if (opts.supplier_id) {
      params.push('SUPPLIER');
      filters.push(`collected_by_type = $${params.length}`);
      params.push(opts.supplier_id);
      filters.push(`supplier_id = $${params.length}::uuid`);
    } else if (opts.supplier_name) {
      params.push('SUPPLIER');
      filters.push(`collected_by_type = $${params.length}`);
      params.push(opts.supplier_name.toLowerCase().trim());
      filters.push(`supplier_name_norm = $${params.length}`);
    } else {
      return {
        items: [],
        meta: { total: 0, page, limit, totalPages: 1 },
      };
    }

    if (opts.search) {
      params.push(`%${opts.search}%`);
      filters.push(`driver_name ILIKE $${params.length}`);
    }
    if (opts.date_from) {
      params.push(opts.date_from);
      filters.push(`service_date >= $${params.length}`);
    }
    if (opts.date_to) {
      params.push(opts.date_to);
      filters.push(`service_date <= $${params.length}`);
    }
    const where = `WHERE ${filters.join(' AND ')}`;

    const countRows = (await this.manager.query(
      `${this.collectionLinesCte}
       SELECT COUNT(*)::int AS c
         FROM collection_lines
        ${where}`,
      params,
    )) as Array<{ c: number }>;
    const total = countRows[0]?.c ?? 0;

    const dataParams = [...params, limit, offset];
    const rows = (await this.manager.query(
      `${this.collectionLinesCte}
       SELECT
         driver_id,
         driver_name,
         driver_phone,
         service_type,
         currency_code,
         1::int                                                            AS collections_count,
         CASE WHEN status = 'PENDING' THEN 1 ELSE 0 END::int               AS pending_count,
         CASE WHEN status = 'COLLECTED' THEN 1 ELSE 0 END::int             AS collected_count,
         (collected_amount + pending_amount)                             AS total_collection,
         collected_amount                                                  AS total_collected,
         pending_amount                                                    AS total_pending,
         requirement_notes                                                 AS notes,
         route_label                                                       AS routes
       FROM collection_lines
       ${where}
       ORDER BY total_collection DESC, driver_name ASC, service_date ASC
       LIMIT $${params.length + 1} OFFSET $${params.length + 2}`,
      dataParams,
    )) as Array<{
      driver_id: string | null;
      driver_name: string;
      driver_phone: string | null;
      service_type: string | null;
      currency_code: string;
      collections_count: number;
      pending_count: number;
      collected_count: number;
      total_collection: string;
      total_collected: string;
      total_pending: string;
      notes: string | null;
      routes: string | null;
    }>;

    return {
      items: rows.map((r) => ({
        driver_id: r.driver_id,
        driver_name: r.driver_name,
        driver_phone: r.driver_phone,
        service_type: r.service_type,
        currency_code: r.currency_code || '',
        collections_count: r.collections_count,
        pending_count: r.pending_count,
        collected_count: r.collected_count,
        total_collection: Number(r.total_collection),
        total_collected: Number(r.total_collected),
        total_pending: Number(r.total_pending),
        notes: r.notes,
        routes: r.routes,
      })),
      meta: {
        total,
        page,
        limit,
        totalPages: Math.ceil(total / limit) || 1,
      },
    };
  }
}