package db

import (
	"fmt"
	"log"
	"slices"
	"strings"
	"time"

	"tangled.org/core/appview/models"
	"tangled.org/core/appview/pagination"
	"tangled.org/core/orm"
)

func AddStar(e Execer, star *models.Star) error {
	query := `insert or ignore into stars (did, subject_type, subject, rkey) values (?, ?, ?, ?)`
	_, err := e.Exec(
		query,
		star.Did,
		string(star.SubjectType),
		star.Subject,
		star.Rkey,
	)
	return err
}

// Get a star record
func GetStar(e Execer, did string, subject string) (*models.Star, error) {
	query := `
	select did, subject_type, subject, created, rkey
	from stars
	where did = ? and subject = ?`
	row := e.QueryRow(query, did, subject)

	var star models.Star
	var created string
	err := row.Scan(&star.Did, &star.SubjectType, &star.Subject, &created, &star.Rkey)
	if err != nil {
		return nil, err
	}

	createdAtTime, err := time.Parse(time.RFC3339, created)
	if err != nil {
		log.Println("unable to determine followed at time")
		star.Created = time.Now()
	} else {
		star.Created = createdAtTime
	}

	return &star, nil
}

func GetStars(e Execer, subject string, page pagination.Page) ([]models.Star, error) {
	query := `
	select did, subject_type, subject, created, rkey
	from stars
	where subject = ?
	order by created desc
	limit ? offset ?
    `
	rows, err := e.Query(query, subject, page.Limit, page.Offset)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var stars []models.Star
	for rows.Next() {
		var star models.Star
		var created string
		if err := rows.Scan(&star.Did, &star.SubjectType, &star.Subject, &created, &star.Rkey); err != nil {
			return nil, err
		}

		star.Created = time.Now()
		if t, err := time.Parse(time.RFC3339, created); err == nil {
			star.Created = t
		}
		stars = append(stars, star)
	}

	return stars, rows.Err()
}

// Remove a star
func DeleteStar(e Execer, did string, subject string) error {
	_, err := e.Exec(`delete from stars where did = ? and subject = ?`, did, subject)
	return err
}

// Remove a star
func DeleteStarByRkey(e Execer, did string, rkey string) error {
	_, err := e.Exec(`delete from stars where did = ? and rkey = ?`, did, rkey)
	return err
}

func GetStarCount(e Execer, subjectType models.StarSubjectType, subject string) (int, error) {
	stars := 0
	err := e.QueryRow(
		`select count(did) from stars where subject_type = ? and subject = ?`,
		string(subjectType), subject,
	).Scan(&stars)
	if err != nil {
		return 0, err
	}
	return stars, nil
}

// getStarStatuses returns a map of subjects to star status for a given user
// This is an internal helper function to avoid N+1 queries
func getStarStatuses(e Execer, userDid string, subjects []string) (map[string]bool, error) {
	if len(subjects) == 0 || userDid == "" {
		return make(map[string]bool), nil
	}

	placeholders := make([]string, len(subjects))
	args := make([]any, len(subjects)+1)
	args[0] = userDid

	for i, subj := range subjects {
		placeholders[i] = "?"
		args[i+1] = subj
	}

	query := fmt.Sprintf(`
		SELECT subject
		FROM stars
		WHERE did = ? AND subject IN (%s)
	`, strings.Join(placeholders, ","))

	rows, err := e.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	result := make(map[string]bool)
	// Initialize all subjects as not starred
	for _, subj := range subjects {
		result[subj] = false
	}

	// Mark starred subjects as true
	for rows.Next() {
		var subj string
		if err := rows.Scan(&subj); err != nil {
			return nil, err
		}
		result[subj] = true
	}

	return result, nil
}

func GetStarStatus(e Execer, userDid string, subject string) bool {
	statuses, err := getStarStatuses(e, userDid, []string{subject})
	if err != nil {
		return false
	}
	return statuses[subject]
}

// GetStarStatuses returns a map of subjects to star status for a given user
func GetStarStatuses(e Execer, userDid string, subjects []string) (map[string]bool, error) {
	return getStarStatuses(e, userDid, subjects)
}

// GetRepoStars return a list of stars each holding target repository.
// If there isn't known repo with starred at-uri, those stars will be ignored.
func GetRepoStars(e Execer, page pagination.Page, filters ...orm.Filter) ([]models.RepoStar, error) {
	var conditions []string
	var args []any
	for _, filter := range filters {
		conditions = append(conditions, filter.Condition())
		args = append(args, filter.Arg()...)
	}

	conditions = append(conditions, "subject_type = 'repo'")

	whereClause := " where " + strings.Join(conditions, " and ")

	pageClause := ""
	if page.Limit != 0 {
		pageClause = fmt.Sprintf(" limit %d offset %d", page.Limit, page.Offset)
	}

	repoQuery := fmt.Sprintf(
		`select did, subject_type, subject, created, rkey
		from stars
		%s
		order by created desc
		%s`,
		whereClause,
		pageClause,
	)
	rows, err := e.Query(repoQuery, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	starMap := make(map[string][]models.Star)
	for rows.Next() {
		var star models.Star
		var created string
		err := rows.Scan(&star.Did, &star.SubjectType, &star.Subject, &created, &star.Rkey)
		if err != nil {
			return nil, err
		}

		star.Created = time.Now()
		if t, err := time.Parse(time.RFC3339, created); err == nil {
			star.Created = t
		}

		starMap[star.Subject] = append(starMap[star.Subject], star)
	}

	// populate *Repo in each star
	args = make([]any, len(starMap))
	i := 0
	for r := range starMap {
		args[i] = r
		i++
	}

	if len(args) == 0 {
		return nil, nil
	}

	repos, err := GetRepos(e, orm.FilterIn("repo_did", args))
	if err != nil {
		return nil, err
	}

	var repoStars []models.RepoStar
	for _, r := range repos {
		if stars, ok := starMap[r.RepoDid]; ok {
			for _, star := range stars {
				repoStars = append(repoStars, models.RepoStar{
					Star: star,
					Repo: &r,
				})
			}
		}
	}

	slices.SortFunc(repoStars, func(a, b models.RepoStar) int {
		if a.Created.After(b.Created) {
			return -1
		}
		if b.Created.After(a.Created) {
			return 1
		}
		return 0
	})

	return repoStars, nil
}

func CountStars(e Execer, filters ...orm.Filter) (int64, error) {
	var conditions []string
	var args []any
	for _, filter := range filters {
		conditions = append(conditions, filter.Condition())
		args = append(args, filter.Arg()...)
	}

	whereClause := ""
	if conditions != nil {
		whereClause = " where " + strings.Join(conditions, " and ")
	}

	repoQuery := fmt.Sprintf(`select count(1) from stars %s`, whereClause)
	var count int64
	if err := e.QueryRow(repoQuery, args...).Scan(&count); err != nil {
		return 0, err
	}

	return count, nil
}

// GetTopStarredReposLastWeek returns the top 8 most starred repositories from the last week
func GetTopStarredReposLastWeek(e Execer) ([]models.Repo, error) {
	// first, get the top repo DIDs by star count from the last week
	query := `
		with recent_starred_repos as (
			select distinct subject
			from stars
			where created >= datetime('now', '-7 days')
			  and subject_type = 'repo'
		),
		repo_star_counts as (
			select
				s.subject,
				count(*) as stars_gained_last_week
			from stars s
			join recent_starred_repos rsr on s.subject = rsr.subject
			where s.created >= datetime('now', '-7 days')
			  and s.subject_type = 'repo'
			group by s.subject
		)
		select rsc.subject
		from repo_star_counts rsc
		order by rsc.stars_gained_last_week desc
		limit 5
	`

	rows, err := e.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var repoDids []string
	for rows.Next() {
		var repoDid string
		err := rows.Scan(&repoDid)
		if err != nil {
			return nil, err
		}
		repoDids = append(repoDids, repoDid)
	}

	if err := rows.Err(); err != nil {
		return nil, err
	}

	if len(repoDids) == 0 {
		return []models.Repo{}, nil
	}

	// get full repo data
	repos, err := GetRepos(e, orm.FilterIn("repo_did", repoDids))
	if err != nil {
		return nil, err
	}

	// sort repos by the original trending order
	repoMap := make(map[string]models.Repo)
	for _, repo := range repos {
		repoMap[repo.RepoDid] = repo
	}

	orderedRepos := make([]models.Repo, 0, len(repoDids))
	for _, did := range repoDids {
		if repo, exists := repoMap[did]; exists {
			orderedRepos = append(orderedRepos, repo)
		}
	}

	return orderedRepos, nil
}