import { prisma } from "./prisma.js";

let ensureCaseRecordSchemaPromise: Promise<void> | null = null;

async function getCurrentDatabaseName() {
  const [row] = await prisma.$queryRaw<Array<{ database_name: string | null }>>`
    SELECT DATABASE() AS database_name
  `;

  const databaseName = String(row?.database_name ?? "").trim();
  if (!databaseName) {
    throw new Error("Unable to resolve current database name.");
  }

  return databaseName;
}

export async function ensureCaseRecordSchema() {
  if (!ensureCaseRecordSchemaPromise) {
    ensureCaseRecordSchemaPromise = (async () => {
      const databaseName = await getCurrentDatabaseName();
      const existingColumns = await prisma.$queryRaw<Array<{ column_name: string }>>`
        SELECT COLUMN_NAME AS column_name
        FROM information_schema.columns
        WHERE table_schema = ${databaseName}
          AND table_name = 'cases'
          AND column_name IN (
            'selected_workflow_template_id',
            'billing_principal_amount',
            'billing_down_payment_amount',
            'billing_installment_count',
            'billing_installment_due_date',
            'billing_late_fee_amount',
            'billing_monthly_interest_amount'
          )
      `;
      const existingColumnNames = new Set(
        existingColumns.map((column) => column.column_name),
      );
      const alterClauses: string[] = [];

      if (!existingColumnNames.has("selected_workflow_template_id")) {
        alterClauses.push(
          "ADD COLUMN selected_workflow_template_id CHAR(36) NULL",
        );
      }

      if (!existingColumnNames.has("billing_principal_amount")) {
        alterClauses.push(
          "ADD COLUMN billing_principal_amount DECIMAL(12, 2) NULL",
        );
      }

      if (!existingColumnNames.has("billing_down_payment_amount")) {
        alterClauses.push(
          "ADD COLUMN billing_down_payment_amount DECIMAL(12, 2) NULL",
        );
      }

      if (!existingColumnNames.has("billing_installment_count")) {
        alterClauses.push(
          "ADD COLUMN billing_installment_count INT NULL",
        );
      }

      if (!existingColumnNames.has("billing_installment_due_date")) {
        alterClauses.push(
          "ADD COLUMN billing_installment_due_date DATE NULL",
        );
      }

      if (!existingColumnNames.has("billing_late_fee_amount")) {
        alterClauses.push(
          "ADD COLUMN billing_late_fee_amount DECIMAL(12, 2) NULL",
        );
      }

      if (!existingColumnNames.has("billing_monthly_interest_amount")) {
        alterClauses.push(
          "ADD COLUMN billing_monthly_interest_amount DECIMAL(12, 2) NULL",
        );
      }

      if (alterClauses.length > 0) {
        await prisma.$executeRawUnsafe(`
          ALTER TABLE cases
          ${alterClauses.join(",\n          ")}
        `);
      }

      const [selectedWorkflowTemplateIndex] = await prisma.$queryRaw<
        Array<{ index_name: string }>
      >`
        SELECT INDEX_NAME AS index_name
        FROM information_schema.statistics
        WHERE table_schema = ${databaseName}
          AND table_name = 'cases'
          AND index_name = 'idx_cases_selected_workflow_template'
        LIMIT 1
      `;

      if (!selectedWorkflowTemplateIndex) {
        await prisma.$executeRawUnsafe(`
          ALTER TABLE cases
          ADD KEY idx_cases_selected_workflow_template (selected_workflow_template_id)
        `);
      }
    })().catch((error) => {
      ensureCaseRecordSchemaPromise = null;
      throw error;
    });
  }

  await ensureCaseRecordSchemaPromise;
}