import { readPocketBaseSchema, fieldsOf } from './pb-schema.js';

const { collections, byId, absPath } = readPocketBaseSchema();

const sensitivePattern = /(password|token|secret|private|session|openid|unionid|accesskey|appkey|apikey|apiV3Key|notifyToken|aesKey|mchId|appSecret)/i;
const likelySafeKeyPattern = /(^key$|fieldKey|tokenKey)$/i;
const jsonRiskPattern = /(stats|progress|config|settings|raw|payload|metadata|fieldValues|svipRegions|recentActivities)/i;

interface Risk {
  severity: 'critical' | 'high' | 'medium' | 'low';
  collection: string;
  field?: string;
  message: string;
}

const risks: Risk[] = [];

for (const collection of collections.filter(c => !c.name.startsWith('_'))) {
  const fields = fieldsOf(collection);

  if (collection.name === 'settings') {
    risks.push({
      severity: 'critical',
      collection: collection.name,
      message: 'Monolithic settings table mixes public config and secrets. Split into tenant_settings, tenant_payment_accounts and app_private.tenant_secrets.',
    });
  }

  if (collection.name === 'users') {
    risks.push({
      severity: 'high',
      collection: collection.name,
      message: 'Legacy users table mixes auth, profile, roles, learning stats, membership and referral data. Split before migration.',
    });
  }

  for (const field of fields) {
    if ((sensitivePattern.test(field.name) && !likelySafeKeyPattern.test(field.name)) || field.hidden) {
      risks.push({
        severity: field.hidden || sensitivePattern.test(field.name) ? 'critical' : 'high',
        collection: collection.name,
        field: field.name,
        message: `Sensitive or hidden field "${field.name}" must not be copied into public normalized tables.`,
      });
    }

    if (field.type === 'json' && jsonRiskPattern.test(field.name)) {
      risks.push({
        severity: 'medium',
        collection: collection.name,
        field: field.name,
        message: `JSON field "${field.name}" needs explicit normalization or a documented reason to remain JSONB.`,
      });
    }

    if (field.type === 'relation' && field.collectionId) {
      const target = byId.get(field.collectionId);
      if (!target) {
        risks.push({
          severity: 'high',
          collection: collection.name,
          field: field.name,
          message: `Relation points to missing collection id "${field.collectionId}".`,
        });
      }
    }
  }
}

const order: Record<Risk['severity'], number> = { critical: 0, high: 1, medium: 2, low: 3 };
risks.sort((a, b) => order[a.severity] - order[b.severity] || a.collection.localeCompare(b.collection));

console.log(`PocketBase schema risk report: ${absPath}`);
console.log(`Risks: ${risks.length}`);
console.log('');

for (const risk of risks) {
  const location = risk.field ? `${risk.collection}.${risk.field}` : risk.collection;
  console.log(`[${risk.severity.toUpperCase()}] ${location} - ${risk.message}`);
}
