const path = require('path');
const XLSX = require('xlsx');

const db = require('../db/models');
const processFile = require('../middlewares/upload');
const TenantsDBApi = require('../db/api/tenants');
const OrganizationsDBApi = require('../db/api/organizations');
const PropertiesDBApi = require('../db/api/properties');
const UnitTypesDBApi = require('../db/api/unit_types');
const UnitsDBApi = require('../db/api/units');
const {
  createBadRequestError,
  loadPropertyContext,
  normalizeInventoryImportRow,
  resolveOrganizationReference,
  resolvePropertyReference,
  resolveTenantIdForOrganization,
  resolveTenantReference,
  resolveUnitTypeReference,
} = require('../db/api/inventoryContext');

const Op = db.Sequelize.Op;

const SUPPORTED_WORKBOOK_EXTENSIONS = new Set(['.xlsx', '.xls']);

const SHEET_DEFINITIONS = [
  {
    key: 'tenants',
    label: 'Tenants',
    aliases: ['tenants', 'tenant'],
    allowedHeaders: [
      'id',
      'name',
      'slug',
      'legal_name',
      'primary_domain',
      'timezone',
      'default_currency',
      'is_active',
      'importHash',
    ],
    requiredHeaders: ['name'],
  },
  {
    key: 'organizations',
    label: 'Organizations',
    aliases: ['organizations', 'organization'],
    allowedHeaders: ['id', 'tenant', 'name', 'importHash'],
    requiredHeaders: ['tenant', 'name'],
  },
  {
    key: 'properties',
    label: 'Properties',
    aliases: ['properties', 'property'],
    allowedHeaders: [
      'id',
      'tenant',
      'organization',
      'name',
      'code',
      'address',
      'city',
      'country',
      'timezone',
      'description',
      'is_active',
      'importHash',
    ],
    requiredHeaders: ['organization', 'name'],
  },
  {
    key: 'unit_types',
    label: 'Unit Types',
    aliases: ['unit_types', 'unit types', 'unit-types', 'unit type'],
    allowedHeaders: [
      'id',
      'property',
      'name',
      'code',
      'description',
      'max_occupancy',
      'bedrooms',
      'bathrooms',
      'minimum_stay_nights',
      'size_sqm',
      'base_nightly_rate',
      'base_monthly_rate',
      'importHash',
    ],
    requiredHeaders: ['property', 'name'],
  },
  {
    key: 'units',
    label: 'Units',
    aliases: ['units', 'unit'],
    allowedHeaders: [
      'id',
      'property',
      'unit_type',
      'unit_number',
      'floor',
      'status',
      'max_occupancy_override',
      'notes',
      'importHash',
    ],
    requiredHeaders: ['property', 'unit_type', 'unit_number'],
  },
];

const SHEET_DEFINITION_BY_KEY = SHEET_DEFINITIONS.reduce((accumulator, definition) => {
  accumulator[definition.key] = definition;
  return accumulator;
}, {});

const normalizeSheetName = (value = '') =>
  String(value)
    .trim()
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, '_')
    .replace(/^_+|_+$/g, '');

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

  if (typeof value === 'string') {
    return value.trim().length === 0;
  }

  return false;
};

const isBlankRow = (row = {}) => Object.values(row).every((value) => isBlankValue(value));

const normalizeRowValue = (value) => {
  if (typeof value === 'string') {
    return value.trim();
  }

  return value;
};

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

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

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

  return error;
};

const validateWorkbookFile = (req) => {
  if (!req.file || !req.file.buffer || !req.file.buffer.length) {
    throw createBadRequestError('Workbook upload is empty.');
  }

  const extension = path.extname(req.file.originalname || '').toLowerCase();

  if (!SUPPORTED_WORKBOOK_EXTENSIONS.has(extension)) {
    throw createBadRequestError('Upload an Excel workbook in .xlsx or .xls format.');
  }
};

const getWorksheetHeader = (worksheet) => {
  const rows = XLSX.utils.sheet_to_json(worksheet, {
    header: 1,
    defval: '',
    blankrows: false,
    raw: false,
  });

  const headerRow = rows.find((row) => Array.isArray(row) && row.some((cell) => !isBlankValue(cell)));

  if (!headerRow) {
    return [];
  }

  return headerRow.map((cell) => String(cell).trim()).filter((cell) => cell.length > 0);
};

const validateHeaders = (headers = [], definition) => {
  if (!headers.length) {
    return;
  }

  const missingHeaders = definition.requiredHeaders.filter((header) => !headers.includes(header));
  const unknownHeaders = headers.filter((header) => !definition.allowedHeaders.includes(header));

  if (!missingHeaders.length && !unknownHeaders.length) {
    return;
  }

  const details = [];

  if (missingHeaders.length) {
    details.push(`Missing required columns: ${missingHeaders.join(', ')}`);
  }

  if (unknownHeaders.length) {
    details.push(`Unexpected columns: ${unknownHeaders.join(', ')}`);
  }

  details.push(`Allowed columns: ${definition.allowedHeaders.join(', ')}`);

  throw createBadRequestError(`${definition.label} sheet is invalid. ${details.join('. ')}`);
};

const getWorksheetRows = (worksheet) => {
  const rawRows = XLSX.utils.sheet_to_json(worksheet, {
    defval: '',
    raw: false,
  });

  return rawRows
    .map((row) => Object.entries(row).reduce((accumulator, [key, value]) => {
      const normalizedKey = String(key).trim();
      if (!normalizedKey) {
        return accumulator;
      }

      accumulator[normalizedKey] = normalizeRowValue(value);
      return accumulator;
    }, {}))
    .filter((row) => !isBlankRow(row));
};

const getWorkbookSheets = (workbook) => {
  const matchedSheets = {};

  for (const definition of SHEET_DEFINITIONS) {
    const sheetName = workbook.SheetNames.find((name) => definition.aliases.includes(normalizeSheetName(name)));

    if (!sheetName) {
      matchedSheets[definition.key] = {
        definition,
        name: null,
        headers: [],
        rows: [],
      };
      continue;
    }

    const worksheet = workbook.Sheets[sheetName];
    const headers = getWorksheetHeader(worksheet);
    validateHeaders(headers, definition);

    matchedSheets[definition.key] = {
      definition,
      name: sheetName,
      headers,
      rows: getWorksheetRows(worksheet),
    };
  }

  return matchedSheets;
};

const getWorkbookTemplateBuffer = () => {
  const workbook = XLSX.utils.book_new();

  for (const definition of SHEET_DEFINITIONS) {
    const worksheet = XLSX.utils.aoa_to_sheet([definition.allowedHeaders]);

    XLSX.utils.book_append_sheet(workbook, worksheet, definition.key);
  }

  return XLSX.write(workbook, {
    type: 'buffer',
    bookType: 'xlsx',
  });
};

const ensureWorkbookHasRows = (sheets) => {
  const hasRows = Object.values(sheets).some((sheet) => sheet.rows.length > 0);

  if (!hasRows) {
    throw createBadRequestError('The workbook does not contain any import rows.');
  }
};

const getPermissionSet = (currentUser) =>
  new Set([
    ...((currentUser?.custom_permissions || []).map((permission) => permission.name)),
    ...((currentUser?.app_role_permissions || []).map((permission) => permission.name)),
  ]);

const ensurePermissionsForSheets = (currentUser, sheets) => {
  if (currentUser?.app_role?.globalAccess) {
    return;
  }

  const permissionSet = getPermissionSet(currentUser);
  const requiredPermissions = [];

  if (sheets.tenants.rows.length) {
    requiredPermissions.push('CREATE_TENANTS');
  }

  if (sheets.organizations.rows.length) {
    requiredPermissions.push('CREATE_ORGANIZATIONS');
  }

  if (sheets.properties.rows.length) {
    requiredPermissions.push('CREATE_PROPERTIES');
  }

  if (sheets.unit_types.rows.length) {
    requiredPermissions.push('CREATE_UNIT_TYPES');
  }

  if (sheets.units.rows.length) {
    requiredPermissions.push('CREATE_UNITS');
  }

  const missingPermissions = requiredPermissions.filter((permission) => !permissionSet.has(permission));

  if (missingPermissions.length) {
    throw createBadRequestError(`Missing import permissions: ${missingPermissions.join(', ')}`);
  }

  if (sheets.tenants.rows.length || sheets.organizations.rows.length) {
    throw createBadRequestError('Tenant and organization sheets require a global admin workspace.');
  }
};

const getOrCreateTenantId = async (row, context) => {
  const normalizedRow = normalizeInventoryImportRow(row);
  const reference = normalizedRow.id || normalizedRow.slug || normalizedRow.name;

  if (reference) {
    const existingTenantId = await resolveTenantReference(reference, context.transaction);
    if (existingTenantId) {
      context.summary.tenants.reused += 1;
      return existingTenantId;
    }
  }

  if (!normalizedRow.name) {
    throw createBadRequestError('Tenant name is required.');
  }

  const createdTenant = await TenantsDBApi.create(normalizedRow, {
    currentUser: context.currentUser,
    transaction: context.transaction,
  });

  context.summary.tenants.created += 1;
  return createdTenant.id;
};

const linkOrganizationToTenant = async (tenantId, organizationId, transaction) => {
  if (!tenantId || !organizationId) {
    return;
  }

  const tenant = await db.tenants.findByPk(tenantId, { transaction });

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

  const linkedOrganizations = await tenant.getOrganizations({ transaction });
  const linkedOrganizationIds = new Set((linkedOrganizations || []).map((item) => item.id));

  if (linkedOrganizationIds.has(organizationId)) {
    return;
  }

  linkedOrganizationIds.add(organizationId);

  await tenant.setOrganizations(Array.from(linkedOrganizationIds), { transaction });
};

const getOrCreateOrganizationId = async (row, context) => {
  const normalizedRow = normalizeInventoryImportRow(row);
  let tenantId = null;

  if (normalizedRow.tenant) {
    tenantId = await resolveTenantReference(normalizedRow.tenant, context.transaction);
    if (!tenantId) {
      throw createBadRequestError(`Tenant "${normalizedRow.tenant}" was not found.`);
    }
  }

  const reference = normalizedRow.id || normalizedRow.name;

  if (reference) {
    const existingOrganizationId = await resolveOrganizationReference(reference, {
      tenantId,
      transaction: context.transaction,
    });

    if (existingOrganizationId) {
      await linkOrganizationToTenant(tenantId, existingOrganizationId, context.transaction);
      context.summary.organizations.reused += 1;
      return existingOrganizationId;
    }
  }

  if (!normalizedRow.name) {
    throw createBadRequestError('Organization name is required.');
  }

  const createdOrganization = await OrganizationsDBApi.create(normalizedRow, {
    currentUser: context.currentUser,
    transaction: context.transaction,
  });

  await linkOrganizationToTenant(tenantId, createdOrganization.id, context.transaction);

  context.summary.organizations.created += 1;
  return createdOrganization.id;
};

const getOrCreatePropertyId = async (row, context) => {
  const normalizedRow = normalizeInventoryImportRow(row);
  let tenantId = null;
  let organizationId = null;

  if (normalizedRow.tenant) {
    tenantId = await resolveTenantReference(normalizedRow.tenant, context.transaction);
  }

  if (normalizedRow.organizations) {
    organizationId = await resolveOrganizationReference(normalizedRow.organizations, {
      tenantId,
      transaction: context.transaction,
    });
  }

  if (!tenantId && organizationId) {
    tenantId = await resolveTenantIdForOrganization(organizationId, context.transaction);
  }

  if (context.currentUser?.app_role?.globalAccess && !organizationId) {
    throw createBadRequestError('Organization is required for property import.');
  }

  const reference = normalizedRow.id || normalizedRow.code || normalizedRow.name;

  if (reference) {
    const existingPropertyId = await resolvePropertyReference(reference, {
      currentUser: context.currentUser,
      organizationId,
      tenantId,
      transaction: context.transaction,
    });

    if (existingPropertyId) {
      context.summary.properties.reused += 1;
      return existingPropertyId;
    }
  }

  if (!normalizedRow.name) {
    throw createBadRequestError('Property name is required.');
  }

  normalizedRow.tenant = tenantId || null;
  normalizedRow.organizations = organizationId || null;

  const createdProperty = await PropertiesDBApi.create(normalizedRow, {
    currentUser: context.currentUser,
    transaction: context.transaction,
  });

  context.summary.properties.created += 1;
  return createdProperty.id;
};

const getOrCreateUnitTypeId = async (row, context) => {
  const normalizedRow = normalizeInventoryImportRow(row);
  const propertyId = await resolvePropertyReference(normalizedRow.property, {
    currentUser: context.currentUser,
    transaction: context.transaction,
  });

  if (!propertyId) {
    throw createBadRequestError('Property is required for unit type import.');
  }

  const property = await loadPropertyContext(propertyId, context.transaction);
  const reference = normalizedRow.id || normalizedRow.code || normalizedRow.name;

  if (reference) {
    const existingUnitTypeId = await resolveUnitTypeReference(reference, {
      currentUser: context.currentUser,
      organizationId: property?.organizationsId || null,
      propertyId,
      transaction: context.transaction,
    });

    if (existingUnitTypeId) {
      context.summary.unit_types.reused += 1;
      return existingUnitTypeId;
    }
  }

  if (!normalizedRow.name) {
    throw createBadRequestError('Unit type name is required.');
  }

  normalizedRow.property = propertyId;
  normalizedRow.organizations = property?.organizationsId || null;

  const createdUnitType = await UnitTypesDBApi.create(normalizedRow, {
    currentUser: context.currentUser,
    transaction: context.transaction,
  });

  context.summary.unit_types.created += 1;
  return createdUnitType.id;
};

const resolveExistingUnit = async ({ id, unitNumber, propertyId, transaction }) => {
  const filters = [];

  if (id) {
    filters.push({ id });
  }

  if (unitNumber) {
    filters.push({
      propertyId,
      unit_number: {
        [Op.iLike]: String(unitNumber),
      },
    });
  }

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

  const units = await db.units.findAll({
    where: {
      [Op.or]: filters,
    },
    transaction,
    limit: 2,
    order: [['updatedAt', 'DESC'], ['createdAt', 'DESC']],
  });

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

  if (units.length > 1) {
    throw createBadRequestError(`Unit "${unitNumber || id}" matches multiple records. Use the ID instead.`);
  }

  return units[0];
};

const getOrCreateUnitId = async (row, context) => {
  const normalizedRow = normalizeInventoryImportRow(row);
  const propertyId = await resolvePropertyReference(normalizedRow.property, {
    currentUser: context.currentUser,
    transaction: context.transaction,
  });

  if (!propertyId) {
    throw createBadRequestError('Property is required for unit import.');
  }

  const property = await loadPropertyContext(propertyId, context.transaction);
  const unitTypeId = await resolveUnitTypeReference(normalizedRow.unit_type, {
    currentUser: context.currentUser,
    organizationId: property?.organizationsId || null,
    propertyId,
    transaction: context.transaction,
  });

  if (!unitTypeId) {
    throw createBadRequestError('Unit type is required for unit import.');
  }

  const existingUnit = await resolveExistingUnit({
    id: normalizedRow.id || null,
    unitNumber: normalizedRow.unit_number || null,
    propertyId,
    transaction: context.transaction,
  });

  if (existingUnit) {
    context.summary.units.reused += 1;
    return existingUnit.id;
  }

  if (!normalizedRow.unit_number) {
    throw createBadRequestError('Unit number is required.');
  }

  normalizedRow.property = propertyId;
  normalizedRow.unit_type = unitTypeId;
  normalizedRow.organizations = property?.organizationsId || null;

  const createdUnit = await UnitsDBApi.create(normalizedRow, {
    currentUser: context.currentUser,
    transaction: context.transaction,
  });

  context.summary.units.created += 1;
  return createdUnit.id;
};

module.exports = class PortfolioWorkbookImportService {
  static getSheetDefinitions() {
    return SHEET_DEFINITIONS;
  }

  static getTemplateBuffer() {
    return getWorkbookTemplateBuffer();
  }

  static async importWorkbook(req, res) {
    await processFile(req, res);
    validateWorkbookFile(req);

    const workbook = XLSX.read(req.file.buffer, {
      type: 'buffer',
      raw: false,
    });

    const sheets = getWorkbookSheets(workbook);
    ensureWorkbookHasRows(sheets);
    ensurePermissionsForSheets(req.currentUser, sheets);

    const transaction = await db.sequelize.transaction();

    const summary = {
      tenants: { created: 0, reused: 0 },
      organizations: { created: 0, reused: 0 },
      properties: { created: 0, reused: 0 },
      unit_types: { created: 0, reused: 0 },
      units: { created: 0, reused: 0 },
    };

    const context = {
      currentUser: req.currentUser,
      transaction,
      summary,
    };

    try {
      for (let index = 0; index < sheets.tenants.rows.length; index += 1) {
        try {
          await getOrCreateTenantId(sheets.tenants.rows[index], context);
        } catch (error) {
          throw prefixSheetImportError(error, SHEET_DEFINITION_BY_KEY.tenants.label, index + 2);
        }
      }

      for (let index = 0; index < sheets.organizations.rows.length; index += 1) {
        try {
          await getOrCreateOrganizationId(sheets.organizations.rows[index], context);
        } catch (error) {
          throw prefixSheetImportError(error, SHEET_DEFINITION_BY_KEY.organizations.label, index + 2);
        }
      }

      for (let index = 0; index < sheets.properties.rows.length; index += 1) {
        try {
          await getOrCreatePropertyId(sheets.properties.rows[index], context);
        } catch (error) {
          throw prefixSheetImportError(error, SHEET_DEFINITION_BY_KEY.properties.label, index + 2);
        }
      }

      for (let index = 0; index < sheets.unit_types.rows.length; index += 1) {
        try {
          await getOrCreateUnitTypeId(sheets.unit_types.rows[index], context);
        } catch (error) {
          throw prefixSheetImportError(error, SHEET_DEFINITION_BY_KEY.unit_types.label, index + 2);
        }
      }

      for (let index = 0; index < sheets.units.rows.length; index += 1) {
        try {
          await getOrCreateUnitId(sheets.units.rows[index], context);
        } catch (error) {
          throw prefixSheetImportError(error, SHEET_DEFINITION_BY_KEY.units.label, index + 2);
        }
      }

      await transaction.commit();

      return {
        workbook: {
          fileName: req.file.originalname,
          sheets: Object.values(sheets)
            .filter((sheet) => sheet.name)
            .map((sheet) => ({
              key: sheet.definition.key,
              name: sheet.name,
              rows: sheet.rows.length,
            })),
        },
        summary,
      };
    } catch (error) {
      await transaction.rollback();
      throw error;
    }
  }
};
