import { closeDb, query } from './db.js';
import { loadEnv } from './env.js';

loadEnv();

type CheckStatus = 'pass' | 'warn' | 'fail';

interface CheckResult {
  name: string;
  status: CheckStatus;
  count?: number;
  message: string;
}

const tenantId = process.env.TENANT_ID || '00000000-0000-0000-0000-000000000001';
const failOnWarnings = process.env.FAIL_ON_WARNINGS === 'true';

async function scalar(sql: string, params: unknown[] = []) {
  const rows = await query<{ count: string }>(sql, params);
  return Number(rows[0]?.count || 0);
}

function result(name: string, count: number, failMessage: string, passMessage: string, warnOnly = false): CheckResult {
  if (count > 0) {
    return {
      name,
      status: warnOnly ? 'warn' : 'fail',
      count,
      message: failMessage,
    };
  }
  return { name, status: 'pass', count, message: passMessage };
}

async function tableCounts(): Promise<CheckResult[]> {
  const tables = [
    'platform_users',
    'regions',
    'region_modules',
    'module_nodes',
    'schools',
    'majors',
    'subjects',
    'categories',
    'questions',
    'orders',
    'svip_plans',
    'activation_codes',
    'vocabulary_units',
    'vocabulary_words',
    'handbook_subjects',
    'handbook_chapters',
    'handbook_entries',
    'products',
  ];

  const checks: CheckResult[] = [];
  for (const table of tables) {
    const count =
      table === 'platform_users'
        ? await scalar('select count(*) from public.platform_users')
        : await scalar(`select count(*) from public.${table} where tenant_id = $1`, [tenantId]);
    checks.push({
      name: `count:${table}`,
      status: 'pass',
      count,
      message: `${table} rows: ${count}`,
    });
  }
  return checks;
}

async function validationChecks(): Promise<CheckResult[]> {
  const checks: CheckResult[] = [];

  checks.push(
    result(
      'tenant_exists',
      (await scalar('select count(*) from public.tenants where id = $1', [tenantId])) === 0 ? 1 : 0,
      'Tenant seed is missing. Run Supabase seed or set TENANT_ID to an existing tenant.',
      'Tenant exists.',
    ),
  );

  checks.push(
    result(
      'raw_records_not_normalized',
      await scalar(
        `
        select count(*)
        from public.pb_raw_records r
        join public.pb_import_runs run on run.id = r.run_id
        where r.tenant_id = $1
          and run.id = (
            select id from public.pb_import_runs
            where tenant_id = $1
            order by started_at desc
            limit 1
          )
          and r.collection_name in (
            'regions','region_modules','module_nodes','schools','majors','subjects',
            'categories','questions','users','orders','svip_plans','codes',
            'vocabulary_units','vocabulary','handbook_subjects','handbook_chapters',
            'handbook_entries','banners','faqs','announcements'
          )
          and r.normalized = false
        `,
        [tenantId],
      ),
      'Some core raw records from the latest run were not normalized.',
      'All latest-run core raw records are marked normalized.',
    ),
  );

  checks.push(
    result(
      'questions_without_current_version',
      await scalar(
        `
        select count(*)
        from public.questions
        where tenant_id = $1 and current_version_id is null
        `,
        [tenantId],
      ),
      'Questions exist without a current version.',
      'Every question has a current version.',
    ),
  );

  checks.push(
    result(
      'question_versions_wrong_tenant',
      await scalar(
        `
        select count(*)
        from public.questions q
        join public.question_versions v on v.question_id = q.id
        where q.tenant_id = $1 and v.tenant_id <> q.tenant_id
        `,
        [tenantId],
      ),
      'Some question_versions have a different tenant_id from their question.',
      'Question version tenant_id values match their questions.',
    ),
  );

  checks.push(
    result(
      'questions_unresolved_subjects',
      await scalar(
        `
        select count(*)
        from public.questions
        where tenant_id = $1 and legacy_subject_id is not null and subject_id is null
        `,
        [tenantId],
      ),
      'Some questions still have legacy_subject_id but no resolved subject_id.',
      'Question subject references are resolved.',
      true,
    ),
  );

  checks.push(
    result(
      'questions_unresolved_categories',
      await scalar(
        `
        select count(*)
        from public.questions
        where tenant_id = $1 and legacy_category_id is not null and category_id is null
        `,
        [tenantId],
      ),
      'Some questions still have legacy_category_id but no resolved category_id.',
      'Question category references are resolved.',
      true,
    ),
  );

  checks.push(
    result(
      'orders_unresolved_users',
      await scalar(
        `
        select count(*)
        from public.orders
        where tenant_id = $1 and legacy_user_id is not null and user_id is null
        `,
        [tenantId],
      ),
      'Some orders still have legacy_user_id but no resolved user_id.',
      'Order user references are resolved.',
      true,
    ),
  );

  checks.push(
    result(
      'orders_paid_without_payment',
      await scalar(
        `
        select count(*)
        from public.orders o
        where o.tenant_id = $1
          and o.status = 'paid'
          and not exists (
            select 1 from public.payments p
            where p.tenant_id = o.tenant_id and p.order_id = o.id
          )
        `,
        [tenantId],
      ),
      'Paid orders exist without payment rows.',
      'Paid orders have payment rows.',
    ),
  );

  checks.push(
    result(
      'legacy_answers_missing_user',
      await scalar(
        `
        select count(*)
        from public.answer_records ar
        left join public.platform_users u on u.id = ar.user_id
        where ar.tenant_id = $1
          and ar.legacy_id is not null
          and u.id is null
        `,
        [tenantId],
      ),
      'Legacy answer_records exist without a valid user.',
      'Legacy answer_records reference valid users.',
    ),
  );

  checks.push(
    result(
      'legacy_answers_unresolved_questions',
      await scalar(
        `
        select count(*)
        from public.answer_records
        where tenant_id = $1
          and legacy_question_id is not null
          and question_id is null
        `,
        [tenantId],
      ),
      'Some legacy answer_records kept only legacy_question_id because questions were not resolved.',
      'Legacy answer_records question references are resolved.',
      true,
    ),
  );

  checks.push(
    result(
      'wrong_questions_invalid_references',
      await scalar(
        `
        select count(*)
        from public.wrong_questions wq
        left join public.platform_users u on u.id = wq.user_id
        left join public.questions q on q.id = wq.question_id and q.tenant_id = wq.tenant_id
        where wq.tenant_id = $1
          and (u.id is null or q.id is null)
        `,
        [tenantId],
      ),
      'Wrong-question rows reference missing users or questions.',
      'Wrong-question rows reference valid users and questions.',
    ),
  );

  checks.push(
    result(
      'legacy_practice_navigation_missing_entries',
      await scalar(
        `
        select case
          when exists (
            select 1 from public.questions
            where tenant_id = $1 and legacy_id is not null
          )
          and not exists (
            select 1 from public.content_entries
            where tenant_id = $1
              and entry_type = 'question_practice'
              and legacy_id is not null
          )
          then 1 else 0 end
        `,
        [tenantId],
      ),
      'Imported legacy questions exist but no question_practice content entries were generated.',
      'Legacy question practice content entries are present when imported questions exist.',
    ),
  );

  checks.push(
    result(
      'legacy_practice_navigation_missing_nodes',
      await scalar(
        `
        select case
          when exists (
            select 1 from public.questions
            where tenant_id = $1 and legacy_id is not null
          )
          and not exists (
            select 1 from public.content_nodes
            where tenant_id = $1
              and legacy_id is not null
              and metadata->>'source' in (
                'pocketbase.module_nodes',
                'pocketbase.subjects',
                'pocketbase.categories',
                'pocketbase.migration_review.unresolved_category'
              )
          )
          then 1 else 0 end
        `,
        [tenantId],
      ),
      'Imported legacy questions exist but no content nodes were generated.',
      'Legacy content nodes are present when imported questions exist.',
    ),
  );

  checks.push(
    result(
      'legacy_practice_collections_missing_items',
      await scalar(
        `
        select count(*)
        from public.question_collections qc
        where qc.tenant_id = $1
          and qc.legacy_id is not null
          and qc.status = 'active'
          and qc.question_count = 0
          and exists (
            select 1
            from public.questions q
            left join public.content_nodes cn
              on cn.tenant_id = q.tenant_id
             and cn.id = q.content_node_id
            where q.tenant_id = qc.tenant_id
              and q.status = 'published'
              and (
                q.content_node_id = qc.node_id
                or q.category_id = qc.category_id
                or (qc.category_id is null and q.subject_id = qc.subject_id)
              )
          )
        `,
        [tenantId],
      ),
      'Active legacy question collections exist without collection items even though matching questions exist.',
      'Active legacy question collections have item counts when matching questions exist.',
    ),
  );

  checks.push(
    result(
      'legacy_questions_without_navigation',
      await scalar(
        `
        select count(*)
        from public.questions
        where tenant_id = $1
          and legacy_id is not null
          and status = 'published'
          and (
            entry_id is null
            or content_node_id is null
            or primary_collection_id is null
          )
        `,
        [tenantId],
      ),
      'Some imported published questions are not attached to entry/node/collection navigation.',
      'Imported published questions are attached to entry/node/collection navigation.',
    ),
  );

  checks.push(
    result(
      'legacy_unresolved_categories_not_isolated',
      await scalar(
        `
        select count(*)
        from public.questions q
        left join public.question_collections qc
          on qc.tenant_id = q.tenant_id
         and qc.id = q.primary_collection_id
        left join public.content_nodes cn
          on cn.tenant_id = q.tenant_id
         and cn.id = q.content_node_id
        where q.tenant_id = $1
          and q.legacy_id is not null
          and q.legacy_category_id is not null
          and q.category_id is null
          and q.node_id is null
          and (
            qc.id is null
            or qc.status <> 'draft'
            or qc.access_rules->>'requiresReview' <> 'true'
            or cn.id is null
            or cn.is_active <> false
            or cn.access_rules->>'requiresReview' <> 'true'
          )
        `,
        [tenantId],
      ),
      'Questions with unresolved legacy categories are not isolated into draft review collections/inactive nodes.',
      'Questions with unresolved legacy categories are isolated for tenant-admin review.',
    ),
  );

  checks.push(
    result(
      'legacy_orphan_node_questions_not_isolated',
      await scalar(
        `
        select count(*)
        from public.questions q
        left join public.question_collections qc
          on qc.tenant_id = q.tenant_id
         and qc.id = q.primary_collection_id
        left join public.content_nodes cn
          on cn.tenant_id = q.tenant_id
         and cn.id = q.content_node_id
        left join public.content_entries ce
          on ce.tenant_id = q.tenant_id
         and ce.id = q.entry_id
        where q.tenant_id = $1
          and q.legacy_id is not null
          and q.legacy_node_id is not null
          and q.node_id is null
          and q.category_id is null
          and q.legacy_category_id is null
          and not (
            qc.status = 'draft'
            and qc.access_rules->>'requiresReview' = 'true'
            and qc.metadata->>'source' = 'pocketbase.migration_review.orphan_subject'
            and cn.is_active = false
            and cn.access_rules->>'requiresReview' = 'true'
            and ce.visibility = 'hidden'
            and ce.access_rules->>'requiresReview' = 'true'
          )
          and not (
            qc.status = 'active'
            and qc.metadata->>'source' = 'pocketbase.subjects'
            and qc.metadata->>'scope' = 'subject_all'
            and cn.node_type = 'subject'
            and cn.marker_type = 'subject'
            and cn.is_active = true
            and ce.visibility <> 'hidden'
          )
        `,
        [tenantId],
      ),
      'Questions with unresolved legacy nodeId are neither isolated into hidden review navigation nor attached to a public subject fallback.',
      'Questions with unresolved legacy nodeId are isolated or attached to a public subject fallback.',
    ),
  );

  checks.push(
    result(
      'legacy_node_questions_wrong_navigation',
      await scalar(
        `
        select count(*)
        from public.questions q
        join public.module_nodes mn
          on mn.tenant_id = q.tenant_id
         and mn.id = q.node_id
        left join public.content_nodes cn
          on cn.tenant_id = q.tenant_id
         and cn.legacy_id = 'module_node:' || mn.legacy_id
        left join public.question_collections qc
          on qc.tenant_id = q.tenant_id
         and qc.legacy_id = 'module_node:' || mn.legacy_id || ':direct'
        where q.tenant_id = $1
          and q.legacy_id is not null
          and q.node_id is not null
          and (
            cn.id is null
            or q.content_node_id is distinct from cn.id
            or qc.id is null
            or q.primary_collection_id is distinct from qc.id
          )
        `,
        [tenantId],
      ),
      'Some imported questions with legacy nodeId are not attached to the matching content node/direct collection.',
      'Imported questions with legacy nodeId are attached to matching content nodes and direct collections.',
    ),
  );

  checks.push(
    result(
      'legacy_mock_blueprints_incomplete',
      await scalar(
        `
        select count(*)
        from public.practice_blueprints
        where tenant_id = $1
          and mode = 'mock_exam'
          and legacy_id is not null
          and (
            question_limit is null
            or jsonb_array_length(sections) = 0
            or rules->>'legacySubjectId' is null
          )
        `,
        [tenantId],
      ),
      'Legacy mock exam blueprints are missing limits, sections, or legacy subject traceability.',
      'Legacy mock exam blueprints have limits, sections, and legacy subject traceability.',
    ),
  );

  checks.push(
    result(
      'referral_qrcodes_incomplete',
      await scalar(
        `
        select count(*)
        from public.referral_qrcodes
        where tenant_id = $1
          and (ref_code is null or scene = '' or page = '')
        `,
        [tenantId],
      ),
      'Referral qrcodes are missing ref_code, scene, or page.',
      'Referral qrcodes have ref_code, scene, and page.',
    ),
  );

  checks.push(
    result(
      'commission_settings_invalid_rate',
      await scalar(
        `
        select count(*)
        from public.tenant_commission_settings
        where tenant_id = $1
          and (default_rate < 0 or default_rate > 1)
        `,
        [tenantId],
      ),
      'Tenant commission settings contain invalid default_rate values.',
      'Tenant commission settings default_rate values are valid.',
    ),
  );

  checks.push(
    result(
      'entitlements_unresolved_user',
      await scalar(
        `
        select count(*)
        from public.entitlements e
        left join public.platform_users u on u.id = e.user_id
        where e.tenant_id = $1 and u.id is null
        `,
        [tenantId],
      ),
      'Entitlements exist without a valid user.',
      'Entitlements reference valid users.',
    ),
  );

  checks.push(
    result(
      'public_sensitive_profile_keys',
      await scalar(
        `
        select count(*)
        from public.platform_users
        where raw_profile::text ~* '"(password|tokenKey|secret|sessionKey|openId|unionId|wechatSessionKey|qqOpenId|wechatOpenId|wechatUnionId)"'
        `,
      ),
      'Sensitive identity or credential keys remain in platform_users.raw_profile.',
      'No sensitive identity or credential keys found in platform_users.raw_profile.',
    ),
  );

  checks.push(
    result(
      'student_stats_legacy_arrays',
      await scalar(
        `
        select count(*)
        from public.student_profiles
        where tenant_id = $1 and (stats ? 'favorites' or stats ? 'wrongBook')
        `,
        [tenantId],
      ),
      'student_profiles.stats still contains favorites/wrongBook legacy arrays.',
      'student profile stats no longer contain favorites/wrongBook arrays.',
    ),
  );

  checks.push(
    result(
      'public_settings_sensitive_keys',
      await scalar(
        `
        select count(*)
        from public.tenant_settings
        where tenant_id = $1
          and public_config::text ~* '(secret|privatekey|sessionkey|accesskey|appkey|apikey|api_v3_key|notifytoken|aeskey)'
        `,
        [tenantId],
      ),
      'Sensitive-looking keys remain in tenant_settings.public_config.',
      'No sensitive-looking keys found in tenant public settings.',
    ),
  );

  checks.push(
    result(
      'public_crm_secret_leak',
      await scalar(
        `
        select count(*)
        from public.crm_config
        where tenant_id = $1 and secret_ref is not null and secret_ref !~ '^app_private\\.tenant_secrets:'
        `,
        [tenantId],
      ),
      'CRM config contains an unsafe secret_ref value.',
      'CRM config stores only private secret references.',
    ),
  );

  checks.push(
    result(
      'critical_import_issues',
      await scalar(
        `
        select count(*)
        from public.pb_import_issues
        where tenant_id = $1
          and severity = 'critical'
          and run_id = (
            select id from public.pb_import_runs
            where tenant_id = $1
            order by started_at desc
            limit 1
          )
        `,
        [tenantId],
      ),
      'Critical import issues exist in the latest run. Review pb_import_issues before launch.',
      'No critical import issues in the latest run.',
      true,
    ),
  );

  return checks;
}

async function main() {
  const checks = [...(await tableCounts()), ...(await validationChecks())];
  let failures = 0;
  let warnings = 0;

  for (const check of checks) {
    const prefix = check.status === 'pass' ? 'PASS' : check.status === 'warn' ? 'WARN' : 'FAIL';
    console.log(`[${prefix}] ${check.name}: ${check.message}${check.count === undefined ? '' : ` (${check.count})`}`);
    if (check.status === 'fail') failures += 1;
    if (check.status === 'warn') warnings += 1;
  }

  console.log(`Validation complete: ${failures} failures, ${warnings} warnings.`);

  if (failures > 0 || (failOnWarnings && warnings > 0)) {
    process.exitCode = 1;
  }
}

main()
  .catch(error => {
    console.error(error);
    process.exitCode = 1;
  })
  .finally(async () => {
    await closeDb();
  });
