TalentFlow — Capa Semántica BI

cube(`Candidates`, {
  sql_table: `public.candidates`,

  // Pre-agregación: conversión diaria por job
  pre_aggregations: {
    daily_funnel: {
      measures: [count, hired_count],
      dimensions: [stage, source],
      time_dimension: created_at,
      granularity: `day`,
      refresh_key: { every: `1 hour` },
    },
  },

  joins: {
    Jobs: {
      relationship: `many_to_one`,
      sql: `${CUBE}.job_id = ${Jobs}.id`,
    },
  },

  measures: {
    count: { type: `count` },

    hired_count: {
      type: `count`,
      filters: [{ sql: `${CUBE}.stage = 'hired'` }],
    },

    // Tasa oferta → contratación
    offer_hire_rate: {
      type: `number`,
      sql: `${hired_count}::float /
        NULLIF(${offer_count}, 0) * 100`,
      format: `percent`,
    },

    offer_count: {
      type: `count`,
      filters: [{
        sql: `${CUBE}.stage IN ('offer','hired')`
      }],
    },
  },

  dimensions: {
    id: {
      type: `number`,
      sql: `id`,
      primary_key: true,
    },
    stage: { type: `string`, sql: `stage` },
    source: { type: `string`, sql: `source` },
    created_at: { type: `time`, sql: `created_at` },
  },
});

cube(`Hires`, {
  sql_table: `public.hires`,

  pre_aggregations: {
    monthly_by_dept: {
      measures: [count, avg_days_to_hire],
      dimensions: [Jobs.department],
      time_dimension: hired_at,
      granularity: `month`,
      refresh_key: { every: `6 hours` },
    },
  },

  joins: {
    Jobs: {
      relationship: `many_to_one`,
      sql: `${CUBE}.job_id = ${Jobs}.id`,
    },
    Accounts: {
      relationship: `many_to_one`,
      sql: `${CUBE}.account_id = ${Accounts}.id`,
    },
  },

  measures: {
    count: { type: `count` },

    // Métrica clave: tiempo promedio por contratación
    avg_days_to_hire: {
      type: `avg`,
      sql: `days_to_hire`,
      format: `number`,
    },

    // Percentil 90 (usando SQL nativo)
    p90_days_to_hire: {
      type: `number`,
      sql: `PERCENTILE_CONT(0.9) WITHIN GROUP
        (ORDER BY days_to_hire)`,
    },

    total_salary_budget: {
      type: `sum`,
      sql: `salary`,
      format: `currency`,
    },
  },

  dimensions: {
    id: {
      type: `number`,
      sql: `id`,
      primary_key: true,
    },
    hired_at: { type: `time`, sql: `hired_at` },
    account_id: { type: `number`, sql: `account_id` },
  },
});

cube(`Accounts`, {
  sql_table: `public.accounts`,

  measures: {
    count: { type: `count` },

    active_count: {
      type: `count`,
      filters: [{
        sql: `${CUBE}.last_activity_at
               > NOW() - INTERVAL '30 days'`
      }],
    },

    // Tasa de activación = empresas con ≥1 job publicado
    activation_rate: {
      type: `number`,
      sql: `${activated_count}::float /
        NULLIF(${count}, 0) * 100`,
      format: `percent`,
    },

    activated_count: {
      type: `count`,
      filters: [{ sql: `${CUBE}.jobs_count > 0` }],
    },

    monthly_retention: {
      type: `number`,
      sql: `${active_count}::float /
        NULLIF(${count}, 0) * 100`,
      format: `percent`,
    },
  },

  dimensions: {
    id: { sql: `id`, type: `number`, primary_key: true },
    name: { sql: `name`, type: `string` },
    plan: { sql: `plan`, type: `string` },
    country: { sql: `country`, type: `string` },
    created_at: { sql: `created_at`, type: `time` },
  },
});

// cube.js — Control de acceso multitenant
module.exports = {

  // Aísla la caché por empresa cliente
  contextToAppId: ({ securityContext }) => {
    return `TALENTFLOW_${securityContext.account_id}`;
  },

  // Reescritura automática de queries por tenant
  queryRewrite: (query, { securityContext }) => {
    const { account_id, role } = securityContext;

    // SIEMPRE filtrar por empresa cliente
    if (account_id) {
      query.filters.push({
        member: `Hires.account_id`,
        operator: `equals`,
        values: [String(account_id)],
      });
    }

    // Solo admins ven salary data
    if (role !== `admin`) {
      query.measures = query.measures
        ?.filter(m => m !== `Hires.total_salary_budget`);
    }

    // Interno CULTIVA: acceso completo
    if (role === `internal`) return query;

    return query;
  },

  // JWT token config
  jwt: {
    key: process.env.CUBE_JWT_SECRET,
    algorithms: [`RS256`],
  },
};