Realestate code

// === Real Estate Management System Tables in ServiceNow with Admin Role Assignments ===

var appMenuName = "Real Estate 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); // Create the module for this table
}

// === 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 ===
function addColumn(tableName, columnName, columnType, columnLabel, referenceTable, choiceList) {
    var column = new GlideRecord('sys_dictionary');
    column.initialize();
    column.name = tableName;
    column.element = columnName;
    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();
        }
    }
      if (columnType === 'boolean') {
        //For boolean fields, we do not set up the choice, rather give 2 options to select.
        column.choice = 1;  // Use 1 for 'Dropdown Choices' (true/false)
    }

    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 ===
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;
            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 ===

// Venture Groups
createTable("u_venture_groups", "Venture Groups", [
    { columnName: "u_name", columnType: "string", columnLabel: "Name" }
]);

// Ventures
createTable("u_ventures", "Ventures", [
    { columnName: "u_venture_name", columnType: "string", columnLabel: "Venture Name" },
    { columnName: "u_address", columnType: "string", columnLabel: "Address" },
    { columnName: "u_launched_on", columnType: "glide_date", columnLabel: "Launched On" },
    { columnName: "u_corpus", columnType: "decimal", columnLabel: "Corpus" },
    { columnName: "u_lp_no", columnType: "string", columnLabel: "LP Number" },
    { columnName: "u_group_id", columnType: "reference", columnLabel: "Group", referenceTable: "u_venture_groups" },
    { columnName: "u_cut_off_commission", columnType: "boolean", columnLabel: "Cut Off Commission" }
]);

// Amenities
createTable("u_amenities", "Amenities", [
    { columnName: "u_name", columnType: "string", columnLabel: "Name" }
]);

// Venture Amenities (Join Table)
createTable("u_venture_amenities", "Venture Amenities", [
    { columnName: "u_venture_id", columnType: "reference", columnLabel: "Venture", referenceTable: "u_ventures" },
    { columnName: "u_amenity_id", columnType: "reference", columnLabel: "Amenity", referenceTable: "u_amenities" }
]);

// Hierarchy Schemes
createTable("u_hierarchy_schemes", "Hierarchy Schemes", [
    { columnName: "u_name", columnType: "string", columnLabel: "Name" }
]);

// Hierarchy Designations
createTable("u_hierarchy_designations", "Hierarchy Designations", [
    { columnName: "u_name", columnType: "string", columnLabel: "Name" }
]);

// Hierarchies
createTable("u_hierarchies", "Hierarchies", [
    { columnName: "u_date", columnType: "glide_date", columnLabel: "Date" },
    { columnName: "u_scheme_id", columnType: "reference", columnLabel: "Scheme", referenceTable: "u_hierarchy_schemes" },
    { columnName: "u_designation_id", columnType: "reference", columnLabel: "Designation", referenceTable: "u_hierarchy_designations" },
    { columnName: "u_level", columnType: "integer", columnLabel: "Level" }
]);

// Agent Codes
createTable("u_agent_codes", "Agent Codes", [
    { columnName: "u_code", columnType: "string", columnLabel: "Code" }
]);

// Agent Hierarchies
createTable("u_agent_hierarchies", "Agent Hierarchies", [
    { columnName: "u_date", columnType: "glide_date", columnLabel: "Date" },
    { columnName: "u_agent_code_id", columnType: "reference", columnLabel: "Agent Code", referenceTable: "u_agent_codes" },
    { columnName: "u_name", columnType: "string", columnLabel: "Name" },
    { columnName: "u_parent_agent_code_id", columnType: "reference", columnLabel: "Parent Agent Code", referenceTable: "u_agent_codes" },
    { columnName: "u_parent_name", columnType: "string", columnLabel: "Parent Name" },
	{ columnName: "u_hierarchy_table_date", columnType: "glide_date", columnLabel: "Hierarchy Table Date" },
    { columnName: "u_hierarchy_table_agent_code", columnType: "string", columnLabel: "Hierarchy Table Agent Code" },
    { columnName: "u_hierarchy_table_agent_name", columnType: "string", columnLabel: "Hierarchy Table Agent Name" },
    { columnName: "u_hierarchy_table_designation", columnType: "string", columnLabel: "Hierarchy Table Designation" },
    { columnName: "u_hierarchy_table_parent_code", columnType: "string", columnLabel: "Hierarchy Table Parent Code" },
    { columnName: "u_hierarchy_table_parent_name", columnType: "string", columnLabel: "Hierarchy Table Parent Name" },
    { columnName: "u_hierarchy_table_designation_parent", columnType: "string", columnLabel: "Hierarchy Table Designation Parent" }
]);

// Towns/Cities
createTable("u_towns_cities", "Towns Cities", [
    { columnName: "u_name", columnType: "string", columnLabel: "Name" }
]);

// States
createTable("u_states", "States", [
    { columnName: "u_name", columnType: "string", columnLabel: "Name" }
]);

//Relations
createTable("u_relations", "Relations", [
	{ columnName: "u_name", columnType: "string", columnLabel : "Name" }
]);

// Facings
createTable("u_facings", "Facings", [
    { columnName: "u_name", columnType: "string", columnLabel: "Name" }
]);

// Teams (for commission details)
createTable("u_teams", "Teams", [
    { columnName: "u_name", columnType: "string", columnLabel: "Name" }
]);

// Customer Booked By
createTable("u_customer_booked_by", "Customer Booked By", [
	{ columnName: "u_name", columnType: "string", columnLabel: "Name"}
]);

// Customers
createTable("u_customers", "Customers", [
    { columnName: "u_date", columnType: "glide_date", columnLabel: "Date" },
    { columnName: "u_pass_book_no", columnType: "string", columnLabel: "Pass Book No" },
    { columnName: "u_app_ref_no", columnType: "string", columnLabel: "App Ref No" },
    { columnName: "u_cust_id", columnType: "string", columnLabel: "Cust ID" },
    { columnName: "u_cust_name", columnType: "string", columnLabel: "Cust Name" },
    { columnName: "u_aadhar_no", columnType: "string", columnLabel: "Aadhar No" },
    { columnName: "u_mobile", columnType: "string", columnLabel: "Mobile" },
    { columnName: "u_mobile_2", columnType: "string", columnLabel: "Mobile 2" },
    { columnName: "u_email", columnType: "email", columnLabel: "Email" },
    { columnName: "u_nominee", columnType: "string", columnLabel: "Nominee" },
    { columnName: "u_relation_id", columnType: "reference", columnLabel: "Relation", referenceTable: "u_relations" },
    { columnName: "u_nominee_age", columnType: "integer", columnLabel: "Nominee Age" },
    { columnName: "u_res_h_no_flat_no", columnType: "string", columnLabel: "Res H No Flat No" },
    { columnName: "u_res_street", columnType: "string", columnLabel: "Res Street" },
    { columnName: "u_res_area", columnType: "string", columnLabel: "Res Area" },
    { columnName: "u_res_town_city_id", columnType: "reference", columnLabel: "Res Town City", referenceTable: "u_towns_cities" },
    { columnName: "u_res_pin", columnType: "integer", columnLabel: "Res Pin" },
    { columnName: "u_res_state_id", columnType: "reference", columnLabel: "Res State", referenceTable: "u_states" },
    { columnName: "u_off_flat_no", columnType: "string", columnLabel: "Off Flat No" },
    { columnName: "u_off_street", columnType: "string", columnLabel: "Off Street" },
    { columnName: "u_off_town_city_id", columnType: "reference", columnLabel: "Off Town City", referenceTable: "u_towns_cities" },
    { columnName: "u_off_pin", columnType: "integer", columnLabel: "Off Pin" },
    { columnName: "u_off_state_id", columnType: "reference", columnLabel: "Off State", referenceTable: "u_states" },
    { columnName: "u_occupation", columnType: "string", columnLabel: "Occupation" },
    { columnName: "u_dob", columnType: "glide_date", columnLabel: "DOB" },
    { columnName: "u_age", columnType: "integer", columnLabel: "Age" },
    { columnName: "u_dom", columnType: "glide_date", columnLabel: "DOM" },
    { columnName: "u_father_name", columnType: "string", columnLabel: "Father Name" },
    { columnName: "u_guardian_name", columnType: "string", columnLabel: "Guardian Name" },
    { columnName: "u_husband_name", columnType: "string", columnLabel: "Husband Name" },
    { columnName: "u_remarks", columnType: "string", columnLabel: "Remarks" },
	{ columnName: "u_team_id", columnType: "reference", columnLabel: "Team", referenceTable: "u_teams" },
	{ columnName: "u_booked_by_id", columnType: "reference", columnLabel: "Booked by", referenceTable: "u_customer_booked_by" },
	{ columnName: "u_direct", columnType: "boolean", columnLabel: "Direct" }
]);

// Customer Plots
createTable("u_customer_plots", "Customer Plots", [
    { columnName: "u_customer_id", columnType: "reference", columnLabel: "Customer", referenceTable: "u_customers" },
    { columnName: "u_s_no", columnType: "integer", columnLabel: "S No" },
    { columnName: "u_site_name", columnType: "string", columnLabel: "Site Name" },  // Should ideally be a reference, but depends on your data model
    { columnName: "u_plot_no", columnType: "string", columnLabel: "Plot No" },
    { columnName: "u_facing_id", columnType: "reference", columnLabel: "Facing", referenceTable: "u_facings" },
    { columnName: "u_area", columnType: "decimal", columnLabel: "Area" },
    { columnName: "u_rate", columnType: "decimal", columnLabel: "Rate" },
    { columnName: "u_e_rate", columnType: "decimal", columnLabel: "E Rate" },
    { columnName: "u_amount", columnType: "decimal", columnLabel: "Amount" },
    { columnName: "u_discount", columnType: "decimal", columnLabel: "Discount" },
    { columnName: "u_others", columnType: "decimal", columnLabel: "Others" },
    { columnName: "u_total_plot_cost", columnType: "decimal", columnLabel: "Total Plot Cost" },
    { columnName: "u_cancel", columnType: "boolean", columnLabel: "Cancel" }
]);

// Sites (Assuming this is needed for Receipts)
createTable("u_sites", "Sites", [
    { columnName: "u_name", columnType: "string", columnLabel: "Name" }
]);

// Collection Agents
createTable("u_collection_agents", "Collection Agents", [
    { columnName: "u_name", columnType: "string", columnLabel: "Name" }
]);

// Cash/Bank Accounts (Debit)
createTable("u_cash_bank_accounts", "Cash Bank Accounts", [
    { columnName: "u_name", columnType: "string", columnLabel: "Name" }
]);

// Accounts (Credit)
createTable("u_accounts", "Accounts", [
    { columnName: "u_name", columnType: "string", columnLabel: "Name" }
]);

// Nature of Payment
createTable("u_nature_of_payment", "Nature of Payment", [
	{ columnName: "u_name", columnType: "string", columnLabel: "Name" }
]);

// Receipts
createTable("u_receipts", "Receipts", [
    { columnName: "u_receipt_no", columnType: "string", columnLabel: "Receipt No" },
    { columnName: "u_site_id", columnType: "reference", columnLabel: "Site", referenceTable: "u_sites" },
    { columnName: "u_date", columnType: "glide_date", columnLabel: "Date" },
    { columnName: "u_coll_agent_id", columnType: "reference", columnLabel: "Coll Agent", referenceTable: "u_collection_agents" },
    { columnName: "u_reference", columnType: "string", columnLabel: "Reference" },
    { columnName: "u_load_all_accounts", columnType: "boolean", columnLabel: "Load All Accounts" },
    { columnName: "u_cash_bank_dr", columnType: "string", columnLabel: "Cash Bank Dr" },
    { columnName: "u_account_cr", columnType: "string", columnLabel: "Account Cr" },
    { columnName: "u_narration", columnType: "string", columnLabel: "Narration" },
    { columnName: "u_nature_of_payment_id", columnType: "reference", columnLabel: "Nature of Payment", referenceTable: "u_nature_of_payment" },
    { columnName: "u_amount", columnType: "decimal", columnLabel: "Amount" },
    { columnName: "u_cheque_no", columnType: "string", columnLabel: "Cheque No" },
    { columnName: "u_pd_date", columnType: "glide_date", columnLabel: "PD Date" },
    { columnName: "u_name", columnType: "string", columnLabel: "Name" },
    { columnName: "u_cc_ot", columnType: "string", columnLabel: "CC OT" },
    { columnName: "u_emp_no", columnType: "string", columnLabel: "Emp No" },
    { columnName: "u_jv_no", columnType: "string", columnLabel: "JV No" }
]);

// Plot Types
createTable("u_plot_types", "Plot Types", [
    { columnName: "u_name", columnType: "string", columnLabel: "Name" }
]);

// Plots
createTable("u_plots", "Plots", [
    { columnName: "u_site_id", columnType: "reference", columnLabel: "Site", referenceTable: "u_sites" },
    { columnName: "u_actual_plot_no", columnType: "string", columnLabel: "Actual Plot No" },
    { columnName: "u_plot_no", columnType: "string", columnLabel: "Plot No" },
    { columnName: "u_facing_id", columnType: "reference", columnLabel: "Facing", referenceTable: "u_facings" },
    { columnName: "u_area", columnType: "decimal", columnLabel: "Area" },
    { columnName: "u_actual_rate", columnType: "decimal", columnLabel: "Actual Rate" },
    { columnName: "u_extra_rate", columnType: "decimal", columnLabel: "Extra Rate" },
    { columnName: "u_points", columnType: "integer", columnLabel: "Points" },
    { columnName: "u_amount", columnType: "decimal", columnLabel: "Amount" },
    { columnName: "u_amenities", columnType: "string", columnLabel: "Amenities" },  //  Ideally, link to u_venture_amenities
    { columnName: "u_discount_amount", columnType: "decimal", columnLabel: "Discount Amount" },
    { columnName: "u_width", columnType: "decimal", columnLabel: "Width" },
    { columnName: "u_length", columnType: "decimal", columnLabel: "Length" },
    { columnName: "u_allotment", columnType: "boolean", columnLabel: "Allotment" },
    { columnName: "u_hold", columnType: "boolean", columnLabel: "Hold" },
    { columnName: "u_part", columnType: "boolean", columnLabel: "Part" },
    { columnName: "u_reg_rate", columnType: "decimal", columnLabel: "Reg Rate" },
    { columnName: "u_reg_amount", columnType: "decimal", columnLabel: "Reg Amount" },
    { columnName: "u_app_fee_per_unit", columnType: "decimal", columnLabel: "App Fee Per Unit" },
    { columnName: "u_total_plot_cost", columnType: "decimal", columnLabel: "Total Plot Cost" },
    { columnName: "u_type_id", columnType: "reference", columnLabel: "Type", referenceTable: "u_plot_types" }
]);

// Blocks
createTable("u_blocks", "Blocks", [
    { columnName: "u_site_id", columnType: "reference", columnLabel: "Site", referenceTable: "u_sites" },
    { columnName: "u_flat_plot", columnType: "string", columnLabel: "Flat Plot" },
    { columnName: "u_customer", columnType: "string", columnLabel: "Customer" }, // Consider reference to u_customers
    { columnName: "u_mobile", columnType: "string", columnLabel: "Mobile" }
]);

// Agent Statuses
createTable("u_agent_statuses", "Agent Statuses", [
    { columnName: "u_name", columnType: "string", columnLabel: "Name" }
]);

// Departments
createTable("u_departments", "Departments", [
    { columnName: "u_name", columnType: "string", columnLabel: "Name" }
]);

// Companies
createTable("u_companies", "Companies", [
    { columnName: "u_name", columnType: "string", columnLabel: "Name" }
]);

// Agents
createTable("u_agents", "Agents", [
    { columnName: "u_emp_id", columnType: "string", columnLabel: "Emp ID" },
    { columnName: "u_emp_name", columnType: "string", columnLabel: "Emp Name" },
    { columnName: "u_team_name_id", columnType: "reference", columnLabel: "Team Name", referenceTable: "u_teams" },
    { columnName: "u_aadhar_no", columnType: "string", columnLabel: "Aadhar No" },
    { columnName: "u_status_id", columnType: "reference", columnLabel: "Status", referenceTable: "u_agent_statuses" },
    { columnName: "u_dob", columnType: "glide_date", columnLabel: "DOB" },
    { columnName: "u_age", columnType: "integer", columnLabel: "Age" },
    { columnName: "u_doj", columnType: "glide_date", columnLabel: "DOJ" },
    { columnName: "u_blood_group", columnType: "string", columnLabel: "Blood Group" },
    { columnName: "u_gender", columnType: "boolean", columnLabel: "Gender" },
    { columnName: "u_marital_status", columnType: "boolean", columnLabel: "Marital Status" },
    { columnName: "u_qualification", columnType: "string", columnLabel: "Qualification" },
    { columnName: "u_experience", columnType: "integer", columnLabel: "Experience" },
    { columnName: "u_company_id", columnType: "reference", columnLabel: "Company", referenceTable: "u_companies" },
    { columnName: "u_salary", columnType: "decimal", columnLabel: "Salary" },
    { columnName: "u_designation", columnType: "string", columnLabel: "Designation" },
    { columnName: "u_department_id", columnType: "reference", columnLabel: "Department", referenceTable: "u_departments" },
    { columnName: "u_bank_name", columnType: "string", columnLabel: "Bank Name" },
    { columnName: "u_branch", columnType: "string", columnLabel: "Branch" },
    { columnName: "u_account_no", columnType: "string", columnLabel: "Account No" },
    { columnName: "u_ifsc_code", columnType: "string", columnLabel: "IFSC Code" },
    { columnName: "u_father_name", columnType: "string", columnLabel: "Father Name" },
    { columnName: "u_nominee_name", columnType: "string", columnLabel: "Nominee Name" },
    { columnName: "u_nominee_address", columnType: "string", columnLabel: "Nominee Address" },
    { columnName: "u_nominee_age", columnType: "integer", columnLabel: "Nominee Age" },
    { columnName: "u_nominee_relationship", columnType: "string", columnLabel: "Nominee Relationship" },
    { columnName: "u_pan_no", columnType: "string", columnLabel: "PAN No" },
    { columnName: "u_phone_no", columnType: "string", columnLabel: "Phone No" },
    { columnName: "u_land_line", columnType: "string", columnLabel: "Land Line" },
    { columnName: "u_mobile", columnType: "string", columnLabel: "Mobile" },
    { columnName: "u_email", columnType: "email", columnLabel: "Email" },
    { columnName: "u_present_address", columnType: "string", columnLabel: "Present Address" },
    { columnName: "u_permanent_address", columnType: "string", columnLabel: "Permanent Address" },
    { columnName: "u_scheme", columnType: "string", columnLabel: "Scheme" },
    { columnName: "u_esi_pf_tds", columnType: "boolean", columnLabel: "ESI PF TDS" },
    { columnName: "u_hobbies", columnType: "string", columnLabel: "Hobbies" }
]);

// Approval for Registrations
createTable("u_approval_for_registrations", "Approval for Registrations", [
    { columnName: "u_approval_no", columnType: "string", columnLabel: "Approval No" },
    { columnName: "u_date", columnType: "glide_date", columnLabel: "Date" },
    { columnName: "u_customer_id", columnType: "string", columnLabel: "Customer ID" }, //  reference u_customers
    { columnName: "u_customer_name", columnType: "string", columnLabel: "Customer Name" },
    { columnName: "u_venture_id", columnType: "reference", columnLabel: "Venture", referenceTable: "u_ventures" },
    { columnName: "u_lp_no", columnType: "string", columnLabel: "LP No" },
    { columnName: "u_plot_no", columnType: "string", columnLabel: "Plot No" },
    { columnName: "u_area", columnType: "decimal", columnLabel: "Area" },
    { columnName: "u_final_rate", columnType: "decimal", columnLabel: "Final Rate" },
    { columnName: "u_cust_info_name", columnType: "string", columnLabel: "Cust Info Name" },
    { columnName: "u_cust_info_age", columnType: "integer", columnLabel: "Cust Info Age" },
    { columnName: "u_cust_info_s_d_w_of", columnType: "string", columnLabel: "Cust Info S/D/W Of" },
    { columnName: "u_cust_info_occupation", columnType: "string", columnLabel: "Cust Info Occupation" },
    { columnName: "u_cust_info_door_no", columnType: "string", columnLabel: "Cust Info Door No" },
    { columnName: "u_cust_info_street_or_village", columnType: "string", columnLabel: "Cust Info Street/Village" },
    { columnName: "u_cust_info_town_city", columnType: "string", columnLabel: "Cust Info Town/City" },
    { columnName: "u_cust_info_pin_code", columnType: "integer", columnLabel: "Cust Info Pin Code" },
    { columnName: "u_registered_to", columnType: "string", columnLabel: "Registered To" },
    { columnName: "u_reg_age", columnType: "integer", columnLabel: "Reg Age" },
    { columnName: "u_reg_s_d_w_of", columnType: "string", columnLabel: "Reg S/D/W Of" },
    { columnName: "u_reg_occupation", columnType: "string", columnLabel: "Reg Occupation" },
    { columnName: "u_reg_door_no", columnType: "string", columnLabel: "Reg Door No" },
    { columnName: "u_reg_street_or_village", columnType: "string", columnLabel: "Reg Street/Village" },
    { columnName: "u_reg_town_city", columnType: "string", columnLabel: "Reg Town/City" },
    { columnName: "u_reg_pin_code", columnType: "integer", columnLabel: "Reg Pin Code" },
    { columnName: "u_total_plot_cost", columnType: "decimal", columnLabel: "Total Plot Cost" },
    { columnName: "u_less_discount", columnType: "decimal", columnLabel: "Less Discount" },
    { columnName: "u_net_plot_cost", columnType: "decimal", columnLabel: "Net Plot Cost" },
    { columnName: "u_registration_expenses", columnType: "decimal", columnLabel: "Registration Expenses" },
    { columnName: "u_total_payable", columnType: "decimal", columnLabel: "Total Payable" },
    { columnName: "u_paid_as_on", columnType: "glide_date", columnLabel: "Paid As On" },
    { columnName: "u_net_balance_due", columnType: "decimal", columnLabel: "Net Balance Due" },
    { columnName: "u_remarks", columnType: "string", columnLabel: "Remarks" }
]);

// Allotments
createTable("u_allotments", "Allotments", [
    { columnName: "u_customer_id", columnType: "string", columnLabel: "Customer ID" }, //  reference u_customers
    { columnName: "u_name", columnType: "string", columnLabel: "Name" },
    { columnName: "u_received_amount", columnType: "decimal", columnLabel: "Received Amount" },
    { columnName: "u_serial_no", columnType: "integer", columnLabel: "Serial No" },
    { columnName: "u_site_name", columnType: "string", columnLabel: "Site Name" }, //  reference u_sites
    { columnName: "u_plot_no", columnType: "string", columnLabel: "Plot No" },
    { columnName: "u_area", columnType: "decimal", columnLabel: "Area" },
    { columnName: "u_facing_id", columnType: "reference", columnLabel: "Facing", referenceTable: "u_facings" },
    { columnName: "u_actual_rate", columnType: "decimal", columnLabel: "Actual Rate" },
    { columnName: "u_total_plot_cost", columnType: "decimal", columnLabel: "Total Plot Cost" },
    { columnName: "u_allotment_amount", columnType: "decimal", columnLabel: "Allotment Amount" },
    { columnName: "u_select", columnType: "boolean", columnLabel: "Select" },
    { columnName: "u_date", columnType: "glide_date", columnLabel: "Date" }
]);

// Payment Paid To
createTable("u_payment_paid_to", "Payment Paid To", [
    { columnName: "u_name", columnType: "string", columnLabel: "Name" }
]);

// Payment Approved By
createTable("u_payment_approved_by", "Payment Approved By", [
    { columnName: "u_name", columnType: "string", columnLabel: "Name" }
]);

// Payments
createTable("u_payments", "Payments", [
    { columnName: "u_voucher_no", columnType: "string", columnLabel: "Voucher No" },
    { columnName: "u_date", columnType: "glide_date", columnLabel: "Date" },
    { columnName: "u_site_name_id", columnType: "reference", columnLabel: "Site Name", referenceTable: "u_sites" },
    { columnName: "u_paid_to_id", columnType: "reference", columnLabel: "Paid To", referenceTable: "u_payment_paid_to" },
    { columnName: "u_approved_by_id", columnType: "reference", columnLabel: "Approved By", referenceTable: "u_payment_approved_by" },
    { columnName: "u_reference", columnType: "string", columnLabel: "Reference" },
    { columnName: "u_pan_no", columnType: "string", columnLabel: "PAN No" },
    { columnName: "u_cash_bank_cr_id", columnType: "reference", columnLabel: "Cash Bank Cr", referenceTable: "u_cash_bank_accounts" },
    { columnName: "u_account_dr_id", columnType: "reference", columnLabel: "Account Dr", referenceTable: "u_accounts" },
    { columnName: "u_narration", columnType: "string", columnLabel: "Narration" },
    { columnName: "u_bill_amount", columnType: "decimal", columnLabel: "Bill Amount" },
    { columnName: "u_tds_percentage", columnType: "decimal", columnLabel: "TDS Percentage" },
    { columnName: "u_tds_amount", columnType: "decimal", columnLabel: "TDS Amount" },
    { columnName: "u_net_amount", columnType: "decimal", columnLabel: "Net Amount" },
    { columnName: "u_cheque_no", columnType: "string", columnLabel: "Cheque No" },
    { columnName: "u_p_d", columnType: "boolean", columnLabel: "P D" },
    { columnName: "u_payment_date", columnType: "glide_date", columnLabel: "Payment Date" },
    { columnName: "u_payee_name", columnType: "string", columnLabel: "Payee Name" },
    { columnName: "u_w_o_no", columnType: "string", columnLabel: "W/O No" },
    { columnName: "u_emp_no", columnType: "string", columnLabel: "Emp No" },
    { columnName: "u_update_dr_head", columnType: "boolean", columnLabel: "Update Dr Head" }
]);

// Cancellations
createTable("u_cancellations", "Cancellations", [
    { columnName: "u_customer_id", columnType: "string", columnLabel: "Customer ID" }, //  reference u_customers
    { columnName: "u_name", columnType: "string", columnLabel: "Name" },
    { columnName: "u_serial_no", columnType: "integer", columnLabel: "Serial No" },
    { columnName: "u_site_name", columnType: "string", columnLabel: "Site Name" },  //  reference u_sites
    { columnName: "u_plot_no", columnType: "string", columnLabel: "Plot No" },
    { columnName: "u_area", columnType: "decimal", columnLabel: "Area" },
    { columnName: "u_facing_id", columnType: "reference", columnLabel: "Facing", referenceTable: "u_facings" },
    { columnName: "u_actual_rate", columnType: "decimal", columnLabel: "Actual Rate" },
    { columnName: "u_amount", columnType: "decimal", columnLabel: "Amount" },
    { columnName: "u_total_plot_cost", columnType: "decimal", columnLabel: "Total Plot Cost" },
    { columnName: "u_cancel", columnType: "boolean", columnLabel: "Cancel" },
    { columnName: "u_date", columnType: "glide_date", columnLabel: "Date" },
    { columnName: "u_commissions_date", columnType: "glide_date", columnLabel: "Commissions Date" },
    { columnName: "u_particulars", columnType: "string", columnLabel: "Particulars" },
    { columnName: "u_commissions_amount", columnType: "decimal", columnLabel: "Commissions Amount" }
]);

// Registration Approval Nos
createTable("u_registration_approval_nos", "Registration Approval Nos", [
    { columnName: "u_approval_no", columnType: "string", columnLabel: "Approval No" }
]);

// Registrations
createTable("u_registrations", "Registrations", [
    { columnName: "u_approval_no_id", columnType: "reference", columnLabel: "Approval No", referenceTable: "u_registration_approval_nos" },
    { columnName: "u_date", columnType: "glide_date", columnLabel: "Date" },
    { columnName: "u_site_name", columnType: "string", columnLabel: "Site Name" }, // reference u_sites
    { columnName: "u_plot_flat_no", columnType: "string", columnLabel: "Plot/Flat No" },
    { columnName: "u_registered_to", columnType: "string", columnLabel: "Registered To" },
    { columnName: "u_address", columnType: "string", columnLabel: "Address" },
    { columnName: "u_customer_id", columnType: "string", columnLabel: "Customer ID" },//  reference u_customers
    { columnName: "u_customer_name", columnType: "string", columnLabel: "Customer Name" },
    { columnName: "u_recd_amt", columnType: "decimal", columnLabel: "Recd Amt" },
    { columnName: "u_area", columnType: "decimal", columnLabel: "Area" },
    { columnName: "u_rate", columnType: "decimal", columnLabel: "Rate" },
    { columnName: "u_amount", columnType: "decimal", columnLabel: "Amount" },
    { columnName: "u_facing", columnType: "string", columnLabel: "Facing" }, // Ideally reference, but consistent with input
    { columnName: "u_extra_rate", columnType: "decimal", columnLabel: "Extra Rate" },
    { columnName: "u_extra_amount", columnType: "decimal", columnLabel: "Extra Amount" },
    { columnName: "u_discount", columnType: "decimal", columnLabel: "Discount" },
    { columnName: "u_dimensions", columnType: "string", columnLabel: "Dimensions" },
    { columnName: "u_amenities", columnType: "string", columnLabel: "Amenities" }, // Ideally a related list
    { columnName: "u_registration_rate", columnType: "decimal", columnLabel: "Registration Rate" },
    { columnName: "u_registration_amount", columnType: "decimal", columnLabel: "Registration Amount" },
    { columnName: "u_plot_cost", columnType: "decimal", columnLabel: "Plot Cost" },
    { columnName: "u_ag_rate", columnType: "decimal", columnLabel: "AG Rate" },
    { columnName: "u_ag_amount", columnType: "decimal", columnLabel: "AG Amount" },
    { columnName: "u_total_cost_with_registration", columnType: "decimal", columnLabel: "Total Cost with Registration" },
    { columnName: "u_feedback_section", columnType: "string", columnLabel: "Feedback Section" },
    { columnName: "u_challan_no", columnType: "string", columnLabel: "Challan No" },
    { columnName: "u_challan_date", columnType: "glide_date", columnLabel: "Challan Date" },
    { columnName: "u_ec_amount", columnType: "decimal", columnLabel: "EC Amount" }
]);


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