package db

import "database/sql"
import "encoding/json"
import "errors"
import "sort"
import "strings"
import "time"

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

func (s *Store) ListSSHServers() ([]models.SSHServer, error) {
	var rows *sql.Rows
	var err error
	var items []models.SSHServer
	var item models.SSHServer
	var tagsJSON string

	rows, err = s.Query(`SELECT public_id, name, host, port, description, tags_json, enabled, created_by_kind, created_by_subject_id, created_by_subject_name, created_at, updated_at
		FROM ssh_servers
		ORDER BY name, host, port`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		err = rows.Scan(&item.ID, &item.Name, &item.Host, &item.Port, &item.Description, &tagsJSON, &item.Enabled, &item.CreatedByKind, &item.CreatedBySubjectID, &item.CreatedBySubjectName, &item.CreatedAt, &item.UpdatedAt)
		if err != nil {
			return nil, err
		}
		item.Tags, err = decodeStringList(tagsJSON)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	err = rows.Err()
	if err != nil {
		return nil, err
	}
	return items, nil
}

func (s *Store) GetSSHServer(id string) (models.SSHServer, error) {
	var row *sql.Row
	var item models.SSHServer
	var tagsJSON string
	var err error

	row = s.QueryRow(`SELECT public_id, name, host, port, description, tags_json, enabled, created_by_kind, created_by_subject_id, created_by_subject_name, created_at, updated_at
		FROM ssh_servers
		WHERE public_id = ?`, strings.TrimSpace(id))
	err = row.Scan(&item.ID, &item.Name, &item.Host, &item.Port, &item.Description, &tagsJSON, &item.Enabled, &item.CreatedByKind, &item.CreatedBySubjectID, &item.CreatedBySubjectName, &item.CreatedAt, &item.UpdatedAt)
	if err != nil {
		return item, err
	}
	item.Tags, err = decodeStringList(tagsJSON)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) ListSSHServersForUser(userID string) ([]models.SSHServer, error) {
	var rows *sql.Rows
	var err error
	var items []models.SSHServer
	var item models.SSHServer
	var tagsJSON string
	var trimmedUserID string

	trimmedUserID = strings.TrimSpace(userID)
	rows, err = s.Query(`SELECT public_id, name, host, port, description, tags_json, enabled, created_by_kind, created_by_subject_id, created_by_subject_name, created_at, updated_at
		FROM ssh_servers
		WHERE created_by_kind = 'user' AND created_by_subject_id = ?
		ORDER BY name, host, port`, trimmedUserID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		err = rows.Scan(&item.ID, &item.Name, &item.Host, &item.Port, &item.Description, &tagsJSON, &item.Enabled, &item.CreatedByKind, &item.CreatedBySubjectID, &item.CreatedBySubjectName, &item.CreatedAt, &item.UpdatedAt)
		if err != nil {
			return nil, err
		}
		item.Tags, err = decodeStringList(tagsJSON)
		if err != nil {
			return nil, err
		}
		item.Editable = item.CreatedByKind == "user" && item.CreatedBySubjectID == trimmedUserID
		items = append(items, item)
	}
	err = rows.Err()
	if err != nil {
		return nil, err
	}
	return items, nil
}

func (s *Store) GetSSHServerForUser(userID string, id string) (models.SSHServer, error) {
	var item models.SSHServer
	var items []models.SSHServer
	var trimmedID string
	var i int
	var err error

	trimmedID = strings.TrimSpace(id)
	items, err = s.ListSSHServersForUser(userID)
	if err != nil {
		return item, err
	}
	for i = 0; i < len(items); i++ {
		if items[i].ID == trimmedID {
			return items[i], nil
		}
	}
	return item, sql.ErrNoRows
}

func (s *Store) GetOwnedSSHServerForUser(userID string, id string) (models.SSHServer, error) {
	var item models.SSHServer
	var trimmedUserID string
	var err error

	trimmedUserID = strings.TrimSpace(userID)
	item, err = s.GetSSHServer(strings.TrimSpace(id))
	if err != nil {
		return item, err
	}
	item.Editable = item.CreatedByKind == "user" && item.CreatedBySubjectID == trimmedUserID
	if !item.Editable {
		return item, sql.ErrNoRows
	}
	return item, nil
}

func (s *Store) CreateSSHServer(item models.SSHServer) (models.SSHServer, error) {
	var err error
	var tagsJSON string
	var now int64

	if strings.TrimSpace(item.ID) == "" {
		item.ID, err = util.NewID()
		if err != nil {
			return item, err
		}
	}
	if item.Port <= 0 {
		item.Port = 22
	}
	item.Tags = normalizeStringList(item.Tags)
	tagsJSON, err = encodeStringList(item.Tags)
	if err != nil {
		return item, err
	}
	now = time.Now().UTC().Unix()
	item.CreatedAt = now
	item.UpdatedAt = now

	_, err = s.Exec(`INSERT INTO ssh_servers (
		public_id,
		name,
		host,
		port,
		description,
		tags_json,
		enabled,
		created_by_kind,
		created_by_subject_id,
		created_by_subject_name,
		created_at,
		updated_at
	) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		item.ID,
		strings.TrimSpace(item.Name),
		strings.TrimSpace(item.Host),
		item.Port,
		strings.TrimSpace(item.Description),
		tagsJSON,
		item.Enabled,
		strings.TrimSpace(item.CreatedByKind),
		strings.TrimSpace(item.CreatedBySubjectID),
		strings.TrimSpace(item.CreatedBySubjectName),
		item.CreatedAt,
		item.UpdatedAt,
	)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) UpdateSSHServer(item models.SSHServer) (models.SSHServer, error) {
	var err error
	var tagsJSON string

	if item.Port <= 0 {
		item.Port = 22
	}
	item.Tags = normalizeStringList(item.Tags)
	tagsJSON, err = encodeStringList(item.Tags)
	if err != nil {
		return item, err
	}
	item.UpdatedAt = time.Now().UTC().Unix()

	_, err = s.Exec(`UPDATE ssh_servers
		SET name = ?, host = ?, port = ?, description = ?, tags_json = ?, enabled = ?, updated_at = ?
		WHERE public_id = ?`,
		strings.TrimSpace(item.Name),
		strings.TrimSpace(item.Host),
		item.Port,
		strings.TrimSpace(item.Description),
		tagsJSON,
		item.Enabled,
		item.UpdatedAt,
		strings.TrimSpace(item.ID),
	)
	if err != nil {
		return item, err
	}
	item, err = s.GetSSHServer(item.ID)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) DeleteSSHServer(id string) error {
	var err error

	_, err = s.Exec(`DELETE FROM ssh_servers WHERE public_id = ?`, strings.TrimSpace(id))
	return err
}

func (s *Store) ListSSHAccessProfiles() ([]models.SSHAccessProfile, error) {
	var rows *sql.Rows
	var err error
	var items []models.SSHAccessProfile
	var item models.SSHAccessProfile
	var principalsJSON string
	var grantIDsJSON string
	var serverTagsJSON string

	rows, err = s.Query(`SELECT
		p.public_id,
		p.server_public_id,
		p.name,
		p.description,
		p.remote_username,
		p.auth_method,
		p.owner_scope,
		p.owner_user_public_id,
		p.allow_user_edit,
		p.enabled,
		COALESCE(p.secret_public_id, ''),
		p.auth_public_key,
		p.auth_public_key_fingerprint,
		COALESCE(p.ssh_user_ca_public_id, ''),
		p.ssh_principal_mode,
		p.ssh_principals_json,
		p.ssh_principal_grant_ids_json,
		p.default_valid_seconds,
		p.max_valid_seconds,
		p.created_by_kind,
		p.created_by_subject_id,
		p.created_by_subject_name,
		p.created_at,
		p.updated_at,
		s.public_id,
		s.name,
		s.host,
		s.port,
		s.description,
		s.tags_json,
		s.enabled,
		s.created_by_kind,
		s.created_by_subject_id,
		s.created_by_subject_name,
		s.created_at,
		s.updated_at
		FROM ssh_access_profiles p
		JOIN ssh_servers s ON s.public_id = p.server_public_id
		ORDER BY s.name, p.name`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		err = rows.Scan(
			&item.ID,
			&item.ServerID,
			&item.Name,
			&item.Description,
			&item.RemoteUsername,
			&item.AuthMethod,
			&item.OwnerScope,
			&item.OwnerUserID,
			&item.AllowUserEdit,
			&item.Enabled,
			&item.SecretID,
			&item.AuthPublicKey,
			&item.AuthPublicKeyFingerprint,
			&item.SSHUserCAID,
			&item.SSHPrincipalMode,
			&principalsJSON,
			&grantIDsJSON,
			&item.DefaultValidSeconds,
			&item.MaxValidSeconds,
			&item.CreatedByKind,
			&item.CreatedBySubjectID,
			&item.CreatedBySubjectName,
			&item.CreatedAt,
			&item.UpdatedAt,
			&item.Server.ID,
			&item.Server.Name,
			&item.Server.Host,
			&item.Server.Port,
			&item.Server.Description,
			&serverTagsJSON,
			&item.Server.Enabled,
			&item.Server.CreatedByKind,
			&item.Server.CreatedBySubjectID,
			&item.Server.CreatedBySubjectName,
			&item.Server.CreatedAt,
			&item.Server.UpdatedAt,
		)
		if err != nil {
			return nil, err
		}
		item.SSHPrincipals, err = decodeStringList(principalsJSON)
		if err != nil {
			return nil, err
		}
		item.SSHPrincipalGrantIDs, err = decodeStringList(grantIDsJSON)
		if err != nil {
			return nil, err
		}
		item.Server.Tags, err = decodeStringList(serverTagsJSON)
		if err != nil {
			return nil, err
		}
		item.Targets, err = s.listSSHAccessProfileTargets(item.ID)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	err = rows.Err()
	if err != nil {
		return nil, err
	}
	return items, nil
}

func (s *Store) GetSSHAccessProfile(id string) (models.SSHAccessProfile, error) {
	var row *sql.Row
	var item models.SSHAccessProfile
	var err error
	var principalsJSON string
	var grantIDsJSON string
	var serverTagsJSON string

	row = s.QueryRow(`SELECT
		p.public_id,
		p.server_public_id,
		p.name,
		p.description,
		p.remote_username,
		p.auth_method,
		p.owner_scope,
		p.owner_user_public_id,
		p.allow_user_edit,
		p.enabled,
		COALESCE(p.secret_public_id, ''),
		p.auth_public_key,
		p.auth_public_key_fingerprint,
		COALESCE(p.ssh_user_ca_public_id, ''),
		p.ssh_principal_mode,
		p.ssh_principals_json,
		p.ssh_principal_grant_ids_json,
		p.default_valid_seconds,
		p.max_valid_seconds,
		p.created_by_kind,
		p.created_by_subject_id,
		p.created_by_subject_name,
		p.created_at,
		p.updated_at,
		s.public_id,
		s.name,
		s.host,
		s.port,
		s.description,
		s.tags_json,
		s.enabled,
		s.created_by_kind,
		s.created_by_subject_id,
		s.created_by_subject_name,
		s.created_at,
		s.updated_at
		FROM ssh_access_profiles p
		JOIN ssh_servers s ON s.public_id = p.server_public_id
		WHERE p.public_id = ?`, strings.TrimSpace(id))
	err = row.Scan(
		&item.ID,
		&item.ServerID,
		&item.Name,
		&item.Description,
		&item.RemoteUsername,
		&item.AuthMethod,
		&item.OwnerScope,
		&item.OwnerUserID,
		&item.AllowUserEdit,
		&item.Enabled,
		&item.SecretID,
		&item.AuthPublicKey,
		&item.AuthPublicKeyFingerprint,
		&item.SSHUserCAID,
		&item.SSHPrincipalMode,
		&principalsJSON,
		&grantIDsJSON,
		&item.DefaultValidSeconds,
		&item.MaxValidSeconds,
		&item.CreatedByKind,
		&item.CreatedBySubjectID,
		&item.CreatedBySubjectName,
		&item.CreatedAt,
		&item.UpdatedAt,
		&item.Server.ID,
		&item.Server.Name,
		&item.Server.Host,
		&item.Server.Port,
		&item.Server.Description,
		&serverTagsJSON,
		&item.Server.Enabled,
		&item.Server.CreatedByKind,
		&item.Server.CreatedBySubjectID,
		&item.Server.CreatedBySubjectName,
		&item.Server.CreatedAt,
		&item.Server.UpdatedAt,
	)
	if err != nil {
		return item, err
	}
	item.SSHPrincipals, err = decodeStringList(principalsJSON)
	if err != nil {
		return item, err
	}
	item.SSHPrincipalGrantIDs, err = decodeStringList(grantIDsJSON)
	if err != nil {
		return item, err
	}
	item.Server.Tags, err = decodeStringList(serverTagsJSON)
	if err != nil {
		return item, err
	}
	item.Targets, err = s.listSSHAccessProfileTargets(item.ID)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) CreateSSHAccessProfile(item models.SSHAccessProfile) (models.SSHAccessProfile, error) {
	var tx *sql.Tx
	var owned bool
	var err error
	var now int64
	var principalsJSON string
	var grantIDsJSON string
	var i int
	var target models.SSHAccessProfileTarget
	var secret models.SSHSecret

	if strings.TrimSpace(item.Name) == "" {
		return item, errors.New("name is required")
	}
	if strings.TrimSpace(item.ServerID) == "" {
		return item, errors.New("server_id is required")
	}
	if strings.TrimSpace(item.RemoteUsername) == "" {
		return item, errors.New("remote_username is required")
	}
	if strings.TrimSpace(item.AuthMethod) == "" {
		return item, errors.New("auth_method is required")
	}
	if strings.TrimSpace(item.ID) == "" {
		item.ID, err = util.NewID()
		if err != nil {
			return item, err
		}
	}
	if item.DefaultValidSeconds <= 0 {
		item.DefaultValidSeconds = 3600
	}
	if item.MaxValidSeconds <= 0 {
		item.MaxValidSeconds = item.DefaultValidSeconds
	}
	item.SSHPrincipals = normalizeStringList(item.SSHPrincipals)
	item.SSHPrincipalGrantIDs = normalizeStringList(item.SSHPrincipalGrantIDs)
	principalsJSON, err = encodeStringList(item.SSHPrincipals)
	if err != nil {
		return item, err
	}
	grantIDsJSON, err = encodeStringList(item.SSHPrincipalGrantIDs)
	if err != nil {
		return item, err
	}
	now = time.Now().UTC().Unix()
	item.CreatedAt = now
	item.UpdatedAt = now
	tx, owned, err = s.begin()
	if err != nil {
		return item, err
	}
	if strings.TrimSpace(item.SecretPayload) != "" {
		secret = models.SSHSecret{
			ID:                   item.SecretID,
			Kind:                 "private_key",
			Payload:              item.SecretPayload,
			CreatedByKind:        item.CreatedByKind,
			CreatedBySubjectID:   item.CreatedBySubjectID,
			CreatedBySubjectName: item.CreatedBySubjectName,
		}
		secret, err = createSSHSecretTx(tx, secret)
		if err != nil {
			rollbackIfOwned(tx, owned)
			return item, err
		}
		item.SecretID = secret.ID
	}
	_, err = tx.Exec(`INSERT INTO ssh_access_profiles (
		public_id,
		server_public_id,
		name,
		description,
		remote_username,
		auth_method,
		owner_scope,
		owner_user_public_id,
		allow_user_edit,
		enabled,
		secret_public_id,
		auth_public_key,
		auth_public_key_fingerprint,
		ssh_user_ca_public_id,
		ssh_principal_mode,
		ssh_principals_json,
		ssh_principal_grant_ids_json,
		default_valid_seconds,
		max_valid_seconds,
		created_by_kind,
		created_by_subject_id,
		created_by_subject_name,
		created_at,
		updated_at
	) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		item.ID,
		strings.TrimSpace(item.ServerID),
		strings.TrimSpace(item.Name),
		strings.TrimSpace(item.Description),
		strings.TrimSpace(item.RemoteUsername),
		strings.TrimSpace(item.AuthMethod),
		strings.TrimSpace(item.OwnerScope),
		strings.TrimSpace(item.OwnerUserID),
		item.AllowUserEdit,
		item.Enabled,
		emptyStringToNil(item.SecretID),
		strings.TrimSpace(item.AuthPublicKey),
		strings.TrimSpace(item.AuthPublicKeyFingerprint),
		emptyStringToNil(item.SSHUserCAID),
		strings.TrimSpace(item.SSHPrincipalMode),
		principalsJSON,
		grantIDsJSON,
		item.DefaultValidSeconds,
		item.MaxValidSeconds,
		strings.TrimSpace(item.CreatedByKind),
		strings.TrimSpace(item.CreatedBySubjectID),
		strings.TrimSpace(item.CreatedBySubjectName),
		item.CreatedAt,
		item.UpdatedAt,
	)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return item, err
	}
	for i = 0; i < len(item.Targets); i++ {
		target = item.Targets[i]
		err = insertSSHAccessProfileTargetTx(tx, item.ID, target.TargetType, target.TargetID, now)
		if err != nil {
			rollbackIfOwned(tx, owned)
			return item, err
		}
	}
	err = commitIfOwned(tx, owned)
	if err != nil {
		return item, err
	}
	item, err = s.GetSSHAccessProfile(item.ID)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) UpdateSSHAccessProfile(item models.SSHAccessProfile) (models.SSHAccessProfile, error) {
	var tx *sql.Tx
	var owned bool
	var err error
	var principalsJSON string
	var grantIDsJSON string
	var i int
	var target models.SSHAccessProfileTarget
	var secret models.SSHSecret

	if strings.TrimSpace(item.Name) == "" {
		return item, errors.New("name is required")
	}
	if strings.TrimSpace(item.ServerID) == "" {
		return item, errors.New("server_id is required")
	}
	if strings.TrimSpace(item.RemoteUsername) == "" {
		return item, errors.New("remote_username is required")
	}
	if strings.TrimSpace(item.AuthMethod) == "" {
		return item, errors.New("auth_method is required")
	}
	if item.DefaultValidSeconds <= 0 {
		item.DefaultValidSeconds = 3600
	}
	if item.MaxValidSeconds <= 0 {
		item.MaxValidSeconds = item.DefaultValidSeconds
	}
	item.SSHPrincipals = normalizeStringList(item.SSHPrincipals)
	item.SSHPrincipalGrantIDs = normalizeStringList(item.SSHPrincipalGrantIDs)
	principalsJSON, err = encodeStringList(item.SSHPrincipals)
	if err != nil {
		return item, err
	}
	grantIDsJSON, err = encodeStringList(item.SSHPrincipalGrantIDs)
	if err != nil {
		return item, err
	}
	item.UpdatedAt = time.Now().UTC().Unix()
	tx, owned, err = s.begin()
	if err != nil {
		return item, err
	}
	if strings.TrimSpace(item.SecretPayload) != "" {
		secret = models.SSHSecret{
			ID:                   item.SecretID,
			Kind:                 "private_key",
			Payload:              item.SecretPayload,
			CreatedByKind:        item.CreatedByKind,
			CreatedBySubjectID:   item.CreatedBySubjectID,
			CreatedBySubjectName: item.CreatedBySubjectName,
		}
		if strings.TrimSpace(item.SecretID) == "" {
			secret, err = createSSHSecretTx(tx, secret)
			if err != nil {
				rollbackIfOwned(tx, owned)
				return item, err
			}
		} else {
			secret, err = updateSSHSecretTx(tx, secret)
			if err != nil {
				rollbackIfOwned(tx, owned)
				return item, err
			}
		}
		item.SecretID = secret.ID
	}
	_, err = tx.Exec(`UPDATE ssh_access_profiles
		SET server_public_id = ?, name = ?, description = ?, remote_username = ?, auth_method = ?, owner_scope = ?, owner_user_public_id = ?, allow_user_edit = ?, enabled = ?, secret_public_id = ?, auth_public_key = ?, auth_public_key_fingerprint = ?, ssh_user_ca_public_id = ?, ssh_principal_mode = ?, ssh_principals_json = ?, ssh_principal_grant_ids_json = ?, default_valid_seconds = ?, max_valid_seconds = ?, updated_at = ?
		WHERE public_id = ?`,
		strings.TrimSpace(item.ServerID),
		strings.TrimSpace(item.Name),
		strings.TrimSpace(item.Description),
		strings.TrimSpace(item.RemoteUsername),
		strings.TrimSpace(item.AuthMethod),
		strings.TrimSpace(item.OwnerScope),
		strings.TrimSpace(item.OwnerUserID),
		item.AllowUserEdit,
		item.Enabled,
		emptyStringToNil(item.SecretID),
		strings.TrimSpace(item.AuthPublicKey),
		strings.TrimSpace(item.AuthPublicKeyFingerprint),
		emptyStringToNil(item.SSHUserCAID),
		strings.TrimSpace(item.SSHPrincipalMode),
		principalsJSON,
		grantIDsJSON,
		item.DefaultValidSeconds,
		item.MaxValidSeconds,
		item.UpdatedAt,
		strings.TrimSpace(item.ID),
	)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return item, err
	}
	_, err = tx.Exec(`DELETE FROM ssh_access_profile_targets WHERE profile_public_id = ?`, strings.TrimSpace(item.ID))
	if err != nil {
		rollbackIfOwned(tx, owned)
		return item, err
	}
	for i = 0; i < len(item.Targets); i++ {
		target = item.Targets[i]
		err = insertSSHAccessProfileTargetTx(tx, item.ID, target.TargetType, target.TargetID, item.UpdatedAt)
		if err != nil {
			rollbackIfOwned(tx, owned)
			return item, err
		}
	}
	err = commitIfOwned(tx, owned)
	if err != nil {
		return item, err
	}
	item, err = s.GetSSHAccessProfile(item.ID)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) DeleteSSHAccessProfile(id string) error {
	var tx *sql.Tx
	var owned bool
	var row *sql.Row
	var secretID sql.NullString
	var err error

	tx, owned, err = s.begin()
	if err != nil {
		return err
	}
	row = tx.QueryRow(`SELECT secret_public_id FROM ssh_access_profiles WHERE public_id = ?`, strings.TrimSpace(id))
	err = row.Scan(&secretID)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return err
	}
	_, err = tx.Exec(`DELETE FROM ssh_access_profiles WHERE public_id = ?`, strings.TrimSpace(id))
	if err != nil {
		rollbackIfOwned(tx, owned)
		return err
	}
	if secretID.Valid && strings.TrimSpace(secretID.String) != "" {
		_, err = tx.Exec(`DELETE FROM ssh_secrets WHERE public_id = ?`, strings.TrimSpace(secretID.String))
		if err != nil {
			rollbackIfOwned(tx, owned)
			return err
		}
	}
	err = commitIfOwned(tx, owned)
	if err != nil {
		return err
	}
	return nil
}

func (s *Store) ListSSHAccessProfilesForUser(userID string) ([]models.SSHAccessProfile, error) {
	var rows *sql.Rows
	var err error
	var items []models.SSHAccessProfile
	var item models.SSHAccessProfile
	var principalsJSON string
	var grantIDsJSON string
	var serverTagsJSON string
	var trimmedUserID string

	trimmedUserID = strings.TrimSpace(userID)
	rows, err = s.Query(`SELECT DISTINCT
		p.public_id,
		p.server_public_id,
		p.name,
		p.description,
		p.remote_username,
		p.auth_method,
		p.owner_scope,
		p.owner_user_public_id,
		p.allow_user_edit,
		p.enabled,
		COALESCE(p.secret_public_id, ''),
		p.auth_public_key,
		p.auth_public_key_fingerprint,
		COALESCE(p.ssh_user_ca_public_id, ''),
		p.ssh_principal_mode,
		p.ssh_principals_json,
		p.ssh_principal_grant_ids_json,
		p.default_valid_seconds,
		p.max_valid_seconds,
		p.created_by_kind,
		p.created_by_subject_id,
		p.created_by_subject_name,
		p.created_at,
		p.updated_at,
		s.public_id,
		s.name,
		s.host,
		s.port,
		s.description,
		s.tags_json,
		s.enabled,
		s.created_by_kind,
		s.created_by_subject_id,
		s.created_by_subject_name,
		s.created_at,
		s.updated_at
		FROM ssh_access_profiles p
		JOIN ssh_servers s ON s.public_id = p.server_public_id
		LEFT JOIN ssh_access_profile_targets t ON t.profile_public_id = p.public_id
		LEFT JOIN user_groups ug ON t.target_type = 'group' AND ug.public_id = t.target_public_id
		LEFT JOIN user_group_members gm ON t.target_type = 'group' AND gm.group_id = ug.id
		LEFT JOIN users gu ON gm.user_id = gu.id
		WHERE p.enabled = 1
			AND s.enabled = 1
			AND (
				(p.owner_scope = 'user' AND p.owner_user_public_id = ?)
				OR
				(
					p.owner_scope = 'admin_shared'
					AND (
						(t.target_type = 'user' AND t.target_public_id = ?)
						OR
						(t.target_type = 'group' AND ug.disabled = 0 AND gu.public_id = ?)
					)
				)
			)
		ORDER BY s.name, p.name`, trimmedUserID, trimmedUserID, trimmedUserID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		err = rows.Scan(
			&item.ID,
			&item.ServerID,
			&item.Name,
			&item.Description,
			&item.RemoteUsername,
			&item.AuthMethod,
			&item.OwnerScope,
			&item.OwnerUserID,
			&item.AllowUserEdit,
			&item.Enabled,
			&item.SecretID,
			&item.AuthPublicKey,
			&item.AuthPublicKeyFingerprint,
			&item.SSHUserCAID,
			&item.SSHPrincipalMode,
			&principalsJSON,
			&grantIDsJSON,
			&item.DefaultValidSeconds,
			&item.MaxValidSeconds,
			&item.CreatedByKind,
			&item.CreatedBySubjectID,
			&item.CreatedBySubjectName,
			&item.CreatedAt,
			&item.UpdatedAt,
			&item.Server.ID,
			&item.Server.Name,
			&item.Server.Host,
			&item.Server.Port,
			&item.Server.Description,
			&serverTagsJSON,
			&item.Server.Enabled,
			&item.Server.CreatedByKind,
			&item.Server.CreatedBySubjectID,
			&item.Server.CreatedBySubjectName,
			&item.Server.CreatedAt,
			&item.Server.UpdatedAt,
		)
		if err != nil {
			return nil, err
		}
		item.SSHPrincipals, err = decodeStringList(principalsJSON)
		if err != nil {
			return nil, err
		}
		item.SSHPrincipalGrantIDs, err = decodeStringList(grantIDsJSON)
		if err != nil {
			return nil, err
		}
		item.Server.Tags, err = decodeStringList(serverTagsJSON)
		if err != nil {
			return nil, err
		}
		item.Targets, err = s.listSSHAccessProfileTargets(item.ID)
		if err != nil {
			return nil, err
		}
		item.Editable = item.OwnerScope == "user" && item.OwnerUserID == trimmedUserID
		items = append(items, item)
	}
	err = rows.Err()
	if err != nil {
		return nil, err
	}
	return items, nil
}

func (s *Store) GetSSHAccessProfileForUser(userID string, id string) (models.SSHAccessProfile, error) {
	var items []models.SSHAccessProfile
	var item models.SSHAccessProfile
	var i int
	var err error

	items, err = s.ListSSHAccessProfilesForUser(userID)
	if err != nil {
		return item, err
	}
	for i = 0; i < len(items); i++ {
		if items[i].ID == strings.TrimSpace(id) {
			return items[i], nil
		}
	}
	return item, sql.ErrNoRows
}

func (s *Store) listSSHAccessProfileTargets(profileID string) ([]models.SSHAccessProfileTarget, error) {
	var rows *sql.Rows
	var err error
	var items []models.SSHAccessProfileTarget
	var item models.SSHAccessProfileTarget

	rows, err = s.Query(`SELECT
		p.public_id,
		t.target_type,
		t.target_public_id,
		CASE
			WHEN t.target_type = 'user' THEN COALESCE(CASE WHEN u.display_name != '' THEN u.display_name || ' (' || u.username || ')' ELSE u.username END, t.target_public_id)
			ELSE COALESCE(g.name, t.target_public_id)
		END,
		CASE
			WHEN t.target_type = 'user' THEN CASE WHEN u.public_id IS NOT NULL AND u.disabled = 0 THEN 1 ELSE 0 END
			ELSE CASE WHEN g.public_id IS NOT NULL AND g.disabled = 0 THEN 1 ELSE 0 END
		END,
		t.created_at
		FROM ssh_access_profile_targets t
		JOIN ssh_access_profiles p ON p.public_id = t.profile_public_id
		LEFT JOIN users u ON t.target_type = 'user' AND u.public_id = t.target_public_id
		LEFT JOIN user_groups g ON t.target_type = 'group' AND g.public_id = t.target_public_id
		WHERE p.public_id = ?
		ORDER BY t.target_type, t.target_public_id`, strings.TrimSpace(profileID))
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		err = rows.Scan(&item.ProfileID, &item.TargetType, &item.TargetID, &item.TargetName, &item.TargetActive, &item.CreatedAt)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	err = rows.Err()
	if err != nil {
		return nil, err
	}
	return items, nil
}

func (s *Store) GetSSHSecret(id string) (models.SSHSecret, error) {
	var row *sql.Row
	var item models.SSHSecret
	var err error

	row = s.QueryRow(`SELECT public_id, kind, payload, metadata_json, created_by_kind, created_by_subject_id, created_by_subject_name, created_at, updated_at
		FROM ssh_secrets
		WHERE public_id = ?`, strings.TrimSpace(id))
	err = row.Scan(&item.ID, &item.Kind, &item.Payload, &item.MetadataJSON, &item.CreatedByKind, &item.CreatedBySubjectID, &item.CreatedBySubjectName, &item.CreatedAt, &item.UpdatedAt)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) ListSSHServerHostKeys(serverID string) ([]models.SSHServerHostKey, error) {
	var rows *sql.Rows
	var items []models.SSHServerHostKey
	var item models.SSHServerHostKey
	var err error

	rows, err = s.Query(`SELECT public_id, server_public_id, algorithm, public_key, fingerprint, created_at
		FROM ssh_server_host_keys
		WHERE server_public_id = ?
		ORDER BY created_at, fingerprint`, strings.TrimSpace(serverID))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&item.ID, &item.ServerID, &item.Algorithm, &item.PublicKey, &item.Fingerprint, &item.CreatedAt)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	err = rows.Err()
	if err != nil {
		return nil, err
	}
	return items, nil
}

func (s *Store) CreateSSHServerHostKey(item models.SSHServerHostKey) (models.SSHServerHostKey, error) {
	var err error
	var now int64

	if strings.TrimSpace(item.ID) == "" {
		item.ID, err = util.NewID()
		if err != nil {
			return item, err
		}
	}
	now = time.Now().UTC().Unix()
	item.CreatedAt = now
	_, err = s.Exec(`INSERT INTO ssh_server_host_keys (public_id, server_public_id, algorithm, public_key, fingerprint, created_at)
		VALUES (?, ?, ?, ?, ?, ?)`,
		item.ID,
		strings.TrimSpace(item.ServerID),
		strings.TrimSpace(item.Algorithm),
		strings.TrimSpace(item.PublicKey),
		strings.TrimSpace(item.Fingerprint),
		item.CreatedAt,
	)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) DeleteSSHServerHostKey(serverID string, hostKeyID string) error {
	var err error

	_, err = s.Exec(`DELETE FROM ssh_server_host_keys WHERE server_public_id = ? AND public_id = ?`, strings.TrimSpace(serverID), strings.TrimSpace(hostKeyID))
	return err
}

func (s *Store) CreateSSHSession(item models.SSHSession) (models.SSHSession, error) {
	var err error
	var now int64

	if strings.TrimSpace(item.ID) == "" {
		item.ID, err = util.NewID()
		if err != nil {
			return item, err
		}
	}
	now = time.Now().UTC().Unix()
	if item.Status == "" {
		item.Status = "pending"
	}
	if item.RequestedTerm == "" {
		item.RequestedTerm = "xterm-256color"
	}
	if item.RequestedCols <= 0 {
		item.RequestedCols = 80
	}
	if item.RequestedRows <= 0 {
		item.RequestedRows = 24
	}
	item.StartedAt = now
	_, err = s.Exec(`INSERT INTO ssh_sessions (
		public_id,
		profile_public_id,
		server_public_id,
		user_public_id,
		username,
		remote_username,
		auth_method,
		host,
		port,
		status,
		host_key_fingerprint,
		requested_term,
		requested_cols,
		requested_rows,
		started_at,
		connected_at,
		ended_at,
		remote_addr,
		user_agent,
		error
	) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		item.ID,
		strings.TrimSpace(item.ProfileID),
		strings.TrimSpace(item.ServerID),
		strings.TrimSpace(item.UserID),
		strings.TrimSpace(item.Username),
		strings.TrimSpace(item.RemoteUsername),
		strings.TrimSpace(item.AuthMethod),
		strings.TrimSpace(item.Host),
		item.Port,
		strings.TrimSpace(item.Status),
		strings.TrimSpace(item.HostKeyFingerprint),
		strings.TrimSpace(item.RequestedTerm),
		item.RequestedCols,
		item.RequestedRows,
		item.StartedAt,
		item.ConnectedAt,
		item.EndedAt,
		strings.TrimSpace(item.RemoteAddr),
		strings.TrimSpace(item.UserAgent),
		strings.TrimSpace(item.Error),
	)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) GetSSHSession(id string) (models.SSHSession, error) {
	var row *sql.Row
	var item models.SSHSession
	var err error

	row = s.QueryRow(`SELECT public_id, profile_public_id, server_public_id, user_public_id, username, remote_username, auth_method, host, port, status, host_key_fingerprint, requested_term, requested_cols, requested_rows, started_at, connected_at, ended_at, remote_addr, user_agent, error
		FROM ssh_sessions
		WHERE public_id = ?`, strings.TrimSpace(id))
	err = row.Scan(&item.ID, &item.ProfileID, &item.ServerID, &item.UserID, &item.Username, &item.RemoteUsername, &item.AuthMethod, &item.Host, &item.Port, &item.Status, &item.HostKeyFingerprint, &item.RequestedTerm, &item.RequestedCols, &item.RequestedRows, &item.StartedAt, &item.ConnectedAt, &item.EndedAt, &item.RemoteAddr, &item.UserAgent, &item.Error)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) ListSSHSessionsForUser(userID string, limit int) ([]models.SSHSession, error) {
	var rows *sql.Rows
	var items []models.SSHSession
	var item models.SSHSession
	var err error

	if limit <= 0 {
		limit = 50
	}
	if limit > 500 {
		limit = 500
	}
	rows, err = s.Query(`SELECT public_id, profile_public_id, server_public_id, user_public_id, username, remote_username, auth_method, host, port, status, host_key_fingerprint, requested_term, requested_cols, requested_rows, started_at, connected_at, ended_at, remote_addr, user_agent, error
		FROM ssh_sessions
		WHERE user_public_id = ?
		ORDER BY started_at DESC
		LIMIT ?`, strings.TrimSpace(userID), limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&item.ID, &item.ProfileID, &item.ServerID, &item.UserID, &item.Username, &item.RemoteUsername, &item.AuthMethod, &item.Host, &item.Port, &item.Status, &item.HostKeyFingerprint, &item.RequestedTerm, &item.RequestedCols, &item.RequestedRows, &item.StartedAt, &item.ConnectedAt, &item.EndedAt, &item.RemoteAddr, &item.UserAgent, &item.Error)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	err = rows.Err()
	if err != nil {
		return nil, err
	}
	return items, nil
}

func (s *Store) UpdateSSHSessionStatus(id string, status string, hostKeyFingerprint string, connectedAt int64, endedAt int64, errorText string) error {
	var err error

	_, err = s.Exec(`UPDATE ssh_sessions
		SET status = ?, host_key_fingerprint = ?, connected_at = ?, ended_at = ?, error = ?
		WHERE public_id = ?`,
		strings.TrimSpace(status),
		strings.TrimSpace(hostKeyFingerprint),
		connectedAt,
		endedAt,
		strings.TrimSpace(errorText),
		strings.TrimSpace(id),
	)
	return err
}

func createSSHSecretTx(tx *sql.Tx, item models.SSHSecret) (models.SSHSecret, error) {
	var err error
	var now int64

	if strings.TrimSpace(item.ID) == "" {
		item.ID, err = util.NewID()
		if err != nil {
			return item, err
		}
	}
	now = time.Now().UTC().Unix()
	item.CreatedAt = now
	item.UpdatedAt = now
	_, err = tx.Exec(`INSERT INTO ssh_secrets (
		public_id,
		kind,
		payload,
		metadata_json,
		created_by_kind,
		created_by_subject_id,
		created_by_subject_name,
		created_at,
		updated_at
	) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		item.ID,
		strings.TrimSpace(item.Kind),
		item.Payload,
		normalizeJSONText(item.MetadataJSON, "{}"),
		strings.TrimSpace(item.CreatedByKind),
		strings.TrimSpace(item.CreatedBySubjectID),
		strings.TrimSpace(item.CreatedBySubjectName),
		item.CreatedAt,
		item.UpdatedAt,
	)
	if err != nil {
		return item, err
	}
	return item, nil
}

func updateSSHSecretTx(tx *sql.Tx, item models.SSHSecret) (models.SSHSecret, error) {
	var err error

	item.UpdatedAt = time.Now().UTC().Unix()
	_, err = tx.Exec(`UPDATE ssh_secrets
		SET kind = ?, payload = ?, metadata_json = ?, updated_at = ?
		WHERE public_id = ?`,
		strings.TrimSpace(item.Kind),
		item.Payload,
		normalizeJSONText(item.MetadataJSON, "{}"),
		item.UpdatedAt,
		strings.TrimSpace(item.ID),
	)
	if err != nil {
		return item, err
	}
	return item, nil
}

func insertSSHAccessProfileTargetTx(tx *sql.Tx, profileID string, targetType string, targetID string, createdAt int64) error {
	var err error
	var id string

	targetType = strings.ToLower(strings.TrimSpace(targetType))
	targetID = strings.TrimSpace(targetID)
	if targetType != "user" && targetType != "group" {
		return errors.New("target_type must be user or group")
	}
	if targetID == "" {
		return errors.New("target_id is required")
	}
	id, err = util.NewID()
	if err != nil {
		return err
	}
	_, err = tx.Exec(`INSERT INTO ssh_access_profile_targets (
		public_id,
		profile_public_id,
		target_type,
		target_public_id,
		created_at
	) VALUES (?, ?, ?, ?, ?)`,
		id,
		strings.TrimSpace(profileID),
		targetType,
		targetID,
		createdAt,
	)
	return err
}

func normalizeStringList(items []string) []string {
	var out []string
	var seen map[string]bool
	var value string
	var i int

	seen = map[string]bool{}
	for i = 0; i < len(items); i++ {
		value = strings.TrimSpace(items[i])
		if value == "" {
			continue
		}
		if seen[value] {
			continue
		}
		seen[value] = true
		out = append(out, value)
	}
	return out
}

func encodeStringList(items []string) (string, error) {
	var raw []byte
	var err error

	raw, err = json.Marshal(items)
	if err != nil {
		return "", err
	}
	return string(raw), nil
}

func decodeStringList(raw string) ([]string, error) {
	var out []string
	var err error

	raw = strings.TrimSpace(raw)
	if raw == "" {
		return []string{}, nil
	}
	err = json.Unmarshal([]byte(raw), &out)
	if err != nil {
		return nil, err
	}
	return normalizeStringList(out), nil
}

func normalizeJSONText(raw string, fallback string) string {
	var value string

	value = strings.TrimSpace(raw)
	if value == "" {
		return fallback
	}
	return value
}

func emptyStringToNil(raw string) any {
	var value string

	value = strings.TrimSpace(raw)
	if value == "" {
		return nil
	}
	return value
}

func NormalizeSSHServerTags(items []string) []string {
	var out []string

	out = normalizeStringList(items)
	sort.Strings(out)
	return out
}