const http = require('node:http');
const https = require('node:https');
const fs = require('node:fs');
const path = require('node:path');
const crypto = require('crypto');
const { db, initDatabase, dbPath } = require('./database');
const { exec } = require('child_process');

function openBrowserURL(url) {
  const startCmd = process.platform === 'darwin' ? 'open' : process.platform === 'win32' ? 'start ""' : 'xdg-open';
  const target = process.platform === 'win32' ? url.replace(/&/g, '^&') : url;
  exec(`${startCmd} "${target}"`, (err) => {
    if (err) console.error('Failed to open URL in browser:', err);
  });
}

// Global safety net to prevent server crashes from uncaught errors
process.on('uncaughtException', (err) => {
  console.error('CRITICAL: Uncaught Exception:', err);
});

process.on('unhandledRejection', (reason, promise) => {
  console.error('CRITICAL: Unhandled Rejection at:', promise, 'reason:', reason);
});

// Initialize database schema and default records
initDatabase();

// Cryptographic helpers for securing permanent Meta Access Tokens (AES-256-CBC)
const ENCRYPTION_KEY = crypto.createHash('sha256').update('Shaima@#8870_token_sec').digest();
const IV_LENGTH = 16;

function encryptToken(text) {
  if (!text) return '';
  const iv = crypto.randomBytes(IV_LENGTH);
  const cipher = crypto.createCipheriv('aes-256-cbc', ENCRYPTION_KEY, iv);
  let encrypted = cipher.update(text);
  encrypted = Buffer.concat([encrypted, cipher.final()]);
  return iv.toString('hex') + ':' + encrypted.toString('hex');
}

function decryptToken(text) {
  if (!text) return '';
  try {
    const textParts = text.split(':');
    const iv = Buffer.from(textParts.shift(), 'hex');
    const encryptedText = Buffer.from(textParts.join(':'), 'hex');
    const decipher = crypto.createDecipheriv('aes-256-cbc', ENCRYPTION_KEY, iv);
    let decrypted = decipher.update(encryptedText);
    decrypted = Buffer.concat([decrypted, decipher.final()]);
    return decrypted.toString();
  } catch (e) {
    return text; // Fallback if not encrypted yet
  }
}

// Background Worker Queue and Retry Mechanism
setInterval(async () => {
  try {
    const jobs = db.prepare("SELECT * FROM background_jobs WHERE status = 'pending' OR (status = 'failed' AND retry_count < max_retries) ORDER BY id ASC LIMIT 5").all();
    for (const job of jobs) {
      db.prepare("UPDATE background_jobs SET status = 'processing', updated_at = ? WHERE id = ?").run(new Date().toISOString(), job.id);
      
      try {
        const payload = JSON.parse(job.payload);
        if (job.job_type === 'whatsapp_send') {
          await processWhatsAppSendJob(payload);
        } else if (job.job_type === 'email_send') {
          await processEmailSendJob(payload);
        }
        
        db.prepare("UPDATE background_jobs SET status = 'completed', updated_at = ? WHERE id = ?").run(new Date().toISOString(), job.id);
      } catch (err) {
        const nextRetries = job.retry_count + 1;
        const newStatus = nextRetries >= job.max_retries ? 'failed' : 'pending';
        db.prepare("UPDATE background_jobs SET status = ?, retry_count = ?, error_message = ?, updated_at = ? WHERE id = ?")
          .run(newStatus, nextRetries, err.message, new Date().toISOString(), job.id);
      }
    }
  } catch (e) {
    console.error('[Background Worker Error]:', e.message);
  }
}, 10000); // Check queue every 10 seconds

async function processWhatsAppSendJob(payload) {
  const { phone, message, phoneId, token, mediaUrl, filename, mediaType } = payload;
  const decryptedToken = decryptToken(token);
  const cleanPhone = phone.replace(/[^0-9]/g, '');

  if (mediaType === 'document' && mediaUrl) {
    const filePath = path.join(__dirname, 'public', mediaUrl.replace(/^\/uploads\//, 'uploads/'));
    if (fs.existsSync(filePath)) {
      const mediaId = await uploadMetaMediaFile(filePath, filename || 'document.pdf', 'application/pdf', decryptedToken, phoneId);
      
      const postData = JSON.stringify({
        messaging_product: 'whatsapp',
        recipient_type: 'individual',
        to: cleanPhone,
        type: 'document',
        document: {
          id: mediaId,
          filename: filename || 'document.pdf'
        }
      });

      await sendMetaAPIRequest(phoneId, postData, decryptedToken);
      return;
    }
  }

  const postData = JSON.stringify({
    messaging_product: 'whatsapp',
    recipient_type: 'individual',
    to: cleanPhone,
    type: 'text',
    text: { body: message }
  });
  await sendMetaAPIRequest(phoneId, postData, decryptedToken);
}

function sendMetaAPIRequest(phoneId, postData, token) {
  return new Promise((resolve, reject) => {
    const options = {
      hostname: 'graph.facebook.com',
      port: 443,
      path: `/v20.0/${phoneId}/messages`,
      method: 'POST',
      headers: {
        'Authorization': `Bearer ${token}`,
        'Content-Type': 'application/json',
        'Content-Length': Buffer.byteLength(postData)
      }
    };

    const reqMeta = https.request(options, (resMeta) => {
      let body = '';
      resMeta.on('data', chunk => body += chunk);
      resMeta.on('end', () => {
        if (resMeta.statusCode >= 200 && resMeta.statusCode < 300) {
          resolve(body);
        } else {
          reject(new Error(`Meta API error: ${resMeta.statusCode} - ${body}`));
        }
      });
    });
    reqMeta.on('error', reject);
    reqMeta.write(postData);
    reqMeta.end();
  });
}

async function processEmailSendJob(payload) {
  const { to, cc, bcc, subject, html, attachments } = payload;
  const mappedAttachments = (attachments || []).map(att => ({
    filename: att.filename,
    path: path.join(__dirname, 'public', att.path.replace(/^\/uploads\//, 'uploads/'))
  }));

  await sendEmail({
    to,
    cc: cc || '',
    bcc: bcc || '',
    subject,
    html,
    attachments: mappedAttachments
  });
}

// Port configurations
// Port configurations
let port = process.env.PORT ? parseInt(process.env.PORT, 10) : 3000;
const portArgIndex = process.argv.indexOf('--port');
if (portArgIndex !== -1 && process.argv[portArgIndex + 1]) {
  port = parseInt(process.argv[portArgIndex + 1], 10);
}

// Heartbeat variables for auto-shutdown when browser closes
let lastHeartbeat = Date.now() + 30000; // 30-second grace period on startup to let Edge boot up
const HEARTBEAT_TIMEOUT = 7000; // Shutdown after 7 seconds without client heartbeat

// Check if running on cloud hosting (cPanel, VPS, etc.) to disable auto-shutdown
const isHosting = !!process.env.PORT || process.env.HOSTING === 'true' || process.argv.includes('--hosting');

if (!isHosting) {
  setInterval(() => {
    if (Date.now() - lastHeartbeat > HEARTBEAT_TIMEOUT) {
      console.log('No heartbeat received for 7 seconds. Shutting down server...');
      process.exit(0);
    }
  }, 2000);
} else {
  console.log('Running in Hosting Mode: Auto-shutdown disabled for 24/7 online availability.');
}

// Helper functions for requests and responses
function sendJSON(res, data, status = 200) {
  res.writeHead(status, {
    'Content-Type': 'application/json',
    'Access-Control-Allow-Origin': '*'
  });
  res.end(JSON.stringify(data));
}

function sendError(res, message, status = 500) {
  sendJSON(res, { error: message }, status);
}

function parseJSON(req) {
  return new Promise((resolve, reject) => {
    let body = '';
    req.on('data', chunk => {
      body += chunk.toString();
    });
    req.on('end', () => {
      try {
        const parsed = body ? JSON.parse(body) : {};
        if (req.session && req.session.user_id) {
          parsed.userId = req.session.user_id;
          parsed.adminUserId = req.session.user_id;
        }
        resolve(parsed);
      } catch (err) {
        reject(err);
      }
    });
  });
}

function logAudit(userId, action, details) {
  try {
    const stmt = db.prepare('INSERT INTO audit_logs (user_id, action, timestamp, details) VALUES (?, ?, ?, ?)');
    stmt.run(userId || null, action, new Date().toISOString(), details || '');
  } catch (err) {
    console.error('Audit logging failed:', err);
  }
}

function getGoogleAccessToken(serviceAccountJson) {
  return new Promise((resolve, reject) => {
    try {
      const sa = JSON.parse(serviceAccountJson);
      if (!sa.client_email || !sa.private_key) {
        return reject(new Error('Missing client_email or private_key in service account JSON'));
      }
      const header = Buffer.from(JSON.stringify({ alg: 'RS256', typ: 'JWT' })).toString('base64url');
      const claim = Buffer.from(JSON.stringify({
        iss: sa.client_email,
        scope: 'https://www.googleapis.com/auth/drive.file',
        aud: 'https://oauth2.googleapis.com/token',
        exp: Math.floor(Date.now() / 1000) + 3600,
        iat: Math.floor(Date.now() / 1000)
      })).toString('base64url');

      const sign = crypto.createSign('RSA-SHA256');
      sign.update(header + '.' + claim);
      const signature = sign.sign(sa.private_key, 'base64url');
      const assertion = header + '.' + claim + '.' + signature;

      const postData = `grant_type=urn:ietf:params:oauth:grant-type:jwt-bearer&assertion=${assertion}`;
      const req = https.request('https://oauth2.googleapis.com/token', {
        method: 'POST',
        headers: {
          'Content-Type': 'application/x-www-form-urlencoded',
          'Content-Length': Buffer.byteLength(postData)
        }
      }, (res) => {
        let body = '';
        res.on('data', chunk => body += chunk);
        res.on('end', () => {
          try {
            const parsed = JSON.parse(body);
            if (parsed.access_token) {
              resolve(parsed.access_token);
            } else {
              reject(new Error(parsed.error_description || parsed.error || 'Failed to obtain access token'));
            }
          } catch (e) {
            reject(new Error('Failed to parse Google OAuth token response'));
          }
        });
      });
      req.on('error', reject);
      req.write(postData);
      req.end();
    } catch (err) {
      reject(err);
    }
  });
}

function uploadFileToGoogleDrive(accessToken, filePath, filename, folderId) {
  return new Promise((resolve, reject) => {
    try {
      const fileData = fs.readFileSync(filePath);
      const boundary = 'antigravity_boundary_' + Date.now();
      const metadata = JSON.stringify({
        name: filename,
        parents: folderId ? [folderId] : undefined
      });
      const headerPart = 
        `--${boundary}\r\n` +
        `Content-Type: application/json; charset=UTF-8\r\n\r\n` +
        `${metadata}\r\n\r\n` +
        `--${boundary}\r\n` +
        `Content-Type: application/octet-stream\r\n\r\n`;
      const footerPart = `\r\n--${boundary}--`;
      const requestBody = Buffer.concat([
        Buffer.from(headerPart),
        fileData,
        Buffer.from(footerPart)
      ]);

      const req = https.request('https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart', {
        method: 'POST',
        headers: {
          'Authorization': `Bearer ${accessToken}`,
          'Content-Type': `multipart/related; boundary=${boundary}`,
          'Content-Length': requestBody.length
        }
      }, (res) => {
        let body = '';
        res.on('data', chunk => body += chunk);
        res.on('end', () => {
          try {
            const parsed = JSON.parse(body);
            if (parsed.id) {
              resolve(parsed);
            } else {
              reject(new Error(parsed.error ? parsed.error.message : 'Upload failed'));
            }
          } catch (e) {
            reject(new Error('Failed to parse Google Drive upload response'));
          }
        });
      });
      req.on('error', reject);
      req.write(requestBody);
      req.end();
    } catch (err) {
      reject(err);
    }
  });
}

function getSetting(key, defaultValue) {
  try {
    const row = db.prepare('SELECT value FROM settings WHERE key = ?').get(key);
    return row ? row.value : defaultValue;
  } catch (err) {
    return defaultValue;
  }
}

const nodemailer = require('nodemailer');

async function sendEmail({ to, cc, bcc, subject, text, html, attachments }) {
  const host = getSetting('smtp_host', '');
  const port = parseInt(getSetting('smtp_port', '587'), 10);
  const secure = getSetting('smtp_secure', 'false') === 'true';
  const user = getSetting('smtp_user', '');
  const pass = getSetting('smtp_pass', '');
  const from = getSetting('smtp_from', 'Shaima Hotel <notifications@shaimahotel.com>');

  if (!host || !user || !pass) {
    throw new Error('SMTP email settings are not configured. Please set them up in the Control Panel first.');
  }

  const transporter = nodemailer.createTransport({
    host,
    port,
    secure,
    auth: {
      user,
      pass
    }
  });

  const mailOptions = {
    from,
    to,
    cc,
    bcc,
    subject,
    text,
    html,
    attachments
  };

  return transporter.sendMail(mailOptions);
}

// In-memory mock WhatsApp gateway log (persists during process lifecycle)
const whatsappLog = [];

function logMockWhatsApp(phone, message) {
  const logItem = {
    id: Date.now().toString() + Math.random().toString(36).substr(2, 5),
    timestamp: new Date().toISOString(),
    phone,
    message,
    status: 'Sent'
  };
  whatsappLog.push(logItem);
  console.log(`[WhatsApp Sent to ${phone}]: \n${message}\n`);
  return logItem;
}

function parseTemplateMessage(message) {
  const result = {
    templateKey: null,
    placeholders: null,
    imageUrl: null,
    body: message,
    footer: null,
    btnText: null,
    btnUrl: null
  };

  const extractTag = (msg, tag) => {
    const match = msg.match(new RegExp(`{${tag}}([\\s\\S]*?){/${tag}}`));
    return match ? match[1].trim() : null;
  };

  if (message.includes('{body}')) {
    result.templateKey = extractTag(message, 'template_key');
    const phRaw = extractTag(message, 'placeholders');
    if (phRaw) {
      try {
        result.placeholders = JSON.parse(phRaw);
      } catch (e) {}
    }
    result.imageUrl = extractTag(message, 'image_url');
    result.body = extractTag(message, 'body') || '';
    result.footer = extractTag(message, 'footer');
    
    const btnRaw = extractTag(message, 'button');
    if (btnRaw && btnRaw.includes('|')) {
      const parts = btnRaw.split('|');
      result.btnText = parts[0].trim();
      result.btnUrl = parts[1].trim();
    }
  }

  return result;
}

function compileTemplateMessage(templateKey, placeholders, guestMobile) {
  try {
    const templates = JSON.parse(getSetting('whatsapp_templates', '{}'));
    const tpl = templates[templateKey];
    
    if (!tpl) return; // No template configured

    // Fetch body or fallback to old configuration format keys
    let body = tpl.body;
    if (body === undefined && typeof templates[templateKey + '_confirmation'] === 'string') {
      body = templates[templateKey + '_confirmation'];
    }
    if (body === undefined && typeof templates[templateKey] === 'string') {
      body = templates[templateKey];
    }
    if (!body) return; // No body configured

    // Replace placeholders in body
    for (const key in placeholders) {
      body = body.replace(new RegExp(`{${key}}`, 'g'), placeholders[key]);
    }

    // Replace placeholders in URL
    let btnUrl = tpl.btn_url || '';
    for (const key in placeholders) {
      btnUrl = btnUrl.replace(new RegExp(`{${key}}`, 'g'), placeholders[key]);
    }

    // Assemble custom tags
    let msg = '';
    msg += `{template_key}${templateKey}{/template_key}\n`;
    msg += `{placeholders}${JSON.stringify(placeholders)}{/placeholders}\n`;
    if (tpl.image) {
      msg += `{image_url}${tpl.image}{/image_url}\n`;
    }
    msg += `{body}${body}{/body}`;
    if (tpl.footer) {
      msg += `\n{footer}${tpl.footer}{/footer}`;
    }
    if (tpl.btn_text && btnUrl) {
      msg += `\n{button}${tpl.btn_text}|${btnUrl}{/button}`;
    }

    sendMockWhatsApp(guestMobile, msg);
  } catch (err) {
    console.error('Error compiling template message:', err);
  }
}

function mapTemplateParameters(templateKey, placeholders, bodyTextTemplate) {
  if (!placeholders) return [];
  const params = [];
  const addParam = (val) => params.push({ type: 'text', text: String(val !== undefined && val !== null ? val : '') });

  // 1. Dynamic extraction of placeholders like {guest_name}, {booking_uid} in order of occurrence
  if (bodyTextTemplate) {
    const regex = /\{([a-zA-Z0-9_-]+)\}/g;
    let match;
    const seen = [];
    while ((match = regex.exec(bodyTextTemplate)) !== null) {
      const key = match[1];
      if (!seen.includes(key)) {
        seen.push(key);
        addParam(placeholders[key] || '');
      }
    }
    if (seen.length > 0) {
      return params;
    }
  }

  // 2. Legacy fallback
  if (templateKey === 'booking') {
    addParam(placeholders.guest_name);
    addParam(placeholders.booking_uid);
    addParam(placeholders.check_in);
    addParam(placeholders.check_out);
    addParam(placeholders.room_type);
  } else if (templateKey === 'welcome') {
    addParam(placeholders.guest_name);
    addParam(placeholders.room_numbers);
    addParam(placeholders.wifi_password);
    addParam(placeholders.reception_phone);
  } else if (templateKey === 'invoice') {
    addParam(placeholders.guest_name);
    addParam(placeholders.amount);
  } else if (templateKey === 'checkout') {
    addParam(placeholders.guest_name);
  }
  return params;
}

function sendMockWhatsApp(phone, message) {
  // Always log to local simulator
  const logItem = logMockWhatsApp(phone, message);

  // Clean phone number
  let cleanPhone = phone.replace(/[^0-9]/g, '');
  if (cleanPhone.length === 10) {
    cleanPhone = '91' + cleanPhone;
  }

  // Parse message components
  const parsed = parseTemplateMessage(message);

  // Check if real provider is configured
  const provider = getSetting('whatsapp_provider', 'None');
  if (provider === 'None') return logItem;

  try {
    if (provider === 'WhatsJet') {
      const url = getSetting('whatsjet_api_url', '');
      const vendorUid = getSetting('whatsjet_vendor_uid', '');
      const token = getSetting('whatsjet_access_token', '');

      if (!url || !vendorUid || !token) {
        console.warn('WhatsJet settings incomplete. Skipping external dispatch.');
        return logItem;
      }

      // Compile message text for WhatsJet (Body + Footer + Button CTA)
      let whatsJetBodyText = parsed.body;
      if (parsed.footer) whatsJetBodyText += `\n\n${parsed.footer}`;
      if (parsed.btnText && parsed.btnUrl) whatsJetBodyText += `\n\n${parsed.btnText}: ${parsed.btnUrl}`;

      const postData = JSON.stringify({
        phone_number: cleanPhone,
        message_body: whatsJetBodyText
      });

      const targetUrl = new URL(`${url.replace(/\/$/, '')}/${vendorUid}/contact/send-message`);

      const options = {
        hostname: targetUrl.hostname,
        port: targetUrl.port || (targetUrl.protocol === 'https:' ? 443 : 80),
        path: targetUrl.pathname + targetUrl.search,
        method: 'POST',
        headers: {
          'Authorization': `Bearer ${token}`,
          'Content-Type': 'application/json',
          'Content-Length': Buffer.byteLength(postData)
        }
      };

      const httpLib = targetUrl.protocol === 'https:' ? https : http;

      const whatsJetReq = httpLib.request(options, (res) => {
        let body = '';
        res.on('data', chunk => body += chunk);
        res.on('end', () => {
          console.log(`WhatsJet dispatch status: ${res.statusCode}. Output: ${body}`);
          try {
            const parsedRes = JSON.parse(body);
            if (parsedRes && (parsedRes.wamid || parsedRes.log_uid)) {
              logItem.id = parsedRes.wamid || parsedRes.log_uid;
            }
          } catch (e) {
            console.error('Failed to parse WhatsJet response:', e);
          }
        });
      });

      whatsJetReq.on('error', (e) => {
        console.error('WhatsJet dispatch error:', e);
      });

      whatsJetReq.write(postData);
      whatsJetReq.end();
    } else if (provider === 'WhatsAppCloudAPI') {
      const token = getSetting('whatsapp_cloud_token', '');
      const phoneId = getSetting('whatsapp_cloud_phone_id', '');

      if (!token || !phoneId) {
        console.warn('WhatsApp Cloud API settings incomplete. Skipping external dispatch.');
        return logItem;
      }

      let postData;
      let isTemplate = false;

      if (parsed.templateKey) {
        let templates = {};
        try {
          templates = JSON.parse(getSetting('whatsapp_templates', '{}'));
        } catch (e) {}
        
        const tpl = templates[parsed.templateKey];
        if (tpl && tpl.meta_template_name) {
          isTemplate = true;
          const bodyParams = mapTemplateParameters(parsed.templateKey, parsed.placeholders, tpl.body);
          const components = [];

          // Image media header component
          if (tpl.image) {
            components.push({
              type: 'header',
              parameters: [
                {
                  type: 'image',
                  image: { link: tpl.image }
                }
              ]
            });
          }

          // Body components parameters
          if (bodyParams.length > 0) {
            components.push({
              type: 'body',
              parameters: bodyParams
            });
          }

          // Dynamic URL Button CTA component
          if (tpl.btn_url && tpl.btn_text) {
            let btnParamVal = '';
            if (parsed.templateKey === 'booking' && parsed.placeholders) {
              btnParamVal = parsed.placeholders.booking_uid || '';
            } else if (parsed.templateKey === 'invoice' && parsed.placeholders) {
              btnParamVal = parsed.placeholders.booking_uid || '';
            }

            if (btnParamVal) {
              components.push({
                type: 'button',
                sub_type: 'url',
                index: '0',
                parameters: [
                  {
                    type: 'text',
                    text: String(btnParamVal)
                  }
                ]
              });
            }
          }

          postData = JSON.stringify({
            messaging_product: 'whatsapp',
            recipient_type: 'individual',
            to: cleanPhone,
            type: 'template',
            template: {
              name: tpl.meta_template_name,
              language: {
                code: tpl.meta_language_code || 'en_US'
              },
              components
            }
          });
        }
      }

      if (!isTemplate) {
        // If an image URL is specified, Meta allows sending an image message first
        if (parsed.imageUrl) {
          const imagePayload = JSON.stringify({
            messaging_product: 'whatsapp',
            recipient_type: 'individual',
            to: cleanPhone,
            type: 'image',
            image: { link: parsed.imageUrl }
          });

          const imageOptions = {
            hostname: 'graph.facebook.com',
            port: 443,
            path: `/v20.0/${phoneId}/messages`,
            method: 'POST',
            headers: {
              'Authorization': `Bearer ${token}`,
              'Content-Type': 'application/json',
              'Content-Length': Buffer.byteLength(imagePayload)
            }
          };

          const imageReq = https.request(imageOptions, (res) => {
            let body = '';
            res.on('data', chunk => body += chunk);
            res.on('end', () => {
              console.log(`Meta Image dispatch status: ${res.statusCode}`);
            });
          });
          imageReq.on('error', (e) => console.error('Meta Image dispatch error:', e));
          imageReq.write(imagePayload);
          imageReq.end();
        }

        // Compile message text for Meta (Body + Footer + Button CTA)
        let metaBodyText = parsed.body;
        if (parsed.footer) metaBodyText += `\n\n${parsed.footer}`;
        if (parsed.btnText && parsed.btnUrl) metaBodyText += `\n\n${parsed.btnText}: ${parsed.btnUrl}`;

        postData = JSON.stringify({
          messaging_product: 'whatsapp',
          recipient_type: 'individual',
          to: cleanPhone,
          type: 'text',
          text: { body: metaBodyText }
        });
      }

      const options = {
        hostname: 'graph.facebook.com',
        port: 443,
        path: `/v20.0/${phoneId}/messages`,
        method: 'POST',
        headers: {
          'Authorization': `Bearer ${token}`,
          'Content-Type': 'application/json',
          'Content-Length': Buffer.byteLength(postData)
        }
      };

      const metaReq = https.request(options, (res) => {
        let body = '';
        res.on('data', chunk => body += chunk);
        res.on('end', () => {
          console.log(`Meta Cloud API dispatch status: ${res.statusCode}. Output: ${body}`);
          try {
            const parsedRes = JSON.parse(body);
            if (parsedRes && parsedRes.messages && parsedRes.messages[0]) {
              const wamid = parsedRes.messages[0].id;
              logItem.id = wamid;
            }
          } catch (e) {
            console.error('Failed to parse Meta response wamid:', e);
          }
        });
      });

      metaReq.on('error', (e) => {
        console.error('Meta Cloud API dispatch error:', e);
      });

      metaReq.write(postData);
      metaReq.end();
    }
  } catch (err) {
    console.error('WhatsApp API dispatch failed:', err);
  }

  // SQLite Persistence & WebSocket Broadcast
  try {
    const cleanPhoneStr = cleanPhone.replace(/\D/g, '');
    const suffixStr = cleanPhoneStr.slice(-10);
    let guest = db.prepare("SELECT * FROM guests WHERE REPLACE(mobile, ' ', '') LIKE ?").get('%' + suffixStr);
    
    if (!guest) {
      const runInsert = db.prepare("INSERT INTO guests (name, mobile, reply_bot_enabled) VALUES (?, ?, 1)");
      const info = runInsert.run(phone, phone);
      guest = { id: info.lastInsertRowid, name: phone, mobile: phone };
    }

    const wamid = logItem.id || `msg_${Date.now()}_${Math.floor(Math.random() * 10000)}`;
    logItem.id = wamid;

    const dbTime = new Date().toISOString();
    let mType = 'text';
    let mUrl = '';
    if (parsed.imageUrl) {
      mType = 'image';
      mUrl = parsed.imageUrl;
    }
    
    db.prepare(`
      INSERT INTO whatsapp_messages (wamid, guest_id, direction, sender_type, body, media_type, media_url, status, timestamp)
      VALUES (?, ?, 'outgoing', 'staff', ?, ?, ?, 'sent', ?)
    `).run(wamid, guest.id, parsed.body || message, mType, mUrl, dbTime);

    broadcast({
      type: 'new_message',
      message: {
        id: wamid,
        guest_id: guest.id,
        guest_name: guest.name,
        direction: 'outgoing',
        sender_type: 'staff',
        body: parsed.body || message,
        media_type: mType,
        media_url: mUrl,
        timestamp: new Date(dbTime).toLocaleTimeString([], { hour: '2-digit', minute: '2-digit' })
      }
    });
  } catch (dbErr) {
    console.error('[SQLite send log error]:', dbErr.message);
  }

  return logItem;
}

// Router API handler
async function handleAPIRequest(req, res, pathname, query) {
  const method = req.method;

  // Enable CORS
  if (method === 'OPTIONS') {
    res.writeHead(200, {
      'Access-Control-Allow-Origin': '*',
      'Access-Control-Allow-Methods': 'GET, POST, OPTIONS, PUT, DELETE',
      'Access-Control-Allow-Headers': 'Content-Type, Authorization'
    });
    res.end();
    return;
  }

  // Token session verification for all private API routes
  const isPublicRoute = (
    pathname === '/api/heartbeat' ||
    pathname === '/api/login' ||
    pathname === '/api/whatsapp/webhook' ||
    pathname === '/api/google/oauth2callback'
  );

  if (pathname.startsWith('/api/') && !isPublicRoute) {
    let token = null;
    const authHeader = req.headers['authorization'];
    if (authHeader && authHeader.startsWith('Bearer ')) {
      token = authHeader.substring(7);
    } else if (query && query.token) {
      token = query.token;
    }

    if (!token) {
      return sendError(res, 'Unauthorized: Access token missing', 401);
    }
    const session = db.prepare(`
      SELECT s.*, u.username, u.role, u.name 
      FROM user_sessions s 
      JOIN users u ON s.user_id = u.id 
      WHERE s.token = ? AND s.expires_at > ?
    `).get(token, new Date().toISOString());

    if (!session) {
      return sendError(res, 'Unauthorized: Session invalid or expired', 401);
    }
    
    // Attach session to request context
    req.session = session;
  }

  // Heartbeat endpoint
  if (pathname === '/api/heartbeat') {
    lastHeartbeat = Date.now();
    return sendJSON(res, { ok: true, serverTime: new Date().toISOString() });
  }

  // Login
  if (pathname === '/api/login' && method === 'POST') {
    const { username, password } = await parseJSON(req);
    const user = db.prepare('SELECT * FROM users WHERE username = ?').get(username);
    if (user && user.salt) {
      const hash = crypto.createHmac('sha256', user.salt).update(password).digest('hex');
      if (hash === user.password) {
        // Generate cryptographic token
        const token = crypto.randomBytes(32).toString('hex');
        const expiresAt = new Date(Date.now() + 24 * 60 * 60 * 1000).toISOString();
        db.prepare('INSERT INTO user_sessions (token, user_id, created_at, expires_at) VALUES (?, ?, ?, ?)')
          .run(token, user.id, new Date().toISOString(), expiresAt);

        logAudit(user.id, 'User Login', `Logged in from IP: ${req.socket.remoteAddress}`);
        return sendJSON(res, { 
          token, 
          user: { id: user.id, username: user.username, role: user.role, name: user.name } 
        });
      }
    }
    return sendError(res, 'Invalid credentials', 401);
  }

  // Dashboard Stats
  if (pathname === '/api/dashboard-stats' && method === 'GET') {
    const today = new Date().toISOString().split('T')[0];
    
    // Total rooms, occupied, vacant, dirty, maintenance
    const rooms = db.prepare('SELECT status, COUNT(*) as count FROM rooms GROUP BY status').all();
    let occupied = 0, vacant = 0, dirty = 0, maintenance = 0, cleaning = 0;
    rooms.forEach(r => {
      if (r.status === 'Occupied') occupied = r.count;
      else if (r.status === 'Vacant') vacant = r.count;
      else if (r.status === 'Dirty') dirty = r.count;
      else if (r.status === 'Maintenance') maintenance = r.count;
      else if (r.status === 'Cleaning') cleaning = r.count;
    });
    const totalRooms = occupied + vacant + dirty + maintenance + cleaning;
    const occupancyRate = totalRooms > 0 ? Math.round((occupied / totalRooms) * 100) : 0;

    // Active Bookings
    const activeBookingsCount = db.prepare("SELECT COUNT(*) as count FROM bookings WHERE status = 'Checked-In'").get().count;
    
    // Revenue today
    const revenueRow = db.prepare("SELECT SUM(paid_amount) as total FROM bookings WHERE created_at LIKE ? AND status != 'Cancelled'").get(today + '%');
    const revenueToday = revenueRow.total || 0;

    // Low stock inventory warning (count items below min_quantity)
    const lowStockCount = db.prepare('SELECT COUNT(*) as count FROM inventory WHERE quantity <= min_quantity').get().count;

    // Room grid list
    const roomList = db.prepare(`
      SELECT r.id, r.room_number, r.status, rt.name as type, rt.base_price as price, f.name as floor
      FROM rooms r
      JOIN room_types rt ON r.room_type_id = rt.id
      JOIN floors f ON r.floor_id = f.id
      ORDER BY r.room_number
    `).all();

    // WhatsApp & Email Statistics
    const activeWANumbers = db.prepare("SELECT COUNT(*) as count FROM whatsapp_phone_numbers WHERE disabled = 0").get().count;
    const defaultNumberRow = db.prepare("SELECT display_number, status FROM whatsapp_phone_numbers WHERE is_default = 1").get();
    const defaultWANumberStatus = defaultNumberRow ? defaultNumberRow.status : 'Disconnected';
    
    const todayStr = new Date().toISOString().split('T')[0];
    const todayChatsCount = db.prepare("SELECT COUNT(DISTINCT guest_id) as count FROM whatsapp_messages WHERE timestamp LIKE ?").get(todayStr + '%').count;
    const unreadCount = db.prepare("SELECT COUNT(*) as count FROM whatsapp_messages WHERE status = 'delivered' AND direction = 'incoming'").get().count;
    
    const msgStatuses = db.prepare("SELECT status, COUNT(*) as count FROM whatsapp_messages WHERE direction = 'outgoing' GROUP BY status").all();
    let sentCount = 0, deliveredCount = 0, readCount = 0, failedCount = 0;
    msgStatuses.forEach(s => {
      if (s.status === 'sent') sentCount = s.count;
      else if (s.status === 'delivered') deliveredCount = s.count;
      else if (s.status === 'read') readCount = s.count;
      else if (s.status === 'failed') failedCount = s.count;
    });
    
    const emailsCount = db.prepare("SELECT COUNT(*) as count FROM email_logs").get().count;
    
    let pendingRepliesCount = 0;
    try {
      pendingRepliesCount = db.prepare(`
        SELECT COUNT(DISTINCT guest_id) as count FROM (
          SELECT guest_id, direction, ROW_NUMBER() OVER (PARTITION BY guest_id ORDER BY timestamp DESC) as rn 
          FROM whatsapp_messages
        ) WHERE rn = 1 AND direction = 'incoming'
      `).get().count;
    } catch (e) {
      // Fallback if ROW_NUMBER syntax has issues
    }

    return sendJSON(res, {
      occupancyRate,
      activeBookingsCount,
      revenueToday,
      lowStockCount,
      roomList,
      roomCounts: { total: totalRooms, occupied, vacant, dirty, maintenance, cleaning },
      waStats: {
        activeNumbers: activeWANumbers,
        defaultStatus: defaultWANumberStatus,
        todayChats: todayChatsCount,
        unread: unreadCount,
        sent: sentCount,
        delivered: deliveredCount,
        read: readCount,
        failed: failedCount,
        emails: emailsCount,
        pending: pendingRepliesCount
      }
    });
  }

  // Rooms CRUD & List
  if (pathname === '/api/rooms' && method === 'GET') {
    const list = db.prepare(`
      SELECT r.id, r.room_number, r.status, rt.id as room_type_id, rt.name as type, COALESCE(r.price, rt.base_price) as price, r.price as custom_price, f.id as floor_id, f.name as floor
      FROM rooms r
      JOIN room_types rt ON r.room_type_id = rt.id
      JOIN floors f ON r.floor_id = f.id
      ORDER BY r.room_number
    `).all();
    return sendJSON(res, list);
  }

  if (pathname === '/api/rooms/status' && method === 'POST') {
    const { roomId, status, userId } = await parseJSON(req);
    const stmt = db.prepare('UPDATE rooms SET status = ? WHERE id = ?');
    stmt.run(status, roomId);
    logAudit(userId, 'Room Status Change', `Room ID ${roomId} updated to ${status}`);
    return sendJSON(res, { ok: true });
  }

  // Room Types & Floors
  if (pathname === '/api/room-types' && method === 'GET') {
    return sendJSON(res, db.prepare('SELECT * FROM room_types').all());
  }
  if (pathname === '/api/floors' && method === 'GET') {
    return sendJSON(res, db.prepare('SELECT * FROM floors').all());
  }

  if (pathname === '/api/floors/save' && method === 'POST') {
    try {
      const { id, name, userId } = await parseJSON(req);
      if (!name) return sendError(res, 'Floor name is required', 400);

      // Check duplicate floor name
      const dupCheck = db.prepare('SELECT id FROM floors WHERE name = ? AND id != ?').get(name, id || -1);
      if (dupCheck) {
        return sendError(res, `Floor "${name}" already exists.`, 400);
      }

      if (id) {
        db.prepare('UPDATE floors SET name = ? WHERE id = ?').run(name, id);
        logAudit(userId, 'Floor Update', `Floor ID ${id} renamed to "${name}"`);
      } else {
        db.prepare('INSERT INTO floors (name) VALUES (?)').run(name);
        logAudit(userId, 'Floor Creation', `Floor "${name}" created`);
      }
      return sendJSON(res, { ok: true });
    } catch (err) {
      return sendError(res, err.message, 500);
    }
  }

  if (pathname === '/api/floors/delete' && method === 'POST') {
    try {
      const { floorId, userId } = await parseJSON(req);
      
      // Check if any rooms are on this floor
      const roomCheck = db.prepare('SELECT id FROM rooms WHERE floor_id = ?').get(floorId);
      if (roomCheck) {
        return sendError(res, 'This floor has rooms allocated to it. Delete or move the rooms first.', 400);
      }

      db.prepare('DELETE FROM floors WHERE id = ?').run(floorId);
      logAudit(userId, 'Floor Deletion', `Floor ID ${floorId} deleted`);
      return sendJSON(res, { ok: true });
    } catch (err) {
      return sendError(res, err.message, 500);
    }
  }

  if (pathname === '/api/rooms/save' && method === 'POST') {
    try {
      const { id, roomNumber, floorId, roomTypeId, status, price, userId } = await parseJSON(req);
      
      // Check if room number already exists for another room
      const dupCheck = db.prepare('SELECT id FROM rooms WHERE room_number = ? AND id != ?').get(roomNumber, id || -1);
      if (dupCheck) {
        return sendError(res, `Room number ${roomNumber} is already in use.`, 400);
      }

      if (id) {
        // Update
        const stmt = db.prepare('UPDATE rooms SET room_number = ?, floor_id = ?, room_type_id = ?, status = ?, price = ? WHERE id = ?');
        stmt.run(roomNumber, floorId, roomTypeId, status, price, id);
        logAudit(userId, 'Room Update', `Room ID ${id} modified to Room ${roomNumber} (Rent: ₹${price})`);
      } else {
        // Insert
        const stmt = db.prepare('INSERT INTO rooms (room_number, floor_id, room_type_id, status, price) VALUES (?, ?, ?, ?, ?)');
        stmt.run(roomNumber, floorId, roomTypeId, status, price);
        logAudit(userId, 'Room Creation', `Room ${roomNumber} created (Rent: ₹${price})`);
      }
      return sendJSON(res, { ok: true });
    } catch (err) {
      return sendError(res, err.message, 500);
    }
  }

  if (pathname === '/api/rooms/delete' && method === 'POST') {
    try {
      const { roomId, userId } = await parseJSON(req);
      
      // Check if the room has active bookings
      const activeBooking = db.prepare(`
        SELECT b.id 
        FROM bookings b
        JOIN booking_rooms br ON b.id = br.booking_id
        WHERE br.room_id = ? AND b.status IN ('Confirmed', 'Checked-In')
      `).get(roomId);

      if (activeBooking) {
        return sendError(res, 'This room has active bookings and cannot be deleted.', 400);
      }

      const stmt = db.prepare('DELETE FROM rooms WHERE id = ?');
      stmt.run(roomId);
      logAudit(userId, 'Room Deletion', `Room ID ${roomId} deleted`);
      return sendJSON(res, { ok: true });
    } catch (err) {
      return sendError(res, err.message, 500);
    }
  }

  // OTA / Channel Manager API
  if (pathname === '/api/ota/bookings' && method === 'GET') {
    const list = db.prepare(`
      SELECT o.*, rt.name as room_type
      FROM ota_bookings o
      JOIN room_types rt ON o.room_type_id = rt.id
      ORDER BY o.id DESC
    `).all();
    return sendJSON(res, list);
  }

  if (pathname === '/api/ota/bookings/sync' && method === 'POST') {
    try {
      const { userId } = await parseJSON(req);
      const channels = ['Booking.com', 'Agoda', 'MakeMyTrip'];
      const guests = ['David Miller', 'Ananya Sen', 'Vikram Rathore', 'Jessica Taylor'];
      const mobiles = ['+919876543220', '+919876543221', '+919876543222', '+919876543223'];
      
      const randomIdx = Math.floor(Math.random() * 4);
      const channel = channels[Math.floor(Math.random() * channels.length)];
      const otaUid = 'OTA-' + Math.random().toString(36).substr(2, 6).toUpperCase();
      
      // Check room type IDs
      const types = db.prepare('SELECT id, base_price FROM room_types').all();
      const rType = types[Math.floor(Math.random() * types.length)];
      
      const inDate = new Date();
      inDate.setDate(inDate.getDate() + Math.floor(Math.random() * 5) + 1);
      const outDate = new Date();
      outDate.setDate(inDate.getDate() + Math.floor(Math.random() * 3) + 1);
      
      const checkInStr = inDate.toISOString().split('T')[0];
      const checkOutStr = outDate.toISOString().split('T')[0];
      
      db.prepare(`
        INSERT INTO ota_bookings (channel, ota_booking_uid, guest_name, guest_mobile, check_in, check_out, room_type_id, amount, status)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'Pending')
      `).run(channel, otaUid, guests[randomIdx], mobiles[randomIdx], checkInStr, checkOutStr, rType.id, rType.base_price * 2);

      logAudit(userId, 'Channel Sync', `Synced with external portals. New booking ${otaUid} downloaded.`);
      return sendJSON(res, { ok: true, otaUid });
    } catch (err) {
      return sendError(res, err.message, 500);
    }
  }

  if (pathname === '/api/ota/bookings/map' && method === 'POST') {
    try {
      const { otaBookingId, userId } = await parseJSON(req);
      db.prepare("UPDATE ota_bookings SET status = 'Mapped' WHERE id = ?").run(otaBookingId);
      logAudit(userId, 'Map OTA Booking', `OTA Booking ID ${otaBookingId} mapped to local system`);
      return sendJSON(res, { ok: true });
    } catch (err) {
      return sendError(res, err.message, 500);
    }
  }

  // Customer Reviews API
  if (pathname === '/api/reviews' && method === 'GET') {
    const list = db.prepare('SELECT * FROM customer_reviews ORDER BY timestamp DESC').all();
    return sendJSON(res, list);
  }

  if (pathname === '/api/reviews/reply' && method === 'POST') {
    try {
      const { reviewId, replyText, userId } = await parseJSON(req);
      db.prepare('UPDATE customer_reviews SET reply = ? WHERE id = ?').run(replyText || '', reviewId);
      logAudit(userId, 'Review Reply', `Replied to review ID ${reviewId}`);
      return sendJSON(res, { ok: true });
    } catch (err) {
      return sendError(res, err.message, 500);
    }
  }

  // Custom WhatsApp dispatch
  if (pathname === '/api/whatsapp/custom' && method === 'POST') {
    try {
      const { phone, message, userId } = await parseJSON(req);
      if (!phone || !message) return sendError(res, 'Phone and message are required', 400);

      const formattedMsg = `{body}${message}{/body}`;
      sendMockWhatsApp(phone, formattedMsg);
      logAudit(userId, 'Send Custom WhatsApp', `Custom message sent to ${phone}`);
      return sendJSON(res, { ok: true });
    } catch (err) {
      return sendError(res, err.message, 500);
    }
  }

  // Real Chat WhatsApp dispatch
  if (pathname === '/api/whatsapp/send' && method === 'POST') {
    try {
      const { phone, message, userId } = await parseJSON(req);
      if (!phone || !message) return sendError(res, 'Phone and message are required', 400);

      const formattedMsg = `{body}${message}{/body}`;
      sendMockWhatsApp(phone, formattedMsg);
      logAudit(userId, 'Send WhatsApp Chat Message', `Chat message sent to ${phone}`);
      return sendJSON(res, { ok: true });
    } catch (err) {
      return sendError(res, err.message, 500);
    }
  }

  // Room Types CRUD
  if (pathname === '/api/room-types' && method === 'GET') {
    const list = db.prepare('SELECT * FROM room_types ORDER BY name').all();
    return sendJSON(res, list);
  }

  if (pathname === '/api/room-types/save' && method === 'POST') {
    try {
      const { id, name, basePrice, userId } = await parseJSON(req);
      if (!name || !basePrice) return sendError(res, 'Name and base price are required', 400);

      if (id) {
        db.prepare('UPDATE room_types SET name = ?, base_price = ? WHERE id = ?').run(name, basePrice, id);
        logAudit(userId, 'Update Room Type', `Room type ${name} updated`);
      } else {
        db.prepare('INSERT INTO room_types (name, base_price) VALUES (?, ?)').run(name, basePrice);
        logAudit(userId, 'Create Room Type', `Room type ${name} created`);
      }
      return sendJSON(res, { ok: true });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/room-types/delete' && method === 'POST') {
    try {
      const { roomTypeId, userId } = await parseJSON(req);
      const roomsCount = db.prepare('SELECT COUNT(*) as count FROM rooms WHERE room_type_id = ?').get(roomTypeId).count;
      if (roomsCount > 0) {
        return sendError(res, 'Cannot delete room type: rooms are currently assigned to this type.', 400);
      }

      db.prepare('DELETE FROM room_types WHERE id = ?').run(roomTypeId);
      logAudit(userId, 'Delete Room Type', `Room type ID ${roomTypeId} deleted`);
      return sendJSON(res, { ok: true });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  // Staff Delete API
  if (pathname === '/api/staff/delete' && method === 'POST') {
    try {
      const { staffId, userId } = await parseJSON(req);
      if (!staffId) return sendError(res, 'Staff ID is required', 400);

      const targetUser = db.prepare('SELECT * FROM users WHERE id = ?').get(staffId);
      if (targetUser && targetUser.username === 'admin') {
        return sendError(res, 'Cannot delete default system administrator.', 403);
      }

      db.prepare('DELETE FROM users WHERE id = ?').run(staffId);
      logAudit(userId, 'Delete Staff', `Staff member ${targetUser ? targetUser.name : staffId} deleted`);
      return sendJSON(res, { ok: true });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  // Guest Delete API
  if (pathname === '/api/guests/delete' && method === 'POST') {
    try {
      const { guestId, userId } = await parseJSON(req);
      if (!guestId) return sendError(res, 'Guest ID is required', 400);

      const activeBookings = db.prepare("SELECT COUNT(*) as count FROM bookings WHERE guest_id = ? AND status != 'Checked-Out' AND status != 'Cancelled'").get(guestId).count;
      if (activeBookings > 0) {
        return sendError(res, 'Cannot delete guest with active or checked-in reservations.', 400);
      }

      db.prepare('DELETE FROM guests WHERE id = ?').run(guestId);
      logAudit(userId, 'Delete Guest Profile', `Guest profile ID ${guestId} deleted`);
      return sendJSON(res, { ok: true });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  // Night Audit API
  if (pathname === '/api/settings/night-audit' && method === 'POST') {
    try {
      const { userId } = await parseJSON(req);
      const todayStr = new Date().toISOString().split('T')[0];

      const overdue = db.prepare("SELECT b.*, g.name as guest_name FROM bookings b JOIN guests g ON b.guest_id = g.id WHERE b.check_out <= ? AND b.status = 'Checked-In'").all(todayStr);
      const revenueRow = db.prepare("SELECT SUM(paid_amount) as total FROM bookings WHERE status = 'Checked-Out' AND created_at = ?").get(todayStr);
      const dailyRevenue = revenueRow.total || 0;

      logAudit(userId, 'Daily Night Audit', `Night audit run for ${todayStr}. Total settled revenue: ₹${dailyRevenue.toLocaleString()}. Overdues reconciled: ${overdue.length}`);
      
      return sendJSON(res, { 
        ok: true, 
        message: `Night audit compiled successfully for date: ${todayStr}!`,
        revenue: dailyRevenue,
        overduesCount: overdue.length
      });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  // Dynamic Room Pricing: Get / Set Pricing
  if (pathname === '/api/room-pricing' && method === 'GET') {
    const list = db.prepare(`
      SELECT rp.id, rt.name as room_type, rp.date, rp.price
      FROM room_pricing rp
      JOIN room_types rt ON rp.room_type_id = rt.id
      ORDER BY rp.date DESC
    `).all();
    return sendJSON(res, list);
  }
  if (pathname === '/api/room-pricing' && method === 'POST') {
    const { roomTypeId, date, price, userId } = await parseJSON(req);
    // Insert or replace dynamic price
    const stmt = db.prepare('INSERT OR REPLACE INTO room_pricing (room_type_id, date, price) VALUES (?, ?, ?)');
    stmt.run(roomTypeId, date, price);
    logAudit(userId, 'Update Room Pricing', `Room type ${roomTypeId} price set to ${price} for ${date}`);
    return sendJSON(res, { ok: true });
  }

  // Guests API
  if (pathname === '/api/guests' && method === 'GET') {
    const search = query.search || '';
    let list;
    if (search) {
      list = db.prepare(`
        SELECT * FROM guests 
        WHERE name LIKE ? OR mobile LIKE ? OR email LIKE ?
        ORDER BY name
      `).all(`%${search}%`, `%${search}%`, `%${search}%`);
    } else {
      list = db.prepare('SELECT * FROM guests ORDER BY name').all();
    }
    return sendJSON(res, list);
  }

  if (pathname === '/api/guests' && method === 'POST') {
    const body = await parseJSON(req);
    const userId = body.userId;
    
    // Check if guest exists by mobile
    const existing = db.prepare('SELECT * FROM guests WHERE mobile = ?').get(body.mobile);

    let guestId;
    if (existing && body.id === undefined) {
      // Return existing guest
      guestId = existing.id;
    } else if (body.id) {
      // Update
      const stmt = db.prepare(`
        UPDATE guests SET 
          name = ?, photo = ?, doc_type = ?, doc_number = ?, mobile = ?, email = ?, 
          address = ?, company_name = ?, gst_number = ?, vehicle_number = ?, 
          is_vip = ?, is_blacklisted = ?, blacklist_reason = ?, loyalty_points = ?
        WHERE id = ?
      `);
      stmt.run(
        body.name, body.photo || null, body.doc_type || null, body.doc_number || null, 
        body.mobile, body.email || null, body.address || null, body.company_name || null, 
        body.gst_number || null, body.vehicle_number || null, 
        body.is_vip ? 1 : 0, body.is_blacklisted ? 1 : 0, body.blacklist_reason || null,
        body.loyalty_points || 0, body.id
      );
      guestId = body.id;
      logAudit(userId, 'Update Guest', `Guest profile ${body.name} (${body.mobile}) updated`);
    } else {
      // Create new
      const stmt = db.prepare(`
        INSERT INTO guests (
          name, photo, doc_type, doc_number, mobile, email, address, company_name, gst_number, vehicle_number, is_vip, is_blacklisted, blacklist_reason, loyalty_points
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `);
      const info = stmt.run(
        body.name, body.photo || null, body.doc_type || null, body.doc_number || null, 
        body.mobile, body.email || null, body.address || null, body.company_name || null, 
        body.gst_number || null, body.vehicle_number || null, 
        body.is_vip ? 1 : 0, body.is_blacklisted ? 1 : 0, body.blacklist_reason || null,
        body.loyalty_points || 0
      );
      guestId = info.lastInsertRowid;
      logAudit(userId, 'Create Guest', `Guest profile ${body.name} (${body.mobile}) created`);
    }

    const savedGuest = db.prepare('SELECT * FROM guests WHERE id = ?').get(guestId);
    return sendJSON(res, savedGuest);
  }

  // Edit specific guest columns (staff assignment, bot toggle, starred, archived)
  if (pathname === '/api/guests/edit' && method === 'POST') {
    try {
      const body = await parseJSON(req);
      const { id, assigned_staff_id, reply_bot_enabled, is_starred, is_archived } = body;
      if (!id) {
        return sendError(res, 'Guest ID is required', 400);
      }

      const guest = db.prepare('SELECT * FROM guests WHERE id = ?').get(id);
      if (!guest) {
        return sendError(res, 'Guest not found', 404);
      }

      const updates = [];
      const params = [];

      if (assigned_staff_id !== undefined) {
        updates.push('assigned_staff_id = ?');
        params.push(assigned_staff_id === '' ? null : assigned_staff_id);
      }
      if (reply_bot_enabled !== undefined) {
        updates.push('reply_bot_enabled = ?');
        params.push(reply_bot_enabled);
      }
      if (is_starred !== undefined) {
        updates.push('is_starred = ?');
        params.push(is_starred);
      }
      if (is_archived !== undefined) {
        updates.push('is_archived = ?');
        params.push(is_archived);
      }

      if (updates.length > 0) {
        params.push(id);
        const queryStr = `UPDATE guests SET ${updates.join(', ')} WHERE id = ?`;
        db.prepare(queryStr).run(...params);
      }

      const updatedGuest = db.prepare('SELECT * FROM guests WHERE id = ?').get(id);
      return sendJSON(res, updatedGuest);
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  // Bookings API
  if (pathname === '/api/bookings' && method === 'GET') {
    const status = query.status || '';
    let queryStr = `
      SELECT b.*, g.name as guest_name, g.mobile as guest_mobile, g.email as guest_email, g.is_vip, g.is_blacklisted,
             GROUP_CONCAT(r.room_number) as room_numbers, GROUP_CONCAT(r.id) as room_ids
      FROM bookings b
      JOIN guests g ON b.guest_id = g.id
      LEFT JOIN booking_rooms br ON b.id = br.booking_id
      LEFT JOIN rooms r ON br.room_id = r.id
    `;
    
    let bookings;
    if (status) {
      queryStr += ` WHERE b.status = ? GROUP BY b.id ORDER BY b.check_in DESC`;
      bookings = db.prepare(queryStr).all(status);
    } else {
      queryStr += ` GROUP BY b.id ORDER BY b.check_in DESC`;
      bookings = db.prepare(queryStr).all();
    }
    
    return sendJSON(res, bookings);
  }

  // Cancel Booking API
  if (pathname === '/api/bookings/cancel' && method === 'POST') {
    try {
      const { bookingId, userId } = await parseJSON(req);
      const booking = db.prepare('SELECT * FROM bookings WHERE id = ?').get(bookingId);
      if (!booking) {
        return sendError(res, 'Booking not found', 404);
      }

      // Update booking status to Cancelled
      db.prepare("UPDATE bookings SET status = 'Cancelled' WHERE id = ?").run(bookingId);

      // Get allocated rooms
      const roomsList = db.prepare('SELECT room_id FROM booking_rooms WHERE booking_id = ?').all(bookingId);
      
      // Reset room status to Vacant
      const updateRoom = db.prepare("UPDATE rooms SET status = 'Vacant' WHERE id = ?");
      roomsList.forEach(r => {
        updateRoom.run(r.room_id);
      });

      logAudit(userId, 'Cancel Booking', `Booking ${booking.booking_uid} cancelled`);
      return sendJSON(res, { ok: true });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/bookings/new' && method === 'POST') {
    const body = await parseJSON(req);
    const { guestId, roomIds, checkIn, checkOut, status, paidAmount, userId, prices } = body;
    
    // Auto-generate booking UID and invoice number placeholder
    const bookingUid = 'BK' + (10000 + Math.floor(Math.random() * 90000));
    const created_at = new Date().toISOString().split('T')[0];

    // Calculate total base amount by summing up prices for all rooms across stay duration
    const diffTime = Math.abs(new Date(checkOut) - new Date(checkIn));
    const nights = Math.max(1, Math.ceil(diffTime / (1000 * 60 * 60 * 24)));
    
    let totalBase = 0;
    roomIds.forEach(rid => {
      const roomPrice = prices[rid] || 0;
      totalBase += roomPrice * nights;
    });

    const gstRate = parseFloat(getSetting('gst_rate', '5'));
    const gstAmount = Math.round(totalBase * (gstRate / 100));
    const totalAmount = totalBase + gstAmount;

    // Check if rooms are vacant
    for (const rid of roomIds) {
      const room = db.prepare('SELECT status FROM rooms WHERE id = ?').get(rid);
      if (status === 'Checked-In' && room.status !== 'Vacant') {
        return sendError(res, `Room ${rid} is not vacant. Current status: ${room.status}`, 400);
      }
    }

    // Insert booking
    const stmt = db.prepare(`
      INSERT INTO bookings (booking_uid, guest_id, check_in, check_out, status, total_amount, paid_amount, gst_amount, created_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);
    const bookingInfo = stmt.run(bookingUid, guestId, checkIn, checkOut, status, totalAmount, paidAmount || 0, gstAmount, created_at);
    const bookingId = bookingInfo.lastInsertRowid;

    // Insert booking rooms
    const insertBR = db.prepare('INSERT INTO booking_rooms (booking_id, room_id, price_per_night) VALUES (?, ?, ?)');
    roomIds.forEach(rid => {
      insertBR.run(bookingId, rid, prices[rid] || 0);
      
      // Update room status if checking-in immediately
      if (status === 'Checked-In') {
        db.prepare("UPDATE rooms SET status = 'Occupied' WHERE id = ?").run(rid);
      }
    });

    // If Checked-In, run inventory deduction
    if (status === 'Checked-In') {
      deductRoomOccupancyInventory(roomIds.length);
    }

    // Log WhatsApp messages if configured
    const guest = db.prepare('SELECT * FROM guests WHERE id = ?').get(guestId);
    
    // Confirmed booking message
    compileTemplateMessage('booking', {
      guest_name: guest.name,
      booking_uid: bookingUid,
      check_in: checkIn,
      check_out: checkOut,
      room_type: 'Hotel Room(s)'
    }, guest.mobile);

    // Welcome message if Checked-In
    if (status === 'Checked-In') {
      const roomNums = db.prepare(`SELECT room_number FROM rooms WHERE id IN (${roomIds.join(',')})`).all().map(r => r.room_number).join(', ');
      const wifi = getSetting('wifi_password', '');
      const phone = getSetting('reception_phone', '');
      
      compileTemplateMessage('welcome', {
        guest_name: guest.name,
        room_numbers: roomNums,
        wifi_password: wifi,
        reception_phone: phone
      }, guest.mobile);
    }

    if (status === 'Confirmed') {
      triggerAutomatedNotification('booking_confirmation', bookingId, guestId);
    } else if (status === 'Checked-In') {
      triggerAutomatedNotification('welcome_message', bookingId, guestId);
    }

    logAudit(userId, 'Create Booking', `Booking ${bookingUid} created for Guest ID ${guestId}`);
    return sendJSON(res, { ok: true, bookingId, bookingUid });
  }

  if (pathname === '/api/bookings/checkin' && method === 'POST') {
    const { bookingId, userId } = await parseJSON(req);
    
    // Get booking details
    const booking = db.prepare('SELECT * FROM bookings WHERE id = ?').get(bookingId);
    if (!booking) return sendError(res, 'Booking not found', 404);
    if (booking.status !== 'Confirmed') return sendError(res, 'Booking is already checked-in or completed', 400);

    // Get rooms
    const brooms = db.prepare('SELECT room_id FROM booking_rooms WHERE booking_id = ?').all(bookingId);
    const roomIds = brooms.map(r => r.room_id);

    // Check availability
    for (const rid of roomIds) {
      const room = db.prepare('SELECT status FROM rooms WHERE id = ?').get(rid);
      if (room.status !== 'Vacant') {
        return sendError(res, `Room is not vacant (status: ${room.status})`, 400);
      }
    }

    // Update rooms to Occupied
    roomIds.forEach(rid => {
      db.prepare("UPDATE rooms SET status = 'Occupied' WHERE id = ?").run(rid);
    });

    // Update booking status
    db.prepare("UPDATE bookings SET status = 'Checked-In' WHERE id = ?").run(bookingId);

    // Deduct stock
    deductRoomOccupancyInventory(roomIds.length);

    // Send Welcome Whatsapp
    const guest = db.prepare('SELECT * FROM guests WHERE id = ?').get(booking.guest_id);
    const roomNums = db.prepare(`SELECT room_number FROM rooms WHERE id IN (${roomIds.join(',')})`).all().map(r => r.room_number).join(', ');
    const wifi = getSetting('wifi_password', '');
    const phone = getSetting('reception_phone', '');

    compileTemplateMessage('welcome', {
      guest_name: guest.name,
      room_numbers: roomNums,
      wifi_password: wifi,
      reception_phone: phone
    }, guest.mobile);

    triggerAutomatedNotification('welcome_message', bookingId, booking.guest_id);

    logAudit(userId, 'Check-In Booking', `Booking ${booking.booking_uid} checked-in`);
    return sendJSON(res, { ok: true });
  }

  if (pathname === '/api/bookings/checkout' && method === 'POST') {
    const { bookingId, paidAmount, isLocalGST, userId } = await parseJSON(req);
    
    // Get booking
    const booking = db.prepare('SELECT * FROM bookings WHERE id = ?').get(bookingId);
    if (!booking) return sendError(res, 'Booking not found', 404);
    if (booking.status !== 'Checked-In') return sendError(res, 'Booking is not checked-in', 400);

    const guest = db.prepare('SELECT * FROM guests WHERE id = ?').get(booking.guest_id);
    const brooms = db.prepare('SELECT room_id FROM booking_rooms WHERE booking_id = ?').all(bookingId);
    const roomIds = brooms.map(r => r.room_id);

    // Generate Invoice Number
    const invoiceNum = 'INV-' + new Date().getFullYear() + '-' + (1000 + bookingId);

    // Update rooms status to Dirty
    roomIds.forEach(rid => {
      db.prepare("UPDATE rooms SET status = 'Dirty' WHERE id = ?").run(rid);
      // Auto assign a housekeeping task
      db.prepare("INSERT INTO housekeeping (room_id, date, status, notes) VALUES (?, ?, 'Pending', 'Clean up after guest checkout')")
        .run(rid, new Date().toISOString().split('T')[0]);
    });

    // Add loyalty points (1 point per 100 Rs spent on total invoice)
    const pointsEarned = Math.floor(booking.total_amount / 100);
    db.prepare('UPDATE guests SET loyalty_points = loyalty_points + ? WHERE id = ?').run(pointsEarned, guest.id);

    // Update booking to Checked-Out
    db.prepare(`
      UPDATE bookings 
      SET status = 'Checked-Out', paid_amount = ?, invoice_number = ?
      WHERE id = ?
    `).run(paidAmount, invoiceNum, bookingId);

    // Send checkout & Invoice WhatsApp
    compileTemplateMessage('invoice', {
      guest_name: guest.name,
      amount: booking.total_amount
    }, guest.mobile);

    triggerAutomatedNotification('invoice', bookingId, booking.guest_id);
    triggerAutomatedNotification('checkout_reminder', bookingId, booking.guest_id);
    triggerAutomatedNotification('thank_you_message', bookingId, booking.guest_id);
    triggerAutomatedNotification('review_request', bookingId, booking.guest_id);

    logAudit(userId, 'Check-Out Guest', `Booking ${booking.booking_uid} checked out. Invoice ${invoiceNum} generated.`);
    return sendJSON(res, { ok: true, invoiceNumber: invoiceNum });
  }

  // Room Transfer
  if (pathname === '/api/bookings/transfer' && method === 'POST') {
    const { bookingId, oldRoomId, newRoomId, userId } = await parseJSON(req);

    // Check if new room is vacant
    const newRoom = db.prepare('SELECT status FROM rooms WHERE id = ?').get(newRoomId);
    if (!newRoom || newRoom.status !== 'Vacant') {
      return sendError(res, 'New room is not vacant', 400);
    }

    // Update booking_rooms table
    db.prepare('UPDATE booking_rooms SET room_id = ? WHERE booking_id = ? AND room_id = ?')
      .run(newRoomId, bookingId, oldRoomId);

    // Swap rooms status
    db.prepare("UPDATE rooms SET status = 'Vacant' WHERE id = ?").run(oldRoomId);
    db.prepare("UPDATE rooms SET status = 'Occupied' WHERE id = ?").run(newRoomId);

    logAudit(userId, 'Room Transfer', `Transferred booking ${bookingId} from Room ID ${oldRoomId} to ${newRoomId}`);
    return sendJSON(res, { ok: true });
  }

  // Extend Stay
  if (pathname === '/api/bookings/extend' && method === 'POST') {
    const { bookingId, newCheckOutDate, additionalAmount, userId } = await parseJSON(req);

    const booking = db.prepare('SELECT * FROM bookings WHERE id = ?').get(bookingId);
    if (!booking) return sendError(res, 'Booking not found', 404);

    const newTotal = booking.total_amount + additionalAmount;
    db.prepare('UPDATE bookings SET check_out = ?, total_amount = ? WHERE id = ?')
      .run(newCheckOutDate, newTotal, bookingId);

    logAudit(userId, 'Extend Stay', `Extended booking ${bookingId} checkout to ${newCheckOutDate}, new total: ${newTotal}`);
    return sendJSON(res, { ok: true });
  }

  // Housekeeping API
  if (pathname === '/api/housekeeping' && method === 'GET') {
    const list = db.prepare(`
      SELECT h.*, r.room_number, r.status as room_status, u.name as staff_name
      FROM housekeeping h
      JOIN rooms r ON h.room_id = r.id
      LEFT JOIN users u ON h.staff_id = u.id
      ORDER BY h.date DESC, h.status DESC
    `).all();
    return sendJSON(res, list);
  }

  if (pathname === '/api/housekeeping/assign' && method === 'POST') {
    const { taskId, staffId, userId } = await parseJSON(req);
    db.prepare('UPDATE housekeeping SET staff_id = ?, status = \'Cleaning\' WHERE id = ?').run(staffId, taskId);
    
    // Also update room status to Cleaning
    const task = db.prepare('SELECT room_id FROM housekeeping WHERE id = ?').get(taskId);
    db.prepare("UPDATE rooms SET status = 'Cleaning' WHERE id = ?").run(task.room_id);

    logAudit(userId, 'Assign Housekeeping', `Housekeeping Task ${taskId} assigned to Staff ${staffId}`);
    return sendJSON(res, { ok: true });
  }

  if (pathname === '/api/housekeeping/status' && method === 'POST') {
    const { taskId, status, notes, userId } = await parseJSON(req);
    db.prepare('UPDATE housekeeping SET status = ?, notes = ? WHERE id = ?').run(status, notes || '', taskId);

    const task = db.prepare('SELECT room_id FROM housekeeping WHERE id = ?').get(taskId);
    if (status === 'Cleaned') {
      // Room is now vacant
      db.prepare("UPDATE rooms SET status = 'Vacant' WHERE id = ?").run(task.room_id);
    } else if (status === 'Maintenance') {
      db.prepare("UPDATE rooms SET status = 'Maintenance' WHERE id = ?").run(task.room_id);
    }
    
    logAudit(userId, 'Update Housekeeping Status', `Housekeeping Task ${taskId} updated to ${status}`);
    return sendJSON(res, { ok: true });
  }

  // Inventory API
  if (pathname === '/api/inventory' && method === 'GET') {
    return sendJSON(res, db.prepare('SELECT * FROM inventory ORDER BY name').all());
  }

  if (pathname === '/api/inventory/update' && method === 'POST') {
    const { id, quantity, min_quantity, name, category, userId } = await parseJSON(req);
    if (id) {
      db.prepare('UPDATE inventory SET quantity = ?, min_quantity = ?, name = ?, category = ? WHERE id = ?')
        .run(quantity, min_quantity, name, category, id);
      logAudit(userId, 'Update Inventory', `Inventory ID ${id} (${name}) updated. Qty: ${quantity}`);
    } else {
      db.prepare('INSERT INTO inventory (name, quantity, min_quantity, category) VALUES (?, ?, ?, ?)')
        .run(name, quantity, min_quantity, category);
      logAudit(userId, 'Add Inventory Item', `Inventory item ${name} added. Qty: ${quantity}`);
    }
    return sendJSON(res, { ok: true });
  }

  // Staff Management API
  if (pathname === '/api/staff' && method === 'GET') {
    return sendJSON(res, db.prepare("SELECT id, username, role, name FROM users WHERE role != 'Admin'").all());
  }

  if (pathname === '/api/staff/save' && method === 'POST') {
    try {
      const { id, name, role, username, password, userId } = await parseJSON(req);
      if (!name || !role || !username) {
        return sendError(res, 'Name, role, and username are required', 400);
      }

      // Check duplicate username
      const existingUser = db.prepare('SELECT id FROM users WHERE username = ? AND id != ?').get(username, id || -1);
      if (existingUser) {
        return sendError(res, 'Username already exists', 400);
      }

      if (id) {
        if (password) {
          const salt = crypto.randomBytes(16).toString('hex');
          const hash = crypto.createHmac('sha256', salt).update(password).digest('hex');
          db.prepare('UPDATE users SET name = ?, role = ?, username = ?, password = ?, salt = ? WHERE id = ?')
            .run(name, role, username, hash, salt, id);
        } else {
          db.prepare('UPDATE users SET name = ?, role = ?, username = ? WHERE id = ?')
            .run(name, role, username, id);
        }
        logAudit(userId, 'Edit Staff Account', `Staff member ${name} updated`);
      } else {
        if (!password) {
          return sendError(res, 'Password is required for new staff accounts', 400);
        }
        const salt = crypto.randomBytes(16).toString('hex');
        const hash = crypto.createHmac('sha256', salt).update(password).digest('hex');
        db.prepare('INSERT INTO users (name, role, username, password, salt) VALUES (?, ?, ?, ?, ?)')
          .run(name, role, username, hash, salt);
        logAudit(userId, 'Create Staff Account', `Staff member ${name} created`);
      }
      return sendJSON(res, { ok: true });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  // Social Sharing API
  if (pathname === '/api/social/share' && method === 'POST') {
    try {
      const { message, platforms, mediaUrl, mediaType, userId } = await parseJSON(req);
      const pageToken = getSetting('meta_social_page_token', '');
      const fbPageId = getSetting('meta_social_fb_page_id', '');
      const igAccountId = getSetting('meta_social_ig_account_id', '');
      const results = [];

      const makeMetaRequest = (url, params) => {
        return new Promise((resolve, reject) => {
          const urlObj = new URL(url);
          const postData = JSON.stringify(params);
          const metaReq = https.request(urlObj, {
            method: 'POST',
            headers: {
              'Content-Type': 'application/json',
              'Content-Length': Buffer.byteLength(postData)
            }
          }, (metaRes) => {
            let body = '';
            metaRes.on('data', chunk => body += chunk);
            metaRes.on('end', () => {
              try {
                const parsed = JSON.parse(body);
                if (metaRes.statusCode >= 200 && metaRes.statusCode < 300) {
                  resolve(parsed);
                } else {
                  reject(new Error(parsed.error ? parsed.error.message : 'Meta API error'));
                }
              } catch (e) {
                reject(new Error('Failed to parse Meta response'));
              }
            });
          });
          metaReq.on('error', reject);
          metaReq.write(postData);
          metaReq.end();
        });
      };

      if (platforms.includes('facebook')) {
        if (pageToken && fbPageId) {
          let fbUrl = `https://graph.facebook.com/v19.0/${fbPageId}/feed`;
          let fbParams = { message, access_token: pageToken };
          if (mediaUrl) {
            fbUrl = `https://graph.facebook.com/v19.0/${fbPageId}/photos`;
            fbParams = { url: mediaUrl, caption: message, access_token: pageToken };
          }
          const postResult = await makeMetaRequest(fbUrl, fbParams);
          results.push({ platform: 'facebook', status: 'Published', id: postResult.id || postResult.post_id });
        } else {
          results.push({ platform: 'facebook', status: 'Published (Simulated)', id: 'fb_' + Math.random().toString(36).substring(2, 10) });
        }
      }

      if (platforms.includes('instagram')) {
        if (pageToken && igAccountId && mediaUrl) {
          let igUrl = `https://graph.facebook.com/v19.0/${igAccountId}/media`;
          let igParams = { caption: message, access_token: pageToken };
          if (mediaType === 'REEL') {
            igParams.video_url = mediaUrl;
            igParams.media_type = 'REELS';
          } else {
            igParams.image_url = mediaUrl;
          }
          const containerResult = await makeMetaRequest(igUrl, igParams);
          const creationId = containerResult.creation_id;

          // Wait for media transcoding, then publish
          await new Promise(r => setTimeout(r, 3000));
          const publishUrl = `https://graph.facebook.com/v19.0/${igAccountId}/media_publish`;
          const publishResult = await makeMetaRequest(publishUrl, { creation_id: creationId, access_token: pageToken });
          results.push({ platform: 'instagram', status: 'Published', id: publishResult.id });
        } else {
          results.push({ platform: 'instagram', status: 'Published (Simulated)', id: 'ig_' + Math.random().toString(36).substring(2, 10) });
        }
      }

      logAudit(userId, 'Social Share', `Shared post to: ${platforms.join(', ')}`);
      return sendJSON(res, { ok: true, results });
    } catch (err) {
      return sendError(res, `Social sharing failed: ${err.message}`, 500);
    }
  }

  if (pathname === '/api/staff/attendance' && method === 'GET') {
    const list = db.prepare(`
      SELECT a.*, u.name, u.role
      FROM attendance a
      JOIN users u ON a.user_id = u.id
      ORDER BY a.date DESC
    `).all();
    return sendJSON(res, list);
  }

  if (pathname === '/api/staff/attendance' && method === 'POST') {
    const { userId, date, status, clockIn, clockOut, adminUserId } = await parseJSON(req);
    db.prepare(`
      INSERT OR REPLACE INTO attendance (user_id, date, status, clock_in, clock_out)
      VALUES (?, ?, ?, ?, ?)
    `).run(userId, date, status, clockIn || null, clockOut || null);
    logAudit(adminUserId, 'Log Attendance', `Staff ID ${userId} attendance logged for ${date} as ${status}`);
    return sendJSON(res, { ok: true });
  }

  if (pathname === '/api/staff/salaries' && method === 'GET') {
    const list = db.prepare(`
      SELECT s.*, u.name, u.role
      FROM salaries s
      JOIN users u ON s.user_id = u.id
      ORDER BY s.month DESC
    `).all();
    return sendJSON(res, list);
  }

  if (pathname === '/api/staff/salaries/pay' && method === 'POST') {
    const { userId, month, baseSalary, bonus, deductions, adminUserId } = await parseJSON(req);
    const paidDate = new Date().toISOString().split('T')[0];
    db.prepare(`
      INSERT OR REPLACE INTO salaries (user_id, month, base_salary, bonus, deductions, paid_date)
      VALUES (?, ?, ?, ?, ?, ?)
    `).run(userId, month, baseSalary, bonus || 0, deductions || 0, paidDate);
    logAudit(adminUserId, 'Pay Salary', `Paid salary to user ${userId} for month ${month}`);
    return sendJSON(res, { ok: true });
  }

  // Lost & Found API
  if (pathname === '/api/lost-found' && method === 'GET') {
    return sendJSON(res, db.prepare('SELECT * FROM lost_found ORDER BY date DESC').all());
  }

  if (pathname === '/api/lost-found/add' && method === 'POST') {
    const body = await parseJSON(req);
    const stmt = db.prepare(`
      INSERT INTO lost_found (item, description, date, location, status, contact_name, contact_phone)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `);
    stmt.run(body.item, body.description || null, body.date, body.location || null, 'Reported', body.contact_name || null, body.contact_phone || null);
    logAudit(body.userId, 'Report Lost Item', `Lost item reported: ${body.item}`);
    return sendJSON(res, { ok: true });
  }

  if (pathname === '/api/lost-found/claim' && method === 'POST') {
    const { id, contact_name, contact_phone, userId } = await parseJSON(req);
    db.prepare("UPDATE lost_found SET status = 'Claimed', contact_name = ?, contact_phone = ? WHERE id = ?")
      .run(contact_name, contact_phone, id);
    logAudit(userId, 'Claim Lost Item', `Lost item ID ${id} marked as Claimed by ${contact_name}`);
    return sendJSON(res, { ok: true });
  }

  // Settings
  if (pathname === '/api/settings' && method === 'GET') {
    const rows = db.prepare('SELECT * FROM settings').all();
    const settings = {};
    rows.forEach(r => {
      settings[r.key] = r.value;
    });
    return sendJSON(res, settings);
  }

  if (pathname === '/api/settings' && method === 'POST') {
    const { settings, userId } = await parseJSON(req);
    const stmt = db.prepare('INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)');
    for (const key in settings) {
      stmt.run(key, settings[key]);
    }
    logAudit(userId, 'Update Settings', 'Global settings updated');
    return sendJSON(res, { ok: true });
  }

// ================= WHATSAPP MEDIA DOWNLOAD HELPER FUNCTIONS ================= //

async function downloadMetaMedia(mediaId, token) {
  return new Promise((resolve, reject) => {
    const options = {
      hostname: 'graph.facebook.com',
      path: `/v20.0/${mediaId}`,
      method: 'GET',
      headers: {
        'Authorization': `Bearer ${token}`
      }
    };

    const req = https.request(options, (res) => {
      let data = '';
      res.on('data', (chunk) => { data += chunk; });
      res.on('end', () => {
        try {
          const mediaObj = JSON.parse(data);
          if (mediaObj.url) {
            downloadBinaryFile(mediaObj.url, token)
              .then(resolve)
              .catch(reject);
          } else {
            reject(new Error(mediaObj.error ? mediaObj.error.message : 'URL not found'));
          }
        } catch (e) {
          reject(e);
        }
      });
    });
    req.on('error', reject);
    req.end();
  });
}

async function downloadBinaryFile(url, token) {
  return new Promise((resolve, reject) => {
    const parsedUrl = new URL(url);
    const options = {
      hostname: parsedUrl.hostname,
      path: parsedUrl.pathname + parsedUrl.search,
      method: 'GET',
      headers: {
        'Authorization': `Bearer ${token}`,
        'User-Agent': 'Node.js/PMS'
      }
    };

    const req = https.request(options, (res) => {
      const mediaDir = path.join(__dirname, 'public', 'uploads');
      if (!fs.existsSync(mediaDir)) {
        fs.mkdirSync(mediaDir, { recursive: true });
      }

      const extMap = {
        'image/jpeg': '.jpg',
        'image/png': '.png',
        'image/gif': '.gif',
        'application/pdf': '.pdf',
        'application/msword': '.doc',
        'application/vnd.openxmlformats-officedocument.wordprocessingml.document': '.docx',
        'application/vnd.ms-excel': '.xls',
        'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet': '.xlsx',
        'audio/mpeg': '.mp3',
        'audio/ogg': '.ogg',
        'audio/aac': '.aac',
        'audio/mp4': '.m4a',
        'audio/amr': '.amr',
        'video/mp4': '.mp4',
        'video/3gpp': '.3gp'
      };

      const contentType = res.headers['content-type'] || '';
      const ext = extMap[contentType] || '.bin';
      const filename = `media_${Date.now()}_${Math.floor(Math.random() * 10000)}${ext}`;
      const filePath = path.join(mediaDir, filename);

      const fileStream = fs.createWriteStream(filePath);
      res.pipe(fileStream);

      fileStream.on('finish', () => {
        fileStream.close();
        resolve(`/uploads/${filename}`);
      });
      fileStream.on('error', reject);
    });
    req.on('error', reject);
    req.end();
  });
}

// ================= WHATSAPP OUTGOING MEDIA UPLOAD & PDF CREATION HELPERS ================= //
const PDFDocument = require('pdfkit');

function createInvoicePDF(booking, rooms, guest, filePath) {
  return new Promise((resolve, reject) => {
    try {
      const doc = new PDFDocument({ size: 'A4', margin: 50 });
      const writeStream = fs.createWriteStream(filePath);
      doc.pipe(writeStream);

      // Header Banner
      doc.fillColor('#1e293b').fontSize(22).text('SHAIMA HOTEL', { align: 'center', stroke: true });
      doc.fillColor('#475569').fontSize(9).text('123 Beach Road, Chennai, Tamil Nadu, India', { align: 'center' });
      doc.text('GSTIN: 33AAAAA1111A1Z1 | Phone: +91 9876543210', { align: 'center' });
      doc.moveDown(1.5);

      // Divider
      doc.moveTo(50, doc.y).lineTo(550, doc.y).strokeColor('#cbd5e1').stroke();
      doc.moveDown();

      // Meta grid
      doc.fillColor('#0f172a').fontSize(12).text('TAX INVOICE / RECEIPTS COPY', { bold: true });
      doc.moveDown(0.5);
      doc.fontSize(9).fillColor('#334155');
      doc.text(`Invoice No: ${booking.invoice_number || 'TEMP-INV-009'}`);
      doc.text(`Booking Ref: ${booking.booking_uid}`);
      doc.text(`Date of Issue: ${new Date().toLocaleDateString()}`);
      doc.text(`Guest Billing Name: ${guest.name}`);
      doc.text(`Mobile: ${guest.mobile}`);
      if (guest.email) doc.text(`Email Address: ${guest.email}`);
      doc.moveDown(1.5);

      // Table Header
      doc.fillColor('#0f172a').fontSize(10);
      const startY = doc.y;
      doc.text('Service Item', 50, startY, { bold: true });
      doc.text('Room(s)', 180, startY);
      doc.text('Stay Duration', 280, startY);
      doc.text('Paid status', 400, startY);
      doc.text('Amount', 480, startY, { align: 'right' });
      
      doc.moveDown(0.5);
      doc.moveTo(50, doc.y).lineTo(550, doc.y).strokeColor('#94a3b8').stroke();
      doc.moveDown(0.8);

      // Table Row
      const nights = Math.ceil((new Date(booking.check_out) - new Date(booking.check_in)) / 86400000) || 1;
      const rowY = doc.y;
      doc.fillColor('#334155').fontSize(9);
      doc.text('Deluxe Hotel Accommodations', 50, rowY);
      doc.text(rooms.map(r => r.room_number).join(', '), 180, rowY);
      doc.text(`${booking.check_in} to ${booking.check_out} (${nights} nights)`, 280, rowY);
      doc.text(booking.status, 400, rowY);
      doc.text(`₹${booking.total_amount.toLocaleString()}`, 480, rowY, { align: 'right' });

      doc.moveDown(2);
      doc.moveTo(50, doc.y).lineTo(550, doc.y).strokeColor('#cbd5e1').stroke();
      doc.moveDown();

      // Calculation summary
      const baseVal = booking.total_amount / 1.05;
      const gstVal = booking.total_amount - baseVal;
      const calcY = doc.y;
      doc.text('Subtotal:', 350, calcY);
      doc.text(`₹${baseVal.toFixed(2)}`, 480, calcY, { align: 'right' });
      doc.text('GST Rate (5%):', 350, calcY + 15);
      doc.text(`₹${gstVal.toFixed(2)}`, 480, calcY + 15, { align: 'right' });
      
      doc.fillColor('#0f172a').fontSize(11);
      doc.text('Total Paid Bill:', 350, calcY + 35, { bold: true });
      doc.text(`₹${booking.total_amount.toLocaleString()}`, 480, calcY + 35, { align: 'right', bold: true });

      doc.moveDown(4);
      doc.fillColor('#475569').fontSize(9).text('Terms & Conditions:', 50, doc.y, { bold: true });
      doc.text('1. Subject to local municipal jurisdictions.', 50, doc.y + 12);
      doc.text('2. This is a computer generated invoice copy requiring no manual signatures.', 50, doc.y + 24);

      doc.end();
      writeStream.on('finish', () => resolve(filePath));
      writeStream.on('error', reject);
    } catch (e) {
      reject(e);
    }
  });
}

function createReportPDF(title, headers, rows, filePath) {
  return new Promise((resolve, reject) => {
    try {
      const doc = new PDFDocument({ size: 'A4', margin: 50 });
      const writeStream = fs.createWriteStream(filePath);
      doc.pipe(writeStream);

      doc.fillColor('#1e293b').fontSize(18).text('SHAIMA HOTEL CONCIERGE', { align: 'center', stroke: true });
      doc.fillColor('#0f172a').fontSize(12).text(title, { align: 'center' });
      doc.fontSize(8).fillColor('#64748b').text(`Export Time: ${new Date().toLocaleString()}`, { align: 'center' });
      doc.moveDown(2);

      doc.moveTo(50, doc.y).lineTo(550, doc.y).strokeColor('#cbd5e1').stroke();
      doc.moveDown();

      doc.fontSize(9).fillColor('#0f172a');
      const startY = doc.y;
      const colWidth = 500 / headers.length;
      headers.forEach((h, i) => {
        doc.text(h, 50 + (i * colWidth), startY, { bold: true });
      });

      doc.moveDown(0.5);
      doc.moveTo(50, doc.y).lineTo(550, doc.y).strokeColor('#94a3b8').stroke();
      doc.moveDown(0.8);

      doc.fillColor('#334155');
      rows.forEach(row => {
        const rowY = doc.y;
        headers.forEach((h, i) => {
          doc.text(String(row[i] || ''), 50 + (i * colWidth), rowY);
        });
        doc.moveDown();
      });

      doc.end();
      writeStream.on('finish', () => resolve(filePath));
      writeStream.on('error', reject);
    } catch (e) {
      reject(e);
    }
  });
}

function uploadMetaMediaFile(filePath, filename, mimeType, token, phoneId) {
  return new Promise((resolve, reject) => {
    const boundary = `----WebKitFormBoundary${Math.random().toString(36).substring(2)}`;
    const fileBuffer = fs.readFileSync(filePath);
    
    const header = 
      `--${boundary}\r\n` +
      `Content-Disposition: form-data; name="file"; filename="${filename}"\r\n` +
      `Content-Type: ${mimeType}\r\n\r\n`;
      
    const footer = 
      `\r\n--${boundary}\r\n` +
      `Content-Disposition: form-data; name="messaging_product"\r\n\r\n` +
      `whatsapp\r\n` +
      `--${boundary}--\r\n`;

    const bodyBuffer = Buffer.concat([
      Buffer.from(header, 'utf8'),
      fileBuffer,
      Buffer.from(footer, 'utf8')
    ]);

    const options = {
      hostname: 'graph.facebook.com',
      port: 443,
      path: `/v20.0/${phoneId}/media`,
      method: 'POST',
      headers: {
        'Authorization': `Bearer ${token}`,
        'Content-Type': `multipart/form-data; boundary=${boundary}`,
        'Content-Length': bodyBuffer.length
      }
    };

    const req = https.request(options, (res) => {
      let data = '';
      res.on('data', chunk => data += chunk);
      res.on('end', () => {
        try {
          const parsed = JSON.parse(data);
          if (parsed.id) {
            resolve(parsed.id);
          } else {
            reject(new Error(parsed.error ? parsed.error.message : 'Media upload failed'));
          }
        } catch (e) {
          reject(e);
        }
      });
    });

    req.on('error', reject);
    req.write(bodyBuffer);
    req.end();
  });
}

  // WhatsApp Webhook Endpoints
  if (pathname === '/api/whatsapp/webhook' && method === 'GET') {
    const mode = query['hub.mode'];
    const token = query['hub.verify_token'];
    const challenge = query['hub.challenge'];
    
    const verifyToken = getSetting('whatsapp_webhook_verify_token', 'shaima_hotel_secret');
    
    if (mode === 'subscribe' && token === verifyToken) {
      console.log('[Webhook Verify]: Verification successful.');
      res.writeHead(200, { 'Content-Type': 'text/plain' });
      res.end(challenge);
      return;
    } else {
      console.warn('[Webhook Verify]: Verification failed.');
      return sendError(res, 'Forbidden', 403);
    }
  }

  if (pathname === '/api/whatsapp/webhook' && method === 'POST') {
    try {
      const body = await parseJSON(req);
      console.log('[Webhook Event Received]:', JSON.stringify(body));

      if (body.entry && body.entry[0] && body.entry[0].changes && body.entry[0].changes[0]) {
        const change = body.entry[0].changes[0];
        const value = change.value;

        // 1. Process Message Status Updates (sent, delivered, read, failed)
        if (value && value.statuses && value.statuses[0]) {
          const statusObj = value.statuses[0];
          const wamid = statusObj.id;
          const status = statusObj.status; // sent, delivered, read, failed

          try {
            db.prepare("UPDATE whatsapp_messages SET status = ? WHERE wamid = ?").run(status, wamid);
            broadcast({ type: 'status_update', wamid, status });
          } catch (dbErr) {
            console.error('[Webhook Status SQLite error]:', dbErr.message);
          }

          const logItem = whatsappLog.find(item => item.id === wamid);
          if (logItem) {
            logItem.status = status.charAt(0).toUpperCase() + status.slice(1);
            console.log(`[Webhook Status Sync]: Log ${wamid} synced status to ${logItem.status}`);
          }
        }

        // 2. Process Incoming Messages
        if (value && value.messages && value.messages[0]) {
          const msgObj = value.messages[0];
          const senderPhone = msgObj.from; // e.g. 914567299438
          const wamid = msgObj.id;
          const msgType = msgObj.type; // text, image, document, location, contacts
          const token = getSetting('whatsapp_cloud_token', '');

          // Resolve Contact profile name if exists
          const contactName = (value.contacts && value.contacts[0] && value.contacts[0].profile && value.contacts[0].profile.name) || senderPhone;

          // Query database to match guest
          const cleanPhone = senderPhone.replace(/\D/g, '');
          const suffix = cleanPhone.slice(-10);
          let guest = db.prepare("SELECT * FROM guests WHERE REPLACE(mobile, ' ', '') LIKE ?").get('%' + suffix);

          if (!guest) {
            const runInsert = db.prepare("INSERT INTO guests (name, mobile, reply_bot_enabled) VALUES (?, ?, 1)");
            const info = runInsert.run(contactName, '+' + cleanPhone);
            guest = { id: info.lastInsertRowid, name: contactName, mobile: '+' + cleanPhone, reply_bot_enabled: 1 };
          }

          let bodyText = '';
          let mediaType = 'text';
          let mediaUrl = '';

          if (msgType === 'text' && msgObj.text) {
            bodyText = msgObj.text.body;
            mediaType = 'text';
          } else if (msgType === 'image' && msgObj.image) {
            bodyText = msgObj.image.caption || 'Image Attachment';
            mediaType = 'image';
            try {
              mediaUrl = await downloadMetaMedia(msgObj.image.id, token);
            } catch (e) {
              console.error('Image media download failed:', e.message);
            }
          } else if (msgType === 'document' && msgObj.document) {
            bodyText = msgObj.document.filename || 'Document Attachment';
            mediaType = 'document';
            try {
              mediaUrl = await downloadMetaMedia(msgObj.document.id, token);
            } catch (e) {
              console.error('Document media download failed:', e.message);
            }
          } else if (msgType === 'video' && msgObj.video) {
            bodyText = 'Video Attachment';
            mediaType = 'video';
            try {
              mediaUrl = await downloadMetaMedia(msgObj.video.id, token);
            } catch (e) {
              console.error('Video media download failed:', e.message);
            }
          } else if (msgType === 'audio' && msgObj.audio) {
            bodyText = 'Audio Attachment';
            mediaType = 'audio';
            try {
              mediaUrl = await downloadMetaMedia(msgObj.audio.id, token);
            } catch (e) {
              console.error('Audio media download failed:', e.message);
            }
          } else if (msgType === 'location' && msgObj.location) {
            const loc = msgObj.location;
            bodyText = `Location: ${loc.latitude}, ${loc.longitude} (${loc.name || 'Current Location'} - ${loc.address || ''})`;
            mediaType = 'location';
          } else if (msgType === 'contacts' && msgObj.contacts && msgObj.contacts[0]) {
            const c = msgObj.contacts[0];
            const contactPhone = (c.phones && c.phones[0] && c.phones[0].phone) || '';
            bodyText = `Contact: ${c.name.formatted_name} (${contactPhone})`;
            mediaType = 'contact';
          } else {
            bodyText = `Received attachment of type: ${msgType}`;
            mediaType = 'document';
          }

          // Insert incoming message into local SQLite database
          const isoTime = new Date(parseInt(msgObj.timestamp, 10) * 1000).toISOString();
          db.prepare(`
            INSERT INTO whatsapp_messages (wamid, guest_id, direction, sender_type, body, media_type, media_url, status, timestamp)
            VALUES (?, ?, 'incoming', 'guest', ?, ?, ?, 'read', ?)
          `).run(wamid, guest.id, bodyText, mediaType, mediaUrl, isoTime);

          // Broadcast real-time websocket packet
          broadcast({
            type: 'new_message',
            message: {
              id: wamid,
              guest_id: guest.id,
              guest_name: guest.name,
              direction: 'incoming',
              sender_type: 'guest',
              body: bodyText,
              media_type: mediaType,
              media_url: mediaUrl,
              timestamp: new Date(isoTime).toLocaleTimeString([], { hour: '2-digit', minute: '2-digit' })
            }
          });

          // Trigger Auto AI Bot Response if enabled
          if (guest.reply_bot_enabled === 1 && mediaType === 'text') {
            setTimeout(async () => {
              let botReply = 'Thank you for reaching out! Our reception desk team has been notified of your message. (Concierge AI Bot)';
              const cleanMsg = bodyText.toLowerCase();

              if (cleanMsg.includes('wifi') || cleanMsg.includes('internet')) {
                botReply = 'The Shaima Hotel WiFi SSID is "SHAIMA_GUEST" and the password is: Shaima@2026. (Concierge AI Bot)';
              } else if (cleanMsg.includes('checkout') || cleanMsg.includes('check out') || cleanMsg.includes('settle')) {
                botReply = 'Standard checkout time is 11:00 AM. If you would like to settle bills early, please click "Billing" in your app to review outstanding room services. (Concierge AI Bot)';
              } else if (cleanMsg.includes('food') || cleanMsg.includes('breakfast') || cleanMsg.includes('menu')) {
                botReply = 'Breakfast is served daily from 7:30 AM to 10:30 AM at the Sea View Restaurant. Room service menu can be viewed on your desk QR code. (Concierge AI Bot)';
              }

              // Dispatch response via Meta Cloud API if token exists
              const phoneId = getSetting('whatsapp_cloud_phone_id', '');
              const botWamid = `bot_${Date.now()}_${Math.floor(Math.random() * 10000)}`;

              if (token && phoneId) {
                try {
                  const payload = {
                    messaging_product: 'whatsapp',
                    recipient_type: 'individual',
                    to: senderPhone,
                    type: 'text',
                    text: { body: botReply }
                  };

                  const postData = JSON.stringify(payload);
                  const reqOptions = {
                    hostname: 'graph.facebook.com',
                    path: `/v20.0/${phoneId}/messages`,
                    method: 'POST',
                    headers: {
                      'Authorization': `Bearer ${token}`,
                      'Content-Type': 'application/json',
                      'Content-Length': Buffer.byteLength(postData)
                    }
                  };

                  const postReq = https.request(reqOptions);
                  postReq.write(postData);
                  postReq.end();
                } catch (metaErr) {
                  console.error('[Bot Auto-response dispatch failed]:', metaErr.message);
                }
              }

              // Save outgoing bot reply to SQLite
              const botTime = new Date().toISOString();
              db.prepare(`
                INSERT INTO whatsapp_messages (wamid, guest_id, direction, sender_type, body, media_type, status, timestamp)
                VALUES (?, ?, 'outgoing', 'bot', ?, 'text', 'sent', ?)
              `).run(botWamid, guest.id, botReply, botTime);

              broadcast({
                type: 'new_message',
                message: {
                  id: botWamid,
                  guest_id: guest.id,
                  guest_name: guest.name,
                  direction: 'outgoing',
                  sender_type: 'bot',
                  body: botReply,
                  media_type: 'text',
                  media_url: '',
                  timestamp: new Date(botTime).toLocaleTimeString([], { hour: '2-digit', minute: '2-digit' })
                }
              });
            }, 1500);
          }
        }
      }

      return sendJSON(res, { ok: true });
    } catch (e) {
      console.error('[Webhook Event Error]:', e);
      return sendError(res, e.message, 500);
    }
  }

  // WhatsApp logs simulation
  if (pathname === '/api/whatsapp-simulator' && method === 'GET') {
    return sendJSON(res, whatsappLog.slice().reverse());
  }

  if (pathname === '/api/whatsapp-simulator/send' && method === 'POST') {
    const { phone, message } = await parseJSON(req);
    const logged = sendMockWhatsApp(phone, message);
    return sendJSON(res, logged);
  }

  function translateMetaPlaceholders(text, templateName) {
    if (!text) return '';
    const name = templateName.toLowerCase();
    if (name.includes('booking')) {
      return text
        .replace(/\{\{1\}\}/g, '{guest_name}')
        .replace(/\{\{2\}\}/g, '{booking_uid}')
        .replace(/\{\{3\}\}/g, '{check_in}')
        .replace(/\{\{4\}\}/g, '{check_out}')
        .replace(/\{\{5\}\}/g, '{room_type}');
    } else if (name.includes('welcome')) {
      return text
        .replace(/\{\{1\}\}/g, '{guest_name}')
        .replace(/\{\{2\}\}/g, '{room_numbers}')
        .replace(/\{\{3\}\}/g, '{wifi_password}')
        .replace(/\{\{4\}\}/g, '{reception_phone}');
    } else if (name.includes('invoice')) {
      return text
        .replace(/\{\{1\}\}/g, '{guest_name}')
        .replace(/\{\{2\}\}/g, '{amount}');
    } else if (name.includes('checkout')) {
      return text
        .replace(/\{\{1\}\}/g, '{guest_name}');
    }
    
    // Generic fallback for custom/other templates
    let index = 1;
    let result = text;
    while (result.includes(`{{${index}}}`)) {
      result = result.replace(new RegExp(`\\\\{\\\\{${index}\\\\}\\\\}(?!\\\\})`, 'g'), `{var${index}}`);
      result = result.replace(new RegExp(`\\{\\{${index}\\}\\}`, 'g'), `{var${index}}`);
      index++;
    }
    return result;
  }

  // ================= GOOGLE BUSINESS PROFILE & SEO ENDPOINTS ================= //
  if (pathname === '/api/google/auth-url' && method === 'GET') {
    const clientId = getSetting('google_client_id', '');
    const redirectUri = getSetting('google_redirect_uri', 'http://localhost:3000/api/google/oauth2callback');
    if (clientId) {
      const url = `https://accounts.google.com/o/oauth2/v2/auth?response_type=code&client_id=${clientId}&redirect_uri=${encodeURIComponent(redirectUri)}&scope=https://www.googleapis.com/auth/business.manage&access_type=offline&prompt=consent`;
      return sendJSON(res, { url });
    } else {
      const url = `/api/google/oauth2callback?code=mock_auth_code_123`;
      return sendJSON(res, { url, simulated: true });
    }
  }

  if (pathname === '/api/google/oauth2callback' && method === 'GET') {
    try {
      const code = query.code || 'mock_auth_code_123';
      const exist = db.prepare("SELECT COUNT(*) as count FROM google_locations WHERE location_id = ?").get('locations/67890').count;
      if (exist === 0) {
        db.prepare(`
          INSERT INTO google_locations (
            location_id, account_id, business_name, primary_category, email, access_token, refresh_token, status, address, phone, website, rating, total_reviews, completion_score, seo_health_score
          ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        `).run(
          'locations/67890',
          'accounts/12345',
          'Shaima Hotel & Marina Residency',
          'Hotel',
          'seo@shaimahotels.com',
          encryptToken('mock_access_token_123'),
          encryptToken('mock_refresh_token_123'),
          'Connected',
          '123 Beach Road, Chennai, Tamil Nadu, India',
          '+91 98765 43210',
          'https://shaimahotel.com',
          4.8,
          142,
          95,
          92
        );
      } else {
        db.prepare("UPDATE google_locations SET status = 'Connected' WHERE location_id = ?").run('locations/67890');
      }

      res.writeHead(200, { 'Content-Type': 'text/html' });
      res.end(`
        <html>
          <body style="font-family: sans-serif; text-align: center; padding-top: 50px; background: #0f172a; color: white;">
            <h2>Google Account Connected Successfully!</h2>
            <p>You can close this window now.</p>
            <script>
              if (window.opener) {
                window.opener.postMessage({ type: 'gmb-oauth-success' }, '*');
              }
              setTimeout(() => window.close(), 1500);
            </script>
          </body>
        </html>
      `);
      return;
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/disconnect' && method === 'POST') {
    try {
      db.prepare("UPDATE google_locations SET status = 'Disconnected' WHERE location_id = ?").run('locations/67890');
      return sendJSON(res, { ok: true });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/manual-save' && method === 'POST') {
    try {
      const { projectId, clientId, clientSecret, redirectUri, locationId } = await parseJSON(req);
      const insertSetting = db.prepare('INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)');
      insertSetting.run('google_project_id', projectId || '');
      insertSetting.run('google_client_id', clientId || '');
      insertSetting.run('google_client_secret', clientSecret || '');
      insertSetting.run('google_redirect_uri', redirectUri || '');
      
      if (locationId) {
        db.prepare(`
          INSERT INTO google_locations (
            location_id, account_id, business_name, primary_category, email, access_token, refresh_token, status, address, phone, website, rating, total_reviews, completion_score, seo_health_score
          ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
          ON CONFLICT(location_id) DO UPDATE SET status = 'Connected'
        `).run(
          locationId,
          'accounts/12345',
          'Shaima Hotel & Marina Residency',
          'Hotel',
          'seo@shaimahotels.com',
          encryptToken('mock_access_token_123'),
          encryptToken('mock_refresh_token_123'),
          'Connected',
          '123 Beach Road, Chennai, Tamil Nadu, India',
          '+91 98765 43210',
          'https://shaimahotel.com',
          4.8,
          142,
          95,
          92
        );
      }
      return sendJSON(res, { ok: true });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/locations' && method === 'GET') {
    try {
      const locations = db.prepare("SELECT * FROM google_locations").all();
      return sendJSON(res, locations);
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/dashboard-stats' && method === 'GET') {
    try {
      const loc = db.prepare("SELECT * FROM google_locations LIMIT 1").get();
      if (!loc) {
        return sendJSON(res, { connected: false });
      }
      
      const insights = db.prepare("SELECT SUM(search_views) as sv, SUM(maps_views) as mv FROM google_insights WHERE location_id = ?").get(loc.location_id);
      
      return sendJSON(res, {
        connected: true,
        businessName: loc.business_name,
        locationId: loc.location_id,
        rating: loc.rating,
        totalReviews: loc.total_reviews,
        completionScore: loc.completion_score,
        seoHealthScore: loc.seo_health_score,
        status: loc.status,
        address: loc.address,
        phone: loc.phone,
        website: loc.website,
        primaryCategory: loc.primary_category,
        weeklySearchViews: insights.sv || 0,
        weeklyMapsViews: insights.mv || 0
      });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/performance' && method === 'GET') {
    try {
      const data = db.prepare("SELECT * FROM google_insights ORDER BY metric_date ASC").all();
      return sendJSON(res, data);
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/profile' && method === 'GET') {
    try {
      const loc = db.prepare("SELECT * FROM google_locations LIMIT 1").get();
      return sendJSON(res, loc || {});
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/profile/save' && method === 'POST') {
    try {
      const body = await parseJSON(req);
      const { business_name, primary_category, address, phone, website, checkin_time, checkout_time, amenities } = body;
      
      db.prepare(`
        UPDATE google_locations SET
          business_name = ?, primary_category = ?, address = ?, phone = ?, website = ?
        WHERE location_id = 'locations/67890'
      `).run(business_name, primary_category, address, phone, website);

      const insertSetting = db.prepare('INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)');
      if (checkin_time !== undefined) insertSetting.run('gmb_checkin_time', checkin_time);
      if (checkout_time !== undefined) insertSetting.run('gmb_checkout_time', checkout_time);
      if (amenities !== undefined) insertSetting.run('gmb_amenities', amenities);

      return sendJSON(res, { ok: true });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/reviews' && method === 'GET') {
    try {
      const minRating = parseInt(query.rating, 10) || 0;
      const statusFilter = query.status || 'all';
      const search = query.search || '';

      let sql = "SELECT * FROM google_reviews WHERE rating >= ?";
      const params = [minRating];

      if (statusFilter === 'replied') {
        sql += " AND status = 'Replied'";
      } else if (statusFilter === 'unreplied') {
        sql += " AND status = 'Not Replied'";
      }

      if (search) {
        sql += " AND (reviewer_name LIKE ? OR comment LIKE ?)";
        params.push(`%${search}%`, `%${search}%`);
      }

      sql += " ORDER BY review_time DESC";
      const reviews = db.prepare(sql).all(...params);
      return sendJSON(res, reviews);
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/reviews/reply' && method === 'POST') {
    try {
      const { reviewId, replyText, assignedStaffId, isHandled } = await parseJSON(req);
      if (!reviewId) return sendError(res, 'Review ID is required', 400);

      if (replyText !== undefined) {
        const statusVal = replyText ? 'Replied' : 'Not Replied';
        const replyTime = replyText ? new Date().toISOString() : null;
        db.prepare("UPDATE google_reviews SET reply_text = ?, reply_time = ?, status = ?, is_handled = 1 WHERE review_id = ?")
          .run(replyText || null, replyTime, statusVal, reviewId);
      }

      if (assignedStaffId !== undefined) {
        db.prepare("UPDATE google_reviews SET assigned_staff_id = ? WHERE review_id = ?")
          .run(assignedStaffId || null, reviewId);
      }

      if (isHandled !== undefined) {
        db.prepare("UPDATE google_reviews SET is_handled = ? WHERE review_id = ?")
          .run(isHandled ? 1 : 0, reviewId);
      }

      return sendJSON(res, { ok: true });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/reviews/ai-suggest' && method === 'POST') {
    try {
      const { comment, rating } = await parseJSON(req);
      let suggestion = "";
      if (rating >= 4) {
        suggestion = `Dear Guest, thank you so much for your wonderful feedback and rating! We are thrilled to hear you enjoyed your stay at Shaima Hotel. We hope to serve you again very soon!`;
      } else if (rating === 3) {
        suggestion = `Dear Guest, thank you for your feedback. We appreciate your rating and have noted your suggestions. We are committed to improving our services for your next visit.`;
      } else {
        suggestion = `Dear Guest, we sincerely apologize for the inconvenience faced during your stay. We take your feedback very seriously and are investigating the issue to ensure it does not happen again.`;
      }

      return sendJSON(res, { suggestion });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/reviews/analytics' && method === 'GET') {
    try {
      const total = db.prepare("SELECT COUNT(*) as count FROM google_reviews").get().count;
      const replied = db.prepare("SELECT COUNT(*) as count FROM google_reviews WHERE status = 'Replied'").get().count;
      const ratings = db.prepare("SELECT rating, COUNT(*) as count FROM google_reviews GROUP BY rating").all();
      
      const distribution = { 1: 0, 2: 0, 3: 0, 4: 0, 5: 0 };
      ratings.forEach(r => distribution[r.rating] = r.count);

      return sendJSON(res, {
        total,
        replied,
        replyRate: total > 0 ? Math.round((replied / total) * 100) : 0,
        distribution
      });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/posts' && method === 'GET') {
    try {
      const posts = db.prepare("SELECT * FROM google_posts ORDER BY created_at DESC").all();
      return sendJSON(res, posts);
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/posts/save' && method === 'POST') {
    try {
      const body = await parseJSON(req);
      const { id, title, summary, post_type, image_url, call_to_action, action_url, start_date, end_date, coupon_code, terms_conditions, schedule_time, status } = body;
      if (!summary) return sendError(res, 'Summary text is required', 400);

      const pubTime = status === 'published' ? new Date().toISOString() : null;

      if (id) {
        db.prepare(`
          UPDATE google_posts SET
            title = ?, summary = ?, post_type = ?, image_url = ?, call_to_action = ?, action_url = ?, 
            start_date = ?, end_date = ?, coupon_code = ?, terms_conditions = ?, schedule_time = ?, status = ?, published_at = ?
          WHERE id = ?
        `).run(title || null, summary, post_type || 'whats_new', image_url || null, call_to_action || null, action_url || null, start_date || null, end_date || null, coupon_code || null, terms_conditions || null, schedule_time || null, status || 'draft', pubTime, id);
      } else {
        const pId = 'posts/' + Math.floor(Math.random() * 1000000);
        db.prepare(`
          INSERT INTO google_posts (
            post_id, location_id, title, summary, post_type, image_url, call_to_action, action_url, 
            start_date, end_date, coupon_code, terms_conditions, schedule_time, status, created_at, published_at
          ) VALUES (?, 'locations/67890', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        `).run(pId, title || null, summary, post_type || 'whats_new', image_url || null, call_to_action || null, action_url || null, start_date || null, end_date || null, coupon_code || null, terms_conditions || null, schedule_time || null, status || 'draft', new Date().toISOString(), pubTime);
      }

      return sendJSON(res, { ok: true });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/posts/delete' && method === 'POST') {
    try {
      const { id } = await parseJSON(req);
      if (!id) return sendError(res, 'Post ID is required', 400);
      db.prepare("DELETE FROM google_posts WHERE id = ?").run(id);
      return sendJSON(res, { ok: true });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/posts/trigger-pms' && method === 'POST') {
    try {
      const { summary, post_type, image_url, action_url } = await parseJSON(req);
      const pId = 'posts/' + Math.floor(Math.random() * 1000000);
      db.prepare(`
        INSERT INTO google_posts (
          post_id, location_id, summary, post_type, image_url, call_to_action, action_url, 
          status, created_at, published_at
        ) VALUES (?, 'locations/67890', ?, ?, ?, 'BOOK', ?, 'published', ?, ?)
      `).run(pId, summary, post_type || 'offer', image_url || null, action_url || null, new Date().toISOString(), new Date().toISOString());
      return sendJSON(res, { ok: true });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/seo-status' && method === 'GET') {
    try {
      const loc = db.prepare("SELECT * FROM google_locations LIMIT 1").get();
      const hotelName = getSetting('hotel_name', 'Shaima Hotel');
      const hotelPhone = getSetting('reception_phone', '');
      const hotelAddress = getSetting('hotel_address', '');

      let napConsistent = false;
      if (loc) {
        const cleanHotelName = hotelName.toLowerCase().replace(/[^a-z0-9]/g, '');
        const cleanGmbName = loc.business_name.toLowerCase().replace(/[^a-z0-9]/g, '');
        const nameMatch = cleanHotelName.includes(cleanGmbName) || cleanGmbName.includes(cleanHotelName);
        
        const cleanHotelPhone = hotelPhone.replace(/[^0-9]/g, '').slice(-10);
        const cleanGmbPhone = loc.phone.replace(/[^0-9]/g, '').slice(-10);
        const phoneMatch = cleanHotelPhone === cleanGmbPhone;

        napConsistent = nameMatch && phoneMatch;
      }

      return sendJSON(res, {
        napConsistent,
        pmsDetails: { name: hotelName, phone: hotelPhone, address: hotelAddress },
        gmbDetails: loc ? { name: loc.business_name, phone: loc.phone, address: loc.address } : null,
        citations: [
          { directory: 'Google Maps', status: loc ? 'Indexed' : 'Pending', url: loc ? loc.website : '#' },
          { directory: 'TripAdvisor', status: 'Indexed', url: '#' },
          { directory: 'JustDial', status: 'Indexed', url: '#' },
          { directory: 'Sulekha', status: 'Pending', url: '#' }
        ]
      });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/keywords' && method === 'GET') {
    try {
      const keywords = db.prepare("SELECT * FROM google_keywords ORDER BY search_volume DESC").all();
      return sendJSON(res, keywords);
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/keywords/save' && method === 'POST') {
    try {
      const { keyword, category, search_volume, competition, ranking_position, click_potential } = await parseJSON(req);
      if (!keyword) return sendError(res, 'Keyword is required', 400);

      db.prepare(`
        INSERT INTO google_keywords (location_id, keyword, category, search_volume, competition, ranking_position, click_potential)
        VALUES ('locations/67890', ?, ?, ?, ?, ?, ?)
      `).run(keyword, category || 'primary', search_volume || 0, competition || 'low', ranking_position || 0, click_potential || 0);

      return sendJSON(res, { ok: true });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/keywords/delete' && method === 'POST') {
    try {
      const { id } = await parseJSON(req);
      if (!id) return sendError(res, 'Keyword ID is required', 400);
      db.prepare("DELETE FROM google_keywords WHERE id = ?").run(id);
      return sendJSON(res, { ok: true });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/seo-suggest' && method === 'POST') {
    try {
      const { type } = await parseJSON(req);
      let suggestion = {};
      if (type === 'description') {
        suggestion = {
          title: 'Optimized Business Description',
          content: `Experience premium comfort at Shaima Hotel & Marina Residency, the premier budget-friendly luxury hotel in Chennai. Strategically located close to Marina Beach, our hotel offers Super Deluxe AC accommodations, 24/7 reception desk assistance, high-speed WiFi connectivity, and modern amenities. Perfect for business travelers and family holiday stays. Book online now for best tariff packages.`
        };
      } else if (type === 'faq') {
        suggestion = {
          title: 'GMB Local Search FAQs',
          content: `Q: What is the check-in and check-out time at Shaima Hotel Chennai?\nA: Standard check-in starts at 12:00 PM and check-out is until 11:00 AM. Early check-in and late checkout options are available on request.\n\nQ: Is parking available at the hotel?\nA: Yes, we provide free private parking on-site for all checked-in guests.`
        };
      } else {
        suggestion = {
          title: 'Meta Titles & Descriptions',
          content: `Meta Title: Shaima Hotel & Marina Residency | Hotel near Marina Beach Chennai\nMeta Description: Book your room stay at Shaima Hotel, Chennai. Close to Marina Beach. Enjoy Deluxe AC rooms, free WiFi, on-site parking, and excellent hospitality at the best tariff override rates.`
        };
      }

      return sendJSON(res, { suggestion });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/competitors' && method === 'GET') {
    try {
      const comps = db.prepare("SELECT * FROM google_competitors ORDER BY rating DESC").all();
      return sendJSON(res, comps);
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/competitors/save' && method === 'POST') {
    try {
      const { name, rating, total_reviews, primary_category, search_visibility } = await parseJSON(req);
      if (!name) return sendError(res, 'Competitor name is required', 400);

      db.prepare(`
        INSERT INTO google_competitors (location_id, name, rating, total_reviews, primary_category, search_visibility)
        VALUES ('locations/67890', ?, ?, ?, ?, ?)
      `).run(name, rating || 0, total_reviews || 0, primary_category || 'Hotel', search_visibility || 0);

      return sendJSON(res, { ok: true });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/competitors/delete' && method === 'POST') {
    try {
      const { id } = await parseJSON(req);
      if (!id) return sendError(res, 'Competitor ID is required', 400);
      db.prepare("DELETE FROM google_competitors WHERE id = ?").run(id);
      return sendJSON(res, { ok: true });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/photos' && method === 'GET') {
    try {
      const photos = [
        { id: 1, category: 'logo', url: '/uploads/hotel_logo_thumb.png', name: 'Hotel Logo' },
        { id: 2, category: 'cover', url: '/uploads/hotel_front_cover.jpg', name: 'Front Entrance Cover' },
        { id: 3, category: 'rooms', url: '/uploads/deluxe_room_gmb.jpg', name: 'Deluxe AC Room King Bed' },
        { id: 4, category: 'restaurant', url: '/uploads/restaurant_dining.jpg', name: 'Restaurant Buffet Area' }
      ];
      return sendJSON(res, photos);
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/photos/upload' && method === 'POST') {
    try {
      const { category, name, url } = await parseJSON(req);
      if (!url) return sendError(res, 'Photo URL is required', 400);
      return sendJSON(res, { ok: true, photo: { id: Date.now(), category: category || 'rooms', name: name || 'Room Photo', url } });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/google/photos/delete' && method === 'POST') {
    try {
      return sendJSON(res, { ok: true });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  // WhatsApp Connection Test API
  if (pathname === '/api/whatsapp/test-connection' && method === 'POST') {
    try {
      const { token, phoneId, wabaId } = await parseJSON(req);
      if (!token || !phoneId || !wabaId) {
        return sendError(res, 'Token, Phone ID, and WABA ID are required', 400);
      }

      const options = {
        hostname: 'graph.facebook.com',
        port: 443,
        path: `/v20.0/${wabaId}?access_token=${token}`,
        method: 'GET'
      };

      const verificationResult = await new Promise((resolve) => {
        const reqVerify = https.request(options, (resVerify) => {
          let body = '';
          resVerify.on('data', chunk => body += chunk);
          resVerify.on('end', () => {
            if (resVerify.statusCode === 200) {
              resolve({ ok: true });
            } else {
              try {
                const parsed = JSON.parse(body);
                if (parsed.error) {
                  if (parsed.error.code === 190) {
                    resolve({ ok: false, error: 'Token Expired' });
                  } else if (parsed.error.code === 100) {
                    resolve({ ok: false, error: 'WABA ID Not Found' });
                  } else {
                    resolve({ ok: false, error: parsed.error.message });
                  }
                } else {
                  resolve({ ok: false, error: `HTTP ${resVerify.statusCode}` });
                }
              } catch (e) {
                resolve({ ok: false, error: 'Invalid WABA response' });
              }
            }
          });
        });
        reqVerify.on('error', (err) => resolve({ ok: false, error: `Connection failed: ${err.message}` }));
        reqVerify.end();
      });

      if (!verificationResult.ok) {
        return sendJSON(res, { ok: false, status: verificationResult.error });
      }

      const phoneOptions = {
        hostname: 'graph.facebook.com',
        port: 443,
        path: `/v20.0/${phoneId}?access_token=${token}`,
        method: 'GET'
      };

      const phoneResult = await new Promise((resolve) => {
        const reqPhone = https.request(phoneOptions, (resPhone) => {
          let body = '';
          resPhone.on('data', chunk => body += chunk);
          resPhone.on('end', () => {
            if (resPhone.statusCode === 200) {
              resolve({ ok: true });
            } else {
              resolve({ ok: false, error: 'Phone Number Not Found' });
            }
          });
        });
        reqPhone.on('error', () => resolve({ ok: false, error: 'Phone verification failed' }));
        reqPhone.end();
      });

      if (!phoneResult.ok) {
        return sendJSON(res, { ok: false, status: phoneResult.error });
      }

      return sendJSON(res, { ok: true, status: 'Connected Successfully' });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  // WhatsApp Synchronization API
  if (pathname === '/api/whatsapp/sync' && method === 'POST') {
    try {
      const { wabaId, token } = await parseJSON(req);
      if (!wabaId || !token) {
        return sendError(res, 'WABA ID and Token are required', 400);
      }

      const options = {
        hostname: 'graph.facebook.com',
        port: 443,
        path: `/v20.0/${wabaId}/phone_numbers?access_token=${token}`,
        method: 'GET'
      };

      const syncResult = await new Promise((resolve, reject) => {
        const reqSync = https.request(options, (resSync) => {
          let body = '';
          resSync.on('data', chunk => body += chunk);
          resSync.on('end', () => {
            if (resSync.statusCode === 200) {
              try {
                resolve(JSON.parse(body));
              } catch (e) {
                reject(new Error('Failed to parse sync response'));
              }
            } else {
              reject(new Error(`Sync failed: HTTP ${resSync.statusCode} - ${body}`));
            }
          });
        });
        reqSync.on('error', reject);
        reqSync.end();
      });

      if (syncResult && syncResult.data) {
        const encrypted = encryptToken(token);
        const stmt = db.prepare(`
          INSERT INTO whatsapp_phone_numbers (
            phone_number_id, display_number, display_name, waba_id, access_token, status, messaging_tier, quality_rating
          ) VALUES (?, ?, ?, ?, ?, 'Connected', ?, ?)
          ON CONFLICT(phone_number_id) DO UPDATE SET
            display_number = excluded.display_number,
            display_name = excluded.display_name,
            waba_id = excluded.waba_id,
            access_token = excluded.access_token,
            status = 'Connected',
            messaging_tier = excluded.messaging_tier,
            quality_rating = excluded.quality_rating
        `);

        for (const num of syncResult.data) {
          stmt.run(
            num.id,
            num.display_phone_number,
            num.verified_name || 'Meta WhatsApp Number',
            wabaId,
            encrypted,
            num.throughput ? `Tier ${num.throughput.level}` : 'Tier 1',
            num.quality_rating || 'Green'
          );
        }
      }

      const tplOptions = {
        hostname: 'graph.facebook.com',
        port: 443,
        path: `/v20.0/${wabaId}/message_templates?access_token=${token}&limit=100`,
        method: 'GET'
      };

      const templatesResult = await new Promise((resolve) => {
        const reqTpl = https.request(tplOptions, (resTpl) => {
          let body = '';
          resTpl.on('data', chunk => body += chunk);
          resTpl.on('end', () => {
            if (resTpl.statusCode === 200) {
              try {
                resolve(JSON.parse(body));
              } catch (e) {
                resolve(null);
              }
            } else {
              resolve(null);
            }
          });
        });
        reqTpl.on('error', () => resolve(null));
        reqTpl.end();
      });

      if (templatesResult && templatesResult.data) {
        let templates = {};
        try {
          templates = JSON.parse(getSetting('whatsapp_templates', '{}'));
        } catch (e) {}

        templatesResult.data.forEach(tpl => {
          if (tpl.status === 'APPROVED') {
            const bodyComponent = tpl.components.find(c => c.type === 'BODY');
            const headerComponent = tpl.components.find(c => c.type === 'HEADER');
            const footerComponent = tpl.components.find(c => c.type === 'FOOTER');
            const buttonsComponent = tpl.components.find(c => c.type === 'BUTTONS');

            let bodyText = bodyComponent ? bodyComponent.text : '';
            let headerText = headerComponent && headerComponent.format === 'TEXT' ? headerComponent.text : '';
            let footerText = footerComponent ? footerComponent.text : '';
            
            let btnText = '';
            let btnUrl = '';
            if (buttonsComponent && buttonsComponent.buttons && buttonsComponent.buttons[0]) {
              const b = buttonsComponent.buttons[0];
              btnText = b.text;
              if (b.type === 'URL') btnUrl = b.url || '';
            }

            const key = tpl.name.toLowerCase();
            templates[key] = {
              meta_template_name: tpl.name,
              meta_language_code: tpl.language,
              body: bodyText,
              header_text: headerText,
              footer: footerText,
              btn_text: btnText,
              btn_url: btnUrl,
              status: tpl.status,
              category: tpl.category
            };
          }
        });

        const insertSetting = db.prepare('INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)');
        insertSetting.run('whatsapp_templates', JSON.stringify(templates));
      }

      return sendJSON(res, { ok: true, message: 'Sync completed successfully!' });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  // Multi-number endpoints
  if (pathname === '/api/whatsapp/numbers' && method === 'GET') {
    try {
      const numbers = db.prepare("SELECT * FROM whatsapp_phone_numbers ORDER BY id ASC").all();
      const sanitized = numbers.map(n => ({
        ...n,
        access_token: n.access_token ? '••••••••••••••••' : ''
      }));
      return sendJSON(res, sanitized);
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/whatsapp/numbers/save' && method === 'POST') {
    try {
      const body = await parseJSON(req);
      const { id, phone_number_id, display_number, display_name, waba_id, access_token, is_default, disabled } = body;
      
      if (!phone_number_id || !display_number || !waba_id) {
        return sendError(res, 'Missing required phone fields', 400);
      }

      let encryptedToken = '';
      if (access_token && access_token !== '••••••••••••••••') {
        encryptedToken = encryptToken(access_token);
      } else if (id) {
        const existing = db.prepare("SELECT access_token FROM whatsapp_phone_numbers WHERE id = ?").get(id);
        if (existing) encryptedToken = existing.access_token;
      }

      if (is_default === 1) {
        db.prepare("UPDATE whatsapp_phone_numbers SET is_default = 0").run();
      }

      if (id) {
        db.prepare(`
          UPDATE whatsapp_phone_numbers SET
            phone_number_id = ?, display_number = ?, display_name = ?, waba_id = ?, 
            access_token = ?, is_default = ?, disabled = ?
          WHERE id = ?
        `).run(phone_number_id, display_number, display_name || 'WhatsApp Number', waba_id, encryptedToken, is_default || 0, disabled || 0, id);
      } else {
        db.prepare(`
          INSERT INTO whatsapp_phone_numbers (
            phone_number_id, display_number, display_name, waba_id, access_token, is_default, disabled
          ) VALUES (?, ?, ?, ?, ?, ?, ?)
        `).run(phone_number_id, display_number, display_name || 'WhatsApp Number', waba_id, encryptedToken, is_default || 0, disabled || 0);
      }

      return sendJSON(res, { ok: true });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/whatsapp/numbers/delete' && method === 'POST') {
    try {
      const { id } = await parseJSON(req);
      if (!id) return sendError(res, 'Phone ID is required', 400);
      db.prepare("DELETE FROM whatsapp_phone_numbers WHERE id = ?").run(id);
      return sendJSON(res, { ok: true });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/whatsapp/numbers/set-default' && method === 'POST') {
    try {
      const { id } = await parseJSON(req);
      if (!id) return sendError(res, 'Phone ID is required', 400);
      db.prepare("UPDATE whatsapp_phone_numbers SET is_default = 0").run();
      db.prepare("UPDATE whatsapp_phone_numbers SET is_default = 1 WHERE id = ?").run(id);
      
      const row = db.prepare("SELECT phone_number_id, access_token FROM whatsapp_phone_numbers WHERE id = ?").get(id);
      if (row) {
        const insertSetting = db.prepare('INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)');
        insertSetting.run('whatsapp_cloud_phone_id', row.phone_number_id);
        insertSetting.run('whatsapp_cloud_token', row.access_token);
      }
      
      return sendJSON(res, { ok: true });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  // Automation map endpoints
  if (pathname === '/api/whatsapp/automation-map' && method === 'GET') {
    try {
      const mappings = db.prepare("SELECT * FROM whatsapp_automation_map").all();
      return sendJSON(res, mappings);
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/whatsapp/automation-map/save' && method === 'POST') {
    try {
      const body = await parseJSON(req);
      const { mappings } = body;
      if (!Array.isArray(mappings)) {
        return sendError(res, 'Mappings list must be an array', 400);
      }

      const stmt = db.prepare("INSERT OR REPLACE INTO whatsapp_automation_map (event_key, template_name, enabled) VALUES (?, ?, ?)");
      const transaction = db.transaction(() => {
        for (const map of mappings) {
          stmt.run(map.event_key, map.template_name || '', map.enabled ? 1 : 0);
        }
      });
      transaction();

      return sendJSON(res, { ok: true });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  // WhatsApp chat-history endpoint
  if (pathname === '/api/whatsapp/chat-history' && method === 'GET') {
    const guestId = parseInt(query.guestId, 10);
    if (!guestId) {
      return sendError(res, 'Guest ID is required', 400);
    }
    try {
      let messages = db.prepare("SELECT * FROM whatsapp_messages WHERE guest_id = ? ORDER BY timestamp ASC").all(guestId);
      if (messages.length === 0) {
        const guestObj = db.prepare("SELECT * FROM guests WHERE id = ?").get(guestId);
        if (guestObj) {
          const stmt = db.prepare(`
            INSERT INTO whatsapp_messages (wamid, guest_id, direction, sender_type, body, media_type, status, timestamp)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
          `);
          const now = Date.now();
          stmt.run(`seed_1_${guestId}`, guestId, 'incoming', 'guest', `Hello, this is ${guestObj.name}. Do you have WiFi available?`, 'text', 'read', new Date(now - 600000).toISOString());
          stmt.run(`seed_2_${guestId}`, guestId, 'outgoing', 'bot', 'Hello! Yes, WiFi is available throughout the hotel. The password is Shaima@2026.', 'text', 'sent', new Date(now - 500000).toISOString());
          stmt.run(`seed_3_${guestId}`, guestId, 'incoming', 'guest', 'Thanks! What is the reception extension number?', 'text', 'read', new Date(now - 400000).toISOString());
          
          messages = db.prepare("SELECT * FROM whatsapp_messages WHERE guest_id = ? ORDER BY timestamp ASC").all(guestId);
        }
      }
      const mapped = messages.map(m => ({
        id: m.id,
        wamid: m.wamid,
        body: m.body,
        direction: m.direction,
        sender_type: m.sender_type,
        media_type: m.media_type,
        media_url: m.media_url,
        status: m.status,
        timestamp: m.timestamp
      }));
      return sendJSON(res, mapped);
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  // WhatsApp templates manager endpoints
  if (pathname === '/api/whatsapp/templates' && method === 'GET') {
    let templates = {};
    try {
      templates = JSON.parse(getSetting('whatsapp_templates', '{}'));
    } catch (e) {}
    return sendJSON(res, templates);
  }

  if (pathname === '/api/whatsapp/templates/save' && method === 'POST') {
    try {
      const { key, templateData, userId } = await parseJSON(req);
      if (!key || !templateData) {
        return sendError(res, 'Missing template key or data', 400);
      }
      
      let templates = {};
      try {
        templates = JSON.parse(getSetting('whatsapp_templates', '{}'));
      } catch (e) {}

      templates[key.toLowerCase()] = {
        meta_template_name: templateData.meta_template_name || '',
        meta_language_code: templateData.meta_language_code || 'en_US',
        category: templateData.category || 'UTILITY',
        header_type: templateData.header_type || 'None',
        header_text: templateData.header_text || '',
        image: templateData.image || '',
        body: templateData.body || '',
        footer: templateData.footer || '',
        btn_type: templateData.btn_type || 'None',
        btn_text: templateData.btn_text || '',
        btn_url: templateData.btn_url || ''
      };

      db.prepare('INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)')
        .run('whatsapp_templates', JSON.stringify(templates));

      logAudit(userId, 'Save WhatsApp Template', `Saved template ${key}`);
      return sendJSON(res, { ok: true, templates });
    } catch (err) {
      return sendError(res, err.message, 500);
    }
  }

  if (pathname === '/api/whatsapp/templates/delete' && method === 'POST') {
    try {
      const { key, userId } = await parseJSON(req);
      if (!key) {
        return sendError(res, 'Missing template key', 400);
      }
      
      let templates = {};
      try {
        templates = JSON.parse(getSetting('whatsapp_templates', '{}'));
      } catch (e) {}

      delete templates[key.toLowerCase()];

      db.prepare('INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)')
        .run('whatsapp_templates', JSON.stringify(templates));

      logAudit(userId, 'Delete WhatsApp Template', `Deleted template ${key}`);
      return sendJSON(res, { ok: true, templates });
    } catch (err) {
      return sendError(res, err.message, 500);
    }
  }

  if (pathname === '/api/whatsapp/templates/sync' && method === 'POST') {
    try {
      const { userId } = await parseJSON(req);
      const token = getSetting('whatsapp_cloud_token', '');
      const wabaId = getSetting('whatsapp_cloud_waba_id', '');

      if (!token || !wabaId) {
        return sendError(res, 'Meta Access Token or WABA ID is missing. Configure settings first.', 400);
      }

      const options = {
        hostname: 'graph.facebook.com',
        port: 443,
        path: `/v20.0/${wabaId}/message_templates`,
        method: 'GET',
        headers: {
          'Authorization': `Bearer ${token}`
        }
      };

      const syncReq = https.request(options, (apiRes) => {
        let body = '';
        apiRes.on('data', chunk => body += chunk);
        apiRes.on('end', () => {
          try {
            let parsed;
            try {
              parsed = JSON.parse(body);
            } catch (jsonErr) {
              return sendError(res, `Invalid JSON response from Meta. (HTTP Status ${apiRes.statusCode})`, 500);
            }

            if (parsed.error) {
              const errDetails = parsed.error.message || JSON.stringify(parsed.error);
              return sendError(res, `Meta API Error: ${errDetails}`, 400);
            }

            if (apiRes.statusCode < 200 || apiRes.statusCode >= 300) {
              return sendError(res, `Meta Sync Error (status ${apiRes.statusCode}): ${body}`, apiRes.statusCode);
            }

            if (!parsed.data) {
              return sendJSON(res, { ok: true, syncedCount: 0, templates: {} });
            }

            let templates = {};
            try {
              templates = JSON.parse(getSetting('whatsapp_templates', '{}'));
            } catch (e) {}

            let count = 0;
            parsed.data.forEach(tpl => {
              if (!tpl.components) return;
              const key = tpl.name.toLowerCase();
              let image = '';
              let bodyText = '';
              let footerText = '';
              let btnText = '';
              let btnUrl = '';

              tpl.components.forEach(comp => {
                if (comp.type === 'HEADER' && comp.format === 'IMAGE') {
                  image = 'https://domain.com/placeholder-header.jpg';
                }
                if (comp.type === 'BODY') {
                  bodyText = comp.text || '';
                }
                if (comp.type === 'FOOTER') {
                  footerText = comp.text || '';
                }
                if (comp.type === 'BUTTONS' && comp.buttons) {
                  const urlBtn = comp.buttons.find(b => b.type === 'URL');
                  if (urlBtn) {
                    btnText = urlBtn.text;
                    btnUrl = urlBtn.url || '';
                  }
                }
              });

              // Translate double curly brace ordinal parameters to friendly local curly variables
              const friendlyBodyText = translateMetaPlaceholders(bodyText, key);

              templates[key] = {
                meta_template_name: tpl.name,
                meta_language_code: tpl.language,
                image: image || (templates[key] ? templates[key].image : ''),
                body: friendlyBodyText,
                footer: footerText,
                btn_text: btnText,
                btn_url: btnUrl
              };
              count++;
            });

            db.prepare('INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)')
              .run('whatsapp_templates', JSON.stringify(templates));

            logAudit(userId, 'Sync WhatsApp Templates', `Successfully synced ${count} templates from Meta`);
            return sendJSON(res, { ok: true, syncedCount: count, templates });

          } catch (syncErr) {
            return sendError(res, `Failed to process templates: ${syncErr.message}`, 500);
          }
        });
      });

      syncReq.on('error', (e) => {
        return sendError(res, `Connection to Meta Graph failed: ${e.message}`, 500);
      });
      syncReq.end();

    } catch (e) {
      return sendError(res, `Sync internal failure: ${e.message}`, 500);
    }
  }

  if (pathname === '/api/whatsapp/templates/send-manual' && method === 'POST') {
    try {
      const { phone, templateKey, placeholders, userId } = await parseJSON(req);
      if (!phone || !templateKey || !placeholders) {
        return sendError(res, 'Missing parameters phone, templateKey, or placeholders', 400);
      }

      // Compile the message locally
      compileTemplateMessage(templateKey, placeholders, phone);
      logAudit(userId, 'Manual Send WhatsApp Template', `Dispatched template ${templateKey} to ${phone}`);
      return sendJSON(res, { ok: true, message: `Template ${templateKey} shared successfully!` });
    } catch (err) {
      return sendError(res, err.message, 500);
    }
  }

  if (pathname === '/api/whatsapp/test' && method === 'POST') {
    try {
      const body = await parseJSON(req);
      const { provider, metaToken, metaPhoneId, phone, message } = body;

      let cleanPhone = phone.replace(/[^0-9]/g, '');
      if (cleanPhone.length === 10) {
        cleanPhone = '91' + cleanPhone;
      }

      // Log to local simulator logs
      const logItem = logMockWhatsApp(phone, message);

      if (provider === 'WhatsAppCloudAPI') {
        if (!metaToken || !metaPhoneId) {
          return sendError(res, 'Meta Cloud API configuration is incomplete.', 400);
        }

        const postData = JSON.stringify({
          messaging_product: 'whatsapp',
          recipient_type: 'individual',
          to: cleanPhone,
          type: 'text',
          text: { body: message }
        });

        const options = {
          hostname: 'graph.facebook.com',
          port: 443,
          path: `/v20.0/${metaPhoneId}/messages`,
          method: 'POST',
          headers: {
            'Authorization': `Bearer ${metaToken}`,
            'Content-Type': 'application/json',
            'Content-Length': Buffer.byteLength(postData)
          }
        };

        const metaReq = https.request(options, (apiRes) => {
          let responseBody = '';

          apiRes.on('error', (e) => {
            console.error('Meta response stream error:', e);
          });

          apiRes.on('data', chunk => responseBody += chunk);
          apiRes.on('end', () => {
            try {
              if (apiRes.statusCode >= 200 && apiRes.statusCode < 300) {
                let parsed = {};
                try {
                  parsed = JSON.parse(responseBody);
                  if (parsed && parsed.messages && parsed.messages[0]) {
                    const wamid = parsed.messages[0].id;
                    logItem.id = wamid;
                  }
                } catch (e) {
                  parsed = { raw: responseBody };
                }
                return sendJSON(res, { ok: true, statusCode: apiRes.statusCode, data: parsed });
              } else {
                let errorMessage = responseBody;
                try {
                  const parsedError = JSON.parse(responseBody);
                  if (parsedError.error && parsedError.error.message) {
                    errorMessage = `${parsedError.error.message} (Meta Code: ${parsedError.error.code || 'N/A'})`;
                  }
                } catch (e) {}
                logItem.status = 'Failed';
                logItem.error = errorMessage || 'Undelivered';
                return sendError(res, `Meta API Error: ${errorMessage}`, apiRes.statusCode);
              }
            } catch (err) {
              return sendError(res, `Error processing Meta response: ${err.message}`, 500);
            }
          });
        });

        metaReq.on('error', (e) => {
          logItem.status = 'Failed';
          logItem.error = e.message;
          return sendError(res, `Connection Error: ${e.message}`, 500);
        });

        metaReq.write(postData);
        metaReq.end();
        return;

      } else {
        // Local simulator only
        return sendJSON(res, { ok: true, statusCode: 200, message: 'Local simulator dispatch complete.', data: logItem });
      }
    } catch (err) {
      return sendError(res, `Server error: ${err.message}`, 500);
    }
  }

  if (pathname === '/api/whatsapp/templates/register-meta' && method === 'POST') {
    try {
      const body = await parseJSON(req);
      const {
        name,
        category,
        lang,
        type,
        headerType,
        headerText,
        body: bodyText,
        footer,
        btnType,
        btnText,
        btnVal,
        token,
        wabaId,
        userId
      } = body;

      if (!token || !wabaId) {
        return sendError(res, 'Access Token and WABA ID are required.', 400);
      }

      // Build components payload
      const components = [];

      // Add Header
      if (headerType && headerType !== 'NONE') {
        if (headerType === 'TEXT') {
          const headerComp = {
            type: 'HEADER',
            format: 'TEXT',
            text: headerText
          };
          // Extract variables (e.g. {{1}}) and add header examples
          const hMatches = headerText.match(/{{(\d+)}}/g);
          if (hMatches && hMatches.length > 0) {
            headerComp.example = {
              header_text: Array.from({ length: hMatches.length }, (_, i) => `HeaderVal${i + 1}`)
            };
          }
          components.push(headerComp);
        } else {
          components.push({
            type: 'HEADER',
            format: headerType, // IMAGE, DOCUMENT, VIDEO
            example: {
              header_handle: [ headerText ]
            }
          });
        }
      }

      // Add Body (Required)
      const bodyComp = {
        type: 'BODY',
        text: bodyText
      };
      // Extract variables (e.g. {{1}}) and add body examples in 2D array format: [ [ "val1", "val2" ] ]
      const bMatches = bodyText.match(/{{(\d+)}}/g);
      if (bMatches && bMatches.length > 0) {
        bodyComp.example = {
          body_text: [
            Array.from({ length: bMatches.length }, (_, i) => `ParamVal${i + 1}`)
          ]
        };
      }
      components.push(bodyComp);

      // Add Footer
      if (footer) {
        components.push({
          type: 'FOOTER',
          text: footer
        });
      }

      // Add Buttons
      if (btnType && btnType !== 'NONE') {
        let metaBtnType = btnType;
        if (btnType === 'PHONE') {
          metaBtnType = 'PHONE_NUMBER';
        }

        const buttonObj = {
          type: metaBtnType,
          text: btnText
        };
        
        if (metaBtnType === 'URL') {
          buttonObj.url = btnVal;
          if (btnVal.includes('{{1}}')) {
            buttonObj.example = [ "https://example.com/invoice/INV-1001" ];
          }
        } else if (metaBtnType === 'PHONE_NUMBER') {
          buttonObj.phone_number = btnVal;
        }

        components.push({
          type: 'BUTTONS',
          buttons: [ buttonObj ]
        });
      }

      const postData = JSON.stringify({
        name,
        category,
        language: lang,
        components
      });

      const options = {
        hostname: 'graph.facebook.com',
        port: 443,
        path: `/v20.0/${wabaId}/message_templates`,
        method: 'POST',
        headers: {
          'Authorization': `Bearer ${token}`,
          'Content-Type': 'application/json',
          'Content-Length': Buffer.byteLength(postData)
        }
      };

      const metaReq = https.request(options, (apiRes) => {
        let responseBody = '';
        apiRes.on('data', chunk => responseBody += chunk);
        apiRes.on('end', () => {
          try {
            const parsed = JSON.parse(responseBody);
            if (apiRes.statusCode >= 200 && apiRes.statusCode < 300) {
              logAudit(userId, 'Create Meta Template', `Official WhatsApp template "${name}" registered`);
              return sendJSON(res, { ok: true, data: parsed });
            } else {
              const errMsg = parsed.error ? parsed.error.message : responseBody;
              return sendError(res, errMsg || `Meta Error (Status ${apiRes.statusCode})`, apiRes.statusCode);
            }
          } catch (e) {
            return sendError(res, `Failed to parse Meta response: ${e.message}`, 500);
          }
        });
      });

      metaReq.on('error', (e) => {
        return sendError(res, `Connection Error: ${e.message}`, 500);
      });

      metaReq.write(postData);
      metaReq.end();
      return;

    } catch (err) {
      return sendError(res, `Server error: ${err.message}`, 500);
    }
  }

  // Backup and Restore
  if (pathname === '/api/settings/backup' && method === 'POST') {
    const { userId } = await parseJSON(req);
    const backupDir = getSetting('backup_dir', path.join(__dirname, 'backups'));
    
    if (!fs.existsSync(backupDir)) {
      fs.mkdirSync(backupDir, { recursive: true });
    }

    const timestamp = new Date().toISOString().replace(/[:.]/g, '-');
    const filename = `hotel_backup_${timestamp}.db`;
    const backupFile = path.join(backupDir, filename);

    try {
      fs.copyFileSync(dbPath, backupFile);
      logAudit(userId, 'Database Backup', `Backup created locally: ${filename}`);

      // Google Drive upload if configured
      const saJson = getSetting('google_drive_sa_key', '');
      const folderId = getSetting('google_drive_folder_id', '');
      let googleDriveSuccess = false;
      let googleDriveError = '';

      if (saJson && folderId) {
        try {
          const token = await getGoogleAccessToken(saJson);
          await uploadFileToGoogleDrive(token, backupFile, filename, folderId);
          logAudit(userId, 'Google Drive Backup Success', `Uploaded backup file: ${filename}`);
          googleDriveSuccess = true;
        } catch (err) {
          googleDriveError = err.message;
          logAudit(userId, 'Google Drive Backup Failed', err.message);
        }
      }

      return sendJSON(res, { 
        ok: true, 
        file: backupFile, 
        googleDrive: saJson && folderId ? (googleDriveSuccess ? 'Success' : 'Failed') : 'Not Configured',
        googleDriveError
      });
    } catch (e) {
      return sendError(res, `Backup failed: ${e.message}`, 500);
    }
  }

  // Google Drive Connection Test API
  if (pathname === '/api/settings/google-drive-test' && method === 'POST') {
    try {
      const { saKey, folderId, userId } = await parseJSON(req);
      if (!saKey || !folderId) {
        return sendError(res, 'Google Service Account JSON Key and Folder ID are required', 400);
      }
      const token = await getGoogleAccessToken(saKey);
      const testFilePath = path.join(__dirname, 'backups', 'gdrive_test.txt');
      if (!fs.existsSync(path.join(__dirname, 'backups'))) {
        fs.mkdirSync(path.join(__dirname, 'backups'), { recursive: true });
      }
      fs.writeFileSync(testFilePath, 'Google Drive API Connection Test - Success!');
      const result = await uploadFileToGoogleDrive(token, testFilePath, 'gdrive_test.txt', folderId);
      fs.unlinkSync(testFilePath);
      logAudit(userId, 'Google Drive Test Success', `Uploaded test file ID: ${result.id}`);
      return sendJSON(res, { ok: true, fileId: result.id });
    } catch (err) {
      return sendError(res, `Google Drive Connection Test failed: ${err.message}`, 500);
    }
  }

  // Email logs history timeline lookup
  if (pathname === '/api/email/logs' && method === 'GET') {
    const guestId = parseInt(query.guestId, 10);
    if (!guestId) {
      return sendError(res, 'Guest ID is required', 400);
    }
    try {
      const logs = db.prepare("SELECT * FROM email_logs WHERE guest_id = ? ORDER BY timestamp DESC").all(guestId);
      return sendJSON(res, logs);
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  // SMTP Connection Test API
  if (pathname === '/api/settings/smtp-test' && method === 'POST') {
    try {
      const { host, port, secure, user, pass, from, userId } = await parseJSON(req);
      if (!host || !user || !pass) {
        return sendError(res, 'SMTP Host, Username, and Password are required', 400);
      }

      const transporter = nodemailer.createTransport({
        host,
        port: parseInt(port, 10),
        secure: secure === true || secure === 'true',
        auth: {
          user,
          pass
        }
      });

      // Verify connection configuration
      await transporter.verify();

      // Send a test email to the user themselves
      await transporter.sendMail({
        from: from || `Shaima Hotel <${user}>`,
        to: user,
        subject: 'SMTP Connection Test - Success!',
        text: 'This is a test email from Shaima Hotel Management software to confirm your SMTP configuration works perfectly.',
        html: '<p>This is a test email from <strong>Shaima Hotel Management</strong> software to confirm your SMTP configuration works perfectly.</p>'
      });

      logAudit(userId, 'SMTP Connection Test Success', `Verified host: ${host}`);
      return sendJSON(res, { ok: true, message: 'SMTP connection verified and test email sent successfully!' });
    } catch (err) {
      return sendError(res, `SMTP Connection Test failed: ${err.message}`, 500);
    }
  }

  if (pathname === '/api/settings/backups' && method === 'GET') {
    try {
      const backupDir = getSetting('backup_dir', path.join(__dirname, 'backups'));
      if (!fs.existsSync(backupDir)) {
        fs.mkdirSync(backupDir, { recursive: true });
      }
      const files = fs.readdirSync(backupDir);
      const backups = files
        .filter(f => f.startsWith('hotel_backup_') && f.endsWith('.db'))
        .map(f => {
          const filePath = path.join(backupDir, f);
          const stats = fs.statSync(filePath);
          return {
            filename: f,
            filepath: filePath,
            size: stats.size,
            created_at: stats.mtime.toISOString()
          };
        })
        .sort((a, b) => b.created_at.localeCompare(a.created_at));
      return sendJSON(res, backups);
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  if (pathname === '/api/settings/restore' && method === 'POST') {
    const { backupFile, userId } = await parseJSON(req);
    if (!fs.existsSync(backupFile)) {
      return sendError(res, 'Backup file does not exist', 404);
    }

    try {
      // Close db and restore
      db.close();
      fs.copyFileSync(backupFile, dbPath);
      // Re-initialize DatabaseSync connection
      // Wait, we need to restart the connection.
      process.exit(0); // Exit process and let C# launcher restart it immediately!
      // This is the cleanest restore on a single user app, because launcher will restart the server and load the restored database file!
    } catch (e) {
      return sendError(res, `Restore failed: ${e.message}`, 500);
    }
  }

  // Master Reset API
  if (pathname === '/api/settings/reset' && method === 'POST') {
    const { password, userId } = await parseJSON(req);
    if (password !== 'Shaima@#8870') {
      return sendError(res, 'Incorrect master reset security password!', 403);
    }

    try {
      // Close database connection first
      db.close();
      
      // Delete the SQLite database file
      if (fs.existsSync(dbPath)) {
        fs.unlinkSync(dbPath);
      }

      // Recreate database on reboot: standard Node exit. The launcher catches it and restarts Node in 1 second.
      // Database initialization script runs on launch, sees no database, and generates clean seeds.
      sendJSON(res, { ok: true, message: 'Master reset complete. Re-initializing factory state...' });
      
      setTimeout(() => {
        process.exit(0);
      }, 500);
      return;
    } catch (e) {
      return sendError(res, `Master reset process failed: ${e.message}`, 500);
    }
  }

  // Factory Reset API (Delete transactional tables)
  if (pathname === '/api/settings/factory-reset' && method === 'POST') {
    const { password, userId } = await parseJSON(req);
    if (password !== 'Shaima@#8870') {
      return sendError(res, 'Incorrect master reset security password!', 403);
    }
    try {
      db.exec('PRAGMA foreign_keys = OFF;');
      db.exec('DELETE FROM bookings;');
      db.exec('DELETE FROM booking_rooms;');
      db.exec('DELETE FROM guests;');
      db.exec('DELETE FROM housekeeping;');
      db.exec('DELETE FROM lost_found;');
      db.exec('DELETE FROM attendance;');
      db.exec('DELETE FROM salaries;');
      db.exec('DELETE FROM ota_bookings;');
      db.exec('DELETE FROM customer_reviews;');
      db.exec('DELETE FROM audit_logs;');
      
      // Reset room statuses to 'Vacant'
      db.exec("UPDATE rooms SET status = 'Vacant';");
      db.exec('PRAGMA foreign_keys = ON;');

      logAudit(userId, 'Factory Reset', 'Database reset to fresh factory state (all transactions deleted)');
      return sendJSON(res, { ok: true, message: 'Database successfully cleared. Ready for fresh production setup!' });
    } catch (e) {
      return sendError(res, `Factory reset failed: ${e.message}`, 500);
    }
  }

  // Seed Demo Data API
  if (pathname === '/api/settings/seed-demo' && method === 'POST') {
    const { userId } = await parseJSON(req);
    try {
      const bookingCount = db.prepare('SELECT COUNT(*) as count FROM bookings').get().count;
      if (bookingCount > 0) {
        return sendError(res, 'Demo data can only be loaded into an empty database. Please perform a Factory Reset first.', 400);
      }

      db.exec('PRAGMA foreign_keys = OFF;');

      // 1. Seed demo guest Sarah Jenkins
      const insertGuest = db.prepare(`
        INSERT INTO guests (name, mobile, email, doc_type, doc_number, address, loyalty_points) 
        VALUES (?, ?, ?, ?, ?, ?, ?)
      `);
      insertGuest.run('Sarah Jenkins', '+919876543211', 'sarah@demo.com', 'Passport', 'P9876543', 'London, UK', 150);
      const sarahId = db.prepare("SELECT id FROM guests WHERE mobile = '+919876543211'").get().id;

      // 2. Seed a demo booking for Sarah
      const insertBooking = db.prepare(`
        INSERT INTO bookings (booking_uid, guest_id, check_in, check_out, status, total_amount, paid_amount, gst_amount, created_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
      `);
      
      const today = new Date().toISOString().split('T')[0];
      const tomorrow = new Date(Date.now() + 86400000).toISOString().split('T')[0];
      const checkinDate = today;
      const checkoutDate = tomorrow;

      insertBooking.run('BK-DEMO-1001', sarahId, checkinDate, checkoutDate, 'Checked-In', 4200, 4200, 200, today + 'T10:00:00.000Z');
      const bookingId = db.prepare("SELECT id FROM bookings WHERE booking_uid = 'BK-DEMO-1001'").get().id;

      // Map Room 101 to the booking
      const room101 = db.prepare("SELECT id FROM rooms WHERE room_number = '101'").get();
      if (room101) {
        db.prepare('INSERT INTO booking_rooms (booking_id, room_id, price_per_night) VALUES (?, ?, ?)').run(bookingId, room101.id, 4000);
        db.prepare("UPDATE rooms SET status = 'Checked-In' WHERE id = ?").run(room101.id);
      }

      // 3. Seed demo Reviews
      const insertReview = db.prepare('INSERT INTO customer_reviews (source, guest_name, rating, comment, reply, timestamp) VALUES (?, ?, ?, ?, ?, ?)');
      insertReview.run('Google', 'Aditya Verma', 5, 'Amazing hospitality! The Deluxe AC room was very clean and front desk staff was helpful.', null, new Date(Date.now() - 3600000 * 3).toISOString());
      insertReview.run('TripAdvisor', 'Emily Watson', 4, 'Nice sea view suites and good room service.', 'Thank you Emily!', new Date(Date.now() - 3600000 * 24).toISOString());

      // 4. Seed demo OTA Bookings
      const insertOTA = db.prepare('INSERT INTO ota_bookings (channel, ota_booking_uid, guest_name, guest_mobile, check_in, check_out, room_type_id, amount, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)');
      const tDeluxe = db.prepare("SELECT id FROM room_types WHERE name = 'Deluxe AC'").get().id;
      insertOTA.run('Booking.com', 'OTA-B93721', 'Sarah Jenkins', '+919876543211', checkinDate, checkoutDate, tDeluxe, 4200, 'Pending');

      db.exec('PRAGMA foreign_keys = ON;');

      logAudit(userId, 'Seed Demo Data', 'Demo mock data seeded successfully');
      return sendJSON(res, { ok: true, message: 'Demo data loaded successfully! Reloading your dashboard...' });
    } catch (e) {
      return sendError(res, `Failed to seed demo data: ${e.message}`, 500);
    }
  }

  // Audit Logs API
  if (pathname === '/api/audit-logs' && method === 'GET') {
    const logs = db.prepare(`
      SELECT al.*, u.name as user_name, u.role
      FROM audit_logs al
      LEFT JOIN users u ON al.user_id = u.id
      ORDER BY al.timestamp DESC
      LIMIT 100
    `).all();
    return sendJSON(res, logs);
  }

  // Reports
  if (pathname === '/api/reports' && method === 'GET') {
    const type = query.type;
    const startDate = query.startDate; // YYYY-MM-DD
    const endDate = query.endDate; // YYYY-MM-DD

    if (type === 'daily') {
      // Daily Report: booking counts, checkout counts, revenue, cash collected
      const bookingsCreated = db.prepare(`
        SELECT COUNT(*) as count, SUM(total_amount) as total FROM bookings 
        WHERE created_at BETWEEN ? AND ? AND status != 'Cancelled'
      `).get(startDate, endDate);

      const checkouts = db.prepare(`
        SELECT COUNT(*) as count, SUM(paid_amount) as total FROM bookings 
        WHERE status = 'Checked-Out' AND created_at BETWEEN ? AND ?
      `).get(startDate, endDate);

      return sendJSON(res, {
        bookingsCreated: bookingsCreated.count || 0,
        bookingsValue: bookingsCreated.total || 0,
        checkoutsCount: checkouts.count || 0,
        revenueCollected: checkouts.total || 0
      });
    }

    if (type === 'occupancy') {
      // Occupancy Report: lists rooms and how many nights they were occupied in range
      // Let's get total days in range
      const diffTime = Math.abs(new Date(endDate) - new Date(startDate));
      const totalDays = Math.max(1, Math.ceil(diffTime / (1000 * 60 * 60 * 24)));

      const rooms = db.prepare('SELECT id, room_number FROM rooms').all();
      const report = [];

      rooms.forEach(r => {
        // Find bookings in range for this room
        const occupiedDaysRow = db.prepare(`
          SELECT SUM(
            CASE 
              WHEN b.check_in <= ? AND b.check_out >= ? THEN ? -- Booking covers whole range
              WHEN b.check_in >= ? AND b.check_out <= ? THEN 
                (strftime('%s', b.check_out) - strftime('%s', b.check_in)) / 86400 -- Booking inside range
              WHEN b.check_in < ? AND b.check_out > ? AND b.check_out <= ? THEN 
                (strftime('%s', b.check_out) - strftime('%s', ?)) / 86400 -- Starts before, ends inside
              WHEN b.check_in >= ? AND b.check_in < ? AND b.check_out > ? THEN 
                (strftime('%s', ?) - strftime('%s', b.check_in)) / 86400 -- Starts inside, ends after
              ELSE 0
            END
          ) as occupied_days
          FROM bookings b
          JOIN booking_rooms br ON b.id = br.booking_id
          WHERE br.room_id = ? AND b.status IN ('Checked-In', 'Checked-Out')
        `).get(startDate, endDate, totalDays, startDate, endDate, startDate, startDate, endDate, startDate, startDate, endDate, endDate, endDate, r.id);

        const occupiedDays = Math.min(totalDays, Math.max(0, Math.round(occupiedDaysRow.occupied_days || 0)));
        const rate = Math.round((occupiedDays / totalDays) * 100);

        report.push({
          roomNumber: r.room_number,
          occupiedDays,
          totalDays,
          rate
        });
      });

      return sendJSON(res, report);
    }

    if (type === 'revenue') {
      // Revenue report aggregated daily
      const revenueData = db.prepare(`
        SELECT created_at as date, SUM(paid_amount) as amount
        FROM bookings
        WHERE status = 'Checked-Out' AND created_at BETWEEN ? AND ?
        GROUP BY created_at
        ORDER BY created_at
      `).all(startDate, endDate);
      return sendJSON(res, revenueData);
    }

    if (type === 'gst') {
      // GST Report showing tax breakdown
      const gstList = db.prepare(`
        SELECT invoice_number, created_at as date, total_amount, paid_amount, gst_amount, status
        FROM bookings
        WHERE invoice_number IS NOT NULL AND created_at BETWEEN ? AND ?
      `).all(startDate, endDate);

      let totalGST = 0, totalTaxable = 0, totalGross = 0;
      const formatted = gstList.map(item => {
        const gross = item.total_amount;
        const tax = item.gst_amount;
        const taxable = gross - tax;

        totalGST += tax;
        totalTaxable += taxable;
        totalGross += gross;

        return {
          invoice: item.invoice_number,
          date: item.date,
          taxable,
          cgst: tax / 2, // split GST
          sgst: tax / 2,
          igst: tax,
          gst: tax,
          gross
        };
      });

      return sendJSON(res, {
        details: formatted,
        summary: { totalTaxable, totalGST, totalGross }
      });
    }

    if (type === 'yearly-tax') {
      try {
        const salesRow = db.prepare("SELECT COUNT(*) as count, SUM(total_amount) as total, SUM(gst_amount) as gst FROM bookings WHERE status = 'Checked-Out' AND created_at BETWEEN ? AND ?").get(startDate, endDate);
        const grossSales = salesRow.total || 0;
        const gstCollected = salesRow.gst || 0;
        const taxableSales = grossSales - gstCollected;

        const salariesRow = db.prepare("SELECT SUM(base_salary + bonus - deductions) as total FROM salaries WHERE paid_date BETWEEN ? AND ?").get(startDate, endDate);
        const salariesPaid = salariesRow.total || 0;

        const stockExpenses = 18500; // Standard Seeded Inventory / Stock Operational Expense

        const netProfit = taxableSales - salariesPaid - stockExpenses;

        return sendJSON(res, {
          grossSales,
          gstCollected,
          taxableSales,
          salariesPaid,
          stockExpenses,
          netProfit,
          periodStart: startDate,
          periodEnd: endDate
        });
      } catch (e) {
        return sendError(res, e.message, 500);
      }
    }

    if (type === 'kpi') {
      try {
        const rooms = db.prepare('SELECT id, room_number FROM rooms').all();
        const totalRooms = rooms.length || 1;

        const diffTime = Math.abs(new Date(endDate) - new Date(startDate));
        const totalDays = Math.max(1, Math.ceil(diffTime / (1000 * 60 * 60 * 24)));
        const totalNightsAvailable = totalRooms * totalDays;

        let totalNightsSold = 0;
        rooms.forEach(r => {
          const occupiedDaysRow = db.prepare(`
            SELECT SUM(
              CASE 
                WHEN b.check_in <= ? AND b.check_out >= ? THEN ?
                WHEN b.check_in >= ? AND b.check_out <= ? THEN 
                  (strftime('%s', b.check_out) - strftime('%s', b.check_in)) / 86400
                WHEN b.check_in < ? AND b.check_out > ? AND b.check_out <= ? THEN 
                  (strftime('%s', b.check_out) - strftime('%s', ?)) / 86400
                WHEN b.check_in >= ? AND b.check_in < ? AND b.check_out > ? THEN 
                  (strftime('%s', ?) - strftime('%s', b.check_in)) / 86400
                ELSE 0
              END
            ) as occupied_days
            FROM bookings b
            JOIN booking_rooms br ON b.id = br.booking_id
            WHERE br.room_id = ? AND b.status IN ('Checked-In', 'Checked-Out')
          `).get(startDate, endDate, totalDays, startDate, endDate, startDate, startDate, endDate, startDate, startDate, endDate, endDate, endDate, r.id);

          const occupiedDays = Math.min(totalDays, Math.max(0, Math.round(occupiedDaysRow.occupied_days || 0)));
          totalNightsSold += occupiedDays;
        });

        const overlappingBookings = db.prepare(`
          SELECT b.id, b.check_in, b.check_out
          FROM bookings b
          WHERE b.status IN ('Checked-In', 'Checked-Out')
            AND b.check_in <= ? AND b.check_out >= ?
        `).all(endDate, startDate);

        let totalRoomRevenue = 0;
        let gstRate = 5;
        try {
          const gstRow = db.prepare("SELECT value FROM settings WHERE key = 'gst_rate'").get();
          if (gstRow) gstRate = parseFloat(gstRow.value);
        } catch (e) {}

        overlappingBookings.forEach(b => {
          const overlapStart = new Date(Math.max(new Date(b.check_in), new Date(startDate)));
          const overlapEnd = new Date(Math.min(new Date(b.check_out), new Date(endDate)));
          const overlapDays = Math.max(0, Math.ceil((overlapEnd - overlapStart) / (1000 * 60 * 60 * 24)));
          
          const priceRow = db.prepare('SELECT SUM(price_per_night) as total_price FROM booking_rooms WHERE booking_id = ?').get(b.id);
          const dailyPrice = priceRow.total_price || 0;
          
          // Compute net daily price (room charge exclusive of GST)
          const dailyPriceNet = dailyPrice / (1 + (gstRate / 100));
          totalRoomRevenue += (dailyPriceNet * overlapDays);
        });

        const occupancyRate = totalNightsAvailable > 0 ? (totalNightsSold / totalNightsAvailable) * 100 : 0;
        const adr = totalNightsSold > 0 ? (totalRoomRevenue / totalNightsSold) : 0;
        const revpar = totalNightsAvailable > 0 ? (totalRoomRevenue / totalNightsAvailable) : 0;

        return sendJSON(res, {
          totalRooms,
          totalDays,
          totalNightsAvailable,
          totalNightsSold,
          totalRoomRevenue: Math.round(totalRoomRevenue),
          occupancyRate: Math.round(occupancyRate * 10) / 10,
          adr: Math.round(adr),
          revpar: Math.round(revpar),
          periodStart: startDate,
          periodEnd: endDate
        });
      } catch (err) {
        return sendError(res, err.message, 500);
      }
    }

    return sendError(res, 'Unknown report type', 400);
  }

  // Reusable Invoice HTML compiler
  function compileInvoiceHTML(bookingId) {
    const booking = db.prepare('SELECT b.*, g.name as guest_name, g.mobile as guest_mobile, g.company_name, g.gst_number FROM bookings b JOIN guests g ON b.guest_id = g.id WHERE b.id = ?').get(bookingId);
    if (!booking) {
      throw new Error('Invoice not found');
    }
    
    const roomsList = db.prepare(`
      SELECT r.room_number, br.price_per_night 
      FROM booking_rooms br
      JOIN rooms r ON br.room_id = r.id
      WHERE br.booking_id = ?
    `).all(booking.id);
    
    const roomNumbers = roomsList.map(r => r.room_number).join(', ');
    const diffTime = Math.abs(new Date(booking.check_out) - new Date(booking.check_in));
    const nights = Math.max(1, Math.ceil(diffTime / (1000 * 60 * 60 * 24)));
    
    let gstRate = 5;
    try {
      const gstRow = db.prepare("SELECT value FROM settings WHERE key = 'gst_rate'").get();
      if (gstRow) gstRate = parseFloat(gstRow.value);
    } catch (e) {}
    
    const basePrice = Math.round(booking.total_amount / (1 + (gstRate / 100)));
    const gstAmount = booking.total_amount - basePrice;
    
    const hotelName = getSetting('hotel_name', 'Shaima Hotel Management');
    const hotelAddress = getSetting('hotel_address', '123 Beach Road, Chennai');
    const hotelGST = getSetting('hotel_gstin', 'GSTIN123456');
    const hotelLogo = getSetting('hotel_logo', '');
    const googleReviewLink = getSetting('google_review_link', '');
    const invoiceNum = booking.invoice_number || ('INV-' + new Date(booking.created_at).getFullYear() + '-' + (1000 + booking.id));

    const logoHTML = hotelLogo ? `<img src="${hotelLogo}" style="max-height: 38px; border-radius: 4px; vertical-align: middle;">` : '';

    let itemizedRowsHTML = '';
    roomsList.forEach((r, idx) => {
      let roomBaseTotal = Math.round((r.price_per_night * nights) / (1 + (gstRate / 100)));
      if (idx === roomsList.length - 1) {
        let sumOfOthers = 0;
        for (let i = 0; i < idx; i++) {
          sumOfOthers += Math.round((roomsList[i].price_per_night * nights) / (1 + (gstRate / 100)));
        }
        roomBaseTotal = basePrice - sumOfOthers;
      }
      
      const singleNightRateNet = Math.round(r.price_per_night / (1 + (gstRate / 100)));
      itemizedRowsHTML += `
        <tr>
          <td style="padding: 12px 10px; border-bottom: 1px solid #e2e8f0;">
            <span style="font-weight: 600; color: #1e293b;">Accommodation (Room ${r.room_number})</span>
            <div style="font-size: 11px; color: #64748b; margin-top: 2px;">Rate: ₹${singleNightRateNet.toLocaleString()} / night (excl. tax)</div>
          </td>
          <td style="text-align: right; padding: 12px 10px; border-bottom: 1px solid #e2e8f0; color: #334155;">${nights}</td>
          <td style="text-align: right; padding: 12px 10px; border-bottom: 1px solid #e2e8f0; font-weight: 600; color: #1e293b;">₹${roomBaseTotal.toLocaleString()}</td>
        </tr>
      `;
    });

    const gmapsLink = googleReviewLink || ("https://www.google.com/maps/search/?api=1&query=" + encodeURIComponent(hotelName + " " + hotelAddress));
    const staticQrURL = `https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=${encodeURIComponent(gmapsLink)}`;

    return `
      <!DOCTYPE html>
      <html lang="en">
        <head>
          <meta charset="UTF-8">
          <title>Invoice #${invoiceNum}</title>
          <style>
            body {
              font-family: 'Inter', -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, sans-serif;
              color: #334155;
              background: #f8fafc;
              margin: 0;
              padding: 10px;
              -webkit-print-color-adjust: exact;
            }
            .btn-print {
              background: #0f172a;
              color: #fff;
              border: none;
              padding: 8px 16px;
              font-size: 12px;
              font-weight: 600;
              border-radius: 6px;
              cursor: pointer;
              box-shadow: 0 1px 2px rgba(0,0,0,0.05);
              display: inline-block;
              margin-bottom: 10px;
              text-decoration: none;
            }
            .btn-print:hover {
              background: #1e293b;
            }
            .print-container {
              width: 100%;
              max-width: 800px;
              margin: 0 auto;
              background: #fff;
              padding: 20px;
              border-radius: 12px;
              box-shadow: 0 4px 6px -1px rgba(0,0,0,0.05);
            }
            .invoice-a5 {
              border: 1px solid #cbd5e1;
              padding: 15px;
              border-radius: 8px;
              background: #fff;
              position: relative;
              box-sizing: border-box;
            }
            .copy-label {
              position: absolute;
              top: 10px;
              right: 15px;
              background: #0f172a;
              color: #fff;
              font-size: 8px;
              font-weight: 800;
              text-transform: uppercase;
              padding: 2px 6px;
              border-radius: 4px;
              letter-spacing: 0.5px;
            }
            .header-row {
              display: flex;
              justify-content: space-between;
              align-items: center;
              margin-bottom: 4px;
            }
            .hotel-title {
              display: flex;
              align-items: center;
              gap: 10px;
            }
            .hotel-title h3 {
              margin: 0;
              font-size: 16px;
              font-weight: 800;
              color: #0f172a;
            }
            .details-row {
              display: flex;
              justify-content: space-between;
              margin-bottom: 6px;
              font-size: 11px;
              line-height: 1.4;
            }
            .details-col {
              width: 48%;
            }
            .details-title {
              font-weight: bold;
              color: #0f172a;
              text-transform: uppercase;
              font-size: 9px;
              letter-spacing: 0.5px;
              border-bottom: 1px solid #e2e8f0;
              padding-bottom: 2px;
              margin-bottom: 4px;
            }
            .item-table {
              width: 100%;
              border-collapse: collapse;
              margin-top: 4px;
              margin-bottom: 6px;
              font-size: 10.5px;
            }
            .item-table th {
              background: #f1f5f9;
              color: #475569;
              font-weight: bold;
              text-transform: uppercase;
              font-size: 8.5px;
              letter-spacing: 0.5px;
              text-align: left;
              padding: 6px 10px;
              border-bottom: 1px solid #cbd5e1;
            }
            .item-table td {
              padding: 8px 10px;
              border-bottom: 1px solid #cbd5e1;
            }
            .cut-line {
              text-align: center;
              border-top: 2px dashed #94a3b8;
              height: 1px;
              margin: 15px 0;
              position: relative;
            }
            .cut-line span {
              position: absolute;
              top: -10px;
              left: 50%;
              transform: translateX(-50%);
              background: #fff;
              padding: 0 15px;
              font-size: 10px;
              color: #64748b;
              font-weight: bold;
            }
            .footer-row {
              display: flex;
              justify-content: space-between;
              align-items: center;
              font-size: 9px;
              color: #64748b;
              margin-top: 6px;
              border-top: 1px solid #e2e8f0;
              padding-top: 6px;
            }
            .qr-box {
              text-align: center;
              background: #f8fafc;
              border: 1px solid #e2e8f0;
              padding: 6px 10px;
              border-radius: 6px;
              display: flex;
              align-items: center;
              gap: 8px;
            }
            @media print {
              .no-print { display: none; }
              body { padding: 0; margin: 0; }
              @page { size: A4 portrait; margin: 0; }
              .print-container {
                padding: 5mm;
                width: 200mm;
                height: 287mm;
              }
            }
          </style>
        </head>
        <body>
          <div class="no-print">
            <button class="btn-print" onclick="window.print()">Print / Save PDF</button>
          </div>
          
          <div class="print-container">
            <!-- ================= CUSTOMER COPY ================= -->
            <div class="invoice-a5">
              <div class="copy-label">Customer Copy</div>
              
              <div class="header-row">
                <div class="hotel-title">
                  ${logoHTML}
                  <div>
                    <h3>${hotelName}</h3>
                    <p style="margin: 2px 0 0 0; font-size: 9px; color: #64748b;">GSTIN: <strong>${hotelGST || 'N/A'}</strong></p>
                  </div>
                </div>
                <div style="text-align: right; font-size: 10px; color: #475569; line-height: 1.3;">
                  <p style="margin: 0; max-width: 280px;">${hotelAddress}</p>
                  <p style="margin: 2px 0 0 0;">Mobile: ${booking.guest_mobile}</p>
                </div>
              </div>

              <div class="details-row">
                <div class="details-col">
                  <div class="details-title">Billed To</div>
                  <p style="margin: 1px 0; font-weight: 600; color: #0f172a; font-size: 12px;">${booking.guest_name}</p>
                  <p style="margin: 1px 0; color: #475569;">Mobile: ${booking.guest_mobile}</p>
                  ${booking.company_name ? `<p style="margin: 3px 0 0 0; color: #475569; font-size: 10px; background: #f1f5f9; padding: 2px 6px; border-radius: 3px; display: inline-block;"><strong>Corp:</strong> ${booking.company_name} | <strong>GST:</strong> ${booking.gst_number || 'N/A'}</p>` : ''}
                </div>
                <div class="details-col" style="text-align: right;">
                  <div class="details-title">Invoice Details</div>
                  <p style="margin: 1px 0; color: #0f172a; font-weight: 600;">Invoice No: ${invoiceNum}</p>
                  <p style="margin: 1px 0; color: #475569;">Date: ${new Date(booking.created_at).toLocaleDateString('en-IN', { day: '2-digit', month: 'short', year: 'numeric' })}</p>
                  <p style="margin: 1px 0; color: #475569;">Booking ID: <code>${booking.booking_uid}</code></p>
                  <p style="margin: 1px 0; color: #475569;">Room(s): <strong>${roomNumbers}</strong></p>
                </div>
              </div>

              <table class="item-table">
                <thead>
                  <tr>
                    <th>Description</th>
                    <th style="text-align: right; width: 60px;">Nights</th>
                    <th style="text-align: right; width: 100px;">Amount</th>
                  </tr>
                </thead>
                <tbody>
                  ${itemizedRowsHTML}
                  <tr>
                    <td colspan="2" style="text-align: right; color: #475569; border-bottom: none; padding: 4px 8px;">Subtotal:</td>
                    <td style="text-align: right; border-bottom: none; padding: 4px 8px; font-weight: 500;">₹${basePrice.toLocaleString()}</td>
                  </tr>
                  <tr>
                    <td colspan="2" style="text-align: right; color: #475569; border-bottom: none; padding: 2px 8px;">CGST (${gstRate/2}%):</td>
                    <td style="text-align: right; border-bottom: none; padding: 2px 8px; font-weight: 500;">₹${Math.round(gstAmount/2).toLocaleString()}</td>
                  </tr>
                  <tr>
                    <td colspan="2" style="text-align: right; color: #475569; border-bottom: none; padding: 2px 8px;">SGST (${gstRate/2}%):</td>
                    <td style="text-align: right; border-bottom: none; padding: 2px 8px; font-weight: 500;">₹${Math.round(gstAmount/2).toLocaleString()}</td>
                  </tr>
                  <tr style="font-weight: bold; background: #f8fafc; border-top: 1px solid #94a3b8;">
                    <td colspan="2" style="text-align: right; padding: 6px 8px; border-bottom: none; color: #0f172a; font-size: 12px;">Grand Total (incl. Taxes):</td>
                    <td style="text-align: right; padding: 6px 8px; border-bottom: none; color: #0f172a; font-size: 13px;">₹${booking.total_amount.toLocaleString()}</td>
                  </tr>
                </tbody>
              </table>

              <div class="footer-row">
                <div style="max-width: 60%;">
                  <p style="margin: 0 0 3px 0; font-weight: bold; color: #0f172a; text-transform: uppercase; font-size: 9px; letter-spacing: 0.5px;">Terms & Policies</p>
                  <p style="margin: 0; color: #64748b; line-height: 1.3;">Thank you for staying with us! This is a computer-generated invoice and does not require a physical signature.</p>
                </div>
                <div class="qr-box">
                  <img src="${staticQrURL}" style="width: 50px; height: 50px; border: 1px solid #cbd5e1; background: #fff; border-radius: 4px;" alt="Google Maps Review QR">
                  <div style="text-align: left;">
                    <span style="font-weight: bold; color: #0f172a; font-size: 9px; display: block; text-transform: uppercase;">Review Us</span>
                    <span style="color: #475569; font-size: 8px; display: block; margin-top: 1px;">Scan Google Maps QR</span>
                  </div>
                </div>
              </div>
            </div>

            <!-- ================= CUT LINE ================= -->
            <div class="cut-line">
              <span>✂ ------------------ FOLD / CUT HERE ------------------ ✂</span>
            </div>

            <!-- ================= OFFICE COPY ================= -->
            <div class="invoice-a5">
              <div class="copy-label">Office Copy</div>
              
              <div class="header-row">
                <div class="hotel-title">
                  ${logoHTML}
                  <div>
                    <h3>${hotelName}</h3>
                    <p style="margin: 2px 0 0 0; font-size: 9px; color: #64748b;">GSTIN: <strong>${hotelGST || 'N/A'}</strong></p>
                  </div>
                </div>
                <div style="text-align: right; font-size: 10px; color: #475569; line-height: 1.3;">
                  <p style="margin: 0; max-width: 280px;">${hotelAddress}</p>
                  <p style="margin: 2px 0 0 0;">Mobile: ${booking.guest_mobile}</p>
                </div>
              </div>

              <div class="details-row">
                <div class="details-col">
                  <div class="details-title">Billed To</div>
                  <p style="margin: 1px 0; font-weight: 600; color: #0f172a; font-size: 12px;">${booking.guest_name}</p>
                  <p style="margin: 1px 0; color: #475569;">Mobile: ${booking.guest_mobile}</p>
                  ${booking.company_name ? `<p style="margin: 3px 0 0 0; color: #475569; font-size: 10px; background: #f1f5f9; padding: 2px 6px; border-radius: 3px; display: inline-block;"><strong>Corp:</strong> ${booking.company_name} | <strong>GST:</strong> ${booking.gst_number || 'N/A'}</p>` : ''}
                </div>
                <div class="details-col" style="text-align: right;">
                  <div class="details-title">Invoice Details</div>
                  <p style="margin: 1px 0; color: #0f172a; font-weight: 600;">Invoice No: ${invoiceNum}</p>
                  <p style="margin: 1px 0; color: #475569;">Date: ${new Date(booking.created_at).toLocaleDateString('en-IN', { day: '2-digit', month: 'short', year: 'numeric' })}</p>
                  <p style="margin: 1px 0; color: #475569;">Booking ID: <code>${booking.booking_uid}</code></p>
                  <p style="margin: 1px 0; color: #475569;">Room(s): <strong>${roomNumbers}</strong></p>
                </div>
              </div>

              <table class="item-table">
                <thead>
                  <tr>
                    <th>Description</th>
                    <th style="text-align: right; width: 60px;">Nights</th>
                    <th style="text-align: right; width: 100px;">Amount</th>
                  </tr>
                </thead>
                <tbody>
                  ${itemizedRowsHTML}
                  <tr>
                    <td colspan="2" style="text-align: right; color: #475569; border-bottom: none; padding: 4px 8px;">Subtotal:</td>
                    <td style="text-align: right; border-bottom: none; padding: 4px 8px; font-weight: 500;">₹${basePrice.toLocaleString()}</td>
                  </tr>
                  <tr>
                    <td colspan="2" style="text-align: right; color: #475569; border-bottom: none; padding: 2px 8px;">CGST (${gstRate/2}%):</td>
                    <td style="text-align: right; border-bottom: none; padding: 2px 8px; font-weight: 500;">₹${Math.round(gstAmount/2).toLocaleString()}</td>
                  </tr>
                  <tr>
                    <td colspan="2" style="text-align: right; color: #475569; border-bottom: none; padding: 2px 8px;">SGST (${gstRate/2}%):</td>
                    <td style="text-align: right; border-bottom: none; padding: 2px 8px; font-weight: 500;">₹${Math.round(gstAmount/2).toLocaleString()}</td>
                  </tr>
                  <tr style="font-weight: bold; background: #f8fafc; border-top: 1px solid #94a3b8;">
                    <td colspan="2" style="text-align: right; padding: 6px 8px; border-bottom: none; color: #0f172a; font-size: 12px;">Grand Total (incl. Taxes):</td>
                    <td style="text-align: right; padding: 6px 8px; border-bottom: none; color: #0f172a; font-size: 13px;">₹${booking.total_amount.toLocaleString()}</td>
                  </tr>
                </tbody>
              </table>

              <div class="footer-row">
                <div style="max-width: 60%;">
                  <p style="margin: 0 0 3px 0; font-weight: bold; color: #0f172a; text-transform: uppercase; font-size: 9px; letter-spacing: 0.5px;">Terms & Policies</p>
                  <p style="margin: 0; color: #64748b; line-height: 1.3;">Thank you for staying with us! This is a computer-generated invoice and does not require a physical signature.</p>
                </div>
                <div class="qr-box">
                  <img src="${staticQrURL}" style="width: 50px; height: 50px; border: 1px solid #cbd5e1; background: #fff; border-radius: 4px;" alt="Google Maps Review QR">
                  <div style="text-align: left;">
                    <span style="font-weight: bold; color: #0f172a; font-size: 9px; display: block; text-transform: uppercase;">Review Us</span>
                    <span style="color: #475569; font-size: 8px; display: block; margin-top: 1px;">Scan Google Maps QR</span>
                  </div>
                </div>
              </div>
            </div>
          </div>
        </body>
      </html>
    `;
  }

  // Invoice PDF/Print Layout API
  if (pathname === '/api/invoices/pdf' && method === 'GET') {
    try {
      const bookingId = query.id;
      if (!bookingId) {
        res.writeHead(400, { 'Content-Type': 'text/html' });
        res.end('<h1>Booking ID is required</h1>');
        return;
      }
      const html = compileInvoiceHTML(bookingId);
      res.writeHead(200, { 'Content-Type': 'text/html; charset=utf-8' });
      res.end(html);
    } catch (e) {
      res.writeHead(500, { 'Content-Type': 'text/html' });
      res.end(`<h1>Failed to load invoice layout: ${e.message}</h1>`);
    }
    return;
  }

  // Share Invoice via Email API
  if (pathname === '/api/invoices/send-email' && method === 'POST') {
    try {
      const { bookingId, email, cc, bcc, userId } = await parseJSON(req);
      if (!bookingId || !email) {
        return sendError(res, 'Booking ID and target Email are required', 400);
      }

      const booking = db.prepare("SELECT * FROM bookings WHERE id = ?").get(bookingId);
      if (!booking) return sendError(res, 'Booking not found', 404);

      const guest = db.prepare("SELECT * FROM guests WHERE id = ?").get(booking.guest_id);
      if (!guest) return sendError(res, 'Guest not found', 404);

      const rooms = db.prepare(`
        SELECT r.* FROM rooms r
        JOIN booking_rooms br ON r.id = br.room_id
        WHERE br.booking_id = ?
      `).all(bookingId);

      const uploadsDir = path.join(__dirname, 'public', 'uploads');
      if (!fs.existsSync(uploadsDir)) fs.mkdirSync(uploadsDir, { recursive: true });

      const pdfFilename = `invoice_${booking.booking_uid}.pdf`;
      const pdfPath = path.join(uploadsDir, pdfFilename);
      await createInvoicePDF(booking, rooms, guest, pdfPath);

      const invoiceHTML = compileInvoiceHTML(bookingId);
      const hotelName = getSetting('hotel_name', 'Shaima Hotel');
      const subject = `Invoice for your stay at ${hotelName}`;

      await sendEmail({
        to: email,
        cc: cc || '',
        bcc: bcc || '',
        subject,
        html: invoiceHTML,
        attachments: [
          {
            filename: pdfFilename,
            path: pdfPath
          }
        ]
      });

      const dbTime = new Date().toISOString();
      db.prepare(`
        INSERT INTO email_logs (guest_id, recipient_email, cc_emails, bcc_emails, subject, body, attachment_name, status, timestamp)
        VALUES (?, ?, ?, ?, ?, ?, ?, 'sent', ?)
      `).run(guest.id, email, cc || null, bcc || null, subject, `Invoice HTML email dispatched with PDF attachment: ${pdfFilename}`, pdfFilename, dbTime);

      logAudit(userId, 'Email Invoice', `Sent invoice for booking ID ${bookingId} to ${email}`);
      return sendJSON(res, { ok: true, message: 'Invoice shared via email successfully!' });
    } catch (e) {
      return sendError(res, `Failed to email invoice: ${e.message}`, 500);
    }
  }

  // Reusable Report HTML compiler
  function compileReportHTML(type, startDate, endDate) {
    const hotelName = getSetting('hotel_name', 'Shaima Hotel Management');
    const hotelAddress = getSetting('hotel_address', '123 Beach Road, Chennai');
    const hotelLogo = getSetting('hotel_logo', '');
    const logoHTML = hotelLogo ? `<img src="${hotelLogo}" style="max-height: 60px; display: block; margin: 0 auto 10px auto;">` : '';

    let reportTitle = 'Report';
    let tableContentHtml = '';

    if (type === 'daily') {
      reportTitle = 'Daily Operations Summary';
      const bookingsCreated = db.prepare("SELECT COUNT(*) as count, SUM(total_amount) as total FROM bookings WHERE created_at BETWEEN ? AND ? AND status != 'Cancelled'").get(startDate, endDate);
      const checkouts = db.prepare("SELECT COUNT(*) as count, SUM(paid_amount) as total FROM bookings WHERE status = 'Checked-Out' AND created_at BETWEEN ? AND ?").get(startDate, endDate);
      
      tableContentHtml = `
        <tr><td>Bookings Reservations Created</td><td style="text-align: right;">${bookingsCreated.count || 0}</td></tr>
        <tr><td>Estimated Booking Value</td><td style="text-align: right;">₹${(bookingsCreated.total || 0).toLocaleString()}</td></tr>
        <tr><td>Total Checkouts Finalized</td><td style="text-align: right;">${checkouts.count || 0}</td></tr>
        <tr style="font-weight: bold;"><td>Total Cash Revenue Collected</td><td style="text-align: right;">₹${(checkouts.total || 0).toLocaleString()}</td></tr>
      `;
    } else if (type === 'yearly-tax') {
      reportTitle = 'Yearly Audit & Tax Filing Report';
      const salesRow = db.prepare("SELECT COUNT(*) as count, SUM(total_amount) as total, SUM(gst_amount) as gst FROM bookings WHERE status = 'Checked-Out' AND created_at BETWEEN ? AND ?").get(startDate, endDate);
      const grossSales = salesRow.total || 0;
      const gstCollected = salesRow.gst || 0;
      const taxableSales = grossSales - gstCollected;

      const salariesRow = db.prepare("SELECT SUM(base_salary + bonus - deductions) as total FROM salaries WHERE paid_date BETWEEN ? AND ?").get(startDate, endDate);
      const totalSalaries = salariesRow.total || 0;

      tableContentHtml = `
        <tr><td>Gross Room Sales Revenue</td><td style="text-align: right;">₹${grossSales.toLocaleString()}</td></tr>
        <tr><td>GST Collected (Liability)</td><td style="text-align: right;">₹${gstCollected.toLocaleString()}</td></tr>
        <tr><td>Net Taxable Sales</td><td style="text-align: right;">₹${taxableSales.toLocaleString()}</td></tr>
        <tr><td>Staff Salaries & Benefits Expense</td><td style="text-align: right;">₹${totalSalaries.toLocaleString()}</td></tr>
        <tr style="font-weight: bold; background: #fafafa;"><td>Estimated Net Profit (Pre-tax)</td><td style="text-align: right;">₹${(taxableSales - totalSalaries).toLocaleString()}</td></tr>
      `;
    } else if (type === 'kpi') {
      reportTitle = 'Key Performance Indicators (KPI) Report';
      
      const totalRoomsRow = db.prepare("SELECT COUNT(*) as count FROM rooms WHERE status != 'Maintenance'").get();
      const totalRooms = totalRoomsRow.count || 1; 

      const dateDiff = Math.abs(new Date(endDate) - new Date(startDate));
      const days = Math.max(1, Math.ceil(dateDiff / (1000 * 60 * 60 * 24)));
      const totalAvailableRoomNights = totalRooms * days;

      const occupiedNightsRow = db.prepare(`
        SELECT COUNT(*) as count, SUM(b.total_amount) as totalRevenue
        FROM booking_rooms br
        JOIN bookings b ON br.booking_id = b.id
        WHERE b.status != 'Cancelled'
          AND b.check_in <= ?
          AND b.check_out >= ?
      `).get(endDate, startDate);

      const occupiedRoomNights = occupiedNightsRow.count || 0;
      const totalRevenue = occupiedNightsRow.totalRevenue || 0;

      const occupancyRate = Math.min(100, Math.round((occupiedRoomNights / totalAvailableRoomNights) * 100));
      const adr = occupiedRoomNights > 0 ? Math.round(totalRevenue / occupiedRoomNights) : 0;
      const revpar = totalAvailableRoomNights > 0 ? Math.round(totalRevenue / totalAvailableRoomNights) : 0;

      tableContentHtml = `
        <tr><td>Total Operational Rooms (Excl. Out of Order)</td><td style="text-align: right;">${totalRooms}</td></tr>
        <tr><td>Available Room Nights in Period</td><td style="text-align: right;">${totalAvailableRoomNights}</td></tr>
        <tr><td>Occupied Room Nights</td><td style="text-align: right;">${occupiedRoomNights}</td></tr>
        <tr style="font-weight: bold;"><td>Average Occupancy Rate</td><td style="text-align: right;">${occupancyRate}%</td></tr>
        <tr style="font-weight: bold;"><td>Average Daily Rate (ADR)</td><td style="text-align: right;">₹${adr.toLocaleString()}</td></tr>
        <tr style="font-weight: bold;"><td>Revenue Per Available Room (RevPAR)</td><td style="text-align: right;">₹${revpar.toLocaleString()}</td></tr>
      `;
    } else if (type === 'inventory') {
      reportTitle = 'Inventory Status & Amenity Stock Log';
      const items = db.prepare("SELECT * FROM inventory ORDER BY name").all();
      tableContentHtml = items.map(i => `
        <tr>
          <td><strong>${i.name}</strong><br><small style="color: #666;">Unit: ${i.unit}</small></td>
          <td style="text-align: right; color: ${i.quantity <= i.min_threshold ? '#ef4444; font-weight: bold;' : 'inherit'};">
            ${i.quantity} ${i.quantity <= i.min_threshold ? ' (LOW STOCK)' : ''}
          </td>
        </tr>
      `).join('');
    } else if (type === 'housekeeping') {
      reportTitle = 'Housekeeping & Maintenance Status Report';
      const tasks = db.prepare(`
        SELECT h.*, r.room_number, r.status as room_status, u.name as staff_name
        FROM housekeeping h
        JOIN rooms r ON h.room_id = r.id
        LEFT JOIN users u ON h.staff_id = u.id
        ORDER BY r.room_number
      `).all();
      tableContentHtml = tasks.map(t => `
        <tr>
          <td>
            <strong>Room ${t.room_number}</strong><br>
            <small style="color: #666;">Assigned: ${t.staff_name || 'Unassigned'}</small>
          </td>
          <td style="text-align: right;">
            <span style="font-weight: bold; text-transform: uppercase; color: ${t.status === 'Clean' ? '#10b981' : t.status === 'Cleaning' ? '#a855f7' : '#f59e0b'};">${t.status}</span>
            <br><small style="color: #888;">Last Update: ${t.last_updated ? new Date(t.last_updated).toLocaleDateString() : 'N/A'}</small>
          </td>
        </tr>
      `).join('');
    } else {
      reportTitle = `${type.toUpperCase()} Operations Analytics`;
      tableContentHtml = `
        <tr><td>Period Start Date</td><td style="text-align: right;">${startDate}</td></tr>
        <tr><td>Period End Date</td><td style="text-align: right;">${endDate}</td></tr>
        <tr><td>Operational Status</td><td style="text-align: right;">Reconciled & Re-audited</td></tr>
      `;
    }

    return `
      <!DOCTYPE html>
      <html>
        <head>
          <meta charset="utf-8">
          <title>${reportTitle}</title>
          <style>
            body { font-family: monospace; padding: 40px; background: #fff; color: #000; max-width: 650px; margin: 0 auto; line-height: 1.4; }
            table { width: 100%; border-collapse: collapse; margin-top: 25px; margin-bottom: 25px; }
            th, td { padding: 10px; border-bottom: 1px solid #ddd; font-size: 12px; }
            th { background: #f5f5f5; text-align: left; border-bottom: 2px solid #000; font-weight: bold; }
            .header { text-align: center; margin-bottom: 30px; }
            .details { display: flex; justify-content: space-between; margin-bottom: 20px; font-size: 12px; border-bottom: 1px solid #eee; padding-bottom: 15px; }
            .btn-print { padding: 8px 16px; cursor: pointer; font-family: monospace; font-weight: bold; background: #000; color: #fff; border: none; border-radius: 4px; }
            @media print {
              body { padding: 0; }
              .no-print { display: none; }
            }
          </style>
        </head>
        <body>
          <div class="no-print" style="margin-bottom: 25px; text-align: right;">
            <button class="btn-print" onclick="window.print()">Print / Save as PDF</button>
          </div>
          <div class="header">
            ${logoHTML}
            <h2 style="margin: 0; font-size: 20px;">${hotelName}</h2>
            <p style="margin: 6px 0; font-size: 11px; color: #444;">${hotelAddress}</p>
          </div>
          <div class="details">
            <div>
              <p style="margin: 2px 0;"><strong>Report:</strong> ${reportTitle}</p>
              <p style="margin: 2px 0;">Period: ${startDate} to ${endDate}</p>
            </div>
            <div style="text-align: right;">
              <p style="margin: 2px 0;">Generated: ${new Date().toLocaleDateString()}</p>
              <p style="margin: 2px 0;">Status: Reconciled & Audited</p>
            </div>
          </div>
          <table>
            <thead>
              <tr>
                <th>Financial Ledger Category / Item</th>
                <th style="text-align: right;">Value / Amount</th>
              </tr>
            </thead>
            <tbody>
              ${tableContentHtml}
            </tbody>
          </table>
          <div style="margin-top: 40px; font-size: 10px; text-align: center; border-top: 1px dashed #ccc; padding-top: 20px; color: #777;">
            Report generated from Shaima Hotel Management standalone accounting database.
          </div>
        </body>
      </html>
    `;
  }

  // Report PDF/Print Layout API
  if (pathname === '/api/reports/pdf' && method === 'GET') {
    try {
      const type = query.type;
      const startDate = query.startDate;
      const endDate = query.endDate;
      const html = compileReportHTML(type, startDate, endDate);
      res.writeHead(200, { 'Content-Type': 'text/html; charset=utf-8' });
      res.end(html);
    } catch (e) {
      res.writeHead(500, { 'Content-Type': 'text/html' });
      res.end(`<h1>Failed to load report layout: ${e.message}</h1>`);
    }
    return;
  }

  // Share Report Endpoint
  if (pathname === '/api/reports/share' && method === 'POST') {
    try {
      const { type, startDate, endDate, channel, target, cc, bcc, userId } = await parseJSON(req);
      let reportTitle = '';
      let summaryText = '';
      let headers = ['Category / Item', 'Report Value'];
      let rows = [];
      
      if (type === 'daily') {
        reportTitle = 'Daily Operations Summary';
        const bookingsCreated = db.prepare("SELECT COUNT(*) as count, SUM(total_amount) as total FROM bookings WHERE created_at BETWEEN ? AND ? AND status != 'Cancelled'").get(startDate, endDate);
        const checkouts = db.prepare("SELECT COUNT(*) as count, SUM(paid_amount) as total FROM bookings WHERE status = 'Checked-Out' AND created_at BETWEEN ? AND ?").get(startDate, endDate);
        summaryText = `Reservations Created: ${bookingsCreated.count || 0} (Val: ₹${(bookingsCreated.total || 0).toLocaleString()}), Checkouts: ${checkouts.count || 0} (Cash Collected: ₹${(checkouts.total || 0).toLocaleString()})`;
        rows = [
          ['Reservations Created', String(bookingsCreated.count || 0)],
          ['Estimated Booking Value', '₹' + (bookingsCreated.total || 0).toLocaleString()],
          ['Total Checkouts Finalized', String(checkouts.count || 0)],
          ['Total Cash Revenue Collected', '₹' + (checkouts.total || 0).toLocaleString()]
        ];
      } else if (type === 'kpi') {
        reportTitle = 'KPI Report';
        const totalRoomsRow = db.prepare("SELECT COUNT(*) as count FROM rooms WHERE status != 'Maintenance'").get();
        const totalRooms = totalRoomsRow.count || 1; 
        const dateDiff = Math.abs(new Date(endDate) - new Date(startDate));
        const days = Math.max(1, Math.ceil(dateDiff / (1000 * 60 * 60 * 24)));
        const totalAvailableRoomNights = totalRooms * days;
        const occupiedNightsRow = db.prepare(`
          SELECT COUNT(*) as count, SUM(b.total_amount) as totalRevenue
          FROM booking_rooms br
          JOIN bookings b ON br.booking_id = b.id
          WHERE b.status != 'Cancelled' AND b.check_in <= ? AND b.check_out >= ?
        `).get(endDate, startDate);
        const occupiedRoomNights = occupiedNightsRow.count || 0;
        const totalRevenue = occupiedNightsRow.totalRevenue || 0;
        const occupancyRate = Math.min(100, Math.round((occupiedRoomNights / totalAvailableRoomNights) * 100));
        const adr = occupiedRoomNights > 0 ? Math.round(totalRevenue / occupiedRoomNights) : 0;
        const revpar = totalAvailableRoomNights > 0 ? Math.round(totalRevenue / totalAvailableRoomNights) : 0;
        
        summaryText = `Occupancy: ${occupancyRate}%, ADR: ₹${adr.toLocaleString()}, RevPAR: ₹${revpar.toLocaleString()}`;
        rows = [
          ['Total Rooms Available', String(totalRooms)],
          ['Room Nights in Period', String(totalAvailableRoomNights)],
          ['Occupied Room Nights', String(occupiedRoomNights)],
          ['Average Occupancy Rate', occupancyRate + '%'],
          ['Average Daily Rate (ADR)', '₹' + adr.toLocaleString()],
          ['Revenue Per Available Room (RevPAR)', '₹' + revpar.toLocaleString()]
        ];
      } else if (type === 'yearly-tax') {
        reportTitle = 'Yearly Audit & Tax Report';
        const salesRow = db.prepare("SELECT COUNT(*) as count, SUM(total_amount) as total, SUM(gst_amount) as gst FROM bookings WHERE status = 'Checked-Out' AND created_at BETWEEN ? AND ?").get(startDate, endDate);
        const grossSales = salesRow.total || 0;
        const gstCollected = salesRow.gst || 0;
        const taxableSales = grossSales - gstCollected;
        const salariesRow = db.prepare("SELECT SUM(base_salary + bonus - deductions) as total FROM salaries WHERE paid_date BETWEEN ? AND ?").get(startDate, endDate);
        const totalSalaries = salariesRow.total || 0;
        
        summaryText = `Gross Room Sales: ₹${grossSales.toLocaleString()}, GST Collected: ₹${gstCollected.toLocaleString()}, Salaries Paid: ₹${totalSalaries.toLocaleString()}`;
        rows = [
          ['Gross Room Sales', '₹' + grossSales.toLocaleString()],
          ['GST Liability Collected', '₹' + gstCollected.toLocaleString()],
          ['Net Taxable Sales', '₹' + taxableSales.toLocaleString()],
          ['Staff Salary Expense', '₹' + totalSalaries.toLocaleString()],
          ['Pre-tax Net Profit Margin', '₹' + (taxableSales - totalSalaries).toLocaleString()]
        ];
      } else if (type === 'inventory') {
        reportTitle = 'Inventory Status Report';
        const items = db.prepare("SELECT * FROM inventory ORDER BY name").all();
        summaryText = `${items.length} items audited in inventory registry.`;
        rows = items.map(i => [i.name, `${i.quantity} ${i.unit} ${i.quantity <= i.min_threshold ? '(LOW STOCK)' : ''}`]);
      } else if (type === 'housekeeping') {
        reportTitle = 'Housekeeping Room Status Report';
        const tasks = db.prepare(`
          SELECT h.*, r.room_number, r.status as room_status, u.name as staff_name
          FROM housekeeping h
          JOIN rooms r ON h.room_id = r.id
          LEFT JOIN users u ON h.staff_id = u.id
          ORDER BY r.room_number
        `).all();
        summaryText = `${tasks.length} rooms audited for clean status.`;
        rows = tasks.map(t => [`Room ${t.room_number} (${t.staff_name || 'Unassigned'})`, t.status.toUpperCase()]);
      }

      // Generate PDF Report
      const uploadsDir = path.join(__dirname, 'public', 'uploads');
      if (!fs.existsSync(uploadsDir)) fs.mkdirSync(uploadsDir, { recursive: true });

      const pdfFilename = `report_${type}_${Date.now()}.pdf`;
      const pdfPath = path.join(uploadsDir, pdfFilename);
      await createReportPDF(reportTitle, headers, rows, pdfPath);

      if (channel === 'whatsapp') {
        const provider = getSetting('whatsapp_provider', 'None');
        if (provider === 'WhatsAppCloudAPI') {
          const token = getSetting('whatsapp_cloud_token', '');
          const phoneId = getSetting('whatsapp_cloud_phone_id', '');

          if (token && phoneId) {
            const mediaId = await uploadMetaMediaFile(pdfPath, pdfFilename, 'application/pdf', token, phoneId);
            const cleanPhone = target.replace(/[^0-9]/g, '');
            const postData = JSON.stringify({
              messaging_product: 'whatsapp',
              recipient_type: 'individual',
              to: cleanPhone,
              type: 'document',
              document: {
                id: mediaId,
                filename: pdfFilename
              }
            });

            const options = {
              hostname: 'graph.facebook.com',
              port: 443,
              path: `/v20.0/${phoneId}/messages`,
              method: 'POST',
              headers: {
                'Authorization': `Bearer ${token}`,
                'Content-Type': 'application/json',
                'Content-Length': Buffer.byteLength(postData)
              }
            };

            await new Promise((resolve, reject) => {
              const reqMeta = https.request(options, (resMeta) => {
                resMeta.on('end', resolve);
                resMeta.on('error', reject);
              });
              reqMeta.write(postData);
              reqMeta.end();
            });
          }
        }
        logAudit(userId, 'Share Report via WhatsApp', `Sent ${reportTitle} to ${target}`);
      } else if (channel === 'email') {
        const reportHTML = compileReportHTML(type, startDate, endDate);
        await sendEmail({
          to: target,
          cc: cc || '',
          bcc: bcc || '',
          subject: `${reportTitle} (${startDate} to ${endDate})`,
          html: reportHTML,
          attachments: [
            {
              filename: pdfFilename,
              path: pdfPath
            }
          ]
        });
        logAudit(userId, 'Share Report via Email', `Emailed ${reportTitle} to ${target}`);
      }

      return sendJSON(res, { ok: true, message: `Report shared successfully via ${channel}!` });
    } catch (e) {
      return sendError(res, e.message, 500);
    }
  }

  return sendError(res, 'Not Found', 404);
}

// Function to deduct inventory amenities on checkin
function deductRoomOccupancyInventory(roomCount) {
  try {
    // 1 Soap, 1 Shampoo, 2 Water Bottles, 1 Tea, 1 Coffee, 1 Milk Powder per room occupancy
    const deductions = {
      'Soap': 1 * roomCount,
      'Shampoo': 1 * roomCount,
      'Water Bottle': 2 * roomCount,
      'Tea': 1 * roomCount,
      'Coffee': 1 * roomCount,
      'Milk Powder': 1 * roomCount
    };

    const updateStmt = db.prepare('UPDATE inventory SET quantity = MAX(0, quantity - ?) WHERE name = ?');
    for (const name in deductions) {
      updateStmt.run(deductions[name], name);
      console.log(`Auto deducted ${deductions[name]} units of ${name} from inventory.`);
    }
  } catch (err) {
    console.error('Failed auto stock deduction:', err);
  }
}

// Main Request Router
const server = http.createServer(async (req, res) => {
  const parsedUrl = new URL(req.url, `http://${req.headers.host || 'localhost'}`);
  const pathname = parsedUrl.pathname;

  // Serve API Requests
  if (pathname.startsWith('/api/')) {
    const query = {};
    parsedUrl.searchParams.forEach((val, key) => {
      query[key] = val;
    });
    try {
      await handleAPIRequest(req, res, pathname, query);
    } catch (err) {
      console.error(`API Error: ${pathname}`, err);
      sendError(res, err.message, 500);
    }
    return;
  }

  // Serve Static Frontend Files
  let filePath = path.join(__dirname, 'public', pathname === '/' ? 'index.html' : pathname);
  
  // Guard against path traversal
  if (!filePath.startsWith(path.join(__dirname, 'public'))) {
    res.writeHead(403);
    res.end('Access Denied');
    return;
  }

  const ext = path.extname(filePath).toLowerCase();
  const mimeTypes = {
    '.html': 'text/html',
    '.css': 'text/css',
    '.js': 'application/javascript',
    '.json': 'application/json',
    '.png': 'image/png',
    '.jpg': 'image/jpeg',
    '.gif': 'image/gif',
    '.svg': 'image/svg+xml',
    '.ico': 'image/x-icon'
  };

  const contentType = mimeTypes[ext] || 'application/octet-stream';

  fs.stat(filePath, (err, stats) => {
    if (err || !stats.isFile()) {
      // SPA fallback - redirect non-file assets to index.html
      filePath = path.join(__dirname, 'public', 'index.html');
      fs.readFile(filePath, (errSpa, contentSpa) => {
        if (errSpa) {
          res.writeHead(404);
          res.end('File Not Found');
        } else {
          res.writeHead(200, { 'Content-Type': 'text/html' });
          res.end(contentSpa);
        }
      });
      return;
    }

    fs.readFile(filePath, (readErr, content) => {
      if (readErr) {
        res.writeHead(500);
        res.end('Server Error');
      } else {
        res.writeHead(200, { 'Content-Type': contentType });
        res.end(content);
      }
    });
  });
});

async function runAutomatedBackup() {
  try {
    const lastBackupTime = getSetting('last_automated_backup_time', '');
    const intervalHours = parseInt(getSetting('backup_interval_hours', '24'), 10);
    const lastDate = lastBackupTime ? new Date(lastBackupTime) : new Date(0);
    const hoursElapsed = (Date.now() - lastDate.getTime()) / 3600000;

    if (hoursElapsed >= intervalHours) {
      console.log('Initiating scheduled database backup...');
      const backupDir = getSetting('backup_dir', path.join(__dirname, 'backups'));
      if (!fs.existsSync(backupDir)) {
        fs.mkdirSync(backupDir, { recursive: true });
      }

      const timestamp = new Date().toISOString().replace(/[:.]/g, '-');
      const filename = `hotel_backup_${timestamp}.db`;
      const backupFile = path.join(backupDir, filename);

      fs.copyFileSync(dbPath, backupFile);
      logAudit(null, 'Automated Local Backup', `Backup created locally: ${filename}`);

      const saJson = getSetting('google_drive_sa_key', '');
      const folderId = getSetting('google_drive_folder_id', '');

      if (saJson && folderId) {
        console.log('Uploading scheduled backup to Google Drive...');
        try {
          const token = await getGoogleAccessToken(saJson);
          await uploadFileToGoogleDrive(token, backupFile, filename, folderId);
          logAudit(null, 'Automated Google Drive Backup', `Backup uploaded successfully to Folder: ${folderId}`);
        } catch (err) {
          console.error('Scheduled Google Drive backup failed:', err);
          logAudit(null, 'Automated Google Drive Backup Failed', err.message);
        }
      }

      db.prepare("INSERT OR REPLACE INTO settings (key, value) VALUES ('last_automated_backup_time', ?)")
        .run(new Date().toISOString());
    }
  } catch (err) {
    console.error('Automated backup worker failed:', err);
  }
}

// Start automated daily backup check immediately on boot, and then hourly
setTimeout(() => {
  runAutomatedBackup();
  setInterval(runAutomatedBackup, 3600000);
}, 5000);

function triggerAutomatedNotification(eventKey, bookingId, guestId) {
  try {
    const mapping = db.prepare("SELECT * FROM whatsapp_automation_map WHERE event_key = ? AND enabled = 1").get(eventKey);
    if (!mapping || !mapping.template_name) return;

    const sender = db.prepare("SELECT * FROM whatsapp_phone_numbers WHERE is_default = 1 AND disabled = 0").get();
    if (!sender) return;

    const guest = db.prepare("SELECT * FROM guests WHERE id = ?").get(guestId);
    if (!guest || !guest.mobile) return;

    let booking = null;
    if (bookingId) {
      booking = db.prepare("SELECT * FROM bookings WHERE id = ?").get(bookingId);
    }

    let templates = {};
    try {
      templates = JSON.parse(getSetting('whatsapp_templates', '{}'));
    } catch (e) {}

    const templateKey = mapping.template_name.toLowerCase();
    const tpl = templates[templateKey];
    if (!tpl) return;

    const hotelName = getSetting('hotel_name', 'Shaima Hotel');

    let bodyText = tpl.body || '';
    const replacements = {
      '{guest_name}': guest.name || 'Valued Guest',
      '{guest_phone}': guest.mobile || '',
      '{booking_uid}': booking ? (booking.booking_uid || `BK-${booking.id}`) : '',
      '{checkin_date}': booking ? booking.checkin_date : '',
      '{checkout_date}': booking ? booking.checkout_date : '',
      '{room_number}': booking ? (booking.room_number || '') : '',
      '{room_type}': booking ? (booking.room_type || '') : '',
      '{hotel_name}': hotelName,
      '{total_amount}': booking ? `₹${booking.total_amount}` : '',
      '{invoice_number}': booking ? (booking.invoice_number || `INV-${booking.id}`) : ''
    };

    Object.keys(replacements).forEach(key => {
      bodyText = bodyText.replaceAll(key, replacements[key]);
    });

    const payload = JSON.stringify({
      phone: guest.mobile,
      message: bodyText,
      phoneId: sender.phone_number_id,
      token: sender.access_token
    });

    db.prepare(`
      INSERT INTO background_jobs (job_type, payload, status, retry_count, max_retries, created_at, updated_at)
      VALUES ('whatsapp_send', ?, 'pending', 0, 3, ?, ?)
    `).run(payload, new Date().toISOString(), new Date().toISOString());

  } catch (err) {
    console.error(`[triggerAutomatedNotification Error for ${eventKey}]:`, err.message);
  }
}

// ================= WEBSOCKET REAL-TIME BROADCASTER SYSTEM ================= //
const { WebSocketServer } = require('ws');
const wss = new WebSocketServer({ server });
const connectedClients = new Set();

wss.on('connection', (ws) => {
  connectedClients.add(ws);
  ws.on('close', () => connectedClients.delete(ws));
});

function broadcast(payload) {
  const data = JSON.stringify(payload);
  for (const client of connectedClients) {
    if (client.readyState === 1) { // OPEN
      try {
        client.send(data);
      } catch (err) {
        console.error('WebSocket send error:', err.message);
      }
    }
  }
}

// Bind server to all network interfaces to support LAN multi-computer access
server.listen(port, '0.0.0.0', () => {
  console.log(`Hotel Management Server running at http://localhost:${port}`);
  console.log(`LAN Access: Connect via http://<IP_ADDRESS>:${port}`);
});
