'use strict';

const fs = require('fs');
const path = require('path');
const { getPool, sql } = require('../../db');
const { db } = require('../../utils/dbName');
const logger = require('../../utils/logger');
const { encryptSecret, decryptSecret, maskSecret } = require('./billing.crypto');
const { PROVIDER, INVOICE_STATUS, MICFDI_CONTRACT } = require('./billing.constants');
const AutoFacturaProviderMock = require('./billing.mockProvider');
const { AutoFacturaProviderHttp, sanitizePayload } = require('./billing.httpProvider');
const offlineSync = require('../sync');

function toDate(value) {
  if (!value) return null;
  return String(value).slice(0, 10);
}

function n(value) {
  return Number(value || 0);
}

function cleanFilePart(value) {
  return String(value || '')
    .replace(/[^a-zA-Z0-9._-]/g, '_')
    .slice(0, 120);
}

function billingArtifactsDir(tenantId) {
  return path.join(__dirname, '..', '..', '..', 'data', 'billing-artifacts', cleanFilePart(tenantId || 'local'));
}

function billingCertificatesDir(tenantId) {
  return path.join(billingArtifactsDir(tenantId), 'certificates');
}

function decodeUploadBase64(value) {
  const raw = String(value || '');
  const data = raw.includes(',') ? raw.split(',').pop() : raw;
  if (!data) return null;
  return Buffer.from(data, 'base64');
}

function saveCertificateFile(tenantContext, emitter, field, ext) {
  const data = decodeUploadBase64(emitter?.[field]);
  if (!data || !data.length) return undefined;
  const dir = billingCertificatesDir(tenantContext.tenantId);
  fs.mkdirSync(dir, { recursive: true });
  const rfc = cleanFilePart(emitter.rfc || 'emisor');
  const filePath = path.join(dir, `${rfc}.${ext}`);
  fs.writeFileSync(filePath, data, { mode: 0o600 });
  return filePath;
}

function persistProviderArtifacts(tenantContext, invoice) {
  if (!invoice || (!invoice.xml_base64 && !invoice.pdf_base64)) return invoice;
  const dir = billingArtifactsDir(tenantContext.tenantId);
  fs.mkdirSync(dir, { recursive: true });
  const baseName = cleanFilePart(invoice.uuid || invoice.folio || `cfdi-${Date.now()}`);
  const next = { ...invoice };
  if (invoice.xml_base64) {
    const xmlPath = path.join(dir, `${baseName}.xml`);
    fs.writeFileSync(xmlPath, Buffer.from(String(invoice.xml_base64), 'base64'));
    next.xml_url_or_path = xmlPath;
  }
  if (invoice.pdf_base64) {
    const pdfPath = path.join(dir, `${baseName}.pdf`);
    fs.writeFileSync(pdfPath, Buffer.from(String(invoice.pdf_base64), 'base64'));
    next.pdf_url_or_path = pdfPath;
  }
  return next;
}

function isFiscalOffline() {
  const offlineFirst = String(process.env.ARKHON_OFFLINE_FIRST || 'true').toLowerCase() !== 'false';
  const internetStatus = String(process.env.ARKHON_INTERNET_STATUS || '').toLowerCase();
  const forcedOffline = String(process.env.ARKHON_FORCE_OFFLINE || '').toLowerCase() === 'true';
  return forcedOffline || (offlineFirst && ['offline', '0', 'false'].includes(internetStatus));
}

async function deferFiscalOperation(tenantContext, entityType, entityId, operation, payload) {
  const pending = {
    status: 'pending_connection',
    pending_sync: true,
    entity_type: entityType,
    entity_id: String(entityId),
    operation,
    message: 'Operacion fiscal registrada localmente y pendiente por conexion.',
  };
  try {
    await offlineSync.service.enqueueEvent({
      entity_type: entityType,
      entity_id: String(entityId),
      operation: 'request',
      payload: {
        billing_operation: operation,
        tenant_id: tenantContext.tenantId,
        branch_id: tenantContext.branchId,
        payload,
      },
    });
  } catch (err) {
    logger.warn({ event: 'billing_offline_defer_enqueue_failed', operation, error: err.message });
  }
  return pending;
}

function normalizeSettings(row, includeSecret = false) {
  if (!row) {
    return {
      provider: PROVIDER,
      base_url: '',
      auth_username: '',
      auth_password_masked: '',
      auth_mode: 'basic',
      auth_path: '',
      token_field: '',
      environment: 'sandbox',
      active: false,
      emitters: [],
      branches: [],
    };
  }
  const storedSecret = row.auth_password_encrypted
    ? decryptSecret(row.auth_password_encrypted)
    : (row.api_key_encrypted ? decryptSecret(row.api_key_encrypted) : null);
  const secret = includeSecret ? storedSecret : null;
  return {
    id: row.id,
    tenant_id: row.tenant_id,
    provider: row.provider,
    base_url: row.base_url || '',
    auth_username: row.auth_username || '',
    auth_password: secret,
    auth_password_masked: maskSecret(storedSecret || ''),
    auth_mode: row.auth_mode || 'basic',
    auth_path: row.auth_path || '',
    token_field: row.token_field || '',
    environment: row.environment || 'sandbox',
    active: !!row.active,
  };
}

async function logProvider(tenantId, operation, status, payload, response) {
  try {
    const pool = await getPool();
    await pool.request()
      .input('tenant_id', sql.NVarChar(80), tenantId)
      .input('provider', sql.NVarChar(50), PROVIDER)
      .input('operation', sql.NVarChar(100), operation)
      .input('status', sql.NVarChar(40), status)
      .input('request_id', sql.NVarChar(80), payload?.request_id || null)
      .input('payload', sql.NVarChar(sql.MAX), JSON.stringify(sanitizePayload(payload || {})).slice(0, 4000))
      .input('response', sql.NVarChar(sql.MAX), JSON.stringify(sanitizePayload(response || {})).slice(0, 4000))
      .query(`
        INSERT INTO ${db()}billing_provider_logs
          (tenant_id, provider, operation, status, request_id, sanitized_payload, sanitized_response)
        VALUES (@tenant_id, @provider, @operation, @status, @request_id, @payload, @response)
      `);
  } catch (err) {
    logger.warn({ event: 'billing_log_failed', error: err.message });
  }
}

async function getSettings(tenantContext, includeSecret = false) {
  const pool = await getPool();
  const settingsResult = await pool.request()
    .input('tenant_id', sql.NVarChar(80), tenantContext.tenantId)
    .input('provider', sql.NVarChar(50), PROVIDER)
    .query(`SELECT TOP 1 * FROM ${db()}tenant_billing_settings WHERE tenant_id=@tenant_id AND provider=@provider`);

  const settings = normalizeSettings(settingsResult.recordset[0], includeSecret);

  const emitters = await pool.request()
    .input('tenant_id', sql.NVarChar(80), tenantContext.tenantId)
    .query(`SELECT * FROM ${db()}billing_emitters WHERE tenant_id=@tenant_id ORDER BY active DESC, legal_name`);

  const branches = await pool.request()
    .input('tenant_id', sql.NVarChar(80), tenantContext.tenantId)
    .query(`SELECT * FROM ${db()}billing_branches WHERE tenant_id=@tenant_id ORDER BY active DESC, name`);

  return {
    ...settings,
    emitters: emitters.recordset.map((emitter) => ({
      ...emitter,
      certificate_configured: Boolean(emitter.certificate_cer_path && emitter.certificate_key_path && emitter.certificate_password_encrypted),
      certificate_cer_path: emitter.certificate_cer_path ? '[configured]' : '',
      certificate_key_path: emitter.certificate_key_path ? '[configured]' : '',
      certificate_password_encrypted: undefined,
    })),
    branches: branches.recordset,
    permissions: [
      'billing.dashboard.view',
      'billing.tickets.view',
      'billing.invoices.view',
      'billing.invoices.create',
      'billing.invoices.cancel',
      'billing.global.create',
      'billing.manual.create',
      'billing.reports.view',
    ],
  };
}

async function listBranches(tenantContext) {
  const pool = await getPool();
  const branches = await pool.request()
    .input('tenant_id', sql.NVarChar(80), tenantContext.tenantId)
    .query(`
      SELECT id, external_branch_id, name, code, active
      FROM ${db()}billing_branches
      WHERE tenant_id=@tenant_id AND active=1
      ORDER BY name
    `);
  return branches.recordset;
}

async function saveSettings(tenantContext, payload) {
  const pool = await getPool();
  const current = await getSettings(tenantContext, true);
  const nativePayload = applyNativeProviderDefaults(payload);
  const authPasswordValue = nativePayload.auth_password || nativePayload.api_password || '';
  const authPassword = authPasswordValue ? encryptSecret(authPasswordValue) : (current.id ? undefined : null);
  const authMode = nativePayload.auth_mode === 'login' ? 'login' : 'basic';

  const request = pool.request()
    .input('tenant_id', sql.NVarChar(80), tenantContext.tenantId)
    .input('provider', sql.NVarChar(50), PROVIDER)
    .input('base_url', sql.NVarChar(500), String(nativePayload.base_url || '').trim())
    .input('auth_username', sql.NVarChar(255), String(nativePayload.auth_username || nativePayload.api_username || '').trim() || null)
    .input('auth_mode', sql.NVarChar(30), authMode)
    .input('auth_path', sql.NVarChar(255), String(nativePayload.auth_path || '').trim() || null)
    .input('token_field', sql.NVarChar(120), String(nativePayload.token_field || '').trim() || null)
    .input('environment', sql.NVarChar(20), nativePayload.environment === 'sandbox' ? 'sandbox' : 'production')
    .input('active', sql.Bit, nativePayload.active !== false && nativePayload.active !== 'false');

  if (authPassword !== undefined) request.input('auth_password', sql.NVarChar(sql.MAX), authPassword);

  await request.query(`
    MERGE ${db()}tenant_billing_settings AS tgt
    USING (VALUES (@tenant_id, @provider, @base_url, @auth_username, @auth_mode, @auth_path, @token_field, @environment, @active)) AS src
      (tenant_id, provider, base_url, auth_username, auth_mode, auth_path, token_field, environment, active)
    ON tgt.tenant_id = src.tenant_id AND tgt.provider = src.provider
    WHEN MATCHED THEN UPDATE SET
      base_url = src.base_url,
      auth_username = src.auth_username,
      auth_mode = src.auth_mode,
      auth_path = src.auth_path,
      token_field = src.token_field,
      environment = src.environment,
      active = src.active,
      ${authPassword !== undefined ? 'auth_password_encrypted = @auth_password,' : ''}
      updated_at = GETDATE()
    WHEN NOT MATCHED THEN INSERT
      (tenant_id, provider, base_url, auth_username, auth_password_encrypted, auth_mode, auth_path, token_field, environment, active)
      VALUES (src.tenant_id, src.provider, src.base_url, src.auth_username, ${authPassword !== undefined ? '@auth_password' : 'NULL'}, src.auth_mode, src.auth_path, src.token_field, src.environment, src.active);
  `);

  if (nativePayload.emitter) await upsertEmitter(tenantContext, nativePayload.emitter);
  if (Array.isArray(nativePayload.branches)) {
    for (const branch of nativePayload.branches) await upsertBranch(tenantContext, branch);
  }

  return getSettings(tenantContext);
}

async function upsertEmitter(tenantContext, emitter) {
  if (!emitter || !emitter.rfc || !emitter.legal_name) return null;
  const pool = await getPool();
  const cerPath = saveCertificateFile(tenantContext, emitter, 'cer_base64', 'cer');
  const keyPath = saveCertificateFile(tenantContext, emitter, 'key_base64', 'key');
  const certPassword = emitter.key_password || emitter.certificate_password || '';
  const certPasswordEncrypted = certPassword ? encryptSecret(certPassword) : undefined;
  await pool.request()
    .input('tenant_id', sql.NVarChar(80), tenantContext.tenantId)
    .input('rfc', sql.NVarChar(13), String(emitter.rfc).trim().toUpperCase())
    .input('legal_name', sql.NVarChar(255), String(emitter.legal_name).trim())
    .input('fiscal_regime', sql.NVarChar(20), emitter.fiscal_regime || null)
    .input('postal_code', sql.NVarChar(10), emitter.postal_code || null)
    .input('certificate_cer_path', sql.NVarChar(500), cerPath || null)
    .input('certificate_key_path', sql.NVarChar(500), keyPath || null)
    .input('certificate_password_encrypted', sql.NVarChar(sql.MAX), certPasswordEncrypted || null)
    .input('active', sql.Bit, emitter.active !== false)
    .query(`
      MERGE ${db()}billing_emitters AS tgt
      USING (VALUES (@tenant_id, @rfc, @legal_name, @fiscal_regime, @postal_code, @active)) AS src
        (tenant_id, rfc, legal_name, fiscal_regime, postal_code, active)
      ON tgt.tenant_id=src.tenant_id AND tgt.rfc=src.rfc
      WHEN MATCHED THEN UPDATE SET legal_name=src.legal_name, fiscal_regime=src.fiscal_regime,
        postal_code=src.postal_code, active=src.active,
        certificate_cer_path=COALESCE(@certificate_cer_path, certificate_cer_path),
        certificate_key_path=COALESCE(@certificate_key_path, certificate_key_path),
        certificate_password_encrypted=COALESCE(@certificate_password_encrypted, certificate_password_encrypted),
        certificate_status=CASE
          WHEN COALESCE(@certificate_cer_path, certificate_cer_path) IS NOT NULL
           AND COALESCE(@certificate_key_path, certificate_key_path) IS NOT NULL
           AND COALESCE(@certificate_password_encrypted, certificate_password_encrypted) IS NOT NULL
          THEN 'configured'
          ELSE certificate_status
        END,
        updated_at=GETDATE()
      WHEN NOT MATCHED THEN INSERT
        (tenant_id, rfc, legal_name, fiscal_regime, postal_code, certificate_cer_path, certificate_key_path,
         certificate_password_encrypted, certificate_status, active)
        VALUES
        (src.tenant_id, src.rfc, src.legal_name, src.fiscal_regime, src.postal_code, @certificate_cer_path,
         @certificate_key_path, @certificate_password_encrypted,
         CASE WHEN @certificate_cer_path IS NOT NULL AND @certificate_key_path IS NOT NULL AND @certificate_password_encrypted IS NOT NULL
              THEN 'configured' ELSE NULL END,
         src.active);
    `);
}

function applyNativeProviderDefaults(payload = {}) {
  const firstBranch = Array.isArray(payload.branches) ? payload.branches[0] || {} : {};
  const branchName = String(firstBranch.name || firstBranch.code || firstBranch.external_branch_id || '').trim();
  const emitter = payload.emitter || {};
  return {
    ...payload,
    provider: PROVIDER,
    base_url: payload.base_url || process.env.ARKHON_BILLING_PROVIDER_URL || MICFDI_CONTRACT.baseUrl,
    auth_mode: payload.auth_mode || MICFDI_CONTRACT.authMode,
    auth_path: payload.auth_path || MICFDI_CONTRACT.authPath,
    token_field: payload.token_field || MICFDI_CONTRACT.tokenField,
    auth_username: payload.auth_username || process.env.ARKHON_BILLING_API_USER || '',
    auth_password: payload.auth_password || process.env.ARKHON_BILLING_API_PASSWORD || '',
    environment: payload.environment || process.env.ARKHON_BILLING_ENV || 'production',
    active: payload.active !== false && payload.active !== 'false',
    emitter: {
      ...emitter,
      rfc: emitter.rfc || process.env.ARKHON_BILLING_DEFAULT_RFC || 'XAXX010101000',
      legal_name: emitter.legal_name || process.env.ARKHON_BILLING_DEFAULT_LEGAL_NAME || branchName || 'Sucursal ARKHON',
    },
    branches: Array.isArray(payload.branches) ? payload.branches.map((branch) => {
      const name = String(branch.name || branch.code || branch.external_branch_id || tenantContextBranchName()).trim();
      const code = String(branch.code || branch.external_branch_id || name).trim();
      return { ...branch, name, code, external_branch_id: branch.external_branch_id || code };
    }) : [],
  };
}

function tenantContextBranchName() {
  return process.env.SUCURSAL_NOMBRE || process.env.SUCURSAL_ID || 'Sucursal principal';
}

async function upsertBranch(tenantContext, branch) {
  if (!branch || !branch.name) return null;
  const pool = await getPool();
  await pool.request()
    .input('tenant_id', sql.NVarChar(80), tenantContext.tenantId)
    .input('external_branch_id', sql.NVarChar(100), branch.external_branch_id || branch.code || tenantContext.branchId)
    .input('name', sql.NVarChar(255), branch.name)
    .input('code', sql.NVarChar(80), branch.code || branch.external_branch_id || tenantContext.branchId)
    .input('active', sql.Bit, branch.active !== false)
    .query(`
      MERGE ${db()}billing_branches AS tgt
      USING (VALUES (@tenant_id, @external_branch_id, @name, @code, @active)) AS src
        (tenant_id, external_branch_id, name, code, active)
      ON tgt.tenant_id=src.tenant_id AND ISNULL(tgt.external_branch_id,'')=ISNULL(src.external_branch_id,'')
      WHEN MATCHED THEN UPDATE SET name=src.name, code=src.code, active=src.active, updated_at=GETDATE()
      WHEN NOT MATCHED THEN INSERT (tenant_id, external_branch_id, name, code, active)
        VALUES (src.tenant_id, src.external_branch_id, src.name, src.code, src.active);
    `);
}

async function getProvider(tenantContext) {
  const settings = await getSettings(tenantContext, true);
  if (process.env.BILLING_PROVIDER_MODE === 'mock') {
    if (process.env.NODE_ENV === 'production') throw new Error('Billing mock provider bloqueado en produccion');
    return new AutoFacturaProviderMock();
  }
  if (!settings.active) throw new Error('Conector de Autofactura inactivo. Configuralo desde Arkhon Engineering.');
  return new AutoFacturaProviderHttp(settings, logger);
}

async function testConnection(tenantContext) {
  const provider = await getProvider(tenantContext);
  try {
    const result = await provider.healthCheck({ tenantContext });
    await logProvider(tenantContext.tenantId, 'healthCheck', 'ok', {}, result);
    return result;
  } catch (err) {
    await logProvider(tenantContext.tenantId, 'healthCheck', 'error', {}, { error: err.message });
    throw err;
  }
}

async function syncTickets(tenantContext, filters = {}) {
  const provider = await getProvider(tenantContext);
  const tickets = await provider.syncTickets(tenantContext, filters);
  const rows = Array.isArray(tickets) ? tickets : (tickets?.tickets || []);
  const pool = await getPool();

  for (const ticket of rows) {
    const branch = ticket.branch || {};
    await upsertBranch(tenantContext, {
      external_branch_id: branch.external_branch_id || branch.id || tenantContext.branchId,
      name: branch.name || tenantContext.branchName,
      code: branch.code || branch.external_branch_id || tenantContext.branchId,
      active: true,
    });

    const br = await pool.request()
      .input('tenant_id', sql.NVarChar(80), tenantContext.tenantId)
      .input('external_branch_id', sql.NVarChar(100), branch.external_branch_id || branch.id || tenantContext.branchId)
      .query(`SELECT TOP 1 id FROM ${db()}billing_branches WHERE tenant_id=@tenant_id AND external_branch_id=@external_branch_id`);

    await pool.request()
      .input('tenant_id', sql.NVarChar(80), tenantContext.tenantId)
      .input('branch_id', sql.Int, br.recordset[0]?.id || null)
      .input('external_ticket_id', sql.NVarChar(120), ticket.external_ticket_id)
      .input('folio', sql.NVarChar(80), ticket.folio)
      .input('date', sql.DateTime, ticket.date ? new Date(ticket.date) : new Date())
      .input('payment_method', sql.NVarChar(80), ticket.payment_method || null)
      .input('subtotal', sql.Decimal(18, 2), n(ticket.subtotal))
      .input('taxes', sql.Decimal(18, 2), n(ticket.taxes))
      .input('total', sql.Decimal(18, 2), n(ticket.total))
      .input('status', sql.NVarChar(40), ticket.status || 'pending')
      .input('customer_rfc', sql.NVarChar(13), ticket.customer_rfc || null)
      .input('cfdi_uuid', sql.NVarChar(80), ticket.cfdi_uuid || null)
      .query(`
        MERGE ${db()}billing_tickets AS tgt
        USING (VALUES (@tenant_id, @external_ticket_id)) AS src (tenant_id, external_ticket_id)
        ON tgt.tenant_id=src.tenant_id AND tgt.external_ticket_id=src.external_ticket_id
        WHEN MATCHED THEN UPDATE SET branch_id=@branch_id, folio=@folio, date=@date, payment_method=@payment_method,
          subtotal=@subtotal, taxes=@taxes, total=@total, status=@status, customer_rfc=@customer_rfc,
          cfdi_uuid=@cfdi_uuid, updated_at=GETDATE()
        WHEN NOT MATCHED THEN INSERT
          (tenant_id, branch_id, external_ticket_id, folio, date, payment_method, subtotal, taxes, total, status, customer_rfc, cfdi_uuid)
          VALUES (@tenant_id, @branch_id, @external_ticket_id, @folio, @date, @payment_method, @subtotal, @taxes, @total, @status, @customer_rfc, @cfdi_uuid);
      `);

    if ((ticket.status || '').toLowerCase() === INVOICE_STATUS.INVOICED && ticket.cfdi_uuid) {
      const savedTicket = await pool.request()
        .input('tenant_id', sql.NVarChar(80), tenantContext.tenantId)
        .input('external_ticket_id', sql.NVarChar(120), ticket.external_ticket_id)
        .query(`SELECT TOP 1 id FROM ${db()}billing_tickets WHERE tenant_id=@tenant_id AND external_ticket_id=@external_ticket_id`);

      await pool.request()
        .input('tenant_id', sql.NVarChar(80), tenantContext.tenantId)
        .input('ticket_id', sql.Int, savedTicket.recordset[0]?.id || null)
        .input('uuid', sql.NVarChar(80), ticket.cfdi_uuid)
        .input('folio', sql.NVarChar(80), ticket.folio)
        .input('customer_rfc', sql.NVarChar(13), ticket.customer_rfc || null)
        .input('subtotal', sql.Decimal(18, 2), n(ticket.subtotal))
        .input('taxes', sql.Decimal(18, 2), n(ticket.taxes))
        .input('total', sql.Decimal(18, 2), n(ticket.total))
        .input('stamped_at', sql.DateTime, ticket.date ? new Date(ticket.date) : new Date())
        .query(`
          IF NOT EXISTS (SELECT 1 FROM ${db()}billing_invoices WHERE tenant_id=@tenant_id AND uuid=@uuid)
          INSERT INTO ${db()}billing_invoices
            (tenant_id, ticket_id, uuid, folio, customer_rfc, subtotal, taxes, total, status, stamped_at)
          VALUES (@tenant_id, @ticket_id, @uuid, @folio, @customer_rfc, @subtotal, @taxes, @total, 'stamped', @stamped_at)
        `);
    }
  }

  await logProvider(tenantContext.tenantId, 'syncTickets', 'ok', filters, { count: rows.length });
  return { synced: rows.length };
}

function applyTicketFilters(request, tenantContext, filters) {
  request.input('tenant_id', sql.NVarChar(80), tenantContext.tenantId);
  request.input('q', sql.NVarChar(120), filters.q ? `%${filters.q}%` : null);
  request.input('branch_id', sql.Int, filters.branch_id ? Number(filters.branch_id) : null);
  request.input('from', sql.Date, filters.from || null);
  request.input('to', sql.Date, filters.to || null);
}

async function listTickets(tenantContext, status, filters = {}) {
  const pool = await getPool();
  const request = pool.request();
  applyTicketFilters(request, tenantContext, filters);
  request.input('status', sql.NVarChar(40), status);
  const result = await request.query(`
    SELECT TOP 500 t.*, b.name AS branch_name, b.external_branch_id
    FROM ${db()}billing_tickets t
    LEFT JOIN ${db()}billing_branches b ON b.id=t.branch_id AND b.tenant_id=t.tenant_id
    WHERE t.tenant_id=@tenant_id AND t.status=@status
      AND (@q IS NULL OR t.folio LIKE @q OR t.external_ticket_id LIKE @q OR t.customer_rfc LIKE @q)
      AND (@branch_id IS NULL OR t.branch_id=@branch_id)
      AND (@from IS NULL OR CAST(t.date AS DATE) >= @from)
      AND (@to IS NULL OR CAST(t.date AS DATE) <= @to)
    ORDER BY t.date DESC, t.id DESC
  `);
  return result.recordset;
}

function withRealStampAuthorization(tenantContext, options = {}) {
  if (!options.realStampAuthorization) return tenantContext;
  return { ...tenantContext, realStampAuthorization: options.realStampAuthorization };
}

function markSaveFailure(error, stamped) {
  error.pacCalled = true;
  error.fiscalStatus = 'save_failed';
  error.manualReviewRequired = true;
  error.code = error.code || 'save_failed';
  error.providerResponse = sanitizePayload(stamped?.provider_response || {});
  return error;
}

function failAuthorizedStampBeforePac(code) {
  const error = new Error(code);
  error.code = code;
  error.status = 503;
  error.pacCalled = false;
  error.fiscalStatus = 'failed_before_pac_call';
  error.manualReviewRequired = false;
  return error;
}

async function findAutofacturaTicket(tenantContext, branchCode, billingCode) {
  const code = String(billingCode || '').trim();
  if (!code) return null;
  const branch = String(branchCode || '').trim();
  const pool = await getPool();
  const result = await pool.request()
    .input('tenant_id', sql.NVarChar(80), tenantContext.tenantId)
    .input('billing_code', sql.NVarChar(120), code)
    .input('branch_code', sql.NVarChar(100), branch || null)
    .query(`
      SELECT TOP 1 t.*, b.name AS branch_name, b.code AS branch_code, b.external_branch_id
      FROM ${db()}billing_tickets t
      LEFT JOIN ${db()}billing_branches b ON b.id=t.branch_id AND b.tenant_id=t.tenant_id
      WHERE t.tenant_id=@tenant_id
        AND (t.folio=@billing_code OR t.external_ticket_id=@billing_code)
        AND (
          @branch_code IS NULL OR @branch_code='' OR
          b.code=@branch_code OR b.external_branch_id=@branch_code OR CONVERT(NVARCHAR(20), b.id)=@branch_code
        )
      ORDER BY t.date DESC, t.id DESC
    `);
  return result.recordset[0] || null;
}

async function findInvoiceForAutofactura(tenantContext, branchCode, billingCode) {
  const code = String(billingCode || '').trim();
  if (!code) return null;
  const branch = String(branchCode || '').trim();
  const pool = await getPool();
  const result = await pool.request()
    .input('tenant_id', sql.NVarChar(80), tenantContext.tenantId)
    .input('billing_code', sql.NVarChar(120), code)
    .input('branch_code', sql.NVarChar(100), branch || null)
    .query(`
      SELECT TOP 1 i.*, t.folio AS ticket_folio, t.external_ticket_id, b.name AS branch_name
      FROM ${db()}billing_invoices i
      LEFT JOIN ${db()}billing_tickets t ON t.id=i.ticket_id AND t.tenant_id=i.tenant_id
      LEFT JOIN ${db()}billing_branches b ON b.id=t.branch_id AND b.tenant_id=t.tenant_id
      WHERE i.tenant_id=@tenant_id
        AND (i.folio=@billing_code OR i.uuid=@billing_code OR t.folio=@billing_code OR t.external_ticket_id=@billing_code)
        AND (
          @branch_code IS NULL OR @branch_code='' OR
          b.code=@branch_code OR b.external_branch_id=@branch_code OR CONVERT(NVARCHAR(20), b.id)=@branch_code
        )
      ORDER BY ISNULL(i.stamped_at, i.created_at) DESC, i.id DESC
    `);
  return result.recordset[0] || null;
}

async function listInvoices(tenantContext, filters = {}) {
  const pool = await getPool();
  const request = pool.request();
  request.input('tenant_id', sql.NVarChar(80), tenantContext.tenantId)
    .input('q', sql.NVarChar(120), filters.q ? `%${filters.q}%` : null)
    .input('from', sql.Date, filters.from || null)
    .input('to', sql.Date, filters.to || null);
  const result = await request.query(`
    SELECT TOP 500 i.*, t.folio AS ticket_folio, b.name AS branch_name
    FROM ${db()}billing_invoices i
    LEFT JOIN ${db()}billing_tickets t ON t.id=i.ticket_id AND t.tenant_id=i.tenant_id
    LEFT JOIN ${db()}billing_branches b ON b.id=t.branch_id AND b.tenant_id=t.tenant_id
    WHERE i.tenant_id=@tenant_id
      AND (@q IS NULL OR i.uuid LIKE @q OR i.folio LIKE @q OR i.customer_rfc LIKE @q OR i.customer_name LIKE @q)
      AND (@from IS NULL OR CAST(ISNULL(i.stamped_at, i.created_at) AS DATE) >= @from)
      AND (@to IS NULL OR CAST(ISNULL(i.stamped_at, i.created_at) AS DATE) <= @to)
    ORDER BY ISNULL(i.stamped_at, i.created_at) DESC, i.id DESC
  `);
  return result.recordset;
}

async function getInvoiceById(tenantContext, id) {
  const pool = await getPool();
  const result = await pool.request()
    .input('tenant_id', sql.NVarChar(80), tenantContext.tenantId)
    .input('id', sql.Int, Number(id))
    .query(`SELECT TOP 1 * FROM ${db()}billing_invoices WHERE tenant_id=@tenant_id AND id=@id`);
  return result.recordset[0] || null;
}

async function dashboard(tenantContext, filters = {}) {
  const pool = await getPool();
  const request = pool.request()
    .input('tenant_id', sql.NVarChar(80), tenantContext.tenantId)
    .input('from', sql.Date, filters.from || null)
    .input('to', sql.Date, filters.to || null)
    .input('branch_id', sql.Int, filters.branch_id ? Number(filters.branch_id) : null);

  const result = await request.query(`
    SELECT
      COUNT(*) AS ventas_periodo,
      SUM(CASE WHEN t.status='invoiced' THEN t.total ELSE 0 END) AS monto_facturado,
      SUM(CASE WHEN i.status='cancelled' THEN 1 ELSE 0 END) AS cancelaciones,
      CAST(CASE WHEN COUNT(*)=0 THEN 0 ELSE 100.0 * SUM(CASE WHEN t.status='invoiced' THEN 1 ELSE 0 END) / COUNT(*) END AS DECIMAL(8,2)) AS tasa_facturacion,
      SUM(t.total) AS venta_total
    FROM ${db()}billing_tickets t
    LEFT JOIN ${db()}billing_invoices i ON i.ticket_id=t.id AND i.tenant_id=t.tenant_id
    WHERE t.tenant_id=@tenant_id
      AND (@from IS NULL OR CAST(t.date AS DATE) >= @from)
      AND (@to IS NULL OR CAST(t.date AS DATE) <= @to)
      AND (@branch_id IS NULL OR t.branch_id=@branch_id)
  `);

  const byBranch = await request.query(`
    SELECT TOP 20 ISNULL(b.name, 'Sin sucursal') AS label, SUM(t.total) AS total, COUNT(*) AS count
    FROM ${db()}billing_tickets t
    LEFT JOIN ${db()}billing_branches b ON b.id=t.branch_id AND b.tenant_id=t.tenant_id
    WHERE t.tenant_id=@tenant_id
      AND (@from IS NULL OR CAST(t.date AS DATE) >= @from)
      AND (@to IS NULL OR CAST(t.date AS DATE) <= @to)
      AND (@branch_id IS NULL OR t.branch_id=@branch_id)
    GROUP BY b.name
    ORDER BY total DESC
  `);

  const byMonth = await request.query(`
    SELECT FORMAT(t.date, 'yyyy-MM') AS label, SUM(t.total) AS total, COUNT(*) AS count
    FROM ${db()}billing_tickets t
    WHERE t.tenant_id=@tenant_id
      AND (@from IS NULL OR CAST(t.date AS DATE) >= @from)
      AND (@to IS NULL OR CAST(t.date AS DATE) <= @to)
      AND (@branch_id IS NULL OR t.branch_id=@branch_id)
    GROUP BY FORMAT(t.date, 'yyyy-MM')
    ORDER BY label
  `);

  const byHour = await request.query(`
    SELECT DATEPART(HOUR, t.date) AS label, SUM(t.total) AS total, COUNT(*) AS count
    FROM ${db()}billing_tickets t
    WHERE t.tenant_id=@tenant_id
      AND (@from IS NULL OR CAST(t.date AS DATE) >= @from)
      AND (@to IS NULL OR CAST(t.date AS DATE) <= @to)
      AND (@branch_id IS NULL OR t.branch_id=@branch_id)
    GROUP BY DATEPART(HOUR, t.date)
    ORDER BY label
  `);

  const pending = await listTickets(tenantContext, INVOICE_STATUS.PENDING, filters);
  return {
    metrics: result.recordset[0] || {},
    comparison: { previous_period_delta: null },
    by_branch: byBranch.recordset,
    by_month: byMonth.recordset,
    by_hour: byHour.recordset,
    top_products: [],
    pending_count: pending.length,
    last_updated: new Date().toISOString(),
  };
}

async function stampTicketInvoice(tenantContext, ticketId, customerFiscalData, options = {}) {
  const pool = await getPool();
  const ticketResult = await pool.request()
    .input('tenant_id', sql.NVarChar(80), tenantContext.tenantId)
    .input('id', sql.Int, Number(ticketId))
    .query(`SELECT TOP 1 * FROM ${db()}billing_tickets WHERE tenant_id=@tenant_id AND id=@id`);
  const ticket = ticketResult.recordset[0];
  if (!ticket) throw new Error('Ticket no encontrado para este tenant');
  if (ticket.status !== INVOICE_STATUS.PENDING) throw new Error('El ticket no esta pendiente de facturar');
  if (isFiscalOffline()) return deferFiscalOperation(tenantContext, 'billing.pending_stamp', ticket.id, 'stampTicketInvoice', { ticket_id: ticket.id, customerFiscalData });

  const provider = await getProvider(tenantContext);
  const providerContext = withRealStampAuthorization(tenantContext, options);
  const stamped = await provider.stampTicketInvoice(providerContext, ticket, customerFiscalData);
  let invoice;
  try {
    invoice = await insertInvoice(providerContext, { ...stamped, ticket_id: ticket.id });
  } catch (error) {
    throw markSaveFailure(error, stamped);
  }
  await pool.request()
    .input('tenant_id', sql.NVarChar(80), tenantContext.tenantId)
    .input('id', sql.Int, ticket.id)
    .input('uuid', sql.NVarChar(80), invoice.uuid)
    .input('customer_rfc', sql.NVarChar(13), invoice.customer_rfc)
    .query(`UPDATE ${db()}billing_tickets SET status='invoiced', cfdi_uuid=@uuid, customer_rfc=@customer_rfc, updated_at=GETDATE() WHERE tenant_id=@tenant_id AND id=@id`);
  await logProvider(tenantContext.tenantId, 'stampTicketInvoice', 'ok', { ticket_id: ticket.id }, { uuid: invoice.uuid });
  return invoice;
}

async function insertInvoice(tenantContext, invoice) {
  invoice = persistProviderArtifacts(tenantContext, invoice);
  const authorization = tenantContext.realStampAuthorization || {};
  const pool = await getPool();
  const result = await pool.request()
    .input('tenant_id', sql.NVarChar(80), tenantContext.tenantId)
    .input('ticket_id', sql.Int, invoice.ticket_id || null)
    .input('uuid', sql.NVarChar(80), invoice.uuid)
    .input('serie', sql.NVarChar(20), invoice.serie || null)
    .input('folio', sql.NVarChar(80), invoice.folio || null)
    .input('customer_rfc', sql.NVarChar(13), invoice.customer_rfc || null)
    .input('customer_name', sql.NVarChar(255), invoice.customer_name || null)
    .input('usage_cfdi', sql.NVarChar(20), invoice.usage_cfdi || null)
    .input('fiscal_regime', sql.NVarChar(20), invoice.fiscal_regime || null)
    .input('tipo', sql.NVarChar(20), invoice.tipo || (invoice.esglobal ? 'global' : 'normal'))
    .input('endpoint_usado', sql.NVarChar(40), invoice.endpoint_usado || null)
    .input('esglobal', sql.Bit, !!invoice.esglobal)
    .input('subtotal', sql.Decimal(18, 2), n(invoice.subtotal))
    .input('taxes', sql.Decimal(18, 2), n(invoice.taxes))
    .input('total', sql.Decimal(18, 2), n(invoice.total))
    .input('status', sql.NVarChar(40), invoice.status || 'stamped')
    .input('xml', sql.NVarChar(500), invoice.xml_url_or_path || null)
    .input('pdf', sql.NVarChar(500), invoice.pdf_url_or_path || null)
    .input('xml_base64', sql.NVarChar(sql.MAX), invoice.xml_base64 || null)
    .input('pdf_base64', sql.NVarChar(sql.MAX), invoice.pdf_base64 || null)
    .input('provider', sql.NVarChar(50), authorization.provider || (invoice.provider || PROVIDER))
    .input('branch_id', sql.NVarChar(100), authorization.branchId || tenantContext.branchId || null)
    .input('attempt_id', sql.NVarChar(80), authorization.attemptId || invoice.attempt_id || null)
    .input('codigosucursal_masked', sql.NVarChar(80), authorization.codigosucursal_masked || null)
    .input('provider_response_sanitized', sql.NVarChar(sql.MAX), JSON.stringify(sanitizePayload(invoice.provider_response || {})).slice(0, 4000))
    .input('stamped_at', sql.DateTime, invoice.stamped_at ? new Date(invoice.stamped_at) : new Date())
    .query(`
      INSERT INTO ${db()}billing_invoices
        (tenant_id, ticket_id, uuid, serie, folio, customer_rfc, customer_name, usage_cfdi, fiscal_regime,
         tipo, endpoint_usado, esglobal, subtotal, taxes, total, status, xml_url_or_path, pdf_url_or_path,
         xml_base64, pdf_base64, provider, branch_id, attempt_id, codigosucursal_masked,
         provider_response_sanitized, stamped_at)
      OUTPUT INSERTED.*
      VALUES
        (@tenant_id, @ticket_id, @uuid, @serie, @folio, @customer_rfc, @customer_name, @usage_cfdi, @fiscal_regime,
         @tipo, @endpoint_usado, @esglobal, @subtotal, @taxes, @total, @status, @xml, @pdf,
         @xml_base64, @pdf_base64, @provider, @branch_id, @attempt_id, @codigosucursal_masked,
         @provider_response_sanitized, @stamped_at)
    `);
  return result.recordset[0];
}

async function stampManualInvoice(tenantContext, payload, options = {}) {
  // TODO fiscal: confirmar reglas de IVA, retenciones RESICO y claves SAT con el proveedor antes de automatizar calculos.
  if (isFiscalOffline()) return deferFiscalOperation(tenantContext, 'billing.pending_stamp', payload.request_id || 'manual', 'stampManualInvoice', payload);
  const provider = await getProvider(tenantContext);
  const providerContext = withRealStampAuthorization(tenantContext, options);
  const stamped = await provider.stampManualInvoice(providerContext, payload);
  let invoice;
  try {
    invoice = await insertInvoice(providerContext, stamped);
  } catch (error) {
    throw markSaveFailure(error, stamped);
  }
  await logProvider(tenantContext.tenantId, 'stampManualInvoice', 'ok', payload, { uuid: invoice.uuid });
  return invoice;
}

async function createGlobalInvoice(tenantContext, payload, options = {}) {
  // TODO fiscal: confirmar periodicidad, mes, forma de pago y reglas CFDI global con el proveedor.
  if (isFiscalOffline()) {
    if (options.realStampAuthorization) throw failAuthorizedStampBeforePac('micfdi_real_stamp_agent_offline');
    return deferFiscalOperation(tenantContext, 'billing.pending_global_invoice', `${payload.year || 'yyyy'}-${payload.month || 'mm'}`, 'createGlobalInvoice', payload);
  }
  const provider = await getProvider(tenantContext);
  const providerContext = withRealStampAuthorization(tenantContext, options);
  const stamped = await provider.createGlobalInvoice(providerContext, payload);
  let invoice;
  try {
    invoice = await insertInvoice(providerContext, {
      ...stamped,
      tipo: 'global',
      endpoint_usado: 'global',
      esglobal: true,
    });
  } catch (error) {
    throw markSaveFailure(error, stamped);
  }
  const total = n(payload.total || payload.summary?.total || payload.expected?.total ||
    payload.tickets?.reduce((sum, item) => sum + n(item.importe), 0) || stamped.total);
  const pool = await getPool();
  let result;
  try {
    result = await pool.request()
    .input('tenant_id', sql.NVarChar(80), tenantContext.tenantId)
    .input('emitter_id', sql.Int, payload.emitter_id || null)
    .input('branch_id', sql.Int, payload.branch_id || null)
    .input('year', sql.Int, Number(payload.year || payload.anio))
    .input('month', sql.Int, Number(payload.month || payload.meses))
    .input('periodicity', sql.NVarChar(30), payload.periodicity || payload.periodicidad || null)
    .input('payment_scope', sql.NVarChar(50), payload.payment_scope || null)
    .input('uuid', sql.NVarChar(80), invoice.uuid || null)
    .input('status', sql.NVarChar(40), invoice.status || 'stamped')
    .input('total', sql.Decimal(18, 2), total)
    .input('xml', sql.NVarChar(500), invoice.xml_url_or_path || null)
    .input('pdf', sql.NVarChar(500), invoice.pdf_url_or_path || null)
    .query(`
      INSERT INTO ${db()}billing_global_invoices
        (tenant_id, emitter_id, branch_id, year, month, periodicity, payment_scope, uuid, status, total, xml_url_or_path, pdf_url_or_path)
      OUTPUT INSERTED.*
      VALUES (@tenant_id, @emitter_id, @branch_id, @year, @month, @periodicity, @payment_scope, @uuid, @status, @total, @xml, @pdf)
    `);
  } catch (error) {
    throw markSaveFailure(error, stamped);
  }
  await logProvider(tenantContext.tenantId, 'createGlobalInvoice', 'ok', payload, { uuid: invoice.uuid });
  return { ...invoice, global_summary_id: result.recordset[0]?.id || null };
}

async function cancelInvoice(tenantContext, id, payload, options = {}) {
  const invoice = await getInvoiceById(tenantContext, id);
  if (!invoice) throw new Error('Factura no encontrada para este tenant');
  if (isFiscalOffline()) return deferFiscalOperation(tenantContext, 'billing.pending_cancel', invoice.id, 'cancelInvoice', { invoice_id: invoice.id, sat_reason: payload.sat_reason, replacement_uuid: payload.replacement_uuid || null });
  const provider = await getProvider(tenantContext);
  const providerContext = withRealStampAuthorization(tenantContext, options);
  const cancelled = await provider.cancelInvoice(providerContext, invoice.uuid, payload.sat_reason, payload.replacement_uuid, { esglobal: !!invoice.esglobal });
  const pool = await getPool();
  await pool.request()
    .input('tenant_id', sql.NVarChar(80), tenantContext.tenantId)
    .input('invoice_id', sql.Int, invoice.id)
    .input('sat_reason', sql.NVarChar(10), payload.sat_reason)
    .input('replacement_uuid', sql.NVarChar(80), payload.replacement_uuid || null)
    .input('acuse', sql.NVarChar(500), cancelled.acknowledgement_url_or_path || null)
    .input('status', sql.NVarChar(40), cancelled.status || 'requested')
    .query(`
      INSERT INTO ${db()}billing_cancellations
        (tenant_id, invoice_id, sat_reason, replacement_uuid, acknowledgement_url_or_path, status)
      VALUES (@tenant_id, @invoice_id, @sat_reason, @replacement_uuid, @acuse, @status);
      UPDATE ${db()}billing_invoices
      SET status=@status, cancelled_at=GETDATE(), updated_at=GETDATE()
      WHERE tenant_id=@tenant_id AND id=@invoice_id;
    `);
  await logProvider(tenantContext.tenantId, 'cancelInvoice', 'ok', { invoice_id: invoice.id }, cancelled);
  return cancelled;
}

async function listCancellations(tenantContext) {
  const pool = await getPool();
  const result = await pool.request()
    .input('tenant_id', sql.NVarChar(80), tenantContext.tenantId)
    .query(`
      SELECT TOP 500 c.*, i.uuid, i.folio, i.customer_name, i.total
      FROM ${db()}billing_cancellations c
      INNER JOIN ${db()}billing_invoices i ON i.id=c.invoice_id AND i.tenant_id=c.tenant_id
      WHERE c.tenant_id=@tenant_id
      ORDER BY c.created_at DESC
    `);
  return result.recordset;
}

async function listGlobalInvoices(tenantContext) {
  const pool = await getPool();
  const result = await pool.request()
    .input('tenant_id', sql.NVarChar(80), tenantContext.tenantId)
    .query(`SELECT TOP 200 * FROM ${db()}billing_global_invoices WHERE tenant_id=@tenant_id ORDER BY created_at DESC`);
  return result.recordset;
}

async function downloadInvoice(tenantContext, id, type) {
  const invoice = await getInvoiceById(tenantContext, id);
  if (!invoice) throw new Error('Factura no encontrada para este tenant');
  const localPath = type === 'xml' ? invoice.xml_url_or_path : invoice.pdf_url_or_path;
  if (localPath && fs.existsSync(localPath)) return fs.readFileSync(localPath);
  const provider = await getProvider(tenantContext);
  return type === 'xml'
    ? provider.downloadXml(tenantContext, invoice.uuid)
    : provider.downloadPdf(tenantContext, invoice.uuid);
}

async function sendInvoiceEmail(tenantContext, id, email) {
  const invoice = await getInvoiceById(tenantContext, id);
  if (!invoice) throw new Error('Factura no encontrada para este tenant');
  const provider = await getProvider(tenantContext);
  return provider.sendInvoiceEmail(tenantContext, invoice.uuid, email);
}

async function report(tenantContext, type, filters = {}) {
  const dash = await dashboard(tenantContext, filters);
  const invoices = await listInvoices(tenantContext, filters);
  return { type, ...dash, invoices };
}

module.exports = {
  getSettings,
  listBranches,
  saveSettings,
  testConnection,
  syncTickets,
  dashboard,
  listTickets,
  findAutofacturaTicket,
  findInvoiceForAutofactura,
  listInvoices,
  getInvoiceById,
  stampTicketInvoice,
  stampManualInvoice,
  createGlobalInvoice,
  listGlobalInvoices,
  cancelInvoice,
  listCancellations,
  downloadInvoice,
  sendInvoiceEmail,
  report,
  toDate,
};
