/**
 * superadmin.controller.js
 * Controller for Standalone SuperAdmin Server (Port 3002)
 */

const crypto = require('crypto');
const Database = require('better-sqlite3');
const path = require('path');
const jwt = require('jsonwebtoken');
const { v4: uuidv4 } = require('uuid');
const LicenseTokenGenerator = require('../services/LicenseTokenGenerator');
const SupabaseSuperAdminService = require('../services/SupabaseSuperAdminService');
const { SUPERADMIN_JWT_SECRET } = require('../middleware/superAdminAuth');
const InstanceSpawner = require('../services/InstanceSpawner');

const mainDbPath = process.env.DB_PATH || path.resolve(__dirname, '../../../../server/data/school.db');

function getDb() {
  const db = new Database(mainDbPath);
  db.pragma('foreign_keys = ON');
  return db;
}

function getArtifactEncryptionKey(tenantId) {
  const secret = process.env.LICENSE_ARTIFACT_SECRET || process.env.SUPERADMIN_PUBLIC_KEY || 'nextgen-lms-licensing-secret-2026';
  return crypto.createHash('sha256').update(`${secret}:${tenantId}`).digest();
}

function encryptArtifactPayload(artifactObject) {
  const tenantId = artifactObject.tenant?.id || artifactObject.license?.tenant_id || 'default';
  const key = getArtifactEncryptionKey(tenantId);
  const iv = crypto.randomBytes(12);
  const cipher = crypto.createCipheriv('aes-256-gcm', key, iv);

  const jsonStr = JSON.stringify(artifactObject);
  const encrypted = Buffer.concat([cipher.update(jsonStr, 'utf8'), cipher.final()]);
  const authTag = cipher.getAuthTag();

  return {
    format_version: '2.0',
    encrypted: true,
    algorithm: 'AES-256-GCM + Ed25519',
    issued_at: new Date().toISOString(),
    issued_by: 'Nextgen LMS SuperAdmin Governance Portal',
    tenant_id: tenantId,
    iv: iv.toString('hex'),
    auth_tag: authTag.toString('hex'),
    ciphertext: encrypted.toString('base64'),
    checksum: crypto.createHash('sha256').update(encrypted).digest('hex')
  };
}

function buildLicenseArtifact(tenant, licenseRow) {
  const sha256 = crypto
    .createHash('sha256')
    .update(licenseRow.signed_license_token || '')
    .digest('hex');

  const rawArtifact = {
    format_version: '1.0',
    issued_at: new Date().toISOString(),
    issued_by: 'Nextgen LMS SuperAdmin Governance Portal',
    tenant: {
      id: tenant.id,
      code: tenant.code,
      name: tenant.name,
      contact_email: tenant.contact_email || null,
      phone: tenant.phone || null
    },
    license: {
      id: licenseRow.id,
      license_key: licenseRow.license_key,
      signed_license_token: licenseRow.signed_license_token,
      algorithm: licenseRow.algorithm || 'EdDSA',
      term_name: licenseRow.term_name,
      academic_year: licenseRow.academic_year,
      start_date: licenseRow.start_date,
      end_date: licenseRow.end_date,
      grace_period_days: licenseRow.grace_period_days != null ? licenseRow.grace_period_days : 7,
      max_students: licenseRow.max_students != null ? licenseRow.max_students : 1000,
      max_staff: licenseRow.max_staff != null ? licenseRow.max_staff : 200,
      status: licenseRow.status || 'active',
      issued_by: licenseRow.issued_by || 'SuperAdmin Server'
    },
    checksum: sha256
  };

  return encryptArtifactPayload(rawArtifact);
}


async function getSupabaseCloudMap() {
  const cloudTenantsMap = new Map();
  const cloudLicensesMap = new Map();
  const isConfigured = SupabaseSuperAdminService.isConfigured();
  if (isConfigured) {
    try {
      const [cloudTenants, cloudLicenses] = await Promise.all([
        SupabaseSuperAdminService.fetchCloudTenants(),
        SupabaseSuperAdminService.fetchCloudLicenses()
      ]);
      (cloudTenants || []).forEach(ct => cloudTenantsMap.set(ct.id, ct));
      (cloudLicenses || []).forEach(cl => {
        const existing = cloudLicensesMap.get(cl.tenant_id);
        if (!existing || new Date(cl.created_at || cl.end_date) > new Date(existing.created_at || existing.end_date)) {
          cloudLicensesMap.set(cl.tenant_id, cl);
        }
      });
    } catch (e) {
      console.warn('[getSupabaseCloudMap] Error fetching cloud state:', e.message);
    }
  }
  return { isConfigured, cloudTenantsMap, cloudLicensesMap };
}

/**
 * SuperAdmin Auth Login
 */
exports.login = async (req, res) => {
  const { username, password, master_key } = req.body;
  if (master_key === (process.env.SUPERADMIN_MASTER_KEY || 'SA-MASTER-KEY-2026-X99') || (username === 'superadmin' && password === 'admin123')) {
    const token = jwt.sign(
      { username: 'superadmin_master', role: 'super_admin_master' },
      SUPERADMIN_JWT_SECRET,
      { expiresIn: '24h' }
    );
    return res.json({
      token,
      user: { username: 'superadmin_master', role: 'super_admin_master' }
    });
  }
  return res.status(401).json({ error: 'Invalid SuperAdmin master credentials or key' });
};

/**
 * GET /api/metrics
 */
exports.getMetrics = async (req, res) => {
  const db = getDb();
  try {
    const totalSchools = db.prepare('SELECT COUNT(*) as cnt FROM tenants').get()?.cnt || 0;
    const activeSchools = db.prepare("SELECT COUNT(*) as cnt FROM tenants WHERE status = 'active'").get()?.cnt || 0;
    const totalUsers = db.prepare('SELECT COUNT(*) as cnt FROM users').get()?.cnt || 0;
    const totalStudents = db.prepare("SELECT COUNT(*) as cnt FROM users WHERE role = 'student'").get()?.cnt || 0;
    const totalStaff = db.prepare("SELECT COUNT(*) as cnt FROM users WHERE role IN ('teacher','principal','accountant','hr')").get()?.cnt || 0;

    const licenses = db.prepare('SELECT * FROM tenant_licenses').all();
    const activeLicenses = licenses.filter(l => new Date(l.end_date) >= new Date()).length;
    const expiredLicenses = licenses.length - activeLicenses;

    let termlyRevenueUsd = 0;
    let termlyRevenuePaymentCount = 0;
    const TERM_WINDOW_DAYS = 90;
    try {
      const termCutoff = new Date(Date.now() - TERM_WINDOW_DAYS * 24 * 60 * 60 * 1000).toISOString();
      const revenueRow = db.prepare(`
        SELECT COALESCE(SUM(amount), 0) AS total, COUNT(*) AS payment_count
        FROM payments
        WHERE status = 'completed' AND amount > 0 AND payment_date >= ?
      `).get(termCutoff);
      termlyRevenueUsd = Math.round(Number(revenueRow.total) * 100) / 100;
      termlyRevenuePaymentCount = Number(revenueRow.payment_count);
    } catch (e) {
      // payments table may not exist on fresh DBs; metric stays at 0
    }

    const { isConfigured, cloudTenantsMap, cloudLicensesMap } = await getSupabaseCloudMap();
    let syncedSchoolsCount = 0;
    let localOnlySchoolsCount = 0;
    let mismatchedSchoolsCount = 0;

    const tenantsList = db.prepare('SELECT * FROM tenants').all();
    if (isConfigured) {
      for (const t of tenantsList) {
        const license = db.prepare('SELECT * FROM tenant_licenses WHERE tenant_id = ? ORDER BY end_date DESC LIMIT 1').get(t.id);
        const cloudTenant = cloudTenantsMap.get(t.id);
        const cloudLicense = cloudLicensesMap.get(t.id);
        if (!cloudTenant) {
          localOnlySchoolsCount++;
        } else if (license && cloudLicense && license.license_key === cloudLicense.license_key && license.end_date === cloudLicense.end_date) {
          syncedSchoolsCount++;
        } else if (!license && !cloudLicense) {
          syncedSchoolsCount++;
        } else {
          mismatchedSchoolsCount++;
        }
      }
    }

    res.json({
      total_schools: totalSchools,
      active_schools: activeSchools,
      suspended_schools: totalSchools - activeSchools,
      total_learners: totalStudents,
      total_staff: totalStaff,
      total_users: totalUsers,
      active_licenses: activeLicenses,
      expired_licenses: expiredLicenses,
      termly_revenue_usd: termlyRevenueUsd,
      termly_revenue_payment_count: termlyRevenuePaymentCount,
      termly_revenue_window_days: TERM_WINDOW_DAYS,
      supabase_sync: {
        configured: isConfigured,
        synced_count: syncedSchoolsCount,
        local_only_count: localOnlySchoolsCount,
        mismatched_count: mismatchedSchoolsCount,
        cloud_tenants_total: cloudTenantsMap.size,
        cloud_licenses_total: cloudLicensesMap.size,
        status_label: isConfigured ? `${syncedSchoolsCount} / ${totalSchools} Synced to Supabase Cloud` : 'Supabase Not Configured'
      },
      server_uptime: process.uptime(),
      timestamp: new Date().toISOString()
    });
  } catch (err) {
    res.status(500).json({ error: 'Failed to fetch global metrics', details: err.message });
  } finally {
    db.close();
  }
};

/**
 * GET /api/tenants
 */
exports.listTenants = async (req, res) => {
  const db = getDb();
  try {
    const tenants = db.prepare('SELECT * FROM tenants ORDER BY created_at DESC').all();
    const { isConfigured, cloudTenantsMap, cloudLicensesMap } = await getSupabaseCloudMap();

    const result = tenants.map(t => {
      const license = db.prepare('SELECT * FROM tenant_licenses WHERE tenant_id = ? ORDER BY end_date DESC LIMIT 1').get(t.id);
      const studentCount = db.prepare("SELECT COUNT(*) as cnt FROM users WHERE tenant_id = ? AND role = 'student'").get(t.id)?.cnt || 0;
      const staffCount = db.prepare("SELECT COUNT(*) as cnt FROM users WHERE tenant_id = ? AND role IN ('teacher','principal','accountant','hr')").get(t.id)?.cnt || 0;

      const now = new Date();
      const isExpired = license ? new Date(license.end_date) < now : true;

      const cloudTenant = cloudTenantsMap.get(t.id);
      const cloudLicense = cloudLicensesMap.get(t.id);

      let supabase_status = 'unconfigured';
      if (isConfigured) {
        if (!cloudTenant) {
          supabase_status = 'local_only';
        } else if (license && cloudLicense) {
          if (license.license_key === cloudLicense.license_key && license.end_date === cloudLicense.end_date) {
            supabase_status = 'synced';
          } else {
            supabase_status = 'mismatched';
          }
        } else if (!license && !cloudLicense) {
          supabase_status = 'synced';
        } else {
          supabase_status = 'mismatched';
        }
      }

      return {
        ...t,
        student_count: studentCount,
        staff_count: staffCount,
        license: license || null,
        is_expired: isExpired,
        supabase_status,
        supabase_synced: supabase_status === 'synced',
        cloud_tenant: cloudTenant || null,
        cloud_license: cloudLicense || null
      };
    });
    res.json(result);
  } catch (err) {
    res.status(500).json({ error: 'Failed to list tenants', details: err.message });
  } finally {
    db.close();
  }
};

/**
 * POST /api/tenants (Onboard School)
 */
exports.createTenant = async (req, res) => {
  const db = getDb();
  try {
    const { id, name, code, contact_email, phone, address, max_students, max_staff, term_name, academic_year, end_date } = req.body;
    if (!name) {
      return res.status(400).json({ error: 'School name is required' });
    }
    const tenantId = (id && String(id).trim()) || uuidv4();
    const tenantCode = code || `SCH-${Math.floor(1000 + Math.random() * 9000)}`;

    db.prepare(`
      INSERT INTO tenants (id, name, code, contact_email, phone, address, status)
      VALUES (?, ?, ?, ?, ?, ?, 'active')
    `).run(tenantId, name, tenantCode, contact_email || null, phone || null, address || null);

    // Generate signed termly license token
    const now = new Date();
    const termEnd = end_date ? new Date(end_date) : new Date(now.getFullYear(), now.getMonth() + 4, now.getDate());

    const licenseSigned = LicenseTokenGenerator.generateToken({
      tenant_id: tenantId,
      tenant_code: tenantCode,
      term_name: term_name || 'Term 1',
      academic_year: academic_year || `${now.getFullYear()}`,
      start_date: now.toISOString().slice(0, 10),
      end_date: termEnd.toISOString().slice(0, 10),
      max_students: max_students || 1000,
      max_staff: max_staff || 200
    });

    const licId = `lic-${uuidv4().slice(0, 8)}`;
    db.prepare(`
      INSERT INTO tenant_licenses (
        id, tenant_id, license_key, signed_license_token, algorithm, term_name, academic_year,
        start_date, end_date, grace_period_days, max_students, max_staff,
        status, issued_by
      ) VALUES (?, ?, ?, ?, 'EdDSA', ?, ?, ?, ?, 7, ?, ?, 'active', 'SuperAdmin Server')
    `).run(
      licId,
      tenantId,
      licenseSigned.license_key,
      licenseSigned.token,
      term_name || 'Term 1',
      academic_year || `${now.getFullYear()}`,
      now.toISOString().slice(0, 10),
      termEnd.toISOString().slice(0, 10),
      max_students || 1000,
      max_staff || 200
    );

    // Sync to Supabase Cloud asynchronously
    SupabaseSuperAdminService.syncTenantToSupabase({
      id: tenantId,
      name,
      code: tenantCode,
      contact_email,
      phone,
      address
    }).catch(err => console.error('[Supabase Sync Error]:', err.message));

    SupabaseSuperAdminService.syncLicenseToSupabase({
      id: licId,
      tenant_id: tenantId,
      license_key: licenseSigned.license_key,
      signed_license_token: licenseSigned.token,
      algorithm: 'EdDSA',
      term_name: term_name || 'Term 1',
      academic_year: academic_year || `${now.getFullYear()}`,
      start_date: now.toISOString().slice(0, 10),
      end_date: termEnd.toISOString().slice(0, 10),
      max_students: max_students || 1000,
      max_staff: max_staff || 200,
      status: 'active'
    }).catch(err => console.error('[Supabase Sync Error]:', err.message));

    const createdLicenseRow = db.prepare('SELECT * FROM tenant_licenses WHERE id = ?').get(licId);
    const artifact = buildLicenseArtifact({ id: tenantId, code: tenantCode, name, contact_email, phone }, createdLicenseRow);

    res.status(201).json({
      message: 'School onboarded successfully and synced to Supabase Cloud',
      tenant_id: tenantId,
      code: tenantCode,
      license_key: licenseSigned.license_key,
      license_token: licenseSigned.token,
      artifact
    });
  } catch (err) {
    res.status(500).json({ error: 'Failed to onboard school', details: err.message });
  } finally {
    db.close();
  }
};

exports.updateTenant = async (req, res) => {
  const db = getDb();
  try {
    const { id } = req.params;
    const tenant = db.prepare('SELECT * FROM tenants WHERE id = ?').get(id);
    if (!tenant) return res.status(404).json({ ok: false, error: 'Tenant not found' });

    const allowedFields = ['name', 'code', 'contact_email', 'phone', 'address', 'status'];
    const providedFields = allowedFields.filter(field => Object.prototype.hasOwnProperty.call(req.body, field));
    if (providedFields.includes('status') && !['active', 'suspended', 'inactive'].includes(req.body.status)) {
      return res.status(400).json({ ok: false, error: 'Invalid tenant status' });
    }

    if (providedFields.length > 0) {
      const assignments = providedFields.map(field => `${field} = ?`).join(', ');
      const values = providedFields.map(field => req.body[field]);
      db.prepare(`UPDATE tenants SET ${assignments} WHERE id = ?`).run(...values, id);
    }

    const updatedTenant = db.prepare('SELECT * FROM tenants WHERE id = ?').get(id);
    await SupabaseSuperAdminService.syncTenantToSupabase(updatedTenant);
    return res.json({ ok: true, tenant: updatedTenant });
  } catch (err) {
    return res.status(500).json({ ok: false, error: err.message });
  } finally {
    db.close();
  }
};

/**
 * POST /api/tenants/:id/licenses (Issue License)
 */
exports.issueLicense = async (req, res) => {
  const db = getDb();
  try {
    const { id } = req.params;
    const { term_name, academic_year, start_date, end_date, max_students, max_staff } = req.body;
    const tenant = db.prepare('SELECT * FROM tenants WHERE id = ?').get(id);
    if (!tenant) return res.status(404).json({ error: 'Tenant not found' });

    const licenseSigned = LicenseTokenGenerator.generateToken({
      tenant_id: tenant.id,
      tenant_code: tenant.code,
      term_name: term_name || 'Term 1',
      academic_year: academic_year || '2026',
      start_date,
      end_date,
      max_students: max_students || 1000,
      max_staff: max_staff || 200
    });

    const licId = `lic-${uuidv4().slice(0, 8)}`;
    db.prepare(`
      INSERT INTO tenant_licenses (
        id, tenant_id, license_key, signed_license_token, algorithm, term_name, academic_year,
        start_date, end_date, grace_period_days, max_students, max_staff, status, issued_by
      ) VALUES (?, ?, ?, ?, 'EdDSA', ?, ?, ?, ?, 7, ?, ?, 'active', 'SuperAdmin Server')
    `).run(
      licId,
      tenant.id,
      licenseSigned.license_key,
      licenseSigned.token,
      term_name || 'Term 1',
      academic_year || '2026',
      start_date,
      end_date,
      max_students || 1000,
      max_staff || 200
    );

    SupabaseSuperAdminService.syncLicenseToSupabase({
      id: licId,
      tenant_id: tenant.id,
      license_key: licenseSigned.license_key,
      signed_license_token: licenseSigned.token,
      algorithm: 'EdDSA',
      term_name: term_name || 'Term 1',
      academic_year: academic_year || '2026',
      start_date,
      end_date,
      max_students: max_students || 1000,
      max_staff: max_staff || 200,
      status: 'active'
    }).catch(err => console.error('[Supabase Sync Error]:', err.message));

    const createdLicenseRow = db.prepare('SELECT * FROM tenant_licenses WHERE id = ?').get(licId);
    const artifact = buildLicenseArtifact(tenant, createdLicenseRow);

    res.status(201).json({
      id: licId,
      license_key: licenseSigned.license_key,
      license_token: licenseSigned.token,
      artifact
    });
  } catch (err) {
    res.status(500).json({ error: 'Failed to issue license', details: err.message });
  } finally {
    db.close();
  }
};

/**
 * GET /api/tenants/:id/licenses/download
 * GET /api/tenants/:id/licenses/:licId/download
 */
exports.downloadLicenseArtifact = async (req, res) => {
  const db = getDb();
  try {
    const { id, licId } = req.params;
    const tenant = db.prepare('SELECT * FROM tenants WHERE id = ?').get(id);
    if (!tenant) return res.status(404).json({ error: 'Tenant not found' });

    let licenseRow = null;
    if (licId) {
      licenseRow = db.prepare('SELECT * FROM tenant_licenses WHERE id = ? AND tenant_id = ?').get(licId, id);
    } else {
      licenseRow = db
        .prepare('SELECT * FROM tenant_licenses WHERE tenant_id = ? ORDER BY date(end_date) DESC, rowid DESC LIMIT 1')
        .get(id);
    }

    if (!licenseRow) {
      return res.status(404).json({ error: 'No active or valid license found for this tenant' });
    }

    const artifact = buildLicenseArtifact(tenant, licenseRow);
    const fileName = `license_${tenant.code}_${(licenseRow.term_name || 'TERM').replace(/\s+/g, '')}_${licenseRow.academic_year || '2026'}.lic.json`;

    res.setHeader('Content-Type', 'application/json');
    res.setHeader('Content-Disposition', `attachment; filename="${fileName}"`);
    return res.status(200).send(JSON.stringify(artifact, null, 2));
  } catch (err) {
    return res.status(500).json({ error: 'Failed to generate license artifact download', details: err.message });
  } finally {
    db.close();
  }
};


/**
 * POST /api/tenants/:id/licenses/repush
 *
 * Re-pushes the tenant's most-recent local license row to Supabase
 * Cloud. Used when the original issue-time sync failed (cloud was
 * down, key was rotated, etc.) and the operator wants to push the
 * current state without re-issuing a new license. The row is read
 * from local SQLite, the push uses the anon key + the
 * `upsert_license_cloud` SECURITY DEFINER RPC, and the response
 * includes both the local row and the cloud post-write state so
 * the UI can show a "synced" / "drift" indicator.
 */
exports.repushLicenseToSupabase = async (req, res) => {
  const db = getDb();
  try {
    const { id } = req.params;
    const tenant = db.prepare('SELECT * FROM tenants WHERE id = ?').get(id);
    if (!tenant) return res.status(404).json({ error: 'Tenant not found' });

    const localRow = db
      .prepare(
        'SELECT * FROM tenant_licenses WHERE tenant_id = ? ORDER BY date(end_date) DESC, rowid DESC LIMIT 1'
      )
      .get(id);
    if (!localRow) {
      return res.status(404).json({ error: 'No license to repush for this tenant' });
    }

    const pushResult = await SupabaseSuperAdminService.syncLicenseToSupabase({
      id: localRow.id,
      tenant_id: localRow.tenant_id,
      license_key: localRow.license_key,
      signed_license_token: localRow.signed_license_token,
      algorithm: localRow.algorithm || 'EdDSA',
      term_name: localRow.term_name,
      academic_year: localRow.academic_year,
      start_date: localRow.start_date,
      end_date: localRow.end_date,
      grace_period_days: localRow.grace_period_days != null ? localRow.grace_period_days : 7,
      max_students: localRow.max_students != null ? localRow.max_students : 1000,
      max_staff: localRow.max_staff != null ? localRow.max_staff : 200,
      status: localRow.status || 'active',
      override_reason: localRow.override_reason || null,
      issued_by: localRow.issued_by || 'SuperAdmin Re-push'
    });

    if (!pushResult) {
      return res.status(502).json({
        error: 'Supabase push returned no row. The service may be unconfigured or the key is invalid.',
        tenant_id: id,
        license_id: localRow.id
      });
    }

    return res.json({
      ok: true,
      tenant_id: id,
      license_id: localRow.id,
      cloud: pushResult,
      local: {
        id: localRow.id,
        license_key: localRow.license_key,
        end_date: localRow.end_date,
        status: localRow.status
      }
    });
  } catch (err) {
    return res.status(500).json({ error: 'Failed to repush license', details: err.message });
  } finally {
    db.close();
  }
};

/**
 * GET /api/tenants/:id/licenses/cloud
 *
 * Fetches the tenant's most-recent license from Supabase Cloud and
 * returns it alongside the local row, so the UI can show a
 * cloud-vs-local sync indicator without depending on the reconciliation
 * report. Returns `{ cloud: null }` when the cloud is unreachable.
 */
exports.getCloudLicenseForTenant = async (req, res) => {
  const db = getDb();
  try {
    const { id } = req.params;
    const tenant = db.prepare('SELECT * FROM tenants WHERE id = ?').get(id);
    if (!tenant) return res.status(404).json({ error: 'Tenant not found' });

    const localRow = db
      .prepare(
        'SELECT * FROM tenant_licenses WHERE tenant_id = ? ORDER BY date(end_date) DESC, rowid DESC LIMIT 1'
      )
      .get(id);

    let cloudRow = null;
    let cloudError = null;
    try {
      const list = await SupabaseSuperAdminService.fetchCloudLicensesForTenant(id);
      if (Array.isArray(list) && list.length > 0) {
        // Match by `id` first, fall back to the row with the latest end_date
        cloudRow = list.find((r) => r.id === (localRow && localRow.id)) || list[0];
      }
    } catch (err) {
      cloudError = err.message;
    }

    let syncState = 'unknown';
    if (localRow && cloudRow) {
      const localEnd = new Date(localRow.end_date).getTime();
      const cloudEnd = new Date(cloudRow.end_date).getTime();
      syncState = localRow.id === cloudRow.id && localEnd === cloudEnd
        ? 'in_sync'
        : 'drift';
    } else if (localRow && !cloudRow) {
      syncState = 'not_pushed';
    } else if (!localRow && cloudRow) {
      syncState = 'cloud_only';
    }

    return res.json({
      tenant_id: id,
      local: localRow
        ? {
            id: localRow.id,
            status: localRow.status,
            end_date: localRow.end_date,
            license_key: localRow.license_key
          }
        : null,
      cloud: cloudRow
        ? {
            id: cloudRow.id,
            status: cloudRow.status,
            end_date: cloudRow.end_date,
            license_key: cloudRow.license_key
          }
        : null,
      cloud_error: cloudError,
      sync_state: syncState
    });
  } catch (err) {
    return res.status(500).json({ error: 'Failed to inspect cloud license', details: err.message });
  } finally {
    db.close();
  }
};

/**
 * POST /api/tenants/:id/override (Emergency Access Override)
 */
exports.grantOverride = async (req, res) => {
  const db = getDb();
  try {
    const { id } = req.params;
    const { reason, extension_days } = req.body;
    if (!reason) return res.status(400).json({ error: 'Override rationale is required' });

    const tenant = db.prepare('SELECT * FROM tenants WHERE id = ?').get(id);
    if (!tenant) return res.status(404).json({ error: 'Tenant not found' });

    const now = new Date();
    const newEnd = new Date();
    newEnd.setDate(now.getDate() + parseInt(extension_days || '14'));

    const licenseSigned = LicenseTokenGenerator.generateToken({
      tenant_id: tenant.id,
      tenant_code: tenant.code,
      term_name: 'Emergency Override',
      academic_year: `${now.getFullYear()}`,
      start_date: now.toISOString().slice(0, 10),
      end_date: newEnd.toISOString().slice(0, 10),
      override_reason: reason
    });

    const licId = `lic-override-${uuidv4().slice(0, 6)}`;
    db.prepare(`
      INSERT INTO tenant_licenses (
        id, tenant_id, license_key, signed_license_token, algorithm, term_name, academic_year,
        start_date, end_date, grace_period_days, max_students, max_staff, status, override_reason, issued_by
      ) VALUES (?, ?, ?, ?, 'EdDSA', 'Emergency Override', ?, ?, ?, 0, 5000, 500, 'overridden', ?, 'SuperAdmin Emergency Override')
    `).run(
      licId,
      tenant.id,
      licenseSigned.license_key,
      licenseSigned.token,
      `${now.getFullYear()}`,
      now.toISOString().slice(0, 10),
      newEnd.toISOString().slice(0, 10),
      reason
    );

    SupabaseSuperAdminService.syncLicenseToSupabase({
      id: licId,
      tenant_id: tenant.id,
      license_key: licenseSigned.license_key,
      signed_license_token: licenseSigned.token,
      algorithm: 'EdDSA',
      term_name: 'Emergency Override',
      academic_year: `${now.getFullYear()}`,
      start_date: now.toISOString().slice(0, 10),
      end_date: newEnd.toISOString().slice(0, 10),
      max_students: 5000,
      max_staff: 500,
      status: 'overridden',
      override_reason: reason,
      issued_by: 'SuperAdmin Emergency Override'
    }).catch(err => console.error('[Supabase Sync Error]:', err.message));

    res.status(201).json({
      id: licId,
      tenant_id: tenant.id,
      license_key: licenseSigned.license_key,
      license_token: licenseSigned.token,
      override_reason: reason,
      extension_days: parseInt(extension_days || '14'),
      new_end_date: newEnd.toISOString().slice(0, 10)
    });
  } catch (err) {
    res.status(500).json({ error: 'Failed to grant override', details: err.message });
  } finally {
    db.close();
  }
};

/**
 * GET /api/instances
 */
exports.listInstances = async (req, res) => {
  try {
    const instances = InstanceSpawner.listInstances();
    res.json(instances);
  } catch (err) {
    res.status(500).json({ error: 'Failed to list instances', details: err.message });
  }
};

/**
 * POST /api/instances/spawn
 */
exports.spawnInstance = async (req, res) => {
  try {
    const { name, code, contact_email, max_students, max_staff, term_name, port_server, port_client } = req.body;
    if (!name) {
      return res.status(400).json({ error: 'School name is required' });
    }
    const instance = await InstanceSpawner.spawnSchoolInstance({
      name,
      code,
      contact_email,
      max_students,
      max_staff,
      term_name,
      port_server,
      port_client
    });
    res.status(201).json({ message: 'School instance spawned successfully', instance });
  } catch (err) {
    res.status(500).json({ error: 'Failed to spawn school instance', details: err.message });
  }
};

/**
 * POST /api/instances/:id/stop
 */
exports.stopInstance = async (req, res) => {
  try {
    const result = InstanceSpawner.stopSchoolInstance(req.params.id);
    res.json(result);
  } catch (err) {
    res.status(500).json({ error: 'Failed to stop instance', details: err.message });
  }
};

exports.startInstance = async (req, res) => {
  try {
    const result = InstanceSpawner.startSchoolInstance(req.params.id);
    res.json({ id: req.params.id, status: 'RUNNING', ...result });
  } catch (err) {
    res.status(err.message === 'Instance not found' ? 404 : 500).json({ error: err.message });
  }
};

exports.restartInstance = async (req, res) => {
  try {
    const result = InstanceSpawner.restartSchoolInstance(req.params.id);
    res.json({ id: req.params.id, status: 'RUNNING', ...result });
  } catch (err) {
    res.status(err.message === 'Instance not found' ? 404 : 500).json({ error: err.message });
  }
};

/**
 * GET /api/instances/:id/logs
 */
exports.getInstanceLogs = async (req, res) => {
  try {
    const logs = InstanceSpawner.getInstanceLogs(req.params.id);
    res.json(logs);
  } catch (err) {
    res.status(500).json({ error: 'Failed to fetch instance logs', details: err.message });
  }
};

/**
 * GET /api/tenants/:id/supabase (Inspect local vs cloud state)
 */
exports.inspectTenantSupabase = async (req, res) => {
  const db = getDb();
  try {
    const { id } = req.params;
    const tenant = db.prepare('SELECT * FROM tenants WHERE id = ?').get(id);
    if (!tenant) return res.status(404).json({ ok: false, error: 'Tenant not found locally' });

    const license = db.prepare('SELECT * FROM tenant_licenses WHERE tenant_id = ? ORDER BY end_date DESC LIMIT 1').get(id);
    const isConfigured = SupabaseSuperAdminService.isConfigured();

    let cloudTenant = null;
    let cloudLicenses = [];
    if (isConfigured) {
      cloudTenant = await SupabaseSuperAdminService.fetchCloudTenantById(id);
      cloudLicenses = await SupabaseSuperAdminService.fetchCloudLicensesForTenant(id);
    }

    const latestCloudLicense = cloudLicenses[0] || null;
    let supabase_status = 'unconfigured';
    const diffs = {};

    if (isConfigured) {
      if (!cloudTenant) {
        supabase_status = 'local_only';
      } else {
        if (tenant.name !== cloudTenant.name) diffs.name = { local: tenant.name, cloud: cloudTenant.name };
        if (tenant.code !== cloudTenant.code) diffs.code = { local: tenant.code, cloud: cloudTenant.code };
        if ((tenant.contact_email || null) !== (cloudTenant.contact_email || null)) diffs.contact_email = { local: tenant.contact_email, cloud: cloudTenant.contact_email };

        if (license && latestCloudLicense) {
          if (license.license_key !== latestCloudLicense.license_key) diffs.license_key = { local: license.license_key, cloud: latestCloudLicense.license_key };
          if (license.end_date !== latestCloudLicense.end_date) diffs.end_date = { local: license.end_date, cloud: latestCloudLicense.end_date };
        } else if (license && !latestCloudLicense) {
          diffs.license = { local: license.id, cloud: null };
        } else if (!license && latestCloudLicense) {
          diffs.license = { local: null, cloud: latestCloudLicense.id };
        }

        supabase_status = Object.keys(diffs).length === 0 ? 'synced' : 'mismatched';
      }
    }

    return res.json({
      ok: true,
      tenant_id: id,
      configured: isConfigured,
      supabase_status,
      local: { tenant, license: license || null },
      cloud: { tenant: cloudTenant, licenses: cloudLicenses, latest_license: latestCloudLicense },
      diffs
    });
  } catch (err) {
    return res.status(500).json({ ok: false, error: 'Failed to inspect tenant Supabase state', details: err.message });
  } finally {
    db.close();
  }
};

/**
 * POST /api/tenants/:id/supabase/repush (Reissue & push local tenant row to Supabase cloud)
 */
exports.repushTenantSupabase = async (req, res) => {
  const db = getDb();
  try {
    const { id } = req.params;
    const tenant = db.prepare('SELECT * FROM tenants WHERE id = ?').get(id);
    if (!tenant) return res.status(404).json({ ok: false, error: 'Tenant not found locally' });

    if (!SupabaseSuperAdminService.isConfigured()) {
      return res.status(503).json({ ok: false, error: 'Supabase client is not configured' });
    }

    let license = db.prepare('SELECT * FROM tenant_licenses WHERE tenant_id = ? ORDER BY end_date DESC LIMIT 1').get(id);

    if (!license) {
      const now = new Date();
      const termEnd = new Date(now.getFullYear(), now.getMonth() + 4, now.getDate());
      const licenseSigned = LicenseTokenGenerator.generateToken({
        tenant_id: tenant.id,
        tenant_code: tenant.code,
        term_name: 'Term 1',
        academic_year: `${now.getFullYear()}`,
        start_date: now.toISOString().slice(0, 10),
        end_date: termEnd.toISOString().slice(0, 10),
        max_students: 1000,
        max_staff: 200
      });

      const licId = `lic-${uuidv4().slice(0, 8)}`;
      db.prepare(`
        INSERT INTO tenant_licenses (
          id, tenant_id, license_key, signed_license_token, algorithm, term_name, academic_year,
          start_date, end_date, grace_period_days, max_students, max_staff, status, issued_by
        ) VALUES (?, ?, ?, ?, 'EdDSA', 'Term 1', ?, ?, ?, 7, 1000, 200, 'active', 'SuperAdmin Reissue')
      `).run(
        licId,
        tenant.id,
        licenseSigned.license_key,
        licenseSigned.token,
        `${now.getFullYear()}`,
        now.toISOString().slice(0, 10),
        termEnd.toISOString().slice(0, 10)
      );

      license = db.prepare('SELECT * FROM tenant_licenses WHERE id = ?').get(licId);
    }

    const cloudTenant = await SupabaseSuperAdminService.syncTenantToSupabase(tenant);
    const cloudLicense = await SupabaseSuperAdminService.syncLicenseToSupabase(license);

    return res.json({
      ok: true,
      message: `Tenant ${tenant.name} license successfully reissued & pushed to Supabase Cloud`,
      tenant_id: id,
      local: { tenant, license },
      cloud: { tenant: cloudTenant, license: cloudLicense }
    });
  } catch (err) {
    return res.status(500).json({ ok: false, error: 'Failed to repush tenant to Supabase', details: err.message });
  } finally {
    db.close();
  }
};

