/**
 * SupabaseSuperAdminService.js
 * Direct integration between SuperAdmin Portal and Supabase Cloud Infrastructure.
 * Manages cloud multi-tenant database tables (tenants, tenant_licenses) and
 * exposes the read paths the SuperAdmin UI needs (cloud tenant list, cloud
 * license list, per-tenant sync state).
 *
 * Configuration is **env-only** and **anon-only**. The SuperAdmin holds the
 * Supabase *anon* key — never the service-role key. Writes go through
 * SECURITY DEFINER Postgres RPC functions (`upsert_tenant_cloud`,
 * `upsert_license_cloud`) that validate the payload and run with the
 * function owner's privileges. Reads use the anon role directly under the
 * RLS policy from migration 2026072900000000.
 *
 * Operators set SUPABASE_URL and SUPABASE_KEY (or VITE_SUPABASE_ANON_KEY)
 * in `superadmin/server/.env` and restart the process.
 */

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

const fs = require('fs');

function loadEnvIfNeeded() {
  const envPaths = [
    path.join(__dirname, '../../.env'),
    path.join(__dirname, '../.env'),
    path.join(__dirname, '../../../../.env'),
    path.join(__dirname, '../../../../server/.env')
  ];
  for (const envPath of envPaths) {
    try {
      if (fs.existsSync(envPath)) {
        const lines = fs.readFileSync(envPath, 'utf8').split(/\r?\n/);
        for (const line of lines) {
          const trimmed = line.trim();
          if (!trimmed || trimmed.startsWith('#')) continue;
          const match = trimmed.match(/^([\w.-]+)\s*=\s*(.*)$/);
          if (match) {
            const key = match[1];
            let value = match[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 (_) {}
  }
}

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

function pickEnv(...names) {
  for (const n of names) {
    const v = process.env[n];
    if (typeof v === 'string' && v.length > 0) return v;
  }
  return '';
}

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

  _init() {
    loadEnvIfNeeded();
    this.activeKeySource = 'none';

    // URL: prefer the explicit service URL, then the legacy
    // VITE_SUPABASE_URL (kept for backwards-compat with the tenant
    // Vite build), and finally the public NextGen default.
    const envUrl =
      pickEnv('SUPABASE_URL', 'VITE_SUPABASE_URL') ||
      'https://api.next_gen.techarvest.co.zw';
    this.supabaseUrl = envUrl;

    // Key: the SuperAdmin uses the **anon** key only. Writes go through
    // SECURITY DEFINER RPC functions that validate the payload before
    // performing the write; reads go through the RLS read policy from
    // migration 2026072900000000. The service-role key is intentionally
    // never read here — see SUPERADMIN_MULTITENANT_DOCUMENTATION.md §2.
    const supabaseKey = pickEnv('SUPABASE_KEY', 'VITE_SUPABASE_ANON_KEY');
    this.supabaseKey = supabaseKey;
    this.activeKeySource = supabaseKey ? 'env' : 'none';

    if (this.supabaseUrl && this.supabaseKey) {
      this.client = createClient(this.supabaseUrl, this.supabaseKey, {
        auth: { persistSession: false }
      });
      console.log(`⚡ SupabaseSuperAdminService initialized (env anon): ${this.supabaseUrl}`);
    } else {
      this.client = null;
      console.warn(
        '⚠️ Supabase credentials missing. Set SUPABASE_URL and SUPABASE_KEY ' +
        '(or VITE_SUPABASE_ANON_KEY) in superadmin/server/.env and restart the process.'
      );
    }
  }

  reinitialize() {
    this._init();
  }

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

  async getStatus() {
    const hasUrl = !!this.supabaseUrl;
    const hasKey = !!this.supabaseKey;
    const configured = hasUrl && hasKey;
    let canConnect = configured;
    if (configured) {
      try {
        const probe = await SuperAdminConfigService.validateSupabaseKey(this.supabaseUrl, this.supabaseKey);
        // Treat as connected if configured unless probe returned explicit auth error
        if (probe && probe.ok === false && (probe.error || '').toLowerCase().includes('invalid api key')) {
          canConnect = false;
        } else {
          canConnect = true;
        }
      } catch (_) {
        canConnect = true;
      }
    }
    return {
      configured,
      hasUrl,
      hasKey,
      keySource: this.activeKeySource,
      canConnect
    };
  }

  /**
   * Push a tenant row to Supabase cloud via the `upsert_tenant_cloud`
   * SECURITY DEFINER RPC. The SuperAdmin uses the anon key; the RPC
   * function validates the payload and performs the write.
   */
  async syncTenantToSupabase(tenantData) {
    if (!this.client) return null;
    try {
      const { data, error } = await this.client.rpc('upsert_tenant_cloud', {
        p_tenant: {
          id: tenantData.id,
          name: tenantData.name,
          code: tenantData.code,
          contact_email: tenantData.contact_email || null,
          phone: tenantData.phone || null,
          address: tenantData.address || null,
          status: tenantData.status || 'active'
        }
      });

      if (error) throw error;
      console.log(`[Supabase Cloud] Tenant ${tenantData.id} synced successfully`);
      return data;
    } catch (err) {
      console.error(`[Supabase Cloud] Error syncing tenant ${tenantData.id}:`, err.message);
      return null;
    }
  }

  /**
   * Push a license row to Supabase cloud via the `upsert_license_cloud`
   * SECURITY DEFINER RPC. The RPC function decodes the JWT payload and
   * asserts the `tid` claim matches `tenant_id` before persisting.
   */
  async syncLicenseToSupabase(licenseData) {
    if (!this.client) return null;
    try {
      const { data, error } = await this.client.rpc('upsert_license_cloud', {
        p_license: {
          id: licenseData.id,
          tenant_id: licenseData.tenant_id,
          license_key: licenseData.license_key,
          signed_license_token: licenseData.signed_license_token || null,
          algorithm: licenseData.algorithm || 'EdDSA',
          term_name: licenseData.term_name,
          academic_year: licenseData.academic_year,
          start_date: licenseData.start_date,
          end_date: licenseData.end_date,
          grace_period_days: licenseData.grace_period_days || 7,
          max_students: licenseData.max_students || 1000,
          max_staff: licenseData.max_staff || 200,
          status: licenseData.status || 'active',
          override_reason: licenseData.override_reason || null,
          issued_by: licenseData.issued_by || 'SuperAdmin'
        }
      });

      if (error) throw error;
      console.log(`[Supabase Cloud] License ${licenseData.id} synced successfully`);
      return data;
    } catch (err) {
      console.error(`[Supabase Cloud] Error syncing license ${licenseData.id}:`, err.message);
      return null;
    }
  }

  async fetchCloudTenants() {
    if (!this.client) return [];
    try {
      const { data, error } = await this.client.from('tenants').select('*').order('created_at', { ascending: false });
      if (error) throw error;
      return data || [];
    } catch (err) {
      console.error('[Supabase Cloud] Error fetching tenants:', err.message);
      return [];
    }
  }

  async fetchCloudLicenses() {
    if (!this.client) return [];
    try {
      const { data, error } = await this.client.from('tenant_licenses').select('*').order('created_at', { ascending: false });
      if (error) throw error;
      return data || [];
    } catch (err) {
      console.error('[Supabase Cloud] Error fetching licenses:', err.message);
      return [];
    }
  }

  async fetchCloudTenantById(id) {
    if (!this.client) return null;
    try {
      const { data, error } = await this.client
        .from('tenants')
        .select('*')
        .eq('id', id)
        .maybeSingle();
      if (error) throw error;
      return data || null;
    } catch (err) {
      console.error(`[Supabase Cloud] Error fetching tenant ${id}:`, err.message);
      return null;
    }
  }

  async fetchCloudLicensesForTenant(tenantId) {
    if (!this.client) return [];
    try {
      const { data, error } = await this.client
        .from('tenant_licenses')
        .select('*')
        .eq('tenant_id', tenantId)
        .order('created_at', { ascending: false });
      if (error) throw error;
      return data || [];
    } catch (err) {
      console.error(`[Supabase Cloud] Error fetching licenses for tenant ${tenantId}:`, err.message);
      return [];
    }
  }

  async pullAndReconcile() {
    const cloudTenants = await this.fetchCloudTenants();
    const cloudLicenses = await this.fetchCloudLicenses();

    let localTenants = [];
    let localLicenses = [];
    let dbError = null;
    let db = null;
    try {
      db = new Database(LOCAL_DB_PATH);
      db.pragma('foreign_keys = ON');
      try {
        localTenants = db.prepare('SELECT * FROM tenants').all();
      } catch (e) {
        console.warn('[pullAndReconcile] local tenants table unreadable:', e.message);
      }
      try {
        localLicenses = db.prepare('SELECT * FROM tenant_licenses').all();
      } catch (e) {
        console.warn('[pullAndReconcile] local tenant_licenses table unreadable:', e.message);
      }
    } catch (e) {
      dbError = e.message;
      console.warn(`[pullAndReconcile] local SQLite unavailable at ${LOCAL_DB_PATH}:`, e.message);
    } finally {
      if (db) {
        try { db.close(); } catch (e) { void e; }
      }
    }

    const TENANT_COMPARE_FIELDS = ['name', 'code', 'contact_email', 'phone', 'address', 'status', 'domain', 'logo_url'];
    const LICENSE_COMPARE_FIELDS = ['tenant_id', 'license_key', 'algorithm', 'term_name', 'academic_year', 'start_date', 'end_date', 'grace_period_days', 'max_students', 'max_staff', 'status', 'override_reason', 'issued_by'];

    const normalize = (val) => (val === null || val === undefined ? null : String(val).trim());

    const localTenantsById = new Map(localTenants.map(t => [t.id, t]));
    const localLicensesByKey = new Map(
      localLicenses.map(l => [`${l.id}|${l.tenant_id}|${l.start_date}`, l])
    );

    const missingLocally = [];
    const mismatchedTenants = [];
    const matchedTenantIds = new Set();

    for (const ct of cloudTenants) {
      const local = localTenantsById.get(ct.id);
      if (!local) {
        missingLocally.push(ct);
        continue;
      }
      matchedTenantIds.add(ct.id);
      const diffs = {};
      for (const field of TENANT_COMPARE_FIELDS) {
        if (normalize(local[field]) !== normalize(ct[field])) {
          diffs[field] = { local: local[field] ?? null, cloud: ct[field] ?? null };
        }
      }
      if (Object.keys(diffs).length > 0) {
        mismatchedTenants.push({ id: ct.id, name: ct.name, code: ct.code, diffs });
      }
    }

    const missingInCloud = localTenants.filter(t => !matchedTenantIds.has(t.id));

    const missingLocallyLicenses = [];
    const mismatchedLicenses = [];
    const matchedLicenseKeys = new Set();

    for (const cl of cloudLicenses) {
      const key = `${cl.id}|${cl.tenant_id}|${cl.start_date}`;
      const local = localLicensesByKey.get(key);
      if (!local) {
        missingLocallyLicenses.push(cl);
        continue;
      }
      matchedLicenseKeys.add(key);
      const diffs = {};
      for (const field of LICENSE_COMPARE_FIELDS) {
        if (normalize(local[field]) !== normalize(cl[field])) {
          diffs[field] = { local: local[field] ?? null, cloud: cl[field] ?? null };
        }
      }
      if (Object.keys(diffs).length > 0) {
        mismatchedLicenses.push({
          id: cl.id,
          tenant_id: cl.tenant_id,
          term_name: cl.term_name,
          start_date: cl.start_date,
          end_date: cl.end_date,
          diffs
        });
      }
    }

    const localLicensesKeyIndex = new Map(
      localLicenses.map(l => [`${l.id}|${l.tenant_id}|${l.start_date}`, l])
    );
    const missingInCloudLicenses = localLicenses
      .filter(l => !matchedLicenseKeys.has(`${l.id}|${l.tenant_id}|${l.start_date}`))
      .map(l => ({
        id: l.id,
        tenant_id: l.tenant_id,
        term_name: l.term_name,
        start_date: l.start_date,
        end_date: l.end_date,
        status: l.status
      }));

    const counts = {
      cloud_tenants: cloudTenants.length,
      local_tenants: localTenants.length,
      missing_locally: missingLocally.length,
      missing_in_cloud: missingInCloud.length,
      mismatched_tenants: mismatchedTenants.length,
      cloud_licenses: cloudLicenses.length,
      local_licenses: localLicenses.length,
      missing_locally_licenses: missingLocallyLicenses.length,
      missing_in_cloud_licenses: missingInCloudLicenses.length,
      mismatched_licenses: mismatchedLicenses.length
    };

    return {
      ok: true,
      generated_at: new Date().toISOString(),
      counts,
      cloudTenants,
      cloudLicenses,
      localTenants,
      localLicenses,
      missingLocally,
      missingInCloud,
      mismatchedTenants,
      missingLocallyLicenses,
      missingInCloudLicenses,
      mismatchedLicenses,
      db_path: LOCAL_DB_PATH,
      db_error: dbError,
      has_local_db: localTenants.length > 0 || localLicenses.length > 0 || dbError === null
    };
  }
}

module.exports = new SupabaseSuperAdminService();
