<?php
class ImportController extends Controller
{
    public function index(Request $request): void
    {
        $this->view('import/index', [
            'title' => lang('admin.import'),
            'breadcrumbs' => [['label' => lang('admin.administration'), 'url' => '/admin/import'], ['label' => lang('admin.import')]],
        ]);
    }

    public function datatable(Request $request): void
    {
        $db = $this->db();
        $rows = $db->fetchAll(
            "SELECT ib.*, u.full_name_en as created_by_name FROM import_batches ib LEFT JOIN auth_users u ON ib.created_by = u.id ORDER BY ib.created_at DESC LIMIT 100"
        );
        $data = [];
        foreach ($rows as $r) {
            $data[] = [
                'batch' => e($r['batch_number']),
                'type' => e($r['import_type']),
                'file' => e($r['source_file_name']),
                'total' => $r['total_rows'],
                'success' => $r['success_rows'],
                'errors' => $r['error_rows'],
                'status' => status_badge($r['status']),
                'by' => e($r['created_by_name'] ?? '-'),
                'date' => format_datetime($r['created_at']),
            ];
        }
        $this->json(['draw' => (int)$request->post('draw', 1), 'recordsTotal' => count($data), 'recordsFiltered' => count($data), 'data' => $data]);
    }

    public function upload(Request $request): void
    {
        if (!CSRF::validate($request->csrfToken())) { $this->json(['success' => false, 'message' => 'Invalid CSRF'], 403); return; }

        $file = $request->file('import_file');
        if (!$file || $file['error'] !== UPLOAD_ERR_OK) {
            $this->json(['success' => false, 'message' => 'No file uploaded'], 400);
            return;
        }

        $upload = new FileUpload($file);
        $upload->setAllowedTypes(['csv', 'xls', 'xlsx'])->setMaxSize(10 * 1024 * 1024)->setDestination('uploads/imports/');
        if (!$upload->validate()) {
            $this->json(['success' => false, 'message' => implode(', ', $upload->errors())], 400);
            return;
        }

        $path = $upload->save();
        if (!$path) {
            $this->json(['success' => false, 'message' => 'Failed to save file'], 500);
            return;
        }

        $importType = $request->post('import_type', 'members');
        $batchNumber = 'IMP-' . date('Ymd') . '-' . str_pad(rand(1, 9999), 4, '0', STR_PAD_LEFT);

        $rows = [];
        $fullPath = PUBLIC_PATH . '/' . $path;
        if (($handle = fopen($fullPath, 'r')) !== false) {
            $header = fgetcsv($handle);
            $rowNum = 0;
            while (($row = fgetcsv($handle)) !== false) {
                $rowNum++;
                $mapped = [];
                foreach ($header as $i => $col) {
                    $mapped[trim($col)] = $row[$i] ?? '';
                }
                $rows[] = ['row_number' => $rowNum, 'data' => $mapped];
            }
            fclose($handle);
        }

        $batchId = $this->db()->insert('import_batches', [
            'batch_number' => $batchNumber,
            'import_type' => $importType,
            'source_file_name' => $upload->originalName(),
            'source_file_path' => $path,
            'source_file_size' => $file['size'],
            'total_rows' => count($rows),
            'status' => 'uploaded',
            'created_by' => Auth::id(),
        ]);

        foreach ($rows as $r) {
            $this->db()->insert('import_batch_rows', [
                'batch_id' => $batchId,
                'row_number' => $r['row_number'],
                'raw_data' => json_encode($r['data'], JSON_UNESCAPED_UNICODE),
                'status' => 'pending',
            ]);
        }

        $this->audit('create', 'import_batches', $batchId, "Uploaded import: {$batchNumber}");
        $this->json(['success' => true, 'message' => count($rows) . ' rows uploaded', 'batch_id' => $batchId]);
    }

    public function validate(Request $request): void
    {
        $id = (int)$request->routeParam('id');
        if (!CSRF::validate($request->csrfToken())) { $this->json(['success' => false, 'message' => 'Invalid CSRF'], 403); return; }
        $db = $this->db();
        $batch = $db->fetchOne("SELECT * FROM import_batches WHERE id = ?", [$id]);
        if (!$batch) { $this->json(['success' => false, 'message' => 'Batch not found'], 404); return; }

        $rows = $db->fetchAll("SELECT * FROM import_batch_rows WHERE batch_id = ? ORDER BY row_number", [$id]);
        $validCount = 0;
        $invalidCount = 0;

        foreach ($rows as $row) {
            $data = json_decode($row['raw_data'], true);
            $errors = [];

            if ($batch['import_type'] === 'members') {
                if (empty($data['first_name_en'] ?? '')) $errors[] = 'first_name_en is required';
                if (empty($data['last_name_en'] ?? '')) $errors[] = 'last_name_en is required';
                if (empty($data['gender'] ?? '')) $errors[] = 'gender is required';
            }

            $status = empty($errors) ? 'valid' : 'invalid';
            if ($status === 'valid') $validCount++;
            else $invalidCount++;

            $db->update('import_batch_rows', [
                'status' => $status,
                'mapped_data' => json_encode($data, JSON_UNESCAPED_UNICODE),
                'error_messages' => empty($errors) ? null : json_encode($errors),
            ], 'id = ?', [$row['id']]);
        }

        $db->update('import_batches', ['status' => 'validated'], 'id = ?', [$id]);
        $this->json(['success' => true, 'message' => "Validated: {$validCount} valid, {$invalidCount} invalid"]);
    }

    public function execute(Request $request): void
    {
        $id = (int)$request->routeParam('id');
        if (!CSRF::validate($request->csrfToken())) { $this->json(['success' => false, 'message' => 'Invalid CSRF'], 403); return; }
        $db = $this->db();
        $batch = $db->fetchOne("SELECT * FROM import_batches WHERE id = ?", [$id]);
        if (!$batch) { $this->json(['success' => false, 'message' => 'Batch not found'], 404); return; }

        $db->update('import_batches', ['status' => 'importing', 'started_at' => date('Y-m-d H:i:s')], 'id = ?', [$id]);

        $rows = $db->fetchAll("SELECT * FROM import_batch_rows WHERE batch_id = ? AND status = 'valid' ORDER BY row_number", [$id]);
        $success = 0;
        $errors = 0;

        foreach ($rows as $row) {
            $data = json_decode($row['mapped_data'] ?: $row['raw_data'], true);
            try {
                if ($batch['import_type'] === 'members') {
                    $memberId = $db->insert('members', [
                        'membership_number' => SequenceGenerator::next('member_number') ?? ('IMP-' . $row['row_number']),
                        'membership_type_id' => (int)($data['membership_type_id'] ?? 1),
                        'first_name_en' => $data['first_name_en'] ?? '',
                        'last_name_en' => $data['last_name_en'] ?? '',
                        'full_name_en' => trim(($data['first_name_en'] ?? '') . ' ' . ($data['last_name_en'] ?? '')),
                        'first_name_ar' => $data['first_name_ar'] ?? null,
                        'last_name_ar' => $data['last_name_ar'] ?? null,
                        'gender' => $data['gender'] ?? 'male',
                        'national_id' => $data['national_id'] ?? null,
                        'primary_phone' => $data['primary_phone'] ?? null,
                        'primary_email' => $data['primary_email'] ?? null,
                        'status' => 'active',
                        'join_date' => date('Y-m-d'),
                        'activation_date' => date('Y-m-d'),
                        'created_by' => Auth::id(),
                    ]);
                    $db->update('import_batch_rows', ['status' => 'imported', 'result_entity_type' => 'member', 'result_entity_id' => $memberId, 'processed_at' => date('Y-m-d H:i:s')], 'id = ?', [$row['id']]);
                    $success++;
                }
            } catch (\Exception $e) {
                $db->update('import_batch_rows', ['status' => 'error', 'error_messages' => json_encode([$e->getMessage()]), 'processed_at' => date('Y-m-d H:i:s')], 'id = ?', [$row['id']]);
                $errors++;
            }
        }

        $db->update('import_batches', [
            'status' => 'completed',
            'completed_at' => date('Y-m-d H:i:s'),
            'processed_rows' => $success + $errors,
            'success_rows' => $success,
            'error_rows' => $errors,
        ], 'id = ?', [$id]);

        $this->audit('execute', 'import_batches', $id, "Import executed: {$success} success, {$errors} errors");
        $this->json(['success' => true, 'message' => "Import complete: {$success} succeeded, {$errors} failed"]);
    }
}