import { createHash } from 'node:crypto';
import {
  buildSupportedMappingsForFieldNames,
  bumpMinorTemplateVersion,
  classifyTemplateField,
  computeFieldDiff,
  createSqlClient,
  extractTemplateFields,
  getEffectiveMappedKeys,
  loadTemplateSourceByOrganizationName,
  summarizeIssues,
  type TemplateMappingSeed
} from './pdf-audit-utils.ts';

type TemplateRow = {
  id: string;
  organizationId: string;
  organizationName: string;
  templateName: string;
  createdBy: string;
  updatedBy: string;
};

type TemplateVersionRow = {
  id: string;
  templateId: string;
  version: string;
  schemaJson: unknown;
  filePath: string | null;
  isActive: boolean;
  createdAt: string;
};

function computeSchemaHash(schemaJson: unknown) {
  return createHash('sha256').update(JSON.stringify(schemaJson)).digest('hex');
}

function normalizeSchemaJson(schemaJson: unknown) {
  if (typeof schemaJson === 'string') {
    return JSON.parse(schemaJson) as unknown;
  }

  return schemaJson;
}

async function upsertMapping(
  sql: any,
  organizationId: string,
  templateVersionId: string,
  mapping: TemplateMappingSeed
) {
  const existing = (
    (await sql`
      select id
      from crm_document_template_mappings
      where template_version_id = ${templateVersionId}
        and placeholder_key = ${mapping.placeholderKey}
      limit 1
    `) as Array<{ id: string }>
  )[0] ?? null;
  const mappingId = String(existing?.id ?? crypto.randomUUID());

  if (existing) {
    await sql`
      update crm_document_template_mappings
      set
        organization_id = ${organizationId},
        source_path = ${mapping.sourcePath},
        data_type = ${mapping.dataType},
        sheet_name = ${null},
        default_value = ${mapping.defaultValue ?? null},
        format_mask = ${mapping.formatMask ?? null},
        sort_order = ${mapping.sortOrder},
        deleted_at = ${null},
        updated_at = now()
      where id = ${mappingId}
    `;
  } else {
    await sql`
      insert into crm_document_template_mappings (
        id,
        organization_id,
        template_version_id,
        placeholder_key,
        source_path,
        data_type,
        sheet_name,
        default_value,
        format_mask,
        sort_order,
        deleted_at
      ) values (
        ${mappingId},
        ${organizationId},
        ${templateVersionId},
        ${mapping.placeholderKey},
        ${mapping.sourcePath},
        ${mapping.dataType},
        ${null},
        ${mapping.defaultValue ?? null},
        ${mapping.formatMask ?? null},
        ${mapping.sortOrder},
        ${null}
      )
    `;
  }

  const desiredColumnNames = new Set((mapping.columns ?? []).map((column) => column.columnName));

  for (const column of mapping.columns ?? []) {
    const existingColumn =
      (
        (await sql`
          select id
          from crm_document_template_table_columns
          where mapping_id = ${mappingId}
            and column_name = ${column.columnName}
          limit 1
        `) as Array<{ id: string }>
      )[0] ?? null;

    if (existingColumn) {
      await sql`
        update crm_document_template_table_columns
        set
          organization_id = ${organizationId},
          source_field = ${column.sourceField},
          column_letter = ${column.columnLetter ?? null},
          sort_order = ${column.sortOrder},
          format_mask = ${column.formatMask ?? null},
          deleted_at = ${null},
          updated_at = now()
        where id = ${existingColumn.id}
      `;
      continue;
    }

    await sql`
      insert into crm_document_template_table_columns (
        id,
        organization_id,
        mapping_id,
        column_name,
        source_field,
        column_letter,
        sort_order,
        format_mask,
        deleted_at
      ) values (
        ${crypto.randomUUID()},
        ${organizationId},
        ${mappingId},
        ${column.columnName},
        ${column.sourceField},
        ${column.columnLetter ?? null},
        ${column.sortOrder},
        ${column.formatMask ?? null},
        ${null}
      )
    `;
  }

  const existingColumns = (await sql`
    select id, column_name as "columnName"
    from crm_document_template_table_columns
    where mapping_id = ${mappingId}
      and deleted_at is null
  `) as Array<{ id: string; columnName: string }>;

  for (const column of existingColumns) {
    if (desiredColumnNames.has(column.columnName)) {
      continue;
    }

    await sql`
      update crm_document_template_table_columns
      set
        deleted_at = now(),
        updated_at = now()
      where id = ${column.id}
    `;
  }
}

async function main() {
  const sql = createSqlClient();

  try {
    const templates = (await sql`
      select
        t.id,
        t.organization_id as "organizationId",
        o.name as "organizationName",
        t.template_name as "templateName",
        t.created_by as "createdBy",
        t.updated_by as "updatedBy"
      from crm_document_templates t
      inner join organizations o
        on o.id = t.organization_id
      where t.document_type = 'quotation'
        and t.file_type = 'pdfme'
        and t.product_type = 'default'
        and t.deleted_at is null
      order by o.name asc, t.created_at asc
    `) as TemplateRow[];
    const results = [];

    for (const template of templates) {
      const templateSource = loadTemplateSourceByOrganizationName(template.organizationName);

      if (!templateSource) {
        results.push({
          organizationName: template.organizationName,
          templateName: template.templateName,
          status: 'WARNING',
          message: 'No source template file found for organization'
        });
        continue;
      }

      const versions = (await sql`
        select
          id,
          template_id as "templateId",
          version,
          schema_json as "schemaJson",
          file_path as "filePath",
          is_active as "isActive",
          created_at as "createdAt"
        from crm_document_template_versions
        where template_id = ${template.id}
          and deleted_at is null
        order by created_at asc
      `) as TemplateVersionRow[];
      const activeVersion = versions.find((version) => version.isActive) ?? versions.at(-1) ?? null;

      if (!activeVersion) {
        throw new Error(`Template ${template.templateName} has no version rows`);
      }

      const targetSchema = templateSource.schemaJson;
      const targetSchemaHash = computeSchemaHash(targetSchema);
      const matchingVersion =
        versions.find(
          (version) => computeSchemaHash(normalizeSchemaJson(version.schemaJson)) === targetSchemaHash
        ) ?? null;
      const previousFields = extractTemplateFields(normalizeSchemaJson(activeVersion.schemaJson));
      const targetFields = extractTemplateFields(targetSchema);
      const targetFieldNames = new Set(targetFields.map((field) => field.name).filter(Boolean));
      const targetTokens = new Set(targetFields.flatMap((field) => field.tokens));
      const supportedMappings = buildSupportedMappingsForFieldNames(targetFieldNames, targetTokens);
      const mappedKeys = getEffectiveMappedKeys(targetFields, supportedMappings);
      const fieldDiff = computeFieldDiff(targetFields, previousFields);
      const createdNewVersion = !matchingVersion;
      const targetVersionLabel = matchingVersion
        ? matchingVersion.version
        : bumpMinorTemplateVersion(versions.at(-1)?.version ?? activeVersion.version);

      const targetVersionId = matchingVersion?.id ?? crypto.randomUUID();

      await sql.begin(async (tx) => {
        if (!matchingVersion) {
          await tx`
            insert into crm_document_template_versions (
              id,
              organization_id,
              template_id,
              version,
              file_path,
              schema_json,
              preview_image_url,
              is_active,
              deleted_at,
              created_by
            ) values (
              ${targetVersionId},
              ${template.organizationId},
              ${template.id},
              ${targetVersionLabel},
              ${templateSource.relativePath},
              ${JSON.stringify(targetSchema)},
              ${null},
              ${true},
              ${null},
              ${template.updatedBy || template.createdBy}
            )
          `;
        } else {
          await tx`
            update crm_document_template_versions
            set
              file_path = ${templateSource.relativePath},
              is_active = ${true},
              updated_at = now()
            where id = ${targetVersionId}
          `;
        }

        await tx`
          update crm_document_template_versions
          set
            is_active = case when id = ${targetVersionId} then true else false end,
            updated_at = now()
          where template_id = ${template.id}
            and deleted_at is null
        `;

        for (const mapping of supportedMappings) {
          await upsertMapping(tx, template.organizationId, targetVersionId, mapping);
        }

        const desiredKeys = new Set(supportedMappings.map((mapping) => mapping.placeholderKey));
        const existingMappings = (await tx`
          select id, placeholder_key as "placeholderKey"
          from crm_document_template_mappings
          where template_version_id = ${targetVersionId}
            and deleted_at is null
        `) as Array<{ id: string; placeholderKey: string }>;

        for (const mapping of existingMappings) {
          if (desiredKeys.has(mapping.placeholderKey)) {
            continue;
          }

          await tx`
            update crm_document_template_mappings
            set
              deleted_at = now(),
              updated_at = now()
            where id = ${mapping.id}
          `;
        }
      });

      const classificationCounts = new Map<string, number>();

      for (const field of targetFields) {
        const classification = classifyTemplateField(field, mappedKeys);
        classificationCounts.set(
          classification,
          (classificationCounts.get(classification) ?? 0) + 1
        );
      }

      results.push({
        organizationName: template.organizationName,
        templateName: template.templateName,
        sourceFile: templateSource.relativePath,
        previousActiveVersion: activeVersion.version,
        activeVersion: targetVersionLabel,
        createdNewVersion,
        retainedInactiveVersions: versions.filter((version) => version.id !== targetVersionId).length,
        deletedFields: fieldDiff.deletedFields,
        newFields: fieldDiff.newFields,
        mappingKeys: supportedMappings.map((mapping) => mapping.placeholderKey),
        classificationCounts: Object.fromEntries(classificationCounts.entries()),
        status: summarizeIssues([
          fieldDiff.deletedFields.includes('exclusion_data') ? 'PASS' : 'PASS',
          supportedMappings.some((mapping) => mapping.placeholderKey === 'tel_label') ? 'PASS' : 'FAIL'
        ])
      });
    }

    const overallStatus = summarizeIssues(
      results.map((result) => result.status as 'PASS' | 'WARNING' | 'FAIL')
    );

    console.log(
      JSON.stringify(
        {
          action: 'reseed-pdf-template-version',
          overallStatus,
          templateCount: results.length,
          results
        },
        null,
        2
      )
    );
  } finally {
    await sql.end({ timeout: 5 });
  }
}

main().catch((error) => {
  console.error(`[reseed-pdf-template-version] ${error.message}`);
  process.exit(1);
});