const mysql = require("mysql2/promise");
const { Sequelize } = require("sequelize");
const config = require("../../../config.json");
const DatabaseConnectionManager = require("../../../utils/databaseConnectionManager");
const fs = require("fs");
const path = require("path");
require("dotenv").config();

module.exports = dbSAPPS = {};

// Database initialization state
let initializationPromise = null;
let isInitializing = false;
let isInitialized = false;
let initializationError = null;

// Associate monitoring state
const associateMonitoring = {
  initialized: false,
  startTime: null,
  endTime: null,
  duration: null,
  modelsProcessed: 0,
  modelsWithAssociations: 0,
  modelsWithoutAssociations: 0,
  errors: [],
  associationsByModel: {},
};

// Log directory for associate monitoring
// Path: sapps-backend/apps/sapps/helpers -> sapps-backend/logs
const logDir = path.join(__dirname, "../../../logs");
const associateLogFile = path.join(logDir, "associations.log");

/**
 * Ensure log directory exists
 */
function ensureLogDirectory() {
  if (!fs.existsSync(logDir)) {
    fs.mkdirSync(logDir, { recursive: true });
  }
}

/**
 * Log associate monitoring information to file (async, non-blocking).
 * Meminimalkan blocking event loop di high load / jutaan transaksi.
 * @param {string} message - Log message
 * @param {Object} data - Additional data to log
 */
function logAssociation(message, data = {}) {
  try {
    ensureLogDirectory();

    const logEntry = {
      timestamp: new Date().toISOString(),
      pid: process.pid,
      message,
      ...data,
    };

    fs.appendFile(associateLogFile, JSON.stringify(logEntry) + "\n", "utf8", (err) => {
      if (err) console.error("Error writing association log:", err);
    });
  } catch (err) {
    console.error("Error writing association log:", err);
  }
}

// Initialize database connection manager with retry mechanism
// ROBUST: Use explicit timeouts even in production to prevent infinite hanging ("Operation Timeout")
// If cPanel is slow, we use generous timeouts (60s) but never infinite.
const isProduction = process.env.NODE_ENV === "production" || config.databaseConfigSAPPS.NODE_ENV === "production";
const connectionTimeout = isProduction ? 20000 : 10000; // 20s for production, 10s for development
const acquireTimeout = isProduction ? 60000 : 30000; // 60s for production, 30s for development

// Pool: bisa di-override via config.databaseConfigSAPPS.pool (untuk server 4 core/16GB: max 40).
const defaultPool = { max: 50, min: 5, acquire: acquireTimeout, idle: 10000 };
const poolConfig = { ...defaultPool, ...(config.databaseConfigSAPPS?.pool || {}) };

const dbConnectionManager = new DatabaseConnectionManager(config.databaseConfigSAPPS, {
  maxRetries: 5,
  retryDelay: 2000, // 2 seconds
  maxRetryDelay: 30000, // 30 seconds
  connectionTimeout: connectionTimeout, // 0 for production/cPanel (no timeout), 10s for development
  pool: poolConfig,
  logging: false, // Set to true for SQL query logging
});

// Start initialization but don't await here (for module loading compatibility)
// The promise will be available for awaiting in app.js
initializationPromise = initialize().catch((error) => {
  initializationError = error;
  // Logging error detail ke file dan console
  console.error("[DB Initialization] Initialization error:", error);
  if (error && error.message) {
    // Log pesan error MySQL spesifik
    console.error("[DB Initialization] MySQL Error Message:", error.message);
  }
  if (error && error.stack) {
    console.error("[DB Initialization] MySQL Error Stack:", error.stack);
  }
  // Log ke file
  logAssociation("Initialization error", {
    error: error.message,
    stack: error.stack,
    status: "failed",
  });
  // Tulis error juga ke file logs/mysql-connection-errors.log (async, non-blocking)
  try {
    const path = require("path");
    const logDir = path.join(__dirname, "../../../logs");
    const mysqlErrorLog = path.join(logDir, "mysql-connection-errors.log");
    if (!fs.existsSync(logDir)) fs.mkdirSync(logDir, { recursive: true });
    fs.appendFile(
      mysqlErrorLog,
      `[${new Date().toISOString()}] ${error && error.message ? error.message : error}\n`,
      "utf8",
      (e) => {
        if (e) console.error("Gagal menulis log error MySQL:", e);
      },
    );
  } catch (e) {
    console.error("Gagal menulis log error MySQL:", e);
  }
  // Jangan throw agar app tetap jalan, retry di background
});

/**
 * NOTE: All model written in camelCase
 * @namespace dbSAPPS
 */

async function initialize() {
  try {
    // Use DatabaseConnectionManager for robust connection with retry
    const sequelize = await dbConnectionManager.connectWithRetry(0);

    // Initialize models and add them to the exported db object
    dbSAPPS.sequelize = sequelize;
    dbSAPPS.Sequelize = Sequelize;
    dbSAPPS.dbConnectionManager = dbConnectionManager; // Export for transaction manager

    /**
     * @typedef {import("../masters")} MasterModel
     * Models in this namespace are created inside `masters` folder
     */

    // masalah pada npm install di hosting
    // dbSAPPS.Whatsapp = require("../services/whatsapps/whatsapp.model")(sequelize);
    dbSAPPS.User = require("../masters/users/user.model")(sequelize);
    dbSAPPS.UserChild = require("../masters/users/userChild.model")(sequelize);
    dbSAPPS.UserAdditionalInfo = require("../masters/users/userAdditionalInfo.model")(sequelize);
    dbSAPPS.Company = require("../masters/companies/company.model")(sequelize);
    dbSAPPS.Role = require("../masters/roles/role.model")(sequelize);
    dbSAPPS.Menu = require("../masters/menus/menu.model")(sequelize);
    dbSAPPS.Permission = require("../masters/permissions/permission.model")(sequelize);
    dbSAPPS.UserActivity = require("../masters/userActivites/userActivity.model")(sequelize);
    dbSAPPS.DocType = require("../masters/docTypes/docType.model")(sequelize);
    dbSAPPS.Project = require("../masters/projects/project.model")(sequelize);
    dbSAPPS.ProjectTeam = require("../masters/projects/projectTeam.model")(sequelize);
    dbSAPPS.Image = require("../masters/images/image.model")(sequelize);
    dbSAPPS.Document = require("../masters/documents/document.model")(sequelize);
    dbSAPPS.Video = require("../masters/videos/video.model")(sequelize);
    dbSAPPS.Email = require("../masters/emails/email.model")(sequelize);
    dbSAPPS.EmailAccount = require("../masters/emailAccounts/emailAccount.model")(sequelize);
    dbSAPPS.Development = require("../masters/developments/development.model")(sequelize);
    dbSAPPS.Aspect = require("../masters/developments/aspect.model")(sequelize);
    dbSAPPS.Factor = require("../masters/developments/factor.model")(sequelize);
    dbSAPPS.ProgressionTable = require("../masters/developments/progressionTable.model")(sequelize);
    dbSAPPS.Level = require("../masters/developments/level.model")(sequelize);
    dbSAPPS.ApiKey = require("../masters/apikeys/apikey.model")(sequelize);
    dbSAPPS.Province = require("../masters/provinces/province.model")(sequelize);
    dbSAPPS.City = require("../masters/cities/city.model")(sequelize);
    dbSAPPS.Year = require("../masters/years/year.model")(sequelize);
    dbSAPPS.Period = require("../masters/periods/period.model")(sequelize);
    dbSAPPS.ClientType = require("../masters/clientTypes/clientType.model")(sequelize);
    dbSAPPS.Specialist = require("../masters/specialists/specialist.model")(sequelize);
    dbSAPPS.ClientSubtype = require("../masters/clientSubtypes/clientSubtype.model")(sequelize);
    dbSAPPS.Faculty = require("../masters/faculties/faculty.model")(sequelize);
    dbSAPPS.Major = require("../masters/majors/major.model")(sequelize);
    dbSAPPS.Employee = require("../masters/employees/employee.model")(sequelize);
    dbSAPPS.SubSpecialist = require("../masters/subSpecialists/subSpecialist.model")(sequelize);
    dbSAPPS.OutletType = require("../masters/outletTypes/outletType.model")(sequelize);
    dbSAPPS.Institution = require("../masters/institutions/institution.model")(sequelize);
    dbSAPPS.TerritoryVersion = require("../masters/territoryVersions/territoryVersion.model")(sequelize);
    dbSAPPS.TerritoryVersionChild = require("../masters/territoryVersions/territoryVersionChild.model")(sequelize);
    dbSAPPS.Territory = require("../masters/territories/territory.model")(sequelize);
    dbSAPPS.Outlet = require("../masters/outlets/outlet.model")(sequelize);
    dbSAPPS.Client = require("../masters/clients/client.model")(sequelize);
    dbSAPPS.Department = require("../masters/departments/department.model")(sequelize);
    dbSAPPS.Team = require("../masters/teams/team.model")(sequelize);
    dbSAPPS.Designation = require("../masters/designations/designation.model")(sequelize);
    dbSAPPS.HolidayGroup = require("../masters/holidayGroups/holidayGroup.model")(sequelize);
    dbSAPPS.Holiday = require("../masters/holidays/holiday.model")(sequelize);
    dbSAPPS.ItemGroup = require("../masters/itemGroups/itemGroup.model")(sequelize);
    dbSAPPS.Item = require("../masters/items/item.model")(sequelize);
    dbSAPPS.ItemCompetitor = require("../masters/itemCompetitors/itemCompetitor.model")(sequelize);
    dbSAPPS.Question = require("../masters/questions/question.model")(sequelize);
    dbSAPPS.Answer = require("../masters/questions/answer.model")(sequelize);
    dbSAPPS.QuestionBank = require("../masters/questionBanks/questionBank.model")(sequelize);
    dbSAPPS.Course = require("../masters/courses/course.model")(sequelize);
    dbSAPPS.DeliveryTrip = require("../masters/deliveryTrips/deliveryTrip.model")(sequelize);
    dbSAPPS.DeliveryTripChild = require("../masters/deliveryTrips/deliveryTripChild.model")(sequelize);
    dbSAPPS.DeliveryTripCost = require("../masters/deliveryTrips/deliveryTripCost.model")(sequelize);
    dbSAPPS.DeliveryExpeditionVendor = require("../masters/deliveryExpeditionVendors/deliveryExpeditionVendor.model")(
      sequelize,
    );
    dbSAPPS.CourseChild = require("../masters/courses/courseChild.model")(sequelize);
    dbSAPPS.UserAnswer = require("../masters/questions/userAnswer.model")(sequelize);
    dbSAPPS.ProjectManagement = require("../masters/projectManagements/projectManagement.model")(sequelize);
    dbSAPPS.QuestionBankReview = require("../masters/questionBanks/questionBankReview.model")(sequelize);
    dbSAPPS.Vehicle = require("../masters/vehicles/vehicle.model")(sequelize);
    dbSAPPS.WorkplanMonitor = require("../masters/workplanMonitors/workplanMonitor.model")(sequelize);
    dbSAPPS.WorkplanMonitorChild = require("../masters/workplanMonitors/workplanMonitorChild.model")(sequelize);
    dbSAPPS.WorkplanMonitorActivityChild = require("../masters/workplanMonitors/workplanMonitorActivityChild.model")(
      sequelize,
    );
    dbSAPPS.CallPlan = require("../masters/callPlans/callPlan.model")(sequelize);
    dbSAPPS.CallPlanDestination = require("../masters/callPlans/callPlanDestination.model")(sequelize);
    dbSAPPS.CallPlanVisit = require("../masters/callPlans/callPlanVisit.model")(sequelize);
    dbSAPPS.CallPlanRealization = require("../masters/callPlans/callPlanRealization.model")(sequelize);
    dbSAPPS.Mom = require("../masters/moms/mom.model")(sequelize);
    dbSAPPS.MomParticipant = require("../masters/moms/momParticipant.model")(sequelize);
    dbSAPPS.MomAgenda = require("../masters/moms/momAgenda.model")(sequelize);
    dbSAPPS.MomResult = require("../masters/moms/momResult.model")(sequelize);
    dbSAPPS.MomFile = require("../masters/moms/momFile.model")(sequelize);
    dbSAPPS.DesignationVersion = require("../masters/designationVersions/designationVersion.model")(sequelize);
    dbSAPPS.DesignationVersionChild = require("../masters/designationVersions/designationVersionChild.model")(
      sequelize,
    );
    dbSAPPS.WorkflowSetting = require("../masters/workflowSettings/workflowSetting.model")(sequelize);
    dbSAPPS.Workflow = require("../masters/workflows/workflow.model")(sequelize);
    dbSAPPS.WorkflowApprover = require("../masters/workflows/workflowApprover.model")(sequelize);
    dbSAPPS.Notification = require("../masters/notifications/notification.model")(sequelize);
    dbSAPPS.EmployeeCheckin = require("../masters/employeeCheckins/employeeCheckin.model")(sequelize);
    dbSAPPS.Attendance = require("../masters/attendances/attendance.model")(sequelize);
    dbSAPPS.AttendanceRequest = require("../masters/attendanceRequests/attendanceRequest.model")(sequelize);
    dbSAPPS.LeaveType = require("../masters/leaveTypes/leaveType.model")(sequelize);
    dbSAPPS.LeaveApplication = require("../masters/leaveApplications/leaveApplication.model")(sequelize);
    dbSAPPS.ShiftType = require("../masters/shiftTypes/shiftType.model")(sequelize);
    dbSAPPS.Distributor = require("../masters/distributors/distributor.model")(sequelize);
    dbSAPPS.DistributorOutlet = require("../masters/distributors/distributorOutlet.model")(sequelize);
    dbSAPPS.DistributorProduct = require("../masters/distributors/distributorProduct.model")(sequelize);
    dbSAPPS.DistributorBranch = require("../masters/distributors/distributorBranch.model")(sequelize);
    dbSAPPS.CustomerList = require("../masters/customerLists/customerList.model")(sequelize);
    dbSAPPS.AddressChild = require("../masters/customerLists/addressChild.model")(sequelize);
    dbSAPPS.CustomerPICChild = require("../masters/customerLists/customerPICChild.model")(sequelize);
    dbSAPPS.AbsenceClarification = require("../masters/absenceClarifications/absenceClarification.model")(sequelize);
    dbSAPPS.AbsenceClarificationChild = require("../masters/absenceClarifications/absenceClarificationChild.model")(
      sequelize,
    );
    dbSAPPS.Udk = require("../masters/udks/udk.model")(sequelize);
    dbSAPPS.UdkProduct = require("../masters/udks/udkProduct.model")(sequelize);
    dbSAPPS.Applicant = require("../masters/applicants/applicant.model")(sequelize);
    dbSAPPS.EmployeeRequest = require("../masters/employeeRequests/employeeRequest.model")(sequelize);
    dbSAPPS.Progression = require("../masters/courses/progression.model")(sequelize);
    dbSAPPS.SalesDistributor = require("../masters/salesDistributors/salesDistributor.model")(sequelize);
    dbSAPPS.PermitEase = require("../masters/permitEases/permitEase.model")(sequelize);
    dbSAPPS.LeaveAllocation = require("../masters/leaveAllocations/leaveAllocation.model")(sequelize);
    dbSAPPS.ClientCollab = require("../masters/clientCollabs/clientCollab.model")(sequelize);
    dbSAPPS.ClientCollabDetail = require("../masters/clientCollabs/clientCollabDetail.model")(sequelize);
    dbSAPPS.DMSLegal = require("../masters/dmsLegals/dmsLegal.model")(sequelize);
    dbSAPPS.DMSLegalChild = require("../masters/dmsLegals/dmsLegalChild.model")(sequelize);
    dbSAPPS.Survey = require("../masters/surveys/survey.model")(sequelize);
    dbSAPPS.SurveySellingIn = require("../masters/surveys/surveySellingIn.model")(sequelize);
    dbSAPPS.SurveyExpense = require("../masters/surveys/surveyExpense.model")(sequelize);
    dbSAPPS.SurveyTreatment = require("../masters/surveys/surveyTreatment.model")(sequelize);
    dbSAPPS.AccountEntries = require("../masters/accountEntries/accountEntries.model")(sequelize);
    dbSAPPS.ReceiptOfGood = require("../masters/receiptOfGoods/receiptOfGood.model")(sequelize);
    dbSAPPS.Udp = require("../masters/udps/udp.model")(sequelize);
    dbSAPPS.UdpProduct = require("../masters/udps/udpProduct.model")(sequelize);
    dbSAPPS.SalesTarget = require("../masters/salesTargets/salesTarget.model")(sequelize);
    dbSAPPS.SalesTargetDetail = require("../masters/salesTargets/salesTargetDetail.model")(sequelize);
    dbSAPPS.GoodReceipt = require("../masters/goodReceipts/goodReceipt.model")(sequelize);
    dbSAPPS.GoodsReceipt = require("../masters/goodsReceipts/goodsReceipt.model")(sequelize);
    dbSAPPS.InternalAudit = require("../masters/internalAudits/internalAudit.model")(sequelize);
    dbSAPPS.InternalAuditItem = require("../masters/internalAudits/internalAuditItem.model")(sequelize);
    dbSAPPS.InternalAuditFinding = require("../masters/internalAudits/internalAuditFinding.model")(sequelize);
    dbSAPPS.ImportLog = require("../imports/importLog.model")(sequelize);
    dbSAPPS.ImportSalesDistributor = require("../imports/importSalesDistributor.model")(sequelize);
    dbSAPPS.WorkInstruction = require("../masters/workInstructions/workInstruction.model")(sequelize);
    dbSAPPS.JobPosting = require("../masters/jobPostings/jobPosting.model")(sequelize);
    dbSAPPS.JobPostingChild = require("../masters/jobPostings/jobPostingChild.model")(sequelize);
    dbSAPPS.NcProduct = require("../masters/ncProducts/ncProduct.model")(sequelize);
    dbSAPPS.NcProductChild = require("../masters/ncProducts/ncProductChild.model")(sequelize);
    dbSAPPS.CompanyAsset = require("../masters/companies/companyAsset.model")(sequelize);
    dbSAPPS.CompanySetting = require("../masters/companies/companySetting.model")(sequelize);
    dbSAPPS.NcProductFU = require("../masters/ncProducts/ncProductFU.model")(sequelize);
    dbSAPPS.ProductReturn = require("../masters/productReturns/productReturn.model")(sequelize);
    dbSAPPS.ProductReturnChild = require("../masters/productReturns/productReturnChild.model")(sequelize);
    dbSAPPS.ProductReturnFU = require("../masters/productReturns/productReturnFU.model")(sequelize);
    dbSAPPS.Comment = require("../masters/comments/comment.model")(sequelize);
    dbSAPPS.SalesPlan = require("../masters/salesPlans/salesPlan.model")(sequelize);
    dbSAPPS.SalesPlanItem = require("../masters/salesPlans/salesPlanItem.model")(sequelize);
    dbSAPPS.SalesPlanEstimate = require("../masters/salesPlans/salesPlanEstimate.model")(sequelize);
    dbSAPPS.InvoiceReceipt = require("../masters/invoiceReceipts/invoiceReceipt.model")(sequelize);
    dbSAPPS.InvoiceReceiptChild = require("../masters/invoiceReceipts/invoiceReceiptChild.model")(sequelize);
    dbSAPPS.Fse = require("../masters/fses/fse.model")(sequelize);
    dbSAPPS.GimmickReqType = require("../masters/gimmickReqTypes/gimmickReqType.model")(sequelize);
    dbSAPPS.GimmickReqSubType = require("../masters/gimmickReqTypes/gimmickReqSubType.model")(sequelize);
    dbSAPPS.FseItem = require("../masters/fses/fseItem.model")(sequelize);
    dbSAPPS.EmployeeStatus = require("../masters/employeeStatuses/employeeStatus.model")(sequelize);
    dbSAPPS.Gender = require("../masters/genders/gender.model")(sequelize);
    dbSAPPS.PtkpStatus = require("../masters/ptkpStatuses/ptkpStatus.model")(sequelize);
    dbSAPPS.Religion = require("../masters/religions/religion.model")(sequelize);
    dbSAPPS.JobRole = require("../masters/jobRoles/jobRole.model")(sequelize);
    dbSAPPS.BankAccount = require("../masters/bankAccounts/bankAccount.model")(sequelize);
    dbSAPPS.MaritalStatus = require("../masters/maritalStatuses/maritalStatus.model")(sequelize);
    dbSAPPS.GimmickRequest = require("../masters/gimmickRequests/gimmickRequest.model")(sequelize);
    dbSAPPS.GimmickAllocation = require("../masters/gimmickRequests/gimmickAllocation.model")(sequelize);
    dbSAPPS.GimmickAllocationOutlet = require("../masters/gimmickRequests/gimmickAllocationOutlet.model")(sequelize);
    dbSAPPS.GimmickReceiver = require("../masters/gimmickRequests/gimmickReceiver.model")(sequelize);
    dbSAPPS.Bank = require("../masters/banks/bank.model")(sequelize);
    dbSAPPS.EmployeeGrade = require("../masters/employeeGrades/employeeGrade.model")(sequelize);
    dbSAPPS.EmployeeGroup = require("../masters/employeeGroups/employeeGroup.model")(sequelize);
    dbSAPPS.EmploymentType = require("../masters/employmentTypes/employmentType.model")(sequelize);
    dbSAPPS.Branch = require("../masters/branches/branch.model")(sequelize);
    dbSAPPS.EmployeeGroupTable = require("../masters/employeeGroups/employeeGroupTable.model")(sequelize);
    dbSAPPS.EmailGroup = require("../masters/emailGroups/emailGroup.model")(sequelize);
    dbSAPPS.EmailGroupMember = require("../masters/emailGroups/emailGroupMember.model")(sequelize);
    dbSAPPS.LeavePeriod = require("../masters/leavePeriods/leavePeriod.model")(sequelize);
    dbSAPPS.HolidayList = require("../masters/holidayLists/holidayList.model")(sequelize);
    dbSAPPS.HolidayListItem = require("../masters/holidayLists/holidayListItem.model")(sequelize);
    dbSAPPS.HistoryLeaveApplication = require("../histories/leaveApplications/historyLeaveApplication.model")(
      sequelize,
    );
    dbSAPPS.HistoryEmail = require("../histories/emails/historyEmail.model")(sequelize);
    dbSAPPS.RegCategories = require("../masters/regulatoryCategories/regulatoryCategory.model")(sequelize);
    dbSAPPS.DocumentGroup = require("../masters/documentGroups/documentGroup.model")(sequelize);
    dbSAPPS.DMSRegulatory = require("../masters/dmsRegulatories/dmsRegulatory.model")(sequelize);
    dbSAPPS.TravelCostRate = require("../masters/travelCostRates/travelCostRate.model")(sequelize);
    dbSAPPS.TrackingLocation = require("../masters/trackingLocations/trackingLocation.model")(sequelize);
    dbSAPPS.BloodGroup = require("../masters/bloodGroups/bloodGroup.model")(sequelize);
    dbSAPPS.EducationLevel = require("../masters/educationLevels/educationLevel.model")(sequelize);
    dbSAPPS.PlacementArea = require("../masters/placementAreas/placementArea.model")(sequelize);
    dbSAPPS.Zone = require("../masters/zones/zone.model")(sequelize);
    dbSAPPS.AssetCategory = require("../masters/assetCategories/assetCategory.model")(sequelize);
    dbSAPPS.AssetManufacturer = require("../masters/assetManufacturers/assetManufacturer.model")(sequelize);
    dbSAPPS.AssetStatusLabel = require("../masters/assetStatusLabels/assetStatusLabel.model")(sequelize);
    dbSAPPS.AssetLocation = require("../masters/assetLocations/assetLocation.model")(sequelize);
    dbSAPPS.AssetModel = require("../masters/assetModels/assetModel.model")(sequelize);
    dbSAPPS.Asset = require("../masters/assets/asset.model")(sequelize);
    dbSAPPS.AssetLog = require("../masters/assets/assetLog.model")(sequelize);
    dbSAPPS.AssetMaintenance = require("../masters/assets/assetMaintenance.model")(sequelize);
    dbSAPPS.License = require("../masters/assets/license.model")(sequelize);
    dbSAPPS.LicenseSeat = require("../masters/assets/licenseSeat.model")(sequelize);
    dbSAPPS.Accessory = require("../masters/assets/accessory.model")(sequelize);
    dbSAPPS.AccessoryCheckout = require("../masters/assets/accessoryCheckout.model")(sequelize);
    dbSAPPS.Consumable = require("../masters/assets/consumable.model")(sequelize);
    dbSAPPS.ConsumableCheckout = require("../masters/assets/consumableCheckout.model")(sequelize);
    dbSAPPS.Component = require("../masters/assets/component.model")(sequelize);
    dbSAPPS.ComponentAsset = require("../masters/assets/componentAsset.model")(sequelize);
    dbSAPPS.AssetLoan = require("../masters/assets/assetLoan.model")(sequelize);
    dbSAPPS.AssetLoanItem = require("../masters/assets/assetLoanItem.model")(sequelize);
    dbSAPPS.HandoverRequest = require("../masters/handovers/handoverRequest.model")(sequelize);
    dbSAPPS.HandoverCompletion = require("../masters/handovers/handoverCompletion.model")(sequelize);
    dbSAPPS.HandoverFeature = require("../masters/handovers/handoverFeature.model")(sequelize);
    dbSAPPS.HandoverTest = require("../masters/handovers/handoverTest.model")(sequelize);

    dbSAPPS.Occupation = require("../masters/occupations/occupation.model")(sequelize);
    dbSAPPS.Nationality = require("../masters/nationalities/nationality.model")(sequelize);
    dbSAPPS.UcSetting = require("../masters/ucSettings/ucSetting.model")(sequelize);
    dbSAPPS.UpCountry = require("../masters/upCountries/upCountry.model")(sequelize);
    dbSAPPS.UpCountryDistance = require("../masters/upCountries/upCountryDistance.model")(sequelize);
    dbSAPPS.UpCountryVisitList = require("../masters/upCountries/upCountryVisitList.model")(sequelize);
    dbSAPPS.UpCountryItinerary = require("../masters/upCountries/upCountryItinerary.model")(sequelize);
    dbSAPPS.UpCountryAccommodation = require("../masters/upCountries/upCountryAccommodation.model")(sequelize);
    dbSAPPS.UcCostRealization = require("../masters/ucCostRealizations/ucCostRealization.model")(sequelize);
    dbSAPPS.ReleaseNote = require("../masters/releaseNotes/releaseNote.model")(sequelize);
    dbSAPPS.HistoryAttendanceRequest = require("../histories/attendanceRequests/historyAttendanceRequest.model")(
      sequelize,
    );
    dbSAPPS.HistoryBudgeting = require("../histories/budgetings/historyBudgeting.model")(sequelize);
    dbSAPPS.Budgeting = require("../masters/budgetings/budgeting.model")(sequelize);
    dbSAPPS.BudgetingItem = require("../masters/budgetings/budgetingItem.model")(sequelize);
    dbSAPPS.BudgetingAccount = require("../masters/budgetings/budgetingAccount.model")(sequelize);
    dbSAPPS.ProcurementTracker = require("../masters/procurementTrackers/procurementTracker.model")(sequelize);
    dbSAPPS.ProcurementTrackerAWB = require("../masters/procurementTrackers/procurementTrackerAWB.model")(sequelize);
    dbSAPPS.ProcurementTrackerStatus = require("../masters/procurementTrackers/procurementTrackerStatus.model")(
      sequelize,
    );
    dbSAPPS.ProcurementTrackerPO = require("../masters/procurementTrackers/procurementTrackerPO.model")(sequelize);
    dbSAPPS.ProcurementTrackerItemPO = require("../masters/procurementTrackers/procurementTrackerItemPO.model")(
      sequelize,
    );
    dbSAPPS.ProcurementTrackerFile = require("../masters/procurementTrackers/procurementTrackerFile.model")(sequelize);
    dbSAPPS.ProcurementTrackerPackingList =
      require("../masters/procurementTrackers/procurementTrackerPackingList.model")(sequelize);
    dbSAPPS.ProcurementTrackerInvoice = require("../masters/procurementTrackers/procurementTrackerInvoice.model")(
      sequelize,
    );
    dbSAPPS.WorkActivity = require("../masters/workActivities/workActivity.model")(sequelize);
    dbSAPPS.WorkActivityItem = require("../masters/workActivities/workActivityItem.model")(sequelize);
    dbSAPPS.WorkActivityItemFile = require("../masters/workActivities/workActivityItemFile.model")(sequelize);
    dbSAPPS.WorkActivityPlan = require("../masters/workActivityPlans/workActivityPlan.model")(sequelize);
    dbSAPPS.WorkActivityPlanItem = require("../masters/workActivityPlans/workActivityPlanItem.model")(sequelize);
    dbSAPPS.Account = require("../masters/accounts/account.model")(sequelize);
    dbSAPPS.BudgetCategory = require("../masters/budgetCategories/budgetCategory.model")(sequelize);
    dbSAPPS.BudgetClassification = require("../masters/budgetClassifications/budgetClassification.model")(sequelize);
    dbSAPPS.BudgetDepartment = require("../masters/budgetDepartments/budgetDepartment.model")(sequelize);
    dbSAPPS.BudgetItem = require("../masters/budgetItems/budgetItem.model")(sequelize);
    dbSAPPS.BudgetAccount = require("../masters/budgetAccounts/budgetAccount.model")(sequelize);
    dbSAPPS.BudgetUnitOfMeasurement = require("../masters/budgetUnitOfMeasurements/budgetUnitOfMeasurement.model")(
      sequelize,
    );
    dbSAPPS.BudgetPlan = require("../masters/budgetPlans/budgetPlan.model")(sequelize);
    dbSAPPS.BudgetPlanItem = require("../masters/budgetPlans/budgetPlanItem.model")(sequelize);
    dbSAPPS.BudgetApprover = require("../masters/budgetApprovers/budgetApprover.model")(sequelize);
    dbSAPPS.TenderCategory = require("../masters/tenderCategories/tenderCategory.model")(sequelize);
    dbSAPPS.Tender = require("../masters/tenders/tender.model")(sequelize);
    dbSAPPS.TenderItem = require("../masters/tenders/tenderItem.model")(sequelize);
    dbSAPPS.TenderFile = require("../masters/tenders/tenderFile.model")(sequelize);
    dbSAPPS.TenderInvitation = require("../masters/tenders/tenderInvitation.model")(sequelize);
    dbSAPPS.TenderQuotation = require("../masters/tenders/tenderQuotation.model")(sequelize);
    dbSAPPS.TenderQuotationFile = require("../masters/tenders/tenderQuotationFile.model")(sequelize);
    dbSAPPS.TenderVendor = require("../masters/tenderVendors/tenderVendor.model")(sequelize);
    dbSAPPS.TenderVendorFile = require("../masters/tenderVendors/tenderVendorFile.model")(sequelize);
    dbSAPPS.TenderPaymentTerm = require("../masters/tenderPaymentTerms/tenderPaymentTerm.model")(sequelize);
    dbSAPPS.SaldoAwalClient = require("../masters/saldoAwalClients/saldoAwalClient.model")(sequelize);
    dbSAPPS.SasaranMutu = require("../masters/sasaranMutus/sasaranMutu.model")(sequelize);

    // IP Tracking Models
    // IP Analytics models (optional - hanya load jika tabel ada, tidak block startup jika belum ada)
    // LAZY-LOAD: Model di-load dengan error handling untuk menghindari startup delay jika tabel belum ada
    try {
      dbSAPPS.ConnectionAttempt = require("./models/connection_attempts")(sequelize, Sequelize);
      dbSAPPS.ISPStatistics = require("./models/isp_statistics")(sequelize, Sequelize);
      dbSAPPS.BlockedIP = require("./models/blocked_ips")(sequelize, Sequelize);
    } catch (ipErr) {
      // IP Analytics models optional - log warning tapi jangan block startup
      console.warn("[DB Init] IP Analytics models skipped (tables may not exist):", ipErr.message);
      // Set to null so routes can check before using
      dbSAPPS.ConnectionAttempt = null;
      dbSAPPS.ISPStatistics = null;
      dbSAPPS.BlockedIP = null;
    }

    // health
    dbSAPPS.Patient = require("../masters/patients/patient.model")(sequelize);
    dbSAPPS.Drug = require("../masters/drugs/drug.model")(sequelize);
    dbSAPPS.DrugGroup = require("../masters/drugGroups/drugGroup.model")(sequelize);
    dbSAPPS.DrugClassification = require("../masters/drugClassifications/drugClassification.model")(sequelize);
    dbSAPPS.DosageForm = require("../masters/dosageForms/dosageForm.model")(sequelize);
    dbSAPPS.DosageUnit = require("../masters/dosageUnits/dosageUnit.model")(sequelize);
    dbSAPPS.DosageType = require("../masters/dosageTypes/dosageType.model")(sequelize);
    dbSAPPS.RouteAdministration = require("../masters/routeAdministrations/routeAdministration.model")(sequelize);
    dbSAPPS.Manufacturer = require("../masters/manufacturers/manufacturer.model")(sequelize);
    dbSAPPS.Dosage = require("../masters/dosages/dosage.model")(sequelize);
    dbSAPPS.AdministrationRoute = require("../masters/administrationRoutes/administrationRoute.model")(sequelize);
    dbSAPPS.Medicine = require("../masters/medicines/medicine.model")(sequelize);
    dbSAPPS.Prescription = require("../masters/prescriptions/prescription.model")(sequelize);
    dbSAPPS.PrescriptionMedicine = require("../masters/prescriptions/prescriptionMedicine.model")(sequelize);
    dbSAPPS.Doctor = require("../masters/doctors/doctor.model")(sequelize);
    dbSAPPS.BloodPressureMonitoring = require("../masters/bloodPressureMonitorings/bloodPressureMonitoring.model")(
      sequelize,
    );
    dbSAPPS.BloodPressureMonitoringMedicine =
      require("../masters/bloodPressureMonitorings/bloodPressureMonitoringMedicine.model")(sequelize);
    dbSAPPS.Age = require("../classifications/ages/age.model")(sequelize);
    dbSAPPS.BodyTemperature = require("../classifications/bodyTemperatures/bodyTemperature.model")(sequelize);
    dbSAPPS.Pulse = require("../classifications/pulses/pulse.model")(sequelize);
    dbSAPPS.RespiratoryRate = require("../classifications/respiratoryRates/respiratoryRate.model")(sequelize);
    dbSAPPS.BloodPressure = require("../classifications/bloodPressures/bloodPressure.model")(sequelize);
    dbSAPPS.PulseAgeRate = require("../classifications/pulseAgeRates/pulseAgeRate.model")(sequelize);
    dbSAPPS.MedicalExamination = require("../masters/medicalExaminations/medicalExamination.model")(sequelize);
    dbSAPPS.MedicalExaminationItem = require("../masters/medicalExaminations/medicalExaminationItem.model")(sequelize);
    dbSAPPS.Hospital = require("../masters/hospitals/hospital.model")(sequelize);

    /**
     * @namespace dbSAPPS
     * @memberof dbSAPPS
     * @description Models in this namespace are created inside `junctions` folder
     */
    dbSAPPS.RoleMenu = require("../junctions/roleMenus/roleMenu.model")(sequelize);
    dbSAPPS.UserProject = require("../junctions/userProjects/userProject.model")(sequelize);
    dbSAPPS.UserCompany = require("../junctions/userCompanies/userCompany.model")(sequelize);
    dbSAPPS.EmailAccountProject = require("../junctions/emailAccountProjects/emailAccountProject.model")(sequelize);
    dbSAPPS.EmployeeTerritory = require("../junctions/employeeTerritories/employeeTerritory.model")(sequelize);
    dbSAPPS.OutletClient = require("../junctions/outletClients/outletClient.model")(sequelize);
    dbSAPPS.TerritoryItemGroup = require("../junctions/territoryItemGroups/territoryItemGroup.model")(sequelize);
    dbSAPPS.CourseImage = require("../junctions/courseImages/courseImage.model")(sequelize);
    dbSAPPS.CourseDocument = require("../junctions/courseDocuments/courseDocument.model")(sequelize);
    dbSAPPS.CourseVideo = require("../junctions/courseVideos/courseVideo.model")(sequelize);
    dbSAPPS.TerritoryOutlet = require("../junctions/territoryOutlet/territoryOutlet.model")(sequelize);
    dbSAPPS.ProjectCourse = require("../junctions/projectCourses/projectCourse.model")(sequelize);
    dbSAPPS.CourseQuestionBank = require("../junctions/courseQuestionBanks/courseQuestionBank.model")(sequelize);
    dbSAPPS.CourseDevelopment = require("../junctions/courseDevelopments/courseDevelopment.model")(sequelize);
    dbSAPPS.MasterList = require("../junctions/masterLists/masterList.model")(sequelize);

    /**
     * Function to manage models which have association
     * @param {Object} sequelize - Sequelize instance
     * @param {Object} dbSAPPS - Database models
     * @returns {Promise<void>}
     */

    // Start monitoring associate initialization
    associateMonitoring.startTime = Date.now();
    associateMonitoring.initialized = false;
    associateMonitoring.errors = [];
    associateMonitoring.associationsByModel = {};

    console.log("========================================");
    console.log("Initializing model associations...");
    console.log("========================================");

    logAssociation("Association initialization started", {
      startTime: new Date(associateMonitoring.startTime).toISOString(),
    });

    Object.keys(dbSAPPS).forEach((modelName) => {
      try {
        associateMonitoring.modelsProcessed++;

        if (dbSAPPS[modelName] && typeof dbSAPPS[modelName].associate === "function") {
          const associationStartTime = Date.now();
          dbSAPPS[modelName].associate(dbSAPPS);
          const associationDuration = Date.now() - associationStartTime;

          associateMonitoring.modelsWithAssociations++;

          // Track associations for this model
          associateMonitoring.associationsByModel[modelName] = {
            hasAssociations: true,
            duration: associationDuration,
            timestamp: new Date().toISOString(),
          };

          console.log(`✓ Association loaded for model: ${modelName} (${associationDuration}ms)`);
          logAssociation(`Association loaded for model: ${modelName}`, {
            model: modelName,
            duration: associationDuration,
            status: "success",
          });
        } else {
          associateMonitoring.modelsWithoutAssociations++;
          associateMonitoring.associationsByModel[modelName] = {
            hasAssociations: false,
            timestamp: new Date().toISOString(),
          };
          logAssociation(`Model without associations: ${modelName}`, {
            model: modelName,
            status: "no_associations",
          });
        }
      } catch (error) {
        const errorInfo = {
          model: modelName,
          error: error.message,
          stack: error.stack,
          timestamp: new Date().toISOString(),
        };
        associateMonitoring.errors.push(errorInfo);
        console.error(`✗ Error loading associations for model: ${modelName}`, error.message);
        logAssociation(`Error loading associations for model: ${modelName}`, {
          ...errorInfo,
          status: "error",
        });
      }
    });

    // Complete monitoring
    associateMonitoring.endTime = Date.now();
    associateMonitoring.duration = associateMonitoring.endTime - associateMonitoring.startTime;
    associateMonitoring.initialized = true;

    console.log("========================================");
    console.log("Association initialization completed:");
    console.log(`  - Total models processed: ${associateMonitoring.modelsProcessed}`);
    console.log(`  - Models with associations: ${associateMonitoring.modelsWithAssociations}`);
    console.log(`  - Models without associations: ${associateMonitoring.modelsWithoutAssociations}`);
    console.log(`  - Errors: ${associateMonitoring.errors.length}`);
    console.log(`  - Duration: ${associateMonitoring.duration}ms`);
    console.log("========================================");

    // Log completion summary
    logAssociation("Association initialization completed", {
      endTime: new Date(associateMonitoring.endTime).toISOString(),
      duration: associateMonitoring.duration,
      modelsProcessed: associateMonitoring.modelsProcessed,
      modelsWithAssociations: associateMonitoring.modelsWithAssociations,
      modelsWithoutAssociations: associateMonitoring.modelsWithoutAssociations,
      errorCount: associateMonitoring.errors.length,
      status: associateMonitoring.errors.length === 0 ? "success" : "completed_with_errors",
    });

    if (associateMonitoring.errors.length > 0) {
      console.error("========================================");
      console.error("Association Errors:");
      associateMonitoring.errors.forEach((err, index) => {
        console.error(`  ${index + 1}. Model: ${err.model}`);
        console.error(`     Error: ${err.error}`);
      });
      console.error("========================================");
    }

    await sequelize.sync({ alter: false, force: false, logging: false });

    // Verify key associations are properly set up
    try {
      // Verify UserCompany association with Company
      if (!dbSAPPS.UserCompany || !dbSAPPS.UserCompany.associations || !dbSAPPS.UserCompany.associations.company) {
        throw new Error("UserCompany association with Company is not properly initialized");
      }

      // Verify UserCompany association with User
      if (!dbSAPPS.UserCompany.associations.user) {
        throw new Error("UserCompany association with User is not properly initialized");
      }

      logAssociation("Key associations verified", {
        userCompanyToCompany: !!dbSAPPS.UserCompany.associations.company,
        userCompanyToUser: !!dbSAPPS.UserCompany.associations.user,
        status: "verified",
      });
    } catch (verifyError) {
      associateMonitoring.errors.push({
        model: "VERIFICATION",
        error: verifyError.message,
        timestamp: new Date().toISOString(),
      });
      logAssociation("Association verification failed", {
        error: verifyError.message,
        status: "verification_failed",
      });
      console.error("[DB Initialization] Association verification failed:", verifyError.message);
    }

    isInitialized = true;
    console.log("========================================");
    console.log("Database connected and models synced successfully (SAPPS).");
    console.log("Connection manager: ACTIVE with retry mechanism");
    console.log("Associations: INITIALIZED and VERIFIED");
    console.log("Status: READY to accept requests");
    console.log("========================================");

    logAssociation("Database initialization completed successfully", {
      status: "ready",
      isInitialized: true,
    });
    return dbSAPPS; // Return dbSAPPS on success
  } catch (error) {
    isInitialized = false;
    initializationError = error;
    associateMonitoring.initialized = false;
    associateMonitoring.errors.push({
      model: "SYSTEM",
      error: error.message,
      stack: error.stack,
      timestamp: new Date().toISOString(),
    });

    console.error("========================================");
    console.error("Unable to connect to the database: ", error);
    console.error("Connection attempts:", dbConnectionManager.connectionAttempts);
    console.error("Association initialization: FAILED");
    console.error("Status: NOT READY - will retry");
    console.error("========================================");

    logAssociation("Database initialization failed", {
      error: error.message,
      connectionAttempts: dbConnectionManager.connectionAttempts,
      status: "failed",
    });

    // Don't re-throw here; allow background retry
    return Promise.reject(error);
  }
}

/**
 * Get associate monitoring status
 * @returns {Object} Associate monitoring information
 */
function getAssociateStatus() {
  return {
    initialized: associateMonitoring.initialized,
    startTime: associateMonitoring.startTime ? new Date(associateMonitoring.startTime).toISOString() : null,
    endTime: associateMonitoring.endTime ? new Date(associateMonitoring.endTime).toISOString() : null,
    duration: associateMonitoring.duration,
    modelsProcessed: associateMonitoring.modelsProcessed,
    modelsWithAssociations: associateMonitoring.modelsWithAssociations,
    modelsWithoutAssociations: associateMonitoring.modelsWithoutAssociations,
    errorCount: associateMonitoring.errors.length,
    errors: associateMonitoring.errors,
    status:
      associateMonitoring.initialized && associateMonitoring.errors.length === 0
        ? "healthy"
        : associateMonitoring.initialized && associateMonitoring.errors.length > 0
          ? "degraded"
          : "failed",
    timestamp: new Date().toISOString(),
  };
}

/**
 * Verify associations are working correctly
 * @param {Object} models - Database models to verify (optional, defaults to dbSAPPS)
 * @returns {Object} Verification result
 */
function verifyAssociations(models = dbSAPPS) {
  const verificationResults = {
    verified: true,
    verifiedAt: new Date().toISOString(),
    checks: [],
    errors: [],
  };

  // Sample check: Verify key models have their associations
  const keyModels = ["User", "Company", "Item", "Client", "Attendance", "Employee"];

  keyModels.forEach((modelName) => {
    try {
      if (!models[modelName]) {
        verificationResults.verified = false;
        verificationResults.errors.push({
          model: modelName,
          error: "Model not found in dbSAPPS",
        });
        return;
      }

      const model = models[modelName];
      const hasAssociations = typeof model.associate === "function";
      const associationsDefined = model.associations && Object.keys(model.associations).length > 0;

      verificationResults.checks.push({
        model: modelName,
        hasAssociateFunction: hasAssociations,
        associationsDefined: associationsDefined,
        associationCount: associationsDefined ? Object.keys(model.associations).length : 0,
        status: associationsDefined ? "ok" : "warning",
      });

      if (!associationsDefined && hasAssociations) {
        verificationResults.verified = false;
        verificationResults.errors.push({
          model: modelName,
          error: "Model has associate function but no associations are defined",
        });
      }
    } catch (error) {
      verificationResults.verified = false;
      verificationResults.errors.push({
        model: modelName,
        error: error.message,
      });
    }
  });

  return verificationResults;
}

/**
 * Wait for database initialization to complete
 * @returns {Promise<Object>} Promise that resolves when initialization is complete
 */
async function waitForInitialization(maxWaitTime = 60000) {
  // If already initialized and ready, return immediately
  if (isInitialized && associateMonitoring.initialized && !initializationError) {
    return dbSAPPS;
  }

  // If initialization error exists but no promise, surface the error
  if (initializationError && !initializationPromise) {
    // Try one more retry before giving up
    retryInitialization();
    if (!initializationPromise) {
      throw initializationError;
    }
  }

  // If no promise exists, start initialization
  if (!initializationPromise) {
    retryInitialization();
    if (!initializationPromise) {
      throw new Error("Initialization not started and cannot be started");
    }
  }

  // Wait for initialization with timeout
  const timeoutPromise = new Promise((_, reject) => {
    setTimeout(() => {
      reject(
        new Error(`Initialization timeout after ${maxWaitTime / 1000}s. Check database connection and configuration.`),
      );
    }, maxWaitTime);
  });

  try {
    // Wait for initialization promise to resolve
    await Promise.race([initializationPromise, timeoutPromise]);

    // Double-check that initialization is truly complete
    // Wait a bit more to ensure all associations are set up
    if (!isInitialized || !associateMonitoring.initialized) {
      // Wait a bit more (up to 5 seconds) for associations
      let checkCount = 0;
      const maxChecks = 50; // 50 * 100ms = 5 seconds
      while (checkCount < maxChecks && (!isInitialized || !associateMonitoring.initialized)) {
        await new Promise((resolve) => setTimeout(resolve, 100));
        checkCount++;
      }

      if (!isInitialized || !associateMonitoring.initialized) {
        throw new Error("Initialization completed but associations not ready");
      }
    }

    return dbSAPPS;
  } catch (error) {
    // If timeout or error, throw with clear message
    throw error;
  }
}

/**
 * Check if database is initialized and ready
 * @returns {boolean} True if initialized and ready
 */
function isReady() {
  return isInitialized && associateMonitoring.initialized && !initializationError;
}

/**
 * Get initialization status
 * @returns {Object} Initialization status information
 */
function getInitializationStatus() {
  return {
    isInitialized,
    isReady: isReady(),
    initializationError: initializationError
      ? {
        message: initializationError.message,
        name: initializationError.name,
      }
      : null,
    associateStatus: getAssociateStatus(),
    timestamp: new Date().toISOString(),
  };
}

// Export monitoring functions and initialization utilities
dbSAPPS.getAssociateStatus = getAssociateStatus;
dbSAPPS.verifyAssociations = verifyAssociations;
dbSAPPS.associateMonitoring = associateMonitoring;
dbSAPPS.waitForInitialization = waitForInitialization;
dbSAPPS.isReady = isReady;
dbSAPPS.getInitializationStatus = getInitializationStatus;
dbSAPPS.initializationPromise = initializationPromise;

// Export isConnectionManagerClosed method from connection manager
// This allows checking if the connection manager has been closed before attempting to use it
dbSAPPS.isConnectionManagerClosed = function () {
  if (dbConnectionManager && typeof dbConnectionManager.isConnectionManagerClosed === "function") {
    return dbConnectionManager.isConnectionManagerClosed();
  }
  return false; // Default to false if method not available
};

/**
 * Retry initialization in background with backoff
 */
function retryInitialization(delayMs = 30000) {
  if (isInitialized || isInitializing) {
    return initializationPromise;
  }
  isInitializing = true;
  initializationPromise = initialize()
    .then((res) => {
      isInitializing = false;
      initializationError = null;
      return res;
    })
    .catch((err) => {
      isInitializing = false;
      initializationError = err;
      // schedule next retry
      setTimeout(() => {
        retryInitialization(Math.min(delayMs * 2, 300000)); // cap at 5 minutes
      }, delayMs);
      return Promise.reject(err);
    });
  return initializationPromise;
}

dbSAPPS.retryInitialization = retryInitialization;
