startersaas.pro-node-api-main/api/workspaces/workspace.service.js

// api/workspaces/workspace.service.js
import * as fs from "fs/promises";
import * as path from "path";
import mysql from "mysql2/promise";
import mongoose from "mongoose";
import BaseService from "../../services/base.service.js";
import Workspace from "./workspace.model.js";

class WorkspaceService extends BaseService {
  getModel() {
    return Workspace;
  }

  async create(data, userId, accountId) {
    data.userId = userId;
    data.accountId = accountId;
    
    // Generate a new ObjectId for each nested item that doesn't have one
    if (data.contacts && data.contacts.length > 0) {
      data.contacts = data.contacts.map(contact => {
        if (!contact.contactId) contact.contactId = new mongoose.Types.ObjectId();
        return contact;
      });
    }
    
    if (data.projects && data.projects.length > 0) {
      data.projects = data.projects.map(project => {
        if (!project.projectId) project.projectId = new mongoose.Types.ObjectId();
        return project;
      });
    }
    
    if (data.meetings && data.meetings.length > 0) {
      data.meetings = data.meetings.map(meeting => {
        if (!meeting.meetingId) meeting.meetingId = new mongoose.Types.ObjectId();
        return meeting;
      });
    }
    
    if (data.notes && data.notes.length > 0) {
      data.notes = data.notes.map(note => {
        if (!note.noteId) note.noteId = new mongoose.Types.ObjectId();
        return note;
      });
    }
    
    if (data.tasks && data.tasks.length > 0) {
      data.tasks = data.tasks.map(task => {
        if (!task.taskId) task.taskId = new mongoose.Types.ObjectId();
        return task;
      });
    }
    
    const workspace = new Workspace(data);
    await workspace.save();
    return workspace.toObject();
  }

  async update(id, userId, accountId, data) {
    // Generate ObjectIds for any new nested items that don't have IDs
    this._ensureNestedIds(data);
    
    // Update the workspace
    return this.getModel().findOneAndUpdate(
      { 
        _id: id, 
        $or: [
          { userId: userId },
          { ownerId: userId },
          { "members.userId": userId }
        ],
        accountId: accountId 
      },
      data,
      { new: true }
    );
  }

  // Helper to ensure all nested items have proper IDs
  _ensureNestedIds(data) {
    const ensureIds = (items, idField) => {
      if (items && items.length > 0) {
        return items.map(item => {
          if (!item[idField]) item[idField] = new mongoose.Types.ObjectId();
          return item;
        });
      }
      return items;
    };
    
    if (data.contacts) data.contacts = ensureIds(data.contacts, 'contactId');
    if (data.projects) data.projects = ensureIds(data.projects, 'projectId');
    if (data.meetings) data.meetings = ensureIds(data.meetings, 'meetingId');
    if (data.notes) data.notes = ensureIds(data.notes, 'noteId');
    if (data.tasks) data.tasks = ensureIds(data.tasks, 'taskId');
  }

  async delete(id, userId, accountId) {
    return this.getModel().findOneAndDelete({ 
      _id: id, 
      $or: [
        { userId: userId },
        { ownerId: userId },
        { "members.userId": userId, "members.role": "admin" }
      ],
      accountId: accountId
    });
  }

  async findByIdAndUser(id, userId, accountId) {
    const workspace = await this.getModel().findOne({
      _id: id,
      $or: [
        { userId: userId },
        { ownerId: userId },
        { "members.userId": userId },
        { "teams._id": { $in: userId.teams?.map(team => team._id) || [] } }
      ],
      accountId: accountId
    }).lean();
    
    return workspace;
  }

  // All existing methods for file handling remain the same
  async getWorkspaceFilePath(name) {
    const workspacesDir = process.env.WORKSPACES_FILES_DIRECTORY || path.join(process.cwd(), 'workspace-files');
    return path.join(workspacesDir, name);
  }

  async workspaceFileExists(filePath) {
    try {
      await fs.access(filePath, fs.constants.F_OK);
      return true;
    } catch (error) {
      return false;
    }
  }

  isValidWorkspaceFileName(name) {
    const validNameRegex = /^[a-zA-Z0-9_-]+\.(pdf|txt|csv|json|xml|zip|tar|gz|xlsx|docx|png|jpg|jpeg|svg)$/;
    return validNameRegex.test(name) && !name.includes('..');
  }

  getContentType(filename) {
    const extension = path.extname(filename).toLowerCase();
    const contentTypes = {
      '.pdf': 'application/pdf',
      '.txt': 'text/plain',
      '.csv': 'text/csv',
      '.json': 'application/json',
      '.xml': 'application/xml',
      '.zip': 'application/zip',
      '.tar': 'application/x-tar',
      '.gz': 'application/gzip',
      '.xlsx': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
      '.docx': 'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
      '.png': 'image/png',
      '.jpg': 'image/jpeg',
      '.jpeg': 'image/jpeg',
      '.svg': 'image/svg+xml'
    };
    
    return contentTypes[extension] || 'application/octet-stream';
  }
  
  // All existing MySQL database methods remain unchanged
  async createDatabaseConnection(workspace) {
    if (!workspace.mysqlHost || !workspace.mysqlUser || !workspace.mysqlDatabase) {
      throw new Error("Missing required database connection information");
    }
    
    try {
      const connection = await mysql.createConnection({
        host: workspace.mysqlHost,
        port: workspace.mysqlPort || 3306,
        user: workspace.mysqlUser,
        password: workspace.mysqlPassword || '',
        database: workspace.mysqlDatabase,
        connectTimeout: 10000
      });
      
      return connection;
    } catch (error) {
      throw new Error(`Database connection error: ${error.message}`);
    }
  }
  
  async getTableData(workspaceId, userId, accountId, limit = 100) {
    const workspace = await this.findByIdAndUser(workspaceId, userId, accountId);
    
    if (!workspace) {
      throw new Error("Workspace not found");
    }
    
    if (!workspace.mysqlTable) {
      throw new Error("No table specified in workspace");
    }
    
    const connection = await this.createDatabaseConnection(workspace);
    
    try {
      const [rows, fields] = await connection.execute(
        `SELECT * FROM \`${workspace.mysqlTable}\` LIMIT ?`, 
        [limit]
      );
      
      await this.update(
        workspace._id,
        userId,
        accountId,
        { lastAccessed: new Date() }
      );
      
      return {
        rows,
        columns: fields.map(field => ({
          name: field.name,
          type: field.type,
          length: field.length
        }))
      };
    } catch (error) {
      throw new Error(`Database query error: ${error.message}`);
    } finally {
      await connection.end();
    }
  }
  
  async listTables(workspaceId, userId, accountId) {
    const workspace = await this.findByIdAndUser(workspaceId, userId, accountId);
    
    if (!workspace) {
      throw new Error("Workspace not found");
    }
    
    const connection = await this.createDatabaseConnection(workspace);
    
    try {
      const [rows] = await connection.execute(
        `SHOW TABLES FROM \`${workspace.mysqlDatabase}\``
      );
      
      await this.update(
        workspace._id,
        userId,
        accountId,
        { lastAccessed: new Date() }
      );
      
      const tableNames = rows.map(row => Object.values(row)[0]);
      
      return tableNames;
    } catch (error) {
      throw new Error(`Error listing tables: ${error.message}`);
    } finally {
      await connection.end();
    }
  }
  
  async testDatabaseConnection(workspaceId, userId, accountId) {
    const workspace = await this.findByIdAndUser(workspaceId, userId, accountId);
    
    if (!workspace) {
      throw new Error("Workspace not found");
    }
    
    const connection = await this.createDatabaseConnection(workspace);
    
    try {
      const [result] = await connection.execute('SELECT 1 AS connection_test');
      const [serverInfo] = await connection.execute('SELECT VERSION() AS version');
      
      return {
        connected: true,
        serverVersion: serverInfo[0].version
      };
    } catch (error) {
      throw new Error(`Connection test failed: ${error.message}`);
    } finally {
      await connection.end();
    }
  }
}

export default new WorkspaceService();