import { prisma } from '@/lib/prisma';

/**
 * 素材操作日志表是否已初始化
 */
let isInitialized = false;

/**
 * 执行原始 SQL（用于创建表和索引）
 */
async function executeRawSQL(sql: string) {
  try {
    await prisma.$executeRawUnsafe(sql);
  } catch (error) {
    console.error('[MaterialLog] Failed to execute SQL:', error);
  }
}

/**
 * 初始化素材日志表
 * 如果表不存在，则创建表和索引
 */
async function initializeMaterialLogTable() {
  if (isInitialized) return;
  
  // 创建表
  await executeRawSQL(`
    CREATE TABLE IF NOT EXISTS "material_logs" (
      "id" TEXT PRIMARY KEY,
      "materialId" TEXT NOT NULL,
      "userId" TEXT NOT NULL,
      "action" TEXT NOT NULL,
      "changes" JSONB,
      "timestamp" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP
    )
  `);
  
  // 创建索引
  await executeRawSQL(
    'CREATE INDEX IF NOT EXISTS "material_logs_materialId_idx" ON "material_logs" ("materialId")'
  );
  await executeRawSQL(
    'CREATE INDEX IF NOT EXISTS "material_logs_userId_idx" ON "material_logs" ("userId")'
  );
  await executeRawSQL(
    'CREATE INDEX IF NOT EXISTS "material_logs_timestamp_idx" ON "material_logs" ("timestamp")'
  );

  // 审计日志保留策略：删除素材时保留日志记录，不允许 materialId 外键级联删除
  await executeRawSQL('ALTER TABLE "material_logs" DROP CONSTRAINT IF EXISTS "material_logs_materialId_fkey"');
  await executeRawSQL('ALTER TABLE "material_logs" DROP CONSTRAINT IF EXISTS "MaterialLog_materialId_fkey"');

  // userId 仍保留外键约束，用于保证操作人数据完整性
  await executeRawSQL(`
    DO $$
    BEGIN
      IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'material_logs_userId_fkey'
      ) THEN
        ALTER TABLE "material_logs"
        ADD CONSTRAINT "material_logs_userId_fkey"
        FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
      END IF;
    END
    $$;
  `);
  
  isInitialized = true;
}

/**
 * 创建素材操作日志
 */
export async function createMaterialLog({
  materialId,
  userId,
  action,
  changes = null,
}: {
  materialId: string;
  userId: string;
  action: string;
  changes?: any;
}) {
  try {
    // 确保表已初始化
    await initializeMaterialLogTable();
    
    // 创建日志记录
    await prisma.materialLog.create({
      data: {
        materialId,
        userId,
        action,
        changes: changes ? JSON.stringify(changes) : null,
      },
    });
  } catch (error) {
    console.error('[MaterialLog] Failed to create log:', error);
  }
}