const db = require('../models');
const { sendResponse } = require('../utils/response');
const asyncHandler = require('../middlewares/asyncHandler');
const { getPagination, getPagingData } = require('../utils/pagination');
const { Op } = require('sequelize');
const bcrypt = require('bcryptjs');
const { generateNextNumber } = require('../services/numberFormatService');
const { ROUTE_CARD_PDF_ROLE_PATTERNS } = require('../constants/routeCardPdfRolePatterns');

const createMasterController = (Model, modelName) => {
  return {
    getAll: asyncHandler(async (req, res) => {
      const { page = 1, limit = 10, search, status, companyId, company_id, country_id, state_id, plantId, plant_id, fiscal_year_id, material_id, all_companies } = req.query;
      const { limit: limitNum, offset } = getPagination(page, limit);

      const where = {};
      
      const attributes = Model.getAttributes();
      if (attributes.company_id) {
        // Support both camelCase and snake_case
        const companyIdValue = companyId || company_id;
        
        // Check if all_companies flag is set (explicit request to see all companies)
        const showAllCompanies = all_companies === 'true' || all_companies === true;
        const isSuperAdmin = req.user && (req.user.isSuperAdmin || req.user.is_super_admin);
        
        if (showAllCompanies && isSuperAdmin) {
          // Super admin explicitly requested all companies - don't filter by company_id
          // Do nothing - where.company_id will not be set
        } else if (companyIdValue && companyIdValue !== 'null' && companyIdValue !== 'undefined' && companyIdValue !== '') {
          // Only filter by company_id if explicitly provided and not empty/null
          // Convert to number to ensure proper type matching with database
          const companyIdNum = typeof companyIdValue === 'string' 
            ? parseInt(companyIdValue, 10) 
            : Number(companyIdValue);
          if (!isNaN(companyIdNum)) {
            where.company_id = companyIdNum;
            console.log(`[${modelName}] Filtering by company_id:`, companyIdNum, 'Type:', typeof companyIdNum, 'Raw value:', companyIdValue);
          } else {
            console.warn(`[${modelName}] Invalid company_id value:`, companyIdValue);
          }
        } else if (!isSuperAdmin && req.user && req.user.companyId) {
          // For non-super admin users, default to their company only if no company_id is provided
          where.company_id = req.user.companyId;
        }
        // If companyIdValue is not provided and user is super admin, don't filter by company_id
        // This allows super admins to see all companies by default
      }

      // Support filtering by country_id
      if (country_id && attributes.country_id) {
        where.country_id = country_id;
      }

      // Support filtering by state_id
      if (state_id && attributes.state_id) {
        where.state_id = state_id;
      }

      // Support filtering by plant_id (both camelCase and snake_case)
      const plantIdValue = plantId || plant_id;
      if (plantIdValue && attributes.plant_id) {
        where.plant_id = plantIdValue;
      }

      // Support filtering by fiscal_year_id (ignore if model doesn't have this field)
      if (fiscal_year_id && attributes.fiscal_year_id) {
        where.fiscal_year_id = fiscal_year_id;
      }

      // Support filtering by material_id
      if (material_id && attributes.material_id) {
        where.material_id = material_id;
      }

      // Support filtering by tax_type for TaxMaster
      if (req.query.tax_type && attributes.tax_type) {
        where.tax_type = req.query.tax_type;
      }

      if (search) {
        const searchFields = Model.getAttributes();
        const searchConditions = [];
        
        Object.keys(searchFields).forEach((field) => {
          if (searchFields[field].type && searchFields[field].type.constructor.name === 'STRING') {
            searchConditions.push({ [field]: { [Op.like]: `%${search}%` } });
          }
        });
        
        if (searchConditions.length > 0) {
          where[Op.or] = searchConditions;
        }
      }

      // Handle status filtering - some models use 'status' (enum), others use 'is_active' (boolean)
      if (status) {
        if (attributes.status) {
          // Model has status field (enum)
          where.status = status;
        } else if (attributes.is_active) {
          // Model has is_active field (boolean) - convert status to boolean
          where.is_active = status === 'ACTIVE' || status === 'active' || status === true;
        }
      } else {
        // Default filtering when no status is provided
        if (attributes.status) {
          where.status = { [Op.ne]: 'DELETED' };
        }
        // Don't filter by is_active by default - show all records
        // Users can filter by status if needed
      }

      // Support direct is_active filtering for models that use boolean is_active
      if (req.query.is_active !== undefined && attributes.is_active) {
        where.is_active = req.query.is_active === 'true' || req.query.is_active === true || req.query.is_active === 1;
      }

      // Filter RolePermission by role_id so assignment screen loads only that role's permissions
      if (modelName === 'RolePermission' && req.query.role_id != null && req.query.role_id !== '') {
        const roleIdVal = parseInt(req.query.role_id, 10);
        if (!isNaN(roleIdVal)) {
          where.role_id = roleIdVal;
        }
      }

      // Special handling for BPAddress and BPGSTIN to include state with gst_state_code
      const includeOptions = [];
      let userRoleFilterActive = false;

      // Special handling for RoutingOperation: include routing/workCenter/machine for list & view screens
      if (modelName === 'RoutingOperation') {
        includeOptions.push(
          {
            model: db.Routing,
            as: 'routing',
            attributes: ['id', 'routing_number'],
            required: false,
          },
          {
            model: db.WorkCenter,
            as: 'workCenter',
            attributes: ['id', 'work_center_code', 'description'],
            required: false,
          },
          {
            model: db.Machine,
            as: 'machine',
            attributes: ['id', 'machine_code', 'machine_description'],
            required: false,
          }
        );
      }

      // Special handling for ProductionOperator: include operation name and work center
      if (modelName === 'ProductionOperator') {
        includeOptions.push(
          {
            model: db.OperationName,
            as: 'operationName',
            attributes: ['id', 'operation_code', 'operation_no', 'description'],
            required: false,
          },
          {
            model: db.WorkCenter,
            as: 'workCenter',
            attributes: ['id', 'work_center_code', 'description'],
            required: false,
          },
          {
            model: db.Company,
            as: 'company',
            attributes: ['id', 'code', 'name'],
            required: false,
          }
        );
      }
      
      // Special handling for RolePermission to include permission and user info
      if (modelName === 'RolePermission') {
        includeOptions.push({
          model: db.Permission,
          as: 'permission',
          attributes: ['id', 'permission_code', 'permission_name', 'module', 'resource', 'operation'],
          required: false
        });
        includeOptions.push({
          model: db.User,
          as: 'createdByUser',
          foreignKey: 'created_by',
          attributes: ['id', 'username', 'first_name', 'last_name'],
          required: false
        });
        includeOptions.push({
          model: db.User,
          as: 'updatedByUser',
          foreignKey: 'updated_by',
          attributes: ['id', 'username', 'first_name', 'last_name'],
          required: false
        });
      }

      // Special handling for StorageLocation to include plant and company
      if (modelName === 'StorageLocation') {
        includeOptions.push({
          model: db.Plant,
          as: 'plant',
          attributes: ['id', 'code', 'name'],
          required: false
        });
        includeOptions.push({
          model: db.Company,
          as: 'company',
          attributes: ['id', 'code', 'name'],
          required: false
        });
      }

      // Special handling for OperationName to include department
      if (modelName === 'OperationName') {
        includeOptions.push({
          model: db.Department,
          as: 'department',
          attributes: ['id', 'department_code', 'department_name'],
          required: false,
        });
      }

      // Special handling for OperationCode to include company
      if (modelName === 'OperationCode') {
        includeOptions.push({
          model: db.Company,
          as: 'company',
          attributes: ['id', 'code', 'name'],
          required: false,
        });
      }

      // Special handling for User to include roles and department
      if (modelName === 'User') {
        let userRoleFilterPatterns = null;
        if (
          String(req.query.production_manager_approvers || '').toLowerCase() === 'true' ||
          String(req.query.production_manager_approvers || '') === '1'
        ) {
          userRoleFilterActive = true;
          userRoleFilterPatterns = ROUTE_CARD_PDF_ROLE_PATTERNS.production_manager;
        }
        const rcPdfRole = String(req.query.route_card_pdf_role || '')
          .trim()
          .toLowerCase()
          .replace(/-/g, '_');
        if (ROUTE_CARD_PDF_ROLE_PATTERNS[rcPdfRole]) {
          userRoleFilterActive = true;
          userRoleFilterPatterns = ROUTE_CARD_PDF_ROLE_PATTERNS[rcPdfRole];
        }

        const rolesInclude = {
          model: db.Role,
          as: 'roles',
          through: userRoleFilterActive
            ? { where: { status: 'ACTIVE' }, attributes: [] }
            : { attributes: [] },
          attributes: ['id', 'role_code', 'role_name'],
          required: userRoleFilterActive,
        };

        if (userRoleFilterActive && userRoleFilterPatterns) {
          const rawCid = where.company_id;
          const cid =
            rawCid != null && typeof rawCid !== 'object' && !Number.isNaN(Number(rawCid))
              ? Number(rawCid)
              : req.user && req.user.companyId != null
                ? Number(req.user.companyId)
                : NaN;
          rolesInclude.where = {
            status: 'ACTIVE',
            ...(Number.isFinite(cid)
              ? {
                  [Op.and]: [
                    { [Op.or]: userRoleFilterPatterns },
                    { [Op.or]: [{ company_id: null }, { company_id: cid }] },
                  ],
                }
              : { [Op.or]: userRoleFilterPatterns }),
          };
        }

        includeOptions.push(rolesInclude);
        includeOptions.push({
          model: db.Department,
          as: 'department',
          attributes: ['id', 'department_code', 'department_name'],
          required: false
        });
        includeOptions.push({
          model: db.Employee,
          as: 'employee',
          attributes: ['id', 'employee_number', 'first_name', 'last_name'],
          required: false
        });
        includeOptions.push({
          model: db.Company,
          as: 'company',
          attributes: ['id', 'code', 'name'],
          required: false
        });
      }
      
      if (modelName === 'BPAddress') {
        includeOptions.push({
          model: db.State,
          as: 'state',
          attributes: ['id', 'state_code', 'state_name', 'gst_state_code']
        });
        includeOptions.push({
          model: db.City,
          as: 'city',
          attributes: ['id', 'city_name']
        });
      }

      // Include state for BPGSTIN
      if (modelName === 'BPGSTIN') {
        includeOptions.push({
          model: db.State,
          as: 'state',
          attributes: ['id', 'state_code', 'state_name', 'gst_state_code']
        });
        includeOptions.push({
          model: db.City,
          as: 'city',
          attributes: ['id', 'city_name']
        });
        includeOptions.push({
          model: db.Country,
          as: 'country',
          attributes: ['id', 'country_code', 'country_name']
        });
      }

      // Special handling for PrintHeader to include company
      if (modelName === 'PrintHeader') {
        includeOptions.push({
          model: db.Company,
          as: 'company',
          attributes: ['id', 'code', 'name']
        });
      }

      // Special handling for Department to include parent department
      if (modelName === 'Department') {
        includeOptions.push({
          model: db.Department,
          as: 'parentDepartment',
          attributes: ['id', 'department_code', 'department_name'],
          required: false
        });
        includeOptions.push({
          model: db.Company,
          as: 'company',
          attributes: ['id', 'code', 'name'],
          required: false
        });
      }

      // Special handling for CostCenter to include related masters
      if (modelName === 'CostCenter') {
        includeOptions.push({
          model: db.Company,
          as: 'company',
          attributes: ['id', 'code', 'name'],
          required: false
        });
        includeOptions.push({
          model: db.CostCenter,
          as: 'parentCostCenter',
          attributes: ['id', 'cost_center_code', 'description'],
          required: false
        });
        includeOptions.push({
          model: db.Department,
          as: 'department',
          attributes: ['id', 'department_code', 'department_name'],
          required: false
        });
        includeOptions.push({
          model: db.OperationName,
          as: 'operationName',
          attributes: ['id', 'operation_code', 'operation_no', 'description'],
          required: false
        });
        includeOptions.push({
          model: db.Employee,
          as: 'responsiblePerson',
          attributes: ['id', 'employee_number', 'first_name', 'last_name'],
          required: false
        });
        includeOptions.push({
          model: db.Currency,
          as: 'currency',
          attributes: ['id', 'currency_code', 'currency_name'],
          required: false
        });
      }

      // Special handling for MaterialSubstitutionMaster to include materials
      if (modelName === 'MaterialSubstitutionMaster') {
        includeOptions.push({
          model: db.Material,
          as: 'originalMaterial',
          attributes: ['id', 'material_number', 'description'],
          required: false
        });
        includeOptions.push({
          model: db.Material,
          as: 'substituteMaterial',
          attributes: ['id', 'material_number', 'description'],
          required: false
        });
        includeOptions.push({
          model: db.Company,
          as: 'company',
          attributes: ['id', 'code', 'name'],
          required: false
        });
        includeOptions.push({
          model: db.Plant,
          as: 'plant',
          attributes: ['id', 'code', 'name'],
          required: false
        });
      }

      // Special handling for BusinessPartner to include vendorCategory
      if (modelName === 'BusinessPartner') {
        includeOptions.push({
          model: db.VendorCategory,
          as: 'vendorCategory',
          attributes: ['id', 'category_code', 'category_name', 'gst_reduction_percentage', 'applies_to_raw_materials', 'applies_to_semi_finished']
        });
      }

      // Special handling for Material to include thickness and grade
      if (modelName === 'Material') {
        includeOptions.push({
          model: db.Company,
          as: 'company',
          attributes: ['id', 'code', 'name'],
          required: false
        });
        includeOptions.push({
          model: db.MaterialThickness,
          as: 'materialThickness',
          attributes: ['id', 'thickness_code', 'thickness_value', 'thickness_unit', 'description'],
          required: false
        });
        includeOptions.push({
          model: db.MaterialGrade,
          as: 'materialGrade',
          attributes: ['id', 'grade_code', 'grade_name', 'description'],
          required: false
        });
        includeOptions.push({
          model: db.MaterialType,
          as: 'materialType',
          attributes: ['id', 'type_code', 'type_name'],
          required: false
        });
        includeOptions.push({
          model: db.MaterialGroup,
          as: 'materialGroup',
          attributes: ['id', 'group_code', 'group_name'],
          required: false
        });
        includeOptions.push({
          model: db.MaterialSheetType,
          as: 'materialSheetType',
          attributes: ['id', 'sheet_type_code', 'sheet_type_name'],
          required: false
        });
        includeOptions.push({
          model: db.UOM,
          as: 'weightUOM',
          attributes: ['id', 'uom_code', 'uom_name'],
          required: false
        });
        includeOptions.push({
          model: db.UOM,
          as: 'baseUOM',
          attributes: ['id', 'uom_code', 'uom_name'],
          required: false
        });
        includeOptions.push({
          model: db.UOM,
          as: 'volumeUOM',
          attributes: ['id', 'uom_code', 'uom_name'],
          required: false
        });
        includeOptions.push({
          model: db.TaxMaster,
          as: 'taxMaster',
          attributes: ['id', 'tax_code', 'tax_name', 'tax_rate'],
          required: false
        });
        // Plant-level opening balance (unit cost + GST) for PO / costing defaults
        if (req.query.include_material_plant === 'true' || req.query.includeMaterialPlant === 'true') {
          const pid = parseInt(req.query.plantId || req.query.plant_id, 10);
          if (Number.isFinite(pid) && pid > 0) {
            includeOptions.push({
              model: db.MaterialPlant,
              as: 'materialPlants',
              where: { plant_id: pid, status: 'ACTIVE' },
              required: false,
              attributes: [
                'id',
                'plant_id',
                'opening_balance_unit_cost',
                'opening_balance_cgst_rate',
                'opening_balance_sgst_rate',
                'opening_balance_igst_rate',
                'opening_balance',
                'storage_location',
                'status',
              ],
            });
          }
        }
        // Include Material Suppliers when explicitly requested (for RFQ/Purchase flows)
        // Use query parameter include_suppliers=true to load suppliers
        if (req.query.include_suppliers === 'true' || req.query.includeSuppliers === 'true') {
          includeOptions.push({
            model: db.MaterialSupplier,
            as: 'suppliers',
            where: { status: 'ACTIVE' },
            required: false,
            include: [
              {
                model: db.BusinessPartner,
                as: 'vendor',
                attributes: ['id', 'bp_number', 'name', 'bp_type'],
                required: false
              },
              {
                model: db.Currency,
                as: 'currency',
                attributes: ['id', 'currency_code', 'currency_name'],
                required: false
              }
            ]
          });
        }
      }

      if (modelName === 'CompanyBankDetail') {
        includeOptions.push({
          model: db.Company,
          as: 'company',
          attributes: ['id', 'code', 'name'],
          required: false
        });
      }

      // Support filtering by bpId for BPAddress and BPGSTIN
      if ((modelName === 'BPAddress' || modelName === 'BPGSTIN') && req.query.bpId) {
        where.bp_id = req.query.bpId;
      }

      // Support filtering by vendor_category_id or vendor_type_id for BusinessPartner
      if (modelName === 'BusinessPartner') {
        if (req.query.vendor_category_id) {
          where.vendor_category_id = req.query.vendor_category_id;
        }
        if (req.query.vendor_type_id) {
          where.vendor_type_id = req.query.vendor_type_id;
        }
        // If both are provided, use vendor_category_id as primary
        if (req.query.vendor_category_id && req.query.vendor_type_id) {
          where.vendor_category_id = req.query.vendor_category_id;
          where.vendor_type_id = req.query.vendor_category_id;
        }
        // Support filtering by bp_type (CUSTOMER, VENDOR, BOTH)
        if (req.query.bp_type) {
          if (req.query.bp_type === 'VENDOR') {
            where.bp_type = { [Op.in]: ['VENDOR', 'BOTH'] };
          } else if (req.query.bp_type === 'CUSTOMER') {
            where.bp_type = { [Op.in]: ['CUSTOMER', 'BOTH'] };
          } else {
            where.bp_type = req.query.bp_type;
          }
        }
      }

      // Special ordering for different models
      let orderBy;
      if (modelName === 'RolePermission') {
        // RolePermission - order by updated_at desc for history
        orderBy = [['updated_at', 'DESC'], ['created_at', 'DESC']];
      } else if (modelName === 'Department') {
        // Department - order by process_order_no (ascending), then by department_code for consistency
        orderBy = [['process_order_no', 'ASC'], ['department_code', 'ASC']];
      } else {
        // Default order by id desc
        orderBy = [['id', 'DESC']];
      }

      // Log the where clause for debugging (especially for Material model)
      if (modelName === 'Material') {
        // Debug logging removed - use proper logging service if needed
      }

      const findOpts = {
        where,
        include: includeOptions.length > 0 ? includeOptions : undefined,
        limit: limitNum,
        offset,
        order: orderBy,
      };
      if (userRoleFilterActive) {
        findOpts.distinct = true;
        findOpts.col = Model.primaryKeyAttribute || 'id';
      }
      const records = await Model.findAndCountAll(findOpts);

      // Log results for Material model
      // Debug logging removed - use proper logging service if needed

      // Special handling for BOMHeader to add components count
      let items = [];
      if (modelName === 'BOMHeader' && records.rows.length > 0) {
        // Get components count for each BOM header efficiently using a single query
        const bomIds = records.rows.map(bom => bom.id);
        const componentsCounts = await db.BOMComponent.findAll({
          attributes: [
            'bom_id',
            [db.sequelize.fn('COUNT', db.sequelize.col('id')), 'count']
          ],
          where: {
            bom_id: { [Op.in]: bomIds },
            status: 'ACTIVE'
          },
          group: ['bom_id'],
          raw: true
        });

        // Create a map of BOM ID to components count
        const componentsCountMap = {};
        componentsCounts.forEach(item => {
          // Handle both camelCase and snake_case column names from raw query
          const bomId = item.bom_id || item.bomId;
          const count = item.count || 0;
          if (bomId) {
            componentsCountMap[bomId] = parseInt(count, 10);
          }
        });

        // Add components count to each record
        items = records.rows.map(bom => {
          const bomData = bom.toJSON();
          bomData.components_count = componentsCountMap[bom.id] || 0;
          return bomData;
        });
      } else {
        // Convert Sequelize instances to plain objects to ensure nested associations are serialized
        // This is especially important for models with includes like Department with parentDepartment
        items = records.rows.map(record => {
          if (!record) return null;
          if (record && typeof record.toJSON === 'function') {
            try {
              return record.toJSON();
            } catch (error) {
              console.error(`[masterController] Error converting ${modelName} record to JSON:`, error);
              // Fallback to plain object if toJSON fails
              return record.get ? record.get({ plain: true }) : record;
            }
          }
          // If it's already a plain object, return as is
          return record;
        }).filter(item => item !== null); // Remove any null entries
      }

      // Ensure items is always an array
      if (!Array.isArray(items)) {
        console.error(`[masterController] ${modelName}: items is not an array:`, typeof items, items);
        items = [];
      }

      // Use records.count for totalItems (from findAndCountAll)
      const totalItems = records.count || 0;
      
      const response = getPagingData({ count: totalItems, rows: items }, page, limitNum);
      sendResponse(res, 200, true, `${modelName}s retrieved successfully`, response);
    }),

    getById: asyncHandler(async (req, res) => {
      const { id } = req.params;

      const includeOptions = [];

      // Special handling for RoutingOperation: include routing/workCenter/machine
      if (modelName === 'RoutingOperation') {
        includeOptions.push(
          {
            model: db.Routing,
            as: 'routing',
            attributes: ['id', 'routing_number'],
            required: false,
          },
          {
            model: db.WorkCenter,
            as: 'workCenter',
            attributes: ['id', 'work_center_code', 'description'],
            required: false,
          },
          {
            model: db.Machine,
            as: 'machine',
            attributes: ['id', 'machine_code', 'machine_description'],
            required: false,
          }
        );
      }

      // Special handling for OperationName to include department
      if (modelName === 'OperationName') {
        includeOptions.push({
          model: db.Department,
          as: 'department',
          attributes: ['id', 'department_code', 'department_name'],
          required: false,
        });
      }

      // Special handling for OperationCode to include company
      if (modelName === 'OperationCode') {
        includeOptions.push({
          model: db.Company,
          as: 'company',
          attributes: ['id', 'code', 'name'],
          required: false,
        });
      }

      // Special handling for ProductionOperator: include operation name and work center
      if (modelName === 'ProductionOperator') {
        includeOptions.push(
          {
            model: db.OperationName,
            as: 'operationName',
            attributes: ['id', 'operation_code', 'operation_no', 'description'],
            required: false,
          },
          {
            model: db.WorkCenter,
            as: 'workCenter',
            attributes: ['id', 'work_center_code', 'description'],
            required: false,
          },
          {
            model: db.Company,
            as: 'company',
            attributes: ['id', 'code', 'name'],
            required: false,
          }
        );
      }
      
      // Special handling for PrintHeader to include company
      if (modelName === 'PrintHeader') {
        includeOptions.push({
          model: db.Company,
          as: 'company',
          attributes: ['id', 'code', 'name']
        });
      }
      
      // Special handling for BusinessPartner to include vendorCategory
      if (modelName === 'BusinessPartner') {
        includeOptions.push({
          model: db.VendorCategory,
          as: 'vendorCategory',
          attributes: ['id', 'category_code', 'category_name', 'gst_reduction_percentage', 'applies_to_raw_materials', 'applies_to_semi_finished']
        });
      }
      
      // Special handling for Department to include parent department, child departments, and related entities
      if (modelName === 'Department') {
        includeOptions.push({
          model: db.Department,
          as: 'parentDepartment',
          attributes: ['id', 'department_code', 'department_name'],
          required: false
        });
        includeOptions.push({
          model: db.Department,
          as: 'childDepartments',
          attributes: ['id', 'department_code', 'department_name', 'status'],
          required: false
        });
        includeOptions.push({
          model: db.Company,
          as: 'company',
          attributes: ['id', 'code', 'name'],
          required: false
        });
        includeOptions.push({
          model: db.CostCenter,
          as: 'costCenter',
          attributes: ['id', 'cost_center_code', 'description'],
          required: false
        });
        includeOptions.push({
          model: db.Employee,
          as: 'head',
          attributes: ['id', 'employee_number', 'first_name', 'last_name'],
          required: false
        });
      }

      // Special handling for CostCenter to include related masters
      if (modelName === 'CostCenter') {
        includeOptions.push({
          model: db.Company,
          as: 'company',
          attributes: ['id', 'code', 'name'],
          required: false
        });
        includeOptions.push({
          model: db.CostCenter,
          as: 'parentCostCenter',
          attributes: ['id', 'cost_center_code', 'description'],
          required: false
        });
        includeOptions.push({
          model: db.Department,
          as: 'department',
          attributes: ['id', 'department_code', 'department_name'],
          required: false
        });
        includeOptions.push({
          model: db.OperationName,
          as: 'operationName',
          attributes: ['id', 'operation_code', 'operation_no', 'description'],
          required: false
        });
        includeOptions.push({
          model: db.Employee,
          as: 'responsiblePerson',
          attributes: ['id', 'employee_number', 'first_name', 'last_name'],
          required: false
        });
        includeOptions.push({
          model: db.Currency,
          as: 'currency',
          attributes: ['id', 'currency_code', 'currency_name'],
          required: false
        });
      }
      
      // Special handling for Material to include thickness and grade
      if (modelName === 'Material') {
        includeOptions.push({
          model: db.MaterialThickness,
          as: 'materialThickness',
          attributes: ['id', 'thickness_code', 'thickness_value', 'thickness_unit', 'description'],
          required: false
        });
        includeOptions.push({
          model: db.MaterialGrade,
          as: 'materialGrade',
          attributes: ['id', 'grade_code', 'grade_name', 'description'],
          required: false
        });
        includeOptions.push({
          model: db.MaterialType,
          as: 'materialType',
          attributes: ['id', 'type_code', 'type_name'],
          required: false
        });
        includeOptions.push({
          model: db.MaterialGroup,
          as: 'materialGroup',
          attributes: ['id', 'group_code', 'group_name'],
          required: false
        });
        includeOptions.push({
          model: db.MaterialSheetType,
          as: 'materialSheetType',
          attributes: ['id', 'sheet_type_code', 'sheet_type_name'],
          required: false
        });
        includeOptions.push({
          model: db.UOM,
          as: 'weightUOM',
          attributes: ['id', 'uom_code', 'uom_name'],
          required: false
        });
        includeOptions.push({
          model: db.UOM,
          as: 'baseUOM',
          attributes: ['id', 'uom_code', 'uom_name'],
          required: false
        });
        includeOptions.push({
          model: db.UOM,
          as: 'volumeUOM',
          attributes: ['id', 'uom_code', 'uom_name'],
          required: false
        });
        includeOptions.push({
          model: db.TaxMaster,
          as: 'taxMaster',
          attributes: ['id', 'tax_code', 'tax_name', 'tax_rate'],
          required: false
        });
        includeOptions.push({
          model: db.MaterialValuation,
          as: 'valuations',
          attributes: ['id', 'plant_id', 'standard_price', 'moving_average_price', 'status'],
          required: false,
          where: { status: 'ACTIVE' }
        });
        includeOptions.push({
          model: db.MaterialPlant,
          as: 'materialPlants',
          attributes: ['id', 'plant_id', 'storage_location', 'opening_balance_unit_cost', 'status'],
          required: false,
          where: { status: 'ACTIVE' }
        });
        includeOptions.push({
          model: db.StorageLocation,
          as: 'storageLocation',
          attributes: ['id', 'storage_location_code', 'description'],
          required: false
        });
        includeOptions.push({
          model: db.MaterialSupplier,
          as: 'suppliers',
          where: { status: 'ACTIVE' },
          required: false,
          include: [
            {
              model: db.BusinessPartner,
              as: 'vendor',
              attributes: ['id', 'bp_number', 'name', 'bp_type'],
              required: false
            },
            {
              model: db.Currency,
              as: 'currency',
              attributes: ['id', 'currency_code', 'currency_name'],
              required: false
            }
          ],
          order: [['is_preferred', 'DESC'], ['id', 'ASC']]
        });
      }

      if (modelName === 'CompanyBankDetail') {
        includeOptions.push({
          model: db.Company,
          as: 'company',
          attributes: ['id', 'code', 'name'],
          required: false
        });
      }

      // Special handling for BOMHeader to include components
      if (modelName === 'BOMHeader') {
        includeOptions.push({
          model: db.BOMComponent,
          as: 'components',
          required: false,
          where: { status: 'ACTIVE' },
          include: [
            {
              model: db.Material,
              as: 'material',
              attributes: ['id', 'material_number', 'description', 'drawing_number', 'material_type', 'material_type_id'],
              include: [
                {
                  model: db.MaterialType,
                  as: 'materialType',
                  attributes: ['id', 'type_code', 'type_name']
                },
                {
                  model: db.MaterialThickness,
                  as: 'materialThickness',
                  attributes: ['id', 'thickness_code', 'thickness_value', 'thickness_unit']
                },
                {
                  model: db.MaterialGrade,
                  as: 'materialGrade',
                  attributes: ['id', 'grade_code', 'grade_name']
                },
                {
                  model: db.UOM,
                  as: 'componentUOM',
                  attributes: ['id', 'uom_code', 'uom_name']
                }
              ]
            }
          ],
          order: [['item_number', 'ASC']]
        });
      }

      // Special handling for StorageLocation to include plant and company
      if (modelName === 'StorageLocation') {
        includeOptions.push({
          model: db.Plant,
          as: 'plant',
          attributes: ['id', 'code', 'name'],
          required: false
        });
        includeOptions.push({
          model: db.Company,
          as: 'company',
          attributes: ['id', 'code', 'name'],
          required: false
        });
      }

      // Special handling for User to include roles
      if (modelName === 'User') {
        includeOptions.push({
          model: db.Role,
          as: 'roles',
          through: { attributes: [] },
          attributes: ['id', 'role_code', 'role_name'],
          required: false
        });
      }

      const record = await Model.findByPk(id, {
        include: includeOptions.length > 0 ? includeOptions : undefined
      });
      
      if (!record) {
        return sendResponse(res, 404, false, `${modelName} not found`, null);
      }

      sendResponse(res, 200, true, `${modelName} retrieved successfully`, record);
    }),

    create: asyncHandler(async (req, res) => {
      const data = req.body;
      
      if (data.created_by === undefined) {
        data.created_by = req.user?.userId || null;
      }
      if (data.updated_by === undefined) {
        data.updated_by = req.user?.userId || null;
      }

      const attributes = Model.getAttributes();
      if (attributes.company_id && data.company_id === undefined && req.user) {
        data.company_id = req.user.companyId;
      }

      // Convert empty strings to null for optional foreign keys to avoid foreign key constraint errors
      Object.keys(data).forEach(key => {
        if (attributes[key] && attributes[key].references && data[key] === '') {
          data[key] = null;
        }
      });

      // Special handling for Department: Validate parent department to prevent circular references
      if (modelName === 'Department' && data.parent_department_id) {
        // Check for circular reference: parent cannot be the same as current department (for updates)
        if (req.params?.id && parseInt(data.parent_department_id) === parseInt(req.params.id)) {
          return sendResponse(res, 400, false, 'A department cannot be its own parent', null);
        }
        
        // Check for circular reference: parent cannot be a child of this department
        const checkCircularReference = async (deptId, parentId) => {
          if (!deptId || !parentId) return false;
          if (parseInt(deptId) === parseInt(parentId)) return true;
          
          const parentDept = await db.Department.findByPk(parentId, {
            attributes: ['id', 'parent_department_id']
          });
          
          if (!parentDept) return false;
          if (parentDept.parent_department_id === parseInt(deptId)) return true;
          
          // Recursively check if parent's parent is the department
          if (parentDept.parent_department_id) {
            return await checkCircularReference(deptId, parentDept.parent_department_id);
          }
          
          return false;
        };
        
        if (req.params?.id) {
          const isCircular = await checkCircularReference(parseInt(req.params.id), parseInt(data.parent_department_id));
          if (isCircular) {
            return sendResponse(res, 400, false, 'Circular reference detected: The selected parent department is a child of this department', null);
          }
        }
      }

      // Special handling for Material: Convert numeric strings to proper types
      if (modelName === 'Material') {
        const numericFields = [
          'gross_weight', 'net_weight', 'unit_cost', 'last_purchase_rate', 'volume', 'length', 'width', 'height', 'thickness', 'tol', 'diameter',
          'minimum_order_qty', 'maximum_order_qty', 'rounding_value', 'lot_size'
        ];
        const integerFields = [
          'planned_delivery_time', 'gr_processing_time', 'in_house_production_time', 'shelf_life_days',
          'storage_location_id',
          'grade_id',
          'thickness_id',
          'sheet_type_id',
          'material_type_id',
          'material_group_id'
        ];
        
        numericFields.forEach(field => {
          if (data[field] !== null && data[field] !== undefined && data[field] !== '') {
            data[field] = parseFloat(data[field]);
          } else if (data[field] === '') {
            data[field] = null;
          }
        });
        
        integerFields.forEach(field => {
          if (data[field] !== null && data[field] !== undefined && data[field] !== '') {
            data[field] = parseInt(data[field]);
          } else if (data[field] === '') {
            data[field] = null;
          }
        });

        if (!data.storage_location_id) {
          return sendResponse(res, 400, false, 'Storage Location is required', null);
        }
        
        // Handle is_default_storage_location boolean field
        if (data.is_default_storage_location !== undefined && data.is_default_storage_location !== null) {
          // Convert string 'true'/'false' to boolean, or keep boolean as is
          if (typeof data.is_default_storage_location === 'string') {
            data.is_default_storage_location = data.is_default_storage_location === 'true' || data.is_default_storage_location === '1';
          } else {
            data.is_default_storage_location = Boolean(data.is_default_storage_location);
          }
        }

        // Map material_type_id to legacy material_type enum field
        // This ensures backward compatibility with the legacy enum field
        if (data.material_type_id) {
          try {
            const materialType = await db.MaterialType.findByPk(data.material_type_id);
            if (materialType) {
              // Map type_code to legacy enum value
              const typeCodeToEnum = {
                'RAW_MAT': 'RAW_MATERIAL',
                'SEMI_FIN': 'SEMI_FINISHED',
                'FIN_GOOD': 'FINISHED_GOOD',
                'PACKAGING': 'PACKAGING',
                'CONSUMABLE': 'CONSUMABLE',
                'SERVICE': 'SERVICE',
                'ASSET': 'ASSET'
              };
              const enumValue = typeCodeToEnum[materialType.type_code];
              if (enumValue) {
                data.material_type = enumValue;
              }
            }
          } catch (error) {
            console.error('Error mapping material_type_id to material_type:', error);
            // Continue without setting material_type if lookup fails
          }
        }
      }

      // Special handling for CostCenter numeric fields
      if (modelName === 'CostCenter') {
        const numericFields = ['cost_per_hour', 'standard_time'];
        numericFields.forEach(field => {
          if (data[field] !== null && data[field] !== undefined && data[field] !== '') {
            data[field] = parseFloat(data[field]);
          } else if (data[field] === '') {
            data[field] = null;
          }
        });
      }

      // Special handling for BusinessPartner: Ensure vendor_category_id and vendor_type_id are properly set
      if (modelName === 'BusinessPartner') {
        // Set default approval_status to PENDING for new business partners
        if (data.approval_status === undefined || data.approval_status === null || data.approval_status === '') {
          data.approval_status = 'PENDING';
        }
        
        // Only set vendor_category_id and vendor_type_id if bp_type is VENDOR or BOTH
        if (data.bp_type === 'VENDOR' || data.bp_type === 'BOTH') {
          // Use vendor_category_id if provided, otherwise use vendor_type_id
          if (data.vendor_category_id) {
            data.vendor_type_id = data.vendor_category_id; // Set both to same value
          } else if (data.vendor_type_id) {
            data.vendor_category_id = data.vendor_type_id; // Set both to same value
          } else {
            // If neither is provided for VENDOR/BOTH, set to null
            data.vendor_category_id = null;
            data.vendor_type_id = null;
          }
        } else {
          // For CUSTOMER type, clear vendor category fields
          data.vendor_category_id = null;
          data.vendor_type_id = null;
        }
        
        // Convert empty strings to null
        if (data.vendor_category_id === '') data.vendor_category_id = null;
        if (data.vendor_type_id === '') data.vendor_type_id = null;
      }

      // Special handling for User: Handle password hashing and role assignment
      let roleIds = null;
      if (modelName === 'User') {
        // Handle password hashing
        if (data.password) {
          // Hash the password before saving
          const saltRounds = 10;
          data.password_hash = await bcrypt.hash(data.password, saltRounds);
          delete data.password; // Remove plain password from data
        }
        
        // Handle role assignment
        if (data.role_ids) {
          roleIds = Array.isArray(data.role_ids) ? data.role_ids : [data.role_ids].filter(Boolean);
        }
        // Remove all role-related fields to prevent them from being saved to User table
        delete data.role_ids;
        delete data.role_id; // Also check for singular form
        delete data.roles; // Remove if roles object is accidentally sent
        
        // Ensure password_hash exists (required field)
        if (!data.password_hash) {
          return sendResponse(res, 400, false, 'Password is required', null);
        }
      }

      // Special handling for RolePermission: Use upsert (update if exists, insert if not)
      let record;
      if (modelName === 'CostCenter') {
        const companyForNumber = data.company_id || req.user?.companyId;
        if (!data.cost_center_code && companyForNumber) {
          try {
            data.cost_center_code = await generateNextNumber(Number(companyForNumber), 'COST_CENTER');
          } catch (error) {
            // Fallback pattern if Number Format is not configured.
            const year = new Date().getFullYear();
            const prefix = `CC${year}`;
            const lastCostCenter = await Model.findOne({
              where: {
                company_id: Number(companyForNumber),
                cost_center_code: { [Op.like]: `${prefix}%` }
              },
              order: [['cost_center_code', 'DESC']]
            });
            let sequence = 1;
            if (lastCostCenter?.cost_center_code) {
              const match = String(lastCostCenter.cost_center_code).match(/(\d{4,})$/);
              if (match) {
                sequence = Number(match[1]) + 1;
              }
            }
            data.cost_center_code = `${prefix}${String(sequence).padStart(4, '0')}`;
          }
        }
      }

      if (modelName === 'RolePermission') {
        // Check if record already exists (including INACTIVE ones)
        const existingRecord = await Model.findOne({
          where: {
            role_id: data.role_id,
            permission_id: data.permission_id
          }
        });

        if (existingRecord) {
          // Update existing record - reactivate if it was inactive
          await existingRecord.update({
            status: data.status || 'ACTIVE',
            updated_by: data.updated_by !== undefined ? data.updated_by : (req.user?.userId || null)
          });
          record = existingRecord;
        } else {
          // Create new record
          if (data.created_by === undefined) {
            data.created_by = req.user?.userId || null;
          }
          if (data.updated_by === undefined) {
            data.updated_by = req.user?.userId || null;
          }
          try {
            record = await Model.create(data);
          } catch (error) {
            // If still getting duplicate error (race condition), try to find and update
            if (error.name === 'SequelizeUniqueConstraintError' || error.parent?.code === 'ER_DUP_ENTRY') {
              const existing = await Model.findOne({
                where: {
                  role_id: data.role_id,
                  permission_id: data.permission_id
                }
              });
              if (existing) {
                await existing.update({
                  status: data.status || 'ACTIVE',
                  updated_by: data.updated_by || req.user?.userId || null
                });
                record = existing;
              } else {
                throw error;
              }
            } else {
              throw error;
            }
          }
        }
      } else {
        record = await Model.create(data);
      }

      // Assign roles to user if provided
      if (modelName === 'User' && roleIds && roleIds.length > 0) {
        const userId = record.id;
        const userRoles = roleIds.map(roleId => ({
          user_id: userId,
          role_id: roleId,
          status: 'ACTIVE',
          created_by: req.user?.userId || null,
          updated_by: req.user?.userId || null
        }));
        await db.UserRole.bulkCreate(userRoles);
        
        // Reload user with roles
        const userWithRoles = await Model.findByPk(userId, {
          include: [{
            model: db.Role,
            as: 'roles',
            through: { attributes: [] },
            attributes: ['id', 'role_code', 'role_name']
          }]
        });
        return sendResponse(res, 201, true, `${modelName} created successfully`, userWithRoles);
      }

      sendResponse(res, 201, true, `${modelName} created successfully`, record);
    }),

    update: asyncHandler(async (req, res) => {
      const { id } = req.params;
      const data = req.body;
      
      if (data.updated_by === undefined) {
        data.updated_by = req.user?.userId || null;
      }

      const record = await Model.findByPk(id);
      if (!record) {
        return sendResponse(res, 404, false, `${modelName} not found`, null);
      }

      // Convert empty strings to null for optional foreign keys to avoid foreign key constraint errors
      const attributes = Model.getAttributes();
      Object.keys(data).forEach(key => {
        if (attributes[key] && attributes[key].references && data[key] === '') {
          data[key] = null;
        }
      });

      // Special handling for Department: Validate parent department to prevent circular references
      if (modelName === 'Department' && data.parent_department_id) {
        // Check for circular reference: parent cannot be the same as current department
        if (parseInt(data.parent_department_id) === parseInt(id)) {
          return sendResponse(res, 400, false, 'A department cannot be its own parent', null);
        }
        
        // Check for circular reference: parent cannot be a child of this department
        const checkCircularReference = async (deptId, parentId) => {
          if (!deptId || !parentId) return false;
          if (parseInt(deptId) === parseInt(parentId)) return true;
          
          const parentDept = await db.Department.findByPk(parentId, {
            attributes: ['id', 'parent_department_id']
          });
          
          if (!parentDept) return false;
          if (parentDept.parent_department_id === parseInt(deptId)) return true;
          
          // Recursively check if parent's parent is the department
          if (parentDept.parent_department_id) {
            return await checkCircularReference(deptId, parentDept.parent_department_id);
          }
          
          return false;
        };
        
        const isCircular = await checkCircularReference(parseInt(id), parseInt(data.parent_department_id));
        if (isCircular) {
          return sendResponse(res, 400, false, 'Circular reference detected: The selected parent department is a child of this department', null);
        }
      }

      // Special handling for BusinessPartner: Ensure vendor_category_id and vendor_type_id are properly set
      if (modelName === 'BusinessPartner') {
        // Get current bp_type if not being updated
        const currentBpType = data.bp_type || (record ? record.bp_type : null);
        const bpTypeToUse = data.bp_type || currentBpType;
        
        // Only set vendor_category_id and vendor_type_id if bp_type is VENDOR or BOTH
        if (bpTypeToUse === 'VENDOR' || bpTypeToUse === 'BOTH') {
          // Use vendor_category_id if provided, otherwise use vendor_type_id
          if (data.vendor_category_id) {
            data.vendor_type_id = data.vendor_category_id; // Set both to same value
          } else if (data.vendor_type_id) {
            data.vendor_category_id = data.vendor_type_id; // Set both to same value
          } else if (data.vendor_category_id === null || data.vendor_type_id === null) {
            // If explicitly set to null, clear both
            data.vendor_category_id = null;
            data.vendor_type_id = null;
          }
          // If neither is provided and not explicitly null, keep existing values (don't change)
        } else {
          // For CUSTOMER type, clear vendor category fields
          data.vendor_category_id = null;
          data.vendor_type_id = null;
        }
        
        // Convert empty strings to null
        if (data.vendor_category_id === '') data.vendor_category_id = null;
        if (data.vendor_type_id === '') data.vendor_type_id = null;
      }

      // Special handling for Material: Convert numeric strings to proper types
      if (modelName === 'Material') {
        const numericFields = [
          'gross_weight', 'net_weight', 'unit_cost', 'last_purchase_rate', 'volume', 'length', 'width', 'height', 'thickness', 'tol', 'diameter',
          'minimum_order_qty', 'maximum_order_qty', 'rounding_value', 'lot_size'
        ];
        const integerFields = [
          'planned_delivery_time', 'gr_processing_time', 'in_house_production_time', 'shelf_life_days',
          'storage_location_id',
          'grade_id',
          'thickness_id',
          'sheet_type_id',
          'material_type_id',
          'material_group_id'
        ];
        
        numericFields.forEach(field => {
          if (data[field] !== null && data[field] !== undefined && data[field] !== '') {
            data[field] = parseFloat(data[field]);
          } else if (data[field] === '') {
            data[field] = null;
          }
        });
        
        integerFields.forEach(field => {
          if (data[field] !== null && data[field] !== undefined && data[field] !== '') {
            data[field] = parseInt(data[field]);
          } else if (data[field] === '') {
            data[field] = null;
          }
        });

        if (Object.prototype.hasOwnProperty.call(data, 'storage_location_id') && !data.storage_location_id) {
          return sendResponse(res, 400, false, 'Storage Location is required', null);
        }
        
        // Handle is_default_storage_location boolean field
        if (data.is_default_storage_location !== undefined && data.is_default_storage_location !== null) {
          // Convert string 'true'/'false' to boolean, or keep boolean as is
          if (typeof data.is_default_storage_location === 'string') {
            data.is_default_storage_location = data.is_default_storage_location === 'true' || data.is_default_storage_location === '1';
          } else {
            data.is_default_storage_location = Boolean(data.is_default_storage_location);
          }
        }

        // Map material_type_id to legacy material_type enum field
        // This ensures backward compatibility with the legacy enum field
        if (data.material_type_id) {
          try {
            const materialType = await db.MaterialType.findByPk(data.material_type_id);
            if (materialType) {
              // Map type_code to legacy enum value
              const typeCodeToEnum = {
                'RAW_MAT': 'RAW_MATERIAL',
                'SEMI_FIN': 'SEMI_FINISHED',
                'FIN_GOOD': 'FINISHED_GOOD',
                'PACKAGING': 'PACKAGING',
                'CONSUMABLE': 'CONSUMABLE',
                'SERVICE': 'SERVICE',
                'ASSET': 'ASSET'
              };
              const enumValue = typeCodeToEnum[materialType.type_code];
              if (enumValue) {
                data.material_type = enumValue;
              }
            }
          } catch (error) {
            console.error('Error mapping material_type_id to material_type:', error);
            // Continue without setting material_type if lookup fails
          }
        }
      }

      // Special handling for CostCenter numeric fields
      if (modelName === 'CostCenter') {
        const numericFields = ['cost_per_hour', 'standard_time'];
        numericFields.forEach(field => {
          if (data[field] !== null && data[field] !== undefined && data[field] !== '') {
            data[field] = parseFloat(data[field]);
          } else if (data[field] === '') {
            data[field] = null;
          }
        });
      }

      // Special handling for User: Handle password hashing and role assignment
      let roleIds = null;
      if (modelName === 'User') {
        // Handle password hashing (only if password is provided for update)
        if (data.password) {
          // Hash the new password before saving
          const saltRounds = 10;
          data.password_hash = await bcrypt.hash(data.password, saltRounds);
          delete data.password; // Remove plain password from data
        }
        // If no password provided, keep existing password_hash (don't update it)
        
        // Handle role assignment
        if (data.role_ids) {
          roleIds = Array.isArray(data.role_ids) ? data.role_ids : [data.role_ids].filter(Boolean);
        }
        // Remove all role-related fields to prevent them from being saved to User table
        delete data.role_ids;
        delete data.role_id; // Also check for singular form
        delete data.roles; // Remove if roles object is accidentally sent
      }

      await record.update(data);

      // Update user roles if provided
      if (modelName === 'User' && roleIds !== null) {
        const userId = record.id;
        // Remove existing roles
        await db.UserRole.destroy({ where: { user_id: userId } });
        // Add new roles
        if (roleIds.length > 0) {
          const userRoles = roleIds.map(roleId => ({
            user_id: userId,
            role_id: roleId,
            status: 'ACTIVE',
            created_by: req.user?.userId || null,
            updated_by: req.user?.userId || null
          }));
          await db.UserRole.bulkCreate(userRoles);
        }
        
        // Reload user with roles
        const userWithRoles = await Model.findByPk(userId, {
          include: [{
            model: db.Role,
            as: 'roles',
            through: { attributes: [] },
            attributes: ['id', 'role_code', 'role_name']
          }]
        });
        return sendResponse(res, 200, true, `${modelName} updated successfully`, userWithRoles);
      }

      sendResponse(res, 200, true, `${modelName} updated successfully`, record);
    }),

    delete: asyncHandler(async (req, res) => {
      const { id } = req.params;

      const record = await Model.findByPk(id);
      if (!record) {
        return sendResponse(res, 404, false, `${modelName} not found`, null);
      }

      const attributes = Model.getAttributes();
      if (attributes.status) {
        await record.update({ status: 'INACTIVE', updated_by: req.user?.userId || null });
      } else {
        await record.destroy();
      }

      sendResponse(res, 200, true, `${modelName} deleted successfully`, null);
    }),
  };
};

module.exports = createMasterController;

