/**
* Organization Service Layer
*
* This service handles all database operations related to organizations in the
* CommTool system. Organizations are the top-level entities in the multi-tenant
* architecture, providing isolation and context for all other objects.
*
* Key concepts:
* - Organizations are stored as Type='group' with hierarchie=1
* - Each organization has a self-referencing memberSys link
* - Organizations can have super admin users for system operations
* - Multi-tenant isolation is enforced at the database level
*
* Database tables involved:
* - ObjectBase: Core organization records
* - Member: Display and search information
* - Links: Relationships and membership
*
* @module OrganizationService
*/
import { query, UUID2hex, HEX2uuid } from '@commtool/sql-query';
import { errorLoggerRead, errorLoggerUpdate } from '../../utils/requestLogger.js';
import { initOrga as configInitOrga } from '../../config/initOrga.js';
/**
* Initialize a new organization with complete setup
*
* This function delegates to the comprehensive organization initialization
* process that creates the organization, system users, permissions, and
* all required components for a fully functional multi-tenant environment.
*
* @param {Object} req - Express request object with organization data
* @param {Object} req.body - Organization configuration data
* @param {string} req.body.UID - Organization UUID
* @param {string} req.body.gender - Organization gender (Optional, default:C=combined:combination of male and female sub groups, BelongsTo: the UID of the parent organization)
* @param {Object} req.query - Query parameters (timestamp)
* @param {Object} req.session - Session data (for setting root organization)
* @returns {Promise<Object>} Response with created organization data
*
* Delegates to: configInitOrga() in config/initOrga.js
* Creates: Organization, system user, super admin, visibility filters
*/
export const initOrga = async (req) => {
return await configInitOrga(req);
};
/**
* Get super admin user for an organization
*
* Retrieves the system user (super admin) that was created for the organization
* during initialization. This user is used for automated operations and system
* tasks within the organization's context.
*
* @param {string|Buffer} orga - Organization UID (in hex format)
* @returns {Promise<string|false>} Super admin UID or false if not found
*
* Database query:
* - Searches Links table for memberSys relationships
* - Links.UIDTarget = organization UID
* - Links.UID = super admin UID
* - Type='memberSys' identifies member system relationships
*
* Note: Returns the first matching UID, which should be the system user
* created during organization initialization.
*/
export const getSuperAdmin = async (orga) => {
try {
// Query for users linked to this organization via memberSys relationships
const result = await query(`SELECT Links.UID
FROM Links
INNER JOIN ObjectBase AS Admin ON (Admin.UID=Links.UID AND Admin.Type='extern')
WHERE Links.UIDTarget= ? AND Links.Type='memberSys'`, [UUID2hex(orga)], { cast: ['UUID'] });
if (result.length > 0) {
// Return the first super admin UID found (converted from hex to UUID)
return HEX2uuid(result[0].UID);
} else {
// No super admin found for this organization
return false;
}
} catch (e) {
errorLoggerRead(e);
return false;
}
};
/**
* Get organizations by their UIDs
*
* Retrieves organization details for a specific set of organization UIDs.
* This is used for batch operations and when you need organization information
* for multiple organizations at once.
*
* @param {string[]} orgaUIDs - Array of organization UUIDs to retrieve
* @returns {Promise<Object[]>} Array of organization objects with UID, Title, Display
*
* Database query details:
* - INNER JOIN between ObjectBase and Member tables
* - Filters for Type='group' AND hierarchie=1 (top-level organizations)
* - Self-referencing check: UID=UIDBelongsTo (organizations own themselves)
* - Uses IN clause for efficient multi-UID lookup
*
* Returns:
* - UID: Organization unique identifier
* - Title: Organization title from ObjectBase
* - Display: Formatted display name from Member table
*
* Error handling: Throws exception on database errors for proper error propagation
*/
export const getOrganizationsByUIDs = async (orgaUIDs) => {
try {
// Convert UUIDs to hex format and query multiple organizations
const result = await query(`SELECT ObjectBase.UID, ObjectBase.Title, Member.Display, userLink.UID AS UIDlogin FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UID)
LEFT JOIN Links AS userLink ON (userLink.UIDTarget=ObjectBase.UID AND userLink.Type='identifyer' )
WHERE ObjectBase.Type='group' AND ObjectBase.hierarchie=1 AND ObjectBase.UID=ObjectBase.UIDBelongsTo AND ObjectBase.UID IN(?)`,
[orgaUIDs.map(o => UUID2hex(o))], { cast: ['UUID'] });
return result;
} catch (e) {
errorLoggerUpdate(e);
throw e;
}
};
/**
* Get all organizations in the system
*
* Retrieves a complete list of all organizations that are properly initialized
* and have the required self-referencing memberSys link. This is used for
* system administration, reporting, and multi-tenant operations.
*
* @returns {Promise<Object[]>} Array of all organization objects
*
* Database query details:
* - Triple JOIN: ObjectBase + Member + Links
* - ObjectBase: Core organization data (Type='group', hierarchie=1)
* - Member: Display names and presentation data
* - Links: Self-referencing memberSys links (ensures proper initialization)
*
* Link validation:
* - Links.UIDTarget = ObjectBase.UID (points to organization)
* - Links.UID = ObjectBase.UID (self-referencing)
* - Links.Type = 'memberSys' (member system relationship)
*
* This ensures only properly initialized organizations are returned,
* filtering out any incomplete or corrupted organization records.
*
* Returns:
* - UID: Organization unique identifier
* - Title: Organization title from ObjectBase
* - Display: Formatted display name from Member table
* - UIDlogin: UUID of the user link associated with the organization
*
* Error handling: Throws exception for proper error propagation to controllers
*/
export const getAllOrganizations = async () => {
try {
// Query all properly initialized organizations with their display data
const result = await query(`SELECT ObjectBase.UID, ObjectBase.Title, Member.Display, userLink.UID AS UIDlogin FROM ObjectBase
INNER JOIN Member ON (Member.UID=ObjectBase.UID)
INNER JOIN Links ON (Links.UIDTarget=ObjectBase.UID AND Links.Type='memberSys' AND Links.UID=ObjectBase.UID)
LEFT JOIN Links AS userLink ON (userLink.UIDTarget=ObjectBase.UID AND userLink.Type='identifyer')
WHERE ObjectBase.Type='group' AND ObjectBase.hierarchie=1`, [], { cast: ['UUID'] });
return result;
} catch (e) {
errorLoggerRead(e);
throw e;
}
};
/**
* Update organization data with property management
*
* This function provides comprehensive organization data management with support
* for adding, updating, and removing properties. It handles null values as
* deletion indicators and merges changes with existing data.
*
* @param {string} orgaUID - Organization UUID to update
* @param {Object} data - Data object with properties to update
* - Properties with null values will be removed
* - Properties with non-null values will be added/updated
* @returns {Promise<Object>} Detailed result object with success status and changes
*
* Process flow:
* 1. Validate organization existence (404 if not found)
* 2. Parse existing organization data from ObjectBase.Data
* 3. Process incoming data properties:
* - null values: Remove property from organization data
* - non-null values: Add or update property
* 4. Update database with merged data
* 5. Return detailed change summary
*
* Database operation:
* - Updates ObjectBase.Data field with JSON string
* - Filters by UID + Type='group' + hierarchie=1 for safety
*
* Response structure:
* - success: Boolean operation status
* - message: Human-readable result description
* - data: Final organization data after changes
* - changes: Detailed breakdown of modifications
* - added: Properties that were newly created
* - updated: Properties that were modified
* - removed: Properties that were deleted (null values)
*
* Error handling:
* - 404: Organization not found
* - 500: Database or parsing errors
* - Graceful handling of malformed existing data
*
* Use cases:
* - Organization configuration updates
* - Feature flag management
* - Custom organization settings
* - Bulk property operations
*/
export const updateOrganizationData = async (orgaUID, data) => {
try {
// Convert organization UID to hex format for database operations
const orgaHex = UUID2hex(orgaUID);
// Verify organization exists before attempting update
// Query includes hierarchie=1 to ensure we're targeting top-level organizations
const existingOrg = await query(
`SELECT ObjectBase.UID, ObjectBase.Data
FROM ObjectBase
WHERE ObjectBase.UID = ? AND ObjectBase.Type = 'group' AND ObjectBase.hierarchie = 1`,
[orgaHex]
);
// Return 404 error if organization is not found
if (existingOrg.length === 0) {
return {
success: false,
status: 404,
message: `Organization with UID ${orgaUID} not found`
};
}
// Initialize data structures for merging operation
let finalData = {};
let existingData = {};
// Parse existing organization data with error handling
if (existingOrg[0].Data) {
try {
existingData = JSON.parse(existingOrg[0].Data);
// Start with existing data as base for merging
finalData = { ...existingData };
} catch (e) {
console.warn(`Failed to parse existing data for organization ${orgaUID}:`, e.message);
// If parsing fails, start fresh (corrupted data recovery)
}
}
// Process each property in the incoming data object
Object.entries(data).forEach(([key, value]) => {
if (value === null) {
// Null values indicate property deletion
delete finalData[key];
} else {
// Non-null values are added or updated
finalData[key] = value;
}
});
// Update organization data in database
await query(
`UPDATE ObjectBase
SET Data = ?
WHERE UID = ? AND Type = 'group' AND hierarchie = 1`,
[JSON.stringify(finalData), orgaHex]
);
// Return detailed success response with change summary
return {
success: true,
message: 'Organization data updated successfully',
data: finalData,
changes: {
// Properties that were newly added (didn't exist before)
added: Object.keys(data).filter(key =>
data[key] !== null && existingData[key] === undefined
),
// Properties that were modified (existed before with different value)
updated: Object.keys(data).filter(key =>
data[key] !== null && existingData[key] !== undefined
),
// Properties that were removed (null value for existing property)
removed: Object.keys(data).filter(key =>
data[key] === null && existingData[key] !== undefined
)
}
};
} catch (e) {
// Log database errors and return structured error response
errorLoggerUpdate(e);
return {
success: false,
status: 500,
message: e.message
};
}
};
/** * Update organization login UID
*
* This function updates the login UID associated with an organization.
* The login UID typically represents the keycloak user ID for the organization.
* It manages the 'identifyer' link type in the Links table to establish
*
* @param {string} orgaUID - Organization UUID to update
* @param {string} loginUID - New login UUID to associate with the organization
* @returns {Promise<Object>} Result object with success status and message
*
* * Process flow:
* 1. Validate organization existence (404 if not found)
* 2. Check for existing 'identifyer' link and update or create as necessary
* 3. Return operation result
*
* Database operations:
* - SELECT to verify organization existence
* - INSERT or UPDATE in Links table for 'identifyer' link type
*
* Response structure:
* - success: Boolean operation status
* - message: Human-readable result description
*
* Error handling:
* - 404: Organization not found
* - 500: Database errors
*/
export const updateOrganizationLogin = async (orgaUID, loginUID) => {
try {
const orgaHex = UUID2hex(orgaUID);
const loginHex = UUID2hex(loginUID);
// Verify organization exists
const existingOrg = await query(
`SELECT ObjectBase.UID FROM ObjectBase
INNER JOIN Links ON (Links.UID =ObjectBase.UID AND Links.Type='memberSys' AND Links.UIDTarget=ObjectBase.UID)
WHERE ObjectBase.UID = ? AND ObjectBase.Type = 'group' AND ObjectBase.hierarchie = 1`,
[orgaHex]
);
if (existingOrg.length === 0) {
return {
success: false,
status: 404,
message: `Organization with UID ${orgaUID} not found`
};
}
// remove existing 'identifyer' link for the organization
await query(
`DELETE FROM Links WHERE UIDTarget = ? AND Type = 'identifyer'`,
[orgaHex]
);
// Insert or update the 'identifyer' link for the organization
await query(
`INSERT IGNORE INTO Links (UID, UIDTarget, Type) VALUES (?, ?, 'identifyer')`,
[loginHex, orgaHex]
);
return {
success: true,
message: `Organization login UID updated successfully`
};
} catch (e) {
errorLoggerUpdate(e);
return {
success: false,
status: 500,
message: e.message
};
}
}