/**
 * SupabaseLicenseService.js
 *
 * Tenant-side Supabase client for fetching the school's signed term
 * license from Supabase cloud `tenant_licenses`. The SuperAdmin writes
 * licenses there at issuance time; this service is the read path.
 *
 * Why this lives here:
 *   • Both the SuperAdmin server and this tenant server use the
 *     Supabase **anon** key (`SUPABASE_KEY` / `VITE_SUPABASE_ANON_KEY`).
 *     No process holds the service-role key. The SuperAdmin's writes
 *     go through SECURITY DEFINER RPC functions (`upsert_tenant_cloud`,
 *     `upsert_license_cloud`) installed by migration
 *     2026072900000100_superadmin_rpc_functions.js.
 *   • The application layer scopes every read by `tenant_id` (an
 *     application-layer filter on the REST query), and the RLS policy
 *     from migration 2026072900000000_tenant_licenses_rls_read_policy.js
 *     is the defence-in-depth layer that lets the anon role SELECT
 *     (this is the documented trade-off — see the migration comment).
 *
 * Public API:
 *   • isConfigured()              → boolean
 *   • fetchActiveLicense(tenantId) → row | null
 *   • upsertLicenseCache(row)     → boolean (write-back to local SQLite)
 */

const { createClient } = require('@supabase/supabase-js');
const Database = require('better-sqlite3');
const path = require('path');
const fs = require('fs');

const DB_PATH = process.env.DB_PATH || path.join(__dirname, '..', '..', 'data', 'school.db');

const SUPABASE_URL =
  process.env.SUPABASE_URL ||
  process.env.VITE_SUPABASE_URL ||
  'https://api.next-gen.techarvest.co.zw';

const SUPABASE_KEY =
  process.env.SUPABASE_KEY ||
  process.env.SUPABASE_ANON_KEY ||
  process.env.VITE_SUPABASE_ANON_KEY ||
  '';

const FETCH_TIMEOUT_MS = 5000;

function loadEnvFromDisk() {
  // The SuperAdmin config service has its own loader; we keep this
  // small and self-contained because the tenant server might not have
  // the SuperAdmin config file. We only read the two keys we need.
  const candidates = [
    path.join(__dirname, '..', '..', '.env'),
    path.join(__dirname, '..', '..', '..', 'superadmin', 'server', '.env'),
    path.join(__dirname, '..', '..', '..', '.env'),
  ];
  for (const envPath of candidates) {
    try {
      if (!fs.existsSync(envPath)) continue;
      const text = fs.readFileSync(envPath, 'utf8');
      for (const line of text.split(/\r?\n/)) {
        const trimmed = line.trim();
        if (!trimmed || trimmed.startsWith('#')) continue;
        const m = trimmed.match(/^([\w.-]+)\s*=\s*(.*)$/);
        if (!m) continue;
        const key = m[1];
        let value = m[2] || '';
        if (
          (value.startsWith('"') && value.endsWith('"')) ||
          (value.startsWith("'") && value.endsWith("'"))
        ) {
          value = value.substring(1, value.length - 1);
        }
        if (!process.env[key]) process.env[key] = value.trim();
      }
    } catch (_) {
      // Best-effort: a server may not have a .env file in production.
    }
  }
}

function withTimeout(promise, ms, label) {
  return new Promise((resolve, reject) => {
    const timer = setTimeout(() => reject(new Error(`${label} timed out after ${ms}ms`)), ms);
    promise.then(
      (v) => {
        clearTimeout(timer);
        resolve(v);
      },
      (e) => {
        clearTimeout(timer);
        reject(e);
      }
    );
  });
}

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

class SupabaseLicenseService {
  constructor() {
    this._init();
  }

  _init() {
    loadEnvFromDisk();

    const url =
      process.env.SUPABASE_URL ||
      process.env.VITE_SUPABASE_URL ||
      SUPABASE_URL;
    const key =
      process.env.SUPABASE_KEY ||
      process.env.SUPABASE_ANON_KEY ||
      process.env.VITE_SUPABASE_ANON_KEY ||
      SUPABASE_KEY;

    this.supabaseUrl = url;
    this.supabaseKey = key;

    if (url && key) {
      this.client = createClient(url, key, {
        auth: { persistSession: false, autoRefreshToken: false },
        global: {
          // AbortController would be nicer but per-request; the
          // `withTimeout` wrapper handles it at the call site.
          fetch: (input, init = {}) =>
            fetch(input, { ...init, signal: AbortSignal.timeout(FETCH_TIMEOUT_MS) }),
        },
      });
      console.log(
        `[SupabaseLicenseService] initialized: ${url} (anon key, read-only)`
      );
    } else {
      this.client = null;
      console.warn(
        '[SupabaseLicenseService] Supabase credentials missing; middleware will fall back to local SQLite'
      );
    }
  }

  isConfigured() {
    return !!this.client;
  }

  /**
   * Fetch the active (latest end_date) license for the given tenant.
   * Returns the row or null. Throws on network/auth errors so the caller
   * can decide whether to fall back.
   */
  async fetchActiveLicense(tenantId) {
    if (!this.client) {
      throw new Error('Supabase client not configured');
    }
    if (!tenantId || typeof tenantId !== 'string') {
      throw new Error('tenantId is required');
    }

    const query = this.client
      .from('tenant_licenses')
      .select(
        '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, updated_at, created_at'
      )
      .eq('tenant_id', tenantId)
      // We accept the latest row regardless of status; the middleware
      // decides whether 'expired' / 'overridden' / 'revoked' should
      // still let mutations through. Defense-in-depth: prefer an
      // explicit `status` filter if the SuperAdmin uses one of the
      // canonical values.
      .order('end_date', { ascending: false })
      .limit(1);

    const { data, error } = await withTimeout(
      query,
      FETCH_TIMEOUT_MS,
      'SupabaseLicenseService.fetchActiveLicense'
    );

    if (error) {
      // Supabase surfaces RLS denials as a 401/403 with code "401" or
      // "PGRST301". Surface usefully so the middleware can decide.
      const code = error.code || '';
      const msg = (error.message || '').toLowerCase();
      if (
        msg.includes('invalid api key') ||
        msg.includes('invalid jwt') ||
        code === '401' ||
        code === 'PGRST301'
      ) {
        const err = new Error(
          'Supabase rejected the anon key (401). Check SUPABASE_KEY / VITE_SUPABASE_ANON_KEY in server .env.'
        );
        err.code = 'SUPABASE_AUTH';
        throw err;
      }
      throw new Error(`Supabase fetch failed: ${error.message || error.code}`);
    }
    if (!data || data.length === 0) return null;
    return data[0];
  }

  /**
   * Write the fetched Supabase row back into the tenant's local SQLite
   * `tenant_licenses` table. The table shape may differ slightly across
   * migrations (older migrations used `tenant_id TEXT`; newer migrations
   * use `tenant_id UUID`). We use a defensive UPSERT that:
   *   • binds the values from the Supabase row
   *   • ignores the row if the local table doesn't have the column
   *   • coalesces optional fields to safe defaults
   * Returns true on success, false on any failure (logged, never thrown).
   */
  upsertLicenseCache(row) {
    if (!row || !row.id || !row.tenant_id) return false;
    let db = null;
    try {
      db = openLocalDb();
      const hasTable = db
        .prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='tenant_licenses'")
        .get();
      if (!hasTable) return false;

      // Discover the columns the local table actually has. Older
      // migrations added `signed_license_token` and `algorithm` in
      // 2026072300000000; the bare minimum is the set we want.
      const cols = db.prepare("PRAGMA table_info(tenant_licenses)").all();
      const colSet = new Set(cols.map((c) => c.name));

      // Build a dynamic UPSERT that only references existing columns.
      const fieldMap = {
        id: row.id,
        tenant_id: row.tenant_id,
        license_key: row.license_key,
        signed_license_token: row.signed_license_token,
        algorithm: row.algorithm || 'EdDSA',
        term_name: row.term_name,
        academic_year: row.academic_year,
        start_date: row.start_date,
        end_date: row.end_date,
        grace_period_days: row.grace_period_days != null ? row.grace_period_days : 7,
        max_students: row.max_students != null ? row.max_students : 1000,
        max_staff: row.max_staff != null ? row.max_staff : 200,
        status: row.status || 'active',
        override_reason: row.override_reason || null,
        issued_by: row.issued_by || 'SuperAdmin Cloud',
      };
      const insertCols = Object.keys(fieldMap).filter((k) => colSet.has(k));
      if (insertCols.length === 0) return false;

      const placeholders = insertCols.map(() => '?').join(', ');
      const updateClauses = insertCols
        .filter((c) => c !== 'id')
        .map((c) => `${c} = excluded.${c}`)
        .join(', ');

      const stmt = db.prepare(
        `INSERT INTO tenant_licenses (${insertCols.join(', ')})
         VALUES (${placeholders})
         ON CONFLICT(id) DO UPDATE SET ${updateClauses}`
      );
      const values = insertCols.map((c) => fieldMap[c]);
      stmt.run(...values);
      return true;
    } catch (err) {
      console.warn('[SupabaseLicenseService] upsertLicenseCache failed:', err.message);
      return false;
    } finally {
      if (db) {
        try {
          db.close();
        } catch (_) {}
      }
    }
  }
}

module.exports = new SupabaseLicenseService();
