<?php
namespace app\Controllers;

use app\Models\PayrollModel;
use app\Models\IncomeMasterModel;
use app\Models\DeductionMasterModel;
use app\Models\HrPersonModel;

class ImportController extends Controller {
    
    public function __construct() {
        if (empty($_SESSION['user_id'])) {
            $_SESSION['error'] = 'กรุณาเข้าสู่ระบบก่อนใช้งาน';
            header('Location: ' . BASE_URL . '/login');
            exit;
        }
    }
    
    public function index() {
        $payrollModel = new PayrollModel();
        // Fetch all months for the dropdown
        $periods = $payrollModel->getAllPayrollPeriods();
        
        $this->view('import/index', [
            'title' => 'นำเข้าข้อมูล | ' . APP_NAME,
            'activeMenu' => 'import',
            'periods' => $periods
        ]);
    }
    
    public function upload() {
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            header('Location: ' . BASE_URL . '/import');
            exit;
        }

        $salaryMonthId = $_POST['salary_month_id'] ?? null;
        $fileType = $_POST['file_type'] ?? 'csv';

        if (empty($salaryMonthId) || empty($_FILES['csv_file']['tmp_name'])) {
            $_SESSION['error'] = 'กรุณาเลือกงวดเงินเดือนและไฟล์';
            header('Location: ' . BASE_URL . '/import');
            exit;
        }

        $file = $_FILES['csv_file'];
        if ($file['error'] !== UPLOAD_ERR_OK) {
            $_SESSION['error'] = 'เกิดข้อผิดพลาดในการอัปโหลดไฟล์';
            header('Location: ' . BASE_URL . '/import');
            exit;
        }

        $ext = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
        if ($fileType === 'cgd_txt' && $ext !== 'txt') {
            $_SESSION['error'] = 'ระบบต้องการไฟล์นามสกุล .txt สำหรับรูปแบบกรมบัญชีกลาง';
            header('Location: ' . BASE_URL . '/import');
            exit;
        } elseif ($fileType === 'csv' && $ext !== 'csv') {
            $_SESSION['error'] = 'รองรับเฉพาะไฟล์ .csv เท่านั้น';
            header('Location: ' . BASE_URL . '/import');
            exit;
        }

        $handle = fopen($file['tmp_name'], 'r');
        if (!$handle) {
            $_SESSION['error'] = 'ไม่สามารถอ่านไฟล์ได้';
            header('Location: ' . BASE_URL . '/import');
            exit;
        }

        $incomeModel = new IncomeMasterModel();
        $deductionModel = new DeductionMasterModel();
        
        $payrollModel = new PayrollModel();
        $payrollModel->beginTransaction();
        
        try {
            if ($fileType === 'cgd_txt') {
                $this->processCGDFile($handle, $salaryMonthId, $payrollModel, $incomeModel, $deductionModel, $file['name']);
            } else {
                $this->processCSVFile($handle, $salaryMonthId, $payrollModel, $incomeModel, $deductionModel, $file['name']);
            }
            
            $payrollModel->commit();
            fclose($handle);
            header('Location: ' . BASE_URL . '/import');
            exit;
        } catch (\Exception $e) {
            $payrollModel->rollBack();
            fclose($handle);
            $_SESSION['error'] = 'เกิดข้อผิดพลาดในการประมวลผล: ' . $e->getMessage();
            header('Location: ' . BASE_URL . '/import');
            exit;
        }
    }

    private function getIncomeMap($incomeModel) {
        $incomes = $incomeModel->getActiveIncomes();
        $map = [];
        foreach ($incomes as $inc) {
            $map[$inc['code']] = $inc['id'];
        }
        return $map;
    }

    private function getDeductionMap($deductionModel) {
        $deductions = $deductionModel->getActiveDeductions();
        $map = [];
        foreach ($deductions as $ded) {
            $map[$ded['code']] = $ded['id'];
        }
        return $map;
    }

    private function getOrCreateIncomeMaster($code, $name, $incomeModel, &$incomeMap) {
        if (isset($incomeMap[$code])) {
            return $incomeMap[$code];
        }
        $incomeModel->createIncome([
            'code' => $code,
            'name' => $name,
            'is_taxable' => 1,
            'is_active' => 1,
            'display_order' => 99,
            'fund_source' => 'CGD'
        ]);
        $incomeMap = $this->getIncomeMap($incomeModel);
        return $incomeMap[$code] ?? null;
    }

    private function getOrCreateDeductionMaster($code, $name, $deductionModel, &$deductionMap) {
        if (isset($deductionMap[$code])) {
            return $deductionMap[$code];
        }
        $deductionModel->createDeduction([
            'code' => $code,
            'name' => $name,
            'is_taxable' => 1,
            'is_active' => 1,
            'display_order' => 99,
            'fund_source' => 'CGD'
        ]);
        $deductionMap = $this->getDeductionMap($deductionModel);
        return $deductionMap[$code] ?? null;
    }

    private function parseCGDAmount($amountStr) {
        if (empty($amountStr) || strlen($amountStr) < 3) return 0;
        return (float) substr_replace($amountStr, ".", -2, 0);
    }

    private function processCGDFile($handle, $salaryMonthId, $payrollModel, $incomeModel, $deductionModel, $fileName) {
        $incomeMap = $this->getIncomeMap($incomeModel);
        $deductionMap = $this->getDeductionMap($deductionModel);

        $totalRecords = 0;
        $successRecords = 0;
        $errorRecords = 0;

        $profileModel = new \app\Models\EmployeeProfileModel();

        $cgdIncomesStatic = [
            15 => 'เงินเดือนตกเบิก/ค่าจ้างประจําตกเบิก',
            16 => 'เงิน ปตจ.',
            17 => 'เงิน ปจต. ตกเบิก',
            18 => 'พ.ข.อ./ตกเบิก',
            19 => 'พ.ส.ร./ตกเบิก',
            20 => 'พ.ค.ว./ตกเบิก',
            21 => 'พ.ป.ผ./ตกเบิก',
            22 => 'สปพ./ตกเบิก',
            23 => 'ตปพ./ตกเบิก',
            24 => 'ต.ข.ท.ปจต.',
            25 => 'ต.ข.ท.ปจต. ตกเบิก',
            26 => 'ต.ข.8-8ว.',
            27 => 'ต.ข.8-8ว. ตกเบิก',
            28 => 'ต.ด.ข.1-7/ตกเบิก, ต.ด.จ./ตกเบิก',
            29 => 'ง.ต.พ.ข./ตกเบิก, ง.ต.พ.จ./ตกเบิก',
            30 => 'ค่าเช่าบ้าน/ตกเบิก',
            31 => 'ช่วยเหลือบุตร/ตกเบิก',
            32 => 'การศึกษาบุตร/ตกเบิก',
            33 => 'เงินรางวัล/เงินท้าทาย',
            42 => 'เงินเพิ่มอื่นๆ',
        ];

        $cgdDeductionsStatic = [
            44 => 'ภาษี/ตกเบิก',
            45 => 'เงินกู้เพื่อที่อยู่อาศัย',
            46 => 'ค่าหุ้น-เงินกู้สหกรณ์',
            47 => 'เงินกู้เพื่อการศึกษา',
            48 => 'กบข./ตกเบิก, กสจ./ตกเบิก',
            49 => 'กบข.ส่วนเพิ่ม/ตกเบิก',
            50 => 'ง.ก.บ.(ธอส.)',
            51 => 'ง.ก.บ.(อส.)',
            52 => 'ง.ก.ธ.',
            53 => 'เงินกู้ ธพ.',
            54 => 'ชดใช้ทางแพ่ง',
            55 => 'เงินเรียกคืน',
            56 => 'ค่าสาธารณูปโภค',
            57 => 'เงินสวัสดิการสโมสร',
            58 => 'ค่าฌาปนกิจ',
            59 => 'งท. สงเคราะห์',
            76 => 'เงินลด/หักอื่นๆ',
        ];

        while (($line = fgets($handle)) !== false) {
            $line = trim($line);
            if (empty($line)) continue;
            
            // Convert encoding from TIS-620/Windows-874 to UTF-8
            $utf8Line = @iconv('Windows-874', 'UTF-8//IGNORE', $line);
            if (!$utf8Line) {
                $utf8Line = @iconv('TIS-620', 'UTF-8//IGNORE', $line);
            }
            if (!$utf8Line) $utf8Line = $line;

            $data = explode('$', $utf8Line);
            if (count($data) < 70) continue; // Basic validation that it's a CGD row

            $totalRecords++;
            
            // Adjust to 1-based indexing for variables like f14, f15 to match user specs
            array_unshift($data, "dummy"); // Now $data[1] is Field 1

            $nationalId = trim($data[3] ?? '');
            $empCode = $nationalId; // Per decision, use national_id as employee_code
            
            if (empty($empCode)) {
                $errorRecords++;
                continue;
            }

            // --- 🔹 Fetch Names from external HR database (hosoffice_2566) ---
            $hrModel = new HrPersonModel();
            $hrData = $hrModel->getPersonByNationalId($nationalId);
            
            if ($hrData) {
                // Get Prefix
                $prefix = trim($hrData['HR_PREFIX_NAME'] ?? '');
                $firstName = trim($hrData['HR_FNAME'] ?? '');
                $lastName = trim($hrData['HR_LNAME'] ?? '');
                
                // Add prefix to first name if it exists (or keep separate, based on old code it had first_name and last_name)
                // Let's store prefix + first_name in first_name
                if (!empty($prefix) && strpos($firstName, $prefix) === false) {
                    $firstName = $prefix . $firstName;
                }
                
                $position = trim(($hrData['HR_POSITION_NAME'] ?? '') . ' ' . ($hrData['HR_LEVEL_NAME'] ?? ''));
                $department = trim($hrData['HR_DEPARTMENT_SUB_SUB_NAME'] ?? '');
            } else {
                // Fallback to text file if not found in HR DB (Column 5 and 6)
                $firstName = trim($data[5] ?? '');
                $lastName = trim($data[6] ?? '');
                $position = '';
                $department = '';
            }
            // -------------------------------------------------------------

            // Extract Bank Details from CGD string (1-indexed due to array_unshift)
            $bankName = trim($data[10] ?? '');
            $bankAccount = trim($data[13] ?? '');
            if (!empty($bankName) || !empty($bankAccount)) {
                $profileModel->saveProfile($nationalId, $bankName, $bankAccount);
            }

            $baseSalaryAmount = $this->parseCGDAmount($data[14] ?? '');

            $totalIncome = 0;
            $incomeEntries = [];
            
            // Base Salary (CGD) as an Income
            if ($baseSalaryAmount > 0) {
                $id = $this->getOrCreateIncomeMaster('CGD_INC_14', 'เงินเดือน/ค่าจ้าง (กรมบัญชีกลาง)', $incomeModel, $incomeMap);
                if ($id) {
                    $totalIncome += $baseSalaryAmount;
                    $incomeEntries[] = ['id' => $id, 'amount' => $baseSalaryAmount];
                }
            }
            
            // Static incomes
            foreach ($cgdIncomesStatic as $index => $name) {
                $amt = $this->parseCGDAmount($data[$index] ?? '');
                if ($amt > 0) {
                    $code = "CGD_INC_" . $index;
                    $id = $this->getOrCreateIncomeMaster($code, $name, $incomeModel, $incomeMap);
                    if ($id) {
                        $totalIncome += $amt;
                        $incomeEntries[] = ['id' => $id, 'amount' => $amt];
                    }
                }
            }
            
            // Dynamic incomes (f34->f35, etc.)
            $dynamicIncomes = [
                34 => 35, 36 => 37, 38 => 39, 40 => 41
            ];
            foreach ($dynamicIncomes as $nameIdx => $valIdx) {
                $name = trim($data[$nameIdx] ?? '');
                $amt = $this->parseCGDAmount($data[$valIdx] ?? '');
                if ($amt > 0 && !empty($name)) {
                    $code = "CGD_INC_" . $valIdx;
                    $id = $this->getOrCreateIncomeMaster($code, $name, $incomeModel, $incomeMap);
                    if ($id) {
                        $totalIncome += $amt;
                        $incomeEntries[] = ['id' => $id, 'amount' => $amt];
                    }
                }
            }

            $totalDeduction = 0;
            $deductionEntries = [];

            // Static deductions
            foreach ($cgdDeductionsStatic as $index => $name) {
                $amt = $this->parseCGDAmount($data[$index] ?? '');
                if ($amt > 0) {
                    $code = "CGD_DED_" . $index;
                    $id = $this->getOrCreateDeductionMaster($code, $name, $deductionModel, $deductionMap);
                    if ($id) {
                        $totalDeduction += $amt;
                        $deductionEntries[] = ['id' => $id, 'amount' => $amt];
                    }
                }
            }

            // Dynamic deductions (f60->f61, etc.)
            $dynamicDeductions = [
                60 => 61, 62 => 63, 64 => 65, 66 => 67, 68 => 69, 70 => 71, 72 => 73, 74 => 75
            ];
            foreach ($dynamicDeductions as $nameIdx => $valIdx) {
                $name = trim($data[$nameIdx] ?? '');
                $amt = $this->parseCGDAmount($data[$valIdx] ?? '');
                if ($amt > 0 && !empty($name)) {
                    $code = "CGD_DED_" . $valIdx;
                    $id = $this->getOrCreateDeductionMaster($code, $name, $deductionModel, $deductionMap);
                    if ($id) {
                        $totalDeduction += $amt;
                        $deductionEntries[] = ['id' => $id, 'amount' => $amt];
                    }
                }
            }

            $netSalary = $totalIncome - $totalDeduction;

            $empData = [
                'salary_month_id' => $salaryMonthId,
                'employee_code' => $empCode,
                'national_id' => $nationalId,
                'first_name' => $firstName,
                'last_name' => $lastName,
                'position' => $position,
                'department' => $department,
                'base_salary' => 0, // Hardcode base_salary to 0 since it's now in incomes
                'total_income' => $totalIncome,
                'total_deduction' => $totalDeduction,
                'net_salary' => $netSalary
            ];

            $empSalId = $payrollModel->insertEmployeeSalary($empData);
            
            foreach ($incomeEntries as $inc) {
                $payrollModel->insertSalaryIncome($empSalId, $inc['id'], $inc['amount']);
            }
            foreach ($deductionEntries as $ded) {
                $payrollModel->insertSalaryDeduction($empSalId, $ded['id'], $ded['amount']);
            }
            
            $successRecords++;
        }

        $payrollModel->updateSalaryMonthStatus($salaryMonthId, 'Imported');
        $status = ($errorRecords > 0) ? 'partial' : 'success';
        $payrollModel->logImportHistory($_SESSION['user_id'], $salaryMonthId, $fileName, $totalRecords, $successRecords, $errorRecords, $status);
        
        $_SESSION['success'] = "นำเข้าข้อมูลกรมบัญชีกลางสำเร็จ $successRecords รายการ (ผิดพลาด $errorRecords รายการ)";
    }

    private function processCSVFile($handle, $salaryMonthId, $payrollModel, $incomeModel, $deductionModel, $fileName) {
        $header = fgetcsv($handle, 1000, ",");
        $incomeMap = $this->getIncomeMap($incomeModel);
        $deductionMap = $this->getDeductionMap($deductionModel);

        $colIdx = [];
        foreach ($header as $index => $colName) {
            $colName = trim($colName);
            $colIdx[$colName] = $index;
        }

        $requiredCols = ['Employee_Code', 'National_ID', 'First_Name', 'Last_Name', 'Base_Salary'];
        foreach ($requiredCols as $req) {
            if (!isset($colIdx[$req])) {
                throw new \Exception("ไฟล์ CSV ไม่ถูกต้อง ขาดคอลัมน์: $req");
            }
        }

        $totalRecords = 0;
        $successRecords = 0;
        $errorRecords = 0;

        while (($data = fgetcsv($handle, 1000, ",")) !== false) {
            $totalRecords++;
            
            $empCode = trim($data[$colIdx['Employee_Code']]);
            if (empty($empCode)) {
                $errorRecords++;
                continue;
            }

            $baseSalary = (float)$data[$colIdx['Base_Salary']];
            $totalIncome = 0;
            $totalDeduction = 0;
            
            $incomeEntries = [];
            foreach ($incomeMap as $code => $id) {
                if (isset($colIdx[$code])) {
                    $amt = (float)$data[$colIdx[$code]];
                    if ($amt > 0) {
                        $totalIncome += $amt;
                        $incomeEntries[] = ['id' => $id, 'amount' => $amt];
                    }
                }
            }

            $deductionEntries = [];
            foreach ($deductionMap as $code => $id) {
                if (isset($colIdx[$code])) {
                    $amt = (float)$data[$colIdx[$code]];
                    if ($amt > 0) {
                        $totalDeduction += $amt;
                        $deductionEntries[] = ['id' => $id, 'amount' => $amt];
                    }
                }
            }
            
            // Convert base salary to income
            if ($baseSalary > 0) {
                $id = $this->getOrCreateIncomeMaster('CGD_INC_14', 'เงินเดือน/ค่าจ้าง (กรมบัญชีกลาง)', $incomeModel, $incomeMap);
                if ($id) {
                    $totalIncome += $baseSalary;
                    $incomeEntries[] = ['id' => $id, 'amount' => $baseSalary];
                }
            }

            $netSalary = $totalIncome - $totalDeduction;

            $empData = [
                'salary_month_id' => $salaryMonthId,
                'employee_code' => $empCode,
                'national_id' => trim($data[$colIdx['National_ID']]),
                'first_name' => trim($data[$colIdx['First_Name']]),
                'last_name' => trim($data[$colIdx['Last_Name']]),
                'base_salary' => 0, // Hardcode base_salary to 0
                'total_income' => $totalIncome,
                'total_deduction' => $totalDeduction,
                'net_salary' => $netSalary
            ];

            $empSalId = $payrollModel->insertEmployeeSalary($empData);
            
            foreach ($incomeEntries as $inc) {
                $payrollModel->insertSalaryIncome($empSalId, $inc['id'], $inc['amount']);
            }
            foreach ($deductionEntries as $ded) {
                $payrollModel->insertSalaryDeduction($empSalId, $ded['id'], $ded['amount']);
            }
            
            $successRecords++;
        }
        
        $payrollModel->updateSalaryMonthStatus($salaryMonthId, 'Imported');
        $status = ($errorRecords > 0) ? 'partial' : 'success';
        $payrollModel->logImportHistory($_SESSION['user_id'], $salaryMonthId, $fileName, $totalRecords, $successRecords, $errorRecords, $status);
        
        $_SESSION['success'] = "นำเข้าข้อมูล CSV สำเร็จ $successRecords รายการ (ผิดพลาด $errorRecords รายการ)";
    }
}