School Management

// === School Management System Tables in ServiceNow with Admin Role Assignments ===
var appMenuName = "School Management";
var appOrder = 100;
var moduleOrder = 50;
var adminRole = "admin"; // Role to be assigned

// === STEP 1: CREATE APPLICATION MENU ===
var appCheck = new GlideRecord('sys_app_application');
appCheck.addQuery('title', appMenuName);
appCheck.query();

var appSysId;
if (appCheck.next()) {
    gs.info("Application menu already exists: " + appMenuName);
    appSysId = appCheck.getValue('sys_id');
} else {
    var appMenu = new GlideRecord('sys_app_application');
    appMenu.initialize();
    appMenu.title = appMenuName;
    appMenu.active = true;
    appMenu.order = appOrder;
    appSysId = appMenu.insert();
    gs.info("Application menu created: " + appMenuName);
}


// === TABLE CREATION FUNCTION ===
function createTable(tableName, tableLabel, fields) {
    var tableCheck = new GlideRecord('sys_db_object');
    tableCheck.addQuery('name', tableName);
    tableCheck.query();

    var tableSysId;
    if (tableCheck.next()) {
        gs.info("Table already exists: " + tableName);
        tableSysId = tableCheck.getValue('sys_id');
    } else {
        var table = new GlideRecord('sys_db_object');
        table.initialize();
        table.name = tableName;
        table.label = tableLabel;
        table.is_extendable = true;
        tableSysId = table.insert();

        if (tableSysId) {
            gs.info("Table created: " + tableName);
            for (var i = 0; i < fields.length; i++) {
                var field = fields[i];
                addColumn(tableName, field.columnName, field.columnType, field.columnLabel, field.referenceTable, field.choiceList);  // Pass choiceList
            }
            assignAdminRole(tableName); // Assign admin role
        } else {
            gs.error("Failed to create table: " + tableName);
        }
    }

    createModule(tableLabel, tableName);
}

// === MODULE CREATION FUNCTION ===
function createModule(moduleLabel, tableName) {
    var moduleCheck = new GlideRecord('sys_app_module');
    moduleCheck.addQuery('title', moduleLabel);
    moduleCheck.addQuery('application', appSysId); // Ensure module is under the created application
    moduleCheck.query();

    if (!moduleCheck.next()) {
        var module = new GlideRecord('sys_app_module');
        module.initialize();
        module.title = moduleLabel;
        module.application = appSysId;
        module.link_type = "LIST";
        module.name = tableName;
        module.order = moduleOrder;
        module.insert();
        gs.info("Module created: " + moduleLabel);
    } else {
        gs.info("Module already exists: " + moduleLabel + " under Application: " + appMenuName);
    }
}

// === COLUMN ADDITION FUNCTION (FIXED REFERENCE HANDLING, CHOICE HANDLING) ===
function addColumn(tableName, columnName, columnType, columnLabel, referenceTable, choiceList) {
    var column = new GlideRecord('sys_dictionary');
    column.initialize();
    column.name = tableName;
    column.element = columnName;   // Use 'element', not 'column_name'
    column.column_label = columnLabel;
    column.internal_type = columnType;
    column.mandatory = false;

    if (columnType === "reference" && referenceTable) {
        column.reference = referenceTable;
    }

    if (columnType === "choice" && choiceList) {
        column.choice = 3;  //  3 means 'Dropdown with --None--'
        // Insert choice list entries
        for (var j = 0; j < choiceList.length; j++) {
          var choice = new GlideRecord('sys_choice');
          choice.initialize();
          choice.name = tableName;
          choice.element = columnName;
          choice.label = choiceList[j].label;
          choice.value = choiceList[j].value;
          choice.sequence = j + 1; // Choice sequence starts from 1
          choice.insert();
        }
    }

    var columnSysId = column.insert();
    if (columnSysId) {
        gs.info("Column created: " + columnName + " in " + tableName);
    } else {
        gs.error("Failed to create column: " + columnName + " in " + tableName);
    }
}

// === ASSIGN ADMIN ROLE FUNCTION ===
// Improved ACL creation
function assignAdminRole(tableName) {
  var aclTypes = ["read", "write", "create", "delete"];
  var operations = {
      "read": "read",
      "write": "write",
      "create": "create",
      "delete": "delete"
  };

  for (var i = 0; i < aclTypes.length; i++) {
      var aclType = aclTypes[i];
      var operationId = operations[aclType]; // Get the sys_id of the operation

      var aclCheck = new GlideRecord('sys_security_acl');
      aclCheck.addQuery('name', tableName);
      aclCheck.addQuery('type', 'record'); // Specify ACL type as 'record'
      aclCheck.addQuery('operation', operationId);
      aclCheck.query();

      if (!aclCheck.next()) {
          var acl = new GlideRecord('sys_security_acl');
          acl.initialize();
          acl.name = tableName;
          acl.type = 'record'; // Specify ACL type
          acl.operation = operationId;
            acl.admin_overrides = true; // Allows admins to bypass
          var role = new GlideRecord('sys_user_role');
           if (role.get('name', adminRole)) {
              acl.role = role.getUniqueValue(); // Set the role's sys_id
           }

          acl.insert();
          gs.info("Assigned " + aclType + " permission to admin role for table: " + tableName);
      } else {
          gs.info("Admin role already has " + aclType + " access for: " + tableName);
      }
  }
}


// === CREATE TABLES ===
createTable("u_students", "Students", [
    { columnName: "u_first_name", columnType: "string", columnLabel: "First Name" },
    { columnName: "u_last_name", columnType: "string", columnLabel: "Last Name" },
    { columnName: "u_date_of_birth", columnType: "glide_date", columnLabel: "Date of Birth" },
    { columnName: "u_gender", columnType: "choice", columnLabel: "Gender", choiceList: [{label: "Male", value: "male"}, {label: "Female", value: "female"}, {label: "Other", value:"other"}] },
    { columnName: "u_email", columnType: "email", columnLabel: "Email" },
    { columnName: "u_phone", columnType: "string", columnLabel: "Phone" },
    { columnName: "u_address", columnType: "string", columnLabel: "Address" },
    { columnName: "u_admission_date", columnType: "glide_date", columnLabel: "Admission Date" }
]);

createTable("u_teachers", "Teachers", [
    { columnName: "u_first_name", columnType: "string", columnLabel: "First Name" },
    { columnName: "u_last_name", columnType: "string", columnLabel: "Last Name" },
    { columnName: "u_email", columnType: "email", columnLabel: "Email" },
    { columnName: "u_phone", columnType: "string", columnLabel: "Phone" },
    { columnName: "u_address", columnType: "string", columnLabel: "Address" },
    { columnName: "u_hire_date", columnType: "glide_date", columnLabel: "Hire Date" },
    { columnName: "u_subject_id", columnType: "reference", columnLabel: "Subject", referenceTable: "u_subjects" }
]);

createTable("u_subjects", "Subjects", [
    { columnName: "u_subject_name", columnType: "string", columnLabel: "Subject Name" },
    { columnName: "u_description", columnType: "string", columnLabel: "Description" }
]);

createTable("u_classes", "Classes", [
    { columnName: "u_class_name", columnType: "string", columnLabel: "Class Name" },
    { columnName: "u_section", columnType: "string", columnLabel: "Section" },
    { columnName: "u_teacher_id", columnType: "reference", columnLabel: "Teacher", referenceTable: "u_teachers" }
]);

createTable("u_enrollments", "Enrollments", [
    { columnName: "u_student_id", columnType: "reference", columnLabel: "Student", referenceTable: "u_students" },
    { columnName: "u_class_id", columnType: "reference", columnLabel: "Class", referenceTable: "u_classes" },
    { columnName: "u_enroll_date", columnType: "glide_date", columnLabel: "Enroll Date" }
]);

createTable("u_attendance", "Attendance", [
    { columnName: "u_student_id", columnType: "reference", columnLabel: "Student", referenceTable: "u_students" },
    { columnName: "u_class_id", columnType: "reference", columnLabel: "Class", referenceTable: "u_classes" },
    { columnName: "u_attendance_date", columnType: "glide_date", columnLabel: "Attendance Date" },
    { columnName: "u_status", columnType: "choice", columnLabel: "Status", choiceList: [{label: "Present", value: "present"}, {label: "Absent", value: "absent"}, {label: "Late", value:"late"}] }
]);

createTable("u_exams", "Exams", [
    { columnName: "u_exam_name", columnType: "string", columnLabel: "Exam Name" },
    { columnName: "u_subject_id", columnType: "reference", columnLabel: "Subject", referenceTable: "u_subjects" },
    { columnName: "u_class_id", columnType: "reference", columnLabel: "Class", referenceTable: "u_classes" },
    { columnName: "u_exam_date", columnType: "glide_date", columnLabel: "Exam Date" }
]);

createTable("u_exam_results", "Exam Results", [
    { columnName: "u_student_id", columnType: "reference", columnLabel: "Student", referenceTable: "u_students" },
    { columnName: "u_exam_id", columnType: "reference", columnLabel: "Exam", referenceTable: "u_exams" },
    { columnName: "u_score", columnType: "decimal", columnLabel: "Score" },
    { columnName: "u_grade", columnType: "string", columnLabel: "Grade" },
    { columnName: "u_remarks", columnType: "string", columnLabel: "Remarks" }
]);

createTable("u_fees", "Fees", [
    { columnName: "u_student_id", columnType: "reference", columnLabel: "Student", referenceTable: "u_students" },
    { columnName: "u_amount", columnType: "decimal", columnLabel: "Amount" },
    { columnName: "u_due_date", columnType: "glide_date", columnLabel: "Due Date" },
    { columnName: "u_paid_date", columnType: "glide_date", columnLabel: "Paid Date" },
    { columnName: "u_status", columnType: "choice", columnLabel: "Status", choiceList: [{label: "Paid", value:"paid"}, {label:"Unpaid", value:"unpaid"}, {label: "Partial", value: "partial"}] }
]);

gs.info("School Management System setup completed with admin role assignments.");