package db

import (
	"database/sql"
	"errors"
	"fmt"
	"log"
	"strings"
	"time"

	"github.com/bluesky-social/indigo/atproto/syntax"

	"yoten.app/api/yoten"
	"yoten.app/internal/types"
	"yoten.app/internal/utils"
)

var (
	ErrSessionDescriptionTooLong     = errors.New("study session description exceeds max length")
	ErrSessionInvalidDuration        = errors.New("study session duration must be positive")
	ErrSessionInvalidDurationHours   = errors.New("study session hours duration must be between 0-24")
	ErrSessionInvalidDurationMinutes = errors.New("study session minutes duration must be between 0-60")
	ErrSessionInvalidDurationSeconds = errors.New("study session seconds duration must be between 0-60")
	ErrSessionMissingActivity        = errors.New("study session must have an associated activity")
	ErrSessionMissingLanguage        = errors.New("study session must have an associated language")
)

type StudySession struct {
	Did  string
	Rkey string
	// 256 characters
	Description  string
	Activity     Activity
	Resource     *Resource
	Language     Language
	XpGained     int
	Duration     time.Duration
	Date         time.Time
	Reactions    []ReactionEvent
	CommentCount int64
	CreatedAt    time.Time
}

func (ss StudySession) GetRkey() string {
	return ss.Rkey
}

func (s StudySession) StudySessionAt() syntax.ATURI {
	return syntax.ATURI(fmt.Sprintf("at://%s/%s/%s", s.Did, yoten.FeedSessionNSID, s.Rkey))
}

type StudySessionFeedItem struct {
	StudySession
	ProfileDisplayName string
	ProfileLevel       int
	BskyProfile        types.BskyProfile
}

func GetStudySessionFeed(e Execer, limit, offset int) ([]*StudySessionFeedItem, error) {
	query := `
		select
            ss.did, ss.rkey, ss.activity_id, ss.resource_id, ss.description, ss.duration, ss.language_code, ss.date, ss.created,
            p.display_name, p.level,
            x.xp_gained
		from study_sessions as ss
        join profiles as p on ss.did = p.did
        left join xp_events x on ss.did = x.did and ss.rkey = x.session_rkey
		order by ss.created desc limit ? offset ?`
	return getFeed(e, query, limit, offset)
}

func GetFriendsStudySessionFeed(e Execer, userDid string, limit, offset int) ([]*StudySessionFeedItem, error) {
	query := `
		select
            ss.did, ss.rkey, ss.activity_id, ss.resource_id, ss.description, ss.duration, ss.language_code, ss.date, ss.created,
            p.display_name, p.level,
            x.xp_gained
		from study_sessions as ss
        join profiles as p on ss.did = p.did
		join follows as f on ss.did = f.subject_did
        left join xp_events x on ss.did = x.did and ss.rkey = x.session_rkey
		where f.user_did = ?
		order by ss.created desc limit ? offset ?`
	return getFeed(e, query, userDid, limit, offset)
}

func UpsertStudySession(e Execer, session *StudySession, rkey string) error {
	var resourceId *int = nil
	if session.Resource != nil {
		resourceId = &session.Resource.ID
	}

	_, err := e.Exec(`
        insert into study_sessions (
			did,
            rkey,
			activity_id,
			resource_id,
			description,
			duration,
            language_code,
			date,
            created
		)
		values (?, ?, ?, ?, ?, ?, ?, ?, ?)
        on conflict(did, rkey) do update set
            activity_id = excluded.activity_id,
            resource_id = excluded.resource_id,
            description = excluded.description,
            duration = excluded.duration,
            language_code = excluded.language_code,
            date = excluded.date`,
		session.Did,
		rkey,
		session.Activity.ID,
		resourceId,
		session.Description,
		session.Duration.Seconds(),
		session.Language.Code,
		session.Date.Format(time.RFC3339),
		session.CreatedAt.Format(time.RFC3339),
	)
	if err != nil {
		return fmt.Errorf("failed to insert or update study session: %w", err)
	}

	return nil
}

func GetStudySessionLogs(e Execer, did string, limit, offset int) ([]*StudySession, error) {
	query := `
		select
            ss.did, ss.rkey, ss.activity_id, ss.resource_id, ss.description, ss.duration, ss.language_code, ss.date, ss.created,
            x.xp_gained
		from study_sessions ss
		left join xp_events x on ss.did = x.did and ss.rkey = x.session_rkey
		where ss.did = ?
		order by ss.created desc
		limit ? offset ?`

	rows, err := e.Query(query, did, limit, offset)
	if err != nil {
		return nil, fmt.Errorf("failed to query for study sessions: %w", err)
	}
	defer rows.Close()

	var studySessions []*StudySession

	for rows.Next() {
		var language Language
		var activity Activity = Activity{}
		var dateStr string
		var duration int64
		var session StudySession
		var createdAtStr string
		// TODO: Are these needed? Can they be used within the structs themselves.
		var activityId int
		var resourceId sql.NullInt64
		var xp_gained sql.NullInt64

		err := rows.Scan(
			&session.Did, &session.Rkey, &activityId, &resourceId,
			&session.Description, &duration, &language.Code, &dateStr,
			&createdAtStr, &xp_gained,
		)
		if err != nil {
			return nil, fmt.Errorf("failed to scan study session row: %w", err)
		}

		session.XpGained = 0
		if xp_gained.Valid {
			session.XpGained = int(xp_gained.Int64)
		}

		parsedTime, err := time.Parse(time.RFC3339, dateStr)
		if err != nil {
			return nil, fmt.Errorf("failed to parse date string '%s': %w", dateStr, err)
		}
		session.Date = parsedTime
		session.Duration = time.Duration(duration) * time.Second

		language, ok := Languages[language.Code]
		if !ok {
			log.Printf("failed to find language '%s'", language.Code)
			continue
		}
		session.Language = language

		// TODO: Optimise this request - should not fetch for each session
		activity, err = GetActivity(e, activityId)
		if err != nil {
			return nil, fmt.Errorf("failed to find activity '%d': %w", activityId, err)
		}
		session.Activity = activity

		if resourceId.Valid {
			var resource Resource = Resource{}
			// TODO: Optimise this request - should not fetch for each session
			resource, err = GetResource(e, int(resourceId.Int64))
			if err != nil {
				return nil, fmt.Errorf("failed to find resource '%d': %w", resourceId.Int64, err)
			}
			session.Resource = &resource
		}

		// TODO: Optimise this request - should not fetch for each session
		reactions, err := GetReactionsForSession(e, session.Did, session.Rkey)
		if err != nil {
			return nil, fmt.Errorf("failed to get reactions: %w", err)
		}
		session.Reactions = reactions

		createdAt, err := time.Parse(time.RFC3339, createdAtStr)
		if err != nil {
			return nil, fmt.Errorf("failed to parse createdAt string '%s': %w", createdAtStr, err)
		}
		session.CreatedAt = createdAt

		studySessions = append(studySessions, &session)
	}
	if err = rows.Err(); err != nil {
		return nil, fmt.Errorf("failed to iterate study session rows: %w", err)
	}

	uris := utils.Map(studySessions, func(session *StudySession) string {
		return string(session.StudySessionAt())
	})
	commentCounts, err := GetCommentCountsForSessions(e, uris)
	if err != nil {
		log.Println("failed to get comment count:", err)
	}
	for _, item := range studySessions {
		if count, ok := commentCounts[string(item.StudySessionAt())]; ok {
			item.CommentCount = count
		} else {
			item.CommentCount = 0
		}
	}

	return studySessions, nil
}

func DeleteStudySessionByRkey(e Execer, did string, rkey string) error {
	_, err := e.Exec(`delete from study_sessions where did = ? and rkey = ?`, did, rkey)
	return err
}

func GetStudySessionByRkey(e Execer, did string, rkey string) (*StudySession, error) {
	var language Language
	var activity Activity = Activity{}
	var dateStr string
	var createdAtStr string
	var duration int64
	var activityId int
	var resourceId sql.NullInt64
	var xp_gained sql.NullInt64

	var session StudySession
	session.Did = did
	session.Rkey = rkey

	err := e.QueryRow(`
        select
            ss.activity_id, ss.resource_id, ss.description, ss.duration, ss.language_code, ss.date, ss.created,
            xp_gained
		from study_sessions ss
		left join xp_events x on ss.did = x.did and ss.rkey = x.session_rkey
		where ss.did = ? and ss.rkey = ?`,
		did, rkey,
	).Scan(&activityId, &resourceId, &session.Description, &duration, &language.Code, &dateStr, &createdAtStr, &xp_gained)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("study session does not exist")
		}
		return nil, err
	}

	session.XpGained = 0
	if xp_gained.Valid {
		session.XpGained = int(xp_gained.Int64)
	}

	activity, err = GetActivity(e, activityId)
	if err != nil {
		return nil, fmt.Errorf("failed to find activity '%d': %w", activityId, err)
	}
	session.Activity = activity

	if resourceId.Valid {
		var resource Resource = Resource{}
		resource, err = GetResource(e, int(resourceId.Int64))
		if err != nil {
			return nil, fmt.Errorf("failed to find resource '%d': %w", resourceId.Int64, err)
		}
		session.Resource = &resource
	}

	date, err := time.Parse(time.RFC3339, dateStr)
	if err != nil {
		return nil, fmt.Errorf("failed to parse date string '%s': %w", dateStr, err)
	}
	session.Date = date
	session.Duration = time.Duration(duration) * time.Second
	created, err := time.Parse(time.RFC3339, createdAtStr)
	if err != nil {
		return nil, fmt.Errorf("failed to parse created string '%s': %w", createdAtStr, err)
	}
	session.CreatedAt = created

	language, ok := Languages[language.Code]
	if !ok {
		return nil, fmt.Errorf("failed to find language '%s'", language.Code)
	}
	session.Language = language

	reactions, err := GetReactionsForSession(e, session.Did, session.Rkey)
	if err != nil {
		log.Println("failed to retrieve reactions", err)
	}
	session.Reactions = reactions

	commentCount, err := GetCommentCountForSession(e, string(session.StudySessionAt()))
	if err != nil {
		log.Println("failed to retrieve comment count", err)
	}
	session.CommentCount = commentCount

	return &session, nil
}

func ValidateStudySessionDuration(hours int64, minutes int64, seconds int64) error {
	if hours < 0 || hours > 24 {
		return ErrSessionInvalidDurationHours
	}
	if minutes < 0 || minutes > 60 {
		return ErrSessionInvalidDurationMinutes
	}
	if seconds < 0 || seconds > 60 {
		return ErrSessionInvalidDurationSeconds
	}

	return nil
}

func ValidateStudySession(session StudySession) error {
	if len(session.Description) > 256 {
		return ErrSessionDescriptionTooLong
	}

	if session.Duration.Seconds() <= 0 {
		return ErrSessionInvalidDuration
	}

	if session.Activity.ID == 0 {
		return ErrSessionMissingActivity
	}

	if session.Language.Code == "" {
		return ErrSessionMissingLanguage
	}

	return nil
}

func scanSessionFeedItem(rows *sql.Rows) (StudySessionFeedItem, int, error) {
	var item StudySessionFeedItem
	var session StudySession
	var activityId int
	var resourceId sql.NullInt64
	var duration int64
	var dateStr, createdAtStr, langCode string
	var xp_gained sql.NullInt64

	err := rows.Scan(
		&session.Did, &session.Rkey, &activityId, &resourceId, &session.Description,
		&duration, &langCode, &dateStr, &createdAtStr, &item.ProfileDisplayName,
		&item.ProfileLevel, &xp_gained,
	)
	if err != nil {
		return StudySessionFeedItem{}, 0, err
	}

	session.XpGained = 0
	if xp_gained.Valid {
		session.XpGained = int(xp_gained.Int64)
	}

	session.Date, err = time.Parse(time.RFC3339, dateStr)
	if err != nil {
		return StudySessionFeedItem{}, 0, fmt.Errorf("failed to parse date string '%s': %w", dateStr, err)
	}
	session.CreatedAt, err = time.Parse(time.RFC3339, createdAtStr)
	if err != nil {
		return StudySessionFeedItem{}, 0, fmt.Errorf("failed to parse created at string '%s': %w", dateStr, err)
	}
	session.Duration = time.Duration(duration) * time.Second
	var ok bool
	session.Language, ok = Languages[LanguageCode(langCode)]
	if !ok {
		return StudySessionFeedItem{}, 0, fmt.Errorf("failed to find language '%s'", langCode)
	}

	if resourceId.Valid {
		item.Resource = &Resource{ID: int(resourceId.Int64)}
	}

	item.StudySession = session
	item.Activity.ID = activityId

	return item, activityId, nil
}

func populateResourcesForFeed(e Execer, feedItems []*StudySessionFeedItem) error {
	if len(feedItems) == 0 {
		return nil
	}

	sessionMap := make(map[string]*StudySessionFeedItem)
	var keyArgs []any
	var placeholders []string
	for _, item := range feedItems {
		compositeKey := item.Did + ":" + item.Rkey
		sessionMap[compositeKey] = item
		placeholders = append(placeholders, "(?, ?)")
		keyArgs = append(keyArgs, item.Did, item.Rkey)
	}

	query := fmt.Sprintf(`
		select
			ss.did,
			ss.rkey,
			ur.id,
			ur.title,
			ur.author,
			ur.type,
			ur.link
		from
			study_sessions ss
		join
			resources ur on ss.resource_id = ur.id
		where
			(ss.did, ss.rkey) in (%s)
	`, strings.Join(placeholders, ","))

	rows, err := e.Query(query, keyArgs...)
	if err != nil {
		return fmt.Errorf("failed to query for session resources: %w", err)
	}
	defer rows.Close()

	for rows.Next() {
		var sessionDid, sessionRkey string
		var resource Resource
		var resourceTypeStr string
		var link sql.NullString

		err := rows.Scan(
			&sessionDid, &sessionRkey,
			&resource.ID, &resource.Title, &resource.Author, &resourceTypeStr, &link,
		)
		if err != nil {
			return fmt.Errorf("failed to scan session resource: %w", err)
		}

		if link.Valid {
			resource.Link = ToPtr(link.String)
		}

		resourceType, err := ResourceTypeFromString(resourceTypeStr)
		if err != nil {
			return fmt.Errorf("failed to parse resource type string '%s': %w", resourceTypeStr, err)
		}
		resource.Type = resourceType

		compositeKey := sessionDid + ":" + sessionRkey
		if session, ok := sessionMap[compositeKey]; ok {
			session.StudySession.Resource = &resource
		}
	}
	return rows.Err()
}

func populateActivitiesForFeed(e Execer, feedItems []*StudySessionFeedItem) error {
	if len(feedItems) == 0 {
		return nil
	}

	activityIDs := make([]int, 0, len(feedItems))
	for _, item := range feedItems {
		activityIDs = append(activityIDs, item.Activity.ID)
	}

	activities, err := GetActivitiesByIDs(e, activityIDs)
	if err != nil {
		return fmt.Errorf("failed to get activities: %w", err)
	}

	activitiesMap := make(map[int]Activity)
	for _, a := range activities {
		activitiesMap[a.ID] = a
	}

	for _, item := range feedItems {
		if activity, ok := activitiesMap[item.Activity.ID]; ok {
			item.StudySession.Activity = activity
		}
	}

	return nil
}

func populateReactionsForFeed(e Execer, feedItems []*StudySessionFeedItem) error {
	if len(feedItems) == 0 {
		return nil
	}

	sessionMap := make(map[string]*StudySessionFeedItem)
	var keyArgs []any
	var placeholders []string
	for _, item := range feedItems {
		compositeKey := item.Did + ":" + item.Rkey
		sessionMap[compositeKey] = item
		placeholders = append(placeholders, "(?, ?)")
		keyArgs = append(keyArgs, item.Did, item.Rkey)
	}

	query := fmt.Sprintf(`
		select session_did, session_rkey, id, did, rkey, reaction_id
		from study_session_reactions
		where (session_did, session_rkey) in (%s)
	`, strings.Join(placeholders, ","))

	rows, err := e.Query(query, keyArgs...)
	if err != nil {
		return fmt.Errorf("failed to query for session reactions: %w", err)
	}
	defer rows.Close()

	for rows.Next() {
		var event ReactionEvent
		err := rows.Scan(
			&event.SessionDid, &event.SessionRkey, &event.ID, &event.Did,
			&event.Rkey, &event.Reaction.ID,
		)
		if err != nil {
			return fmt.Errorf("failed to scan reaction event: %w", err)
		}
		reaction, ok := Reactions[event.Reaction.ID.String()]
		if !ok {
			log.Println("failed to find reaction")
			continue
		}
		event.Reaction = reaction
		compositeKey := event.SessionDid + ":" + event.SessionRkey
		if session, ok := sessionMap[compositeKey]; ok {
			session.Reactions = append(session.Reactions, event)
		}
	}
	return rows.Err()
}

func populateCommentsForFeed(e Execer, feedItems []*StudySessionFeedItem) error {
	if len(feedItems) == 0 {
		return nil
	}

	uris := utils.Map(feedItems, func(item *StudySessionFeedItem) string {
		return item.StudySessionAt().String()
	})
	commentCounts, err := GetCommentCountsForSessions(e, uris)
	if err != nil {
		return fmt.Errorf("failed to get comment counts: %w", err)
	}

	for _, item := range feedItems {
		if count, ok := commentCounts[string(item.StudySessionAt())]; ok {
			item.CommentCount = count
		} else {
			item.CommentCount = 0
		}
	}

	return nil
}

func getFeed(e Execer, query string, args ...any) ([]*StudySessionFeedItem, error) {
	rows, err := e.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var feedItems []*StudySessionFeedItem
	for rows.Next() {
		item, _, err := scanSessionFeedItem(rows)
		if err != nil {
			log.Printf("failed to scan study session row: %v", err)
			continue
		}
		feedItems = append(feedItems, &item)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}

	if err := populateActivitiesForFeed(e, feedItems); err != nil {
		return nil, err
	}

	if err := populateReactionsForFeed(e, feedItems); err != nil {
		return nil, err
	}

	if err := populateResourcesForFeed(e, feedItems); err != nil {
		return nil, err
	}

	if err := populateCommentsForFeed(e, feedItems); err != nil {
		return nil, err
	}

	return feedItems, nil
}