import { type User, type InsertUser, type Property, type InsertProperty, type Message, type InsertMessage, type MovingService, type InsertMovingService, type Booking, type InsertBooking, type AdminUser, type InsertAdminUser } from "@shared/schema";
import { randomUUID } from "crypto";

export interface IStorage {
  // User operations
  getUser(id: string): Promise<User | undefined>;
  getUserByEmail(email: string): Promise<User | undefined>;
  createUser(user: InsertUser): Promise<User>;
  updateUser(id: string, updates: Partial<User>): Promise<User | undefined>;
  updateUserRole(userId: string, role: string, profileCompleted: boolean): Promise<User | undefined>;
  
  // Property operations
  getProperty(id: string): Promise<Property | undefined>;
  getProperties(filters?: {
    type?: string;
    propertyType?: string;
    location?: string;
    minPrice?: number;
    maxPrice?: number;
    limit?: number;
    offset?: number;
  }): Promise<Property[]>;
  getPropertiesForMap(): Promise<Array<{
    id: string;
    title: string;
    price: string;
    type: string;
    latitude: string | null;
    longitude: string | null;
    image_url: string | null;
  }>>;
  createProperty(property: InsertProperty): Promise<Property>;
  updateProperty(id: string, updates: Partial<Property>): Promise<Property | undefined>;
  deleteProperty(id: string): Promise<boolean>;
  
  // Message operations
  getMessage(id: string): Promise<Message | undefined>;
  getMessages(userId: string): Promise<Message[]>;
  createMessage(message: InsertMessage): Promise<Message>;
  
  // Moving service operations
  getMovingService(id: string): Promise<MovingService | undefined>;
  getMovingServices(): Promise<MovingService[]>;
  createMovingService(service: InsertMovingService): Promise<MovingService>;
  updateMovingService(id: string, updates: Partial<MovingService>): Promise<MovingService | undefined>;
  deleteMovingService(id: string): Promise<boolean>;
  
  // Booking operations
  getBooking(id: string): Promise<Booking | undefined>;
  getBookings(userId?: string): Promise<Booking[]>;
  createBooking(booking: InsertBooking): Promise<Booking>;
  updateBooking(id: string, updates: Partial<Booking>): Promise<Booking | undefined>;
  
  // Admin operations
  getAdminUser(id: string): Promise<AdminUser | undefined>;
  getAdminUserByUsername(username: string): Promise<AdminUser | undefined>;
  getAdminUserByEmail(email: string): Promise<AdminUser | undefined>;
  createAdminUser(adminUser: InsertAdminUser): Promise<AdminUser>;
  updateAdminUser(id: string, updates: Partial<AdminUser>): Promise<AdminUser | undefined>;
  deleteAdminUser(id: string): Promise<boolean>;
  
  // Admin dashboard analytics
  getDashboardStats(): Promise<{
    totalUsers: number;
    totalProperties: number;
    totalBookings: number;
    totalMovingServices: number;
    recentUsers: User[];
    recentProperties: Property[];
    recentBookings: Booking[];
  }>;
}

export class MemStorage implements IStorage {
  private users: Map<string, User> = new Map();
  private properties: Map<string, Property> = new Map();
  private messages: Map<string, Message> = new Map();
  private movingServices: Map<string, MovingService> = new Map();
  private bookings: Map<string, Booking> = new Map();
  private adminUsers: Map<string, AdminUser> = new Map();

  constructor() {
    this.seedData();
    this.seedAdminUser();
    this.fixLegacyUserRoles();
  }

  private seedData() {
    // Seed moving services
    const movingServicesData = [
      {
        name: "Mogadishu Movers",
        description: "Professional residential and commercial moving services",
        phone: "+252 61 123 4567",
        email: "info@mogadishumovers.so",
        location: "Mogadishu",
        rating: "4.9",
        availability: "24/7",
        imageUrl: "https://images.unsplash.com/photo-1558618666-fcd25c85cd64?ixlib=rb-4.0.3&auto=format&fit=crop&w=400&h=200"
      },
      {
        name: "Swift Transport",
        description: "Fast and reliable local and long-distance moves",
        phone: "+252 61 234 5678",
        email: "contact@swifttransport.so",
        location: "Mogadishu",
        rating: "4.2",
        availability: "Mon-Sat",
        imageUrl: "https://pixabay.com/get/gceb5e6d4d9af08da229d8841cdd0f140b196734b3c6c0ab1ebec300dd2a4b260e61097233644190135d403df91a7a8e61982bfa42058dad3bc8b92b8e2312874_1280.jpg"
      },
      {
        name: "City Express",
        description: "Affordable small moves and furniture delivery",
        phone: "+252 61 345 6789",
        email: "hello@cityexpress.so",
        location: "Mogadishu",
        rating: "4.7",
        availability: "7 days",
        imageUrl: "https://images.unsplash.com/photo-1566576912321-d58ddd7a6088?ixlib=rb-4.0.3&auto=format&fit=crop&w=400&h=200"
      },
      {
        name: "Premium Movers",
        description: "Luxury moving with packing and storage services",
        phone: "+252 61 456 7890",
        email: "service@premiummovers.so",
        location: "Mogadishu",
        rating: "5.0",
        availability: "24/7",
        imageUrl: "https://pixabay.com/get/gf90a1f9de2c15d13d41d047ecac4ba6df0e7b931601fb134788ddd4084d5aeb848cdb5467140b4cb4ae95f1d44e81c16ba0e4d03d7bb0749fe7425a3898ad14d_1280.jpg"
      }
    ];

    movingServicesData.forEach(serviceData => {
      const id = randomUUID();
      const service: MovingService = {
        ...serviceData,
        id,
        createdAt: new Date(),
        updatedAt: new Date()
      };
      this.movingServices.set(id, service);
    });

    // Seed properties
    const propertiesData = [
      {
        title: "Modern 2BR Apartment",
        description: "Beautiful modern apartment with contemporary amenities and great city views.",
        price: "850.00",
        type: "rent",
        propertyType: "apartment",
        location: "Hamar Weyne, Mogadishu",
        latitude: "2.0469",
        longitude: "45.3182",
        bedrooms: 2,
        bathrooms: 2,
        sqft: 950,
        features: ["Air Conditioning", "Balcony", "Modern Kitchen", "Parking"],
        images: ["/api/placeholder/800/600"],
        ownerId: "owner1",
        isActive: true
      },
      {
        title: "Family Villa with Garden",
        description: "Spacious family villa with private garden and traditional architectural elements.",
        price: "125000.00",
        type: "buy",
        propertyType: "house",
        location: "Hodan, Mogadishu",
        latitude: "2.0567",
        longitude: "45.3289",
        bedrooms: 4,
        bathrooms: 3,
        sqft: 2100,
        features: ["Garden", "Garage", "Security", "Traditional Design"],
        images: ["/api/placeholder/800/600"],
        ownerId: "owner2",
        isActive: true
      },
      {
        title: "Beachfront Apartment",
        description: "Stunning beachfront apartment with ocean views and modern amenities.",
        price: "1200.00",
        type: "rent",
        propertyType: "apartment",
        location: "Jazeera Beach, Mogadishu",
        latitude: "2.0401",
        longitude: "45.3440",
        bedrooms: 3,
        bathrooms: 2,
        sqft: 1250,
        features: ["Ocean View", "Beach Access", "Modern Kitchen", "Balcony"],
        images: ["https://images.unsplash.com/photo-1512917774080-9991f1c4c750?ixlib=rb-4.0.3&auto=format&fit=crop&w=800&h=600"],
        ownerId: "owner3",
        isActive: true
      },
      {
        title: "Commercial Office Space",
        description: "Prime commercial office space in the business district with modern facilities.",
        price: "250000.00",
        type: "buy",
        propertyType: "commercial",
        location: "Banadir, Mogadishu",
        latitude: "2.0580",
        longitude: "45.3180",
        bedrooms: 0,
        bathrooms: 2,
        sqft: 3500,
        features: ["Conference Rooms", "Parking", "Security", "Modern Facilities"],
        images: ["https://images.unsplash.com/photo-1560518883-ce09059eeffa?ixlib=rb-4.0.3&auto=format&fit=crop&w=800&h=600"],
        ownerId: "owner4",
        isActive: true
      },
      {
        title: "Affordable 1BR Apartment",
        description: "Comfortable and affordable apartment perfect for young professionals.",
        price: "450.00",
        type: "rent",
        propertyType: "apartment",
        location: "Karaan, Mogadishu",
        latitude: "2.0650",
        longitude: "45.3350",
        bedrooms: 1,
        bathrooms: 1,
        sqft: 650,
        features: ["Furnished", "Utilities Included", "Transport Links"],
        images: ["https://images.unsplash.com/photo-1568605114967-8130f3a36994?ixlib=rb-4.0.3&auto=format&fit=crop&w=800&h=600"],
        ownerId: "owner5",
        isActive: true
      },
      {
        title: "Development Land",
        description: "Prime development land with excellent potential for residential or commercial projects.",
        price: "85000.00",
        type: "buy",
        propertyType: "land",
        location: "Dharkenley, Mogadishu",
        latitude: "2.0720",
        longitude: "45.3420",
        bedrooms: 0,
        bathrooms: 0,
        sqft: 5000,
        features: ["Road Access", "Utilities Available", "Development Potential"],
        images: ["https://images.unsplash.com/photo-1500382017468-9049fed747ef?ixlib=rb-4.0.3&auto=format&fit=crop&w=800&h=600"],
        ownerId: "owner6",
        isActive: true
      }
    ];

    propertiesData.forEach(propertyData => {
      const id = randomUUID();
      const property: Property = {
        ...propertyData,
        id,
        createdAt: new Date(),
        updatedAt: new Date()
      };
      this.properties.set(id, property);
    });
  }

  private seedAdminUser() {
    // Create default admin user: admin / admin123
    const adminUser: AdminUser = {
      id: randomUUID(),
      username: "admin",
      password: "admin123", // In production, this should be hashed
      email: "admin@kiroservices.com",
      name: "System Administrator",
      role: "admin",
      isActive: true,
      lastLogin: null,
      createdAt: new Date(),
      updatedAt: new Date()
    };
    this.adminUsers.set(adminUser.id, adminUser);
  }

  private fixLegacyUserRoles() {
    // Fix any existing users with 'owner' role to 'property_owner'
    const usersArray = Array.from(this.users.entries());
    for (const [userId, user] of usersArray) {
      if (user.role === 'owner') {
        const updatedUser = {
          ...user,
          role: 'property_owner' as const,
          updatedAt: new Date()
        };
        this.users.set(userId, updatedUser);
        console.log(`Fixed legacy role for user ${user.email}: 'owner' -> 'property_owner'`);
      }
    }
  }

  // User operations
  async getUser(id: string): Promise<User | undefined> {
    return this.users.get(id);
  }

  async getUserByEmail(email: string): Promise<User | undefined> {
    return Array.from(this.users.values()).find(user => user.email === email);
  }

  async getAllUsers(): Promise<User[]> {
    return Array.from(this.users.values());
  }

  async createUser(insertUser: InsertUser): Promise<User> {
    const id = randomUUID();
    const user: User = {
      ...insertUser,
      id,
      role: insertUser.role || "buyer",
      phone: insertUser.phone || null,
      profileCompleted: insertUser.profileCompleted || false,
      createdAt: new Date(),
      updatedAt: new Date()
    };
    this.users.set(id, user);
    return user;
  }

  async updateUser(id: string, updates: Partial<User>): Promise<User | undefined> {
    const user = this.users.get(id);
    if (!user) return undefined;
    
    const updatedUser = { ...user, ...updates, updatedAt: new Date() };
    this.users.set(id, updatedUser);
    return updatedUser;
  }

  async updateUserRole(userId: string, role: string, profileCompleted: boolean): Promise<User | undefined> {
    const user = this.users.get(userId);
    if (!user) return undefined;
    
    const updatedUser = { 
      ...user, 
      role, 
      profileCompleted, 
      updatedAt: new Date() 
    };
    this.users.set(userId, updatedUser);
    return updatedUser;
  }

  // Property operations
  async getProperty(id: string): Promise<Property | undefined> {
    return this.properties.get(id);
  }

  async getProperties(filters?: {
    type?: string;
    propertyType?: string;
    location?: string;
    minPrice?: number;
    maxPrice?: number;
    limit?: number;
    offset?: number;
  }): Promise<Property[]> {
    let properties = Array.from(this.properties.values()).filter(p => p.isActive);

    if (filters) {
      if (filters.type) {
        properties = properties.filter(p => p.type === filters.type);
      }
      if (filters.propertyType) {
        properties = properties.filter(p => p.propertyType === filters.propertyType);
      }
      if (filters.location) {
        properties = properties.filter(p => p.location.toLowerCase().includes(filters.location!.toLowerCase()));
      }
      if (filters.minPrice !== undefined) {
        properties = properties.filter(p => parseFloat(p.price) >= filters.minPrice!);
      }
      if (filters.maxPrice !== undefined) {
        properties = properties.filter(p => parseFloat(p.price) <= filters.maxPrice!);
      }

      // Apply pagination
      const offset = filters.offset || 0;
      const limit = filters.limit || 50;
      properties = properties.slice(offset, offset + limit);
    }

    return properties.sort((a, b) => b.createdAt!.getTime() - a.createdAt!.getTime());
  }

  async createProperty(insertProperty: InsertProperty): Promise<Property> {
    const id = randomUUID();
    const property: Property = {
      ...insertProperty,
      id,
      latitude: insertProperty.latitude || null,
      longitude: insertProperty.longitude || null,
      bedrooms: insertProperty.bedrooms || null,
      bathrooms: insertProperty.bathrooms || null,
      sqft: insertProperty.sqft || null,
      features: insertProperty.features || [],
      images: insertProperty.images || [],
      isActive: insertProperty.isActive ?? true,
      createdAt: new Date(),
      updatedAt: new Date()
    };
    this.properties.set(id, property);
    return property;
  }

  async updateProperty(id: string, updates: Partial<Property>): Promise<Property | undefined> {
    const property = this.properties.get(id);
    if (!property) return undefined;
    
    const updatedProperty = { ...property, ...updates, updatedAt: new Date() };
    this.properties.set(id, updatedProperty);
    return updatedProperty;
  }

  async deleteProperty(id: string): Promise<boolean> {
    return this.properties.delete(id);
  }

  async getPropertiesForMap(): Promise<Array<{
    id: string;
    title: string;
    price: string;
    type: string;
    latitude: string | null;
    longitude: string | null;
    image_url: string | null;
  }>> {
    const properties = Array.from(this.properties.values()).filter(p => p.isActive && p.latitude && p.longitude);
    
    return properties.map(property => ({
      id: property.id,
      title: property.title,
      price: property.price,
      type: property.type,
      latitude: property.latitude,
      longitude: property.longitude,
      image_url: property.images && property.images.length > 0 ? property.images[0] : null
    }));
  }

  // Message operations
  async getMessage(id: string): Promise<Message | undefined> {
    return this.messages.get(id);
  }

  async getMessages(userId: string): Promise<Message[]> {
    return Array.from(this.messages.values())
      .filter(m => m.senderId === userId || m.receiverId === userId)
      .sort((a, b) => b.createdAt!.getTime() - a.createdAt!.getTime());
  }

  async createMessage(insertMessage: InsertMessage): Promise<Message> {
    const id = randomUUID();
    const message: Message = {
      ...insertMessage,
      id,
      propertyId: insertMessage.propertyId || null,
      createdAt: new Date()
    };
    this.messages.set(id, message);
    return message;
  }

  // Moving service operations
  async getMovingService(id: string): Promise<MovingService | undefined> {
    return this.movingServices.get(id);
  }

  async getMovingServices(): Promise<MovingService[]> {
    return Array.from(this.movingServices.values())
      .sort((a, b) => parseFloat(b.rating || "0") - parseFloat(a.rating || "0"));
  }

  async createMovingService(insertService: InsertMovingService): Promise<MovingService> {
    const id = randomUUID();
    const service: MovingService = {
      ...insertService,
      id,
      rating: insertService.rating || null,
      imageUrl: insertService.imageUrl || null,
      createdAt: new Date(),
      updatedAt: new Date()
    };
    this.movingServices.set(id, service);
    return service;
  }

  async updateMovingService(id: string, updates: Partial<MovingService>): Promise<MovingService | undefined> {
    const service = this.movingServices.get(id);
    if (!service) return undefined;
    
    const updatedService = { ...service, ...updates, updatedAt: new Date() };
    this.movingServices.set(id, updatedService);
    return updatedService;
  }

  async deleteMovingService(id: string): Promise<boolean> {
    return this.movingServices.delete(id);
  }

  // Booking operations
  async getBooking(id: string): Promise<Booking | undefined> {
    return this.bookings.get(id);
  }

  async getBookings(userId?: string): Promise<Booking[]> {
    let bookings = Array.from(this.bookings.values());
    
    if (userId) {
      bookings = bookings.filter(b => b.userId === userId);
    }
    
    return bookings.sort((a, b) => b.createdAt!.getTime() - a.createdAt!.getTime());
  }

  async createBooking(insertBooking: InsertBooking): Promise<Booking> {
    const id = randomUUID();
    const booking: Booking = {
      ...insertBooking,
      id,
      status: insertBooking.status || "pending",
      notes: insertBooking.notes || null,
      createdAt: new Date(),
      updatedAt: new Date()
    };
    this.bookings.set(id, booking);
    return booking;
  }

  async updateBooking(id: string, updates: Partial<Booking>): Promise<Booking | undefined> {
    const booking = this.bookings.get(id);
    if (!booking) return undefined;
    
    const updatedBooking = { ...booking, ...updates, updatedAt: new Date() };
    this.bookings.set(id, updatedBooking);
    return updatedBooking;
  }

  // Admin operations
  async getAdminUser(id: string): Promise<AdminUser | undefined> {
    return this.adminUsers.get(id);
  }

  async getAdminUserByUsername(username: string): Promise<AdminUser | undefined> {
    return Array.from(this.adminUsers.values()).find(admin => admin.username === username);
  }

  async getAdminUserByEmail(email: string): Promise<AdminUser | undefined> {
    return Array.from(this.adminUsers.values()).find(admin => admin.email === email);
  }

  async createAdminUser(insertAdminUser: InsertAdminUser): Promise<AdminUser> {
    const id = randomUUID();
    const adminUser: AdminUser = {
      ...insertAdminUser,
      id,
      role: insertAdminUser.role || "admin",
      isActive: insertAdminUser.isActive ?? true,
      lastLogin: null,
      createdAt: new Date(),
      updatedAt: new Date()
    };
    this.adminUsers.set(id, adminUser);
    return adminUser;
  }

  async updateAdminUser(id: string, updates: Partial<AdminUser>): Promise<AdminUser | undefined> {
    const adminUser = this.adminUsers.get(id);
    if (!adminUser) return undefined;
    
    const updatedAdminUser = { ...adminUser, ...updates, updatedAt: new Date() };
    this.adminUsers.set(id, updatedAdminUser);
    return updatedAdminUser;
  }

  async deleteAdminUser(id: string): Promise<boolean> {
    return this.adminUsers.delete(id);
  }

  async getDashboardStats(): Promise<{
    totalUsers: number;
    totalProperties: number;
    totalBookings: number;
    totalMovingServices: number;
    recentUsers: User[];
    recentProperties: Property[];
    recentBookings: Booking[];
  }> {
    const users = Array.from(this.users.values());
    const properties = Array.from(this.properties.values());
    const bookings = Array.from(this.bookings.values());
    const movingServices = Array.from(this.movingServices.values());

    return {
      totalUsers: users.length,
      totalProperties: properties.length,
      totalBookings: bookings.length,
      totalMovingServices: movingServices.length,
      recentUsers: users.sort((a, b) => b.createdAt!.getTime() - a.createdAt!.getTime()).slice(0, 5),
      recentProperties: properties.sort((a, b) => b.createdAt!.getTime() - a.createdAt!.getTime()).slice(0, 5),
      recentBookings: bookings.sort((a, b) => b.createdAt!.getTime() - a.createdAt!.getTime()).slice(0, 5)
    };
  }
}

import { db } from "./db";
import { eq, sql, desc, and } from "drizzle-orm";
import { users, properties, movingServices, bookings, adminUsers, messages } from "@shared/schema";

// rewrite MemStorage to DatabaseStorage
export class DatabaseStorage implements IStorage {
  constructor() {
    // Initialize with seeding data if needed
    this.seedDataIfEmpty();
  }

  private async seedDataIfEmpty() {
    try {
      // Check if we have any properties - if not, seed the data
      const existingProperties = await db.select().from(properties).limit(1);
      if (existingProperties.length === 0) {
        await this.seedProperties();
      }

      // Check if we have admin user - if not, create default admin
      const existingAdmin = await db.select().from(adminUsers).limit(1);
      if (existingAdmin.length === 0) {
        await this.seedAdminUser();
      }

      // Check if we have moving services - if not, seed them
      const existingServices = await db.select().from(movingServices).limit(1);
      if (existingServices.length === 0) {
        await this.seedMovingServices();
      }
    } catch (error) {
      console.error('Error seeding data:', error);
    }
  }

  async getUser(id: string): Promise<User | undefined> {
    const [user] = await db.select().from(users).where(eq(users.id, id));
    return user || undefined;
  }

  async getUserByEmail(email: string): Promise<User | undefined> {
    const [user] = await db.select().from(users).where(eq(users.email, email));
    return user || undefined;
  }

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

  async updateUser(id: string, updates: Partial<User>): Promise<User | undefined> {
    const [user] = await db
      .update(users)
      .set({ ...updates, updatedAt: new Date() })
      .where(eq(users.id, id))
      .returning();
    return user || undefined;
  }

  async updateUserRole(id: string, role: string, profileCompleted: boolean = true): Promise<User | undefined> {
    const [user] = await db
      .update(users)
      .set({ role, profileCompleted, updatedAt: new Date() })
      .where(eq(users.id, id))
      .returning();
    return user || undefined;
  }

  async updateUserStatus(id: string, isActive: boolean): Promise<User | undefined> {
    const [user] = await db
      .update(users)
      .set({ isActive, updatedAt: new Date() })
      .where(eq(users.id, id))
      .returning();
    return user || undefined;
  }

  async getPropertiesForMap(): Promise<Array<{id: string, title: string, price: string, type: string, latitude: string | null, longitude: string | null, image_url: string | null}>> {
    const mapProperties = await db
      .select({
        id: properties.id,
        title: properties.title,
        latitude: properties.latitude,
        longitude: properties.longitude,
        price: properties.price,
        type: properties.type,
        image_url: properties.images
      })
      .from(properties)
      .where(eq(properties.isActive, true));
    
    return mapProperties;
  }

  async getActiveUserPropertiesForMap(): Promise<Array<{id: string, title: string, price: string, type: string, latitude: string | null, longitude: string | null, image_url: string | null}>> {
    const mapProperties = await db
      .select({
        id: properties.id,
        title: properties.title,
        latitude: properties.latitude,
        longitude: properties.longitude,
        price: properties.price,
        type: properties.type,
        image_url: properties.images
      })
      .from(properties)
      .innerJoin(users, eq(properties.ownerId, users.id))
      .where(and(eq(properties.isActive, true), eq(users.isActive, true)));
    
    return mapProperties;
  }

  async getActiveUserProperties(): Promise<Property[]> {
    const result = await db
      .select({
        properties: properties
      })
      .from(properties)
      .innerJoin(users, eq(properties.ownerId, users.id))
      .where(and(eq(properties.isActive, true), eq(users.isActive, true)));
    
    return result.map(r => r.properties);
  }

  async getActiveMovingServices(): Promise<MovingService[]> {
    return await db
      .select()
      .from(movingServices)
      .where(eq(movingServices.isActive, true));
  }

  async updateProperty(id: string, updates: Partial<Property>): Promise<Property | undefined> {
    // Remove timestamp fields from updates to prevent conflicts
    const { createdAt, updatedAt, ...cleanUpdates } = updates;
    
    const [property] = await db
      .update(properties)
      .set({ ...cleanUpdates, updatedAt: new Date() })
      .where(eq(properties.id, id))
      .returning();
    return property || undefined;
  }

  async createMessage(message: InsertMessage): Promise<Message> {
    const [newMessage] = await db
      .insert(messages)
      .values(message)
      .returning();
    return newMessage;
  }

  async getMessages(): Promise<Message[]> {
    return await db.select().from(messages);
  }

  async getMessage(id: string): Promise<Message | undefined> {
    const [message] = await db.select().from(messages).where(eq(messages.id, id));
    return message || undefined;
  }

  async getAllUsers(): Promise<User[]> {
    return await db.select().from(users);
  }

  async getDashboardStats(): Promise<any> {
    const [userCount] = await db.select({ count: sql<number>`count(*)` }).from(users);
    const [propertyCount] = await db.select({ count: sql<number>`count(*)` }).from(properties);
    const [bookingCount] = await db.select({ count: sql<number>`count(*)` }).from(bookings);
    const [serviceCount] = await db.select({ count: sql<number>`count(*)` }).from(movingServices);

    const recentUsers = await db
      .select()
      .from(users)
      .orderBy(desc(users.createdAt))
      .limit(5);

    const recentProperties = await db
      .select()
      .from(properties)
      .orderBy(desc(properties.createdAt))
      .limit(5);

    const recentBookings = await db
      .select()
      .from(bookings)
      .orderBy(desc(bookings.createdAt))
      .limit(5);

    return {
      totalUsers: userCount.count || 0,
      totalProperties: propertyCount.count || 0,
      totalBookings: bookingCount.count || 0,
      totalMovingServices: serviceCount.count || 0,
      recentUsers: recentUsers || [],
      recentProperties: recentProperties || [],
      recentBookings: recentBookings || []
    };
  }

  async getProperties(): Promise<Property[]> {
    return await db.select().from(properties).where(eq(properties.isActive, true));
  }

  async getProperty(id: string): Promise<Property | undefined> {
    const [property] = await db.select().from(properties).where(eq(properties.id, id));
    return property || undefined;
  }

  async createProperty(insertProperty: InsertProperty): Promise<Property> {
    const [property] = await db
      .insert(properties)
      .values(insertProperty)
      .returning();
    return property;
  }

  async deleteProperty(id: string): Promise<boolean> {
    const result = await db.delete(properties).where(eq(properties.id, id));
    return (result.rowCount || 0) > 0;
  }

  async getMovingServices(): Promise<MovingService[]> {
    return await db.select().from(movingServices);
  }

  async createMovingService(service: InsertMovingService): Promise<MovingService> {
    const [newService] = await db
      .insert(movingServices)
      .values(service)
      .returning();
    return newService;
  }

  async deleteMovingService(id: string): Promise<boolean> {
    const result = await db.delete(movingServices).where(eq(movingServices.id, id));
    return (result.rowCount || 0) > 0;
  }

  async getBookings(): Promise<Booking[]> {
    return await db.select().from(bookings);
  }

  async createBooking(booking: InsertBooking): Promise<Booking> {
    const [newBooking] = await db
      .insert(bookings)
      .values(booking)
      .returning();
    return newBooking;
  }

  async updateBooking(id: string, updates: { status: string }): Promise<Booking | undefined> {
    const [booking] = await db
      .update(bookings)
      .set({ ...updates, updatedAt: new Date() })
      .where(eq(bookings.id, id))
      .returning();
    return booking || undefined;
  }

  async getAdminUsers(): Promise<AdminUser[]> {
    return await db.select().from(adminUsers);
  }

  async getAdminUserByEmail(email: string): Promise<AdminUser | undefined> {
    const [admin] = await db.select().from(adminUsers).where(eq(adminUsers.email, email));
    return admin || undefined;
  }

  async getAdminUserByUsername(username: string): Promise<AdminUser | undefined> {
    const [admin] = await db.select().from(adminUsers).where(eq(adminUsers.username, username));
    return admin || undefined;
  }

  async updateAdminUser(id: string, updates: Partial<AdminUser>): Promise<AdminUser | undefined> {
    const [admin] = await db
      .update(adminUsers)
      .set({ ...updates, updatedAt: new Date() })
      .where(eq(adminUsers.id, id))
      .returning();
    return admin || undefined;
  }

  async updateUserStatus(id: string, isActive: boolean): Promise<User | undefined> {
    // For users table, we'll use a custom field to track active status
    const [user] = await db
      .update(users)
      .set({ profileCompleted: isActive, updatedAt: new Date() })
      .where(eq(users.id, id))
      .returning();
    return user || undefined;
  }

  async deleteUser(id: string): Promise<boolean> {
    const result = await db.delete(users).where(eq(users.id, id));
    return (result.rowCount || 0) > 0;
  }

  async updateMovingService(id: string, updates: Partial<any>): Promise<any | undefined> {
    const [service] = await db
      .update(movingServices)
      .set({ ...updates, updatedAt: new Date() })
      .where(eq(movingServices.id, id))
      .returning();
    return service || undefined;
  }

  async deleteBooking(id: string): Promise<boolean> {
    const result = await db.delete(bookings).where(eq(bookings.id, id));
    return (result.rowCount || 0) > 0;
  }

  async getBooking(id: string): Promise<any | undefined> {
    const [booking] = await db.select().from(bookings).where(eq(bookings.id, id));
    return booking || undefined;
  }

  private async seedProperties() {
    const propertiesData = [
      {
        title: "Modern 2BR Apartment",
        description: "Beautiful modern apartment with stunning city views and contemporary amenities.",
        price: "180000.00",
        type: "buy",
        propertyType: "apartment",
        location: "Hamar Weyne, Mogadishu",
        latitude: "2.0469",
        longitude: "45.3182",
        bedrooms: 2,
        bathrooms: 2,
        sqft: 1200,
        features: ["City Views", "Modern Kitchen", "Air Conditioning", "Parking"],
        images: ["/api/placeholder/800/600"],
        ownerId: "owner1",
        isActive: true
      },
      {
        title: "Family Villa with Garden",
        description: "Spacious family villa with large garden, perfect for families looking for comfort and space.",
        price: "320000.00",
        type: "buy",
        propertyType: "house",
        location: "Wadajir, Mogadishu",
        latitude: "2.0520",
        longitude: "45.3250",
        bedrooms: 4,
        bathrooms: 3,
        sqft: 2500,
        features: ["Garden", "Garage", "Security", "Traditional Design"],
        images: ["https://images.unsplash.com/photo-1564013799919-ab600027ffc6?ixlib=rb-4.0.3&auto=format&fit=crop&w=800&h=600"],
        ownerId: "owner2",
        isActive: true
      },
      {
        title: "Beachfront Apartment",
        description: "Stunning beachfront apartment with ocean views and modern amenities.",
        price: "1200.00",
        type: "rent",
        propertyType: "apartment",
        location: "Jazeera Beach, Mogadishu",
        latitude: "2.0401",
        longitude: "45.3440",
        bedrooms: 3,
        bathrooms: 2,
        sqft: 1250,
        features: ["Ocean View", "Beach Access", "Modern Kitchen", "Balcony"],
        images: ["https://images.unsplash.com/photo-1512917774080-9991f1c4c750?ixlib=rb-4.0.3&auto=format&fit=crop&w=800&h=600"],
        ownerId: "owner3",
        isActive: true
      },
      {
        title: "Commercial Office Space",
        description: "Prime commercial office space in the business district with modern facilities.",
        price: "250000.00",
        type: "buy",
        propertyType: "commercial",
        location: "Banadir, Mogadishu",
        latitude: "2.0580",
        longitude: "45.3180",
        bedrooms: 0,
        bathrooms: 2,
        sqft: 3500,
        features: ["Conference Rooms", "Parking", "Security", "Modern Facilities"],
        images: ["https://images.unsplash.com/photo-1560518883-ce09059eeffa?ixlib=rb-4.0.3&auto=format&fit=crop&w=800&h=600"],
        ownerId: "owner4",
        isActive: true
      },
      {
        title: "Affordable 1BR Apartment",
        description: "Comfortable and affordable apartment perfect for young professionals.",
        price: "450.00",
        type: "rent",
        propertyType: "apartment",
        location: "Karaan, Mogadishu",
        latitude: "2.0650",
        longitude: "45.3350",
        bedrooms: 1,
        bathrooms: 1,
        sqft: 650,
        features: ["Furnished", "Utilities Included", "Transport Links"],
        images: ["https://images.unsplash.com/photo-1568605114967-8130f3a36994?ixlib=rb-4.0.3&auto=format&fit=crop&w=800&h=600"],
        ownerId: "owner5",
        isActive: true
      },
      {
        title: "Development Land",
        description: "Prime development land with excellent potential for residential or commercial projects.",
        price: "85000.00",
        type: "buy",
        propertyType: "land",
        location: "Dharkenley, Mogadishu",
        latitude: "2.0720",
        longitude: "45.3420",
        bedrooms: 0,
        bathrooms: 0,
        sqft: 5000,
        features: ["Road Access", "Utilities Available", "Development Potential"],
        images: ["https://images.unsplash.com/photo-1500382017468-9049fed747ef?ixlib=rb-4.0.3&auto=format&fit=crop&w=800&h=600"],
        ownerId: "owner6",
        isActive: true
      }
    ];

    await db.insert(properties).values(propertiesData);
  }

  private async seedAdminUser() {
    const adminUser = {
      username: "admin",
      password: "admin123",
      email: "admin@kiroservices.com",
      name: "System Administrator",
      role: "admin",
      isActive: true
    };

    await db.insert(adminUsers).values(adminUser);
  }

  private async seedMovingServices() {
    const servicesData = [
      {
        name: "Premium Movers",
        description: "Professional moving services with experienced team and modern equipment",
        phone: "+252 61 234 5678",
        email: "info@premiummovers.so",
        location: "Banadir, Mogadishu",
        rating: "4.8",
        availability: "24/7"
      },
      {
        name: "Quick Transport Solutions",
        description: "Fast and reliable transportation services for all your moving needs",
        phone: "+252 61 345 6789",
        email: "contact@quicktransport.so",
        location: "Hamar Weyne, Mogadishu",
        rating: "4.5",
        availability: "Mon-Sat 6AM-10PM"
      },
      {
        name: "Family Moving Services",
        description: "Specialized in residential moving with care for your belongings",
        phone: "+252 61 456 7890",
        email: "hello@familymoving.so",
        location: "Wadajir, Mogadishu",
        rating: "4.6",
        availability: "Mon-Fri 8AM-6PM"
      },
      {
        name: "Express Logistics",
        description: "Express moving and logistics solutions for urgent relocations",
        phone: "+252 61 567 8901",
        email: "support@expresslogistics.so",
        location: "Dharkenley, Mogadishu",
        rating: "4.4",
        availability: "24/7"
      }
    ];

    await db.insert(movingServices).values(servicesData);
  }
}

export const storage = new DatabaseStorage();