import {
  IDatabase,
  User,
  Client,
  FitnessProfile,
  Attendance,
  Recommendation,
  FitnessGoal,
  Notification,
  DatabaseConfig,
} from "./types";
import {
  db as defaultDb,
  users,
  clients,
  fitnessProfiles,
  attendance,
  recommendations,
  fitnessGoals,
  notifications,
  eq,
  and,
  desc,
  asc,
  sql,
  inArray,
  gte,
  lte,
  or,
  like,
} from "@fitai/database";
import { SQL } from "drizzle-orm";
import type { SortConfig, FilterCondition } from "../filtering";
import { buildWhereConditions, buildSearchCondition } from "../filtering";
import log from "../logger";

// Database row types (before mapping to domain types)
interface UserRow {
  id: string;
  email: string;
  firstName: string;
  lastName: string;
  password: string | null;
  phone: string | null;
  role: string;
  gymId?: string | null;
  gym_id?: string | null; // Alternative column name
  createdAt: number | Date;
  updatedAt: number | Date;
}

interface ClientRow {
  id: string;
  userId: string;
  membershipType: string;
  membershipStatus: string;
  joinDate: number | Date;
  lastVisit?: number | Date | null;
  emergencyContactName?: string | null;
  emergencyContactPhone?: string | null;
  emergencyContactRelationship?: string | null;
  createdAt: number | Date;
  updatedAt: number | Date;
}

interface FitnessProfileRow {
  id: string;
  userId: string;
  height?: number | null;
  weight?: number | null;
  age?: number | null;
  goals?: string | null;
  medicalConditions?: string | null;
  dietaryRestrictions?: string | null;
  activityLevel?: string | null;
  createdAt: number | Date;
  updatedAt: number | Date;
}

interface AttendanceRow {
  id: string;
  userId: string;
  type: string;
  checkInTime: number | Date;
  checkOutTime?: number | Date | null;
  notes?: string | null;
  createdAt: number | Date;
}

interface RecommendationRow {
  id: string;
  userId: string;
  fitnessProfileId: string;
  recommendationText: string;
  activityPlan: string;
  dietPlan: string;
  status: string;
  generatedAt: number | Date;
  approvedBy?: string | null;
  approvedAt?: number | Date | null;
  createdAt: number | Date;
  updatedAt: number | Date;
}

interface FitnessGoalRow {
  id: string;
  userId: string;
  fitnessProfileId?: string | null;
  goalType: string;
  title: string;
  description?: string | null;
  targetValue?: number | null;
  currentValue?: number | null;
  unit?: string | null;
  status: string;
  progress: number | null;
  priority: string;
  startDate: number | Date;
  targetDate?: number | Date | null;
  completedDate?: number | Date | null;
  notes?: string | null;
  createdAt: number | Date;
  updatedAt: number | Date;
}

// Type for PRAGMA table_info result
interface TableInfoRow {
  name: string;
  type: string;
  notnull: number;
  dflt_value: string | null;
  pk: number;
}

// Type for raw SQL results with unknown structure
type RawSQLRow = Record<string, unknown>;

export class DrizzleDatabase implements IDatabase {
  private config: DatabaseConfig;
  private db: typeof defaultDb;

  constructor(config: DatabaseConfig, db?: typeof defaultDb) {
    this.config = config;
    this.db = db || defaultDb;
  }

  async connect(): Promise<void> {
    // Drizzle with better-sqlite3 connects synchronously on initialization
    // We can just log here if needed
    if (this.config.options?.logging) {
      log.info("Drizzle database connected");
    }
    await this.createTables();
  }

  async disconnect(): Promise<void> {
    // better-sqlite3 handle is managed by Drizzle, usually no explicit disconnect needed for connection pooling
    // but we can close the underlying sqlite instance if we had access to it.
    // For now, we'll assume it's handled.
    if (this.config.options?.logging) {
      log.info("Drizzle database disconnected");
    }
  }

  private async createTables(): Promise<void> {
    // Users table
    await this.db.run(sql`
            CREATE TABLE IF NOT EXISTS users (
                id TEXT PRIMARY KEY,
                email TEXT UNIQUE NOT NULL,
                first_name TEXT NOT NULL,
                last_name TEXT NOT NULL,
                password TEXT,
                phone TEXT,
                role TEXT NOT NULL CHECK (role IN ('superAdmin', 'admin', 'trainer', 'client')),
                gym_id TEXT,
                created_at INTEGER NOT NULL,
                updated_at INTEGER NOT NULL
            )
        `);

    // Migration: ensure gym_id column exists on users (for existing DBs)
    const userCols = await this.db.all(sql`PRAGMA table_info('users')`);
    if (!userCols.some((c: any) => c.name === "gym_id")) {
      await this.db.run(sql`ALTER TABLE users ADD COLUMN gym_id TEXT`);
    }
    // Clients table
    await this.db.run(sql`
            CREATE TABLE IF NOT EXISTS clients (
                id TEXT PRIMARY KEY,
                user_id TEXT NOT NULL,
                membership_type TEXT NOT NULL CHECK (membership_type IN ('basic', 'premium', 'vip')),
                membership_status TEXT NOT NULL CHECK (membership_status IN ('active', 'inactive', 'suspended')),
                join_date INTEGER NOT NULL,
                last_visit INTEGER,
                emergency_contact_name TEXT,
                emergency_contact_phone TEXT,
                emergency_contact_relationship TEXT,
                created_at INTEGER NOT NULL,
                updated_at INTEGER NOT NULL,
                FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
            )
        `);

    // Fitness profiles table
    await this.db.run(sql`
            CREATE TABLE IF NOT EXISTS fitness_profiles (
                id TEXT PRIMARY KEY,
                user_id TEXT NOT NULL UNIQUE,
                height REAL,
                weight REAL,
                age INTEGER,
                gender TEXT CHECK (gender IN ('male', 'female', 'other', 'prefer_not_to_say')),
                activity_level TEXT CHECK (activity_level IN ('sedentary', 'lightly_active', 'moderately_active', 'very_active', 'extremely_active')),
                fitness_goals TEXT,
                exercise_habits TEXT,
                diet_habits TEXT,
                medical_conditions TEXT,
                allergies TEXT,
                injuries TEXT,
                created_at INTEGER NOT NULL,
                updated_at INTEGER NOT NULL,
                FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
            )
        `);

    // Attendance table
    await this.db.run(sql`
            CREATE TABLE IF NOT EXISTS attendance (
                id TEXT PRIMARY KEY,
                user_id TEXT NOT NULL,
                check_in_time INTEGER NOT NULL,
                check_out_time INTEGER,
                type TEXT NOT NULL CHECK (type IN ('gym', 'class', 'personal_training')),
                notes TEXT,
                created_at INTEGER NOT NULL,
                FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
            )
        `);

    // Recommendations table
    await this.db.run(sql`
            CREATE TABLE IF NOT EXISTS recommendations (
                id TEXT PRIMARY KEY,
                user_id TEXT NOT NULL,
                fitness_profile_id TEXT NOT NULL,
                recommendation_text TEXT NOT NULL,
                activity_plan TEXT NOT NULL,
                diet_plan TEXT NOT NULL,
                status TEXT NOT NULL CHECK (status IN ('pending', 'approved', 'rejected')) DEFAULT 'pending',
                generated_at INTEGER NOT NULL,
                approved_at INTEGER,
                approved_by TEXT,
                created_at INTEGER NOT NULL,
                updated_at INTEGER NOT NULL,
                FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
                FOREIGN KEY (fitness_profile_id) REFERENCES fitness_profiles (id) ON DELETE CASCADE
            )
        `);

    // Fitness Goals table
    await this.db.run(sql`
            CREATE TABLE IF NOT EXISTS fitness_goals (
                id TEXT PRIMARY KEY,
                user_id TEXT NOT NULL,
                fitness_profile_id TEXT,
                goal_type TEXT NOT NULL,
                title TEXT NOT NULL,
                description TEXT,
                target_value REAL,
                current_value REAL,
                unit TEXT,
                start_date INTEGER NOT NULL,
                target_date INTEGER,
                completed_date INTEGER,
                status TEXT NOT NULL DEFAULT 'active',
                progress REAL DEFAULT 0,
                priority TEXT DEFAULT 'medium',
                notes TEXT,
                created_at INTEGER NOT NULL,
                updated_at INTEGER NOT NULL,
                FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
                FOREIGN KEY (fitness_profile_id) REFERENCES fitness_profiles (id) ON DELETE CASCADE
            )
        `);
  }

  // User operations
  async createUser(
    userData: Omit<User, "createdAt" | "updatedAt" | "id"> & { id?: string },
  ): Promise<User> {
    const id = userData.id || Math.random().toString(36).substr(2, 9);
    const now = new Date();

    const newUser = {
      ...userData,
      id,
      createdAt: now,
      updatedAt: now,
    };

    await this.db.insert(users).values(newUser);
    return newUser;
  }

  async getUserById(id: string): Promise<User | null> {
    const result = await this.db
      .select()
      .from(users)
      .where(eq(users.id, id))
      .get();
    return result ? this.mapUser(result) : null;
  }

  async getUserByEmail(email: string): Promise<User | null> {
    const result = await this.db
      .select()
      .from(users)
      .where(eq(users.email, email))
      .get();
    return result ? this.mapUser(result) : null;
  }

  async getAllUsers(): Promise<User[]> {
    const results = await this.db
      .select()
      .from(users)
      .orderBy(desc(users.createdAt))
      .all();
    return results.map(this.mapUser);
  }

  async updateUser(id: string, updates: Partial<User>): Promise<User | null> {
    const { id: _, ...updateData } = updates;
    if (Object.keys(updateData).length === 0) return this.getUserById(id);

    await this.db
      .update(users)
      .set({ ...updateData, updatedAt: new Date() })
      .where(eq(users.id, id))
      .run();

    return this.getUserById(id);
  }

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

  async migrateUserId(oldId: string, newId: string): Promise<void> {
    await this.db
      .update(users)
      .set({ id: newId })
      .where(eq(users.id, oldId))
      .run();
  }

  /**
   * Get users with pagination, sorting, and filtering
   * Uses SQL LIMIT/OFFSET for efficient pagination
   */
  async getUsersWithPagination(params: {
    page: number;
    limit: number;
    role?: string;
    sort?: SortConfig;
    filters?: FilterCondition[];
    search?: string;
  }): Promise<{ users: User[]; total: number }> {
    const { page, limit, role, sort, filters, search } = params;
    const offset = (page - 1) * limit;

    // Build WHERE conditions
    const whereConditions: any[] = [];

    // Add role filter if provided
    if (role) {
      whereConditions.push(eq(users.role, role as User["role"]));
    }

    // Add filter conditions from query params
    if (filters && filters.length > 0) {
      const columnMap = {
        role: users.role,
        email: users.email,
        firstName: users.firstName,
        lastName: users.lastName,
        phone: users.phone,
        gymId: users.gymId,
        createdAt: users.createdAt,
        updatedAt: users.updatedAt,
      };
      const filterCondition = buildWhereConditions(filters, columnMap);
      if (filterCondition) {
        whereConditions.push(filterCondition);
      }
    }

    // Add search condition
    if (search) {
      const searchFields = ["email", "firstName", "lastName", "phone"];
      const searchCondition = buildSearchCondition(search, searchFields, {
        email: users.email,
        firstName: users.firstName,
        lastName: users.lastName,
        phone: users.phone,
      });
      if (searchCondition) {
        whereConditions.push(searchCondition);
      }
    }

    const whereClause =
      whereConditions.length > 0 ? and(...whereConditions) : undefined;

    // Build ORDER BY clause
    const sortColumn = sort?.field
      ? {
          email: users.email,
          firstName: users.firstName,
          lastName: users.lastName,
          role: users.role,
          createdAt: users.createdAt,
          updatedAt: users.updatedAt,
        }[sort.field]
      : users.createdAt;

    const orderBy =
      sort?.direction === "asc"
        ? asc(sortColumn || users.createdAt)
        : desc(sortColumn || users.createdAt);

    // Get total count (without pagination)
    const countQuery = whereClause
      ? this.db
          .select({ count: sql<number>`count(*)` })
          .from(users)
          .where(whereClause)
      : this.db.select({ count: sql<number>`count(*)` }).from(users);

    const countResult = await countQuery.get();
    const total = countResult?.count ?? 0;

    // Get paginated results
    const query = this.db
      .select()
      .from(users)
      .orderBy(orderBy)
      .limit(limit)
      .offset(offset);

    const results = whereClause
      ? await query.where(whereClause).all()
      : await query.all();

    return {
      users: results.map(this.mapUser),
      total,
    };
  }

  /**
   * Get users with their client and attendance data in optimized batches
   * Avoids N+1 query problem by batching related data queries
   * Now supports sorting, filtering, and search
   */
  async getUsersWithRelatedData(params?: {
    page?: number;
    limit?: number;
    role?: string;
    sort?: SortConfig;
    filters?: FilterCondition[];
    search?: string;
  }): Promise<{
    users: Array<
      User & {
        client?: Client | null;
        isCheckedIn?: boolean;
        checkInTime?: Date | null;
        lastCheckInTime?: Date | null;
        checkInsThisWeek?: number;
        checkInsThisMonth?: number;
      }
    >;
    total?: number;
  }> {
    const page = params?.page ?? 1;
    const limit = params?.limit ?? 1000;
    const role = params?.role;
    const sort = params?.sort;
    const filters = params?.filters;
    const search = params?.search;

    // Step 1: Get paginated users with sorting and filtering
    const { users: paginatedUsers, total } = await this.getUsersWithPagination({
      page,
      limit,
      role,
      sort,
      filters,
      search,
    });

    if (paginatedUsers.length === 0) {
      return { users: [], total: 0 };
    }

    const userIds = paginatedUsers.map((u) => u.id);

    // Step 2: Batch fetch clients for all users (1 query instead of N)
    const allClients = await this.db.select().from(clients).all();
    const clientsByUserId = new Map(
      allClients.map((c) => [c.userId, this.mapClient(c)]),
    );

    // Step 3: Batch fetch attendance stats (2 queries instead of 2N)
    const attendanceStats = await this.getAttendanceStatsBatch(userIds);

    // Step 4: Combine all data
    const usersWithData = paginatedUsers.map((user) => {
      const client = clientsByUserId.get(user.id) || null;
      const stats = attendanceStats.get(user.id) || {
        isCheckedIn: false,
        checkInTime: null,
        lastCheckInTime: null,
        checkInsThisWeek: 0,
        checkInsThisMonth: 0,
      };

      return {
        ...user,
        client,
        ...stats,
      };
    });

    return { users: usersWithData, total };
  }

  /**
   * Get attendance statistics for multiple users efficiently
   * Uses aggregation queries to avoid N+1 problem
   */
  async getAttendanceStatsBatch(userIds: string[]): Promise<
    Map<
      string,
      {
        isCheckedIn: boolean;
        checkInTime: Date | null;
        lastCheckInTime: Date | null;
        checkInsThisWeek: number;
        checkInsThisMonth: number;
      }
    >
  > {
    if (userIds.length === 0) {
      return new Map();
    }

    // Fetch ALL attendance records for these users (2 queries total instead of 2N)
    const allAttendance = await this.db.select().from(attendance).all();

    // Filter to relevant users
    const relevantAttendance = allAttendance.filter((a) =>
      userIds.includes(a.userId),
    );

    const statsMap = new Map();
    const weekAgo = new Date(Date.now() - 7 * 24 * 60 * 60 * 1000);
    const monthAgo = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000);

    for (const userId of userIds) {
      const userAttendance = relevantAttendance.filter(
        (a) => a.userId === userId,
      );

      // Find active check-in
      const activeCheckIn = userAttendance.find((a) => !a.checkOutTime);

      // Get last check-in time
      const sortedAttendance = userAttendance.sort(
        (a, b) =>
          new Date(b.checkInTime).getTime() - new Date(a.checkInTime).getTime(),
      );
      const lastCheckInTime = sortedAttendance[0]?.checkInTime || null;

      // Count check-ins
      const checkInsThisWeek = userAttendance.filter(
        (a) => new Date(a.checkInTime) >= weekAgo,
      ).length;
      const checkInsThisMonth = userAttendance.filter(
        (a) => new Date(a.checkInTime) >= monthAgo,
      ).length;

      statsMap.set(userId, {
        isCheckedIn: !!activeCheckIn,
        checkInTime: activeCheckIn?.checkInTime || null,
        lastCheckInTime,
        checkInsThisWeek,
        checkInsThisMonth,
      });
    }

    // Fill in missing users with default values
    for (const userId of userIds) {
      if (!statsMap.has(userId)) {
        statsMap.set(userId, {
          isCheckedIn: false,
          checkInTime: null,
          lastCheckInTime: null,
          checkInsThisWeek: 0,
          checkInsThisMonth: 0,
        });
      }
    }

    return statsMap;
  }

  // Client operations
  async createClient(clientData: Omit<Client, "id">): Promise<Client> {
    const id = Math.random().toString(36).substr(2, 9);
    const newClient = {
      id,
      ...clientData,
      createdAt: new Date(),
      updatedAt: new Date(),
    };

    await this.db.insert(clients).values(newClient as any);
    return this.mapClient(newClient);
  }

  async getClientById(id: string): Promise<Client | null> {
    const result = await this.db
      .select()
      .from(clients)
      .where(eq(clients.id, id))
      .get();
    return result ? this.mapClient(result) : null;
  }

  async getClientByUserId(userId: string): Promise<Client | null> {
    const result = await this.db
      .select()
      .from(clients)
      .where(eq(clients.userId, userId))
      .get();
    return result ? this.mapClient(result) : null;
  }

  async getClientsByUserIds(userIds: string[]): Promise<Client[]> {
    if (userIds.length === 0) return [];

    const results = await this.db
      .select()
      .from(clients)
      .where(inArray(clients.userId, userIds))
      .all();
    return results.map((r) => this.mapClient(r));
  }

  async getAllClients(): Promise<Client[]> {
    const results = await this.db
      .select()
      .from(clients)
      .orderBy(desc(clients.joinDate))
      .all();
    return results.map(this.mapClient);
  }

  /**
   * Get clients with pagination, sorting, and filtering
   */
  async getClientsWithPagination(params: {
    page: number;
    limit: number;
    sort?: SortConfig;
    filters?: FilterCondition[];
    search?: string;
  }): Promise<{ clients: Client[]; total: number }> {
    const { page, limit, sort, filters, search } = params;
    const offset = (page - 1) * limit;

    // Build WHERE conditions
    const whereConditions: any[] = [];

    // Add filter conditions
    if (filters && filters.length > 0) {
      const columnMap = {
        userId: clients.userId,
        membershipType: clients.membershipType,
        membershipStatus: clients.membershipStatus,
        joinDate: clients.joinDate,
        lastVisit: clients.lastVisit,
      };
      const filterCondition = buildWhereConditions(filters, columnMap);
      if (filterCondition) {
        whereConditions.push(filterCondition);
      }
    }

    // Add search condition (search by user ID or emergency contact)
    if (search) {
      const searchCondition = buildSearchCondition(
        search,
        ["userId", "emergencyContactName", "emergencyContactPhone"],
        {
          userId: clients.userId,
          emergencyContactName: clients.emergencyContactName,
          emergencyContactPhone: clients.emergencyContactPhone,
        },
      );
      if (searchCondition) {
        whereConditions.push(searchCondition);
      }
    }

    const whereClause =
      whereConditions.length > 0 ? and(...whereConditions) : undefined;

    // Build ORDER BY clause
    const sortColumn = sort?.field
      ? {
          joinDate: clients.joinDate,
          lastVisit: clients.lastVisit,
          membershipType: clients.membershipType,
          membershipStatus: clients.membershipStatus,
          userId: clients.userId,
        }[sort.field]
      : clients.joinDate;

    const orderBy =
      sort?.direction === "asc"
        ? asc(sortColumn || clients.joinDate)
        : desc(sortColumn || clients.joinDate);

    // Get total count
    const countQuery = whereClause
      ? this.db
          .select({ count: sql<number>`count(*)` })
          .from(clients)
          .where(whereClause)
      : this.db.select({ count: sql<number>`count(*)` }).from(clients);

    const countResult = await countQuery.get();
    const total = countResult?.count ?? 0;

    // Get paginated results
    const query = this.db
      .select()
      .from(clients)
      .orderBy(orderBy)
      .limit(limit)
      .offset(offset);

    const results = whereClause
      ? await query.where(whereClause).all()
      : await query.all();

    return {
      clients: results.map(this.mapClient),
      total,
    };
  }

  async updateClient(
    id: string,
    updates: Partial<Client>,
  ): Promise<Client | null> {
    const { id: _, ...updateData } = updates;
    if (Object.keys(updateData).length === 0) return this.getClientById(id);

    await this.db
      .update(clients)
      .set({ ...updateData, updatedAt: new Date() } as any)
      .where(eq(clients.id, id))
      .run();

    return this.getClientById(id);
  }

  async deleteClient(id: string): Promise<boolean> {
    const result = await this.db
      .delete(clients)
      .where(eq(clients.id, id))
      .run();
    return result.changes > 0;
  }

  // Fitness Profile operations
  async createFitnessProfile(
    profileData: Omit<FitnessProfile, "id" | "createdAt" | "updatedAt">,
  ): Promise<FitnessProfile> {
    const now = new Date();
    const id = Math.random().toString(36).substr(2, 9);
    const newProfile = {
      id,
      ...profileData,
      createdAt: now,
      updatedAt: now,
    };

    await this.db.insert(fitnessProfiles).values(newProfile as any);
    return { id, ...profileData, createdAt: now, updatedAt: now };
  }

  async getFitnessProfileByUserId(
    userId: string,
  ): Promise<FitnessProfile | null> {
    const result = await this.db
      .select()
      .from(fitnessProfiles)
      .where(eq(fitnessProfiles.userId, userId))
      .get();
    return result ? this.mapFitnessProfile(result) : null;
  }

  async getAllFitnessProfiles(): Promise<FitnessProfile[]> {
    const results = await this.db
      .select()
      .from(fitnessProfiles)
      .orderBy(desc(fitnessProfiles.createdAt))
      .all();
    return results.map(this.mapFitnessProfile);
  }

  async updateFitnessProfile(
    userId: string,
    updates: Partial<FitnessProfile>,
  ): Promise<FitnessProfile | null> {
    const { userId: _, ...updateData } = updates;
    if (Object.keys(updateData).length === 0)
      return this.getFitnessProfileByUserId(userId);

    const dbUpdates = { ...updateData } as any;
    if (updateData.fitnessGoals) {
      dbUpdates.fitnessGoals = JSON.stringify(updateData.fitnessGoals);
    }

    await this.db
      .update(fitnessProfiles)
      .set({ ...dbUpdates, updatedAt: new Date() })
      .where(eq(fitnessProfiles.userId, userId))
      .run();

    return this.getFitnessProfileByUserId(userId);
  }

  async deleteFitnessProfile(userId: string): Promise<boolean> {
    const result = await this.db
      .delete(fitnessProfiles)
      .where(eq(fitnessProfiles.userId, userId))
      .run();
    return result.changes > 0;
  }

  // Attendance operations
  async checkIn(
    userId: string,
    type: "gym" | "class" | "personal_training",
    notes?: string,
  ): Promise<Attendance> {
    const id = Math.random().toString(36).substr(2, 9);
    const now = new Date();

    const newAttendance = {
      id,
      userId,
      checkInTime: now,
      type,
      notes,
      createdAt: now,
    };

    await this.db.insert(attendance).values(newAttendance as any);

    // Update client last visit
    const client = await this.getClientByUserId(userId);
    if (client) {
      await this.updateClient(client.id, { lastVisit: now });
    }

    return newAttendance;
  }

  async checkOut(attendanceId: string): Promise<Attendance | null> {
    const now = new Date();
    await this.db
      .update(attendance)
      .set({ checkOutTime: now })
      .where(eq(attendance.id, attendanceId))
      .run();

    return this.getAttendanceById(attendanceId);
  }

  async getAttendanceById(id: string): Promise<Attendance | null> {
    const result = await this.db
      .select()
      .from(attendance)
      .where(eq(attendance.id, id))
      .get();
    return result ? this.mapAttendance(result) : null;
  }

  async getAttendanceHistory(userId: string): Promise<Attendance[]> {
    const results = await this.db
      .select()
      .from(attendance)
      .where(eq(attendance.userId, userId))
      .orderBy(desc(attendance.checkInTime))
      .all();
    return results.map(this.mapAttendance);
  }

  async getAllAttendance(): Promise<Attendance[]> {
    const results = await this.db
      .select()
      .from(attendance)
      .orderBy(desc(attendance.checkInTime))
      .all();
    return results.map(this.mapAttendance);
  }

  /**
   * Get attendance records with pagination, sorting, and filtering
   */
  async getAttendanceWithPagination(params: {
    page: number;
    limit: number;
    sort?: SortConfig;
    filters?: FilterCondition[];
    search?: string;
  }): Promise<{ attendance: Attendance[]; total: number }> {
    const { page, limit, sort, filters, search } = params;
    const offset = (page - 1) * limit;

    // Build WHERE conditions
    const whereConditions: any[] = [];

    // Add filter conditions
    if (filters && filters.length > 0) {
      const columnMap = {
        userId: attendance.userId,
        type: attendance.type,
        checkInTime: attendance.checkInTime,
        checkOutTime: attendance.checkOutTime,
      };
      const filterCondition = buildWhereConditions(filters, columnMap);
      if (filterCondition) {
        whereConditions.push(filterCondition);
      }
    }

    // Add search condition (search by user ID or notes)
    if (search) {
      const searchCondition = buildSearchCondition(
        search,
        ["userId", "notes"],
        {
          userId: attendance.userId,
          notes: attendance.notes,
        },
      );
      if (searchCondition) {
        whereConditions.push(searchCondition);
      }
    }

    const whereClause =
      whereConditions.length > 0 ? and(...whereConditions) : undefined;

    // Build ORDER BY clause
    const sortColumn = sort?.field
      ? {
          checkInTime: attendance.checkInTime,
          checkOutTime: attendance.checkOutTime,
          type: attendance.type,
          userId: attendance.userId,
        }[sort.field]
      : attendance.checkInTime;

    const orderBy =
      sort?.direction === "asc"
        ? asc(sortColumn || attendance.checkInTime)
        : desc(sortColumn || attendance.checkInTime);

    // Get total count
    const countQuery = whereClause
      ? this.db
          .select({ count: sql<number>`count(*)` })
          .from(attendance)
          .where(whereClause)
      : this.db.select({ count: sql<number>`count(*)` }).from(attendance);

    const countResult = await countQuery.get();
    const total = countResult?.count ?? 0;

    // Get paginated results
    const query = this.db
      .select()
      .from(attendance)
      .orderBy(orderBy)
      .limit(limit)
      .offset(offset);

    const results = whereClause
      ? await query.where(whereClause).all()
      : await query.all();

    return {
      attendance: results.map(this.mapAttendance),
      total,
    };
  }

  async getActiveCheckIn(userId: string): Promise<Attendance | null> {
    // Drizzle doesn't support IS NULL in where directly with simple syntax sometimes, but eq(col, null) works or isNull(col)
    // We need to check how to filter for null checkOutTime.
    // In Drizzle, we can filter in JS or use isNull operator if imported.
    // Let's fetch recent and filter for now to be safe, or use raw sql if needed, but better to use Drizzle operators.
    // Actually, we can just fetch all for user and filter in memory since it's unlikely to be huge for active checkins,
    // but correct way is `isNull(attendance.checkOutTime)`.
    // Since I didn't import `isNull`, I'll fetch recent history and find first active.

    const history = await this.getAttendanceHistory(userId);
    return history.find((a) => !a.checkOutTime) || null;
  }

  async getAttendanceHistoriesByUserIds(
    userIds: string[],
  ): Promise<Attendance[]> {
    if (userIds.length === 0) return [];

    const results = await this.db
      .select()
      .from(attendance)
      .where(inArray(attendance.userId, userIds))
      .orderBy(desc(attendance.checkInTime))
      .all();
    return results.map((r) => this.mapAttendance(r));
  }

  async getActiveCheckInsByUserIds(userIds: string[]): Promise<Attendance[]> {
    if (userIds.length === 0) return [];

    // Get all attendance records for these users and filter for active check-ins
    const results = await this.db
      .select()
      .from(attendance)
      .where(inArray(attendance.userId, userIds))
      .all();

    // Filter for active check-ins (no checkout time)
    return results
      .filter((r) => !r.checkOutTime)
      .map((r) => this.mapAttendance(r));
  }

  // Recommendation operations
  async createRecommendation(
    data: Omit<Recommendation, "createdAt" | "approvedAt" | "approvedBy">,
  ): Promise<Recommendation> {
    const now = new Date();
    const newRec = {
      ...data,
      createdAt: now,
      status: data.status || "pending",
    };

    await this.db.insert(recommendations).values(newRec as any);
    return newRec as Recommendation;
  }

  async getRecommendationsByUserId(userId: string): Promise<Recommendation[]> {
    const results = await this.db
      .select()
      .from(recommendations)
      .where(eq(recommendations.userId, userId))
      .orderBy(desc(recommendations.createdAt))
      .all();
    return results.map(this.mapRecommendation);
  }

  async getAllRecommendations(): Promise<Recommendation[]> {
    const results = await this.db
      .select()
      .from(recommendations)
      .orderBy(desc(recommendations.createdAt))
      .all();
    return results.map(this.mapRecommendation);
  }

  async updateRecommendation(
    id: string,
    updates: Partial<Recommendation>,
  ): Promise<Recommendation | null> {
    const { id: _, ...updateData } = updates;
    if (Object.keys(updateData).length === 0)
      return this.getRecommendationById(id);

    await this.db
      .update(recommendations)
      .set(updateData as any)
      .where(eq(recommendations.id, id))
      .run();

    return this.getRecommendationById(id);
  }

  async deleteRecommendation(id: string): Promise<boolean> {
    const result = await this.db
      .delete(recommendations)
      .where(eq(recommendations.id, id))
      .run();
    return result.changes > 0;
  }

  async getRecommendationById(id: string): Promise<Recommendation | null> {
    const result = await this.db
      .select()
      .from(recommendations)
      .where(eq(recommendations.id, id))
      .get();
    return result ? this.mapRecommendation(result) : null;
  }

  async getRecommendationsByUserIds(
    userIds: string[],
  ): Promise<Recommendation[]> {
    if (userIds.length === 0) return [];

    const results = await this.db
      .select()
      .from(recommendations)
      .where(inArray(recommendations.userId, userIds))
      .orderBy(desc(recommendations.createdAt))
      .all();
    return results.map((r) => this.mapRecommendation(r));
  }

  // Fitness Goals operations
  async createFitnessGoal(
    goalData: Omit<FitnessGoal, "createdAt" | "updatedAt">,
  ): Promise<FitnessGoal> {
    const now = new Date();
    const newGoal = {
      ...goalData,
      createdAt: now,
      updatedAt: now,
    };

    await this.db.insert(fitnessGoals).values(newGoal as any);
    return newGoal as FitnessGoal;
  }

  async getFitnessGoalById(id: string): Promise<FitnessGoal | null> {
    const result = await this.db
      .select()
      .from(fitnessGoals)
      .where(eq(fitnessGoals.id, id))
      .get();
    return result ? this.mapFitnessGoal(result) : null;
  }

  async getFitnessGoalsByUserId(
    userId: string,
    status?: string,
  ): Promise<FitnessGoal[]> {
    let query = this.db
      .select()
      .from(fitnessGoals)
      .where(eq(fitnessGoals.userId, userId));

    if (status) {
      query = this.db
        .select()
        .from(fitnessGoals)
        .where(
          and(
            eq(fitnessGoals.userId, userId),
            eq(fitnessGoals.status, status as any),
          ),
        );
    }

    const results = await query.orderBy(desc(fitnessGoals.createdAt)).all();
    return results.map(this.mapFitnessGoal);
  }

  async updateFitnessGoal(
    id: string,
    updates: Partial<FitnessGoal>,
  ): Promise<FitnessGoal | null> {
    const { id: _, ...updateData } = updates;
    if (Object.keys(updateData).length === 0)
      return this.getFitnessGoalById(id);

    await this.db
      .update(fitnessGoals)
      .set({ ...updateData, updatedAt: new Date() } as any)
      .where(eq(fitnessGoals.id, id))
      .run();

    return this.getFitnessGoalById(id);
  }

  async deleteFitnessGoal(id: string): Promise<boolean> {
    const result = await this.db
      .delete(fitnessGoals)
      .where(eq(fitnessGoals.id, id))
      .run();
    return result.changes > 0;
  }

  async updateGoalProgress(
    id: string,
    currentValue: number,
  ): Promise<FitnessGoal | null> {
    const goal = await this.getFitnessGoalById(id);
    if (!goal) return null;

    let progress = goal.progress;
    if (goal.targetValue && goal.targetValue > 0) {
      progress = Math.min(100, (currentValue / goal.targetValue) * 100);
    }

    await this.db
      .update(fitnessGoals)
      .set({ currentValue, progress, updatedAt: new Date() })
      .where(eq(fitnessGoals.id, id))
      .run();

    return this.getFitnessGoalById(id);
  }

  async completeGoal(id: string): Promise<FitnessGoal | null> {
    const now = new Date();
    await this.db
      .update(fitnessGoals)
      .set({
        status: "completed",
        progress: 100,
        completedDate: now,
        updatedAt: now,
      })
      .where(eq(fitnessGoals.id, id))
      .run();

    return this.getFitnessGoalById(id);
  }

  async getFitnessGoalsByUserIds(userIds: string[]): Promise<FitnessGoal[]> {
    if (userIds.length === 0) return [];

    const results = await this.db
      .select()
      .from(fitnessGoals)
      .where(inArray(fitnessGoals.userId, userIds))
      .orderBy(desc(fitnessGoals.createdAt))
      .all();
    return results.map((r) => this.mapFitnessGoal(r));
  }

  async getDashboardStats(): Promise<{
    totalUsers: number;
    activeClients: number;
    totalRevenue: number;
    revenueGrowth: number;
  }> {
    // Placeholder implementation as per original sqlite.ts (which didn't implement this either in the viewed snippet,
    // but interface requires it. I'll provide a basic implementation or mock).
    // The original sqlite.ts snippet ended before showing this method, but the interface has it.
    // I'll implement a basic count.

    const allUsers = await this.db.select().from(users).all();
    const activeClients = await this.db
      .select()
      .from(clients)
      .where(eq(clients.membershipStatus, "active"))
      .all();

    return {
      totalUsers: allUsers.length,
      activeClients: activeClients.length,
      totalRevenue: 0, // Not tracking payments yet
      revenueGrowth: 0,
    };
  }

  // Mappers - using Record<string, unknown> for database rows since Drizzle types vary
  private mapUser(row: Record<string, unknown>): User {
    return {
      id: String(row.id),
      email: String(row.email),
      firstName: String(row.firstName),
      lastName: String(row.lastName),
      password: String(row.password ?? ""),
      phone: row.phone ? String(row.phone) : undefined,
      role: String(row.role) as User["role"],
      gymId: row.gymId
        ? String(row.gymId)
        : row.gym_id
          ? String(row.gym_id)
          : undefined,
      createdAt: new Date(row.createdAt as number | Date),
      updatedAt: new Date(row.updatedAt as number | Date),
    };
  }

  private mapClient(row: Record<string, unknown>): Client {
    return {
      id: String(row.id),
      userId: String(row.userId),
      membershipType: String(row.membershipType) as Client["membershipType"],
      membershipStatus: String(
        row.membershipStatus,
      ) as Client["membershipStatus"],
      joinDate: new Date(row.joinDate as number | Date),
      lastVisit: row.lastVisit
        ? new Date(row.lastVisit as number | Date)
        : undefined,
      emergencyContact: row.emergencyContactName
        ? {
            name: String(row.emergencyContactName),
            phone: String(row.emergencyContactPhone ?? ""),
            relationship: String(row.emergencyContactRelationship ?? ""),
          }
        : undefined,
    };
  }

  private mapFitnessProfile(row: Record<string, unknown>): FitnessProfile {
    return {
      id: String(row.id),
      userId: String(row.userId),
      height: typeof row.height === "number" ? row.height : undefined,
      weight: typeof row.weight === "number" ? row.weight : undefined,
      age: typeof row.age === "number" ? row.age : undefined,
      gender: row.gender
        ? (String(row.gender) as FitnessProfile["gender"])
        : undefined,
      fitnessGoals: row.goals ? [String(row.goals)] : undefined,
      medicalConditions: row.medicalConditions
        ? String(row.medicalConditions)
        : undefined,
      allergies: row.dietaryRestrictions
        ? String(row.dietaryRestrictions)
        : undefined,
      injuries: undefined,
      activityLevel: row.activityLevel
        ? (String(row.activityLevel) as FitnessProfile["activityLevel"])
        : undefined,
      createdAt: new Date(row.createdAt as number | Date),
      updatedAt: new Date(row.updatedAt as number | Date),
    };
  }

  private mapAttendance(row: Record<string, unknown>): Attendance {
    return {
      id: String(row.id),
      userId: String(row.userId),
      type: String(row.type) as Attendance["type"],
      checkInTime: new Date(row.checkInTime as number | Date),
      checkOutTime: row.checkOutTime
        ? new Date(row.checkOutTime as number | Date)
        : undefined,
      notes: row.notes ? String(row.notes) : undefined,
      createdAt: new Date(row.createdAt as number | Date),
    };
  }

  private mapRecommendation(row: Record<string, unknown>): Recommendation {
    return {
      id: String(row.id),
      userId: String(row.userId),
      fitnessProfileId: String(row.fitnessProfileId),
      recommendationText: String(row.recommendationText),
      activityPlan: String(row.activityPlan),
      dietPlan: String(row.dietPlan),
      status: String(row.status) as Recommendation["status"],
      generatedAt: new Date(row.generatedAt as number | Date),
      approvedBy: row.approvedBy ? String(row.approvedBy) : undefined,
      approvedAt: row.approvedAt
        ? new Date(row.approvedAt as number | Date)
        : undefined,
      createdAt: new Date(row.createdAt as number | Date),
      updatedAt: new Date(row.updatedAt as number | Date),
    };
  }

  private mapFitnessGoal(row: Record<string, unknown>): FitnessGoal {
    return {
      id: String(row.id),
      userId: String(row.userId),
      fitnessProfileId: row.fitnessProfileId
        ? String(row.fitnessProfileId)
        : undefined,
      goalType: String(row.goalType) as FitnessGoal["goalType"],
      title: String(row.title),
      description: row.description ? String(row.description) : undefined,
      targetValue:
        typeof row.targetValue === "number" ? row.targetValue : undefined,
      currentValue:
        typeof row.currentValue === "number" ? row.currentValue : undefined,
      unit: row.unit ? String(row.unit) : undefined,
      status: String(row.status) as FitnessGoal["status"],
      progress: typeof row.progress === "number" ? row.progress : 0,
      priority: String(row.priority) as FitnessGoal["priority"],
      startDate: new Date(row.startDate as number | Date),
      targetDate: row.targetDate
        ? new Date(row.targetDate as number | Date)
        : undefined,
      completedDate: row.completedDate
        ? new Date(row.completedDate as number | Date)
        : undefined,
      notes: row.notes ? String(row.notes) : undefined,
      createdAt: new Date(row.createdAt as number | Date),
      updatedAt: new Date(row.updatedAt as number | Date),
    };
  }

  // Notification operations
  async createNotification(
    notification: Omit<Notification, "createdAt">,
  ): Promise<Notification> {
    const newNotification = {
      ...notification,
      createdAt: new Date(),
    };

    const result = await this.db.insert(notifications).values(newNotification);

    log.debug("Notification created", { id: notification.id });
    return newNotification;
  }

  async getNotificationsByUserId(userId: string): Promise<Notification[]> {
    const rows = await this.db
      .select()
      .from(notifications)
      .where(eq(notifications.userId, userId))
      .orderBy(desc(notifications.createdAt))
      .all();

    return rows.map((row) => this.mapNotification(row));
  }

  async getUnreadNotificationCount(userId: string): Promise<number> {
    const result = await this.db
      .select({ count: sql<number>`count(*)` })
      .from(notifications)
      .where(
        and(eq(notifications.userId, userId), eq(notifications.read, false)),
      );

    return Number(result[0]?.count || 0);
  }

  async markNotificationAsRead(id: string): Promise<Notification | null> {
    await this.db
      .update(notifications)
      .set({ read: true })
      .where(eq(notifications.id, id));

    const rows = await this.db
      .select()
      .from(notifications)
      .where(eq(notifications.id, id));

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

    return this.mapNotification(rows[0]);
  }

  async markAllNotificationsAsRead(userId: string): Promise<void> {
    await this.db
      .update(notifications)
      .set({ read: true })
      .where(
        and(eq(notifications.userId, userId), eq(notifications.read, false)),
      );

    log.debug("All notifications marked as read", { userId });
  }

  async deleteNotification(id: string): Promise<boolean> {
    const result = await this.db
      .delete(notifications)
      .where(eq(notifications.id, id));

    return result.changes > 0;
  }

  private mapNotification(row: Record<string, unknown>): Notification {
    const createdAtValue = row.createdAt as number | Date;
    const createdAt =
      typeof createdAtValue === "number"
        ? new Date(createdAtValue * 1000) // SQLite stores in seconds, convert to milliseconds
        : createdAtValue;

    return {
      id: String(row.id),
      userId: String(row.userId || row.user_id),
      title: String(row.title),
      message: String(row.message),
      type: String(row.type) as Notification["type"],
      read: Boolean(row.read),
      createdAt,
    };
  }
}
