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

    public function datatable(Request $request): void
    {
        $db = $this->db();
        $draw = (int) $request->get('draw', 1);
        $start = (int) $request->get('start', 0);
        $length = (int) $request->get('length', 25);
        $search = $request->get('search')['value'] ?? '';
        $statusFilter = $request->get('status', '');

        $where = '1=1';
        $params = [];
        if ($search) {
            $where .= " AND (mt.transfer_number LIKE ? OR mf.full_name_en LIKE ? OR mt.to_applicant_name LIKE ?)";
            $params = array_merge($params, ["%{$search}%", "%{$search}%", "%{$search}%"]);
        }
        if ($statusFilter) {
            $where .= " AND mt.status = ?";
            $params[] = $statusFilter;
        }

        $total = (int) $db->fetchColumn("SELECT COUNT(*) FROM membership_transfers");
        $filtered = (int) $db->fetchColumn(
            "SELECT COUNT(*) FROM membership_transfers mt LEFT JOIN members mf ON mt.from_member_id = mf.id WHERE {$where}", $params
        );

        $rows = $db->fetchAll(
            "SELECT mt.*, mf.full_name_en as from_name, mf.membership_number as from_number,
                    mto.full_name_en as to_name, mto.membership_number as to_number,
                    tt.name_en as type_name
             FROM membership_transfers mt
             LEFT JOIN members mf ON mt.from_member_id = mf.id
             LEFT JOIN members mto ON mt.to_member_id = mto.id
             LEFT JOIN transfer_types tt ON mt.transfer_type_id = tt.id
             WHERE {$where}
             ORDER BY mt.created_at DESC
             LIMIT {$length} OFFSET {$start}",
            $params
        );

        $data = [];
        foreach ($rows as $row) {
            $to = $row['to_name'] ?: e($row['to_applicant_name'] ?: '-');
            $actions = '<a href="/transfers/' . $row['id'] . '" class="btn btn-sm btn-outline-primary me-1"><i class="fas fa-eye"></i></a>';

            $data[] = [
                'id' => $row['id'],
                'number' => e($row['transfer_number']),
                'from' => e($row['from_name'] ?? '') . ' <small class="text-muted">(' . e($row['from_number'] ?? '') . ')</small>',
                'to' => $to,
                'type' => e($row['type_name'] ?? ''),
                'reason' => status_badge($row['reason']),
                'status' => status_badge($row['status']),
                'date' => format_date($row['request_date']),
                'actions' => $actions,
            ];
        }

        $this->json(['draw' => $draw, 'recordsTotal' => $total, 'recordsFiltered' => $filtered, 'data' => $data]);
    }

    public function create(Request $request): void
    {
        $transferTypes = $this->db()->fetchAll("SELECT * FROM transfer_types WHERE is_active = 1 ORDER BY name_en");
        $membershipTypes = $this->db()->fetchAll("SELECT id, name_en, name_ar FROM membership_types WHERE is_active = 1 ORDER BY sort_order");

        $this->view('transfers/create', [
            'title' => lang('board.create_transfer'),
            'breadcrumbs' => [
                ['label' => lang('board.transfers_module'), 'url' => '/transfers'],
                ['label' => lang('board.transfers'), 'url' => '/transfers'],
                ['label' => lang('common.create')]
            ],
            'transferTypes' => $transferTypes,
            'membershipTypes' => $membershipTypes,
        ]);
    }

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

        $validator = new Validator($request->all(), [
            'transfer_type_id'       => 'required|integer|exists:transfer_types,id',
            'from_member_id'         => 'required|integer|exists:members,id',
            'to_applicant_name'      => 'required|string|max:300',
            'to_applicant_national_id'=> 'nullable|string|max:20',
            'to_applicant_phone'     => 'nullable|string|max:20',
            'relationship_to_original'=> 'nullable|string|max:100',
            'reason'                 => 'required|in:inheritance,divorce,gift,sale,court_order,board_decision,other',
            'reason_details'         => 'nullable|string|max:5000',
            'membership_type_id'     => 'required|integer|exists:membership_types,id',
            'new_membership_type_id' => 'nullable|integer',
            'notes'                  => 'nullable|string|max:5000',
        ]);

        if ($validator->fails()) {
            $this->json(['success' => false, 'errors' => $validator->errors()], 422);
            return;
        }

        $data = $validator->validated();
        $trNum = SequenceGenerator::next('transfer_number');
        $data['transfer_number'] = $trNum ?: 'TRN-' . str_pad(rand(1, 99999), 5, '0', STR_PAD_LEFT);
        $data['request_date'] = date('Y-m-d');
        $data['status'] = 'requested';
        $data['created_by'] = auth_id();

        // Calculate fees from transfer type
        $type = $this->db()->fetchOne("SELECT * FROM transfer_types WHERE id = ?", [$data['transfer_type_id']]);
        $data['transfer_fee'] = $type['fee_amount'] ?? 0;
        $data['total_fees'] = $data['transfer_fee'];

        $id = $this->db()->insert('membership_transfers', $data);

        // Log status history
        $this->db()->insert('transfer_status_history', [
            'transfer_id' => $id,
            'to_status' => 'requested',
            'changed_by' => auth_id(),
            'notes' => 'Transfer request created',
        ]);

        $this->audit('create', 'membership_transfers', $id, 'Created transfer: ' . $data['transfer_number']);
        $this->json(['success' => true, 'message' => lang('board.transfer_created'), 'redirect' => '/transfers/' . $id]);
    }

    public function show(Request $request): void
    {
        $id = (int) $request->routeParam('id');
        $db = $this->db();

        $transfer = $db->fetchOne(
            "SELECT mt.*, tt.name_en as type_name, tt.name_ar as type_name_ar, tt.requires_board_approval,
                    mf.full_name_en as from_name, mf.membership_number as from_number,
                    mto.full_name_en as to_name, mto.membership_number as to_number,
                    mtype.name_en as membership_type_name
             FROM membership_transfers mt
             LEFT JOIN transfer_types tt ON mt.transfer_type_id = tt.id
             LEFT JOIN members mf ON mt.from_member_id = mf.id
             LEFT JOIN members mto ON mt.to_member_id = mto.id
             LEFT JOIN membership_types mtype ON mt.membership_type_id = mtype.id
             WHERE mt.id = ?", [$id]
        );
        if (!$transfer) { $this->abort(404); return; }

        $documents = $db->fetchAll("SELECT * FROM transfer_documents WHERE transfer_id = ? ORDER BY uploaded_at DESC", [$id]);
        $heirs = $db->fetchAll("SELECT * FROM transfer_heirs WHERE transfer_id = ? ORDER BY is_primary_heir DESC, heir_name", [$id]);
        $history = $db->fetchAll(
            "SELECT tsh.*, au.full_name_en as changed_by_name
             FROM transfer_status_history tsh
             LEFT JOIN auth_users au ON tsh.changed_by = au.id
             WHERE tsh.transfer_id = ?
             ORDER BY tsh.changed_at DESC", [$id]
        );

        $this->view('transfers/show', [
            'title' => e($transfer['transfer_number']),
            'breadcrumbs' => [
                ['label' => lang('board.transfers_module'), 'url' => '/transfers'],
                ['label' => lang('board.transfers'), 'url' => '/transfers'],
                ['label' => e($transfer['transfer_number'])]
            ],
            'transfer' => $transfer,
            'documents' => $documents,
            'heirs' => $heirs,
            'history' => $history,
        ]);
    }

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

        $transfer = $this->db()->fetchOne("SELECT * FROM membership_transfers WHERE id = ?", [$id]);
        if (!$transfer) { $this->json(['success' => false, 'message' => 'Not found'], 404); return; }

        $newStatus = $request->post('status', '');
        $reason = $request->post('reason', '');
        $validStatuses = ['requested','documents_pending','under_review','board_pending','approved','rejected','completed','cancelled'];

        if (!in_array($newStatus, $validStatuses)) {
            $this->json(['success' => false, 'message' => 'Invalid status'], 400);
            return;
        }

        $this->db()->update('membership_transfers', [
            'status' => $newStatus,
            'status_changed_at' => date('Y-m-d H:i:s'),
        ], 'id = ?', [$id]);

        $this->db()->insert('transfer_status_history', [
            'transfer_id' => $id,
            'from_status' => $transfer['status'],
            'to_status' => $newStatus,
            'reason' => $reason,
            'changed_by' => auth_id(),
        ]);

        $this->audit('update', 'membership_transfers', $id, "Transfer status: {$transfer['status']} → {$newStatus}");
        $this->json(['success' => true, 'message' => lang('board.status_updated')]);
    }

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

        $file = $request->file('document');
        $upload = new FileUpload($file);
        $upload->setAllowedTypes(['pdf', 'jpg', 'jpeg', 'png', 'doc', 'docx']);
        $upload->setDestination('uploads/transfers/' . $id);

        if (!$upload->validate()) {
            $this->json(['success' => false, 'message' => implode(', ', $upload->errors())], 422);
            return;
        }

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

        $this->db()->insert('transfer_documents', [
            'transfer_id' => $id,
            'document_type' => $request->post('document_type', 'general'),
            'document_name' => $request->post('document_name', $upload->originalName()),
            'file_path' => $path,
            'file_size_bytes' => $upload->size(),
            'mime_type' => $upload->mimeType(),
            'uploaded_by' => auth_id(),
        ]);

        $this->audit('create', 'transfer_documents', null, "Uploaded document for transfer {$id}");
        $this->json(['success' => true, 'message' => lang('board.document_uploaded')]);
    }

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

        $validator = new Validator($request->all(), [
            'heir_name'       => 'required|string|max:300',
            'heir_national_id'=> 'nullable|string|max:20',
            'heir_phone'      => 'nullable|string|max:20',
            'relationship'    => 'required|string|max:100',
            'share_percentage'=> 'required|decimal|min:0|max:100',
            'is_primary_heir' => 'nullable|boolean',
        ]);

        if ($validator->fails()) {
            $this->json(['success' => false, 'errors' => $validator->errors()], 422);
            return;
        }

        $data = $validator->validated();
        $data['transfer_id'] = $id;
        $data['is_primary_heir'] = $request->post('is_primary_heir') ? 1 : 0;
        $data['consent_given'] = 0;

        $this->db()->insert('transfer_heirs', $data);
        $this->audit('create', 'transfer_heirs', null, "Added heir for transfer {$id}");
        $this->json(['success' => true, 'message' => lang('board.heir_added')]);
    }

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

        $heirId = (int) $request->post('heir_id');
        $this->db()->delete('transfer_heirs', 'id = ? AND transfer_id = ?', [$heirId, $id]);
        $this->audit('delete', 'transfer_heirs', $heirId, "Removed heir from transfer {$id}");
        $this->json(['success' => true, 'message' => lang('board.heir_removed')]);
    }

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

        $transfer = $this->db()->fetchOne("SELECT * FROM membership_transfers WHERE id = ?", [$id]);
        if (!$transfer) { $this->json(['success' => false, 'message' => 'Not found'], 404); return; }

        $this->db()->update('membership_transfers', [
            'status' => 'approved',
            'status_changed_at' => date('Y-m-d H:i:s'),
            'approved_by' => auth_id(),
            'approved_at' => date('Y-m-d H:i:s'),
        ], 'id = ?', [$id]);

        $this->db()->insert('transfer_status_history', [
            'transfer_id' => $id,
            'from_status' => $transfer['status'],
            'to_status' => 'approved',
            'changed_by' => auth_id(),
            'notes' => $request->post('notes', 'Approved'),
        ]);

        $this->audit('approve', 'membership_transfers', $id, 'Approved transfer: ' . $transfer['transfer_number']);
        $this->json(['success' => true, 'message' => lang('board.transfer_approved')]);
    }

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

        $transfer = $this->db()->fetchOne("SELECT * FROM membership_transfers WHERE id = ? AND status = 'approved'", [$id]);
        if (!$transfer) {
            $this->json(['success' => false, 'message' => 'Transfer not found or not approved'], 400);
            return;
        }

        $db = $this->db();
        $db->beginTransaction();

        try {
            // Terminate original member
            $db->execute("CALL sp_terminate_member(?, ?, ?, @result)", [
                $transfer['from_member_id'],
                'Membership transferred: ' . $transfer['transfer_number'],
                auth_id(),
            ]);

            // Update transfer record
            $db->update('membership_transfers', [
                'status' => 'completed',
                'transfer_date' => date('Y-m-d'),
                'status_changed_at' => date('Y-m-d H:i:s'),
                'completed_by' => auth_id(),
                'completed_at' => date('Y-m-d H:i:s'),
            ], 'id = ?', [$id]);

            $db->insert('transfer_status_history', [
                'transfer_id' => $id,
                'from_status' => 'approved',
                'to_status' => 'completed',
                'changed_by' => auth_id(),
                'notes' => 'Transfer completed. Original member terminated.',
            ]);

            $db->commit();
        } catch (\Exception $e) {
            $db->rollBack();
            Logger::error('Transfer completion failed: ' . $e->getMessage());
            $this->json(['success' => false, 'message' => 'Transfer completion failed: ' . $e->getMessage()], 500);
            return;
        }

        $this->audit('execute', 'membership_transfers', $id, 'Completed transfer: ' . $transfer['transfer_number']);
        $this->json(['success' => true, 'message' => lang('board.transfer_completed'), 'redirect' => '/transfers/' . $id]);
    }
}