// src/server/shared/db/base.service.ts
// Base Service Factory for standard CRUD operations with transaction support

import crypto from 'node:crypto';
import { db } from './client';
import { eq, and, isNull } from 'drizzle-orm';
import type { MySqlTable } from 'drizzle-orm/mysql-core';
import type { MySql2Transaction } from 'drizzle-orm/mysql2';
import { getSoftDeleteTimestamp } from './helpers';
import { mapKeysToCamel } from '../lib/case';
import type { z } from 'zod';
import { emitter } from '../lib/events';
import { loggerService } from '../lib/logger';

// Initialize logger for base service operations
const baseServiceLogger = loggerService.get('DB');

/**
 * Transaction type alias for convenience
 * Using any to avoid complex Drizzle type inference issues
 */
export type Transaction = any;

/**
 * Common table interface with soft delete support
 */
export interface BaseTable {
  id: string;
  createdAt: Date;
  updatedAt: Date;
  archivedAt: Date | null;
}

/**
 * Base service interface with standard CRUD operations
 */
export interface BaseService<T extends BaseTable> {
  findAll(tx?: Transaction): Promise<T[]>;
  findById(id: string, tx?: Transaction): Promise<T | null>;
  softDelete(id: string, tx?: Transaction): Promise<void>;
  delete(id: string, tx?: Transaction): Promise<void>;
}

/**
 * Extended base service interface with create, update, upsert operations
 * @template T - Table row type
 * @template TCreate - Create input type
 * @template TUpdate - Update input type
 * @template TOutput - Output DTO type (Zod schema output)
 */
export interface ExtendedBaseService<T extends BaseTable, TCreate, TUpdate, TOutput = T> {
  findAll(tx?: Transaction): Promise<TOutput[]>;
  findById(id: string, tx?: Transaction): Promise<TOutput | null>;
  softDelete(id: string, tx?: Transaction): Promise<void>;
  delete(id: string, tx?: Transaction): Promise<void>;
  create(data: TCreate, tx?: Transaction): Promise<TOutput>;
  update(id: string, data: TUpdate, tx?: Transaction): Promise<TOutput | null>;
  upsert(data: TCreate & { id: string }, tx?: Transaction): Promise<TOutput>;
  withTransaction<TResult>(callback: (tx: Transaction) => Promise<TResult>): Promise<TResult>;
}

/**
 * Configuration options for base service
 */
export interface BaseServiceOptions<T extends BaseTable, TOutput = T> {
  /**
   * Optional Zod schema for output transformation
   * If provided, results will be validated and transformed using this schema
   */
  outputSchema?: z.ZodType<TOutput>;
  /**
   * Enable automatic snake_case -> camelCase conversion for output
   * @default true
   */
  autoCamelCase?: boolean;
}

/**
 * Create a base service for a table with standard CRUD operations
 * 
 * @param table - Drizzle table definition
 * @returns Base service object with findAll, findById, and softDelete methods
 * 
 * @example
 * ```ts
 * const roomsService = createBaseService(rooms);
 * const allRooms = await roomsService.findAll();
 * const room = await roomsService.findById('123');
 * await roomsService.softDelete('123');
 * ```
 */
export function createBaseService<T extends BaseTable>(
  table: MySqlTable
): BaseService<T> {
  return {
    /**
      * Get all non-archived records
      * @param tx - Optional transaction object
      * @returns Array of records
      */
    async findAll(tx?: Transaction): Promise<T[]> {
      const database = tx || db;
      
      // Check if table has archivedAt column
      const hasArchivedAt = 'archivedAt' in (table as any);
      
      let query = database.select().from(table);
      
      // Only add archivedAt filter if the column exists
      if (hasArchivedAt) {
        query = query.where(isNull((table as any).archivedAt));
      }
      
      const result = await query;
      
      return result as T[];
    },

    /**
      * Find a record by ID
      * @param id - Record ID
      * @param tx - Optional transaction object
      * @returns Record or null if not found or archived
      */
    async findById(id: string, tx?: Transaction): Promise<T | null> {
      const database = tx || db;
      
      // Anti-Stupid Guard: Prevent query without ID (would return ALL rows)
      if (!id || id.trim() === '') {
        throw new Error(`CRITICAL: Attempted to find record without ID in table ${(table as any).key}. This would return ALL rows!`);
      }
      
      // Check if table has archivedAt column
      const hasArchivedAt = 'archivedAt' in (table as any);
      
      let query = database.select().from(table).where(eq((table as any).id, id));
      
      // Only add archivedAt filter if the column exists
      if (hasArchivedAt) {
        query = query.where(and(
          eq((table as any).id, id),
          isNull((table as any).archivedAt)
        ));
      }
      
      const result = await query.limit(1);
      
      return result.length > 0 ? (result[0] as T) : null;
    },

    /**
      * Soft delete a record by setting archivedAt timestamp
      * @param id - Record ID
      * @param tx - Optional transaction object
      */
    async softDelete(id: string, tx?: Transaction): Promise<void> {
      const database = tx || db;
      
      // Anti-Stupid Guard: Prevent soft delete without ID (would affect ALL rows)
      if (!id || id.trim() === '') {
        throw new Error(`CRITICAL: Attempted to soft delete record without ID in table ${(table as any).key}. This would affect ALL rows!`);
      }
      
      await database
        .update(table)
        .set({
          archivedAt: getSoftDeleteTimestamp(),
          updatedAt: new Date(),
        })
        .where(eq((table as any).id, id));
    },

    /**
      * Hard delete a record by physically removing it from the database
      * @param id - Record ID
      * @param tx - Optional transaction object
      */
    async delete(id: string, tx?: Transaction): Promise<void> {
      const database = tx || db;
      
      // Anti-Stupid Guard: Prevent hard delete without ID (would delete ALL rows)
      if (!id || id.trim() === '') {
        throw new Error(`CRITICAL: Attempted to hard delete record without ID in table ${(table as any).key}. This would delete ALL rows!`);
      }
      
      await database
        .delete(table)
        .where(eq((table as any).id, id));
    },
  };
}

/**
 * Create an extended base service with full CRUD operations
 * 
 * @param table - Drizzle table definition
 * @param options - Optional configuration including output schema
 * @returns Extended base service object with create, update, upsert, and withTransaction methods
 * 
 * @example
 * ```ts
 * const roomsService = createExtendedBaseService(rooms, {
 *   outputSchema: RoomResponseSchema,
 *   autoCamelCase: true,
 * });
 * const newRoom = await roomsService.create({ id: 'A101', type: 'стандарт' });
 * const updatedRoom = await roomsService.update('A101', { type: 'апарт' });
 * const upsertedRoom = await roomsService.upsert({ id: 'A102', type: 'стандарт' });
 * ```
 */
/**
 * Standard Event Payload structure for all events
 */
export interface StandardEventPayload<T = any> {
  action: string;
  payload: T;
  metadata: {
    timestamp: number;
    userId?: string;
  };
}

/**
 * Emit event with standard payload structure
 */
function emitStandardEvent<T>(
  tableName: string,
  action: string,
  data: T,
  userId?: string
): void {
  const eventName = `${tableName}:${action}`;
  const payload: StandardEventPayload<T> = {
    action: `${tableName}:${action}`,
    payload: data,
    metadata: {
      timestamp: Date.now(),
      userId,
    },
  };

  baseServiceLogger.debug(`Emitting event: ${eventName}`, payload);
  emitter.emit(eventName, payload);
}

export function createExtendedBaseService<
  T extends BaseTable,
  TCreate = Omit<T, 'id' | 'createdAt' | 'updatedAt' | 'archivedAt'>,
  TUpdate = Partial<TCreate>,
  TOutput = T
>(
  table: MySqlTable,
  options: BaseServiceOptions<T, TOutput> = {}
): ExtendedBaseService<T, TCreate, TUpdate, TOutput> {
  const baseService = createBaseService<T>(table);
  const { outputSchema, autoCamelCase = true } = options;
  
  // Extract table name from table definition
  const tableName = (table as any).key || 'unknown';

  /**
   * Transform raw database row to output DTO
   */
  function transformToOutput(row: any): TOutput {
    // Early return for null/undefined
    if (row === null || row === undefined) {
      return row;
    }

    let result: any = row;

    // Apply camelCase transformation if enabled
    if (autoCamelCase) {
      result = mapKeysToCamel(result);
    }

    // Convert Date objects to ISO strings before schema validation
    // This ensures Zod schemas expecting strings receive strings, not Date objects
    function convertDatesToISO(obj: any): any {
      if (obj === null || obj === undefined) {
        return obj;
      }
      
      if (obj instanceof Date) {
        return obj.toISOString();
      }
      
      if (Array.isArray(obj)) {
        return obj.map(item => convertDatesToISO(item));
      }
      
      if (typeof obj === 'object') {
        const converted: any = {};
        for (const key in obj) {
          converted[key] = convertDatesToISO(obj[key]);
        }
        return converted;
      }
      
      return obj;
    }
    
    result = convertDatesToISO(result);

    // Apply Zod schema validation if provided
    if (outputSchema) {
      return outputSchema.parse(result);
    }

    return result;
  }

  return {
    ...baseService,

    /**
      * Override findAll to return transformed output
      * @param tx - Optional transaction object
      * @returns Array of records (transformed to output DTO)
      */
    async findAll(tx?: Transaction): Promise<TOutput[]> {
      const database = tx || db;
      
      // Check if table has archivedAt column
      const hasArchivedAt = 'archivedAt' in (table as any);
      
      let query = database.select().from(table);
      
      // Only add archivedAt filter if column exists
      if (hasArchivedAt) {
        query = query.where(isNull((table as any).archivedAt));
      }
      
      const result = await query;
      
      // Transform all results
      return result.map((row: any) => transformToOutput(row));
    },

    /**
      * Override findById to return transformed output
      * @param id - Record ID
      * @param tx - Optional transaction object
      * @returns Record or null if not found or archived (transformed to output DTO)
      */
    async findById(id: string, tx?: Transaction): Promise<TOutput | null> {
      const database = tx || db;
      
      // Anti-Stupid Guard: Prevent query without ID (would return ALL rows)
      if (!id || id.trim() === '') {
        throw new Error(`CRITICAL: Attempted to find record without ID in table ${(table as any).key}. This would return ALL rows!`);
      }
      
      // Check if table has archivedAt column
      const hasArchivedAt = 'archivedAt' in (table as any);
      
      let query = database.select().from(table).where(eq((table as any).id, id));
      
      // Only add archivedAt filter if column exists
      if (hasArchivedAt) {
        query = query.where(and(
          eq((table as any).id, id),
          isNull((table as any).archivedAt)
        ));
      }
      
      const result = await query.limit(1);
      
      return result.length > 0 ? transformToOutput(result[0]) : null;
    },

    /**
      * Create a new record
      * @param data - Record data
      * @param tx - Optional transaction object
      * @returns Created record (transformed to output DTO)
      */
    async create(data: TCreate, tx?: Transaction): Promise<TOutput> {
      const database = tx || db;
      const now = new Date();

      // Extract ID if provided in data, otherwise generate a UUID
      const id = (data as any).id || crypto.randomUUID();
      
      baseServiceLogger.debug(`Creating record with ID: ${id}, table: ${(table as any).key}`);
      
      // Insert and then select by ID within same transaction
      // Note: For MySQL/MariaDB, we use insert + select instead of returning()
      // because returning() may not be supported or may cause issues
      await database
        .insert(table)
        .values({
          ...data,
          id,
          createdAt: now,
          updatedAt: now,
          archivedAt: null,
        } as any);
      
      baseServiceLogger.debug(`Insert completed, selecting record by ID: ${id}`);
      
      // Select the created record by ID (within the same transaction if provided)
      const result = await database
        .select()
        .from(table)
        .where(eq((table as any).id, id))
        .limit(1);
      
      baseServiceLogger.debug(`Select result: ${result.length > 0 ? 'found' : 'not found'}`);
      
      if (!result || result.length === 0) {
        throw new Error(`Failed to create record: ID ${id} not found after insert`);
      }
      
      const transformedOutput = transformToOutput(result[0]);
      
      // Emit creation event
      emitStandardEvent(tableName, 'created', transformedOutput);
      
      return transformedOutput;
    },

    /**
      * Update a record by ID
      * @param id - Record ID
      * @param data - Partial data to update
      * @param tx - Optional transaction object
      * @returns Updated record or null if not found (transformed to output DTO)
      */
    async update(id: string, data: TUpdate, tx?: Transaction): Promise<TOutput | null> {
      const database = tx || db;
      
      // Anti-Stupid Guard: Prevent update without ID (would affect ALL rows)
      if (!id || id.trim() === '') {
        throw new Error(`CRITICAL: Attempted to update record without ID in table ${(table as any).key}. This would affect ALL rows!`);
      }
      
      baseServiceLogger.debug(`Updating record with ID: ${id}, table: ${(table as any).key}`);
      
      // Update and then select by ID within same transaction
      // Note: For MySQL/MariaDB, we use update + select instead of returning()
      await database
        .update(table)
        .set({
          ...data,
          updatedAt: new Date(),
        } as any)
        .where(eq((table as any).id, id));
      
      baseServiceLogger.debug(`Update completed, selecting record by ID: ${id}`);
      
      // Select the updated record by ID (within the same transaction if provided)
      const result = await database
        .select()
        .from(table)
        .where(eq((table as any).id, id))
        .limit(1);
      
      baseServiceLogger.debug(`Select result: ${result.length > 0 ? 'found' : 'not found'}`);
      
      if (!result || result.length === 0) {
        return null;
      }
      
      const transformedOutput = transformToOutput(result[0]);
      
      // Emit update event
      emitStandardEvent(tableName, 'updated', transformedOutput);
      
      return transformedOutput;
    },

    /**
      * Upsert a record (insert or update if exists)
      * Uses MySQL's ON DUPLICATE KEY UPDATE
      * 
      * @param data - Record data with ID
      * @param tx - Optional transaction object
      * @returns Created or updated record (transformed to output DTO)
      */
    async upsert(data: TCreate & { id: string }, tx?: Transaction): Promise<TOutput> {
      const database = tx || db;
      const now = new Date();
      
      baseServiceLogger.debug(`Upserting record with ID: ${data.id}, table: ${(table as any).key}`);
      
      // Prepare values with timestamps
      const values = {
        ...data,
        createdAt: now,
        updatedAt: now,
        archivedAt: null,
      } as any;

      // Build update set for ON DUPLICATE KEY UPDATE
      // Exclude id, createdAt, archivedAt from update
      const updateSet: Record<string, any> = {};
      for (const key in data) {
        if (key !== 'id' && key !== 'createdAt' && key !== 'updatedAt' && key !== 'archivedAt') {
          updateSet[key] = data[key as keyof typeof data];
        }
      }
      updateSet.updatedAt = now;

      // Perform upsert using MySQL's ON DUPLICATE KEY UPDATE and then select by ID
      // Note: For MySQL/MariaDB, we use upsert + select instead of returning()
      await database
        .insert(table)
        .values(values)
        .onDuplicateKeyUpdate({ set: updateSet } as any);

      baseServiceLogger.debug(`Upsert completed, selecting record by ID: ${data.id}`);
      
      // Select the upserted record by ID (within the same transaction if provided)
      const result = await database
        .select()
        .from(table)
        .where(eq((table as any).id, data.id))
        .limit(1);

      baseServiceLogger.debug(`Select result: ${result.length > 0 ? 'found' : 'not found'}`);
      
      if (!result || result.length === 0) {
        throw new Error(`Failed to upsert record: ID ${data.id} not found after upsert`);
      }

      return transformToOutput(result[0]);
    },

    /**
      * Override softDelete to emit deletion event
      * @param id - Record ID
      * @param tx - Optional transaction object
      */
    async softDelete(id: string, tx?: Transaction): Promise<void> {
      const database = tx || db;
      
      // Anti-Stupid Guard: Prevent soft delete without ID (would affect ALL rows)
      if (!id || id.trim() === '') {
        throw new Error(`CRITICAL: Attempted to soft delete record without ID in table ${(table as any).key}. This would affect ALL rows!`);
      }
      
      // First, get the record before deletion for the event payload
      // Check if table has archivedAt column
      const hasArchivedAt = 'archivedAt' in (table as any);
      
      let query = database.select().from(table).where(eq((table as any).id, id));
      
      // Only add archivedAt filter if column exists
      if (hasArchivedAt) {
        query = query.where(and(
          eq((table as any).id, id),
          isNull((table as any).archivedAt)
        ));
      }
      
      const result = await query.limit(1);
      
      // Perform soft delete
      await database
        .update(table)
        .set({
          archivedAt: getSoftDeleteTimestamp(),
          updatedAt: new Date(),
        })
        .where(eq((table as any).id, id));
      
      // Emit deletion event if record was found
      if (result && result.length > 0) {
        const transformedOutput = transformToOutput(result[0]);
        emitStandardEvent(tableName, 'deleted', transformedOutput);
      }
    },

    /**
      * Hard delete a record by physically removing it from the database
      * @param id - Record ID
      * @param tx - Optional transaction object
      */
    async delete(id: string, tx?: Transaction): Promise<void> {
      const database = tx || db;
      
      // Anti-Stupid Guard: Prevent hard delete without ID (would delete ALL rows)
      if (!id || id.trim() === '') {
        throw new Error(`CRITICAL: Attempted to hard delete record without ID in table ${(table as any).key}. This would delete ALL rows!`);
      }
      
      await database
        .delete(table)
        .where(eq((table as any).id, id));
    },

    /**
      * Execute a callback within a transaction
      * Provides a convenient way to perform multiple operations atomically
      *
      * @param callback - Function to execute within transaction
      * @returns Result of the callback
      *
      * @example
      * ```ts
      * await roomsService.withTransaction(async (tx) => {
      *   await roomsService.update('A101', { type: 'апарт' }, tx);
      *   await someOtherService.create(..., tx);
      * });
      * ```
      */
    async withTransaction<TResult>(callback: (tx: Transaction) => Promise<TResult>): Promise<TResult> {
      return db.transaction(async (tx) => {
        return callback(tx);
      });
    },
  };
}