package main

import (
	"context"
	"fmt"
	"strings"

	"zkillsusser/models"

	utils "git.site.quack-lab.dev/dave/cyutils"

	"github.com/ClickHouse/clickhouse-go/v2"
	"github.com/ClickHouse/clickhouse-go/v2/lib/driver"
	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
	"gorm.io/gorm/schema"
)

type DB interface {
	Init() error
	Get() *gorm.DB

	SaveFlatKillmails(killmails []*FlatKillmail, attackers []FlatKillmailAttacker, items []FlatKillmailItem) error
	SearchShips(query string, limit int) ([]models.InvType, error)
	SearchSystems(query string, limit int) ([]models.MapSolarSystem, error)
	SearchModules(query string, limit int) ([]models.InvType, error)
	SearchGroups(query string, limit int) ([]models.InvGroup, error)

	GetItemTypes(itemIDs []int64) ([]models.InvType, error)
	GetSolarSystems(systemIDs []int64) ([]models.MapSolarSystem, error)
	ExpandGroupsIntoItemTypeIds(groups []int64) ([]int64, error)
	GetModuleSlots(moduleIDs []int64) (map[int64]ModuleSlot, error)

	GetType(ctx context.Context, typeID int32) (*models.InvType, error)
	GetGroup(ctx context.Context, groupID int32) (*models.InvGroup, error)
	GetCategory(ctx context.Context, categoryID int32) (*models.InvCategory, error)
	GetMarketGroup(ctx context.Context, marketGroupID int32) (*models.InvMarketGroup, error)
	GetSolarSystem(ctx context.Context, systemID int32) (*models.MapSolarSystem, error)
	GetConstellation(ctx context.Context, constellationID int32) (*models.MapConstellation, error)
	GetRegion(ctx context.Context, regionID int32) (*models.MapRegion, error)

	// Analytics queries
	QueryTimeByHour(ctx context.Context, filters AnalyticsFilters) ([]TimeAggregationByHour, error)
	QueryTimeByDay(ctx context.Context, filters AnalyticsFilters) ([]TimeAggregationByDay, error)
	QueryTimeByDate(ctx context.Context, filters AnalyticsFilters) ([]TimeAggregationByDate, error)
	QueryTimeByMonth(ctx context.Context, filters AnalyticsFilters) ([]TimeAggregationByMonth, error)
	QueryLocationBySystem(ctx context.Context, filters AnalyticsFilters) ([]LocationAggregationBySystem, error)
	QueryLocationByRegion(ctx context.Context, filters AnalyticsFilters) ([]LocationAggregationByRegion, error)
	QueryLocationByConstellation(ctx context.Context, filters AnalyticsFilters) ([]LocationAggregationByConstellation, error)
	QueryLocationBySecurity(ctx context.Context, filters AnalyticsFilters) ([]LocationAggregationBySecurity, error)
	QueryShipByVictim(ctx context.Context, filters AnalyticsFilters) ([]ShipAggregationByVictimShip, error)
	QueryShipByAttacker(ctx context.Context, filters AnalyticsFilters) ([]ShipAggregationByAttackerShip, error)
	QueryPlayerByVictimCharacter(ctx context.Context, filters AnalyticsFilters) ([]PlayerAggregationByVictimCharacter, error)
	QueryPlayerByVictimCorporation(ctx context.Context, filters AnalyticsFilters) ([]PlayerAggregationByVictimCorporation, error)
	QueryPlayerByVictimAlliance(ctx context.Context, filters AnalyticsFilters) ([]PlayerAggregationByVictimAlliance, error)
	QueryPlayerByAttackerCharacter(ctx context.Context, filters AnalyticsFilters) ([]PlayerAggregationByAttackerCharacter, error)
	QueryPlayerByAttackerCorporation(ctx context.Context, filters AnalyticsFilters) ([]PlayerAggregationByAttackerCorporation, error)
	QueryPlayerByAttackerAlliance(ctx context.Context, filters AnalyticsFilters) ([]PlayerAggregationByAttackerAlliance, error)
	QueryModuleBySlotType(ctx context.Context, filters AnalyticsFilters) ([]ModuleAggregationBySlotType, error)
	QueryModuleByModule(ctx context.Context, filters AnalyticsFilters) ([]ModuleAggregationByModule, error)
	QueryModuleCoOccurrence(ctx context.Context, filters AnalyticsFilters, selectedModuleID int32, selectedSlot string) ([]ModuleCoOccurrence, error)
	QueryKillmailIDs(ctx context.Context, filters AnalyticsFilters, limit, offset int) ([]int64, error)
	QueryKillmailWithItems(ctx context.Context, killmailID int64) (*FlatKillmailComplete, error)
}

type DBWrapper struct {
	ch driver.Conn
	db *gorm.DB // For SQLite (EVE static data)

	getTypeMemo          func(context.Context, int32) (*models.InvType, error)
	getGroupMemo         func(context.Context, int32) (*models.InvGroup, error)
	getCategoryMemo      func(context.Context, int32) (*models.InvCategory, error)
	getMarketGroupMemo   func(context.Context, int32) (*models.InvMarketGroup, error)
	getSystemMemo        func(context.Context, int32) (*models.MapSolarSystem, error)
	getConstellationMemo func(context.Context, int32) (*models.MapConstellation, error)
	getRegionMemo        func(context.Context, int32) (*models.MapRegion, error)
}

var db *DBWrapper

func GetDB() (DB, error) {
	if db != nil {
		return db, nil
	}

	sdb, err := GetDBSqlite()
	if err != nil {
		return nil, fmt.Errorf("failed to connect to SQLite: %w", err)
	}

	conn, err := GetDBClickhouse()
	if err != nil {
		return nil, fmt.Errorf("failed to connect to ClickHouse: %w", err)
	}

	db = &DBWrapper{
		ch: conn,
		db: sdb,
	}

	getTypeFn := func(ctx context.Context, typeID int32) (*models.InvType, error) {
		var t models.InvType
		if err := db.db.Where("typeID = ?", typeID).First(&t).Error; err != nil {
			return nil, fmt.Errorf("failed to get type %d: %w", typeID, err)
		}
		return &t, nil
	}

	getGroupFn := func(ctx context.Context, groupID int32) (*models.InvGroup, error) {
		var g models.InvGroup
		if err := db.db.Where("groupID = ?", groupID).First(&g).Error; err != nil {
			return nil, fmt.Errorf("failed to get group %d: %w", groupID, err)
		}
		return &g, nil
	}

	getCategoryFn := func(ctx context.Context, categoryID int32) (*models.InvCategory, error) {
		var c models.InvCategory
		if err := db.db.Where("categoryID = ?", categoryID).First(&c).Error; err != nil {
			return nil, fmt.Errorf("failed to get category %d: %w", categoryID, err)
		}
		return &c, nil
	}

	getMarketGroupFn := func(ctx context.Context, marketGroupID int32) (*models.InvMarketGroup, error) {
		var mg models.InvMarketGroup
		if err := db.db.Where("marketGroupID = ?", marketGroupID).First(&mg).Error; err != nil {
			return nil, fmt.Errorf("failed to get market group %d: %w", marketGroupID, err)
		}
		return &mg, nil
	}

	getSystemFn := func(ctx context.Context, systemID int32) (*models.MapSolarSystem, error) {
		var s models.MapSolarSystem
		if err := db.db.Where("solarSystemID = ?", systemID).First(&s).Error; err != nil {
			return nil, fmt.Errorf("failed to get solar system %d: %w", systemID, err)
		}
		return &s, nil
	}

	getConstellationFn := func(ctx context.Context, constellationID int32) (*models.MapConstellation, error) {
		var c models.MapConstellation
		if err := db.db.Where("constellationID = ?", constellationID).First(&c).Error; err != nil {
			return nil, fmt.Errorf("failed to get constellation %d: %w", constellationID, err)
		}
		return &c, nil
	}

	getRegionFn := func(ctx context.Context, regionID int32) (*models.MapRegion, error) {
		var r models.MapRegion
		if err := db.db.Where("regionID = ?", regionID).First(&r).Error; err != nil {
			return nil, fmt.Errorf("failed to get region %d: %w", regionID, err)
		}
		return &r, nil
	}

	db.getTypeMemo = utils.Memoized(getTypeFn).(func(context.Context, int32) (*models.InvType, error))
	db.getGroupMemo = utils.Memoized(getGroupFn).(func(context.Context, int32) (*models.InvGroup, error))
	db.getCategoryMemo = utils.Memoized(getCategoryFn).(func(context.Context, int32) (*models.InvCategory, error))
	db.getMarketGroupMemo = utils.Memoized(getMarketGroupFn).(func(context.Context, int32) (*models.InvMarketGroup, error))
	db.getSystemMemo = utils.Memoized(getSystemFn).(func(context.Context, int32) (*models.MapSolarSystem, error))
	db.getConstellationMemo = utils.Memoized(getConstellationFn).(func(context.Context, int32) (*models.MapConstellation, error))
	db.getRegionMemo = utils.Memoized(getRegionFn).(func(context.Context, int32) (*models.MapRegion, error))

	err = db.Init()
	return db, err
}

func GetDBSqlite() (*gorm.DB, error) {
	return gorm.Open(sqlite.Open("sqlite-latest.sqlite"), &gorm.Config{
		NamingStrategy: schema.NamingStrategy{
			NoLowerCase: true,
		},
	})
}

func GetDBClickhouse() (driver.Conn, error) {
	options := &clickhouse.Options{
		Addr: []string{clickhouseHost},
		Auth: clickhouse.Auth{
			Database: clickhouseDatabase,
			Username: clickhouseUsername,
			Password: clickhousePassword,
		},
		Protocol: clickhouse.HTTP,
		Settings: clickhouse.Settings{
			"max_query_size": 100000000,
		},
	}
	return clickhouse.Open(options)
}

func (db *DBWrapper) Get() *gorm.DB {
	return db.db
}

func (db *DBWrapper) Init() error {
	return nil
}

func (db *DBWrapper) ExpandGroupsIntoItemTypeIds(groups []int64) ([]int64, error) {
	var groupTypeIDs []int64
	result := db.db.Model(&models.InvType{}).
		Select("typeID").
		Where("groupID IN ?", groups).
		Pluck("typeID", &groupTypeIDs)
	return groupTypeIDs, result.Error
}

func (db *DBWrapper) SearchShips(query string, limit int) ([]models.InvType, error) {
	var ships []models.InvType
	searchPattern := "%" + strings.ToLower(query) + "%"
	err := db.db.Table("invTypes").
		Joins("INNER JOIN invGroups ON invTypes.groupID = invGroups.groupID").
		Where("LOWER(invTypes.\"typeName\") LIKE ? AND invGroups.categoryID IN (6)", searchPattern).
		Limit(limit).
		Find(&ships).Error
	return ships, err
}

func (db *DBWrapper) SearchSystems(query string, limit int) ([]models.MapSolarSystem, error) {
	var systems []models.MapSolarSystem
	searchPattern := "%" + strings.ToLower(query) + "%"
	err := db.db.Table("mapSolarSystems").
		Where("LOWER(\"solarSystemName\") LIKE ?", searchPattern).
		Limit(limit).
		Find(&systems).Error
	return systems, err
}

func (db *DBWrapper) SearchModules(query string, limit int) ([]models.InvType, error) {
	var modules []models.InvType
	searchPattern := "%" + strings.ToLower(query) + "%"
	err := db.db.Table("invTypes").
		Joins("INNER JOIN invGroups ON invTypes.groupID = invGroups.groupID").
		Where("LOWER(invTypes.\"typeName\") LIKE ? AND invGroups.categoryID IN (7, 66)", searchPattern).
		Limit(limit).
		Find(&modules).Error
	return modules, err
}

func (db *DBWrapper) SearchGroups(query string, limit int) ([]models.InvGroup, error) {
	var groups []models.InvGroup
	searchPattern := "%" + strings.ToLower(query) + "%"
	err := db.db.Table("invGroups").
		Where("LOWER(\"groupName\") LIKE ?", searchPattern).
		Limit(limit).
		Find(&groups).Error
	return groups, err
}

func (db *DBWrapper) GetItemTypes(itemIDs []int64) ([]models.InvType, error) {
	var itemTypes []models.InvType
	res := db.db.Model(&models.InvType{}).
		Where("typeID IN ?", itemIDs).
		Find(&itemTypes)
	return itemTypes, res.Error
}

func (db *DBWrapper) GetSolarSystems(systemIDs []int64) ([]models.MapSolarSystem, error) {
	var systems []models.MapSolarSystem
	res := db.db.Model(&models.MapSolarSystem{}).
		Where("solarSystemID IN ?", systemIDs).
		Find(&systems)
	return systems, res.Error
}

func deduplicateInt64(slice []int64) []int64 {
	seen := make(map[int64]bool)
	result := make([]int64, 0, len(slice))
	for _, v := range slice {
		if !seen[v] {
			seen[v] = true
			result = append(result, v)
		}
	}
	return result
}

func (db *DBWrapper) GetModuleSlots(moduleIDs []int64) (map[int64]ModuleSlot, error) {
	if len(moduleIDs) == 0 {
		return make(map[int64]ModuleSlot), nil
	}

	var effects []models.DgmTypeEffect
	qres := db.db.Model(&models.DgmTypeEffect{}).
		Select("typeID, effectID").
		Where("typeID IN ? AND effectID IN (11, 12, 13, 2663)", moduleIDs).
		Find(&effects)
	if qres.Error != nil {
		return nil, qres.Error
	}

	result := make(map[int64]ModuleSlot)
	for _, e := range effects {
		var slot ModuleSlot
		switch e.EffectID {
		case 11:
			slot = ModuleSlotLow
		case 12:
			slot = ModuleSlotHigh
		case 13:
			slot = ModuleSlotMid
		case 2663:
			slot = ModuleSlotRig
		}
		result[int64(e.TypeID)] = slot
	}

	return result, nil
}

func (db *DBWrapper) GetType(ctx context.Context, typeID int32) (*models.InvType, error) {
	return db.getTypeMemo(ctx, typeID)
}

func (db *DBWrapper) GetGroup(ctx context.Context, groupID int32) (*models.InvGroup, error) {
	return db.getGroupMemo(ctx, groupID)
}

func (db *DBWrapper) GetCategory(ctx context.Context, categoryID int32) (*models.InvCategory, error) {
	return db.getCategoryMemo(ctx, categoryID)
}

func (db *DBWrapper) GetMarketGroup(ctx context.Context, marketGroupID int32) (*models.InvMarketGroup, error) {
	return db.getMarketGroupMemo(ctx, marketGroupID)
}

func (db *DBWrapper) GetSolarSystem(ctx context.Context, systemID int32) (*models.MapSolarSystem, error) {
	return db.getSystemMemo(ctx, systemID)
}

func (db *DBWrapper) GetConstellation(ctx context.Context, constellationID int32) (*models.MapConstellation, error) {
	return db.getConstellationMemo(ctx, constellationID)
}

func (db *DBWrapper) GetRegion(ctx context.Context, regionID int32) (*models.MapRegion, error) {
	return db.getRegionMemo(ctx, regionID)
}