<?php

namespace App\Http\Controllers;

use App\Services\SupabaseService;
use Illuminate\Http\Request;
use Google\Client as GoogleClient;
use Google\Service\Sheets as GoogleSheets;
use Google\Service\Sheets\ValueRange;
use Google\Service\Sheets\BatchUpdateValuesRequest;

/**
 * Fase 2: input data instansi lewat tabel yang bisa diedit (data ke Supabase,
 * lalu disinkronkan satu arah ke Google Sheets).
 *
 * Generik: kolom dibaca dari instansi_field, data dari instansi_data.
 */
class DataEntryController extends Controller
{
    /** Nama bulan Indonesia, index 1..12. */
    public const BULAN = [
        1 => 'Januari', 2 => 'Februari', 3 => 'Maret', 4 => 'April',
        5 => 'Mei', 6 => 'Juni', 7 => 'Juli', 8 => 'Agustus',
        9 => 'September', 10 => 'Oktober', 11 => 'November', 12 => 'Desember',
    ];

    public function __construct(private SupabaseService $supabase)
    {
    }

    public function edit(Request $request, string $slug)
    {
        $ref = $this->supabase->getInstansiRefBySlug($slug);
        if (!$ref) {
            abort(404, 'Instansi tidak ditemukan.');
        }

        $tahun = (int) ($request->query('tahun') ?: date('Y'));
        $fields = $this->supabase->getFields($ref['id']);
        $data = $this->supabase->getData($ref['id'], $tahun);

        return view('data-entry', [
            'ref' => $ref,
            'slug' => $slug,
            'tahun' => $tahun,
            'fields' => $fields,
            'data' => $data,
            'bulanList' => self::BULAN,
        ]);
    }

    public function save(Request $request, string $slug)
    {
        $ref = $this->supabase->getInstansiRefBySlug($slug);
        if (!$ref) {
            abort(404, 'Instansi tidak ditemukan.');
        }

        $tahun = (int) ($request->input('tahun') ?: date('Y'));
        $fields = $this->supabase->getFields($ref['id']);
        $fieldKeys = array_column($fields, 'field_key');

        $input = $request->input('data', []); // data[bulan][field_key]

        $rows = [];
        foreach ($input as $bulan => $perField) {
            $bulan = (int) $bulan;
            if ($bulan < 1 || $bulan > 12) {
                continue;
            }
            foreach ($perField as $key => $nilai) {
                if (!in_array($key, $fieldKeys, true)) {
                    continue;
                }
                $rows[] = [
                    'instansi_ref_id' => $ref['id'],
                    'tahun' => $tahun,
                    'bulan' => $bulan,
                    'field_key' => $key,
                    'nilai' => $this->normalizeNumber($nilai),
                ];
            }
        }

        // 1) Simpan ke Supabase (sumber utama). Kalau gagal, batalkan.
        try {
            $this->supabase->upsertData($rows);
        } catch (\Throwable $e) {
            return back()->with('error', 'Gagal menyimpan: ' . $e->getMessage());
        }

        // 2) Sinkron ke Google Sheets (best-effort). Kalau gagal (mis. service
        //    account belum punya akses Editor), data tetap tersimpan di Supabase.
        $syncWarning = null;
        try {
            $this->syncToSheet($ref, $tahun, $fields);
        } catch (\Throwable $e) {
            $syncWarning = $this->sheetErrorMessage($e);
        }

        $redirect = redirect()->route('data.edit', ['slug' => $slug, 'tahun' => $tahun]);

        if ($syncWarning) {
            return $redirect->with('success', "Data $tahun tersimpan di Supabase.")
                ->with('warning', 'Namun gagal sinkron ke Google Sheets: ' . $syncWarning);
        }

        return $redirect->with('success', "Data $tahun berhasil disimpan dan disinkronkan ke Google Sheets.");
    }

    /**
     * Sinkronkan data Supabase → Google Sheets (satu arah), per kolom.
     * Mendukung layout 'bulan_baris' (bulan jadi baris).
     */
    private function syncToSheet(array $ref, int $tahun, array $fields): void
    {
        $spreadsheetId = $ref['spreadsheet_id'] ?? null;
        if (!$spreadsheetId) {
            return; // tidak ada spreadsheet → lewati sync
        }

        $sheetName = !empty($ref['sheet_name']) ? $ref['sheet_name'] : (string) $tahun;
        $startRow = (int) ($ref['sheet_start_row'] ?? 2);
        $data = $this->supabase->getData($ref['id'], $tahun);

        $client = new GoogleClient();
        $client->setAuthConfig(storage_path('app/credentials.json'));
        $client->addScope(GoogleSheets::SPREADSHEETS);
        $sheets = new GoogleSheets($client);

        $valueRanges = [];
        foreach ($fields as $f) {
            $col = $f['sheet_col'] ?? null;
            if (!$col) {
                continue; // kolom tanpa pemetaan sheet → tidak disync
            }
            $column = [];
            for ($bulan = 1; $bulan <= 12; $bulan++) {
                $column[] = [$data[$bulan][$f['field_key']] ?? ''];
            }
            $range = "{$sheetName}!{$col}{$startRow}:{$col}" . ($startRow + 11);
            $valueRanges[] = new ValueRange(['range' => $range, 'values' => $column]);
        }

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

        $body = new BatchUpdateValuesRequest([
            'valueInputOption' => 'RAW',
            'data' => $valueRanges,
        ]);
        $sheets->spreadsheets_values->batchUpdate($spreadsheetId, $body);
    }

    /** Pesan ringkas untuk error Google Sheets (mis. izin). */
    private function sheetErrorMessage(\Throwable $e): string
    {
        $msg = $e->getMessage();
        if (str_contains($msg, 'PERMISSION_DENIED') || str_contains($msg, 'does not have permission')) {
            return 'service account belum punya akses Editor ke spreadsheet ini.';
        }
        return \Illuminate\Support\Str::limit($msg, 120);
    }

    /** Ubah input "2.517,42" / "2517.42" / "" menjadi float|null. */
    private function normalizeNumber($raw): ?float
    {
        if ($raw === null) {
            return null;
        }
        $s = trim((string) $raw);
        if ($s === '' || $s === '-') {
            return null;
        }
        // Jika ada koma → anggap format Indonesia (titik ribuan, koma desimal).
        if (str_contains($s, ',')) {
            $s = str_replace('.', '', $s);
            $s = str_replace(',', '.', $s);
        }
        return is_numeric($s) ? (float) $s : null;
    }
}
