// SPDX-FileCopyrightText: 2025-2026 Vasyl Zakharchenko
// SPDX-License-Identifier: MIT

import { ForgeSqlOrmOptions } from "..";
import { VerioningModificationForgeSQL, ForgeSqlOperation } from "./ForgeSQLQueryBuilder";
import { AnyMySqlTable } from "drizzle-orm/mysql-core";
import { and, AnyColumn, eq, InferInsertModel, SQL } from "drizzle-orm";
import { getPrimaryKeys, getTableMetadata } from "../utils";
import { saveTableIfInsideCacheContext } from "../utils/cacheContextUtils";

/**
 * Class implementing Modification operations for ForgeSQL ORM.
 * Provides methods for inserting, updating, and deleting records with support for optimistic locking.
 */
export class ForgeSQLCrudOperations implements VerioningModificationForgeSQL {
  private readonly forgeOperations: ForgeSqlOperation;
  private readonly options: ForgeSqlOrmOptions;

  /**
   * Creates a new instance of ForgeSQLCrudOperations.
   * @param forgeSqlOperations - The ForgeSQL operations instance
   * @param options - Configuration options for the ORM
   */
  constructor(forgeSqlOperations: ForgeSqlOperation, options: ForgeSqlOrmOptions) {
    this.forgeOperations = forgeSqlOperations;
    this.options = options;
  }

  /**
   * Inserts records into the database with optional versioning support.
   * If a version field exists in the schema, versioning is applied.
   *
   * This method automatically handles:
   * - Version field initialization for optimistic locking
   * - Batch insertion for multiple records
   * - Duplicate key handling with optional updates
   *
   * @template T - The type of the table schema
   * @param schema - The entity schema
   * @param models - Array of entities to insert
   * @param updateIfExists - Whether to update existing records (default: false)
   * @returns Promise that resolves to the number of inserted rows
   * @throws Error if the insert operation fails
   */
  async insert<T extends AnyMySqlTable>(
    schema: T,
    models: InferInsertModel<T>[],
    updateIfExists: boolean = false,
  ): Promise<number> {
    if (!models?.length) return 0;

    const { tableName, columns } = getTableMetadata(schema);
    const versionMetadata = this.validateVersionField(tableName, columns);

    // Prepare models with version field if needed
    const preparedModels = models.map((model) =>
      this.prepareModelWithVersion(model, versionMetadata, columns),
    );

    // Build insert query

    // eslint-disable-next-line @typescript-eslint/no-explicit-any -- Drizzle's .values() expects InferInsertModel<T>[] but `preparedModels` is a partial map for batch insert
    const queryBuilder = this.forgeOperations.insert(schema).values(preparedModels as any);

    // Add onDuplicateKeyUpdate if needed
    const finalQuery = updateIfExists
      ? queryBuilder.onDuplicateKeyUpdate({
          set: Object.fromEntries(
            // eslint-disable-next-line @typescript-eslint/no-explicit-any -- Drizzle MySqlTable columns are exposed only via symbol-indexed internal types
            Object.keys(preparedModels[0]).map((key) => [key, (schema as any)[key]]),
            // eslint-disable-next-line @typescript-eslint/no-explicit-any -- Drizzle's onDuplicateKeyUpdate.set expects a typed assignment object that is not exported
          ) as any,
        })
      : queryBuilder;

    // Execute query
    const result = await finalQuery;
    await saveTableIfInsideCacheContext(schema);
    return result[0].insertId;
  }

  /**
   * Deletes a record by its primary key with optional version check.
   * If versioning is enabled, ensures the record hasn't been modified since last read.
   *
   * This method automatically handles:
   * - Single primary key validation
   * - Optimistic locking checks if versioning is enabled
   * - Version field validation before deletion
   *
   * @template T - The type of the table schema
   * @param id - The ID of the record to delete
   * @param schema - The entity schema
   * @returns Promise that resolves to the number of affected rows
   * @throws Error if the delete operation fails
   * @throws Error if multiple primary keys are found
   * @throws Error if optimistic locking check fails
   */
  async deleteById<T extends AnyMySqlTable>(id: unknown, schema: T): Promise<number> {
    const { tableName, columns } = getTableMetadata(schema);
    const primaryKeys = this.getPrimaryKeys(schema);

    if (primaryKeys.length !== 1) {
      throw new Error("Only single primary key is supported");
    }

    const [primaryKeyName, primaryKeyColumn] = primaryKeys[0];
    const versionMetadata = this.validateVersionField(tableName, columns);

    // Build delete conditions
    const conditions: SQL<unknown>[] = [eq(primaryKeyColumn, id)];

    // Add version check if needed
    if (versionMetadata && columns) {
      const versionField = columns[versionMetadata.fieldName];
      if (versionField) {
        const oldModel = await this.getOldModel({ [primaryKeyName]: id }, schema, [
          versionMetadata.fieldName,
          versionField,
        ]);
        conditions.push(
          eq(versionField, (oldModel as Record<string, unknown>)[versionMetadata.fieldName]),
        );
      }
    }

    // Execute delete query
    const queryBuilder = this.forgeOperations.delete(schema).where(and(...conditions));

    const result = await queryBuilder;
    if (versionMetadata && result[0].affectedRows === 0) {
      throw new Error(`Optimistic locking failed: record with primary key ${id} has been modified`);
    }
    await saveTableIfInsideCacheContext(schema);
    return result[0].affectedRows;
  }

  /**
   * Updates a record by its primary key with optimistic locking support.
   * If versioning is enabled:
   * - Retrieves the current version
   * - Checks for concurrent modifications
   * - Increments the version on successful update
   *
   * This method automatically handles:
   * - Primary key validation
   * - Version field retrieval and validation
   * - Optimistic locking conflict detection
   * - Version field incrementation
   *
   * @template T - The type of the table schema
   * @param entity - The entity with updated values (must include primary key)
   * @param schema - The entity schema
   * @returns Promise that resolves to the number of affected rows
   * @throws Error if the primary key is not provided
   * @throws Error if optimistic locking check fails
   * @throws Error if multiple primary keys are found
   */
  async updateById<T extends AnyMySqlTable>(
    entity: Partial<InferInsertModel<T>>,
    schema: T,
  ): Promise<number> {
    const { tableName, columns } = getTableMetadata(schema);
    const primaryKeys = this.getPrimaryKeys(schema);

    if (primaryKeys.length !== 1) {
      throw new Error("Only single primary key is supported");
    }

    const [primaryKeyName, primaryKeyColumn] = primaryKeys[0];
    const versionMetadata = this.validateVersionField(tableName, columns);

    // Validate primary key
    if (!(primaryKeyName in entity)) {
      throw new Error(`Primary key ${primaryKeyName} must be provided in the entity`);
    }

    // Get current version if needed
    const currentVersion = await this.getCurrentVersion(
      entity,
      primaryKeyName,
      versionMetadata,
      columns,
      schema,
    );

    // Prepare update data with version
    const updateData = this.prepareUpdateData(entity, versionMetadata, columns, currentVersion);

    // Build update conditions
    const conditions: SQL<unknown>[] = [
      eq(primaryKeyColumn, entity[primaryKeyName as keyof typeof entity]),
    ];
    if (versionMetadata && columns) {
      const versionField = columns[versionMetadata.fieldName];
      if (versionField) {
        conditions.push(eq(versionField, currentVersion));
      }
    }

    // Execute update query
    const queryBuilder = this.forgeOperations
      .update(schema)

      // eslint-disable-next-line @typescript-eslint/no-explicit-any -- Drizzle's .set() type is generic over the table's update model, which our Partial<InferInsertModel<T>> with optional version field doesn't satisfy precisely
      .set(updateData as any)
      .where(and(...conditions));

    const result = await queryBuilder;
    // Check optimistic locking
    if (versionMetadata && result[0].affectedRows === 0) {
      throw new Error(
        `Optimistic locking failed: record with primary key ${entity[primaryKeyName as keyof typeof entity]} has been modified`,
      );
    }
    await saveTableIfInsideCacheContext(schema);
    return result[0].affectedRows;
  }

  /**
   * Updates specified fields of records based on provided conditions.
   * This method does not support versioning and should be used with caution.
   *
   * @template T - The type of the table schema
   * @param {Partial<InferInsertModel<T>>} updateData - The data to update
   * @param {T} schema - The entity schema
   * @param {SQL<unknown>} where - The WHERE conditions
   * @returns {Promise<number>} Number of affected rows
   * @throws {Error} If WHERE conditions are not provided
   * @throws {Error} If the update operation fails
   */
  async updateFields<T extends AnyMySqlTable>(
    updateData: Partial<InferInsertModel<T>>,
    schema: T,
    where?: SQL<unknown>,
  ): Promise<number> {
    if (!where) {
      throw new Error("WHERE conditions must be provided");
    }

    const queryBuilder = this.forgeOperations
      .update(schema)
      // eslint-disable-next-line @typescript-eslint/no-explicit-any -- Drizzle's .set() type is generic over the table's update model
      .set(updateData as any)
      .where(where);

    const result = await queryBuilder;
    await saveTableIfInsideCacheContext(schema);
    return result[0].affectedRows;
  }

  // Helper methods

  /**
   * Gets primary keys from the schema.
   * @template T - The type of the table schema
   * @param {T} schema - The table schema
   * @returns {[string, AnyColumn][]} Array of primary key name and column pairs
   * @throws {Error} If no primary keys are found
   */
  private getPrimaryKeys<T extends AnyMySqlTable>(schema: T): [string, AnyColumn][] {
    const primaryKeys = getPrimaryKeys(schema);
    if (!primaryKeys) {
      throw new Error(`No primary keys found for schema: ${schema}`);
    }

    return primaryKeys;
  }

  /**
   * Finds version field in columns by name.
   */
  private findVersionField(
    versionMetadata: { fieldName: string },
    columns: Record<string, AnyColumn>,
  ): { fieldName: string; field: AnyColumn } | null {
    let fieldName = versionMetadata.fieldName;
    let versionField = columns[versionMetadata.fieldName];

    if (!versionField) {
      const find = Object.entries(columns).find(([, c]) => c.name === versionMetadata.fieldName);
      if (find) {
        fieldName = find[0];
        versionField = find[1];
      }
    }

    return versionField ? { fieldName, field: versionField } : null;
  }

  /**
   * Validates that version field is not nullable.
   */
  private validateVersionFieldNotNull(
    versionMetadata: { fieldName: string },
    versionField: AnyColumn,
    tableName: string,
  ): boolean {
    if (!versionField.notNull) {
      // eslint-disable-next-line no-console
      console.warn(
        `Version field "${versionMetadata.fieldName}" in table ${tableName} is nullable. Versioning may not work correctly.`,
      );
      return false;
    }
    return true;
  }

  /**
   * Validates that version field type is supported.
   */
  private validateVersionFieldType(
    versionMetadata: { fieldName: string },
    fieldType: string,
    tableName: string,
  ): boolean {
    const supportedTypes = ["datetime", "timestamp", "int", "number", "decimal"];
    const isSupportedType = supportedTypes.includes(fieldType);

    if (!isSupportedType) {
      // eslint-disable-next-line no-console
      console.warn(
        `Version field "${versionMetadata.fieldName}" in table ${tableName} has unsupported type "${fieldType}". ` +
          `Only datetime, timestamp, int, and decimal types are supported for versioning. Versioning will be skipped.`,
      );
      return false;
    }
    return true;
  }

  /**
   * Validates and retrieves version field metadata.
   * @param {string} tableName - The name of the table
   * @param {Record<string, AnyColumn>} columns - The table columns
   * @returns {Object | undefined} Version field metadata if valid, undefined otherwise
   */
  private validateVersionField(
    tableName: string,
    columns: Record<string, AnyColumn>,
  ): { fieldName: string; type: string } | undefined {
    // Early exit conditions
    if (this.options.disableOptimisticLocking) {
      return undefined;
    }
    const versionMetadata = this.options.additionalMetadata?.[tableName]?.versionField;
    if (!versionMetadata) {
      return undefined;
    }

    // Find and validate version field
    const found = this.findVersionField(versionMetadata, columns);
    if (!found) {
      // eslint-disable-next-line no-console
      console.warn(
        `Version field "${versionMetadata.fieldName}" not found in table ${tableName}. Versioning will be skipped.`,
      );
      return undefined;
    }

    // Validate field properties
    const isValid =
      this.validateVersionFieldNotNull(versionMetadata, found.field, tableName) &&
      this.validateVersionFieldType(versionMetadata, found.field.getSQLType(), tableName);

    if (!isValid) {
      return undefined;
    }

    return { fieldName: found.fieldName, type: found.field.getSQLType() };
  }

  /**
   * Gets the current version of an entity.
   * @template T - The type of the table schema
   * @param {Partial<InferInsertModel<T>>} entity - The entity
   * @param {string} primaryKeyName - The name of the primary key
   * @param {Object | undefined} versionMetadata - Version field metadata
   * @param {Record<string, AnyColumn>} columns - The table columns
   * @param {T} schema - The table schema
   * @returns {Promise<unknown>} The current version value
   */
  private async getCurrentVersion<T extends AnyMySqlTable>(
    entity: Partial<InferInsertModel<T>>,
    primaryKeyName: string,
    versionMetadata: { fieldName: string; type: string } | undefined,
    columns: Record<string, AnyColumn>,
    schema: T,
  ): Promise<unknown> {
    if (!versionMetadata || !columns) return undefined;

    const versionField = columns[versionMetadata.fieldName];
    if (!versionField) return undefined;

    if (versionMetadata.fieldName in entity) {
      return entity[versionMetadata.fieldName as keyof typeof entity];
    }

    const oldModel = await this.getOldModel(
      { [primaryKeyName]: entity[primaryKeyName as keyof typeof entity] },
      schema,
      [versionMetadata.fieldName, versionField],
    );

    return (oldModel as Record<string, unknown>)[versionMetadata.fieldName];
  }

  /**
   * Prepares a model for insertion with version field.
   * @template T - The type of the table schema
   * @param {Partial<InferInsertModel<T>>} model - The model to prepare
   * @param {Object | undefined} versionMetadata - Version field metadata
   * @param {Record<string, AnyColumn>} columns - The table columns
   * @returns {InferInsertModel<T>} The prepared model
   */
  private prepareModelWithVersion<T extends AnyMySqlTable>(
    model: Partial<InferInsertModel<T>>,
    versionMetadata: { fieldName: string; type: string } | undefined,
    columns: Record<string, AnyColumn>,
  ): InferInsertModel<T> {
    if (!versionMetadata || !columns) return model as InferInsertModel<T>;
    let fieldName = versionMetadata.fieldName;
    let versionField = columns[versionMetadata.fieldName];
    if (!versionField) {
      const find = Object.entries(columns).find(([, c]) => c.name === versionMetadata.fieldName);
      if (find) {
        fieldName = find[0];
        versionField = find[1];
      }
    }

    if (!versionField) return model as InferInsertModel<T>;

    const modelWithVersion = { ...model };
    const fieldType = versionField.getSQLType();
    const dateTimeTypes = ["datetime", "timestamp"];
    const versionValue = dateTimeTypes.includes(fieldType) ? new Date() : 1;
    modelWithVersion[fieldName as keyof typeof modelWithVersion] =
      versionValue as (typeof modelWithVersion)[keyof typeof modelWithVersion];

    return modelWithVersion as InferInsertModel<T>;
  }

  /**
   * Calculates new version value based on field type.
   * Coerces `currentVersion` via `Number()` so DECIMAL columns returned as
   * strings increment instead of concatenating; non-numeric inputs propagate
   * as `NaN` (matching prior behavior for missing/invalid versions).
   */
  private calculateNewVersionValue(fieldType: string, currentVersion: unknown): Date | number {
    const dateTimeTypes = ["datetime", "timestamp"];
    return dateTimeTypes.includes(fieldType) ? new Date() : Number(currentVersion) + 1;
  }

  /**
   * Prepares update data with version field.
   * @template T - The type of the table schema
   * @param {Partial<InferInsertModel<T>>} entity - The entity to update
   * @param {Object | undefined} versionMetadata - Version field metadata
   * @param {Record<string, AnyColumn>} columns - The table columns
   * @param {unknown} currentVersion - The current version value
   * @returns {Partial<InferInsertModel<T>>} The prepared update data
   */
  private prepareUpdateData<T extends AnyMySqlTable>(
    entity: Partial<InferInsertModel<T>>,
    versionMetadata: { fieldName: string; type: string } | undefined,
    columns: Record<string, AnyColumn>,
    currentVersion: unknown,
  ): Partial<InferInsertModel<T>> {
    const updateData = { ...entity };

    if (versionMetadata && columns) {
      const versionField = columns[versionMetadata.fieldName];
      if (versionField) {
        const fieldType = versionField.getSQLType();
        updateData[versionMetadata.fieldName as keyof typeof updateData] =
          this.calculateNewVersionValue(
            fieldType,
            currentVersion,
          ) as (typeof updateData)[keyof typeof updateData];
      }
    }

    return updateData;
  }

  /**
   * Retrieves an existing model by primary key.
   * @template T - The type of the table schema
   * @param {Record<string, unknown>} primaryKeyValues - The primary key values
   * @param {T} schema - The table schema
   * @param {[string, AnyColumn]} versionField - The version field name and column
   * @returns {Promise<Awaited<T> extends unknown[] ? Awaited<T>[number] | undefined : Awaited<T> | undefined>} The existing model
   * @throws {Error} If the record is not found
   */
  private async getOldModel<T extends AnyMySqlTable>(
    primaryKeyValues: Record<string, unknown>,
    schema: T,
    versionField: [string, AnyColumn],
  ): Promise<
    Awaited<T> extends unknown[] ? Awaited<T>[number] | undefined : Awaited<T> | undefined
  > {
    const [versionFieldName, versionFieldColumn] = versionField;
    const primaryKeys = this.getPrimaryKeys(schema);
    const [primaryKeyName, primaryKeyColumn] = primaryKeys[0];

    const resultQuery = this.forgeOperations
      .select({
        // eslint-disable-next-line @typescript-eslint/no-explicit-any -- Drizzle .select() requires SQLWrapper-typed columns; AnyColumn from getPrimaryKeys/columns isn't structurally accepted here
        [primaryKeyName]: primaryKeyColumn as any,
        // eslint-disable-next-line @typescript-eslint/no-explicit-any -- Drizzle .select() requires SQLWrapper-typed columns; AnyColumn from getPrimaryKeys/columns isn't structurally accepted here
        [versionFieldName]: versionFieldColumn as any,
      })
      .from(schema)
      .where(eq(primaryKeyColumn, primaryKeyValues[primaryKeyName]));

    const model = await this.forgeOperations.fetch().executeQueryOnlyOne(resultQuery);

    if (!model) {
      throw new Error(`Record not found in table ${schema}`);
    }

    return model as Awaited<T> extends unknown[] ? Awaited<T>[number] : Awaited<T>;
  }
}