package db

import "database/sql"
import "strings"

import "codit/internal/models"

const projectSummaryRecentLimit = 10

// GetProjectSummary returns aggregate counts plus a few recent repos and boards
// for a project's dashboard, in one round trip of cheap queries.
func (s *Store) GetProjectSummary(projectID string) (models.ProjectSummary, error) {
	var summary models.ProjectSummary
	var pid int64
	var err error
	var rows *sql.Rows

	err = s.QueryRow(`SELECT id FROM projects WHERE public_id = ?`, strings.TrimSpace(projectID)).Scan(&pid)
	if err != nil {
		return summary, err
	}

	// Repos = local repos plus foreign repos attached to this project (matching
	// the repo list). Members counts user role bindings (group roles are listed
	// separately on the page).
	err = s.QueryRow(`SELECT
		(SELECT COUNT(*) FROM repos WHERE project_id = ?)
			+ (SELECT COUNT(*) FROM project_repos pr JOIN repos r ON r.id = pr.repo_id WHERE pr.project_id = ? AND r.project_id <> ?),
		(SELECT COUNT(*) FROM boards WHERE project_id = ? AND delete_at = 0),
		(SELECT COUNT(*) FROM issues WHERE project_id = ?),
		(SELECT COUNT(*) FROM wiki_pages WHERE project_id = ?),
		(SELECT COUNT(*) FROM project_role_bindings WHERE project_id = ? AND subject_type = 'user')`,
		pid, pid, pid, pid, pid, pid, pid).Scan(
		&summary.Counts.Repos, &summary.Counts.Boards, &summary.Counts.Issues, &summary.Counts.Wiki, &summary.Counts.Members)
	if err != nil {
		return summary, err
	}

	rows, err = s.Query(`SELECT public_id, name, type, created_at
		FROM (
			SELECT r.public_id, r.name, r.type, r.created_at
			FROM repos r
			WHERE r.project_id = ?
			UNION ALL
			SELECT r.public_id, r.name, r.type, r.created_at
			FROM project_repos pr
			JOIN repos r ON r.id = pr.repo_id
			WHERE pr.project_id = ? AND r.project_id <> ?
		)
		ORDER BY created_at DESC, name LIMIT ?`, pid, pid, pid, projectSummaryRecentLimit)
	if err != nil {
		return summary, err
	}
	defer rows.Close()
	for rows.Next() {
		var item models.ProjectSummaryRepo
		var createdAt int64
		err = rows.Scan(&item.ID, &item.Name, &item.Type, &createdAt)
		if err != nil {
			return summary, err
		}
		summary.RecentRepos = append(summary.RecentRepos, item)
	}
	err = rows.Err()
	if err != nil {
		return summary, err
	}

	rows, err = s.Query(`SELECT public_id, title FROM boards
		WHERE project_id = ? AND delete_at = 0 ORDER BY created_at DESC, title LIMIT ?`, pid, projectSummaryRecentLimit)
	if err != nil {
		return summary, err
	}
	defer rows.Close()
	for rows.Next() {
		var item models.ProjectSummaryBoard
		err = rows.Scan(&item.ID, &item.Name)
		if err != nil {
			return summary, err
		}
		summary.RecentBoards = append(summary.RecentBoards, item)
	}
	return summary, rows.Err()
}