'use strict';

const installV33MenuAndActivityRoutes = require('./v33-menu-controls-routes');

function finiteOrNull(value) {
  if (value === null || value === undefined || value === '') return null;
  const number = Number(value);
  return Number.isFinite(number) ? number : null;
}

function cleanText(value, max = 300, fallback = '') {
  const text = String(value ?? fallback).trim();
  return text.slice(0, max);
}

function cleanJsonArray(value) {
  return Array.isArray(value) ? value : [];
}

function rowForClient(row) {
  const details = row.details && typeof row.details === 'object' ? row.details : {};
  return {
    submission_id: `curriculum-${row.id}`,
    curriculum_submission_id: row.id,
    assignment_id: row.assignment_key,
    assignment_key: row.assignment_key,
    client_submission_key: row.client_submission_key,
    student_id: row.student_id,
    assignment_title: row.assignment_title,
    title: row.assignment_title,
    course_name: row.course_name,
    subject: row.subject,
    unit_title: row.unit_title,
    lesson_title: row.lesson_title,
    assignment_type: row.assignment_type,
    assignment_weight_category: row.weight_category,
    weight_category: row.weight_category,
    status: row.status,
    submission_status: row.status,
    percentage: row.percentage === null ? null : Number(row.percentage),
    letter_grade: row.letter_grade,
    points_earned: row.points_earned === null ? null : Number(row.points_earned),
    points_possible: row.points_possible === null ? null : Number(row.points_possible),
    point_value: row.point_value === null ? null : Number(row.point_value),
    counts_toward_grade: row.counts_toward_grade,
    needs_review: row.needs_review,
    auto_graded: row.auto_graded === true,
    writing_score: row.writing_score === null || row.writing_score === undefined ? null : Number(row.writing_score),
    concern_flag: row.concern_flag === true,
    concern_reason: row.concern_reason || '',
    time_spent_seconds: row.time_spent_seconds,
    language_key: row.language_key,
    local_grade6_elective_kind: row.elective_kind,
    submission_date: row.submitted_at,
    submittedAt: row.submitted_at,
    graded_date: row.graded_at,
    gradedAt: row.graded_at,
    updated_at: row.updated_at,
    questions: cleanJsonArray(details.questions),
    answers: cleanJsonArray(details.answers),
    results: cleanJsonArray(details.results),
    written_response: cleanText(details.written_response, 20000),
    feedback: cleanText(details.feedback, 10000),
    parsed_submission: {
      questions: cleanJsonArray(details.questions),
      answers: cleanJsonArray(details.answers),
      results: cleanJsonArray(details.results),
      written_response: cleanText(details.written_response, 20000),
      feedback: cleanText(details.feedback, 10000)
    },
    remote_curriculum_submission: true,
    local_grade6_spelling: /^g6sp-/i.test(row.assignment_key),
    local_grade6_math: /^g6-math-/i.test(row.assignment_key),
    local_grade6_science: /^g6-(science|sci)-/i.test(row.assignment_key),
    local_grade6_social: /^g6-(social|ss)-/i.test(row.assignment_key),
    local_grade6_language: /^g6-lang-/i.test(row.assignment_key),
    local_grade6_elective: /^g6-(art|music|pe|health|tech)-/i.test(row.assignment_key),
    is_local_grade6_row: true
  };
}

function parseSubmissionId(value) {
  const match = String(value || '').match(/^(?:curriculum-)?(\d+)$/);
  return match ? Number(match[1]) : null;
}

function canManageProgramContent(req) {
  const role = String(req.user?.role || req.user?.type || req.user?.user_type || '').toLowerCase();
  return role === 'parent' || role.includes('admin');
}

async function canAccessStudent(req, pool, studentId) {
  const user = req.user || {};
  const role = String(user.role || user.type || user.user_type || '').toLowerCase();
  const userId = Number(user.id || user.user_id || user.parent_id || 0);
  const tokenStudentId = Number(user.student_id || user.student?.id || (role === 'student' ? user.id : 0));
  if (role.includes('admin')) return true;
  if (role === 'student') return tokenStudentId === studentId;
  if ((role === 'parent' || role === '') && userId > 0) {
    const candidates = ['parent_id', 'parent_user_id', 'user_id', 'account_id'];
    const columns = await pool.query(
      `SELECT column_name FROM information_schema.columns
       WHERE table_schema='public' AND table_name='students' AND column_name = ANY($1::text[])`,
      [candidates]
    );
    const ownerColumn = candidates.find(name => columns.rows.some(row => row.column_name === name));
    if (ownerColumn) {
      const owned = await pool.query(`SELECT 1 FROM students WHERE id=$1 AND ${ownerColumn}=$2 LIMIT 1`, [studentId, userId]);
      return owned.rowCount > 0;
    }
    const linkTable = await pool.query(`SELECT to_regclass('public.parent_student_links') AS name`);
    if (linkTable.rows[0]?.name) {
      const linked = await pool.query('SELECT 1 FROM parent_student_links WHERE parent_user_id=$1 AND student_id=$2 LIMIT 1', [userId, studentId]);
      return linked.rowCount > 0;
    }
  }
  return false;
}

module.exports = function installV34LaunchRoutes(app, pool, authenticateToken) {
  if (!app || !pool) throw new Error('V34 routes require the Express app and PostgreSQL pool.');
  if (typeof authenticateToken !== 'function') throw new Error('V34 routes require the existing authenticateToken middleware.');

  // V34 is the single installer. Do not register V33 separately.
  installV33MenuAndActivityRoutes(app, pool, authenticateToken);
  const protect = authenticateToken;

  app.get('/api/v34/status', protect, async (req, res) => {
    try {
      const result = await pool.query(`SELECT
        to_regclass('public.student_menu_controls') IS NOT NULL AS menu_ready,
        to_regclass('public.student_activity_sessions') IS NOT NULL AS attendance_ready,
        to_regclass('public.curriculum_submissions') IS NOT NULL AS submissions_ready,
        to_regclass('public.student_curriculum_settings') IS NOT NULL AS settings_ready,
        to_regclass('public.program_announcements') IS NOT NULL AS announcements_ready`);
      const row = result.rows[0] || {};
      return res.json({ version: 'V34', ready: !!(row.menu_ready && row.attendance_ready && row.submissions_ready && row.settings_ready && row.announcements_ready), ...row });
    } catch (error) {
      console.error('V34 status failed:', error);
      return res.status(500).json({ error: 'V34 database readiness check failed' });
    }
  });

  app.get('/api/v34/announcements', protect, async (req, res) => {
    try {
      let result = await pool.query(
        `SELECT id, title, audience, status, display_date AS date, body, updated_at
         FROM program_announcements ORDER BY updated_at DESC, id`
      );
      const autoGraded = body.auto_graded === true;
      const writingScore = finiteOrNull(body.writing_score);
      if (autoGraded || writingScore !== null) {
        await pool.query(
          `UPDATE curriculum_submissions
           SET auto_graded=$2, writing_score=$3, needs_review=FALSE, updated_at=NOW()
           WHERE id=$1`,
          [result.rows[0].id, autoGraded, writingScore]
        );
        const recentWriting = await pool.query(
          `SELECT id, writing_score FROM curriculum_submissions
           WHERE student_id=$1 AND auto_graded=TRUE AND writing_score IS NOT NULL
           ORDER BY submitted_at DESC, id DESC LIMIT 3`,
          [studentId]
        );
        const repeatedConcern = recentWriting.rowCount === 3 && recentWriting.rows.every(row => Number(row.writing_score) < 70);
        await pool.query(
          `UPDATE curriculum_submissions SET concern_flag=FALSE, concern_reason='' WHERE student_id=$1 AND concern_flag=TRUE`,
          [studentId]
        );
        if (repeatedConcern) {
          await pool.query(
            `UPDATE curriculum_submissions
             SET concern_flag=TRUE,
                 concern_reason='Three recent automatically graded writing responses are below 70%.'
             WHERE id=$1`,
            [recentWriting.rows[0].id]
          );
        }
        result = await pool.query('SELECT * FROM curriculum_submissions WHERE id=$1', [result.rows[0].id]);
      }
      return res.json({ announcements: result.rows });
    } catch (error) {
      console.error('V34 announcements load failed:', error);
      return res.status(500).json({ error: 'Unable to load announcements' });
    }
  });

  app.put('/api/v34/announcements', protect, async (req, res) => {
    if (!canManageProgramContent(req)) return res.status(403).json({ error: 'Only a parent or administrator can change announcements' });
    const source = Array.isArray(req.body?.announcements) ? req.body.announcements.slice(0, 100) : [];
    const announcements = source.map((item, index) => ({
      id: cleanText(item?.id, 120, `announcement-${Date.now()}-${index}`).replace(/\s+/g, '-'),
      title: cleanText(item?.title, 300),
      audience: cleanText(item?.audience, 120, 'All Students'),
      status: String(item?.status || '').toLowerCase() === 'published' ? 'Published' : 'Draft',
      date: cleanText(item?.date, 80),
      body: cleanText(item?.body, 10000)
    })).filter(item => item.id && item.title && item.body);
    const client = typeof pool.connect === 'function' ? await pool.connect() : pool;
    try {
      await client.query('BEGIN');
      await client.query('DELETE FROM program_announcements');
      for (const item of announcements) {
        await client.query(
          `INSERT INTO program_announcements (id, title, audience, status, display_date, body, updated_at)
           VALUES ($1,$2,$3,$4,$5,$6,NOW())`,
          [item.id, item.title, item.audience, item.status, item.date, item.body]
        );
      }
      await client.query('COMMIT');
      return res.json({ announcements });
    } catch (error) {
      try { await client.query('ROLLBACK'); } catch {}
      console.error('V34 announcements save failed:', error);
      return res.status(500).json({ error: 'Unable to save announcements' });
    } finally {
      if (client !== pool && typeof client.release === 'function') client.release();
    }
  });

  app.get('/api/students/:studentId/curriculum-settings', protect, async (req, res) => {
    try {
      const studentId = Number(req.params.studentId);
      if (!Number.isInteger(studentId) || studentId <= 0) return res.status(400).json({ error: 'Invalid student ID' });
      if (!(await canAccessStudent(req, pool, studentId))) return res.status(403).json({ error: 'Student record access denied' });
      const result = await pool.query('SELECT foreign_language_key, updated_at FROM student_curriculum_settings WHERE student_id=$1', [studentId]);
      return res.json({ student_id: studentId, foreign_language_key: result.rows[0]?.foreign_language_key || 'spanish', updated_at: result.rows[0]?.updated_at || null });
    } catch (error) {
      console.error('V34 curriculum settings load failed:', error);
      return res.status(500).json({ error: 'Unable to load curriculum settings' });
    }
  });

  app.put('/api/students/:studentId/curriculum-settings', protect, async (req, res) => {
    try {
      const studentId = Number(req.params.studentId);
      if (!Number.isInteger(studentId) || studentId <= 0) return res.status(400).json({ error: 'Invalid student ID' });
      if (!(await canAccessStudent(req, pool, studentId))) return res.status(403).json({ error: 'Student record access denied' });
      const languageKey = cleanText(req.body?.foreign_language_key, 20).toLowerCase();
      if (!['spanish','french','portuguese'].includes(languageKey)) return res.status(400).json({ error: 'Choose Spanish, French, or Portuguese' });
      const result = await pool.query(
        `INSERT INTO student_curriculum_settings (student_id, foreign_language_key, updated_at)
         VALUES ($1,$2,NOW()) ON CONFLICT (student_id) DO UPDATE
         SET foreign_language_key=EXCLUDED.foreign_language_key, updated_at=NOW()
         RETURNING foreign_language_key, updated_at`,
        [studentId, languageKey]
      );
      return res.json({ student_id: studentId, ...result.rows[0] });
    } catch (error) {
      console.error('V34 curriculum settings save failed:', error);
      return res.status(500).json({ error: 'Unable to save curriculum settings' });
    }
  });

  app.get('/api/students/:studentId/curriculum-submissions', protect, async (req, res) => {
    try {
      const studentId = Number(req.params.studentId);
      if (!Number.isInteger(studentId) || studentId <= 0) return res.status(400).json({ error: 'Invalid student ID' });
      if (!(await canAccessStudent(req, pool, studentId))) return res.status(403).json({ error: 'Student record access denied' });
      const result = await pool.query(
        `SELECT * FROM curriculum_submissions
         WHERE student_id = $1
         ORDER BY submitted_at DESC, id DESC`,
        [studentId]
      );
      return res.json({ submissions: result.rows.map(rowForClient) });
    } catch (error) {
      console.error('V34 curriculum submission list failed:', error);
      return res.status(500).json({ error: 'Unable to load curriculum submissions' });
    }
  });

  app.get('/api/students/:studentId/curriculum-submissions/:submissionId', protect, async (req, res) => {
    try {
      const studentId = Number(req.params.studentId);
      const submissionId = parseSubmissionId(req.params.submissionId);
      if (!Number.isInteger(studentId) || studentId <= 0 || !submissionId) return res.status(400).json({ error: 'Invalid submission request' });
      if (!(await canAccessStudent(req, pool, studentId))) return res.status(403).json({ error: 'Student record access denied' });
      const result = await pool.query(
        'SELECT * FROM curriculum_submissions WHERE id = $1 AND student_id = $2',
        [submissionId, studentId]
      );
      if (!result.rows[0]) return res.status(404).json({ error: 'Submission not found' });
      return res.json(rowForClient(result.rows[0]));
    } catch (error) {
      console.error('V34 curriculum submission detail failed:', error);
      return res.status(500).json({ error: 'Unable to load the curriculum submission' });
    }
  });

  app.post('/api/students/:studentId/curriculum-submissions', protect, async (req, res) => {
    try {
      const studentId = Number(req.params.studentId);
      if (!Number.isInteger(studentId) || studentId <= 0) return res.status(400).json({ error: 'Invalid student ID' });
      if (!(await canAccessStudent(req, pool, studentId))) return res.status(403).json({ error: 'Student record access denied' });
      const body = req.body && typeof req.body === 'object' ? req.body : {};
      const assignmentKey = cleanText(body.assignment_id || body.assignment_key, 180);
      const submittedAtValue = new Date(body.submission_date || body.submittedAt || Date.now());
      const submittedAt = Number.isNaN(submittedAtValue.getTime()) ? new Date() : submittedAtValue;
      const clientKey = cleanText(body.client_submission_key || `${assignmentKey}:${submittedAt.toISOString()}`, 260);
      if (!assignmentKey || !clientKey) return res.status(400).json({ error: 'Assignment and submission keys are required' });

      const status = cleanText(body.status || (body.needs_review ? 'submitted' : 'completed'), 40, 'completed');
      const percentage = finiteOrNull(body.percentage);
      const pointsEarned = finiteOrNull(body.points_earned);
      const pointsPossible = finiteOrNull(body.points_possible);
      const pointValue = finiteOrNull(body.point_value ?? pointsPossible);
      const details = {
        questions: cleanJsonArray(body.questions),
        answers: cleanJsonArray(body.answers),
        results: cleanJsonArray(body.results),
        written_response: cleanText(body.written_response || body.writtenResponse, 20000),
        feedback: cleanText(body.feedback, 10000)
      };
      const gradedAtValue = body.gradedAt || body.graded_at;
      const gradedAt = gradedAtValue ? new Date(gradedAtValue) : null;

      const result = await pool.query(
        `INSERT INTO curriculum_submissions
          (student_id, client_submission_key, assignment_key, assignment_title, course_name, subject,
           unit_title, lesson_title, assignment_type, weight_category, status, percentage, letter_grade,
           points_earned, points_possible, point_value, counts_toward_grade, needs_review,
           time_spent_seconds, language_key, elective_kind, details, submitted_at, graded_at, updated_at)
         VALUES
          ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22::jsonb,$23,$24,NOW())
         ON CONFLICT (student_id, client_submission_key) DO UPDATE SET
           assignment_title=EXCLUDED.assignment_title, course_name=EXCLUDED.course_name, subject=EXCLUDED.subject,
           unit_title=EXCLUDED.unit_title, lesson_title=EXCLUDED.lesson_title, assignment_type=EXCLUDED.assignment_type,
           weight_category=EXCLUDED.weight_category, status=EXCLUDED.status, percentage=EXCLUDED.percentage,
           letter_grade=EXCLUDED.letter_grade, points_earned=EXCLUDED.points_earned,
           points_possible=EXCLUDED.points_possible, point_value=EXCLUDED.point_value,
           counts_toward_grade=EXCLUDED.counts_toward_grade, needs_review=EXCLUDED.needs_review,
           time_spent_seconds=GREATEST(curriculum_submissions.time_spent_seconds, EXCLUDED.time_spent_seconds),
           language_key=EXCLUDED.language_key, elective_kind=EXCLUDED.elective_kind,
           details=EXCLUDED.details, graded_at=COALESCE(EXCLUDED.graded_at, curriculum_submissions.graded_at), updated_at=NOW()
         RETURNING *`,
        [studentId, clientKey, assignmentKey, cleanText(body.assignment_title || body.title || assignmentKey, 500),
         cleanText(body.course_name || body.subject || 'Grade 6 Course', 300), cleanText(body.subject || body.course_name || 'Grade 6 Course', 200),
         cleanText(body.unit_title, 300), cleanText(body.lesson_title, 300), cleanText(body.assignment_type, 80, 'classwork'),
         cleanText(body.weight_category || body.assignment_weight_category, 80, 'classwork'), status, percentage,
         cleanText(body.letter_grade, 8), pointsEarned, pointsPossible, pointValue, body.counts_toward_grade !== false,
         body.needs_review === true, Math.max(0, Math.round(Number(body.time_spent_seconds || 0))),
         cleanText(body.language_key, 40) || null, cleanText(body.local_grade6_elective_kind || body.elective_kind, 40) || null,
         JSON.stringify(details), submittedAt.toISOString(), gradedAt && !Number.isNaN(gradedAt.getTime()) ? gradedAt.toISOString() : null]
      );
      const activitySeconds = Math.max(0, Math.round(Number(body.time_spent_seconds || 0)));
      if (activitySeconds > 0) {
        const activityStart = new Date(submittedAt.getTime() - activitySeconds * 1000);
        await pool.query(
          `INSERT INTO student_activity_sessions
            (student_id, session_key, activity_type, started_at, last_activity_at, ended_at, subject, note, assignment_id, assignment_title, updated_at)
           VALUES ($1,$2,'assignment',$3,$4,$4,$5,$6,$7,$8,NOW())
           ON CONFLICT (student_id, session_key) DO UPDATE SET
             started_at=EXCLUDED.started_at, last_activity_at=EXCLUDED.last_activity_at,
             ended_at=EXCLUDED.ended_at, subject=EXCLUDED.subject, note=EXCLUDED.note,
             assignment_id=EXCLUDED.assignment_id, assignment_title=EXCLUDED.assignment_title, updated_at=NOW()`,
          [studentId, `assignment:${clientKey}`, activityStart.toISOString(), submittedAt.toISOString(),
           cleanText(body.subject || body.course_name || 'Grade 6 Course', 200), 'Assignment submitted',
           assignmentKey, cleanText(body.assignment_title || body.title || assignmentKey, 500)]
        );
      }
      return res.status(201).json(rowForClient(result.rows[0]));
    } catch (error) {
      console.error('V34 curriculum submission save failed:', error);
      return res.status(500).json({ error: 'Unable to save curriculum work' });
    }
  });
};
