tilbudgivern/scripts/run-ordrestyring-simulations.js

const path = require('path');
const fs = require('fs/promises');
const mysql = require('mysql2/promise');
const databaseService = require('../backend/src/services/databaseService');
const OrderSuggestionService = require('../backend/src/services/orderSuggestionService');

require('dotenv').config({
  path: path.join(__dirname, '..', 'backend/.env')
});

const MAX_CASES = Math.max(1, parseInt(process.env.ORDRESTYRING_SIM_MAX_CASES || '10', 10) || 10);

function isRoofCaseText(value) {
  const text = (value || '').toLowerCase();
  return [
    'tag', 'tegl', 'tagpap', 'velux', 'tagvindue', 'skorsten', 'inddækning', 'inddaekning', 'b7', 'eternit', 'rygning'
  ].some(token => text.includes(token));
}

function ensureEnvVar(key) {
  if (!process.env[key]) {
    throw new Error(`Environment variable ${key} is required to run simulations`);
  }
}

async function loadRichCases(connection, limit = MAX_CASES * 2) {
  const [rows] = await connection.execute(
    `
      SELECT
        ranked.case_number,
        ranked.customer_number,
        ranked.description,
        ranked.remarks,
        ranked.status,
        ranked.latest_activity
      FROM (
        SELECT
          c.case_number,
          c.customer_number,
          c.description,
          c.remarks,
          c.status,
          COALESCE(c.updated_at, c.creation_date, c.created_at) AS latest_activity,
          ROW_NUMBER() OVER (
            PARTITION BY c.case_number
            ORDER BY COALESCE(c.updated_at, c.creation_date, c.created_at, 0) DESC, c.id DESC
          ) AS row_rank
        FROM ordrestyring_local.cases c
        WHERE c.case_number IS NOT NULL
          AND c.case_number <> ''
          AND EXISTS (
            SELECT 1
            FROM ordrestyring_local.hours h
            WHERE h.new_case_number = c.case_number
              AND h.new_case_number <> ''
          )
          AND EXISTS (
            SELECT 1
            FROM ordrestyring_local.case_materials m
            WHERE m.case_number = c.case_number
              AND m.case_number <> ''
          )
      ) ranked
      WHERE ranked.row_rank = 1
      ORDER BY GREATEST(
        CHAR_LENGTH(COALESCE(ranked.description, '')),
        CHAR_LENGTH(COALESCE(ranked.remarks, ''))
      ) DESC,
      ranked.latest_activity DESC
      LIMIT ?
    `,
    [limit]
  );

  return rows;
}

async function loadDiverseCaseNumbers(connection, limit = MAX_CASES) {
  const roofPackageIds = [
    'komplet_tagrenovering',
    'b7_tag_udskiftning',
    'tegl_tag_renovering',
    'tagpap_renovering',
    'tagreparation_skorsten',
    'velux_tagvindue'
  ];
  const roofTypeHints = ['b7', 'tegl', 'tagpap', 'skorsten', 'velux'];

  const [rows] = await connection.execute(
    `
      SELECT
        ranked.case_number,
        ranked.inferred_package_id,
        ranked.last_activity_at_ordrestyring
      FROM (
        SELECT
          f.case_number,
          COALESCE(NULLIF(TRIM(f.inferred_package_id), ''), 'ukendt') AS inferred_package_id,
          f.last_activity_at_ordrestyring,
          ROW_NUMBER() OVER (
            PARTITION BY COALESCE(NULLIF(TRIM(f.inferred_package_id), ''), 'ukendt')
            ORDER BY COALESCE(f.last_activity_at_ordrestyring, f.created_at_ordrestyring, '1970-01-01') DESC,
                     f.case_number ASC
          ) AS package_rank
        FROM tilbudgivern.ordrestyring_case_features f
        WHERE f.case_number IS NOT NULL
          AND f.case_number <> ''
          AND (
            COALESCE(NULLIF(TRIM(f.inferred_package_id), ''), '') IN (${roofPackageIds.map(() => '?').join(', ')})
            OR COALESCE(NULLIF(TRIM(f.inferred_roof_type), ''), '') IN (${roofTypeHints.map(() => '?').join(', ')})
            OR LOWER(COALESCE(f.search_text, '')) LIKE '%tag%'
            OR LOWER(COALESCE(f.search_text, '')) LIKE '%velux%'
            OR LOWER(COALESCE(f.search_text, '')) LIKE '%tegl%'
            OR LOWER(COALESCE(f.search_text, '')) LIKE '%skorsten%'
            OR LOWER(COALESCE(f.search_text, '')) LIKE '%eternit%'
            OR LOWER(COALESCE(f.search_text, '')) LIKE '%tagpap%'
          )
      ) ranked
      ORDER BY ranked.package_rank ASC,
               ranked.last_activity_at_ordrestyring DESC
      LIMIT ?
    `,
    [...roofPackageIds, ...roofTypeHints, limit]
  );

  return rows.map(row => row.case_number).filter(Boolean);
}

async function loadCasesByNumbers(connection, caseNumbers = []) {
  if (!Array.isArray(caseNumbers) || caseNumbers.length === 0) {
    return [];
  }

  const placeholders = caseNumbers.map(() => '?').join(', ');
  const [rows] = await connection.execute(
    `
      SELECT
        ranked.case_number,
        ranked.customer_number,
        ranked.description,
        ranked.remarks,
        ranked.status,
        ranked.latest_activity
      FROM (
        SELECT
          c.case_number,
          c.customer_number,
          c.description,
          c.remarks,
          c.status,
          COALESCE(c.updated_at, c.creation_date, c.created_at) AS latest_activity,
          ROW_NUMBER() OVER (
            PARTITION BY c.case_number
            ORDER BY COALESCE(c.updated_at, c.creation_date, c.created_at, 0) DESC, c.id DESC
          ) AS row_rank
        FROM ordrestyring_local.cases c
        WHERE c.case_number IN (${placeholders})
      ) ranked
      WHERE ranked.row_rank = 1
    `,
    caseNumbers
  );

  const map = new Map(rows.map(row => [row.case_number, row]));
  return caseNumbers.map(caseNumber => map.get(caseNumber)).filter(Boolean);
}

async function createSimulationProject(databaseService, caseRow) {
  const projectName = `Simulering: Ordrestyring sag ${caseRow.case_number}`;
  const description = `${caseRow.description || ''} ${caseRow.remarks || ''}`.trim() || 'Simuleret beskrivelse';
  const customerName = caseRow.customer_name || caseRow.customer_number || 'Ordrestyring kunde';
  const [result] = await databaseService.pool.execute(
    `
      INSERT INTO customer_projects (
        project_name,
        customer_name,
        customer_number,
        project_description
      ) VALUES (?, ?, ?, ?)
    `,
    [
      projectName,
      customerName,
      caseRow.customer_number || null,
      description
    ]
  );

  const projectId = result.insertId;
  await databaseService.pool.execute(
    `
      INSERT INTO roof_geometry (
        project_id,
        roof_type,
        total_area,
        roof_pitch
      ) VALUES (?, ?, ?, ?)
    `,
    [projectId, 'skraat_tag', 120, 30]
  );

  return projectId;
}

async function cleanupSimulationProject(databaseService, projectId) {
  await databaseService.pool.execute('DELETE FROM roof_geometry WHERE project_id = ?', [projectId]);
  await databaseService.pool.execute('DELETE FROM customer_projects WHERE id = ?', [projectId]);
}

function summarizeSuggestion(suggestion) {
  const materialsCount = suggestion.materialsDraft.length;
  const laborHours = suggestion.laborDraft
    .reduce((sum, task) => sum + (Number(task.totalHours) || 0), 0);

  return {
    recommendation: suggestion.recommendation?.recommendedPackage?.packageId || suggestion.recommendation?.recommendedPackage?.name || 'Ingen pakke',
    description: suggestion.recommendation?.descriptionDraft || suggestion.recommendation?.suggestedDescription || 'Ingen beskrivelse',
    materialsCount,
    laborHours: Number(laborHours.toFixed(2)),
    warnings: suggestion.recommendation?.warnings || [],
    clarifications: suggestion.recommendation?.clarifications || []
  };
}

async function runSimulation() {
  ensureEnvVar('DB_PASSWORD');
  let ordrestyringConn = null;

  try {
    ordrestyringConn = await mysql.createConnection({
      host: process.env.DB_HOST || '127.0.0.1',
      port: parseInt(process.env.DB_PORT, 10) || 3306,
      user: process.env.DB_USER || 'tilbudgivern_service',
      password: process.env.DB_PASSWORD,
      database: 'ordrestyring_local'
    });

    await databaseService.initialize();
    const suggestionService = new OrderSuggestionService(databaseService);

    const diverseCaseNumbers = await loadDiverseCaseNumbers(ordrestyringConn, MAX_CASES);
    const diverseCases = await loadCasesByNumbers(ordrestyringConn, diverseCaseNumbers);
    const richCases = await loadRichCases(ordrestyringConn, MAX_CASES * 3);
    const selectedCaseMap = new Map();

    for (const row of diverseCases) {
      if (!isRoofCaseText(`${row.description || ''} ${row.remarks || ''}`)) {
        continue;
      }
      selectedCaseMap.set(row.case_number, row);
    }
    for (const row of richCases) {
      if (selectedCaseMap.size >= MAX_CASES) {
        break;
      }
      if (!isRoofCaseText(`${row.description || ''} ${row.remarks || ''}`)) {
        continue;
      }
      if (!selectedCaseMap.has(row.case_number)) {
        selectedCaseMap.set(row.case_number, row);
      }
    }

    const selectedCases = [...selectedCaseMap.values()].slice(0, MAX_CASES);
    console.log(`Fetched ${richCases.length} rows, simulating ${selectedCases.length} unique cases`);
    const results = [];

    for (const caseRow of selectedCases) {
      console.log(`\n=== Simulerer case ${caseRow.case_number} (${caseRow.status || 'ukendt status'}) ===`);
      const projectId = await createSimulationProject(databaseService, caseRow);
      try {
        const suggestion = await suggestionService.getOrderSuggestions(projectId, { limit: 5 });
        const summary = summarizeSuggestion(suggestion);
        console.log(JSON.stringify({
          caseNumber: caseRow.case_number,
          summary,
          matches: suggestion.matches.map(match => ({
            source: match.sourceType,
            description: match.description || match.projectDescription || '',
            totalHours: match.totalHours,
            relevance: match.relevanceScore
          }))
        }, null, 2));
        results.push({ caseNumber: caseRow.case_number, summary });
      } catch (error) {
        console.error(`Fejl ved simulering af ${caseRow.case_number}:`, error.message);
      } finally {
        await cleanupSimulationProject(databaseService, projectId);
      }
    }

    console.log('\n--- Simulationsoverblik ---');
    console.log(JSON.stringify(results, null, 2));

    const byRecommendation = results.reduce((acc, entry) => {
      const key = entry.summary.recommendation || 'Ingen pakke';
      acc[key] = (acc[key] || 0) + 1;
      return acc;
    }, {});
    console.log('\n--- Opgavetype-fordeling ---');
    console.log(JSON.stringify(byRecommendation, null, 2));

    const reportDir = path.join(__dirname, '..', 'docs', 'status-reports');
    const reportPath = path.join(reportDir, `ORDRESTYRING_${MAX_CASES}_SIMULATIONS_latest.json`);
    await fs.mkdir(reportDir, { recursive: true });
    await fs.writeFile(
      reportPath,
      JSON.stringify(
        {
          generatedAt: new Date().toISOString(),
          maxCases: MAX_CASES,
          caseCount: results.length,
          recommendationDistribution: byRecommendation,
          cases: results
        },
        null,
        2
      ),
      'utf8'
    );
    console.log(`\nRapport gemt: ${reportPath}`);
  } finally {
    if (ordrestyringConn) {
      await ordrestyringConn.end();
    }
    if (databaseService.pool) {
      await databaseService.pool.end();
    }
  }
}

runSimulation()
  .then(() => process.exit(0))
  .catch(error => {
    console.error('Simuleringsscript fejlede:', error.message);
    process.exit(1);
  });