alrahma_sunday_school/app/Controllers/View/EmailExtractorController.php

<?php

namespace App\Controllers\View;

use CodeIgniter\HTTP\ResponseInterface;
use CodeIgniter\Controller;
use CodeIgniter\Database\Exceptions\DatabaseException;

class EmailExtractorController extends Controller
{
    /**
     * GET /email-extractor
     * Renders the frontend page (view) with CSV upload and comparison UI.
     */
    public function index()
    {
        return view('/emails/parent_email_extractor');
    }

    /**
     * GET /api/emails
     * Returns JSON: { users: string[], parents: string[] }
     * Pulls emails from users.email and parents.secondparent_email
     */
    public function getEmails()
    {
        $db = db_connect();
        $users = [];
        $parents = [];

        try {
            // Fetch users.email (non-null, non-empty)
            $builderUsers = $db->table('users')->select('email');
            $userRows = $builderUsers->get()->getResultArray();
            foreach ($userRows as $row) {
                $email = strtolower(trim((string)($row['email'] ?? '')));
                if ($email !== '' && filter_var($email, FILTER_VALIDATE_EMAIL)) {
                    $users[] = $email;
                }
            }
            // De-duplicate
            $users = array_values(array_unique($users));

            // Fetch parents.secondparent_email (non-null, non-empty)
            $builderParents = $db->table('parents')->select('secondparent_email');
            $parentRows = $builderParents->get()->getResultArray();
            foreach ($parentRows as $row) {
                $email = strtolower(trim((string)($row['secondparent_email'] ?? '')));
                if ($email !== '' && filter_var($email, FILTER_VALIDATE_EMAIL)) {
                    $parents[] = $email;
                }
            }
            // De-duplicate
            $parents = array_values(array_unique($parents));

            return $this->response->setJSON([
                'users'   => $users,
                'parents' => $parents,
            ])->setStatusCode(ResponseInterface::HTTP_OK);
        } catch (DatabaseException $e) {
            return $this->response->setJSON([
                'error' => 'Database error: ' . $e->getMessage(),
            ])->setStatusCode(ResponseInterface::HTTP_INTERNAL_SERVER_ERROR);
        }
    }

    /**
     * POST /api/compare
     * Accepts multipart/form-data with a CSV file named 'file' (optional),
     * or JSON body with { csvEmails: string[] } (optional).
     * Compares against DB and returns:
     * {
     *   existed: string[],        // in CSV AND in DB
     *   needToAdd: string[],      // in DB BUT NOT in CSV
     *   counts: { csv: number, db: number, users: number, parents: number }
     * }
     */
    public function compare()
    {
        $request = $this->request;
        $csvEmails = [];

        // 1) Try read from uploaded file
        $file = $request->getFile('file');
        if ($file && $file->isValid()) {
            $contents = file_get_contents($file->getTempName());
            $csvEmails = $this->extractEmailsFromText($contents);
        }

        // 2) Or from JSON body
        if (empty($csvEmails) && $request->getHeaderLine('Content-Type')) {
            $contentType = $request->getHeaderLine('Content-Type');
            if (stripos($contentType, 'application/json') !== false) {
                $json = $request->getJSON(true);
                if (isset($json['csvEmails']) && is_array($json['csvEmails'])) {
                    $csvEmails = $this->normalizeEmailArray($json['csvEmails']);
                }
            }
        }

        // 3) Compare with DB
        $db = db_connect();

        // Fetch DB emails
        $users = [];
        $parents = [];

        $userRows = $db->table('users')->select('email')->get()->getResultArray();
        foreach ($userRows as $row) {
            $e = strtolower(trim((string)($row['email'] ?? '')));
            if ($e !== '' && filter_var($e, FILTER_VALIDATE_EMAIL)) {
                $users[] = $e;
            }
        }
        $users = array_values(array_unique($users));

        $parentRows = $db->table('parents')->select('secondparent_email')->get()->getResultArray();
        foreach ($parentRows as $row) {
            $e = strtolower(trim((string)($row['secondparent_email'] ?? '')));
            if ($e !== '' && filter_var($e, FILTER_VALIDATE_EMAIL)) {
                $parents[] = $e;
            }
        }
        $parents = array_values(array_unique($parents));

        // Sets for comparison
        $csvSet = array_flip(array_values(array_unique($csvEmails)));
        $dbUnion = array_values(array_unique(array_merge($users, $parents)));
        $dbSet = array_flip($dbUnion);

        // existed: in CSV and in DB
        $existed = [];
        foreach ($csvSet as $email => $_) {
            if (isset($dbSet[$email])) {
                $existed[] = $email;
            }
        }
        sort($existed);

        // needToAdd: in DB but NOT in CSV
        $needToAdd = [];
        foreach ($dbSet as $email => $_) {
            if (!isset($csvSet[$email])) {
                $needToAdd[] = $email;
            }
        }
        sort($needToAdd);

        return $this->response->setJSON([
            'existed'   => $existed,
            'needToAdd' => $needToAdd,
            'counts'    => [
                'csv'     => count($csvEmails),
                'db'      => count($dbUnion),
                'users'   => count($users),
                'parents' => count($parents),
            ],
        ]);
    }

    // Helpers

    private function normalizeEmailArray(array $arr): array
    {
        $out = [];
        foreach ($arr as $e) {
            $email = strtolower(trim((string)$e));
            if ($email !== '' && filter_var($email, FILTER_VALIDATE_EMAIL)) {
                $out[] = $email;
            }
        }
        return array_values(array_unique($out));
    }

    private function extractEmailsFromText(string $text): array
    {
        $pattern = '/[A-Z0-9._%+-]+@[A-Z0-9.-]+\\.[A-Z]{2,}/i';
        preg_match_all($pattern, $text, $matches);
        $emails = $matches[0] ?? [];
        return $this->normalizeEmailArray($emails);
    }
}