/**
 * Transfers Controller — Academic Interoperability & Digital Consent (FR-XFER1, FR-XFER2, FR-XFER3)
 * Handles school-to-school record transfers, progression packages, and Zim Cyber Act consent workflows.
 */

const express = require('express');
const router = express.Router();
const path = require('path');
const Database = require('better-sqlite3');
const { v4: uuidv4 } = require('uuid');

const dbPath = process.env.DB_PATH || path.join(__dirname, '../../data/school.db');
let db;
const getDb = () => {
  if (!db) {
    db = new Database(dbPath);
    db.pragma('foreign_keys = ON');
    // Ensure transfer_consents table exists
    db.exec(`
      CREATE TABLE IF NOT EXISTS transfer_consents (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        uid TEXT UNIQUE NOT NULL,
        student_id INTEGER NOT NULL,
        requested_by TEXT NOT NULL,
        target_school TEXT NOT NULL,
        consent_status TEXT NOT NULL DEFAULT 'pending',
        signed_at DATETIME,
        signature_hash TEXT,
        is_deleted INTEGER DEFAULT 0,
        sync_status TEXT DEFAULT 'pending',
        last_synced_at DATETIME,
        created_at DATETIME DEFAULT (datetime('now','localtime')),
        updated_at DATETIME DEFAULT (datetime('now','localtime'))
      );
    `);
  }
  return db;
};

// Auth middleware
const auth = (req, res, next) => {
  const token = req.headers.authorization?.split(' ')[1];
  if (!token) return res.status(401).json({ error: 'No token provided' });
  try {
    const jwt = require('jsonwebtoken');
    const { jwtSecret: JWT_SECRET } = require('../config');
    req.user = jwt.verify(token, JWT_SECRET);
    next();
  } catch (err) {
    res.status(401).json({ error: 'Invalid token' });
  }
};

const adminOnly = (req, res, next) => {
  if (!['school_admin', 'systems_admin', 'principal'].includes(req.user.role)) {
    return res.status(403).json({ error: 'Admin access required' });
  }
  next();
};

/**
 * GET /api/transfers or /api/xfer
 * List transfer requests
 */
router.get('/', auth, adminOnly, (req, res) => {
  try {
    const records = getDb().prepare(`
      SELECT tc.*, u.first_name, u.last_name, u.email
      FROM transfer_consents tc
      JOIN users u ON tc.student_id = u.id
      WHERE tc.is_deleted = 0
      ORDER BY tc.created_at DESC
    `).all();
    res.json({ success: true, transfers: records });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

/**
 * GET /api/transfers/requests
 * Alias for transfer requests list
 */
router.get('/requests', auth, adminOnly, (req, res) => {
  try {
    const records = getDb().prepare(`
      SELECT tc.*, u.first_name, u.last_name, u.email
      FROM transfer_consents tc
      JOIN users u ON tc.student_id = u.id
      WHERE tc.is_deleted = 0
      ORDER BY tc.created_at DESC
    `).all();
    res.json({ success: true, requests: records });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

/**
 * POST /api/transfers/initiate
 * Initiate student transfer package (FR-XFER1)
 */
router.post('/initiate', auth, adminOnly, (req, res) => {
  try {
    const { student_id, target_school, reason } = req.body;
    if (!student_id || !target_school) {
      return res.status(400).json({ error: 'student_id and target_school are required' });
    }

    const student = getDb().prepare('SELECT id, first_name, last_name FROM users WHERE id = ? AND is_deleted = 0').get(student_id);
    if (!student) {
      return res.status(404).json({ error: 'Student record not found' });
    }

    const uid = uuidv4();
    getDb().prepare(`
      INSERT INTO transfer_consents (uid, student_id, requested_by, target_school, consent_status, sync_status)
      VALUES (?, ?, ?, ?, 'pending', 'pending')
    `).run(uid, student_id, req.user.email || 'admin', target_school);

    res.status(201).json({
      success: true,
      transfer_id: uid,
      student_name: `${student.first_name} ${student.last_name}`,
      target_school,
      consent_status: 'pending',
      instructions: 'Digital consent required from parent or adult student before record package transmission.'
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

/**
 * POST /api/transfers/consent (FR-XFER3)
 * Digital consent sign-off with PIN/signature hash (Zim Cyber & Data Protection Act compliance)
 */
router.post('/consent', auth, (req, res) => {
  try {
    const { transfer_id, consent_status, signature_pin } = req.body;
    if (!transfer_id || !consent_status) {
      return res.status(400).json({ error: 'transfer_id and consent_status are required' });
    }

    const transfer = getDb().prepare('SELECT * FROM transfer_consents WHERE uid = ? AND is_deleted = 0').get(transfer_id);
    if (!transfer) {
      return res.status(404).json({ error: 'Transfer request not found' });
    }

    const crypto = require('crypto');
    const signatureHash = crypto.createHash('sha256').update(`${transfer_id}-${req.user.id}-${Date.now()}-${signature_pin || ''}`).digest('hex');

    getDb().prepare(`
      UPDATE transfer_consents
      SET consent_status = ?,
          signed_at = datetime('now','localtime'),
          signature_hash = ?,
          updated_at = datetime('now','localtime'),
          sync_status = 'pending'
      WHERE uid = ?
    `).run(consent_status, signatureHash, transfer_id);

    res.json({
      success: true,
      transfer_id,
      consent_status,
      signature_hash: signatureHash,
      signed_at: new Date().toISOString()
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

/**
 * GET /api/transfers/consent
 * Retrieve consent status by transfer_id or student_id
 */
router.get('/consent', auth, adminOnly, (req, res) => {
  try {
    const { student_id, transfer_id } = req.query;
    let query = 'SELECT * FROM transfer_consents WHERE is_deleted = 0';
    const params = [];

    if (transfer_id) {
      query += ' AND uid = ?';
      params.push(transfer_id);
    } else if (student_id) {
      query += ' AND student_id = ?';
      params.push(student_id);
    }

    const consents = getDb().prepare(query).all(...params);
    res.json({ success: true, consents });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// ============ FR-XFER2 — PROGRESSION / GRADUATION EXPORT ============
//
// Packages a student's complete academic + attendance + disciplinary +
// financial-clearance record for transmission to the next institution
// (Form 1 placement, High School to University, transfer between
// Next Gen schools that have already consented).
//
// Auth:
//   - admins, principals, systems_admin — full access
//   - the student themselves — read-self
//   - a linked parent — read-dependents
//
// Required body: { student_id (or uid), target_institution, consent_id? }
// Returns: a self-contained JSON package that the receiving school can
// ingest. The actual transmission (signed/encrypted payload) is left to
// the client; this controller produces the canonical record.

router.post('/graduation', auth, (req, res) => {
  try {
    const { student_id, student_uid, target_institution, consent_id } = req.body || {};
    if (!target_institution) {
      return res.status(400).json({ error: 'target_institution is required' });
    }
    if (!student_id && !student_uid) {
      return res.status(400).json({ error: 'student_id or student_uid is required' });
    }

    if (req.user.role === 'student' && student_id && Number(student_id) !== req.user.id) {
      return res.status(403).json({ error: 'Access denied' });
    }

    const db = getDb();
    const student = student_uid
      ? db.prepare('SELECT * FROM users WHERE uid = ? AND is_deleted = 0').get(student_uid)
      : db.prepare('SELECT * FROM users WHERE id = ? AND is_deleted = 0').get(Number(student_id));
    if (!student) return res.status(404).json({ error: 'Student not found' });

    // Self-scope check
    if (req.user.role === 'student' && student.id !== req.user.id) {
      return res.status(403).json({ error: 'Access denied' });
    }
    if (req.user.role === 'parent') {
      const linked = db.prepare('SELECT 1 FROM parent_students WHERE parent_id = ? AND student_id = ? AND is_deleted = 0').get(req.user.id, student.id);
      if (!linked) return res.status(403).json({ error: 'Access denied' });
    }

    // Optional consent verification: if the caller passes a consent_id,
    // the consent must belong to this student and be in an approved state.
    let consent = null;
    if (consent_id) {
      consent = db.prepare('SELECT * FROM transfer_consents WHERE uid = ? AND is_deleted = 0').get(consent_id);
      if (!consent) return res.status(404).json({ error: 'consent_id not found' });
      if (consent.student_id !== student.id) {
        return res.status(400).json({ error: 'consent_id does not match student' });
      }
      if (consent.consent_status !== 'approved' && consent.consent_status !== 'granted') {
        return res.status(409).json({
          error: 'Consent not yet approved',
          consent_status: consent.consent_status,
          instructions: 'Per FR-XFER3, the transfer package may not be exported until the parent / adult student signs the consent workflow (POST /api/xfer/consent).',
        });
      }
    }

    // 1) Academic history — grades per subject per term.
    // The grades table stores `academic_year` and `term` as text columns
    // (not FKs); the student's class is derived via the enrollments table
    // (one active enrollment per student per year).
    const grades = db.prepare(`
      SELECT g.*, s.name AS subject_name, s.code AS subject_code,
             c.name AS class_name, c.section AS class_section
      FROM grades g
      JOIN subjects s ON g.subject_id = s.id
      LEFT JOIN enrollments e ON e.student_id = g.student_id
        AND e.academic_year = g.academic_year
        AND e.is_deleted = 0
      LEFT JOIN classes c ON e.class_id = c.id
      WHERE g.student_id = ? AND g.is_deleted = 0
      ORDER BY g.academic_year DESC, g.term ASC, s.name ASC
    `).all(student.id);

    // 2) Attendance summary
    const attendance = db.prepare(`
      SELECT
        COUNT(*) AS total_sessions,
        SUM(CASE WHEN status = 'present' THEN 1 ELSE 0 END) AS present,
        SUM(CASE WHEN status = 'absent' THEN 1 ELSE 0 END) AS absent,
        SUM(CASE WHEN status = 'late' THEN 1 ELSE 0 END) AS late
      FROM attendance
      WHERE student_id = ? AND is_deleted = 0
    `).get(student.id);

    // 3) Disciplinary record — best-effort, the table may not exist in
    // every deploy. Wrap in try/catch so missing schema doesn't kill
    // the export.
    let disciplinary = [];
    try {
      disciplinary = db.prepare(`
        SELECT * FROM disciplinary_records
        WHERE student_id = ? AND is_deleted = 0
        ORDER BY incident_date DESC
      `).all(student.id);
    } catch (e) {
      disciplinary = [];
    }

    // 4) Financial clearance — outstanding balance and any active holds
    const financial = db.prepare(`
      SELECT
        COALESCE(SUM(sf.amount - sf.paid_amount), 0) AS total_outstanding,
        SUM(CASE WHEN sf.status = 'paid' THEN 1 ELSE 0 END) AS paid_lines,
        SUM(CASE WHEN sf.status = 'partial' THEN 1 ELSE 0 END) AS partial_lines,
        SUM(CASE WHEN sf.status = 'pending' THEN 1 ELSE 0 END) AS pending_lines,
        COUNT(*) AS total_lines
      FROM student_fees sf
      WHERE sf.student_id = ? AND sf.is_deleted = 0
    `).get(student.id);

    const totalOutstanding = financial?.total_outstanding || 0;
    const financialClearance = totalOutstanding === 0;

    const pkg = {
      success: true,
      package_type: 'graduation_export',
      schema_version: '1.0',
      generated_at: new Date().toISOString(),
      generated_by: { id: req.user.id, role: req.user.role, email: req.user.email },
      target_institution,
      student: {
        id: student.id,
        uid: student.uid,
        id_number: student.id_number,
        first_name: student.first_name,
        last_name: student.last_name,
        date_of_birth: student.date_of_birth,
        gender: student.gender,
      },
      academic_history: {
        grades,
        grade_count: grades.length,
      },
      attendance_summary: {
        total_sessions: attendance?.total_sessions || 0,
        present: attendance?.present || 0,
        absent: attendance?.absent || 0,
        late: attendance?.late || 0,
        attendance_rate: attendance?.total_sessions > 0
          ? Math.round((attendance.present / attendance.total_sessions) * 100)
          : null,
      },
      disciplinary_record: disciplinary,
      financial_clearance: {
        cleared: financialClearance,
        total_outstanding: totalOutstanding,
        paid_lines: financial?.paid_lines || 0,
        partial_lines: financial?.partial_lines || 0,
        pending_lines: financial?.pending_lines || 0,
      },
      consent: consent ? {
        consent_id: consent.uid,
        consent_status: consent.consent_status,
        signed_at: consent.signed_at,
        target_school: consent.target_school,
      } : null,
      signature: {
        // The receiving school can verify integrity by recomputing this
        // SHA-256 over the JSON-serialised payload (minus this field).
        algorithm: 'sha256',
        // Computed below; see signature_digest
        signature_digest: null,
      },
    };

    const { createHash } = require('crypto');
    const canonical = JSON.stringify(pkg, (k, v) => (k === 'signature' ? undefined : v));
    pkg.signature.signature_digest = createHash('sha256').update(canonical).digest('hex');

    res.json(pkg);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;
