/**
 * Query Builders for Demo Bookings
 *
 * This module provides high-level query builders that encapsulate
 * database query logic. Benefits:
 * - Centralized query logic (easy to modify)
 * - Type-safe queries
 * - Consistent error handling
 * - Reusable across multiple endpoints
 * - Easy to test in isolation
 *
 * @module lib/staff-dashboard/demo-bookings/queries
 */

type DatabaseClient = Record<string, unknown>;
import type { DemoBookingRecord, DemoBookingFilters, DemoBookingUpdate } from './types';
import {
  DEMO_BOOKINGS_TABLE,
  MAX_BOOKINGS_PER_QUERY,
  QUERY_CONFIG,
  DB_FIELDS,
  SEARCHABLE_FIELDS,
} from './constants';
import { getDateRangeBoundary, buildSearchPattern } from './mappers';

/**
 * Query builder result type
 *
 * Standardizes the return format from all query builders.
 */
export type QueryResult<T> = {
  data: T | null;
  error: Error | null;
  count?: number;
};

/**
 * Fetch all demo bookings with optional filters
 *
 * This is the primary query function for the staff dashboard.
 * It supports filtering by status, searching across multiple fields,
 * and filtering by date range. All filters are optional and can be combined.
 *
 * @param client - Database admin client
 * @param filters - Optional filters to apply
 * @param limit - Maximum number of results (default: 100)
 * @returns Query result with array of booking records
 *
 * @example
 *
 * if (result.error) {
 *   console.error('Query failed:', result.error);
 * } else {
 *   console.log('Found bookings:', result.data.length);
 * }
 */
export async function fetchDemoBookings(
  client: DatabaseClient,
  filters?: DemoBookingFilters,
  limit: number = MAX_BOOKINGS_PER_QUERY
): Promise<QueryResult<DemoBookingRecord[]>> {
  try {
    // Start building query - select all fields
    let query = client.from(DEMO_BOOKINGS_TABLE).select('*');

    // Apply status filter
    if (filters?.status && filters.status !== 'all') {
      query = query.eq(DB_FIELDS.STATUS, filters.status);
    }

    // Apply search filter (searches across name, email, and phone)
    if (filters?.search) {
      const pattern = buildSearchPattern(filters.search);
      const searchCondition = SEARCHABLE_FIELDS
        .map(field => `${field}.ilike.${pattern}`)
        .join(',');
      query = query.or(searchCondition);
    }

    // Apply date range filter
    if (filters?.dateRange && filters.dateRange !== 'all') {
      const startDate = getDateRangeBoundary(filters.dateRange);
      if (startDate) {
        query = query.gte(DB_FIELDS.CREATED_AT, startDate);
      }
    }

    // Apply ordering and limit
    const { data, error } = await query
      .order(QUERY_CONFIG.DEFAULT_ORDER, { ascending: QUERY_CONFIG.DEFAULT_ASCENDING })
      .limit(limit);

    if (error) {
      return { data: null, error: new Error(error.message) };
    }

    return { data: data || [], error: null, count: data?.length || 0 };
  } catch (error) {
    return {
      data: null,
      error: error instanceof Error ? error : new Error('Unknown error in fetchDemoBookings'),
    };
  }
}

/**
 * Fetch a single demo booking by ID
 *
 * Used when you need detailed information about a specific booking.
 *
 * @param client - Database admin client
 * @param bookingId - Unique booking identifier
 * @returns Query result with single booking record
 *
 * @example
 * if (result.data) {
 *   console.log('Booking:', result.data.full_name);
 * }
 */
export async function fetchDemoBookingById(
  client: DatabaseClient,
  bookingId: string
): Promise<QueryResult<DemoBookingRecord>> {
  try {
    const { data, error } = await client
      .from(DEMO_BOOKINGS_TABLE)
      .select('*')
      .eq(DB_FIELDS.ID, bookingId)
      .single();

    if (error) {
      return { data: null, error: new Error(error.message) };
    }

    return { data, error: null };
  } catch (error) {
    return {
      data: null,
      error: error instanceof Error ? error : new Error('Unknown error in fetchDemoBookingById'),
    };
  }
}

/**
 * Update a demo booking
 *
 * Updates a booking with new data. All fields in updateData are optional.
 * The function automatically handles:
 * - Setting updated_at timestamp
 * - Setting contacted_at/contacted_by for certain actions
 *
 * @param client - Database admin client
 * @param bookingId - ID of booking to update
 * @param updateData - Fields to update (snake_case format)
 * @returns Query result with updated booking record
 *
 * @example
 */
export async function updateDemoBooking(
  client: DatabaseClient,
  bookingId: string,
  updateData: DemoBookingUpdate
): Promise<QueryResult<DemoBookingRecord>> {
  try {
    const { data, error } = await client
      .from(DEMO_BOOKINGS_TABLE)
      .update(updateData)
      .eq(DB_FIELDS.ID, bookingId)
      .select()
      .single();

    if (error) {
      return { data: null, error: new Error(error.message) };
    }

    return { data, error: null };
  } catch (error) {
    return {
      data: null,
      error: error instanceof Error ? error : new Error('Unknown error in updateDemoBooking'),
    };
  }
}

/**
 * Count bookings by status
 *
 * Useful for dashboard statistics and metrics.
 *
 * @param client - Database admin client
 * @returns Object with counts by status
 *
 * @example
 * console.log(`Pending: ${counts.pending}, Confirmed: ${counts.confirmed}`);
 */
export async function countBookingsByStatus(
  client: DatabaseClient
): Promise<{
  pending: number;
  confirmed: number;
  cancelled: number;
  total: number;
  error: Error | null;
}> {
  try {
    const [pendingResult, confirmedResult, cancelledResult, totalResult] = await Promise.all([
      client.from(DEMO_BOOKINGS_TABLE).select('id', { count: 'exact', head: true }).eq(DB_FIELDS.STATUS, 'pending'),
      client.from(DEMO_BOOKINGS_TABLE).select('id', { count: 'exact', head: true }).eq(DB_FIELDS.STATUS, 'confirmed'),
      client.from(DEMO_BOOKINGS_TABLE).select('id', { count: 'exact', head: true }).eq(DB_FIELDS.STATUS, 'cancelled'),
      client.from(DEMO_BOOKINGS_TABLE).select('id', { count: 'exact', head: true }),
    ]);

    return {
      pending: pendingResult.count || 0,
      confirmed: confirmedResult.count || 0,
      cancelled: cancelledResult.count || 0,
      total: totalResult.count || 0,
      error: null,
    };
  } catch (error) {
    return {
      pending: 0,
      confirmed: 0,
      cancelled: 0,
      total: 0,
      error: error instanceof Error ? error : new Error('Unknown error in countBookingsByStatus'),
    };
  }
}

/**
 * Fetch recent bookings (last N bookings)
 *
 * Useful for dashboard widgets showing recent activity.
 *
 * @param client - Database admin client
 * @param limit - Number of recent bookings to fetch
 * @returns Query result with recent bookings
 *
 * @example
 * // Returns 5 most recent bookings
 */
export async function fetchRecentBookings(
  client: DatabaseClient,
  limit: number = 10
): Promise<QueryResult<DemoBookingRecord[]>> {
  return fetchDemoBookings(client, { status: 'all' }, limit);
}

/**
 * Fetch bookings requiring action (pending status)
 *
 * Useful for staff todo lists and notifications.
 *
 * @param client - Database admin client
 * @returns Query result with pending bookings
 *
 * @example
 * console.log(`${result.data.length} bookings need attention`);
 */
export async function fetchPendingBookings(
  client: DatabaseClient
): Promise<QueryResult<DemoBookingRecord[]>> {
  return fetchDemoBookings(client, { status: 'pending' });
}

/**
 * Search bookings by email
 *
 * Exact email lookup for finding specific customer's bookings.
 *
 * @param client - Database admin client
 * @param email - Customer email address
 * @returns Query result with matching bookings
 */
export async function fetchBookingsByEmail(
  client: DatabaseClient,
  email: string
): Promise<QueryResult<DemoBookingRecord[]>> {
  try {
    const { data, error } = await client
      .from(DEMO_BOOKINGS_TABLE)
      .select('*')
      .eq(DB_FIELDS.EMAIL, email.toLowerCase())
      .order(QUERY_CONFIG.DEFAULT_ORDER, { ascending: QUERY_CONFIG.DEFAULT_ASCENDING });

    if (error) {
      return { data: null, error: new Error(error.message) };
    }

    return { data: data || [], error: null };
  } catch (error) {
    return {
      data: null,
      error: error instanceof Error ? error : new Error('Unknown error in fetchBookingsByEmail'),
    };
  }
}

/**
 * Check if booking exists
 *
 * Quick existence check without fetching full record.
 *
 * @param client - Database admin client
 * @param bookingId - Booking ID to check
 * @returns True if booking exists, false otherwise
 */
export async function bookingExists(
  client: DatabaseClient,
  bookingId: string
): Promise<boolean> {
  try {
    const { data, error } = await client
      .from(DEMO_BOOKINGS_TABLE)
      .select('id')
      .eq(DB_FIELDS.ID, bookingId)
      .single();

    return !error && data !== null;
  } catch {
    return false;
  }
}

/**
 * Batch update multiple bookings
 *
 * Useful for bulk operations (e.g., cancel all pending from yesterday).
 *
 * @param client - Database admin client
 * @param bookingIds - Array of booking IDs to update
 * @param updateData - Update to apply to all bookings
 * @returns Number of bookings updated
 *
 * @example
 * const count = await batchUpdateBookings(
 */
export async function batchUpdateBookings(
  client: DatabaseClient,
  bookingIds: string[],
  updateData: DemoBookingUpdate
): Promise<{ count: number; error: Error | null }> {
  try {
    const { data, error } = await client
      .from(DEMO_BOOKINGS_TABLE)
      .update(updateData)
      .in(DB_FIELDS.ID, bookingIds)
      .select();

    if (error) {
      return { count: 0, error: new Error(error.message) };
    }

    return { count: data?.length || 0, error: null };
  } catch (error) {
    return {
      count: 0,
      error: error instanceof Error ? error : new Error('Unknown error in batchUpdateBookings'),
    };
  }
}
