// ════════════════════════════════════════════════════════════════════════════
// TALENTSPARK · server.js (rewritten May 2026)
// ════════════════════════════════════════════════════════════════════════════
//
// Single-file Node.js server. Runs on Hostinger.
// Requires the new schema.sql to be applied to the MySQL DB.
// Reads all secrets from process.env (see .env.example).
//
// Architecture:
//   - Public client flow: register → OTP → pay → assess → result
//   - School flow:        admin creates school → student registers via slug
//                        → student assesses → coordinator views dashboard
//   - Admin:              header x-admin-key gates all /api/admin/* routes
//
// Assessment flow (per scenario, 6 scenarios × 5 turns = 30 turns total):
//   opener (DB) → fu1 (AI free-form) → fu2 (AI free-form) →
//   probe-choice (AI 3-card forced choice) → probe-why (AI open follow-up)
//
// ════════════════════════════════════════════════════════════════════════════

'use strict';

const express     = require('express');
const cors        = require('cors');
const mysql       = require('mysql2/promise');
const rateLimit   = require('express-rate-limit');
const crypto      = require('crypto');
const path        = require('path');
const fs          = require('fs');

// Load .env if present (dotenv is optional; Hostinger sets env vars natively)
try { require('dotenv').config(); } catch (e) { /* dotenv not installed — OK */ }

const app = express();
const PORT = process.env.PORT || 3000;
const BASE_URL = process.env.BASE_URL || 'https://talentspark.in';
const PRICE_INR_PAISE = parseInt(process.env.PRICE_INR_PAISE || '66600', 10);

// Hostinger runs Node behind a reverse proxy. Trust the first hop so
// express-rate-limit sees real client IPs from X-Forwarded-For.
app.set('trust proxy', 1);

app.use(express.json({ limit: '64kb' }));
app.use(cors());
app.use(express.static(path.join(__dirname, 'public')));


// ── RATE LIMITERS ──────────────────────────────────────────────────────────
const chatLimiter = rateLimit({
  windowMs: 60 * 1000,
  max: 40,
  standardHeaders: true,
  legacyHeaders: false,
  message: { error: 'Too many requests. Please slow down.' }
});

const otpLimiter = rateLimit({
  windowMs: 60 * 60 * 1000, // 1 hour
  max: 10,
  message: { success: false, message: 'Too many OTP requests. Try again in an hour.' }
});

const registerLimiter = rateLimit({
  windowMs: 60 * 1000,
  max: 5,
  message: { error: 'Too many registrations. Please slow down.' }
});


// ── DB POOL ────────────────────────────────────────────────────────────────
let pool;

async function getDB() {
  if (pool) return pool;
  pool = mysql.createPool({
    host:     process.env.DB_HOST || 'localhost',
    port:     parseInt(process.env.DB_PORT || '3306', 10),
    user:     process.env.DB_USER,
    password: process.env.DB_PASS,
    database: process.env.DB_NAME,
    waitForConnections: true,
    connectionLimit: 20,
    queueLimit: 0,
    charset:  'utf8mb4'
  });
  // Test connection
  const conn = await pool.getConnection();
  await conn.ping();
  conn.release();
  console.log('[db] pool ready');
  return pool;
}

// Keep the pool warm; auto-recover if it dies
setInterval(async () => {
  if (!pool) return;
  try {
    const c = await pool.getConnection();
    await c.ping();
    c.release();
  } catch (e) {
    console.error('[db] ping failed — resetting pool:', e.message);
    pool = null;
  }
}, 4 * 60 * 1000);


// ── HELPERS ─────────────────────────────────────────────────────────────────
function shortId(len = 8) {
  return crypto.randomBytes(len).toString('hex').slice(0, len);
}

function ageBandFor(age) {
  const n = parseInt(age, 10) || 10;
  if (n <= 9)        return '8-9';
  else if (n <= 11)  return '10-11';
  else if (n <= 13)  return '12-13';
  else if (n <= 15)  return '14-15';
  else               return '16';
}

function requireAdmin(req, res, next) {
  const key = req.headers['x-admin-key'];
  if (!key || key !== process.env.ADMIN_KEY) {
    return res.status(401).json({ error: 'Unauthorised' });
  }
  next();
}

// Strip code fences from AI responses
function extractJson(text) {
  const cleaned = text
    .replace(/^```json\s*/i, '')
    .replace(/^```\s*/i, '')
    .replace(/```\s*$/i, '')
    .trim();
  const start = cleaned.indexOf('{');
  const end = cleaned.lastIndexOf('}');
  if (start === -1 || end === -1) throw new Error('No JSON in response');
  return JSON.parse(cleaned.substring(start, end + 1));
}

// ── BACKGROUND JOB STORE ────────────────────────────────────────────────────
// Holds in-flight and recently-completed report-generation jobs.
// Keys are jobIds. Values: { status, report?, error?, createdAt, completedAt? }
// Cleaned up after 30 minutes.
const reportJobs = new Map();

// ── CHILD SELF-REPORT STAGING ───────────────────────────────────────────────
// In-memory cache of child self-reports submitted at the start of an assessment,
// before any assessment row exists. Keyed by leadId. Copied into the assessment
// row when save-assessment runs. Cleared after 2 hours to bound memory.
const pendingSelfReports = new Map();

function makeJobId() {
  return 'job_' + Date.now().toString(36) + '_' + crypto.randomBytes(4).toString('hex');
}

setInterval(() => {
  const cutoff = Date.now() - 30 * 60 * 1000;
  for (const [id, job] of reportJobs.entries()) {
    if ((job.completedAt || job.createdAt) < cutoff) reportJobs.delete(id);
  }
  // Self-reports kept for 2 hours
  const srCutoff = Date.now() - 2 * 60 * 60 * 1000;
  for (const [id, entry] of pendingSelfReports.entries()) {
    if (entry.createdAt < srCutoff) pendingSelfReports.delete(id);
  }
}, 5 * 60 * 1000);


// ════════════════════════════════════════════════════════════════════════════
// PAGE ROUTES (serve HTML)
// ════════════════════════════════════════════════════════════════════════════
function servePage(file) {
  return (req, res) => res.sendFile(path.join(__dirname, 'public', file));
}

app.get('/',                  servePage('index.html'));
app.get('/register',          servePage('register.html'));
app.get('/assess',            servePage('assess.html'));
app.get('/result',            servePage('result.html'));
app.get('/result/:shareId',   servePage('result.html'));
app.get('/privacy',           servePage('privacy.html'));
app.get('/privacy-centre',    servePage('privacy-centre.html'));
app.get('/privacy-center',    servePage('privacy-centre.html')); // US spelling alias
app.get('/admin',             servePage('admin.html'));
app.get('/school/:slug',      servePage('school_register.html'));
app.get('/school-dashboard',  servePage('school_dashboard.html'));


// ════════════════════════════════════════════════════════════════════════════
// REGISTRATION  ·  POST /api/register
// Creates a lead row, returns leadId
// ════════════════════════════════════════════════════════════════════════════
app.post('/api/register', registerLimiter, async (req, res) => {
  try {
    const {
      childName, childAge, childDob, childGender,
      schoolName, classGrade, city,
      parentName, parentPhone, parentEmail, source,
      // DPDP consent fields:
      consentAcknowledged, consentTextVersion, parentRelationship
    } = req.body;

    if (!childName || !childAge || !parentName || !parentPhone) {
      return res.status(400).json({ success: false, error: 'Missing required fields' });
    }
    const age = parseInt(childAge, 10);
    if (!age || age < 5 || age > 18) {
      return res.status(400).json({ success: false, error: 'Age must be between 5 and 18' });
    }

    // DPDP: parent must explicitly acknowledge consent before we process child's data.
    if (!consentAcknowledged) {
      return res.status(400).json({
        success: false,
        error: 'Parental consent is required before we can process your child\'s data. Please tick the consent acknowledgement on the registration page.'
      });
    }

    // Capture audit-trail fields. req.ip is trustworthy because app.set('trust proxy', 1).
    const consentIp = req.ip || req.connection?.remoteAddress || null;
    const consentUA = (req.headers['user-agent'] || '').slice(0, 255);
    const relationship = (parentRelationship === 'parent' || parentRelationship === 'legal_guardian') 
                          ? parentRelationship : 'parent_or_guardian';
    const textVersion = (consentTextVersion && String(consentTextVersion).slice(0, 20)) || 'v1-2026-06';

    const db = await getDB();
    const [r] = await db.execute(
      `INSERT INTO leads (child_name, child_age, child_dob, child_gender,
                          school_name, class_grade, city,
                          parent_name, parent_phone, parent_email, source,
                          consent_text_version, consent_ip, consent_user_agent,
                          consent_timestamp, parent_relationship)
       VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),?)`,
      [childName, age, childDob || null, childGender || null,
       schoolName || null, classGrade || null, city || null,
       parentName, parentPhone, parentEmail || null, source || 'direct',
       textVersion, consentIp, consentUA, relationship]
    );

    console.log('[register] new lead:', r.insertId, '| consent:', textVersion, '| ip:', consentIp);
    return res.json({ success: true, leadId: r.insertId });
  } catch (err) {
    console.error('[register]', err.message);
    return res.status(500).json({ success: false, error: 'Registration failed' });
  }
});


// ════════════════════════════════════════════════════════════════════════════
// OTP  ·  POST /api/send-otp  +  POST /api/verify-otp
// Persisted to DB so OTPs survive restart. 10-min expiry. 60s resend cooldown.
// ════════════════════════════════════════════════════════════════════════════
app.post('/api/send-otp', otpLimiter, async (req, res) => {
  try {
    const phone = (req.body.phone || '').toString().replace(/\D/g, '').slice(-10);
    if (phone.length !== 10) {
      return res.json({ success: false, message: 'Invalid phone number' });
    }

    const db = await getDB();

    // 60-second resend cooldown
    const [recent] = await db.execute(
      `SELECT created_at FROM otp_verifications
       WHERE phone = ? AND created_at > DATE_SUB(NOW(), INTERVAL 60 SECOND)
       ORDER BY id DESC LIMIT 1`,
      [phone]
    );
    if (recent.length > 0) {
      const elapsed = Math.floor((Date.now() - new Date(recent[0].created_at).getTime()) / 1000);
      const remaining = Math.max(0, 60 - elapsed);
      return res.json({ success: false, message: `Please wait ${remaining} seconds before requesting a new OTP` });
    }

    const otp = Math.floor(100000 + Math.random() * 900000).toString();
    await db.execute(
      `INSERT INTO otp_verifications (phone, otp_code, expires_at)
       VALUES (?, ?, DATE_ADD(NOW(), INTERVAL 10 MINUTE))`,
      [phone, otp]
    );

    // Send via Fast2SMS
    const fastKey = process.env.FAST2SMS_API_KEY;
    if (!fastKey) {
      console.warn('[otp] FAST2SMS_API_KEY missing — returning debug OTP');
      return res.json({ success: true, message: 'OTP sent (dev mode)', debug_otp: otp });
    }

    const url = 'https://www.fast2sms.com/dev/bulkV2' +
                '?authorization=' + encodeURIComponent(fastKey) +
                '&route=q' +
                '&message=' + encodeURIComponent(otp + ' is your TalentSpark OTP. Valid for 10 minutes.') +
                '&flash=0&numbers=' + phone;

    try {
      const r = await fetch(url);
      const data = await r.json();
      console.log('[otp] sent to', phone, '| Fast2SMS:', JSON.stringify(data).substring(0, 100));
      if (data.return === true) {
        return res.json({ success: true, message: 'OTP sent successfully' });
      }
      return res.json({ success: true, sms_failed: true, message: 'OTP generated', debug_otp: otp });
    } catch (smsErr) {
      console.error('[otp] sms error:', smsErr.message);
      return res.json({ success: true, sms_failed: true, message: 'OTP generated', debug_otp: otp });
    }
  } catch (err) {
    console.error('[send-otp]', err.message);
    return res.status(500).json({ success: false, error: 'OTP send failed' });
  }
});

app.post('/api/verify-otp', async (req, res) => {
  try {
    const phone = (req.body.phone || '').toString().replace(/\D/g, '').slice(-10);
    const otp = (req.body.otp || '').toString().trim();
    const leadId = req.body.leadId ? parseInt(req.body.leadId, 10) : null;

    if (!phone || !otp) {
      return res.json({ success: false, message: 'Missing phone or OTP' });
    }

    const db = await getDB();
    const [rows] = await db.execute(
      `SELECT * FROM otp_verifications
       WHERE phone = ? AND verified = 0 AND expires_at > NOW()
       ORDER BY id DESC LIMIT 1`,
      [phone]
    );

    if (rows.length === 0) {
      return res.json({ success: false, message: 'OTP expired or not sent. Request a new one.' });
    }

    const record = rows[0];

    if (record.attempts >= 5) {
      return res.json({ success: false, message: 'Too many failed attempts. Request a new OTP.' });
    }

    if (record.otp_code !== otp) {
      await db.execute('UPDATE otp_verifications SET attempts = attempts + 1 WHERE id = ?', [record.id]);
      return res.json({ success: false, message: 'Invalid OTP' });
    }

    await db.execute('UPDATE otp_verifications SET verified = 1 WHERE id = ?', [record.id]);

    if (leadId) {
      await db.execute('UPDATE leads SET phone_verified = 1 WHERE id = ?', [leadId]);
    }

    return res.json({ success: true, message: 'Verified' });
  } catch (err) {
    console.error('[verify-otp]', err.message);
    return res.status(500).json({ success: false, error: 'Verification failed' });
  }
});


// ════════════════════════════════════════════════════════════════════════════
// PAYMENT (Razorpay)  ·  POST /api/create-order  +  POST /api/verify-payment
// ════════════════════════════════════════════════════════════════════════════
app.post('/api/create-order', async (req, res) => {
  try {
    const leadId = req.body.leadId ? parseInt(req.body.leadId, 10) : null;

    const keyId = process.env.RAZORPAY_KEY_ID;
    const keySecret = process.env.RAZORPAY_KEY_SECRET;
    if (!keyId || !keySecret) {
      console.error('[razorpay] keys missing');
      return res.status(500).json({ error: 'Payment configuration error' });
    }

    const auth = Buffer.from(keyId + ':' + keySecret).toString('base64');
    const payload = {
      amount: PRICE_INR_PAISE,
      currency: 'INR',
      receipt: 'ts_' + (leadId || Date.now()),
      notes: { leadId: String(leadId || '') }
    };

    const response = await fetch('https://api.razorpay.com/v1/orders', {
      method: 'POST',
      headers: { 'Content-Type': 'application/json', 'Authorization': 'Basic ' + auth },
      body: JSON.stringify(payload)
    });
    const order = await response.json();

    if (!response.ok) {
      console.error('[razorpay] order create failed:', JSON.stringify(order).substring(0, 200));
      return res.status(500).json({ error: (order.error && order.error.description) || 'Order creation failed' });
    }

    // Record the order locally
    try {
      const db = await getDB();
      await db.execute(
        `INSERT INTO payments (lead_id, razorpay_order_id, amount, currency, status)
         VALUES (?, ?, ?, 'INR', 'created')`,
        [leadId, order.id, PRICE_INR_PAISE]
      );
    } catch (dbErr) {
      console.error('[razorpay] payment record write failed:', dbErr.message);
    }

    console.log('[razorpay] order:', order.id, '| lead:', leadId, '| amount:', PRICE_INR_PAISE);
    return res.json({
      success: true,
      orderId: order.id,
      amount: order.amount,
      currency: order.currency,
      keyId: keyId
    });
  } catch (err) {
    console.error('[create-order]', err.message);
    return res.status(500).json({ error: err.message });
  }
});

app.post('/api/verify-payment', async (req, res) => {
  try {
    const { razorpay_order_id, razorpay_payment_id, razorpay_signature, leadId } = req.body;

    if (!razorpay_order_id || !razorpay_payment_id || !razorpay_signature) {
      return res.status(400).json({ success: false, error: 'Missing payment fields' });
    }

    const keySecret = process.env.RAZORPAY_KEY_SECRET;
    const expected = crypto
      .createHmac('sha256', keySecret)
      .update(razorpay_order_id + '|' + razorpay_payment_id)
      .digest('hex');

    if (expected !== razorpay_signature) {
      console.error('[razorpay] signature mismatch — possible tamper attempt');
      return res.status(400).json({ success: false, error: 'Payment verification failed' });
    }

    const db = await getDB();
    await db.execute(
      `UPDATE payments
       SET razorpay_payment_id = ?, razorpay_signature = ?, status = 'paid', paid_at = NOW()
       WHERE razorpay_order_id = ?`,
      [razorpay_payment_id, razorpay_signature, razorpay_order_id]
    );

    if (leadId) {
      await db.execute('UPDATE leads SET paid = 1, payment_id = ? WHERE id = ?',
        [razorpay_payment_id, leadId]);
    }

    console.log('[razorpay] paid:', razorpay_payment_id, '| lead:', leadId);
    return res.json({ success: true, paymentId: razorpay_payment_id });
  } catch (err) {
    console.error('[verify-payment]', err.message);
    return res.status(500).json({ success: false, error: err.message });
  }
});


// ════════════════════════════════════════════════════════════════════════════
// PARENT OBSERVATIONS  ·  POST /api/parent-observations
// Saves the optional structured input from the registration "About child" step.
// Body: { leadId, observations: { ... } | null }
// Sending null/empty observations is valid (parent skipped) — we still return success.
// ════════════════════════════════════════════════════════════════════════════
app.post('/api/parent-observations', async (req, res) => {
  try {
    const leadId = req.body.leadId ? parseInt(req.body.leadId, 10) : null;
    const observations = req.body.observations || null;

    if (!leadId) {
      return res.status(400).json({ success: false, error: 'Missing leadId' });
    }

    // Light validation/sanitisation
    let cleaned = null;
    if (observations && typeof observations === 'object') {
      cleaned = {
        noticed_strengths:   Array.isArray(observations.noticed_strengths) ? observations.noticed_strengths.slice(0, 8).map(s => String(s).slice(0, 60)) : [],
        noticed_loves:       Array.isArray(observations.noticed_loves) ? observations.noticed_loves.slice(0, 8).map(s => String(s).slice(0, 60)) : [],
        noticed_avoids:      Array.isArray(observations.noticed_avoids) ? observations.noticed_avoids.slice(0, 6).map(s => String(s).slice(0, 60)) : [],
        stuck_with_6mo:      Array.isArray(observations.stuck_with_6mo) ? observations.stuck_with_6mo.slice(0, 6).map(s => String(s).slice(0, 60)) : [],
        self_taught:         observations.self_taught ? String(observations.self_taught).slice(0, 500) : '',
        concrete_memory:     observations.concrete_memory ? String(observations.concrete_memory).slice(0, 800) : '',
        signature_strength:  observations.signature_strength ? String(observations.signature_strength).slice(0, 120) : '',
        submitted_at:        observations.submitted_at || new Date().toISOString()
      };
    }

    const db = await getDB();
    await db.execute(
      'UPDATE leads SET parent_observations_json = ? WHERE id = ?',
      [cleaned ? JSON.stringify(cleaned) : null, leadId]
    );

    console.log('[parent-obs] saved for lead:', leadId, '| skipped:', cleaned === null);
    return res.json({ success: true });
  } catch (err) {
    console.error('[parent-obs]', err.message);
    return res.status(500).json({ success: false, error: err.message });
  }
});


// ════════════════════════════════════════════════════════════════════════════
// CHILD SELF-REPORT  ·  POST /api/child-self-report
// Saves the optional structured input from the assessment "About you" step.
// Body: { leadId, selfReport: { ... } | null }
// Stored against the assessment row if it exists, else against the lead row
// as a pending blob (folded in at save-assessment time).
// ════════════════════════════════════════════════════════════════════════════
app.post('/api/child-self-report', async (req, res) => {
  try {
    const leadId = req.body.leadId ? parseInt(req.body.leadId, 10) : null;
    const sr = req.body.selfReport || null;

    if (!leadId) {
      return res.status(400).json({ success: false, error: 'Missing leadId' });
    }

    let cleaned = null;
    if (sr && typeof sr === 'object') {
      cleaned = {
        energy_activities:  Array.isArray(sr.energy_activities) ? sr.energy_activities.slice(0, 8).map(s => String(s).slice(0, 60)) : [],
        unprompted_topics:  Array.isArray(sr.unprompted_topics) ? sr.unprompted_topics.slice(0, 8).map(s => String(s).slice(0, 60)) : [],
        self_taught_skills: Array.isArray(sr.self_taught_skills) ? sr.self_taught_skills.slice(0, 8).map(s => String(s).slice(0, 60)) : [],
        mistake_tolerance:  Array.isArray(sr.mistake_tolerance) ? sr.mistake_tolerance.slice(0, 6).map(s => String(s).slice(0, 60)) : [],
        proud_recent:       sr.proud_recent ? String(sr.proud_recent).slice(0, 500) : '',
        rare_skill:         sr.rare_skill ? String(sr.rare_skill).slice(0, 400) : '',
        wish_better:        sr.wish_better ? String(sr.wish_better).slice(0, 400) : '',
        submitted_at:       sr.submitted_at || new Date().toISOString(),
        age_band_used:      sr.age_band_used || 'unknown'
      };
    }

    // Stash on the lead row as a holding spot. The save-assessment handler
    // copies it into assessments.child_self_report_json when the report saves.
    // (Reusing parent_observations_json column would conflict; we use a custom
    // small field. To avoid yet another schema change, we serialize into the
    // existing parent_observations_json under a nested key only if needed.
    // Simpler: cache in process memory keyed by leadId.)
    if (cleaned) {
      pendingSelfReports.set(leadId, { sr: cleaned, createdAt: Date.now() });
    }

    console.log('[child-sr] cached for lead:', leadId, '| skipped:', cleaned === null);
    return res.json({ success: true });
  } catch (err) {
    console.error('[child-sr]', err.message);
    return res.status(500).json({ success: false, error: err.message });
  }
});


// ════════════════════════════════════════════════════════════════════════════
// PRIVACY REQUEST  ·  POST /api/privacy-request
// Intake for DPDP rights: access / deletion / correction / withdraw_consent /
// grievance. Logs to privacy_requests table and returns a case number.
// Email notification to grievance@talentspark.in is best-effort (handled
// out-of-band; the DB record is the source of truth).
// ════════════════════════════════════════════════════════════════════════════
const privacyLimiter = rateLimit({
  windowMs: 10 * 60 * 1000,
  max: 5,
  message: { success: false, error: 'Too many requests. Try again in 10 minutes.' },
  standardHeaders: true,
  legacyHeaders: false
});

app.post('/api/privacy-request', privacyLimiter, async (req, res) => {
  try {
    const {
      request_type, parent_name, parent_email, parent_phone,
      child_name, child_age, details
    } = req.body;

    // Validation
    const allowedTypes = ['access', 'deletion', 'correction', 'withdraw_consent', 'grievance', 'other'];
    if (!allowedTypes.includes(request_type)) {
      return res.status(400).json({ success: false, error: 'Invalid request type.' });
    }
    if (!parent_name || !parent_email || !details) {
      return res.status(400).json({ success: false, error: 'Name, email, and description are required.' });
    }
    if (String(parent_email).indexOf('@') < 1) {
      return res.status(400).json({ success: false, error: 'Email looks invalid.' });
    }

    const requestIp = req.ip || req.connection?.remoteAddress || null;
    const db = await getDB();
    const [r] = await db.execute(
      `INSERT INTO privacy_requests
        (request_type, parent_name, parent_email, parent_phone,
         child_name, child_age, details, request_ip, status)
       VALUES (?,?,?,?,?,?,?,?, 'open')`,
      [
        request_type,
        String(parent_name).slice(0, 120),
        String(parent_email).slice(0, 160),
        parent_phone ? String(parent_phone).slice(0, 20) : null,
        child_name ? String(child_name).slice(0, 120) : null,
        (child_age !== null && child_age !== undefined && !isNaN(child_age)) ? parseInt(child_age, 10) : null,
        String(details).slice(0, 2000),
        requestIp
      ]
    );

    const caseNumber = 'TS-PR-' + String(r.insertId).padStart(6, '0');
    console.log('[privacy-request] new:', caseNumber, '| type:', request_type, '| email:', parent_email);

    return res.json({ success: true, caseNumber: caseNumber });
  } catch (err) {
    console.error('[privacy-request]', err.message);
    return res.status(500).json({ success: false, error: 'Could not record your request. Please email grievance@talentspark.in directly.' });
  }
});


// ════════════════════════════════════════════════════════════════════════════
// SCENARIO CHAT  ·  POST /api/scenario-chat
// The single AI endpoint. Phases:
//   fetch  → returns scenario data (opener, setup, title, etc.)
//   fu1    → AI free-form follow-up (uses fu1_instruction from DB)
//   fu2    → AI free-form follow-up (uses fu2_instruction from DB)
//   fu3    → probe-choice (3 dimension-tagged option cards)
//   fu4    → probe-why   (one open follow-up about the choice)
//   complete → generates the full report (returns JSON)
// ════════════════════════════════════════════════════════════════════════════
app.post('/api/scenario-chat', chatLimiter, async (req, res) => {
  try {
    const { messages, childName, childAge, scenarioIndex, scenarioPhase, scenarioId, timings, leadId } = req.body;
    const apiKey = process.env.ANTHROPIC_API_KEY;
    if (!apiKey) return res.status(500).json({ error: 'AI service unavailable' });

    if (!childName || !childAge) return res.status(400).json({ error: 'Missing child info' });
    const age = parseInt(childAge, 10) || 10;
    const ageBand = ageBandFor(age);
    const ageIsYoung = age <= 11;
    const childFirstName = (childName || 'the child').split(' ')[0];

    const db = await getDB();

    // ── PHASE: fetch ────────────────────────────────────────────────────────
    if (scenarioPhase === 'fetch') {
      const [rows] = await db.execute(
        `SELECT * FROM scenarios
         WHERE age_band = ? AND active = 1
         ORDER BY band_order ASC LIMIT 6`,
        [ageBand]
      );
      if (rows.length === 0) {
        return res.status(404).json({
          error: 'No scenarios found for age band: ' + ageBand,
          ageBand, age
        });
      }
      const scenario = rows[scenarioIndex];
      if (!scenario) return res.status(404).json({ error: 'Scenario index out of range', scenarioIndex });

      return res.json({
        type: 'scenario',
        scenarioId: scenario.scenario_id,
        scenarioIndex,
        title: scenario.title,
        cluster: scenario.cluster,
        secondaryCluster: scenario.secondary_cluster,
        compoundPair: scenario.compound_pair,
        setup: scenario.setup_text,
        opener: scenario.opener,
        totalScenarios: 6
      });
    }

    // ── PHASE: free-form follow-ups (fu1, fu2) ──────────────────────────────
    if (scenarioPhase === 'fu1' || scenarioPhase === 'fu2') {
      const [scenRows] = await db.execute(
        'SELECT * FROM scenarios WHERE scenario_id = ?', [scenarioId]
      );
      if (scenRows.length === 0) return res.status(404).json({ error: 'Scenario not found' });
      const sc = scenRows[0];
      const fuInstruction = scenarioPhase === 'fu1' ? sc.fu1_instruction : sc.fu2_instruction;

      const currentMessages = (messages || []).slice(-10);

      const sysPrompt = `You are TalentSpark conducting a scenario-based assessment for ${childFirstName}, age ${age}.

YOU ARE CURRENTLY IN THIS SPECIFIC SCENARIO ONLY:
SCENARIO: "${sc.title}"
SETUP: ${sc.setup_text}
CLUSTERS: ${sc.cluster} × ${sc.secondary_cluster}

Ask ONE follow-up question (${scenarioPhase.toUpperCase()}) based on the child's last message.

FOLLOW-UP INSTRUCTION:
${fuInstruction}

RULES:
1. ONE question only — never two questions combined.
2. Respond to the child's EXACT last words.
3. NEVER reference any other scenario — stay inside "${sc.title}".
4. ${ageIsYoung ? `Simple warm language for age ${age}. Short sentences.` : `Appropriate depth for age ${age}.`}
5. No judgment, no implied right answer.
6. Respond ONLY with valid JSON: {"type":"followup","phase":"${scenarioPhase}","question":"your question here"}`;

      const r = await fetch('https://api.anthropic.com/v1/messages', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json', 'x-api-key': apiKey, 'anthropic-version': '2023-06-01' },
        body: JSON.stringify({
          model: 'claude-sonnet-4-6',
          max_tokens: 400,
          system: sysPrompt,
          messages: currentMessages
        })
      });
      const responseText = await r.text();
      if (!r.ok) {
        console.error('[fu] api error:', responseText.substring(0, 200));
        return res.status(500).json({ error: 'AI error' });
      }
      const data = JSON.parse(responseText);
      try {
        const parsed = extractJson(data.content[0].text);
        return res.json(parsed);
      } catch (e) {
        return res.status(500).json({ error: 'Invalid response format' });
      }
    }

    // ── PHASE: fu3 — PROBE-CHOICE ───────────────────────────────────────────
    if (scenarioPhase === 'fu3') {
      const [scenRows] = await db.execute(
        'SELECT * FROM scenarios WHERE scenario_id = ?', [scenarioId]
      );
      if (scenRows.length === 0) return res.status(404).json({ error: 'Scenario not found' });
      const sc = scenRows[0];

      const validDims = ['building', 'leading', 'creating', 'analysing', 'helping', 'performing'];
      const currentMessages = (messages || []).slice(-10);

      const sysPrompt = `You are TalentSpark conducting a scenario-based assessment for ${childFirstName}, age ${age}.

YOU ARE INSIDE THIS SCENARIO:
SCENARIO: "${sc.title}"
SETUP: ${sc.setup_text}
CLUSTERS: ${sc.cluster} × ${sc.secondary_cluster}

The child has answered fu1 and fu2. Now write a SINGLE in-story moment that
offers them THREE concrete actions to take next inside this scenario.

CRITICAL FRAMING:
- The question reads like the next sentence of the story, not a quiz.
- The three options must be three DIFFERENT VERBS — three different ways to act.
- Each option is one short line a child of ${age} would say or do (8–14 words).
- NEVER reference "career", "interest", "skill", "preference", or "what you like".
  This is an action choice INSIDE THE STORY.
- The options must be genuinely different in dimension — not three flavours of the same choice.

DIMENSION TAGGING (mandatory, invisible to the child):
Each option tagged with ONE dimension from: ${validDims.join(' | ')}
- building   = make, fix, construct, design something physical or systemic
- leading    = take charge, decide for others, set direction, speak first
- creating   = invent, imagine, perform, express, tell a story
- analysing  = study, investigate, figure out, gather information, watch
- helping    = comfort, support, mediate, share with someone struggling
- performing = present, demonstrate, show, perform for an audience

Choose 3 dimensions that FIT NATURALLY inside "${sc.title}". Don't force all six.

${ageIsYoung
  ? `LANGUAGE: simple, warm, concrete for age ${age}.`
  : `LANGUAGE: appropriate depth for age ${age}. Concrete actions, not abstract values.`}

RESPOND ONLY WITH VALID JSON in this exact format:
{
  "type": "probe",
  "phase": "fu3",
  "question": "The in-story setup sentence leading into the choice.",
  "choices": [
    {"id": "a", "label": "Short action (8–14 words)", "dimension": "one of: ${validDims.join('|')}"},
    {"id": "b", "label": "A genuinely different action",     "dimension": "one of: ${validDims.join('|')}"},
    {"id": "c", "label": "A third action — different again",  "dimension": "one of: ${validDims.join('|')}"}
  ]
}`;

      const r = await fetch('https://api.anthropic.com/v1/messages', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json', 'x-api-key': apiKey, 'anthropic-version': '2023-06-01' },
        body: JSON.stringify({
          model: 'claude-sonnet-4-6',
          max_tokens: 600,
          system: sysPrompt,
          messages: currentMessages
        })
      });
      const responseText = await r.text();
      if (!r.ok) {
        console.error('[probe-choice] api error:', responseText.substring(0, 200));
        return res.status(500).json({ error: 'Probe generation failed' });
      }
      const data = JSON.parse(responseText);
      try {
        const parsed = extractJson(data.content[0].text);
        const ok =
          parsed.type === 'probe' &&
          typeof parsed.question === 'string' && parsed.question.length > 0 &&
          Array.isArray(parsed.choices) && parsed.choices.length >= 2 && parsed.choices.length <= 4 &&
          parsed.choices.every(c =>
            c && typeof c.label === 'string' && c.label.length > 0 &&
            validDims.indexOf(c.dimension) !== -1
          );
        if (!ok) return res.status(500).json({ error: 'Invalid probe structure' });

        parsed.choices = parsed.choices.map((c, i) => ({
          id: c.id || String.fromCharCode(97 + i),
          label: c.label,
          dimension: c.dimension
        }));

        console.log('[probe-choice]', sc.scenario_id, '| dims:', parsed.choices.map(c => c.dimension).join(','));
        return res.json(parsed);
      } catch (e) {
        console.error('[probe-choice] parse fail:', e.message);
        return res.status(500).json({ error: 'Probe parse failed' });
      }
    }

    // ── PHASE: fu4 — PROBE-WHY ──────────────────────────────────────────────
    if (scenarioPhase === 'fu4') {
      const [scenRows] = await db.execute(
        'SELECT * FROM scenarios WHERE scenario_id = ?', [scenarioId]
      );
      if (scenRows.length === 0) return res.status(404).json({ error: 'Scenario not found' });
      const sc = scenRows[0];

      const currentMessages = (messages || []).slice(-10);

      const sysPrompt = `You are TalentSpark conducting a scenario-based assessment for ${childFirstName}, age ${age}.

YOU ARE INSIDE THIS SCENARIO:
SCENARIO: "${sc.title}"
SETUP: ${sc.setup_text}

The child just made an in-story choice between three options. Their last
message is the option they chose. Read it.

Ask ONE question that invites them to explain THE REASON for their choice
in their own words.

RULES:
1. Reference the SPECIFIC thing they chose — quote or describe it back.
2. Phrase it as natural curiosity, not a quiz. Examples (don't copy verbatim):
   - "What made you decide to ___ instead of the other things?"
   - "Walk me through what you were thinking when you picked ___."
   - "What was going through your head when you chose ___?"
3. Stay inside the scenario world.
4. NEVER use: "interest", "preference", "skill", "talent", "what you like".
5. ONE question only.
6. ${ageIsYoung ? `Simple warm language for age ${age}.` : `Appropriate depth for age ${age}.`}

RESPOND ONLY WITH VALID JSON:
{"type":"followup","phase":"fu4","question":"your question here"}`;

      const r = await fetch('https://api.anthropic.com/v1/messages', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json', 'x-api-key': apiKey, 'anthropic-version': '2023-06-01' },
        body: JSON.stringify({
          model: 'claude-sonnet-4-6',
          max_tokens: 400,
          system: sysPrompt,
          messages: currentMessages
        })
      });
      const responseText = await r.text();
      if (!r.ok) {
        console.error('[probe-why] api error:', responseText.substring(0, 200));
        return res.status(500).json({ error: 'Probe-why generation failed' });
      }
      const data = JSON.parse(responseText);
      try {
        const parsed = extractJson(data.content[0].text);
        if (parsed.type !== 'followup' || !parsed.question) {
          return res.status(500).json({ error: 'Invalid probe-why structure' });
        }
        return res.json(parsed);
      } catch (e) {
        return res.status(500).json({ error: 'Probe-why parse failed' });
      }
    }

    // ── PHASE: complete — kick off background report generation ─────────────
    if (scenarioPhase === 'complete') {
      // Build response-time summary from client timings
      let avgSec = null;
      if (timings && Array.isArray(timings) && timings.length > 0) {
        const sum = timings.reduce((s, t) => s + (Number(t) || 0), 0);
        avgSec = Math.round((sum / timings.length) * 10) / 10;
      }
      const tempoNote = (avgSec !== null) ? `\n[avg_response_seconds: ${avgSec}]` : '';

      // Fetch parent observations from the lead row, if present
      let parentObservations = null;
      if (leadId) {
        try {
          const db = await getDB();
          const [rows] = await db.execute(
            'SELECT parent_observations_json FROM leads WHERE id = ?',
            [parseInt(leadId, 10)]
          );
          if (rows.length > 0 && rows[0].parent_observations_json) {
            const raw = rows[0].parent_observations_json;
            parentObservations = (typeof raw === 'string') ? JSON.parse(raw) : raw;
          }
        } catch (e) {
          console.warn('[report] could not load parent observations:', e.message);
        }
      }

      const reportFormat = buildReportFormatJson(childFirstName, age);
      const sysPrompt = buildReportSystemPrompt(childFirstName, age, reportFormat, !!parentObservations);

      const augMessages = (messages || []).slice();

      // Inject parent observations as a system-context message AT THE TOP
      // (the child self-report is already there, prepended by the client)
      if (parentObservations) {
        augMessages.unshift({
          role: 'user',
          content: '[PARENT OBSERVATIONS — completed by the parent at registration]\n' +
                   JSON.stringify(parentObservations, null, 2)
        });
      }

      if (tempoNote) {
        augMessages.push({
          role: 'user',
          content: '[response-timing-context]' + tempoNote
        });
      }

      // Create job, return jobId immediately, work in background
      const jobId = makeJobId();
      reportJobs.set(jobId, { status: 'pending', createdAt: Date.now(), childName, childAge: age, avgSec });

      // Background work (not awaited — fire and forget)
      (async () => {
        try {
          const r = await fetch('https://api.anthropic.com/v1/messages', {
            method: 'POST',
            headers: { 'Content-Type': 'application/json', 'x-api-key': apiKey, 'anthropic-version': '2023-06-01' },
            body: JSON.stringify({
              model: 'claude-sonnet-4-20250514',
              max_tokens: parseInt(process.env.REPORT_MAX_TOKENS || '10000', 10),
              temperature: parseFloat(process.env.REPORT_TEMPERATURE || '0.5'),
              system: sysPrompt,
              messages: augMessages
            })
          });
          const responseText = await r.text();
          if (!r.ok) {
            console.error('[report] api error:', responseText.substring(0, 400));
            reportJobs.set(jobId, {
              status: 'error',
              error: 'AI service error: ' + responseText.substring(0, 200),
              createdAt: Date.now(),
              completedAt: Date.now()
            });
            return;
          }
          const data = JSON.parse(responseText);
          const parsed = extractJson(data.content[0].text);
          parsed.type = 'result';
          parsed.childName = childName;
          parsed.childAge = age;
          parsed.assessmentMeta = parsed.assessmentMeta || {};
          parsed.assessmentMeta.avgResponseSeconds = avgSec;
          console.log('[report] generated for', childName, '| clusters:', (parsed.talentClusters || []).length, '| job:', jobId);

          reportJobs.set(jobId, {
            status: 'done',
            report: parsed,
            createdAt: reportJobs.get(jobId).createdAt,
            completedAt: Date.now(),
            childName, childAge: age, avgSec
          });
        } catch (err) {
          console.error('[report] background job failed:', err.message);
          reportJobs.set(jobId, {
            status: 'error',
            error: err.message || 'Unknown error',
            createdAt: reportJobs.get(jobId).createdAt,
            completedAt: Date.now()
          });
        }
      })();

      console.log('[report] job started:', jobId, 'for', childName);
      return res.json({ type: 'job', jobId, status: 'pending' });
    }

    return res.status(400).json({ error: 'Invalid scenario phase: ' + scenarioPhase });
  } catch (err) {
    console.error('[scenario-chat]', err.message);
    return res.status(500).json({ error: err.message });
  }
});

// ── POLLING: GET /api/report-status/:jobId ─────────────────────────────────
// Client polls every 3s after kicking off a 'complete' phase.
// Returns: {status:'pending'} | {status:'done', report:{...}} | {status:'error', error:'...'}
app.get('/api/report-status/:jobId', (req, res) => {
  const jobId = req.params.jobId;
  const job = reportJobs.get(jobId);
  if (!job) {
    return res.status(404).json({ status: 'not_found', error: 'Job not found or expired' });
  }
  if (job.status === 'pending') {
    return res.json({ status: 'pending' });
  }
  if (job.status === 'error') {
    return res.json({ status: 'error', error: job.error });
  }
  if (job.status === 'done') {
    return res.json({ status: 'done', report: job.report });
  }
  return res.status(500).json({ status: 'unknown', error: 'Unexpected job state' });
});


// ── Report Prompt Builders (separated for readability) ─────────────────────
function buildReportFormatJson(childFirstName, age) {
  return `{
  "type": "result",
  "assessmentMeta": {
    "date": "e.g. May 2026",
    "scenarioCount": 6,
    "questionMoments": 30,
    "reportId": "TS-${new Date().getFullYear()}-${shortId(6).toUpperCase()}"
  },
  "whatTheyBring": {
    "framingNote": "What ${childFirstName} brings to the table — drawn from what they told us and what their parent observes. This is the surface layer: what they enjoy, what they're good at, what they're working on. The reasoning analysis below is the depth layer.",
    "demonstratedStrengths": ["Strength — short evidence-anchored description (e.g. 'Drawing — taught themselves manga style over the last year (parent obs)')", "Another strength similar format", "Up to 4 total. Each must reference whether it came from child self-report, parent observations, or both."],
    "enjoyedActivities":      ["Activity — short description (e.g. 'Building with Lego — chose this unprompted as a free-time activity (child self-report)')", "Another", "Up to 4 total."],
    "uniqueHints":            "1–2 sentences. The most distinctive item across both inputs — the thing that, taken together with the reasoning profile, hints at something unusual. Skip if nothing distinctive surfaced.",
    "agreementZones":         ["Where parent observation and child self-report agree (e.g. 'Both flagged music as a stuck-with-it interest')", "Up to 3."],
    "disagreementZones":      ["Where they disagree — the most useful zone (e.g. 'Parent didn't mention coding, but child reports teaching themselves Python')", "Up to 3. Skip section if no meaningful gaps."],
    "dataAvailability":       "One of: 'Parent and child both provided input' | 'Only parent provided input' | 'Only child provided input' | 'Neither provided input — analysis below is purely from reasoning observation.'"
  },
  "childSummary": "THREE paragraphs separated by double newline. Paragraph 1: the most striking thing about how this child moves through the world — specific. Paragraph 2: how their mind works under pressure — what the fu2 complications revealed. Paragraph 3: what is still forming — honest, specific. Every sentence must reference a scenario moment or their actual words.",
  "inOneSentence": "One sentence. The single most accurate observation about this child. Specific enough that the parent reads it and thinks 'that is exactly right'.",
  "superpowerName": "2–4 words. A NAME for their superpower. Earned, not generic.",
  "superpower": "2–3 sentences. What this child sees or does that others don't. Specific to their scenario responses.",
  "superpowerProvenance": "Derived from [top 2–3 clusters] — consistent across [N] of 6 scenarios.",
  "threeActions": [
    {"action": "Verb-led action title", "detail": "2–3 sentences specific to this child — references something from the assessment."},
    {"action": "Second action title", "detail": "2–3 sentences specific to this child."},
    {"action": "Third action title", "detail": "2–3 sentences specific to this child."}
  ],
  "talentClusters": [
    {
      "name": "e.g. Ethical Intelligence",
      "emoji": "single emoji",
      "matchScore": 85,
      "patternName": "2–3 word pattern label",
      "subTraits": ["trait 1", "trait 2", "trait 3", "trait 4"],
      "description": "THREE paragraphs. P1: what this child actually does in this dimension. P2: the most revealing data point — name the scenario, quote their words, explain what it reveals. P3: the shadow side — where this strength becomes a limitation.",
      "ownWords": "[Scenario title, Follow-up N]: their words here",
      "crossScenarioPattern": "[Different scenario title, Follow-up N]: their words here",
      "coreStrengths": ["Evidence pattern 1 — one-line scenario reference", "Pattern 2", "Pattern 3", "Pattern 4"],
      "careerPaths": ["path 1", "path 2", "path 3", "path 4", "path 5"],
      "confidenceNote": "ONLY if matchScore < 70: 'Pattern emerging — only [N] primary scenarios measured this. Reassess at age [X].'"
    }
  ],
  "capabilityIndicators": {
    "framingNote": "These are six things ${childFirstName} demonstrated in the assessment. Each one is observed in actual responses — not inferred from a quiz. Confidence reflects how consistently the pattern appeared across the 30 question moments.",
    "indicators": [
      {
        "name": "Reasoning Depth",
        "score": 75,
        "level": "Strong | Above average | Balanced | Developing",
        "evidence": "Multi-step thinking visible in N of 6 scenarios. Example: in [Scenario], when asked X, ${childFirstName} reasoned A → B → C."
      },
      {
        "name": "Pattern Recognition",
        "score": 70,
        "level": "Strong | Above average | Balanced | Developing",
        "evidence": "Named the underlying tension in N of 6 scenarios before being prompted. Example with quote."
      },
      {
        "name": "Self-Awareness",
        "score": 80,
        "level": "Exceptional | Strong | Above average | Developing",
        "evidence": "Used reflective self-reference in N of 6 scenarios. Quote one unprompted moment."
      },
      {
        "name": "Decision Tempo",
        "score": null,
        "level": "Deliberate | Balanced | Quick",
        "evidence": "Average response time was N seconds. ${childFirstName} ___. (Use the timing context provided.)"
      },
      {
        "name": "Verbal Precision",
        "score": 70,
        "level": "Strong | Above average | Balanced | Developing",
        "evidence": "Specific feeling-words and concrete nouns; low filler ratio. Quote one precise phrase."
      },
      {
        "name": "Moral Reasoning Stage",
        "score": null,
        "level": "Stage 2 (instrumental) | Stage 3 (interpersonal) | Stage 4 (social-system) | Stage 5 (principled) | Stage 3-4 transition",
        "evidence": "Across ethical scenarios (S1, S5, S6), ${childFirstName} reasoned at [stage]. Quote one phrase. Per Kohlberg."
      }
    ]
  },
  "thinkingStyleLabel": "2–3 words",
  "thinkingStyle": "2 sentences. HOW they reason — what is their mental sequence? Reference a scenario moment.",
  "learningStyleLabel": "2–3 words",
  "learningStyle": "2 sentences. How they engage with information across scenarios.",
  "emotionalIntelligence": "3 sentences. What they demonstrated, what scenario showed it, what is not yet developed.",
  "leadershipStyleLabel": "2–4 words",
  "leadershipStyle": "2 sentences. What they actually did when influence was possible.",
  "personalityAttributes": ["Attribute — one-line scenario evidence", "Attribute — evidence", "Attribute — evidence", "Attribute — evidence", "Attribute — evidence"],
  "characteristics": ["characteristic 1", "characteristic 2", "characteristic 3"],
  "growthEdge": "One paragraph. The single most important developmental opportunity. What did the fu2 complications reveal?",
  "watchOut": "One direct sentence. The most honest limitation. Not softened.",
  "careerFamilies": ["Family 1", "Family 2", "Family 3", "Family 4", "Family 5", "Family 6"],
  "roadmap": [
    {"phase": "Now (Age ${age}–${age + 2})", "actions": ["Action 1", "Action 2", "Action 3", "Action 4"]},
    {"phase": "Next (Age ${age + 2}–${age + 5})", "actions": ["Action 1", "Action 2", "Action 3", "Action 4"]},
    {"phase": "Future (Age ${age + 5}+)", "actions": ["Action 1", "Action 2", "Action 3"]}
  ],
  "recommendedCourses": [
    {"name": "Course name", "platform": "Platform", "ageRange": "age range", "description": "Why this fits this child — reference their cluster profile."}
  ],
  "universities": {
    "india":  ["University — Program — Why it fits", "...", "...", "...", "...", "..."],
    "global": ["University — Country — Why it fits", "...", "...", "...", "...", "..."]
  },
  "careerOutlook": {
    "topRoles": ["Role 1", "Role 2", "Role 3", "Role 4", "Role 5"],
    "salaryRange": "Realistic INR range entry to mid-career",
    "industryGrowth": "Growth outlook 1–2 sentences with % if known",
    "whyThisChild": "Why this cluster combination suits these careers. Reference specific patterns. End with: Career families derived from cluster intersection — reasoning-style matching, not interest matching."
  },
  "realityCheck": {
    "framingNote": "How does the reasoning profile compare with what ${childFirstName} and their parent told us about strengths and interests? This is where ${childFirstName}'s outer self and inner self either align — or interestingly don't.",
    "alignments": ["A specific match. E.g. 'Parent flagged strong ethical sense; ethical reasoning scored 88% — these match.' Anchor each to specific evidence.", "Up to 3 alignments. Each one a real, evidence-anchored match. Skip if no meaningful matches surface."],
    "tensions":   ["A specific gap that opens a useful conversation. E.g. 'Parent doesn't mention coding, but child reports teaching themselves Python AND reasoning showed strong systems thinking — worth exploring.' Each gap must be useful, not just contradictory.", "Up to 3 tensions. Skip section if no useful gaps surface."],
    "hiddenSignals": "ONE paragraph. The most interesting thing that emerged ONLY because we had both the reasoning analysis AND the stated talents/interests — something neither source would have revealed alone. Skip with 'Both sources told the same story.' if nothing distinctive emerges.",
    "honestCaveat": "If only one source (parent OR child) provided input, or neither did, name that here in one line. E.g. 'Only ${childFirstName}'s own input was available — parent observations would have added another lens.' Otherwise: skip this field or set to null."
  },
  "parentNote": "Letter format. 'To ${childFirstName}'s parent,' — then 3–4 paragraphs. P1: most important thing to know about this child. P2: what their child will find hard. P3: one thing the parent may be doing that is not serving this child — specific to the profile. End with exactly: Three things to do in the next 30 days: 1. [specific] 2. [specific] 3. [specific]",
  "methodologyNote": "${childFirstName} completed 6 compound scenarios across 6 intelligence dimensions — each designed to surface 2 cluster readings simultaneously. Each scenario produced 1 fixed opener + 2 AI-adaptive reasoning follow-ups + 1 in-story forced choice + 1 open follow-up explaining that choice, for 30 total question moments. Scenarios are adapted from validated public-domain instruments including Kohlberg Moral Reasoning, PISA Financial Literacy, Mayer-Salovey-Caruso EI, and CASEL socio-emotional. Confidence scores reflect response consistency across scenarios measuring the same cluster. Capability Indicators are observed measures derived from ${childFirstName}'s actual responses. Career families come from cluster intersection — reasoning-style matching, not stated interests. The What They Bring layer combines optional structured input from the child (self-report) and the parent (observations) — when provided."
}`;
}

function buildReportSystemPrompt(childFirstName, age, reportFormat, hasParentObservations) {
  return `You are TalentSpark generating a Holistic Intelligence Profile for ${childFirstName}, age ${age}.

The child completed 6 compound scenarios × 5 questions = 30 data points. The full Q&A is in the messages.
Cluster pairs: Financial × Ethical (S1), Social × Emotional (S2), Creative × Physical (S3),
Financial × Social (S4), Ethical × Emotional (S5), Creative × Ethical (S6).

OPTIONAL CONTEXT BLOCKS may appear at the start of the message stream:
- [CHILD SELF-REPORT — ...]: structured input from the child (energy, interests, skills, mistakes-they-tolerate, proud-recent, rare-skill, wish-better). Filled at assessment start.
- [PARENT OBSERVATIONS — ...]: structured input from the parent (strengths, loves, avoids, stuck-with, self-taught, concrete-memory, signature-strength). Filled at registration.

These are SURFACE-LEVEL inputs — stated talents and interests. The 30-data-point scenario analysis is the DEPTH-LEVEL signal. Always treat the scenarios as primary evidence. Treat the context blocks as anchor points to validate against, contradict, or extend.

THE STANDARD YOU ARE WRITING TO:
This report costs ₹666. The parent must feel they underpaid. Every sentence must earn its place.
A parent should read this and think: "How do they know my child this well from a 30-minute test?"
The answer is: because every claim is anchored to something their child specifically said or did in a specific scenario.

ANTI-BARNUM STANDARD — apply to every sentence:
Before writing any sentence ask: "Could this appear unchanged in 60% of reports?" If yes — delete it.
Replace with: which scenario, what the child said, what it reveals.

BANNED PHRASES (apply to almost every child — forbidden):
"processes deeply" / "fair-minded" / "strong sense of justice" / "questions rules" / "private emotional world" / "not the loudest" / "empathetic and caring" / "thinks before acting" / "creative problem-solver" / "natural leader" / "deep thinker" / "sensitive soul" / "sees the big picture" / "unique perspective"

═══════════════════════════════════════════════════════════════════════
LAYER 0 — "whatTheyBring" — SURFACE-LEVEL STATED STRENGTHS AND INTERESTS
═══════════════════════════════════════════════════════════════════════
This is a NEW first layer of the report. It sits at the TOP. It is concrete and observational, not analytical. Its job is to make the parent feel: "yes, that's what we see in our home." It precedes — and prepares the ground for — the deeper reasoning analysis.

RULES:
1. demonstratedStrengths and enjoyedActivities are FACTUAL, not interpretive. They are drawn directly from what the child and parent told us. Do not synthesise here; quote what was said.
2. Each item MUST be tagged with its source: (child self-report) / (parent obs) / (both). If both sources mention the same thing, label it (both) — that's a strong signal worth flagging.
3. SKIP empty arrays. If parent said no strengths and child gave none, do not invent. Output [] and let the dataAvailability field carry the meaning.
4. uniqueHints is ONE sentence about the most distinctive item — distinctive means: something not generic, not "likes reading" or "is creative", but specific (e.g. "taught self origami advanced fold patterns from YouTube — sustained 9 months").
5. agreementZones and disagreementZones are about CROSS-VALIDATION: where do the two sources agree vs disagree? Disagreements are often more useful than agreements — flag the most useful ones.
6. dataAvailability is honest: state exactly which sources we received.
${hasParentObservations
  ? '7. Parent observations ARE present in this assessment. Use them.'
  : '7. Parent observations were NOT provided for this assessment. Set dataAvailability accordingly and skip agreementZones/disagreementZones if only child input is present.'}

LAYER 0 IS NOT ANALYSIS. Save the interpretation for the reasoning section and the Reality Check.

═══════════════════════════════════════════════════════════════════════
REALITY CHECK — alignment of scenario analysis with stated talents/interests
═══════════════════════════════════════════════════════════════════════
Comes near the END of the report, after the cluster analysis and career outlook. Its job is to surface the gap or alignment between WHAT THE CHILD/PARENT TOLD US and WHAT THE SCENARIOS REVEALED.

RULES:
1. alignments: where scenario reasoning matches stated talent or interest. Each one MUST cite both — what the scenario showed AND what was stated. Example: "Parent flagged 'strong sense of fairness' as the signature strength. Ethical cluster scored 88% across S1, S5, S6 with consistent principle-anchored reasoning. These match."
2. tensions: useful gaps. Where do the scenarios suggest something different from what was stated? OR where does the child reveal something the parent didn't notice? These are HIGH-VALUE moments. Be careful — only flag gaps that open a USEFUL conversation, not contradictory ones that are simply different (e.g. "parent didn't mention coding" is useful if scenarios showed systems thinking; not useful as a standalone gripe).
3. hiddenSignals: ONE paragraph. The most interesting thing that emerged ONLY because we had BOTH sources AND the reasoning analysis. Example: "Child mentioned wishing they were better at public speaking. The scenarios showed strong verbal precision but consistent withdrawal in social-pressure moments (S2 fu2, S4 fu2). Combined: the verbal capacity exists; the social safety doesn't. Practice with low-stakes audiences first." This insight is impossible from any one source alone.
4. honestCaveat: ONLY include if a source is missing. Otherwise skip the field. Examples: "Only the parent provided input — ${childFirstName}'s own perspective on energy and interest would have added another lens." OR "Neither source provided input — this section reflects scenario analysis only."
5. If ALL sources agree perfectly: hiddenSignals can be "Both sources told the same story — and the scenarios confirmed it." Do not invent tension where none exists.

REALITY CHECK IS THE MOST POWERFUL SECTION FOR PARENTS. It is where they go: "I never saw it like that." Write it with care.

═══════════════════════════════════════════════════════════════════════

CLUSTER DESCRIPTION STRUCTURE (mandatory — 3 paragraphs per cluster):
Paragraph 1: What this child actually does in this dimension. Specific verb. Specific pattern. No flattery.
Paragraph 2: The most revealing data point. Name the scenario. Quote or paraphrase their exact words.
Paragraph 3: The shadow side. Where does this strength become a limitation? Honest.

OWN WORDS (mandatory — TWO quotes per cluster):
ownWords: from one specific scenario, with Follow-up number.
crossScenarioPattern: from a DIFFERENT scenario showing the same pattern confirmed.

CONFIDENCE SCORING:
80–95%: pattern consistent across 3+ scenarios measuring this cluster.
60–79%: consistent across 2 scenarios.
Below 60%: emerging — MUST include confidenceNote.
Physical Intelligence (S3 only): will score 55–65% — flag explicitly with confidenceNote.

SUPERPOWER:
superpowerName must be a NAMED thing — "The Fairness Compass", "The Bridge Builder", "The Quiet Force".
Something the parent will frame and put on a wall.

CAPABILITY INDICATORS (mandatory — 6 observed measures, in this exact order):

1. Reasoning Depth — score 0–100. Multi-step reasoning ("because", "but also", "if X then Y"). Evidence quotes a specific multi-step moment.
2. Pattern Recognition — score 0–100. How often the child named the underlying tension BEFORE fu2 surfaced it.
3. Self-Awareness — score 0–100. First-person reflective language ("I would feel", "I usually"). Quote one unprompted moment.
4. Decision Tempo — score null. Level is "Deliberate" if avg > 60s, "Balanced" 30–60s, "Quick" < 30s. Use the timing context provided in the last user message.
5. Verbal Precision — score 0–100. Specific nouns and named emotions vs. fillers. Quote one precise phrase.
6. Moral Reasoning Stage — score null. Apply Kohlberg to ethical-cluster responses (S1, S5, S6). Stage 2/3/4/5 or transitions. Quote one phrase that demonstrates the stage.

CAPABILITY-INDICATOR RULES:
- Every evidence string MUST reference a specific scenario and quote/paraphrase the child's actual words.
- Level labels must match the schema's allowed values exactly.
- If you can't honestly justify a score, score it as 50 and write evidence as: "Limited data — N moments observed. Pattern is too early to call confidently."

PARENT NOTE:
Write as a letter. "To ${childFirstName}'s parent," — then speak directly to them.
Reference at least 2 specific things their child said, with scenario names.
Include one honest "what NOT to do" based on their profile.
If parent observations were provided AND there's a meaningful tension with the reasoning profile, the parent note is the right place to address it directly but gently.
The parent should feel you understand their child better than their child's school does.

CAREER FAMILIES:
careerFamilies is a top-level array of 5–6 career family names.
Derived from cluster intersection — not stated interests.
Financial × Ethical × Social → Law, Policy, Public Service, Social Enterprise
Creative × Ethical × Emotional → Design, Education, Counselling, Arts
Financial × Social × Creative → Business, Marketing, Entrepreneurship
Ethical × Emotional × Social → Medicine, Psychology, NGO, Governance
Creative × Physical × Financial → Architecture, Engineering, Product Design

LANGUAGE: Plain English. No clinical language. No "socio-emotional domain". No "metacognitive awareness".

RESPOND ONLY WITH VALID JSON. No markdown. No backticks. Start { end }.

${reportFormat}`;
}


// ════════════════════════════════════════════════════════════════════════════
// SAVE ASSESSMENT / RESULT
// ════════════════════════════════════════════════════════════════════════════
// Shared handler — both /api/save-assessment and /api/save-result use this
async function handleSaveAssessment(req, res) {
  try {
    const { leadId, childName, childAge, reportJson, conversationJson,
            scenarioChoicesJson, totalTurns, avgResponseSeconds, childSelfReport } = req.body;

    if (!childName || !childAge || !reportJson) {
      return res.status(400).json({ success: false, error: 'Missing required fields' });
    }

    // Resolve child self-report: prefer the one in the request, fall back to the
    // staged one from the assessment-start step. This handles both paths cleanly.
    let resolvedSelfReport = childSelfReport;
    if (!resolvedSelfReport && leadId && pendingSelfReports.has(leadId)) {
      resolvedSelfReport = pendingSelfReports.get(leadId).sr;
      pendingSelfReports.delete(leadId);  // consume it
    }

    const shareId = shortId(8);
    const db = await getDB();

    await db.execute(
      `INSERT INTO assessments
       (lead_id, share_id, child_name, child_age, report_json, conversation_json,
        scenario_choices_json, total_turns, avg_response_seconds, child_self_report_json)
       VALUES (?,?,?,?,?,?,?,?,?,?)`,
      [
        leadId || null,
        shareId,
        childName,
        parseInt(childAge, 10) || 10,
        typeof reportJson === 'string' ? reportJson : JSON.stringify(reportJson),
        typeof conversationJson === 'string' ? conversationJson : JSON.stringify(conversationJson || []),
        typeof scenarioChoicesJson === 'string' ? scenarioChoicesJson : JSON.stringify(scenarioChoicesJson || []),
        parseInt(totalTurns || 30, 10),
        avgResponseSeconds || null,
        resolvedSelfReport ? JSON.stringify(resolvedSelfReport) : null
      ]
    );

    return res.json({ success: true, shareId, shareUrl: `${BASE_URL}/result/${shareId}` });
  } catch (err) {
    console.error('[save-assessment]', err.message);
    return res.status(500).json({ success: false, error: 'Save failed' });
  }
}

app.post('/api/save-assessment', handleSaveAssessment);
app.post('/api/save-result',     handleSaveAssessment); // legacy alias


// ════════════════════════════════════════════════════════════════════════════
// RESULT FETCH
// ════════════════════════════════════════════════════════════════════════════
app.get('/api/result/:shareId', async (req, res) => {
  try {
    const shareId = req.params.shareId;
    const db = await getDB();
    const [rows] = await db.execute(
      'SELECT child_name, child_age, report_json, completed_at FROM assessments WHERE share_id = ? AND deleted_at IS NULL LIMIT 1',
      [shareId]
    );
    if (rows.length === 0) return res.status(404).json({ error: 'Result not found' });

    let report;
    try {
      report = JSON.parse(rows[0].report_json);
    } catch (e) {
      return res.status(500).json({ error: 'Corrupt report data' });
    }

    return res.json({
      success: true,
      shareId,
      childName: rows[0].child_name,
      childAge: rows[0].child_age,
      completedAt: rows[0].completed_at,
      report
    });
  } catch (err) {
    console.error('[result fetch]', err.message);
    return res.status(500).json({ error: err.message });
  }
});


// ════════════════════════════════════════════════════════════════════════════
// REVIEWS
// ════════════════════════════════════════════════════════════════════════════
app.post('/api/review', async (req, res) => {
  try {
    const { shareId, childName, childAge, parentName, city, rating, reviewText } = req.body;
    if (!rating || rating < 1 || rating > 5) {
      return res.status(400).json({ success: false, error: 'Rating must be 1–5' });
    }
    const db = await getDB();
    await db.execute(
      `INSERT INTO reviews (share_id, child_name, child_age, parent_name, city, rating, review_text)
       VALUES (?,?,?,?,?,?,?)`,
      [shareId || null, childName || null, childAge || null,
       parentName || null, city || null, rating,
       (reviewText || '').substring(0, 500)]
    );
    return res.json({ success: true });
  } catch (err) {
    console.error('[review]', err.message);
    return res.status(500).json({ success: false, error: 'Review save failed' });
  }
});


// ════════════════════════════════════════════════════════════════════════════
// TRACKING (lightweight analytics)
// ════════════════════════════════════════════════════════════════════════════
app.post('/api/track', async (req, res) => {
  try {
    const { eventType, page, referrer, metadata } = req.body;
    const ua = (req.headers['user-agent'] || '').substring(0, 500);
    const ip = (req.headers['x-forwarded-for'] || req.ip || '').toString().substring(0, 50);
    const db = await getDB();
    await db.execute(
      `INSERT INTO page_events (event_type, page, referrer, user_agent, ip, metadata)
       VALUES (?,?,?,?,?,?)`,
      [eventType || 'pageview', (page || '').substring(0, 100),
       (referrer || '').substring(0, 500), ua, ip,
       metadata ? JSON.stringify(metadata).substring(0, 500) : null]
    );
    return res.json({ ok: true });
  } catch (err) {
    return res.json({ ok: false }); // never break the client over tracking
  }
});


// ════════════════════════════════════════════════════════════════════════════
// SCHOOL FLOW
// ════════════════════════════════════════════════════════════════════════════

// Get school by slug — public, used by /school/:slug page
app.get('/api/school/:slug', async (req, res) => {
  try {
    const db = await getDB();
    const [rows] = await db.execute(
      `SELECT id, name, slug, city, student_quota, students_used, active
       FROM schools WHERE slug = ? AND active = 1 LIMIT 1`,
      [req.params.slug]
    );
    if (rows.length === 0) return res.json({ success: false, error: 'School not found' });

    const s = rows[0];
    return res.json({
      success: true,
      school: {
        id: s.id, name: s.name, slug: s.slug, city: s.city,
        quota: s.student_quota, used: s.students_used,
        remaining: Math.max(0, s.student_quota - s.students_used)
      }
    });
  } catch (err) {
    return res.status(500).json({ success: false, error: err.message });
  }
});

// Student registers for school assessment
app.post('/api/school/register-student', async (req, res) => {
  try {
    const { schoolId, studentName, classGrade, section, age, gender, rollNumber } = req.body;
    if (!schoolId || !studentName || !age) {
      return res.status(400).json({ success: false, error: 'Missing required fields' });
    }
    const ageNum = parseInt(age, 10);
    if (!ageNum || ageNum < 5 || ageNum > 18) {
      return res.status(400).json({ success: false, error: 'Invalid age' });
    }

    const db = await getDB();

    // Check quota
    const [sch] = await db.execute(
      'SELECT student_quota, students_used FROM schools WHERE id = ? AND active = 1 LIMIT 1',
      [schoolId]
    );
    if (sch.length === 0) return res.status(404).json({ success: false, error: 'School not found' });
    if (sch[0].students_used >= sch[0].student_quota) {
      return res.status(403).json({ success: false, error: 'School quota full' });
    }

    const [r] = await db.execute(
      `INSERT INTO school_students
       (school_id, student_name, class_grade, section, age, gender, roll_number, status, started_at)
       VALUES (?,?,?,?,?,?,?,'in_progress', NOW())`,
      [schoolId, studentName, classGrade || null, section || null,
       ageNum, gender || null, rollNumber || null]
    );

    await db.execute('UPDATE schools SET students_used = students_used + 1 WHERE id = ?', [schoolId]);

    return res.json({ success: true, studentId: r.insertId });
  } catch (err) {
    console.error('[school register-student]', err.message);
    if (err.code === 'ER_DUP_ENTRY') {
      return res.status(409).json({ success: false, error: 'Student with this roll number already registered' });
    }
    return res.status(500).json({ success: false, error: 'Registration failed' });
  }
});

// Update student progress
app.post('/api/school/student-progress', async (req, res) => {
  try {
    const { studentId, status } = req.body;
    if (!studentId || !status) return res.status(400).json({ success: false });
    const validStatus = ['registered', 'in_progress', 'completed', 'abandoned'];
    if (!validStatus.includes(status)) return res.status(400).json({ success: false });
    const db = await getDB();
    await db.execute('UPDATE school_students SET status = ? WHERE id = ?', [status, studentId]);
    return res.json({ success: true });
  } catch (err) {
    return res.status(500).json({ success: false });
  }
});

// Save student's result (links the school_students row to an assessment shareId)
app.post('/api/school/save-result', async (req, res) => {
  try {
    const { studentId, shareId } = req.body;
    if (!studentId || !shareId) return res.status(400).json({ success: false });
    const db = await getDB();
    await db.execute(
      `UPDATE school_students
       SET share_id = ?, status = 'completed', completed_at = NOW()
       WHERE id = ?`,
      [shareId, studentId]
    );
    return res.json({ success: true });
  } catch (err) {
    return res.status(500).json({ success: false });
  }
});

// Coordinator dashboard — gated by access_key
app.get('/api/school/dashboard', async (req, res) => {
  try {
    const key = req.query.key;
    if (!key) return res.status(400).json({ success: false, error: 'Missing key' });
    const db = await getDB();

    const [sch] = await db.execute(
      `SELECT id, name, slug, city, student_quota, students_used, active
       FROM schools WHERE access_key = ? LIMIT 1`,
      [key]
    );
    if (sch.length === 0) return res.status(401).json({ success: false, error: 'Invalid access key' });

    const school = sch[0];

    const [students] = await db.execute(
      `SELECT id, student_name, class_grade, section, age, gender, roll_number,
              share_id, status, started_at, completed_at, created_at
       FROM school_students
       WHERE school_id = ?
       ORDER BY created_at DESC`,
      [school.id]
    );

    const counts = students.reduce((acc, s) => {
      acc.total++;
      if (s.status === 'completed')    acc.completed++;
      else if (s.status === 'in_progress') acc.inProgress++;
      else if (s.status === 'abandoned')   acc.abandoned++;
      else acc.registered++;
      return acc;
    }, { total: 0, completed: 0, inProgress: 0, abandoned: 0, registered: 0 });

    return res.json({
      success: true,
      school: {
        id: school.id, name: school.name, slug: school.slug, city: school.city,
        quota: school.student_quota, used: school.students_used,
        remaining: Math.max(0, school.student_quota - school.students_used)
      },
      students,
      stats: counts
    });
  } catch (err) {
    console.error('[school dashboard]', err.message);
    return res.status(500).json({ success: false, error: err.message });
  }
});


// ════════════════════════════════════════════════════════════════════════════
// ADMIN ROUTES (all gated by x-admin-key header)
// ════════════════════════════════════════════════════════════════════════════

app.get('/api/admin/analytics', requireAdmin, async (req, res) => {
  try {
    const db = await getDB();
    const [leads]        = await db.execute('SELECT COUNT(*) AS n FROM leads');
    const [paidLeads]    = await db.execute('SELECT COUNT(*) AS n FROM leads WHERE paid = 1');
    const [assessments]  = await db.execute('SELECT COUNT(*) AS n FROM assessments');
    const [reviews]      = await db.execute('SELECT COUNT(*) AS n FROM reviews');
    const [pendingRev]   = await db.execute('SELECT COUNT(*) AS n FROM reviews WHERE approved = 0');
    const [schools]      = await db.execute('SELECT COUNT(*) AS n FROM schools WHERE active = 1');
    const [students]     = await db.execute('SELECT COUNT(*) AS n FROM school_students');
    const [studentsDone] = await db.execute('SELECT COUNT(*) AS n FROM school_students WHERE status = "completed"');
    const [last7Leads]   = await db.execute('SELECT COUNT(*) AS n FROM leads WHERE created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)');
    const [last7Asmts]   = await db.execute('SELECT COUNT(*) AS n FROM assessments WHERE completed_at > DATE_SUB(NOW(), INTERVAL 7 DAY)');

    return res.json({
      success: true,
      totals: {
        leads:           leads[0].n,
        paidLeads:       paidLeads[0].n,
        assessments:     assessments[0].n,
        reviews:         reviews[0].n,
        pendingReviews:  pendingRev[0].n,
        schools:         schools[0].n,
        schoolStudents:  students[0].n,
        schoolCompleted: studentsDone[0].n
      },
      last7Days: {
        leads:       last7Leads[0].n,
        assessments: last7Asmts[0].n
      }
    });
  } catch (err) {
    return res.status(500).json({ success: false, error: err.message });
  }
});

app.get('/api/admin/leads', requireAdmin, async (req, res) => {
  try {
    const db = await getDB();
    const [rows] = await db.execute(
      `SELECT id, child_name, child_age, parent_name, parent_phone, parent_email,
              city, school_name, paid, phone_verified, source, created_at
       FROM leads ORDER BY created_at DESC LIMIT 500`
    );
    return res.json({ success: true, leads: rows });
  } catch (err) {
    return res.status(500).json({ success: false, error: err.message });
  }
});

app.get('/api/admin/results', requireAdmin, async (req, res) => {
  try {
    const db = await getDB();
    const [rows] = await db.execute(
      `SELECT id, share_id, child_name, child_age, total_turns, avg_response_seconds, completed_at
       FROM assessments ORDER BY completed_at DESC LIMIT 500`
    );
    return res.json({ success: true, results: rows });
  } catch (err) {
    return res.status(500).json({ success: false, error: err.message });
  }
});

app.get('/api/admin/reviews', requireAdmin, async (req, res) => {
  try {
    const db = await getDB();
    const [rows] = await db.execute(
      `SELECT id, share_id, child_name, child_age, parent_name, city,
              rating, review_text, approved, created_at
       FROM reviews ORDER BY created_at DESC LIMIT 500`
    );
    return res.json({ success: true, reviews: rows });
  } catch (err) {
    return res.status(500).json({ success: false, error: err.message });
  }
});

app.post('/api/admin/review/:id/approve', requireAdmin, async (req, res) => {
  try {
    const db = await getDB();
    await db.execute('UPDATE reviews SET approved = 1 WHERE id = ?', [req.params.id]);
    return res.json({ success: true });
  } catch (err) {
    return res.status(500).json({ success: false, error: err.message });
  }
});

app.delete('/api/admin/review/:id', requireAdmin, async (req, res) => {
  try {
    const db = await getDB();
    await db.execute('DELETE FROM reviews WHERE id = ?', [req.params.id]);
    return res.json({ success: true });
  } catch (err) {
    return res.status(500).json({ success: false, error: err.message });
  }
});

app.post('/api/admin/create-school', requireAdmin, async (req, res) => {
  try {
    const { name, slug, city, coordinatorName, coordinatorEmail, coordinatorPhone, studentQuota } = req.body;
    if (!name || !slug) {
      return res.status(400).json({ success: false, error: 'Missing name or slug' });
    }
    const accessKey = crypto.randomBytes(24).toString('hex');
    const db = await getDB();
    const [r] = await db.execute(
      `INSERT INTO schools (name, slug, city, coordinator_name, coordinator_email,
                            coordinator_phone, access_key, student_quota)
       VALUES (?,?,?,?,?,?,?,?)`,
      [name, slug.toLowerCase().replace(/[^a-z0-9-]/g, '-'),
       city || null, coordinatorName || null, coordinatorEmail || null,
       coordinatorPhone || null, accessKey, parseInt(studentQuota || 30, 10)]
    );
    return res.json({
      success: true,
      schoolId: r.insertId,
      accessKey,
      registerUrl:  `${BASE_URL}/school/${slug}`,
      dashboardUrl: `${BASE_URL}/school-dashboard?key=${accessKey}`
    });
  } catch (err) {
    if (err.code === 'ER_DUP_ENTRY') {
      return res.status(409).json({ success: false, error: 'Slug or access key already exists' });
    }
    return res.status(500).json({ success: false, error: err.message });
  }
});

app.get('/api/admin/schools', requireAdmin, async (req, res) => {
  try {
    const db = await getDB();
    const [rows] = await db.execute(
      `SELECT id, name, slug, city, coordinator_name, coordinator_email,
              coordinator_phone, access_key, student_quota, students_used, active, created_at
       FROM schools ORDER BY created_at DESC`
    );
    return res.json({ success: true, schools: rows });
  } catch (err) {
    return res.status(500).json({ success: false, error: err.message });
  }
});

app.delete('/api/admin/schools/:id', requireAdmin, async (req, res) => {
  try {
    const db = await getDB();
    await db.execute('UPDATE schools SET active = 0 WHERE id = ?', [req.params.id]);
    return res.json({ success: true });
  } catch (err) {
    return res.status(500).json({ success: false, error: err.message });
  }
});

app.get('/api/admin/scenarios', requireAdmin, async (req, res) => {
  try {
    const db = await getDB();
    const [rows] = await db.execute(
      `SELECT scenario_id, title, cluster, secondary_cluster, compound_pair,
              age_band, age_min, age_max, band_order, active, created_at
       FROM scenarios ORDER BY age_band, band_order`
    );
    return res.json({ success: true, scenarios: rows });
  } catch (err) {
    return res.status(500).json({ success: false, error: err.message });
  }
});


// ════════════════════════════════════════════════════════════════════════════
// HEALTH CHECK
// ════════════════════════════════════════════════════════════════════════════
app.get('/health', async (req, res) => {
  try {
    const db = await getDB();
    const [r] = await db.execute('SELECT 1 AS ok');
    return res.json({ status: 'ok', db: r[0].ok === 1 });
  } catch (err) {
    return res.status(500).json({ status: 'error', error: err.message });
  }
});


// ════════════════════════════════════════════════════════════════════════════
// 404 catch-all
// ════════════════════════════════════════════════════════════════════════════
app.use((req, res) => {
  if (req.path.startsWith('/api/')) {
    return res.status(404).json({ error: 'Endpoint not found' });
  }
  return res.status(404).sendFile(path.join(__dirname, 'public', 'index.html'));
});


// ════════════════════════════════════════════════════════════════════════════
// START
// ════════════════════════════════════════════════════════════════════════════
(async () => {
  try {
    await getDB();
    app.listen(PORT, () => {
      console.log(`[talentspark] listening on :${PORT}`);
      console.log(`[talentspark] base URL: ${BASE_URL}`);
      console.log(`[talentspark] price: ${PRICE_INR_PAISE} paise = ₹${PRICE_INR_PAISE / 100}`);
    });
  } catch (err) {
    console.error('[startup] DB connection failed:', err.message);
    process.exit(1);
  }
})();