<?php

declare(strict_types=1);

namespace App\Modules\Guest;

use App\Core\Logger;

/**
 * GuestService — Co-ordinates file validation, mapping, duplicate checks, and token generation.
 *
 * Implements row-level isolation and logs execution statistics.
 *
 * @package Inveetaire
 * @ref     PILOT-002
 */
class GuestService
{
    private GuestModel $model;
    private const MAX_ROW_LIMIT = 500; // Standard Pilot launch safety threshold

    public function __construct()
    {
        $this->model = new GuestModel();
    }

    /**
     * Process Guest list file upload and import valid records.
     *
     * @param int    $workspaceId
     * @param string $tempFilePath
     * @param string $originalFilename
     * @return array Summary of import execution.
     */
    public function importGuests(int $workspaceId, string $tempFilePath, string $originalFilename): array
    {
        $startTime = microtime(true);
        
        $ext = strtolower(pathinfo($originalFilename, PATHINFO_EXTENSION));
        if (!in_array($ext, ['csv', 'xlsx'], true)) {
            throw new \InvalidArgumentException("Invalid file type. Only CSV and XLSX files are supported.");
        }

        $importer = new GuestImporter();
        $rawRows = $importer->parseFile($tempFilePath, $ext);

        if (empty($rawRows)) {
            throw new \RuntimeException("The uploaded file is empty or could not be parsed.");
        }

        // Limit check
        $totalDataRows = count($rawRows) - 1; // subtract header row
        if ($totalDataRows > self::MAX_ROW_LIMIT) {
            throw new \RuntimeException("File exceeds the maximum limit of " . self::MAX_ROW_LIMIT . " rows (contains {$totalDataRows} rows).");
        }

        // 1. Identify columns from the header row
        $headers = array_shift($rawRows);
        $headers = array_map(function ($h) {
            return strtolower(trim((string) $h));
        }, $headers);

        $nameIdx      = -1;
        $phoneIdx     = -1;
        $addressIdx   = -1;
        $categoryIdx  = -1;
        $groupIdx     = -1;
        $seatIdx      = -1;
        $paxIdx       = -1;
        $honorificIdx = -1;

        foreach ($headers as $idx => $header) {
            if (in_array($header, ['guest name', 'name', 'full name', 'nama', 'guest_name', 'full_name'])) {
                $nameIdx = $idx;
            } elseif (in_array($header, ['phone number', 'phone', 'no hp', 'whatsapp', 'phone_number'])) {
                $phoneIdx = $idx;
            } elseif (in_array($header, ['address', 'alamat'])) {
                $addressIdx = $idx;
            } elseif (in_array($header, ['category', 'kategori'])) {
                $categoryIdx = $idx;
            } elseif (in_array($header, ['group', 'kelompok', 'guest_group', 'group_name'])) {
                $groupIdx = $idx;
            } elseif (in_array($header, ['seat number', 'seat', 'no kursi', 'seat_number', 'kursi'])) {
                $seatIdx = $idx;
            } elseif (in_array($header, ['pax', 'invited pax', 'jumlah', 'invited_pax', 'jumlah_pax'])) {
                $paxIdx = $idx;
            } elseif (in_array($header, ['prefix', 'honorific', 'gelar', 'title'])) {
                $honorificIdx = $idx;
            }
        }

        if ($nameIdx === -1) {
            throw new \RuntimeException("Required header 'Guest Name' is missing. Please ensure your file has a column for guest names.");
        }

        // Fetch current count & limit
        $currentCount = $this->model->getGuestsCount($workspaceId);
        $db = \App\Core\Database::getConnection();
        $stmt = $db->prepare('
            SELECT COALESCE(w.custom_guest_limit, cp.max_guests) as guest_limit 
            FROM workspaces w
            JOIN commercial_plans cp ON cp.id = w.commercial_plan_id
            WHERE w.id = :workspace_id
        ');
        $stmt->execute(['workspace_id' => $workspaceId]);
        $limit = (int) ($stmt->fetchColumn() ?: 1000);

        // Initialize execution metrics
        $stats = [
            'total_rows'  => $totalDataRows,
            'imported'    => 0,
            'skipped'     => 0,
            'duplicate'   => 0,
            'errors'      => [],
            'time_ms'     => 0
        ];

        $validator = new GuestValidator();
        $seenInFile = []; // Track duplicate rows inside the uploaded file itself

        foreach ($rawRows as $lineNo => $row) {
            // Adjust row number for 1-based display (accounting for skipped headers)
            $displayRow = $lineNo + 2; 

            if (empty($row) || (count($row) === 1 && trim((string)$row[0]) === '')) {
                // Skip empty row silently
                $stats['skipped']++;
                continue;
            }

            // Guest limit check
            $rowName = isset($row[$nameIdx]) ? trim((string)$row[$nameIdx]) : 'Unknown';
            if ($currentCount + $stats['imported'] >= $limit) {
                $stats['errors'][] = [
                    'row'    => $displayRow,
                    'name'   => $rowName,
                    'reason' => "Skipped: reached the maximum guest limit of {$limit} allowed under your plan."
                ];
                $stats['skipped']++;
                continue;
            }

            // Extract values safely
            $fullName  = isset($row[$nameIdx]) ? trim((string) $row[$nameIdx]) : '';
            $phone     = ($phoneIdx !== -1 && isset($row[$phoneIdx])) ? trim((string) $row[$phoneIdx]) : '';
            $address   = ($addressIdx !== -1 && isset($row[$addressIdx])) ? trim((string) $row[$addressIdx]) : '';
            $category  = ($categoryIdx !== -1 && isset($row[$categoryIdx])) ? trim((string) $row[$categoryIdx]) : '';
            $group     = ($groupIdx !== -1 && isset($row[$groupIdx])) ? trim((string) $row[$groupIdx]) : '';
            $seat      = ($seatIdx !== -1 && isset($row[$seatIdx])) ? trim((string) $row[$seatIdx]) : '';
            $honorific = ($honorificIdx !== -1 && isset($row[$honorificIdx])) ? trim((string) $row[$honorificIdx]) : '';
            
            // Pax parsing: default to 1 if empty or not provided
            $paxRaw = ($paxIdx !== -1 && isset($row[$paxIdx]) && $row[$paxIdx] !== '') ? trim((string) $row[$paxIdx]) : '1';

            // Validate row contents
            $mappedRow = [
                'honorific'    => $honorific,
                'full_name'    => $fullName,
                'phone_number' => $phone,
                'invited_pax'  => $paxRaw,
                'guest_group'  => $group
            ];

            $rowErrors = $validator->validateRow($mappedRow);
            if (!empty($rowErrors)) {
                $stats['errors'][] = [
                    'row'    => $displayRow,
                    'name'   => $fullName ?: '(Blank)',
                    'reason' => implode(', ', $rowErrors)
                ];
                continue;
            }

            // check duplicate within the uploaded file itself (case-insensitive)
            $normalizedName = strtolower(trim($fullName));
            if (isset($seenInFile[$normalizedName])) {
                $stats['duplicate']++;
                $stats['errors'][] = [
                    'row'    => $displayRow,
                    'name'   => $fullName,
                    'reason' => "Duplicate entry found in the uploaded file (matches row {$seenInFile[$normalizedName]})."
                ];
                continue;
            }
            $seenInFile[$normalizedName] = $displayRow;

            // check duplicate in database for this workspace
            if ($this->model->guestExistsByName($workspaceId, $fullName)) {
                $stats['duplicate']++;
                $stats['errors'][] = [
                    'row'    => $displayRow,
                    'name'   => $fullName,
                    'reason' => "Guest already exists in this workspace."
                ];
                continue;
            }

            // Generate canonical unique 5-character guest_code via central generator
            try {
                $token = $this->generateUniqueGuestCode($workspaceId);
            } catch (\RuntimeException $ex) {
                $stats['errors'][] = [
                    'row'    => $displayRow,
                    'name'   => $fullName,
                    'reason' => $ex->getMessage()
                ];
                continue;
            }

            // Package metadata info into notes column
            $notesArr = [];
            if ($category !== '') {
                $notesArr[] = "Category: {$category}";
            }
            if ($seat !== '') {
                $notesArr[] = "Seat Number: {$seat}";
            }
            if ($address !== '') {
                $notesArr[] = "Address: {$address}";
            }
            $notesField = implode(" | ", $notesArr);

            // Database insert
            try {
                $this->model->insertGuest($workspaceId, [
                    'guest_code'   => $token,
                    'honorific'    => $honorific,
                    'full_name'    => $fullName,
                    'side'         => 'couple',
                    'guest_group'  => $validator->resolveGroupSlug($group) ?: 'others',
                    'invited_pax'  => (int) $paxRaw,
                    'phone_number' => preg_replace('/[\s\-\(\)]/', '', $phone),
                    'notes'        => $notesField
                ]);
                $stats['imported']++;
            } catch (\Throwable $dbEx) {
                Logger::error("GuestService: database insert failed for row {$displayRow}", [
                    'workspace_id' => $workspaceId,
                    'name'         => $fullName,
                    'error'        => $dbEx->getMessage()
                ]);
                $stats['errors'][] = [
                    'row'    => $displayRow,
                    'name'   => $fullName,
                    'reason' => "Database write error: " . $dbEx->getMessage()
                ];
            }
        }

        $stats['time_ms'] = (int) round((microtime(true) - $startTime) * 1000);
        return $stats;
    }

    /**
     * Parse and validate the import file, compiling a preview validation report.
     *
     * @param int    $workspaceId
     * @param string $tempFilePath
     * @param string $originalFilename
     * @return array
     */
    public function validateImportFile(int $workspaceId, string $tempFilePath, string $originalFilename): array
    {
        $ext = strtolower(pathinfo($originalFilename, PATHINFO_EXTENSION));
        if (!in_array($ext, ['csv', 'xlsx'], true)) {
            throw new \InvalidArgumentException("Invalid file type. Only CSV and XLSX files are supported.");
        }

        $importer = new GuestImporter();
        $rawRows = $importer->parseFile($tempFilePath, $ext);

        if (empty($rawRows)) {
            throw new \RuntimeException("The uploaded file is empty or could not be parsed.");
        }

        // Header Row Identification
        $headers = array_shift($rawRows);
        $headers = array_map(function ($h) {
            return strtolower(trim((string) $h));
        }, $headers);

        $nameIdx      = -1;
        $phoneIdx     = -1;
        $addressIdx   = -1;
        $categoryIdx  = -1;
        $groupIdx     = -1;
        $seatIdx      = -1;
        $paxIdx       = -1;
        $honorificIdx = -1;

        foreach ($headers as $idx => $header) {
            if (in_array($header, ['guest name', 'name', 'full name', 'nama', 'guest_name', 'full_name'])) {
                $nameIdx = $idx;
            } elseif (in_array($header, ['phone number', 'phone', 'no hp', 'whatsapp', 'phone_number'])) {
                $phoneIdx = $idx;
            } elseif (in_array($header, ['address', 'alamat'])) {
                $addressIdx = $idx;
            } elseif (in_array($header, ['category', 'kategori'])) {
                $categoryIdx = $idx;
            } elseif (in_array($header, ['group', 'kelompok', 'guest_group', 'group_name'])) {
                $groupIdx = $idx;
            } elseif (in_array($header, ['seat number', 'seat', 'no kursi', 'seat_number', 'kursi'])) {
                $seatIdx = $idx;
            } elseif (in_array($header, ['pax', 'invited pax', 'jumlah', 'invited_pax', 'jumlah_pax'])) {
                $paxIdx = $idx;
            } elseif (in_array($header, ['prefix', 'honorific', 'gelar', 'title'])) {
                $honorificIdx = $idx;
            }
        }

        if ($nameIdx === -1) {
            throw new \RuntimeException("Required header 'Guest Name' is missing. Please ensure your file has a column for guest names.");
        }

        $validator = new GuestValidator();
        $seenNamesInFile = [];
        $seenPhonesInFile = [];
        
        $validRows = [];
        $report = [];
        $summary = [
            'total_rows' => count($rawRows),
            'valid'      => 0,
            'invalid'    => 0,
            'duplicates' => 0
        ];

        foreach ($rawRows as $lineNo => $row) {
            $displayRow = $lineNo + 2; 

            if (empty($row) || (count($row) === 1 && trim((string)$row[0]) === '')) {
                continue;
            }

            // Extract values
            $fullName  = isset($row[$nameIdx]) ? trim((string) $row[$nameIdx]) : '';
            $phone     = ($phoneIdx !== -1 && isset($row[$phoneIdx])) ? trim((string) $row[$phoneIdx]) : '';
            $address   = ($addressIdx !== -1 && isset($row[$addressIdx])) ? trim((string) $row[$addressIdx]) : '';
            $category  = ($categoryIdx !== -1 && isset($row[$categoryIdx])) ? trim((string) $row[$categoryIdx]) : '';
            $group     = ($groupIdx !== -1 && isset($row[$groupIdx])) ? trim((string) $row[$groupIdx]) : '';
            $seat      = ($seatIdx !== -1 && isset($row[$seatIdx])) ? trim((string) $row[$seatIdx]) : '';
            $honorific = ($honorificIdx !== -1 && isset($row[$honorificIdx])) ? trim((string) $row[$honorificIdx]) : '';
            $paxRaw    = ($paxIdx !== -1 && isset($row[$paxIdx]) && $row[$paxIdx] !== '') ? trim((string) $row[$paxIdx]) : '1';

            $mappedRow = [
                'honorific'    => $honorific,
                'full_name'    => $fullName,
                'phone_number' => $phone,
                'invited_pax'  => $paxRaw,
                'guest_group'  => $group
            ];

            // 1. Core Format Validation
            $rowErrors = $validator->validateRow($mappedRow);
            
            // 2. Duplicate Checks
            $normalizedName = strtolower($fullName);
            $normalizedPhone = preg_replace('/[\s\-\(\)]/', '', $phone);
            
            $status = 'valid';
            $reason = '';

            if (!empty($rowErrors)) {
                $status = 'invalid';
                $reason = implode(', ', $rowErrors);
            } else {
                // Name duplicate in file
                if ($fullName !== '' && isset($seenNamesInFile[$normalizedName])) {
                    $status = 'duplicate';
                    $reason = "Duplicate name in file (matches row {$seenNamesInFile[$normalizedName]})";
                }
                // Phone duplicate in file
                elseif ($normalizedPhone !== '' && isset($seenPhonesInFile[$normalizedPhone])) {
                    $status = 'duplicate';
                    $reason = "Duplicate phone in file (matches row {$seenPhonesInFile[$normalizedPhone]})";
                }
                // Name duplicate in database
                elseif ($this->model->guestExistsByName($workspaceId, $fullName)) {
                    $status = 'duplicate';
                    $reason = "Guest already exists in database";
                }
                // Phone duplicate in database
                elseif ($normalizedPhone !== '' && $this->model->guestExistsByPhone($workspaceId, $normalizedPhone)) {
                    $status = 'duplicate';
                    $reason = "Phone number already exists in database";
                }
            }

            if ($status === 'valid') {
                $summary['valid']++;
                if ($fullName !== '') {
                    $seenNamesInFile[$normalizedName] = $displayRow;
                }
                if ($normalizedPhone !== '') {
                    $seenPhonesInFile[$normalizedPhone] = $displayRow;
                }
                
                // Pack metadata details in notes
                $notesArr = [];
                if ($category !== '') $notesArr[] = "Category: {$category}";
                if ($seat !== '')     $notesArr[] = "Seat: {$seat}";
                if ($address !== '')  $notesArr[] = "Address: {$address}";
                $notes = implode(" | ", $notesArr);

                $validRows[] = [
                    'honorific'    => $honorific,
                    'full_name'    => $fullName,
                    'phone_number' => preg_replace('/[\s\-\(\)]/', '', $phone),
                    'invited_pax'  => (int) $paxRaw,
                    'guest_group'  => $validator->resolveGroupSlug($group) ?: 'others',
                    'notes'        => $notes
                ];
            } elseif ($status === 'duplicate') {
                $summary['duplicates']++;
            } else {
                $summary['invalid']++;
            }

            $report[] = [
                'row'    => $displayRow,
                'name'   => $fullName ?: '(Blank)',
                'phone'  => $phone ?: '(None)',
                'status' => $status,
                'reason' => $reason
            ];
        }

        return [
            'valid_rows' => $validRows,
            'report'     => $report,
            'summary'    => $summary
        ];
    }

    /**
     * Finalize valid rows by inserting them into the database.
     *
     * @param int   $workspaceId
     * @param array $validRows
     * @return int Count of imported guests.
     */
    public function finalizeImport(int $workspaceId, array $validRows): int
    {
        $importedCount = 0;
        foreach ($validRows as $row) {
            $token = $this->generateUniqueGuestCode($workspaceId);
            
            if ($token === '') {
                continue;
            }
            
            $this->model->insertGuest($workspaceId, [
                'guest_code'   => $token,
                'honorific'    => $row['honorific'],
                'full_name'    => $row['full_name'],
                'side'         => 'couple',
                'guest_group'  => $row['guest_group'],
                'invited_pax'  => $row['invited_pax'],
                'phone_number' => $row['phone_number'],
                'notes'        => $row['notes']
            ]);
            $importedCount++;
        }

        return $importedCount;
    }

    /**
     * Centralized Canonical Generator for persisted Guest entities.
     * Generates a cryptographically secure 5-character uppercase alphanumeric code (^[A-Z0-9]{5}$).
     * Checks uniqueness per workspace_id and retries on collision.
     * Throws \RuntimeException if a unique code cannot be generated after retry limit.
     *
     * @param int $workspaceId
     * @param int $maxAttempts Max collision retries (default 10)
     * @return string Generated 5-character guest_code.
     * @throws \RuntimeException If unique code generation fails after max attempts.
     */
    public function generateUniqueGuestCode(int $workspaceId, int $maxAttempts = 10): string
    {
        $charset = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
        $charsetLen = strlen($charset) - 1;
        for ($loop = 0; $loop < $maxAttempts; $loop++) {
            $candidate = '';
            for ($i = 0; $i < 5; $i++) {
                $candidate .= $charset[random_int(0, $charsetLen)];
            }
            if (!$this->model->guestCodeExists($workspaceId, $candidate)) {
                return $candidate;
            }
        }

        throw new \RuntimeException("Failed to generate a unique 5-character guest code after {$maxAttempts} attempts for workspace ID {$workspaceId}.");
    }
}
