<?php

namespace App\Services;

use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\Http;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Storage;

class KpiService
{
    /**
     * Cache duration in hours (24 hours = 1 day, matching original PHP logic)
     */
    private const CACHE_TTL_HOURS = 24;

    /**
     * Cache key for KPI data
     */
    private const CACHE_KEY = 'kpi_dashboard_data';

    /**
     * Google Sheets TSV URL
     */
    private const GOOGLE_SHEETS_URL = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vTjlgCkkYF27MLsD4oLSNLwDzpTn_sK0cJxaZ2k18TR0vn92YprsGkUxCg0YpD_W7XUIEQBeeO9Ga1d/pub?gid=2146907259&single=true&output=tsv';

    /**
     * Default KPI values (fallback if fetch fails)
     */
    private const DEFAULT_KPI = [
        'annual_conferences' => 6,
        'programs' => 129,
        'technical_study_cases' => 661,
        'trainee_companies' => 2157,
        'trainee' => 11496,
        'industry_user_cases' => 714,
        'lectures' => 431,
        'industry_involved' => 15,
    ];

    /**
     * Get KPI data with caching
     *
     * @return array{data: array, updated_at: string|null}
     */
    public function getKpiData(): array
    {
        return Cache::remember(self::CACHE_KEY, self::CACHE_TTL_HOURS * 60 * 60, function () {
            return $this->fetchKpiData();
        });
    }

    /**
     * Clear KPI cache
     *
     * @return void
     */
    public function clearCache(): void
    {
        Cache::forget(self::CACHE_KEY);
    }

    /**
     * Fetch KPI data from Google Sheets TSV
     *
     * @return array{data: array, updated_at: string|null}
     */
    private function fetchKpiData(): array
    {
        try {
            $response = Http::timeout(10)->get(self::GOOGLE_SHEETS_URL);

            if (!$response->successful()) {
                Log::warning('[KpiService] Failed to fetch Google Sheets data', [
                    'status' => $response->status(),
                ]);
                return $this->getDefaultData();
            }

            $tsvContent = $response->body();
            $parsedData = $this->parseTsvContent($tsvContent);

            return [
                'data' => $parsedData,
                'updated_at' => now()->format('Y/m/d H:i'),
            ];
        } catch (\Exception $e) {
            Log::error('[KpiService] Exception while fetching KPI data', [
                'error' => $e->getMessage(),
            ]);
            return $this->getDefaultData();
        }
    }

    /**
     * Parse TSV content into key-value array
     * TSV format: Key\tValue (first row is header)
     *
     * @param string $content
     * @return array
     */
    private function parseTsvContent(string $content): array
    {
        $lines = explode("\r\n", $content);

        // If \r\n doesn't work, try \n
        if (count($lines) <= 1) {
            $lines = explode("\n", $content);
        }

        $result = self::DEFAULT_KPI;

        // Skip header row (index 0)
        for ($i = 1; $i < count($lines); $i++) {
            $line = trim($lines[$i]);
            if (empty($line)) {
                continue;
            }

            $parts = explode("\t", $line);
            if (count($parts) >= 2) {
                $key = $this->normalizeKey($parts[0]);
                $value = $this->parseNumericValue($parts[1]);

                if ($key && isset($result[$key])) {
                    $result[$key] = $value;
                }
            }
        }

        return $result;
    }

    /**
     * Key mapping from Google Sheets to internal keys
     * Google Sheets key (lowercase) => internal key
     */
    private const KEY_MAPPING = [
        'annual_conferences' => 'annual_conferences',
        'lecturers' => 'lectures',
        'programs' => 'programs',
        'trainee_companies' => 'trainee_companies',
        'industrial_cases' => 'industry_user_cases',
        'trainees' => 'trainee',
        'technical_cases' => 'technical_study_cases',
    ];

    /**
     * Normalize key from TSV to match our expected keys
     * "Annual Conferences" -> "annual_conferences"
     * Also maps Google Sheets keys to internal keys
     *
     * @param string $key
     * @return string
     */
    private function normalizeKey(string $key): string
    {
        $key = strtolower(trim($key));
        $key = str_replace(' ', '_', $key);

        // Remove "_count" suffix if present (from original PHP)
        $key = preg_replace('/_count$/', '', $key);

        // Map Google Sheets key to internal key
        return self::KEY_MAPPING[$key] ?? $key;
    }

    /**
     * Parse numeric value, removing commas and converting to integer
     *
     * @param string $value
     * @return int
     */
    private function parseNumericValue(string $value): int
    {
        $value = str_replace(',', '', trim($value));
        return (int) $value;
    }

    /**
     * Get default data when fetch fails
     *
     * @return array{data: array, updated_at: string|null}
     */
    private function getDefaultData(): array
    {
        return [
            'data' => self::DEFAULT_KPI,
            'updated_at' => null,
        ];
    }

    /**
     * Format KPI data for frontend display
     * Returns data with formatted numbers (e.g., 11496 -> "11,496")
     *
     * @return array
     */
    public function getFormattedKpiData(): array
    {
        $kpiData = $this->getKpiData();

        $formatted = [];
        foreach ($kpiData['data'] as $key => $value) {
            $formatted[$key] = [
                'value' => $value,
                'formatted' => number_format($value),
            ];
        }

        return [
            'kpi' => $formatted,
            'updated_at' => $kpiData['updated_at'],
        ];
    }
}