<?php

namespace App\Services;

use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Schema;

class FinanceSyncService
{
    protected $api;

    public function __construct(FinanceApiService $api)
    {
        $this->api = $api;
    }

    public function getStudentFinance($admissionNumber, $schoolId = null, $forceRefresh = false)
    {
        $admissionNumber = trim((string) $admissionNumber);
        if ($admissionNumber === '') {
            return null;
        }

        $ttl = $this->ttl();
        $cacheKey = $this->cacheKey($admissionNumber, $schoolId);

        if (!$forceRefresh) {
            $cached = Cache::get($cacheKey);
            if (is_array($cached)) {
                $this->syncUserCurrencyProfile($admissionNumber, $schoolId, (array) data_get($cached, 'summary', []));
                return $cached;
            }

            $dbCached = $this->getDbCache($admissionNumber, $schoolId);
            if (is_array($dbCached) && !$this->isExpired($dbCached)) {
                Cache::put($cacheKey, $dbCached, $ttl);
                $this->syncUserCurrencyProfile($admissionNumber, $schoolId, (array) data_get($dbCached, 'summary', []));
                return $dbCached;
            }
        }

        $summary = $this->api->getFinanceSummary($admissionNumber, $schoolId);
        $invoiceResponse = $this->api->getFinanceInvoices($admissionNumber, $schoolId);

        if (is_array($summary)) {
            $payload = [
                'summary' => $this->reconcileStatus($summary),
                'invoices' => is_array($invoiceResponse) ? (array) data_get($invoiceResponse, 'invoices', []) : [],
                'source' => 'wordpress',
                'fetched_at' => now()->toDateTimeString(),
                'expires_at' => now()->addSeconds($ttl)->toDateTimeString(),
            ];

            Cache::put($cacheKey, $payload, $ttl);
            $this->storeDbCache($admissionNumber, $schoolId, $payload);
            $this->syncUserCurrencyProfile($admissionNumber, $schoolId, $payload['summary']);

            return $payload;
        }

        $fallback = $this->getDbCache($admissionNumber, $schoolId);
        if (is_array($fallback)) {
            $fallback['source'] = 'cache';
            $this->syncUserCurrencyProfile($admissionNumber, $schoolId, (array) data_get($fallback, 'summary', []));
            return $fallback;
        }

        return null;
    }

    public function rememberWebhookSnapshot($admissionNumber, $schoolId, array $summary)
    {
        $admissionNumber = trim((string) $admissionNumber);
        if ($admissionNumber === '') {
            return;
        }

        $ttl = $this->ttl();
        $existing = Cache::get($this->cacheKey($admissionNumber, $schoolId));
        if (!is_array($existing)) {
            $existing = $this->getDbCache($admissionNumber, $schoolId);
        }
        $payload = [
            'summary' => $this->reconcileStatus(array_merge((array) data_get($existing, 'summary', []), $summary)),
            'invoices' => (array) data_get($existing, 'invoices', []),
            'source' => 'webhook',
            'fetched_at' => now()->toDateTimeString(),
            'expires_at' => now()->addSeconds($ttl)->toDateTimeString(),
        ];

        Cache::put($this->cacheKey($admissionNumber, $schoolId), $payload, $ttl);
        $this->storeDbCache($admissionNumber, $schoolId, $payload);
        $this->syncUserCurrencyProfile($admissionNumber, $schoolId, $payload['summary']);
    }

    public function syncAllStudents($schoolId = null)
    {
        $result = [
            'total' => 0,
            'synced' => 0,
            'failed' => 0,
        ];

        if (!Schema::hasTable('users') || !Schema::hasColumn('users', 'admission_number')) {
            return $result;
        }

        $students = DB::table('users')
            ->select('id', 'school_id', 'admission_number')
            ->whereNotNull('admission_number')
            ->where('admission_number', '<>', '')
            ->when($schoolId, function ($query) use ($schoolId) {
                $query->where('school_id', $schoolId);
            })
            ->orderBy('id')
            ->limit(250)
            ->get();

        foreach ($students as $student) {
            $result['total']++;

            try {
                $payload = $this->getStudentFinance((string) $student->admission_number, $schoolId ?: $student->school_id, true);
                if (is_array($payload)) {
                    $result['synced']++;
                } else {
                    $result['failed']++;
                }
            } catch (\Throwable $e) {
                $result['failed']++;
                Log::warning('Finance student sync failed.', [
                    'admission_number' => (string) $student->admission_number,
                    'error' => $e->getMessage(),
                ]);
            }
        }

        return $result;
    }

    public function reconcileStatus(array $summary)
    {
        $status = (string) data_get($summary, 'status', 'pending');
        $balance = (float) data_get($summary, 'mandatory_outstanding', data_get($summary, 'balance', 0));

        $summary['balance'] = $balance;
        $summary['overdue'] = $status === 'overdue';
        $summary['paid'] = $status === 'paid';
        $summary['status'] = $status;

        return $summary;
    }

    private function syncUserCurrencyProfile($admissionNumber, $schoolId, array $summary)
    {
        if (!Schema::hasTable('users') || !Schema::hasColumn('users', 'admission_number')) {
            return;
        }

        $updates = [];
        $userType = $this->normalizeUserType(data_get($summary, 'user_type'));
        $currencyCode = strtoupper(trim((string) data_get($summary, 'currency', '')));
        $currencySymbol = trim((string) data_get($summary, 'symbol', ''));

        if ($userType !== null && Schema::hasColumn('users', 'user_type')) {
            $updates['user_type'] = $userType;
        }

        if ($currencyCode !== '' && Schema::hasColumn('users', 'currency_code')) {
            $updates['currency_code'] = $currencyCode;
        }

        if ($currencySymbol !== '' && Schema::hasColumn('users', 'currency_symbol')) {
            $updates['currency_symbol'] = $currencySymbol;
        }

        if (empty($updates)) {
            return;
        }

        if (Schema::hasColumn('users', 'updated_at')) {
            $updates['updated_at'] = now();
        }

        DB::table('users')
            ->where('admission_number', (string) $admissionNumber)
            ->when($schoolId, function ($query) use ($schoolId) {
                $query->where('school_id', $schoolId);
            })
            ->update($updates);
    }

    private function normalizeUserType($userType)
    {
        $userType = strtolower(trim((string) $userType));
        if ($userType === '') {
            return null;
        }

        if (in_array($userType, ['local', 'kenyan', 'kes'], true)) {
            return 'local';
        }

        return $userType === 'international' ? 'international' : null;
    }

    private function ttl()
    {
        return min(180, max(60, (int) config('finance.sync_ttl', 120)));
    }

    private function cacheKey($admissionNumber, $schoolId)
    {
        return 'finance-sync:' . ($schoolId ?: 'global') . ':' . sha1((string) $admissionNumber);
    }

    private function getDbCache($admissionNumber, $schoolId)
    {
        if (!Schema::hasTable('finance_sync_cache')) {
            return null;
        }

        $row = DB::table('finance_sync_cache')
            ->where('admission_number', (string) $admissionNumber)
            ->where(function ($query) use ($schoolId) {
                $schoolId ? $query->where('school_id', $schoolId) : $query->whereNull('school_id');
            })
            ->first();

        if (!$row) {
            return null;
        }

        return [
            'summary' => json_decode((string) $row->summary, true) ?: [],
            'invoices' => json_decode((string) $row->invoices, true) ?: [],
            'source' => (string) $row->source,
            'fetched_at' => (string) $row->fetched_at,
            'expires_at' => (string) $row->expires_at,
        ];
    }

    private function storeDbCache($admissionNumber, $schoolId, array $payload)
    {
        if (!Schema::hasTable('finance_sync_cache')) {
            return;
        }

        $data = [
            'school_id' => $schoolId,
            'admission_number' => (string) $admissionNumber,
            'summary' => json_encode($payload['summary'] ?? []),
            'invoices' => json_encode($payload['invoices'] ?? []),
            'source' => (string) ($payload['source'] ?? 'cache'),
            'fetched_at' => $payload['fetched_at'] ?? now(),
            'expires_at' => $payload['expires_at'] ?? now()->addSeconds($this->ttl()),
            'updated_at' => now(),
        ];

        $existing = DB::table('finance_sync_cache')
            ->where('admission_number', (string) $admissionNumber)
            ->where(function ($query) use ($schoolId) {
                $schoolId ? $query->where('school_id', $schoolId) : $query->whereNull('school_id');
            })
            ->first();

        if ($existing) {
            DB::table('finance_sync_cache')->where('id', $existing->id)->update($data);
            return;
        }

        $data['created_at'] = now();
        DB::table('finance_sync_cache')->insert($data);
    }

    private function isExpired(array $payload)
    {
        $expiresAt = data_get($payload, 'expires_at');

        if (!$expiresAt) {
            return true;
        }

        try {
            return now()->greaterThan(\Illuminate\Support\Carbon::parse($expiresAt));
        } catch (\Throwable $e) {
            return true;
        }
    }
}
