<?php

declare(strict_types=1);

namespace App\Services\Reports\Providers;

use App\Models\ReportJob;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;

/**
 * deals_export — детальная выгрузка сделок за период (8 колонок).
 *
 * parameters в ReportJob:
 *   - date_from, date_to (required, Y-m-d)
 *   - project_id (optional)
 *   - manager_id (optional)
 *
 * Soft-deleted сделки скрыты (whereNull deleted_at). LEFT JOIN на
 * supplier_lead_costs — стоимость может отсутствовать (manual create).
 */
class DealsExportProvider implements ReportDataProvider
{
    public function headers(): array
    {
        return ['ID', 'Телефон', 'Контакт', 'Статус', 'Проект', 'Менеджер', 'Стоимость (₽)', 'Получено'];
    }

    public function rows(ReportJob $job): array
    {
        $params = $job->parameters ?? [];
        $dateFrom = Carbon::parse($params['date_from'])->startOfDay();
        $dateTo = Carbon::parse($params['date_to'])->endOfDay();

        return DB::transaction(function () use ($job, $params, $dateFrom, $dateTo): array {
            DB::statement('SET LOCAL app.current_tenant_id = '.(int) $job->tenant_id);

            $query = DB::table('deals')
                ->leftJoin('projects', 'deals.project_id', '=', 'projects.id')
                ->leftJoin('users', 'deals.manager_id', '=', 'users.id')
                ->leftJoin('supplier_lead_costs', 'deals.id', '=', 'supplier_lead_costs.deal_id')
                ->where('deals.tenant_id', $job->tenant_id)
                ->whereNull('deals.deleted_at')
                ->whereBetween('deals.received_at', [$dateFrom, $dateTo])
                ->orderBy('deals.received_at')
                ->orderBy('deals.id')
                ->select([
                    'deals.id',
                    'deals.phone',
                    'deals.contact_name',
                    'deals.status',
                    'projects.name as project_name',
                    'users.first_name as user_first_name',
                    'users.last_name as user_last_name',
                    'users.email as user_email',
                    'supplier_lead_costs.cost_rub',
                    'deals.received_at',
                ]);

            if (isset($params['project_id'])) {
                $query->where('deals.project_id', (int) $params['project_id']);
            }
            if (isset($params['manager_id'])) {
                $query->where('deals.manager_id', (int) $params['manager_id']);
            }

            return $query->get()->map(function ($row): array {
                $managerName = trim(($row->user_first_name ?? '').' '.($row->user_last_name ?? ''));
                if ($managerName === '') {
                    $managerName = $row->user_email ?? '';
                }

                // Провайдер отдаёт СЫРЫЕ данные. Защита от CSV/formula-инъекции —
                // в писателях-форматтерах (CsvFormatter/XlsxFormatter), чтобы не
                // портить не-табличные форматы (JSON). См. F-CSV.
                return [
                    (int) $row->id,
                    (string) ($row->phone ?? ''),
                    (string) ($row->contact_name ?? ''),
                    (string) ($row->status ?? ''),
                    (string) ($row->project_name ?? ''),
                    $managerName,
                    $row->cost_rub !== null ? (string) $row->cost_rub : null,
                    $row->received_at !== null ? Carbon::parse($row->received_at)->toIso8601String() : null,
                ];
            })->all();
        });
    }

    public function slug(): string
    {
        return 'deals';
    }
}
