package db

import "context"
import "database/sql"
import "strings"
import "time"

import "codit/internal/models"
import "codit/internal/util"

func (s *Store) CreateUser(user models.User, passwordHash string) (models.User, error) {
	var id string
	var err error
	var nowUnix int64
	if user.ID == "" {
		id, err = util.NewID()
		if err != nil {
			return user, err
		}
		user.ID = id
	}
	nowUnix = time.Now().UTC().Unix()
	user.CreatedAt = nowUnix
	user.UpdatedAt = nowUnix
	_, err = s.Exec(`
		INSERT INTO users (public_id, username, display_name, email, password_hash, is_admin, disabled, auth_provider_id, totp_required, external_subject, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, CASE WHEN ? = '' THEN NULL ELSE (SELECT id FROM auth_providers WHERE public_id = ?) END, ?, ?, ?, ?)
	`, user.ID, user.Username, user.DisplayName, user.Email, passwordHash, user.IsAdmin, user.Disabled, user.AuthProviderID, user.AuthProviderID, user.TOTPRequired, user.ExternalSubject, nowUnix, nowUnix)
	return user, err
}

func (s *Store) UpdateUser(user models.User) error {
	var err error
	var nowUnix int64
	nowUnix = time.Now().UTC().Unix()
	user.UpdatedAt = nowUnix
	_, err = s.Exec(`UPDATE users SET display_name = ?, email = ?, is_admin = ?, disabled = ?, totp_required = ?, updated_at = ? WHERE public_id = ?`,
		user.DisplayName, user.Email, user.IsAdmin, user.Disabled, user.TOTPRequired, nowUnix, user.ID)
	return err
}

func (s *Store) UpdateUserWithPassword(ctx context.Context, user models.User, passwordHash string) error {
	var err error
	var nowUnix int64
	var tx txExecutor
	var owned bool

	nowUnix = time.Now().UTC().Unix()
	user.UpdatedAt = nowUnix
	tx, owned, err = s.beginContext(ctx)
	if err != nil {
		return err
	}
	_, err = tx.Exec(`UPDATE users SET display_name = ?, email = ?, is_admin = ?, disabled = ?, totp_required = ?, password_hash = ?, updated_at = ? WHERE public_id = ?`,
		user.DisplayName, user.Email, user.IsAdmin, user.Disabled, user.TOTPRequired, passwordHash, nowUnix, user.ID)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return err
	}
	err = commitIfOwned(tx, owned)
	if err != nil {
		return err
	}
	return nil
}

func (s *Store) SetUserPassword(userID, passwordHash string) error {
	var err error
	_, err = s.Exec(`UPDATE users SET password_hash = ?, updated_at = ? WHERE public_id = ?`, passwordHash, time.Now().UTC().Unix(), userID)
	return err
}

func (s *Store) GetUserByID(id string) (models.User, error) {
	var user models.User
	var row *sql.Row
	var err error
	row = s.QueryRow(`SELECT u.public_id, u.username, u.display_name, u.email, u.is_admin, u.disabled, COALESCE(ap.public_id, ''), COALESCE(t.enabled, 0), u.totp_required, CASE WHEN u.avatar_storage_path != '' THEN '/api/users/' || u.public_id || '/avatar?v=' || u.avatar_updated_at ELSE '' END, u.avatar_updated_at, u.created_at, u.updated_at FROM users u LEFT JOIN auth_providers ap ON ap.id = u.auth_provider_id LEFT JOIN user_totp t ON t.user_id = u.id WHERE u.public_id = ?`, id)
	err = row.Scan(&user.ID, &user.Username, &user.DisplayName, &user.Email, &user.IsAdmin, &user.Disabled, &user.AuthProviderID, &user.TOTPEnabled, &user.TOTPRequired, &user.AvatarURL, &user.AvatarUpdatedAt, &user.CreatedAt, &user.UpdatedAt)
	if err != nil {
		return user, err
	}
	return user, nil
}

func (s *Store) GetUserByUsername(username string) (models.User, string, error) {
	var user models.User
	var passwordHash sql.NullString
	var row *sql.Row
	var err error
	row = s.QueryRow(`SELECT u.public_id, u.username, u.display_name, u.email, u.is_admin, u.disabled, COALESCE(ap.public_id, ''), u.password_hash, COALESCE(t.enabled, 0), u.totp_required, CASE WHEN u.avatar_storage_path != '' THEN '/api/users/' || u.public_id || '/avatar?v=' || u.avatar_updated_at ELSE '' END, u.avatar_updated_at, u.created_at, u.updated_at FROM users u LEFT JOIN auth_providers ap ON ap.id = u.auth_provider_id LEFT JOIN user_totp t ON t.user_id = u.id WHERE u.username = ?`, username)
	err = row.Scan(&user.ID, &user.Username, &user.DisplayName, &user.Email, &user.IsAdmin, &user.Disabled, &user.AuthProviderID, &passwordHash, &user.TOTPEnabled, &user.TOTPRequired, &user.AvatarURL, &user.AvatarUpdatedAt, &user.CreatedAt, &user.UpdatedAt)
	if err != nil {
		return user, passwordHash.String, err
	}
	return user, passwordHash.String, nil
}

func (s *Store) ListUsers() ([]models.User, error) {
	var rows *sql.Rows
	var err error
	var users []models.User
	var u models.User
	rows, err = s.Query(`SELECT u.public_id, u.username, u.display_name, u.email, u.is_admin, u.disabled, COALESCE(ap.public_id, ''), COALESCE(t.enabled, 0), u.totp_required, CASE WHEN u.avatar_storage_path != '' THEN '/api/users/' || u.public_id || '/avatar?v=' || u.avatar_updated_at ELSE '' END, u.avatar_updated_at, u.created_at, u.updated_at FROM users u LEFT JOIN auth_providers ap ON ap.id = u.auth_provider_id LEFT JOIN user_totp t ON t.user_id = u.id ORDER BY u.username`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		u = models.User{}
		err = rows.Scan(&u.ID, &u.Username, &u.DisplayName, &u.Email, &u.IsAdmin, &u.Disabled, &u.AuthProviderID, &u.TOTPEnabled, &u.TOTPRequired, &u.AvatarURL, &u.AvatarUpdatedAt, &u.CreatedAt, &u.UpdatedAt)
		if err != nil {
			return nil, err
		}
		users = append(users, u)
	}
	return users, rows.Err()
}

func (s *Store) GetUserAvatar(id string) (models.UserAvatar, error) {
	var avatar models.UserAvatar
	var row *sql.Row
	var err error
	row = s.QueryRow(`SELECT public_id, avatar_storage_path, avatar_content_type, avatar_updated_at FROM users WHERE public_id = ? AND avatar_storage_path != ''`, id)
	err = row.Scan(&avatar.UserID, &avatar.StoragePath, &avatar.ContentType, &avatar.UpdatedAt)
	if err != nil {
		return avatar, err
	}
	return avatar, nil
}

func (s *Store) UpdateUserAvatar(id string, storagePath string, contentType string) error {
	var err error
	var nowUnix int64
	nowUnix = time.Now().UTC().Unix()
	_, err = s.Exec(`UPDATE users SET avatar_storage_path = ?, avatar_content_type = ?, avatar_updated_at = ?, updated_at = ? WHERE public_id = ?`,
		storagePath, contentType, nowUnix, nowUnix, id)
	return err
}

func (s *Store) ClearUserAvatar(id string) error {
	var err error
	_, err = s.Exec(`UPDATE users SET avatar_storage_path = '', avatar_content_type = '', avatar_updated_at = 0, updated_at = ? WHERE public_id = ?`,
		time.Now().UTC().Unix(), id)
	return err
}

func (s *Store) DeleteUser(ctx context.Context, id string) error {
	var tx txExecutor
	var owned bool
	var err error

	tx, owned, err = s.beginContext(ctx)
	if err != nil { return err }

	_, err = tx.Exec(`DELETE FROM project_role_bindings WHERE subject_type = 'user' AND subject_id = (SELECT id FROM users WHERE public_id = ?)`, id)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return err
	}
	_, err = tx.Exec(`DELETE FROM ssh_principal_grant_targets WHERE target_type = 'user' AND target_id = (SELECT id FROM users WHERE public_id = ?)`, id)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return err
	}
	_, err = tx.Exec(`DELETE FROM subject_permissions WHERE subject_type = 'user' AND subject_id = (SELECT id FROM users WHERE public_id = ?)`, id)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return err
	}
	// Remove the user's personal signing keys (and their private material). Only
	// personal keys carry owner_user_id; the gpg_keys FK also cascades, but this
	// is explicit and runs while the user row still resolves.
	_, err = tx.Exec(`DELETE FROM gpg_keys WHERE owner_user_id = (SELECT id FROM users WHERE public_id = ?)`, id)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return err
	}
	_, err = tx.Exec(`DELETE FROM users WHERE public_id = ?`, id)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return err
	}
	err = commitIfOwned(tx, owned)
	return err
}

func (s *Store) GetUserByProviderAndSub(providerPublicID string, sub string) (models.User, error) {
	var user models.User
	var row *sql.Row
	var err error
	row = s.QueryRow(`
		SELECT u.public_id, u.username, u.display_name, u.email, u.is_admin, u.disabled,
		       COALESCE(ap.public_id, ''), u.external_subject, COALESCE(t.enabled, 0), u.totp_required,
		       CASE WHEN u.avatar_storage_path != '' THEN '/api/users/' || u.public_id || '/avatar?v=' || u.avatar_updated_at ELSE '' END,
		       u.avatar_updated_at, u.created_at, u.updated_at
		FROM users u
		LEFT JOIN auth_providers ap ON ap.id = u.auth_provider_id
		LEFT JOIN user_totp t ON t.user_id = u.id
		WHERE ap.public_id = ? AND u.external_subject = ?
	`, strings.TrimSpace(providerPublicID), strings.TrimSpace(sub))
	err = row.Scan(&user.ID, &user.Username, &user.DisplayName, &user.Email, &user.IsAdmin, &user.Disabled,
		&user.AuthProviderID, &user.ExternalSubject, &user.TOTPEnabled, &user.TOTPRequired,
		&user.AvatarURL, &user.AvatarUpdatedAt, &user.CreatedAt, &user.UpdatedAt)
	if err != nil { return user, err }
	return user, nil
}

func (s *Store) SetUserDisabled(id string, disabled bool) error {
	var err error
	_, err = s.Exec(`UPDATE users SET disabled = ?, updated_at = ? WHERE public_id = ?`, disabled, time.Now().UTC().Unix(), id)
	return err
}

func (s *Store) GetUserByAPIKeyHash(ctx context.Context, tokenHash string) (models.User, error) {
	var user models.User
	var tx txExecutor
	var owned bool
	var row *sql.Row
	var keyID int64
	var nowUnix int64
	var err error

	tx, owned, err = s.beginContext(ctx)
	if err != nil { return user, err }

	nowUnix = time.Now().UTC().Unix()
	row = tx.QueryRow(`
		SELECT u.public_id, u.username, u.display_name, u.email, u.is_admin, u.disabled, COALESCE(ap.public_id, ''), u.created_at, u.updated_at, k.id
		FROM api_keys k
		JOIN users u ON u.id = k.user_id
		LEFT JOIN auth_providers ap ON ap.id = u.auth_provider_id
		WHERE k.token_hash = ?
		  AND u.disabled = 0
		  AND k.disabled = 0
		  AND (k.expires_at = 0 OR k.expires_at > ?)
		LIMIT 1
	`, tokenHash, nowUnix)
	err = row.Scan(&user.ID, &user.Username, &user.DisplayName, &user.Email, &user.IsAdmin, &user.Disabled, &user.AuthProviderID, &user.CreatedAt, &user.UpdatedAt, &keyID)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return user, err
	}
	nowUnix = time.Now().UTC().Unix()
	_, err = tx.Exec(`UPDATE api_keys SET last_used_at = ? WHERE id = ?`, nowUnix, keyID)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return user, err
	}
	err = commitIfOwned(tx, owned)
	if err != nil {
		return user, err
	}
	return user, nil
}