// JNM Grade 6 live PostgreSQL verifier.
// Run this from the backend project after the spelling seed:
//   node backend_seed/verify-grade6-postgresql.js
const { Pool } = require('pg');
const fs = require('fs');
const path = require('path');
try { require('dotenv').config(); } catch {}

const pool = new Pool({
  user: process.env.DB_USER || 'homeschool_user',
  password: process.env.DB_PASSWORD,
  host: process.env.DB_HOST || 'localhost',
  port: Number(process.env.DB_PORT || 5432),
  database: process.env.DB_NAME || 'jnm_homeschool_db'
});

const checks = [];
const add = (name, passed, detail) => checks.push({ name, status: passed ? 'PASS' : 'FAIL', detail });

async function scalar(client, sql, params = []) {
  const result = await client.query(sql, params);
  return Number(Object.values(result.rows[0] || {})[0] || 0);
}

(async () => {
  const client = await pool.connect();
  try {
    const courses = await client.query("SELECT id FROM courses WHERE name = 'Grade 6 Spelling' AND grade_level = 6 AND subject = 'Spelling' ORDER BY id");
    add('Exactly one Grade 6 Spelling course exists', courses.rowCount === 1, `${courses.rowCount} matching course row(s).`);
    if (courses.rowCount !== 1) throw new Error('Cannot safely continue until duplicate or missing Grade 6 Spelling courses are corrected.');
    const courseId = courses.rows[0].id;
    const unitCount = await scalar(client, 'SELECT COUNT(*) FROM units WHERE course_id = $1', [courseId]);
    const distinctWeeks = await scalar(client, 'SELECT COUNT(DISTINCT unit_number) FROM units WHERE course_id = $1 AND unit_number BETWEEN 1 AND 36', [courseId]);
    const lessonCount = await scalar(client, 'SELECT COUNT(*) FROM lessons l JOIN units u ON u.id = l.unit_id WHERE u.course_id = $1', [courseId]);
    const assignmentCount = await scalar(client, 'SELECT COUNT(*) FROM assignments a JOIN lessons l ON l.id = a.lesson_id JOIN units u ON u.id = l.unit_id WHERE u.course_id = $1', [courseId]);
    const wrongUnitAssignmentCount = await scalar(client, `SELECT COUNT(*) FROM (SELECT u.id FROM units u JOIN lessons l ON l.unit_id=u.id JOIN assignments a ON a.lesson_id=l.id WHERE u.course_id=$1 GROUP BY u.id HAVING COUNT(a.id) <> 5) bad`, [courseId]);
    const wrongWordListCount = await scalar(client, `SELECT COUNT(*) FROM lessons l JOIN units u ON u.id=l.unit_id WHERE u.course_id=$1 AND jsonb_array_length(COALESCE(l.word_list, '[]'::jsonb)) <> 20`, [courseId]);
    const weekdayTitles = await scalar(client, `SELECT COUNT(*) FROM assignments a JOIN lessons l ON l.id=a.lesson_id JOIN units u ON u.id=l.unit_id WHERE u.course_id=$1 AND a.title ~* '\\m(Monday|Tuesday|Wednesday|Thursday|Friday)\\M'`, [courseId]);
    const duplicateTitles = await scalar(client, `SELECT COUNT(*) FROM (SELECT a.lesson_id,a.title FROM assignments a JOIN lessons l ON l.id=a.lesson_id JOIN units u ON u.id=l.unit_id WHERE u.course_id=$1 GROUP BY a.lesson_id,a.title HAVING COUNT(*)>1) dup`, [courseId]);
    const obsoleteRows = await scalar(client, `SELECT COUNT(*) FROM assignments a JOIN lessons l ON l.id=a.lesson_id JOIN units u ON u.id=l.unit_id WHERE u.course_id=$1 AND (a.title ~* 'spelling practice|fill in the missing letters|use words in sentences|weekly spelling test' AND a.title !~* '^Assignment [1-5]:')`, [courseId]);
    const writingAssignments = await scalar(client, `SELECT COUNT(*) FROM assignments a JOIN lessons l ON l.id=a.lesson_id JOIN units u ON u.id=l.unit_id WHERE u.course_id=$1 AND a.weight_category='writing' AND jsonb_array_length(COALESCE(a.questions,'[]'::jsonb))=20 AND NOT EXISTS (SELECT 1 FROM jsonb_array_elements(COALESCE(a.questions,'[]'::jsonb)) q WHERE COALESCE((q->>'needs_review')::boolean,false)=false)`, [courseId]);
    const smallObjectiveAssignments = await scalar(client, `SELECT COUNT(*) FROM assignments a JOIN lessons l ON l.id=a.lesson_id JOIN units u ON u.id=l.unit_id WHERE u.course_id=$1 AND a.weight_category <> 'writing' AND jsonb_array_length(COALESCE(a.questions,'[]'::jsonb)) < 5`, [courseId]);

    add('Grade 6 Spelling has 36 units', unitCount === 36, `${unitCount} unit row(s).`);
    add('Weeks 1–36 are unique and complete', distinctWeeks === 36, `${distinctWeeks} distinct numbered week(s).`);
    add('Each week has one lesson', lessonCount === 36, `${lessonCount} lesson row(s).`);
    add('Exactly 180 assignments exist', assignmentCount === 180, `${assignmentCount} assignment row(s).`);
    add('Every week has exactly five assignments', wrongUnitAssignmentCount === 0, `${wrongUnitAssignmentCount} week(s) have the wrong assignment count.`);
    add('Every week has 20 spelling words', wrongWordListCount === 0, `${wrongWordListCount} week(s) have the wrong word count.`);
    add('No weekday assignment titles remain', weekdayTitles === 0, `${weekdayTitles} weekday title(s).`);
    add('No duplicate assignment title remains within a lesson', duplicateTitles === 0, `${duplicateTitles} duplicate title group(s).`);
    add('No obsolete spelling assignment rows remain', obsoleteRows === 0, `${obsoleteRows} obsolete row(s).`);
    add('All 36 sentence assignments contain 20 review-required responses', writingAssignments === 36, `${writingAssignments}/36 writing assignments are correct.`);
    add('Every objective spelling assignment has at least five questions', smallObjectiveAssignments === 0, `${smallObjectiveAssignments} undersized objective assignment(s).`);

    const menuTable = await client.query("SELECT to_regclass('public.student_menu_controls') AS table_name");
    add('Student menu controls table exists', !!menuTable.rows[0]?.table_name, menuTable.rows[0]?.table_name ? 'student_menu_controls is ready.' : 'student_menu_controls is missing.');
    if (menuTable.rows[0]?.table_name) {
      const menuPrimaryKey = await scalar(client, `SELECT COUNT(*) FROM pg_constraint WHERE conrelid='student_menu_controls'::regclass AND contype='p'`);
      const invalidMenuRows = await scalar(client, `SELECT COUNT(*) FROM student_menu_controls WHERE controls IS NULL OR jsonb_typeof(controls) <> 'object'`);
      add('Menu settings remain one row per student', menuPrimaryKey === 1, `${menuPrimaryKey} primary-key constraint(s).`);
      add('Saved menu settings are valid JSON objects', invalidMenuRows === 0, `${invalidMenuRows} invalid menu-control row(s).`);
    }

    const activityTable = await client.query("SELECT to_regclass('public.student_activity_sessions') AS table_name");
    add('Student activity session table exists', !!activityTable.rows[0]?.table_name, activityTable.rows[0]?.table_name ? 'student_activity_sessions is ready.' : 'student_activity_sessions is missing.');
    if (activityTable.rows[0]?.table_name) {
      const sessionUniqueness = await scalar(client, `SELECT COUNT(*) FROM pg_constraint WHERE conrelid='student_activity_sessions'::regclass AND contype='u'`);
      const invalidSessions = await scalar(client, `SELECT COUNT(*) FROM student_activity_sessions WHERE session_key='' OR last_activity_at < started_at`);
      add('Student login sessions have a uniqueness constraint', sessionUniqueness >= 1, `${sessionUniqueness} unique constraint(s).`);
      add('Saved student activity times are valid', invalidSessions === 0, `${invalidSessions} invalid activity session row(s).`);
    }

    const curriculumTable = await client.query("SELECT to_regclass('public.curriculum_submissions') AS table_name");
    add('Shared curriculum submissions table exists', !!curriculumTable.rows[0]?.table_name, curriculumTable.rows[0]?.table_name ? 'curriculum_submissions is ready.' : 'curriculum_submissions is missing.');
    if (curriculumTable.rows[0]?.table_name) {
      const curriculumUniqueness = await scalar(client, `SELECT COUNT(*) FROM pg_constraint WHERE conrelid='curriculum_submissions'::regclass AND contype='u'`);
      const invalidCurriculumRows = await scalar(client, `SELECT COUNT(*) FROM curriculum_submissions WHERE assignment_key='' OR client_submission_key='' OR time_spent_seconds < 0 OR (needs_review AND percentage IS NOT NULL)`);
      add('Curriculum submission retries cannot create duplicates', curriculumUniqueness >= 1, `${curriculumUniqueness} unique constraint(s).`);
      add('Saved curriculum submissions have valid grading state', invalidCurriculumRows === 0, `${invalidCurriculumRows} invalid curriculum submission row(s).`);
    }

    const settingsTable = await client.query("SELECT to_regclass('public.student_curriculum_settings') AS table_name");
    add('Shared curriculum settings table exists', !!settingsTable.rows[0]?.table_name, settingsTable.rows[0]?.table_name ? 'student_curriculum_settings is ready.' : 'student_curriculum_settings is missing.');
    if (settingsTable.rows[0]?.table_name) {
      const invalidLanguageSettings = await scalar(client, `SELECT COUNT(*) FROM student_curriculum_settings WHERE foreign_language_key NOT IN ('spanish','french','portuguese')`);
      add('Saved language selections are valid', invalidLanguageSettings === 0, `${invalidLanguageSettings} invalid language setting row(s).`);
    }

    const announcementsTable = await client.query("SELECT to_regclass('public.program_announcements') AS table_name");
    add('Shared announcements table exists', !!announcementsTable.rows[0]?.table_name, announcementsTable.rows[0]?.table_name ? 'program_announcements is ready.' : 'program_announcements is missing.');
    if (announcementsTable.rows[0]?.table_name) {
      const invalidAnnouncements = await scalar(client, `SELECT COUNT(*) FROM program_announcements WHERE title='' OR body='' OR status NOT IN ('Draft','Published')`);
      add('Saved announcements have valid content and status', invalidAnnouncements === 0, `${invalidAnnouncements} invalid announcement row(s).`);
    }
  } finally {
    client.release();
    await pool.end();
  }

  const report = {
    schema_version: 1,
    checked_at: new Date().toISOString(),
    pass_count: checks.filter(c => c.status === 'PASS').length,
    fail_count: checks.filter(c => c.status === 'FAIL').length,
    pending_count: 0,
    checks
  };
  const outputDir = path.resolve(__dirname, '..', 'verifiers');
  fs.mkdirSync(outputDir, { recursive: true });
  fs.writeFileSync(path.join(outputDir, 'postgresql_live_results.json'), JSON.stringify(report, null, 2) + '\n');
  checks.forEach(c => console.log(`${c.status}: ${c.name} — ${c.detail}`));
  console.log(`TOTAL: ${checks.length} | PASS: ${report.pass_count} | FAIL: ${report.fail_count} | PENDING: 0`);
  if (report.fail_count) process.exitCode = 1;
})().catch(async error => {
  console.error(`PostgreSQL verification failed: ${error.message}`);
  try { await pool.end(); } catch {}
  process.exitCode = 1;
});
