package storage

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"time"
)

const DexConnectorID = "unified"

var (
	ErrLastAdmin       = errors.New("storage: the admin group would have no enabled member")
	ErrReferencedGroup = errors.New("storage: group is referenced by the configuration")
	ErrGroupTaken      = errors.New("storage: group name is taken")
)

type Revoked struct {
	Sessions       int64
	RefreshTokens  int64
	Codes          int64
	PendingSignIns int64
}

type DashboardCounts struct {
	Users              int
	DisabledUsers      int
	Groups             int
	PendingInvitations int
	LiveSessions       int
}

const (
	enabledMembersQuery = `
SELECT count(*) FROM memberships m
JOIN groups g ON g.id = m.group_id
JOIN users u ON u.id = m.user_id
WHERE g.name = ? AND u.disabled = 0`
	isEnabledMemberQuery = enabledMembersQuery + ` AND u.id = ?`
	dashboardQuery       = `
SELECT
    (SELECT count(*) FROM users),
    (SELECT coalesce(sum(disabled), 0) FROM users),
    (SELECT count(*) FROM groups),
    (SELECT count(*) FROM invitations WHERE used_at IS NULL AND revoked_at IS NULL AND expires_at > ?1),
    (SELECT count(*) FROM forward_sessions WHERE revoked = 0 AND expires_at > ?1)`
	sessionEpochQuery = `
SELECT u.credentials_epoch FROM forward_sessions f
JOIN users u ON u.id = f.user_id
WHERE f.id = ? AND f.app_domain = ? AND f.revoked = 0 AND f.expires_at > ? AND u.disabled = 0`
	liveSessionEpochQuery = `
SELECT u.credentials_epoch FROM users u
WHERE u.id = ?1 AND u.disabled = 0
  AND (SELECT count(*) FROM forward_sessions f
       WHERE f.user_id = u.id AND f.app_domain = ?2 AND f.created_at = ?3) = 1
  AND EXISTS (SELECT 1 FROM forward_sessions f
              WHERE f.user_id = u.id AND f.app_domain = ?2 AND f.created_at = ?3
                AND f.revoked = 0 AND f.expires_at > ?4)`
)

var dexCredentialDeletes = [4]struct{ name, query string }{
	{"dex refresh tokens", `DELETE FROM refresh_tokens WHERE claims_user_id = ? AND connector_id = ?`},
	{"dex offline sessions", `DELETE FROM offline_sessions WHERE user_id = ? AND conn_id = ?`},
	{"dex auth codes", `DELETE FROM auth_codes WHERE claims_user_id = ? AND connector_id = ?`},
	{"dex auth requests", `DELETE FROM auth_requests WHERE connector_id = ?2 AND (claims_user_id = ?1 OR substr(CAST(connector_data AS TEXT), 1, length(?1) + 1) = ?1 || '.' OR (CASE WHEN json_valid(CAST(connector_data AS TEXT)) THEN json_extract(CAST(connector_data AS TEXT), '$.` + PendingUserKey + `') END) = ?1)`},
}

func (s *Store) CreateUserAudited(ctx context.Context, input CreateUserInput, groups []string, actor string) (User, error) {
	const operation = "create user"
	if err := s.checkOpen(operation); err != nil {
		return User{}, err
	}
	prepared, err := s.prepareUser(operation, input)
	if err != nil {
		return User{}, err
	}
	var created User
	err = s.audited(ctx, operation, actor, func(tx *sql.Tx, at time.Time) error {
		if err := requireFree(ctx, tx, operation, `SELECT 1 FROM users WHERE username = ?`, prepared.Username, ErrUsernameTaken); err != nil {
			return err
		}
		prepared.CreatedAt = at
		user, err := insertUser(ctx, tx, operation, prepared)
		if err != nil {
			return err
		}
		granted := []string{}
		for _, name := range groups {
			group, _, err := s.ensureGroup(ctx, tx, operation, name, "system")
			if err != nil {
				return err
			}
			if contains(granted, group.Name) {
				continue
			}
			if err := addMembership(ctx, tx, operation, user.ID, group.ID); err != nil {
				return err
			}
			granted = append(granted, group.Name)
		}
		created = user
		return writeAudit(ctx, tx, operation, actor, "user.create", user.ID, at, map[string]any{"username": user.Username, "groups": granted})
	})
	if err != nil {
		return User{}, err
	}
	return created, nil
}

func (s *Store) UpdateProfileAudited(ctx context.Context, userID, displayName, actor string) (User, error) {
	const operation = "update profile"
	if err := validateDisplayName(displayName); err != nil {
		return User{}, invalid(operation, err)
	}
	var updated User
	err := s.audited(ctx, operation, actor, func(tx *sql.Tx, at time.Time) error {
		current, err := userInTx(ctx, tx, operation, userID)
		if err != nil {
			return err
		}
		if _, err := tx.ExecContext(ctx, `UPDATE users SET display_name = ? WHERE id = ?`, displayName, userID); err != nil {
			return classify(operation, err)
		}
		if err := writeAudit(ctx, tx, operation, actor, "user.update", userID, at, map[string]any{"username": current.Username, "display_name_changed": current.DisplayName != displayName}); err != nil {
			return err
		}
		updated, err = userInTx(ctx, tx, operation, userID)
		return err
	})
	if err != nil {
		return User{}, err
	}
	return updated, nil
}

func (s *Store) SetUserDisabledAudited(ctx context.Context, userID string, disabled bool, actor, adminGroup string) (Revoked, error) {
	const operation = "set user disabled"
	var revoked Revoked
	err := s.audited(ctx, operation, actor, func(tx *sql.Tx, at time.Time) error {
		username, _, err := userNameAndUID(ctx, tx, operation, userID)
		if err != nil {
			return err
		}
		wasAdmin := false
		if disabled {
			if wasAdmin, err = isEnabledMember(ctx, tx, operation, userID, adminGroup); err != nil {
				return err
			}
		}
		changed, err := execCount(ctx, tx, operation, `UPDATE users SET disabled = ? WHERE id = ? AND disabled <> ?`, boolToInt(disabled), userID, boolToInt(disabled))
		if err != nil && !disabled && errors.Is(err, ErrConflict) {
			if pending, perr := isPending(ctx, tx, operation, userID); perr == nil && pending {
				return fmt.Errorf("%s: %w: %w", operation, ErrUserPending, ErrConflict)
			}
		}
		if err != nil || changed == 0 {
			return err
		}
		if wasAdmin {
			if err := requireEnabledMember(ctx, tx, operation, adminGroup); err != nil {
				return err
			}
		}
		if revoked, err = revokeCredentials(ctx, tx, operation, userID, "", disabled); err != nil {
			return err
		}
		if disabled {
			if _, err := endResetLink(ctx, tx, operation, userID); err != nil {
				return err
			}
		}
		if !disabled {
			return writeAudit(ctx, tx, operation, actor, "user.enable", userID, at, revoked.into(map[string]any{"username": username}))
		}
		if err := writeAudit(ctx, tx, operation, actor, "user.disable", userID, at, map[string]any{"username": username}); err != nil {
			return err
		}
		return writeAudit(ctx, tx, operation+" revoke", actor, "session.revoke_all", userID, at, revoked.into(map[string]any{"username": username}))
	})
	if err != nil {
		return Revoked{}, err
	}
	return revoked, nil
}

func (s *Store) DeleteUserAudited(ctx context.Context, userID, actor, adminGroup string) (Revoked, error) {
	const operation = "delete user"
	var revoked Revoked
	err := s.audited(ctx, operation, actor, func(tx *sql.Tx, at time.Time) error {
		username, uid, err := userNameAndUID(ctx, tx, operation, userID)
		if err != nil {
			return err
		}
		wasAdmin, err := isEnabledMember(ctx, tx, operation, userID, adminGroup)
		if err != nil {
			return err
		}
		if revoked, err = revokeCredentials(ctx, tx, operation, userID, "", true); err != nil {
			return err
		}
		if err := blankRedeemedLabels(ctx, tx, operation, userID); err != nil {
			return err
		}
		deleted, err := execCount(ctx, tx, operation, `DELETE FROM users WHERE id = ?`, userID)
		if err != nil {
			return err
		}
		if deleted == 0 {
			return fmt.Errorf("%s: %w", operation, ErrNotFound)
		}
		if wasAdmin {
			if err := requireEnabledMember(ctx, tx, operation, adminGroup); err != nil {
				return err
			}
		}
		return writeAudit(ctx, tx, operation, actor, "user.delete", userID, at, revoked.into(map[string]any{"username": username, "uid_number": uid}))
	})
	if err != nil {
		return Revoked{}, err
	}
	return revoked, nil
}

func (s *Store) AddMembershipAudited(ctx context.Context, userID, groupID, actor string) error {
	const operation = "add membership"
	return s.audited(ctx, operation, actor, func(tx *sql.Tx, at time.Time) error {
		username, _, err := userNameAndUID(ctx, tx, operation, userID)
		if err != nil {
			return err
		}
		group, err := groupInTx(ctx, tx, operation, groupID)
		if err != nil {
			return err
		}
		if err := addMembership(ctx, tx, operation, userID, groupID); err != nil {
			return err
		}
		return writeAudit(ctx, tx, operation, actor, "membership.add", userID, at, map[string]any{"username": username, "group": group.Name, "group_id": groupID})
	})
}

func (s *Store) RemoveMembershipAudited(ctx context.Context, userID, groupID, actor, adminGroup string) error {
	const operation = "remove membership"
	return s.audited(ctx, operation, actor, func(tx *sql.Tx, at time.Time) error {
		username, _, err := userNameAndUID(ctx, tx, operation, userID)
		if err != nil {
			return err
		}
		group, err := groupInTx(ctx, tx, operation, groupID)
		if err != nil {
			return err
		}
		wasAdmin := false
		if group.Name == adminGroup {
			if wasAdmin, err = isEnabledMember(ctx, tx, operation, userID, adminGroup); err != nil {
				return err
			}
		}
		removed, err := execCount(ctx, tx, operation, `DELETE FROM memberships WHERE user_id = ? AND group_id = ?`, userID, groupID)
		if err != nil {
			return err
		}
		if removed == 0 {
			return fmt.Errorf("%s: %w", operation, ErrNotFound)
		}
		if wasAdmin {
			if err := requireEnabledMember(ctx, tx, operation, adminGroup); err != nil {
				return err
			}
		}
		return writeAudit(ctx, tx, operation, actor, "membership.remove", userID, at, map[string]any{"username": username, "group": group.Name, "group_id": groupID})
	})
}

func (s *Store) CreateGroupAudited(ctx context.Context, input CreateGroupInput, actor string) (Group, error) {
	const operation = "create group"
	if err := s.checkOpen(operation); err != nil {
		return Group{}, err
	}
	prepared, err := s.prepareGroup(operation, input)
	if err != nil {
		return Group{}, err
	}
	var created Group
	err = s.audited(ctx, operation, actor, func(tx *sql.Tx, at time.Time) error {
		if err := requireFree(ctx, tx, operation, `SELECT 1 FROM groups WHERE name = ?`, prepared.Name, ErrGroupTaken); err != nil {
			return err
		}
		prepared.CreatedAt = at
		group, err := insertGroup(ctx, tx, operation, prepared)
		if err != nil {
			return err
		}
		created = group
		return writeAudit(ctx, tx, operation, actor, "group.create", group.ID, at, map[string]any{"group": group.Name, "gid_number": group.GIDNumber})
	})
	if err != nil {
		return Group{}, err
	}
	return created, nil
}

func (s *Store) UpdateGroupDescriptionAudited(ctx context.Context, groupID, description, actor string) (Group, error) {
	const operation = "update group"
	if err := validateGroupDescription(description); err != nil {
		return Group{}, invalid(operation, err)
	}
	var updated Group
	err := s.audited(ctx, operation, actor, func(tx *sql.Tx, at time.Time) error {
		group, err := groupInTx(ctx, tx, operation, groupID)
		if err != nil {
			return err
		}
		if _, err := tx.ExecContext(ctx, `UPDATE groups SET description = ? WHERE id = ?`, description, groupID); err != nil {
			return classify(operation, err)
		}
		if err := writeAudit(ctx, tx, operation, actor, "group.update", groupID, at, map[string]any{"group": group.Name}); err != nil {
			return err
		}
		updated, err = groupInTx(ctx, tx, operation, groupID)
		return err
	})
	if err != nil {
		return Group{}, err
	}
	return updated, nil
}

func (s *Store) DeleteGroupAudited(ctx context.Context, groupID, actor string, referenced []string) error {
	const operation = "delete group"
	return s.audited(ctx, operation, actor, func(tx *sql.Tx, at time.Time) error {
		group, err := groupInTx(ctx, tx, operation, groupID)
		if err != nil {
			return err
		}
		if contains(referenced, group.Name) {
			return fmt.Errorf("%s %q: %w", operation, group.Name, ErrReferencedGroup)
		}
		if _, err := tx.ExecContext(ctx, `DELETE FROM groups WHERE id = ?`, groupID); err != nil {
			return classify(operation, err)
		}
		return writeAudit(ctx, tx, operation, actor, "group.delete", groupID, at, map[string]any{"group": group.Name, "gid_number": group.GIDNumber})
	})
}

func (s *Store) RevokeInvitationAudited(ctx context.Context, invitationID, actor string) error {
	const operation = "revoke invitation"
	return s.audited(ctx, operation, actor, func(tx *sql.Tx, at time.Time) error {
		result, err := tx.ExecContext(ctx, `
UPDATE invitations
SET revoked_at = ?
WHERE id = ? AND used_at IS NULL AND revoked_at IS NULL`, timeToInt(at), invitationID)
		if err != nil {
			return classify(operation, err)
		}
		if err := requireInvitationTransition(ctx, tx, operation, invitationID, result); err != nil {
			return err
		}
		return writeAudit(ctx, tx, operation, actor, "invitation.revoke", invitationID, at, map[string]any{})
	})
}

func (s *Store) RevokeUserCredentialsAudited(ctx context.Context, userID, keepSessionID string, audit AppendAuditInput) (Revoked, error) {
	return s.RevokeUserCredentialsAuditedAt(ctx, userID, keepSessionID, audit, s.now())
}

func (s *Store) RevokeUserCredentialsAuditedAt(ctx context.Context, userID, keepSessionID string, audit AppendAuditInput, now time.Time) (Revoked, error) {
	const operation = "revoke user credentials"
	fields := map[string]any{}
	if audit.Detail != "" {
		if err := json.Unmarshal([]byte(audit.Detail), &fields); err != nil || fields == nil {
			return Revoked{}, invalid(operation, fmt.Errorf("audit detail is not a JSON object"))
		}
	}
	var revoked Revoked
	err := s.auditedAt(ctx, operation, audit.Actor, now, func(tx *sql.Tx, at time.Time) error {
		if keepSessionID != "" {
			var owner string
			err := tx.QueryRowContext(ctx, `SELECT user_id FROM forward_sessions WHERE id = ?`, keepSessionID).Scan(&owner)
			switch {
			case errors.Is(err, sql.ErrNoRows), err == nil && owner != userID:
				return invalid(operation, fmt.Errorf("kept session does not belong to the user"))
			case err != nil:
				return classify(operation, err)
			}
		}
		username, _, err := userNameAndUID(ctx, tx, operation, userID)
		if err != nil {
			return err
		}
		if revoked, err = revokeCredentials(ctx, tx, operation, userID, keepSessionID, true); err != nil {
			return err
		}
		fields["username"] = username
		return writeAudit(ctx, tx, operation, audit.Actor, audit.Action, audit.Subject, at, revoked.into(fields))
	})
	if err != nil {
		return Revoked{}, err
	}
	return revoked, nil
}

func (s *Store) SessionEpoch(ctx context.Context, sessionID, host string, now time.Time) (int64, error) {
	return s.epochBy(ctx, "session credentials epoch", sessionEpochQuery, sessionID, host, timeToInt(now))
}

func (s *Store) LiveSessionEpoch(ctx context.Context, userID, host string, since, now time.Time) (int64, error) {
	return s.epochBy(ctx, "live session credentials epoch", liveSessionEpochQuery, userID, host, timeToInt(since), timeToInt(now))
}

func (s *Store) epochBy(ctx context.Context, operation, query string, args ...any) (int64, error) {
	if err := s.checkOpen(operation); err != nil {
		return 0, err
	}
	var epoch int64
	err := s.sql.QueryRowContext(ctx, query, args...).Scan(&epoch)
	if errors.Is(err, sql.ErrNoRows) {
		return 0, fmt.Errorf("%s: %w", operation, ErrNotFound)
	}
	if err != nil {
		return 0, classify(operation, err)
	}
	return epoch, nil
}

func (s *Store) Dashboard(ctx context.Context, now time.Time) (DashboardCounts, error) {
	const operation = "dashboard counts"
	if err := s.checkOpen(operation); err != nil {
		return DashboardCounts{}, err
	}
	var c DashboardCounts
	if err := s.sql.QueryRowContext(ctx, dashboardQuery, timeToInt(now)).Scan(&c.Users, &c.DisabledUsers, &c.Groups, &c.PendingInvitations, &c.LiveSessions); err != nil {
		return DashboardCounts{}, classify(operation, err)
	}
	return c, nil
}

func (s *Store) GroupsForUsers(ctx context.Context, userIDs []string) (map[string][]Group, error) {
	const operation = "list groups for users"
	if err := s.checkOpen(operation); err != nil {
		return nil, err
	}
	groups := map[string][]Group{}
	if len(userIDs) == 0 {
		return groups, nil
	}
	ids, err := json.Marshal(userIDs)
	if err != nil {
		return nil, fmt.Errorf("%s: %w", operation, err)
	}
	rows, err := s.sql.QueryContext(ctx, `
SELECT m.user_id, g.id, g.name, g.description, g.gid_number, g.created_at
FROM memberships m
JOIN groups g ON g.id = m.group_id
WHERE m.user_id IN (SELECT value FROM json_each(?))
ORDER BY m.user_id, g.name, g.id`, string(ids))
	if err != nil {
		return nil, classify(operation, err)
	}
	defer rows.Close()
	for rows.Next() {
		var userID string
		var group Group
		var createdAt int64
		if err := rows.Scan(&userID, &group.ID, &group.Name, &group.Description, &group.GIDNumber, &createdAt); err != nil {
			return nil, classify(operation, err)
		}
		group.CreatedAt = intToTime(createdAt)
		groups[userID] = append(groups[userID], group)
	}
	if err := rows.Err(); err != nil {
		return nil, classify(operation, err)
	}
	return groups, nil
}

func (s *Store) audited(ctx context.Context, operation, actor string, write func(tx *sql.Tx, at time.Time) error) error {
	return s.auditedAt(ctx, operation, actor, s.now(), write)
}

func (s *Store) auditedAt(ctx context.Context, operation, actor string, now time.Time, write func(tx *sql.Tx, at time.Time) error) error {
	if err := s.checkOpen(operation); err != nil {
		return err
	}
	if err := requireValue("actor", actor); err != nil {
		return invalid(operation, err)
	}
	tx, err := s.sql.BeginTx(ctx, nil)
	if err != nil {
		return classify("begin "+operation, err)
	}
	defer tx.Rollback()
	if err := write(tx, canonicalTime(now)); err != nil {
		return err
	}
	return classify("commit "+operation, tx.Commit())
}

func revokeCredentials(ctx context.Context, q execer, operation, userID, keepSessionID string, advance bool) (Revoked, error) {
	if advance {
		advanced, err := execCount(ctx, q, operation+" credentials epoch", `UPDATE users SET credentials_epoch = credentials_epoch + 1 WHERE id = ?`, userID)
		if err != nil {
			return Revoked{}, err
		}
		if advanced == 0 {
			return Revoked{}, fmt.Errorf("%s: %w", operation, ErrNotFound)
		}
	}
	sessions, err := revokeForwardSessionsByUser(ctx, q, operation, userID, keepSessionID)
	if err != nil {
		return Revoked{}, err
	}
	var deleted [len(dexCredentialDeletes)]int64
	for i, d := range dexCredentialDeletes {
		if deleted[i], err = execCount(ctx, q, operation+" "+d.name, d.query, userID, DexConnectorID); err != nil {
			return Revoked{}, err
		}
	}
	return Revoked{Sessions: sessions, RefreshTokens: deleted[0], Codes: deleted[2], PendingSignIns: deleted[3]}, nil
}

func (r Revoked) into(fields map[string]any) map[string]any {
	fields["sessions"] = r.Sessions
	fields["refresh_tokens"] = r.RefreshTokens
	fields["codes"] = r.Codes
	fields["pending_sign_ins"] = r.PendingSignIns
	return fields
}

func writeAudit(ctx context.Context, q execer, operation, actor, action, subject string, at time.Time, fields map[string]any) error {
	detail, err := json.Marshal(fields)
	if err != nil {
		return fmt.Errorf("%s: encode audit detail: %w", operation, err)
	}
	_, err = appendAudit(ctx, q, operation+" audit", AppendAuditInput{Actor: actor, Action: action, Subject: subject, Detail: string(detail)}, at)
	return err
}

func execCount(ctx context.Context, q execer, operation, query string, args ...any) (int64, error) {
	result, err := q.ExecContext(ctx, query, args...)
	if err != nil {
		return 0, classify(operation, err)
	}
	count, err := result.RowsAffected()
	if err != nil {
		return 0, classify(operation+" rows affected", err)
	}
	return count, nil
}

func userInTx(ctx context.Context, q execer, operation, userID string) (User, error) {
	user, err := scanUser(q.QueryRowContext(ctx, `SELECT `+userColumns+` FROM users WHERE id = ?`, userID))
	if errors.Is(err, sql.ErrNoRows) {
		return User{}, fmt.Errorf("%s: %w", operation, ErrNotFound)
	}
	if err != nil {
		return User{}, classify(operation, err)
	}
	return user, nil
}

func groupInTx(ctx context.Context, q execer, operation, groupID string) (Group, error) {
	group, err := scanGroup(q.QueryRowContext(ctx, `SELECT id, name, description, gid_number, created_at FROM groups WHERE id = ?`, groupID))
	if errors.Is(err, sql.ErrNoRows) {
		return Group{}, fmt.Errorf("%s: %w", operation, ErrNotFound)
	}
	if err != nil {
		return Group{}, classify(operation, err)
	}
	return group, nil
}

func userNameAndUID(ctx context.Context, q execer, operation, userID string) (string, int, error) {
	var username string
	var uid int
	err := q.QueryRowContext(ctx, `SELECT username, uid_number FROM users WHERE id = ?`, userID).Scan(&username, &uid)
	if errors.Is(err, sql.ErrNoRows) {
		return "", 0, fmt.Errorf("%s: %w", operation, ErrNotFound)
	}
	if err != nil {
		return "", 0, classify(operation, err)
	}
	return username, uid, nil
}

func isEnabledMember(ctx context.Context, q execer, operation, userID, group string) (bool, error) {
	if group == "" {
		return false, nil
	}
	var count int
	if err := q.QueryRowContext(ctx, isEnabledMemberQuery, group, userID).Scan(&count); err != nil {
		return false, classify(operation+" admin membership", err)
	}
	return count > 0, nil
}

func requireEnabledMember(ctx context.Context, q execer, operation, group string) error {
	var count int
	if err := q.QueryRowContext(ctx, enabledMembersQuery, group).Scan(&count); err != nil {
		return classify(operation+" count admins", err)
	}
	if count == 0 {
		return fmt.Errorf("%s: %w", operation, ErrLastAdmin)
	}
	return nil
}

func contains(names []string, name string) bool {
	for _, existing := range names {
		if existing == name {
			return true
		}
	}
	return false
}