// packages/core/middlewares/resolveTenantFromCore.mjs
import { poolCore, poolTenants } from '@suitecoffee/db';

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

/**
 * Verifica si existe el esquema en la DB de tenants.
 * No requiere setear search_path.
 */
async function schemaExists(schemaName) {
  if (!schemaName) return false;
  const q = `
    SELECT 1
    FROM information_schema.schemata
    WHERE schema_name = $1
    LIMIT 1
  `;
  const { rowCount } = await poolTenants.query(q, [schemaName]);
  return rowCount === 1;
}

/**
 * Devuelve { id, schema } o null.
 * Reglas:
 * 1) Si el usuario tiene default_tenant => usarlo (y validar estado y existencia del schema).
 * 2) Si no, buscar membresías:
 *    - si hay exactamente 1 => usarla (validando schema).
 *    - si hay 0 o >1 => devolver null (forzar selección explícita).
 *
 * @param {import('express').Request} req
 * @param {any} sess  (req.session)
 * @param {Object} [opts]
 * @param {boolean} [opts.debug=false]
 * @param {Console} [opts.logger=console]
 * @param {string[]} [opts.acceptStates=['ready']]  // estados de sc_tenants aceptados
 * @returns {Promise<{id:string, schema:string} | null>}
 */
export async function resolveTenantFromCore(req, sess, opts = {}) {
  const {
    debug = false,
    logger = console,
    acceptStates = ['ready'],
  } = opts;

  const log = (msg, obj) => {
    if (debug) logger.debug?.(`[resolveTenantFromCore] ${msg}`, obj ?? '');
  };

  const sub = sess?.user?.sub;
  if (!sub) {
    log('no-sub-in-session');
    return null;
  }

  try {
    // 1) sc_users: obtener user_id y default_tenant
    const uSql = `
      SELECT user_id, default_tenant
      FROM sc_users
      WHERE sub = $1
      LIMIT 1
    `;
    const ures = await poolCore.query(uSql, [sub]);
    if (ures.rowCount === 0) {
      log('user-not-found', { sub });
      return null;
    }

    const { user_id, default_tenant } = ures.rows[0] ?? {};

    // Helper para validar fila de tenant y existencia de schema
    const validateTenantRow = async (row) => {
      if (!row) return null;
      const { tenant_id, schema_name, state } = row;
      if (!UUID_RX.test(String(tenant_id))) return null;
      if (!schema_name) return null;
      if (acceptStates.length && !acceptStates.includes(String(state))) return null;

      // Comprobar que el schema exista realmente en la DB de tenants
      const exists = await schemaExists(schema_name);
      if (!exists) {
        log('schema-missing-in-tenants-db', { schema_name });
        return null;
      }
      return { id: String(tenant_id), schema: String(schema_name) };
    };

    // 2) Si hay default_tenant, cargar su schema y validar
    if (default_tenant) {
      const tSql = `
        SELECT tenant_id, schema_name, state
        FROM sc_tenants
        WHERE tenant_id = $1
        LIMIT 1
      `;
      const tres = await poolCore.query(tSql, [default_tenant]);
      if (tres.rowCount === 1) {
        const ok = await validateTenantRow(tres.rows[0]);
        if (ok) {
          sess.tenant = ok;
          log('resolved-from-default_tenant', ok);
          return ok;
        }
        // default_tenant roto → seguimos a membresías
        log('default_tenant-invalid', { default_tenant });
      }
    }

    // 3) Sin default_tenant válido: ver membresías (aceptando sólo tenants en estados permitidos)
    const mSql = `
      SELECT m.tenant_id, t.schema_name, t.state, t.created_at, m.role
      FROM sc_memberships m
      JOIN sc_tenants t USING (tenant_id)
      WHERE m.user_id = $1
        ${acceptStates.length ? `AND t.state = ANY($2)` : ''}
      ORDER BY (m.role = 'owner') DESC, t.created_at ASC
      LIMIT 2
    `;
    const mParams = acceptStates.length ? [user_id, acceptStates] : [user_id];
    const mres = await poolCore.query(mSql, mParams);

    if (mres.rowCount === 1) {
      const ok = await validateTenantRow(mres.rows[0]);
      if (ok) {
        sess.tenant = ok;
        log('resolved-from-single-membership', ok);
        return ok;
      }
      log('single-membership-invalid-row', mres.rows[0]);
      return null;
    }

    // 0 o >1 membresías → el usuario debe elegir explícitamente
    log('ambiguous-or-no-memberships', { count: mres.rowCount });
    return null;
  } catch (err) {
    logger.error?.('[resolveTenantFromCore] error', { message: err?.message });
    return null; // preferimos no romper el request; el middleware decidirá
  }
}