'use strict';
const express = require('express');
const router = express.Router();
const { requireAuth } = require('../middleware/auth');
const pool = require('../db/index');

// GET /api/onboarding/progress
router.get('/progress', requireAuth, async (req, res) => {
  try {
    const userId = req.user.id;

    // Check add_connection
    const connResult = await pool.query(
      'SELECT COUNT(*) as cnt FROM oracle_connections WHERE user_id = :p1',
      { p1: userId }
    );
    const hasConnection = parseInt(connResult.rows[0].cnt) > 0;

    // Check run_health_check
    const hcResult = await pool.query(
      `SELECT COUNT(*) as cnt FROM health_checks hc
       JOIN oracle_connections oc ON hc.connection_id = oc.id
       WHERE oc.user_id = :p1 AND hc.status = 'completed'`,
      { p1: userId }
    );
    const hasHealthCheck = parseInt(hcResult.rows[0].cnt) > 0;

    // Check try_tunebot — check analytics_events for tunebot_query
    const tunebotResult = await pool.query(
      `SELECT COUNT(*) as cnt FROM analytics_events
       WHERE user_id = :p1 AND event_name = 'tunebot_query'`,
      { p1: userId }
    );
    const hasTunebot = parseInt(tunebotResult.rows[0].cnt) > 0;

    // try_dba_agent — check analytics_events for dba_agent_query or agent_query
    const agentResult = await pool.query(
      `SELECT COUNT(*) as cnt FROM analytics_events
       WHERE user_id = :p1 AND event_name IN ('dba_agent_query','agent_started')`,
      { p1: userId }
    );
    const hasDbaAgent = parseInt(agentResult.rows[0].cnt) > 0;

    // check_security_score — check if user has viewed security page
    const securityResult = await pool.query(
      `SELECT COUNT(*) as cnt FROM analytics_events
       WHERE user_id = :p1 AND event_name = 'page_view' AND JSON_VALUE(properties, '$.page') = 'security'`,
      { p1: userId }
    );
    const hasSecurityScore = parseInt(securityResult.rows[0].cnt) > 0;

    // download_report — check analytics_events for report_downloaded or pdf_downloaded
    const reportResult = await pool.query(
      `SELECT COUNT(*) as cnt FROM analytics_events
       WHERE user_id = :p1 AND event_name IN ('report_downloaded','pdf_downloaded','health_check_pdf_downloaded')`,
      { p1: userId }
    );
    const hasDownloadedReport = parseInt(reportResult.rows[0].cnt) > 0;

    // Check setup_monitoring — check connection_schedules
    const scheduleResult = await pool.query(
      `SELECT COUNT(*) as cnt FROM connection_schedules cs
       JOIN oracle_connections oc ON cs.connection_id = oc.id
       WHERE oc.user_id = :p1 AND cs.enabled = TRUE`,
      { p1: userId }
    );
    const hasMonitoring = parseInt(scheduleResult.rows[0].cnt) > 0;

    // Check dismissed — user_preferences is keyed by user_id (unique), with an
    // onboarding_dismissed boolean column (added via ensureColumns()).
    const dismissResult = await pool.query(
      `SELECT onboarding_dismissed FROM user_preferences WHERE user_id = :p1`,
      { p1: userId }
    ).catch(() => ({ rows: [] }));
    const dismissed = dismissResult.rows.length > 0 && dismissResult.rows[0].onboarding_dismissed === true;

    if (dismissed) {
      return res.json({ dismissed: true });
    }

    res.json({
      add_connection: hasConnection,
      run_health_check: hasHealthCheck,
      try_dba_agent: hasDbaAgent,
      check_security_score: hasSecurityScore,
      try_tunebot: hasTunebot,
      download_report: hasDownloadedReport,
      setup_monitoring: hasMonitoring,
    });
  } catch(e) {
    console.error('[onboarding] progress error:', e);
    res.json({});
  }
});

// POST /api/onboarding/dismiss
router.post('/dismiss', requireAuth, async (req, res) => {
  try {
    await pool.query(
      `MERGE INTO user_preferences t
         USING (SELECT :p1 AS user_id FROM DUAL) s
         ON (t.user_id = s.user_id)
       WHEN MATCHED THEN UPDATE SET onboarding_dismissed = TRUE, updated_at = CURRENT_TIMESTAMP
       WHEN NOT MATCHED THEN INSERT (user_id, onboarding_dismissed, updated_at)
         VALUES (:p1, TRUE, CURRENT_TIMESTAMP)`,
      { p1: req.user.id }
    );
    res.json({ ok: true });
  } catch(e) {
    console.error('[onboarding] dismiss error:', e);
    res.json({ ok: false });
  }
});

module.exports = router;
