'use strict';

/**
 * db/perf-tablespace.js — TuneVault's own tablespace-usage history.
 *
 * WHY THIS TABLE EXISTS
 * Tablespace growth trends have only ever come from DBA_HIST_TBSPC_SPACE_USAGE,
 * which is AWR and therefore Diagnostics Pack. A customer who answers "not
 * licensed" on the consent panel loses the growth chart entirely and gets nothing
 * in its place — correct, but useless.
 *
 * DBA_TABLESPACE_USAGE_METRICS carries no pack requirement and reports the same
 * numbers. It just has no history: it is a point-in-time view. So we sample it on
 * a schedule and keep the points ourselves. This is the free-view tier of the
 * fallback: no customer install, no PERFSTAT schema, no licence exposure, and it
 * works on Standard Edition 2 where AWR does not exist at all.
 *
 * VOLUME
 * Sampled every 6 hours (tablespace growth is a daily-granularity question; more
 * frequent sampling buys nothing and multiplies rows). A database with 20
 * tablespaces produces ~80 rows/day, ~29k rows/year. Retention is 400 days so a
 * year-over-year comparison — quarter-end vs the same quarter-end — is possible,
 * which is the actual reason a DBA looks at this chart.
 */

const pool = require('./index');

const RETENTION_DAYS = 400;

/**
 * Insert one sample sweep for a connection. Rows: [{name, used_mb, total_mb, pct}].
 * Written as a single multi-row insert; a failed sweep is skipped entirely rather
 * than half-recorded, so a partial read cannot look like tablespaces shrinking.
 */
async function insertSample(connectionId, rows) {
  if (!Array.isArray(rows) || !rows.length) return 0;
  let written = 0;
  for (const r of rows) {
    if (!r || !r.name) continue;
    try {
      await pool.query(
        `INSERT INTO perf_tablespace_samples
           (connection_id, tablespace_name, used_mb, total_mb, pct_used, sampled_at)
         VALUES (:p1, :p2, :p3, :p4, :p5, CURRENT_TIMESTAMP)`,
        { p1: connectionId,
          p2: String(r.name).slice(0, 128),
          p3: Number.isFinite(r.used_mb)  ? r.used_mb  : null,
          p4: Number.isFinite(r.total_mb) ? r.total_mb : null,
          p5: Number.isFinite(r.pct)      ? r.pct      : null }
      );
      written++;
    } catch (e) {
      console.warn('[perf-tablespace] insert failed for %s/%s: %s', connectionId, r.name, e.message);
    }
  }
  return written;
}

/**
 * Daily series per tablespace, shaped to match mapGrowth() in routes/space-advisor
 * so the AWR path and this one render through identical code.
 *
 * MAX per day, not AVG: the question a growth chart answers is "how close did this
 * get to full", and an average hides a spike that filled the tablespace at 03:00.
 */
async function getGrowthSeries(connectionId, days = 30) {
  const { rows } = await pool.query(
    `SELECT tablespace_name,
            TO_CHAR(TRUNC(sampled_at), 'YYYY-MM-DD')          AS day,
            ROUND(MAX(used_mb) / 1024, 2)                     AS used_gb
       FROM perf_tablespace_samples
      WHERE connection_id = :p1
        AND sampled_at > CURRENT_TIMESTAMP - NUMTODSINTERVAL(:p2, 'DAY')
      GROUP BY tablespace_name, TRUNC(sampled_at)
      ORDER BY tablespace_name, TRUNC(sampled_at)`,
    { p1: connectionId, p2: days }
  );
  // Uppercase keys so mapGrowth() consumes this exactly as it consumes AWR rows.
  return rows.map(r => ({
    TABLESPACE_NAME: r.tablespace_name,
    DAY:             r.day,
    USED_GB:         r.used_gb,
  }));
}

/** How many distinct days of history exist — drives honest "collecting since" copy. */
async function getCoverage(connectionId) {
  const { rows } = await pool.query(
    `SELECT COUNT(DISTINCT TRUNC(sampled_at)) AS days,
            MIN(sampled_at)                   AS since
       FROM perf_tablespace_samples
      WHERE connection_id = :p1`,
    { p1: connectionId }
  );
  return { days: Number(rows[0]?.days || 0), since: rows[0]?.since || null };
}

async function purgeOld() {
  const res = await pool.query(
    `DELETE FROM perf_tablespace_samples
      WHERE sampled_at < CURRENT_TIMESTAMP - NUMTODSINTERVAL(:p1, 'DAY')`,
    { p1: RETENTION_DAYS }
  );
  return res.rowCount || 0;
}

module.exports = { insertSample, getGrowthSeries, getCoverage, purgeOld, RETENTION_DAYS };
