deploy-backend/services/googleSheets.js

const { google } = require('googleapis');
const db = require('../db');
const { calculateLeadScore } = require('./aiEngine');
require('dotenv').config();

// Initialize Google Auth
let auth;
if (process.env.GOOGLE_CREDS_JSON) {
  try {
    const credentials = JSON.parse(process.env.GOOGLE_CREDS_JSON);
    auth = new google.auth.GoogleAuth({
      credentials,
      scopes: ['https://www.googleapis.com/auth/spreadsheets'],
    });
  } catch (e) {
    console.error('Failed to parse GOOGLE_CREDS_JSON environment variable:', e);
    auth = new google.auth.GoogleAuth({
      keyFile: 'credentials.json',
      scopes: ['https://www.googleapis.com/auth/spreadsheets'],
    });
  }
} else {
  auth = new google.auth.GoogleAuth({
    keyFile: 'credentials.json', // Path to your service account key file for local dev
    scopes: ['https://www.googleapis.com/auth/spreadsheets'],
  });
}

const sheets = google.sheets({ version: 'v4', auth });
const SPREADSHEET_ID = process.env.SPREADSHEET_ID;

/**
 * Parses date strings in DD/MM/YYYY hh:mm:ss format or other fallback formats
 */
const parseSheetDate = (dateStr) => {
  if (!dateStr) return new Date().toISOString();
  
  // Handle DD/MM/YYYY format with potential time
  const parts = dateStr.trim().split(/[\/\s:]/);
  if (parts.length >= 3) {
    const day = parseInt(parts[0], 10);
    const month = parseInt(parts[1], 10) - 1; // 0-indexed
    const year = parseInt(parts[2], 10);
    
    let hour = 0;
    let minute = 0;
    let second = 0;
    
    if (parts.length >= 6) {
      hour = parseInt(parts[3], 10);
      minute = parseInt(parts[4], 10);
      second = parseInt(parts[5], 10);
    }
    
    // Ensure numbers are valid
    if (day > 0 && day <= 31 && month >= 0 && month < 12 && year > 1900) {
      const date = new Date(year, month, day, hour, minute, second);
      if (!isNaN(date.getTime())) {
        return date.toISOString();
      }
    }
  }
  
  const fallback = new Date(dateStr);
  return !isNaN(fallback.getTime()) ? fallback.toISOString() : new Date().toISOString();
};

/**
 * Fetch all rows from Google Sheets and sync to PostgreSQL relational tables
 */
const syncSheetsToDB = async (io) => {
  if (!SPREADSHEET_ID || SPREADSHEET_ID === 'YOUR_SPREADSHEET_ID_HERE') {
    console.log('Skipping sync: SPREADSHEET_ID not configured in .env');
    return;
  }

  try {
    // Read from Sheet1!A2:L to get all columns (ID, Student Name, Phone, Email, Course, Fee, Source, Status, Admission, Counselor, Date, Contacted)
    const response = await sheets.spreadsheets.values.get({
      spreadsheetId: SPREADSHEET_ID,
      range: 'Sheet1!A2:L', // Fetch up to L
    });

    const rows = response.data.values;
    if (!rows || rows.length === 0) {
      console.log('No data found in Google Sheets.');
      return;
    }

    let newlyAdded = 0;
    let updated = 0;

    for (const row of rows) {
      // Corrected indices based on actual spreadsheet layout:
      // 0: ID (Serial No)
      // 1: Student Name
      // 2: Phone Number
      // 3: Email
      // 4: Interested Course
      // 5: Course Fee
      // 6: Lead Source
      // 7: Lead Status (followup_status)
      // 8: Admission Status
      // 9: Counselor Name
      // 10: Follow-up Date
      // 11: Last Contacted (created_date)
      
      const student_name = row[1] || '';
      const phone = row[2] || '';
      const email = row[3] || '';
      const course_interested = row[4] || '';
      const fees = row[5] ? parseFloat(row[5].toString().replace(/[^0-9.]/g, '')) : 0;
      const source = row[6] || '';
      const followup_status = row[7] || 'Pending';
      const admission_status = row[8] || 'Pending'; // 'Pending' or 'Admitted'
      const counselor_name = row[9] || '';
      const last_contacted_str = row[11] || row[10] || ''; // Fallback to Follow-up Date if Last Contacted is empty
      
      if (!phone || phone === 'Phone Number' || student_name === 'Student Name') continue; // Skip header/invalid rows

      // 1. Dynamic Counselor Lookup / Creation
      let counselorId = null;
      if (counselor_name) {
        const { data: existingCounselors, error: cErr } = await db
          .from('counselors')
          .select('id')
          .eq('name', counselor_name);

        if (cErr) {
          console.error('Error fetching counselor:', cErr);
        } else if (existingCounselors && existingCounselors.length > 0) {
          counselorId = existingCounselors[0].id;
        } else {
          // Dynamically create counselor with a generated unique email address
          const cEmail = `${counselor_name.toLowerCase().replace(/[^a-z0-9]/g, '')}@acadflow.com`;
          const { data: newCounselors, error: createCErr } = await db
            .from('counselors')
            .insert([{
              name: counselor_name,
              email: cEmail,
              role: 'Counselor'
            }])
            .select('id');

          if (createCErr) {
            console.error('Error creating counselor:', createCErr);
          } else if (newCounselors && newCounselors.length > 0) {
            counselorId = newCounselors[0].id;
          }
        }
      }

      // 2. Check if lead already exists based on phone
      const { data: existingLeads, error: selectError } = await db
        .from('leads')
        .select('*')
        .eq('phone', phone);

      if (selectError) {
        console.error('Error fetching lead from Supabase:', selectError);
        continue;
      }

      // Parse dates safely
      const created_at = parseSheetDate(last_contacted_str);

      if (existingLeads && existingLeads.length === 0) {
        // Calculate AI Lead Score
        const lead_score = calculateLeadScore({
          lead_source: source,
          followup_status: followup_status,
          interested_course: course_interested
        });

        // Insert new lead
        const { data: newLeads, error: insertError } = await db
          .from('leads')
          .insert([{
            name: student_name,
            phone,
            email,
            course_interested,
            source,
            status: followup_status,
            lead_score,
            counselor_id: counselorId,
            created_at,
            updated_at: created_at
          }])
          .select('id');
        
        if (insertError) {
          console.error('Error inserting lead into Supabase:', insertError);
        } else if (newLeads && newLeads.length > 0) {
          const leadId = newLeads[0].id;
          newlyAdded++;

          if (io && io.addNotification) {
            io.addNotification({
              title: lead_score > 80 ? "New hot lead assigned 🔥" : "New lead assigned 👤",
              message: `${student_name} is interested in ${course_interested} (Score: ${lead_score}%).`,
              type: "LEAD_ALERT",
              priority: lead_score > 80 ? "High" : "Low",
              action_url: `/leads?search=${student_name}`
            });
          }

          // Create dynamic linked follow-up record
          const { error: followUpError } = await db
            .from('follow_ups')
            .insert([{
              lead_id: leadId,
              followup_date: created_at,
              followup_type: 'Call',
              status: followup_status === 'Pending' ? 'Pending' : 'Completed',
              remarks: 'Initial sync from Google Sheets',
              created_by: counselorId
            }]);

          if (followUpError) {
            console.error('Error creating follow-up:', followUpError);
          }

          // Create dynamic linked admission record if Admitted
          if (admission_status === 'Admitted') {
            const { error: admissionError } = await db
              .from('admissions')
              .insert([{
                lead_id: leadId,
                course: course_interested,
                fees,
                payment_status: 'Pending',
                joined_date: created_at
              }]);

            if (admissionError) {
              console.error('Error creating admission:', admissionError);
            }
          }
        }
      } else {
        // True Two-Way Sync logic:
        // Update DB if Sheet has new basic info
        const lead = existingLeads[0];
        const hasBasicChanges = lead.name !== student_name || lead.course_interested !== course_interested || lead.counselor_id !== counselorId || lead.email !== email;
        const statusChanged = lead.status !== followup_status;

        if (hasBasicChanges || statusChanged) {
          const updates = {};
          if (lead.name !== student_name) updates.name = student_name;
          if (lead.email !== email) updates.email = email;
          if (lead.course_interested !== course_interested) updates.course_interested = course_interested;
          if (lead.counselor_id !== counselorId) updates.counselor_id = counselorId;
          if (statusChanged) updates.status = followup_status;

          const { error: updateError } = await db
            .from('leads')
            .update(updates)
            .eq('id', lead.id);

          if (updateError) {
            console.error('Error updating lead in DB:', updateError);
          } else {
            updated++;
          }
        }

        // Sync linked admissions record based on admission status
        const { data: existingAdmissions, error: admSelectError } = await db
          .from('admissions')
          .select('id')
          .eq('lead_id', lead.id);

        if (!admSelectError) {
          const hasAdmissionRecord = existingAdmissions && existingAdmissions.length > 0;
          if (admission_status === 'Admitted' && !hasAdmissionRecord) {
            // Create admissions record dynamically
            await db.from('admissions').insert([{
              lead_id: lead.id,
              course: course_interested,
              fees,
              payment_status: 'Pending',
              joined_date: created_at
            }]);
          } else if (admission_status !== 'Admitted' && hasAdmissionRecord) {
            // Remove admissions record
            await db.from('admissions').delete().eq('lead_id', lead.id);
          }
        }
      }
    }

    if (newlyAdded > 0 || updated > 0) {
      console.log(`Successfully synced: ${newlyAdded} new leads, ${updated} updated leads.`);
      if (io) {
        io.emit('leads_updated', { message: 'New leads synced from Google Sheets', count: newlyAdded + updated });
        if (io.addNotification) {
          io.addNotification({
            title: "Google Sheets synced successfully 🔄",
            message: `Synced ${newlyAdded} new leads, ${updated} updated leads from external database.`,
            type: "SYSTEM_ALERT",
            priority: "Low",
            action_url: "/"
          });
        }
      }
    }

  } catch (error) {
    console.error('Error syncing Google Sheets to DB:', error);
  }
};

/**
 * Update a specific lead in Google Sheets (DB to Sheet)
 */
const updateLeadInSheet = async (phone, statusData) => {
  if (!SPREADSHEET_ID) return;

  try {
    // Fetch all rows to find the exact row index
    const response = await sheets.spreadsheets.values.get({
      spreadsheetId: SPREADSHEET_ID,
      range: 'Sheet1!A:L',
    });

    const rows = response.data.values;
    if (!rows) return;

    // Find row index (Adding 1 because array is 0-indexed and sheet is 1-indexed)
    const rowIndex = rows.findIndex(row => row[2] === phone); // Phone is row[2]
    
    if (rowIndex !== -1) {
      const sheetRowNumber = rowIndex + 1;
      
      // Column H is Lead Status (row[7]) and Column I is Admission Status (row[8])
      // We update just those cells to avoid overwriting other data
      await sheets.spreadsheets.values.update({
        spreadsheetId: SPREADSHEET_ID,
        range: `Sheet1!H${sheetRowNumber}:I${sheetRowNumber}`,
        valueInputOption: 'USER_ENTERED',
        resource: {
          values: [
            [statusData.followup_status, statusData.admission_status]
          ]
        }
      });
      console.log(`Successfully updated lead ${phone} in Google Sheet.`);
    }
  } catch (error) {
    console.error('Error updating Google Sheet:', error);
  }
};

module.exports = {
  syncSheetsToDB,
  updateLeadInSheet
};