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

function numberOrNull(value: unknown) {
  if (value === null || value === undefined || value === '') return null;
  const parsed = Number(value);
  return Number.isFinite(parsed) ? parsed : null;
}

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

  private requireDatabase() {
    if (!this.db.isConfigured()) throw new ServiceUnavailableException('DATABASE_NOT_CONFIGURED');
  }

  async overview(projectId?: string) {
    if (!this.db.isConfigured()) {
      return { configured: false, projectId: projectId ?? null, metrics: null, recentReports: [], openTasks: [], alerts: [] };
    }
    if (!projectId) throw new BadRequestException('PROJECT_ID_REQUIRED');

    const [metrics, reports, tasks, alerts] = await Promise.all([
      this.db.query(
        `SELECT
          COALESCE(SUM(eggs_total) FILTER (WHERE report_date = CURRENT_DATE),0)::int AS eggs_today,
          COALESCE(SUM(mortality) FILTER (WHERE report_date >= CURRENT_DATE - INTERVAL '6 days'),0)::int AS mortality_7d,
          COALESCE(SUM(feed_consumption) FILTER (WHERE report_date = CURRENT_DATE),0)::numeric AS feed_today,
          MAX(closing_poultry) FILTER (WHERE report_date <= CURRENT_DATE) AS latest_poultry
         FROM farm_daily_reports WHERE project_id = $1`,
        [projectId]
      ),
      this.db.query(
        `SELECT id, report_date, opening_poultry, mortality, closing_poultry, eggs_total, feed_consumption, feed_unit
         FROM farm_daily_reports WHERE project_id = $1 ORDER BY report_date DESC LIMIT 14`,
        [projectId]
      ),
      this.db.query(
        `SELECT id, title, category, priority, status, due_at
         FROM farm_tasks WHERE project_id = $1 AND status <> 'DONE' ORDER BY due_at NULLS LAST LIMIT 12`,
        [projectId]
      ),
      this.db.query(
        `SELECT id, insight_type, severity, title, summary, generated_at
         FROM ai_operational_insights WHERE project_id = $1 AND status = 'OPEN' ORDER BY generated_at DESC LIMIT 8`,
        [projectId]
      )
    ]);

    return { configured: true, projectId, metrics: metrics.rows[0] ?? null, recentReports: reports.rows, openTasks: tasks.rows, alerts: alerts.rows };
  }

  async listUnits(projectId?: string) {
    if (!this.db.isConfigured()) return { configured: false, items: [] };
    if (!projectId) throw new BadRequestException('PROJECT_ID_REQUIRED');
    const result = await this.db.query(
      `SELECT fu.id, fu.name, fu.unit_type, fu.capacity, fu.capacity_unit, f.name AS farm_name
       FROM farm_units fu JOIN farms f ON f.id = fu.farm_id
       WHERE f.project_id = $1 ORDER BY f.name, fu.name`,
      [projectId]
    );
    return { configured: true, items: result.rows };
  }

  async listDailyReports(projectId?: string, limit = 30) {
    if (!this.db.isConfigured()) return { configured: false, items: [] };
    if (!projectId) throw new BadRequestException('PROJECT_ID_REQUIRED');
    const safeLimit = Math.max(1, Math.min(100, Number(limit) || 30));
    const result = await this.db.query(
      `SELECT id, report_date, farm_unit_id, opening_poultry, additions, mortality, closing_poultry,
              eggs_total, good_eggs, broken_eggs, feed_consumption, feed_unit, water_consumption, water_unit,
              medication, notes, created_at
       FROM farm_daily_reports WHERE project_id = $1 ORDER BY report_date DESC LIMIT $2`,
      [projectId, safeLimit]
    );
    return { configured: true, items: result.rows };
  }

  async createDailyReport(input: Record<string, unknown>) {
    this.requireDatabase();
    const projectId = String(input.projectId || '');
    const reportDate = String(input.reportDate || '');
    if (!projectId || !/^\d{4}-\d{2}-\d{2}$/.test(reportDate)) throw new BadRequestException('PROJECT_ID_AND_REPORT_DATE_REQUIRED');

    const opening = numberOrNull(input.openingPoultry);
    const additions = Number(input.additions || 0);
    const mortality = Number(input.mortality || 0);
    const closing = numberOrNull(input.closingPoultry);
    const eggs = Number(input.eggsTotal || 0);
    const good = Number(input.goodEggs || 0);
    const broken = Number(input.brokenEggs || 0);
    if ([additions, mortality, eggs, good, broken].some((v) => !Number.isFinite(v) || v < 0)) throw new BadRequestException('INVALID_NON_NEGATIVE_NUMBER');
    if (opening !== null && closing !== null && closing !== opening + additions - mortality) throw new BadRequestException('CLOSING_POULTRY_MISMATCH');
    if (good + broken > eggs) throw new BadRequestException('EGG_BREAKDOWN_EXCEEDS_TOTAL');

    const result = await this.db.query(
      `INSERT INTO farm_daily_reports
       (project_id, farm_id, farm_unit_id, poultry_batch_id, report_date, opening_poultry, additions, mortality,
        closing_poultry, eggs_total, good_eggs, broken_eggs, feed_consumption, feed_unit, water_consumption,
        water_unit, medication, notes, employee_id, source)
       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,'MANUAL')
       RETURNING id, report_date, created_at`,
      [
        projectId, input.farmId || null, input.farmUnitId || null, input.poultryBatchId || null, reportDate,
        opening, additions, mortality, closing, eggs, good, broken, numberOrNull(input.feedConsumption),
        input.feedUnit || null, numberOrNull(input.waterConsumption), input.waterUnit || null,
        input.medication || null, input.notes || null, input.employeeId || null
      ]
    );
    return { ok: true, report: result.rows[0] };
  }
}