// server.js
require('dotenv').config();
const express = require('express');
const db = require('./db');
const app = express();
const axios = require('axios');
const { google } = require('googleapis');
const { OAuth2Client } = require('google-auth-library');
const nodemailer = require('nodemailer');
const fs = require('fs');

// Google Calendar API setup
const oauth2Client = new OAuth2Client(
    process.env.GOOGLE_CLIENT_ID,
    process.env.GOOGLE_CLIENT_SECRET,
    process.env.GOOGLE_REDIRECT_URI
);

// Set credentials
oauth2Client.setCredentials({
    refresh_token: process.env.GOOGLE_REFRESH_TOKEN
});

const calendar = google.calendar({ version: 'v3', auth: oauth2Client });

const transporter = nodemailer.createTransport({
    host: "donotemail.mmtprep.com",
    port: 465,
    secure: true,
    auth: {
        user: "donotreply@donotemail.mmtprep.com",
        pass: "Mmtmmt1234!"
    },
});

// DB connection check...
db.getConnection((err, conn) => {
    if (err) {
        console.error('[DB] 연결 실패:', err.message);
        process.exit(1);
    }
    console.log('[DB] 연결 성공');
    conn.release();
});

app.use(express.urlencoded({ extended: true }));
app.use(express.json());

function convertSpeechToDigits(input) {
    if (!input) return '';

    const wordToNumber = {
        'zero': '0', 'one': '1', 'two': '2', 'three': '3', 'four': '4',
        'five': '5', 'six': '6', 'seven': '7', 'eight': '8', 'nine': '9'
    };

    const directionalMap = {
        'north': 'N',
        'south': 'S',
        'east': 'E',
        'west': 'W',
        'northeast': 'NE',
        'northwest': 'NW',
        'southeast': 'SE',
        'southwest': 'SW'
    };

    const words = input.toLowerCase().split(/\s+/);
    let result = [];
    let numberBuffer = '';

    for (let word of words) {
        if (wordToNumber[word] !== undefined) {
            numberBuffer += wordToNumber[word];
        } else if (/^\d+$/.test(word)) {
            numberBuffer += word;
        } else {
            if (numberBuffer.length > 0) {
                result.push(numberBuffer);
                numberBuffer = '';
            }

            if (directionalMap[word]) {
                result.push(directionalMap[word]);
            } else {
                result.push(word);
            }
        }
    }

    // 남은 숫자 버퍼가 있으면 마지막에 추가
    if (numberBuffer.length > 0) {
        result.push(numberBuffer);
    }

    return result.join(' ');
}

// New function to convert speech to numbers without spaces
function formatPhoneNumber(input) {
    if (!input) return '';

    // First convert speech to digits with the existing function
    const converted = convertSpeechToDigits(input);

    // Remove all non-digit characters (including commas and spaces)
    return converted.replace(/[^0-9]/g, '');
}

// New function to format addresses (keeps some spacing for readability)
function formatAddress(input) {
    if (!input) return '';

    // First convert speech to digits
    const converted = convertSpeechToDigits(input);

    // Remove extra spaces and normalize
    return converted.replace(/\s+/g, ' ').trim();
}

function convertToGoogleCalendarEvent(reservation) {
    // Parse the date and time
    const [year, month, day] = reservation.client_date.split('-');
    const [hours, minutes] = reservation.client_time.split(':');

    // Create start and end times (assuming 1-hour duration)
    const startTime = new Date(year, month - 1, day, parseInt(hours) + 3, minutes);
    const endTime = new Date(startTime.getTime() + 30 * 60 * 1000);

    return {
        summary: `Reservation: ${reservation.client_name}`,
        description: `Client: ${reservation.client_name}\nPhone: ${reservation.client_phone_number}\nProperty: ${reservation.client_property}`,
        start: {
            dateTime: startTime.toISOString(),
            timeZone: 'America/Los_Angeles', // Adjust timezone as needed
        },
        end: {
            dateTime: endTime.toISOString(),
            timeZone: 'America/Los_Angeles', // Adjust timezone as needed
        },
        reminders: {
            useDefault: false,
            overrides: [
                { method: 'email', minutes: 24 * 60 }, // 1 day before
                { method: 'popup', minutes: 60 }, // 1 hour before
            ],
        },
    };
}

// Function to create Google Calendar event
async function createGoogleCalendarEvent(event) {
    try {
        const response = await calendar.events.insert({
            calendarId: 'primary',
            resource: event,
        });
        return response.data;
    } catch (error) {
        console.error('Error creating Google Calendar event:', error);
        throw error;
    }
}

function convertSpokenDateToISO(spokenDate) {
    const months = {
        'january': '01', 'february': '02', 'march': '03', 'april': '04',
        'may': '05', 'june': '06', 'july': '07', 'august': '08',
        'september': '09', 'october': '10', 'november': '11', 'december': '12'
    };

    const ordinalNumbers = {
        'first': '01', 'second': '02', 'third': '03', 'fourth': '04',
        'fifth': '05', 'sixth': '06', 'seventh': '07', 'eighth': '08',
        'ninth': '09', 'tenth': '10', 'eleventh': '11', 'twelfth': '12',
        'thirteenth': '13', 'fourteenth': '14', 'fifteenth': '15',
        'sixteenth': '16', 'seventeenth': '17', 'eighteenth': '18',
        'nineteenth': '19', 'twentieth': '20', 'twenty-first': '21',
        'twenty-second': '22', 'twenty-third': '23', 'twenty-fourth': '24',
        'twenty-fifth': '25', 'twenty-sixth': '26', 'twenty-seventh': '27',
        'twenty-eighth': '28', 'twenty-ninth': '29', 'thirtieth': '30',
        'thirty-first': '31'
    };

    const words = spokenDate.toLowerCase().split(' ');
    let month = '';
    let day = '';
    let year = new Date().getFullYear();

    for (let word of words) {
        if (months[word]) {
            month = months[word];
        } else if (ordinalNumbers[word]) {
            day = ordinalNumbers[word];
        } else if (word.match(/^\d{4}$/)) {
            year = word;
        }
    }

    // If we're in the current year and the month has passed, assume next year
    const currentDate = new Date();
    const currentMonth = currentDate.getMonth() + 1;
    if (year === currentDate.getFullYear() && parseInt(month) < currentMonth) {
        year++;
    }

    return `${year}-${month}-${day}`;
}

function convertSpokenTimeTo24Hour(spokenTime) {
    const timeMap = {
        'midnight': '00:00',
        'noon': '12:00',
        'one': '01', 'two': '02', 'three': '03', 'four': '04',
        'five': '05', 'six': '06', 'seven': '07', 'eight': '08',
        'nine': '09', 'ten': '10', 'eleven': '11', 'twelve': '12'
    };

    const words = spokenTime.toLowerCase().split(' ');
    let hour = '';
    let minute = '00';
    let isPM = false;

    for (let word of words) {
        if (timeMap[word]) {
            hour = timeMap[word];
        } else if (word === 'pm') {
            isPM = true;
        } else if (word === 'am') {
            isPM = false;
        } else if (word.match(/^\d{1,2}$/)) {
            minute = word.padStart(2, '0');
        }
    }

    if (isPM && hour !== '12') {
        hour = (parseInt(hour) + 12).toString();
    } else if (!isPM && hour === '12') {
        hour = '00';
    }

    return `${hour}:${minute}:00`;
}

async function sendReservationEmail(reservation) {
    try {
        // Read the email template
        let emailTemplate = fs.readFileSync('./email_template/reservation.html', 'utf-8');

        // Replace placeholders with actual data
        emailTemplate = emailTemplate
            .replace('{{client_name}}', reservation.client_name)
            .replace('{{client_phone_number}}', reservation.client_phone_number)
            .replace('{{client_property}}', reservation.client_property)
            .replace('{{client_date}}', reservation.client_date)
            .replace('{{client_time}}', reservation.client_time);

        // Send to both email addresses
        const emailPromises = [
            'aiden1393@gmail.com',
            'sean@toprealtyco.com'
        ].map(email =>
            transporter.sendMail({
                from: 'donotreply@donotemail.mmtprep.com',
                to: email,
                subject: '[TOP REALTY] New Reservation Details',
                html: emailTemplate
            })
        );

        await Promise.all(emailPromises);
        console.log('Reservation emails sent successfully');
    } catch (error) {
        console.error('Error sending reservation emails:', error);
        throw error;
    }
}

async function sendRescheduleEmail(schedule) {
    try {
        // Read the email template
        let emailTemplate = fs.readFileSync('./email_template/reschedule.html', 'utf-8');

        // Replace placeholders with actual data
        emailTemplate = emailTemplate
            .replace('{{fullname}}', schedule.fullname || 'Not provided')
            .replace('{{phone}}', schedule.phone)
            .replace('{{date}}', schedule.date)
            .replace('{{time}}', schedule.time);

        // Send to both email addresses
        const emailPromises = [
            'aiden1393@gmail.com',
            'sean@toprealtyco.com'
        ].map(email =>
            transporter.sendMail({
                from: 'donotreply@donotemail.mmtprep.com',
                to: email,
                subject: '[TOP REALTY] New Rescheduling Request',
                html: emailTemplate
            })
        );

        await Promise.all(emailPromises);
        console.log('Reschedule emails sent successfully');
    } catch (error) {
        console.error('Error sending reschedule emails:', error);
        throw error;
    }
}

async function deleteGoogleCalendarEvent(eventId) {
    try {
        await calendar.events.delete({
            calendarId: 'primary',
            eventId: eventId
        });
        console.log('Google Calendar event deleted successfully');
    } catch (error) {
        console.error('Error deleting Google Calendar event:', error);
        throw error;
    }
}

async function findAndDeleteCalendarEvents(phone, fullname, date) {
    try {
        // Convert date to start and end of day in ISO format
        const startDate = new Date(date);
        startDate.setHours(0, 0, 0, 0);
        const endDate = new Date(date);
        endDate.setHours(23, 59, 59, 999);

        // Search for events in the calendar
        const response = await calendar.events.list({
            calendarId: 'primary',
            timeMin: startDate.toISOString(),
            timeMax: endDate.toISOString(),
            singleEvents: true,
            orderBy: 'startTime'
        });

        const events = response.data.items;
        const deletePromises = [];

        for (const event of events) {
            const description = event.description || '';
            const summary = event.summary || '';

            // Check if event matches either phone number or fullname
            if (description.includes(phone) ||
                (fullname && (description.includes(fullname) || summary.includes(fullname)))) {
                deletePromises.push(deleteGoogleCalendarEvent(event.id));
            }
        }

        await Promise.all(deletePromises);
        console.log(`Deleted ${deletePromises.length} matching calendar events`);
    } catch (error) {
        console.error('Error finding/deleting calendar events:', error);
        throw error;
    }
}

async function sendRemovalEmail(removal) {
    try {
        // Read the email template
        let emailTemplate = fs.readFileSync('./email_template/removal.html', 'utf-8');

        // Replace placeholders with actual data
        emailTemplate = emailTemplate
            .replace('{{fullname}}', removal.fullname || 'Not provided')
            .replace('{{phone}}', removal.phone)
            .replace('{{date}}', removal.date);

        // Send to both email addresses
        const emailPromises = [
            'aiden1393@gmail.com',
            'sean@toprealtyco.com'
        ].map(email =>
            transporter.sendMail({
                from: 'donotreply@donotemail.mmtprep.com',
                to: email,
                subject: '[TOP REALTY] Reservation Cancellation Notice',
                html: emailTemplate
            })
        );

        await Promise.all(emailPromises);
        console.log('Removal notification emails sent successfully');
    } catch (error) {
        console.error('Error sending removal notification emails:', error);
        throw error;
    }
}

app.post('/api/save-reservation', async (req, res) => {
    console.log("==== SAVE RESERVATION - RAW BODY ====", req.body);
    let {
        client_name,
        client_phone_number,
        client_property,
        client_date,
        client_time
    } = req.body;

    console.log("==== SAVE RESERVATION - ORIGINAL DATA ====", {
        client_name,
        client_phone_number,
        client_property,
        client_date,
        client_time
    });

    // Convert speech to appropriate formats
    client_phone_number = formatPhoneNumber(client_phone_number);
    client_property = formatAddress(client_property);

    // Handle date conversion
    if (client_date.includes('T')) {
        // If it's an ISO date string, extract just the date part
        client_date = client_date.split('T')[0];
    } else if (!client_date.match(/^\d{4}-\d{2}-\d{2}$/)) {
        // Only convert if it's not already in YYYY-MM-DD format
        client_date = convertSpokenDateToISO(client_date);
    }

    // Only convert time if it's not already in HH:MM format
    if (!client_time.match(/^\d{2}:\d{2}$/)) {
        client_time = convertSpokenTimeTo24Hour(client_time);
    }

    console.log("==== SAVE RESERVATION - PROCESSED DATA ====", {
        client_name,
        client_phone_number,
        client_property,
        client_date,
        client_time
    });

    if (!client_name || !client_phone_number || !client_property || !client_date || !client_time) {
        console.log("==== SAVE RESERVATION - VALIDATION FAILED ====");
        return res.status(400).json({
            success: false,
            message: 'All fields are required.'
        });
    }

    const reservation = {
        client_name,
        client_phone_number,
        client_property,
        client_date,
        client_time
    };

    try {
        // First, save to database
        const insertSql = `
            INSERT INTO reservation (client_name, client_phone_number, client_property, client_date, client_time)
            VALUES (?, ?, ?, ?, ?)
        `;

        console.log("==== SAVE RESERVATION - SQL PARAMS ====", [client_name, client_phone_number, client_property, client_date, client_time]);

        const dbResult = await new Promise((resolve, reject) => {
            db.query(
                insertSql,
                [client_name, client_phone_number, client_property, client_date, client_time],
                (err, result) => {
                    if (err) reject(err);
                    else resolve(result);
                }
            );
        });

        // Then, create Google Calendar event
        const calendarEvent = convertToGoogleCalendarEvent(reservation);
        const calendarResult = await createGoogleCalendarEvent(calendarEvent);

        await sendReservationEmail(reservation);

        console.log("==== SAVE RESERVATION - SUCCESS ====", {
            insertId: dbResult.insertId,
            affectedRows: dbResult.affectedRows,
            calendarEventId: calendarResult.id
        });

        return res.json({
            success: true,
            message: 'Reservation saved successfully.',
            reservationId: dbResult.insertId,
            calendarEventId: calendarResult.id
        });
    } catch (error) {
        console.error('==== SAVE RESERVATION - ERROR ====', error);
        return res.status(500).json({
            success: false,
            message: 'Failed to save reservation or create calendar event.'
        });
    }
});

app.post('/api/remove-reservation', async (req, res) => {
    console.log("==== REMOVE RESERVATION - RAW BODY ====", req.body);
    const { phone_number, fullname, date } = req.body;

    console.log("==== REMOVE RESERVATION - ORIGINAL DATA ====", {
        phone_number,
        fullname,
        date
    });

    if (!phone_number || !date) {
        console.log("==== REMOVE RESERVATION - VALIDATION FAILED ====");
        return res.status(400).json({
            success: false,
            message: 'Phone number and date are required.'
        });
    }

    // Convert speech to digits if necessary
    const processedPhoneNumber = formatPhoneNumber(phone_number);
    
    // Handle date conversion if needed
    let processedDate = date;
    if (date.includes('T')) {
        // If it's an ISO date string, extract just the date part
        processedDate = date.split('T')[0];
    } else if (!date.match(/^\d{4}-\d{2}-\d{2}$/)) {
        // Only convert if it's not already in YYYY-MM-DD format
        processedDate = convertSpokenDateToISO(date);
    }

    console.log("==== REMOVE RESERVATION - PROCESSED DATA ====", {
        phone: processedPhoneNumber,
        date: processedDate
    });

    const deleteSql = `
        DELETE FROM reservation 
        WHERE client_phone_number = ? AND client_date = ?
    `;

    console.log("==== REMOVE RESERVATION - SQL QUERY ====", deleteSql);
    console.log("==== REMOVE RESERVATION - SQL PARAMS ====", [processedPhoneNumber, processedDate]);

    try {
        // First, delete from database
        const dbResult = await new Promise((resolve, reject) => {
            db.query(deleteSql, [processedPhoneNumber, processedDate], (err, result) => {
                if (err) reject(err);
                else resolve(result);
            });
        });

        if (dbResult.affectedRows === 0) {
            console.log("==== REMOVE RESERVATION - NOT FOUND ====");
            return res.status(404).json({
                success: false,
                message: 'No reservation found with that phone number and date.'
            });
        }

        // Then, delete from Google Calendar
        await findAndDeleteCalendarEvents(processedPhoneNumber, fullname, processedDate);

        // Send removal notification email
        await sendRemovalEmail({
            phone: processedPhoneNumber,
            fullname: fullname || 'Not provided',
            date: processedDate
        });

        console.log("==== REMOVE RESERVATION - SUCCESS ====", {
            deletedCount: dbResult.affectedRows
        });

        return res.json({
            success: true,
            message: `Successfully removed ${dbResult.affectedRows} reservation(s).`,
            deletedCount: dbResult.affectedRows
        });
    } catch (error) {
        console.error('==== REMOVE RESERVATION - ERROR ====', error);
        return res.status(500).json({
            success: false,
            message: 'Failed to remove reservation or calendar event.'
        });
    }
});

app.post('/api/new-schedule', async (req, res) => {
    console.log("==== NEW SCHEDULE - RAW BODY ====", req.body);
    const { phone, date, time, fullname } = req.body;

    console.log("==== NEW SCHEDULE - ORIGINAL DATA ====", {
        phone,
        date,
        time,
        fullname
    });

    if (!phone || !date || !time) {
        console.log("==== NEW SCHEDULE - VALIDATION FAILED ====");
        return res.status(400).json({
            success: false,
            message: 'Phone, date, and time are required.'
        });
    }

    // Convert speech to digits if necessary for phone
    const processedPhone = formatPhoneNumber(phone);

    // Handle date conversion
    let processedDate = date;
    if (date.includes('T')) {
        // If it's an ISO date string, extract just the date part
        processedDate = date.split('T')[0];
    } else if (!date.match(/^\d{4}-\d{2}-\d{2}$/)) {
        // Only convert if it's not already in YYYY-MM-DD format
        processedDate = convertSpokenDateToISO(date);
    }

    // Only convert time if it's not already in HH:MM format
    let processedTime = time;
    if (!time.match(/^\d{2}:\d{2}$/)) {
        processedTime = convertSpokenTimeTo24Hour(time);
    }

    console.log("==== NEW SCHEDULE - PROCESSED DATA ====", {
        phone: processedPhone,
        date: processedDate,
        time: processedTime,
        fullname
    });

    const insertSql = `
        INSERT INTO new_schedule (phone, date, time)
        VALUES (?, ?, ?)
    `;

    console.log("==== NEW SCHEDULE - SQL QUERY ====", insertSql);
    console.log("==== NEW SCHEDULE - SQL PARAMS ====", [processedPhone, processedDate, processedTime]);

    try {
        const result = await new Promise((resolve, reject) => {
            db.query(
                insertSql,
                [processedPhone, processedDate, processedTime],
                (err, result) => {
                    if (err) reject(err);
                    else resolve(result);
                }
            );
        });

        // Send email notification
        await sendRescheduleEmail({
            phone: processedPhone,
            date: processedDate,
            time: processedTime,
            fullname: fullname || 'Not provided'
        });

        console.log("==== NEW SCHEDULE - SUCCESS ====", {
            scheduleId: result.insertId,
            affectedRows: result.affectedRows
        });

        return res.json({
            success: true,
            message: 'Schedule saved successfully.',
            scheduleId: result.insertId
        });
    } catch (error) {
        console.error('==== NEW SCHEDULE - ERROR ====', error);
        return res.status(500).json({
            success: false,
            message: 'Failed to save schedule or send email notification.'
        });
    }
});

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
    console.log(`서버 실행 중: http://localhost:${PORT}`);
});