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 { PaymentSettlementEntity } from '../../../entities/payment-settlement.entity';

/**
 * Analytical aggregations over `payment_settlements`.
 *
 * These are GROUP BY queries — NOT separate tables. Each method
 * returns the same row shape as you'd see in a star-schema "by"
 * dashboard (one row per dimension value, with pre-aggregated metrics).
 *
 *   by-supplier  → accounts payable view ("who do I owe what")
 *   by-agent     → commission base view ("what did each agent sell")
 *   by-service   → product P&L view ("which lines make us money")
 *
 * Tenant scoping is added explicitly on every raw SQL — CLS doesn't
 * auto-scope raw queries. Indexes that back these:
 *   (tenant_id, supplier_id) — by-supplier
 *   (tenant_id, sales_agent_id) — by-agent
 *   (tenant_id, source_item_type, source_item_id) — by-service
 */
@Injectable()
export class SettlementAnalyticsRepository extends TenantAwareRepository<PaymentSettlementEntity> {
  constructor(
    @InjectRepository(PaymentSettlementEntity)
    repo: Repository<PaymentSettlementEntity>,
    cls: ClsService,
  ) {
    super(repo, cls);
  }

  /**
   * Aggregate supplier payables. One row per supplier the tenant owes
   * money on. Filters: search (supplier name ILIKE), date window on
   * settlement created_at.
   *
   * Metrics:
   *   bookings_count           — distinct bookings this supplier is owed on
   *   total_owed               — Σ amount on party=SUPPLIER, type=REFUND
   *   total_paid               — Σ amount_settled on same
   *   outstanding              — total_owed − total_paid
   *   oldest_unsettled_booking — booking_number + created_at of the
   *                              oldest non-SETTLED row (drives the
   *                              "what's overdue" workflow)
   */
  async bySupplier(opts: {
    search?: string;
    date_from?: string;
    date_to?: string;
    page?: number;
    limit?: number;
  }): Promise<{
    items: Array<{
      supplier_id: string;
      supplier_name: string;
      bookings_count: number;
      total_owed: number;
      total_paid: number;
      outstanding: number;
      oldest_unsettled_booking: { booking_number: string; created_at: string } | null;
    }>;
    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[] = [
      `s.tenant_id = $1`,
      `s.is_deleted = false`,
      `s.party_type = 'SUPPLIER'`,
      `s.supplier_id IS NOT NULL`,
    ];
    const params: unknown[] = [tenantId];
    if (opts.search) {
      params.push(`%${opts.search}%`);
      filters.push(`sup.name ILIKE $${params.length}`);
    }
    // Date window applies to booking travel start_date (the trip's
    // first day) — stakeholder direction 2026-06-30. JOIN bookings →
    // queries so the filter has a column to reference.
    if (opts.date_from) {
      params.push(opts.date_from);
      filters.push(`q.start_date >= $${params.length}`);
    }
    if (opts.date_to) {
      params.push(opts.date_to);
      filters.push(`q.start_date <= $${params.length}`);
    }
    const where = filters.join(' AND ');

    // Outer query — count distinct suppliers for pagination meta.
    const countRows = (await this.manager.query(
      `SELECT COUNT(DISTINCT s.supplier_id)::int AS c
         FROM payment_settlements s
         JOIN suppliers sup ON sup.id = s.supplier_id
         JOIN bookings b   ON b.id = s.booking_id  AND b.is_deleted = false
         JOIN queries  q   ON q.id = b.query_id    AND q.is_deleted = false
        WHERE ${where}`,
      params,
    )) as Array<{ c: number }>;
    const total = countRows[0]?.c ?? 0;

    // Page data — one row per supplier with rollups + oldest_unsettled.
    const dataParams = [...params, limit, offset];
    const rows = (await this.manager.query(
      `
      SELECT
        s.supplier_id                                                              AS supplier_id,
        MAX(sup.name)                                                              AS supplier_name,
        COUNT(DISTINCT s.booking_id)                                               AS bookings_count,
        COALESCE(SUM(CASE WHEN s.type = 'REFUND' THEN s.amount ELSE 0 END), 0)     AS total_owed,
        COALESCE(SUM(CASE WHEN s.type = 'REFUND' THEN s.amount_settled ELSE 0 END), 0) AS total_paid,
        (
          SELECT row_to_json(o)
            FROM (
              SELECT b.booking_number, s2.created_at
                FROM payment_settlements s2
                JOIN bookings b ON b.id = s2.booking_id AND b.is_deleted = false
               WHERE s2.tenant_id = s.tenant_id
                 AND s2.supplier_id = s.supplier_id
                 AND s2.party_type = 'SUPPLIER'
                 AND s2.is_deleted = false
                 AND s2.status <> 'SETTLED'
               ORDER BY s2.created_at ASC
               LIMIT 1
            ) o
        ) AS oldest_unsettled_booking
        FROM payment_settlements s
        JOIN suppliers sup ON sup.id = s.supplier_id
        JOIN bookings b ON b.id = s.booking_id  AND b.is_deleted = false
        JOIN queries  q ON q.id = b.query_id    AND q.is_deleted = false
       WHERE ${where}
       GROUP BY s.tenant_id, s.supplier_id
       ORDER BY (
         COALESCE(SUM(CASE WHEN s.type = 'REFUND' THEN s.amount ELSE 0 END), 0)
         - COALESCE(SUM(CASE WHEN s.type = 'REFUND' THEN s.amount_settled ELSE 0 END), 0)
       ) DESC
       LIMIT $${dataParams.length - 1} OFFSET $${dataParams.length}
      `,
      dataParams,
    )) as Array<{
      supplier_id: string;
      supplier_name: string;
      bookings_count: string;
      total_owed: string;
      total_paid: string;
      oldest_unsettled_booking: { booking_number: string; created_at: string } | null;
    }>;

    const items = rows.map((r) => {
      const owed = Number(r.total_owed);
      const paid = Number(r.total_paid);
      return {
        supplier_id: r.supplier_id,
        supplier_name: r.supplier_name,
        bookings_count: Number(r.bookings_count),
        total_owed: +owed.toFixed(2),
        total_paid: +paid.toFixed(2),
        outstanding: +(owed - paid).toFixed(2),
        oldest_unsettled_booking: r.oldest_unsettled_booking ?? null,
      };
    });

    return {
      items,
      meta: { total, page, limit, totalPages: Math.ceil(total / limit) },
    };
  }

  /**
   * Aggregate by sales agent (commission base view). Filters: search
   * (agent name), date window on settlement created_at.
   *
   * Metrics:
   *   bookings_count       — distinct bookings this agent owns
   *   total_billed         — Σ amount on CUSTOMER COLLECTION rows
   *   total_collected      — Σ amount_settled on those
   *   total_supplier_owed  — Σ amount on SUPPLIER REFUND rows
   *   total_supplier_paid  — Σ amount_settled on those
   *   realised_margin      — total_collected − total_supplier_paid
   *                          (cash-basis margin; what's actually in
   *                          the bank after supplier payouts)
   */
  async byAgent(opts: {
    search?: string;
    date_from?: string;
    date_to?: string;
    page?: number;
    limit?: number;
  }): Promise<{
    items: Array<{
      sales_agent_id: string;
      agent_name: string;
      bookings_count: number;
      total_billed: number;
      total_collected: number;
      total_supplier_owed: number;
      total_supplier_paid: number;
      realised_margin: number;
    }>;
    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[] = [
      `s.tenant_id = $1`,
      `s.is_deleted = false`,
      `s.sales_agent_id IS NOT NULL`,
    ];
    const params: unknown[] = [tenantId];
    if (opts.search) {
      params.push(`%${opts.search}%`);
      filters.push(`u.name ILIKE $${params.length}`);
    }
    // Booking start_date window (stakeholder 2026-06-30) — JOIN
    // bookings + queries so the filter has a column to reference.
    if (opts.date_from) {
      params.push(opts.date_from);
      filters.push(`q.start_date >= $${params.length}`);
    }
    if (opts.date_to) {
      params.push(opts.date_to);
      filters.push(`q.start_date <= $${params.length}`);
    }
    const where = filters.join(' AND ');

    const countRows = (await this.manager.query(
      `SELECT COUNT(DISTINCT s.sales_agent_id)::int AS c
         FROM payment_settlements s
         JOIN users u    ON u.id = s.sales_agent_id
         JOIN bookings b ON b.id = s.booking_id  AND b.is_deleted = false
         JOIN queries  q ON q.id = b.query_id    AND q.is_deleted = false
        WHERE ${where}`,
      params,
    )) as Array<{ c: number }>;
    const total = countRows[0]?.c ?? 0;

    const dataParams = [...params, limit, offset];
    const rows = (await this.manager.query(
      `
      SELECT
        s.sales_agent_id                                                                                 AS sales_agent_id,
        MAX(u.name)                                                                                      AS agent_name,
        COUNT(DISTINCT s.booking_id)                                                                     AS bookings_count,
        COALESCE(SUM(CASE WHEN s.party_type = 'CUSTOMER' AND s.type = 'COLLECTION' THEN s.amount         ELSE 0 END), 0) AS total_billed,
        COALESCE(SUM(CASE WHEN s.party_type = 'CUSTOMER' AND s.type = 'COLLECTION' THEN s.amount_settled ELSE 0 END), 0) AS total_collected,
        COALESCE(SUM(CASE WHEN s.party_type = 'SUPPLIER' AND s.type = 'REFUND'     THEN s.amount         ELSE 0 END), 0) AS total_supplier_owed,
        COALESCE(SUM(CASE WHEN s.party_type = 'SUPPLIER' AND s.type = 'REFUND'     THEN s.amount_settled ELSE 0 END), 0) AS total_supplier_paid
        FROM payment_settlements s
        JOIN users u    ON u.id = s.sales_agent_id
        JOIN bookings b ON b.id = s.booking_id  AND b.is_deleted = false
        JOIN queries  q ON q.id = b.query_id    AND q.is_deleted = false
       WHERE ${where}
       GROUP BY s.tenant_id, s.sales_agent_id
       ORDER BY total_collected DESC
       LIMIT $${dataParams.length - 1} OFFSET $${dataParams.length}
      `,
      dataParams,
    )) as Array<{
      sales_agent_id: string;
      agent_name: string;
      bookings_count: string;
      total_billed: string;
      total_collected: string;
      total_supplier_owed: string;
      total_supplier_paid: string;
    }>;

    const items = rows.map((r) => {
      const collected = Number(r.total_collected);
      const supplierPaid = Number(r.total_supplier_paid);
      return {
        sales_agent_id: r.sales_agent_id,
        agent_name: r.agent_name,
        bookings_count: Number(r.bookings_count),
        total_billed: +Number(r.total_billed).toFixed(2),
        total_collected: +collected.toFixed(2),
        total_supplier_owed: +Number(r.total_supplier_owed).toFixed(2),
        total_supplier_paid: +supplierPaid.toFixed(2),
        realised_margin: +(collected - supplierPaid).toFixed(2),
      };
    });

    return {
      items,
      meta: { total, page, limit, totalPages: Math.ceil(total / limit) },
    };
  }

  /**
   * Aggregate by trip source (= external "agent" the booking
   * originated from — TravClan, MakeMyTrip, walk-in office, …).
   *
   * Walks payment_settlements → bookings → queries → trip_sources so
   * one row per source carries the same shape as byAgent (which
   * groups by sales rep instead). Renders the "By Agent" tab on the
   * All Settlements page per §12f.
   */
  async bySource(opts: {
    search?: string;
    date_from?: string;
    date_to?: string;
    page?: number;
    limit?: number;
  }): Promise<{
    items: Array<{
      source_id: string;
      source_name: string;
      bookings_count: number;
      total_billed: number;
      total_collected: number;
      total_supplier_owed: number;
      total_supplier_paid: number;
      realised_margin: number;
    }>;
    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[] = [
      `s.tenant_id = $1`,
      `s.is_deleted = false`,
      `q.source_id IS NOT NULL`,
    ];
    const params: unknown[] = [tenantId];
    if (opts.search) {
      params.push(`%${opts.search}%`);
      filters.push(`ts.name ILIKE $${params.length}`);
    }
    // Booking start_date window (stakeholder 2026-06-30). q is already
    // joined below so the filter has a column to reference.
    if (opts.date_from) {
      params.push(opts.date_from);
      filters.push(`q.start_date >= $${params.length}`);
    }
    if (opts.date_to) {
      params.push(opts.date_to);
      filters.push(`q.start_date <= $${params.length}`);
    }
    const where = filters.join(' AND ');

    const countRows = (await this.manager.query(
      `SELECT COUNT(DISTINCT q.source_id)::int AS c
         FROM payment_settlements s
         JOIN bookings b ON b.id = s.booking_id AND b.is_deleted = false
         JOIN queries  q ON q.id = b.query_id   AND q.is_deleted = false
         JOIN trip_sources ts ON ts.id = q.source_id
        WHERE ${where}`,
      params,
    )) as Array<{ c: number }>;
    const total = countRows[0]?.c ?? 0;

    const dataParams = [...params, limit, offset];
    const rows = (await this.manager.query(
      `
      SELECT
        q.source_id                                                                                       AS source_id,
        MAX(ts.name)                                                                                      AS source_name,
        COUNT(DISTINCT s.booking_id)                                                                      AS bookings_count,
        COALESCE(SUM(CASE WHEN s.party_type = 'CUSTOMER' AND s.type = 'COLLECTION' THEN s.amount         ELSE 0 END), 0) AS total_billed,
        COALESCE(SUM(CASE WHEN s.party_type = 'CUSTOMER' AND s.type = 'COLLECTION' THEN s.amount_settled ELSE 0 END), 0) AS total_collected,
        COALESCE(SUM(CASE WHEN s.party_type = 'SUPPLIER' AND s.type = 'REFUND'     THEN s.amount         ELSE 0 END), 0) AS total_supplier_owed,
        COALESCE(SUM(CASE WHEN s.party_type = 'SUPPLIER' AND s.type = 'REFUND'     THEN s.amount_settled ELSE 0 END), 0) AS total_supplier_paid
        FROM payment_settlements s
        JOIN bookings b ON b.id = s.booking_id AND b.is_deleted = false
        JOIN queries  q ON q.id = b.query_id   AND q.is_deleted = false
        JOIN trip_sources ts ON ts.id = q.source_id
       WHERE ${where}
       GROUP BY q.source_id
       ORDER BY total_collected DESC
       LIMIT $${dataParams.length - 1} OFFSET $${dataParams.length}
      `,
      dataParams,
    )) as Array<{
      source_id: string;
      source_name: string;
      bookings_count: string;
      total_billed: string;
      total_collected: string;
      total_supplier_owed: string;
      total_supplier_paid: string;
    }>;

    const items = rows.map((r) => {
      const collected = Number(r.total_collected);
      const supplierPaid = Number(r.total_supplier_paid);
      return {
        source_id: r.source_id,
        source_name: r.source_name,
        bookings_count: Number(r.bookings_count),
        total_billed: +Number(r.total_billed).toFixed(2),
        total_collected: +collected.toFixed(2),
        total_supplier_owed: +Number(r.total_supplier_owed).toFixed(2),
        total_supplier_paid: +supplierPaid.toFixed(2),
        realised_margin: +(collected - supplierPaid).toFixed(2),
      };
    });

    return {
      items,
      meta: { total, page, limit, totalPages: Math.ceil(total / limit) },
    };
  }

  /**
   * Aggregate by service line (product P&L). Each row corresponds to a
   * specific transport service / activity / hotel / special master.
   * Filters: destination_id (from the booking → query), date window.
   *
   * Metrics:
   *   bookings_sold   — DISTINCT bookings the service appeared on
   *   total_selling   — Σ subtotal on the originating line items
   *   total_cost      — Σ amount on party=SUPPLIER, type=REFUND rows
   *                     (what we owe suppliers for this service)
   *   margin          — total_selling − total_cost
   *   margin_pct      — margin / total_selling (null if no selling)
   *
   * Joins the line-item table for the selling number (we don't store
   * per-line selling on the settlement) and the service master for the
   * human-readable name.
   */
  async byService(opts: {
    destination_id?: string;
    date_from?: string;
    date_to?: string;
    page?: number;
    limit?: number;
  }): Promise<{
    items: Array<{
      source_item_type: string;
      service_id: string;
      service_name: string;
      bookings_sold: number;
      total_selling: number;
      total_cost: number;
      outstanding: number;
      margin: number;
      margin_pct: number | null;
    }>;
    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;

    // Four branches united — transport / activity / hotel / special.
    // Specials have no service master, so the "service" is the free-
    // text service_name itself; rows with the same service_name + same
    // booking-line-id resolve to a unique product line. UAT 2026-06-30:
    // ops asked for SPECIAL items to show on the product P&L so they
    // can see margin on ad-hoc add-ons (visa drops, photographer,
    // custom extras).
    //
    // Each branch:
    //   - joins to its line-item table for the service master FK
    //   - joins to the service master for the display name
    //   - LEFT joins to payment_settlements (party=SUPPLIER) for cost
    //   - groups by the service master id
    //
    // Filters (destination + date window) apply to the booking the
    // line item belongs to.
    const params: unknown[] = [tenantId];
    let destClause = '';
    if (opts.destination_id) {
      params.push(opts.destination_id);
      destClause = `AND q.destination_id = $${params.length}`;
    }
    let dateClause = '';
    if (opts.date_from) {
      params.push(opts.date_from);
      dateClause += ` AND q.start_date >= $${params.length}`;
    }
    if (opts.date_to) {
      params.push(opts.date_to);
      dateClause += ` AND q.start_date <= $${params.length}`;
    }

    const baseSql = `
      WITH transport_lines AS (
        SELECT
          'booking_transport_item'::text AS source_item_type,
          ts.id                          AS service_id,
          ts.from_city || ' → ' || ts.to_city AS service_name,
          ti.id                          AS line_id,
          ti.booking_id                  AS booking_id,
          ti.subtotal                    AS subtotal
        FROM booking_transport_items ti
        JOIN transport_services ts ON ts.id = ti.transport_service_id
        JOIN bookings b ON b.id = ti.booking_id
        JOIN queries q ON q.id = b.query_id
        WHERE ti.tenant_id = $1
          AND ti.is_deleted = false
          AND b.is_deleted = false
          ${destClause}
          ${dateClause}
      ),
      activity_lines AS (
        SELECT
          'booking_activity_item'::text AS source_item_type,
          a.id                          AS service_id,
          a.name                        AS service_name,
          ai.id                         AS line_id,
          ai.booking_id                 AS booking_id,
          ai.subtotal                   AS subtotal
        FROM booking_activity_items ai
        JOIN activities a ON a.id = ai.activity_id
        JOIN bookings b ON b.id = ai.booking_id
        JOIN queries q ON q.id = b.query_id
        WHERE ai.tenant_id = $1
          AND ai.is_deleted = false
          AND b.is_deleted = false
          ${destClause}
          ${dateClause}
      ),
      hotel_lines AS (
        SELECT
          'booking_hotel_item'::text AS source_item_type,
          h.id                       AS service_id,
          h.name                     AS service_name,
          hi.id                      AS line_id,
          hi.booking_id              AS booking_id,
          hi.subtotal                AS subtotal
        FROM booking_hotel_items hi
        JOIN hotels h ON h.id = hi.hotel_id
        JOIN bookings b ON b.id = hi.booking_id
        JOIN queries q ON q.id = b.query_id
        WHERE hi.tenant_id = $1
          AND hi.is_deleted = false
          AND b.is_deleted = false
          ${destClause}
          ${dateClause}
      ),
      special_lines AS (
        -- Specials have no service master; group by service_name to
        -- show one row per ad-hoc product (e.g. "Visa drop-off",
        -- "Photographer"). The line id stands in for service_id so
        -- the row key in the FE stays stable.
        SELECT
          'booking_special_item'::text AS source_item_type,
          si.id                        AS service_id,
          COALESCE(NULLIF(TRIM(si.service_name), ''), 'Special') AS service_name,
          si.id                        AS line_id,
          si.booking_id                AS booking_id,
          si.total_price               AS subtotal
        FROM booking_special_items si
        JOIN bookings b ON b.id = si.booking_id
        JOIN queries q ON q.id = b.query_id
        WHERE si.tenant_id = $1
          AND si.is_deleted = false
          AND b.is_deleted = false
          ${destClause}
          ${dateClause}
      ),
      all_lines AS (
        SELECT * FROM transport_lines
        UNION ALL SELECT * FROM activity_lines
        UNION ALL SELECT * FROM hotel_lines
        UNION ALL SELECT * FROM special_lines
      ),
      agg AS (
        SELECT
          l.source_item_type,
          l.service_id,
          MAX(l.service_name)                                AS service_name,
          COUNT(DISTINCT l.booking_id)                       AS bookings_sold,
          COALESCE(SUM(l.subtotal), 0)                       AS total_selling,
          COALESCE((
            SELECT SUM(s.amount)
              FROM payment_settlements s
             WHERE s.tenant_id = $1
               AND s.is_deleted = false
               AND s.party_type = 'SUPPLIER'
               AND s.type = 'REFUND'
               AND s.source_item_type = l.source_item_type
               AND s.source_item_id IN (
                 SELECT line_id FROM all_lines l2
                  WHERE l2.source_item_type = l.source_item_type
                    AND l2.service_id = l.service_id
               )
          ), 0)                                              AS total_cost,
          COALESCE((
            SELECT SUM(s.amount - s.amount_settled)
              FROM payment_settlements s
             WHERE s.tenant_id = $1
               AND s.is_deleted = false
               AND s.party_type = 'SUPPLIER'
               AND s.type = 'REFUND'
               AND s.source_item_type = l.source_item_type
               AND s.source_item_id IN (
                 SELECT line_id FROM all_lines l2
                  WHERE l2.source_item_type = l.source_item_type
                    AND l2.service_id = l.service_id
               )
          ), 0)                                              AS outstanding
        FROM all_lines l
        GROUP BY l.source_item_type, l.service_id
      )
      SELECT * FROM agg
    `;

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

    const dataParams = [...params, limit, offset];
    const rows = (await this.manager.query(
      `${baseSql}
       ORDER BY total_selling DESC
       LIMIT $${dataParams.length - 1} OFFSET $${dataParams.length}`,
      dataParams,
    )) as Array<{
      source_item_type: string;
      service_id: string;
      service_name: string;
      bookings_sold: string;
      total_selling: string;
      total_cost: string;
      outstanding: string;
    }>;

    const items = rows.map((r) => {
      const selling = Number(r.total_selling);
      const cost = Number(r.total_cost);
      const margin = selling - cost;
      return {
        source_item_type: r.source_item_type,
        service_id: r.service_id,
        service_name: r.service_name,
        bookings_sold: Number(r.bookings_sold),
        total_selling: +selling.toFixed(2),
        total_cost: +cost.toFixed(2),
        outstanding: +Number(r.outstanding).toFixed(2),
        margin: +margin.toFixed(2),
        margin_pct: selling > 0.005 ? +((margin / selling) * 100).toFixed(2) : null,
      };
    });

    return {
      items,
      meta: { total, page, limit, totalPages: Math.ceil(total / limit) },
    };
  }
}