import { eq, and, desc, gte, lte, or, like, between } from "drizzle-orm";
import { drizzle } from "drizzle-orm/mysql2";
import { 
  InsertUser, users, 
  walletTransactions, WalletTransaction, InsertWalletTransaction,
  vtuProviders, VtuProvider,
  vtuTransactions, VtuTransaction, InsertVtuTransaction,
  giftCardProviders, GiftCardProvider,
  giftCardDenominations, GiftCardDenomination,
  giftCardTransactions, GiftCardTransaction, InsertGiftCardTransaction,
  supportTickets, SupportTicket, InsertSupportTicket,
  ticketMessages, TicketMessage, InsertTicketMessage,
  fraudRules, FraudRule, InsertFraudRule,
  fraudAlerts, FraudAlert, InsertFraudAlert,
  adminAuditLogs, AdminAuditLog, InsertAdminAuditLog,
  faqs, Faq, InsertFaq,
} from "../drizzle/schema";
import { ENV } from './_core/env';

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

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

export async function getUserById(id: number) {
  const db = await getDb();
  if (!db) return undefined;
  const result = await db.select().from(users).where(eq(users.id, id)).limit(1);
  return result.length > 0 ? result[0] : undefined;
}

// Wallet functions
export async function updateWalletBalance(userId: number, newBalance: string) {
  const db = await getDb();
  if (!db) return false;
  await db.update(users).set({ walletBalance: newBalance }).where(eq(users.id, userId));
  return true;
}

export async function createWalletTransaction(transaction: InsertWalletTransaction) {
  const db = await getDb();
  if (!db) return null;
  const result = await db.insert(walletTransactions).values(transaction);
  return result;
}

export async function getWalletTransactions(userId: number, limit: number = 50, offset: number = 0) {
  const db = await getDb();
  if (!db) return [];
  return await db.select().from(walletTransactions)
    .where(eq(walletTransactions.userId, userId))
    .orderBy(desc(walletTransactions.createdAt))
    .limit(limit)
    .offset(offset);
}

// VTU Provider functions
export async function getVtuProviders() {
  const db = await getDb();
  if (!db) return [];
  return await db.select().from(vtuProviders).where(eq(vtuProviders.isActive, true));
}

export async function getVtuProviderById(id: number) {
  const db = await getDb();
  if (!db) return null;
  const result = await db.select().from(vtuProviders).where(eq(vtuProviders.id, id)).limit(1);
  return result.length > 0 ? result[0] : null;
}

// VTU Transaction functions
export async function createVtuTransaction(transaction: InsertVtuTransaction) {
  const db = await getDb();
  if (!db) return null;
  const result = await db.insert(vtuTransactions).values(transaction);
  return result;
}

export async function getVtuTransactionByReference(reference: string) {
  const db = await getDb();
  if (!db) return null;
  const result = await db.select().from(vtuTransactions).where(eq(vtuTransactions.reference, reference)).limit(1);
  return result.length > 0 ? result[0] : null;
}

export async function updateVtuTransaction(id: number, updates: Partial<VtuTransaction>) {
  const db = await getDb();
  if (!db) return false;
  await db.update(vtuTransactions).set(updates).where(eq(vtuTransactions.id, id));
  return true;
}

export async function getVtuTransactions(userId: number, limit: number = 50, offset: number = 0) {
  const db = await getDb();
  if (!db) return [];
  return await db.select().from(vtuTransactions)
    .where(eq(vtuTransactions.userId, userId))
    .orderBy(desc(vtuTransactions.createdAt))
    .limit(limit)
    .offset(offset);
}

// Gift Card Provider functions
export async function getGiftCardProviders() {
  const db = await getDb();
  if (!db) return [];
  return await db.select().from(giftCardProviders).where(eq(giftCardProviders.isActive, true));
}

export async function getGiftCardDenominations(providerId: number) {
  const db = await getDb();
  if (!db) return [];
  return await db.select().from(giftCardDenominations)
    .where(and(eq(giftCardDenominations.providerId, providerId), eq(giftCardDenominations.isActive, true)))
    .orderBy(giftCardDenominations.denomination);
}

// Gift Card Transaction functions
export async function createGiftCardTransaction(transaction: InsertGiftCardTransaction) {
  const db = await getDb();
  if (!db) return null;
  const result = await db.insert(giftCardTransactions).values(transaction);
  return result;
}

export async function getGiftCardTransactions(userId: number, limit: number = 50, offset: number = 0) {
  const db = await getDb();
  if (!db) return [];
  return await db.select().from(giftCardTransactions)
    .where(eq(giftCardTransactions.userId, userId))
    .orderBy(desc(giftCardTransactions.createdAt))
    .limit(limit)
    .offset(offset);
}

export async function updateGiftCardTransaction(id: number, updates: Partial<GiftCardTransaction>) {
  const db = await getDb();
  if (!db) return false;
  await db.update(giftCardTransactions).set(updates).where(eq(giftCardTransactions.id, id));
  return true;
}

// Support Ticket functions
export async function createSupportTicket(ticket: InsertSupportTicket) {
  const db = await getDb();
  if (!db) return null;
  const result = await db.insert(supportTickets).values(ticket);
  return result;
}

export async function getSupportTickets(userId: number, limit: number = 50, offset: number = 0) {
  const db = await getDb();
  if (!db) return [];
  return await db.select().from(supportTickets)
    .where(eq(supportTickets.userId, userId))
    .orderBy(desc(supportTickets.createdAt))
    .limit(limit)
    .offset(offset);
}

export async function getSupportTicketById(id: number) {
  const db = await getDb();
  if (!db) return null;
  const result = await db.select().from(supportTickets).where(eq(supportTickets.id, id)).limit(1);
  return result.length > 0 ? result[0] : null;
}

export async function updateSupportTicket(id: number, updates: Partial<SupportTicket>) {
  const db = await getDb();
  if (!db) return false;
  await db.update(supportTickets).set(updates).where(eq(supportTickets.id, id));
  return true;
}

export async function addTicketMessage(message: InsertTicketMessage) {
  const db = await getDb();
  if (!db) return null;
  const result = await db.insert(ticketMessages).values(message);
  return result;
}

export async function getTicketMessages(ticketId: number) {
  const db = await getDb();
  if (!db) return [];
  return await db.select().from(ticketMessages)
    .where(eq(ticketMessages.ticketId, ticketId))
    .orderBy(ticketMessages.createdAt);
}

// Fraud Rule functions
export async function createFraudRule(rule: InsertFraudRule) {
  const db = await getDb();
  if (!db) return null;
  const result = await db.insert(fraudRules).values(rule);
  return result;
}

export async function getFraudRules() {
  const db = await getDb();
  if (!db) return [];
  return await db.select().from(fraudRules).where(eq(fraudRules.isEnabled, true));
}

export async function updateFraudRule(id: number, updates: Partial<FraudRule>) {
  const db = await getDb();
  if (!db) return false;
  await db.update(fraudRules).set(updates).where(eq(fraudRules.id, id));
  return true;
}

// Fraud Alert functions
export async function createFraudAlert(alert: InsertFraudAlert) {
  const db = await getDb();
  if (!db) return null;
  const result = await db.insert(fraudAlerts).values(alert);
  return result;
}

export async function getFraudAlerts(limit: number = 100, offset: number = 0) {
  const db = await getDb();
  if (!db) return [];
  return await db.select().from(fraudAlerts)
    .orderBy(desc(fraudAlerts.createdAt))
    .limit(limit)
    .offset(offset);
}

export async function updateFraudAlert(id: number, updates: Partial<FraudAlert>) {
  const db = await getDb();
  if (!db) return false;
  await db.update(fraudAlerts).set(updates).where(eq(fraudAlerts.id, id));
  return true;
}

// Admin Audit Log functions
export async function createAuditLog(log: InsertAdminAuditLog) {
  const db = await getDb();
  if (!db) return null;
  const result = await db.insert(adminAuditLogs).values(log);
  return result;
}

export async function getAuditLogs(limit: number = 100, offset: number = 0) {
  const db = await getDb();
  if (!db) return [];
  return await db.select().from(adminAuditLogs)
    .orderBy(desc(adminAuditLogs.createdAt))
    .limit(limit)
    .offset(offset);
}

// FAQ functions
export async function getFaqs(category?: string) {
  const db = await getDb();
  if (!db) return [];
  
  if (category) {
    return await db.select().from(faqs)
      .where(and(eq(faqs.isActive, true), eq(faqs.category, category)))
      .orderBy(faqs.order);
  }
  
  return await db.select().from(faqs)
    .where(eq(faqs.isActive, true))
    .orderBy(faqs.order);
}

export async function getFaqCategories() {
  const db = await getDb();
  if (!db) return [];
  
  const result = await db.selectDistinct({ category: faqs.category }).from(faqs).where(eq(faqs.isActive, true));
  return result.map(r => r.category);
}

// Admin functions
export async function getAllUsers(limit: number = 50, offset: number = 0) {
  const db = await getDb();
  if (!db) return [];
  return await db.select().from(users)
    .orderBy(desc(users.createdAt))
    .limit(limit)
    .offset(offset);
}

export async function getAllTransactions(limit: number = 100, offset: number = 0) {
  const db = await getDb();
  if (!db) return [];
  return await db.select().from(walletTransactions)
    .orderBy(desc(walletTransactions.createdAt))
    .limit(limit)
    .offset(offset);
}

export async function searchTransactions(query: string, limit: number = 50) {
  const db = await getDb();
  if (!db) return [];
  return await db.select().from(walletTransactions)
    .where(or(
      like(walletTransactions.reference, `%${query}%`),
      like(walletTransactions.description, `%${query}%`)
    ))
    .limit(limit);
}

export async function updateUserStatus(userId: number, isActive: boolean) {
  const db = await getDb();
  if (!db) return false;
  await db.update(users).set({ isActive }).where(eq(users.id, userId));
  return true;
}

export async function updateUserRole(userId: number, role: "user" | "admin") {
  const db = await getDb();
  if (!db) return false;
  await db.update(users).set({ role }).where(eq(users.id, userId));
  return true;
}