alrahma_sunday_school/app/Views/emails/parent_email_extractor.php

<?= $this->extend('layout/management_layout') ?>

<?= $this->section('content') ?>
<div class="container my-4">
  <h2 class="text-center mb-2">Parent Email Extractor</h2>
  <p class="text-center mb-4">Upload a CSV file with emails, compare against your database emails, and see matches and gaps.</p>

  <div class="row g-3">
    <!-- Card: Upload CSV -->
    <div class="col-12 col-lg-6">
      <div class="card shadow-sm">
        <div class="card-body">
          <h5 class="card-title mb-3">1) Upload CSV of emails (source)</h5>
          <div class="row g-2 align-items-center">
            <div class="col-12 col-md-8">
              <input id="csvFile" type="file" accept=".csv" class="form-control" />
            </div>
            <div class="col-12 col-md-4 text-md-end">
              <button id="parseCsvBtn" class="btn btn-primary w-100">Parse CSV</button>
            </div>
          </div>
          <p class="text-muted small mt-2 mb-0">The CSV can be a single column of emails or multi-column. This tool extracts any values that look like emails.</p>
          <div id="csvSummary" class="text-muted small mt-2"></div>
        </div>
      </div>
    </div>

    <!-- Card: Provide DB emails -->
    <div class="col-12 col-lg-6">
      <div class="card shadow-sm h-100">
        <div class="card-body">
          <h5 class="card-title mb-3">2) Provide database emails</h5>

          <div class="mb-2"><span class="fw-semibold">Option A:</span> Paste emails (one per line)</div>
          <label class="form-label small-muted">First parent's email</label>
          <textarea id="usersTextarea" class="form-control" placeholder="user1@example.com&#10;user2@example.com"></textarea>

          <label class="form-label small-muted mt-3">Second parent's email</label>
          <textarea id="parentsTextarea" class="form-control" placeholder="parent1@example.com&#10;parent2@example.com"></textarea>

          <div class="text-center text-muted my-2">— or —</div>

          <div class="mb-2"><span class="fw-semibold">Option B:</span> Fetch from API</div>
          <div class="row g-2 align-items-center">
            <div class="col-12 col-md-8">
              <input id="apiUrl" type="url" value="<?= site_url('api/emails') ?>" class="form-control" />
            </div>
            <div class="col-12 col-md-4 text-md-end">
              <button id="fetchApiBtn" class="btn btn-outline-secondary w-100">Fetch</button>
            </div>
          </div>
          <p class="text-muted small mt-2 mb-0">Provide a backend endpoint that returns JSON like:
            <code>{"users": [...], "parents": [...]}</code>
          </p>
        </div>
      </div>
    </div>
  </div>

  <!-- Card: Compare -->
  <div class="card shadow-sm mt-3">
    <div class="card-body">
      <h5 class="card-title mb-3">3) Compare</h5>
      <div class="d-flex flex-wrap gap-2">
        <button id="compareBtn" class="btn btn-primary">Run Comparison</button>
        <button id="resetBtn" class="btn btn-outline-secondary">Reset</button>
      </div>
      <div id="compareSummary" class="text-muted small mt-2"></div>

      <div class="row g-3 mt-2">
        <!-- Existed -->
        <div class="col-12 col-lg-6">
          <div class="p-3 border rounded-3 bg-light">
            <h6 class="d-flex align-items-center gap-2 mb-2">
              Existed emails <span class="badge bg-success-subtle text-success" id="countExisted">0</span>
            </h6>
            <table id="tableExisted" class="table table-striped table-hover table-sm w-100">
              <thead>
                <tr><th>Email</th></tr>
              </thead>
              <tbody></tbody>
            </table>
            <div class="d-flex justify-content-end">
              <button id="downloadExisted" class="btn btn-outline-secondary btn-sm">Download CSV</button>
            </div>
          </div>
        </div>

        <!-- Need to add -->
        <div class="col-12 col-lg-6">
          <div class="p-3 border rounded-3 bg-light">
            <h6 class="d-flex align-items-center gap-2 mb-2">
              Need to add email <span class="badge bg-danger-subtle text-danger" id="countNeedToAdd">0</span>
            </h6>
            <div class="text-muted small mb-2">Emails present in database (users or parents) but <strong>NOT</strong> in your uploaded CSV.</div>
            <table id="tableNeedToAdd" class="table table-striped table-hover table-sm w-100">
              <thead>
                <tr><th>Email</th><th>Source</th></tr>
              </thead>
              <tbody></tbody>
            </table>
            <div class="d-flex justify-content-end">
              <button id="downloadNeedToAdd" class="btn btn-outline-secondary btn-sm">Download CSV</button>
            </div>
          </div>
        </div>
      </div>

      <div class="alert alert-info mt-3 mb-0 p-2">
        <ul class="mb-0 small">
          <li>Comparison is case-insensitive and trims whitespace.</li>
          <li>Duplicates are removed within each source before comparison.</li>
          <li>CSV parsing extracts any values that resemble valid email addresses from any column.</li>
        </ul>
      </div>
    </div>
  </div>
</div>
<?= $this->endSection() ?>

<?= $this->section('scripts') ?>

<script>
  // Email regex (case-insensitive)
  const emailRegex = /[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}/gi;

  let csvEmails = new Set();
  let usersEmails = new Set();
  let parentsEmails = new Set();

  // DataTable instances
  let dtExisted = null;
  let dtNeedToAdd = null;

  function normalizeEmail(e) {
    return (e || '').trim().toLowerCase();
  }

  function extractEmailsFromCSVText(text) {
    const found = text.match(emailRegex) || [];
    return Array.from(new Set(found.map(normalizeEmail)));
  }

  function extractEmailsFromTextarea(text) {
    return Array.from(new Set(
      (text || '')
        .split(/\n|,|;|\t/)
        .map(normalizeEmail)
        .filter(x => x && x.includes('@'))
    ));
  }

  function downloadCSV(filename, rows) {
    const csvContent = 'data:text/csv;charset=utf-8,' + rows.map(r => '"' + r.replace(/"/g, '""') + '"').join('\n');
    const encodedUri = encodeURI(csvContent);
    const link = document.createElement('a');
    link.setAttribute('href', encodedUri);
    link.setAttribute('download', filename);
    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);
  }

  // Generic DT renderer
  function renderDataTable(tableId, data, columns, existingInstanceRefSetter) {
    const $table = $('#' + tableId);
    if ($.fn.DataTable.isDataTable($table)) {
      const inst = $table.DataTable();
      inst.clear();
      inst.rows.add(data);
      inst.draw();
      existingInstanceRefSetter(inst);
      return;
    }
    const inst = $table.DataTable({
      data,
      columns,
      pageLength: 100,
      dom:
        "<'row'<'col-sm-12 col-md-6'B><'col-sm-12 col-md-6'f>>" +
        "<'row'<'col-sm-12'tr>>" +
        "<'row'<'col-sm-12 col-md-5'i><'col-sm-12 col-md-7'p>>",
      buttons: [
        { extend: 'copy', className: 'btn btn-sm btn-outline-secondary' },
        { extend: 'csv', className: 'btn btn-sm btn-outline-secondary' },
        { extend: 'excel', className: 'btn btn-sm btn-outline-secondary' },
        { extend: 'print', className: 'btn btn-sm btn-outline-secondary' }
      ],
      order: []
    });
    existingInstanceRefSetter(inst);
  }

  function renderExistedTable(rows) {
    renderDataTable(
      'tableExisted',
      rows.map(e => [e]), // single column
      [{ title: 'Email' }],
      inst => { dtExisted = inst; }
    );
  }

  function renderNeedToAddTable(rowsWithSource) {
    // rowsWithSource: [{email, source}]
    const data = rowsWithSource.map(r => [r.email, r.source]);
    renderDataTable(
      'tableNeedToAdd',
      data,
      [{ title: 'Email' }, { title: 'Source' }],
      inst => { dtNeedToAdd = inst; }
    );
  }

  async function handleParseCsv() {
    const fileInput = document.getElementById('csvFile');
    const file = fileInput.files?.[0];
    if (!file) {
      alert('Please choose a CSV file.');
      return;
    }
    const text = await file.text();
    const emails = extractEmailsFromCSVText(text);
    csvEmails = new Set(emails);
    document.getElementById('csvSummary').textContent = `Found ${emails.length} unique emails in CSV.`;
  }

  async function handleFetchApi() {
    const url = document.getElementById('apiUrl').value.trim();
    if (!url) {
      alert('Enter your /api/emails endpoint URL.');
      return;
    }
    try {
      const res = await fetch(url);
      if (!res.ok) throw new Error(`HTTP ${res.status}`);
      const data = await res.json();
      const users = (data.users || []).map(normalizeEmail).filter(Boolean);
      const parents = (data.parents || []).map(normalizeEmail).filter(Boolean);
      usersEmails = new Set(users);
      parentsEmails = new Set(parents);
      document.getElementById('usersTextarea').value = users.join('\n');
      document.getElementById('parentsTextarea').value = parents.join('\n');
      alert(`Fetched ${users.length} users and ${parents.length} parents emails.`);
    } catch (e) {
      console.error(e);
      alert('Failed to fetch API. See console for details.');
    }
  }

  function handleCompare() {
    const usersArr = extractEmailsFromTextarea(document.getElementById('usersTextarea').value);
    const parentsArr = extractEmailsFromTextarea(document.getElementById('parentsTextarea').value);
    usersEmails = new Set(usersArr);
    parentsEmails = new Set(parentsArr);

    const dbSet = new Set([...usersEmails, ...parentsEmails]);

    const existed = [...csvEmails].filter(e => dbSet.has(e)).sort();
    const needToAddEmails = [...dbSet].filter(e => !csvEmails.has(e)).sort();

    const needToAddWithSource = needToAddEmails.map(e => {
      const inUsers = usersEmails.has(e);
      const inParents = parentsEmails.has(e);
      let source = '';
      if (inUsers && inParents) source = 'Users + Parents';
      else if (inUsers) source = 'Users';
      else if (inParents) source = 'Parents';
      return { email: e, source };
    });

    document.getElementById('countExisted').textContent = existed.length;
    document.getElementById('countNeedToAdd').textContent = needToAddEmails.length;
    document.getElementById('compareSummary').textContent =
      `Compared CSV (${csvEmails.size}) vs DB (${dbSet.size} unique: ${usersEmails.size} users + ${parentsEmails.size} parents).`;

    renderExistedTable(existed);
    renderNeedToAddTable(needToAddWithSource);

    // Optional: keep your custom CSV buttons
    document.getElementById('downloadExisted').onclick = () => downloadCSV('existed_emails.csv', existed);
    document.getElementById('downloadNeedToAdd').onclick = () =>
      downloadCSV('need_to_add_emails.csv', needToAddEmails);
  }

  function handleReset() {
    csvEmails = new Set();
    usersEmails = new Set();
    parentsEmails = new Set();

    // Clear inputs
    document.getElementById('csvFile').value = '';
    document.getElementById('csvSummary').textContent = '';
    document.getElementById('usersTextarea').value = '';
    document.getElementById('parentsTextarea').value = '';
    document.getElementById('apiUrl').value = '<?= site_url('api/emails') ?>';

    // Reset counters and summary
    document.getElementById('countExisted').textContent = '0';
    document.getElementById('countNeedToAdd').textContent = '0';
    document.getElementById('compareSummary').textContent = '';

    // Clear tables if initialized
    if (dtExisted) { dtExisted.clear().draw(); }
    if (dtNeedToAdd) { dtNeedToAdd.clear().draw(); }
  }

  // Bind events
  document.getElementById('parseCsvBtn').addEventListener('click', handleParseCsv);
  document.getElementById('fetchApiBtn').addEventListener('click', handleFetchApi);
  document.getElementById('compareBtn').addEventListener('click', handleCompare);
  document.getElementById('resetBtn').addEventListener('click', handleReset);
</script>
<?= $this->endSection() ?>