/**
* Organization Initialization Service
*
* This service handles the complete initialization of organizations in the CommTool system.
* It provides database creation, organization setup, and system user configuration.
*
* Key responsibilities:
* - Database schema creation and initialization
* - Organization object creation with proper hierarchy
* - System user and super admin setup
* - Visibility filters and permission configuration
* - Multi-tenant organization isolation
*
* Database components initialized:
* - ObjectBase: Core objects (organizations, users, jobs)
* - Member: Member-specific data and display information
* - Links: Relationships between objects (memberSys links)
* - Visible: Visibility permissions for users
*
* @module OrganizationInitialization
*/
import {getUID, isValidUID} from '../utils/UUIDs.js'
import { superAdminData } from './superAdminJob.js';
import { errorLogger } from '../utils/requestLogger.js';
import { parseTimestampToSeconds } from '../utils/parseTimestamp.js';
import fs from 'fs';
import { group } from 'console';
const {query,transaction,getConnection,connection,UUID2hex,HEX2uuid} = await import('@commtool/sql-query')
const {getConfig, configs} = await import('../utils/compileTemplates.js')
const { renderObject } =await import('../utils/renderTemplates.js');
const { queueAdd} =await import('../tree/treeQueue/treeQueue.js')
const fsPromises = fs.promises;
/**
* Utility function to read files asynchronously
*
* @param {string} path - File path to read
* @returns {Promise<string>} File contents as UTF-8 string
*/
function readFile(path)
{
return fsPromises.readFile(path, 'utf8')
}
/**
* Create database and initialize schema
* sole use-case: running tests on a fresh database
* This function creates a new database if it doesn't exist and initializes
* all required tables, views, and functions for the CommTool system.
*
* @param {string} dataBase - Database name to create/initialize
* @returns {Promise<void>} Promise that resolves when database is ready
*
* Initialization process:
* 1. Check if database exists, create if not
* 2. Initialize tables from SQL schema files
* 3. Create database views for complex queries
* 4. Set up stored functions and procedures
*
* SQL files executed:
* - initTables.sql: Core table structure (ObjectBase, Member, Links, etc.)
* - createViews.sql: Database views for reporting and queries
* - initFunctions.sql: Stored procedures and functions
*
* Note: Uses multipleStatements connection option for SQL script execution
*/
export const createDB= (dataBase)=>
(
new Promise(async fullfill=>
{
// Create database connection without specifying database
const ac = await getConnection({database:undefined})
// Check if the target database already exists
const dbExists = await ac.query(`SELECT SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = ?`, [dataBase])
// Create database if it doesn't exist
if (dbExists.length === 0) {
await ac.query(`
CREATE DATABASE ${dataBase} ;
`)
}
await ac.close()
console.log('createDB started')
// Create the database schema in one transaction for consistency
await connection(async (connection)=>
{
// Switch to the target database
await connection.query(`use \`${dataBase}\`;`)
// Check if core tables already exist to avoid recreation
const exists=await connection.query(`SHOW TABLES LIKE 'ObjectBase'`,[])
if(exists.length===0)
{
// Initialize core database tables
const initTables= await readFile('./src/config/sql/initTables.sql')
await connection.query(`use \`${dataBase}\`;\n`+initTables)
// Create database views for complex queries
const initViews= await readFile('./src/config/sql/createViews.sql')
await connection.query(`use \`${dataBase}\`;\n`+initViews)
// Set up stored functions and procedures
const initFunctions= await readFile('./src/config/sql/initFunctions.sql')
await connection.query(`use \`${dataBase}\`;\n`+initFunctions)
}
},
// Enable multiple statements for SQL script execution
{connectionOptions:{multipleStatements:true, database:dataBase}})
fullfill()
console.log('createDB finsished')
})
)
/**
* Check if organization exists and create if necessary
*
* This function verifies if an organization exists in the database.
* If not found, it automatically creates the organization with default
* settings and proper initialization.
*
* @param {string} UID - Organization UUID to check/create
* @returns {Promise<Object>} Response object with success status and message
*
* Process:
* 1. Query ObjectBase for existing organization (Type='group')
* 2. If not found, call initOrga() with default parameters
* 3. Return success status and appropriate message
*
* Default organization settings:
* - hierarchie: 1 (top-level organization)
* - stage: 0 (initial stage)
* - gender: 'C' (company/organization type)
* - belongsTo: self-referencing (organization owns itself)
*
* Error handling:
* - Logs errors for monitoring and debugging
* - Returns structured error response with details
*/
export const checkOrgaExists = async (UID) => {
try {
// Check if organization already exists in ObjectBase
// Type='group' identifies organization records
const exists = await query(`SELECT UID FROM ObjectBase WHERE UID=? AND Type='group'`, [UUID2hex(UID)])
if (exists.length === 0) {
// Organization doesn't exist - create it with default settings
// Try to load config for the org so we can respect the configured founded date
const cfg = await getConfig('db', { session: { root: UID } }).catch(() => null);
let ts = Date.now();
if (cfg && cfg.founded) {
const parsed = Date.parse(cfg.founded);
if (!Number.isNaN(parsed)) {
ts = parsed;
}
else {
// Support DD.MM.YYYY format used in some config files (e.g., "15.02.1976")
const m = String(cfg.founded).match(/^(\d{2})\.(\d{2})\.(\d{4})$/);
if (m) {
ts = Date.parse(`${m[3]}-${m[2]}-${m[1]}`);
}
}
}
return await initOrga({
body:{
UID:UID,
hierarchie:1, // Top-level organization
stage:0, // Initial organization stage
gender:'C' // Company/Corporation type
},
query:{
timestamp:ts,
belongsTo:UID // Organization belongs to itself
},
session:{
root:UID // Set as root organization
}
})
}
else
{
// Organization already exists
return {success:true, message:'orga already exists'}
}
}
catch (e) {
// Log error for monitoring and return structured error response
errorLogger(e)
return {success:false, message:'Error checking orga existence', error:e.message}
}
}
/**
* Initialize a new organization with complete setup
*
* This is the core function that creates a new organization with all required
* components: the organization object, system users, permissions, and filters.
* It sets up a complete multi-tenant environment for the organization.
*
* @param {Object} req - Express-like request object with body, query, and session
* @param {Object} req.body - 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) (for backdating)
* @param {Object} req.session - Session data (root organization)
* @returns {Promise<Object>} Response with created organization data
*
* Complete initialization process:
* 1. Generate unique UIDs for organization and system components
* 2. Load configuration templates for organization display
* 3. Render organization object with template engine
* 4. Create organization record in ObjectBase and Member tables
* 5. Set up system user (BOT user) for automated operations
* 6. Create super admin job with full permissions
* 7. Configure visibility filters for access control
* 8. Establish proper relationships via Links table
* 9. Initialize tree queue for hierarchical operations
*
* Database tables affected:
* - ObjectBase: Main object storage (org, extern user, job, filter)
* - Member: Member-specific data and display information
* - Links: Relationships (memberSys links for hierarchy), which as well labels this entry as an organization
* - Visible: Visibility permissions for users
*
* Security features:
* - Creates isolated system user per organization
* - Sets up super admin with full visibility filter
* - Establishes proper multi-tenant boundaries
* - Configures hierarchical permission structure
*
* Template system:
* - Uses Jinja2-style templates for object rendering
* - Supports organization-specific configuration overrides
* - Handles internationalization and display formatting
*/
export const initOrga =async (req)=>
{
try {
// Generate unique UID for this organization
const UID=await getUID(req)
req.session.root=req.body.UID
// Default organization template configuration
let config ={}
let template=
{
Title: "Organisation",
// Dynamic display using template variables
Display: "{% if config.Orga %}{{config.Orga}}{% if config.Location %}-{% endif %}{% endif %}{% if config.Location %}{{config.Location}}{% endif %}",
// Sorting index for hierarchical display
SortIndex: "{{hierarchie}} {{config.Orga}} {{stage}}_{{gender}} {{name}}",
// Full-text search indexing
FullTextIndex: "Orga {{config.Orga}} {{config.Location}}",
hierarchie : "{{hierarchie}}",
stage : "{{stage}}",
gender : "{{gender}}",
}
// Load organization-specific configuration if available
const myConfig=await getConfig('db',req)
if(myConfig)
{
config=myConfig
// Use custom template if defined, otherwise use default
template=config.ObjectTemplates?.group ? config.ObjectTemplates.group : template
}
else
{
console.warn(`[initOrga] No config found for org ${req.body.UID}, using defaults`)
}
// if we have a gender in the config, this alway overwrites the passed value
if(config.OrgaGender)
{
req.body.gender=config.OrgaGender
}
// Render organization object using template engine
// Combines template with actual data to generate display strings
let groupData = {UID, hierarchie:1,stage:0, gender:req.body.gender || 'C',name:config?.Orga || 'please enter orga name', location:config?.Location || ''}
const object=await renderObject(template,groupData,req)
// Determine organization ownership (normally self-referencing)
const belongsTo= req.body.belongsTo ? UUID2hex(req.body.belongsTo) : UUID2hex(req.body.UID)
// Validate UID format to prevent database corruption
if(!isValidUID(req.body.UID))
{
return {success:false,message:'invalid UID format in body.UID'}
}
// Parse timestamp for backdating operations (testing/migration) or take it from config
const timestamp = parseTimestampToSeconds(req.query.timestamp) ?? (config?.founded ? Date.parse(config.founded)/1000 : null)
// Check if organization already exists to determine create vs update
const exists=await query(`SELECT UID FROM ObjectBase WHERE UID=?`,[UID])
// Generate unique UIDs for system components
// In testing, UIDs can be provided for reproducible tests
// In production, they are automatically generated
let UIDExtern,UIDJob,UIDFilter
if(!req.body.testUIDs)
{
// Generate new UUIDs using database UIDV1() function
[{UIDExtern,UIDJob,UIDFilter}]=await query(`SELECT UIDV1() AS UIDExtern,UIDV1() AS UIDJob,UIDV1() AS UIDFilter`,[])
}
else
{
// Use provided test UIDs for deterministic testing
[UIDExtern,UIDJob,UIDFilter]=req.body.testUIDs.map(UID=>UUID2hex(UID))
}
// Execute all organization setup in a single transaction for consistency
await transaction(async (connection)=>
{
// Prepare organization data (exclude UID from data to avoid duplication)
groupData={...groupData,UID:undefined}
// Create/update organization record in ObjectBase
// Uses upsert pattern for idempotent operations
await connection.query(`
INSERT INTO ObjectBase(UID,Type,UIDBelongsTo,Title,dindex,hierarchie,stage,gender,Data)
VALUES (?,'group',?,?,?,?,?,?,?)
ON DUPLICATE KEY UPDATE Title=VALUE(Title),
dindex=VALUE(dindex),hierarchie=VALUE(hierarchie),stage=VALUE(stage),gender=VALUE(gender)`,
[object.UID,belongsTo,object.Title,0,
1,object.stage,object.gender,JSON.stringify({root:true})],{log:false})
// Create/update member record with display information
// Member table stores presentation and search data
await query( `INSERT INTO Member
(UID,Display,SortName,FullTextIndex, Data)
VALUES(?,?,?,?,?)
ON DUPLICATE KEY UPDATE Display=VALUE(Display),SortName=VALUE(SortName),FullTextIndex=VALUE(FullTextIndex),Data=VALUE(Data)`,
[UID,object.Display,object.SortIndex,object.FullTextIndex,JSON.stringify(groupData)])
// Only create system components for new organizations
if(exists.length===0)
{
// Create self-referencing memberSys link for tree hierarchy
// This allows the organization to appear in its own member tree
await query(`INSERT INTO Links (UID,Type,UIDTarget) VALUES(?,?,?)`,[UID,'memberSys',UID])
// Create system user (BOT user) for automated operations
// This user performs system tasks on behalf of the organization
await connection.query(`INSERT INTO Member (UID,Display,SortName, FullTextIndex,Data)
VALUES(?,?,'@@','',?)`,
[UIDExtern,object.Display,JSON.stringify({firstName:'BOT user:',lastName:object.Display})]
)
// Add system user to ObjectBase with extern type
// High dindex (9999) ensures it appears last in lists
await connection.query(`INSERT INTO ObjectBase (UID,UIDBelongsTo,Type,Title,dindex,hierarchie,stage,gender)
VALUES(?,?,'extern','@@SuperAdmin',9999,1,0,'M')`,
[UIDExtern,UIDExtern])
// Link system user to organization as a member
// This establishes the relationship for permissions and visibility
await connection.query(`INSERT INTO Links (UID,Type,UIDTarget) VALUES(?,'memberSys',?)`,[UIDExtern,UID])
// Register system user in application configurations
// This makes the system user available to all apps for this org
Object.values(configs).forEach(app=>
{
if(app[UID])
{
app[UID].sysUser=UIDExtern
}
})
// Create super admin job with full system privileges
// This job defines the capabilities of the system user
superAdminData.group=groupData
await connection.query(`INSERT INTO ObjectBase(UID,UIDBelongsTo,Type,Title,dindex,hierarchie,stage,gender,Data)
VALUES(?,?,'job','@@SuperAdmin',9999,1,0,'M',?)`,
[UIDJob,UIDExtern,JSON.stringify(superAdminData)])
// Link super admin job to organization
await connection.query(`INSERT INTO Links (UID,Type,UIDTarget) VALUES(?,'memberSys',?)`,[UIDJob,UID])
// Create comprehensive visibility filter for system user
// This filter grants the system user access to all object types
// Essential for system operations and administrative tasks
const filter= {
"person":{"all":null}, // Access to all persons
"group":{"all":null}, // Access to all groups/organizations
"job":{"all":null}, // Access to all jobs/roles
"guest":{"all":null}, // Access to all guests
"extern":{"all":null}, // Access to all external users
"event":{"all":null} // Access to all events
}
// Create visibility filter object in database
// Type='changeable' indicates this is a configurable filter
await connection.query(`
INSERT INTO ObjectBase(UID,Type,UIDBelongsTo,Title,Display,Data)
VALUES (?,'changeable',?,?,?,?)
`,
[
UIDFilter,UID,'super user Visiblility Filter',`${object.Display}`,JSON.stringify(filter)
]
)
// Establish direct changeable permissions for system user
// These grant immediate access without waiting for filter processing
// Grant system user changeable to the organization itself
await query(`INSERT INTO Visible (UID,Type,UIDUser) VALUES(?,'changeable',?)`,[UID,UIDExtern])
// Grant system user changeable to the super admin job
await query(`INSERT INTO Visible (UID,Type,UIDUser) VALUES(?,'changeable',?)`,[UIDJob,UIDExtern])
// Add visibility filter to processing queue
// This will apply the comprehensive filter rules to the system user
// Parameters: targetUID, userUID, type, filterUID, orgUID, parentUID, jobUID
queueAdd(UID,UIDExtern, 'changeable', UIDFilter,UID,null,UIDJob)
// publish to the bots, that we have a new organization
const { publishEvent } = await import('../utils/events.js');
publishEvent('/add/organization',{organization: HEX2uuid(UID), data: HEX2uuid(UID)})
}
},
// Support backdating for data migration and testing scenarios
{backDate:timestamp})
// Return success response with complete organization data
return {success:true,result:{...object,UID: HEX2uuid(object.UID),Data:{...req.body,hierarchie:1}}}
}
catch(e)
{
// Log any errors that occur during organization initialization
errorLogger(e)
return {success:false, error: e.message}
}
}