<?php

namespace App\Http\Controllers;

use App\Models\Member;
use App\Models\Season;
use App\Models\Team;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Str;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing;

class ImportController extends Controller
{
    public function teams(Request $request): JsonResponse
    {
        $request->validate([
            'file' => ['required', 'file', 'mimes:xlsx,xls', 'max:10240'],
        ]);

        $season = Season::active() ?? Season::firstOrCreate(['is_active' => true], ['name' => 'Season']);

        try {
            $spreadsheet = IOFactory::load($request->file('file')->getRealPath());
        } catch (\Throwable $e) {
            return response()->json(['message' => 'Could not parse the Excel file: '.$e->getMessage()], 422);
        }

        $sheet = $spreadsheet->getActiveSheet();
        $rows  = $sheet->toArray(null, true, true, true); // assoc: ['A' => val, 'B' => val, ...]

        // Map first-row header → column letter (case-insensitive)
        $header = array_map(fn ($v) => strtolower(trim((string) $v)), reset($rows) ?: []);
        $colFor = function (array $names) use ($header) {
            foreach ($header as $letter => $h) {
                foreach ($names as $n) {
                    if ($h === strtolower($n)) return $letter;
                }
            }
            return null;
        };
        $colTeam    = $colFor(['team_name', 'team']);
        $colMember  = $colFor(['member_name', 'member']);
        $colCaptain = $colFor(['is_captain', 'captain']);
        $colLogo    = $colFor(['team_logo', 'logo']);

        if (!$colTeam || !$colMember) {
            return response()->json(['message' => 'Missing required columns: team_name, member_name'], 422);
        }

        // Extract embedded images keyed by their row number
        $imagesByRow = [];
        foreach ($sheet->getDrawingCollection() as $drawing) {
            $coords = $drawing->getCoordinates(); // e.g. "D2"
            if (!preg_match('/([A-Z]+)(\d+)/', $coords, $m)) continue;
            $row = (int) $m[2];
            if ($row < 2) continue;

            $bytes = null;
            $ext   = 'png';
            try {
                if ($drawing instanceof MemoryDrawing) {
                    ob_start();
                    call_user_func($drawing->getRenderingFunction(), $drawing->getImageResource());
                    $bytes = ob_get_clean();
                    $ext   = match ($drawing->getMimeType()) {
                        MemoryDrawing::MIMETYPE_PNG => 'png',
                        MemoryDrawing::MIMETYPE_JPEG, MemoryDrawing::MIMETYPE_DEFAULT => 'jpg',
                        MemoryDrawing::MIMETYPE_GIF => 'gif',
                        default => 'png',
                    };
                } elseif ($drawing instanceof Drawing) {
                    $ext = strtolower($drawing->getExtension() ?: 'png');

                    // Newer PhpSpreadsheet exposes raw bytes directly
                    if (method_exists($drawing, 'getImageContents')) {
                        $bytes = $drawing->getImageContents();
                    }
                    // Fallback: read the underlying path (may be a zip:// stream)
                    if (!$bytes) {
                        $path = $drawing->getPath();
                        if ($path) {
                            // Try direct read
                            $bytes = @file_get_contents($path);
                            // Fallback: copy via stream (handles zip://) then read
                            if (!$bytes) {
                                $tmp = tempnam(sys_get_temp_dir(), 'xlimg_').'.'.$ext;
                                if (@copy($path, $tmp) && filesize($tmp) > 0) {
                                    $bytes = file_get_contents($tmp);
                                }
                                @unlink($tmp);
                            }
                        }
                    }
                }
                if ($bytes) $imagesByRow[$row] = ['bytes' => $bytes, 'ext' => $ext];
            } catch (\Throwable $e) {
                // skip this drawing
            }
        }

        // Last-resort fallback: parse the xlsx as a zip and pull embedded images
        // mapped via drawing XML cell anchors.
        if (empty($imagesByRow) && $colLogo) {
            $imagesByRow = $this->extractImagesFromXlsxZip($request->file('file')->getRealPath());
        }

        $created = ['teams' => 0, 'members' => 0];
        $updated = ['teams' => 0, 'members' => 0, 'logos' => 0, 'captains' => 0];
        $skipped = [];

        $teamsByName = [];

        $rowIdx = 0;
        foreach ($rows as $rowNo => $row) {
            $rowIdx++;
            if ($rowIdx === 1) continue; // header

            $teamName   = trim((string) ($row[$colTeam] ?? ''));
            $memberName = trim((string) ($row[$colMember] ?? ''));
            $isCaptain  = $colCaptain ? $this->truthy($row[$colCaptain] ?? null) : false;

            if ($teamName === '' || $memberName === '') {
                $skipped[] = "Row {$rowNo}: missing team or member";
                continue;
            }

            // Find/create team in the active season
            $team = $teamsByName[$teamName] ?? null;
            if (!$team) {
                $team = Team::where('season_id', $season->id)->where('name', $teamName)->first();
                if ($team) {
                    $updated['teams']++;
                } else {
                    $team = Team::create([
                        'season_id'       => $season->id,
                        'slug'            => Str::slug($teamName).'-'.Str::random(4),
                        'name'            => $teamName,
                        'crest_shape'     => 'shield',
                        'crest_motif'     => 'star',
                        'crest_primary'   => '#b21e2e',
                        'crest_secondary' => '#0e0e10',
                        'crest_initials'  => strtoupper(substr($teamName, 0, 2)),
                    ]);
                    $created['teams']++;
                }
                $teamsByName[$teamName] = $team;
            }

            // Save embedded logo if present in this row
            if ($colLogo && isset($imagesByRow[$rowNo])) {
                $img = $imagesByRow[$rowNo];
                $path = 'team-logos/'.Str::random(40).'.'.$img['ext'];
                Storage::disk('public')->put($path, $img['bytes']);
                if ($team->logo_path) {
                    Storage::disk('public')->delete($team->logo_path);
                }
                $team->logo_path = $path;
                $team->crest_mode = 'logo';
                $team->save();
                $updated['logos']++;
            }

            // Find/create member (unique on team_id + name)
            $member = Member::where('team_id', $team->id)->where('name', $memberName)->first();
            if (!$member) {
                $member = Member::create(['team_id' => $team->id, 'name' => $memberName]);
                $created['members']++;
            }

            // Set captain
            if ($isCaptain && $team->captain_id !== $member->id) {
                $team->captain_id = $member->id;
                $team->save();
                $updated['captains']++;
            }
        }

        return response()->json([
            'message' => 'Import complete',
            'summary' => compact('created', 'updated', 'skipped'),
        ]);
    }

    private function truthy($v): bool
    {
        if (is_bool($v)) return $v;
        $s = strtolower(trim((string) $v));
        return in_array($s, ['1', 'true', 'yes', 'y', 't', '✓', 'captain'], true);
    }

    /**
     * Parse an xlsx as a zip and pull embedded images mapped to their cell row.
     * @return array<int, array{bytes:string, ext:string}>
     */
    private function extractImagesFromXlsxZip(string $xlsxPath): array
    {
        $result = [];
        $zip = new \ZipArchive();
        if ($zip->open($xlsxPath) !== true) return $result;

        try {
            // Find sheet drawing XML(s) — typically xl/drawings/drawing*.xml
            for ($i = 0; $i < $zip->numFiles; $i++) {
                $name = $zip->getNameIndex($i);
                if (!preg_match('#^xl/drawings/drawing\d+\.xml$#', $name)) continue;

                $relsName = 'xl/drawings/_rels/'.basename($name).'.rels';
                $relsXml  = $zip->getFromName($relsName);
                if (!$relsXml) continue;
                $rels = simplexml_load_string($relsXml);
                $relMap = []; // id => media path
                foreach ($rels->Relationship as $r) {
                    $attr = $r->attributes();
                    $relMap[(string) $attr['Id']] = (string) $attr['Target'];
                }

                $drawXml = $zip->getFromName($name);
                if (!$drawXml) continue;
                $draw = simplexml_load_string($drawXml);
                if (!$draw) continue;
                $draw->registerXPathNamespace('xdr', 'http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing');
                $draw->registerXPathNamespace('r',   'http://schemas.openxmlformats.org/officeDocument/2006/relationships');
                $draw->registerXPathNamespace('a',   'http://schemas.openxmlformats.org/drawingml/2006/main');

                foreach ($draw->xpath('//xdr:oneCellAnchor | //xdr:twoCellAnchor') as $anchor) {
                    $rowNode = $anchor->xpath('xdr:from/xdr:row');
                    if (!$rowNode) continue;
                    $row = ((int) $rowNode[0]) + 1; // xdr rows are 0-based

                    $blip = $anchor->xpath('.//a:blip');
                    if (!$blip) continue;
                    $attrs = $blip[0]->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships');
                    $rid = (string) $attrs['embed'];
                    if (!$rid || empty($relMap[$rid])) continue;

                    $target = $relMap[$rid]; // e.g. "../media/image1.png"
                    $mediaPath = 'xl/'.ltrim(preg_replace('#^\.\./#', '', $target), '/');
                    $bytes = $zip->getFromName($mediaPath);
                    if ($bytes === false) continue;
                    $ext = strtolower(pathinfo($mediaPath, PATHINFO_EXTENSION) ?: 'png');
                    $result[$row] = ['bytes' => $bytes, 'ext' => $ext];
                }
            }
        } finally {
            $zip->close();
        }

        return $result;
    }
}
