<?php

namespace App\Services;

use App\Enums\ClientOrigin;
use App\Enums\Currency;
use App\Enums\PaymentStatus;
use App\Enums\SaleOrigin;
use App\Enums\SaleType;
use App\Models\Account;
use App\Models\Client;
use App\Models\Sale;
use App\Models\UpworkEarning;
use App\Models\User;
use Carbon\Carbon;

/**
 * Importa el Transaction History de Upwork.
 *
 * Formato CSV:
 *   Date, Transaction ID, Transaction type, Transaction summary,
 *   Transaction summary details, Description 1, Description 2, Description 3,
 *   Agency team, Freelancer, Client team, Account name, PO, Ref ID,
 *   Amount $, Amount in local currency, Currency, Current balance $, Payment method
 *
 * Por cada fila Hourly / Fixed-price:
 *   1. Busca o crea el Client (por nombre de "Client team")
 *   2. Busca o crea la Sale   (por external_id = Ref ID)
 *   3. Crea el UpworkEarning  vinculado a ambos
 *
 * Withdrawal / Withdrawal Fee:
 *   Crea el UpworkEarning vinculado a la Account destino (Payoneer, Airtm/ACH, etc.)
 *
 * Service Fee / Subscription:
 *   Se omiten
 */
class UpworkEarningsImporter
{
    // Solo Service Fee se omite: es la comisión de Upwork sobre ganancias (no es un gasto real,
    // es simplemente que Upwork ya descontó su parte antes de acreditarte)
    private const SKIP_TYPES     = ['Service Fee'];

    private const EARNING_TYPES  = ['Hourly', 'Fixed-price', 'Fixed Price', 'Bonus'];

    // Gastos reales cobrados por Upwork
    private const EXPENSE_TYPES  = ['Connects', 'Subscription'];

    // Pagos con tarjeta que recargan el balance para comprar Connects (se omiten:
    // el gasto real ya está capturado en la fila "Connects" correspondiente)
    private const PAYMENT_TYPES  = ['Payment'];

    // ── Paso 1: Analizar sin guardar ─────────────────────────────────────────

    public function analyze(string $fileContent): array
    {
        $rows = $this->parseCsv($fileContent);

        $newRows       = [];
        $duplicateRows = [];
        $invalidRows   = [];

        foreach ($rows as $i => $row) {
            try {
                $parsed = $this->parseRow($row);

                if ($parsed === null) {
                    continue; // Omitida intencionalmente
                }
                if ($parsed === false) {
                    $invalidRows[] = ['line' => $i + 2, 'raw' => implode(', ', array_slice(array_values($row), 0, 5))];
                    continue;
                }

                $exists = UpworkEarning::where('ref_id', $parsed['ref_id'])->exists();

                if ($exists) {
                    $duplicateRows[] = $parsed;
                } else {
                    $newRows[] = $parsed;
                }

            } catch (\Throwable $e) {
                $invalidRows[] = ['line' => $i + 2, 'error' => $e->getMessage()];
            }
        }

        return [
            'new'        => $newRows,
            'duplicates' => $duplicateRows,
            'invalid'    => $invalidRows,
        ];
    }

    // ── Paso 2: Guardar los nuevos ────────────────────────────────────────────

    public function import(array $newRows, User $user): array
    {
        $created        = 0;
        $salesCreated   = 0;
        $clientsCreated = 0;
        $errors         = [];

        foreach ($newRows as $row) {
            try {
                $clientId  = null;
                $saleId    = null;
                $accountId = null;
                $isEarning    = in_array($row['payment_type'], self::EARNING_TYPES);
                $isWithdrawal = in_array($row['payment_type'], ['Withdrawal', 'Withdrawal Fee']);

                if ($isEarning && $row['client_name']) {
                    [$clientId, $clientWasNew] = $this->findOrCreateClient($row);
                    if ($clientWasNew) $clientsCreated++;

                    [$saleId, $saleWasNew] = $this->findOrCreateSale($row, $clientId, $user);
                    if ($saleWasNew) $salesCreated++;
                }

                if ($isWithdrawal) {
                    $accountId = $this->findAccountId($row['contract_title']);
                }

                UpworkEarning::create([
                    'ref_id'              => $row['ref_id'],
                    'transaction_date'    => $row['transaction_date'],
                    'date_from'           => $row['date_from'],
                    'date_to'             => $row['date_to'],
                    'team_name'           => $row['team_name'],
                    'team_upwork_id'      => null,
                    'client_name'         => $row['client_name'],
                    'contract_title'      => $row['contract_title'],
                    'contract_upwork_id'  => null,
                    'amount'              => $row['amount'],
                    'payment_type'        => $row['payment_type'],
                    'client_id'           => $clientId,
                    'sale_id'             => $saleId,
                    'account_id'          => $accountId,
                    'imported_by_user_id' => $user->id,
                    'imported_at'         => now(),
                ]);

                $created++;

            } catch (\Throwable $e) {
                $errors[] = "{$row['contract_title']} ({$row['transaction_date']}): " . $e->getMessage();
            }
        }

        return [
            'created'         => $created,
            'sales_created'   => $salesCreated,
            'clients_created' => $clientsCreated,
            'errors'          => $errors,
        ];
    }

    // ── Client: buscar o crear ────────────────────────────────────────────────

    /**
     * Retorna [client_id, $wasNew]
     */
    private function findOrCreateClient(array $row): array
    {
        $name = trim($row['client_name']);
        $lower = strtolower($name);

        $client = Client::whereRaw('LOWER(name) = ?', [$lower])->first()
               ?? Client::whereRaw('LOWER(name) LIKE ?', ["%{$lower}%"])->first();

        if ($client) {
            return [$client->id, false];
        }

        $client = Client::create([
            'name'             => $name,
            'origin'           => ClientOrigin::Upwork,
            'currency_default' => Currency::USD,
        ]);

        return [$client->id, true];
    }

    // ── Sale: buscar o crear ──────────────────────────────────────────────────

    /**
     * Retorna [sale_id, $wasNew]
     */
    private function findOrCreateSale(array $row, int $clientId, User $user): array
    {
        // Buscar por external_id = ref_id (importación idempotente)
        $sale = Sale::where('external_id', $row['ref_id'])
                    ->where('external_source', 'upwork')
                    ->first();

        if ($sale) {
            return [$sale->id, false];
        }

        $type        = $row['payment_type'];
        $saleType    = str_contains(strtolower($type), 'fixed') ? SaleType::Fixed : SaleType::Hourly;
        $description = $row['desc_detail'] ?: null;

        // Para Hourly: extraer horas y tarifa de la descripción
        $hourlyRate    = null;
        $hoursQuantity = null;
        if ($saleType === SaleType::Hourly && $description) {
            if (preg_match('/(\d+\.?\d*)\s+hours?\s+x\s+\$(\d+\.?\d*)/i', $description, $m)) {
                $hoursQuantity = (float) $m[1];
                $hourlyRate    = (float) $m[2];
            }
        }

        $sale = Sale::create([
            'client_id'          => $clientId,
            'owner_user_id'      => $user->id,
            'created_by_user_id' => $user->id,
            'origin'             => SaleOrigin::Upwork,
            'sale_type'          => $saleType,
            'title'              => $row['contract_title'],
            'description'        => $description,
            'sale_date'          => $row['date_from'],
            'currency'           => Currency::USD,
            'fixed_amount'       => $saleType === SaleType::Fixed  ? $row['amount'] : null,
            'hourly_rate'        => $hourlyRate,
            'hours_quantity'     => $hoursQuantity,
            'gross_amount'       => $row['amount'],
            'payment_status'     => PaymentStatus::Paid,
            'external_source'    => 'upwork',
            'external_id'        => $row['ref_id'],
        ]);

        return [$sale->id, true];
    }

    // ── Account: detectar por nombre del destino del retiro ──────────────────

    /**
     * "Payoneer"  → busca Account cuyo nombre contenga "payoneer"
     * "ACH 6890"  → Airtm; busca Account cuyo nombre esté contenido en "ach 6890"
     *               o que contenga "airtm" / "ach"
     */
    private function findAccountId(string $destination): ?int
    {
        $dest = strtolower(trim($destination));
        if (!$dest) return null;

        // Coincidencia exacta
        $account = Account::whereRaw('LOWER(name) = ?', [$dest])->first();
        if ($account) return $account->id;

        // El nombre de la cuenta está contenido en el destino (ej: "Payoneer" en "Payoneer ...")
        $account = Account::whereRaw('LOWER(?) LIKE CONCAT("%", LOWER(name), "%")', [$dest])->first();
        if ($account) return $account->id;

        // El destino está contenido en el nombre de la cuenta
        $account = Account::whereRaw('LOWER(name) LIKE ?', ["%{$dest}%"])->first();
        if ($account) return $account->id;

        // Alias: Upwork usa "ACH XXXX" para retiros a Airtm
        if (str_contains($dest, 'ach')) {
            $account = Account::whereRaw('LOWER(name) LIKE ?', ['%airtm%'])->first();
            if ($account) return $account->id;
        }

        return null;
    }

    // ── Parser de fila ────────────────────────────────────────────────────────

    /**
     * null  → omitida intencionalmente (Service Fee, Payment/recarga de Connects)
     * false → inválida (faltan datos)
     * array → datos válidos (Earning, Withdrawal, Expense)
     */
    private function parseRow(array $row): null|false|array
    {
        $type = trim($row['Transaction type'] ?? '');

        if (!$type || in_array($type, self::SKIP_TYPES) || in_array($type, self::PAYMENT_TYPES)) {
            return null;
        }

        $refId = trim($row['Ref ID'] ?? '');
        if (!$refId) {
            return null;
        }

        $transactionDate = $this->parseDate(trim($row['Date'] ?? ''));
        if (!$transactionDate) {
            return false;
        }

        $rawAmount = str_replace(['$', ',', ' '], '', trim($row['Amount $'] ?? ''));
        if (!is_numeric($rawAmount)) {
            return false;
        }

        $summary    = trim($row['Transaction summary']         ?? '');
        $details    = trim($row['Transaction summary details'] ?? '');
        $descDetail = trim($row['Description 2']              ?? '');

        if (in_array($type, self::EARNING_TYPES)) {
            [$dateFrom, $dateTo] = $this->parsePeriod($details, $transactionDate);
        } else {
            $dateFrom = $dateTo = $transactionDate;
        }

        $agencyTeam  = trim($row['Agency team']  ?? '');
        $accountName = trim($row['Account name'] ?? '');
        $clientTeam  = trim($row['Client team']  ?? '');

        return [
            'ref_id'           => $refId,
            'transaction_date' => $transactionDate,
            'date_from'        => $dateFrom,
            'date_to'          => $dateTo,
            'team_name'        => $agencyTeam ?: $accountName,
            'client_name'      => $clientTeam,
            'contract_title'   => $summary,
            'desc_detail'      => $descDetail,  // "4.50 hours x $10.00 = $45.00"
            'amount'           => (float) $rawAmount,
            'payment_type'     => $type,
        ];
    }

    // ── Parseo de fecha ───────────────────────────────────────────────────────

    private function parseDate(string $dateStr): ?string
    {
        if (!$dateStr) return null;
        try {
            return Carbon::createFromFormat('M j, Y', $dateStr)->format('Y-m-d');
        } catch (\Exception) {
            try {
                return Carbon::parse($dateStr)->format('Y-m-d');
            } catch (\Exception) {
                return null;
            }
        }
    }

    /**
     * "Earnings for Feb 9-Feb 15, 2026"   → ["2026-02-09", "2026-02-15"]
     * "Earnings for Jan 26-Feb 1, 2026"   → ["2026-01-26", "2026-02-01"]
     * "Earnings for Dec 29-Jan 4, 2026"   → ["2025-12-29", "2026-01-04"]
     */
    private function parsePeriod(string $details, string $fallbackDate): array
    {
        if (preg_match(
            '/Earnings\s+for\s+(\w+)\s+(\d+)\s*-\s*(\w+)\s+(\d+),\s*(\d{4})/i',
            $details, $m
        )) {
            $toYear = (int) $m[5];
            try {
                $toDate   = Carbon::createFromFormat('M j Y', "{$m[3]} {$m[4]} {$toYear}");
                $fromDate = Carbon::createFromFormat('M j Y', "{$m[1]} {$m[2]} {$toYear}");
                if ($fromDate->gt($toDate)) {
                    $fromDate->subYear(); // cruce de año (ej: Dec → Jan)
                }
                return [$fromDate->format('Y-m-d'), $toDate->format('Y-m-d')];
            } catch (\Exception) {}
        }

        return [$fallbackDate, $fallbackDate];
    }

    // ── CSV Parser ────────────────────────────────────────────────────────────

    private function parseCsv(string $content): array
    {
        $content = ltrim($content, "\xEF\xBB\xBF");

        $encoding = mb_detect_encoding($content, ['UTF-8', 'ISO-8859-1', 'Windows-1252'], true);
        if ($encoding && $encoding !== 'UTF-8') {
            $content = mb_convert_encoding($content, 'UTF-8', $encoding);
        }

        $content = str_replace(["\r\n", "\r"], "\n", $content);
        $lines   = explode("\n", $content);
        $rows    = [];
        $headers = null;

        foreach ($lines as $line) {
            $line = trim($line);
            if ($line === '') continue;

            $cols = str_getcsv($line, ',', '"', '\\');

            if ($headers === null) {
                $headers = array_map('trim', $cols);
                continue;
            }

            $cols = array_pad($cols, count($headers), '');
            if (count($cols) > count($headers)) {
                $cols = array_slice($cols, 0, count($headers));
            }

            $rows[] = array_combine($headers, $cols);
        }

        return $rows;
    }
}
