import { Response } from 'express';
import { nanoid } from 'nanoid';
import { prisma } from '../config/database';
import { AuthRequest } from '../middleware/auth';
import { generateQRCodeBuffer } from '../utils/qrcode';
import { uploadToS3 } from '../config/s3';
import { fireOnRegistration } from '../utils/email-triggers';
import { io } from '../server';

// --------------- Helpers ---------------

function extractSheetId(url: string): string | null {
  // Supports:
  //   https://docs.google.com/spreadsheets/d/SHEET_ID/...
  //   https://docs.google.com/forms/d/FORM_ID/...  (won't work, need sheet)
  const match = url.match(/\/spreadsheets\/d\/([a-zA-Z0-9_-]+)/);
  return match ? match[1] : null;
}

function extractGid(url: string): string {
  const match = url.match(/[#&?]gid=(\d+)/);
  return match ? match[1] : '0';
}

async function fetchSheetCSV(sheetId: string, gid: string): Promise<{ ok: boolean; csv: string; error?: string }> {
  // Try multiple approaches since Google has different access rules for each endpoint

  // Approach 1: gviz/tq endpoint — works for "Anyone with the link" sheets
  const gvizUrl = `https://docs.google.com/spreadsheets/d/${sheetId}/gviz/tq?tqx=out:csv&gid=${gid}`;
  try {
    const r1 = await fetch(gvizUrl, { redirect: 'follow' });
    if (r1.ok) {
      const ct = r1.headers.get('content-type') || '';
      const text = await r1.text();
      // gviz returns text/csv for valid responses, text/html for errors
      if (ct.includes('text/csv') || (text.length > 0 && !text.trimStart().startsWith('<!DOCTYPE') && !text.trimStart().startsWith('<html'))) {
        return { ok: true, csv: text };
      }
    }
  } catch { /* try next approach */ }

  // Approach 2: /export?format=csv — works for "Published to web" sheets
  const exportUrl = `https://docs.google.com/spreadsheets/d/${sheetId}/export?format=csv&gid=${gid}`;
  try {
    const r2 = await fetch(exportUrl, { redirect: 'follow' });
    if (r2.ok) {
      const text = await r2.text();
      if (!text.trimStart().startsWith('<!DOCTYPE') && !text.trimStart().startsWith('<html')) {
        return { ok: true, csv: text };
      }
    }
  } catch { /* try next approach */ }

  // Approach 3: /pub?output=csv — works for explicitly published sheets
  const pubUrl = `https://docs.google.com/spreadsheets/d/${sheetId}/pub?output=csv&gid=${gid}`;
  try {
    const r3 = await fetch(pubUrl, { redirect: 'follow' });
    if (r3.ok) {
      const text = await r3.text();
      if (!text.trimStart().startsWith('<!DOCTYPE') && !text.trimStart().startsWith('<html')) {
        return { ok: true, csv: text };
      }
    }
  } catch { /* all approaches failed */ }

  return {
    ok: false,
    csv: '',
    error: 'Cannot access the Google Sheet. Please ensure:\n1. Open the Google Sheet → Share → "Anyone with the link" → Viewer\n2. Or go to File → Share → Publish to web → CSV',
  };
}

function parseCSV(text: string): string[][] {
  const rows: string[][] = [];
  let current = '';
  let inQuotes = false;
  let row: string[] = [];

  for (let i = 0; i < text.length; i++) {
    const ch = text[i];
    const next = text[i + 1];

    if (inQuotes) {
      if (ch === '"' && next === '"') {
        current += '"';
        i++; // skip next quote
      } else if (ch === '"') {
        inQuotes = false;
      } else {
        current += ch;
      }
    } else {
      if (ch === '"') {
        inQuotes = true;
      } else if (ch === ',') {
        row.push(current.trim());
        current = '';
      } else if (ch === '\n' || (ch === '\r' && next === '\n')) {
        row.push(current.trim());
        current = '';
        if (row.length > 0) rows.push(row);
        row = [];
        if (ch === '\r') i++; // skip \n
      } else {
        current += ch;
      }
    }
  }

  // Last field/row
  row.push(current.trim());
  if (row.some((cell) => cell.length > 0)) rows.push(row);

  return rows;
}

// --------------- Fetch Google Sheet headers ---------------

export async function fetchSheetHeaders(req: AuthRequest, res: Response): Promise<void> {
  try {
    const eventId = req.params.eventId || req.params.id;
    const { url } = req.body;

    if (!url) {
      res.status(400).json({ error: 'Google Sheet URL is required' });
      return;
    }

    const event = await prisma.event.findFirst({
      where: { id: eventId, organizerId: req.organizerId },
    });
    if (!event) {
      res.status(404).json({ error: 'Event not found' });
      return;
    }

    const sheetId = extractSheetId(url);
    if (!sheetId) {
      res.status(400).json({ error: 'Invalid Google Sheet URL. Please provide a valid Google Spreadsheet link.' });
      return;
    }

    const gid = extractGid(url);
    const result = await fetchSheetCSV(sheetId, gid);

    if (!result.ok) {
      res.status(400).json({ error: result.error });
      return;
    }

    const rows = parseCSV(result.csv);

    if (rows.length === 0) {
      res.status(400).json({ error: 'Google Sheet is empty. No headers found.' });
      return;
    }

    const headers = rows[0];
    const sampleRow = rows.length > 1 ? rows[1] : [];

    // Auto-suggest mapping based on common column names
    const suggestedMapping: Record<string, string> = {};
    const namePatterns = /^(name|full\s*name|attendee\s*name|participant)/i;
    const emailPatterns = /^(email|e-mail|email\s*address)/i;
    const mobilePatterns = /^(mobile|phone|contact|tel|mobile\s*no|phone\s*no|contact\s*number)/i;
    const companyPatterns = /^(company|organization|org|company\s*name)/i;
    const jobPatterns = /^(job\s*title|designation|role|position|title)/i;

    for (const h of headers) {
      if (namePatterns.test(h) && !suggestedMapping.name) suggestedMapping.name = h;
      else if (emailPatterns.test(h) && !suggestedMapping.email) suggestedMapping.email = h;
      else if (mobilePatterns.test(h) && !suggestedMapping.mobile) suggestedMapping.mobile = h;
      else if (companyPatterns.test(h) && !suggestedMapping.companyName) suggestedMapping.companyName = h;
      else if (jobPatterns.test(h) && !suggestedMapping.jobTitle) suggestedMapping.jobTitle = h;
    }

    res.json({
      headers,
      sampleRow,
      totalRows: rows.length - 1,
      suggestedMapping,
    });
  } catch (error) {
    console.error('fetchSheetHeaders error:', error);
    res.status(500).json({ error: 'Failed to fetch Google Sheet' });
  }
}

// --------------- Core sync logic (reusable) ---------------

export async function syncEventSheet(eventId: string, event: {
  googleSheetUrl: string | null;
  googleSheetMapping: any;
  requireApproval: boolean;
}): Promise<{ synced: number; skipped: number; total: number; error?: string }> {
  if (!event.googleSheetUrl) return { synced: 0, skipped: 0, total: 0, error: 'No Google Sheet configured' };

  const mapping = (event.googleSheetMapping as Record<string, string>) || {};
  if (!mapping.name) return { synced: 0, skipped: 0, total: 0, error: 'Name column not mapped' };

  // Find default ticket category for this event
  const defaultCategory = await prisma.ticketCategory.findFirst({
    where: { eventId, isDefault: true },
  }) || await prisma.ticketCategory.findFirst({
    where: { eventId },
    orderBy: { order: 'asc' },
  });

  const sheetId = extractSheetId(event.googleSheetUrl);
  if (!sheetId) return { synced: 0, skipped: 0, total: 0, error: 'Invalid Sheet URL' };

  const gid = extractGid(event.googleSheetUrl);
  const result = await fetchSheetCSV(sheetId, gid);
  if (!result.ok) return { synced: 0, skipped: 0, total: 0, error: result.error };

  const rows = parseCSV(result.csv);
  if (rows.length < 2) return { synced: 0, skipped: 0, total: 0 };

  const headers = rows[0];
  const dataRows = rows.slice(1);

  const colIndex: Record<string, number> = {};
  for (const [field, colName] of Object.entries(mapping)) {
    const idx = headers.findIndex((h) => h === colName);
    if (idx >= 0) colIndex[field] = idx;
  }
  if (colIndex.name === undefined) return { synced: 0, skipped: 0, total: dataRows.length, error: 'Name column not found in headers' };

  const existingAttendees = await prisma.attendee.findMany({
    where: { eventId },
    select: { email: true, mobile: true },
  });

  const existingSet = new Set<string>();
  for (const a of existingAttendees) {
    if (a.email) existingSet.add(`email:${a.email.toLowerCase()}`);
    if (a.mobile) existingSet.add(`mobile:${a.mobile}`);
  }

  let synced = 0;
  let skipped = 0;

  for (const row of dataRows) {
    const name = colIndex.name !== undefined ? row[colIndex.name] : '';
    const email = colIndex.email !== undefined ? row[colIndex.email] : '';
    const mobile = colIndex.mobile !== undefined ? row[colIndex.mobile] : '';
    const companyName = colIndex.companyName !== undefined ? row[colIndex.companyName] : '';
    const jobTitle = colIndex.jobTitle !== undefined ? row[colIndex.jobTitle] : '';

    if (!name || !name.trim()) { skipped++; continue; }

    const isDuplicate =
      (email && existingSet.has(`email:${email.toLowerCase()}`)) ||
      (mobile && existingSet.has(`mobile:${mobile}`));
    if (isDuplicate) { skipped++; continue; }

    const shortId = nanoid(10).toUpperCase();
    const qrBuffer = await generateQRCodeBuffer(shortId);
    const qrKey = `qrcodes/${eventId}/${shortId}.png`;
    const qrCodeUrl = await uploadToS3(qrBuffer, qrKey, 'image/png');

    const newAttendee = await prisma.attendee.create({
      data: {
        shortId,
        eventId,
        name: name.trim(),
        email: email?.trim() || null,
        mobile: mobile?.trim() || '',
        companyName: companyName?.trim() || null,
        jobTitle: jobTitle?.trim() || null,
        categoryId: defaultCategory?.id || null,
        qrCodeUrl,
        source: 'google-form',
        status: event.requireApproval ? 'PENDING' : 'APPROVED',
      },
    });

    // Fire registration email trigger (non-blocking)
    fireOnRegistration(eventId, newAttendee.id).catch(() => {});

    if (email) existingSet.add(`email:${email.toLowerCase()}`);
    if (mobile) existingSet.add(`mobile:${mobile}`);
    synced++;
  }

  await prisma.event.update({
    where: { id: eventId },
    data: { googleSheetLastSync: new Date() },
  });

  // Notify frontend via Socket.io if new attendees were synced
  if (synced > 0) {
    io.to(`event:${eventId}`).emit('attendees:updated', { eventId, synced });
  }

  return { synced, skipped, total: dataRows.length };
}

// --------------- Sync API endpoint ---------------

export async function syncGoogleSheet(req: AuthRequest, res: Response): Promise<void> {
  try {
    const eventId = req.params.eventId || req.params.id;

    const event = await prisma.event.findFirst({
      where: { id: eventId, organizerId: req.organizerId },
    });

    if (!event) { res.status(404).json({ error: 'Event not found' }); return; }
    if (!event.googleSheetUrl) { res.status(400).json({ error: 'No Google Sheet configured' }); return; }

    const result = await syncEventSheet(eventId, event);

    if (result.error) {
      res.status(400).json({ error: result.error });
      return;
    }

    res.json({
      ...result,
      message: result.synced > 0 ? `${result.synced} new attendee(s) synced` : 'No new entries to sync',
    });
  } catch (error) {
    console.error('syncGoogleSheet error:', error);
    res.status(500).json({ error: 'Failed to sync Google Sheet' });
  }
}

// --------------- Background auto-sync (called by scheduler) ---------------

export async function autoSyncAllSheets(): Promise<void> {
  try {
    const events = await prisma.event.findMany({
      where: {
        googleSheetUrl: { not: null },
        googleSheetMapping: { not: null },
      },
      select: {
        id: true,
        googleSheetUrl: true,
        googleSheetMapping: true,
        requireApproval: true,
      },
    });

    if (events.length === 0) return;

    for (const event of events) {
      try {
        const result = await syncEventSheet(event.id, event as any);
        if (result.synced > 0) {
          console.log(`[Auto-Sync] Event ${event.id}: ${result.synced} new attendee(s) synced`);
        }
      } catch (err) {
        console.error(`[Auto-Sync] Event ${event.id} failed:`, err);
      }
    }
  } catch (error) {
    console.error('[Auto-Sync] Failed:', error);
  }
}

// --------------- Remove Google Sheet config ---------------

export async function removeGoogleSheetConfig(req: AuthRequest, res: Response): Promise<void> {
  try {
    const eventId = req.params.eventId || req.params.id;

    const event = await prisma.event.findFirst({
      where: { id: eventId, organizerId: req.organizerId },
    });

    if (!event) {
      res.status(404).json({ error: 'Event not found' });
      return;
    }

    await prisma.event.update({
      where: { id: eventId },
      data: {
        googleSheetUrl: null,
        googleSheetMapping: null,
        googleSheetLastSync: null,
      },
    });

    res.json({ message: 'Google Sheet configuration removed' });
  } catch (error) {
    console.error('removeGoogleSheetConfig error:', error);
    res.status(500).json({ error: 'Failed to remove configuration' });
  }
}