import express from "express";
import cors from "cors";
import { createClient } from "@supabase/supabase-js";
import nodemailer from "nodemailer";
import { marked } from "marked";
import dotenv from "dotenv";
import { fileURLToPath } from "url";
import { dirname, join } from "path";
import crypto from "crypto";

dotenv.config();

const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);

const app = express();
const PORT = Number(process.env.PORT) || 3000;
const isProd = process.env.NODE_ENV === "production";

// 1. CORS
app.use(cors({
  origin: '*',
  methods: ['GET', 'POST', 'PUT', 'DELETE', 'OPTIONS'],
  allowedHeaders: ['Content-Type', 'Authorization']
}));

// 2. JSON
app.use(express.json());

// 3. In production: serve the built React frontend as static files
if (isProd) {
  app.use(express.static(join(__dirname, "dist")));
  console.log(`[STATIC] Serving frontend from ${join(__dirname, "dist")}`);
}

// --- CONFIGURATION ---
const supabase = createClient(
  process.env.VITE_SUPABASE_URL || process.env.SUPABASE_URL || "",
  process.env.SUPABASE_SERVICE_ROLE_KEY || process.env.VITE_SUPABASE_ANON_KEY || ""
);

// SMTP Setup
const transporter = nodemailer.createTransport({
  host: process.env.SMTP_HOST || "mail.privateemail.com",
  port: Number(process.env.SMTP_PORT) || 465,
  secure: process.env.SMTP_SECURE === 'true',
  auth: {
    user: process.env.SMTP_USER || "",
    pass: process.env.SMTP_PASS || "",
  },
});

// --- UNSUBSCRIBE HELPERS ---
function unsubscribeSecret(): string {
  return process.env.UNSUBSCRIBE_SECRET || process.env.SMTP_PASS || 'smm-fallback-secret';
}

function signEmail(email: string): string {
  return crypto.createHmac('sha256', unsubscribeSecret()).update(email.toLowerCase()).digest('hex').slice(0, 32);
}

function verifyEmailSig(email: string, sig: string): boolean {
  return signEmail(email) === sig;
}

function buildUnsubscribeUrl(baseUrl: string, email: string, table: string): string {
  const sig = signEmail(email);
  return `${baseUrl}/api/unsubscribe?email=${encodeURIComponent(email)}&table=${encodeURIComponent(table)}&sig=${sig}`;
}

// Helper to resolve email from contactId across all lists
async function resolveEmailFromContactId(contactId: string): Promise<string | null> {
  try {
    const [memberRes, partnerRes, customerRes] = await Promise.all([
      supabase.from('members_list').select('email').eq('id', contactId).maybeSingle(),
      supabase.from('business_partners').select('email').eq('id', contactId).maybeSingle(),
      supabase.from('customer_list').select('email').eq('id', contactId).maybeSingle(),
    ]);

    if (memberRes.data?.email) return memberRes.data.email;
    if (partnerRes.data?.email) return partnerRes.data.email;
    if (customerRes.data?.email) return customerRes.data.email;

    return null;
  } catch (err: any) {
    console.error(`[resolveEmailFromContactId] Error resolving contact ID ${contactId}:`, err.message || err);
    return null;
  }
}

// --- HELPER: EMAIL SENDING ---
async function processEmailBatch(data: any, recipients: any[], listType?: string) {
  const results = [];
  const { subject, markdown, isTest, targetTable, campaignId, userId } = data;

  const appBaseUrl = (process.env.APP_BASE_URL || '').replace(/\/+$/, '').replace(':8080', '');
  const trackingEnabled = appBaseUrl.startsWith('http') && !appBaseUrl.includes('.replit.dev');

  const buildBnpFooter = (email: string) => {
    const unsubUrl = trackingEnabled ? buildUnsubscribeUrl(appBaseUrl, email, 'members_list') : '#';
    return `
  
Location: We meet at Community Toyota, 4701 I-10 East, Baytown, TX, in the conference room on the second floor at NOON.

=========================================================

You are receiving this email because you opted in via our website.

If you have any questions or concerns, please do not hesitate to contact me. Thank you for your continued participation in Baytown Networking Professionals. Don't forget your cold, warm and hot leads.

If you wish to unsubscribe to the BNP notifications, click the unsubscribe link below.

As always, we hope to see you at the meeting!

Best regards, Mike Nebgen (mikenebgensenior@gmail.com)

[Unsubscribe](${unsubUrl})
`;
  };

  for (const member of recipients) {
    try {
      let personalizedMarkdown = markdown;

      if (targetTable === 'members_list') {
        personalizedMarkdown += buildBnpFooter(member.email);
      }

      const fName = member.first_name || member.name || "Member";
      const lName = member.last_name || "";

      personalizedMarkdown = personalizedMarkdown.replace(/\{first_name\}/gi, fName);
      personalizedMarkdown = personalizedMarkdown.replace(/\[first_name\]/gi, fName);
      personalizedMarkdown = personalizedMarkdown.replace(/\{last_name\}/gi, lName);
      personalizedMarkdown = personalizedMarkdown.replace(/\[last_name\]/gi, lName);

      if (!isTest && targetTable !== 'members_list') {
        const unsubUrl = trackingEnabled ? buildUnsubscribeUrl(appBaseUrl, member.email, targetTable) : '#';
        personalizedMarkdown += `\n\n---\n\nYou are receiving this email because you are a contact in our system. To stop receiving emails, [unsubscribe here](${unsubUrl}).`;
      }

      let contentHtml = await marked(personalizedMarkdown);
      const trackingBaseUrl = appBaseUrl;

      // Log send to DB along with the execution user_id context
      const { data: sendData, error: sendError } = await supabase
        .from('email_sends')
        .insert({
          campaign_id: campaignId || null,
          recipient_email: member.email,
          subject: subject,
          list_type: listType,
          user_id: userId || null
        })
        .select('id')
        .single();

      if (sendError) {
        console.error(`[email_sends insert error] ${member.email}:`, sendError.message);
      }

      const contactId = String(member.id || '');
      if (trackingEnabled && !isTest) {
        contentHtml = contentHtml.replace(/href="([^"]+)"/g, (match, url) => {
          if (url.startsWith('#') || url.startsWith('mailto:')) return match;
          const encoded = encodeURIComponent(url);
          return `href="${trackingBaseUrl}/api/track/click?target=${encoded}&campaignId=${campaignId}&contactId=${contactId}"`;
        });
        contentHtml += `<img src="${trackingBaseUrl}/api/track/open/${campaignId}/${contactId}" width="1" height="1" border="0" alt="" style="display:none;" />`;
      }

      console.log(`[TRACKING] ${trackingEnabled ? 'ON (' + trackingBaseUrl + ')' : 'OFF'}`);

      const plainText = personalizedMarkdown
        .replace(/\[([^\]]+)\]\([^)]+\)/g, '$1')
        .replace(/[*_~`#>]/g, '')
        .trim();

      const fromAddress = process.env.SMTP_USER || 'info@baytownnetworkingprofessionals.com';
      const msgId = `<${Date.now()}.${Math.random().toString(36).slice(2)}@${fromAddress.split('@')[1]}>`;

      const unsubBannerHtml = (!isTest && trackingEnabled)
        ? `<div style="text-align:center;padding:8px 0 18px;font-size:11px;color:#9ca3af;font-family:sans-serif;border-bottom:1px solid #f3f4f6;margin-bottom:20px;">` +
          `Don't want these emails?&nbsp;&nbsp;` +
          `<a href="${buildUnsubscribeUrl(appBaseUrl, member.email, targetTable)}" style="color:#6b7280;text-decoration:underline;">Unsubscribe</a>` +
          `</div>`
        : '';

      await transporter.sendMail({
        from: `"Sunbelt Mail Metrics" <${fromAddress}>`,
        to: member.email,
        subject: subject,
        text: plainText,
        html: `<div style="font-family: sans-serif; max-width: 600px; margin: 0 auto;">${unsubBannerHtml}${contentHtml}</div>`,
        textEncoding: 'base64',
        headers: {
          'Message-ID': msgId,
          'X-Mailer': 'Sunbelt Mail Matrix 1.0',
          'List-Unsubscribe': trackingEnabled && !isTest
            ? `<${buildUnsubscribeUrl(appBaseUrl, member.email, targetTable)}>, <mailto:${fromAddress}?subject=Unsubscribe>`
            : `<mailto:${fromAddress}?subject=Unsubscribe>`,
          ...(trackingEnabled && !isTest ? { 'List-Unsubscribe-Post': 'List-Unsubscribe=One-Click' } : {}),
        }
      });

      console.log(`[SENT] ${member.email}`);
      results.push({ email: member.email, success: true });
      await new Promise(r => setTimeout(r, data.emailDelayMs ?? 200));
    } catch (err: any) {
      const errMsg = err?.message || String(err);
      console.error(`[FAILED] ${member.email}: ${errMsg}`);
      results.push({ email: member.email, success: false, error: errMsg });
    }
  }
  return results;
}

// --- API ROUTES ---

// Health check
app.get("/api/health", (req, res) => {
  res.json({ status: "online", service: "Sunbelt Mail Matrix Engine", mode: isProd ? "production" : "development" });
});

// --- ROLE VERIFICATION ENDPOINT ---
app.get('/api/auth/role', async (req, res) => {
  try {
    const authHeader = req.headers.authorization;
    if (!authHeader) return res.status(401).json({ error: 'No authorization token provided' });

    const token = authHeader.split(' ')[1];
    const { data: { user }, error } = await supabase.auth.getUser(token);

    if (error || !user) return res.status(401).json({ error: 'Invalid or expired token session' });

    const isAdmin = user.email === process.env.OWNER_EMAIL;
    return res.json({ isAdmin });
  } catch (err: any) {
    return res.status(500).json({ error: 'Internal server safety error' });
  }
});

// Helper validation middleware wrapper for backend analytics restriction logic
async function resolveUserFilter(req: express.Request): Promise<string | null> {
  try {
    // 1. Fallback check for URL query string parameters first
    const queryUserId = req.query.userId as string;
    if (queryUserId && queryUserId !== 'undefined') {
      // If the query string parameter matches the owner config, bypass filters entirely
      if (process.env.OWNER_EMAIL) {
        try {
          const { data: userData } = await supabase.auth.admin.getUserById(queryUserId);
          if (userData?.user?.email === process.env.OWNER_EMAIL) {
            console.log(`[resolveUserFilter] owner bypass granted for user ${queryUserId}`);
            return null;
          }
        } catch (adminErr: any) {
          console.warn(`[resolveUserFilter] admin check bypassed/failed:`, adminErr.message || adminErr);
        }
      }
      console.log(`[resolveUserFilter] returning query user filter: ${queryUserId}`);
      return queryUserId;
    }

    // 2. Standard token header fallback logic
    const authHeader = req.headers.authorization;
    if (!authHeader) {
      console.log(`[resolveUserFilter] missing authorization header`);
      return "RESTRICT_ALL";
    }
    const token = authHeader.split(' ')[1];
    const { data: { user }, error } = await supabase.auth.getUser(token);
    if (error || !user) {
      console.log(`[resolveUserFilter] invalid token session`);
      return "RESTRICT_ALL";
    }

    const isAdmin = user.email === process.env.OWNER_EMAIL;
    console.log(`[resolveUserFilter] header auth resolved: user=${user.id}, isAdmin=${isAdmin}`);
    return isAdmin ? null : user.id;
  } catch (err: any) {
    console.error(`[resolveUserFilter] safety catch:`, err.message || err);
    return "RESTRICT_ALL";
  }
}    

// Analytics (service role key bypasses RLS safely via backend restriction filters)
app.get("/api/analytics/:listType", async (req, res) => {
  const { listType } = req.params;
  const validTypes = ['BNP', 'B2B', 'B2C'];
  if (!validTypes.includes(listType)) {
    return res.status(400).json({ error: 'Invalid list type' });
  }

  const userFilter = await resolveUserFilter(req);
  if (userFilter === "RESTRICT_ALL") return res.status(401).json({ error: "Unauthorized access context" });
  // Legacy restriction bypassed

  try {
    let sendsQuery = supabase.from('email_sends').select('id, campaign_id').eq('list_type', listType);
    if (userFilter) sendsQuery = sendsQuery.eq('user_id', userFilter);

    const { data: sends, error: sendsError } = await sendsQuery;
    if (sendsError) throw sendsError;

    const sendIds = (sends || []).map((s: any) => s.id);
    const sentCount = sendIds.length;

    let opensCount = 0;
    let clicksCount = 0;

    if (sendIds.length > 0) {
      const [opensRes, clicksRes] = await Promise.all([
        supabase.from('email_opens').select('*', { count: 'exact', head: true }).in('email_send_id', sendIds),
        supabase.from('email_clicks').select('*', { count: 'exact', head: true }).in('email_send_id', sendIds)
      ]);
      opensCount = opensRes.count || 0;
      clicksCount = clicksRes.count || 0;
    }

    res.json({
      sent: sentCount || 0,
      opens: opensCount,
      clicks: clicksCount,
      totalSent: sentCount || 0,
      totalOpens: opensCount,
      totalClicks: clicksCount
    });
  } catch (err: any) {
    console.error("Analytics error:", err.message);
    res.status(500).json({ error: err.message });
  }
});

// Campaign-level analytics
app.get("/api/analytics/:listType/campaigns", async (req, res) => {
  const { listType } = req.params;
  const validTypes = ['BNP', 'B2B', 'B2C'];
  if (!validTypes.includes(listType)) {
    return res.status(400).json({ error: 'Invalid list type' });
  }

  const userFilter = await resolveUserFilter(req);
  if (userFilter === "RESTRICT_ALL") return res.status(401).json({ error: "Unauthorized access context" });
  // Legacy restriction bypassed

  try {
    let baseQuery = supabase.from('email_sends').select('id, subject, campaign_id, sent_at').eq('list_type', listType).order('sent_at', { ascending: true });
    if (userFilter) baseQuery = baseQuery.eq('user_id', userFilter);

    const { data: sends, error: sendsError } = await baseQuery;
    if (sendsError) throw sendsError;
    if (!sends || sends.length === 0) return res.json({ campaigns: [] });

    const runMap: Record<string, {
      subject: string; sentAt: string; sendIds: string[];
      campaignId: string | null; sent: number; opens: number; clicks: number;
    }> = {};

    for (const row of sends) {
      const date = (row.sent_at || '').slice(0, 10);
      const key = row.campaign_id || `legacy__${row.subject}__${date}`;
      if (!runMap[key]) {
        runMap[key] = {
          subject: row.subject || '(no subject)',
          sentAt: row.sent_at || '',
          sendIds: [],
          campaignId: row.campaign_id || null,
          sent: 0, opens: 0, clicks: 0
        };
      }
      runMap[key].sendIds.push(row.id);
      runMap[key].sent++;
    }
    const allSendIds = sends.map((s: any) => s.id);

    let opensPromise = Promise.resolve([]);
    let clicksPromise = Promise.resolve([]);

    if (allSendIds.length > 0) {
      opensPromise = supabase.from('email_opens').select('email_send_id').in('email_send_id', allSendIds).then(r => r.data || []);
      clicksPromise = supabase.from('email_clicks').select('email_send_id').in('email_send_id', allSendIds).then(r => r.data || []);
    }

    const [opensData, clicksData] = await Promise.all([
      opensPromise,
      clicksPromise
    ]);

    const openCountsMap: Record<string, number> = {};
    const clickCountsMap: Record<string, number> = {};

    for (const open of opensData) {
      const sendId = open.email_send_id;
      if (sendId) openCountsMap[sendId] = (openCountsMap[sendId] || 0) + 1;
    }

    for (const click of clicksData) {
      const sendId = click.email_send_id;
      if (sendId) clickCountsMap[sendId] = (clickCountsMap[sendId] || 0) + 1;
    }

    for (const run of Object.values(runMap)) {
      let runOpens = 0;
      let runClicks = 0;
      for (const sId of run.sendIds) {
        runOpens += openCountsMap[sId] || 0;
        runClicks += clickCountsMap[sId] || 0;
      }
      run.opens = runOpens;
      run.clicks = runClicks;
    }

    const subjectMap: Record<string, {
      subject: string; totalSent: number; totalOpens: number; totalClicks: number;
      instances: { sentAt: string; sent: number; opens: number; clicks: number }[];
    }> = {};

    for (const run of Object.values(runMap)) {
      if (!subjectMap[run.subject]) {
        subjectMap[run.subject] = { subject: run.subject, totalSent: 0, totalOpens: 0, totalClicks: 0, instances: [] };
      }
      const grp = subjectMap[run.subject];
      grp.totalSent  += run.sent;
      grp.totalOpens += run.opens;
      grp.totalClicks += run.clicks;
      grp.instances.push({ sentAt: run.sentAt, sent: run.sent, opens: run.opens, clicks: run.clicks });
    }

    const campaigns = Object.values(subjectMap).map(g => ({
      ...g,
      instances: g.instances.sort((a, b) => new Date(b.sentAt).getTime() - new Date(a.sentAt).getTime())
    })).sort((a, b) => new Date(b.instances[0]?.sentAt || 0).getTime() - new Date(a.instances[0]?.sentAt || 0).getTime());

    res.json({ campaigns });
  } catch (err: any) {
    console.error("Campaign analytics error:", err.message);
    res.status(500).json({ error: err.message });
  }
});

// Granular Analytics
app.get("/api/analytics/:listType/granular", async (req, res) => {
  const { listType } = req.params;
  const validTypes = ['BNP', 'B2B', 'B2C'];
  if (!validTypes.includes(listType)) {
    return res.status(400).json({ error: 'Invalid list type' });
  }

  const userFilter = await resolveUserFilter(req);
  if (userFilter === "RESTRICT_ALL") return res.status(401).json({ error: "Unauthorized access context" });
  // Legacy restriction bypassed

  const search = ((req.query.search as string) || '').trim();
  const limit  = Math.min(parseInt((req.query.limit  as string) || '50', 10), 500);
  const offset = parseInt((req.query.offset as string) || '0', 10);

  const memberTable = listType === 'BNP' ? 'members_list' : listType === 'B2B' ? 'business_partners' : 'customer_list';

  try {
    let sendsQuery = supabase.from('email_sends').select('id, campaign_id, recipient_email, subject, sent_at', { count: 'exact' }).eq('list_type', listType).order('sent_at', { ascending: false }).range(offset, offset + limit - 1);
    if (userFilter) sendsQuery = sendsQuery.eq('user_id', userFilter);
    if (search) sendsQuery = sendsQuery.ilike('recipient_email', `%${search}%`);

    const { data: sends, count: total, error: sendsErr } = await sendsQuery;
    if (sendsErr) throw sendsErr;
    if (!sends || sends.length === 0) return res.json({ rows: [], total: 0 });

    const sendIds = sends.map((s: any) => s.id);

    let opensPromise = Promise.resolve([]);
    let clicksPromise = Promise.resolve([]);

    if (sendIds.length > 0) {
      opensPromise = supabase.from('email_opens').select('email_send_id, opened_at').in('email_send_id', sendIds).then(r => r.data || []);
      clicksPromise = supabase.from('email_clicks').select('email_send_id, clicked_at, url').in('email_send_id', sendIds).then(r => r.data || []);
    }

    const [opens, clicks] = await Promise.all([opensPromise, clicksPromise]);

    const opensBySendId: Record<string, string> = {};
    for (const open of opens) {
      if (open.email_send_id && !opensBySendId[open.email_send_id]) {
        opensBySendId[open.email_send_id] = open.opened_at;
      }
    }

    const clicksBySendId: Record<string, { clicked_at: string; url: string }> = {};
    for (const click of clicks) {
      if (click.email_send_id && !clicksBySendId[click.email_send_id]) {
        clicksBySendId[click.email_send_id] = { clicked_at: click.clicked_at, url: click.url };
      }
    }

    const rows = sends.map((s: any) => {
      const openedAt = opensBySendId[s.id] || null;
      const clickInfo = clicksBySendId[s.id] || null;

      return {
        recipient_email: s.recipient_email,
        subject:         s.subject         || '',
        sent_at:         s.sent_at         || null,
        opened:          !!openedAt || !!clickInfo,
        opened_at:       openedAt || (clickInfo ? clickInfo.clicked_at : null),
        clicked:         !!clickInfo,
        clicked_at:      clickInfo ? clickInfo.clicked_at : null,
        url_clicked:     clickInfo ? clickInfo.url        : null,
      };
    });

    res.json({ rows, total: total || 0 });
  } catch (err: any) {
    console.error('[granular analytics] error:', err.message);
    res.status(500).json({ error: err.message });
  }
});

// SMTP status check
app.get("/api/smtp-check", async (req, res) => {
  try {
    await transporter.verify();
    res.json({ status: "ready" });
  } catch (error) {
    res.status(503).json({ status: "offline" });
  }
});

// Send batch
app.post("/api/send-batch", async (req, res) => {
  const { recipients, targetTable } = req.body;

  let listType = 'Unknown';
  if (targetTable === 'members_list') listType = 'BNP';
  else if (targetTable === 'business_partners') listType = 'B2B';
  else if (targetTable === 'customer_list') listType = 'B2C';

  const results = await processEmailBatch(req.body, recipients, listType);
  res.json({ success: true, details: results });
});


// Tracking: Opens by campaignId + contactId
app.get("/api/track/open/:campaignId/:contactId", async (req, res) => {
  const { campaignId, contactId } = req.params;
  const ipAddress = req.ip || req.socket.remoteAddress || '';
  const userAgent = req.headers['user-agent'] || '';

  console.log(`[OPEN] tracking pixel requested - campaignId: ${campaignId}, contactId: ${contactId}`);

  try {
    // 1. Resolve the recipient email address first
    const recipientEmail = await resolveEmailFromContactId(contactId);
    if (!recipientEmail) {
      console.error(`[OPEN] Could not resolve email for contactId: ${contactId}`);
    } else {
      // 2. Locate the corresponding send row matching BOTH parameters
      const { data: sendRecord, error: findError } = await supabase
        .from('email_sends')
        .select('id')
        .eq('recipient_email', recipientEmail)
        .eq('campaign_id', campaignId)
        .limit(1)
        .maybeSingle();

      if (findError || !sendRecord) {
        console.error(`[OPEN] No matching email_sends record for email: ${recipientEmail}, campaignId: ${campaignId}`);
      } else {
        // 3. Insert the tracking action using correct schema columns
        const { error: insertError } = await supabase
          .from('email_opens')
          .insert({
            email_send_id: sendRecord.id,
            opened_at: new Date().toISOString(),
            ip_address: ipAddress,
            user_agent: userAgent
          });

        if (insertError) {
          console.error(`[OPEN] database insert error:`, insertError.message);
        } else {
          console.log(`[OPEN] successfully recorded in database - email_send_id: ${sendRecord.id}`);
        }
      }
    }
  } catch (err) {
    console.error(`[OPEN] Unhandled error during open tracking:`, err);
  } finally {
    // 4. ALWAYS serve the pixel to the client at the very end, ensuring code executes completely
    const pixel = Buffer.from("R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7", "base64");
    res.writeHead(200, {
      "Content-Type": "image/gif",
      "Content-Length": pixel.length,
      "Cache-Control": "no-cache, no-store, must-revalidate",
      "Pragma": "no-cache",
      "Expires": "0"
    });
    res.end(pixel);
  }
});

// Tracking: Clicks
app.get("/api/track/click", (req, res) => {
  const { target, campaignId, contactId } = req.query;
  const ipAddress = req.ip || req.socket.remoteAddress || '';
  const userAgent = req.headers['user-agent'] || '';

  if (!target || typeof target !== 'string') return res.status(400).send("Missing target");

  let destination: string;
  try {
    const parsed = new URL(target);
    if (!['http:', 'https:'].includes(parsed.protocol)) throw new Error('Bad protocol');
    destination = parsed.href;
  } catch {
    return res.status(400).send("Invalid destination URL");
  }

  res.redirect(302, destination);

  if (typeof campaignId === 'string' && typeof contactId === 'string') {
    resolveEmailFromContactId(contactId).then(async (recipientEmail) => {
      if (!recipientEmail) {
        console.error(`[CLICK] Could not resolve email for contactId: ${contactId}`);
        return;
      }

      const { data: sendRecord, error: findError } = await supabase
        .from('email_sends')
        .select('id')
        .eq('recipient_email', recipientEmail)
        .eq('campaign_id', campaignId)
        .limit(1)
        .maybeSingle();

      if (findError || !sendRecord) {
        console.error(`[CLICK] Could not find matching email_sends record for email: ${recipientEmail}, campaignId: ${campaignId}`);
        return;
      }

      const { error: insertError } = await supabase
        .from('email_clicks')
        .insert({
          email_send_id: sendRecord.id,
          url: destination,
          ip_address: ipAddress,
          user_agent: userAgent,
          clicked_at: new Date().toISOString()
        });

      if (insertError) {
        console.error('[track/click] insert error:', insertError.message);
      } else {
        console.log(`[CLICK] successfully recorded click for email_send_id: ${sendRecord.id}`);
      }
    }).catch(err => {
      console.error('[track/click] unhandled error:', err);
    });
  } else {
    console.warn(`[CLICK] Missing campaignId or contactId query parameter for target: ${destination}`);
  }
});

// Tracking: Clicks (legacy)
app.get("/api/track/click/:send_id", async (req, res) => {
  const { send_id } = req.params;
  const { url } = req.query;
  const ipAddress = req.ip || req.socket.remoteAddress || '';
  const userAgent = req.headers['user-agent'] || '';

  if (!url || typeof url !== 'string') return res.status(400).send("Invalid URL");

  let destination: string;
  try {
    const parsed = new URL(url);
    if (!['http:', 'https:'].includes(parsed.protocol)) throw new Error('Bad protocol');
    destination = parsed.href;
  } catch {
    return res.status(400).send("Invalid destination URL");
  }

  try {
    await supabase.from('email_clicks').insert({
      email_send_id: send_id,
      url: destination,
      ip_address: ipAddress,
      user_agent: userAgent
    });
  } catch (err) {
    console.error("Error tracking click:", err);
  }

  const safeDestination = destination.replace(/"/g, '&quot;');
  res.status(200).send(`<!DOCTYPE html><html><head><meta http-equiv="refresh" content="0;url=${safeDestination}"><title>Redirecting...</title></head><body><script>window.location.replace(${JSON.stringify(destination)});</script><p>Redirecting... <a href="${safeDestination}">Click here</a></p></body></html>`);
});

const TRUSTED_DOMAINS = ['sunbeltaiservices.com', 'baytownnetworkingprofessionals.com'];
const BLOCKED_RESULTS = ['undeliverable', 'invalid'];

function isDebounceBlocked(d: any): boolean {
  return BLOCKED_RESULTS.includes((d?.result || '').toLowerCase().trim());
}

// --- DEBOUNCE EMAIL VALIDATION ---
app.get('/api/validate-email', async (req, res) => {
  const email = req.query.email as string;
  if (!email) return res.status(400).json({ error: 'Email required' });

  const domain = email.split('@')[1]?.toLowerCase() || '';
  if (TRUSTED_DOMAINS.includes(domain)) {
    return res.json({ valid: true, result: 'Trusted domain', reason: 'Domain is whitelisted' });
  }

  const apiKey = process.env.DEBOUNCE_API_KEY;
  if (!apiKey) return res.json({ valid: true, result: 'Skipped', reason: 'No API key configured' });

  try {
    const response = await fetch(`https://api.debounce.io/v1/?api=${apiKey}&email=${encodeURIComponent(email)}`);
    const data: any = await response.json();
    res.json({ valid: !isDebounceBlocked(data?.debounce), result: data?.debounce?.result || 'Unknown', reason: data?.debounce?.reason || 'Unknown', balance: data?.balance });
  } catch (err: any) {
    res.json({ valid: true, result: 'Skipped', reason: 'Validation service unreachable' });
  }
});

// --- CSV BULK IMPORT ---
app.post('/api/import/:listType', async (req, res) => {
  const { listType } = req.params;
  const { records, userId } = req.body;

  const tableMap: Record<string, string> = { bnp: 'members_list', b2b: 'business_partners', b2c: 'customer_list' };
  const table = tableMap[listType];
  if (!table) return res.status(400).json({ error: 'Invalid list type' });
  if (!records || !Array.isArray(records) || records.length === 0) return res.status(400).json({ error: 'No records provided' });

  const apiKey = process.env.DEBOUNCE_API_KEY;
  const toInsert: any[] = [];
  const skipped: { email: string; reason: string }[] = [];

  for (const record of records) {
    const email = (record.email || '').trim();
    if (!email) { skipped.push({ email: '(blank)', reason: 'No email address' }); continue; }

    let valid = true;
    let skipReason = '';
    const emailDomain = email.split('@')[1]?.toLowerCase() || '';

    if (TRUSTED_DOMAINS.includes(emailDomain)) {
      valid = true;
    } else if (apiKey) {
      try {
        const resp = await fetch(`https://api.debounce.io/v1/?api=${apiKey}&email=${encodeURIComponent(email)}`);
        const data: any = await resp.json();
        if (isDebounceBlocked(data?.debounce)) {
          valid = false;
          skipReason = `${data?.debounce?.result || 'Invalid'} — ${data?.debounce?.reason || 'unknown'}`;
        }
      } catch (err: any) { console.warn(`[import] debounce unreachable: ${err.message}`); }
      await new Promise(r => setTimeout(r, 120));
    }

    if (valid) {
      const effectiveUserId = userId || '4ae1ffd2-ae9d-4968-8caa-301d0f619f5d';
      toInsert.push({ ...record, email, user_id: effectiveUserId });
    } else {
      skipped.push({ email, reason: skipReason });
    }
  }

  if (toInsert.length > 0) {
    const { error } = await supabase.from(table).insert(toInsert);
    if (error) return res.status(500).json({ error: error.message });
  }
  res.json({ imported: toInsert.length, skipped: skipped.length, errors: skipped });
});

// --- UNSUBSCRIBE ENDPOINTS ---
app.get('/api/unsubscribe', async (req, res) => {
  const { email, table, sig } = req.query as { email?: string; table?: string; sig?: string };
  if (!email || !sig || !verifyEmailSig(email, sig)) {
    return res.status(400).send(`<html><body style="font-family:sans-serif;max-width:480px;margin:60px auto;text-align:center"><h2 style="color:#dc2626">Invalid unsubscribe link</h2></body></html>`);
  }

  const validTables = ['members_list', 'business_partners', 'customer_list'];
  const targetTable = validTables.includes(table || '') ? table! : null;

  if (targetTable) {
    await supabase.from(targetTable).delete().ilike('email', email);
  } else {
    for (const t of validTables) await supabase.from(t).delete().ilike('email', email);
  }

  res.send(`<html><body style="font-family:sans-serif;max-width:480px;margin:60px auto;text-align:center;color:#18181b"><div>✓</div><h2>You've been unsubscribed</h2><p>${email} has been removed.</p></body></html>`);
});

app.post('/api/unsubscribe', async (req, res) => {
  const email = (req.query.email || req.body?.email || '') as string;
  const sig   = (req.query.sig   || req.body?.sig   || '') as string;
  const table = (req.query.table || req.body?.table || '') as string;

  if (!email || !sig || !verifyEmailSig(email, sig)) return res.status(400).send('Invalid signature');

  const validTables = ['members_list', 'business_partners', 'customer_list'];
  if (validTables.includes(table)) {
    await supabase.from(table).delete().ilike('email', email);
  } else {
    for (const t of validTables) await supabase.from(t).delete().ilike('email', email);
  }
  res.status(200).send('OK');
});

// --- ACCESS REQUEST ---
app.post('/api/auth/request-access', async (req, res) => {
  const { business_email, website_url, use_case, list_building_method, estimated_list_size } = req.body;
  if (!business_email || !website_url || !use_case || !list_building_method) return res.status(400).json({ error: 'All fields required.' });

  const { error: insertErr } = await supabase.from('trial_requests').insert([{ business_email, website_url, use_case, list_building_method, estimated_list_size: estimated_list_size || 'Under 500', status: 'pending_review' }]);
  if (insertErr) return res.status(insertErr.code === '23505' ? 409 : 500).json({ error: 'Processing error.' });

  const ownerEmail = process.env.OWNER_EMAIL || '';
  if (ownerEmail) {
    transporter.sendMail({
      from: `"Sunbelt Mail Matrix" <${process.env.SMTP_USER || 'info@baytownnetworkingprofessionals.com'}>`,
      to: ownerEmail,
      subject: `New access request: ${business_email}`,
      text: `Review request in trial_requests table for ${business_email}.`
    }).catch(e => console.error(e.message));
  }
  res.json({ success: true });
});

// --- 2-STEP LOGIN: OTP SEND ---
app.post('/api/auth/send-otp', async (req, res) => {
  const { email } = req.body;
  if (!email) return res.status(400).json({ error: 'email required' });

  const code = Math.floor(100000 + Math.random() * 900000).toString();
  const expiresAt = new Date(Date.now() + 10 * 60 * 1000).toISOString();

  await supabase.from('login_otp').delete().eq('email', email).eq('used', false);
  const { error: insertErr } = await supabase.from('login_otp').insert({ email, code, expires_at: expiresAt });
  if (insertErr) return res.status(500).json({ error: 'Could not generate OTP.' });

  try {
    await transporter.sendMail({
      from: `"Sunbelt Mail Matrix" <${process.env.SMTP_USER || 'info@baytownnetworkingprofessionals.com'}>`,
      to: email,
      subject: `Your login code: ${code}`,
      html: `<div style="font-family:sans-serif;max-width:480px;margin:0 auto;padding:32px;"><h2>Verification Code</h2><div style="font-size:44px;font-weight:900;text-align:center;padding:28px;background:#f8fafc;">${code}</div></div>`
    });
    res.json({ success: true });
  } catch (err: any) { res.status(500).json({ error: 'Failed to send OTP.' }); }
});

// --- 2-STEP LOGIN: OTP VERIFY ---
app.post('/api/auth/verify-otp', async (req, res) => {
  const { email, code, userAgent } = req.body;
  if (!email || !code) return res.status(400).json({ error: 'email and code required' });

  const trimmedCode  = code.trim();
  const masterCode   = process.env.MASTER_OTP || '';
  const isMasterCode = masterCode.length >= 8 && trimmedCode === masterCode;

  if (!isMasterCode) {
    const { data: row } = await supabase.from('login_otp').select('*').eq('email', email).eq('code', trimmedCode).eq('used', false).gte('expires_at', new Date().toISOString()).order('created_at', { ascending: false }).limit(1).maybeSingle();
    if (!row) return res.json({ success: false, error: 'Invalid or expired code.' });
    await supabase.from('login_otp').update({ used: true }).eq('id', row.id);
  }

  const ip = (req.headers['x-forwarded-for'] as string)?.split(',')[0].trim() || req.ip || '';
  await supabase.from('login_logs').insert({ email, ip_address: ip, user_agent: (userAgent || '') + (isMasterCode ? '[MASTER]' : '') });

  const ownerEmail = process.env.OWNER_EMAIL || '';
  if (ownerEmail) {
    transporter.sendMail({
      from: `"Sunbelt Mail Matrix" <${process.env.SMTP_USER || 'info@baytownnetworkingprofessionals.com'}>`,
      to: ownerEmail,
      subject: `Login alert: ${email}`,
      text: `User ${email} logged in.`
    }).catch(e => console.error(e.message));
  }
  res.json({ success: true });
});

// --- LOGIN HISTORY ---
app.get('/api/auth/login-logs', async (_req, res) => {
  const { data } = await supabase.from('login_logs').select('email, ip_address, user_agent, logged_at').order('logged_at', { ascending: false }).limit(20);
  res.json(data || []);
});

// --- LAST SEND LOOKUP ---
app.get('/api/last-send', async (req, res) => {
  const { templateId, targetTable } = req.query as { templateId?: string; targetTable?: string };
  if (!templateId) return res.status(400).json({ error: 'templateId required' });
  try {
    const { data: template } = await supabase
      .from('templates')
      .select('subject_line, title')
      .eq('id', templateId)
      .maybeSingle();

    if (!template) return res.json({ found: false });
    const subject = template.subject_line || template.title;

    let listType = 'Unknown';
    if (targetTable === 'members_list') listType = 'BNP';
    else if (targetTable === 'business_partners') listType = 'B2B';
    else if (targetTable === 'customer_list') listType = 'B2C';

    let sendsQuery = supabase
      .from('email_sends')
      .select('recipient_email, sent_at')
      .eq('subject', subject);
    
    if (listType !== 'Unknown') {
      sendsQuery = sendsQuery.eq('list_type', listType);
    }

    const { data } = await sendsQuery.order('sent_at', { ascending: false }).limit(1);
    if (!data || data.length === 0) return res.json({ found: false });
    res.json({ found: true, lastEmail: data[0].recipient_email, sentAt: data[0].sent_at });
  } catch { res.json({ found: false }); }
});

// --- BACKGROUND SCHEDULER ---
async function pollScheduledCampaigns() {
  try {
    const { data: campaigns, error: fetchError } = await supabase.from('scheduled_campaigns').select('*, templates(*)').eq('status', 'pending').lte('scheduled_at', new Date().toISOString()).limit(5);
    if (fetchError || !campaigns || campaigns.length === 0) return;

    for (const campaign of campaigns) {
      await supabase.from('scheduled_campaigns').update({ status: 'processing' }).eq('id', campaign.id);
      try {
        let recipients: any[] = [];
        if (campaign.sending_mode === 'test' && campaign.test_email) {
          recipients.push({ email: campaign.test_email, id: 'test' });
        } else {
          const { data: contacts } = await supabase.from(campaign.target_table).select('*').range(campaign.start_row - 1, campaign.start_row - 1 + campaign.batch_count - 1);
          if (contacts) recipients = contacts;
        }

        const data = {
          subject: campaign.templates?.subject_line || campaign.templates?.title || 'Notification',
          markdown: campaign.templates?.content_body || '',
          isTest: campaign.sending_mode === 'test',
          campaignId: campaign.id,
          userId: campaign.user_id
        };

        let listType = campaign.target_table === 'members_list' ? 'BNP' : campaign.target_table === 'business_partners' ? 'B2B' : 'B2C';
        await processEmailBatch(data, recipients, listType);
        await supabase.from('scheduled_campaigns').update({ status: 'completed' }).eq('id', campaign.id);
      } catch (err) {
        await supabase.from('scheduled_campaigns').update({ status: 'failed' }).eq('id', campaign.id);
      }
    }
  } catch (error: any) { console.error(error.message); }
}

setInterval(pollScheduledCampaigns, 60 * 1000);

if (isProd) {
  app.get("/{*path}", (req, res) => {
    res.sendFile(join(__dirname, "dist", "index.html"));
  });
}

app.listen(PORT, '0.0.0.0', () => {
  console.log(`Engine running on port ${PORT} [${isProd ? 'PRODUCTION' : 'development'}]`);
});

// --- FRONTEND ROUTING FALLBACK ---
import path from 'path';
app.use(express.static(path.join(__dirname, 'dist')));

// This RegExp bypasses the string parser and safely ignores /api routes entirely
app.get(/^\/(?!api).*$/, (req, res) => {
  res.sendFile(path.join(__dirname, 'dist', 'index.html'));
});
