'use strict';

const AREA_CATALOG_SPECS = {
  areasrestaurant: ['idarearestaurant', 'idarea', 'clave', 'descripcion', 'descripcionarea', 'nombre', 'nombrearea', 'area'],
  areasrestaurante: ['idarearestaurant', 'idarea', 'clave', 'descripcion', 'descripcionarea', 'nombre', 'nombrearea', 'area'],
  areas: ['idarearestaurant', 'idarea', 'clave', 'descripcion', 'descripcionarea', 'nombre', 'nombrearea', 'area'],
  area: ['idarearestaurant', 'idarea', 'clave', 'descripcion', 'descripcionarea', 'nombre', 'nombrearea', 'area'],
};

const ID_COLUMNS = ['idarearestaurant', 'idarea', 'clave'];
const NAME_COLUMNS = ['descripcion', 'descripcionarea', 'nombre', 'nombrearea', 'area'];
const SOURCE_AREA_COLUMNS = ['idarearestaurant', 'idarea', 'area'];

function detectedColumn(metadata, tableName, candidates) {
  return candidates.find((column) => metadata.tables?.[tableName]?.columns?.[column]) || null;
}

function detectAreaCatalog(metadata) {
  for (const tableName of Object.keys(AREA_CATALOG_SPECS)) {
    if (!metadata.tables?.[tableName]?.exists) continue;
    const idColumn = detectedColumn(metadata, tableName, ID_COLUMNS);
    const nameColumn = detectedColumn(metadata, tableName, NAME_COLUMNS);
    if (idColumn) return { tableName, idColumn, nameColumn };
  }
  return null;
}

function detectSourceAreaColumn(metadata, tableName) {
  return detectedColumn(metadata, tableName, SOURCE_AREA_COLUMNS);
}

function areaCatalogSql(metadata, databasePrefix, sourceAreaExpression, alias = 'area_catalog') {
  const catalog = detectAreaCatalog(metadata);
  if (!catalog || !sourceAreaExpression || sourceAreaExpression === 'NULL') {
    return { join: '', nameExpression: 'NULL', catalog: null };
  }
  const join = `LEFT JOIN ${databasePrefix}${catalog.tableName} ${alias}
    ON LTRIM(RTRIM(CAST(${alias}.${catalog.idColumn} AS NVARCHAR(80)))) = LTRIM(RTRIM(CAST(${sourceAreaExpression} AS NVARCHAR(80))))`;
  const nameExpression = catalog.nameColumn
    ? `NULLIF(LTRIM(RTRIM(CAST(${alias}.${catalog.nameColumn} AS NVARCHAR(180)))), '')`
    : 'NULL';
  return { join, nameExpression, catalog };
}

function normalizedType(name) {
  const value = String(name || '').normalize('NFD').replace(/[\u0300-\u036f]/g, '').toUpperCase();
  if (/UBER|RAPPI|DIDI|PLATAFORMA/.test(value)) return 'delivery_platform';
  if (/DOMICIL|DELIVERY|REPARTO|ENVIO/.test(value)) return 'delivery';
  if (/LLEVAR|RECOGER|TAKE.?OUT|PICK.?UP/.test(value)) return 'takeaway';
  if (/DRIVE|AUTO/.test(value)) return 'drive_thru';
  if (/COMEDOR|MESA|SALON/.test(value)) return 'dine_in';
  return value ? 'other' : 'unknown';
}

function normalizeServiceArea({ code, name } = {}) {
  const areaCode = code == null ? null : String(code).trim() || null;
  const areaName = name == null ? null : String(name).trim() || null;
  return {
    area_codigo: areaCode,
    area_nombre: areaName || (areaCode ? `Área ${areaCode}` : null),
    area_tipo: normalizedType(areaName),
    area_estado: areaName ? 'resolved' : areaCode ? 'code_only' : 'unknown',
  };
}

module.exports = {
  AREA_CATALOG_SPECS,
  SOURCE_AREA_COLUMNS,
  detectAreaCatalog,
  detectSourceAreaColumn,
  areaCatalogSql,
  normalizeServiceArea,
  normalizedType,
};
