<?php
class ApplicationApiController extends Controller
{
    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'] ?? '';
        $orderCol = (int) ($request->get('order')[0]['column'] ?? 0);
        $orderDir = ($request->get('order')[0]['dir'] ?? 'desc') === 'asc' ? 'ASC' : 'DESC';

        $columns = ['a.id', 'a.application_number', 'a.full_name_en', 'a.primary_phone', 'mt.name_en', 'a.status', 'a.priority', 'a.submission_date', 'u.full_name_en'];
        $orderColumn = $columns[$orderCol] ?? 'a.id';

        $where = "a.is_deleted = 0";
        $params = [];

        // Filters
        $statusFilter = $request->get('status');
        if ($statusFilter) {
            $where .= " AND a.status = ?";
            $params[] = $statusFilter;
        }
        $typeFilter = $request->get('membership_type_id');
        if ($typeFilter) {
            $where .= " AND a.membership_type_id = ?";
            $params[] = $typeFilter;
        }
        $priorityFilter = $request->get('priority');
        if ($priorityFilter) {
            $where .= " AND a.priority = ?";
            $params[] = $priorityFilter;
        }
        $dateFrom = $request->get('date_from');
        if ($dateFrom) {
            $where .= " AND a.created_at >= ?";
            $params[] = $dateFrom . ' 00:00:00';
        }
        $dateTo = $request->get('date_to');
        if ($dateTo) {
            $where .= " AND a.created_at <= ?";
            $params[] = $dateTo . ' 23:59:59';
        }
        $assignedTo = $request->get('assigned_to');
        if ($assignedTo) {
            $where .= " AND a.assigned_to = ?";
            $params[] = $assignedTo;
        }

        if ($search) {
            $where .= " AND (a.application_number LIKE ? OR a.full_name_en LIKE ? OR a.full_name_ar LIKE ? OR a.national_id LIKE ? OR a.primary_phone LIKE ? OR a.email LIKE ?)";
            $s = "%{$search}%";
            $params = array_merge($params, [$s, $s, $s, $s, $s, $s]);
        }

        $countSql = "SELECT COUNT(*) FROM applications a WHERE {$where}";
        $totalFiltered = (int) $db->fetchColumn($countSql, $params);
        $totalRecords = (int) $db->fetchColumn("SELECT COUNT(*) FROM applications WHERE is_deleted = 0");

        $sql = "SELECT a.id, a.application_number, a.full_name_en, a.full_name_ar, a.national_id,
                       a.primary_phone, a.email, a.status, a.priority, a.submission_date,
                       a.source, a.created_at, a.photo_path,
                       mt.name_en as type_name_en, mt.name_ar as type_name_ar,
                       u.full_name_en as assigned_to_name
                FROM applications a
                LEFT JOIN membership_types mt ON a.membership_type_id = mt.id
                LEFT JOIN auth_users u ON a.assigned_to = u.id
                WHERE {$where}
                ORDER BY {$orderColumn} {$orderDir}
                LIMIT " . (int)$length . " OFFSET " . (int)$start;

        $rows = $db->fetchAll($sql, $params);

        $data = [];
        foreach ($rows as $row) {
            $actions = '<div class="btn-group btn-group-sm">';
            $actions .= '<a href="/applications/' . $row['id'] . '" class="btn btn-outline-primary btn-sm" data-bs-toggle="tooltip" title="View"><i class="fas fa-eye"></i></a>';
            if (in_array($row['status'], ['draft', 'submitted', 'documents_pending'])) {
                $actions .= '<a href="/applications/' . $row['id'] . '/edit" class="btn btn-outline-warning btn-sm" data-bs-toggle="tooltip" title="Edit"><i class="fas fa-edit"></i></a>';
            }
            $actions .= '</div>';

            $data[] = [
                'id'               => $row['id'],
                'application_number' => $row['application_number'],
                'full_name'        => '<div>' . e($row['full_name_en']) . '</div>' . ($row['full_name_ar'] ? '<small class="text-muted">' . e($row['full_name_ar']) . '</small>' : ''),
                'phone'            => e($row['primary_phone'] ?? ''),
                'type'             => e(current_lang() === 'ar' ? $row['type_name_ar'] : $row['type_name_en']),
                'status'           => status_badge($row['status']),
                'priority'         => status_badge($row['priority']),
                'submission_date'  => $row['submission_date'] ? format_date($row['submission_date']) : '<span class="text-muted">-</span>',
                'assigned_to'      => e($row['assigned_to_name'] ?? '-'),
                'actions'          => $actions,
            ];
        }

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

    public function stats(Request $request): void
    {
        $db = $this->db();
        $stats = $db->fetchOne(
            "SELECT
                SUM(CASE WHEN status = 'submitted' THEN 1 ELSE 0 END) as submitted,
                SUM(CASE WHEN status = 'under_review' THEN 1 ELSE 0 END) as under_review,
                SUM(CASE WHEN status = 'board_pending' THEN 1 ELSE 0 END) as board_pending,
                SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) as approved,
                SUM(CASE WHEN status = 'rejected' THEN 1 ELSE 0 END) as rejected,
                SUM(CASE WHEN status = 'documents_pending' THEN 1 ELSE 0 END) as documents_pending,
                SUM(CASE WHEN status = 'interview_scheduled' THEN 1 ELSE 0 END) as interview_scheduled,
                SUM(CASE WHEN status = 'draft' THEN 1 ELSE 0 END) as draft,
                COUNT(*) as total
             FROM applications WHERE is_deleted = 0"
        );

        $this->json(['success' => true, 'data' => $stats]);
    }

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

        $db = $this->db();
        $app = $db->fetchOne("SELECT * FROM applications WHERE id = ? AND is_deleted = 0", [$id]);
        if (!$app) {
            $this->json(['success' => false, 'message' => 'Application not found'], 404);
            return;
        }
        if ($app['status'] !== 'draft') {
            $this->json(['success' => false, 'message' => 'Application can only be submitted from draft status'], 422);
            return;
        }

        $db->update('applications', [
            'status'            => 'submitted',
            'status_reason'     => 'Application submitted for review',
            'status_changed_at' => date('Y-m-d H:i:s'),
            'status_changed_by' => Auth::id(),
            'submission_date'   => date('Y-m-d'),
            'applicant_declaration' => 1,
            'declaration_date'  => date('Y-m-d'),
        ], 'id = ?', [$id]);

        $this->audit('update', 'applications', $id, "Application submitted: {$app['application_number']}");
        $this->json(['success' => true, 'message' => lang('applications.submitted_success')]);
    }

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

        $newStatus = $request->post('status');
        $reason = $request->post('reason', '');
        $notes = $request->post('notes', '');

        $validStatuses = ['submitted', 'under_review', 'documents_pending', 'interview_scheduled',
                          'interview_completed', 'board_pending', 'approved', 'rejected', 'waitlisted', 'cancelled'];

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

        $db = $this->db();
        $app = $db->fetchOne("SELECT * FROM applications WHERE id = ? AND is_deleted = 0", [$id]);
        if (!$app) {
            $this->json(['success' => false, 'message' => 'Application not found'], 404);
            return;
        }

        $updateData = [
            'status'            => $newStatus,
            'status_reason'     => $reason,
            'status_changed_at' => date('Y-m-d H:i:s'),
            'status_changed_by' => Auth::id(),
            'internal_notes'    => $notes ? ($app['internal_notes'] ? $app['internal_notes'] . "\n" . $notes : $notes) : $app['internal_notes'],
        ];

        if ($newStatus === 'approved') {
            $updateData['approval_date'] = date('Y-m-d');
        }
        if ($newStatus === 'rejected') {
            $updateData['rejection_date'] = date('Y-m-d');
            $updateData['rejection_reason'] = $reason;
        }

        $assignedTo = $request->post('assigned_to');
        if ($assignedTo) {
            $updateData['assigned_to'] = $assignedTo;
        }

        $db->update('applications', $updateData, 'id = ?', [$id]);
        $this->audit('update', 'applications', $id, "Status changed to {$newStatus}: {$app['application_number']}", ['status' => $app['status']], ['status' => $newStatus]);

        $this->json(['success' => true, 'message' => lang('applications.status_changed')]);
    }

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

        $validator = new Validator($request->all(), [
            'review_type' => 'required|in:initial,document,background,final',
            'decision'    => 'required|in:approve,reject,return,escalate',
        ]);

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

        $db = $this->db();
        $app = $db->fetchOne("SELECT * FROM applications WHERE id = ? AND is_deleted = 0", [$id]);
        if (!$app) {
            $this->json(['success' => false, 'message' => 'Application not found'], 404);
            return;
        }

        $db->insert('application_reviews', [
            'application_id' => $id,
            'reviewer_id'    => Auth::id(),
            'review_type'    => $request->post('review_type'),
            'decision'       => $request->post('decision'),
            'comments'       => $request->post('comments', ''),
        ]);

        $decision = $request->post('decision');
        $statusMap = [
            'approve'  => $app['status'] === 'under_review' ? 'board_pending' : $app['status'],
            'reject'   => 'rejected',
            'return'   => 'documents_pending',
            'escalate' => 'board_pending',
        ];
        $newStatus = $statusMap[$decision] ?? $app['status'];

        if ($newStatus !== $app['status']) {
            $db->update('applications', [
                'status'            => $newStatus,
                'status_reason'     => "Review decision: {$decision}",
                'status_changed_at' => date('Y-m-d H:i:s'),
                'status_changed_by' => Auth::id(),
            ], 'id = ?', [$id]);
        }

        $this->audit('update', 'applications', $id, "Review recorded: {$decision}");
        $this->json(['success' => true, 'message' => lang('applications.review_recorded')]);
    }

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

        $db = $this->db();
        $app = $db->fetchOne("SELECT * FROM applications WHERE id = ? AND is_deleted = 0", [$id]);
        if (!$app) {
            $this->json(['success' => false, 'message' => 'Application not found'], 404);
            return;
        }

        $action = $request->post('action', 'schedule');

        if ($action === 'schedule') {
            $db->insert('application_interviews', [
                'application_id' => $id,
                'scheduled_date' => $request->post('scheduled_date'),
                'location'       => $request->post('location', ''),
                'status'         => 'scheduled',
                'recorded_by'    => Auth::id(),
            ]);

            if ($app['status'] !== 'interview_scheduled') {
                $db->update('applications', [
                    'status' => 'interview_scheduled',
                    'status_changed_at' => date('Y-m-d H:i:s'),
                    'status_changed_by' => Auth::id(),
                ], 'id = ?', [$id]);
            }
        } elseif ($action === 'record') {
            $interviewId = $request->post('interview_id');
            $db->update('application_interviews', [
                'actual_date'       => date('Y-m-d H:i:s'),
                'status'            => 'completed',
                'interview_notes'   => $request->post('interview_notes', ''),
                'rating'            => $request->post('rating') ?: null,
                'recommendation'    => $request->post('recommendation', 'defer'),
                'recorded_by'       => Auth::id(),
            ], 'id = ?', [$interviewId]);

            $db->update('applications', [
                'status' => 'interview_completed',
                'status_changed_at' => date('Y-m-d H:i:s'),
                'status_changed_by' => Auth::id(),
            ], 'id = ?', [$id]);
        }

        $this->audit('update', 'applications', $id, "Interview {$action}");
        $this->json(['success' => true, 'message' => lang('applications.interview_saved')]);
    }

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

        $db = $this->db();
        $app = $db->fetchOne("SELECT * FROM applications WHERE id = ? AND is_deleted = 0", [$id]);
        if (!$app) {
            $this->json(['success' => false, 'message' => 'Application not found'], 404);
            return;
        }

        $votes = $request->post('votes', []);
        if (!is_array($votes) || empty($votes)) {
            $this->json(['success' => false, 'message' => 'No votes provided'], 422);
            return;
        }

        $db->beginTransaction();
        try {
            foreach ($votes as $userId => $vote) {
                $voteValue = $vote['vote'] ?? 'abstain';
                $comments = $vote['comments'] ?? '';

                $existing = $db->fetchOne(
                    "SELECT id FROM application_board_votes WHERE application_id = ? AND board_member_user_id = ?",
                    [$id, $userId]
                );

                if ($existing) {
                    $db->update('application_board_votes', [
                        'vote'     => $voteValue,
                        'comments' => $comments,
                        'voted_at' => date('Y-m-d H:i:s'),
                    ], 'id = ?', [$existing['id']]);
                } else {
                    $db->insert('application_board_votes', [
                        'application_id'       => $id,
                        'board_member_user_id' => $userId,
                        'vote'                 => $voteValue,
                        'comments'             => $comments,
                    ]);
                }
            }

            // Calculate result
            $voteCounts = $db->fetchOne(
                "SELECT
                    SUM(CASE WHEN vote = 'approve' THEN 1 ELSE 0 END) as approve_count,
                    SUM(CASE WHEN vote = 'reject' THEN 1 ELSE 0 END) as reject_count,
                    SUM(CASE WHEN vote = 'abstain' THEN 1 ELSE 0 END) as abstain_count,
                    COUNT(*) as total
                 FROM application_board_votes WHERE application_id = ?",
                [$id]
            );

            $approves = (int) $voteCounts['approve_count'];
            $rejects = (int) $voteCounts['reject_count'];

            if ($approves > $rejects) {
                $db->update('applications', [
                    'status' => 'approved',
                    'status_reason' => "Board approved: {$approves} for, {$rejects} against",
                    'status_changed_at' => date('Y-m-d H:i:s'),
                    'status_changed_by' => Auth::id(),
                    'approval_date' => date('Y-m-d'),
                ], 'id = ?', [$id]);
            } elseif ($rejects > $approves) {
                $db->update('applications', [
                    'status' => 'rejected',
                    'status_reason' => "Board rejected: {$rejects} against, {$approves} for",
                    'status_changed_at' => date('Y-m-d H:i:s'),
                    'status_changed_by' => Auth::id(),
                    'rejection_date' => date('Y-m-d'),
                    'rejection_reason' => "Board vote: {$rejects} reject vs {$approves} approve",
                ], 'id = ?', [$id]);
            }

            $db->commit();
            $this->audit('update', 'applications', $id, "Board votes recorded");
            $this->json(['success' => true, 'message' => lang('applications.votes_recorded')]);
        } catch (\Exception $e) {
            $db->rollBack();
            Logger::error('Board vote failed: ' . $e->getMessage());
            $this->json(['success' => false, 'message' => 'Failed to record votes'], 500);
        }
    }

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

        $db = $this->db();
        $app = $db->fetchOne("SELECT * FROM applications WHERE id = ? AND is_deleted = 0", [$id]);
        if (!$app) {
            $this->json(['success' => false, 'message' => 'Application not found'], 404);
            return;
        }
        if ($app['status'] !== 'approved') {
            $this->json(['success' => false, 'message' => 'Application must be approved first'], 422);
            return;
        }
        if ($app['resulting_member_id']) {
            $this->json(['success' => false, 'message' => 'Member already created from this application'], 422);
            return;
        }

        try {
            $db->execute("CALL sp_create_member_from_application(?, ?, @member_id, @result_msg)", [$id, Auth::id()]);
            $result = $db->fetchOne("SELECT @member_id AS member_id, @result_msg AS result_msg");

            if ($result['member_id']) {
                $this->audit('create', 'members', (int)$result['member_id'], "Member created from application: {$app['application_number']}");
                $this->json([
                    'success'  => true,
                    'message'  => $result['result_msg'],
                    'redirect' => '/members/' . $result['member_id'],
                ]);
            } else {
                $this->json(['success' => false, 'message' => $result['result_msg'] ?? 'Failed to create member'], 422);
            }
        } catch (\Exception $e) {
            Logger::error('Create member from application failed: ' . $e->getMessage());
            $this->json(['success' => false, 'message' => 'Failed to create member: ' . $e->getMessage()], 500);
        }
    }

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

        $db = $this->db();
        $app = $db->fetchOne("SELECT * FROM applications WHERE id = ? AND is_deleted = 0", [$id]);
        if (!$app) {
            $this->json(['success' => false, 'message' => 'Application not found'], 404);
            return;
        }
        if (!in_array($app['status'], ['draft', 'cancelled'])) {
            $this->json(['success' => false, 'message' => 'Only draft or cancelled applications can be deleted'], 422);
            return;
        }

        $db->update('applications', ['is_deleted' => 1], 'id = ?', [$id]);
        $this->audit('delete', 'applications', $id, "Application deleted: {$app['application_number']}");
        $this->json(['success' => true, 'message' => lang('applications.deleted_success'), 'redirect' => '/applications']);
    }

    public function getDocuments(Request $request): void
    {
        $id = (int) $request->routeParam('id');
        $db = $this->db();
        $documents = $db->fetchAll(
            "SELECT ad.*, au.full_name_en as verified_by_name
             FROM application_documents ad
             LEFT JOIN auth_users au ON ad.verified_by = au.id
             WHERE ad.application_id = ? ORDER BY ad.uploaded_at DESC",
            [$id]
        );
        $this->json(['success' => true, 'data' => $documents]);
    }

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

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

        $upload = new FileUpload($file);
        $upload->setAllowedTypes(['pdf', 'jpg', 'jpeg', 'png', 'doc', 'docx'])
               ->setMaxSize(10 * 1024 * 1024)
               ->setDestination('uploads/applications/documents/' . $id);

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

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

        $db = $this->db();
        $docId = $db->insert('application_documents', [
            'application_id'    => $id,
            'document_type'     => $request->post('document_type', 'general'),
            'document_name'     => $request->post('document_name', $upload->originalName()),
            'file_path'         => $path,
            'file_original_name'=> $upload->originalName(),
            'file_size_bytes'   => $upload->size(),
            'mime_type'         => $upload->mimeType(),
            'uploaded_by'       => Auth::id(),
        ]);

        $this->audit('create', 'application_documents', $docId, "Document uploaded for application #{$id}");
        $this->json(['success' => true, 'message' => lang('applications.document_uploaded')]);
    }

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

        $db = $this->db();
        $db->delete('application_documents', 'id = ?', [$docId]);
        $this->json(['success' => true, 'message' => lang('applications.document_deleted')]);
    }

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

        $db = $this->db();
        $db->update('application_documents', [
            'is_verified' => 1,
            'verified_by' => Auth::id(),
            'verified_at' => date('Y-m-d H:i:s'),
        ], 'id = ?', [$docId]);

        $this->json(['success' => true, 'message' => lang('applications.document_verified')]);
    }

    public function export(Request $request): void
    {
        $db = $this->db();
        $status = $request->get('status', '');
        $where = "a.is_deleted = 0";
        $params = [];
        if ($status) {
            $where .= " AND a.status = ?";
            $params[] = $status;
        }

        $rows = $db->fetchAll(
            "SELECT a.application_number, a.full_name_en, a.full_name_ar, a.national_id,
                    a.primary_phone, a.email, a.status, a.priority, a.submission_date,
                    a.source, a.created_at, mt.name_en as type
             FROM applications a
             LEFT JOIN membership_types mt ON a.membership_type_id = mt.id
             WHERE {$where} ORDER BY a.id DESC",
            $params
        );

        $format = $request->get('format', 'csv');
        $headers = ['App #', 'Name (EN)', 'Name (AR)', 'National ID', 'Phone', 'Email', 'Status', 'Priority', 'Submission Date', 'Source', 'Type', 'Created'];

        if ($format === 'excel') {
            Export::excel('applications_export.xls', $headers, $rows);
        } else {
            Export::csv('applications_export.csv', $headers, $rows);
        }
    }
}