import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function main() {
  const projectId = process.argv[2];
  if (!projectId) {
    throw new Error('Usage: npx tsx scripts/backfill_equery_context.ts <projectId>');
  }

  const beforeRows = await prisma.$queryRawUnsafe<Array<{ total: bigint; missing: bigint }>>(`
    SELECT
      COUNT(*)::bigint AS total,
      COUNT(*) FILTER (
        WHERE COALESCE(event_id, '') = '' OR COALESCE(form_name, '') = ''
      )::bigint AS missing
    FROM iit_schema.equery
    WHERE project_id = $1
  `, projectId);

  // Phase 1: 严格匹配（record + rule + field）
  const strictUpdated = await prisma.$executeRawUnsafe(`
    WITH matched AS (
      SELECT
        e.id,
        fs.event_id,
        fs.form_name,
        ROW_NUMBER() OVER (
          PARTITION BY e.id
          ORDER BY fs.last_qc_at DESC NULLS LAST, fs.updated_at DESC NULLS LAST
        ) AS rn
      FROM iit_schema.equery e
      JOIN iit_schema.qc_field_status fs
        ON fs.project_id = e.project_id
       AND fs.record_id = e.record_id
       AND COALESCE(fs.rule_name, '') = COALESCE(e.category, '')
       AND COALESCE(fs.field_name, '') = COALESCE(e.field_name, '')
      WHERE e.project_id = $1
        AND (COALESCE(e.event_id, '') = '' OR COALESCE(e.form_name, '') = '')
    )
    UPDATE iit_schema.equery e
    SET
      event_id = COALESCE(NULLIF(e.event_id, ''), matched.event_id),
      form_name = COALESCE(NULLIF(e.form_name, ''), matched.form_name),
      updated_at = NOW()
    FROM matched
    WHERE e.id = matched.id
      AND matched.rn = 1
  `, projectId);

  // Phase 2: 容错匹配（record + rule），用于历史“拆字段”eQuery
  const relaxedUpdated = await prisma.$executeRawUnsafe(`
    WITH matched AS (
      SELECT
        e.id,
        fs.event_id,
        fs.form_name,
        ROW_NUMBER() OVER (
          PARTITION BY e.id
          ORDER BY fs.last_qc_at DESC NULLS LAST, fs.updated_at DESC NULLS LAST
        ) AS rn
      FROM iit_schema.equery e
      JOIN iit_schema.qc_field_status fs
        ON fs.project_id = e.project_id
       AND fs.record_id = e.record_id
       AND COALESCE(fs.rule_name, '') = COALESCE(e.category, '')
      WHERE e.project_id = $1
        AND (COALESCE(e.event_id, '') = '' OR COALESCE(e.form_name, '') = '')
    )
    UPDATE iit_schema.equery e
    SET
      event_id = COALESCE(NULLIF(e.event_id, ''), matched.event_id),
      form_name = COALESCE(NULLIF(e.form_name, ''), matched.form_name),
      updated_at = NOW()
    FROM matched
    WHERE e.id = matched.id
      AND matched.rn = 1
  `, projectId);

  const afterRows = await prisma.$queryRawUnsafe<Array<{ total: bigint; missing: bigint }>>(`
    SELECT
      COUNT(*)::bigint AS total,
      COUNT(*) FILTER (
        WHERE COALESCE(event_id, '') = '' OR COALESCE(form_name, '') = ''
      )::bigint AS missing
    FROM iit_schema.equery
    WHERE project_id = $1
  `, projectId);

  const before = beforeRows[0];
  const after = afterRows[0];
  console.log(JSON.stringify({
    projectId,
    total: Number(before.total),
    missingBefore: Number(before.missing),
    strictUpdatedRows: strictUpdated,
    relaxedUpdatedRows: relaxedUpdated,
    updatedRows: Number(strictUpdated) + Number(relaxedUpdated),
    missingAfter: Number(after.missing),
  }, null, 2));
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });