// ==============================
// NIKKAH PATHWAYS — COMBINED SCRIPT
// Receives website registrations via webhook
// + Sends shortlisting emails automatically
// ==============================

// ── CONFIGURATION ──
const SHEET_NAME                  = "Form Responses 1";
const QUESTIONNAIRE_SHEET_NAME    = "Form Responses 2";
const DASHBOARD_SHEET_NAME        = "Event Dashboard";
const EVENT_LOCATION              = "NSW";
const EMAIL_SUBJECT               = "You've Been Shortlisted for Nikkah Pathways!";
const QUESTIONNAIRE_EMAIL_SUBJECT = "Nikkah Pathways — Please Complete Your Questionnaire";
const QUESTIONNAIRE_FORM_LINK     = "https://forms.gle/CB4LnhVhm1AfhFcN8";
const SEND_HOUR_START             = 9;
const SEND_HOUR_END               = 20;
const ADMIN_EMAIL                 = "admin@nikkahpathways.com.au";

// ── COLUMN MAPPING (0-indexed) ──
const COL = {
  timestamp:       0,
  fullName:        1,
  email:           2,
  contact:         3,
  age:             4,
  gender:          5,
  nationality:     6,
  city:            7,   // which event they registered for
  eventDate:       8,
  source:          9,   // "website" or "form"
};

// ── DASHBOARD COLUMNS (1-indexed) ──
const DASH = {
  name:     1,
  age:      2,
  gender:   3,
  city:     4,
  refund:   5,
  attended: 6,
  match:    7,
};

// ── COLORS ──
const COLOR = {
  green:    "#b7e1cd",
  red:      "#f4cccc",
  yellow:   "#fff2cc",
  blue:     "#c9daf8",
  pink:     "#fce4ec",
  headerBg: "#1a1a2e",
  headerText:"#ffffff",
};

// ==============================
// doPost — receives website registrations
// ==============================
function doPost(e) {
  try {
    const data = JSON.parse(e.postData.contents);

    const ss    = SpreadsheetApp.getActiveSpreadsheet();
    let sheet   = ss.getSheetByName(SHEET_NAME);

    // Create sheet if it doesn't exist
    if (!sheet) {
      sheet = ss.insertSheet(SHEET_NAME);
      sheet.appendRow([
        "Timestamp", "Full Name", "Email", "Phone", "Age",
        "Gender", "Nationality", "City", "Event Date", "Source",
        "Email Status"
      ]);
    }

    // Check for duplicate email + city combo
    const existing = sheet.getDataRange().getValues();
    for (let i = 1; i < existing.length; i++) {
      if (
        existing[i][COL.email]?.toString().toLowerCase() === data.email?.toLowerCase() &&
        existing[i][COL.city]?.toString().toLowerCase()  === (data.city || "").toLowerCase()
      ) {
        return ContentService
          .createTextOutput(JSON.stringify({ status: "duplicate" }))
          .setMimeType(ContentService.MimeType.JSON);
      }
    }

    // Append the new registration row
    sheet.appendRow([
      new Date(),                                    // Timestamp
      (data.fname || "") + " " + (data.lname || ""), // Full Name
      data.email        || "",                        // Email
      data.phone        || "",                        // Phone
      data.age          || "",                        // Age
      data.gender       || "",                        // Gender
      data.nationality  || "",                        // Nationality
      data.city         || "",                        // City / Event
      data.date         || "",                        // Event Date
      "website",                                      // Source
      "Pending"                                        // Email Status
    ]);

    // Auto-send shortlisting email
    const name  = ((data.fname || "") + " " + (data.lname || "")).trim();
    const city  = data.city  || "the event";
    const email = data.email || "";

    if (email) {
      try {
        sendShortlistingEmail(name, email, city, data.date || "TBC");

        // Mark as sent in sheet
        const lastRow = sheet.getLastRow();
        const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
        const statusCol = headers.indexOf("Email Status") + 1;
        if (statusCol > 0) {
          const sentTime = Utilities.formatDate(new Date(), "Australia/Sydney", "dd/MM/yyyy HH:mm:ss");
          sheet.getRange(lastRow, statusCol).setValue("Sent - " + sentTime);
        }
      } catch (emailErr) {
        Logger.log("Email error: " + emailErr.message);
        const lastRow = sheet.getLastRow();
        const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
        const statusCol = headers.indexOf("Email Status") + 1;
        if (statusCol > 0) sheet.getRange(lastRow, statusCol).setValue("Email Error: " + emailErr.message);
      }
    }

    return ContentService
      .createTextOutput(JSON.stringify({ status: "ok" }))
      .setMimeType(ContentService.MimeType.JSON);

  } catch (err) {
    Logger.log("doPost error: " + err.message);
    return ContentService
      .createTextOutput(JSON.stringify({ status: "error", message: err.message }))
      .setMimeType(ContentService.MimeType.JSON);
  }
}

// ==============================
// sendShortlistingEmail — reusable function
// ==============================
function sendShortlistingEmail(name, email, city, eventDate) {
  const subject = "You've Been Shortlisted for Nikkah Pathways " + city + "!";

  const plainBody =
`Assalamu Alaykum ${name},

Thank you for registering for the Nikkah Pathways ${city} Event — a halal and supportive space for hearts to meet.

Congratulations, you've been shortlisted!

────────────────────────────
EVENT DETAILS
────────────────────────────
City:     ${city}
Date:     ${eventDate}
Time:     1:00 PM - 5:00 PM
          (Doors open at 12:30 PM — please collect your name tag before entering)
Closing:  Doors close strictly at 1:15 PM
Cost:     $150 per person
Meals:    Included

We encourage all female attendees to bring along their mahram (parent/chaperone) for comfort and support.
Please note, a $100 mahram fee will apply.

────────────────────────────
TO SECURE YOUR SPOT
────────────────────────────
Please make your payment within 48 hours to confirm your place:

Account Name:   Ilyas Aden
BSB:            182-182
Account Number: 031453087
Description:    NP ${city} - ${name}

Please include "NP ${city} - ${name}" as your payment reference so we can identify your payment.

────────────────────────────
IMPORTANT NOTES
────────────────────────────
- Seats are limited, and your spot cannot be held beyond 3 days.
- If payment is not received within this timeframe, your spot will be offered to the next shortlisted candidate.
- Payments are eligible for a full refund within one (1) week following the finalization of the event date.
- This email is not an official invitation. Invitations will be sent once payment has been received in full.

Please reply to this email with a screenshot or receipt of your payment to confirm your booking.

We look forward to welcoming you to this special event.

With warm regards,
The Nikkah Pathways Team
${ADMIN_EMAIL}`;

  const htmlBody =
`<div style="font-family:Arial,sans-serif;max-width:600px;margin:auto;color:#333;font-size:15px;line-height:1.7">
  <div style="background:#1a1a2e;padding:30px;text-align:center;border-radius:8px 8px 0 0">
    <h1 style="color:#fff;margin:0;font-size:22px;letter-spacing:1px">Nikkah Pathways ${city}</h1>
    <p style="color:#c9a96e;margin:6px 0 0;font-size:14px">A halal and supportive space for hearts to meet</p>
  </div>
  <div style="background:#fff;padding:30px;border:1px solid #e5e5e5">
    <p>Assalamu Alaykum <strong>${name}</strong>,</p>
    <p>Thank you for registering for the <strong>Nikkah Pathways ${city} Event</strong>.</p>
    <div style="background:#fff8ee;border-left:4px solid #c9a96e;padding:16px 20px;border-radius:4px;margin:20px 0;text-align:center">
      <p style="font-size:20px;margin:0;font-weight:bold;color:#1a1a2e">🎉 Congratulations, you've been shortlisted!</p>
    </div>
    <h2 style="color:#1a1a2e;border-bottom:1px solid #e5e5e5;padding-bottom:8px">Event Details</h2>
    <table style="width:100%;font-size:15px">
      <tr><td style="padding:6px 0;width:130px"><strong>City</strong></td><td>${city}</td></tr>
      <tr><td style="padding:6px 0"><strong>Date</strong></td><td>${eventDate}</td></tr>
      <tr><td style="padding:6px 0"><strong>Time</strong></td><td>1:00 PM - 5:00 PM <span style="color:#888;font-size:13px">(Doors open 12:30 PM)</span></td></tr>
      <tr><td style="padding:6px 0"><strong>Doors Close</strong></td><td>Strictly 1:15 PM</td></tr>
      <tr><td style="padding:6px 0"><strong>Cost</strong></td><td>$150 per person</td></tr>
      <tr><td style="padding:6px 0"><strong>Meals</strong></td><td>Included</td></tr>
    </table>
    <p style="background:#f5f5f5;padding:12px 16px;border-radius:4px;font-size:14px;margin-top:10px">
      Female attendees are encouraged to bring a <strong>mahram</strong>. A <strong>$100 mahram fee</strong> applies.
    </p>
    <h2 style="color:#1a1a2e;border-bottom:1px solid #e5e5e5;padding-bottom:8px">To Secure Your Spot</h2>
    <p>Please make payment within <strong>48 hours</strong>:</p>
    <div style="background:#f9f9f9;border:1px solid #e0e0e0;border-radius:6px;padding:16px 20px">
      <p style="margin:4px 0"><strong>Account Name:</strong> Ilyas Aden</p>
      <p style="margin:4px 0"><strong>BSB:</strong> 182-182</p>
      <p style="margin:4px 0"><strong>Account Number:</strong> 031453087</p>
      <p style="margin:4px 0"><strong>Description:</strong> <span style="color:#c9a96e;font-weight:bold">NP ${city} - ${name}</span></p>
    </div>
    <h2 style="color:#1a1a2e;border-bottom:1px solid #e5e5e5;padding-bottom:8px">Important Notes</h2>
    <ul style="padding-left:20px;font-size:14px;color:#444;line-height:2">
      <li>Seats are limited. Your spot <strong>cannot be held beyond 3 days</strong>.</li>
      <li>Full refund available within <strong>1 week</strong> of event date confirmation.</li>
      <li>This is <strong>not an official invitation</strong> — invitations sent after payment.</li>
    </ul>
    <p>Please <strong>reply with your payment receipt</strong> to confirm your booking.</p>
    <p>We look forward to welcoming you, <em>inshallah</em>.</p>
  </div>
  <div style="background:#1a1a2e;padding:20px;text-align:center;border-radius:0 0 8px 8px">
    <p style="color:#fff;margin:0;font-size:14px"><strong>The Nikkah Pathways Team</strong></p>
    <a href="mailto:${ADMIN_EMAIL}" style="color:#c9a96e;text-decoration:none;font-size:14px">${ADMIN_EMAIL}</a>
    <p style="color:#888;font-size:12px;margin-top:8px">WhatsApp: 0488 885 770</p>
  </div>
</div>`;

  GmailApp.sendEmail(email, subject, plainBody, {
    htmlBody:  htmlBody,
    replyTo:   ADMIN_EMAIL,
    name:      "Nikkah Pathways"
  });

  Logger.log("Shortlisting email sent to: " + name + " (" + email + ")");
}

// ==============================
// MENU
// ==============================
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Nikkah Pathways")
    .addItem("Send Shortlisting Emails (Bulk)", "sendShortlistingEmailsBulk")
    .addItem("Send Questionnaire to Paid Attendees", "sendQuestionnaireEmails")
    .addItem("Sync Questionnaire Completions", "syncQuestionnaireCompletions")
    .addItem("Refresh Event Dashboard", "refreshEventDashboard")
    .addToUi();
}

// ==============================
// BULK SEND — for existing rows not yet emailed
// ==============================
function sendShortlistingEmailsBulk() {
  const now         = new Date();
  const sydneyTime  = new Date(now.toLocaleString("en-US", { timeZone: "Australia/Sydney" }));
  const currentHour = sydneyTime.getHours();

  if (currentHour < SEND_HOUR_START || currentHour >= SEND_HOUR_END) {
    SpreadsheetApp.getUi().alert("Outside sending window (9 AM – 8 PM Sydney). Please try again later.");
    return;
  }

  const sheet   = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  if (!sheet) { SpreadsheetApp.getUi().alert("Sheet not found: " + SHEET_NAME); return; }

  const data    = sheet.getDataRange().getValues();
  const headers = data[0];
  const statusColIndex = getOrCreateColumn(sheet, headers, "Email Status");

  let sent = 0, skipped = 0;

  for (let i = 1; i < data.length; i++) {
    const row    = data[i];
    const status = row[statusColIndex]?.toString().trim() || "";

    if (status.startsWith("Sent")) { skipped++; continue; }

    const name  = row[COL.fullName]?.toString().trim() || "";
    const email = row[COL.email]?.toString().trim()    || "";
    const city  = row[COL.city]?.toString().trim()     || "the event";
    const date  = row[COL.eventDate]?.toString().trim()|| "TBC";

    if (!email) { skipped++; continue; }

    try {
      sendShortlistingEmail(name, email, city, date);
      const sentTime = Utilities.formatDate(new Date(), "Australia/Sydney", "dd/MM/yyyy HH:mm:ss");
      sheet.getRange(i + 1, statusColIndex + 1).setValue("Sent - " + sentTime);
      sent++;
    } catch (e) {
      sheet.getRange(i + 1, statusColIndex + 1).setValue("Error: " + e.message);
      Logger.log("Failed for " + name + ": " + e.message);
    }
  }

  SpreadsheetApp.getUi().alert("Done!\n\nSent: " + sent + "\nSkipped: " + skipped);
}

// ==============================
// HELPER — get or create column
// ==============================
function getOrCreateColumn(sheet, headers, headerName) {
  let colIndex = headers.indexOf(headerName);
  if (colIndex === -1) {
    colIndex = headers.length;
    sheet.getRange(1, colIndex + 1).setValue(headerName);
    headers.push(headerName);
  }
  return colIndex;
}

// ==============================
// REFRESH EVENT DASHBOARD
// ==============================
function refreshEventDashboard() {
  const ss        = SpreadsheetApp.getActiveSpreadsheet();
  const mainSheet = ss.getSheetByName(SHEET_NAME);
  if (!mainSheet) { SpreadsheetApp.getUi().alert("Sheet not found: " + SHEET_NAME); return; }

  const mainData    = mainSheet.getDataRange().getValues();
  const mainHeaders = mainData[0];
  const paymentColIndex = mainHeaders.indexOf("Payment Status");

  let dashSheet = ss.getSheetByName(DASHBOARD_SHEET_NAME);
  if (!dashSheet) dashSheet = ss.insertSheet(DASHBOARD_SHEET_NAME);

  // Preserve existing manual entries
  const existingData = dashSheet.getDataRange().getValues();
  const existingMap  = {};
  for (let i = 1; i < existingData.length; i++) {
    const storedEmail = existingData[i][7] ? existingData[i][7].toString().trim().toLowerCase() : null;
    if (storedEmail) {
      existingMap[storedEmail] = {
        refund:   existingData[i][DASH.refund - 1]   || "",
        attended: existingData[i][DASH.attended - 1] || "",
        match:    existingData[i][DASH.match - 1]    || "",
      };
    }
  }

  dashSheet.clearContents();
  dashSheet.clearFormats();

  const headers = ["Name", "Age", "Gender", "City", "Refund?", "Attended?", "Match?", "email_key"];
  dashSheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  dashSheet.getRange(1, 1, 1, headers.length)
    .setBackground(COLOR.headerBg).setFontColor(COLOR.headerText)
    .setFontWeight("bold").setHorizontalAlignment("center");
  dashSheet.hideColumns(8);
  dashSheet.setFrozenRows(1);

  let rowIndex = 2, added = 0;

  for (let i = 1; i < mainData.length; i++) {
    const row  = mainData[i];
    const name = row[COL.fullName]?.toString().trim() || "";
    const email = row[COL.email]?.toString().trim().toLowerCase() || "";
    const age  = row[COL.age]?.toString().trim()  || "";
    const gender = row[COL.gender]?.toString().trim() || "";
    const city = row[COL.city]?.toString().trim()  || "";

    if (!name || !email) continue;

    // Only show paid if payment column exists, otherwise show all
    if (paymentColIndex !== -1) {
      const payment = row[paymentColIndex]?.toString().trim().toLowerCase() || "";
      if (payment !== "paid") continue;
    }

    const saved    = existingMap[email] || {};
    const refund   = saved.refund   || "";
    const attended = saved.attended || "";
    const match    = saved.match    || "";

    dashSheet.getRange(rowIndex, DASH.name).setValue(name);
    dashSheet.getRange(rowIndex, DASH.age).setValue(age).setHorizontalAlignment("center");
    dashSheet.getRange(rowIndex, DASH.gender).setValue(gender).setHorizontalAlignment("center");
    dashSheet.getRange(rowIndex, DASH.city).setValue(city).setHorizontalAlignment("center");
    dashSheet.getRange(rowIndex, DASH.refund).setValue(refund).setHorizontalAlignment("center");
    dashSheet.getRange(rowIndex, DASH.attended).setValue(attended).setHorizontalAlignment("center");
    dashSheet.getRange(rowIndex, DASH.match).setValue(match).setHorizontalAlignment("center");
    dashSheet.getRange(rowIndex, 8).setValue(email);

    let rowColor = COLOR.green;
    if (match.toLowerCase() === "yes")    rowColor = COLOR.pink;
    else if (refund.toLowerCase() === "yes")   rowColor = COLOR.red;
    else if (attended.toLowerCase() === "yes") rowColor = COLOR.blue;
    else if (attended.toLowerCase() === "no")  rowColor = COLOR.yellow;

    dashSheet.getRange(rowIndex, 1, 1, 7)
      .setBackground(rowColor).setFontSize(11).setVerticalAlignment("middle");
    dashSheet.setRowHeight(rowIndex, 32);

    rowIndex++; added++;
  }

  if (added > 0) {
    const yesNoRule = SpreadsheetApp.newDataValidation()
      .requireValueInList(["Yes", "No"], true).setAllowInvalid(false).build();
    dashSheet.getRange(2, DASH.refund,   added, 1).setDataValidation(yesNoRule);
    dashSheet.getRange(2, DASH.attended, added, 1).setDataValidation(yesNoRule);
    dashSheet.getRange(2, DASH.match,    added, 1).setDataValidation(yesNoRule);
  }

  SpreadsheetApp.getUi().alert("Dashboard refreshed! " + added + " attendee(s) shown.");
}

// ==============================
// SYNC QUESTIONNAIRE COMPLETIONS
// ==============================
function syncQuestionnaireCompletions() {
  const ss         = SpreadsheetApp.getActiveSpreadsheet();
  const mainSheet  = ss.getSheetByName(SHEET_NAME);
  const questSheet = ss.getSheetByName(QUESTIONNAIRE_SHEET_NAME);
  if (!questSheet) { SpreadsheetApp.getUi().alert("Sheet not found: " + QUESTIONNAIRE_SHEET_NAME); return; }

  const mainData    = mainSheet.getDataRange().getValues();
  const questData   = questSheet.getDataRange().getValues();
  const mainHeaders = mainData[0];
  const questHeaders = questData[0];

  const questEmailColIndex = questHeaders.findIndex(h => h.toString().toLowerCase().includes("email"));
  if (questEmailColIndex === -1) { SpreadsheetApp.getUi().alert("No email column found in questionnaire sheet."); return; }

  const completedEmails = {};
  for (let i = 1; i < questData.length; i++) {
    const email = questData[i][questEmailColIndex]?.toString().trim().toLowerCase();
    if (email) completedEmails[email] = questData[i][0];
  }

  const completedColIndex = getOrCreateColumn(mainSheet, mainHeaders, "Questionnaire Completed");
  let updated = 0, alreadyMarked = 0, notFound = 0;

  for (let i = 1; i < mainData.length; i++) {
    const email         = mainData[i][COL.email]?.toString().trim().toLowerCase() || "";
    const currentStatus = mainData[i][completedColIndex]?.toString().trim() || "";
    if (!email) continue;
    if (currentStatus.startsWith("Yes")) { alreadyMarked++; continue; }
    if (completedEmails[email]) {
      const t = Utilities.formatDate(new Date(completedEmails[email]), "Australia/Sydney", "dd/MM/yyyy HH:mm:ss");
      mainSheet.getRange(i + 1, completedColIndex + 1).setValue("Yes - " + t);
      updated++;
    } else { notFound++; }
  }

  SpreadsheetApp.getUi().alert("Sync complete!\n\nNewly marked: " + updated + "\nAlready marked: " + alreadyMarked + "\nNot yet completed: " + notFound);
}

// ==============================
// SEND QUESTIONNAIRE EMAILS
// ==============================
function sendQuestionnaireEmails() {
  const now         = new Date();
  const sydneyTime  = new Date(now.toLocaleString("en-US", { timeZone: "Australia/Sydney" }));
  if (sydneyTime.getHours() < SEND_HOUR_START || sydneyTime.getHours() >= SEND_HOUR_END) {
    SpreadsheetApp.getUi().alert("Outside sending window (9 AM – 8 PM Sydney)."); return;
  }

  const sheet   = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  const data    = sheet.getDataRange().getValues();
  const headers = data[0];
  const paymentColIndex       = getOrCreateColumn(sheet, headers, "Payment Status");
  const questionnaireColIndex = getOrCreateColumn(sheet, headers, "Questionnaire Sent");

  let sent = 0, skipped = 0;

  for (let i = 1; i < data.length; i++) {
    const row         = data[i];
    const payment     = row[paymentColIndex]?.toString().trim().toLowerCase() || "";
    const questStatus = row[questionnaireColIndex]?.toString().trim() || "";
    if (payment !== "paid" || questStatus.startsWith("Sent")) { skipped++; continue; }

    const name  = row[COL.fullName]?.toString().trim() || "";
    const email = row[COL.email]?.toString().trim()    || "";
    const city  = row[COL.city]?.toString().trim()     || "the event";
    if (!email) { skipped++; continue; }

    const htmlBody =
`<div style="font-family:Arial,sans-serif;max-width:600px;margin:auto;color:#333;font-size:15px;line-height:1.7">
  <div style="background:#1a1a2e;padding:30px;text-align:center;border-radius:8px 8px 0 0">
    <h1 style="color:#fff;margin:0;font-size:22px">Nikkah Pathways ${city}</h1>
    <p style="color:#c9a96e;margin:6px 0 0;font-size:14px">A halal and supportive space for hearts to meet</p>
  </div>
  <div style="background:#fff;padding:30px;border:1px solid #e5e5e5">
    <p>Assalamu Alaykum,</p>
    <p>Please complete your questionnaire to help us prepare for the event:</p>
    <div style="text-align:center;margin:30px 0">
      <a href="${QUESTIONNAIRE_FORM_LINK}" style="background:#c9a96e;color:#1a1a2e;padding:14px 32px;border-radius:6px;text-decoration:none;font-weight:bold;font-size:16px;display:inline-block">
        Complete the Questionnaire →
      </a>
    </div>
    <p style="font-size:13px;color:#666;text-align:center"><a href="${QUESTIONNAIRE_FORM_LINK}" style="color:#c9a96e">${QUESTIONNAIRE_FORM_LINK}</a></p>
    <p>Your responses help us match you as accurately as possible. Food will be provided, <em>inshAllah</em>.</p>
    <p>Questions? Reply to this email or WhatsApp us at <strong>0488 885 770</strong>.</p>
  </div>
  <div style="background:#1a1a2e;padding:20px;text-align:center;border-radius:0 0 8px 8px">
    <p style="color:#fff;margin:0"><strong>The Nikkah Pathways Team</strong></p>
    <a href="mailto:${ADMIN_EMAIL}" style="color:#c9a96e;text-decoration:none">${ADMIN_EMAIL}</a>
  </div>
</div>`;

    try {
      GmailApp.sendEmail(email, QUESTIONNAIRE_EMAIL_SUBJECT, "", { htmlBody, replyTo: ADMIN_EMAIL, name: "Nikkah Pathways" });
      const t = Utilities.formatDate(new Date(), "Australia/Sydney", "dd/MM/yyyy HH:mm:ss");
      sheet.getRange(i + 1, questionnaireColIndex + 1).setValue("Sent - " + t);
      sent++;
    } catch (e) {
      sheet.getRange(i + 1, questionnaireColIndex + 1).setValue("Error: " + e.message);
    }
  }

  SpreadsheetApp.getUi().alert("Done!\n\nQuestionnaire sent: " + sent + "\nSkipped: " + skipped);
}