<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class CompetencyController extends Controller
{
    /**
     * Get all units with their competencies
     */
    public function getUnits(Request $request)
    {
        $language = $request->query('language'); // 'en', 'fr', or null
        
        // Map language parameter to column suffix
        $langSuffix = null;
        if ($language === 'English' || $language === 'en') {
            $langSuffix = 'en';
        } elseif ($language === 'French' || $language === 'fr') {
            $langSuffix = 'fr';
        }

        $unitsQuery = DB::table('competency_units')
            ->orderBy('order');
        
        // Filter units that have content in the requested language
        if ($langSuffix) {
            $unitsQuery->whereNotNull("title_{$langSuffix}")
                       ->where("title_{$langSuffix}", '!=', '');
        }
        
        $units = $unitsQuery->get();

        foreach ($units as $unit) {
            // Transform title based on language
            if ($langSuffix) {
                $unit->title = $unit->{"title_{$langSuffix}"} ?? $unit->title;
            }
            
            // Get main competencies (those without parent_id)
            $competenciesQuery = DB::table('competencies')
                ->where('unit_id', $unit->id)
                ->whereNull('parent_id')
                ->orderBy('order');
            
            // Filter competencies that have content in the requested language
            if ($langSuffix) {
                $competenciesQuery->whereNotNull("description_{$langSuffix}")
                                  ->where("description_{$langSuffix}", '!=', '');
            }
            
            $competencies = $competenciesQuery->get();

            // For each main competency, get its sub-competencies
            foreach ($competencies as $competency) {
                // Transform description based on language
                if ($langSuffix) {
                    $competency->description = $competency->{"description_{$langSuffix}"} ?? $competency->description;
                }
                
                $subCompetenciesQuery = DB::table('competencies')
                    ->where('parent_id', $competency->id)
                    ->orderBy('order');
                
                // Filter sub-competencies by language
                if ($langSuffix) {
                    $subCompetenciesQuery->whereNotNull("description_{$langSuffix}")
                                         ->where("description_{$langSuffix}", '!=', '');
                }
                
                $subCompetencies = $subCompetenciesQuery->get();
                
                // Transform sub-competency descriptions
                if ($langSuffix) {
                    foreach ($subCompetencies as $sub) {
                        $sub->description = $sub->{"description_{$langSuffix}"} ?? $sub->description;
                    }
                }
                
                $competency->sub_competencies = $subCompetencies;
            }

            $unit->competencies = $competencies;
        }

        return response()->json($units);
    }

    /**
     * Get a single unit
     */
    public function getUnit($id)
    {
        $unit = DB::table('competency_units')->find($id);
        
        if (!$unit) {
            return response()->json(['error' => 'Unit not found'], 404);
        }

        $unit->competencies = DB::table('competencies')
            ->where('unit_id', $unit->id)
            ->orderBy('order')
            ->get();

        return response()->json($unit);
    }

    /**
     * Create a new unit
     */
    public function createUnit(Request $request)
    {
        $validated = $request->validate([
            'title_en' => 'required_without:title_fr|nullable|string|max:255',
            'title_fr' => 'required_without:title_en|nullable|string|max:255',
            'order' => 'nullable|integer',
        ]);

        // Get the max order if not provided
        if (!isset($validated['order'])) {
            $maxOrder = DB::table('competency_units')->max('order');
            $validated['order'] = ($maxOrder ?? -1) + 1;
        }

        // Remove null values to avoid database errors
        $validated = array_filter($validated, fn($value) => $value !== null);

        $validated['created_at'] = now();
        $validated['updated_at'] = now();

        $id = DB::table('competency_units')->insertGetId($validated);

        return response()->json([
            'message' => 'Unit created successfully',
            'id' => $id
        ], 201);
    }

    /**
     * Update a unit
     */
    public function updateUnit(Request $request, $id)
    {
        $validated = $request->validate([
            'title_en' => 'required_without:title_fr|nullable|string|max:255',
            'title_fr' => 'required_without:title_en|nullable|string|max:255',
            'order' => 'nullable|integer',
        ]);

        // Remove null values to avoid database errors
        $validated = array_filter($validated, fn($value) => $value !== null);

        $validated['updated_at'] = now();

        DB::table('competency_units')
            ->where('id', $id)
            ->update($validated);

        return response()->json(['message' => 'Unit updated successfully']);
    }

    /**
     * Delete a unit
     */
    public function deleteUnit($id)
    {
        // Delete all competencies in this unit first
        DB::table('competencies')->where('unit_id', $id)->delete();
        
        // Delete the unit
        DB::table('competency_units')->where('id', $id)->delete();

        return response()->json(['message' => 'Unit and related competencies deleted successfully']);
    }

    /**
     * Get all competencies
     */
    public function getCompetencies()
    {
        $competencies = DB::table('competencies')
            ->orderBy('unit_id')
            ->orderBy('order')
            ->get();

        return response()->json($competencies);
    }

    /**
     * Get competencies by unit
     */
    public function getCompetenciesByUnit($unitId)
    {
        $competencies = DB::table('competencies')
            ->where('unit_id', $unitId)
            ->orderBy('order')
            ->get();

        return response()->json($competencies);
    }

    /**
     * Get competencies by designation
     */
    public function getCompetenciesByDesignation($designation)
    {
        $competencies = DB::table('competencies')
            ->where('designation', $designation)
            ->orderBy('unit_id')
            ->orderBy('order')
            ->get();

        return response()->json($competencies);
    }

    /**
     * Create a new competency
     */
    public function createCompetency(Request $request)
    {
        $validated = $request->validate([
            'unit_id' => 'required|integer|exists:competency_units,id',
            'parent_id' => 'nullable|integer|exists:competencies,id',
            'code' => 'required|string|max:50',
            'description_en' => 'required_without:description_fr|nullable|string',
            'description_fr' => 'required_without:description_en|nullable|string',
            'designation' => 'required|in:RCIC,RISIA',
            'order' => 'nullable|integer',
        ]);

        // Get the max order if not provided
        if (!isset($validated['order'])) {
            $query = DB::table('competencies')
                ->where('unit_id', $validated['unit_id']);
            
            // If it's a sub-competency, get order within parent
            if (isset($validated['parent_id'])) {
                $query->where('parent_id', $validated['parent_id']);
            } else {
                $query->whereNull('parent_id');
            }
            
            $maxOrder = $query->max('order');
            $validated['order'] = ($maxOrder ?? -1) + 1;
        }

        // Remove null values to avoid database errors
        $validated = array_filter($validated, fn($value) => $value !== null);

        $validated['created_at'] = now();
        $validated['updated_at'] = now();

        $id = DB::table('competencies')->insertGetId($validated);

        return response()->json([
            'message' => 'Competency created successfully',
            'id' => $id
        ], 201);
    }

    /**
     * Update a competency
     */
    public function updateCompetency(Request $request, $id)
    {
        $validated = $request->validate([
            'unit_id' => 'required|integer|exists:competency_units,id',
            'parent_id' => 'nullable|integer|exists:competencies,id',
            'code' => 'required|string|max:50',
            'description_en' => 'required_without:description_fr|nullable|string',
            'description_fr' => 'required_without:description_en|nullable|string',
            'designation' => 'required|in:RCIC,RISIA',
            'order' => 'nullable|integer',
        ]);

        // Remove null values to avoid database errors
        $validated = array_filter($validated, fn($value) => $value !== null);

        $validated['updated_at'] = now();

        DB::table('competencies')
            ->where('id', $id)
            ->update($validated);

        return response()->json(['message' => 'Competency updated successfully']);
    }

    /**
     * Delete a competency
     */
    public function deleteCompetency($id)
    {
        DB::table('competencies')->where('id', $id)->delete();

        return response()->json(['message' => 'Competency deleted successfully']);
    }

    /**
     * Reorder units
     */
    public function reorderUnits(Request $request)
    {
        $validated = $request->validate([
            'units' => 'required|array',
            'units.*.id' => 'required|integer',
            'units.*.order' => 'required|integer',
        ]);

        foreach ($validated['units'] as $unit) {
            DB::table('competency_units')
                ->where('id', $unit['id'])
                ->update(['order' => $unit['order'], 'updated_at' => now()]);
        }

        return response()->json(['message' => 'Units reordered successfully']);
    }

    /**
     * Reorder competencies
     */
    public function reorderCompetencies(Request $request)
    {
        $validated = $request->validate([
            'competencies' => 'required|array',
            'competencies.*.id' => 'required|integer',
            'competencies.*.order' => 'required|integer',
        ]);

        foreach ($validated['competencies'] as $competency) {
            DB::table('competencies')
                ->where('id', $competency['id'])
                ->update(['order' => $competency['order'], 'updated_at' => now()]);
        }

        return response()->json(['message' => 'Competencies reordered successfully']);
    }

    /**
     * Download CSV template for bulk upload
     */
    public function downloadTemplate()
    {
        $headers = [
            'Content-Type' => 'text/csv',
            'Content-Disposition' => 'attachment; filename="competency_template.csv"',
        ];

        $columns = [
            'unit_title_en',
            'unit_title_fr',
            'competency_code',
            'competency_description_en',
            'competency_description_fr',
            'competency_designation',
            'sub_competency_code',
            'sub_competency_description_en',
            'sub_competency_description_fr',
            'sub_competency_designation'
        ];

        $callback = function() use ($columns) {
            $file = fopen('php://output', 'w');
            
            // Write header
            fputcsv($file, $columns);
            
            // Example 1: Main competency with sub-competencies (Bilingual RCIC)
            fputcsv($file, [
                'Foundational Knowledge',                                           // unit_title_en
                'Connaissances fondamentales',                                      // unit_title_fr
                '1.1',                                                              // competency_code
                'Demonstrates knowledge of the Canadian legal system.',             // competency_description_en
                'Démontre la connaissance du système juridique canadien.',          // competency_description_fr
                'RCIC',                                                             // competency_designation
                '1.1.1',                                                            // sub_competency_code
                'Summarizes the Canadian justice system.',                          // sub_competency_description_en
                'Résume le système judiciaire canadien.',                           // sub_competency_description_fr
                'RCIC'                                                              // sub_competency_designation
            ]);
            
            fputcsv($file, [
                'Foundational Knowledge',
                'Connaissances fondamentales',
                '1.1',
                'Demonstrates knowledge of the Canadian legal system.',
                'Démontre la connaissance du système juridique canadien.',
                'RCIC',
                '1.1.2',
                'Outlines the historical development of Canadian immigration laws.',
                'Décrit le développement historique des lois canadiennes sur l\'immigration.',
                'RCIC'
            ]);
            
            // Example 2: Different designation for same competency
            fputcsv($file, [
                'Foundational Knowledge test 2',
                '',                                                                 // unit_title_fr (English only)
                '1.2',
                'Demonstrates knowledge of the Canadian legal system.',
                '',                                                                 // competency_description_fr (English only)
                'RISIA',
                '1.2.1',
                'Summarizes the Canadian justice system.',
                '',
                'RISIA'
            ]);
            
            // Example 3: Main competency only (no sub-competency)
            fputcsv($file, [
                'Professional Standards',
                'Normes professionnelles',
                '2.1',
                'Maintains professional conduct.',
                'Maintient une conduite professionnelle.',
                'RCIC',
                '',                                                                 // sub_competency_code (empty)
                '',                                                                 // sub_competency_description_en (empty)
                '',                                                                 // sub_competency_description_fr (empty)
                ''                                                                  // sub_competency_designation (empty)
            ]);
            
            fclose($file);
        };

        return response()->stream($callback, 200, $headers);
    }

    /**
     * Bulk upload competencies from CSV
     */
    public function bulkUpload(Request $request)
    {
        $request->validate([
            'file' => 'required|file|mimes:csv,txt|max:10240',  // 10MB max
        ]);

        $file = $request->file('file');
        $path = $file->getRealPath();
        
        // Read file content and handle encoding
        $content = file_get_contents($path);
        
        // Detect and convert encoding to UTF-8
        $encoding = mb_detect_encoding($content, ['UTF-8', 'ASCII', 'ISO-8859-1', 'Windows-1252'], true);
        if ($encoding && $encoding !== 'UTF-8') {
            // Replace Windows-1252 special chars BEFORE encoding conversion (while still in original encoding)
            $content = str_replace(
                ["\x92", "\x93", "\x94", "\x91", "\x96", "\x97", "\x85", "\x9C", "\x8C"],  // Windows-1252 special chars
                ["'", '"', '"', "'", '-', '-', '...', 'oe', 'OE'],                          // ASCII equivalents
                $content
            );
            $content = mb_convert_encoding($content, 'UTF-8', $encoding);
        }
        
        // Write to temp file for proper CSV parsing with fgetcsv
        $tempPath = sys_get_temp_dir() . '/competencies_' . uniqid() . '.csv';
        file_put_contents($tempPath, $content);
        
        // Parse CSV using fgetcsv (handles multi-line fields correctly)
        $handle = fopen($tempPath, 'r');
        $data = [];
        while (($row = fgetcsv($handle)) !== false) {
            $data[] = $row;
        }
        fclose($handle);
        unlink($tempPath);
        
        // Remove header row and filter truly empty rows (allow French-only rows where column 0 is empty)
        $headers = array_shift($data);
        $data = array_filter($data, fn($row) => count($row) > 2 && (!empty(trim($row[0] ?? '')) || !empty(trim($row[1] ?? '')) || !empty(trim($row[2] ?? ''))));
        
        $stats = [
            'total' => count($data),
            'success' => 0,
            'failed' => 0,
            'errors' => []
        ];

        $unitCache = [];
        $competencyCache = [];

        DB::beginTransaction();
        try {
            foreach ($data as $rowIndex => $row) {
                $rowNumber = $rowIndex + 2; // +2 because we removed header and arrays are 0-indexed

                try {
                    // Map CSV columns and trim whitespace
                    $unitTitleEn = !empty($row[0]) ? trim($row[0]) : null;
                    $unitTitleFr = !empty($row[1]) ? trim($row[1]) : null;
                    $competencyCode = !empty($row[2]) ? trim($row[2]) : null;
                    $competencyDescEn = !empty($row[3]) ? trim($row[3]) : null;
                    $competencyDescFr = !empty($row[4]) ? trim($row[4]) : null;
                    $competencyDesignation = !empty($row[5]) ? strtoupper(trim($row[5])) : 'RCIC';
                    $subCompetencyCode = !empty($row[6]) ? trim($row[6]) : null;
                    $subCompetencyDescEn = !empty($row[7]) ? trim($row[7]) : null;
                    $subCompetencyDescFr = !empty($row[8]) ? trim($row[8]) : null;
                    $subCompetencyDesignation = !empty($row[9]) ? strtoupper(trim($row[9])) : null;

                    // Validate required fields
                    if (empty($competencyCode)) {
                        throw new \Exception("Competency code is required");
                    }
                    if (empty($competencyDescEn) && empty($competencyDescFr)) {
                        throw new \Exception("At least one competency description (EN or FR) is required");
                    }
                    if (empty($unitTitleEn) && empty($unitTitleFr)) {
                        throw new \Exception("At least one unit title (EN or FR) is required");
                    }
                    
                    // Validate competency designation
                    if (!in_array($competencyDesignation, ['RCIC', 'RISIA'])) {
                        throw new \Exception("Competency designation must be either RCIC or RISIA, got: {$competencyDesignation}");
                    }
                    
                    // Validate sub-competency designation if provided
                    if ($subCompetencyCode && $subCompetencyDesignation && !in_array($subCompetencyDesignation, ['RCIC', 'RISIA'])) {
                        throw new \Exception("Sub-competency designation must be either RCIC or RISIA, got: {$subCompetencyDesignation}");
                    }

                    // Get or create unit - include designation in the key since same unit name can exist for different designations
                    $unitKey = ($unitTitleEn ?: '') . '|' . ($unitTitleFr ?: '') . '|' . $competencyDesignation;
                    if (!isset($unitCache[$unitKey])) {
                        // Look for exact match on title AND check if any competency in that unit has the same designation
                        $existingUnit = DB::table('competency_units')
                            ->where(function($query) use ($unitTitleEn, $unitTitleFr) {
                                if ($unitTitleEn) $query->where('title_en', $unitTitleEn);
                                elseif ($unitTitleFr) $query->where('title_fr', $unitTitleFr);
                            })
                            ->whereExists(function ($query) use ($competencyDesignation) {
                                $query->select(DB::raw(1))
                                    ->from('competencies')
                                    ->whereColumn('competencies.unit_id', 'competency_units.id')
                                    ->where('competencies.designation', $competencyDesignation);
                            })
                            ->first();
                        
                        // If no unit with matching designation found, try to find a unit without any competencies yet
                        if (!$existingUnit) {
                            $existingUnit = DB::table('competency_units')
                                ->where(function($query) use ($unitTitleEn, $unitTitleFr) {
                                    if ($unitTitleEn) $query->where('title_en', $unitTitleEn);
                                    elseif ($unitTitleFr) $query->where('title_fr', $unitTitleFr);
                                })
                                ->whereNotExists(function ($query) {
                                    $query->select(DB::raw(1))
                                        ->from('competencies')
                                        ->whereColumn('competencies.unit_id', 'competency_units.id');
                                })
                                ->first();
                        }

                        if ($existingUnit) {
                            $unitId = $existingUnit->id;
                        } else {
                            $maxOrder = DB::table('competency_units')->max('order') ?? -1;
                            $unitData = array_filter([
                                'title_en' => $unitTitleEn,
                                'title_fr' => $unitTitleFr,
                                'title' => null,
                                'order' => $maxOrder + 1,
                                'created_at' => now(),
                                'updated_at' => now(),
                            ], fn($value) => $value !== null);
                            
                            $unitId = DB::table('competency_units')->insertGetId($unitData);
                        }
                        $unitCache[$unitKey] = $unitId;
                    }
                    $unitId = $unitCache[$unitKey];

                    // Create/Update main competency - include designation in lookup for uniqueness
                    $existingCompetency = DB::table('competencies')
                        ->where('code', $competencyCode)
                        ->where('unit_id', $unitId)
                        ->where('designation', $competencyDesignation)
                        ->whereNull('parent_id')
                        ->first();

                    if ($existingCompetency) {
                        // Update existing main competency
                        $updateData = array_filter([
                            'description_en' => $competencyDescEn,
                            'description_fr' => $competencyDescFr,
                            'description' => null,
                            'designation' => $competencyDesignation,
                            'updated_at' => now(),
                        ], fn($value) => $value !== null);
                        
                        DB::table('competencies')
                            ->where('id', $existingCompetency->id)
                            ->update($updateData);
                        
                        $competencyId = $existingCompetency->id;
                    } else {
                        // Create new main competency
                        $maxOrder = DB::table('competencies')
                            ->where('unit_id', $unitId)
                            ->whereNull('parent_id')
                            ->max('order') ?? -1;

                        $insertData = array_filter([
                            'unit_id' => $unitId,
                            'parent_id' => null,
                            'code' => $competencyCode,
                            'description_en' => $competencyDescEn,
                            'description_fr' => $competencyDescFr,
                            'description' => null,
                            'designation' => $competencyDesignation,
                            'order' => $maxOrder + 1,
                            'created_at' => now(),
                            'updated_at' => now(),
                        ], fn($value) => $value !== null);
                        
                        $competencyId = DB::table('competencies')->insertGetId($insertData);
                    }

                    $competencyCache[$competencyCode . '|' . $competencyDesignation] = $competencyId;

                    // Create/Update sub-competency if provided
                    if ($subCompetencyCode) {
                        // Validate sub-competency has description
                        if (empty($subCompetencyDescEn) && empty($subCompetencyDescFr)) {
                            throw new \Exception("Sub-competency code '{$subCompetencyCode}' requires at least one description (EN or FR)");
                        }
                        
                        // Use competency designation if sub-designation not provided
                        $subDesignation = $subCompetencyDesignation ?: $competencyDesignation;

                        $existingSubCompetency = DB::table('competencies')
                            ->where('code', $subCompetencyCode)
                            ->where('unit_id', $unitId)
                            ->where('parent_id', $competencyId)
                            ->where('designation', $subDesignation)
                            ->first();

                        if ($existingSubCompetency) {
                            // Update existing sub-competency
                            $updateData = array_filter([
                                'description_en' => $subCompetencyDescEn,
                                'description_fr' => $subCompetencyDescFr,
                                'description' => null,
                                'designation' => $subDesignation,
                                'updated_at' => now(),
                            ], fn($value) => $value !== null);
                            
                            DB::table('competencies')
                                ->where('id', $existingSubCompetency->id)
                                ->update($updateData);
                        } else {
                            // Create new sub-competency
                            $maxSubOrder = DB::table('competencies')
                                ->where('unit_id', $unitId)
                                ->where('parent_id', $competencyId)
                                ->max('order') ?? -1;

                            $insertSubData = array_filter([
                                'unit_id' => $unitId,
                                'parent_id' => $competencyId,
                                'code' => $subCompetencyCode,
                                'description_en' => $subCompetencyDescEn,
                                'description_fr' => $subCompetencyDescFr,
                                'description' => null,
                                'designation' => $subDesignation,
                                'order' => $maxSubOrder + 1,
                                'created_at' => now(),
                                'updated_at' => now(),
                            ], fn($value) => $value !== null);
                            
                            DB::table('competencies')->insertGetId($insertSubData);
                        }
                    }

                    $stats['success']++;
                } catch (\Exception $e) {
                    $stats['failed']++;
                    $stats['errors'][] = "Row {$rowNumber}: " . $e->getMessage();
                }
            }

            DB::commit();
            
            return response()->json([
                'message' => 'Bulk upload completed',
                'data' => $stats
            ]);
        } catch (\Exception $e) {
            DB::rollBack();
            return response()->json([
                'message' => 'Bulk upload failed',
                'error' => $e->getMessage()
            ], 500);
        }
    }
}
