package storage

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
)

func (s *Store) AddMembership(ctx context.Context, userID, groupID string) error {
	const operation = "add membership"
	if err := s.checkOpen(operation); err != nil {
		return err
	}
	return addMembership(ctx, s.sql, operation, userID, groupID)
}

func addMembership(ctx context.Context, q execer, operation, userID, groupID string) error {
	if _, err := q.ExecContext(ctx, `INSERT INTO memberships (user_id, group_id) VALUES (?, ?)`, userID, groupID); err != nil {
		return classify(operation, err)
	}
	return nil
}

func (s *Store) RemoveMembership(ctx context.Context, userID, groupID string) error {
	const operation = "remove membership"
	if err := s.checkOpen(operation); err != nil {
		return err
	}
	result, err := s.sql.ExecContext(ctx, `DELETE FROM memberships WHERE user_id = ? AND group_id = ?`, userID, groupID)
	if err != nil {
		return classify(operation, err)
	}
	return requireChanged(operation, result)
}

func (s *Store) GroupsForUser(ctx context.Context, userID string) ([]Group, error) {
	const operation = "list groups for user"
	if err := s.checkOpen(operation); err != nil {
		return nil, err
	}
	rows, err := s.sql.QueryContext(ctx, `
SELECT g.id, g.name, g.description, g.gid_number, g.created_at
FROM groups g
JOIN memberships m ON m.group_id = g.id
WHERE m.user_id = ?
ORDER BY g.name, g.id`, userID)
	if err != nil {
		return nil, classify(operation, err)
	}
	defer rows.Close()
	var groups []Group
	for rows.Next() {
		group, err := scanGroup(rows)
		if err != nil {
			return nil, classify(operation, err)
		}
		groups = append(groups, group)
	}
	if err := rows.Err(); err != nil {
		return nil, classify(operation, err)
	}
	return groups, nil
}

func (s *Store) UsersInGroup(ctx context.Context, groupID string) ([]User, error) {
	const operation = "list users in group"
	if err := s.checkOpen(operation); err != nil {
		return nil, err
	}
	rows, err := s.sql.QueryContext(ctx, `
SELECT u.id, u.username, u.display_name,
       u.password_hash, u.disabled, u.uid_number, u.created_at, u.last_login_at, u.credentials_epoch
FROM users u
JOIN memberships m ON m.user_id = u.id
WHERE m.group_id = ?
ORDER BY u.username, u.id`, groupID)
	if err != nil {
		return nil, classify(operation, err)
	}
	defer rows.Close()
	var users []User
	for rows.Next() {
		user, err := scanUser(rows)
		if err != nil {
			return nil, classify(operation, err)
		}
		users = append(users, user)
	}
	if err := rows.Err(); err != nil {
		return nil, classify(operation, err)
	}
	return users, nil
}

func (s *Store) GoogleLinkByUser(ctx context.Context, userID string) (GoogleLink, error) {
	return s.googleLinkBy(ctx, "get google link by user", `SELECT user_id, google_sub, linked_at FROM google_links WHERE user_id = ?`, userID)
}

func (s *Store) GoogleLinkBySubject(ctx context.Context, googleSub string) (GoogleLink, error) {
	return s.googleLinkBy(ctx, "get google link by subject", `SELECT user_id, google_sub, linked_at FROM google_links WHERE google_sub = ?`, googleSub)
}

func (s *Store) googleLinkBy(ctx context.Context, operation, query, arg string) (GoogleLink, error) {
	if err := s.checkOpen(operation); err != nil {
		return GoogleLink{}, err
	}
	var link GoogleLink
	var linkedAt int64
	err := s.sql.QueryRowContext(ctx, query, arg).Scan(&link.UserID, &link.GoogleSub, &linkedAt)
	if errors.Is(err, sql.ErrNoRows) {
		return GoogleLink{}, fmt.Errorf("%s: %w", operation, ErrNotFound)
	}
	if err != nil {
		return GoogleLink{}, classify(operation, err)
	}
	link.LinkedAt = intToTime(linkedAt)
	return link, nil
}