/* eslint-disable @typescript-eslint/no-explicit-any */
"use server";

import { sql } from 'drizzle-orm';
import { oracleKnex } from '@/lib/oracle-db/db';
import db from '@/db/db';
import { DatabaseSchema, TableName } from '@/lib/oracle-db/nonsap/oracle-schema';
import * as nonsapSchema from '@/db/schema/NONSAP/nonsap';

// 동기화할 테이블 목록 (단순화)
const TARGET_TABLES: TableName[] = ['CMCTB_CDNM', 'CMCTB_CD'];

// 간단한 로거
const logger = {
  info: (message: string, ...args: unknown[]) => console.log(`[NONSAP-SYNC] ${message}`, ...args),
  error: (message: string, ...args: unknown[]) => console.error(`[NONSAP-SYNC ERROR] ${message}`, ...args),
  warn: (message: string, ...args: unknown[]) => console.warn(`[NONSAP-SYNC WARN] ${message}`, ...args),
  success: (message: string, ...args: unknown[]) => console.log(`[NONSAP-SYNC SUCCESS] ${message}`, ...args),
};

/**
 * Oracle에서 테이블 데이터 조회 (단순화)
 */
async function fetchOracleData<T extends TableName>(tableName: T): Promise<DatabaseSchema[T][]> {
  try {
    const query = `SELECT * FROM ${tableName}`;
    const result = await oracleKnex.raw(query);

    // Oracle knex raw 결과에서 실제 데이터 추출
    const rows = Array.isArray(result) ? result : result.rows || [];
    const cleanResults = rows.map((row: any) => row);

    logger.info(`Fetched ${cleanResults.length} records from ${tableName}`);
    return cleanResults as DatabaseSchema[T][];
  } catch (error) {
    logger.error(`Error fetching data from ${tableName}:`, error);
    throw error;
  }
}

/**
 * 레코드 정규화 (컬럼명 대문자화 및 필터링)
 */
function normalizeRecord(record: any, tableSchema: any): any {
  const normalizedRecord: any = {};
  const schemaColumns = Object.keys(tableSchema);

  for (const [key, value] of Object.entries(record)) {
    if (!key || typeof key !== 'string' || key === 'undefined' || key.trim() === '') {
      continue;
    }

    // 대문자로 변환하여 스키마와 매칭
    const upperKey = key.toUpperCase();

    // 스키마에 해당 컬럼이 있는지 확인
    if (schemaColumns.includes(upperKey)) {
      normalizedRecord[upperKey] = value;
    }
  }

  return normalizedRecord;
}

/**
 * PostgreSQL에 데이터 삽입 (삭제 후 재삽입)
 */
async function syncToPostgres<T extends TableName>(
  tableName: T,
  data: DatabaseSchema[T][]
): Promise<void> {
  if (data.length === 0) return;

  try {
    // 테이블 스키마 가져오기
    const tableCamelCase = tableName.toLowerCase()
      .split('_')
      .map((word, index) => index === 0 ? word : word.charAt(0).toUpperCase() + word.slice(1))
      .join('');

    const tableSchema = (nonsapSchema as any)[tableCamelCase];

    if (!tableSchema) {
      throw new Error(`Table schema not found for ${tableName} (${tableCamelCase})`);
    }

    // 데이터 정규화
    const cleanData = data
      .map(record => normalizeRecord(record, tableSchema))
      .filter(record => Object.keys(record).length > 0);

    if (cleanData.length === 0) {
      logger.info(`${tableName} - No valid records after cleaning`);
      return;
    }

    // 1. 기존 데이터 모두 삭제
    const tableNameLower = tableName.toLowerCase();
    logger.info(`${tableName} - Deleting all existing data`);

    await db.execute(sql`
      DELETE FROM ${sql.identifier('nonsap')}.${sql.identifier(tableNameLower)}
    `);

    // 2. 새 데이터 모두 삽입
    logger.info(`${tableName} - Inserting ${cleanData.length} new records`);
    await db.insert(tableSchema as any).values(cleanData);

    logger.success(`Successfully synced ${cleanData.length} records for ${tableName}`);
  } catch (error) {
    logger.error(`Error syncing data to ${tableName}:`, error);
    throw error;
  }
}

/**
 * 테이블 동기화 (단순화)
 */
async function syncTable<T extends TableName>(tableName: T): Promise<void> {
  logger.info(`Starting sync for table: ${tableName}`);

  try {
    // Oracle에서 데이터 조회
    const oracleData = await fetchOracleData(tableName);

    if (oracleData.length === 0) {
      logger.info(`No data found for ${tableName}, skipping...`);
      return;
    }

    // PostgreSQL에 동기화 (삭제 후 재삽입)
    await syncToPostgres(tableName, oracleData);

    logger.success(`Table ${tableName} sync completed successfully`);
  } catch (error) {
    logger.error(`Table ${tableName} sync failed:`, error);
    throw error;
  }
}

/**
 * 모든 테이블 동기화 (단순화)
 */
async function syncAllTables(): Promise<void> {
  logger.info('Starting simplified NONSAP data synchronization');

  const startTime = Date.now();
  let successCount = 0;
  let errorCount = 0;

  logger.info(`Total tables to sync: ${TARGET_TABLES.length}`);

  for (const tableName of TARGET_TABLES) {
    try {
      await syncTable(tableName);
      successCount++;
    } catch (error) {
      errorCount++;
      logger.error(`Failed to sync ${tableName}:`, error);
    }
  }

  const duration = Date.now() - startTime;
  logger.info(`Sync completed in ${(duration / 1000).toFixed(2)}s`);
  logger.info(`Success: ${successCount}, Errors: ${errorCount}`);

  if (errorCount === 0) {
    logger.success('✅ All tables synchronized successfully!');
  } else {
    logger.warn(`${errorCount} tables had errors during sync`);
  }
}

/**
 * 수동 동기화 트리거 (단순화)
 */
export async function triggerSync(): Promise<void> {
  logger.info('Manual sync triggered');
  await syncAllTables();
}

/**
 * 특정 테이블 동기화
 */
export async function syncSpecificTable(tableName: TableName): Promise<void> {
  logger.info(`Manual sync triggered for table: ${tableName}`);
  await syncTable(tableName);
}