import { eq, desc, and, sql } from "drizzle-orm";
import { drizzle } from "drizzle-orm/mysql2";
import { 
  InsertUser, 
  users, 
  tracks, 
  stems, 
  fingerprints, 
  embeddings, 
  attributionResults,
  processingJobs,
  InsertTrack,
  InsertStem,
  InsertFingerprint,
  InsertEmbedding,
  InsertAttributionResult,
  InsertProcessingJob,
} from "../drizzle/schema";
import { ENV } from './_core/env';
import { mockDb, isMockDb } from "./mockDb";

let _db: ReturnType<typeof drizzle> | null = null;
let _useMockDb = false;

export async function getDb() {
  if (_useMockDb) return null; // Using mock database
  
  if (!_db && process.env.DATABASE_URL) {
    try {
      _db = drizzle(process.env.DATABASE_URL);
    } catch (error) {
      console.warn("[Database] Failed to connect:", error);
      _db = null;
    }
  }
  
  // If no MySQL available, fall back to mock database
  if (!_db && !process.env.DATABASE_URL) {
    _useMockDb = true;
    console.log("[Database] No DATABASE_URL set, using in-memory mock database");
  }
  
  return _db;
}

export function isUsingMockDb(): boolean {
  return _useMockDb || !process.env.DATABASE_URL;
}

export async function upsertUser(user: InsertUser): Promise<void> {
  if (!user.openId) {
    throw new Error("User openId is required for upsert");
  }

  // Use mock database if no MySQL
  if (isUsingMockDb()) {
    mockDb.upsertUser({
      openId: user.openId,
      name: user.name || null,
      email: user.email || null,
      loginMethod: user.loginMethod || null,
      role: user.role || (user.openId === ENV.ownerOpenId ? "admin" : "user"),
      lastSignedIn: user.lastSignedIn || new Date(),
    });
    return;
  }

  const db = await getDb();
  if (!db) {
    console.warn("[Database] Cannot upsert user: database not available");
    return;
  }

  try {
    const values: InsertUser = {
      openId: user.openId,
    };
    const updateSet: Record<string, unknown> = {};

    const textFields = ["name", "email", "loginMethod"] as const;
    type TextField = (typeof textFields)[number];

    const assignNullable = (field: TextField) => {
      const value = user[field];
      if (value === undefined) return;
      const normalized = value ?? null;
      values[field] = normalized;
      updateSet[field] = normalized;
    };

    textFields.forEach(assignNullable);

    if (user.lastSignedIn !== undefined) {
      values.lastSignedIn = user.lastSignedIn;
      updateSet.lastSignedIn = user.lastSignedIn;
    }
    if (user.role !== undefined) {
      values.role = user.role;
      updateSet.role = user.role;
    } else if (user.openId === ENV.ownerOpenId) {
      values.role = 'admin';
      updateSet.role = 'admin';
    }

    if (!values.lastSignedIn) {
      values.lastSignedIn = new Date();
    }

    if (Object.keys(updateSet).length === 0) {
      updateSet.lastSignedIn = new Date();
    }

    await db.insert(users).values(values).onDuplicateKeyUpdate({
      set: updateSet,
    });
  } catch (error) {
    console.error("[Database] Failed to upsert user:", error);
    throw error;
  }
}

export async function getUserByOpenId(openId: string) {
  // Use mock database if no MySQL
  if (isUsingMockDb()) {
    return mockDb.getUserByOpenId(openId);
  }

  const db = await getDb();
  if (!db) {
    console.warn("[Database] Cannot get user: database not available");
    return undefined;
  }

  const result = await db.select().from(users).where(eq(users.openId, openId)).limit(1);

  return result.length > 0 ? result[0] : undefined;
}

// Track operations
export async function createTrack(track: InsertTrack) {
  // Use mock database if no MySQL
  if (isUsingMockDb()) {
    return mockDb.createTrack({
      userId: track.userId || null,
      title: track.title,
      artist: track.artist || null,
      trackType: track.trackType,
      fileKey: track.fileKey,
      fileUrl: track.fileUrl,
      fileSize: track.fileSize || null,
      mimeType: track.mimeType || null,
      duration: track.duration || null,
      status: track.status || "pending",
      errorMessage: track.errorMessage || null,
    });
  }

  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  const result = await db.insert(tracks).values(track);
  return result[0].insertId;
}

export async function getTrackById(id: number) {
  // Use mock database if no MySQL
  if (isUsingMockDb()) {
    return mockDb.getTrackById(id);
  }

  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  const result = await db.select().from(tracks).where(eq(tracks.id, id)).limit(1);
  return result[0];
}

export async function getUserTracks(userId: number) {
  // Use mock database if no MySQL
  if (isUsingMockDb()) {
    return mockDb.getUserTracks(userId);
  }

  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  return db.select().from(tracks)
    .where(eq(tracks.userId, userId))
    .orderBy(desc(tracks.createdAt));
}

export async function updateTrackStatus(id: number, status: "pending" | "processing" | "completed" | "failed", errorMessage?: string) {
  // Use mock database if no MySQL
  if (isUsingMockDb()) {
    mockDb.updateTrackStatus(id, status, errorMessage);
    return;
  }

  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  await db.update(tracks)
    .set({ status, errorMessage, updatedAt: new Date() })
    .where(eq(tracks.id, id));
}

// Stem operations
export async function createStem(stem: InsertStem) {
  // Use mock database if no MySQL
  if (isUsingMockDb()) {
    return mockDb.createStem({
      trackId: stem.trackId,
      stemType: stem.stemType,
      fileKey: stem.fileKey,
      fileUrl: stem.fileUrl,
      duration: stem.duration || null,
    });
  }

  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  const result = await db.insert(stems).values(stem);
  return result[0].insertId;
}

export async function getTrackStems(trackId: number) {
  // Use mock database if no MySQL
  if (isUsingMockDb()) {
    return mockDb.getTrackStems(trackId);
  }

  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  return db.select().from(stems).where(eq(stems.trackId, trackId));
}

// Fingerprint operations
export async function createFingerprint(fingerprint: InsertFingerprint) {
  // Use mock database if no MySQL
  if (isUsingMockDb()) {
    return mockDb.createFingerprint({
      stemId: fingerprint.stemId,
      algorithm: fingerprint.algorithm,
      fingerprintData: fingerprint.fingerprintData,
      version: fingerprint.version || null,
    });
  }

  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  const result = await db.insert(fingerprints).values(fingerprint);
  return result[0].insertId;
}

// Embedding operations
export async function createEmbedding(embedding: InsertEmbedding) {
  // Use mock database if no MySQL
  if (isUsingMockDb()) {
    return mockDb.createEmbedding({
      stemId: embedding.stemId,
      model: embedding.model,
      embeddingVector: embedding.embeddingVector,
      dimension: embedding.dimension,
    });
  }

  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  const result = await db.insert(embeddings).values(embedding);
  return result[0].insertId;
}

// Attribution operations
export async function createAttributionResult(attribution: InsertAttributionResult) {
  // Use mock database if no MySQL
  if (isUsingMockDb()) {
    return mockDb.createAttributionResult({
      aiTrackId: attribution.aiTrackId,
      aiStemId: attribution.aiStemId || null,
      trainingTrackId: attribution.trainingTrackId,
      trainingStemId: attribution.trainingStemId || null,
      method: attribution.method,
      score: attribution.score,
      confidence: attribution.confidence || null,
      metadata: attribution.metadata || null,
    });
  }

  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  const result = await db.insert(attributionResults).values(attribution);
  return result[0].insertId;
}

export async function getTrackAttributions(aiTrackId: number) {
  // Use mock database if no MySQL
  if (isUsingMockDb()) {
    return mockDb.getTrackAttributions(aiTrackId);
  }

  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  // Join with training tracks to get full details
  return db.select({
    id: attributionResults.id,
    aiTrackId: attributionResults.aiTrackId,
    aiStemId: attributionResults.aiStemId,
    trainingTrackId: attributionResults.trainingTrackId,
    trainingStemId: attributionResults.trainingStemId,
    method: attributionResults.method,
    score: attributionResults.score,
    confidence: attributionResults.confidence,
    metadata: attributionResults.metadata,
    createdAt: attributionResults.createdAt,
    trainingTrack: {
      id: tracks.id,
      title: tracks.title,
      artist: tracks.artist,
      fileUrl: tracks.fileUrl,
    }
  })
  .from(attributionResults)
  .leftJoin(tracks, eq(attributionResults.trainingTrackId, tracks.id))
  .where(eq(attributionResults.aiTrackId, aiTrackId))
  .orderBy(desc(attributionResults.score));
}

export async function clearTrackAttributions(aiTrackId: number): Promise<number> {
  // Use mock database if no MySQL
  if (isUsingMockDb()) {
    return mockDb.clearTrackAttributions(aiTrackId);
  }

  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  const result = await db.delete(attributionResults)
    .where(eq(attributionResults.aiTrackId, aiTrackId));
  return result[0].affectedRows || 0;
}

// Processing job operations
export async function createProcessingJob(job: InsertProcessingJob) {
  // Use mock database if no MySQL
  if (isUsingMockDb()) {
    return mockDb.createProcessingJob({
      trackId: job.trackId,
      jobType: job.jobType,
      status: job.status || "pending",
      progress: job.progress || 0,
      errorMessage: job.errorMessage || null,
      resultData: job.resultData || null,
      startedAt: job.startedAt || null,
      completedAt: job.completedAt || null,
    });
  }

  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  const result = await db.insert(processingJobs).values(job);
  return result[0].insertId;
}

export async function updateProcessingJob(
  id: number, 
  updates: { 
    status?: "pending" | "running" | "completed" | "failed";
    progress?: number;
    errorMessage?: string;
    resultData?: Record<string, unknown>;
    startedAt?: Date;
    completedAt?: Date;
  }
) {
  // Use mock database if no MySQL
  if (isUsingMockDb()) {
    mockDb.updateProcessingJob(id, updates);
    return;
  }

  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  await db.update(processingJobs).set(updates).where(eq(processingJobs.id, id));
}

export async function getTrackJobs(trackId: number) {
  // Use mock database if no MySQL
  if (isUsingMockDb()) {
    return mockDb.getTrackJobs(trackId);
  }

  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  return db.select().from(processingJobs)
    .where(eq(processingJobs.trackId, trackId))
    .orderBy(desc(processingJobs.createdAt));
}

// Get all training reference tracks for matching
export async function getTrainingTracks() {
  // Use mock database if no MySQL
  if (isUsingMockDb()) {
    return mockDb.getTrainingTracks();
  }

  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  return db.select().from(tracks)
    .where(eq(tracks.trackType, "training_reference"))
    .orderBy(desc(tracks.createdAt));
}