import { users, lendableItems, lendableItemCategories, loanAgreements, legalContent, notifications, userPreferences, conversations, chatMessages, memberActivity, contentBlocks, contentBlockHistory, userFavorites, loanTransactions, staticPages, hourOfferings, emailNotificationSettings, emailNotificationLog, emailDigestQueue, type User, type InsertUser, type LendableItem, type InsertLendableItem, type LendableItemCategory, type LoanTransaction, type LegalContent, type Notification, type Conversation, type InsertConversation, type ChatMessage, type InsertChatMessage, type HourOffering, type InsertHourOffering } from "@shared/schema";
import { db, pool, withConnection, withTransaction } from './db.js';
import { eq, desc, sql, and, or, isNotNull, isNull, ne, inArray, not, gte, asc, exists, notExists } from "drizzle-orm";
import { alias } from "drizzle-orm/pg-core";
import { spawn } from 'child_process';
import { promises as fs } from 'fs';
import path from 'path';

// Thumbnail generation utility
async function generateThumbnail(originalImagePath: string, thumbnailPath: string): Promise<boolean> {
  return new Promise((resolve) => {
    const magick = spawn('magick', [
      originalImagePath,
      '-resize', '150x150^',
      '-gravity', 'center',
      '-crop', '150x150+0+0',
      '+repage',
      '-quality', '85',
      thumbnailPath
    ]);

    magick.on('close', (code) => {
      resolve(code === 0);
    });

    magick.on('error', () => {
      resolve(false);
    });
  });
}

// Get thumbnail for item image with automatic generation
async function getItemThumbnail(imageName: string | null): Promise<string | null> {
  if (!imageName) return imageName;
  
  // Check for existing thumbnail following naming convention
  const baseName = path.parse(imageName).name;
  const extension = path.parse(imageName).ext;
  const thumbnailName = `${baseName}_thumb${extension}`;
  const thumbnailPath = path.join('public/lendable_items', thumbnailName);
  const originalPath = path.join('public/lendable_items', imageName);
  
  try {
    // Check if thumbnail exists
    await fs.access(thumbnailPath);
    return thumbnailName;
  } catch (error) {
    // Thumbnail doesn't exist, try to generate it
    try {
      await fs.access(originalPath);
      const success = await generateThumbnail(originalPath, thumbnailPath);
      if (success) {
        console.log(`Generated thumbnail: ${thumbnailName}`);
        return thumbnailName;
      }
    } catch (originalError) {
      console.log(`Original image not found: ${imageName}`);
    }
  }
  
  // Fallback to original image
  return imageName;
}

// Notification template system interfaces and utilities
export interface StandardizedNotificationData {
  userId: number;
  type: NotificationType;
  title?: string;
  message?: string;
  relatedId?: number;
  relatedType?: string;
  priority?: number;
  templateData?: NotificationTemplateData;
}

export interface NotificationTemplateData {
  senderName?: string;
  itemName?: string;
  startDate?: string;
  endDate?: string;
  details?: string;
  ownerName?: string;
  borrowerName?: string;
  agreementId?: number;
  offeringTitle?: string;
  issueType?: string;
  reminderMessage?: string;
}

export type NotificationType = 
  | 'borrow_request' 
  | 'request_approved' 
  | 'request_denied'
  | 'counter_offer'
  | 'agreement_ready' 
  | 'return_initiated' 
  | 'return_confirmed'
  | 'hour_request'
  | 'hour_response'
  | 'new_message' 
  | 'found_item' 
  | 'item_issue'
  | 'reminder_sent'
  | 'return_overdue' 
  | 'owner_overdue' 
  | 'return_due_today' 
  | 'return_due_tomorrow'
  | 'owner_due_today' 
  | 'owner_due_tomorrow'
  | 'send_reminder';

export interface IStorage {
  // User operations
  getUser(id: number): Promise<User | undefined>;
  getUserByEmail(email: string): Promise<User | undefined>;
  createUser(user: InsertUser): Promise<User>;
  getAllUsers(): Promise<User[]>;
  getDemoUsers(): Promise<User[]>;
  authenticateUser(email: string, password: string): Promise<User | null>;
  isUserAdmin(userId: number): Promise<boolean>;
  canUserEditItem(userId: number, itemId: number): Promise<boolean>;
  getUserPreferences(userId: number): Promise<any>;
  updateUser(userId: number, updateData: any): Promise<void>;
  getAdminTooltips(): Promise<Record<string, string>>;
  updateAdminTooltip(key: string, text: string): Promise<boolean>;
  
  // Chat operations
  createConversation(conversation: InsertConversation): Promise<Conversation>;
  getConversation(itemId: number, ownerId: number, borrowerId: number): Promise<Conversation | undefined>;
  getAllConversations(): Promise<Conversation[]>;
  getConversationsForItem(itemId: number, userId: number): Promise<any[]>;
  getConversationById(id: number): Promise<any>;
  sendChatMessage(message: InsertChatMessage): Promise<ChatMessage>;
  sendMessage(data: { conversationId: number; senderId: number; message?: string; messageType?: string; audioPath?: string; }): Promise<ChatMessage>;
  getChatMessages(conversationId: number): Promise<any[]>;
  getChatMessage(messageId: number): Promise<any>;
  updateLastMessageTime(conversationId: number): Promise<void>;
  updateChatMessageTranscription(messageId: number, transcription: string): Promise<void>;
  
  // Item operations
  getUserItems(userId: number): Promise<LendableItem[]>;
  getAllAvailableItems(randomize: boolean): Promise<any[]>;
  getNewListings(limit?: number): Promise<any[]>;
  getItem(id: number): Promise<LendableItem | undefined>;
  createItem(item: InsertLendableItem): Promise<LendableItem>;
  updateItem(id: number, updates: Partial<LendableItem>): Promise<LendableItem>;
  deleteItem(id: number): Promise<void>;
  
  // Category operations
  getAllCategories(): Promise<LendableItemCategory[]>;
  
  // Favorites operations
  addToFavorites(userId: number, itemId: number): Promise<boolean>;
  removeFromFavorites(userId: number, itemId: number): Promise<boolean>;
  isItemFavorited(userId: number, itemId: number): Promise<boolean>;
  getUserFavorites(userId: number): Promise<any[]>;
  
  // Stats and activity
  getUserStats(userId: number): Promise<{
    itemsShared: number;
    itemsBorrowed: number;
    totalMembers: number;
  }>;
  getRecentActivity(userId: number): Promise<any[]>;
  
  // Item interaction methods
  getItemLoanStatus(itemId: number): Promise<any>;
  incrementItemViews(itemId: number): Promise<void>;
  createLoanRequest(data: any): Promise<any>;
  getItemAvailabilityForMonth(itemId: number, year: number, month: number): Promise<any[]>;
  getItemLoanCalendar(itemId: number): Promise<any[]>;
  updateItemImages(itemId: number, primaryLogo?: string, extraImages?: string): Promise<void>;
  getItemWithOwner(itemId: number): Promise<any>;
  
  // Borrow request operations
  createBorrowRequest(request: InsertBorrowRequest): Promise<BorrowRequest>;
  getBorrowRequest(id: number): Promise<BorrowRequest | undefined>;
  getBorrowRequestsByOwner(ownerId: number): Promise<BorrowRequest[]>;
  getBorrowRequestsByBorrower(borrowerId: number): Promise<BorrowRequest[]>;
  updateBorrowRequestStatus(id: number, status: string): Promise<void>;
  
  // Legal content operations
  getLegalContent(): Promise<LegalContent[]>;
  
  // Notification operations
  createNotification(notification: any): Promise<Notification>;
  createStandardizedNotification(data: StandardizedNotificationData): Promise<Notification>;
  getUserNotifications(userId: number): Promise<Notification[]>;
  markNotificationAsRead(id: number): Promise<void>;
  getNotificationSettings(userId: number): Promise<NotificationSettings | undefined>;
  
  // Authentication validation
  validateUserAuthentication(userId: number | undefined): Promise<{ valid: boolean; user?: User; error?: string }>;
  validateItemOwnership(userId: number, itemId: number): Promise<{ valid: boolean; item?: LendableItem; error?: string }>;
  validateLoanParticipation(userId: number, loanId: number): Promise<{ valid: boolean; loan?: any; role?: 'owner' | 'borrower'; error?: string }>;
  getExistingBookings(itemId: number): Promise<any[]>;
  getUserNotificationData(userId: number): Promise<{
    hasUnread: boolean;
    unreadCount: number;
    recentNotifications: any[];
  }>;
  getUserUnreadChatData(userId: number): Promise<{
    hasUnreadChats: boolean;
    unreadChatCount: number;
  }>;
  getUserRecentActivity(userId: number): Promise<any[]>;
  getUserBorrowingHistory(userId: number): Promise<any[]>;
  getUserUnreadMessages(userId: number): Promise<any[]>;
  getAllUserMessages(userId: number): Promise<{received: any[], sent: any[]}>;
  getUnreturnedItemMessages(userId: number): Promise<{received: any[], sent: any[], threaded: any[]}>;
  markMessageAsRead(messageId: number): Promise<void>;
  getUserItemsDetailed(userId: number): Promise<any[]>;
  getUserDraftItems(userId: number): Promise<any[]>;
  
  // Member import operations
  createUser(userData: any): Promise<number>;
  updateUserWelcomeStatus(userId: number, completed: boolean): Promise<boolean>;
  getImportedMemberCount(): Promise<number>;
  getWelcomePendingCount(): Promise<number>;
  getRecentImports(limit: number): Promise<any[]>;
  logImportActivity(adminId: number, memberCount: number): Promise<void>;
  deleteEmptyConversations(): Promise<number>;
  getEmptyConversationCount(): Promise<number>;
  getDuplicateBorrowRequests(): Promise<any[]>;
  deleteDuplicateBorrowRequests(): Promise<number>;
  deleteBorrowedItem(borrowId: number, userId: number): Promise<boolean>;
  deleteBorrowedItemAdmin(borrowId: number): Promise<boolean>;
  toggleItemAvailability(itemId: number, isAvailable: boolean): Promise<void>;
  getBorrowRequestsWithDetails(ownerId: number): Promise<any[]>;
  returnLoan(loanId: number): Promise<boolean>;
  pushReturnLoan(loanId: number, ownerId: number, hasConcerns?: boolean, concernsMessage?: string): Promise<boolean>;
  getActiveLoansByBorrower(borrowerId: number): Promise<any[]>;
  getActiveLoansByOwner(ownerId: number): Promise<any[]>;
  initiateReturn(loanId: number, borrowerId: number, thankYouMessage?: string): Promise<void>;
  completeReturn(loanTrackingId: number, ownerId: number, condition: string, notes?: string): Promise<boolean>;
  
  // Content block methods
  getContentBlock(identifier: string): Promise<any>;
  
  // Admin methods
  getConversationsForAdmin(): Promise<any[]>;
  getAdminStats(): Promise<any>;
  deactivateMessage(conversationId: number): Promise<boolean>;
  reactivateMessage(conversationId: number): Promise<boolean>;
  deactivateActivity(activityId: number): Promise<boolean>;
  reactivateActivity(activityId: number): Promise<boolean>;
  deleteMessage(messageId: number): Promise<boolean>;
  
  // Static page methods
  getStaticPageBySlug(slug: string): Promise<any>;
  processStaticPageContent(content: string): Promise<string>;
  
  // Hour offerings methods
  createHourOffering(offering: InsertHourOffering): Promise<HourOffering>;
  getUserHourOfferings(userId: number): Promise<HourOffering[]>;
  getAvailableHourOfferings(): Promise<any[]>;
  getHourOfferingById(id: number): Promise<HourOffering | undefined>;
  updateHourOffering(id: number, updates: Partial<HourOffering>): Promise<HourOffering>;
  deleteHourOffering(id: number): Promise<void>;
  bookHourOffering(id: number): Promise<boolean>;
  
  // Additional missing methods
  generateLoanReturnNotifications(): Promise<void>;
  cleanupOldNotifications(): Promise<void>;
  getSystemNotifications(userId: number): Promise<any[]>;
  getUserActiveLoans(userId: number): Promise<any[]>;
  getItemByQRCode(qrCode: string): Promise<any>;
  updateItemStatusByQR(qrCode: string, status: string): Promise<boolean>;
  getOrCreateItemQR(itemId: number): Promise<string>;
  pushForwardWorkflow(itemId: number): Promise<boolean>;
  getLoanAgreement(id: number): Promise<any>;
  getSignedAgreementForItem(itemId: number): Promise<any>;
  getLoanTransactionByBorrowRequestId(borrowRequestId: number): Promise<any>;
  getLoanTransaction(loanTransactionId: number): Promise<any>;
  linkLoanTransactionToAgreement(loanTransactionId: number, agreementId: number): Promise<void>;
  signAgreement(agreementId: number, userId: number, signature: string, isOwner: boolean): Promise<boolean>;
  getBorrowRequestsForOwner(ownerId: number): Promise<any[]>;
  getActiveLoansByUser(userId: number): Promise<any[]>;
  updateLoanTransactionStatus(id: number, status: string, ownerResponse?: string, modifiedStartDate?: Date, modifiedEndDate?: Date): Promise<void>;
  
  // Spreadsheet data methods
  getUserSpreadsheetData(userId: number): Promise<{
    myItems: any[];
    borrowedItems: any[];
    wishListItems: any[];
    offeredHours: any[];
  }>;
  
  // Loan history data methods
  getUserLoanHistoryData(userId: number): Promise<{
    loanedItems: any[];
    borrowedItems: any[];
  }>;

  // Email notification system operations
  getEmailNotificationSettings(): Promise<any>;
  updateEmailNotificationSettings(updates: any): Promise<void>;
  createEmailLog(logData: any): Promise<any>;
  getEmailLogs(limit?: number): Promise<any[]>;
  createEmailDigestQueue(queueData: any): Promise<any>;
  getEmailDigestStats(date: string): Promise<{ sent: number; failed: number; skipped: number }>;
  getRecentMessagesForUser(userId: number, since: Date): Promise<any[]>;
  getRecentNotificationsForUser(userId: number, since: Date): Promise<any[]>;

  // Alpha feedback methods
  createAlphaFeedback(userId: number, userName: string, userAvatar: string | null, pageTitle: string, previousPage: string | null, currentUrl: string, userAgent: string | null, browserInfo: string | null, sessionData: string | null, testerComments: string, ipAddress: string | null, screenshot1?: string | null, screenshot2?: string | null, screenshot3?: string | null): Promise<any>;
  getAllAlphaFeedback(): Promise<any[]>;
  getAlphaFeedbackById(id: number): Promise<any | null>;
  markAlphaFeedbackResolved(id: number): Promise<any | null>;
  saveAlphaFeedbackReply(feedbackId: number, reply: string, adminId: number): Promise<any | null>;
}

export class DatabaseStorage implements IStorage {
  async getUser(id: number): Promise<User | undefined> {
    try {
      const [user] = await db.select({
        id: users.id,
        firstName: users.firstName,
        lastName: users.lastName,
        showName: users.showName,
        email: users.email,
        roles: users.roles,
        password: users.password,
        active: users.active,
        home: users.home,
        phone: users.phone,
        address: users.address,
        town: users.town,
        zipcode: users.zipcode,
        gender: users.gender,
        age: users.age,
        bio: users.bio,
        avatar: users.avatar,
        avatarBackgroundColor: users.avatarBackgroundColor,
        invitedBy: users.invitedBy,
        welcomeSequenceCompleted: users.welcomeSequenceCompleted,
        isImported: users.isImported,
        registeredOn: users.registeredOn
      }).from(users).where(eq(users.id, id));
      return user;
    } catch (error) {
      console.error('Error fetching user:', error);
      return undefined;
    }
  }

  async getUserByEmail(email: string): Promise<User | undefined> {
    try {
      const [user] = await db.select().from(users).where(eq(users.email, email));
      return user;
    } catch (error) {
      console.error('Error fetching user by email:', error);
      return undefined;
    }
  }

  async createUser(user: InsertUser): Promise<User> {
    const [newUser] = await db.insert(users).values(user).returning();
    return newUser;
  }

  async getAllUsers(): Promise<User[]> {
    try {
      const allUsers = await db
        .select({
          id: users.id,
          firstName: users.firstName,
          lastName: users.lastName,
          showName: users.showName,
          email: users.email,
          roles: users.roles,
          password: users.password,
          active: users.active,
          home: users.home,
          phone: users.phone,
          address: users.address,
          town: users.town,
          zipcode: users.zipcode,
          gender: users.gender,
          age: users.age,
          bio: users.bio,
          avatar: users.avatar,
          registeredOn: users.registeredOn,
          itemCount: sql<number>`(SELECT COUNT(*) FROM ${lendableItems} WHERE ${lendableItems.owner} = ${users.id} AND ${lendableItems.status} = true)`
        })
        .from(users)
        .where(eq(users.active, true));
      
      return allUsers;
    } catch (error) {
      console.error('Error fetching all users:', error);
      return [];
    }
  }

  async getDemoUsers(): Promise<User[]> {
    // Get users for demo homepage with their show_name using email addresses
    try {
      const result = await pool.query(`
        SELECT id, first_name, last_name, show_name, email, avatar, active 
        FROM users 
        WHERE email IN (
          'jacob.lowery@ruralcarpool.com', 'kiley.ford@ruralcarpool.com', 'brianna.jenkins@ruralcarpool.com', 'josh.mcintosh@ruralcarpool.com', 
          'allyson.mcfarland@ruralcarpool.com', 'chloe.trevino@ruralcarpool.com', 'dilly@locallendables.com', 'morgan.gilbert@ruralcarpool.com',
          'patricia.burton@ruralcarpool.com', 'adrien.mahoney@ruralcarpool.com', 'nora.rush@ruralcarpool.com', 'mariana.rivas@ruralcarpool.com',
          'jaelyn.oneal@ruralcarpool.com', 'adelyn.griffin@ruralcarpool.com'
        )
        AND active = true
        ORDER BY id;
      `);
      
      // Map email to demo username for template compatibility
      const emailToUsername = {
        'jacob.lowery@ruralcarpool.com': 'jacob.lowery',
        'kiley.ford@ruralcarpool.com': 'kiley.ford',
        'brianna.jenkins@ruralcarpool.com': 'brianna.jenkins',
        'josh.mcintosh@ruralcarpool.com': 'josh.mcintosh',
        'allyson.mcfarland@ruralcarpool.com': 'allyson.mcfarland',
        'chloe.trevino@ruralcarpool.com': 'chloe.trevino',
        'dilly@locallendables.com': 'dilly.admin',
        'morgan.gilbert@ruralcarpool.com': 'morgan.gilbert',
        'patricia.burton@ruralcarpool.com': 'patricia.burton',
        'adrien.mahoney@ruralcarpool.com': 'adrien.mahoney',
        'nora.rush@ruralcarpool.com': 'nora.rush',
        'mariana.rivas@ruralcarpool.com': 'mariana.rivas',
        'jaelyn.oneal@ruralcarpool.com': 'jaelyn.oneal',
        'adelyn.griffin@ruralcarpool.com': 'adelyn.griffin'
      };
      
      return result.rows.map(row => ({
        id: row.id,
        username: emailToUsername[row.email] || row.email,
        firstName: row.first_name,
        lastName: row.last_name,
        showName: row.show_name,
        email: row.email,
        avatar: row.avatar,
        active: row.active
      }));
    } catch (error) {
      console.error('Error fetching demo users:', error);
      return [];
    }
  }

  async authenticateUser(email: string, password: string): Promise<User | null> {
    try {
      const [user] = await db.select().from(users).where(eq(users.email, email));
      if (user && user.password === password) {
        return user;
      }
      return null;
    } catch (error) {
      console.error('Error authenticating user:', error);
      return null;
    }
  }

  async getUserItems(userId: number): Promise<any[]> {
    try {
      const userItems = await db
        .select({
          id: lendableItems.id,
          owner: lendableItems.owner,
          name: lendableItems.name,
          description: lendableItems.description,
          category: lendableItems.category,
          categoryName: lendableItemCategories.category,
          primaryLogo: lendableItems.primaryLogo,
          extraImages: lendableItems.extraImages,
          pricePerDay: lendableItems.pricePerDay,
          addedOn: lendableItems.addedOn,
          status: lendableItems.status,
          isRequest: lendableItems.isRequest,
          views: lendableItems.views
        })
        .from(lendableItems)
        .leftJoin(lendableItemCategories, eq(lendableItems.category, lendableItemCategories.id))
        .where(and(
          eq(lendableItems.owner, userId),
          eq(lendableItems.status, true)
        ))
        .orderBy(desc(lendableItems.addedOn));
      
      // Add thumbnail support and availability status for each item
      const itemsWithEnhancedData = await Promise.all(
        userItems.map(async (item) => {
          // Get loan status for availability
          let isAvailable = true;
          if (!item.isRequest) {
            const loanStatus = await this.getItemLoanStatus(item.id);
            isAvailable = !loanStatus.isOnLoan;
          }
          
          return {
            ...item,
            title: item.name, // Template expects 'title' field
            dailyRate: item.pricePerDay, // Template expects 'dailyRate' field
            thumbnailLogo: item.primaryLogo ? await getItemThumbnail(item.primaryLogo) : null,
            isAvailable: isAvailable,
            views: item.views || 0 // Use actual views from database
          };
        })
      );
      
      return itemsWithEnhancedData;
    } catch (error) {
      console.error('Error fetching user items:', error);
      return [];
    }
  }

  async getAllAvailableItems(randomize: boolean = false): Promise<any[]> {
    try {
      // Use dedicated connection for complex operation with timeout
      const availableItems = await db
        .select({
          id: lendableItems.id,
          owner: lendableItems.owner,
          name: lendableItems.name,
          description: lendableItems.description,
          specialInstructions: lendableItems.specialInstructions,
          category: lendableItems.category,
          categoryName: lendableItemCategories.category,
          primaryLogo: lendableItems.primaryLogo,
          extraImages: lendableItems.extraImages,
          pricePerDay: lendableItems.pricePerDay,
          deposit: lendableItems.deposit,
          addedOn: lendableItems.addedOn,
          isRequest: lendableItems.isRequest,
          ownerFirstName: users.firstName,
          ownerLastName: users.lastName,
          ownerShowName: users.showName,
          ownerAvatar: users.avatar
        })
        .from(lendableItems)
        .leftJoin(users, eq(lendableItems.owner, users.id))
        .leftJoin(lendableItemCategories, eq(lendableItems.category, lendableItemCategories.id))
        .where(and(
          eq(lendableItems.status, true),
          isNotNull(users.id)
        ))
        .orderBy(desc(lendableItems.addedOn));

      // Process thumbnails in smaller batches to prevent connection exhaustion
      const batchSize = 5;
      const itemsWithThumbnails = [];
      
      for (let i = 0; i < availableItems.length; i += batchSize) {
        const batch = availableItems.slice(i, i + batchSize);
        const processedBatch = await Promise.all(
          batch.map(async (item) => {
            try {
              const thumbnailLogo = item.primaryLogo ? await getItemThumbnail(item.primaryLogo) : null;
              return {
                ...item,
                thumbnailLogo
              };
            } catch (thumbError) {
              console.error(`Thumbnail generation failed for item ${item.id}:`, thumbError);
              return {
                ...item,
                thumbnailLogo: null
              };
            }
          })
        );
        itemsWithThumbnails.push(...processedBatch);
        
        // Small delay between batches to prevent overwhelming the system
        if (i + batchSize < availableItems.length) {
          await new Promise(resolve => setTimeout(resolve, 10));
        }
      }

      if (randomize) {
        // Shuffle the array for randomization
        for (let i = itemsWithThumbnails.length - 1; i > 0; i--) {
          const j = Math.floor(Math.random() * (i + 1));
          [itemsWithThumbnails[i], itemsWithThumbnails[j]] = [itemsWithThumbnails[j], itemsWithThumbnails[i]];
        }
      }

      return itemsWithThumbnails;
    } catch (error) {
      console.error('Error fetching available items:', error);
      return [];
    }
  }

  async getNewListings(limit: number = 10): Promise<any[]> {
    try {
      const newListings = await db
        .select({
          id: lendableItems.id,
          owner: lendableItems.owner,
          name: lendableItems.name,
          description: lendableItems.description,
          category: lendableItems.category,
          categoryName: lendableItemCategories.category,
          primaryLogo: lendableItems.primaryLogo,
          pricePerDay: lendableItems.pricePerDay,
          addedOn: lendableItems.addedOn,
          isRequest: lendableItems.isRequest,
          ownerFirstName: users.firstName,
          ownerLastName: users.lastName,
          ownerShowName: users.showName,
          ownerAvatar: users.avatar
        })
        .from(lendableItems)
        .leftJoin(users, eq(lendableItems.owner, users.id))
        .leftJoin(lendableItemCategories, eq(lendableItems.category, lendableItemCategories.id))
        .where(and(
          eq(lendableItems.status, true),
          isNotNull(users.id)
        ))
        .orderBy(desc(lendableItems.addedOn))
        .limit(limit);

      // Add thumbnail support for each item
      const listingsWithThumbnails = await Promise.all(
        newListings.map(async (item) => ({
          ...item,
          thumbnailLogo: item.primaryLogo ? await getItemThumbnail(item.primaryLogo) : (item.imageUrl ? await getItemThumbnail(item.imageUrl) : null)
        }))
      );

      // Randomize the order for each request
      for (let i = listingsWithThumbnails.length - 1; i > 0; i--) {
        const j = Math.floor(Math.random() * (i + 1));
        [listingsWithThumbnails[i], listingsWithThumbnails[j]] = [listingsWithThumbnails[j], listingsWithThumbnails[i]];
      }

      return listingsWithThumbnails;
    } catch (error) {
      console.error('Error fetching new listings:', error);
      return [];
    }
  }

  async getItem(id: number): Promise<any | undefined> {
    try {
      const [item] = await db
        .select({
          id: lendableItems.id,
          owner: lendableItems.owner,
          name: lendableItems.name,
          description: lendableItems.description,
          specialInstructions: lendableItems.specialInstructions,
          condition: lendableItems.condition,
          category: lendableItems.category,
          categoryName: lendableItemCategories.category,
          primaryLogo: lendableItems.primaryLogo,
          extraImages: lendableItems.extraImages,
          pricePerDay: lendableItems.pricePerDay,
          deposit: lendableItems.deposit,
          status: lendableItems.status,
          addedOn: lendableItems.addedOn,
          isRequest: lendableItems.isRequest,
          ownerFirstName: users.firstName,
          ownerLastName: users.lastName,
          ownerShowName: users.showName,
          ownerAvatar: users.avatar
        })
        .from(lendableItems)
        .leftJoin(users, eq(lendableItems.owner, users.id))
        .leftJoin(lendableItemCategories, eq(lendableItems.category, lendableItemCategories.id))
        .where(and(
          eq(lendableItems.id, id),
          eq(lendableItems.status, true)
        ));
      return item;
    } catch (error) {
      console.error('Error fetching item:', error);
      return undefined;
    }
  }

  async createItem(item: any): Promise<any> {
    // Use the lendable_items table for new item creation
    const [newItem] = await db.insert(lendableItems).values(item).returning();
    return newItem;
  }

  async updateItem(id: number, updates: any): Promise<any> {
    const [updatedItem] = await db
      .update(lendableItems)
      .set(updates)
      .where(eq(lendableItems.id, id))
      .returning();
    return updatedItem;
  }

  async deleteItem(id: number): Promise<void> {
    // Check for active loans using unified loan_transactions table
    const activeLoans = await db
      .select()
      .from(loanTransactions)
      .where(and(
        eq(loanTransactions.itemId, id),
        or(
          eq(loanTransactions.status, 'pending'),
          eq(loanTransactions.status, 'approved'),
          eq(loanTransactions.status, 'active'),
          eq(loanTransactions.status, 'overdue'),
          eq(loanTransactions.status, 'return_requested')
        )
      ));

    if (activeLoans.length > 0) {
      throw new Error('Cannot delete item: It has active loans that must be returned first');
    }

    // Remove any favorites for this item
    await db.delete(userFavorites).where(eq(userFavorites.itemId, id));
    
    // Remove any completed loan transactions (safe to clean up historical data)
    await db.delete(loanTransactions).where(and(
      eq(loanTransactions.itemId, id),
      or(
        eq(loanTransactions.status, 'returned'),
        eq(loanTransactions.status, 'declined'),
        eq(loanTransactions.status, 'cancelled'),
        eq(loanTransactions.status, 'expired')
      )
    ));
    
    // Finally delete the item
    await db.delete(lendableItems).where(eq(lendableItems.id, id));
  }

  async getAllCategories(): Promise<LendableItemCategory[]> {
    try {
      return await db
        .select()
        .from(lendableItemCategories)
        .where(eq(lendableItemCategories.status, true))
        .orderBy(lendableItemCategories.category);
    } catch (error) {
      console.error('Error fetching categories:', error);
      return [];
    }
  }

  async getUserStats(userId: number): Promise<{
    itemsShared: number;
    itemsBorrowed: number;
    totalMembers: number;
    itemsCurrentlyLent: number;
    totalViews: number;
    activeConversations: number;
    joinedDate: string;
  }> {
    try {
      // Use comprehensive queries across all loan tracking systems per v0.86 architecture
      const results = await Promise.all([
        // Items shared (owned by user and active)
        pool.query(`SELECT COUNT(*) as count FROM lendable_items WHERE owner = $1 AND status = true`, [userId]),
        
        // Items borrowed (unified system - count all current/active loan transactions)
        // Uses only loan_transactions table, matching spreadsheet logic
        pool.query(`
          SELECT COUNT(DISTINCT item_id) as count 
          FROM loan_transactions 
          WHERE borrower_id = $1 
          AND status NOT IN ('returned', 'denied', 'dismissed')
        `, [userId]),
        
        // Total active members
        pool.query(`SELECT COUNT(*) as count FROM users WHERE active = true`),
        
        // Items currently lent (unified system - using only loan_transactions)
        pool.query(`
          SELECT COUNT(DISTINCT lt.item_id) as count 
          FROM loan_transactions lt 
          INNER JOIN lendable_items li ON lt.item_id = li.id 
          WHERE li.owner = $1 AND lt.status IN ('pending', 'approved', 'active', 'return_requested')
        `, [userId]),
        
        // Total views (sum of actual views from all user's items)
        pool.query(`SELECT COALESCE(SUM(views), 0) as count FROM lendable_items WHERE owner = $1 AND status = true`, [userId]),
        
        // Active conversations
        pool.query(`SELECT COUNT(*) as count FROM conversations WHERE owner_id = $1 OR borrower_id = $1`, [userId])
      ]);
      
      const [itemsShared, itemsBorrowed, totalMembers, itemsLent, totalViews, conversations] = results;
      
      // Get user join date
      const userQuery = `SELECT registered_on FROM users WHERE id = $1`;
      const userResult = await pool.query(userQuery, [userId]);
      const joinedDate = userResult.rows.length > 0 ? userResult.rows[0].registered_on : new Date();

      return {
        itemsShared: parseInt(itemsShared.rows[0]?.count || '0') || 0,
        itemsBorrowed: parseInt(itemsBorrowed.rows[0]?.count || '0') || 0,
        totalMembers: parseInt(totalMembers.rows[0]?.count || '0') || 0,
        itemsCurrentlyLent: parseInt(itemsLent.rows[0]?.count || '0') || 0,
        totalViews: parseInt(totalViews.rows[0]?.count || '0') || 0,
        activeConversations: parseInt(conversations.rows[0]?.count || '0') || 0,
        joinedDate: joinedDate.toISOString()
      };
    } catch (error) {
      console.error('Error fetching user stats:', error);
      return { 
        itemsShared: 0, 
        itemsBorrowed: 0, 
        totalMembers: 0,
        itemsCurrentlyLent: 0,
        totalViews: 0,
        activeConversations: 0,
        joinedDate: new Date().toISOString()
      };
    }
  }

  async getRecentActivity(userId: number): Promise<any[]> {
    try {
      // For now, return an empty array since we don't have activity tracking yet
      return [];
    } catch (error) {
      console.error('Error fetching recent activity:', error);
      return [];
    }
  }

  async getItemLoanStatus(itemId: number): Promise<any> {
    try {
      // UNIFIED SYSTEM: Use only loan_transactions table
      const [loan] = await db
        .select({
          trackingId: loanTransactions.id,
          status: loanTransactions.status,
          pickedUpAt: loanTransactions.pickedUpAt,
          firstName: users.firstName,
          showName: users.showName,
          avatar: users.avatar,
          startDate: loanTransactions.requestedDate,
          endDate: loanTransactions.expectedReturnDate,
          agreementId: loanTransactions.agreementId,
          returnQrCode: loanAgreements.returnQrCode
        })
        .from(loanTransactions)
        .leftJoin(users, eq(loanTransactions.borrowerId, users.id))
        .leftJoin(loanAgreements, eq(loanTransactions.agreementId, loanAgreements.id))
        .where(and(
          eq(loanTransactions.itemId, itemId),
          inArray(loanTransactions.status, ['pending', 'approved', 'active', 'return_requested'])
        ))
        .orderBy(desc(loanTransactions.requestedDate))
        .limit(1);
      
      if (!loan) {
        return { isOnLoan: false };
      }
      
      return {
        isOnLoan: true,
        trackingId: loan.trackingId,
        agreementId: loan.agreementId,
        status: loan.status,
        borrowerName: loan.showName || loan.firstName,
        borrowerAvatar: loan.avatar,
        startDate: loan.startDate,
        endDate: loan.endDate,
        pickedUp: !!loan.pickedUpAt,
        returnQrCode: loan.returnQrCode,
        statusDisplay: loan.status === 'pending' ? 'Waiting for Agreement Signature' :
                     loan.status === 'return_requested' ? 'Return Pending' : 
                     loan.status === 'active' ? 'Reserved/Out on Loan' : 
                     loan.status === 'approved' ? 'Ready for Pickup' :
                     loan.status === 'reserved' ? 'Reserved/Out on Loan' : loan.status
      };
    } catch (error) {
      console.error('Error checking item loan status:', error);
      return { isOnLoan: false };
    }
  }

  async incrementItemViews(itemId: number): Promise<void> {
    try {
      await db
        .update(lendableItems)
        .set({ 
          views: sql`${lendableItems.views} + 1` 
        })
        .where(eq(lendableItems.id, itemId));
      console.log(`View incremented for item ${itemId}`);
    } catch (error) {
      console.error('Error incrementing item views:', error);
    }
  }

  // Generate time-sensitive notifications for loan returns
  async generateLoanReturnNotifications(): Promise<void> {
    try {
      const now = new Date();
      const tomorrow = new Date(now);
      tomorrow.setDate(tomorrow.getDate() + 1);
      
      // Get all active loan transactions with proper timing logic - exclude returned items (UNIFIED SYSTEM)
      const activeLoans = await pool.query(`
        SELECT 
          lt.id,
          lt.borrower_id,
          lt.expected_return_date as end_date,
          li.id as item_id,
          li.name as item_name,
          li.owner as owner_id,
          CASE 
            WHEN lt.expected_return_date::date = CURRENT_DATE + INTERVAL '1 day' THEN 'due_tomorrow'
            WHEN lt.expected_return_date::date = CURRENT_DATE THEN 'due_today'
            WHEN lt.expected_return_date::date < CURRENT_DATE THEN 'overdue'
            ELSE 'future'
          END as notification_type,
          lt.expected_return_date::date < CURRENT_DATE as is_past_due_date
        FROM loan_transactions lt
        JOIN lendable_items li ON lt.item_id = li.id
        WHERE lt.status IN ('approved', 'active')
        AND lt.status != 'returned'
        AND lt.returned_at IS NULL
        AND NOT EXISTS (
          SELECT 1 FROM notifications 
          WHERE user_id = lt.borrower_id 
          AND type = 'loan_completed' 
          AND message LIKE '%' || li.name || '%'
        )
        AND lt.expected_return_date IS NOT NULL
        AND (
          lt.expected_return_date::date = CURRENT_DATE + INTERVAL '1 day' OR
          lt.expected_return_date::date = CURRENT_DATE OR
          lt.expected_return_date::date < CURRENT_DATE
        )
      `);

      for (const loan of activeLoans.rows) {
        const { borrower_id, owner_id, item_id, item_name, end_date, notification_type, is_past_due_date } = loan;
        
        // Skip overdue notifications if we're still on the due date
        if (notification_type === 'overdue' && !is_past_due_date) {
          continue;
        }
        const dueDateStr = new Date(end_date).toLocaleDateString();

        // Check if notification already exists for today (and delete old conflicting notifications)
        if (notification_type === 'overdue') {
          // When creating overdue notification, delete any existing due_today notifications for the same item
          await pool.query(`
            DELETE FROM notifications 
            WHERE user_id = $1 
            AND type = 'return_due_today'
            AND message LIKE '%' || $2 || '%'
          `, [borrower_id, item_name]);
          
          await pool.query(`
            DELETE FROM notifications 
            WHERE user_id = $1 
            AND type = 'owner_due_today'
            AND message LIKE '%' || $2 || '%'
          `, [owner_id, item_name]);
        }
        
        const existingNotification = await pool.query(`
          SELECT id FROM notifications 
          WHERE user_id = $1 
          AND type = $2
          AND message LIKE '%' || $3 || '%'
          AND created_at::date = CURRENT_DATE
        `, [borrower_id, `return_${notification_type}`, item_name]);

        if (existingNotification.rows.length === 0) {
          let borrowerMessage = '';
          let ownerMessage = '';

          switch (notification_type) {
            case 'due_tomorrow':
              borrowerMessage = `Time to Return Item: "${item_name}" is due tomorrow (${dueDateStr})`;
              ownerMessage = `Lendable "${item_name}" due date approaching (${dueDateStr})`;
              break;
            case 'due_today':
              borrowerMessage = `Time to Return Item: "${item_name}" is due today (${dueDateStr})`;
              ownerMessage = `Lendable "${item_name}" due date approaching (${dueDateStr})`;
              break;
            case 'overdue':
              borrowerMessage = `Overdue! Item "${item_name}" was due on ${dueDateStr}`;
              ownerMessage = `Item "${item_name}" Overdue! Shall we send a reminder?`;
              break;
          }

          // Create notification for borrower
          if (borrowerMessage) {
            await db.insert(notifications).values({
              userId: borrower_id,
              type: `return_${notification_type}`,
              title: notification_type === 'overdue' ? 'Item Return Overdue' : 'Item Return Reminder',
              message: borrowerMessage,
              itemId: item_id,
              isRead: false,
              createdAt: now
            });
          }

          // Create notification for owner
          if (ownerMessage) {
            await db.insert(notifications).values({
              userId: owner_id,
              type: `owner_${notification_type}`,
              title: notification_type === 'overdue' ? 'Item Overdue' : 'Item Due Soon',
              message: ownerMessage,
              itemId: item_id,
              isRead: false,
              createdAt: now
            });
          }
        }
      }
      
      console.log('Daily loan notification generation completed');
      
      // ENHANCED: Clean up any stale notifications after generation
      await this.cleanupStaleNotifications();
      
    } catch (error) {
      console.error('Error generating loan return notifications:', error);
    }
  }

  // NEW: Clean up stale notifications for items that are already returned
  async cleanupStaleNotifications(): Promise<void> {
    try {
      console.log('Starting stale notification cleanup...');
      
      // Find notifications for items that are already returned but notifications still exist
      const staleNotifications = await pool.query(`
        SELECT DISTINCT n.id, n.user_id, n.type, n.item_id, n.message, li.name as item_name
        FROM notifications n
        JOIN lendable_items li ON n.item_id = li.id
        WHERE n.type IN (
          'return_overdue', 'return_due_today', 'return_due_tomorrow',
          'owner_overdue', 'owner_due_today', 'owner_due_tomorrow'
        )
        AND n.is_dismissed = false
        AND NOT EXISTS (
          SELECT 1 FROM loan_transactions lt
          WHERE lt.item_id = n.item_id
          AND lt.status IN ('approved', 'active', 'return_requested')
          AND lt.returned_at IS NULL
        )
      `);
      
      if (staleNotifications.rows.length > 0) {
        console.log(`Found ${staleNotifications.rows.length} stale notifications to clean up`);
        
        // Mark all stale notifications as dismissed
        const staleIds = staleNotifications.rows.map(n => n.id);
        await pool.query(`
          UPDATE notifications 
          SET is_read = true, is_dismissed = true
          WHERE id = ANY($1)
        `, [staleIds]);
        
        console.log(`Cleaned up ${staleIds.length} stale notifications`);
      } else {
        console.log('No stale notifications found');
      }
    } catch (error) {
      console.error('Error cleaning up stale notifications:', error);
    }
  }

  // Get system notifications (time-sensitive return notifications)
  async getSystemNotifications(userId: number): Promise<any[]> {
    try {
      const systemNotifications = await db
        .select()
        .from(notifications)
        .where(and(
          eq(notifications.userId, userId),
          or(
            sql`type LIKE 'return_%'`,
            sql`type LIKE 'owner_%'`,
            eq(notifications.type, 'loan_completed'),
            eq(notifications.type, 'agreement_ready')
          )
        ))
        .orderBy(desc(notifications.createdAt));

      console.log(`DEBUG: getSystemNotifications for user ${userId} found ${systemNotifications.length} notifications:`, 
        systemNotifications.map(n => ({ id: n.id, type: n.type, isRead: n.isRead, isDismissed: n.isDismissed })));
      
      return systemNotifications;
    } catch (error) {
      console.error('Error fetching system notifications:', error);
      return [];
    }
  }

  // Clean up old/duplicate notifications to reduce clutter
  async cleanupOldNotifications(): Promise<void> {
    try {
      // Delete notifications older than 30 days that are read
      await db.delete(notifications)
        .where(and(
          eq(notifications.isRead, true),
          sql`created_at < NOW() - INTERVAL '30 days'`
        ));

      // Delete duplicate notifications (keep only the most recent per user per item)
      await pool.query(`
        DELETE FROM notifications n1
        USING notifications n2
        WHERE n1.user_id = n2.user_id
        AND n1.type = n2.type
        AND n1.message = n2.message
        AND n1.created_at < n2.created_at
        AND n1.type IN ('return_overdue', 'return_due_today', 'return_due_tomorrow', 'owner_overdue', 'owner_due_today', 'owner_due_tomorrow')
      `);

      console.log('Old notifications cleaned up successfully');
    } catch (error) {
      console.error('Error cleaning up notifications:', error);
    }
  }

  // Mark notification as read
  async markNotificationAsRead(notificationId: number): Promise<void> {
    try {
      await db
        .update(notifications)
        .set({ isRead: true })
        .where(eq(notifications.id, notificationId));
    } catch (error) {
      console.error('Error marking notification as read:', error);
      throw error;
    }
  }

  async createLoanRequest(data: any): Promise<any> {
    try {
      // For now, return null since loan request system is not implemented yet
      console.log('Loan request created:', data);
      return null;
    } catch (error) {
      console.error('Error creating loan request:', error);
      return null;
    }
  }

  async getItemAvailabilityForMonth(itemId: number, year: number, month: number): Promise<any[]> {
    try {
      console.log(`Fetching calendar availability for item ${itemId} (UNIFIED SYSTEM)`);
      
      // UNIFIED SYSTEM: Use only loan_transactions table
      const loanTransactionsResult = await db
        .select({
          startDate: loanTransactions.borrowedDate,
          endDate: loanTransactions.expectedReturnDate,
          status: loanTransactions.status,
          type: sql<string>`'loan'`,
          borrowerId: loanTransactions.borrowerId,
          borrowerName: sql<string>`COALESCE(${users.showName}, ${users.firstName})`,
          borrowerAvatar: users.avatar
        })
        .from(loanTransactions)
        .leftJoin(users, eq(loanTransactions.borrowerId, users.id))
        .where(and(
          eq(loanTransactions.itemId, itemId),
          // Include pending, approved, active, and return_requested loans
          inArray(loanTransactions.status, ['pending', 'approved', 'active', 'return_requested']),
          // Only include loans that haven't been returned
          isNull(loanTransactions.returnedAt)
        ));

      console.log(`Found ${loanTransactionsResult.length} active loan transactions for calendar`);

      // Convert to expected format
      const allBookings = loanTransactionsResult.map(loan => ({
        start_date: loan.startDate,
        end_date: loan.endDate,
        status: loan.status,
        type: loan.type,
        borrowerId: loan.borrowerId,
        borrowerName: loan.borrowerName || 'Unknown User',
        borrowerAvatar: loan.borrowerAvatar
      }));

      console.log(`Calendar bookings for item ${itemId}:`, allBookings.map(b => `${b.status}: ${b.start_date} to ${b.end_date} by ${b.borrowerName}`));

      return allBookings;
    } catch (error) {
      console.error('Error fetching item availability:', error);
      return [];
    }
  }

  async getItemLoanCalendar(itemId: number): Promise<any[]> {
    try {
      console.log(`Getting calendar data for item ${itemId} using UNIFIED SYSTEM`);
      
      // STEP 1: Get all loan transactions for this item
      const loanTransactionResults = await db
        .select({
          id: loanTransactions.id,
          itemId: loanTransactions.itemId,
          borrowerId: loanTransactions.borrowerId,
          ownerId: loanTransactions.ownerId,
          status: loanTransactions.status,
          requestedDate: loanTransactions.requestedDate,
          approvedDate: loanTransactions.approvedDate,
          borrowedDate: loanTransactions.borrowedDate,
          expectedReturnDate: loanTransactions.expectedReturnDate,
          returnedAt: loanTransactions.returnedAt,
          sourceSystem: loanTransactions.sourceSystem,
          borrowerFirstName: users.firstName,
          borrowerShowName: users.showName,
          borrowerAvatar: users.avatar
        })
        .from(loanTransactions)
        .leftJoin(users, eq(loanTransactions.borrowerId, users.id))
        .where(and(
          eq(loanTransactions.itemId, itemId),
          // Include pending, approved, active, return_requested (exclude rejected, cancelled, returned)
          inArray(loanTransactions.status, ['pending', 'approved', 'active', 'return_requested'])
        ))
        .orderBy(desc(loanTransactions.requestedDate));

      console.log(`Found ${loanTransactionResults.length} loan transactions for item ${itemId}`);
      
      const currentDate = new Date();
      currentDate.setHours(0, 0, 0, 0);
      
      const calendarData = loanTransactionResults.map(loan => {
        // Use borrowedDate if available, otherwise approvedDate for start
        const startDate = loan.borrowedDate || loan.approvedDate || loan.requestedDate;
        const endDate = loan.expectedReturnDate;
        
        // Determine actual status based on dates and current status
        let actualStatus = loan.status;
        if (loan.status === 'approved' && endDate) {
          const loanEndDate = new Date(endDate);
          loanEndDate.setHours(0, 0, 0, 0);
          
          // If approved loan is past due, mark as overdue
          if (loanEndDate < currentDate) {
            actualStatus = 'overdue';
          }
        }
        
        const borrowerName = loan.borrowerShowName || loan.borrowerFirstName || 'Unknown';
        
        console.log(`Loan ${loan.id}: ${startDate} to ${endDate}, status: ${actualStatus}, borrower: ${borrowerName}`);
        
        return {
          id: loan.id,
          borrowedDate: startDate,
          expectedReturnDate: endDate,
          status: actualStatus,
          borrowerName: borrowerName,
          borrowerAvatar: loan.borrowerAvatar,
          borrowerId: loan.borrowerId,
          source: 'loan_transactions'
        };
      });

      // Sort by start date, most recent first
      calendarData.sort((a, b) => {
        const dateA = new Date(a.borrowedDate);
        const dateB = new Date(b.borrowedDate);
        return dateB.getTime() - dateA.getTime();
      });

      console.log(`Returning ${calendarData.length} calendar entries for item ${itemId}`);
      return calendarData;
    } catch (error) {
      console.error('Error fetching item loan calendar (unified system):', error);
      return [];
    }
  }



  async updateItemImages(itemId: number, primaryLogo?: string, extraImages?: string): Promise<void> {
    try {
      const updates: any = {};
      if (primaryLogo !== undefined) updates.primaryLogo = primaryLogo;
      if (extraImages !== undefined) updates.extraImages = extraImages;
      
      if (Object.keys(updates).length > 0) {
        await db
          .update(lendableItems)
          .set(updates)
          .where(eq(lendableItems.id, itemId));
      }
    } catch (error) {
      console.error('Error updating item images:', error);
    }
  }

  async isUserAdmin(userId: number): Promise<boolean> {
    try {
      const user = await this.getUser(userId);
      if (!user) return false;
      
      // Handle both string and already parsed roles
      let roles = [];
      if (typeof user.roles === 'string') {
        try {
          roles = JSON.parse(user.roles);
        } catch (parseError) {
          // If JSON parsing fails, treat as empty roles
          console.log('Roles field is not valid JSON, treating as empty:', user.roles);
          roles = [];
        }
      } else {
        roles = user.roles || [];
      }
      
      return roles.includes('ROLE_ADMIN') || user.id === 1; // Admin user ID 1 fallback
    } catch (error) {
      console.error('Error checking admin status:', error);
      return false;
    }
  }

  async getActiveLoans(userId: number): Promise<any[]> {
    try {
      console.log(`Getting active loans for user ${userId} using UNIFIED SYSTEM`);
      
      // Use unified loan_transactions table only
      const result = await pool.query(`
        SELECT 
          lt.id,
          lt.item_id as "itemId",
          lt.borrowed_date as "borrowedDate", 
          lt.expected_return_date as "expectedReturnDate",
          lt.status,
          lt.agreement_id as "agreementId",
          la.status as "agreementStatus",
          li.name as "itemName",
          li.primary_logo as "itemImage",
          COALESCE(u.show_name, u.first_name || ' ' || u.last_name, u.first_name, 'Unknown') as "ownerName",
          u.avatar as "ownerAvatar"
        FROM loan_transactions lt
        LEFT JOIN lendable_items li ON lt.item_id = li.id
        LEFT JOIN users u ON li.owner = u.id
        LEFT JOIN loan_agreements la ON lt.agreement_id = la.id
        WHERE lt.borrower_id = $1
        AND lt.status IN ('pending', 'approved', 'active', 'return_requested')
        ORDER BY lt.requested_date DESC
      `, [userId]);

      console.log(`Found ${result.rows.length} active loan transactions for user ${userId}`);

      const currentDate = new Date();
      currentDate.setHours(0, 0, 0, 0);

      return result.rows.map(loan => {
        // Determine if loan is overdue
        let isOverdue = false;
        if (loan.expectedReturnDate) {
          const returnDate = new Date(loan.expectedReturnDate);
          returnDate.setHours(0, 0, 0, 0);
          isOverdue = returnDate < currentDate;
        }

        console.log(`Active loan ${loan.id}: item ${loan.itemId}, status: ${loan.status}, overdue: ${isOverdue}, due: ${loan.expectedReturnDate}`);

        return {
          id: loan.id,
          itemId: loan.itemId,
          item_name: loan.itemName,
          item_image: loan.itemImage,
          owner_show_name: loan.ownerName,
          owner_first_name: loan.ownerName,
          owner_avatar: loan.ownerAvatar,
          start_date: loan.borrowedDate,
          end_date: loan.expectedReturnDate,
          status: isOverdue ? 'overdue' : loan.status,
          is_overdue: isOverdue,
          agreementId: loan.agreementId,
          agreementStatus: loan.agreementStatus,
          source: 'loan_transactions'
        };
      });
    } catch (error) {
      console.error('Error fetching active loans (unified system):', error);
      return [];
    }
  }

  async getOwnerLoans(userId: number): Promise<any[]> {
    try {
      console.log(`Getting owner loans for user ${userId} using UNIFIED SYSTEM (checking all sources)`);
      
      // Get loans from unified loan_transactions table where user is the owner
      const loanResults = await db
        .select({
          id: loanTransactions.id,
          itemId: loanTransactions.itemId,
          borrowerId: loanTransactions.borrowerId,
          status: loanTransactions.status,
          borrowedDate: loanTransactions.borrowedDate,
          expectedReturnDate: loanTransactions.expectedReturnDate,
          returnedAt: loanTransactions.returnedAt,
          itemName: lendableItems.name,
          itemImage: lendableItems.primaryLogo,
          thumbnail: lendableItems.primaryLogo,
          primaryImage: lendableItems.primaryLogo,
          borrowerFirstName: users.firstName,
          borrowerShowName: users.showName,
          borrowerAvatar: users.avatar,
          sourceSystem: loanTransactions.sourceSystem,
          agreementId: loanTransactions.agreementId
        })
        .from(loanTransactions)
        .leftJoin(lendableItems, eq(loanTransactions.itemId, lendableItems.id))
        .leftJoin(users, eq(loanTransactions.borrowerId, users.id))
        .where(and(
          eq(loanTransactions.ownerId, userId),
          // Include pending, approved, active, return_requested (exclude returned, rejected, cancelled)
          inArray(loanTransactions.status, ['pending', 'approved', 'active', 'return_requested']),
          // Only include loans that haven't been returned
          isNull(loanTransactions.returnedAt)
        ))
        .orderBy(desc(loanTransactions.borrowedDate));

      console.log(`Found ${loanResults.length} active loans in unified loan_transactions system for owner ${userId}`);

      const currentDate = new Date();
      currentDate.setHours(0, 0, 0, 0);

      // Process loans from unified system only
      const allLoans = loanResults;

      return allLoans.map(loan => {
        // Determine if loan is overdue
        let isOverdue = false;
        if (loan.expectedReturnDate) {
          const returnDate = new Date(loan.expectedReturnDate);
          returnDate.setHours(0, 0, 0, 0);
          isOverdue = returnDate < currentDate;
        }

        const borrowerName = loan.borrowerShowName || loan.borrowerFirstName || 'Unknown';
        const source = loan.sourceSystem || 'loan_transactions';
        
        console.log(`Owner loan ${loan.id}: item ${loan.itemId}, borrower: ${borrowerName}, status: ${loan.status}, overdue: ${isOverdue}, source: ${source}`);

        return {
          id: loan.id,
          itemId: loan.itemId,
          itemName: loan.itemName,
          itemImage: loan.itemImage,
          thumbnail: loan.itemImage,
          primaryImage: loan.itemImage,
          borrowerName: borrowerName,
          borrowerAvatar: loan.borrowerAvatar,
          borrowerId: loan.borrowerId,
          borrowedDate: loan.borrowedDate,
          expectedReturnDate: loan.expectedReturnDate,
          status: isOverdue ? 'overdue' : loan.status,
          is_overdue: isOverdue,
          source: source,
          agreementId: loan.agreementId
        };
      });
    } catch (error) {
      console.error('Error fetching owner loans (unified system):', error);
      return [];
    }
  }

  // REMOVED: Legacy getBorrowRequestsByOwner function - replaced with unified system version

  async deleteBorrowRequest(requestId: number): Promise<void> {
    try {
      // First delete any associated loan agreements
      await db
        .delete(loanAgreements)
        .where(eq(loanAgreements.borrowRequestId, requestId));
      
      // Then delete the loan transaction
      await db
        .delete(loanTransactions)
        .where(eq(loanTransactions.id, requestId));
      
      console.log(`Deleted loan transaction ${requestId} and associated records`);
    } catch (error) {
      console.error('Error deleting loan transaction:', error);
      throw error;
    }
  }

  async canUserEditItem(userId: number, itemId: number): Promise<boolean> {
    try {
      // Check if user is admin first
      const isAdmin = await this.isUserAdmin(userId);
      if (isAdmin) return true;
      
      // Check if user owns the item
      const item = await this.getItem(itemId);
      return item ? item.owner === userId : false;
    } catch (error) {
      console.error('Error checking edit permissions:', error);
      return false;
    }
  }

  async getItemWithOwner(itemId: number): Promise<any> {
    try {
      const result = await db
        .select({
          id: lendableItems.id,
          name: lendableItems.name,
          description: lendableItems.description,
          primaryLogo: lendableItems.primaryLogo,
          pricePerDay: lendableItems.pricePerDay,
          category: lendableItems.category,
          owner: lendableItems.owner,
          ownerFirstName: users.firstName,
          ownerShowName: users.showName,
          categoryName: lendableItemCategories.category
        })
        .from(lendableItems)
        .leftJoin(users, eq(lendableItems.owner, users.id))
        .leftJoin(lendableItemCategories, eq(lendableItems.category, lendableItemCategories.id))
        .where(eq(lendableItems.id, itemId));
      
      return result[0];
    } catch (error) {
      console.error('Error fetching item with owner:', error);
      return undefined;
    }
  }

  async createBorrowRequest(request: InsertBorrowRequest): Promise<BorrowRequest> {
    try {
      // Create unified loan transaction directly
      const [loanTransaction] = await db
        .insert(loanTransactions)
        .values({
          itemId: request.itemId,
          borrowerId: request.borrowerId,
          ownerId: request.ownerId,
          status: 'pending',
          requestedDate: new Date(),
          startDate: request.startDate,
          endDate: request.endDate,
          message: request.message || null
        })
        .returning();
      
      // Get or create conversation for this item/borrower/owner combination
      let conversation = await this.getConversation(request.itemId, request.ownerId, request.borrowerId);
      
      if (!conversation) {
        conversation = await this.createConversation({
          itemId: request.itemId,
          ownerId: request.ownerId,
          borrowerId: request.borrowerId
        });
      }
      
      // Create automatic chat message showing the borrow request
      const startDateFormatted = request.startDate ? new Date(request.startDate).toLocaleDateString() : 'TBD';
      const endDateFormatted = request.endDate ? new Date(request.endDate).toLocaleDateString() : 'TBD';
      
      let requestMessageText = `Borrow request: ${startDateFormatted} - ${endDateFormatted}`;
      if (request.message) {
        requestMessageText += `\n\nMessage: ${request.message}`;
      }
      
      // Add the initial chat message showing the borrow request
      await this.sendChatMessage({
        conversationId: conversation.id,
        senderId: request.borrowerId,
        messageType: 'text',
        textContent: requestMessageText
      });
      
      return loanTransaction;
    } catch (error) {
      console.error('Error creating borrow request:', error);
      throw error;
    }
  }

  async getBorrowRequest(id: number): Promise<BorrowRequest | undefined> {
    try {
      console.log(`Getting loan transaction with ID: ${id} (type: ${typeof id}) from unified system`);
      
      // Use unified loan_transactions table with item name
      const [result] = await db
        .select({
          id: loanTransactions.id,
          itemId: loanTransactions.itemId,
          borrowerId: loanTransactions.borrowerId,
          ownerId: loanTransactions.ownerId,
          borrowedDate: loanTransactions.borrowedDate,
          expectedReturnDate: loanTransactions.expectedReturnDate,
          message: loanTransactions.message,
          status: loanTransactions.status,
          createdAt: loanTransactions.createdAt,
          approvedDate: loanTransactions.approvedDate,
          agreementId: loanTransactions.agreementId,
          itemName: lendableItems.name
        })
        .from(loanTransactions)
        .innerJoin(lendableItems, eq(loanTransactions.itemId, lendableItems.id))
        .where(eq(loanTransactions.id, id));
      
      if (result) {
        console.log(`Loan transaction found: ID ${result.id}, owner ${result.ownerId}, borrower ${result.borrowerId}, itemName: ${result.itemName}`);
        return {
          id: result.id,
          itemId: result.itemId,
          borrowerId: result.borrowerId,
          ownerId: result.ownerId,
          startDate: result.borrowedDate,
          endDate: result.expectedReturnDate,
          message: result.message || '',
          status: result.status,
          createdAt: result.createdAt,
          respondedAt: result.approvedDate,
          expiresAt: null, // unified system doesn't have expiry dates
          ownerResponse: '',
          modifiedStartDate: result.borrowedDate,
          modifiedEndDate: result.expectedReturnDate,
          agreementId: result.agreementId,
          itemName: result.itemName // This was missing!
        };
      } else {
        console.log(`Loan transaction with ID ${id} not found`);
        return undefined;
      }
    } catch (error) {
      console.error('Error fetching loan transaction:', error);
      return undefined;
    }
  }

  async getBorrowRequestsByOwner(ownerId: number): Promise<any[]> {
    try {
      // Use unified loan_transactions table - simplified to avoid SQL errors
      const loanTransactionResults = await db
        .select()
        .from(loanTransactions)
        .where(and(
          eq(loanTransactions.ownerId, ownerId),
          eq(loanTransactions.status, 'pending')
        ))
        .orderBy(desc(loanTransactions.createdAt));

      console.log(`Found ${loanTransactionResults.length} pending loan requests for owner ${ownerId}`);

      // Enrich with item and user data separately to avoid SQL template errors
      const enrichedResults = await Promise.all(
        loanTransactionResults.map(async (row) => {
          const item = await this.getItem(row.itemId);
          const borrower = await this.getUser(row.borrowerId);
          return {
            id: row.id,
            itemId: row.itemId,
            itemName: item?.name || 'Unknown Item',
            borrowerName: borrower?.showName || borrower?.firstName || 'Unknown User',
            startDate: row.borrowedDate,
            endDate: row.expectedReturnDate,
            message: row.message,
            status: row.status,
            createdAt: row.createdAt
          };
        })
      );

      return enrichedResults;
    } catch (error) {
      console.error('Error fetching loan requests for owner:', error);
      return [];
    }
  }



  async getBorrowRequestsByBorrower(borrowerId: number): Promise<any[]> {
    try {
      return await db.select().from(loanTransactions).where(eq(loanTransactions.borrowerId, borrowerId));
    } catch (error) {
      console.error('Error fetching borrower requests:', error);
      return [];
    }
  }

  async updateBorrowRequestStatus(id: number, status: string, ownerResponse?: string, modifiedStartDate?: Date, modifiedEndDate?: Date): Promise<void> {
    try {
      const updates: any = { status, approvedDate: new Date() };
      if (ownerResponse) updates.ownerResponse = ownerResponse;
      if (modifiedStartDate) updates.startDate = modifiedStartDate;
      if (modifiedEndDate) updates.endDate = modifiedEndDate;
      
      await db
        .update(loanTransactions)
        .set(updates)
        .where(eq(loanTransactions.id, id));
    } catch (error) {
      console.error('Error updating borrow request status:', error);
      throw error;
    }
  }

  async updateLoanTransactionStatus(id: number, status: string, ownerResponse?: string, modifiedStartDate?: Date, modifiedEndDate?: Date): Promise<void> {
    try {
      const updates: any = { status, approvedDate: new Date() };
      if (ownerResponse) updates.ownerResponse = ownerResponse; // Store response in owner_response field
      if (modifiedStartDate) updates.borrowedDate = modifiedStartDate;
      if (modifiedEndDate) updates.expectedReturnDate = modifiedEndDate;
      
      await db
        .update(loanTransactions)
        .set(updates)
        .where(eq(loanTransactions.id, id));
      
      console.log(`Updated loan transaction ${id} status to: ${status}`);
    } catch (error) {
      console.error('Error updating loan transaction status:', error);
      throw error;
    }
  }

  async createLoanAgreement(data: {
    borrowRequestId: number;
    itemId: number;
    borrowerId: number;
    ownerId: number;
    startDate: Date;
    endDate: Date;
    agreementTerms: string;
    dailyRate?: number;
    totalAmount?: number;
    returnInstructions?: string;
    status?: string;
  }): Promise<any> {
    try {
      // Business rule: ALL loans and rentals require proactive signing action
      const requiresSignature = true;
      
      // Use provided status or default to pending (requiring signatures)
      const agreementStatus = data.status || 'pending';
      
      // Get or create the item's permanent QR code (for convenience, not requirement)
      const itemQR = await this.getOrCreateItemQR(data.itemId);
      
      const [agreement] = await db.insert(loanAgreements).values({
        borrowRequestId: data.borrowRequestId,
        itemId: data.itemId,
        borrowerId: data.borrowerId,
        ownerId: data.ownerId,
        startDate: data.startDate,
        endDate: data.endDate,
        agreementTerms: data.agreementTerms,
        dailyRate: data.dailyRate?.toString(),
        totalAmount: data.totalAmount?.toString(),
        returnInstructions: data.returnInstructions || 'Please return item in original condition',
        returnQrCode: itemQR.qr_code,
        status: agreementStatus
      }).returning();
      
      // Create loan tracking record with appropriate initial status
      const initialStatus = (agreementStatus === 'pending') ? 'reserved' : 'active';
      
      // Create corresponding loan transaction record
      await db.insert(loanTransactions).values({
        itemId: data.itemId,
        borrowerId: data.borrowerId,
        ownerId: data.ownerId,
        startDate: data.startDate,
        endDate: data.endDate,
        status: agreementStatus === 'pending' ? 'approved' : 'active',
        agreementId: agreement.id,
        sourceSystem: 'loan_agreement'
      });
      
      // If no signature required, mark item as unavailable immediately
      if (agreementStatus === 'signed') {
        await pool.query(`
          UPDATE lendable_items 
          SET is_available = false 
          WHERE id = $1
        `, [data.itemId]);
      }
      
      return agreement;
    } catch (error) {
      console.error('Error creating loan agreement:', error);
      throw error;
    }
  }

  async signAgreement(agreementId: number, userId: number, signatureData: string, isOwner: boolean): Promise<boolean> {
    try {
      await pool.query('BEGIN');
      
      if (isOwner) {
        await pool.query(`
          UPDATE loan_agreements 
          SET owner_signed_at = CURRENT_TIMESTAMP, owner_signature = $1 
          WHERE id = $2
        `, [signatureData, agreementId]);
      } else {
        await pool.query(`
          UPDATE loan_agreements 
          SET borrower_signed_at = CURRENT_TIMESTAMP, borrower_signature = $1 
          WHERE id = $2
        `, [signatureData, agreementId]);
      }

      // Get agreement details for notification cleanup
      const agreementDetailsResult = await pool.query(`
        SELECT borrower_signed_at, owner_signed_at, item_id, borrower_id, owner_id 
        FROM loan_agreements 
        WHERE id = $1
      `, [agreementId]);
      
      const agreementDetails = agreementDetailsResult.rows[0];
      
      // Dismiss agreement notifications for the user who just signed
      if (agreementDetails) {
        // Clean up agreement notifications by both item_id and agreement_id (related_id)
        await pool.query(`
          UPDATE notifications 
          SET is_read = true, is_dismissed = true
          WHERE type = 'agreement_ready' 
          AND (item_id = $1 OR related_id = $3)
          AND user_id = $2 
          AND is_dismissed = false
        `, [agreementDetails.item_id, userId, agreementId]);
        
        console.log(`Dismissed agreement notifications for user ${userId}, item ${agreementDetails.item_id}, agreement ${agreementId}`);
      }

      // Check if both parties have signed (use the details we already fetched)
      const agreement = agreementDetails;
      if (agreement.borrower_signed_at && agreement.owner_signed_at) {
        // Both signed - update status and make item unavailable
        await pool.query(`
          UPDATE loan_agreements 
          SET status = 'signed' 
          WHERE id = $1
        `, [agreementId]);
        
        await pool.query(`
          UPDATE lendable_items 
          SET is_available = false 
          WHERE id = $1
        `, [agreement.item_id]);
        
        // Update unified loan transaction to active
        await db
          .update(loanTransactions)
          .set({ 
            status: 'active', 
            pickedUpAt: sql`CURRENT_TIMESTAMP`,
            updatedAt: sql`CURRENT_TIMESTAMP`
          })
          .where(eq(loanTransactions.agreementId, agreementId));
      }

      await pool.query('COMMIT');
      return true;
    } catch (error) {
      await pool.query('ROLLBACK');
      console.error('Error signing agreement:', error);
      throw error;
    }
  }

  async getLoanAgreement(agreementId: number): Promise<any> {
    try {
      const [agreement] = await db.select().from(loanAgreements).where(eq(loanAgreements.id, agreementId));
      return agreement;
    } catch (error) {
      console.error('Error fetching loan agreement:', error);
      return null;
    }
  }

  async getSignedAgreementForItem(itemId: number): Promise<any> {
    try {
      // Check for signed agreements for this item
      const [agreement] = await db
        .select()
        .from(loanAgreements)
        .where(and(
          eq(loanAgreements.itemId, itemId),
          eq(loanAgreements.status, 'signed')
        ))
        .orderBy(desc(loanAgreements.createdAt))
        .limit(1);
      
      return agreement || null;
    } catch (error) {
      console.error('Error fetching signed agreement for item:', error);
      return null;
    }
  }

  async getLoanTransactionByBorrowRequestId(borrowRequestId: number): Promise<any> {
    try {
      // Find loan transaction by borrow request original_id
      const [loanTransaction] = await db
        .select()
        .from(loanTransactions)
        .where(and(
          eq(loanTransactions.originalId, borrowRequestId),
          eq(loanTransactions.sourceSystem, 'borrow_requests')
        ));
      
      return loanTransaction || null;
    } catch (error) {
      console.error('Error fetching loan transaction by borrow request ID:', error);
      return null;
    }
  }

  async getLoanTransaction(loanTransactionId: number): Promise<any> {
    try {
      console.log(`Getting loan transaction with ID: ${loanTransactionId} (type: ${typeof loanTransactionId}) from unified system`);
      
      const [loanTransaction] = await db
        .select()
        .from(loanTransactions)
        .where(eq(loanTransactions.id, loanTransactionId));
      
      if (loanTransaction) {
        console.log(`Loan transaction found: ID ${loanTransaction.id}, owner ${loanTransaction.ownerId}, borrower ${loanTransaction.borrowerId}`);
      } else {
        console.log(`No loan transaction found with ID ${loanTransactionId}`);
      }
      
      return loanTransaction || null;
    } catch (error) {
      console.error('Error fetching loan transaction by ID:', error);
      return null;
    }
  }

  async linkLoanTransactionToAgreement(loanTransactionId: number, agreementId: number): Promise<void> {
    try {
      await db
        .update(loanTransactions)
        .set({ agreementId: agreementId })
        .where(eq(loanTransactions.id, loanTransactionId));
      
      console.log(`Linked loan transaction ${loanTransactionId} to agreement ${agreementId}`);
    } catch (error) {
      console.error('Error linking loan transaction to agreement:', error);
      throw error;
    }
  }

  async getActiveLoansByUser(userId: number): Promise<any[]> {
    try {
      // UNIFIED SYSTEM: Use only loan_transactions table
      const loans = await db
        .select({
          id: loanTransactions.id,
          status: loanTransactions.status,
          pickedUpAt: loanTransactions.pickedUpAt,
          returnRequestedAt: loanTransactions.returnRequestedAt,
          itemId: lendableItems.id,
          itemName: lendableItems.name,
          primaryLogo: lendableItems.primaryLogo,
          description: lendableItems.description,
          condition: lendableItems.condition,
          pricePerDay: lendableItems.pricePerDay,
          ownerFirstName: users.firstName,
          ownerShowName: users.showName,
          startDate: loanTransactions.requestedDate,
          endDate: loanTransactions.expectedReturnDate,
          totalAmount: loanAgreements.totalAmount
        })
        .from(loanTransactions)
        .leftJoin(lendableItems, eq(loanTransactions.itemId, lendableItems.id))
        .leftJoin(users, eq(loanTransactions.ownerId, users.id))
        .leftJoin(loanAgreements, eq(loanTransactions.agreementId, loanAgreements.id))
        .where(and(
          eq(loanTransactions.borrowerId, userId),
          inArray(loanTransactions.status, ['pending', 'approved', 'active'])
        ))
        .orderBy(desc(loanTransactions.requestedDate));
      
      return loans;
    } catch (error) {
      console.error('Error fetching active loans by user:', error);
      return [];
    }
  }

  async initiateReturn(loanId: number, borrowerId: number, thankYouMessage?: string): Promise<void> {
    try {
      console.log(`UNIFIED SYSTEM: Initiating return for loan ${loanId}, borrower ${borrowerId}`);
      
      // Update the unified loan_transactions table
      await db
        .update(loanTransactions)
        .set({ 
          returnRequestedAt: new Date(),
          status: 'return_requested',
          updatedAt: new Date()
        })
        .where(and(
          eq(loanTransactions.id, loanId),
          eq(loanTransactions.borrowerId, borrowerId)
        ));

      // Get loan details for notification
      const [loanDetails] = await db
        .select({
          itemId: loanTransactions.itemId,
          ownerId: loanTransactions.ownerId,
          itemName: lendableItems.name
        })
        .from(loanTransactions)
        .leftJoin(lendableItems, eq(loanTransactions.itemId, lendableItems.id))
        .where(eq(loanTransactions.id, loanId));

      if (loanDetails) {
        // Create notification for the owner
        await this.createNotification({
          userId: loanDetails.ownerId,
          type: 'return_initiated',
          title: 'Item Return Initiated',
          message: `A borrower has initiated the return process for "${loanDetails.itemName}". Please confirm the return when you receive the item.`,
          relatedId: loanDetails.itemId,
          relatedType: 'item'
        });
      }

      console.log(`Successfully initiated return for unified loan ${loanId}`);
    } catch (error) {
      console.error('Error initiating return (unified system):', error);
      throw error;
    }
  }

  async completeReturn(loanTrackingId: number, ownerId: number, condition: string, notes?: string): Promise<boolean> {
    try {
      await pool.query('BEGIN');
      
      // Update unified loan transaction status
      await db
        .update(loanTransactions)
        .set({ 
          returnedAt: sql`CURRENT_TIMESTAMP`,
          status: 'returned',
          returnCondition: condition,
          returnNotes: notes,
          updatedAt: sql`CURRENT_TIMESTAMP`
        })
        .where(eq(loanTransactions.id, loanTrackingId));

      // Get loan transaction details for points award
      const [loanDetails] = await db
        .select({
          agreementId: loanTransactions.agreementId,
          itemId: loanTransactions.itemId,
          borrowerId: loanTransactions.borrowerId,
          ownerId: loanTransactions.ownerId
        })
        .from(loanTransactions)
        .where(eq(loanTransactions.id, loanTrackingId));
      
      if (!loanDetails) {
        await pool.query('ROLLBACK');
        return false;
      }

      // Update agreement status
      await pool.query(`
        UPDATE loan_agreements 
        SET status = 'completed' 
        WHERE id = $1
      `, [loanDetails.agreementId]);

      // Update item status to available
      await pool.query(`
        UPDATE lendable_items 
        SET is_available = true 
        WHERE id = $1
      `, [loanDetails.itemId]);

      // Award points to both parties
      await this.awardPoints(loanDetails.ownerId, 1, 'lending', 'Successful lending cycle completed', loanDetails.itemId, 'item');
      await this.awardPoints(loanDetails.borrowerId, 1, 'borrowing', 'Successful borrowing cycle completed', loanDetails.itemId, 'item');

      // Get item and user details for email notification
      const itemResult = await pool.query('SELECT name FROM lendable_items WHERE id = $1', [loanDetails.itemId]);
      const ownerResult = await pool.query('SELECT first_name, email FROM users WHERE id = $1', [loanDetails.ownerId]);
      const borrowerResult = await pool.query('SELECT first_name, email FROM users WHERE id = $1', [loanDetails.borrowerId]);
      
      if (itemResult.rows.length > 0 && ownerResult.rows.length > 0 && borrowerResult.rows.length > 0) {
        const itemName = itemResult.rows[0].name;
        const owner = ownerResult.rows[0];
        const borrower = borrowerResult.rows[0];
        
        // Clean up stale notifications before creating new ones
        await pool.query(`
          UPDATE notifications 
          SET is_dismissed = true 
          WHERE related_id = $1 
            AND related_type = 'item' 
            AND type IN ('return_initiated', 'overdue') 
            AND is_dismissed = false
        `, [loanDetails.itemId]);

        // Send completion notifications to both parties
        await this.createNotification({
          userId: loanDetails.ownerId,
          type: 'loan_completed',
          title: 'Loan Completed',
          message: `Loan of "${itemName}" has been completed successfully. You've earned 1 community point!`,
          relatedId: loanDetails.itemId,
          relatedType: 'item'
        });
        
        await this.createNotification({
          userId: loanDetails.borrowerId,
          type: 'loan_completed',
          title: 'Loan Completed',
          message: `Your loan of "${itemName}" has been completed successfully. You've earned 1 community point!`,
          relatedId: loanDetails.itemId,
          relatedType: 'item'
        });
        
        // TODO: Send email notifications when SENDGRID_API_KEY is configured
        // Email subject: "Loan of [Item Name] Completed"
        // Email body: Confirmation that the loan cycle is complete with points awarded
      }

      await pool.query('COMMIT');
      return true;
    } catch (error) {
      await pool.query('ROLLBACK');
      console.error('Error completing return:', error);
      throw error;
    }
  }

  async getItemCategory(itemId: number): Promise<any> {
    try {
      const result = await pool.query(`
        SELECT lc.* 
        FROM lendable_item_categories lc
        JOIN lendable_items li ON lc.id = li.category
        WHERE li.id = $1
      `, [itemId]);
      
      return result.rows[0] || null;
    } catch (error) {
      console.error('Error fetching item category:', error);
      return null;
    }
  }

  async findOrCreateConversation(itemId: number, ownerId: number, borrowerId: number): Promise<number> {
    console.log('findOrCreateConversation called with:', { itemId, ownerId, borrowerId });
    try {
      // Check if conversation already exists
      const result = await pool.query(`
        SELECT id FROM conversations 
        WHERE item_id = $1 AND owner_id = $2 AND borrower_id = $3
      `, [itemId, ownerId, borrowerId]);
      
      console.log('Existing conversation query result:', result.rows);
      
      if (result.rows.length > 0) {
        return result.rows[0].id;
      }
      
      // Create new conversation
      const [conversation] = await db.insert(conversations).values({
        itemId,
        ownerId,
        borrowerId
      }).returning();
      
      return conversation.id;
    } catch (error) {
      console.error('Error finding/creating conversation:', error);
      throw error;
    }
  }

  async getLegalContent(): Promise<LegalContent[]> {
    try {
      return await db.select().from(legalContent).where(eq(legalContent.isActive, true));
    } catch (error) {
      console.error('Error fetching legal content:', error);
      return [];
    }
  }

  // NOTIFICATION TEMPLATE SYSTEM - Standardized notification creation with consistent formatting
  private getNotificationPriority(type: NotificationType): number {
    const priorityMap: Record<NotificationType, number> = {
      'agreement_ready': 5,           // Highest: Agreement signing required
      'return_overdue': 4,            // Critical: Overdue returns
      'owner_overdue': 4,             // Critical: Owner overdue notifications
      'return_due_today': 3,          // High: Due today
      'owner_due_today': 3,           // High: Due today
      'borrow_request': 3,            // High: New borrow requests
      'return_due_tomorrow': 2,       // Medium: Due tomorrow
      'owner_due_tomorrow': 2,        // Medium: Due tomorrow
      'request_approved': 2,          // Medium: Request approvals
      'counter_offer': 2,             // Medium: Counter offers
      'return_initiated': 2,          // Medium: Return processes
      'return_confirmed': 2,          // Medium: Return confirmations
      'hour_request': 2,              // Medium: Hour requests
      'new_message': 1,               // Low: General messages
      'found_item': 1,                // Low: Found items
      'item_issue': 1,                // Low: Item issues
      'reminder_sent': 1,             // Low: Reminders
      'request_denied': 1,            // Low: Denials
      'hour_response': 1,             // Low: Hour responses
      'send_reminder': 1              // Low: Send reminder actions
    };
    return priorityMap[type] || 1;
  }

  private generateNotificationMessage(type: NotificationType, templateData: NotificationTemplateData): { title: string; message: string } {
    const { senderName, itemName, startDate, endDate, details, ownerName, borrowerName, agreementId, offeringTitle, issueType, reminderMessage } = templateData;
    
    switch (type) {
      case 'borrow_request':
        return {
          title: 'New Borrow Request',
          message: `${senderName} wants to borrow your "${itemName}" from ${startDate} to ${endDate}`
        };
      
      case 'request_approved':
        return {
          title: 'Borrow Request Approved',
          message: `Your request to borrow "${itemName}" has been approved by ${ownerName}`
        };
      
      case 'request_denied':
        return {
          title: 'Borrow Request Declined',
          message: `Your request to borrow "${itemName}" was declined by ${ownerName}${details ? `: ${details}` : ''}`
        };
      
      case 'counter_offer':
        return {
          title: 'Counter Offer Received',
          message: `${ownerName} sent a counter offer for "${itemName}": ${details}`
        };
      
      case 'agreement_ready':
        return {
          title: 'Agreement Ready',
          message: `Your borrow request for "${itemName}" has been approved. Please sign the loan agreement to complete the process.`
        };
      
      case 'return_initiated':
        return {
          title: 'Return Process Started',
          message: `${borrowerName} has initiated the return process for "${itemName}"`
        };
      
      case 'return_confirmed':
        return {
          title: 'Return Confirmed',
          message: `${ownerName} has confirmed the return of "${itemName}"`
        };
      
      case 'hour_request':
        return {
          title: 'Hour Offering Request',
          message: `${senderName} is interested in your "${offeringTitle}" offering`
        };
      
      case 'hour_response':
        return {
          title: 'Hour Offering Response',
          message: `${ownerName} responded to your request for "${offeringTitle}"`
        };
      
      case 'new_message':
        return {
          title: 'New Message',
          message: `${senderName} sent you a message about "${itemName}"`
        };
      
      case 'found_item':
        return {
          title: 'Someone Found Your Item',
          message: `${senderName} says: ${details}`
        };
      
      case 'item_issue':
        return {
          title: 'Issue Reported with Your Item',
          message: `${senderName} reported a ${issueType} issue with "${itemName}": ${details}`
        };
      
      case 'reminder_sent':
        return {
          title: 'Reminder Sent',
          message: `${ownerName} sent you a reminder about "${itemName}": ${reminderMessage}`
        };
      
      case 'return_overdue':
        return {
          title: 'Item Return Overdue',
          message: `Overdue! Item "${itemName}" was due on ${endDate}`
        };
      
      case 'owner_overdue':
        return {
          title: 'Borrower Item Overdue',
          message: `Item "${itemName}" is overdue (due: ${endDate}). Shall we send a reminder?`
        };
      
      case 'return_due_today':
        return {
          title: 'Item Due Today',
          message: `Time to return "${itemName}" - due today (${endDate})`
        };
      
      case 'return_due_tomorrow':
        return {
          title: 'Item Due Tomorrow',
          message: `Item "${itemName}" is due tomorrow (${endDate})`
        };
      
      case 'owner_due_today':
        return {
          title: 'Item Due Back Today',
          message: `Lendable "${itemName}" due back today (${endDate})`
        };
      
      case 'owner_due_tomorrow':
        return {
          title: 'Item Due Soon',
          message: `Lendable "${itemName}" due date approaching (${endDate})`
        };
      
      case 'send_reminder':
        return {
          title: 'Send Reminder',
          message: `Shall we send a reminder to ${borrowerName} about "${itemName}"?`
        };
      
      default:
        return {
          title: 'Notification',
          message: details || 'You have a new notification'
        };
    }
  }

  async createStandardizedNotification(data: StandardizedNotificationData): Promise<Notification> {
    try {
      // Generate standardized title and message if not provided
      const { title, message } = data.title && data.message 
        ? { title: data.title, message: data.message }
        : this.generateNotificationMessage(data.type, data.templateData || {});
      
      // Calculate priority if not provided
      const priority = data.priority ?? this.getNotificationPriority(data.type);
      
      // Clean up superseded notifications before creating new one
      await this.cleanupSupersededNotifications(data.userId, data.type, data.relatedId);
      
      // Create the notification with standardized data
      const notificationData = {
        userId: data.userId,
        type: data.type,
        title,
        message,
        relatedId: data.relatedId,
        relatedType: data.relatedType,
        priority,
        isRead: false,
        isDismissed: false,
        createdAt: new Date()
      };
      
      console.log(`Creating standardized notification: ${data.type} for user ${data.userId} - ${title}`);
      
      const [newNotification] = await db
        .insert(notifications)
        .values(notificationData)
        .returning();
      
      return newNotification;
    } catch (error) {
      console.error('Error creating standardized notification:', error);
      throw error;
    }
  }

  // AUTHENTICATION HELPERS - Standardized authentication validation
  async validateUserAuthentication(userId: number | undefined): Promise<{ valid: boolean; user?: User; error?: string }> {
    if (!userId) {
      return { valid: false, error: 'Authentication required' };
    }

    try {
      const user = await this.getUser(userId);
      if (!user) {
        return { valid: false, error: 'Invalid user session' };
      }
      return { valid: true, user };
    } catch (error) {
      console.error('Authentication validation error:', error);
      return { valid: false, error: 'Authentication validation failed' };
    }
  }

  async validateItemOwnership(userId: number, itemId: number): Promise<{ valid: boolean; item?: LendableItem; error?: string }> {
    try {
      const item = await this.getItem(itemId);
      if (!item) {
        return { valid: false, error: 'Item not found' };
      }
      
      if (item.owner !== userId) {
        return { valid: false, error: 'Unauthorized: Not item owner' };
      }
      
      return { valid: true, item };
    } catch (error) {
      console.error('Item ownership validation error:', error);
      return { valid: false, error: 'Ownership validation failed' };
    }
  }

  async validateLoanParticipation(userId: number, loanId: number): Promise<{ valid: boolean; loan?: any; role?: 'owner' | 'borrower'; error?: string }> {
    try {
      const loan = await this.getLoanTransaction(loanId);
      if (!loan) {
        return { valid: false, error: 'Loan not found' };
      }
      
      if (loan.ownerId === userId) {
        return { valid: true, loan, role: 'owner' };
      } else if (loan.borrowerId === userId) {
        return { valid: true, loan, role: 'borrower' };
      } else {
        return { valid: false, error: 'Unauthorized: Not loan participant' };
      }
    } catch (error) {
      console.error('Loan participation validation error:', error);
      return { valid: false, error: 'Loan validation failed' };
    }
  }

  // NOTIFICATION CLEANUP SYSTEM - Automatically dismiss superseded notifications
  private async cleanupSupersededNotifications(userId: number, type: NotificationType, relatedId?: number): Promise<void> {
    try {
      const cleanupRules: Record<NotificationType, NotificationType[]> = {
        // Agreement ready supersedes borrow requests
        'agreement_ready': ['borrow_request'],
        
        // Return confirmations supersede return processes
        'return_confirmed': ['return_initiated', 'return_overdue', 'return_due_today', 'return_due_tomorrow'],
        
        // Approved requests supersede pending requests
        'request_approved': ['borrow_request'],
        
        // Overdue notifications supersede due notifications
        'return_overdue': ['return_due_today', 'return_due_tomorrow'],
        'owner_overdue': ['owner_due_today', 'owner_due_tomorrow'],
        
        // Due today supersedes due tomorrow
        'return_due_today': ['return_due_tomorrow'],
        'owner_due_today': ['owner_due_tomorrow'],
        
        // Counter offers supersede original requests
        'counter_offer': ['borrow_request'],
        
        // Denials supersede requests and counter offers
        'request_denied': ['borrow_request', 'counter_offer']
      };

      const typesToCleanup = cleanupRules[type];
      if (!typesToCleanup || typesToCleanup.length === 0) {
        return;
      }

      const conditions = [
        eq(notifications.userId, userId),
        inArray(notifications.type, typesToCleanup),
        eq(notifications.isDismissed, false)
      ];

      // If related to specific item/transaction, only clean up for that item
      if (relatedId) {
        conditions.push(eq(notifications.relatedId, relatedId));
      }

      const dismissedCount = await db
        .update(notifications)
        .set({ 
          isDismissed: true,
          isRead: true 
        })
        .where(and(...conditions));

      if (dismissedCount.rowCount && dismissedCount.rowCount > 0) {
        console.log(`Cleaned up ${dismissedCount.rowCount} superseded ${typesToCleanup.join(', ')} notifications for user ${userId}`);
      }
    } catch (error) {
      console.error('Error cleaning up superseded notifications:', error);
      // Don't throw - cleanup failure shouldn't prevent notification creation
    }
  }

  async createNotification(notification: any): Promise<Notification> {
    try {
      const [newNotification] = await db
        .insert(notifications)
        .values(notification)
        .returning();
      
      return newNotification;
    } catch (error) {
      console.error('Error creating notification:', error);
      throw error;
    }
  }

  async getUserNotifications(userId: number, includeDismissed: boolean = false): Promise<Notification[]> {
    try {
      const conditions = [eq(notifications.userId, userId)];
      if (!includeDismissed) {
        conditions.push(eq(notifications.isDismissed, false));
      }
      
      return await db
        .select()
        .from(notifications)
        .where(and(...conditions))
        .orderBy(desc(notifications.createdAt));
    } catch (error) {
      console.error('Error fetching user notifications:', error);
      return [];
    }
  }

  async dismissNotification(id: number): Promise<void> {
    try {
      await db
        .update(notifications)
        .set({ isDismissed: true })
        .where(eq(notifications.id, id));
    } catch (error) {
      console.error('Error dismissing notification:', error);
      throw error;
    }
  }

  async markMessageAsRead(messageId: number): Promise<void> {
    try {
      await db
        .update(chatMessages)
        .set({ isRead: true })
        .where(eq(chatMessages.id, messageId));
    } catch (error) {
      console.error('Error marking message as read:', error);
      throw error;
    }
  }

  async getUserItemsDetailed(userId: number): Promise<any[]> {
    try {
      // Use the existing getUserItems method as base and enhance it
      const items = await this.getUserItems(userId);
      
      // Enhance items with additional details and status
      const itemsWithStatus = await Promise.all(items.map(async (item) => {
        const loanStatus = await this.getItemLoanStatus(item.id);
        return {
          ...item,
          // categoryName is already provided by getUserItems via LEFT JOIN
          categoryName: item.categoryName || 'Uncategorized',
          isOnLoan: loanStatus.isOnLoan,
          dailyRate: item.pricePerDay,
          deposit: item.depositRequired || 0,
          specialInstructions: item.workflowNotes,
          views: item.views || 0,
          timesLent: item.timesLent || 0,
          qrCode: item.qrCode,
          condition: item.condition
        };
      }));

      return itemsWithStatus.filter(item => !item.isRequest); // Only actual items
    } catch (error) {
      console.error('Error fetching user items detailed:', error);
      return [];
    }
  }

  async getUserDraftItems(userId: number): Promise<any[]> {
    try {
      // For now, return empty array since availability field may not exist in current schema
      // This can be expanded when draft functionality is needed
      return [];
    } catch (error) {
      console.error('Error fetching user draft items:', error);
      return [];
    }
  }

  async markNotificationAsRead(id: number): Promise<void> {
    try {
      await db
        .update(notifications)
        .set({})
        .where(eq(notifications.id, id));
    } catch (error) {
      console.error('Error marking notification as read:', error);
      throw error;
    }
  }

  async getNotification(id: number): Promise<any> {
    try {
      const [notification] = await db
        .select()
        .from(notifications)
        .where(eq(notifications.id, id));
      
      return notification;
    } catch (error) {
      console.error('Error fetching notification:', error);
      return null;
    }
  }

  async getNotificationSettings(userId: number): Promise<NotificationSettings | undefined> {
    try {
      const [settings] = await db
        .select()
        .from(notificationSettings)
        .where(eq(notificationSettings.userId, userId));
      
      return settings;
    } catch (error) {
      console.error('Error fetching notification settings:', error);
      return undefined;
    }
  }

  async getExistingBookings(itemId: number): Promise<any[]> {
    try {
      return await db
        .select({
          id: loanTransactions.id,
          startDate: loanTransactions.startDate,
          endDate: loanTransactions.endDate,
          status: loanTransactions.status,
          borrowerName: users.firstName
        })
        .from(loanTransactions)
        .leftJoin(users, eq(loanTransactions.borrowerId, users.id))
        .where(
          and(
            eq(loanTransactions.itemId, itemId),
            or(
              eq(loanTransactions.status, 'pending'),
              eq(loanTransactions.status, 'approved')
            )
          )
        )
        .orderBy(loanTransactions.startDate);
    } catch (error) {
      console.error('Error fetching existing bookings:', error);
      return [];
    }
  }

  async getUserNotificationData(userId: number): Promise<{
    hasUnread: boolean;
    unreadCount: number;
    recentNotifications: any[];
  }> {
    try {
      const userNotifications = await db
        .select()
        .from(notifications)
        .where(eq(notifications.userId, userId))
        .orderBy(desc(notifications.createdAt))
        .limit(5);

      const unreadNotifications = userNotifications.filter((n: any) => !n.isRead);
      
      return {
        hasUnread: unreadNotifications.length > 0,
        unreadCount: unreadNotifications.length,
        recentNotifications: userNotifications
      };
    } catch (error) {
      console.error('Error fetching notification data:', error);
      return { hasUnread: false, unreadCount: 0, recentNotifications: [] };
    }
  }

  async getUserUnreadChatData(userId: number): Promise<{
    hasUnreadChats: boolean;
    unreadChatCount: number;
  }> {
    try {
      // Find all conversations where the user is either owner or borrower
      const userConversations = await db
        .select()
        .from(conversations)
        .where(or(eq(conversations.ownerId, userId), eq(conversations.borrowerId, userId)));

      if (userConversations.length === 0) {
        return { hasUnreadChats: false, unreadChatCount: 0 };
      }

      const conversationIds = userConversations.map(c => c.id);
      
      if (conversationIds.length === 0) {
        return { hasUnreadChats: false, unreadChatCount: 0 };
      }
      
      // Count unread messages in these conversations (where sender is NOT the current user)
      const unreadMessages = await db
        .select()
        .from(chatMessages)
        .where(
          and(
            inArray(chatMessages.conversationId, conversationIds),
            ne(chatMessages.senderId, userId),
            eq(chatMessages.isRead, false)
          )
        );

      return {
        hasUnreadChats: unreadMessages.length > 0,
        unreadChatCount: unreadMessages.length
      };
    } catch (error) {
      console.error('Error fetching unread chat data:', error);
      return { hasUnreadChats: false, unreadChatCount: 0 };
    }
  }

  async getAllUserMessages(userId: number): Promise<{received: any[], sent: any[]}> {
    try {
      // Get all conversations where user is either owner or borrower
      const userConversations = await db
        .select({ id: conversations.id })
        .from(conversations)
        .where(or(
          eq(conversations.ownerId, userId),
          eq(conversations.borrowerId, userId)
        ));

      if (userConversations.length === 0) {
        return { received: [], sent: [] };
      }

      const conversationIds = userConversations.map(c => c.id);
      
      // Get all messages with sender info, conversation details, and item info
      const allMessages = await db
        .select({
          messageId: chatMessages.id,
          conversationId: chatMessages.conversationId,
          senderId: chatMessages.senderId,
          messageType: chatMessages.messageType,
          textContent: chatMessages.textContent,
          transcription: chatMessages.transcription,
          createdAt: chatMessages.createdAt,
          senderFirstName: sql`sender.first_name`,
          senderLastName: sql`sender.last_name`,
          senderShowName: sql`sender.show_name`,
          senderAvatar: sql`sender.avatar`,
          itemId: conversations.itemId,
          ownerId: conversations.ownerId,
          borrowerId: conversations.borrowerId,
          itemName: lendableItems.name,
          itemPrimaryLogo: lendableItems.primaryLogo
        })
        .from(chatMessages)
        .innerJoin(sql`users sender`, eq(chatMessages.senderId, sql`sender.id`))
        .innerJoin(conversations, eq(chatMessages.conversationId, conversations.id))
        .leftJoin(lendableItems, eq(conversations.itemId, lendableItems.id))
        .where(inArray(chatMessages.conversationId, conversationIds))
        .orderBy(desc(chatMessages.createdAt));

      // Separate received and sent messages
      const received = [];
      const sent = [];

      for (const msg of allMessages) {
        const messageData = {
          messageId: msg.messageId,
          conversationId: msg.conversationId,
          item_name: msg.itemName || 'Unknown Item',
          item_thumbnail: msg.itemPrimaryLogo,
          preview: msg.messageType === 'text' 
            ? (msg.textContent ? (msg.textContent.substring(0, 100) + (msg.textContent.length > 100 ? '...' : '')) : '')
            : msg.transcription 
              ? (msg.transcription.substring(0, 100) + (msg.transcription.length > 100 ? '...' : ''))
              : 'Audio message',
          created_at: msg.createdAt,
          item_id: msg.itemId
        };

        if (msg.senderId === userId) {
          // Message sent by current user
          const recipientId = msg.ownerId === userId ? msg.borrowerId : msg.ownerId;
          const recipient = await this.getUser(recipientId);
          sent.push({
            ...messageData,
            recipient_id: recipientId,
            recipient_name: recipient ? (recipient.showName || `${recipient.firstName} ${recipient.lastName}`.trim() || 'Unknown User') : 'Unknown User',
            recipient_avatar: recipient ? `/ll_images/${recipient.avatar || 'f10.jpg'}` : '/ll_images/f10.jpg'
          });
        } else {
          // Message received by current user
          received.push({
            ...messageData,
            sender_id: msg.senderId,
            sender_name: msg.senderShowName || msg.senderFirstName,
            sender_avatar: `/ll_images/${msg.senderAvatar || 'f10.jpg'}`
          });
        }
      }

      return { received, sent };
    } catch (error) {
      console.error('Error fetching all user messages:', error);
      return { received: [], sent: [] };
    }
  }

  async getUnreturnedItemMessages(userId: number): Promise<{received: any[], sent: any[], threaded: any[]}> {
    try {
      // Get conversations for items that have unreturned loans
      const unreturnedConversations = await db
        .select({
          conversationId: conversations.id,
          itemId: conversations.itemId,
          ownerId: conversations.ownerId,
          borrowerId: conversations.borrowerId,
          itemName: lendableItems.name,
          loanStatus: loanTransactions.status,
          ownerName: sql`owner.show_name`,
          ownerAvatar: sql`owner.avatar`,
          borrowerName: sql`borrower.show_name`,
          borrowerAvatar: sql`borrower.avatar`
        })
        .from(conversations)
        .innerJoin(lendableItems, eq(conversations.itemId, lendableItems.id))
        .innerJoin(loanTransactions, eq(conversations.itemId, loanTransactions.itemId))
        .innerJoin(sql`users owner`, eq(conversations.ownerId, sql`owner.id`))
        .innerJoin(sql`users borrower`, eq(conversations.borrowerId, sql`borrower.id`))
        .where(
          and(
            or(
              eq(conversations.ownerId, userId),
              eq(conversations.borrowerId, userId)
            ),
            not(inArray(loanTransactions.status, ['returned', 'denied', 'dismissed']))
          )
        );

      if (unreturnedConversations.length === 0) {
        return { received: [], sent: [], threaded: [] };
      }

      const conversationIds = unreturnedConversations.map(c => c.conversationId);
      
      // Get all messages for unreturned items
      const allMessages = await db
        .select({
          messageId: chatMessages.id,
          conversationId: chatMessages.conversationId,
          senderId: chatMessages.senderId,
          messageType: chatMessages.messageType,
          textContent: chatMessages.textContent,
          transcription: chatMessages.transcription,
          audioPath: chatMessages.audioPath,
          createdAt: chatMessages.createdAt,
          senderFirstName: sql`sender.first_name`,
          senderLastName: sql`sender.last_name`,
          senderShowName: sql`sender.show_name`,
          senderAvatar: sql`sender.avatar`
        })
        .from(chatMessages)
        .innerJoin(sql`users sender`, eq(chatMessages.senderId, sql`sender.id`))
        .where(inArray(chatMessages.conversationId, conversationIds))
        .orderBy(desc(chatMessages.createdAt));

      // Process messages
      const received = [];
      const sent = [];
      const threaded = [];

      // Group messages by conversation for threading
      const messagesByConversation = new Map();

      for (const msg of allMessages) {
        const conv = unreturnedConversations.find(c => c.conversationId === msg.conversationId);
        if (!conv) continue;

        const messageData = {
          messageId: msg.messageId,
          conversationId: msg.conversationId,
          item_name: conv.itemName,
          item_id: conv.itemId,
          preview: msg.messageType === 'text' 
            ? (msg.textContent ? (msg.textContent.substring(0, 100) + (msg.textContent.length > 100 ? '...' : '')) : '')
            : msg.transcription 
              ? (msg.transcription.substring(0, 100) + (msg.transcription.length > 100 ? '...' : ''))
              : 'Audio message',
          full_content: msg.messageType === 'text' ? msg.textContent : msg.transcription,
          message_type: msg.messageType,
          audio_path: msg.audioPath,
          transcription: msg.transcription,
          created_at: msg.createdAt,
          sender_id: msg.senderId,
          sender_name: msg.senderShowName,
          sender_show_name: msg.senderShowName,
          sender_avatar: `/lendables/${msg.senderAvatar || 'f10.jpg'}`,
          loan_status: conv.loanStatus
        };

        // Add to conversation threading
        if (!messagesByConversation.has(msg.conversationId)) {
          messagesByConversation.set(msg.conversationId, {
            conversation_id: msg.conversationId,
            item_name: conv.itemName,
            item_id: conv.itemId,
            owner_id: conv.ownerId,
            owner_name: conv.ownerName,
            owner_show_name: conv.ownerName,
            owner_avatar: `/lendables/${conv.ownerAvatar || 'f10.jpg'}`,
            borrower_id: conv.borrowerId,
            borrower_name: conv.borrowerName,
            borrower_show_name: conv.borrowerName,
            borrower_avatar: `/lendables/${conv.borrowerAvatar || 'f10.jpg'}`,
            loan_status: conv.loanStatus,
            messages: [],
            last_message_date: msg.createdAt,
            total_messages: 0
          });
        }

        const threadData = messagesByConversation.get(msg.conversationId);
        threadData.messages.push(messageData);
        threadData.total_messages++;
        if (msg.createdAt > threadData.last_message_date) {
          threadData.last_message_date = msg.createdAt;
        }

        // Separate received and sent
        if (msg.senderId === userId) {
          const recipientId = conv.ownerId === userId ? conv.borrowerId : conv.ownerId;
          const recipientName = conv.ownerId === userId ? conv.borrowerName : conv.ownerName;
          const recipientAvatar = conv.ownerId === userId ? conv.borrowerAvatar : conv.ownerAvatar;
          
          sent.push({
            ...messageData,
            recipient_id: recipientId,
            recipient_name: recipientName,
            recipient_avatar: `/lendables/${recipientAvatar || 'f10.jpg'}`
          });
        } else {
          received.push(messageData);
        }
      }

      // Convert threaded messages to array
      for (const thread of messagesByConversation.values()) {
        thread.messages.sort((a, b) => new Date(b.created_at).getTime() - new Date(a.created_at).getTime());
        threaded.push(thread);
      }

      return { received, sent, threaded };
    } catch (error) {
      console.error('Error fetching unreturned item messages:', error);
      return { received: [], sent: [], threaded: [] };
    }
  }

  async getUserUnreadMessages(userId: number): Promise<any[]> {
    try {
      // Find all conversations where the user is either owner or borrower
      // Only include conversations for active loans or items still available (not completed transactions)
      const userConversations = await db
        .select({
          id: conversations.id,
          itemId: conversations.itemId,
          ownerId: conversations.ownerId,
          borrowerId: conversations.borrowerId
        })
        .from(conversations)
        .leftJoin(loanTransactions, and(
          eq(loanTransactions.itemId, conversations.itemId),
          eq(loanTransactions.borrowerId, conversations.borrowerId)
        ))
        .where(
          and(
            or(eq(conversations.ownerId, userId), eq(conversations.borrowerId, userId)),
            // Only include conversations where:
            // 1. No loan exists (just browsing/asking questions), OR
            // 2. Loan exists but item hasn't been returned yet
            or(
              isNull(loanTransactions.id), // No active loan
              ne(loanTransactions.status, 'returned') // Active loan not returned
            )
          )
        );

      if (userConversations.length === 0) {
        return [];
      }

      const conversationIds = userConversations.map(c => c.id);
      
      // Get unread messages with minimal info, then fetch sender details separately
      const unreadMessagesRaw = await db
        .select({
          messageId: chatMessages.id,
          conversationId: chatMessages.conversationId,
          senderId: chatMessages.senderId,
          messageType: chatMessages.messageType,
          textContent: chatMessages.textContent,
          transcription: chatMessages.transcription,
          audioPath: chatMessages.audioPath,
          createdAt: chatMessages.createdAt,
          itemId: conversations.itemId,
          ownerId: conversations.ownerId,
          borrowerId: conversations.borrowerId,
          itemName: lendableItems.name,
          itemPrimaryLogo: lendableItems.primaryLogo
        })
        .from(chatMessages)
        .innerJoin(conversations, eq(chatMessages.conversationId, conversations.id))
        .leftJoin(lendableItems, eq(conversations.itemId, lendableItems.id))
        .where(
          and(
            inArray(chatMessages.conversationId, conversationIds),
            ne(chatMessages.senderId, userId),
            eq(chatMessages.isRead, false)
          )
        )
        .orderBy(desc(chatMessages.createdAt));

      // Get sender details for all messages
      const senderIds = [...new Set(unreadMessagesRaw.map(msg => msg.senderId))];
      const senders = senderIds.length > 0 ? await db
        .select({
          id: users.id,
          firstName: users.firstName,
          showName: users.showName,
          avatar: users.avatar
        })
        .from(users)
        .where(inArray(users.id, senderIds)) : [];

      const senderMap = new Map(senders.map(sender => [sender.id, sender]));

      // Combine message data with sender info
      const unreadMessages = unreadMessagesRaw.map(msg => {
        const sender = senderMap.get(msg.senderId);
        return {
          ...msg,
          senderFirstName: sender?.firstName || 'Unknown',
          senderShowName: sender?.showName || 'Unknown',
          senderSelfie: sender?.avatar || null
        };
      });

      // Get item names for each conversation
      const uniqueItemIds = unreadMessages.map(msg => msg.itemId).filter(Boolean);
      const itemIds = uniqueItemIds.filter((id, index, arr) => arr.indexOf(id) === index);
      const items = itemIds.length > 0 ? await db
        .select({ id: lendableItems.id, name: lendableItems.name })
        .from(lendableItems)
        .where(inArray(lendableItems.id, itemIds)) : [];

      const itemMap = new Map(items.map(item => [item.id, item.name]));

      return unreadMessages.map(msg => ({
        ...msg,
        sender_name: msg.senderShowName || msg.senderFirstName,
        sender_avatar: msg.senderSelfie,
        item_name: msg.itemName || 'Unknown Item',
        item_thumbnail: msg.itemPrimaryLogo,
        audio_path: msg.audioPath,
        preview: msg.messageType === 'text' 
          ? (msg.textContent ? (msg.textContent.substring(0, 100) + (msg.textContent.length > 100 ? '...' : '')) : '')
          : msg.transcription 
            ? (msg.transcription.substring(0, 100) + (msg.transcription.length > 100 ? '...' : ''))
            : 'Audio message',
        created_at: msg.createdAt,
        owner_id: msg.ownerId,
        borrower_id: msg.borrowerId,
        item_id: msg.itemId
      }));
    } catch (error) {
      console.error('Error fetching unread messages:', error);
      return [];
    }
  }

  async toggleItemAvailability(itemId: number, isAvailable: boolean): Promise<void> {
    try {
      await db
        .update(lendableItems)
        .set({})
        .where(eq(lendableItems.id, itemId));
    } catch (error) {
      console.error('Error toggling item availability:', error);
      throw error;
    }
  }

  async getUserRecentActivity(userId: number, limit: number = 10): Promise<any[]> {
    try {
      // First get loan transactions (unified system)
      const loanTransactionsQuery = `
        SELECT 
          lt.id, 
          'borrow_request' as type,
          lt.item_id as "itemId",
          li.name as "itemName",
          lt.requested_date as "createdAt",
          lt.status,
          lt.borrower_id as "otherUserId",
          COALESCE(u.show_name, u.first_name) as "otherUserName",
          '' as "messageType",
          '' as preview,
          '' as "conversationId",
          1 as "messageCount"
        FROM loan_transactions lt
        INNER JOIN lendable_items li ON lt.item_id = li.id
        INNER JOIN users u ON lt.borrower_id = u.id
        WHERE lt.owner_id = $1
        ORDER BY lt.requested_date DESC
        LIMIT $2
      `;
      
      // Then get chat conversations (threaded by conversation)
      const chatConversationsQuery = `
        SELECT DISTINCT ON (c.id)
          c.id as "conversationId",
          'chat_conversation' as type,
          c.item_id as "itemId",
          li.name as "itemName",
          latest_msg.created_at as "createdAt",
          '' as status,
          CASE 
            WHEN c.owner_id = $1 THEN c.borrower_id 
            ELSE c.owner_id 
          END as "otherUserId",
          CASE 
            WHEN c.owner_id = $1 THEN COALESCE(borrower.show_name, borrower.first_name)
            ELSE COALESCE(owner.show_name, owner.first_name)
          END as "otherUserName",
          latest_msg.message_type as "messageType",
          CASE 
            WHEN latest_msg.message_type = 'text' THEN COALESCE(latest_msg.text_content, '')
            ELSE COALESCE(latest_msg.transcription, 'Audio message')
          END as preview,
          msg_count.count as "messageCount"
        FROM conversations c
        INNER JOIN lendable_items li ON c.item_id = li.id
        INNER JOIN users owner ON c.owner_id = owner.id
        INNER JOIN users borrower ON c.borrower_id = borrower.id
        INNER JOIN (
          SELECT DISTINCT ON (conversation_id) 
            conversation_id, created_at, message_type, text_content, transcription
          FROM chat_messages 
          ORDER BY conversation_id, created_at DESC
        ) latest_msg ON c.id = latest_msg.conversation_id
        INNER JOIN (
          SELECT conversation_id, COUNT(*) as count
          FROM chat_messages
          GROUP BY conversation_id
        ) msg_count ON c.id = msg_count.conversation_id
        WHERE (c.owner_id = $1 OR c.borrower_id = $1)
        ORDER BY c.id, latest_msg.created_at DESC
      `;
      
      const [loanTransactionsResult, chatConversationsResult] = await Promise.all([
        pool.query(loanTransactionsQuery, [userId, limit]),
        pool.query(chatConversationsQuery, [userId])
      ]);
      
      // Combine and sort by most recent activity
      const allActivity = [
        ...loanTransactionsResult.rows,
        ...chatConversationsResult.rows
      ].sort((a, b) => new Date(b.createdAt).getTime() - new Date(a.createdAt).getTime());
      
      return allActivity.slice(0, limit);
    } catch (error) {
      console.error('Error fetching user recent activity:', error);
      return [];
    }
  }

  async getUserBorrowingHistory(userId: number): Promise<any[]> {
    try {
      // Comprehensive borrowing history with deduplication and proper date formatting
      const historyQuery = `
        WITH deduplicated_history AS (
          SELECT DISTINCT ON (all_records.item_id, all_records.borrower_id)
            all_records.item_id as "itemId",
            li.name as "itemName", 
            li.primary_logo as "itemImage",
            COALESCE(u.show_name, u.first_name) as "ownerName",
            u.avatar as "ownerAvatar",
            all_records.start_date,
            all_records.end_date,
            all_records.status,
            all_records.created_at as "createdAt",
            all_records.source,
            all_records.request_id as "requestId"
          FROM (
            -- UNIFIED SYSTEM ONLY - loan_transactions table
            SELECT 
              1 as priority,
              lt.id as request_id,
              lt.item_id,
              lt.borrower_id as borrower_id,
              lt.borrowed_date as start_date,
              COALESCE(lt.expected_return_date, lt.borrowed_date) as end_date,
              lt.status,
              lt.created_at,
              'loan_transactions' as source
            FROM loan_transactions lt
            WHERE lt.borrower_id = $1
          ) all_records
          INNER JOIN lendable_items li ON all_records.item_id = li.id
          INNER JOIN users u ON li.owner = u.id
          ORDER BY all_records.item_id, all_records.borrower_id, all_records.priority
        )
        SELECT 
          "requestId",
          "itemId",
          "itemName",
          "itemImage",
          "ownerName",
          "ownerAvatar",
          TO_CHAR(start_date, 'YYYY-MM-DD') || ' to ' || TO_CHAR(end_date, 'YYYY-MM-DD') as "requestedDates",
          status,
          "createdAt",
          source
        FROM deduplicated_history
        ORDER BY "createdAt" DESC
      `;
      
      const result = await pool.query(historyQuery, [userId]);
      console.log(`Found ${result.rows.length} borrowing history records for user ${userId}`);
      
      // Debug: Log first few records to verify data structure
      if (result.rows.length > 0) {
        console.log('Sample borrowing history record:', JSON.stringify(result.rows[0], null, 2));
        console.log('All borrowing records for debug:');
        result.rows.forEach((record, index) => {
          console.log(`  ${index + 1}. ${record.itemName}: ${record.requestedDates} (status: ${record.status})`);
        });
      }
      
      return result.rows;
    } catch (error) {
      console.error('Error fetching borrowing history:', error);
      return [];
    }
  }

  async getUserPreferences(userId: number): Promise<any> {
    try {
      const [prefs] = await db
        .select()
        .from(userPreferences)
        .where(eq(userPreferences.userId, userId));
      
      return prefs;
    } catch (error) {
      console.error('Error fetching user preferences:', error);
      return undefined;
    }
  }

  async updateUser(userId: number, updateData: any): Promise<void> {
    try {
      await db
        .update(users)
        .set(updateData)
        .where(eq(users.id, userId));
    } catch (error) {
      console.error('Error updating user:', error);
      throw error;
    }
  }

  async getBorrowRequestsWithDetails(ownerId: number): Promise<any[]> {
    try {
      return await db
        .select({
          id: loanTransactions.id,
          itemId: loanTransactions.itemId,
          borrowerId: loanTransactions.borrowerId,
          startDate: loanTransactions.startDate,
          endDate: loanTransactions.endDate,
          message: loanTransactions.message,
          status: loanTransactions.status,
          createdAt: loanTransactions.createdAt,
          expiresAt: loanTransactions.expiresAt,
          itemName: lendableItems.name,
          borrowerName: users.firstName
        })
        .from(loanTransactions)
        .leftJoin(lendableItems, eq(loanTransactions.itemId, lendableItems.id))
        .leftJoin(users, eq(loanTransactions.borrowerId, users.id))
        .where(eq(loanTransactions.ownerId, ownerId))
        .orderBy(desc(loanTransactions.createdAt));
    } catch (error) {
      console.error('Error fetching borrow requests with details:', error);
      return [];
    }
  }

  // Chat operations
  async createConversation(conversation: InsertConversation): Promise<Conversation> {
    const [newConversation] = await db.insert(conversations).values({
      itemId: conversation.itemId,
      ownerId: conversation.ownerId,
      borrowerId: conversation.borrowerId
    }).returning();
    return newConversation;
  }

  async getConversation(itemId: number, ownerId: number, borrowerId: number): Promise<Conversation | undefined> {
    const [conversation] = await db
      .select()
      .from(conversations)
      .where(and(
        eq(conversations.itemId, itemId),
        eq(conversations.ownerId, ownerId),
        eq(conversations.borrowerId, borrowerId)
      ))
      .limit(1);
    
    return conversation;
  }

  async getAllConversations(): Promise<Conversation[]> {
    const allConversations = await db
      .select()
      .from(conversations)
      .orderBy(desc(conversations.lastMessageAt));
    
    return allConversations;
  }

  async getConversationsForItem(itemId: number, userId: number): Promise<any[]> {
    try {
      const result = await pool.query(`
        SELECT 
          c.id, c.item_id, c.owner_id, c.borrower_id, c.status, c.last_message_at, c.created_at,
          COUNT(cm.id) as message_count,
          li.name as item_name,
          CASE 
            WHEN c.owner_id = $2 THEN COALESCE(borrower.show_name, borrower.first_name || ' ' || borrower.last_name)
            ELSE COALESCE(owner.show_name, owner.first_name || ' ' || owner.last_name)
          END as other_participant_name
        FROM conversations c
        INNER JOIN lendable_items li ON c.item_id = li.id
        INNER JOIN users owner ON c.owner_id = owner.id
        INNER JOIN users borrower ON c.borrower_id = borrower.id
        LEFT JOIN chat_messages cm ON c.id = cm.conversation_id
        WHERE c.item_id = $1 AND (c.owner_id = $2 OR c.borrower_id = $2)
        GROUP BY c.id, c.item_id, c.owner_id, c.borrower_id, c.status, c.last_message_at, c.created_at, li.name, other_participant_name
        ORDER BY c.last_message_at DESC NULLS LAST, message_count DESC
      `, [itemId, userId]);
      
      return result.rows.map(row => ({
        id: row.id,
        itemId: row.item_id,
        ownerId: row.owner_id,
        borrowerId: row.borrower_id,
        status: row.status,
        messageCount: parseInt(row.message_count) || 0,
        itemName: row.item_name,
        otherParticipantName: row.other_participant_name,
        lastMessageAt: row.last_message_at,
        createdAt: row.created_at
      }));
    } catch (error) {
      console.error('Error fetching conversations for item:', error);
      return [];
    }
  }

  async getConversationById(id: number): Promise<any> {
    const result = await pool.query(`
      SELECT 
        c.id, c.item_id, c.owner_id, c.borrower_id, c.status, c.last_message_at, c.created_at,
        li.name as item_name, li.primary_logo as item_image,
        owner.first_name as owner_first_name, owner.show_name as owner_show_name, owner.avatar as owner_avatar,
        borrower.first_name as borrower_first_name, borrower.show_name as borrower_show_name, borrower.avatar as borrower_avatar
      FROM conversations c
      INNER JOIN lendable_items li ON c.item_id = li.id
      INNER JOIN users owner ON c.owner_id = owner.id
      INNER JOIN users borrower ON c.borrower_id = borrower.id
      WHERE c.id = $1
    `, [id]);
    
    return result.rows[0] || null;
  }

  async sendChatMessage(message: InsertChatMessage): Promise<ChatMessage> {
    const [newMessage] = await db.insert(chatMessages).values({
      conversationId: message.conversationId,
      senderId: message.senderId,
      messageType: message.messageType || 'text',
      textContent: message.textContent || null,
      audioPath: message.audioPath || null,
      transcription: message.transcription || null
    }).returning();
    
    // Update conversation's last message time
    await this.updateLastMessageTime(message.conversationId);
    
    return newMessage;
  }

  // Wrapper method for simplified message sending
  async sendMessage(data: {
    conversationId: number;
    senderId: number;
    message?: string;
    messageType?: string;
    audioPath?: string;
  }): Promise<ChatMessage> {
    return this.sendChatMessage({
      conversationId: data.conversationId,
      senderId: data.senderId,
      messageType: data.messageType || 'text',
      textContent: data.message || null,
      audioPath: data.audioPath || null,
      transcription: null
    });
  }

  async getChatMessages(conversationId: number): Promise<any[]> {
    const result = await pool.query(`
      SELECT 
        cm.id, cm.conversation_id, cm.sender_id, cm.message_type, cm.text_content, 
        cm.audio_path, cm.transcription, cm.is_read, cm.created_at,
        u.first_name as sender_first_name, u.show_name as sender_show_name, u.avatar as sender_avatar
      FROM chat_messages cm
      INNER JOIN users u ON cm.sender_id = u.id
      WHERE cm.conversation_id = $1
      ORDER BY cm.created_at ASC
    `, [conversationId]);
    
    return result.rows;
  }

  async getChatMessage(messageId: number): Promise<any> {
    const result = await pool.query(`
      SELECT 
        cm.id, cm.conversation_id, cm.sender_id, cm.message_type, cm.text_content, 
        cm.audio_path, cm.transcription, cm.is_read, cm.created_at,
        u.first_name as sender_first_name, u.show_name as sender_show_name, u.avatar as sender_avatar
      FROM chat_messages cm
      INNER JOIN users u ON cm.sender_id = u.id
      WHERE cm.id = $1
    `, [messageId]);
    
    return result.rows[0] || null;
  }

  async updateLastMessageTime(conversationId: number): Promise<void> {
    await db
      .update(conversations)
      .set({ lastMessageAt: new Date() })
      .where(eq(conversations.id, conversationId));
  }

  async updateChatMessageTranscription(messageId: number, transcription: string): Promise<void> {
    await db
      .update(chatMessages)
      .set({ transcription: transcription })
      .where(eq(chatMessages.id, messageId));
  }

  // Admin functions for managing messages and activity
  async getAllMessagesForAdmin(): Promise<any[]> {
    const result = await pool.query(`
      SELECT 
        cm.id, cm.conversation_id, cm.sender_id, cm.message_type, cm.text_content, 
        cm.audio_path, cm.transcription, cm.is_read, cm.is_active, cm.created_at,
        COALESCE(u.show_name, u.first_name || ' ' || u.last_name) as sender_name, u.avatar as sender_avatar,
        li.name as item_name
      FROM chat_messages cm
      INNER JOIN users u ON cm.sender_id = u.id
      LEFT JOIN conversations c ON cm.conversation_id = c.id
      LEFT JOIN lendable_items li ON c.item_id = li.id
      ORDER BY cm.created_at DESC
      LIMIT 100
    `);
    
    return result.rows;
  }

  async getAllActivityForAdmin(): Promise<any[]> {
    const result = await pool.query(`
      SELECT 
        ma.id, ma.user_id, ma.activity_type, ma.description, ma.related_item_id, 
        ma.related_message_id, ma.is_active, ma.created_at,
        COALESCE(u.show_name, u.first_name || ' ' || u.last_name) as user_name, u.avatar as user_avatar
      FROM member_activity ma
      INNER JOIN users u ON ma.user_id = u.id
      ORDER BY ma.created_at DESC
      LIMIT 100
    `);
    
    return result.rows;
  }

  async getAdminStats(): Promise<any> {
    const [messageStats, userStats, itemStats, loanStats, requestStats] = await Promise.all([
      pool.query('SELECT COUNT(*) as total_messages FROM chat_messages'),
      pool.query('SELECT COUNT(*) as total_users FROM users'),
      pool.query('SELECT COUNT(*) as total_items FROM lendable_items'),
      pool.query('SELECT COUNT(*) as total_loans FROM loan_transactions WHERE status IN ($1, $2)', ['active', 'approved']),
      pool.query('SELECT COUNT(*) as total_requests FROM loan_transactions WHERE status = $1', ['pending'])
    ]);
    
    return {
      totalMessages: parseInt(messageStats.rows[0]?.total_messages || '0') || 0,
      totalUsers: parseInt(userStats.rows[0]?.total_users || '0') || 0,
      activeItems: parseInt(itemStats.rows[0]?.total_items || '0') || 0,
      activeLoans: parseInt(loanStats.rows[0]?.total_loans || '0') || 0,
      pendingRequests: parseInt(requestStats.rows[0]?.total_requests || '0') || 0
    };
  }

  async deactivateMessage(messageId: number): Promise<boolean> {
    try {
      await pool.query('UPDATE chat_messages SET is_active = false WHERE id = $1', [messageId]);
      return true;
    } catch (error) {
      console.error('Error deactivating message:', error);
      return false;
    }
  }

  async reactivateMessage(messageId: number): Promise<boolean> {
    try {
      await pool.query('UPDATE chat_messages SET is_active = true WHERE id = $1', [messageId]);
      return true;
    } catch (error) {
      console.error('Error reactivating message:', error);
      return false;
    }
  }



  async updateUserWelcomeStatus(userId: number, completed: boolean): Promise<boolean> {
    try {
      await pool.query(
        'UPDATE users SET welcome_sequence_completed = $1 WHERE id = $2',
        [completed, userId]
      );
      return true;
    } catch (error) {
      console.error('Error updating welcome status:', error);
      return false;
    }
  }

  async getImportedMemberCount(): Promise<number> {
    try {
      const result = await pool.query('SELECT COUNT(*) as count FROM users WHERE is_imported = true');
      return parseInt(result.rows[0]?.count || '0') || 0;
    } catch (error) {
      console.error('Error getting imported member count:', error);
      return 0;
    }
  }

  async getWelcomePendingCount(): Promise<number> {
    try {
      const result = await pool.query('SELECT COUNT(*) as count FROM users WHERE is_imported = true AND welcome_sequence_completed = false');
      return parseInt(result.rows[0]?.count || '0') || 0;
    } catch (error) {
      console.error('Error getting welcome pending count:', error);
      return 0;
    }
  }

  async getRecentImports(limit: number): Promise<any[]> {
    try {
      // For now, return recent user registrations that are imported
      const result = await pool.query(`
        SELECT 
          COUNT(*) as member_count,
          DATE_TRUNC('day', registered_on) as import_date,
          invited_by
        FROM users 
        WHERE is_imported = true 
        GROUP BY DATE_TRUNC('day', registered_on), invited_by
        ORDER BY import_date DESC 
        LIMIT $1
      `, [limit]);
      
      return result.rows.map(row => ({
        memberCount: parseInt(row.member_count),
        importDate: row.import_date,
        adminId: row.invited_by
      }));
    } catch (error) {
      console.error('Error getting recent imports:', error);
      return [];
    }
  }

  async logImportActivity(adminId: number, memberCount: number): Promise<void> {
    try {
      // Log in member_activity table if it exists, otherwise skip
      await pool.query(`
        INSERT INTO member_activity (user_id, action_type, description, created_at)
        VALUES ($1, 'member_import', $2, NOW())
      `, [adminId, `Imported ${memberCount} members`]);
    } catch (error) {
      // Don't throw error if activity table doesn't exist
      console.log('Activity logging skipped:', error.message);
    }
  }

  // Admin conversation cleanup
  async deleteEmptyConversations(): Promise<number> {
    try {
      const result = await pool.query(`
        DELETE FROM conversations 
        WHERE id IN (
          SELECT c.id 
          FROM conversations c 
          LEFT JOIN chat_messages cm ON c.id = cm.conversation_id 
          WHERE cm.id IS NULL
        )
      `);
      return result.rowCount || 0;
    } catch (error) {
      console.error('Error deleting empty conversations:', error);
      return 0;
    }
  }

  async getEmptyConversationCount(): Promise<number> {
    try {
      const result = await pool.query(`
        SELECT COUNT(*) as count
        FROM conversations c 
        LEFT JOIN chat_messages cm ON c.id = cm.conversation_id 
        WHERE cm.id IS NULL
      `);
      return parseInt(result.rows[0]?.count || '0') || 0;
    } catch (error) {
      console.error('Error getting empty conversation count:', error);
      return 0;
    }
  }

  async getDuplicateBorrowRequests(): Promise<any[]> {
    try {
      const result = await pool.query(`
        SELECT 
          br.borrower_id,
          br.item_id,
          li.name as item_name,
          COUNT(*) as duplicate_count,
          ARRAY_AGG(br.id ORDER BY br.created_at DESC) as request_ids,
          ARRAY_AGG(br.status) as statuses
        FROM borrow_requests br
        JOIN lendable_items li ON br.item_id = li.id
        GROUP BY br.borrower_id, br.item_id, li.name
        HAVING COUNT(*) > 1
        ORDER BY duplicate_count DESC
      `);
      return result.rows;
    } catch (error) {
      console.error('Error getting duplicate borrow requests:', error);
      return [];
    }
  }

  async deleteDuplicateBorrowRequests(): Promise<number> {
    try {
      // Delete all but the most recent request for each borrower/item combination
      const result = await pool.query(`
        DELETE FROM borrow_requests 
        WHERE id IN (
          SELECT id FROM (
            SELECT id,
              ROW_NUMBER() OVER (
                PARTITION BY borrower_id, item_id 
                ORDER BY created_at DESC
              ) as row_num
            FROM borrow_requests
          ) ranked_requests
          WHERE row_num > 1
        )
      `);
      return result.rowCount || 0;
    } catch (error) {
      console.error('Error deleting duplicate borrow requests:', error);
      return 0;
    }
  }

  async deleteBorrowedItem(borrowId: number, userId: number): Promise<boolean> {
    try {
      console.log(`Deleting loan transaction with ID: ${borrowId} for user: ${userId}`);
      
      // Delete from unified loan_transactions table only
      const result = await db
        .delete(loanTransactions)
        .where(and(
          eq(loanTransactions.id, borrowId),
          eq(loanTransactions.borrowerId, userId)
        ));

      console.log(`Deleted ${result.rowCount || 0} loan transactions`);
      return (result.rowCount || 0) > 0;
    } catch (error) {
      console.error('Error deleting borrowed item:', error);
      return false;
    }
  }

  async deleteBorrowedItemAdmin(borrowId: number): Promise<boolean> {
    try {
      console.log(`Admin deleting loan transaction with ID: ${borrowId}`);
      
      // Admin can delete from unified loan_transactions table without restrictions
      const result = await db
        .delete(loanTransactions)
        .where(eq(loanTransactions.id, borrowId));
      
      console.log(`Deleted ${result.rowCount || 0} loan transactions`);
      return (result.rowCount || 0) > 0;
    } catch (error) {
      console.error('Error deleting borrowed item (admin):', error);
      return false;
    }
  }

  async deactivateActivity(activityId: number): Promise<boolean> {
    try {
      await pool.query('UPDATE member_activity SET is_active = false WHERE id = $1', [activityId]);
      return true;
    } catch (error) {
      console.error('Error deactivating activity:', error);
      return false;
    }
  }

  async reactivateActivity(activityId: number): Promise<boolean> {
    try {
      await pool.query('UPDATE member_activity SET is_active = true WHERE id = $1', [activityId]);
      return true;
    } catch (error) {
      console.error('Error reactivating activity:', error);
      return false;
    }
  }

  async logMemberActivity(userId: number, activityType: string, description: string, relatedItemId?: number, relatedMessageId?: number): Promise<void> {
    try {
      await pool.query(`
        INSERT INTO member_activity (user_id, activity_type, description, related_item_id, related_message_id)
        VALUES ($1, $2, $3, $4, $5)
      `, [userId, activityType, description, relatedItemId || null, relatedMessageId || null]);
    } catch (error) {
      console.error('Error logging member activity:', error);
    }
  }

  // Alpha Testing Feedback Methods
  async createAlphaFeedback(userId: number, userName: string, userAvatar: string | null, pageTitle: string, previousPage: string | null, currentUrl: string, userAgent: string | null, browserInfo: string | null, sessionData: string | null, testerComments: string, ipAddress: string | null, screenshot1?: string | null, screenshot2?: string | null, screenshot3?: string | null): Promise<any> {
    try {
      const result = await pool.query(`
        INSERT INTO temporary_alpha_responses 
        (user_id, user_name, user_avatar, page_title, previous_page, current_url, user_agent, browser_info, session_data, tester_comments, ip_address, screenshot1, screenshot2, screenshot3)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
        RETURNING *
      `, [userId, userName, userAvatar, pageTitle, previousPage, currentUrl, userAgent, browserInfo, sessionData, testerComments, ipAddress, screenshot1 || null, screenshot2 || null, screenshot3 || null]);
      
      return result.rows[0];
    } catch (error) {
      console.error('Error creating alpha feedback:', error);
      throw error;
    }
  }

  async getAllAlphaFeedback(): Promise<any[]> {
    try {
      const result = await pool.query(`
        SELECT * FROM temporary_alpha_responses 
        ORDER BY timestamp DESC
      `);
      return result.rows;
    } catch (error) {
      console.error('Error getting alpha feedback:', error);
      throw error;
    }
  }

  async getAlphaFeedbackById(id: number): Promise<any | null> {
    try {
      const result = await pool.query(`
        SELECT * FROM temporary_alpha_responses 
        WHERE id = $1
      `, [id]);
      
      return result.rows[0] || null;
    } catch (error) {
      console.error('Error getting alpha feedback by ID:', error);
      throw error;
    }
  }

  async markAlphaFeedbackResolved(id: number): Promise<any | null> {
    try {
      const result = await pool.query(`
        UPDATE temporary_alpha_responses 
        SET resolved = true 
        WHERE id = $1 
        RETURNING *
      `, [id]);
      
      return result.rows[0] || null;
    } catch (error) {
      console.error('Error marking alpha feedback as resolved:', error);
      throw error;
    }
  }

  async saveAlphaFeedbackReply(feedbackId: number, reply: string, adminId: number): Promise<any | null> {
    try {
      const result = await pool.query(`
        UPDATE temporary_alpha_responses 
        SET admin_reply = $1, admin_reply_date = NOW(), admin_id = $2 
        WHERE id = $3 
        RETURNING *
      `, [reply, adminId, feedbackId]);
      
      return result.rows[0] || null;
    } catch (error) {
      console.error('Error saving alpha feedback reply:', error);
      throw error;
    }
  }

  async returnLoan(loanId: number): Promise<boolean> {
    try {
      // UNIFIED SYSTEM: Get loan details from loan_transactions
      const [loanDetails] = await db
        .select({
          id: loanTransactions.id,
          itemId: loanTransactions.itemId,
          borrowerId: loanTransactions.borrowerId,
          itemName: lendableItems.name,
          ownerId: loanTransactions.ownerId,
          borrowerName: users.firstName
        })
        .from(loanTransactions)
        .leftJoin(lendableItems, eq(loanTransactions.itemId, lendableItems.id))
        .leftJoin(users, eq(loanTransactions.borrowerId, users.id))
        .where(eq(loanTransactions.id, loanId));

      if (!loanDetails) {
        return false;
      }

      // UNIFIED SYSTEM: Update loan_transactions status to return_requested
      await db
        .update(loanTransactions)
        .set({ 
          status: 'return_requested',
          returnRequestedAt: new Date()
        })
        .where(and(
          eq(loanTransactions.itemId, loanDetails.itemId),
          eq(loanTransactions.borrowerId, loanDetails.borrowerId),
          eq(loanTransactions.status, 'active')
        ));

      // Create notification for the owner
      await this.createNotification({
        userId: loanDetails.ownerId,
        type: 'return_initiated',
        title: 'Item Return Initiated',
        message: `${loanDetails.borrowerName} has initiated the return process for "${loanDetails.itemName}". Please confirm the return when you receive the item.`,
        relatedId: loanDetails.itemId,
        relatedType: 'item'
      });

      return true;
    } catch (error) {
      console.error('Error returning loan:', error);
      return false;
    }
  }

  // Push Return: Owner declares item returned without borrower coordination
  async pushReturnLoan(loanId: number, ownerId: number, hasConcerns?: boolean, concernsMessage?: string): Promise<boolean> {
    try {
      console.log(`Push return attempt (UNIFIED SYSTEM): loanId=${loanId}, ownerId=${ownerId}`);

      // CONSOLIDATED: Check only the unified loan_transactions table (including pending for dismissal)
      const [loan] = await db
        .select({
          id: loanTransactions.id,
          itemId: loanTransactions.itemId,
          borrowerId: loanTransactions.borrowerId,
          ownerId: loanTransactions.ownerId,
          status: loanTransactions.status,
          sourceSystem: loanTransactions.sourceSystem,
          originalId: loanTransactions.originalId,
          itemName: lendableItems.name,
          borrowerName: users.firstName,
          borrowerShowName: users.showName
        })
        .from(loanTransactions)
        .leftJoin(lendableItems, eq(loanTransactions.itemId, lendableItems.id))
        .leftJoin(users, eq(loanTransactions.borrowerId, users.id))
        .where(and(
          eq(loanTransactions.id, loanId),
          inArray(loanTransactions.status, ['pending', 'approved', 'active', 'return_requested'])
        ));

      if (!loan) {
        console.log(`No active loan found with ID ${loanId}`);
        return false;
      }

      if (loan.ownerId !== ownerId) {
        console.log(`Owner authorization failed: expected ${ownerId}, got ${loan.ownerId}`);
        return false;
      }

      console.log(`Found unified loan: ${loan.itemName}, status=${loan.status}, source=${loan.sourceSystem}`);

      // Handle pending vs active loans differently
      const isPendingLoan = loan.status === 'pending';
      const newStatus = isPendingLoan ? 'dismissed' : 'returned';
      
      // Update the unified loan transaction
      await db
        .update(loanTransactions)
        .set({
          status: newStatus,
          returnedAt: new Date(),
          updatedAt: new Date()
        })
        .where(eq(loanTransactions.id, loanId));

      // UNIFIED SYSTEM: All loan tracking now handled by loan_transactions table only
      // No need to update legacy tables since unified system is the single source of truth

      // ENHANCED COMPREHENSIVE NOTIFICATION CLEANUP: Clean up ALL overdue/due notifications for this item
      await db
        .update(notifications)
        .set({ 
          isRead: true,
          isDismissed: true 
        })
        .where(and(
          eq(notifications.itemId, loan.itemId),
          inArray(notifications.type, [
            'return_overdue', 'owner_overdue', 
            'return_due_tomorrow', 'owner_due_tomorrow',
            'return_due_today', 'owner_due_today'  // FIXED: Added missing due_today types
          ]),
          eq(notifications.isDismissed, false)
        ));

      // Also clean up any borrower-specific notifications for this item
      await db
        .update(notifications)
        .set({ 
          isRead: true,
          isDismissed: true 
        })
        .where(and(
          eq(notifications.userId, loan.borrowerId),
          eq(notifications.itemId, loan.itemId),
          inArray(notifications.type, [
            'return_overdue', 'return_due_tomorrow', 'return_due_today', 
            'borrow_request_approved'
          ]),
          eq(notifications.isDismissed, false)
        ));

      console.log(`Comprehensive cleanup completed for item ${loan.itemId}, borrower ${loan.borrowerId}`);

      // Create appropriate notification for borrower
      let notificationMessage = isPendingLoan 
        ? `Your request for "${loan.itemName}" has been dismissed by the owner.`
        : `Your return of "${loan.itemName}" has been confirmed by the owner.`;
      
      // Add concerns message if provided
      if (hasConcerns && concernsMessage) {
        notificationMessage += `\n\nOwner's concerns: ${concernsMessage}`;
      }
      
      const notificationTitle = isPendingLoan ? 'Request Dismissed' : 'Item Return Confirmed';
      
      await this.createNotification({
        userId: loan.borrowerId,
        type: isPendingLoan ? 'request_dismissed' : 'return_confirmed',
        title: notificationTitle,
        message: notificationMessage
      });

      console.log(`Successfully marked unified loan ${loanId} as ${newStatus} (${loan.sourceSystem})`);
      return true;
    } catch (error) {
      console.error('Error pushing return (unified system):', error);
      return false;
    }
  }

  // Admin methods for managing conversations and activities  
  async deleteMessage(messageId: number): Promise<boolean> {
    try {
      await pool.query('DELETE FROM chat_messages WHERE id = $1', [messageId]);
      return true;
    } catch (error) {
      console.error('Error deleting message:', error);
      return false;
    }
  }

  // Points and Rewards System
  async getUserPoints(userId: number): Promise<{ totalPoints: number; lifetimePoints: number }> {
    try {
      const result = await pool.query(
        'SELECT total_points, lifetime_points FROM user_points WHERE user_id = $1',
        [userId]
      );
      
      if (result.rows.length === 0) {
        // Initialize points for new user
        await pool.query(
          'INSERT INTO user_points (user_id, total_points, lifetime_points) VALUES ($1, 0, 0)',
          [userId]
        );
        return { totalPoints: 0, lifetimePoints: 0 };
      }
      
      return {
        totalPoints: result.rows[0].total_points,
        lifetimePoints: result.rows[0].lifetime_points
      };
    } catch (error) {
      console.error('Error getting user points:', error);
      return { totalPoints: 0, lifetimePoints: 0 };
    }
  }

  async awardPoints(userId: number, points: number, type: string, description: string, referenceId?: number, referenceType?: string): Promise<boolean> {
    try {
      await pool.query('BEGIN');
      
      // Add points transaction
      await pool.query(
        'INSERT INTO points_transactions (user_id, points_change, transaction_type, description, reference_id, reference_type) VALUES ($1, $2, $3, $4, $5, $6)',
        [userId, points, type, description, referenceId, referenceType]
      );
      
      // Update user points
      await pool.query(
        'UPDATE user_points SET total_points = total_points + $1, lifetime_points = lifetime_points + $1, updated_at = CURRENT_TIMESTAMP WHERE user_id = $2',
        [points, userId]
      );
      
      await pool.query('COMMIT');
      return true;
    } catch (error) {
      await pool.query('ROLLBACK');
      console.error('Error awarding points:', error);
      return false;
    }
  }

  async getRewardsCatalog(): Promise<any[]> {
    try {
      const result = await pool.query(
        'SELECT * FROM rewards_catalog WHERE is_active = true ORDER BY category, points_cost'
      );
      return result.rows;
    } catch (error) {
      console.error('Error getting rewards catalog:', error);
      return [];
    }
  }

  async redeemReward(userId: number, rewardId: number): Promise<{ success: boolean; couponCode?: string; error?: string }> {
    try {
      await pool.query('BEGIN');
      
      // Check user points
      const userPoints = await this.getUserPoints(userId);
      
      // Get reward details
      const rewardResult = await pool.query(
        'SELECT * FROM rewards_catalog WHERE id = $1 AND is_active = true',
        [rewardId]
      );
      
      if (rewardResult.rows.length === 0) {
        await pool.query('ROLLBACK');
        return { success: false, error: 'Reward not found or inactive' };
      }
      
      const reward = rewardResult.rows[0];
      
      if (userPoints.totalPoints < reward.points_cost) {
        await pool.query('ROLLBACK');
        return { success: false, error: 'Insufficient points' };
      }
      
      if (reward.quantity_available !== null && reward.quantity_available <= 0) {
        await pool.query('ROLLBACK');
        return { success: false, error: 'Reward out of stock' };
      }
      
      // Generate unique coupon code
      const couponCode = `LL${Date.now()}${userId}${rewardId}`.substring(0, 16);
      
      // Create redemption record
      await pool.query(
        'INSERT INTO reward_redemptions (user_id, reward_id, points_spent, status, notes) VALUES ($1, $2, $3, $4, $5)',
        [userId, rewardId, reward.points_cost, 'pending', `Coupon Code: ${couponCode}`]
      );
      
      // Deduct points
      await pool.query(
        'UPDATE user_points SET total_points = total_points - $1, updated_at = CURRENT_TIMESTAMP WHERE user_id = $2',
        [reward.points_cost, userId]
      );
      
      // Add transaction record
      await pool.query(
        'INSERT INTO points_transactions (user_id, points_change, transaction_type, description, reference_id, reference_type) VALUES ($1, $2, $3, $4, $5, $6)',
        [userId, -reward.points_cost, 'redemption', `Redeemed: ${reward.name}`, rewardId, 'reward']
      );
      
      // Update quantity if applicable
      if (reward.quantity_available !== null) {
        await pool.query(
          'UPDATE rewards_catalog SET quantity_available = quantity_available - 1 WHERE id = $1',
          [rewardId]
        );
      }
      
      await pool.query('COMMIT');
      return { success: true, couponCode };
    } catch (error) {
      await pool.query('ROLLBACK');
      console.error('Error redeeming reward:', error);
      return { success: false, error: 'Failed to process redemption' };
    }
  }

  async getUserRedemptions(userId: number): Promise<any[]> {
    try {
      const result = await pool.query(`
        SELECT rr.*, rc.name as reward_name, rc.sponsor_name
        FROM reward_redemptions rr
        JOIN rewards_catalog rc ON rr.reward_id = rc.id
        WHERE rr.user_id = $1
        ORDER BY rr.redeemed_at DESC
      `, [userId]);
      return result.rows;
    } catch (error) {
      console.error('Error getting user redemptions:', error);
      return [];
    }
  }

  async getUserActiveLoans(userId: number): Promise<any[]> {
    try {
      // UNIFIED SYSTEM: Use only loan_transactions table
      const loans = await db
        .select({
          trackingId: loanTransactions.id,
          status: loanTransactions.status,
          pickedUpAt: loanTransactions.pickedUpAt,
          returnRequestedAt: loanTransactions.returnRequestedAt,
          itemName: lendableItems.name,
          itemId: lendableItems.id,
          requiresDigitalSignature: lendableItems.requiresDigitalSignature,
          ownerCanPushForward: lendableItems.ownerCanPushForward,
          startDate: loanTransactions.requestedDate,
          endDate: loanTransactions.expectedReturnDate,
          agreementId: loanTransactions.agreementId,
          agreementStatus: loanAgreements.status,
          returnQrCode: loanAgreements.returnQrCode,
          itemQrCode: itemQrCodes.qrCode,
          itemQrUrl: itemQrCodes.qrUrl,
          borrowerName: users.firstName,
          ownerName: users.firstName,
          ownerId: loanTransactions.ownerId,
          borrowerId: loanTransactions.borrowerId
        })
        .from(loanTransactions)
        .leftJoin(loanAgreements, eq(loanTransactions.agreementId, loanAgreements.id))
        .leftJoin(lendableItems, eq(loanTransactions.itemId, lendableItems.id))
        .leftJoin(itemQrCodes, eq(lendableItems.id, itemQrCodes.itemId))
        .leftJoin(users, eq(loanTransactions.borrowerId, users.id))
        .where(and(
          or(
            eq(loanTransactions.borrowerId, userId),
            eq(loanTransactions.ownerId, userId)
          ),
          inArray(loanTransactions.status, ['pending', 'approved', 'active', 'return_requested'])
        ))
        .orderBy(desc(loanTransactions.requestedDate));
      
      // Add computed fields for compatibility
      return loans.map(loan => ({
        ...loan,
        otherPartyName: loan.ownerId === userId ? loan.borrowerName : loan.ownerName,
        userRole: loan.ownerId === userId ? 'owner' : 'borrower'
      }));
    } catch (error) {
      console.error('Error getting user active loans:', error);
      return [];
    }
  }

  async pushForwardWorkflow(trackingId: number, ownerId: number, action: string, notes?: string): Promise<{ success: boolean; message: string }> {
    try {
      // UNIFIED SYSTEM: Verify user is owner and can push forward
      const [tracking] = await db
        .select({
          id: loanTransactions.id,
          status: loanTransactions.status,
          itemId: loanTransactions.itemId,
          borrowerId: loanTransactions.borrowerId,
          ownerId: loanTransactions.ownerId,
          ownerCanPushForward: lendableItems.ownerCanPushForward,
          requiresDigitalSignature: lendableItems.requiresDigitalSignature,
          owner: lendableItems.owner
        })
        .from(loanTransactions)
        .leftJoin(lendableItems, eq(loanTransactions.itemId, lendableItems.id))
        .where(and(
          eq(loanTransactions.id, trackingId),
          eq(lendableItems.owner, ownerId)
        ));
      
      if (!tracking) {
        return { success: false, message: 'Not authorized or loan not found' };
      }
      
      if (!tracking.ownerCanPushForward) {
        return { success: false, message: 'Owner push-forward not enabled for this item' };
      }

      switch (action) {
        case 'skip_to_active':
          // UNIFIED SYSTEM: Owner can bypass signature requirement and mark as active
          await db
            .update(loanTransactions)
            .set({ 
              status: 'active',
              pickedUpAt: new Date()
            })
            .where(eq(loanTransactions.id, trackingId));
          
          if (tracking.agreementId) {
            await db
              .update(loanAgreements)
              .set({ status: 'owner_bypassed' })
              .where(eq(loanAgreements.id, tracking.agreementId));
          }
          
          await db
            .update(lendableItems)
            .set({ isAvailable: false })
            .where(eq(lendableItems.id, tracking.itemId));
          
          return { success: true, message: 'Loan activated by owner (signatures bypassed)' };

        case 'mark_returned':
          // UNIFIED SYSTEM: Owner can directly complete return process
          try {
            await db
              .update(loanTransactions)
              .set({ 
                status: 'returned',
                returnCompletedAt: new Date()
              })
              .where(eq(loanTransactions.id, trackingId));
              
            await db
              .update(lendableItems)
              .set({ isAvailable: true })
              .where(eq(lendableItems.id, tracking.itemId));
              
            return { success: true, message: 'Return completed by owner' };
          } catch (error) {
            return { success: false, message: 'Failed to complete return' };
          }

        default:
          return { success: false, message: 'Unknown action' };
      }
    } catch (error) {
      console.error('Error pushing forward workflow:', error);
      return { success: false, message: 'Server error' };
    }
  }

  async getOrCreateItemQR(itemId: number): Promise<any> {
    try {
      // QR code is just the item ID - simple and direct
      const qrCode = itemId.toString();
      const qrUrl = `/qr/${qrCode}`;
      
      return {
        item_id: itemId,
        qr_code: qrCode,
        qr_url: qrUrl,
        created_at: new Date()
      };
    } catch (error) {
      console.error('Error creating QR code:', error);
      throw error;
    }
  }

  async getItemByQRCode(qrCode: string): Promise<any> {
    try {
      // QR code is just the item ID - convert directly
      const itemId = parseInt(qrCode);
      if (isNaN(itemId)) {
        console.error('Invalid QR code - not a number:', qrCode);
        return null;
      }
      
      console.log(`Looking up item ID ${itemId} from QR code: ${qrCode}`);
      
      // Get item details with current loan status
      const [result] = await db
        .select({
          id: lendableItems.id,
          name: lendableItems.name,
          description: lendableItems.description,
          category: lendableItems.category,
          owner: lendableItems.owner,
          primaryLogo: lendableItems.primaryLogo,
          isAvailable: lendableItems.isAvailable,
          qrCode: sql`${qrCode}`.as('qrCode'),
          ownerName: users.firstName,
          ownerDisplayName: users.showName,
          currentLoanStatus: loanTransactions.status,
          trackingId: loanTransactions.id,
          agreementId: loanTransactions.agreementId,
          borrowerId: loanTransactions.borrowerId,
          startDate: loanTransactions.requestedDate,
          endDate: loanTransactions.expectedReturnDate
        })
        .from(lendableItems)
        .leftJoin(users, eq(lendableItems.owner, users.id))
        .leftJoin(loanTransactions, and(
          eq(loanTransactions.itemId, lendableItems.id),
          inArray(loanTransactions.status, ['pending', 'approved', 'active', 'return_requested'])
        ))
        .where(eq(lendableItems.id, itemId));
      
      return result || null;
    } catch (error) {
      console.error('Error getting item by QR code:', error);
      return null;
    }
  }

  async updateItemStatusByQR(qrCode: string, action: string, userId: number, notes?: string, thankYouMessage?: string): Promise<{ success: boolean; message: string; data?: any }> {
    try {
      const item = await this.getItemByQRCode(qrCode);
      if (!item) {
        return { success: false, message: 'Item not found for this QR code' };
      }

      switch (action) {
        case 'pickup':
          if (item.currentLoanStatus !== 'approved') {
            return { success: false, message: 'Item is not ready for pickup' };
          }
          
          // UNIFIED SYSTEM: Update loan_transactions for pickup
          await db
            .update(loanTransactions)
            .set({ 
              status: 'active',
              pickedUpAt: new Date()
            })
            .where(eq(loanTransactions.id, item.trackingId));
          
          return { success: true, message: 'Item pickup confirmed', data: { status: 'active' } };

        case 'return_request':
          if (item.currentLoanStatus !== 'active') {
            return { success: false, message: 'Item is not currently on loan' };
          }
          
          // UNIFIED SYSTEM: Update loan_transactions for return request
          await db
            .update(loanTransactions)
            .set({ 
              status: 'return_requested',
              returnRequestedAt: new Date()
            })
            .where(eq(loanTransactions.id, item.trackingId));
          
          return { success: true, message: 'Return request initiated', data: { status: 'return_requested' } };

        case 'return_complete':
          if (item.current_loan_status !== 'return_requested') {
            return { success: false, message: 'Return was not initiated' };
          }
          
          // This would typically require owner confirmation
          const success = await this.completeReturn(item.tracking_id, item.owner, 'good', notes);
          if (success) {
            return { success: true, message: 'Return completed and points awarded!', data: { status: 'returned' } };
          } else {
            return { success: false, message: 'Failed to complete return' };
          }

        default:
          return { success: false, message: 'Unknown action' };
      }
    } catch (error) {
      console.error('Error updating item status by QR:', error);
      return { success: false, message: 'Server error' };
    }
  }

  async getPointsTransactions(userId: number, limit: number = 20): Promise<any[]> {
    try {
      const result = await pool.query(
        'SELECT * FROM points_transactions WHERE user_id = $1 ORDER BY created_at DESC LIMIT $2',
        [userId, limit]
      );
      return result.rows;
    } catch (error) {
      console.error('Error getting points transactions:', error);
      return [];
    }
  }

  async getConversationsForAdmin() {
    // Get all conversations with complete participant details
    const conversationGroups = await pool.query(`
      SELECT 
        c.id as conversation_id,
        c.item_id,
        li.name as item_name,
        c.owner_id,
        c.borrower_id,
        COALESCE(owner.show_name, owner.first_name || ' ' || owner.last_name) as owner_name,
        COALESCE(borrower.show_name, borrower.first_name || ' ' || borrower.last_name) as borrower_name,
        COUNT(cm.id)::int as message_count,
        MIN(cm.created_at) as first_message_at,
        MAX(cm.created_at) as last_message_at
      FROM conversations c
      LEFT JOIN lendable_items li ON c.item_id = li.id
      LEFT JOIN users owner ON c.owner_id = owner.id
      LEFT JOIN users borrower ON c.borrower_id = borrower.id
      LEFT JOIN chat_messages cm ON c.id = cm.conversation_id
      GROUP BY c.id, c.item_id, li.name, c.owner_id, c.borrower_id, owner_name, borrower_name
      ORDER BY MAX(cm.created_at) DESC NULLS LAST
    `);

    const conversationData = conversationGroups.rows;

    // Get recent messages for each conversation
    const conversationsWithDetails = await Promise.all(
      conversationData.map(async (conv) => {
        const recentMessagesBase = await db
          .select()
          .from(chatMessages)
          .where(eq(chatMessages.conversationId, conv.conversation_id))
          .orderBy(desc(chatMessages.createdAt))
          .limit(3);

        // Enrich with user data separately 
        const recentMessages = await Promise.all(
          recentMessagesBase.map(async (msg) => {
            const sender = await this.getUser(msg.senderId);
            return {
              id: msg.id,
              senderId: msg.senderId,
              textContent: msg.textContent,
              audioPath: msg.audioPath,
              transcription: msg.transcription,
              createdAt: msg.createdAt,
              senderName: sender?.showName || sender?.firstName || 'Unknown User'
            };
          })
        );

        return {
          id: conv.conversation_id,
          conversationId: conv.conversation_id,
          itemName: conv.item_name || `Conversation ${conv.conversation_id}`,
          itemId: conv.item_id,
          ownerId: conv.owner_id,
          borrowerId: conv.borrower_id,
          ownerName: conv.owner_name || 'Unknown Owner',
          borrowerName: conv.borrower_name || 'Unknown Borrower',
          messageCount: parseInt(conv.message_count) || 0,
          createdAt: conv.first_message_at,
          lastMessageAt: conv.last_message_at,
          isActive: true,
          recentMessages: recentMessages.map(msg => ({
            ...msg,
            content: msg.textContent,
            senderName: msg.senderName || 'Unknown User'
          }))
        };
      })
    );

    return conversationsWithDetails;
  }



  async addToFavorites(userId: number, itemId: number): Promise<boolean> {
    try {
      await db.insert(userFavorites).values({ userId, itemId });
      return true;
    } catch (error) {
      console.error('Error adding to favorites:', error);
      return false;
    }
  }

  async removeFromFavorites(userId: number, itemId: number): Promise<boolean> {
    try {
      await db
        .delete(userFavorites)
        .where(and(
          eq(userFavorites.userId, userId),
          eq(userFavorites.itemId, itemId)
        ));
      return true;
    } catch (error) {
      console.error('Error removing from favorites:', error);
      return false;
    }
  }

  async isItemFavorited(userId: number, itemId: number): Promise<boolean> {
    try {
      const result = await db
        .select()
        .from(userFavorites)
        .where(and(
          eq(userFavorites.userId, userId),
          eq(userFavorites.itemId, itemId)
        ))
        .limit(1);
      return result.length > 0;
    } catch (error) {
      console.error('Error checking if item is favorited:', error);
      return false;
    }
  }

  async getUserFavorites(userId: number): Promise<any[]> {
    try {
      const favorites = await db
        .select({
          id: lendableItems.id,
          name: lendableItems.name,
          description: lendableItems.description,
          primaryLogo: lendableItems.primaryLogo,
          category: lendableItems.category,
          categoryName: lendableItemCategories.category,
          owner: lendableItems.owner,
          ownerFirstName: users.firstName,
          ownerLastName: users.lastName,
          ownerShowName: users.showName,
          ownerAvatar: users.avatar,
          addedOn: lendableItems.addedOn,
          favoritedAt: userFavorites.createdAt
        })
        .from(userFavorites)
        .leftJoin(lendableItems, eq(userFavorites.itemId, lendableItems.id))
        .leftJoin(users, eq(lendableItems.owner, users.id))
        .leftJoin(lendableItemCategories, eq(lendableItems.category, lendableItemCategories.id))
        .where(and(
          eq(userFavorites.userId, userId),
          eq(lendableItems.status, true)
        ))
        .orderBy(desc(userFavorites.createdAt));

      return favorites;
    } catch (error) {
      console.error('Error fetching user favorites:', error);
      return [];
    }
  }

  async getActiveLoansByBorrower(borrowerId: number): Promise<any[]> {
    try {
      const loans = await db
        .select({
          id: loanTransactions.id,
          itemId: loanTransactions.itemId,
          borrowedDate: loanTransactions.startDate,
          expectedReturnDate: loanTransactions.endDate,
          actualReturnDate: loanTransactions.returnDate,
          status: loanTransactions.status,
          message: loanTransactions.message
        })
        .from(loanTransactions)
        .where(
          and(
            eq(loanTransactions.borrowerId, borrowerId),
            or(
              eq(loanTransactions.status, 'approved'),
              eq(loanTransactions.status, 'active'),
              eq(loanTransactions.status, 'overdue'),
              eq(loanTransactions.status, 'return_requested')
            )
          )
        );

      // Get item details for each loan
      const loansWithItems = await Promise.all(
        loans.map(async (loan) => {
          const [item] = await db
            .select()
            .from(lendableItems)
            .where(eq(lendableItems.id, loan.itemId));
          
          return {
            ...loan,
            item: item || null
          };
        })
      );

      return loansWithItems.filter(loan => loan.item);
    } catch (error) {
      console.error('Error fetching active loans by borrower:', error);
      return [];
    }
  }

  async getActiveLoansByOwner(ownerId: number): Promise<any[]> {
    try {
      const loans = await db
        .select({
          id: loanTransactions.id,
          itemId: loanTransactions.itemId,
          borrowerId: loanTransactions.borrowerId,
          borrowedDate: loanTransactions.startDate,
          expectedReturnDate: loanTransactions.endDate,
          actualReturnDate: loanTransactions.returnDate,
          status: loanTransactions.status,
          message: loanTransactions.message
        })
        .from(loanTransactions)
        .innerJoin(lendableItems, eq(loanTransactions.itemId, lendableItems.id))
        .where(
          and(
            eq(loanTransactions.ownerId, ownerId),
            or(
              eq(loanTransactions.status, 'approved'),
              eq(loanTransactions.status, 'active'),
              eq(loanTransactions.status, 'overdue'),
              eq(loanTransactions.status, 'return_requested')
            )
          )
        );

      // Get item and borrower details for each loan
      const loansWithDetails = await Promise.all(
        loans.map(async (loan) => {
          const [item] = await db
            .select()
            .from(lendableItems)
            .where(eq(lendableItems.id, loan.itemId));
          
          const [borrower] = await db
            .select()
            .from(users)
            .where(eq(users.id, loan.borrowerId));
          
          return {
            ...loan,
            item: item || null,
            borrower: borrower || null
          };
        })
      );

      return loansWithDetails.filter(loan => loan.item && loan.borrower);
    } catch (error) {
      console.error('Error fetching active loans by owner:', error);
      return [];
    }
  }

  async getContentBlock(identifier: string): Promise<any> {
    try {
      const [block] = await db
        .select()
        .from(contentBlocks)
        .where(eq(contentBlocks.identifier, identifier))
        .limit(1);
      
      return block;
    } catch (error) {
      console.error('Error fetching content block:', error);
      return null;
    }
  }

  async updateUserPreferences(userId: number, preferences: any): Promise<void> {
    try {
      // Check if preferences exist
      const existing = await this.getUserPreferences(userId);
      
      if (existing) {
        // Update existing preferences
        await db
          .update(userPreferences)
          .set(preferences)
          .where(eq(userPreferences.userId, userId));
      } else {
        // Create new preferences record
        await db
          .insert(userPreferences)
          .values({
            userId,
            ...preferences
          });
      }
    } catch (error) {
      console.error('Error updating user preferences:', error);
      throw error;
    }
  }

  async getAdminTooltips(): Promise<Record<string, string>> {
    try {
      const result = await db.execute(sql`
        SELECT tooltip_key, tooltip_text 
        FROM admin_tooltips
      `);
      
      const tooltips: Record<string, string> = {};
      for (const row of result.rows) {
        tooltips[row[0] as string] = row[1] as string;
      }
      
      return tooltips;
    } catch (error) {
      console.error('Error fetching admin tooltips:', error);
      return {};
    }
  }

  async updateAdminTooltip(key: string, text: string): Promise<boolean> {
    try {
      await db.execute(sql`
        INSERT INTO admin_tooltips (tooltip_key, tooltip_text)
        VALUES (${key}, ${text})
        ON CONFLICT (tooltip_key) 
        DO UPDATE SET 
          tooltip_text = EXCLUDED.tooltip_text,
          updated_at = CURRENT_TIMESTAMP
      `);
      
      return true;
    } catch (error) {
      console.error('Error updating admin tooltip:', error);
      return false;
    }
  }

  // Static Pages methods
  async getAllStaticPages(): Promise<any[]> {
    try {
      const pages = await db.select().from(staticPages).orderBy(staticPages.sortOrder, staticPages.title);
      return pages;
    } catch (error) {
      console.error('Error fetching static pages:', error);
      return [];
    }
  }

  async getStaticPageBySlug(slug: string): Promise<any | null> {
    try {
      const pages = await db.select().from(staticPages).where(eq(staticPages.slug, slug));
      return pages[0] || null;
    } catch (error) {
      console.error('Error fetching static page:', error);
      return null;
    }
  }

  async createStaticPage(pageData: any, userId: number): Promise<any | null> {
    try {
      const result = await db.insert(staticPages).values({
        slug: pageData.slug,
        title: pageData.title,
        content: pageData.content,
        variables: pageData.variables ? JSON.stringify(pageData.variables) : null,
        isActive: pageData.isActive !== undefined ? pageData.isActive : true,
        sortOrder: pageData.sortOrder || 0,
        createdBy: userId,
        updatedBy: userId
      }).returning();
      
      return result[0] || null;
    } catch (error) {
      console.error('Error creating static page:', error);
      return null;
    }
  }

  async updateStaticPage(slug: string, pageData: any, userId: number): Promise<any | null> {
    try {
      const result = await db.update(staticPages)
        .set({
          title: pageData.title,
          content: pageData.content,
          variables: pageData.variables ? JSON.stringify(pageData.variables) : null,
          isActive: pageData.isActive !== undefined ? pageData.isActive : true,
          sortOrder: pageData.sortOrder || 0,
          updatedBy: userId,
          updatedAt: new Date()
        })
        .where(eq(staticPages.slug, slug))
        .returning();
      
      return result[0] || null;
    } catch (error) {
      console.error('Error updating static page:', error);
      return null;
    }
  }

  async deleteStaticPage(slug: string): Promise<boolean> {
    try {
      await db.delete(staticPages).where(eq(staticPages.slug, slug));
      return true;
    } catch (error) {
      console.error('Error deleting static page:', error);
      return false;
    }
  }

  // Process dynamic variables in static page content
  processStaticPageContent(content: string, variables: any = {}): string {
    if (!content) return '';
    
    // Default system variables
    const systemVars = {
      site_name: 'LocalLendables',
      current_date: new Date().toLocaleDateString(),
      last_updated: new Date().toLocaleDateString(),
      manager_name: 'Community Manager',
      manager_email: 'manager@locallendables.com',
      community_phone: '(555) 123-4567',
      total_members: '42',
      total_items: '156', 
      active_loans: '23',
      ...variables
    };

    let processedContent = content;
    
    // Replace all variables using regex
    Object.entries(systemVars).forEach(([key, value]) => {
      const regex = new RegExp(`\\{\\{${key}\\}\\}`, 'g');
      processedContent = processedContent.replace(regex, String(value));
    });
    
    return processedContent;
  }

  // Hour offerings methods
  async createHourOffering(offering: InsertHourOffering): Promise<HourOffering> {
    try {
      const [result] = await db.insert(hourOfferings).values({
        userId: offering.userId,
        title: offering.title,
        description: offering.description,
        offerDate: offering.offerDate,
        endTime: offering.endTime,
        isActive: offering.isActive ?? true,
        isBooked: offering.isBooked ?? false,
        isRecurring: offering.isRecurring ?? false,
        recurringType: offering.recurringType ?? null,
        recurringCount: offering.recurringCount ?? null,
        recurringEndDate: offering.recurringEndDate ?? null,
        parentOfferingId: offering.parentOfferingId ?? null
      }).returning();
      return result;
    } catch (error) {
      console.error('Error creating hour offering:', error);
      throw error;
    }
  }

  async getUserHourOfferings(userId: number): Promise<HourOffering[]> {
    try {
      const today = new Date();
      const thirtyDaysFromNow = new Date();
      thirtyDaysFromNow.setDate(today.getDate() + 30);
      
      return await db.select().from(hourOfferings)
        .where(and(
          eq(hourOfferings.userId, userId),
          eq(hourOfferings.isActive, true),
          sql`${hourOfferings.offerDate} >= CURRENT_DATE`,
          sql`${hourOfferings.offerDate} <= ${thirtyDaysFromNow.toISOString().split('T')[0]}`
        ))
        .orderBy(hourOfferings.offerDate);
    } catch (error) {
      console.error('Error getting user hour offerings:', error);
      return [];
    }
  }

  async getAvailableHourOfferings(): Promise<any[]> {
    try {
      const results = await db.select({
        id: hourOfferings.id,
        userId: hourOfferings.userId,
        title: hourOfferings.title,
        description: hourOfferings.description,
        offerDate: hourOfferings.offerDate,
        startTime: hourOfferings.offerDate, // For template compatibility
        endTime: hourOfferings.endTime,
        isActive: hourOfferings.isActive,
        isBooked: hourOfferings.isBooked,
        isRecurring: hourOfferings.isRecurring,
        recurringType: hourOfferings.recurringType,
        recurringCount: hourOfferings.recurringCount,
        recurringEndDate: hourOfferings.recurringEndDate,
        parentOfferingId: hourOfferings.parentOfferingId,
        createdAt: hourOfferings.createdAt,
        userName: sql`COALESCE(${users.showName}, ${users.firstName})`.as('userName'),
        offerUserShowName: users.showName, // Template expects this field name
        offerUserFirstName: users.firstName, // Template expects this field name  
        offerUserAvatar: users.avatar // Template expects this field name
      })
      .from(hourOfferings)
      .leftJoin(users, eq(hourOfferings.userId, users.id))
      .where(and(
        eq(hourOfferings.isActive, true),
        eq(hourOfferings.isBooked, false),
        sql`${hourOfferings.offerDate} >= CURRENT_DATE`
      ))
      .orderBy(hourOfferings.offerDate);

      return results;
    } catch (error) {
      console.error('Error getting available hour offerings:', error);
      return [];
    }
  }

  async getHourOfferingsByDateRange(startDate: string, endDate: string): Promise<any[]> {
    try {
      const results = await db.select({
        id: hourOfferings.id,
        userId: hourOfferings.userId,
        title: hourOfferings.title,
        description: hourOfferings.description,
        offerDate: hourOfferings.offerDate,
        startTime: hourOfferings.offerDate, // For template compatibility
        endTime: hourOfferings.endTime,
        isActive: hourOfferings.isActive,
        isBooked: hourOfferings.isBooked,
        isRecurring: hourOfferings.isRecurring,
        recurringType: hourOfferings.recurringType,
        recurringCount: hourOfferings.recurringCount,
        recurringEndDate: hourOfferings.recurringEndDate,
        parentOfferingId: hourOfferings.parentOfferingId,
        createdAt: hourOfferings.createdAt,
        userName: sql`COALESCE(${users.showName}, ${users.firstName})`.as('userName'),
        offerUserShowName: users.showName, // Template expects this field name
        offerUserFirstName: users.firstName, // Template expects this field name
        offerUserAvatar: users.avatar // Template expects this field name
      })
      .from(hourOfferings)
      .leftJoin(users, eq(hourOfferings.userId, users.id))
      .where(and(
        eq(hourOfferings.isActive, true),
        eq(hourOfferings.isBooked, false)
      ))
      .orderBy(hourOfferings.offerDate);

      // Expand recurring offerings into individual instances
      const expandedResults = [];
      const startDateObj = new Date(startDate);
      const endDateObj = new Date(endDate);

      for (const offering of results) {
        if (offering.isRecurring && offering.recurringCount && offering.recurringCount > 1) {
          // Generate recurring instances
          const baseDate = new Date(offering.offerDate);
          const baseEndTime = new Date(offering.endTime);
          const timeDiff = baseEndTime.getTime() - baseDate.getTime();
          
          for (let i = 0; i < offering.recurringCount; i++) {
            const instanceDate = new Date(baseDate);
            
            // Add weeks for recurring instances
            if (offering.recurringType === 'weekly') {
              instanceDate.setDate(instanceDate.getDate() + (i * 7));
            } else if (offering.recurringType === 'daily') {
              instanceDate.setDate(instanceDate.getDate() + i);
            }
            
            // Check if this instance falls within the requested date range
            if (instanceDate >= startDateObj && instanceDate <= endDateObj) {
              const instanceEndTime = new Date(instanceDate.getTime() + timeDiff);
              
              expandedResults.push({
                ...offering,
                offerDate: instanceDate.toISOString(),
                startTime: instanceDate.toISOString(),
                endTime: instanceEndTime.toISOString(),
                id: `${offering.id}-${i}` // Unique ID for each instance
              });
            }
          }
        } else {
          // Non-recurring offering - only include if it falls within date range
          const offeringDate = new Date(offering.offerDate);
          if (offeringDate >= startDateObj && offeringDate <= endDateObj) {
            expandedResults.push(offering);
          }
        }
      }

      return expandedResults.sort((a, b) => new Date(a.offerDate).getTime() - new Date(b.offerDate).getTime());
    } catch (error) {
      console.error('Error getting hour offerings by date range:', error);
      return [];
    }
  }

  async getHourOfferingById(id: number): Promise<HourOffering | undefined> {
    try {
      const [result] = await db.select().from(hourOfferings).where(eq(hourOfferings.id, id));
      return result;
    } catch (error) {
      console.error('Error getting hour offering by ID:', error);
      return undefined;
    }
  }

  async updateHourOffering(id: number, updates: Partial<HourOffering>): Promise<HourOffering> {
    try {
      const updateData = {
        ...updates,
        // Ensure recurring fields are properly handled
        isRecurring: updates.isRecurring ?? false,
        recurringType: updates.recurringType ?? null,
        recurringCount: updates.recurringCount ?? null,
        recurringEndDate: updates.recurringEndDate ?? null,
        parentOfferingId: updates.parentOfferingId ?? null
      };
      
      const [result] = await db.update(hourOfferings)
        .set(updateData)
        .where(eq(hourOfferings.id, id))
        .returning();
      return result;
    } catch (error) {
      console.error('Error updating hour offering:', error);
      throw error;
    }
  }

  async deleteHourOffering(id: number): Promise<void> {
    try {
      await db.delete(hourOfferings).where(eq(hourOfferings.id, id));
    } catch (error) {
      console.error('Error deleting hour offering:', error);
      throw error;
    }
  }

  async bookHourOffering(id: number): Promise<boolean> {
    try {
      const [result] = await db.update(hourOfferings)
        .set({ isBooked: true })
        .where(and(
          eq(hourOfferings.id, id),
          eq(hourOfferings.isBooked, false)
        ))
        .returning();
      
      return !!result;
    } catch (error) {
      console.error('Error booking hour offering:', error);
      return false;
    }
  }

  async getUserSpreadsheetData(userId: number): Promise<{
    myItems: any[];
    borrowedItems: any[];
    wishListItems: any[];
    offeredHours: any[];
  }> {
    try {
      console.log(`Fetching comprehensive spreadsheet data for user ${userId}`);

      // Get My Items with loan status (exclude request items - those go in Wish List)
      // Must match dashboard logic: status = true AND isRequest = false
      const myItems = await db.select()
      .from(lendableItems)
      .leftJoin(lendableItemCategories, eq(lendableItems.category, lendableItemCategories.id))
      .where(and(
        eq(lendableItems.owner, userId),
        eq(lendableItems.status, true),
        eq(lendableItems.isRequest, false)
      ))
      .orderBy(desc(lendableItems.addedOn));

      // Process my items with loan status
      const myItemsWithStatus = await Promise.all(myItems.map(async (row) => {
        const item = row.lendable_items;
        const category = row.lendable_item_categories;
        const loanStatus = await this.getItemLoanStatus(item.id);
        
        return {
          id: item.id,
          name: item.name,
          description: item.description,
          categoryName: category?.category || 'General',
          status: item.status,
          addedOn: item.addedOn,
          isRequest: item.isRequest,
          primaryLogo: item.primaryLogo,
          pricePerDay: item.pricePerDay,
          deposit: item.deposit,
          isOnLoan: !!loanStatus?.loanId,
          isOverdue: loanStatus?.isOverdue || false,
          borrowerName: loanStatus?.borrowerName,
          expectedReturnDate: loanStatus?.expectedReturnDate,
          borrowedDays: loanStatus?.borrowedDays
        };
      }));

      // Get Items I've Borrowed (current status only - no archive items)
      const borrowedItemsResult = await db.select()
      .from(loanTransactions)
      .innerJoin(lendableItems, eq(loanTransactions.itemId, lendableItems.id))
      .leftJoin(lendableItemCategories, eq(lendableItems.category, lendableItemCategories.id))
      .leftJoin(users, eq(loanTransactions.ownerId, users.id))
      .where(and(
        eq(loanTransactions.borrowerId, userId),
        or(
          eq(loanTransactions.status, 'pending'),
          eq(loanTransactions.status, 'approved'),
          eq(loanTransactions.status, 'active'),
          eq(loanTransactions.status, 'overdue'),
          eq(loanTransactions.status, 'return_requested')
        )
      ))
      .orderBy(desc(loanTransactions.createdAt));

      console.log(`Found ${borrowedItemsResult.length} borrowed items for user ${userId}`);
      if (borrowedItemsResult.length > 0) {
        console.log('Borrowed items statuses:', borrowedItemsResult.map(row => ({ 
          id: row.loan_transactions.id, 
          itemName: row.lendable_items.name,
          status: row.loan_transactions.status 
        })));
      }

      const borrowedItems = borrowedItemsResult.map(row => {
        try {
          const today = new Date();
          const borrowedDate = new Date(row.loan_transactions.borrowedDate);
          const expectedReturnDate = new Date(row.loan_transactions.expectedReturnDate);
        
        // Process current status items only
        let displayStatus = row.loan_transactions.status;
        
        if (row.loan_transactions.status === 'pending') {
          displayStatus = 'Approval Pending';
        } else if (row.loan_transactions.status === 'approved' && borrowedDate > today) {
          displayStatus = 'Reserved';
        } else if (row.loan_transactions.status === 'approved' && expectedReturnDate < today) {
          displayStatus = 'Overdue';
        } else if (row.loan_transactions.status === 'approved') {
          displayStatus = 'Borrowed';
        } else if (row.loan_transactions.status === 'active') {
          displayStatus = 'Currently Borrowed';
        } else if (row.loan_transactions.status === 'overdue') {
          displayStatus = 'Overdue';
        } else if (row.loan_transactions.status === 'return_requested') {
          displayStatus = 'Return Requested';
        } else {
          displayStatus = row.loan_transactions.status;
        }

        return {
          id: row.lendable_items.id,
          name: row.lendable_items.name,
          description: row.lendable_items.description,
          categoryName: row.lendable_item_categories?.category || 'General',
          status: row.loan_transactions.status,
          displayStatus: displayStatus,
          borrowedDate: row.loan_transactions.borrowedDate,
          expectedReturnDate: row.loan_transactions.expectedReturnDate,
          ownerName: row.users?.showName || row.users?.firstName || 'Unknown',
          borrowedDays: row.loan_transactions.expectedReturnDate && row.loan_transactions.borrowedDate 
            ? Math.ceil((expectedReturnDate.getTime() - borrowedDate.getTime()) / (1000 * 60 * 60 * 24))
            : 0,
          isCurrent: ['pending', 'approved', 'active', 'overdue', 'return_requested'].includes(row.loan_transactions.status)
        };
        } catch (error) {
          console.error(`Error processing borrowed item ${row.loan_transactions.id}:`, error);
          return null;
        }
      }).filter(item => item !== null);

      console.log(`Processed ${borrowedItems.length} borrowed items after mapping`);
      console.log('Processed borrowed items:', borrowedItems.map(item => ({ 
        id: item.id, 
        name: item.name, 
        status: item.status,
        displayStatus: item.displayStatus,
        isCurrent: item.isCurrent 
      })));

      // Get Wish List Items (items marked as isRequest by the user)
      const wishListResult = await db.select()
      .from(lendableItems)
      .leftJoin(lendableItemCategories, eq(lendableItems.category, lendableItemCategories.id))
      .where(and(
        eq(lendableItems.owner, userId),
        eq(lendableItems.isRequest, true)
      ))
      .orderBy(desc(lendableItems.addedOn));

      const wishListItems = wishListResult.map(row => ({
        id: row.lendable_items.id,
        name: row.lendable_items.name,
        description: row.lendable_items.description,
        categoryName: row.lendable_item_categories?.category || 'General',
        addedOn: row.lendable_items.addedOn
      }));

      // Get Offered Hours (only future offerings - filter out past due)
      const today = new Date();
      today.setHours(0, 0, 0, 0); // Set to start of today
      
      const offeredHours = await db.select()
      .from(hourOfferings)
      .where(and(
        eq(hourOfferings.userId, userId),
        sql`DATE(${hourOfferings.offerDate}) >= DATE(${today.toISOString()})`
      ))
      .orderBy(desc(hourOfferings.offerDate));

      // Get Inactive Items (owned by user but status = false, not request items)
      const inactiveItemsResult = await db.select()
      .from(lendableItems)
      .leftJoin(lendableItemCategories, eq(lendableItems.category, lendableItemCategories.id))
      .where(and(
        eq(lendableItems.owner, userId),
        eq(lendableItems.status, false),
        eq(lendableItems.isRequest, false)
      ))
      .orderBy(desc(lendableItems.addedOn));

      const inactiveItems = inactiveItemsResult.map(row => ({
        id: row.lendable_items.id,
        name: row.lendable_items.name,
        description: row.lendable_items.description,
        categoryName: row.lendable_item_categories?.category || 'General',
        addedOn: row.lendable_items.addedOn,
        primaryLogo: row.lendable_items.primaryLogo,
        pricePerDay: row.lendable_items.pricePerDay,
        deposit: row.lendable_items.deposit
      }));

      console.log(`Spreadsheet data fetched - My Items: ${myItemsWithStatus.length}, Borrowed: ${borrowedItems.length}, Wish List: ${wishListItems.length}, Hours: ${offeredHours.length}, Inactive: ${inactiveItems.length}`);
      console.log('Final borrowedItems data being returned:', borrowedItems.map(item => ({ 
        id: item.id, 
        name: item.name, 
        displayStatus: item.displayStatus,
        isCurrent: item.isCurrent 
      })));

      return {
        myItems: myItemsWithStatus,
        borrowedItems: borrowedItems,
        wishListItems,
        offeredHours,
        inactiveItems
      };

    } catch (error) {
      console.error('Error fetching user spreadsheet data:', error);
      return {
        myItems: [],
        borrowedItems: [],
        wishListItems: [],
        offeredHours: [],
        inactiveItems: []
      };
    }
  }

  async getUserLoanHistoryData(userId: number): Promise<{
    loanedItems: any[];
    borrowedItems: any[];
  }> {
    try {
      console.log(`Fetching comprehensive loan history data for user ${userId}`);

      // Get Items I've Loaned to Others (historical data only - returned/completed loans)
      const loanedItemsResult = await db.select()
      .from(loanTransactions)
      .innerJoin(lendableItems, eq(loanTransactions.itemId, lendableItems.id))
      .leftJoin(lendableItemCategories, eq(lendableItems.category, lendableItemCategories.id))
      .leftJoin(users, eq(loanTransactions.borrowerId, users.id))
      .where(and(
        eq(loanTransactions.ownerId, userId),
        eq(loanTransactions.status, 'returned')
      ))
      .orderBy(desc(loanTransactions.returnedAt));

      const loanedItems = loanedItemsResult.map(row => {
        // Handle null borrowed dates
        const borrowedDate = row.loan_transactions.borrowedDate ? new Date(row.loan_transactions.borrowedDate) : null;
        const returnedDate = new Date(row.loan_transactions.returnedAt);
        
        // Calculate days only if both dates exist
        let borrowedDays = 1; // Default to 1 day minimum
        if (borrowedDate) {
          borrowedDays = Math.ceil((returnedDate.getTime() - borrowedDate.getTime()) / (1000 * 60 * 60 * 24));
          borrowedDays = Math.max(1, borrowedDays); // Ensure minimum 1 day
        }

        // Only include primaryLogo if it exists and is not a placeholder/default
        const primaryLogo = row.lendable_items.primaryLogo;
        const hasRealImage = primaryLogo && 
          primaryLogo !== 'default-placeholder.jpg' && 
          !primaryLogo.includes('placeholder');

        return {
          id: row.lendable_items.id,
          name: row.lendable_items.name,
          description: row.lendable_items.description,
          categoryName: row.lendable_item_categories?.category || 'General',
          status: 'returned',
          displayStatus: 'Returned',
          borrowedDate: borrowedDate ? borrowedDate.toISOString().split('T')[0] : null,
          statusDate: returnedDate.toISOString().split('T')[0], // Date when status changed
          ownerName: row.users?.showName || row.users?.firstName || 'Unknown',
          ownerAvatar: row.users?.avatar,
          borrowedDays: borrowedDays,
          primaryLogo: hasRealImage ? primaryLogo : null
        };
      });

      // Get Items I've Borrowed (historical data - returned/completed loans AND declined/rejected requests) 
      const borrowedItemsResult = await db.select()
      .from(loanTransactions)
      .innerJoin(lendableItems, eq(loanTransactions.itemId, lendableItems.id))
      .leftJoin(lendableItemCategories, eq(lendableItems.category, lendableItemCategories.id))
      .leftJoin(users, eq(loanTransactions.ownerId, users.id))
      .where(and(
        eq(loanTransactions.borrowerId, userId),
        or(
          eq(loanTransactions.status, 'returned'),
          eq(loanTransactions.status, 'rejected'),
          eq(loanTransactions.status, 'denied'),
          eq(loanTransactions.status, 'dismissed'),
          eq(loanTransactions.status, 'expired')
        )
      ))
      .orderBy(desc(loanTransactions.createdAt));

      const borrowedItems = borrowedItemsResult.map(row => {
        // Handle different status types with proper date processing
        const status = row.loan_transactions.status;
        let displayStatus, statusDate;
        
        if (status === 'returned') {
          displayStatus = 'Returned';
          statusDate = new Date(row.loan_transactions.returnedAt);
        } else if (status === 'rejected' || status === 'denied') {
          displayStatus = 'Declined';
          statusDate = new Date(row.loan_transactions.createdAt);
        } else if (status === 'dismissed') {
          displayStatus = 'Dismissed';
          statusDate = new Date(row.loan_transactions.createdAt);
        } else if (status === 'expired') {
          displayStatus = 'Expired';
          statusDate = new Date(row.loan_transactions.createdAt);
        } else {
          displayStatus = status;
          statusDate = new Date(row.loan_transactions.createdAt);
        }
        
        // Handle borrowed dates and calculate days for returned items
        const borrowedDate = row.loan_transactions.borrowedDate ? new Date(row.loan_transactions.borrowedDate) : null;
        let borrowedDays = 0;
        
        if (status === 'returned' && borrowedDate) {
          borrowedDays = Math.ceil((statusDate.getTime() - borrowedDate.getTime()) / (1000 * 60 * 60 * 24));
          borrowedDays = Math.max(1, borrowedDays); // Ensure minimum 1 day
        }

        // Only include primaryLogo if it exists and is not a placeholder/default
        const primaryLogo = row.lendable_items.primaryLogo;
        const hasRealImage = primaryLogo && 
          primaryLogo !== 'default-placeholder.jpg' && 
          !primaryLogo.includes('placeholder');

        return {
          id: row.lendable_items.id,
          name: row.lendable_items.name,
          description: row.lendable_items.description,
          categoryName: row.lendable_item_categories?.category || 'General',
          status: status,
          displayStatus: displayStatus,
          borrowedDate: borrowedDate ? borrowedDate.toISOString().split('T')[0] : null,
          statusDate: statusDate.toISOString().split('T')[0], // Date when status changed
          ownerName: row.users?.showName || row.users?.firstName || 'Unknown',
          ownerAvatar: row.users?.avatar,
          borrowedDays: borrowedDays,
          primaryLogo: hasRealImage ? primaryLogo : null
        };
      });

      console.log(`Loan history data fetched - Loaned: ${loanedItems.length}, Borrowed: ${borrowedItems.length}`);

      return {
        loanedItems,
        borrowedItems
      };

    } catch (error) {
      console.error('Error fetching user loan history data:', error);
      return {
        loanedItems: [],
        borrowedItems: []
      };
    }
  }

  // Email notification system implementations
  async getEmailNotificationSettings(): Promise<any> {
    try {
      const [settings] = await db.select().from(emailNotificationSettings).limit(1);
      if (!settings) {
        // Create default settings if none exist
        const [newSettings] = await db.insert(emailNotificationSettings).values({}).returning();
        return newSettings;
      }
      return settings;
    } catch (error) {
      console.error('Error fetching email notification settings:', error);
      return null;
    }
  }

  async updateEmailNotificationSettings(updates: any): Promise<void> {
    try {
      const [existing] = await db.select().from(emailNotificationSettings).limit(1);
      if (existing) {
        await db.update(emailNotificationSettings)
          .set({ ...updates, updatedAt: new Date() })
          .where(eq(emailNotificationSettings.id, existing.id));
      } else {
        await db.insert(emailNotificationSettings).values(updates);
      }
    } catch (error) {
      console.error('Error updating email notification settings:', error);
    }
  }

  async createEmailLog(logData: any): Promise<any> {
    try {
      const [log] = await db.insert(emailNotificationLog).values(logData).returning();
      return log;
    } catch (error) {
      console.error('Error creating email log:', error);
      return null;
    }
  }

  async getEmailLogs(limit: number = 50): Promise<any[]> {
    try {
      const logs = await db.select()
        .from(emailNotificationLog)
        .orderBy(desc(emailNotificationLog.sentAt))
        .limit(limit);
      return logs;
    } catch (error) {
      console.error('Error fetching email logs:', error);
      return [];
    }
  }

  async createEmailDigestQueue(queueData: any): Promise<any> {
    try {
      const [queue] = await db.insert(emailDigestQueue).values(queueData).returning();
      return queue;
    } catch (error) {
      console.error('Error creating email digest queue:', error);
      return null;
    }
  }

  async getEmailDigestStats(date: string): Promise<{ sent: number; failed: number; skipped: number }> {
    try {
      const result = await db
        .select({
          status: emailDigestQueue.status,
          count: sql<number>`COUNT(*)`
        })
        .from(emailDigestQueue)
        .where(sql`DATE(${emailDigestQueue.processedAt}) = ${date}`)
        .groupBy(emailDigestQueue.status);

      const stats = { sent: 0, failed: 0, skipped: 0 };
      result.forEach(row => {
        if (row.status === 'sent') stats.sent = Number(row.count);
        if (row.status === 'failed') stats.failed = Number(row.count);
        if (row.status === 'skipped') stats.skipped = Number(row.count);
      });

      return stats;
    } catch (error) {
      console.error('Error fetching email digest stats:', error);
      return { sent: 0, failed: 0, skipped: 0 };
    }
  }

  async getRecentMessagesForUser(userId: number, since: Date): Promise<any[]> {
    try {
      const result = await pool.query(`
        SELECT 
          cm.id,
          cm.text_content,
          cm.created_at,
          COALESCE(sender.show_name, sender.first_name || ' ' || sender.last_name, sender.email) as sender_name,
          li.name as item_name
        FROM chat_messages cm
        JOIN conversations c ON cm.conversation_id = c.id
        JOIN users sender ON cm.sender_id = sender.id
        JOIN lendable_items li ON c.item_id = li.id
        WHERE (c.owner_id = $1 OR c.borrower_id = $1)
          AND cm.sender_id != $1
          AND cm.created_at >= $2
          AND cm.is_active = true
        ORDER BY cm.created_at DESC
      `, [userId, since]);
      
      return result.rows;
    } catch (error) {
      console.error('Error fetching recent messages for user:', error);
      return [];
    }
  }

  async getRecentNotificationsForUser(userId: number, since: Date): Promise<any[]> {
    try {
      const result = await pool.query(`
        SELECT 
          id,
          type,
          title,
          message,
          created_at
        FROM notifications
        WHERE user_id = $1
          AND created_at >= $2
          AND is_dismissed = false
        ORDER BY created_at DESC
      `, [userId, since]);
      
      return result.rows;
    } catch (error) {
      console.error('Error fetching recent notifications for user:', error);
      return [];
    }
  }



  async getAllItems(): Promise<any[]> {
    try {
      const result = await pool.query(`
        SELECT id, name, description, owner, status, is_request
        FROM lendable_items
        ORDER BY name
      `);
      return result.rows;
    } catch (error) {
      console.error('Error fetching all items:', error);
      return [];
    }
  }

  async getAllHourOfferings(): Promise<any[]> {
    try {
      const result = await pool.query(`
        SELECT id, title, description, user_id as "userId", is_active
        FROM hour_offerings
        ORDER BY title
      `);
      return result.rows;
    } catch (error) {
      console.error('Error fetching all hour offerings:', error);
      return [];
    }
  }



  async deleteHourOffering(offeringId: number): Promise<void> {
    try {
      await pool.query('DELETE FROM hour_offerings WHERE id = $1', [offeringId]);
    } catch (error) {
      console.error('Error deleting hour offering:', error);
      throw error;
    }
  }

  async getHourOffering(offeringId: number): Promise<any | null> {
    try {
      const result = await pool.query(`
        SELECT id, title, description, user_id as "userId", offer_date, end_time
        FROM hour_offerings
        WHERE id = $1
      `, [offeringId]);
      
      return result.rows[0] || null;
    } catch (error) {
      console.error('Error fetching hour offering:', error);
      return null;
    }
  }

  async createLoanTransaction(data: any): Promise<number> {
    try {
      const result = await pool.query(`
        INSERT INTO loan_transactions (
          item_id, borrower_id, owner_id, requested_date, 
          expected_return_date, status, created_at
        )
        VALUES ($1, $2, $3, $4, $5, $6, NOW())
        RETURNING id
      `, [
        data.itemId,
        data.borrowerId,
        data.ownerId,
        data.requestedDate,
        data.expectedReturnDate,
        data.status || 'pending'
      ]);
      
      return result.rows[0].id;
    } catch (error) {
      console.error('Error creating loan transaction:', error);
      throw error;
    }
  }
}

export const storage = new DatabaseStorage();