'use strict';

const crypto = require('crypto');
const { inspectTables, qname } = require('../../softrestaurantIntrospection');
const { isSensitiveKey } = require('../security/payload-security');

function identifier(value) {
  if (!/^[A-Za-z0-9_]+$/.test(value)) throw new Error(`Identificador SQL invalido: ${value}`);
  return `[${value}]`;
}

function cleanValue(value) {
  if (value instanceof Date) return value.toISOString();
  if (value === null || value === undefined) return '';
  return value;
}

function deterministicExternalId(sourceTable, idColumns, row) {
  const raw = idColumns.map((column) => cleanValue(row[column]) ?? '').join('|');
  return `${sourceTable}:${crypto.createHash('sha256').update(raw).digest('hex')}`;
}

function createTableExtractor(config) {
  const requestedColumns = [...new Set([...config.idColumns, ...(config.columns || []), ...(config.watermarkCandidates || [])])];
  const forbiddenColumns = requestedColumns.filter(isSensitiveKey);
  if (forbiddenColumns.length) {
    throw new Error(`Extractor ${config.table} incluye columnas sensibles prohibidas: ${forbiddenColumns.join(', ')}`);
  }

  async function extract(pool, cursor = null, limit = 100) {
    const introspection = await inspectTables(pool, { [config.table]: requestedColumns });
    const table = introspection.tables?.[config.table];
    if (!table?.exists || config.idColumns.some((column) => !table.columns?.[column])) return [];

    const available = requestedColumns.filter((column) => table.columns?.[column]);
    const watermarkColumn = (config.watermarkCandidates || []).find((column) => table.columns?.[column]) || null;
    const tieColumns = config.idColumns.filter((column) => column !== watermarkColumn);
    const cursorColumns = tieColumns.length ? tieColumns : config.idColumns;
    const keyParts = cursorColumns.map((column) => `COALESCE(CAST(${identifier(column)} AS nvarchar(255)), '')`);
    const keyExpression = keyParts.length === 1 ? keyParts[0] : `CONCAT(${keyParts.join(", '|', ")})`;
    const dateCursor = typeof cursor === 'string' ? cursor : cursor?.watermark;
    const keyCursor = typeof cursor === 'object' ? cursor?.key : null;
    const where = [];
    const request = pool.request().input('mb_limit', Math.max(1, Math.min(Number(limit) || 100, 1000)));

    if (watermarkColumn && dateCursor && keyCursor !== null && keyCursor !== undefined) {
      where.push(`(${identifier(watermarkColumn)} > @mb_watermark OR (${identifier(watermarkColumn)} = @mb_watermark AND ${keyExpression} > @mb_key))`);
      request.input('mb_watermark', new Date(dateCursor));
      request.input('mb_key', String(keyCursor));
    } else if (watermarkColumn && dateCursor) {
      where.push(`${identifier(watermarkColumn)} > @mb_watermark`);
      request.input('mb_watermark', new Date(dateCursor));
    } else if (!watermarkColumn && keyCursor !== null && keyCursor !== undefined) {
      where.push(`${keyExpression} > @mb_key`);
      request.input('mb_key', String(keyCursor));
    }

    const orderBy = watermarkColumn
      ? `${identifier(watermarkColumn)}, ${cursorColumns.map(identifier).join(', ')}`
      : cursorColumns.map(identifier).join(', ');
    const result = await request.query(`
      SELECT TOP (@mb_limit) ${available.map(identifier).join(', ')}
      FROM ${qname(config.table)} WITH (NOLOCK)
      ${where.length ? `WHERE ${where.join(' AND ')}` : ''}
      ORDER BY ${orderBy}
    `);
    return result.recordset.map((row) => ({
      ...Object.fromEntries(Object.entries(row).map(([key, value]) => [key, cleanValue(value)])),
      external_id: deterministicExternalId(config.table, config.idColumns, row),
      source_table: config.table,
      source_id: config.idColumns.map((column) => cleanValue(row[column]) ?? '').join('|'),
      source_updated_at: watermarkColumn && row[watermarkColumn] ? new Date(row[watermarkColumn]).toISOString() : '2000-01-01T00:00:00.000Z',
      cursor: {
        watermark: watermarkColumn && row[watermarkColumn] ? new Date(row[watermarkColumn]).toISOString() : null,
        key: cursorColumns.map((column) => cleanValue(row[column]) ?? '').join('|'),
      },
    }));
  }

  return { entityType: config.entityType, sourceTables: [config.table], extract };
}

function createMultiTableExtractor(entityType, configs) {
  const extractors = configs.map(createTableExtractor);
  return {
    entityType,
    sourceTables: configs.map((config) => config.table),
    async extract(pool, cursor = {}, limit = 100) {
      const perTableLimit = Math.max(1, Math.floor(limit / configs.length));
      const rows = [];
      for (const extractor of extractors) {
        const table = extractor.sourceTables[0];
        rows.push(...await extractor.extract(pool, cursor?.[table] || cursor, perTableLimit));
      }
      return rows.sort((a, b) => String(a.source_updated_at).localeCompare(String(b.source_updated_at)) || a.external_id.localeCompare(b.external_id));
    },
  };
}

module.exports = { createTableExtractor, createMultiTableExtractor, deterministicExternalId };
