package repositories

import (
	"astrology-api/dto"
	"strings"

	"gorm.io/gorm"
)

const BaseURL = "https://api.astrology.togwe.com/"

type AstrologerRepository interface {
	GetAstrologers(req dto.AstrologerListRequest) ([]dto.AstrologerResponse, error)

	SearchAstrologers(req dto.SearchAstrologerRequest) ([]dto.SearchAstrologerResponse, error)

	GetAstrologerByID(req dto.GetAstrologerByIDRequest) (*dto.GetAstrologerByIDResponse, error)

	// GetTopAstrologers ranks every visible astrologer by rating and
	// attended chats/calls (astrologer_top_repository.go).
	GetTopAstrologers(limit int) ([]dto.TopAstrologerResponse, error)
}

type astrologerRepository struct {
	db *gorm.DB
}

func NewAstrologerRepository(db *gorm.DB) AstrologerRepository {
	return &astrologerRepository{
		db: db,
	}
}

// Temporary implementation
func (r *astrologerRepository) GetAstrologers(req dto.AstrologerListRequest) ([]dto.AstrologerResponse, error) {

	var astrologers []dto.AstrologerBrief

	ratingQuery := r.ratingSubQuery()

	chatQuery := r.chatSummarySubQuery()

	callQuery := r.callSummarySubQuery()

	query := r.db.Table("astrologers AS a").Select(` a.id, a.name, a.profileImage,a.charge,
		a.audioCallRate, a.videoCallRate, a.reportRate, a.experience,
		a.primarySkill,
		a.allSkill,
		a.languageKnown,
		a.astrologerCategoryId,
		a.is_available,
		a.isVerified,

		IFNULL(r.averageRating,0) as averageRating,
		IFNULL(r.ratingCount,0) as ratingCount,

		IFNULL(ch.chatOrders,0) as chatOrders,
		IFNULL(ch.chatMinutes,0) as chatMinutes,

		IFNULL(cl.callOrders,0) as callOrders,
		IFNULL(cl.callMinutes,0) as callMinutes
	`).
		Joins(`
		LEFT JOIN (?) r
		ON r.astrologerId = a.id
	`, ratingQuery).
		Joins(`
		LEFT JOIN (?) ch
		ON ch.astrologerId = a.id
	`, chatQuery).
		Joins(`
		LEFT JOIN (?) cl
		ON cl.astrologerId = a.id
	`, callQuery)

	//-------------------------------------------------
	// Active Astrologers Only
	//-------------------------------------------------

	query = query.
		Where("isActive = ?", 1).
		Where("isDelete = ?", 0).
		Where("isVerified = ?", 1)

	//-------------------------------------------------
	// Search
	//-------------------------------------------------

	if strings.TrimSpace(req.Search) != "" {
		keyword := "%" + strings.TrimSpace(req.Search) + "%"
		query = query.Where(`a.name LIKE ? OR a.email LIKE ? OR a.contactNo LIKE ?`, keyword, keyword, keyword)
	}

	//-------------------------------------------------
	// Chat / Call Filter
	//-------------------------------------------------

	switch strings.ToLower(req.Type) {

	case "chat":

		query = query.Where("a.charge >= 0")

	case "call":

		query = query.Where("a.audioCallRate  >= 0")

	case "video":

		query = query.Where("a.videoCallRate >= 0")

	case "report":

		query = query.Where("a.reportRate > =0")
	}

	//-------------------------------------------------
	// Category Filter
	//-------------------------------------------------

	if req.CategoryID > 0 {

		query = query.Where(
			"FIND_IN_SET(?, astrologerCategoryId)",
			req.CategoryID,
		)
	}

	//-------------------------------------------------
	// Language Filter
	//-------------------------------------------------

	if req.LanguageID > 0 {

		query = query.Where(
			"FIND_IN_SET(?, languageKnown)",
			req.LanguageID,
		)
	}

	//-------------------------------------------------
	// Skill Filter
	//-------------------------------------------------

	if req.SkillID > 0 {

		query = query.Where(
			"FIND_IN_SET(?, allSkill)",
			req.SkillID,
		)
	}

	//-------------------------------------------------
	// Pagination
	//-------------------------------------------------

	limit := req.Limit

	if limit <= 0 {
		limit = 20
	}

	start := req.Start

	if start < 0 {
		start = 0
	}

	query = query.
		Offset(start).
		Limit(limit)

	//-------------------------------------------------
	// Default Sorting
	//-------------------------------------------------

	query = query.Order("id DESC")

	//-------------------------------------------------
	// Execute
	//-------------------------------------------------

	if err := query.Find(&astrologers).Error; err != nil {

		return nil, err
	}

	//-------------------------------------------------
	// Response Mapping
	//-------------------------------------------------

	response := make([]dto.AstrologerResponse, 0)

	for _, astro := range astrologers {

		rating, ratingCount := r.getRating(astro.ID)

		chatOrders := r.getChatOrders(astro.ID)

		callOrders := r.getCallOrders(astro.ID)

		primarySkill, skills := r.resolveSkillNames(
			astro.ID,
			astro.PrimarySkill,
			astro.AllSkill,
		)

		languages := r.resolveLanguageNames(astro.ID, astro.LanguageKnown)

		categories := r.resolveCategoryNames(astro.AstrologerCategory)

		busy := r.isBusy(astro.ID)

		freeChat := r.isFreeChatAvailable(astro.ID)

		response = append(response, dto.AstrologerResponse{

			ID:           astro.ID,
			Name:         astro.Name,
			ProfileImage: astro.ProfileImage,
			Experience:   astro.Experience,
			Charge:       astro.Charge,

			ChatRate: astro.ChatRate,

			CallRate:           astro.CallRate,
			VideoCallRate:      astro.VideoCallRate,
			ReportRate:         astro.ReportRate,
			Rating:             rating,
			RatingCount:        int(ratingCount),
			TotalOrder:         int(chatOrders + callOrders),
			PrimarySkill:       primarySkill,
			AllSkill:           skills,
			LanguageKnown:      languages,
			Category:           categories,
			IsVerified:         astro.IsVerified,
			IsOnline:           astro.IsAvailable != 0,
			IsProfileCompleted: astro.IsProfileCompleted,
			IsBusy:             busy,
			IsFreeAvailable:    freeChat,
		})
	}

	return response, nil
}

type AstrologerResponse struct {
	ID            uint   `json:"id"`
	Name          string `json:"name"`
	ProfileImage  string `json:"profileImage"`
	Experience    int    `json:"experience"`
	Charge        int    `json:"charge"`
	ChatRate      int    `json:"chatRate"`
	CallRate      int    `json:"callRate"`
	VideoCallRate int    `json:"videoCallRate"`
	ReportRate    int    `json:"reportRate"`

	Rating      float64 `json:"rating"`
	RatingCount int64   `json:"ratingCount"`

	ChatOrders int64 `json:"chatOrders"`
	CallOrders int64 `json:"callOrders"`

	ChatMinutes float64 `json:"chatMinutes"`
	CallMinutes float64 `json:"callMinutes"`

	PrimarySkill  string `json:"primarySkill"`
	AllSkill      string `json:"allSkill"`
	LanguageKnown string `json:"languageKnown"`
	Category      string `json:"category"`

	IsVerified      bool `json:"isVerified"`
	IsOnline        bool `json:"isOnline"`
	IsBusy          bool `json:"isBusy"`
	IsFreeAvailable bool `json:"isFreeAvailable"`

	TotalOrder int64 `json:"totalOrder"`
}

func (r *astrologerRepository) getRating(astrologerID uint) (float64, int64) {

	var avg float64
	var count int64

	r.db.
		Table("user_reviews").
		Where("astrologerId = ?", astrologerID).
		Select("IFNULL(AVG(rating),0)").
		Scan(&avg)

	r.db.
		Table("user_reviews").
		Where("astrologerId = ?", astrologerID).
		Count(&count)

	return avg, count
}

func (r *astrologerRepository) getChatOrders(astrologerID uint) int64 {

	var total int64
	total = 0
	r.db.
		Table("chatrequest").
		Where("astrologerId = ?", astrologerID).
		Where("chatStatus = ?", "Completed").
		Count(&total)

	return total
}

func (r *astrologerRepository) getChatMinutes(astrologerID uint) float64 {

	var total float64

	r.db.
		Table("chatrequest").
		Where("astrologerId = ?", astrologerID).
		Where("chatStatus = ?", "Completed").
		Select("IFNULL(SUM(CAST(totalMin AS DECIMAL(10,2))),0)").
		Scan(&total)

	return total
}

func (r *astrologerRepository) getCallMinutes(astrologerID uint) float64 {

	var total float64

	r.db.
		Table("callrequest").
		Where("astrologerId = ?", astrologerID).
		Where("callStatus = ?", "Completed").
		Select("IFNULL(SUM(CAST(totalMin AS DECIMAL(10,2))),0)").
		Scan(&total)

	return total
}

func (r *astrologerRepository) getCallOrders(astrologerID uint) int64 {

	var total int64

	r.db.
		Table("callrequest").
		Where("astrologerId = ?", astrologerID).
		Where("callStatus = ?", "Completed").
		Count(&total)

	return total
}

// The astrologers row carries denormalised id lists — primarySkill, allSkill,
// languageKnown, astrologerCategoryId — but the astrologer's actual selection
// lives in the astrologer_languages and astrologer_skills join tables that the
// profile steps write. Those columns were left empty (or, before step 1 was
// fixed, filled with free text such as "Hindi"), so getSkillNames /
// getLanguageNames resolved nothing and all four fields came back "".
//
// The resolvers below read the join tables first and fall back to the column,
// so a profile saved through the current steps and a legacy row both display.

// resolveSkillNames returns the astrologer's primary skill and full skill list
// as comma-separated master names.
func (r *astrologerRepository) resolveSkillNames(
	astrologerID uint,
	primaryColumn string,
	allColumn string,
) (string, string) {

	type skillRow struct {
		Name      string `gorm:"column:name"`
		IsPrimary bool   `gorm:"column:is_primary"`
	}

	var rows []skillRow

	r.db.
		Table("astrologer_skills AS asx").
		Select("s.name AS name, asx.is_primary AS is_primary").
		Joins("JOIN skills s ON s.id = asx.skill_id").
		Where("asx.astrologer_id = ?", astrologerID).
		Order("asx.is_primary DESC, asx.id ASC").
		Scan(&rows)

	if len(rows) > 0 {

		names := make([]string, 0, len(rows))

		primary := ""

		for _, row := range rows {

			names = append(names, row.Name)

			if row.IsPrimary && primary == "" {
				primary = row.Name
			}
		}

		// No row flagged primary: the first selection stands in, which is what
		// the profile step falls back to when it fills the column.
		if primary == "" {
			primary = names[0]
		}

		return primary, strings.Join(names, ", ")
	}

	return r.resolveOrRaw(r.getSkillNames(primaryColumn), primaryColumn),
		r.resolveOrRaw(r.getSkillNames(allColumn), allColumn)
}

// resolveLanguageNames returns the astrologer's languages as comma-separated
// master names.
func (r *astrologerRepository) resolveLanguageNames(
	astrologerID uint,
	column string,
) string {

	var names []string

	r.db.
		Table("astrologer_languages AS al").
		Select("l.languageName").
		Joins("JOIN languages l ON l.id = al.language_id").
		Where("al.astrologer_id = ?", astrologerID).
		Order("al.id ASC").
		Pluck("l.languageName", &names)

	if len(names) > 0 {
		return strings.Join(names, ", ")
	}

	return r.resolveOrRaw(r.getLanguageNames(column), column)
}

// resolveCategoryNames returns the astrologer's categories as comma-separated
// master names. There is no join table for these — astrologerCategoryId is the
// only source.
func (r *astrologerRepository) resolveCategoryNames(column string) string {

	return r.resolveOrRaw(r.getCategoryNames(column), column)
}

// resolveOrRaw keeps a legacy column readable. The columns are meant to hold
// ids, but older rows hold plain text ("Astrology", "Hindi") that resolves to
// nothing — showing that text beats showing an empty field.
func (r *astrologerRepository) resolveOrRaw(resolved string, column string) string {

	if resolved != "" {
		return resolved
	}

	return strings.TrimSpace(column)
}

func (r *astrologerRepository) getSkillNames(ids string) string {

	if ids == "" {
		return ""
	}

	var names []string

	r.db.
		Table("skills").
		Where("id IN ?", strings.Split(ids, ",")).
		Pluck("name", &names)

	return strings.Join(names, ", ")
}

func (r *astrologerRepository) getLanguageNames(languageIDs string) string {

	if languageIDs == "" {
		return ""
	}

	var names []string

	r.db.
		Table("languages").
		Where("id IN (?)", strings.Split(languageIDs, ",")).
		Pluck("languageName", &names)

	return strings.Join(names, ", ")
}

func (r *astrologerRepository) getCategoryNames(ids string) string {

	if ids == "" {
		return ""
	}

	var names []string

	r.db.
		Table("astrologer_categories").
		Where("id IN ?", strings.Split(ids, ",")).
		Pluck("name", &names)

	return strings.Join(names, ", ")
}

func (r *astrologerRepository) isOnline(astrologerID uint) bool {

	var total int64

	r.db.
		Table("systemflag").
		Where("valueType = ?", "astrologer").
		Where("name = ?", astrologerID).
		Where("value = ?", "Online").
		Count(&total)

	return total > 0
}

func (r *astrologerRepository) isBusy(astrologerID uint) bool {

	var chatCount int64
	var callCount int64

	r.db.
		Table("chatrequest").
		Where("astrologerId = ?", astrologerID).
		Where("chatStatus = ?", "Running").
		Count(&chatCount)

	r.db.
		Table("callrequest").
		Where("astrologerId = ?", astrologerID).
		Where("callStatus = ?", "Running").
		Count(&callCount)

	return chatCount > 0 || callCount > 0
}

func (r *astrologerRepository) isFreeChatAvailable(astrologerID uint) bool {

	var total int64

	r.db.
		Table("chatrequest").
		Where("astrologerId = ?", astrologerID).
		Where("isFreeSession = ?", 1).
		Count(&total)

	return total == 0
}

func (r *astrologerRepository) ratingSubQuery() *gorm.DB {

	return r.db.
		Table("user_reviews").
		Select(`
			astrologerId,
			AVG(rating) as averageRating,
			COUNT(id) as ratingCount
		`).
		Group("astrologerId")
}

func (r *astrologerRepository) chatSummarySubQuery() *gorm.DB {

	return r.db.
		Table("chatrequest").
		Select(`
			astrologerId,
			COUNT(id) as chatOrders,
			IFNULL(SUM(CAST(totalMin AS DECIMAL(10,2))),0) as chatMinutes
		`).
		Where("chatStatus = ?", "Completed").
		Group("astrologerId")
}

func (r *astrologerRepository) callSummarySubQuery() *gorm.DB {

	return r.db.
		Table("callrequest").
		Select(`
			astrologerId,
			COUNT(id) as callOrders,
			IFNULL(SUM(CAST(totalMin AS DECIMAL(10,2))),0) as callMinutes
		`).
		Where("callStatus = ?", "Completed").
		Group("astrologerId")
}

func (r *astrologerRepository) SearchAstrologers(
	req dto.SearchAstrologerRequest,
) ([]dto.SearchAstrologerResponse, error) {

	// Scanned through an explicit column list rather than SELECT *: the
	// response DTO's field names resolve to snake_case, so a bare Find could
	// not match primarySkill / allSkill / languageKnown / astrologerCategoryId
	// and left all four empty whatever the row held.
	type searchRow struct {
		ID                 uint    `gorm:"column:id"`
		Name               string  `gorm:"column:name"`
		ProfileImage       string  `gorm:"column:profileImage"`
		Charge             float64 `gorm:"column:charge"`
		Experience         float64 `gorm:"column:experience"`
		PrimarySkill       string  `gorm:"column:primarySkill"`
		AllSkill           string  `gorm:"column:allSkill"`
		LanguageKnown      string  `gorm:"column:languageKnown"`
		AstrologerCategory string  `gorm:"column:astrologerCategoryId"`
	}

	var rows []searchRow

	query := r.db.Table("astrologers").Select(`
		id,
		name,
		profileImage,
		charge,
		experience,
		primarySkill,
		allSkill,
		languageKnown,
		astrologerCategoryId
	`)

	if req.FilterKey == "astrologer" {

		query = query.Where(
			"name LIKE ?",
			"%"+req.SearchString+"%",
		)
	}

	if req.StartIndex >= 0 && req.FetchRecord > 0 {

		query = query.Offset(req.StartIndex).
			Limit(req.FetchRecord)
	}

	if err := query.Scan(&rows).Error; err != nil {
		return nil, err
	}

	result := make([]dto.SearchAstrologerResponse, 0, len(rows))

	for _, row := range rows {

		primarySkill, allSkill := r.resolveSkillNames(
			row.ID,
			row.PrimarySkill,
			row.AllSkill,
		)

		result = append(result, dto.SearchAstrologerResponse{

			ID: row.ID,

			Name: row.Name,

			ProfileImage: row.ProfileImage,

			Charge: row.Charge,

			Experience: row.Experience,

			PrimarySkill: primarySkill,

			AllSkill: allSkill,

			LanguageKnown: r.resolveLanguageNames(row.ID, row.LanguageKnown),

			AstrologerCategory: r.resolveCategoryNames(row.AstrologerCategory),

			Rating: r.getAverageRating(row.ID),

			IsFreeAvailable: r.isFreeChatAvailable(row.ID),
		})
	}

	return result, nil
}

func (r *astrologerRepository) GetAstrologerByID(req dto.GetAstrologerByIDRequest) (*dto.GetAstrologerByIDResponse, error) {

	var result dto.AstrologerDBResult

	err := r.db.Table("astrologers a").
		Select(`a.id, a.name, a.profileImage, a.charge, a.audioCallRate, a.videoCallRate, a.reportRate,
			a.experience,
			a.primarySkill,
			a.allSkill,
			a.languageKnown,
			a.astrologerCategoryId,

			IFNULL(AVG(ur.rating),0) as rating,

			COUNT(DISTINCT ur.id) as rating_count,

			(
				SELECT IFNULL(SUM(totalMin),0)
				FROM chatrequest
				WHERE astrologerId=a.id
			) as chat_minutes,

			(
				SELECT IFNULL(SUM(totalMin),0)
				FROM callrequest
				WHERE astrologerId=a.id
			) as call_minutes
		`).
		Joins(`
			LEFT JOIN user_reviews ur
			ON ur.astrologerId=a.id
		`).
		Where("a.id=?", req.AstrologerID).
		Group("a.id").
		Scan(&result).Error

	if err != nil {
		return nil, err
	}

	primarySkill, allSkill := r.resolveSkillNames(
		result.ID,
		result.PrimarySkill,
		result.AllSkill,
	)

	response := dto.GetAstrologerByIDResponse{

		ID: result.ID,

		Name: result.Name,

		ProfileImage: result.ProfileImage,

		Charge: result.Charge,

		CallRate: result.CallRate,

		VideoCallRate: result.VideoCallRate,

		ReportRate: result.ReportRate,

		Experience: result.Experience,

		Rating: result.Rating,

		RatingCount: result.RatingCount,

		ChatMinutes: result.ChatMinutes,

		CallMinutes: result.CallMinutes,

		PrimarySkill: primarySkill,

		AllSkill: allSkill,

		LanguageKnown: r.resolveLanguageNames(result.ID, result.LanguageKnown),

		Category: r.resolveCategoryNames(result.AstrologerCategoryID),

		IsFreeAvailable: r.isFreeChatAvailable(req.UserID),

		IsFollowing: r.isFollowing(req.UserID, req.AstrologerID),
	}

	return &response, nil
}

func (r *astrologerRepository) isFollowing(userID uint, astrologerID uint) bool {

	var total int64

	err := r.db.
		Table("astrologer_followers").
		Where("userId = ?", userID).
		Where("astrologerId = ?", astrologerID).
		Where("isActive = ?", 1).
		Where("isDelete = ?", 0).
		Count(&total).Error

	if err != nil {
		return false
	}

	return total > 0
}

func (r *astrologerRepository) getAverageRating(astrologerID uint) float64 {

	var rating float64

	r.db.
		Table("user_reviews").
		Where("astrologerId = ?", astrologerID).
		Select("IFNULL(AVG(rating),0)").
		Scan(&rating)

	return rating
}