import { eq, and, gt, lt, isNull, or, desc } from "drizzle-orm";
import { drizzle } from "drizzle-orm/mysql2";
import { InsertUser, users, xtreamUsers, streamCategories, musicTracks, activeConnections } from "../drizzle/schema";
import { ENV } from './_core/env';

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

// Lazily create the drizzle instance so local tooling can run without a DB.
export async function getDb() {
  if (!_db && process.env.DATABASE_URL) {
    try {
      _db = drizzle(process.env.DATABASE_URL);
    } catch (error) {
      console.warn("[Database] Failed to connect:", error);
      _db = null;
    }
  }
  return _db;
}

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

  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) {
  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;
}

// ============ Xtream Users Management ============

export async function createXtreamUser(data: {
  username: string;
  password: string;
  expirationDate?: Date;
  maxConnections?: number;
}) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");

  const result = await db.insert(xtreamUsers).values({
    username: data.username,
    password: data.password,
    expirationDate: data.expirationDate,
    maxConnections: data.maxConnections || 1,
    isActive: true,
  });

  return result;
}

export async function updateXtreamUser(
  id: number,
  data: Partial<{
    username: string;
    password: string;
    expirationDate: Date | null;
    maxConnections: number;
    isActive: boolean;
  }>
) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");

  return db
    .update(xtreamUsers)
    .set({ ...data, updatedAt: new Date() })
    .where(eq(xtreamUsers.id, id));
}

export async function deleteXtreamUser(id: number) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");

  return db.delete(xtreamUsers).where(eq(xtreamUsers.id, id));
}

export async function getXtreamUserByUsername(username: string) {
  const db = await getDb();
  if (!db) return undefined;

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

  return result[0];
}

export async function getXtreamUserById(id: number) {
  const db = await getDb();
  if (!db) return undefined;

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

  return result[0];
}

export async function listXtreamUsers(filters?: {
  search?: string;
  isActive?: boolean;
  showExpired?: boolean;
}) {
  const db = await getDb();
  if (!db) return [];

  const conditions = [];

  if (filters?.isActive !== undefined) {
    conditions.push(eq(xtreamUsers.isActive, filters.isActive));
  }

  if (filters?.showExpired === false) {
    // Show only non-expired users
    conditions.push(
      or(
        isNull(xtreamUsers.expirationDate),
        gt(xtreamUsers.expirationDate, new Date())
      )
    );
  }

  if (conditions.length > 0) {
    return db
      .select()
      .from(xtreamUsers)
      .where(and(...conditions))
      .orderBy(desc(xtreamUsers.createdAt));
  }

  return db
    .select()
    .from(xtreamUsers)
    .orderBy(desc(xtreamUsers.createdAt));
}

export async function getXtreamUserStatus(id: number) {
  const user = await getXtreamUserById(id);
  if (!user) return null;

  const now = new Date();
  const isExpired =
    user.expirationDate && user.expirationDate < now;

  return {
    ...user,
    isExpired: isExpired || false,
    daysUntilExpiration: user.expirationDate
      ? Math.ceil(
          (user.expirationDate.getTime() - now.getTime()) /
            (1000 * 60 * 60 * 24)
        )
      : null,
  };
}

// ============ Stream Categories Management ============

export async function createStreamCategory(data: {
  name: string;
  description?: string;
  categoryType?: "live" | "vod" | "series" | "music";
}) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");

  return db.insert(streamCategories).values({
    name: data.name,
    description: data.description,
    categoryType: data.categoryType || "music",
  });
}

export async function listStreamCategories() {
  const db = await getDb();
  if (!db) return [];

  return db.select().from(streamCategories);
}

export async function getStreamCategoryById(id: number) {
  const db = await getDb();
  if (!db) return undefined;

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

  return result[0];
}

// ============ Music Tracks Management ============

export async function createMusicTrack(data: {
  categoryId: number;
  title: string;
  artist?: string;
  album?: string;
  duration?: number;
  streamUrl: string;
  thumbnailUrl?: string;
}) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");

  return db.insert(musicTracks).values({
    categoryId: data.categoryId,
    title: data.title,
    artist: data.artist,
    album: data.album,
    duration: data.duration,
    streamUrl: data.streamUrl,
    thumbnailUrl: data.thumbnailUrl,
    isActive: true,
  });
}

export async function listMusicTracksByCategory(categoryId: number) {
  const db = await getDb();
  if (!db) return [];

  return db
    .select()
    .from(musicTracks)
    .where(
      and(
        eq(musicTracks.categoryId, categoryId),
        eq(musicTracks.isActive, true)
      )
    );
}

export async function getMusicTrackById(id: number) {
  const db = await getDb();
  if (!db) return undefined;

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

  return result[0];
}

// ============ Active Connections Management ============

export async function createActiveConnection(data: {
  userId: number;
  sessionToken: string;
  ipAddress?: string;
  userAgent?: string;
}) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");

  return db.insert(activeConnections).values({
    userId: data.userId,
    sessionToken: data.sessionToken,
    ipAddress: data.ipAddress,
    userAgent: data.userAgent,
  });
}

export async function getActiveConnectionsByUserId(userId: number) {
  const db = await getDb();
  if (!db) return [];

  return db
    .select()
    .from(activeConnections)
    .where(eq(activeConnections.userId, userId));
}

export async function deleteActiveConnection(sessionToken: string) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");

  return db
    .delete(activeConnections)
    .where(eq(activeConnections.sessionToken, sessionToken));
}

export async function updateConnectionActivity(sessionToken: string) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");

  return db
    .update(activeConnections)
    .set({ lastActivityAt: new Date() })
    .where(eq(activeConnections.sessionToken, sessionToken));
}

export async function countActiveConnectionsByUserId(userId: number) {
  const connections = await getActiveConnectionsByUserId(userId);
  return connections.length;
}

// ============ Dashboard Statistics ============

export async function getDashboardStats() {
  const db = await getDb();
  if (!db) {
    return {
      totalUsers: 0,
      activeUsers: 0,
      expiredUsers: 0,
      totalConnections: 0,
    };
  }

  const now = new Date();
  const allUsers = await db.select().from(xtreamUsers);

  const activeUsers = allUsers.filter(
    (u) =>
      u.isActive &&
      (!u.expirationDate || u.expirationDate > now)
  ).length;

  const expiredUsers = allUsers.filter(
    (u) => u.expirationDate && u.expirationDate <= now
  ).length;

  const allConnections = await db
    .select()
    .from(activeConnections);

  return {
    totalUsers: allUsers.length,
    activeUsers,
    expiredUsers,
    totalConnections: allConnections.length,
  };
}
