import { BadRequestException, Injectable, ServiceUnavailableException } from '@nestjs/common';
import { DatabaseService } from '../database/database.service';

@Injectable()
export class ReportsService {
  constructor(private readonly db: DatabaseService) {}

  async summary(projectId?: string, from?: string, to?: string) {
    if (!this.db.isConfigured()) return { configured: false, projectId: projectId ?? null, summary: null };
    if (!projectId) throw new BadRequestException('PROJECT_ID_REQUIRED');
    const start = from || '1900-01-01';
    const end = to || '2999-12-31';
    const [production, finance] = await Promise.all([
      this.db.query(
        `SELECT COALESCE(SUM(eggs_total),0)::bigint AS eggs_total,
                COALESCE(SUM(mortality),0)::bigint AS mortality_total,
                COALESCE(SUM(feed_consumption),0)::numeric AS feed_total,
                MIN(report_date) AS first_date, MAX(report_date) AS last_date
         FROM farm_daily_reports WHERE project_id = $1 AND report_date BETWEEN $2::date AND $3::date`,
        [projectId, start, end]
      ),
      this.db.query(
        `SELECT
          COALESCE((SELECT SUM(amount) FROM sales WHERE project_id=$1 AND occurred_on BETWEEN $2::date AND $3::date),0)::numeric AS sales_total,
          COALESCE((SELECT SUM(amount) FROM expenses WHERE project_id=$1 AND occurred_on BETWEEN $2::date AND $3::date),0)::numeric AS expenses_total`,
        [projectId, start, end]
      )
    ]);
    return { configured: true, projectId, period: { from: start, to: end }, production: production.rows[0], finance: finance.rows[0] };
  }

  async queue(input: Record<string, unknown>) {
    if (!this.db.isConfigured()) throw new ServiceUnavailableException('DATABASE_NOT_CONFIGURED');
    const reportType = String(input.reportType || '');
    const format = String(input.format || 'PDF').toUpperCase();
    if (!reportType) throw new BadRequestException('REPORT_TYPE_REQUIRED');
    if (!['PDF', 'XLSX', 'CSV'].includes(format)) throw new BadRequestException('UNSUPPORTED_REPORT_FORMAT');
    const result = await this.db.query(
      `INSERT INTO report_jobs(project_id, requested_by, report_type, period_start, period_end, format)
       VALUES($1,$2,$3,$4,$5,$6) RETURNING id, status, created_at`,
      [input.projectId || null, input.requestedBy || null, reportType, input.periodStart || null, input.periodEnd || null, format]
    );
    return { ok: true, job: result.rows[0] };
  }

  async jobs(projectId?: string) {
    if (!this.db.isConfigured()) return { configured: false, items: [] };
    const result = await this.db.query(
      `SELECT id, project_id, report_type, format, status, created_at, completed_at, error_code
       FROM report_jobs WHERE ($1::uuid IS NULL OR project_id=$1::uuid) ORDER BY created_at DESC LIMIT 50`,
      [projectId || null]
    );
    return { configured: true, items: result.rows };
  }
}