const fs = require('fs').promises;
const fsSync = require('fs');
const path = require('path');
const os = require('os');
const AdmZip = require('adm-zip');
const ExcelJS = require('exceljs');

class OfficeModifier {
  static getDefaultWorkspace() {
    const storageRoot = process.env.STORAGE_DIR || 
      path.resolve(__dirname, '../../../storage');
    return path.join(storageRoot, 'anythingllm-fs');
  }

  static expandHome(filepath) {
    if (filepath.startsWith('~/') || filepath === '~') {
      return path.join(os.homedir(), filepath.slice(1));
    }
    return filepath;
  }

  static async resolvePath(inputPath, allowedDirs) {
    let resolved = OfficeModifier.expandHome(inputPath);
    
    if (!path.isAbsolute(resolved)) {
      for (const allowedDir of allowedDirs) {
        const testPath = path.join(allowedDir, resolved);
        if (fsSync.existsSync(testPath)) {
          resolved = testPath;
          break;
        }
      }
      
      if (!path.isAbsolute(resolved)) {
        resolved = path.join(allowedDirs[0], resolved);
      }
    }
    
    return path.normalize(resolved);
  }

  static async validatePath(resolvedPath, allowedDirs) {
    const normalized = path.normalize(resolvedPath);
    
    const isAllowed = allowedDirs.some(dir => {
      try {
        return normalized.startsWith(path.normalize(dir));
      } catch {
        return false;
      }
    });

    if (!isAllowed) {
      throw new Error(`Access denied: ${resolvedPath} not in allowed directories`);
    }

    return normalized;
  }
}

module.exports.runtime = {
  handler: async function ({ 
    operation, 
    filePath, 
    find, 
    replace, 
    content, 
    property, 
    value, 
    tableData,
    sheet,
    cell,
    rowData,
    insertBeforeRow,
    columnName,
    insertBeforeColumn
  }) {
    try {
      if (!operation || !filePath) {
        throw new Error('operation and filePath are required');
      }

      let allowedDirs = [];
      const customDirs = (this.runtimeArgs.allowed_directories || '').trim();
      
      if (customDirs.length > 0) {
        allowedDirs = customDirs
          .split(',')
          .map(d => d.trim())
          .filter(d => d.length > 0)
          .map(d => OfficeModifier.expandHome(d))
          .map(d => path.resolve(d));
        this.introspect(`Using custom directories: ${allowedDirs.join(', ')}`);
      } else {
        allowedDirs = [OfficeModifier.getDefaultWorkspace()];
        this.introspect(`Using default workspace: ${allowedDirs[0]}`);
      }

      for (const dir of allowedDirs) {
        try {
          await fs.mkdir(dir, { recursive: true });
        } catch (error) {
          this.logger(`Warning: Could not create directory ${dir}: ${error.message}`);
        }
      }

      const resolvedPath = await OfficeModifier.resolvePath(filePath, allowedDirs);
      const validatedPath = await OfficeModifier.validatePath(resolvedPath, allowedDirs);

      if (!fsSync.existsSync(validatedPath)) {
        throw new Error(`File not found: ${validatedPath}`);
      }

      const ext = path.extname(validatedPath).toLowerCase();
      
      if (ext === '.docx') {
        return await this.handleDocx(validatedPath, operation, { find, replace, content, property, value, tableData });
      } else if (ext === '.xlsx') {
        return await this.handleXlsx(validatedPath, operation, { sheet, cell, value, rowData, insertBeforeRow, columnName, insertBeforeColumn });
      } else {
        throw new Error('File must be .docx or .xlsx');
      }

    } catch (error) {
      this.logger(`Error in office-modifier: ${error.message}`);
      throw error;
    }
  },

  handleDocx: async function (filePath, operation, params) {
    this.introspect(`Processing DOCX: ${path.basename(filePath)}`);
    this.introspect(`Operation: ${operation}`);

    switch (operation) {
      case 'read':
        return await this.readDocx(filePath);
      case 'replace-text':
        return await this.replaceTextDocx(filePath, params.find, params.replace);
      case 'append-content':
        return await this.appendContentDocx(filePath, params.content);
      case 'update-property':
        return await this.updatePropertyDocx(filePath, params.property, params.value);
      case 'add-table':
        return await this.addTableDocx(filePath, params.tableData);
      default:
        throw new Error(`Unknown DOCX operation: ${operation}`);
    }
  },

  handleXlsx: async function (filePath, operation, params) {
    this.introspect(`Processing XLSX: ${path.basename(filePath)}`);
    this.introspect(`Operation: ${operation}`);

    switch (operation) {
      case 'read':
        return await this.readXlsx(filePath, params.sheet);
      case 'update-cell':
        return await this.updateCellXlsx(filePath, params.sheet, params.cell, params.value);
      case 'add-row':
        return await this.addRowXlsx(filePath, params.sheet, params.rowData, params.insertBeforeRow);
      case 'add-column':
        return await this.addColumnXlsx(filePath, params.sheet, params.columnName, params.insertBeforeColumn);
      default:
        throw new Error(`Unknown XLSX operation: ${operation}`);
    }
  },

  // DOCX Operations (keep existing implementation)
  readDocx: async function (filePath) {
    const buffer = await fs.readFile(filePath);
    const zip = new AdmZip(buffer);
    
    const documentXml = zip.readAsText('word/document.xml');
    const paragraphs = this.parseDocumentXml(documentXml);
    
    let metadata = {};
    try {
      const coreXml = zip.readAsText('docProps/core.xml');
      metadata = this.parseCoreProperties(coreXml);
    } catch (e) {
      this.logger('No core properties found');
    }

    this.introspect(`Found ${paragraphs.length} paragraphs`);

    const result = {
      fileName: path.basename(filePath),
      filePath: filePath,
      fileSize: buffer.length,
      metadata: metadata,
      paragraphCount: paragraphs.length,
      content: paragraphs.join('\n\n')
    };

    return `DOCX Contents:\n${JSON.stringify(result, null, 2)}`;
  },

  replaceTextDocx: async function (filePath, findText, replaceText) {
    if (!findText) throw new Error('find parameter required');

    const approval = await this.requestToolApproval({
      payload: { filePath, find: findText, replace: replaceText },
      description: `Replace "${findText}" with "${replaceText}" in ${path.basename(filePath)}?`
    });

    if (!approval.approved) return `Operation cancelled: ${approval.message}`;

    const buffer = await fs.readFile(filePath);
    const zip = new AdmZip(buffer);
    
    const documentXml = zip.readAsText('word/document.xml');
    const escapedFind = findText.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
    const count = (documentXml.match(new RegExp(escapedFind, 'g')) || []).length;
    const modifiedXml = documentXml.split(findText).join(replaceText || '');
    
    zip.updateFile('word/document.xml', Buffer.from(modifiedXml, 'utf8'));
    await fs.writeFile(filePath, zip.toBuffer());

    this.introspect(`Replaced ${count} occurrences`);
    return `Success! Replaced "${findText}" with "${replaceText}" (${count} times) in ${path.basename(filePath)}`;
  },

  appendContentDocx: async function (filePath, newContent) {
    if (!newContent) throw new Error('content parameter required');

    const approval = await this.requestToolApproval({
      payload: { filePath },
      description: `Append content to ${path.basename(filePath)}?`
    });

    if (!approval.approved) return `Operation cancelled: ${approval.message}`;

    const buffer = await fs.readFile(filePath);
    const zip = new AdmZip(buffer);
    const documentXml = zip.readAsText('word/document.xml');
    const newParagraphsXml = this.contentToDocxXml(newContent);
    const modifiedXml = documentXml.replace('</w:body>', `${newParagraphsXml}</w:body>`);
    
    zip.updateFile('word/document.xml', Buffer.from(modifiedXml, 'utf8'));
    await fs.writeFile(filePath, zip.toBuffer());

    this.introspect(`Appended ${newContent.length} characters`);
    return `Success! Appended content to ${path.basename(filePath)}`;
  },

  updatePropertyDocx: async function (filePath, propertyName, propertyValue) {
    const validProps = ['title', 'author', 'subject', 'keywords', 'creator'];
    if (!propertyName || !validProps.includes(propertyName.toLowerCase())) {
      throw new Error(`Invalid property. Valid: ${validProps.join(', ')}`);
    }

    const approval = await this.requestToolApproval({
      payload: { filePath, property: propertyName, value: propertyValue },
      description: `Update ${propertyName} in ${path.basename(filePath)}?`
    });

    if (!approval.approved) return `Operation cancelled: ${approval.message}`;

    const buffer = await fs.readFile(filePath);
    const zip = new AdmZip(buffer);
    
    let coreXml;
    try {
      coreXml = zip.readAsText('docProps/core.xml');
    } catch (e) {
      coreXml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
</cp:coreProperties>`;
    }

    const propMap = { 'title': 'dc:title', 'author': 'dc:creator', 'subject': 'dc:subject', 'keywords': 'cp:keywords', 'creator': 'dc:creator' };
    const tagName = propMap[propertyName.toLowerCase()];
    const modifiedXml = this.updateXmlProperty(coreXml, tagName, propertyValue);
    
    zip.updateFile('docProps/core.xml', Buffer.from(modifiedXml, 'utf8'));
    await fs.writeFile(filePath, zip.toBuffer());

    this.introspect(`Updated ${propertyName}`);
    return `Success! Updated ${propertyName} in ${path.basename(filePath)}`;
  },

  addTableDocx: async function (filePath, tableDataJson) {
    if (!tableDataJson) throw new Error('tableData parameter required');

    let tableData;
    try {
      tableData = JSON.parse(tableDataJson);
    } catch {
      throw new Error('Invalid JSON in tableData');
    }

    if (!tableData.headers || !Array.isArray(tableData.headers)) {
      throw new Error('tableData must have headers array');
    }

    const approval = await this.requestToolApproval({
      payload: { filePath, tableData },
      description: `Add table to ${path.basename(filePath)}?`
    });

    if (!approval.approved) return `Operation cancelled: ${approval.message}`;

    const buffer = await fs.readFile(filePath);
    const zip = new AdmZip(buffer);
    const documentXml = zip.readAsText('word/document.xml');
    const tableXml = this.createTableXml(tableData.headers, tableData.rows || []);
    const modifiedXml = documentXml.replace('</w:body>', `${tableXml}</w:body>`);
    
    zip.updateFile('word/document.xml', Buffer.from(modifiedXml, 'utf8'));
    await fs.writeFile(filePath, zip.toBuffer());

    this.introspect(`Added table with ${tableData.headers.length} columns`);
    return `Success! Added table to ${path.basename(filePath)}`;
  },

  // XLSX Operations using ExcelJS
  readXlsx: async function (filePath, sheetName) {
    const workbook = new ExcelJS.Workbook();
    await workbook.xlsx.readFile(filePath);
    
    let worksheet;
    if (sheetName) {
      worksheet = workbook.getWorksheet(sheetName);
      if (!worksheet) throw new Error(`Sheet "${sheetName}" not found`);
    } else {
      worksheet = workbook.worksheets[0];
    }

    const data = [];
    worksheet.eachRow(function(row, rowNumber) {
      data.push(row.values.slice(1));
    });

    const sheets = workbook.worksheets.map(ws => ws.name);

    this.introspect(`Found ${data.length} rows in "${worksheet.name}"`);

    const result = {
      fileName: path.basename(filePath),
      filePath: filePath,
      sheets: sheets,
      activeSheet: worksheet.name,
      rowCount: data.length,
      data: data
    };

    return `XLSX Contents:\n${JSON.stringify(result, null, 2)}`;
  },

  updateCellXlsx: async function (filePath, sheetName, cellRef, cellValue) {
    if (!cellRef) throw new Error('cell parameter required');

    const approval = await this.requestToolApproval({
      payload: { filePath, sheet: sheetName, cell: cellRef, value: cellValue },
      description: `Update cell ${cellRef} in ${path.basename(filePath)}?`
    });

    if (!approval.approved) return `Operation cancelled: ${approval.message}`;

    const workbook = new ExcelJS.Workbook();
    await workbook.xlsx.readFile(filePath);
    
    let worksheet;
    if (sheetName) {
      worksheet = workbook.getWorksheet(sheetName);
      if (!worksheet) throw new Error(`Sheet "${sheetName}" not found`);
    } else {
      worksheet = workbook.worksheets[0];
    }

    const cell = worksheet.getCell(cellRef);
    cell.value = cellValue;

    await workbook.xlsx.writeFile(filePath);

    this.introspect(`Updated cell ${cellRef}`);
    return `Success! Updated cell ${cellRef} in ${path.basename(filePath)}`;
  },

  addRowXlsx: async function (filePath, sheetName, rowDataJson, insertBeforeRowNum) {
    if (!rowDataJson) throw new Error('rowData parameter required');

    let rowData;
    try {
      rowData = JSON.parse(rowDataJson);
    } catch {
      throw new Error('Invalid JSON in rowData');
    }

    const insertPosition = insertBeforeRowNum ? `before row ${insertBeforeRowNum}` : 'at end';
    const approval = await this.requestToolApproval({
      payload: { filePath, sheet: sheetName, rowData, insertBeforeRow: insertBeforeRowNum },
      description: `Add row ${insertPosition} to ${path.basename(filePath)}?`
    });

    if (!approval.approved) return `Operation cancelled: ${approval.message}`;

    const workbook = new ExcelJS.Workbook();
    await workbook.xlsx.readFile(filePath);
    
    let worksheet;
    if (sheetName) {
      worksheet = workbook.getWorksheet(sheetName);
      if (!worksheet) throw new Error(`Sheet "${sheetName}" not found`);
    } else {
      worksheet = workbook.worksheets[0];
    }

    if (insertBeforeRowNum) {
      const row = worksheet.getRow(insertBeforeRowNum);
      row.values = rowData;
      worksheet.spliceRows(insertBeforeRowNum, 0, rowData);
    } else {
      worksheet.addRow(rowData);
    }

    await workbook.xlsx.writeFile(filePath);

    this.introspect(`Added row with ${rowData.length} cells ${insertPosition}`);
    return `Success! Added row ${insertPosition} to ${path.basename(filePath)}`;
  },

  addColumnXlsx: async function (filePath, sheetName, columnName, insertBeforeColLetter) {
    if (!columnName) throw new Error('columnName parameter required');

    const insertPosition = insertBeforeColLetter ? `before column ${insertBeforeColLetter}` : 'at end';
    const approval = await this.requestToolApproval({
      payload: { filePath, sheet: sheetName, columnName, insertBeforeColumn: insertBeforeColLetter },
      description: `Add column "${columnName}" ${insertPosition} to ${path.basename(filePath)}?`
    });

    if (!approval.approved) return `Operation cancelled: ${approval.message}`;

    const workbook = new ExcelJS.Workbook();
    await workbook.xlsx.readFile(filePath);
    
    let worksheet;
    if (sheetName) {
      worksheet = workbook.getWorksheet(sheetName);
      if (!worksheet) throw new Error(`Sheet "${sheetName}" not found`);
    } else {
      worksheet = workbook.worksheets[0];
    }

    let targetColNum;
    if (insertBeforeColLetter) {
      const colLetter = insertBeforeColLetter.toUpperCase();
      targetColNum = colLetter.charCodeAt(0) - 64;
      worksheet.spliceColumns(targetColNum, 0, [columnName]);
    } else {
      const lastCol = worksheet.columnCount;
      targetColNum = lastCol + 1;
      const headerRow = worksheet.getRow(1);
      headerRow.getCell(targetColNum).value = columnName;
      headerRow.commit();
    }

    await workbook.xlsx.writeFile(filePath);

    this.introspect(`Added column "${columnName}" ${insertPosition}`);
    return `Success! Added column "${columnName}" ${insertPosition} to ${path.basename(filePath)}`;
  },

  // Helper methods for DOCX
  parseDocumentXml: function (xml) {
    const paragraphs = [];
    const paraRegex = /<w:p[^>]*>([\s\S]*?)<\/w:p>/g;
    let paraMatch;

    while ((paraMatch = paraRegex.exec(xml)) !== null) {
      const paraContent = paraMatch[1];
      const textParts = [];
      const textRegex = /<w:t[^>]*>([^<]*)<\/w:t>/g;
      let textMatch;

      while ((textMatch = textRegex.exec(paraContent)) !== null) {
        textParts.push(textMatch[1]);
      }

      if (textParts.length > 0) {
        paragraphs.push(textParts.join(''));
      }
    }

    return paragraphs.filter(p => p.trim().length > 0);
  },

  parseCoreProperties: function (xml) {
    const props = {};
    const propMap = {
      'dc:title': 'title',
      'dc:creator': 'author',
      'dc:subject': 'subject',
      'cp:keywords': 'keywords',
      'dcterms:created': 'created',
      'dcterms:modified': 'modified'
    };

    for (const [tag, name] of Object.entries(propMap)) {
      const regex = new RegExp(`<${tag}[^>]*>([^<]*)</${tag}>`);
      const match = xml.match(regex);
      if (match) props[name] = match[1];
    }

    return props;
  },

  contentToDocxXml: function (content) {
    const paragraphs = content.split('\n').filter(p => p.trim());
    return paragraphs.map(p => 
      `<w:p><w:r><w:t>${this.escapeXml(p)}</w:t></w:r></w:p>`
    ).join('');
  },

  createTableXml: function (headers, rows) {
    const parts = ['<w:tbl>', '<w:tblPr><w:tblStyle w:val="TableGrid"/></w:tblPr>'];
    
    parts.push('<w:tr>');
    headers.forEach(h => parts.push(`<w:tc><w:p><w:r><w:t>${this.escapeXml(h)}</w:t></w:r></w:p></w:tc>`));
    parts.push('</w:tr>');
    
    rows.forEach(row => {
      parts.push('<w:tr>');
      row.forEach(cell => parts.push(`<w:tc><w:p><w:r><w:t>${this.escapeXml(String(cell))}</w:t></w:r></w:p></w:tc>`));
      parts.push('</w:tr>');
    });
    
    parts.push('</w:tbl>');
    return parts.join('');
  },

  updateXmlProperty: function (xml, tagName, value) {
    const escapedValue = this.escapeXml(value);
    const openTag = `<${tagName}>`;
    const closeTag = `</${tagName}>`;
    const regex = new RegExp(`<${tagName}[^>]*>[^<]*</${tagName}>`);
    
    if (regex.test(xml)) {
      return xml.replace(regex, `${openTag}${escapedValue}${closeTag}`);
    } else {
      const insertPoint = xml.indexOf('</cp:coreProperties>');
      if (insertPoint === -1) throw new Error('Invalid core properties XML');
      return xml.slice(0, insertPoint) + `${openTag}${escapedValue}${closeTag}` + xml.slice(insertPoint);
    }
  },

  escapeXml: function (text) {
    if (!text) return '';
    return String(text)
      .replace(/&/g, '&amp;')
      .replace(/</g, '&lt;')
      .replace(/>/g, '&gt;')
      .replace(/"/g, '&quot;')
      .replace(/'/g, '&apos;');
  }
};
