import { query, queryOne, getClient } from '../../config/database.js';
import { NotFoundError, ValidationError } from '../../shared/errors/index.js';
import { logger } from '../../shared/utils/logger.js';

// ============================================================================
// TYPES
// ============================================================================

export interface BillingRate {
  id: string;
  tenant_id: string;
  company_id: string;
  project_id?: string;
  user_id?: string;
  rate_type: 'project' | 'user' | 'project_user';
  hourly_rate: number;
  currency_id: string;
  currency_code?: string;
  effective_from?: Date;
  effective_to?: Date;
  active: boolean;
  created_at: Date;
}

export interface CreateBillingRateDto {
  company_id: string;
  project_id?: string;
  user_id?: string;
  hourly_rate: number;
  currency_id: string;
  effective_from?: string;
  effective_to?: string;
}

export interface UpdateBillingRateDto {
  hourly_rate?: number;
  currency_id?: string;
  effective_from?: string | null;
  effective_to?: string | null;
  active?: boolean;
}

export interface UnbilledTimesheet {
  id: string;
  project_id: string;
  project_name: string;
  task_id?: string;
  task_name?: string;
  user_id: string;
  user_name: string;
  date: Date;
  hours: number;
  description?: string;
  hourly_rate: number;
  billable_amount: number;
  currency_id: string;
  currency_code?: string;
}

export interface TimesheetBillingSummary {
  project_id: string;
  project_name: string;
  partner_id?: string;
  partner_name?: string;
  total_hours: number;
  total_amount: number;
  currency_id: string;
  currency_code?: string;
  timesheet_count: number;
  date_range: {
    from: Date;
    to: Date;
  };
}

export interface InvoiceFromTimesheetsResult {
  invoice_id: string;
  invoice_number?: string;
  timesheets_billed: number;
  total_hours: number;
  total_amount: number;
}

export interface BillingFilters {
  project_id?: string;
  user_id?: string;
  partner_id?: string;
  date_from?: string;
  date_to?: string;
}

// ============================================================================
// SERVICE
// ============================================================================

class BillingService {
  // --------------------------------------------------------------------------
  // BILLING RATES MANAGEMENT
  // --------------------------------------------------------------------------

  /**
   * Get billing rate for a project/user combination
   * Priority: project_user > project > user > company default
   */
  async getBillingRate(
    tenantId: string,
    companyId: string,
    projectId?: string,
    userId?: string,
    date?: Date
  ): Promise<BillingRate | null> {
    const targetDate = date || new Date();

    // Try to find the most specific rate
    const rates = await query<BillingRate>(
      `SELECT br.*, c.code as currency_code
       FROM projects.billing_rates br
       LEFT JOIN core.currencies c ON br.currency_id = c.id
       WHERE br.tenant_id = $1
         AND br.company_id = $2
         AND br.active = true
         AND (br.effective_from IS NULL OR br.effective_from <= $3)
         AND (br.effective_to IS NULL OR br.effective_to >= $3)
         AND (
           (br.project_id = $4 AND br.user_id = $5) OR
           (br.project_id = $4 AND br.user_id IS NULL) OR
           (br.project_id IS NULL AND br.user_id = $5) OR
           (br.project_id IS NULL AND br.user_id IS NULL)
         )
       ORDER BY
         CASE
           WHEN br.project_id IS NOT NULL AND br.user_id IS NOT NULL THEN 1
           WHEN br.project_id IS NOT NULL THEN 2
           WHEN br.user_id IS NOT NULL THEN 3
           ELSE 4
         END
       LIMIT 1`,
      [tenantId, companyId, targetDate, projectId, userId]
    );

    return rates.length > 0 ? rates[0] : null;
  }

  /**
   * Create a new billing rate
   */
  async createBillingRate(
    dto: CreateBillingRateDto,
    tenantId: string,
    userId: string
  ): Promise<BillingRate> {
    if (dto.hourly_rate < 0) {
      throw new ValidationError('La tarifa por hora no puede ser negativa');
    }

    // Determine rate type
    let rateType: 'project' | 'user' | 'project_user' = 'project';
    if (dto.project_id && dto.user_id) {
      rateType = 'project_user';
    } else if (dto.user_id) {
      rateType = 'user';
    }

    const rate = await queryOne<BillingRate>(
      `INSERT INTO projects.billing_rates (
        tenant_id, company_id, project_id, user_id, rate_type,
        hourly_rate, currency_id, effective_from, effective_to, created_by
      )
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
       RETURNING *`,
      [
        tenantId, dto.company_id, dto.project_id, dto.user_id, rateType,
        dto.hourly_rate, dto.currency_id, dto.effective_from, dto.effective_to, userId
      ]
    );

    return rate!;
  }

  /**
   * Update a billing rate
   */
  async updateBillingRate(
    id: string,
    dto: UpdateBillingRateDto,
    tenantId: string,
    userId: string
  ): Promise<BillingRate> {
    const existing = await queryOne<BillingRate>(
      `SELECT * FROM projects.billing_rates WHERE id = $1 AND tenant_id = $2`,
      [id, tenantId]
    );

    if (!existing) {
      throw new NotFoundError('Tarifa de facturación no encontrada');
    }

    const updateFields: string[] = [];
    const values: any[] = [];
    let paramIndex = 1;

    if (dto.hourly_rate !== undefined) {
      if (dto.hourly_rate < 0) {
        throw new ValidationError('La tarifa por hora no puede ser negativa');
      }
      updateFields.push(`hourly_rate = $${paramIndex++}`);
      values.push(dto.hourly_rate);
    }
    if (dto.currency_id !== undefined) {
      updateFields.push(`currency_id = $${paramIndex++}`);
      values.push(dto.currency_id);
    }
    if (dto.effective_from !== undefined) {
      updateFields.push(`effective_from = $${paramIndex++}`);
      values.push(dto.effective_from);
    }
    if (dto.effective_to !== undefined) {
      updateFields.push(`effective_to = $${paramIndex++}`);
      values.push(dto.effective_to);
    }
    if (dto.active !== undefined) {
      updateFields.push(`active = $${paramIndex++}`);
      values.push(dto.active);
    }

    if (updateFields.length === 0) {
      return existing;
    }

    updateFields.push(`updated_by = $${paramIndex++}`);
    values.push(userId);
    updateFields.push(`updated_at = CURRENT_TIMESTAMP`);

    values.push(id, tenantId);

    await query(
      `UPDATE projects.billing_rates SET ${updateFields.join(', ')}
       WHERE id = $${paramIndex++} AND tenant_id = $${paramIndex}`,
      values
    );

    const updated = await queryOne<BillingRate>(
      `SELECT br.*, c.code as currency_code
       FROM projects.billing_rates br
       LEFT JOIN core.currencies c ON br.currency_id = c.id
       WHERE br.id = $1`,
      [id]
    );

    return updated!;
  }

  /**
   * Get all billing rates for a company
   */
  async getBillingRates(
    tenantId: string,
    companyId: string,
    projectId?: string
  ): Promise<BillingRate[]> {
    let whereClause = 'WHERE br.tenant_id = $1 AND br.company_id = $2';
    const params: any[] = [tenantId, companyId];

    if (projectId) {
      whereClause += ' AND (br.project_id = $3 OR br.project_id IS NULL)';
      params.push(projectId);
    }

    return query<BillingRate>(
      `SELECT br.*, c.code as currency_code,
              p.name as project_name, u.name as user_name
       FROM projects.billing_rates br
       LEFT JOIN core.currencies c ON br.currency_id = c.id
       LEFT JOIN projects.projects p ON br.project_id = p.id
       LEFT JOIN auth.users u ON br.user_id = u.id
       ${whereClause}
       ORDER BY br.project_id NULLS LAST, br.user_id NULLS LAST, br.effective_from DESC`,
      params
    );
  }

  // --------------------------------------------------------------------------
  // UNBILLED TIMESHEETS
  // --------------------------------------------------------------------------

  /**
   * Get unbilled approved timesheets with calculated billable amounts
   */
  async getUnbilledTimesheets(
    tenantId: string,
    companyId: string,
    filters: BillingFilters = {}
  ): Promise<{ data: UnbilledTimesheet[]; total: number }> {
    const { project_id, user_id, partner_id, date_from, date_to } = filters;

    let whereClause = `WHERE ts.tenant_id = $1
      AND ts.company_id = $2
      AND ts.status = 'approved'
      AND ts.billable = true
      AND ts.invoice_line_id IS NULL`;
    const params: any[] = [tenantId, companyId];
    let paramIndex = 3;

    if (project_id) {
      whereClause += ` AND ts.project_id = $${paramIndex++}`;
      params.push(project_id);
    }

    if (user_id) {
      whereClause += ` AND ts.user_id = $${paramIndex++}`;
      params.push(user_id);
    }

    if (partner_id) {
      whereClause += ` AND p.partner_id = $${paramIndex++}`;
      params.push(partner_id);
    }

    if (date_from) {
      whereClause += ` AND ts.date >= $${paramIndex++}`;
      params.push(date_from);
    }

    if (date_to) {
      whereClause += ` AND ts.date <= $${paramIndex++}`;
      params.push(date_to);
    }

    // Get count
    const countResult = await queryOne<{ count: string }>(
      `SELECT COUNT(*) as count
       FROM projects.timesheets ts
       JOIN projects.projects p ON ts.project_id = p.id
       ${whereClause}`,
      params
    );

    // Get timesheets with billing rates
    const timesheets = await query<UnbilledTimesheet>(
      `SELECT ts.id, ts.project_id, p.name as project_name,
              ts.task_id, t.name as task_name,
              ts.user_id, u.name as user_name,
              ts.date, ts.hours, ts.description,
              COALESCE(br.hourly_rate, 0) as hourly_rate,
              COALESCE(br.hourly_rate * ts.hours, 0) as billable_amount,
              COALESCE(br.currency_id, c.id) as currency_id,
              COALESCE(cur.code, 'MXN') as currency_code
       FROM projects.timesheets ts
       JOIN projects.projects p ON ts.project_id = p.id
       LEFT JOIN projects.tasks t ON ts.task_id = t.id
       JOIN auth.users u ON ts.user_id = u.id
       LEFT JOIN auth.companies c ON ts.company_id = c.id
       LEFT JOIN LATERAL (
         SELECT hourly_rate, currency_id
         FROM projects.billing_rates br2
         WHERE br2.tenant_id = ts.tenant_id
           AND br2.company_id = ts.company_id
           AND br2.active = true
           AND (br2.effective_from IS NULL OR br2.effective_from <= ts.date)
           AND (br2.effective_to IS NULL OR br2.effective_to >= ts.date)
           AND (
             (br2.project_id = ts.project_id AND br2.user_id = ts.user_id) OR
             (br2.project_id = ts.project_id AND br2.user_id IS NULL) OR
             (br2.project_id IS NULL AND br2.user_id = ts.user_id) OR
             (br2.project_id IS NULL AND br2.user_id IS NULL)
           )
         ORDER BY
           CASE
             WHEN br2.project_id IS NOT NULL AND br2.user_id IS NOT NULL THEN 1
             WHEN br2.project_id IS NOT NULL THEN 2
             WHEN br2.user_id IS NOT NULL THEN 3
             ELSE 4
           END
         LIMIT 1
       ) br ON true
       LEFT JOIN core.currencies cur ON br.currency_id = cur.id
       ${whereClause}
       ORDER BY ts.date DESC, ts.created_at DESC`,
      params
    );

    return {
      data: timesheets,
      total: parseInt(countResult?.count || '0', 10),
    };
  }

  /**
   * Get billing summary by project
   */
  async getBillingSummary(
    tenantId: string,
    companyId: string,
    filters: BillingFilters = {}
  ): Promise<TimesheetBillingSummary[]> {
    const { partner_id, date_from, date_to } = filters;

    let whereClause = `WHERE ts.tenant_id = $1
      AND ts.company_id = $2
      AND ts.status = 'approved'
      AND ts.billable = true
      AND ts.invoice_line_id IS NULL`;
    const params: any[] = [tenantId, companyId];
    let paramIndex = 3;

    if (partner_id) {
      whereClause += ` AND p.partner_id = $${paramIndex++}`;
      params.push(partner_id);
    }

    if (date_from) {
      whereClause += ` AND ts.date >= $${paramIndex++}`;
      params.push(date_from);
    }

    if (date_to) {
      whereClause += ` AND ts.date <= $${paramIndex++}`;
      params.push(date_to);
    }

    return query<TimesheetBillingSummary>(
      `SELECT p.id as project_id, p.name as project_name,
              p.partner_id, pr.name as partner_name,
              SUM(ts.hours) as total_hours,
              SUM(COALESCE(br.hourly_rate * ts.hours, 0)) as total_amount,
              COALESCE(MIN(br.currency_id), (SELECT id FROM core.currencies WHERE code = 'MXN' LIMIT 1)) as currency_id,
              COALESCE(MIN(cur.code), 'MXN') as currency_code,
              COUNT(ts.id) as timesheet_count,
              MIN(ts.date) as date_from,
              MAX(ts.date) as date_to
       FROM projects.timesheets ts
       JOIN projects.projects p ON ts.project_id = p.id
       LEFT JOIN core.partners pr ON p.partner_id = pr.id
       LEFT JOIN LATERAL (
         SELECT hourly_rate, currency_id
         FROM projects.billing_rates br2
         WHERE br2.tenant_id = ts.tenant_id
           AND br2.company_id = ts.company_id
           AND br2.active = true
           AND (br2.effective_from IS NULL OR br2.effective_from <= ts.date)
           AND (br2.effective_to IS NULL OR br2.effective_to >= ts.date)
           AND (
             (br2.project_id = ts.project_id AND br2.user_id = ts.user_id) OR
             (br2.project_id = ts.project_id AND br2.user_id IS NULL) OR
             (br2.project_id IS NULL AND br2.user_id = ts.user_id) OR
             (br2.project_id IS NULL AND br2.user_id IS NULL)
           )
         ORDER BY
           CASE
             WHEN br2.project_id IS NOT NULL AND br2.user_id IS NOT NULL THEN 1
             WHEN br2.project_id IS NOT NULL THEN 2
             WHEN br2.user_id IS NOT NULL THEN 3
             ELSE 4
           END
         LIMIT 1
       ) br ON true
       LEFT JOIN core.currencies cur ON br.currency_id = cur.id
       ${whereClause}
       GROUP BY p.id, p.name, p.partner_id, pr.name
       ORDER BY total_amount DESC`,
      params
    );
  }

  // --------------------------------------------------------------------------
  // CREATE INVOICE FROM TIMESHEETS
  // --------------------------------------------------------------------------

  /**
   * Create an invoice from unbilled timesheets
   */
  async createInvoiceFromTimesheets(
    tenantId: string,
    companyId: string,
    partnerId: string,
    timesheetIds: string[],
    userId: string,
    options: {
      currency_id?: string;
      group_by?: 'project' | 'user' | 'task' | 'none';
      notes?: string;
    } = {}
  ): Promise<InvoiceFromTimesheetsResult> {
    const { currency_id, group_by = 'project', notes } = options;

    if (!timesheetIds.length) {
      throw new ValidationError('Debe seleccionar al menos un timesheet para facturar');
    }

    const client = await getClient();

    try {
      await client.query('BEGIN');

      // Verify all timesheets exist, are approved, billable, and not yet invoiced
      const timesheets = await query<{
        id: string;
        project_id: string;
        project_name: string;
        task_id: string;
        task_name: string;
        user_id: string;
        user_name: string;
        date: Date;
        hours: number;
        description: string;
        hourly_rate: number;
        billable_amount: number;
        currency_id: string;
      }>(
        `SELECT ts.id, ts.project_id, p.name as project_name,
                ts.task_id, t.name as task_name,
                ts.user_id, u.name as user_name,
                ts.date, ts.hours, ts.description,
                COALESCE(br.hourly_rate, 0) as hourly_rate,
                COALESCE(br.hourly_rate * ts.hours, 0) as billable_amount,
                COALESCE(br.currency_id, (SELECT id FROM core.currencies WHERE code = 'MXN' LIMIT 1)) as currency_id
         FROM projects.timesheets ts
         JOIN projects.projects p ON ts.project_id = p.id
         LEFT JOIN projects.tasks t ON ts.task_id = t.id
         JOIN auth.users u ON ts.user_id = u.id
         LEFT JOIN LATERAL (
           SELECT hourly_rate, currency_id
           FROM projects.billing_rates br2
           WHERE br2.tenant_id = ts.tenant_id
             AND br2.company_id = ts.company_id
             AND br2.active = true
             AND (br2.effective_from IS NULL OR br2.effective_from <= ts.date)
             AND (br2.effective_to IS NULL OR br2.effective_to >= ts.date)
             AND (
               (br2.project_id = ts.project_id AND br2.user_id = ts.user_id) OR
               (br2.project_id = ts.project_id AND br2.user_id IS NULL) OR
               (br2.project_id IS NULL AND br2.user_id = ts.user_id) OR
               (br2.project_id IS NULL AND br2.user_id IS NULL)
             )
           ORDER BY
             CASE
               WHEN br2.project_id IS NOT NULL AND br2.user_id IS NOT NULL THEN 1
               WHEN br2.project_id IS NOT NULL THEN 2
               WHEN br2.user_id IS NOT NULL THEN 3
               ELSE 4
             END
           LIMIT 1
         ) br ON true
         WHERE ts.id = ANY($1)
           AND ts.tenant_id = $2
           AND ts.company_id = $3
           AND ts.status = 'approved'
           AND ts.billable = true
           AND ts.invoice_line_id IS NULL`,
        [timesheetIds, tenantId, companyId]
      );

      if (timesheets.length !== timesheetIds.length) {
        throw new ValidationError(
          `Algunos timesheets no son válidos para facturación. ` +
          `Esperados: ${timesheetIds.length}, Encontrados: ${timesheets.length}. ` +
          `Verifique que estén aprobados, sean facturables y no estén ya facturados.`
        );
      }

      // Determine currency
      const invoiceCurrency = currency_id || timesheets[0]?.currency_id;

      // Calculate totals
      const totalHours = timesheets.reduce((sum, ts) => sum + ts.hours, 0);
      const totalAmount = timesheets.reduce((sum, ts) => sum + ts.billable_amount, 0);

      // Create invoice
      const invoiceResult = await client.query<{ id: string }>(
        `INSERT INTO financial.invoices (
          tenant_id, company_id, partner_id, invoice_type, invoice_date,
          currency_id, amount_untaxed, amount_tax, amount_total,
          amount_paid, amount_residual, notes, created_by
        )
         VALUES ($1, $2, $3, 'customer', CURRENT_DATE, $4, $5, 0, $5, 0, $5, $6, $7)
         RETURNING id`,
        [tenantId, companyId, partnerId, invoiceCurrency, totalAmount, notes, userId]
      );

      const invoiceId = invoiceResult.rows[0].id;

      // Group timesheets for invoice lines
      let lineData: { description: string; hours: number; rate: number; amount: number; timesheetIds: string[] }[];

      if (group_by === 'none') {
        // One line per timesheet
        lineData = timesheets.map(ts => ({
          description: `${ts.project_name}${ts.task_name ? ' - ' + ts.task_name : ''}: ${ts.description || 'Horas de trabajo'} (${ts.date})`,
          hours: ts.hours,
          rate: ts.hourly_rate,
          amount: ts.billable_amount,
          timesheetIds: [ts.id],
        }));
      } else if (group_by === 'project') {
        // Group by project
        const byProject = new Map<string, typeof lineData[0]>();
        for (const ts of timesheets) {
          const existing = byProject.get(ts.project_id);
          if (existing) {
            existing.hours += ts.hours;
            existing.amount += ts.billable_amount;
            existing.timesheetIds.push(ts.id);
          } else {
            byProject.set(ts.project_id, {
              description: `Horas de consultoría - ${ts.project_name}`,
              hours: ts.hours,
              rate: ts.hourly_rate,
              amount: ts.billable_amount,
              timesheetIds: [ts.id],
            });
          }
        }
        lineData = Array.from(byProject.values());
      } else if (group_by === 'user') {
        // Group by user
        const byUser = new Map<string, typeof lineData[0]>();
        for (const ts of timesheets) {
          const existing = byUser.get(ts.user_id);
          if (existing) {
            existing.hours += ts.hours;
            existing.amount += ts.billable_amount;
            existing.timesheetIds.push(ts.id);
          } else {
            byUser.set(ts.user_id, {
              description: `Horas de consultoría - ${ts.user_name}`,
              hours: ts.hours,
              rate: ts.hourly_rate,
              amount: ts.billable_amount,
              timesheetIds: [ts.id],
            });
          }
        }
        lineData = Array.from(byUser.values());
      } else {
        // Group by task
        const byTask = new Map<string, typeof lineData[0]>();
        for (const ts of timesheets) {
          const key = ts.task_id || 'no-task';
          const existing = byTask.get(key);
          if (existing) {
            existing.hours += ts.hours;
            existing.amount += ts.billable_amount;
            existing.timesheetIds.push(ts.id);
          } else {
            byTask.set(key, {
              description: ts.task_name ? `Tarea: ${ts.task_name}` : `Proyecto: ${ts.project_name}`,
              hours: ts.hours,
              rate: ts.hourly_rate,
              amount: ts.billable_amount,
              timesheetIds: [ts.id],
            });
          }
        }
        lineData = Array.from(byTask.values());
      }

      // Create invoice lines and link timesheets
      for (const line of lineData) {
        // Create invoice line
        const lineResult = await client.query<{ id: string }>(
          `INSERT INTO financial.invoice_lines (
            invoice_id, description, quantity, price_unit,
            amount_untaxed, amount_tax, amount_total
          )
           VALUES ($1, $2, $3, $4, $5, 0, $5)
           RETURNING id`,
          [invoiceId, `${line.description} (${line.hours} hrs)`, line.hours, line.rate, line.amount]
        );

        const lineId = lineResult.rows[0].id;

        // Link timesheets to this invoice line
        await client.query(
          `UPDATE projects.timesheets
           SET invoice_line_id = $1, updated_by = $2, updated_at = CURRENT_TIMESTAMP
           WHERE id = ANY($3)`,
          [lineId, userId, line.timesheetIds]
        );
      }

      await client.query('COMMIT');

      logger.info('Invoice created from timesheets', {
        invoice_id: invoiceId,
        timesheet_count: timesheets.length,
        total_hours: totalHours,
        total_amount: totalAmount,
      });

      return {
        invoice_id: invoiceId,
        timesheets_billed: timesheets.length,
        total_hours: totalHours,
        total_amount: totalAmount,
      };
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }

  /**
   * Get billing history for a project
   */
  async getProjectBillingHistory(
    tenantId: string,
    projectId: string
  ): Promise<{
    total_hours_billed: number;
    total_amount_billed: number;
    unbilled_hours: number;
    unbilled_amount: number;
    invoices: { id: string; number: string; date: Date; amount: number; status: string }[];
  }> {
    // Get billed totals
    const billedStats = await queryOne<{ hours: string; amount: string }>(
      `SELECT COALESCE(SUM(ts.hours), 0) as hours,
              COALESCE(SUM(il.amount_total), 0) as amount
       FROM projects.timesheets ts
       JOIN financial.invoice_lines il ON ts.invoice_line_id = il.id
       WHERE ts.tenant_id = $1 AND ts.project_id = $2 AND ts.invoice_line_id IS NOT NULL`,
      [tenantId, projectId]
    );

    // Get unbilled totals
    const unbilledStats = await queryOne<{ hours: string; amount: string }>(
      `SELECT COALESCE(SUM(ts.hours), 0) as hours,
              COALESCE(SUM(COALESCE(br.hourly_rate * ts.hours, 0)), 0) as amount
       FROM projects.timesheets ts
       LEFT JOIN LATERAL (
         SELECT hourly_rate
         FROM projects.billing_rates br2
         WHERE br2.tenant_id = ts.tenant_id
           AND br2.company_id = ts.company_id
           AND br2.active = true
           AND (br2.effective_from IS NULL OR br2.effective_from <= ts.date)
           AND (br2.effective_to IS NULL OR br2.effective_to >= ts.date)
           AND (
             (br2.project_id = ts.project_id AND br2.user_id = ts.user_id) OR
             (br2.project_id = ts.project_id AND br2.user_id IS NULL) OR
             (br2.project_id IS NULL AND br2.user_id = ts.user_id) OR
             (br2.project_id IS NULL AND br2.user_id IS NULL)
           )
         ORDER BY
           CASE
             WHEN br2.project_id IS NOT NULL AND br2.user_id IS NOT NULL THEN 1
             WHEN br2.project_id IS NOT NULL THEN 2
             WHEN br2.user_id IS NOT NULL THEN 3
             ELSE 4
           END
         LIMIT 1
       ) br ON true
       WHERE ts.tenant_id = $1
         AND ts.project_id = $2
         AND ts.status = 'approved'
         AND ts.billable = true
         AND ts.invoice_line_id IS NULL`,
      [tenantId, projectId]
    );

    // Get related invoices
    const invoices = await query<{ id: string; number: string; date: Date; amount: number; status: string }>(
      `SELECT DISTINCT i.id, i.number, i.invoice_date as date, i.amount_total as amount, i.status
       FROM financial.invoices i
       JOIN financial.invoice_lines il ON il.invoice_id = i.id
       JOIN projects.timesheets ts ON ts.invoice_line_id = il.id
       WHERE ts.tenant_id = $1 AND ts.project_id = $2
       ORDER BY i.invoice_date DESC`,
      [tenantId, projectId]
    );

    return {
      total_hours_billed: parseFloat(billedStats?.hours || '0'),
      total_amount_billed: parseFloat(billedStats?.amount || '0'),
      unbilled_hours: parseFloat(unbilledStats?.hours || '0'),
      unbilled_amount: parseFloat(unbilledStats?.amount || '0'),
      invoices,
    };
  }
}

export const billingService = new BillingService();
