package main

import (
	"context"
	"fmt"
	"strings"

	logger "git.site.quack-lab.dev/dave/cylogger"
)

// Time aggregation queries
func (db *DBWrapper) QueryTimeByHour(ctx context.Context, filters AnalyticsFilters) ([]TimeAggregationByHour, error) {
	flog := logger.Default.WithPrefix("QueryTimeByHour")
	whereClause, args := buildWhereClause(filters)

	query := fmt.Sprintf(`
		SELECT 
			kill_hour,
			count() as kill_count,
			count(DISTINCT victim_character_id) as unique_victims,
			count(DISTINCT solar_system_id) as systems_hit
		FROM zkill.killmails
		%s
		GROUP BY kill_hour
		ORDER BY kill_hour
	`, whereClause)

	flog.Debug("Executing query: %s", query)
	rows, err := db.ch.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query time by hour: %w", err)
	}
	defer rows.Close()

	var results []TimeAggregationByHour
	for rows.Next() {
		var r TimeAggregationByHour
		if err := rows.Scan(&r.KillHour, &r.KillCount, &r.UniqueVictims, &r.SystemsHit); err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}
		results = append(results, r)
	}
	return results, nil
}

func (db *DBWrapper) QueryTimeByDay(ctx context.Context, filters AnalyticsFilters) ([]TimeAggregationByDay, error) {
	flog := logger.Default.WithPrefix("QueryTimeByDay")
	whereClause, args := buildWhereClause(filters)

	query := fmt.Sprintf(`
		SELECT 
			kill_day_of_week,
			CASE kill_day_of_week
				WHEN 1 THEN 'Monday'
				WHEN 2 THEN 'Tuesday'
				WHEN 3 THEN 'Wednesday'
				WHEN 4 THEN 'Thursday'
				WHEN 5 THEN 'Friday'
				WHEN 6 THEN 'Saturday'
				WHEN 7 THEN 'Sunday'
			END as day_name,
			count() as kill_count,
			count(DISTINCT victim_character_id) as unique_victims
		FROM zkill.killmails
		%s
		GROUP BY kill_day_of_week
		ORDER BY kill_day_of_week
	`, whereClause)

	flog.Debug("Executing query: %s", query)
	rows, err := db.ch.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query time by day: %w", err)
	}
	defer rows.Close()

	var results []TimeAggregationByDay
	for rows.Next() {
		var r TimeAggregationByDay
		if err := rows.Scan(&r.KillDayOfWeek, &r.DayName, &r.KillCount, &r.UniqueVictims); err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}
		results = append(results, r)
	}
	return results, nil
}

func (db *DBWrapper) QueryTimeByDate(ctx context.Context, filters AnalyticsFilters) ([]TimeAggregationByDate, error) {
	flog := logger.Default.WithPrefix("QueryTimeByDate")
	whereClause, args := buildWhereClause(filters)

	query := fmt.Sprintf(`
		SELECT 
			toString(kill_date) as kill_date,
			count() as kill_count,
			count(DISTINCT victim_character_id) as unique_victims,
			count(DISTINCT solar_system_id) as systems_hit
		FROM zkill.killmails
		%s
		GROUP BY kill_date
		ORDER BY kill_date DESC
	`, whereClause)

	flog.Debug("Executing query: %s", query)
	rows, err := db.ch.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query time by date: %w", err)
	}
	defer rows.Close()

	var results []TimeAggregationByDate
	for rows.Next() {
		var r TimeAggregationByDate
		if err := rows.Scan(&r.KillDate, &r.KillCount, &r.UniqueVictims, &r.SystemsHit); err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}
		results = append(results, r)
	}
	return results, nil
}

func (db *DBWrapper) QueryTimeByMonth(ctx context.Context, filters AnalyticsFilters) ([]TimeAggregationByMonth, error) {
	flog := logger.Default.WithPrefix("QueryTimeByMonth")
	whereClause, args := buildWhereClause(filters)

	query := fmt.Sprintf(`
		SELECT 
			toString(toYYYYMM(killmail_time)) as month,
			count() as kill_count,
			count(DISTINCT victim_character_id) as unique_victims
		FROM zkill.killmails
		%s
		GROUP BY month
		ORDER BY month DESC
	`, whereClause)

	flog.Debug("Executing query: %s", query)
	rows, err := db.ch.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query time by month: %w", err)
	}
	defer rows.Close()

	var results []TimeAggregationByMonth
	for rows.Next() {
		var r TimeAggregationByMonth
		if err := rows.Scan(&r.Month, &r.KillCount, &r.UniqueVictims); err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}
		results = append(results, r)
	}
	return results, nil
}

// Location aggregation queries
func (db *DBWrapper) QueryLocationBySystem(ctx context.Context, filters AnalyticsFilters) ([]LocationAggregationBySystem, error) {
	flog := logger.Default.WithPrefix("QueryLocationBySystem")
	whereClause, args := buildWhereClause(filters)

	query := fmt.Sprintf(`
		SELECT 
			solar_system_id,
			solar_system_name,
			region_name,
			security,
			count() as kill_count,
			count(DISTINCT victim_character_id) as unique_victims,
			count(DISTINCT victim_ship_type_name) as ship_variety
		FROM zkill.killmails
		%s
		GROUP BY solar_system_id, solar_system_name, region_name, security
		ORDER BY kill_count DESC
	`, whereClause)

	flog.Debug("Executing query: %s", query)
	rows, err := db.ch.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query location by system: %w", err)
	}
	defer rows.Close()

	var results []LocationAggregationBySystem
	for rows.Next() {
		var r LocationAggregationBySystem
		if err := rows.Scan(&r.SolarSystemID, &r.SolarSystemName, &r.RegionName, &r.Security, &r.KillCount, &r.UniqueVictims, &r.ShipVariety); err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}
		results = append(results, r)
	}
	return results, nil
}

func (db *DBWrapper) QueryLocationByRegion(ctx context.Context, filters AnalyticsFilters) ([]LocationAggregationByRegion, error) {
	flog := logger.Default.WithPrefix("QueryLocationByRegion")
	whereClause, args := buildWhereClause(filters)

	query := fmt.Sprintf(`
		SELECT 
			region_name,
			count() as kill_count,
			count(DISTINCT solar_system_name) as systems_with_kills,
			count(DISTINCT victim_character_id) as unique_victims
		FROM zkill.killmails
		%s
		GROUP BY region_name
		ORDER BY kill_count DESC
	`, whereClause)

	flog.Debug("Executing query: %s", query)
	rows, err := db.ch.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query location by region: %w", err)
	}
	defer rows.Close()

	var results []LocationAggregationByRegion
	for rows.Next() {
		var r LocationAggregationByRegion
		if err := rows.Scan(&r.RegionName, &r.KillCount, &r.SystemsWithKills, &r.UniqueVictims); err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}
		results = append(results, r)
	}
	return results, nil
}

func (db *DBWrapper) QueryLocationByConstellation(ctx context.Context, filters AnalyticsFilters) ([]LocationAggregationByConstellation, error) {
	flog := logger.Default.WithPrefix("QueryLocationByConstellation")
	whereClause, args := buildWhereClause(filters)

	query := fmt.Sprintf(`
		SELECT 
			constellation_name,
			region_name,
			count() as kill_count,
			count(DISTINCT solar_system_name) as systems_in_constellation
		FROM zkill.killmails
		%s
		GROUP BY constellation_name, region_name
		ORDER BY kill_count DESC
	`, whereClause)

	flog.Debug("Executing query: %s", query)
	rows, err := db.ch.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query location by constellation: %w", err)
	}
	defer rows.Close()

	var results []LocationAggregationByConstellation
	for rows.Next() {
		var r LocationAggregationByConstellation
		if err := rows.Scan(&r.ConstellationName, &r.RegionName, &r.KillCount, &r.SystemsInConstellation); err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}
		results = append(results, r)
	}
	return results, nil
}

func (db *DBWrapper) QueryLocationBySecurity(ctx context.Context, filters AnalyticsFilters) ([]LocationAggregationBySecurity, error) {
	flog := logger.Default.WithPrefix("QueryLocationBySecurity")
	whereClause, args := buildWhereClause(filters)

	query := fmt.Sprintf(`
		SELECT 
			CASE 
				WHEN security >= 0.5 THEN 'High Sec'
				WHEN security > 0.0 THEN 'Low Sec'
				ELSE 'Null Sec'
			END as sec_status,
			count() as kill_count,
			count(DISTINCT solar_system_name) as systems
		FROM zkill.killmails
		%s
		GROUP BY sec_status
		ORDER BY kill_count DESC
	`, whereClause)

	flog.Debug("Executing query: %s", query)
	rows, err := db.ch.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query location by security: %w", err)
	}
	defer rows.Close()

	var results []LocationAggregationBySecurity
	for rows.Next() {
		var r LocationAggregationBySecurity
		if err := rows.Scan(&r.SecStatus, &r.KillCount, &r.Systems); err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}
		results = append(results, r)
	}
	return results, nil
}

// Ship aggregation queries
func (db *DBWrapper) QueryShipByVictim(ctx context.Context, filters AnalyticsFilters) ([]ShipAggregationByVictimShip, error) {
	flog := logger.Default.WithPrefix("QueryShipByVictim")
	whereClause, args := buildWhereClause(filters)

	query := fmt.Sprintf(`
		SELECT 
			victim_ship_type_id,
			victim_ship_type_name,
			victim_ship_group_name,
			victim_ship_category_name,
			count() as kill_count,
			count(DISTINCT victim_character_id) as unique_pilots_killed
		FROM zkill.killmails
		%s
		GROUP BY victim_ship_type_id, victim_ship_type_name, victim_ship_group_name, victim_ship_category_name
		ORDER BY kill_count DESC
	`, whereClause)

	flog.Debug("Executing query: %s", query)
	rows, err := db.ch.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query ship by victim: %w", err)
	}
	defer rows.Close()

	var results []ShipAggregationByVictimShip
	for rows.Next() {
		var r ShipAggregationByVictimShip
		if err := rows.Scan(&r.ShipTypeID, &r.ShipTypeName, &r.ShipGroupName, &r.ShipCategoryName, &r.KillCount, &r.UniquePilots); err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}
		results = append(results, r)
	}
	return results, nil
}

func (db *DBWrapper) QueryShipByAttacker(ctx context.Context, filters AnalyticsFilters) ([]ShipAggregationByAttackerShip, error) {
	flog := logger.Default.WithPrefix("QueryShipByAttacker")
	killmailSubquery, killmailArgs := getKillmailIDSubquery(filters)
	whereClause, whereArgs := buildAttackerWhereClause(filters)

	args := append(killmailArgs, whereArgs...)
	query := fmt.Sprintf(`
		SELECT 
			ship_type_name,
			ship_group_name,
			count() as times_used,
			count(DISTINCT killmail_id) as kills_participated,
			sum(damage_done) as total_damage,
			countIf(final_blow) as final_blows
		FROM zkill.killmail_attackers
		WHERE killmail_id IN %s
		%s
		GROUP BY ship_type_name, ship_group_name
		ORDER BY kills_participated DESC
	`, killmailSubquery, whereClause)

	flog.Debug("Executing query: %s", query)
	rows, err := db.ch.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query ship by attacker: %w", err)
	}
	defer rows.Close()

	var results []ShipAggregationByAttackerShip
	for rows.Next() {
		var r ShipAggregationByAttackerShip
		if err := rows.Scan(&r.ShipTypeName, &r.ShipGroupName, &r.TimesUsed, &r.KillsParticipated, &r.TotalDamage, &r.FinalBlows); err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}
		results = append(results, r)
	}
	return results, nil
}

// Player aggregation queries
func (db *DBWrapper) QueryPlayerByVictimCharacter(ctx context.Context, filters AnalyticsFilters) ([]PlayerAggregationByVictimCharacter, error) {
	flog := logger.Default.WithPrefix("QueryPlayerByVictimCharacter")
	whereClause, args := buildWhereClause(filters)

	query := fmt.Sprintf(`
		SELECT 
			victim_character_name,
			victim_corporation_name,
			victim_alliance_name,
			count() as deaths,
			count(DISTINCT victim_ship_type_name) as ships_lost
		FROM zkill.killmails
		%s
		GROUP BY victim_character_name, victim_corporation_name, victim_alliance_name
		ORDER BY deaths DESC
	`, whereClause)

	flog.Debug("Executing query: %s", query)
	rows, err := db.ch.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query player by victim character: %w", err)
	}
	defer rows.Close()

	var results []PlayerAggregationByVictimCharacter
	for rows.Next() {
		var r PlayerAggregationByVictimCharacter
		if err := rows.Scan(&r.CharacterName, &r.Corporation, &r.Alliance, &r.Deaths, &r.ShipsLost); err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}
		results = append(results, r)
	}
	return results, nil
}

func (db *DBWrapper) QueryPlayerByVictimCorporation(ctx context.Context, filters AnalyticsFilters) ([]PlayerAggregationByVictimCorporation, error) {
	flog := logger.Default.WithPrefix("QueryPlayerByVictimCorporation")
	whereClause, args := buildWhereClause(filters)

	query := fmt.Sprintf(`
		SELECT 
			victim_corporation_name,
			victim_alliance_name,
			count() as losses,
			count(DISTINCT victim_character_name) as members_killed
		FROM zkill.killmails
		%s
		GROUP BY victim_corporation_name, victim_alliance_name
		ORDER BY losses DESC
	`, whereClause)

	flog.Debug("Executing query: %s", query)
	rows, err := db.ch.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query player by victim corporation: %w", err)
	}
	defer rows.Close()

	var results []PlayerAggregationByVictimCorporation
	for rows.Next() {
		var r PlayerAggregationByVictimCorporation
		if err := rows.Scan(&r.Corporation, &r.Alliance, &r.Losses, &r.MembersKilled); err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}
		results = append(results, r)
	}
	return results, nil
}

func (db *DBWrapper) QueryPlayerByVictimAlliance(ctx context.Context, filters AnalyticsFilters) ([]PlayerAggregationByVictimAlliance, error) {
	flog := logger.Default.WithPrefix("QueryPlayerByVictimAlliance")
	whereClause, args := buildWhereClause(filters)

	whereWithAlliance := whereClause
	if whereClause == "" {
		whereWithAlliance = "WHERE victim_alliance_name != ''"
	} else {
		whereWithAlliance = whereClause + " AND victim_alliance_name != ''"
	}

	query := fmt.Sprintf(`
		SELECT 
			victim_alliance_name,
			count() as losses,
			count(DISTINCT victim_corporation_name) as corps_hit,
			count(DISTINCT victim_character_name) as members_killed
		FROM zkill.killmails
		%s
		GROUP BY victim_alliance_name
		ORDER BY losses DESC
	`, whereWithAlliance)

	flog.Debug("Executing query: %s", query)
	rows, err := db.ch.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query player by victim alliance: %w", err)
	}
	defer rows.Close()

	var results []PlayerAggregationByVictimAlliance
	for rows.Next() {
		var r PlayerAggregationByVictimAlliance
		if err := rows.Scan(&r.Alliance, &r.Losses, &r.CorpsHit, &r.MembersKilled); err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}
		results = append(results, r)
	}
	return results, nil
}

func (db *DBWrapper) QueryPlayerByAttackerCharacter(ctx context.Context, filters AnalyticsFilters) ([]PlayerAggregationByAttackerCharacter, error) {
	flog := logger.Default.WithPrefix("QueryPlayerByAttackerCharacter")
	killmailSubquery, killmailArgs := getKillmailIDSubquery(filters)
	whereClause, whereArgs := buildAttackerWhereClause(filters)

	args := append(killmailArgs, whereArgs...)
	query := fmt.Sprintf(`
		SELECT 
			character_name,
			corporation_name,
			alliance_name,
			count(DISTINCT killmail_id) as kills_participated,
			countIf(final_blow) as final_blows,
			sum(damage_done) as total_damage
		FROM zkill.killmail_attackers
		WHERE killmail_id IN %s
		%s
		GROUP BY character_name, corporation_name, alliance_name
		ORDER BY kills_participated DESC
	`, killmailSubquery, whereClause)

	flog.Debug("Executing query: %s", query)
	rows, err := db.ch.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query player by attacker character: %w", err)
	}
	defer rows.Close()

	var results []PlayerAggregationByAttackerCharacter
	for rows.Next() {
		var r PlayerAggregationByAttackerCharacter
		if err := rows.Scan(&r.CharacterName, &r.Corporation, &r.Alliance, &r.KillsParticipated, &r.FinalBlows, &r.TotalDamage); err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}
		results = append(results, r)
	}
	return results, nil
}

func (db *DBWrapper) QueryPlayerByAttackerCorporation(ctx context.Context, filters AnalyticsFilters) ([]PlayerAggregationByAttackerCorporation, error) {
	flog := logger.Default.WithPrefix("QueryPlayerByAttackerCorporation")
	killmailSubquery, killmailArgs := getKillmailIDSubquery(filters)
	whereClause, whereArgs := buildAttackerWhereClause(filters)

	args := append(killmailArgs, whereArgs...)
	query := fmt.Sprintf(`
		SELECT 
			corporation_name,
			alliance_name,
			count(DISTINCT killmail_id) as kills_participated,
			count(DISTINCT character_name) as members_involved
		FROM zkill.killmail_attackers
		WHERE killmail_id IN %s
		%s
		GROUP BY corporation_name, alliance_name
		ORDER BY kills_participated DESC
	`, killmailSubquery, whereClause)

	flog.Debug("Executing query: %s", query)
	rows, err := db.ch.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query player by attacker corporation: %w", err)
	}
	defer rows.Close()

	var results []PlayerAggregationByAttackerCorporation
	for rows.Next() {
		var r PlayerAggregationByAttackerCorporation
		if err := rows.Scan(&r.Corporation, &r.Alliance, &r.KillsParticipated, &r.MembersInvolved); err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}
		results = append(results, r)
	}
	return results, nil
}

func (db *DBWrapper) QueryPlayerByAttackerAlliance(ctx context.Context, filters AnalyticsFilters) ([]PlayerAggregationByAttackerAlliance, error) {
	flog := logger.Default.WithPrefix("QueryPlayerByAttackerAlliance")
	killmailSubquery, killmailArgs := getKillmailIDSubquery(filters)
	whereClause, whereArgs := buildAttackerWhereClause(filters)

	args := append(killmailArgs, whereArgs...)
	query := fmt.Sprintf(`
		SELECT 
			alliance_name,
			count(DISTINCT killmail_id) as kills_participated,
			count(DISTINCT corporation_name) as corps_involved,
			count(DISTINCT character_name) as members_involved
		FROM zkill.killmail_attackers
		WHERE killmail_id IN %s
		%s AND alliance_name != ''
		GROUP BY alliance_name
		ORDER BY kills_participated DESC
	`, killmailSubquery, whereClause)

	flog.Debug("Executing query: %s", query)
	rows, err := db.ch.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query player by attacker alliance: %w", err)
	}
	defer rows.Close()

	var results []PlayerAggregationByAttackerAlliance
	for rows.Next() {
		var r PlayerAggregationByAttackerAlliance
		if err := rows.Scan(&r.Alliance, &r.KillsParticipated, &r.CorpsInvolved, &r.MembersInvolved); err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}
		results = append(results, r)
	}
	return results, nil
}

// Module aggregation queries
func (db *DBWrapper) QueryModuleBySlotType(ctx context.Context, filters AnalyticsFilters) ([]ModuleAggregationBySlotType, error) {
	flog := logger.Default.WithPrefix("QueryModuleBySlotType")
	killmailSubquery, killmailArgs := getKillmailIDSubquery(filters)

	var slotTypeFilter string
	var slotArgs []interface{}
	if len(filters.SlotType) > 0 {
		placeholders := make([]string, len(filters.SlotType))
		for i := range placeholders {
			placeholders[i] = "?"
		}
		slotTypeFilter = "AND slot_type IN (" + strings.Join(placeholders, ",") + ")"
		for _, s := range filters.SlotType {
			slotArgs = append(slotArgs, s)
		}
	}

	args := append(killmailArgs, slotArgs...)
	query := fmt.Sprintf(`
		SELECT 
			slot_type,
			count() as items_fitted,
			count(DISTINCT killmail_id) as ships_with_slot,
			count(DISTINCT item_type_name) as module_variety
		FROM zkill.killmail_items
		WHERE killmail_id IN %s
		%s
		GROUP BY slot_type
		ORDER BY items_fitted DESC
	`, killmailSubquery, slotTypeFilter)

	flog.Debug("Executing query: %s", query)
	rows, err := db.ch.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query module by slot type: %w", err)
	}
	defer rows.Close()

	var results []ModuleAggregationBySlotType
	for rows.Next() {
		var r ModuleAggregationBySlotType
		if err := rows.Scan(&r.SlotType, &r.ItemsFitted, &r.ShipsWithSlot, &r.ModuleVariety); err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}
		results = append(results, r)
	}
	return results, nil
}

func (db *DBWrapper) QueryModuleByModule(ctx context.Context, filters AnalyticsFilters) ([]ModuleAggregationByModule, error) {
	flog := logger.Default.WithPrefix("QueryModuleByModule")
	killmailSubquery, killmailArgs := getKillmailIDSubquery(filters)

	var slotTypeFilter string
	var slotArgs []interface{}
	if len(filters.SlotType) > 0 {
		placeholders := make([]string, len(filters.SlotType))
		for i := range placeholders {
			placeholders[i] = "?"
		}
		slotTypeFilter = "AND slot_type IN (" + strings.Join(placeholders, ",") + ")"
		for _, s := range filters.SlotType {
			slotArgs = append(slotArgs, s)
		}
	}

	args := append(killmailArgs, slotArgs...)
	query := fmt.Sprintf(`
		SELECT 
			item_type_id,
			item_type_name,
			item_group_name,
			item_category_name,
			count() as times_fitted,
			count(DISTINCT killmail_id) as ships_with_module
		FROM zkill.killmail_items
		WHERE killmail_id IN %s
		%s
		GROUP BY item_type_id, item_type_name, item_group_name, item_category_name
		ORDER BY times_fitted DESC
	`, killmailSubquery, slotTypeFilter)

	flog.Debug("Executing query: %s", query)
	rows, err := db.ch.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query module by module: %w", err)
	}
	defer rows.Close()

	var results []ModuleAggregationByModule
	for rows.Next() {
		var r ModuleAggregationByModule
		if err := rows.Scan(&r.ItemTypeID, &r.ItemTypeName, &r.ItemGroupName, &r.ItemCategoryName, &r.TimesFitted, &r.ShipsWithModule); err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}
		results = append(results, r)
	}
	return results, nil
}

func (db *DBWrapper) QueryModuleCoOccurrence(ctx context.Context, filters AnalyticsFilters, selectedModuleID int32, selectedSlot string) ([]ModuleCoOccurrence, error) {
	flog := logger.Default.WithPrefix("QueryModuleCoOccurrence")
	killmailSubquery, killmailArgs := getKillmailIDSubquery(filters)

	args := append(killmailArgs, selectedModuleID, selectedSlot)
	query := fmt.Sprintf(`
		WITH selected_ships AS (
			SELECT DISTINCT killmail_id
			FROM zkill.killmail_items
			WHERE item_type_id = ?
			AND slot_type = ?
			AND killmail_id IN %s
		)
		SELECT 
			item_type_name,
			slot_type,
			count() as times_fitted_together,
			count() * 100.0 / (SELECT count() FROM selected_ships) as percentage
		FROM zkill.killmail_items
		WHERE killmail_id IN selected_ships
		AND NOT (item_type_id = ? AND slot_type = ?)
		GROUP BY item_type_name, slot_type
		ORDER BY times_fitted_together DESC
	`, killmailSubquery)

	args = append(args, selectedModuleID, selectedSlot)
	flog.Debug("Executing query: %s", query)
	rows, err := db.ch.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query module co-occurrence: %w", err)
	}
	defer rows.Close()

	var results []ModuleCoOccurrence
	for rows.Next() {
		var r ModuleCoOccurrence
		if err := rows.Scan(&r.ItemTypeName, &r.SlotType, &r.TimesTogether, &r.Percentage); err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}
		results = append(results, r)
	}
	return results, nil
}

func (db *DBWrapper) QueryKillmailIDs(ctx context.Context, filters AnalyticsFilters, limit, offset int) ([]int64, error) {
	flog := logger.Default.WithPrefix("QueryKillmailIDs")
	whereClause, args := buildWhereClause(filters)

	query := fmt.Sprintf(`
		SELECT killmail_id
		FROM zkill.killmails
		%s
		ORDER BY killmail_id DESC
		LIMIT ? OFFSET ?
	`, whereClause)

	args = append(args, limit, offset)
	flog.Debug("Executing query: %s", query)
	rows, err := db.ch.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to query killmail IDs: %w", err)
	}
	defer rows.Close()

	var results []int64
	for rows.Next() {
		var id int64
		if err := rows.Scan(&id); err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}
		results = append(results, id)
	}
	return results, nil
}

func (db *DBWrapper) QueryKillmailWithItems(ctx context.Context, killmailID int64) (*FlatKillmailComplete, error) {
	flog := logger.Default.WithPrefix("QueryKillmailWithItems")

	query := `
		SELECT 
			k.killmail_id,
			k.killmail_hash,
			toString(k.killmail_time) as killmail_time,
			k.solar_system_id,
			k.solar_system_name,
			k.constellation_name,
			k.region_name,
			k.security,
			k.victim_character_id,
			k.victim_character_name,
			k.victim_corporation_id,
			k.victim_corporation_name,
			k.victim_alliance_id,
			k.victim_alliance_name,
			k.victim_ship_type_id,
			k.victim_ship_type_name,
			k.victim_ship_group_name,
			k.victim_ship_category_name,
			k.victim_damage_taken,
			k.attacker_count,
			toString(k.http_last_modified) as http_last_modified,
			ki.item_type_id,
			ki.item_type_name,
			ki.item_group_name,
			ki.item_category_name,
			ki.item_market_group_name,
			ki.flag,
			ki.slot_type,
			ki.quantity_destroyed,
			ki.quantity_dropped,
			ki.singleton
		FROM zkill.killmails k
		LEFT JOIN zkill.killmail_items ki ON k.killmail_id = ki.killmail_id
		WHERE k.killmail_id = ?
		AND (ki.slot_type == 'high' or ki.slot_type == 'mid' or ki.slot_type == 'low' or ki.slot_type == 'rig' or ki.slot_type == 'subsystem' or ki.slot_type == 'drone_bay')
		ORDER BY ki.flag
	`

	rows, err := db.ch.Query(ctx, query, killmailID)
	if err != nil {
		return nil, fmt.Errorf("failed to query killmail: %w", err)
	}
	defer rows.Close()

	var result *FlatKillmailComplete
	var items []FlatKillmailItem

	for rows.Next() {
		var km FlatKillmail
		var item FlatKillmailItem
		var itemTypeID int32
		var itemTypeName string
		var itemGroupName string
		var itemCategoryName string
		var itemMarketGroupName string
		var flag int32
		var slotType string
		var quantityDestroyed int64
		var quantityDropped int64
		var singleton int32

		if err := rows.Scan(
			&km.KillmailID,
			&km.KillmailHash,
			&km.KillmailTime,
			&km.SolarSystemID,
			&km.SolarSystemName,
			&km.ConstellationName,
			&km.RegionName,
			&km.Security,
			&km.VictimCharacterID,
			&km.VictimCharacterName,
			&km.VictimCorporationID,
			&km.VictimCorporationName,
			&km.VictimAllianceID,
			&km.VictimAllianceName,
			&km.VictimShipTypeID,
			&km.VictimShipTypeName,
			&km.VictimShipGroupName,
			&km.VictimShipCategoryName,
			&km.VictimDamageTaken,
			&km.AttackerCount,
			&km.HTTPLastModified,
			&itemTypeID,
			&itemTypeName,
			&itemGroupName,
			&itemCategoryName,
			&itemMarketGroupName,
			&flag,
			&slotType,
			&quantityDestroyed,
			&quantityDropped,
			&singleton,
		); err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}

		if result == nil {
			result = &FlatKillmailComplete{
				FlatKillmail: km,
				Items:        []FlatKillmailItem{},
			}
		}

		if itemTypeID != 0 {
			item.KillmailID = km.KillmailID
			item.ItemTypeID = itemTypeID
			item.ItemTypeName = itemTypeName
			item.ItemGroupName = itemGroupName
			item.ItemCategoryName = itemCategoryName
			item.ItemMarketGroupName = itemMarketGroupName
			item.Flag = flag
			item.SlotType = slotType
			item.QuantityDestroyed = quantityDestroyed
			item.QuantityDropped = quantityDropped
			item.Singleton = singleton
			items = append(items, item)
		}
	}

	if result == nil {
		return nil, fmt.Errorf("killmail not found: %d", killmailID)
	}

	result.Items = items
	flog.Info("Query returned killmail %d with %d items", killmailID, len(items))
	return result, nil
}