import { getDb } from "../db.ts";
import { transactions, paymentMethods, wallets, users } from "../../drizzle/schema.ts";
import { eq, and } from "drizzle-orm";

export interface Transaction {
  id: number;
  userId: number;
  type: "deposit" | "withdrawal" | "purchase" | "reward";
  amount: number;
  currency: "USD" | "SC" | "GC";
  status: "pending" | "completed" | "failed" | "cancelled";
  paymentMethod: "stripe" | "paypal" | "square";
  description: string;
  createdAt: Date;
  completedAt?: Date;
}

export interface PaymentMethod {
  id: number;
  userId: number;
  type: "credit_card" | "debit_card" | "paypal" | "bank_account";
  last4: string;
  brand: string;
  expiryMonth: number;
  expiryYear: number;
  isDefault: boolean;
  createdAt: Date;
}

export interface KYCVerification {
  userId: number;
  status: "pending" | "verified" | "rejected";
  documentType: string;
  documentUrl: string;
  verifiedAt?: Date;
  rejectionReason?: string;
}

const GOLD_COIN_PACKAGES = [
  { id: 1, amount: 500, price: 4.99, bonus: 50 },
  { id: 2, amount: 1200, price: 9.99, bonus: 150 },
  { id: 3, amount: 3000, price: 24.99, bonus: 500 },
  { id: 4, amount: 6500, price: 49.99, bonus: 1300 },
  { id: 5, amount: 15000, price: 99.99, bonus: 3500 },
];

export async function createTransaction(
  userId: number,
  type: Transaction["type"],
  amount: number,
  currency: Transaction["currency"],
  paymentMethod: Transaction["paymentMethod"],
  description: string
): Promise<Transaction | null> {
  const db = await getDb();
  if (!db) return null;

  const result = await db.insert(transactions).values({
    userId,
    type,
    amount,
    currency,
    status: "pending",
    paymentMethod,
    description,
    createdAt: new Date(),
  });

  return {
    id: result.insertId as number,
    userId,
    type,
    amount,
    currency,
    status: "pending",
    paymentMethod,
    description,
    createdAt: new Date(),
  };
}

export async function completeTransaction(transactionId: number): Promise<boolean> {
  const db = await getDb();
  if (!db) return false;

  const transaction = await db
    .select()
    .from(transactions)
    .where(eq(transactions.id, transactionId))
    .limit(1);

  if (transaction.length === 0) return false;

  const txn = transaction[0];

  // Update transaction status
  await db
    .update(transactions)
    .set({ status: "completed", completedAt: new Date() })
    .where(eq(transactions.id, transactionId));

  // Update wallet based on transaction type
  if (txn.type === "deposit" && txn.currency === "GC") {
    const wallet = await db
      .select()
      .from(wallets)
      .where(eq(wallets.userId, txn.userId))
      .limit(1);

    if (wallet.length > 0) {
      const currentBalance = parseFloat(wallet[0].gcBalance || "0");
      const newBalance = currentBalance + txn.amount;

      await db
        .update(wallets)
        .set({ gcBalance: newBalance.toString() })
        .where(eq(wallets.userId, txn.userId));
    }
  }

  return true;
}

export async function getTransactionHistory(userId: number, limit: number = 50): Promise<Transaction[]> {
  const db = await getDb();
  if (!db) return [];

  const history = await db
    .select()
    .from(transactions)
    .where(eq(transactions.userId, userId))
    .orderBy((table) => [table.createdAt])
    .limit(limit);

  return history as Transaction[];
}

export async function addPaymentMethod(
  userId: number,
  type: PaymentMethod["type"],
  last4: string,
  brand: string,
  expiryMonth: number,
  expiryYear: number
): Promise<PaymentMethod | null> {
  const db = await getDb();
  if (!db) return null;

  const result = await db.insert(paymentMethods).values({
    userId,
    type,
    last4,
    brand,
    expiryMonth,
    expiryYear,
    isDefault: false,
    createdAt: new Date(),
  });

  return {
    id: result.insertId as number,
    userId,
    type,
    last4,
    brand,
    expiryMonth,
    expiryYear,
    isDefault: false,
    createdAt: new Date(),
  };
}

export async function getPaymentMethods(userId: number): Promise<PaymentMethod[]> {
  const db = await getDb();
  if (!db) return [];

  const methods = await db
    .select()
    .from(paymentMethods)
    .where(eq(paymentMethods.userId, userId));

  return methods as PaymentMethod[];
}

export async function setDefaultPaymentMethod(userId: number, methodId: number): Promise<boolean> {
  const db = await getDb();
  if (!db) return false;

  // Reset all to non-default
  await db
    .update(paymentMethods)
    .set({ isDefault: false })
    .where(eq(paymentMethods.userId, userId));

  // Set new default
  await db
    .update(paymentMethods)
    .set({ isDefault: true })
    .where(and(eq(paymentMethods.id, methodId), eq(paymentMethods.userId, userId)));

  return true;
}

export async function requestWithdrawal(userId: number, amount: number, currency: "SC" | "GC"): Promise<Transaction | null> {
  const db = await getDb();
  if (!db) return null;

  // Check KYC verification
  const user = await db.select().from(users).where(eq(users.id, userId)).limit(1);

  if (user.length === 0) return null;

  // Create withdrawal transaction
  return createTransaction(userId, "withdrawal", amount, currency, "square", `Withdrawal of ${amount} ${currency}`);
}

export function getGoldCoinPackages() {
  return GOLD_COIN_PACKAGES;
}

export function getGoldCoinPackageById(packageId: number) {
  return GOLD_COIN_PACKAGES.find((p) => p.id === packageId);
}

export async function processRefund(transactionId: number): Promise<boolean> {
  const db = await getDb();
  if (!db) return false;

  const transaction = await db
    .select()
    .from(transactions)
    .where(eq(transactions.id, transactionId))
    .limit(1);

  if (transaction.length === 0) return false;

  const txn = transaction[0];

  // Update transaction status
  await db
    .update(transactions)
    .set({ status: "cancelled" })
    .where(eq(transactions.id, transactionId));

  // Refund wallet if deposit
  if (txn.type === "deposit" && txn.currency === "GC") {
    const wallet = await db
      .select()
      .from(wallets)
      .where(eq(wallets.userId, txn.userId))
      .limit(1);

    if (wallet.length > 0) {
      const currentBalance = parseFloat(wallet[0].gcBalance || "0");
      const newBalance = Math.max(0, currentBalance - txn.amount);

      await db
        .update(wallets)
        .set({ gcBalance: newBalance.toString() })
        .where(eq(wallets.userId, txn.userId));
    }
  }

  return true;
}

export async function getRevenueStats(): Promise<{
  totalRevenue: number;
  totalTransactions: number;
  averageTransactionValue: number;
  topPaymentMethod: string;
}> {
  const db = await getDb();
  if (!db) {
    return {
      totalRevenue: 0,
      totalTransactions: 0,
      averageTransactionValue: 0,
      topPaymentMethod: "square",
    };
  }

  const allTransactions = await db.select().from(transactions);

  const completedTransactions = allTransactions.filter((t) => t.status === "completed" && t.type === "deposit");

  const totalRevenue = completedTransactions.reduce((sum, t) => sum + (t.amount || 0), 0);
  const totalTransactions = completedTransactions.length;
  const averageTransactionValue = totalTransactions > 0 ? totalRevenue / totalTransactions : 0;

  // Find top payment method
  const methodCounts: Record<string, number> = {};
  completedTransactions.forEach((t) => {
    methodCounts[t.paymentMethod] = (methodCounts[t.paymentMethod] || 0) + 1;
  });

  const topPaymentMethod = Object.entries(methodCounts).sort(([, a], [, b]) => b - a)[0]?.[0] || "square";

  return {
    totalRevenue,
    totalTransactions,
    averageTransactionValue,
    topPaymentMethod,
  };
}