/**
 * Settings Controller
 * System configuration and preferences
 */

const express = require('express');
const router = express.Router();

// Database path
const dbPath = process.env.DB_PATH || require('path').join(__dirname, '../../data/school.db');
const Database = require('better-sqlite3');
let db;

const getDb = () => {
  if (!db) {
    db = new Database(dbPath);
    db.pragma('foreign_keys = ON');
  }
  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' });
  }
};

// Admin-only middleware (Systems or School admin)
const adminOnly = (req, res, next) => {
  if (!['systems_admin', 'school_admin'].includes(req.user.role)) {
    return res.status(403).json({ error: 'Admin access required' });
  }
  next();
};

// ============ SYSTEM SETTINGS (STATIC ROUTES FIRST) ============

/**
 * @openapi
 * /api/settings:
 *   get:
 *     summary: List system settings
 *     description: |
 *       Admins see every category with sensitive values masked as `***`.
 *       Non-admins receive only `general` and `academic` categories.
 *     tags:
 *       - Settings
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: category
 *         schema:
 *           type: string
 *         description: Filter to one category
 *     responses:
 *       200:
 *         description: Key-value map of settings
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               additionalProperties: true
 */
// Get all settings (for admin) or public settings (for all users)
router.get('/', auth, (req, res) => {
  try {
    const { category } = req.query;
    
    let query = 'SELECT key, value, description, category FROM system_settings WHERE is_deleted = 0';
    const params = [];
    
    // Non-admins only see general settings
    if (!['systems_admin', 'school_admin'].includes(req.user.role)) {
      query += " AND category IN ('general', 'academic')";
    }
    
    if (category) {
      query += ' AND category = ?';
      params.push(category);
    }
    
    const settings = getDb().prepare(query).all(...params);
    
    // Convert to key-value object and mask sensitive values
    const result = settings.reduce((acc, s) => {
      const lowerKey = s.key.toLowerCase();
      if (lowerKey.includes('key') || lowerKey.includes('secret') || lowerKey.includes('password') || lowerKey.includes('supabase') || lowerKey.includes('token') || lowerKey.includes('auth')) {
        acc[s.key] = s.value ? '***' : '';
      } else {
        acc[s.key] = s.value;
      }
      return acc;
    }, {});

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

/**
 * @openapi
 * /api/settings/academic:
 *   get:
 *     summary: Academic settings
 *     description: Returns the `academic` category (current year, term start/end, etc.).
 *     tags:
 *       - Settings
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Academic settings key-value map
 */
// Get academic settings
router.get('/academic', auth, (req, res) => {
  try {
    const settings = getDb().prepare(`
      SELECT key, value FROM system_settings
      WHERE category = 'academic' AND is_deleted = 0
    `).all();
    
    res.json(settings.reduce((acc, s) => { acc[s.key] = s.value; return acc; }, {}));
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/settings/payment:
 *   get:
 *     summary: Payment settings
 *     description: Paynow configuration (admin only).
 *     tags:
 *       - Settings
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Payment settings
 *       403: { description: Admin required }
 */
// Get payment settings
router.get('/payment', auth, adminOnly, (req, res) => {
  try {
    const settings = getDb().prepare(`
      SELECT key, value FROM system_settings
      WHERE category = 'payment' AND is_deleted = 0
    `).all();
    
    // Mask sensitive values
    const masked = settings.reduce((acc, s) => {
      const lowerKey = s.key.toLowerCase();
      if (lowerKey.includes('key') || lowerKey.includes('secret') || lowerKey.includes('password') || lowerKey.includes('supabase') || lowerKey.includes('token')) {
        acc[s.key] = s.value ? '***' : '';
      } else {
        acc[s.key] = s.value;
      }
      return acc;
    }, {});
    
    res.json(masked);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/settings/sync:
 *   get:
 *     summary: Sync settings
 *     description: Supabase URL/key and interval (admin only). Sensitive values masked.
 *     tags:
 *       - Settings
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Sync settings
 *       403: { description: Admin required }
 */
// Get sync settings
router.get('/sync', auth, adminOnly, (req, res) => {
  try {
    const settings = getDb().prepare(`
      SELECT key, value FROM system_settings
      WHERE category = 'sync' AND is_deleted = 0
    `).all();
    
    // Mask sensitive values
    const masked = settings.reduce((acc, s) => {
      const lowerKey = s.key.toLowerCase();
      if (lowerKey.includes('key') || lowerKey.includes('secret') || lowerKey.includes('password') || lowerKey.includes('supabase') || lowerKey.includes('token')) {
        acc[s.key] = s.value ? '***' : '';
      } else {
        acc[s.key] = s.value;
      }
      return acc;
    }, {});
    
    res.json(masked);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/settings/diagnostics:
 *   get:
 *     summary: Quick diagnostic snapshot
 *     description: Returns uptime, memory, DB size, and per-table row counts. Admin only.
 *     tags:
 *       - Settings
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Diagnostics
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: boolean }
 *                 uptime: { type: number }
 *                 memory:
 *                   type: object
 *                   properties:
 *                     total: { type: integer }
 *                     free: { type: integer }
 *                     usage: { type: integer }
 *                 tables:
 *                   type: object
 *                   additionalProperties: { type: integer }
 *                 dbSize: { type: integer }
 *       403: { description: Admin required }
 */
// Get diagnostic information (admin only)
router.get('/diagnostics', auth, adminOnly, (req, res) => {
  try {
    const tables = getDb().prepare("SELECT name FROM sqlite_master WHERE type='table'").all();
    const tableCounts = tables.reduce((acc, t) => {
      try {
        const count = getDb().prepare(`SELECT COUNT(*) as c FROM "${t.name}"`).get().c;
        acc[t.name] = count;
      } catch (e) {
        acc[t.name] = 0;
      }
      return acc;
    }, {});

    const os = require('os');
    res.json({
      success: true,
      uptime: process.uptime(),
      memory: {
        total: os.totalmem(),
        free: os.freemem(),
        usage: os.totalmem() - os.freemem()
      },
      tables: tableCounts,
      dbSize: require('fs').statSync(dbPath).size
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/settings/backup/download:
 *   get:
 *     summary: Download SQLite database backup
 *     description: Streams the SQLite file as `school-backup.db`. Admin only.
 *     tags:
 *       - Settings
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: SQLite file stream
 *         content:
 *           application/octet-stream:
 *             schema:
 *               type: string
 *               format: binary
 *       403: { description: Admin required }
 */
// Download database backup (admin only)
router.get('/backup/download', auth, adminOnly, (req, res) => {
  try {
    res.download(dbPath, 'school-backup.db');
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

const multer = require('multer');
const path = require('path');
const fs = require('fs');

const storage = multer.diskStorage({
  destination: function (req, file, cb) {
    const uploadDir = path.join(__dirname, '../../uploads');
    if (!fs.existsSync(uploadDir)) {
      fs.mkdirSync(uploadDir, { recursive: true });
    }
    cb(null, uploadDir);
  },
  filename: function (req, file, cb) {
    const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
    cb(null, 'logo-' + uniqueSuffix + path.extname(file.originalname));
  }
});

const upload = multer({
  storage: storage,
  limits: { fileSize: 5 * 1024 * 1024 }, // 5MB limit
  fileFilter: (req, file, cb) => {
    const filetypes = /jpeg|jpg|png|gif|svg|webp|svg\+xml/;
    const mimetype = filetypes.test(file.mimetype);
    const extname = filetypes.test(path.extname(file.originalname).toLowerCase());
    
    if (mimetype || extname) {
      cb(null, true);
    } else {
      cb(new Error('Only image files (SVG, PNG, JPEG, GIF, WEBP) are allowed'));
    }
  }
});

/**
 * @openapi
 * /api/settings/logo:
 *   post:
 *     summary: Upload school logo
 *     description: |
 *       Accepts a single image (PNG/JPEG/GIF/WEBP/SVG, ≤5MB) and persists
 *       `school_logo_path` and `school_logo_url` settings. Admin only.
 *     tags:
 *       - Settings
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         multipart/form-data:
 *           schema:
 *             type: object
 *             properties:
 *               logo:
 *                 type: string
 *                 format: binary
 *     responses:
 *       200:
 *         description: Logo URL
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 url: { type: string }
 *       400: { description: No file uploaded }
 *       403: { description: Admin required }
 */
router.post('/logo', auth, adminOnly, upload.single('logo'), (req, res) => {
  try {
    if (!req.file) {
      return res.status(400).json({ error: 'No file uploaded' });
    }

    const absolutePath = path.join(__dirname, '../../uploads', req.file.filename);
    const localUrl = `/uploads/${req.file.filename}`;

    const db = getDb();
    
    // Upsert school_logo_path setting
    const existingPath = db.prepare('SELECT id FROM system_settings WHERE key = ?').get('school_logo_path');
    if (existingPath) {
      db.prepare("UPDATE system_settings SET value = ?, category = 'general', updated_at = CURRENT_TIMESTAMP, sync_status = 'pending' WHERE key = ?")
        .run(absolutePath, 'school_logo_path');
    } else {
      db.prepare("INSERT INTO system_settings (key, value, category, sync_status) VALUES ('school_logo_path', ?, 'general', 'pending')")
        .run(absolutePath);
    }

    // Upsert school_logo_url setting. Both keys MUST live under
    // category='general' so the non-admin /api/settings filter
    // (category IN ('general','academic')) includes them — otherwise
    // student/parent/teacher portals silently fall back to the
    // absolute path row and the logo refuses to load in the browser.
    const existingUrl = db.prepare('SELECT id FROM system_settings WHERE key = ?').get('school_logo_url');
    if (existingUrl) {
      db.prepare("UPDATE system_settings SET value = ?, category = 'general', updated_at = CURRENT_TIMESTAMP, sync_status = 'pending' WHERE key = ?")
        .run(localUrl, 'school_logo_url');
    } else {
      db.prepare("INSERT INTO system_settings (key, value, category, sync_status) VALUES ('school_logo_url', ?, 'general', 'pending')")
        .run(localUrl);
    }

    // Force run sync cycle in background to push the new logo to Supabase
    try {
      const { getSyncEngine } = require('../services/SyncEngine');
      getSyncEngine().runSyncCycle();
    } catch (e) {
      // Ignore background sync errors
    }

    res.json({
      success: true,
      logo_path: absolutePath,
      logo_url: localUrl
    });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// ============ SYSTEM SETTINGS (WILDCARD ROUTES LAST) ============

/**
 * @openapi
 * /api/settings/{key}:
 *   get:
 *     summary: Get a single setting by key
 *     description: Sensitive keys have their value masked as `***`.
 *     tags:
 *       - Settings
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: key
 *         required: true
 *         schema:
 *           type: string
 *     responses:
 *       200:
 *         description: Setting object
 *       404: { description: Setting not found }
 */
/**
 * @openapi
 * /api/settings/ministry-push:
 *   get:
 *     summary: Ministry weekly push configuration
 *     description: |
 *       Returns the four DB-overridable keys that drive WeeklyGovPushService.
 *       Auth and hmac_secret values are masked as '***' when set. Empty
 *       values mean the corresponding env-var fallback is in use.
 *     tags:
 *       - Settings
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Config bundle
 *       401: { description: Unauthorized }
 *       403: { description: Forbidden }
 *   put:
 *     summary: Save Ministry weekly push configuration
 *     description: |
 *       Upserts any of the four DB-overridable keys. Keys not present in
 *       the body are left untouched. The literal string '***' is treated
 *       as 'no change' so the masked GET response can be round-tripped
 *       without overwriting a real secret.
 *     tags:
 *       - Settings
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               weekly_gov_push_urls: { type: string }
 *               weekly_gov_push_auth: { type: string }
 *               weekly_gov_push_hmac_secret: { type: string }
 *               weekly_gov_push_school_id: { type: string }
 *     responses:
 *       200: { description: Updated }
 *       403: { description: Forbidden }
 */
router.get('/ministry-push', auth, adminOnly, (req, res) => {
  try {
    const rows = getDb().prepare(
      "SELECT key, value FROM system_settings WHERE key LIKE 'weekly_gov_push_%' AND is_deleted = 0"
    ).all();
    const out = {};
    for (const r of rows) {
      const lk = r.key.toLowerCase();
      if (lk.includes('auth') || lk.includes('secret')) {
        out[r.key] = r.value ? '***' : '';
      } else {
        out[r.key] = r.value;
      }
    }
    res.json(out);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

router.put('/ministry-push', auth, adminOnly, (req, res) => {
  try {
    const ALLOWED = [
      'weekly_gov_push_urls',
      'weekly_gov_push_auth',
      'weekly_gov_push_hmac_secret',
      'weekly_gov_push_school_id',
    ];
    const db = getDb();
    const upsert = db.prepare(`
      INSERT INTO system_settings (key, value, category, updated_at)
      VALUES (?, ?, 'integration', datetime('now', 'localtime'))
      ON CONFLICT(key) DO UPDATE SET value = excluded.value, updated_at = datetime('now', 'localtime')
    `);
    const updated = [];
    for (const k of ALLOWED) {
      if (req.body[k] === undefined) continue;
      const v = req.body[k];
      // Treat the masked sentinel '***' as "no change" so the form can
      // re-submit unchanged secrets without clobbering them.
      if (v === '***') continue;
      if (v === '') {
        // Empty string means "clear this DB override and fall back to env".
        const existing = db.prepare('SELECT id FROM system_settings WHERE key = ?').get(k);
        if (existing) {
          db.prepare(
            "UPDATE system_settings SET is_deleted = 1, updated_at = datetime('now', 'localtime') WHERE key = ?"
          ).run(k);
        }
        updated.push(k + ' (cleared)');
        continue;
      }
      upsert.run(k, String(v));
      updated.push(k);
    }
    try {
      db.prepare(`
        INSERT INTO audit_logs (action, entity_type, entity_id, new_data, created_at)
        VALUES ('ministry_push_config_updated', 'integration', 'weekly_gov_push', ?, datetime('now', 'localtime'))
      `).run(JSON.stringify({ updated_by: req.user.id, keys: updated }));
    } catch (_) { /* audit is best-effort */ }
    // Invalidate the push service's config cache so the next cron tick
    // (or test push) sees the new values immediately.
    try {
      const { invalidatePushConfigCache } = require('../services/WeeklyGovPushService');
      invalidatePushConfigCache();
    } catch (_) { /* service may not be loaded in tests */ }
    res.json({ success: true, updated });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

router.get('/:key', auth, (req, res) => {
  try {
    const setting = getDb().prepare(`
      SELECT key, value, description, category
      FROM system_settings
      WHERE key = ? AND is_deleted = 0
    `).get(req.params.key);
    
    if (!setting) {
      return res.status(404).json({ error: 'Setting not found' });
    }
    
    // Mask value if it's sensitive
    const lowerKey = setting.key.toLowerCase();
    if (lowerKey.includes('key') || lowerKey.includes('secret') || lowerKey.includes('password') || lowerKey.includes('supabase') || lowerKey.includes('token')) {
      setting.value = setting.value ? '***' : '';
    }
    
    res.json(setting);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/settings:
 *   put:
 *     summary: Bulk update settings
 *     description: |
 *       Accepts a key-value object (or `{ settings: { ... } }`) and upserts
 *       each. Admin only.
 *     tags:
 *       - Settings
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             additionalProperties:
 *               type: string
 *             example:
 *               school_name: "Africa Alert"
 *               academic_year: "2026"
 *     responses:
 *       200:
 *         description: Update summary
 *       400: { description: Settings object required }
 *       403: { description: Admin required }
 */
// Bulk update settings
router.put('/', auth, adminOnly, (req, res) => {
  try {
    const settingsToUpdate = req.body.settings || req.body;
    
    if (!settingsToUpdate || typeof settingsToUpdate !== 'object') {
      return res.status(400).json({ error: 'Settings object required' });
    }
    
    const updateMany = getDb().transaction((data) => {
      const results = [];
      for (const [key, value] of Object.entries(data)) {
        try {
          const existing = getDb().prepare('SELECT id FROM system_settings WHERE key = ?').get(key);
          
          if (existing) {
            getDb().prepare(`
              UPDATE system_settings SET value = ?, updated_at = CURRENT_TIMESTAMP, sync_status = 'pending'
              WHERE key = ?
            `).run(String(value), key);
          } else {
            getDb().prepare(`
              INSERT INTO system_settings (key, value, category, sync_status)
              VALUES (?, ?, 'general', 'pending')
            `).run(key, String(value));
          }
          
          results.push({ key, success: true });
        } catch (e) {
          results.push({ key, success: false, error: e.message });
        }
      }
      return results;
    });
    
    const results = updateMany(settingsToUpdate);
    res.json({ updated: results.filter(r => r.success).length, results });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/settings/payment:
 *   put:
 *     summary: Update payment settings
 *     description: Upserts Paynow integration credentials and callback URL. Admin only.
 *     tags:
 *       - Settings
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               paynow_integration_id: { type: string }
 *               paynow_integration_key: { type: string, format: password }
 *               paynow_callback_url: { type: string, format: uri }
 *     responses:
 *       200:
 *         description: Update OK
 *       403: { description: Admin required }
 */
// Update payment settings
router.put('/payment', auth, adminOnly, (req, res) => {
  try {
    const { paynow_integration_id, paynow_integration_key, paynow_callback_url } = req.body;
    
    const upsert = (key, value) => {
      const existing = getDb().prepare('SELECT id FROM system_settings WHERE key = ?').get(key);
      if (existing) {
        getDb().prepare('UPDATE system_settings SET value = ?, updated_at = CURRENT_TIMESTAMP WHERE key = ?').run(value, key);
      } else {
        getDb().prepare('INSERT INTO system_settings (key, value, category) VALUES (?, ?, ?)').run(key, value, 'payment');
      }
    };
    
    if (paynow_integration_id !== undefined) upsert('paynow_integration_id', paynow_integration_id);
    if (paynow_integration_key !== undefined) upsert('paynow_integration_key', paynow_integration_key);
    if (paynow_callback_url !== undefined) upsert('paynow_callback_url', paynow_callback_url);
    
    res.json({ success: true });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/settings/sync:
 *   put:
 *     summary: Update sync settings
 *     description: Upserts Supabase credentials and sync interval. Admin only.
 *     tags:
 *       - Settings
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               supabase_url: { type: string, format: uri }
 *               supabase_key: { type: string, format: password }
 *               sync_interval_seconds: { type: integer, minimum: 5 }
 *     responses:
 *       200: { description: Update OK }
 *       403: { description: Admin required }
 */
// Update sync settings
router.put('/sync', auth, adminOnly, (req, res) => {
  try {
    const { supabase_url, supabase_key, sync_interval_seconds } = req.body;
    
    const upsert = (key, value) => {
      const existing = getDb().prepare('SELECT id FROM system_settings WHERE key = ?').get(key);
      if (existing) {
        getDb().prepare('UPDATE system_settings SET value = ?, updated_at = CURRENT_TIMESTAMP WHERE key = ?').run(value, key);
      } else {
        getDb().prepare('INSERT INTO system_settings (key, value, category) VALUES (?, ?, ?)').run(key, value, 'sync');
      }
    };
    
    if (supabase_url !== undefined) upsert('supabase_url', supabase_url);
    if (supabase_key !== undefined) upsert('supabase_key', supabase_key);
    if (sync_interval_seconds !== undefined) upsert('sync_interval_seconds', sync_interval_seconds);
    
    res.json({ success: true });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/settings/{key}:
 *   put:
 *     summary: Update or create a single setting
 *     description: Upserts one setting by key. Admin only.
 *     tags:
 *       - Settings
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: key
 *         required: true
 *         schema:
 *           type: string
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required: [value]
 *             properties:
 *               value: { type: string }
 *               description: { type: string }
 *               category: { type: string }
 *     responses:
 *       200: { description: Updated setting }
 *       400: { description: Value required }
 *       403: { description: Admin required }
 */

// Update/create single setting (admin only)
router.put('/:key', auth, adminOnly, (req, res) => {
  try {
    const { value, description, category } = req.body;
    
    if (value === undefined) {
      return res.status(400).json({ error: 'Value is required' });
    }
    
    // Check if setting exists
    const existing = getDb().prepare('SELECT id FROM system_settings WHERE key = ?').get(req.params.key);
    
    if (existing) {
      getDb().prepare(`
        UPDATE system_settings SET
          value = ?,
          description = COALESCE(?, description),
          category = COALESCE(?, category),
          updated_at = CURRENT_TIMESTAMP,
          sync_status = 'pending'
        WHERE key = ?
      `).run(String(value), description, category, req.params.key);
    } else {
      getDb().prepare(`
        INSERT INTO system_settings (key, value, description, category, sync_status)
        VALUES (?, ?, ?, ?, 'pending')
      `).run(req.params.key, String(value), description || '', category || 'general');
    }
    
    const setting = getDb().prepare('SELECT * FROM system_settings WHERE key = ?').get(req.params.key);
    res.json(setting);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/settings/{key}:
 *   delete:
 *     summary: Soft-delete a setting
 *     description: Sets `is_deleted = 1`. Admin only.
 *     tags:
 *       - Settings
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: key
 *         required: true
 *         schema:
 *           type: string
 *     responses:
 *       200: { description: Setting deleted }
 *       403: { description: Admin required }
 */
// Delete setting (admin only)
router.delete('/:key', auth, adminOnly, (req, res) => {
  try {
    // Soft delete
    getDb().prepare(`
      UPDATE system_settings SET is_deleted = 1, updated_at = CURRENT_TIMESTAMP, sync_status = 'pending'
      WHERE key = ?
    `).run(req.params.key);
    
    res.json({ success: true, message: 'Setting deleted' });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/settings/reset:
 *   post:
 *     summary: Reset core settings to defaults
 *     description: |
 *       Resets `school_name`, `academic_year`, `currency`, and
 *       `sync_interval_seconds` to hardcoded defaults. Admin only.
 *     tags:
 *       - Settings
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200: { description: Reset complete }
 *       403: { description: Admin required }
 */
// Reset to defaults
router.post('/reset', auth, adminOnly, (req, res) => {
  try {
    const defaults = {
      school_name: 'Africa Alert School',
      academic_year: new Date().getFullYear().toString(),
      currency: 'USD',
      sync_interval_seconds: '30'
    };
    
    const updateMany = getDb().transaction((defaults) => {
      for (const [key, value] of Object.entries(defaults)) {
        const existing = getDb().prepare('SELECT id FROM system_settings WHERE key = ?').get(key);
        if (existing) {
          getDb().prepare('UPDATE system_settings SET value = ?, updated_at = CURRENT_TIMESTAMP WHERE key = ?').run(value, key);
        } else {
          getDb().prepare('INSERT INTO system_settings (key, value, category) VALUES (?, ?, ?)').run(key, value, 'general');
        }
      }
    });
    
    updateMany(defaults);
    res.json({ success: true, message: 'Settings reset to defaults' });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/settings/terminate-sessions:
 *   post:
 *     summary: Acknowledge terminate-all-sessions
 *     description: |
 *       Stateless shim. JWTs remain valid until expiry; this endpoint
 *       only acknowledges the admin action. Admin only.
 *     tags:
 *       - Settings
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200: { description: Sessions terminated }
 *       403: { description: Admin required }
 */
// Terminate all sessions (admin only)
router.post('/terminate-sessions', auth, adminOnly, (req, res) => {
  try {
    // Statelessly simulate terminating all active sessions
    res.json({ success: true, message: 'All active sessions have been terminated' });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/settings/integrity-check:
 *   post:
 *     summary: Run SQLite integrity and FK checks
 *     description: |
 *       Runs `PRAGMA integrity_check` and `PRAGMA foreign_key_check` and
 *       returns a human-readable summary plus the raw violation rows. Admin only.
 *     tags:
 *       - Settings
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Integrity summary
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: boolean }
 *                 result: { type: string }
 *                 details:
 *                   type: object
 *                   properties:
 *                     structure: { type: string }
 *                     fk_violations:
 *                       type: array
 *                       items: { type: object }
 *       403: { description: Admin required }
 */
// Run integrity check (admin only)
router.post('/integrity-check', auth, adminOnly, (req, res) => {
  try {
    const db = getDb();
    
    // 1. Run structure integrity check
    const structResult = db.prepare('PRAGMA integrity_check').get();
    const isStructOk = structResult && structResult['integrity_check'] === 'ok';

    // 2. Run foreign key verification (identifies orphaned records violating constraints)
    const fkViolations = db.prepare('PRAGMA foreign_key_check').all();
    
    let resultMessage = '';
    if (isStructOk && fkViolations.length === 0) {
      resultMessage = 'Database structure is healthy. Verified all foreign key constraints with zero orphaned records.';
    } else {
      const parts = [];
      if (!isStructOk) {
        parts.push(`Structure error: ${structResult ? structResult['integrity_check'] : 'Unknown error'}`);
      }
      if (fkViolations.length > 0) {
        parts.push(`Found ${fkViolations.length} orphaned/violating records in tables: ${[...new Set(fkViolations.map(v => v.table))].join(', ')}`);
      }
      resultMessage = parts.join(' | ');
    }

    res.json({ 
      success: true, 
      result: resultMessage,
      details: {
        structure: structResult ? structResult['integrity_check'] : 'failed',
        fk_violations: fkViolations
      }
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

module.exports = router;