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

export type ClientStructureValueRecord = {
  id: string;
  law_firm_id: string;
  client_id: string;
  client_structure_node_id: string;
  value_text: string | null;
  normalized_value_json: string | null;
  source_type: string;
  source_id: string | null;
  confidence_score: number | null;
  ai_run_id: string | null;
  created_at: Date;
  updated_at: Date;
};

export type ClientStructureRepeatableValueRecord = {
  id: string;
  law_firm_id: string;
  client_id: string;
  repeat_group_node_id: string;
  repeat_instance_no: number;
  client_structure_node_id: string;
  value_text: string | null;
  normalized_value_json: string | null;
  source_type: string;
  source_id: string | null;
  confidence_score: number | null;
  ai_run_id: string | null;
  created_at: Date;
  updated_at: Date;
};

let ensureClientStructureValueSchemaPromise: 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 ensureClientStructureValueSchema() {
  if (!ensureClientStructureValueSchemaPromise) {
    ensureClientStructureValueSchemaPromise = (async () => {
      await prisma.$executeRawUnsafe(`
        CREATE TABLE IF NOT EXISTS client_data_structure_values (
          id CHAR(36) NOT NULL PRIMARY KEY,
          law_firm_id CHAR(36) NOT NULL,
          client_id CHAR(36) NOT NULL,
          client_structure_node_id CHAR(36) NOT NULL,
          value_text LONGTEXT NULL,
          normalized_value_json JSON NULL,
          source_type VARCHAR(50) NOT NULL,
          source_id CHAR(36) NULL,
          confidence_score DECIMAL(5,4) NULL,
          ai_run_id CHAR(36) NULL,
          created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          UNIQUE KEY uq_client_data_structure_values_client_node (client_id, client_structure_node_id),
          KEY idx_client_data_structure_values_client (client_id, updated_at),
          KEY idx_client_data_structure_values_node (client_structure_node_id, updated_at),
          CONSTRAINT fk_client_data_structure_values_law_firm
            FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
          CONSTRAINT fk_client_data_structure_values_client
            FOREIGN KEY (client_id) REFERENCES clients (id),
          CONSTRAINT fk_client_data_structure_values_node
            FOREIGN KEY (client_structure_node_id) REFERENCES client_data_structure_nodes (id),
          CONSTRAINT fk_client_data_structure_values_ai_run
            FOREIGN KEY (ai_run_id) REFERENCES ai_runs (id),
          CONSTRAINT chk_client_data_structure_values_confidence
            CHECK (confidence_score IS NULL OR (confidence_score >= 0.0000 AND confidence_score <= 1.0000))
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
      `);

      await prisma.$executeRawUnsafe(`
        CREATE TABLE IF NOT EXISTS client_data_structure_repeatable_values (
          id CHAR(36) NOT NULL PRIMARY KEY,
          law_firm_id CHAR(36) NOT NULL,
          client_id CHAR(36) NOT NULL,
          repeat_group_node_id CHAR(36) NOT NULL,
          repeat_instance_no INT UNSIGNED NOT NULL,
          client_structure_node_id CHAR(36) NOT NULL,
          value_text LONGTEXT NULL,
          normalized_value_json JSON NULL,
          source_type VARCHAR(50) NOT NULL,
          source_id CHAR(36) NULL,
          confidence_score DECIMAL(5,4) NULL,
          ai_run_id CHAR(36) NULL,
          created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          UNIQUE KEY uq_client_data_structure_repeatable_values_scope (
            client_id,
            repeat_group_node_id,
            repeat_instance_no,
            client_structure_node_id
          ),
          KEY idx_client_data_structure_repeatable_values_client (client_id, updated_at),
          KEY idx_client_data_structure_repeatable_values_group (
            repeat_group_node_id,
            repeat_instance_no,
            updated_at
          ),
          KEY idx_client_data_structure_repeatable_values_node (client_structure_node_id, updated_at),
          CONSTRAINT fk_client_data_structure_repeatable_values_law_firm
            FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
          CONSTRAINT fk_client_data_structure_repeatable_values_client
            FOREIGN KEY (client_id) REFERENCES clients (id),
          CONSTRAINT fk_client_data_structure_repeatable_values_group
            FOREIGN KEY (repeat_group_node_id) REFERENCES client_data_structure_nodes (id),
          CONSTRAINT fk_client_data_structure_repeatable_values_node
            FOREIGN KEY (client_structure_node_id) REFERENCES client_data_structure_nodes (id),
          CONSTRAINT fk_client_data_structure_repeatable_values_ai_run
            FOREIGN KEY (ai_run_id) REFERENCES ai_runs (id),
          CONSTRAINT chk_client_data_structure_repeatable_values_confidence
            CHECK (confidence_score IS NULL OR (confidence_score >= 0.0000 AND confidence_score <= 1.0000))
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
      `);

      const databaseName = await getCurrentDatabaseName();
      const [questionTargetColumn] = await prisma.$queryRaw<
        Array<{ column_name: string }>
      >`
        SELECT COLUMN_NAME AS column_name
        FROM information_schema.columns
        WHERE table_schema = ${databaseName}
          AND table_name = 'questionnaire_question_targets'
          AND column_name = 'client_structure_node_id'
        LIMIT 1
      `;

      if (!questionTargetColumn) {
        await prisma.$executeRawUnsafe(`
          ALTER TABLE questionnaire_question_targets
          ADD COLUMN client_structure_node_id CHAR(36) NULL AFTER data_field_id
        `);
      }

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

      if (!questionTargetIndex) {
        await prisma.$executeRawUnsafe(`
          ALTER TABLE questionnaire_question_targets
          ADD KEY idx_questionnaire_question_targets_client_structure_node (client_structure_node_id)
        `);
      }
    })().catch((error) => {
      ensureClientStructureValueSchemaPromise = null;
      throw error;
    });
  }

  await ensureClientStructureValueSchemaPromise;
}

function normalizeStoredValue(value: unknown) {
  const normalized = String(value ?? "").trim();
  return normalized || null;
}

export async function listClientStructureFieldValues(input: {
  lawFirmId: string;
  clientId: string;
}) {
  await ensureClientStructureValueSchema();

  return prisma.$queryRaw<ClientStructureValueRecord[]>`
    SELECT
      id,
      law_firm_id,
      client_id,
      client_structure_node_id,
      value_text,
      CAST(normalized_value_json AS CHAR) AS normalized_value_json,
      source_type,
      source_id,
      confidence_score,
      ai_run_id,
      created_at,
      updated_at
    FROM client_data_structure_values
    WHERE law_firm_id = ${input.lawFirmId}
      AND client_id = ${input.clientId}
    ORDER BY updated_at DESC, created_at DESC
  `;
}

export async function getClientStructureFieldValueMap(input: {
  lawFirmId: string;
  clientId: string;
}) {
  const rows = await listClientStructureFieldValues(input);
  return new Map(rows.map((row) => [row.client_structure_node_id, row] as const));
}

export async function upsertClientStructureFieldValue(input: {
  lawFirmId: string;
  clientId: string;
  clientStructureNodeId: string;
  valueText: string;
  normalizedValueJson?: string | null;
  sourceType: string;
  sourceId?: string | null;
  confidenceScore?: number | null;
  aiRunId?: string | null;
}) {
  await ensureClientStructureValueSchema();

  const valueText = normalizeStoredValue(input.valueText);
  if (!valueText) {
    return null;
  }

  const normalizedValueJson = normalizeStoredValue(input.normalizedValueJson);
  const confidenceScore =
    typeof input.confidenceScore === "number" && Number.isFinite(input.confidenceScore)
      ? Math.max(0, Math.min(1, input.confidenceScore))
      : null;

  const id = createId();
  await prisma.$executeRaw`
    INSERT INTO client_data_structure_values (
      id,
      law_firm_id,
      client_id,
      client_structure_node_id,
      value_text,
      normalized_value_json,
      source_type,
      source_id,
      confidence_score,
      ai_run_id,
      created_at,
      updated_at
    ) VALUES (
      ${id},
      ${input.lawFirmId},
      ${input.clientId},
      ${input.clientStructureNodeId},
      ${valueText},
      ${normalizedValueJson},
      ${input.sourceType},
      ${input.sourceId ?? null},
      ${confidenceScore},
      ${input.aiRunId ?? null},
      CURRENT_TIMESTAMP,
      CURRENT_TIMESTAMP
    )
    ON DUPLICATE KEY UPDATE
      value_text = VALUES(value_text),
      normalized_value_json = VALUES(normalized_value_json),
      source_type = VALUES(source_type),
      source_id = VALUES(source_id),
      confidence_score = VALUES(confidence_score),
      ai_run_id = VALUES(ai_run_id),
      updated_at = CURRENT_TIMESTAMP
  `;

  const [row] = await prisma.$queryRaw<ClientStructureValueRecord[]>`
    SELECT
      id,
      law_firm_id,
      client_id,
      client_structure_node_id,
      value_text,
      CAST(normalized_value_json AS CHAR) AS normalized_value_json,
      source_type,
      source_id,
      confidence_score,
      ai_run_id,
      created_at,
      updated_at
    FROM client_data_structure_values
    WHERE law_firm_id = ${input.lawFirmId}
      AND client_id = ${input.clientId}
      AND client_structure_node_id = ${input.clientStructureNodeId}
    LIMIT 1
  `;

  return row ?? null;
}

export async function deleteClientStructureFieldValue(input: {
  lawFirmId: string;
  clientId: string;
  clientStructureNodeId: string;
}) {
  await ensureClientStructureValueSchema();

  await prisma.$executeRaw`
    DELETE FROM client_data_structure_values
    WHERE law_firm_id = ${input.lawFirmId}
      AND client_id = ${input.clientId}
      AND client_structure_node_id = ${input.clientStructureNodeId}
  `;
}

export async function listClientStructureRepeatableValues(input: {
  lawFirmId: string;
  clientId: string;
}) {
  await ensureClientStructureValueSchema();

  return prisma.$queryRaw<ClientStructureRepeatableValueRecord[]>`
    SELECT
      id,
      law_firm_id,
      client_id,
      repeat_group_node_id,
      repeat_instance_no,
      client_structure_node_id,
      value_text,
      CAST(normalized_value_json AS CHAR) AS normalized_value_json,
      source_type,
      source_id,
      confidence_score,
      ai_run_id,
      created_at,
      updated_at
    FROM client_data_structure_repeatable_values
    WHERE law_firm_id = ${input.lawFirmId}
      AND client_id = ${input.clientId}
    ORDER BY repeat_group_node_id ASC, repeat_instance_no ASC, updated_at DESC, created_at DESC
  `;
}

export async function getClientStructureRepeatableValueMap(input: {
  lawFirmId: string;
  clientId: string;
}) {
  const rows = await listClientStructureRepeatableValues(input);
  return new Map(
    rows.map((row) => [
      `${row.repeat_group_node_id}:${row.repeat_instance_no}:${row.client_structure_node_id}`,
      row,
    ] as const),
  );
}

export async function upsertClientStructureRepeatableValue(input: {
  lawFirmId: string;
  clientId: string;
  repeatGroupNodeId: string;
  repeatInstanceNo: number;
  clientStructureNodeId: string;
  valueText: string;
  normalizedValueJson?: string | null;
  sourceType: string;
  sourceId?: string | null;
  confidenceScore?: number | null;
  aiRunId?: string | null;
}) {
  await ensureClientStructureValueSchema();

  const valueText = normalizeStoredValue(input.valueText);
  if (!valueText) {
    return null;
  }

  const repeatInstanceNo = Math.max(1, Math.trunc(Number(input.repeatInstanceNo) || 1));
  const normalizedValueJson = normalizeStoredValue(input.normalizedValueJson);
  const confidenceScore =
    typeof input.confidenceScore === "number" && Number.isFinite(input.confidenceScore)
      ? Math.max(0, Math.min(1, input.confidenceScore))
      : null;

  const id = createId();
  await prisma.$executeRaw`
    INSERT INTO client_data_structure_repeatable_values (
      id,
      law_firm_id,
      client_id,
      repeat_group_node_id,
      repeat_instance_no,
      client_structure_node_id,
      value_text,
      normalized_value_json,
      source_type,
      source_id,
      confidence_score,
      ai_run_id,
      created_at,
      updated_at
    ) VALUES (
      ${id},
      ${input.lawFirmId},
      ${input.clientId},
      ${input.repeatGroupNodeId},
      ${repeatInstanceNo},
      ${input.clientStructureNodeId},
      ${valueText},
      ${normalizedValueJson},
      ${input.sourceType},
      ${input.sourceId ?? null},
      ${confidenceScore},
      ${input.aiRunId ?? null},
      CURRENT_TIMESTAMP,
      CURRENT_TIMESTAMP
    )
    ON DUPLICATE KEY UPDATE
      value_text = VALUES(value_text),
      normalized_value_json = VALUES(normalized_value_json),
      source_type = VALUES(source_type),
      source_id = VALUES(source_id),
      confidence_score = VALUES(confidence_score),
      ai_run_id = VALUES(ai_run_id),
      updated_at = CURRENT_TIMESTAMP
  `;

  const [row] = await prisma.$queryRaw<ClientStructureRepeatableValueRecord[]>`
    SELECT
      id,
      law_firm_id,
      client_id,
      repeat_group_node_id,
      repeat_instance_no,
      client_structure_node_id,
      value_text,
      CAST(normalized_value_json AS CHAR) AS normalized_value_json,
      source_type,
      source_id,
      confidence_score,
      ai_run_id,
      created_at,
      updated_at
    FROM client_data_structure_repeatable_values
    WHERE law_firm_id = ${input.lawFirmId}
      AND client_id = ${input.clientId}
      AND repeat_group_node_id = ${input.repeatGroupNodeId}
      AND repeat_instance_no = ${repeatInstanceNo}
      AND client_structure_node_id = ${input.clientStructureNodeId}
    LIMIT 1
  `;

  return row ?? null;
}

export async function deleteClientStructureRepeatableValues(input: {
  lawFirmId: string;
  clientId: string;
  repeatGroupNodeId: string;
  repeatInstanceNo?: number | null;
}) {
  await ensureClientStructureValueSchema();

  if (typeof input.repeatInstanceNo === "number" && Number.isFinite(input.repeatInstanceNo)) {
    await prisma.$executeRaw`
      DELETE FROM client_data_structure_repeatable_values
      WHERE law_firm_id = ${input.lawFirmId}
        AND client_id = ${input.clientId}
        AND repeat_group_node_id = ${input.repeatGroupNodeId}
        AND repeat_instance_no = ${Math.max(1, Math.trunc(input.repeatInstanceNo))}
    `;
    return;
  }

  await prisma.$executeRaw`
    DELETE FROM client_data_structure_repeatable_values
    WHERE law_firm_id = ${input.lawFirmId}
      AND client_id = ${input.clientId}
      AND repeat_group_node_id = ${input.repeatGroupNodeId}
  `;
}