const db = require('../models');

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

const UUID_PATTERN = /^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i;

const getCurrentOrganizationId = (currentUser) => {
  if (!currentUser) {
    return null;
  }

  return (
    currentUser.organization?.id ||
    currentUser.organizations?.id ||
    currentUser.organizationId ||
    currentUser.organizationsId ||
    null
  );
};

const createBadRequestError = (message) => {
  const error = new Error(message);
  error.code = 400;
  return error;
};

const getImportCellValue = (item, keys) => {
  for (const key of keys) {
    const value = item?.[key];

    if (value === 0 || value === false) {
      return value;
    }

    if (typeof value === 'string') {
      const trimmed = value.trim();
      if (trimmed) {
        return trimmed;
      }
      continue;
    }

    if (value !== undefined && value !== null && value !== '') {
      return value;
    }
  }

  return null;
};

const normalizeReferenceValue = (value) => {
  if (value === undefined || value === null) {
    return null;
  }

  if (typeof value === 'string') {
    const trimmed = value.trim();
    return trimmed || null;
  }

  return value;
};

const buildReferenceClauses = (reference, fields = []) => {
  const normalizedReference = normalizeReferenceValue(reference);

  if (!normalizedReference) {
    return [];
  }

  const clauses = [];

  if (typeof normalizedReference === 'string' && UUID_PATTERN.test(normalizedReference)) {
    clauses.push({ id: normalizedReference });
  }

  for (const field of fields) {
    clauses.push({
      [field]: {
        [Op.iLike]: String(normalizedReference),
      },
    });
  }

  return clauses;
};

const buildLookupWhere = (reference, fields = [], extraWhere = {}) => {
  const clauses = buildReferenceClauses(reference, fields);

  if (!clauses.length) {
    return null;
  }

  const filters = [{ [Op.or]: clauses }];

  if (extraWhere && Object.keys(extraWhere).length) {
    filters.unshift(extraWhere);
  }

  if (filters.length === 1) {
    return filters[0];
  }

  return {
    [Op.and]: filters,
  };
};

const lookupSingleRecordByReference = async ({
  model,
  label,
  reference,
  fields = [],
  extraWhere = {},
  transaction,
}) => {
  const normalizedReference = normalizeReferenceValue(reference);

  if (!normalizedReference) {
    return null;
  }

  const where = buildLookupWhere(normalizedReference, fields, extraWhere);

  if (!where) {
    return null;
  }

  const records = await model.findAll({
    where,
    transaction,
    limit: 2,
    order: [['updatedAt', 'DESC'], ['createdAt', 'DESC']],
  });

  if (!records.length) {
    throw createBadRequestError(`${label} "${normalizedReference}" was not found.`);
  }

  if (records.length > 1) {
    throw createBadRequestError(`${label} "${normalizedReference}" matches multiple records. Use the ID instead.`);
  }

  return records[0];
};

const normalizeImportedBoolean = (value) => {
  if (typeof value === 'boolean') {
    return value;
  }

  if (typeof value === 'string') {
    const normalized = value.trim().toLowerCase();
    if (['true', '1', 'yes', 'y'].includes(normalized)) {
      return true;
    }
    if (['false', '0', 'no', 'n'].includes(normalized)) {
      return false;
    }
  }

  return value;
};

const normalizeInventoryImportRow = (item = {}) => ({
  ...item,
  id: getImportCellValue(item, ['id']) || undefined,
  importHash: getImportCellValue(item, ['importHash']) || null,
  tenant: getImportCellValue(item, ['tenant', 'tenantId']),
  organizations: getImportCellValue(item, ['organizations', 'organization', 'organizationsId', 'organizationId']),
  property: getImportCellValue(item, ['property', 'propertyId']),
  unit_type: getImportCellValue(item, ['unit_type', 'unitType', 'unit_typeId', 'unitTypeId']),
  is_active: normalizeImportedBoolean(item?.is_active),
});

const prefixImportError = (error, rowNumber) => {
  if (!error) {
    return error;
  }

  const prefixedMessage = `Import row ${rowNumber}: ${error.message}`;

  if (typeof error.message === 'string' && !error.message.startsWith(`Import row ${rowNumber}:`)) {
    error.message = prefixedMessage;
  }

  return error;
};

const resolveTenantIdForOrganization = async (organizationId, transaction) => {
  if (!organizationId) {
    return null;
  }

  const rows = await db.sequelize.query(
    `
      SELECT "tenants_organizationsId" AS "tenantId"
      FROM "tenantsOrganizationsOrganizations"
      WHERE "organizationId" = :organizationId
      ORDER BY "updatedAt" DESC, "createdAt" DESC
      LIMIT 1
    `,
    {
      replacements: { organizationId },
      type: QueryTypes.SELECT,
      transaction,
    },
  );

  return rows[0]?.tenantId || null;
};

const resolveOrganizationIdsForTenant = async (tenantId, transaction) => {
  if (!tenantId) {
    return [];
  }

  const rows = await db.sequelize.query(
    `
      SELECT "organizationId"
      FROM "tenantsOrganizationsOrganizations"
      WHERE "tenants_organizationsId" = :tenantId
      ORDER BY "updatedAt" DESC, "createdAt" DESC
    `,
    {
      replacements: { tenantId },
      type: QueryTypes.SELECT,
      transaction,
    },
  );

  return rows.map((row) => row.organizationId).filter(Boolean);
};

const resolveTenantReference = async (reference, transaction) => {
  const tenant = await lookupSingleRecordByReference({
    model: db.tenants,
    label: 'Tenant',
    reference,
    fields: ['slug', 'name'],
    transaction,
  });

  return tenant?.id || null;
};

const resolveOrganizationReference = async (reference, { tenantId, transaction } = {}) => {
  const normalizedReference = normalizeReferenceValue(reference);

  if (!normalizedReference) {
    return null;
  }

  const extraWhere = {};

  if (tenantId) {
    const organizationIds = await resolveOrganizationIdsForTenant(tenantId, transaction);

    if (!organizationIds.length) {
      throw createBadRequestError('The selected tenant has no organizations available for import.');
    }

    extraWhere.id = {
      [Op.in]: organizationIds,
    };
  }

  const organization = await lookupSingleRecordByReference({
    model: db.organizations,
    label: 'Organization',
    reference: normalizedReference,
    fields: ['name'],
    extraWhere,
    transaction,
  });

  return organization?.id || null;
};

const resolvePropertyReference = async (reference, { currentUser, organizationId, tenantId, transaction } = {}) => {
  const normalizedReference = normalizeReferenceValue(reference);

  if (!normalizedReference) {
    return null;
  }

  const globalAccess = Boolean(currentUser?.app_role?.globalAccess);
  const extraWhere = {};

  if (organizationId) {
    extraWhere.organizationsId = organizationId;
  } else if (!globalAccess) {
    const currentOrganizationId = getCurrentOrganizationId(currentUser);

    if (currentOrganizationId) {
      extraWhere.organizationsId = currentOrganizationId;
    }
  }

  if (tenantId) {
    extraWhere.tenantId = tenantId;
  }

  const property = await lookupSingleRecordByReference({
    model: db.properties,
    label: 'Property',
    reference: normalizedReference,
    fields: ['code', 'name'],
    extraWhere,
    transaction,
  });

  return property?.id || null;
};

const resolveUnitTypeReference = async (reference, { currentUser, organizationId, propertyId, transaction } = {}) => {
  const normalizedReference = normalizeReferenceValue(reference);

  if (!normalizedReference) {
    return null;
  }

  const globalAccess = Boolean(currentUser?.app_role?.globalAccess);
  const extraWhere = {};

  if (propertyId) {
    extraWhere.propertyId = propertyId;
  }

  if (organizationId) {
    extraWhere.organizationsId = organizationId;
  } else if (!globalAccess) {
    const currentOrganizationId = getCurrentOrganizationId(currentUser);

    if (currentOrganizationId) {
      extraWhere.organizationsId = currentOrganizationId;
    }
  }

  const unitType = await lookupSingleRecordByReference({
    model: db.unit_types,
    label: 'Unit type',
    reference: normalizedReference,
    fields: ['code', 'name'],
    extraWhere,
    transaction,
  });

  return unitType?.id || null;
};

const loadPropertyContext = async (propertyId, transaction) => {
  if (!propertyId) {
    return null;
  }

  const property = await db.properties.findByPk(propertyId, { transaction });

  if (!property) {
    throw createBadRequestError('Selected property was not found.');
  }

  return property;
};

const loadUnitTypeContext = async (unitTypeId, transaction) => {
  if (!unitTypeId) {
    return null;
  }

  const unitType = await db.unit_types.findByPk(unitTypeId, { transaction });

  if (!unitType) {
    throw createBadRequestError('Selected unit type was not found.');
  }

  return unitType;
};

module.exports = {
  createBadRequestError,
  getCurrentOrganizationId,
  getImportCellValue,
  loadPropertyContext,
  loadUnitTypeContext,
  normalizeImportedBoolean,
  normalizeInventoryImportRow,
  normalizeReferenceValue,
  prefixImportError,
  resolveOrganizationIdsForTenant,
  resolveOrganizationReference,
  resolvePropertyReference,
  resolveTenantIdForOrganization,
  resolveTenantReference,
  resolveUnitTypeReference,
};
