const db = require('../db/models');
const ValidationError = require('./notifications/errors/validation');

const Sequelize = db.Sequelize;
const Op = Sequelize.Op;

/**
 * @param {string} permission
 * @param {object} currentUser
 */
async function checkPermissions(permission, currentUser) {

  if (!currentUser) {
    throw new ValidationError('auth.unauthorized');
  }

  const userPermission = currentUser.custom_permissions.find(
    (cp) => cp.name === permission,
  );

  if (userPermission) {
    return true;
  }

  try {
    if (!currentUser.app_role) {
      throw new ValidationError('auth.forbidden');
    }

    const permissions = await currentUser.app_role.getPermissions();

    return !!permissions.find((p) => p.name === permission);
  } catch (e) {
    throw e;
  }
}

module.exports = class SearchService {
  static async search(searchQuery, currentUser ) {
    try {
      if (!searchQuery) {
        throw new ValidationError('iam.errors.searchQueryRequired');
      }
      const tableColumns = {
        
          
          

          
            "users": [
              
                "firstName",
              
                "lastName",
              
                "phoneNumber",
              
                "email",
              
            ],
          
        
          
          
          
          

          
            "system_parameters": [
              
                "key",
              
                "value",
              
                "description",
              
            ],
          
        
          
          

          
            "holidays": [
              
                "holidayName",
              
            ],
          
        
          
          

          
        
          
          

          
        
          
          

          
        
          
          

          
            "cost_centers": [
              
                "code",
              
                "name",
              
            ],
          
        
          
          

          
            "departments": [
              
                "code",
              
                "name",
              
            ],
          
        
          
          

          
            "positions": [
              
                "title",
              
            ],
          
        
          
          

          
            "shifts": [
              
                "name",
              
                "timeIn",
              
                "timeOut",
              
            ],
          
        
          
          

          
            "employees": [
              
                "employeeCode",
              
                "firstName",
              
                "middleName",
              
                "lastName",
              
                "nameExtension",
              
                "placeOfBirth",
              
                "nationality",
              
                "religion",
              
                "bloodType",
              
                "homeStreet",
              
                "homeBarangay",
              
                "homeCity",
              
                "homeProvince",
              
                "homeZipCode",
              
                "permanentStreet",
              
                "permanentBarangay",
              
                "permanentCity",
              
                "permanentProvince",
              
                "permanentZipCode",
              
                "personalEmail",
              
                "personalMobile",
              
                "emergencyContactName",
              
                "emergencyContactRel",
              
                "emergencyContactMobile",
              
                "sssNumber",
              
                "philhealthNumber",
              
                "pagibigNumber",
              
                "tinNumber",
              
                "bankName",
              
                "bankAccountNumber",
              
                "bankAccountName",
              
                "workLocation",
              
                "separationReason",
              
                "clearanceStatus",
              
            ],
          
        
          
          

          
            "employee_documents": [
              
                "fileName",
              
            ],
          
        
          
          

          
            "time_records": [
              
                "scheduledTimeIn",
              
                "scheduledTimeOut",
              
                "modificationReason",
              
            ],
          
        
          
          

          
            "biometric_imports": [
              
                "deviceCode",
              
                "fileName",
              
                "errorDetailsJson",
              
            ],
          
        
          
          

          
            "leave_types": [
              
                "code",
              
                "name",
              
            ],
          
        
          
          

          
        
          
          

          
            "leave_applications": [
              
                "reason",
              
                "managerNote",
              
                "hrNote",
              
            ],
          
        
          
          

          
            "employee_loans": [
              
                "loanSource",
              
                "loanAccountNumber",
              
            ],
          
        
          
          

          
            "loan_deductions": [
              
                "deferredReason",
              
            ],
          
        
          
          

          
            "payroll_runs": [
              
                "notes",
              
            ],
          
        
          
          

          
            "payroll_items": [
              
                "payslipHash",
              
            ],
          
        
          
          

          
            "bank_files": [
              
                "fileHash",
              
            ],
          
        
          
          

          
            "accounts": [
              
                "code",
              
                "name",
              
                "description",
              
            ],
          
        
          
          

          
            "journal_entries": [
              
                "entryNumber",
              
                "description",
              
                "reference",
              
            ],
          
        
          
          

          
            "journal_lines": [
              
                "description",
              
            ],
          
        
          
          

          
            "vendors": [
              
                "vendorCode",
              
                "vendorName",
              
                "tinNumber",
              
                "birRegAddress",
              
                "contactPerson",
              
                "contactNumber",
              
                "email",
              
                "address",
              
                "wtaxAtcCode",
              
                "bankName",
              
                "bankAccountNumber",
              
                "bankAccountName",
              
            ],
          
        
          
          

          
            "ap_invoices": [
              
                "invoiceNumber",
              
                "vendorInvoiceNumber",
              
                "description",
              
            ],
          
        
          
          

          
            "ap_payments": [
              
                "checkNumber",
              
                "bankName",
              
            ],
          
        
          
          

          
        
          
          

          
            "customers": [
              
                "customerCode",
              
                "customerName",
              
                "customerType",
              
                "tinNumber",
              
                "contactPerson",
              
                "billingAddress",
              
            ],
          
        
          
          

          
            "ar_invoices": [
              
                "invoiceNumber",
              
            ],
          
        
          
          

          
            "ar_invoice_items": [
              
                "description",
              
            ],
          
        
          
          

          
            "job_requisitions": [
              
                "requestingDept",
              
                "positionTitle",
              
                "justification",
              
                "requiredSkills",
              
            ],
          
        
          
          

          
            "applicants": [
              
                "firstName",
              
                "lastName",
              
                "email",
              
                "mobile",
              
                "psychTestResult",
              
                "medicalResult",
              
                "backgroundCheck",
              
            ],
          
        
          
          

          
            "approval_requests": [
              
                "entityId",
              
                "entityType",
              
                "rejectionReason",
              
                "notes",
              
            ],
          
        
          
          

          
            "notifications": [
              
                "title",
              
                "message",
              
                "link",
              
            ],
          
        
          
          

          
            "export_requests": [
              
                "exportType",
              
                "filtersJson",
              
            ],
          
        
          
          

          
            "password_histories": [
              
                "passwordHash",
              
            ],
          
        
          
          

          
            "refresh_tokens": [
              
                "tokenHash",
              
                "ipAddress",
              
                "userAgent",
              
            ],
          
        
          
          

          
            "audit_logs": [
              
                "module",
              
                "entityId",
              
                "entityType",
              
                "oldValueJson",
              
                "newValueJson",
              
                "ipAddress",
              
                "description",
              
            ],
          
        
      };
      const columnsInt = {
        
          
          
          
        
          
          
          
          
          
        
          
          
          
            "holidays": [
              
                "year",
              
            ],
          
        
          
          
          
            "sss_contribution_tables": [
              
                "salaryFrom",
              
                "salaryTo",
              
                "msc",
              
                "employeeShare",
              
                "employerShare",
              
                "wispEmployee",
              
                "wispEmployer",
              
                "effectiveYear",
              
            ],
          
        
          
          
          
            "withholding_tax_tables": [
              
                "salaryFrom",
              
                "salaryTo",
              
                "baseTax",
              
                "excessRate",
              
                "excessOver",
              
                "effectiveYear",
              
            ],
          
        
          
          
          
            "fiscal_periods": [
              
                "year",
              
                "month",
              
            ],
          
        
          
          
          
        
          
          
          
        
          
          
          
        
          
          
          
            "shifts": [
              
                "breakDurationMinutes",
              
                "totalWorkingHours",
              
                "rotationCycleDays",
              
            ],
          
        
          
          
          
            "employees": [
              
                "basicSalary",
              
                "finalPayAmount",
              
            ],
          
        
          
          
          
        
          
          
          
            "time_records": [
              
                "tardinessMinutes",
              
                "undertimeMinutes",
              
                "overtimeMinutes",
              
                "nightDiffHours",
              
            ],
          
        
          
          
          
            "biometric_imports": [
              
                "totalRecords",
              
                "successCount",
              
                "errorCount",
              
            ],
          
        
          
          
          
            "leave_types": [
              
                "daysPerYear",
              
                "minServiceMonths",
              
            ],
          
        
          
          
          
            "leave_balances": [
              
                "year",
              
                "openingBalance",
              
                "earnedThisYear",
              
                "usedYtd",
              
                "forfeited",
              
                "convertedToCash",
              
                "currentBalance",
              
            ],
          
        
          
          
          
            "leave_applications": [
              
                "totalDays",
              
            ],
          
        
          
          
          
            "employee_loans": [
              
                "loanAmount",
              
                "monthlyAmortization",
              
                "remainingBalance",
              
                "termsMonths",
              
            ],
          
        
          
          
          
            "loan_deductions": [
              
                "amountDeducted",
              
                "balanceBefore",
              
                "balanceAfter",
              
            ],
          
        
          
          
          
        
          
          
          
            "payroll_items": [
              
                "regularDaysWorked",
              
                "absentDays",
              
                "tardinessMinutes",
              
                "overtimeHours",
              
                "nightDiffHours",
              
                "holidayDaysWorked",
              
                "basicPay",
              
                "overtimePay",
              
                "nightDifferential",
              
                "holidayPay",
              
                "allowances",
              
                "otherEarnings",
              
                "grossPay",
              
                "sssEmployee",
              
                "philhealthEmployee",
              
                "pagibigEmployee",
              
                "withholdingTax",
              
                "sssEmployer",
              
                "philhealthEmployer",
              
                "pagibigEmployer",
              
                "sssLoanDeduction",
              
                "pagibigLoanDeduction",
              
                "companyLoanDeduction",
              
                "otherDeductions",
              
                "totalDeductions",
              
                "netPay",
              
                "ytdGrossPay",
              
                "ytdWithholdingTax",
              
                "ytdSssEmployee",
              
                "ytdPhilhealthEmployee",
              
                "ytdPagibigEmployee",
              
                "ytdBasicPay",
              
            ],
          
        
          
          
          
            "bank_files": [
              
                "totalAmount",
              
                "recordCount",
              
            ],
          
        
          
          
          
        
          
          
          
        
          
          
          
            "journal_lines": [
              
                "debit",
              
                "credit",
              
                "lineOrder",
              
            ],
          
        
          
          
          
            "vendors": [
              
                "paymentTermsDays",
              
                "wtaxRate",
              
            ],
          
        
          
          
          
            "ap_invoices": [
              
                "grossAmount",
              
                "vatAmount",
              
                "ewtAmount",
              
                "netPayable",
              
            ],
          
        
          
          
          
            "ap_payments": [
              
                "totalAmount",
              
            ],
          
        
          
          
          
            "ap_payment_lines": [
              
                "amountPaid",
              
            ],
          
        
          
          
          
            "customers": [
              
                "creditLimit",
              
                "paymentTerms",
              
            ],
          
        
          
          
          
            "ar_invoices": [
              
                "subtotal",
              
                "vatAmount",
              
                "totalAmountDue",
              
                "collectedAmount",
              
                "outstandingBalance",
              
            ],
          
        
          
          
          
            "ar_invoice_items": [
              
                "quantity",
              
                "unitPrice",
              
                "lineTotal",
              
            ],
          
        
          
          
          
            "job_requisitions": [
              
                "numberOfVacancies",
              
                "salaryRangeFrom",
              
                "salaryRangeTo",
              
            ],
          
        
          
          
          
        
          
          
          
            "approval_requests": [
              
                "amount",
              
            ],
          
        
          
          
          
        
          
          
          
            "export_requests": [
              
                "recordCount",
              
            ],
          
        
          
          
          
        
          
          
          
        
          
          
          
        
      };

      let allFoundRecords = [];

      for (const tableName in tableColumns) {
        if (tableColumns.hasOwnProperty(tableName)) {
          const attributesToSearch = tableColumns[tableName];
          const attributesIntToSearch = columnsInt[tableName] || [];
          const whereCondition = {
            [Op.or]: [
              ...attributesToSearch.map(attribute => ({
                [attribute]: {
                  [Op.iLike] : `%${searchQuery}%`,
                },
              })),
              ...attributesIntToSearch.map(attribute => (
                Sequelize.where(
                  Sequelize.cast(Sequelize.col(`${tableName}.${attribute}`), 'varchar'),
                  { [Op.iLike]: `%${searchQuery}%` }
                )
              )),
            ],
          };

          

          const hasPermission = await checkPermissions(`READ_${tableName.toUpperCase()}`, currentUser);
          if (!hasPermission) {
            continue;
          }

          const foundRecords = await db[tableName].findAll({
            where: whereCondition,
            attributes: [...tableColumns[tableName], 'id', ...attributesIntToSearch],
          });
      
          const modifiedRecords = foundRecords.map((record) => {
            const matchAttribute = [];
 
            for (const attribute of attributesToSearch) {
              if (record[attribute]?.toLowerCase()?.includes(searchQuery.toLowerCase())) {
                matchAttribute.push(attribute);
              }
            }

            for (const attribute of attributesIntToSearch) {
              const castedValue = String(record[attribute]);
              if (castedValue && castedValue.toLowerCase().includes(searchQuery.toLowerCase())) {
                matchAttribute.push(attribute);
              }
            }
      
            return {
              ...record.get(),
              matchAttribute,
              tableName,
            };
          });

          allFoundRecords = allFoundRecords.concat(modifiedRecords);
        }
      }

      return allFoundRecords;
    } catch (error) {
      throw error;
    }
  }
}