package db

import "database/sql"
import "errors"
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 now time.Time
	var nowUnix int64
	if user.ID == "" {
		id, err = util.NewID()
		if err != nil {
			return user, err
		}
		user.ID = id
	}
	now = time.Now().UTC()
	nowUnix = now.Unix()
	user.CreatedAt = nowUnix
	user.UpdatedAt = nowUnix
	if user.AuthSource == "" {
		user.AuthSource = "db"
	}
	_, err = s.DB.Exec(`
		INSERT INTO users (public_id, username, display_name, email, password_hash, is_admin, disabled, auth_source, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	`, user.ID, user.Username, user.DisplayName, user.Email, passwordHash, user.IsAdmin, user.Disabled, user.AuthSource, now, now)
	return user, err
}

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

func (s *Store) UpdateUserWithPassword(user models.User, passwordHash string) error {
	var err error
	var now time.Time
	var nowUnix int64
	var tx *sql.Tx
	now = time.Now().UTC()
	nowUnix = now.Unix()
	user.UpdatedAt = nowUnix
	tx, err = s.DB.Begin()
	if err != nil {
		return err
	}
	_, err = tx.Exec(`UPDATE users SET display_name = ?, email = ?, is_admin = ?, disabled = ?, password_hash = ?, updated_at = ? WHERE public_id = ?`,
		user.DisplayName, user.Email, user.IsAdmin, user.Disabled, passwordHash, now, user.ID)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	err = tx.Commit()
	if err != nil {
		return err
	}
	return nil
}

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

func (s *Store) GetUserByID(id string) (models.User, error) {
	var user models.User
	var row *sql.Row
	var created time.Time
	var updated time.Time
	var err error
	row = s.DB.QueryRow(`SELECT public_id, username, display_name, email, is_admin, disabled, auth_source, created_at, updated_at FROM users WHERE public_id = ?`, id)
	err = row.Scan(&user.ID, &user.Username, &user.DisplayName, &user.Email, &user.IsAdmin, &user.Disabled, &user.AuthSource, &created, &updated)
	if err != nil {
		return user, err
	}
	user.CreatedAt = created.Unix()
	user.UpdatedAt = updated.Unix()
	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
	var created time.Time
	var updated time.Time
	row = s.DB.QueryRow(`SELECT public_id, username, display_name, email, is_admin, disabled, auth_source, password_hash, created_at, updated_at FROM users WHERE username = ?`, username)
	err = row.Scan(&user.ID, &user.Username, &user.DisplayName, &user.Email, &user.IsAdmin, &user.Disabled, &user.AuthSource, &passwordHash, &created, &updated)
	if err != nil {
		return user, passwordHash.String, err
	}
	user.CreatedAt = created.Unix()
	user.UpdatedAt = updated.Unix()
	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
	var created time.Time
	var updated time.Time
	rows, err = s.DB.Query(`SELECT public_id, username, display_name, email, is_admin, disabled, auth_source, created_at, updated_at FROM users ORDER BY username`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&u.ID, &u.Username, &u.DisplayName, &u.Email, &u.IsAdmin, &u.Disabled, &u.AuthSource, &created, &updated)
		if err != nil {
			return nil, err
		}
		u.CreatedAt = created.Unix()
		u.UpdatedAt = updated.Unix()
		users = append(users, u)
	}
	return users, rows.Err()
}

func (s *Store) DeleteUser(id string) error {
	var err error
	_, err = s.DB.Exec(`DELETE FROM users WHERE public_id = ?`, id)
	return err
}

func (s *Store) GetAuthSettings() (models.AuthSettings, error) {
	var settings models.AuthSettings
	var rows *sql.Rows
	var err error
	var key string
	var value string
	rows, err = s.DB.Query(`SELECT key, value FROM app_settings WHERE key IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		"auth.mode",
		"auth.oidc.enabled",
		"auth.ldap.url",
		"auth.ldap.bind_dn",
		"auth.ldap.bind_password",
		"auth.ldap.user_base_dn",
		"auth.ldap.user_filter",
		"auth.ldap.tls_insecure_skip_verify",
		"auth.oidc.client_id",
		"auth.oidc.client_secret",
		"auth.oidc.authorize_url",
		"auth.oidc.token_url",
		"auth.oidc.userinfo_url",
		"auth.oidc.redirect_url",
		"auth.oidc.scopes",
		"auth.oidc.tls_insecure_skip_verify")
	if err != nil {
		return settings, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&key, &value)
		if err != nil {
			return settings, err
		}
		switch key {
		case "auth.mode":
			settings.AuthMode = value
		case "auth.oidc.enabled":
			settings.OIDCEnabled = value == "1"
		case "auth.ldap.url":
			settings.LDAPURL = value
		case "auth.ldap.bind_dn":
			settings.LDAPBindDN = value
		case "auth.ldap.bind_password":
			settings.LDAPBindPassword = value
		case "auth.ldap.user_base_dn":
			settings.LDAPUserBaseDN = value
		case "auth.ldap.user_filter":
			settings.LDAPUserFilter = value
		case "auth.ldap.tls_insecure_skip_verify":
			settings.LDAPTLSInsecureSkipVerify = value == "1"
		case "auth.oidc.client_id":
			settings.OIDCClientID = value
		case "auth.oidc.client_secret":
			settings.OIDCClientSecret = value
		case "auth.oidc.authorize_url":
			settings.OIDCAuthorizeURL = value
		case "auth.oidc.token_url":
			settings.OIDCTokenURL = value
		case "auth.oidc.userinfo_url":
			settings.OIDCUserInfoURL = value
		case "auth.oidc.redirect_url":
			settings.OIDCRedirectURL = value
		case "auth.oidc.scopes":
			settings.OIDCScopes = value
		case "auth.oidc.tls_insecure_skip_verify":
			settings.OIDCTLSInsecureSkipVerify = value == "1"
		}
	}
	err = rows.Err()
	if err != nil {
		return settings, err
	}
	return settings, nil
}

func (s *Store) SetAuthSettings(settings models.AuthSettings) error {
	var tx *sql.Tx
	var err error
	var now int64
	var tlsInsecure string
	tx, err = s.DB.Begin()
	if err != nil {
		return err
	}
	now = time.Now().UTC().Unix()
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"auth.mode", settings.AuthMode, now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	if settings.OIDCEnabled {
		tlsInsecure = "1"
	} else {
		tlsInsecure = "0"
	}
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"auth.oidc.enabled", tlsInsecure, now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"auth.ldap.url", settings.LDAPURL, now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"auth.ldap.bind_dn", settings.LDAPBindDN, now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"auth.ldap.bind_password", settings.LDAPBindPassword, now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"auth.ldap.user_base_dn", settings.LDAPUserBaseDN, now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"auth.ldap.user_filter", settings.LDAPUserFilter, now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	if settings.LDAPTLSInsecureSkipVerify {
		tlsInsecure = "1"
	} else {
		tlsInsecure = "0"
	}
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"auth.ldap.tls_insecure_skip_verify", tlsInsecure, now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"auth.oidc.client_id", settings.OIDCClientID, now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"auth.oidc.client_secret", settings.OIDCClientSecret, now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"auth.oidc.authorize_url", settings.OIDCAuthorizeURL, now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"auth.oidc.token_url", settings.OIDCTokenURL, now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"auth.oidc.userinfo_url", settings.OIDCUserInfoURL, now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"auth.oidc.redirect_url", settings.OIDCRedirectURL, now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"auth.oidc.scopes", settings.OIDCScopes, now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	if settings.OIDCTLSInsecureSkipVerify {
		tlsInsecure = "1"
	} else {
		tlsInsecure = "0"
	}
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"auth.oidc.tls_insecure_skip_verify", tlsInsecure, now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	err = tx.Commit()
	if err != nil {
		return err
	}
	return nil
}

func (s *Store) GetTLSSettings() (models.TLSSettings, error) {
	var settings models.TLSSettings
	var rows *sql.Rows
	var err error
	var key string
	var value string
	rows, err = s.DB.Query(`SELECT key, value FROM app_settings WHERE key IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		"tls.http_addrs",
		"tls.https_addrs",
		"tls.server_cert_source",
		"tls.cert_file",
		"tls.key_file",
		"tls.pki_server_cert_id",
		"tls.client_auth",
		"tls.client_ca_file",
		"tls.pki_client_ca_id",
		"tls.min_version")
	if err != nil {
		return settings, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&key, &value)
		if err != nil {
			return settings, err
		}
		switch key {
		case "tls.http_addrs":
			settings.HTTPAddrs = splitCSVValue(value)
		case "tls.https_addrs":
			settings.HTTPSAddrs = splitCSVValue(value)
		case "tls.server_cert_source":
			settings.TLSServerCertSource = value
		case "tls.cert_file":
			settings.TLSCertFile = value
		case "tls.key_file":
			settings.TLSKeyFile = value
		case "tls.pki_server_cert_id":
			settings.TLSPKIServerCertID = value
		case "tls.client_auth":
			settings.TLSClientAuth = value
		case "tls.client_ca_file":
			settings.TLSClientCAFile = value
		case "tls.pki_client_ca_id":
			settings.TLSPKIClientCAID = value
		case "tls.min_version":
			settings.TLSMinVersion = value
		}
	}
	err = rows.Err()
	if err != nil {
		return settings, err
	}
	return settings, nil
}

func (s *Store) SetTLSSettings(settings models.TLSSettings) error {
	var tx *sql.Tx
	var err error
	var now int64
	tx, err = s.DB.Begin()
	if err != nil {
		return err
	}
	now = time.Now().UTC().Unix()
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"tls.http_addrs", strings.Join(settings.HTTPAddrs, ","), now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"tls.https_addrs", strings.Join(settings.HTTPSAddrs, ","), now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"tls.server_cert_source", settings.TLSServerCertSource, now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"tls.cert_file", settings.TLSCertFile, now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"tls.key_file", settings.TLSKeyFile, now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"tls.pki_server_cert_id", settings.TLSPKIServerCertID, now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"tls.client_auth", settings.TLSClientAuth, now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"tls.client_ca_file", settings.TLSClientCAFile, now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"tls.pki_client_ca_id", settings.TLSPKIClientCAID, now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	_, err = tx.Exec(`INSERT INTO app_settings (key, value, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at`,
		"tls.min_version", settings.TLSMinVersion, now)
	if err != nil {
		_ = tx.Rollback()
		return err
	}
	err = tx.Commit()
	if err != nil {
		return err
	}
	return nil
}

func splitCSVValue(value string) []string {
	var parts []string
	var out []string
	var i int
	var p string
	parts = strings.Split(value, ",")
	for i = 0; i < len(parts); i++ {
		p = strings.TrimSpace(parts[i])
		if p == "" {
			continue
		}
		out = append(out, p)
	}
	return out
}

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

func (s *Store) CreateAPIKey(userID string, name string, tokenHash string, prefix string, expiresAt int64) (models.APIKey, error) {
	var key models.APIKey
	var err error
	var now time.Time
	var nowUnix int64
	var id string
	id, err = util.NewID()
	if err != nil {
		return key, err
	}
	now = time.Now().UTC()
	nowUnix = now.Unix()
	key = models.APIKey{
		ID:         id,
		UserID:     userID,
		Name:       name,
		Prefix:     prefix,
		CreatedAt:  nowUnix,
		LastUsedAt: 0,
		ExpiresAt:  expiresAt,
		Disabled:   false,
	}
	_, err = s.DB.Exec(`INSERT INTO api_keys (public_id, user_id, name, token_hash, token_prefix, created_at, last_used_at, expires_at, disabled)
		VALUES (?, (SELECT id FROM users WHERE public_id = ?), ?, ?, ?, ?, ?, ?, ?)`,
		key.ID, key.UserID, key.Name, tokenHash, key.Prefix, key.CreatedAt, key.LastUsedAt, key.ExpiresAt, key.Disabled)
	return key, err
}

func (s *Store) ListAPIKeys(userID string) ([]models.APIKey, error) {
	var rows *sql.Rows
	var err error
	var keys []models.APIKey
	var key models.APIKey
	rows, err = s.DB.Query(`SELECT k.public_id, u.public_id, k.name, k.token_prefix, k.created_at, k.last_used_at, k.expires_at, k.disabled
		FROM api_keys k
		JOIN users u ON u.id = k.user_id
		WHERE u.public_id = ?
		ORDER BY k.created_at DESC`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&key.ID, &key.UserID, &key.Name, &key.Prefix, &key.CreatedAt, &key.LastUsedAt, &key.ExpiresAt, &key.Disabled)
		if err != nil {
			return nil, err
		}
		keys = append(keys, key)
	}
	return keys, rows.Err()
}

func (s *Store) DeleteAPIKey(userID string, id string) error {
	var err error
	_, err = s.DB.Exec(`DELETE FROM api_keys WHERE public_id = ? AND user_id = (SELECT id FROM users WHERE public_id = ?)`, id, userID)
	return err
}

func (s *Store) SetAPIKeyDisabled(userID string, id string, disabled bool) error {
	var err error
	_, err = s.DB.Exec(`UPDATE api_keys SET disabled = ? WHERE public_id = ? AND user_id = (SELECT id FROM users WHERE public_id = ?)`, disabled, id, userID)
	return err
}

func (s *Store) DeleteAPIKeyByID(id string) error {
	var err error
	_, err = s.DB.Exec(`DELETE FROM api_keys WHERE public_id = ?`, id)
	return err
}

func (s *Store) SetAPIKeyDisabledByID(id string, disabled bool) error {
	var err error
	_, err = s.DB.Exec(`UPDATE api_keys SET disabled = ? WHERE public_id = ?`, disabled, id)
	return err
}

func (s *Store) ListAPIKeysAdmin(userID string, query string) ([]models.AdminAPIKey, error) {
	var rows *sql.Rows
	var err error
	var keys []models.AdminAPIKey
	var key models.AdminAPIKey
	var sqlQuery string
	var like string
	query = strings.TrimSpace(query)
	like = "%" + query + "%"
	if userID != "" && query != "" {
		sqlQuery = `
			SELECT
				k.public_id, u.public_id, u.username, u.display_name, u.email,
				k.name, k.token_prefix, k.created_at, k.last_used_at, k.expires_at, k.disabled
			FROM api_keys k
			JOIN users u ON u.id = k.user_id
			WHERE u.public_id = ?
			  AND (k.name LIKE ? OR k.token_prefix LIKE ? OR u.username LIKE ? OR u.display_name LIKE ? OR u.email LIKE ?)
			ORDER BY k.created_at DESC
		`
		rows, err = s.DB.Query(sqlQuery, userID, like, like, like, like, like)
	} else if userID != "" {
		sqlQuery = `
			SELECT
				k.public_id, u.public_id, u.username, u.display_name, u.email,
				k.name, k.token_prefix, k.created_at, k.last_used_at, k.expires_at, k.disabled
			FROM api_keys k
			JOIN users u ON u.id = k.user_id
			WHERE u.public_id = ?
			ORDER BY k.created_at DESC
		`
		rows, err = s.DB.Query(sqlQuery, userID)
	} else if query != "" {
		sqlQuery = `
			SELECT
				k.public_id, u.public_id, u.username, u.display_name, u.email,
				k.name, k.token_prefix, k.created_at, k.last_used_at, k.expires_at, k.disabled
			FROM api_keys k
			JOIN users u ON u.id = k.user_id
			WHERE k.name LIKE ? OR k.token_prefix LIKE ? OR u.username LIKE ? OR u.display_name LIKE ? OR u.email LIKE ?
			ORDER BY k.created_at DESC
		`
		rows, err = s.DB.Query(sqlQuery, like, like, like, like, like)
	} else {
		sqlQuery = `
			SELECT
				k.public_id, u.public_id, u.username, u.display_name, u.email,
				k.name, k.token_prefix, k.created_at, k.last_used_at, k.expires_at, k.disabled
			FROM api_keys k
			JOIN users u ON u.id = k.user_id
			ORDER BY k.created_at DESC
		`
		rows, err = s.DB.Query(sqlQuery)
	}
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(
			&key.ID,
			&key.UserID,
			&key.Username,
			&key.DisplayName,
			&key.Email,
			&key.Name,
			&key.Prefix,
			&key.CreatedAt,
			&key.LastUsedAt,
			&key.ExpiresAt,
			&key.Disabled,
		)
		if err != nil {
			return nil, err
		}
		keys = append(keys, key)
	}
	return keys, rows.Err()
}

func (s *Store) GetUserByAPIKeyHash(tokenHash string) (models.User, error) {
	var user models.User
	var row *sql.Row
	var created time.Time
	var updated time.Time
	var keyID int64
	var now time.Time
	var nowUnix int64
	var err error
	var currentUnix int64
	now = time.Now().UTC()
	currentUnix = now.Unix()
	row = s.DB.QueryRow(`
		SELECT u.public_id, u.username, u.display_name, u.email, u.is_admin, u.disabled, u.auth_source, u.created_at, u.updated_at, k.id
		FROM api_keys k
		JOIN users u ON u.id = k.user_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, currentUnix)
	err = row.Scan(&user.ID, &user.Username, &user.DisplayName, &user.Email, &user.IsAdmin, &user.Disabled, &user.AuthSource, &created, &updated, &keyID)
	if err != nil {
		return user, err
	}
	user.CreatedAt = created.Unix()
	user.UpdatedAt = updated.Unix()
	now = time.Now().UTC()
	nowUnix = now.Unix()
	_, _ = s.DB.Exec(`UPDATE api_keys SET last_used_at = ? WHERE id = ?`, nowUnix, keyID)
	return user, nil
}

func (s *Store) CreateSession(userID, token string, expiresAt time.Time) error {
	var err error
	_, err = s.DB.Exec(`INSERT INTO sessions (id, user_id, token, expires_at, created_at)
		VALUES (?, (SELECT id FROM users WHERE public_id = ?), ?, ?, ?)`,
		mustID(), userID, token, expiresAt, time.Now().UTC())
	return err
}

func (s *Store) DeleteSession(token string) error {
	var err error
	_, err = s.DB.Exec(`DELETE FROM sessions WHERE token = ?`, token)
	return err
}

func (s *Store) GetSessionUser(token string) (models.User, time.Time, error) {
	var user models.User
	var expires time.Time
	var row *sql.Row
	var err error
	var created time.Time
	var updated time.Time
	row = s.DB.QueryRow(`
		SELECT u.public_id, u.username, u.display_name, u.email, u.is_admin, u.disabled, u.auth_source, u.created_at, u.updated_at, s.expires_at
		FROM sessions s JOIN users u ON u.id = s.user_id
		WHERE s.token = ? AND u.disabled = 0
	`, token)
	err = row.Scan(&user.ID, &user.Username, &user.DisplayName, &user.Email, &user.IsAdmin, &user.Disabled, &user.AuthSource, &created, &updated, &expires)
	if err != nil {
		return user, time.Time{}, err
	}
	user.CreatedAt = created.Unix()
	user.UpdatedAt = updated.Unix()
	return user, expires, nil
}

func (s *Store) CreateProject(project models.Project) (models.Project, error) {
	var id string
	var err error
	var now time.Time
	var nowUnix int64
	var tx *sql.Tx
	if project.ID == "" {
		id, err = util.NewID()
		if err != nil {
			return project, err
		}
		project.ID = id
	}
	now = time.Now().UTC()
	nowUnix = now.Unix()
	project.CreatedAt = nowUnix
	project.UpdatedAt = nowUnix
	if project.UpdatedBy == "" {
		project.UpdatedBy = project.CreatedBy
	}
	tx, err = s.DB.Begin()
	if err != nil {
		return project, err
	}
	if project.HomePage == "" {
		project.HomePage = "info"
	}
	_, err = tx.Exec(`INSERT INTO projects (public_id, slug, name, description, home_page, created_by, updated_by, created_at, updated_at, created_at_unix, updated_at_unix)
		VALUES (?, ?, ?, ?, ?, (SELECT id FROM users WHERE public_id = ?), (SELECT id FROM users WHERE public_id = ?), ?, ?, ?, ?)`,
		project.ID,
		project.Slug,
		project.Name,
		project.Description,
		project.HomePage,
		project.CreatedBy,
		project.UpdatedBy,
		now,
		now,
		project.CreatedAt,
		project.UpdatedAt,
	)
	if err != nil {
		_ = tx.Rollback()
		return project, err
	}
	_, err = tx.Exec(`INSERT INTO project_members (project_id, user_id, role, created_at)
		VALUES ((SELECT id FROM projects WHERE public_id = ?), (SELECT id FROM users WHERE public_id = ?), ?, ?)`,
		project.ID, project.CreatedBy, "admin", now)
	if err != nil {
		_ = tx.Rollback()
		return project, err
	}
	err = tx.Commit()
	if err != nil {
		_ = tx.Rollback()
		return project, err
	}
	return project, nil
}

func (s *Store) UpdateProject(project models.Project) error {
	var err error
	if project.HomePage == "" {
		project.HomePage = "info"
	}
	_, err = s.DB.Exec(`UPDATE projects SET slug = ?, name = ?, description = ?, home_page = ?, updated_at = ?, updated_by = (SELECT id FROM users WHERE public_id = ?), updated_at_unix = ? WHERE public_id = ?`,
		project.Slug,
		project.Name,
		project.Description,
		project.HomePage,
		time.Unix(project.UpdatedAt, 0).UTC(),
		project.UpdatedBy,
		project.UpdatedAt,
		project.ID,
	)
	return err
}

func (s *Store) GetProject(id string) (models.Project, error) {
	var project models.Project
	var row *sql.Row
	row = s.DB.QueryRow(`
		SELECT p.public_id, p.slug, p.name, p.description, p.home_page,
			   c.public_id, u.public_id,
			   COALESCE(c.username, ''), COALESCE(u.username, ''),
			   p.created_at_unix, p.updated_at_unix
		FROM projects p
		LEFT JOIN users c ON c.id = p.created_by
		LEFT JOIN users u ON u.id = p.updated_by
		WHERE p.public_id = ?
	`, id)
	return project, row.Scan(
		&project.ID,
		&project.Slug,
		&project.Name,
		&project.Description,
		&project.HomePage,
		&project.CreatedBy,
		&project.UpdatedBy,
		&project.CreatedByName,
		&project.UpdatedByName,
		&project.CreatedAt,
		&project.UpdatedAt,
	)
}

func (s *Store) GetProjectBySlug(slug string) (models.Project, error) {
	var project models.Project
	var row *sql.Row
	var err error
	row = s.DB.QueryRow(`
		SELECT p.public_id, p.slug, p.name, p.description, p.home_page,
			   c.public_id, u.public_id,
			   COALESCE(c.username, ''), COALESCE(u.username, ''),
			   p.created_at_unix, p.updated_at_unix
		FROM projects p
		LEFT JOIN users c ON c.id = p.created_by
		LEFT JOIN users u ON u.id = p.updated_by
		WHERE p.slug = ?
	`, slug)
	err = row.Scan(
		&project.ID,
		&project.Slug,
		&project.Name,
		&project.Description,
		&project.HomePage,
		&project.CreatedBy,
		&project.UpdatedBy,
		&project.CreatedByName,
		&project.UpdatedByName,
		&project.CreatedAt,
		&project.UpdatedAt,
	)
	if err != nil {
		return project, err
	}
	return project, nil
}

func (s *Store) ListProjects() ([]models.Project, error) {
	var rows *sql.Rows
	var err error
	var projects []models.Project
	var p models.Project
	rows, err = s.DB.Query(`
		SELECT p.public_id, p.slug, p.name, p.description, p.home_page,
			   c.public_id, u.public_id,
			   COALESCE(c.username, ''), COALESCE(u.username, ''),
			   p.created_at_unix, p.updated_at_unix
		FROM projects p
		LEFT JOIN users c ON c.id = p.created_by
		LEFT JOIN users u ON u.id = p.updated_by
		ORDER BY p.name
	`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(
			&p.ID,
			&p.Slug,
			&p.Name,
			&p.Description,
			&p.HomePage,
			&p.CreatedBy,
			&p.UpdatedBy,
			&p.CreatedByName,
			&p.UpdatedByName,
			&p.CreatedAt,
			&p.UpdatedAt,
		)
		if err != nil {
			return nil, err
		}
		projects = append(projects, p)
	}
	return projects, rows.Err()
}

func (s *Store) ListProjectsForUser(userID string) ([]models.Project, error) {
	var rows *sql.Rows
	var err error
	var projects []models.Project
	var p models.Project
	rows, err = s.DB.Query(`
		SELECT p.public_id, p.slug, p.name, p.description, p.home_page,
			   c.public_id, u.public_id,
			   COALESCE(c.username, ''), COALESCE(u.username, ''),
			   p.created_at_unix, p.updated_at_unix
		FROM projects p
		JOIN project_members m ON m.project_id = p.id
		LEFT JOIN users c ON c.id = p.created_by
		LEFT JOIN users u ON u.id = p.updated_by
		WHERE m.user_id = (SELECT id FROM users WHERE public_id = ?)
		ORDER BY p.name
	`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(
			&p.ID,
			&p.Slug,
			&p.Name,
			&p.Description,
			&p.HomePage,
			&p.CreatedBy,
			&p.UpdatedBy,
			&p.CreatedByName,
			&p.UpdatedByName,
			&p.CreatedAt,
			&p.UpdatedAt,
		)
		if err != nil {
			return nil, err
		}
		projects = append(projects, p)
	}
	return projects, rows.Err()
}

func (s *Store) ListProjectsFiltered(limit int, offset int, query string) ([]models.Project, error) {
	var rows *sql.Rows
	var err error
	var projects []models.Project
	var p models.Project
	if limit <= 0 {
		limit = 100
	}
	if offset < 0 {
		offset = 0
	}
	if query == "" {
		rows, err = s.DB.Query(
			`SELECT p.public_id, p.slug, p.name, p.description, p.home_page,
			   c.public_id, u.public_id,
			   COALESCE(c.username, ''), COALESCE(u.username, ''),
			   p.created_at_unix, p.updated_at_unix
			 FROM projects p
			 LEFT JOIN users c ON c.id = p.created_by
			 LEFT JOIN users u ON u.id = p.updated_by
			 ORDER BY p.name LIMIT ? OFFSET ?`,
			limit,
			offset,
		)
	} else {
		rows, err = s.DB.Query(
			`SELECT p.public_id, p.slug, p.name, p.description, p.home_page,
			   c.public_id, u.public_id,
			   COALESCE(c.username, ''), COALESCE(u.username, ''),
			   p.created_at_unix, p.updated_at_unix
			 FROM projects p
			 LEFT JOIN users c ON c.id = p.created_by
			 LEFT JOIN users u ON u.id = p.updated_by
			 WHERE p.name LIKE ? OR p.slug LIKE ?
			 ORDER BY p.name LIMIT ? OFFSET ?`,
			"%"+query+"%",
			"%"+query+"%",
			limit,
			offset,
		)
	}
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(
			&p.ID,
			&p.Slug,
			&p.Name,
			&p.Description,
			&p.HomePage,
			&p.CreatedBy,
			&p.UpdatedBy,
			&p.CreatedByName,
			&p.UpdatedByName,
			&p.CreatedAt,
			&p.UpdatedAt,
		)
		if err != nil {
			return nil, err
		}
		projects = append(projects, p)
	}
	return projects, rows.Err()
}

func (s *Store) ListProjectsFilteredForUser(userID string, limit int, offset int, query string) ([]models.Project, error) {
	var rows *sql.Rows
	var err error
	var projects []models.Project
	var p models.Project
	if limit <= 0 {
		limit = 100
	}
	if offset < 0 {
		offset = 0
	}
	if query == "" {
		rows, err = s.DB.Query(
			`SELECT p.public_id, p.slug, p.name, p.description, p.home_page,
			   c.public_id, u.public_id,
			   COALESCE(c.username, ''), COALESCE(u.username, ''),
			   p.created_at_unix, p.updated_at_unix
			 FROM projects p
			 JOIN project_members m ON m.project_id = p.id
			 LEFT JOIN users c ON c.id = p.created_by
			 LEFT JOIN users u ON u.id = p.updated_by
			 WHERE m.user_id = (SELECT id FROM users WHERE public_id = ?)
			 ORDER BY p.name LIMIT ? OFFSET ?`,
			userID,
			limit,
			offset,
		)
	} else {
		rows, err = s.DB.Query(
			`SELECT p.public_id, p.slug, p.name, p.description, p.home_page,
			   c.public_id, u.public_id,
			   COALESCE(c.username, ''), COALESCE(u.username, ''),
			   p.created_at_unix, p.updated_at_unix
			 FROM projects p
			 JOIN project_members m ON m.project_id = p.id
			 LEFT JOIN users c ON c.id = p.created_by
			 LEFT JOIN users u ON u.id = p.updated_by
			 WHERE m.user_id = (SELECT id FROM users WHERE public_id = ?) AND (p.name LIKE ? OR p.slug LIKE ?)
			 ORDER BY p.name LIMIT ? OFFSET ?`,
			userID,
			"%"+query+"%",
			"%"+query+"%",
			limit,
			offset,
		)
	}
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(
			&p.ID,
			&p.Slug,
			&p.Name,
			&p.Description,
			&p.HomePage,
			&p.CreatedBy,
			&p.UpdatedBy,
			&p.CreatedByName,
			&p.UpdatedByName,
			&p.CreatedAt,
			&p.UpdatedAt,
		)
		if err != nil {
			return nil, err
		}
		projects = append(projects, p)
	}
	return projects, rows.Err()
}

func (s *Store) DeleteProject(id string) error {
	var err error
	_, err = s.DB.Exec(`DELETE FROM projects WHERE public_id = ?`, id)
	return err
}

func (s *Store) AddProjectMember(projectID, userID, role string) (models.ProjectMember, error) {
	var member models.ProjectMember
	var err error
	var now time.Time
	now = time.Now().UTC()
	member = models.ProjectMember{ProjectID: projectID, UserID: userID, Role: role, CreatedAt: now.Unix()}
	_, err = s.DB.Exec(`INSERT INTO project_members (project_id, user_id, role, created_at)
		VALUES ((SELECT id FROM projects WHERE public_id = ?), (SELECT id FROM users WHERE public_id = ?), ?, ?)`,
		member.ProjectID, member.UserID, member.Role, now)
	return member, err
}

func (s *Store) UpdateProjectMemberRole(projectID, userID, role string) error {
	var err error
	_, err = s.DB.Exec(`UPDATE project_members SET role = ?
		WHERE project_id = (SELECT id FROM projects WHERE public_id = ?)
		  AND user_id = (SELECT id FROM users WHERE public_id = ?)`, role, projectID, userID)
	return err
}

func (s *Store) RemoveProjectMember(projectID, userID string) error {
	var err error
	_, err = s.DB.Exec(`DELETE FROM project_members
		WHERE project_id = (SELECT id FROM projects WHERE public_id = ?)
		  AND user_id = (SELECT id FROM users WHERE public_id = ?)`, projectID, userID)
	return err
}

func (s *Store) ListProjectMembers(projectID string) ([]models.ProjectMember, error) {
	var rows *sql.Rows
	var err error
	var members []models.ProjectMember
	var m models.ProjectMember
	var created time.Time
	rows, err = s.DB.Query(`SELECT p.public_id, u.public_id, m.role, m.created_at
		FROM project_members m
		JOIN projects p ON p.id = m.project_id
		JOIN users u ON u.id = m.user_id
		WHERE m.project_id = (SELECT id FROM projects WHERE public_id = ?)
		ORDER BY m.role`, projectID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&m.ProjectID, &m.UserID, &m.Role, &created)
		if err != nil {
			return nil, err
		}
		m.CreatedAt = created.Unix()
		members = append(members, m)
	}
	return members, rows.Err()
}

func (s *Store) GetProjectMemberRole(projectID, userID string) (string, error) {
	var role string
	var row *sql.Row
	var err error
	row = s.DB.QueryRow(`SELECT role FROM project_members
		WHERE project_id = (SELECT id FROM projects WHERE public_id = ?)
		  AND user_id = (SELECT id FROM users WHERE public_id = ?)`, projectID, userID)
	err = row.Scan(&role)
	if err != nil {
		return "", err
	}
	return role, nil
}

func (s *Store) CreateRepo(repo models.Repo) (models.Repo, error) {
	var id string
	var err error
	var now time.Time
	var nowUnix int64
	if repo.ID == "" {
		id, err = util.NewID()
		if err != nil {
			return repo, err
		}
		repo.ID = id
	}
	now = time.Now().UTC()
	nowUnix = now.Unix()
	repo.CreatedAt = nowUnix
	_, err = s.DB.Exec(`INSERT INTO repos (public_id, project_id, name, type, path, created_by, created_at)
		VALUES (?, (SELECT id FROM projects WHERE public_id = ?), ?, ?, ?, (SELECT id FROM users WHERE public_id = ?), ?)`,
		repo.ID, repo.ProjectID, repo.Name, repo.Type, repo.Path, repo.CreatedBy, now)
	return repo, err
}

func (s *Store) RepoNameExists(projectID string, name string, repoType string) (bool, error) {
	var count int
	var row *sql.Row
	var err error
	row = s.DB.QueryRow(`SELECT COUNT(1) FROM repos
		WHERE project_id = (SELECT id FROM projects WHERE public_id = ?)
		  AND name = ? AND type = ?`, projectID, name, repoType)
	err = row.Scan(&count)
	if err != nil {
		return false, err
	}
	return count > 0, nil
}

func (s *Store) GetRepo(id string) (models.Repo, error) {
	var repo models.Repo
	var row *sql.Row
	var created time.Time
	var err error
	row = s.DB.QueryRow(`SELECT r.public_id, p.public_id, r.name, r.type, r.path, u.public_id, r.created_at
		FROM repos r
		JOIN projects p ON p.id = r.project_id
		JOIN users u ON u.id = r.created_by
		WHERE r.public_id = ?`, id)
	err = row.Scan(&repo.ID, &repo.ProjectID, &repo.Name, &repo.Type, &repo.Path, &repo.CreatedBy, &created)
	if err != nil {
		return repo, err
	}
	repo.CreatedAt = created.Unix()
	return repo, nil
}

func (s *Store) GetRepoStorageIDs(id string) (int64, int64, error) {
	var row *sql.Row
	var projectID int64
	var repoID int64
	var err error
	row = s.DB.QueryRow(`SELECT p.id, r.id
		FROM repos r
		JOIN projects p ON p.id = r.project_id
		WHERE r.public_id = ?`, id)
	err = row.Scan(&projectID, &repoID)
	if err != nil {
		return 0, 0, err
	}
	return projectID, repoID, nil
}

func (s *Store) GetRepoByProjectNameType(projectID string, name string, repoType string) (models.Repo, error) {
	var repo models.Repo
	var row *sql.Row
	var err error
	var created time.Time
	row = s.DB.QueryRow(`SELECT r.public_id, p.public_id, r.name, r.type, r.path, u.public_id, r.created_at
		FROM repos r
		JOIN projects p ON p.id = r.project_id
		JOIN users u ON u.id = r.created_by
		WHERE r.project_id = (SELECT id FROM projects WHERE public_id = ?)
		  AND r.name = ? AND r.type = ?`,
		projectID, name, repoType)
	err = row.Scan(&repo.ID, &repo.ProjectID, &repo.Name, &repo.Type, &repo.Path, &repo.CreatedBy, &created)
	if err != nil {
		return repo, err
	}
	repo.CreatedAt = created.Unix()
	return repo, nil
}

func (s *Store) ListRepos(projectID string) ([]models.Repo, error) {
	var rows *sql.Rows
	var err error
	var repos []models.Repo
	var r models.Repo
	var isForeign int
	var created time.Time
	rows, err = s.DB.Query(`
		SELECT r.public_id, p.public_id, r.name, r.type, r.path, u.public_id, r.created_at, 0 AS is_foreign
		FROM repos r
		JOIN projects p ON p.id = r.project_id
		JOIN users u ON u.id = r.created_by
		WHERE r.project_id = (SELECT id FROM projects WHERE public_id = ?)
		UNION ALL
		SELECT r.public_id, p.public_id, r.name, r.type, r.path, u.public_id, r.created_at, 1 AS is_foreign
		FROM repos r
		JOIN projects p ON p.id = r.project_id
		JOIN users u ON u.id = r.created_by
		JOIN project_repos pr ON pr.repo_id = r.id
		WHERE pr.project_id = (SELECT id FROM projects WHERE public_id = ?)
		  AND r.project_id <> (SELECT id FROM projects WHERE public_id = ?)
		ORDER BY 3
	`, projectID, projectID, projectID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&r.ID, &r.ProjectID, &r.Name, &r.Type, &r.Path, &r.CreatedBy, &created, &isForeign)
		if err != nil {
			return nil, err
		}
		r.CreatedAt = created.Unix()
		r.IsForeign = isForeign != 0
		repos = append(repos, r)
	}
	return repos, rows.Err()
}

func (s *Store) ListReposOwned(projectID string) ([]models.Repo, error) {
	var rows *sql.Rows
	var err error
	var repos []models.Repo
	var r models.Repo
	var created time.Time
	rows, err = s.DB.Query(`
		SELECT r.public_id, p.public_id, r.name, r.type, r.path, u.public_id, r.created_at
		FROM repos r
		JOIN projects p ON p.id = r.project_id
		JOIN users u ON u.id = r.created_by
		WHERE r.project_id = (SELECT id FROM projects WHERE public_id = ?)
		ORDER BY r.name
	`, projectID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&r.ID, &r.ProjectID, &r.Name, &r.Type, &r.Path, &r.CreatedBy, &created)
		if err != nil {
			return nil, err
		}
		r.CreatedAt = created.Unix()
		repos = append(repos, r)
	}
	return repos, rows.Err()
}

func (s *Store) ListAllRepos() ([]models.Repo, error) {
	var rows *sql.Rows
	var err error
	var repos []models.Repo
	var r models.Repo
	var created time.Time
	rows, err = s.DB.Query(`
		SELECT r.public_id, p.public_id, r.name, r.type, r.path, u.public_id, r.created_at
		FROM repos r
		JOIN projects p ON p.id = r.project_id
		JOIN users u ON u.id = r.created_by
		ORDER BY r.name
	`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&r.ID, &r.ProjectID, &r.Name, &r.Type, &r.Path, &r.CreatedBy, &created)
		if err != nil {
			return nil, err
		}
		r.CreatedAt = created.Unix()
		repos = append(repos, r)
	}
	return repos, rows.Err()
}

func (s *Store) ListReposByProjectIDs(projectIDs []string) ([]models.Repo, error) {
	var rows *sql.Rows
	var err error
	var repos []models.Repo
	var r models.Repo
	var created time.Time
	var placeholders []string
	var args []interface{}
	var i int
	var selected []interface{}
	if len(projectIDs) == 0 {
		return []models.Repo{}, nil
	}
	placeholders = make([]string, len(projectIDs))
	args = make([]interface{}, len(projectIDs))
	for i = 0; i < len(projectIDs); i++ {
		placeholders[i] = "?"
		selected = append(selected, projectIDs[i])
	}
	args = make([]interface{}, len(selected))
	for i = 0; i < len(selected); i++ {
		args[i] = selected[i]
	}
	rows, err = s.DB.Query(`
		SELECT r.public_id, p.public_id, r.name, r.type, r.path, u.public_id, r.created_at
		FROM repos r
		JOIN projects p ON p.id = r.project_id
		JOIN users u ON u.id = r.created_by
		WHERE p.public_id IN (`+strings.Join(placeholders, ",")+`)
		ORDER BY r.name
	`, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&r.ID, &r.ProjectID, &r.Name, &r.Type, &r.Path, &r.CreatedBy, &created)
		if err != nil {
			return nil, err
		}
		r.CreatedAt = created.Unix()
		repos = append(repos, r)
	}
	return repos, rows.Err()
}

func (s *Store) ListProjectIDsForUser(userID string) ([]string, error) {
	var rows *sql.Rows
	var err error
	var ids []string
	var id string
	rows, err = s.DB.Query(`SELECT p.public_id
		FROM project_members m
		JOIN projects p ON p.id = m.project_id
		WHERE m.user_id = (SELECT id FROM users WHERE public_id = ?)`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&id)
		if err != nil {
			return nil, err
		}
		ids = append(ids, id)
	}
	return ids, rows.Err()
}

func (s *Store) UpdateRepo(repo models.Repo) error {
	var err error
	_, err = s.DB.Exec(`UPDATE repos SET name = ?, path = ? WHERE public_id = ?`, repo.Name, repo.Path, repo.ID)
	return err
}

func (s *Store) DeleteRepo(id string) error {
	var err error
	_, err = s.DB.Exec(`DELETE FROM repos WHERE public_id = ?`, id)
	return err
}

func (s *Store) AttachRepoToProject(projectID string, repoID string) error {
	var err error
	_, err = s.DB.Exec(`INSERT INTO project_repos (project_id, repo_id, created_at)
		VALUES ((SELECT id FROM projects WHERE public_id = ?), (SELECT id FROM repos WHERE public_id = ?), ?)`,
		projectID, repoID, time.Now().UTC())
	return err
}

func (s *Store) DetachRepoFromProject(projectID string, repoID string) error {
	var err error
	_, err = s.DB.Exec(`DELETE FROM project_repos
		WHERE project_id = (SELECT id FROM projects WHERE public_id = ?)
		  AND repo_id = (SELECT id FROM repos WHERE public_id = ?)`, projectID, repoID)
	return err
}

func (s *Store) GetRepoProjectIDs(repoID string) ([]string, error) {
	var rows *sql.Rows
	var err error
	var ids []string
	var id string
	rows, err = s.DB.Query(`
		SELECT p.public_id FROM repos r JOIN projects p ON p.id = r.project_id WHERE r.public_id = ?
		UNION
		SELECT p.public_id
		FROM project_repos pr
		JOIN projects p ON p.id = pr.project_id
		WHERE pr.repo_id = (SELECT id FROM repos WHERE public_id = ?)
	`, repoID, repoID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&id)
		if err != nil {
			return nil, err
		}
		ids = append(ids, id)
	}
	return ids, rows.Err()
}

func (s *Store) ListAvailableReposForProject(projectID string, query string, limit int, offset int) ([]models.Repo, error) {
	var rows *sql.Rows
	var err error
	var repos []models.Repo
	var r models.Repo
	var created time.Time
	if limit <= 0 {
		limit = 100
	}
	if offset < 0 {
		offset = 0
	}
	if query == "" {
		rows, err = s.DB.Query(`
			SELECT r.public_id, p.public_id, r.name, r.type, r.path, u.public_id, r.created_at
			FROM repos r
			JOIN projects p ON p.id = r.project_id
			JOIN users u ON u.id = r.created_by
			WHERE p.public_id <> ?
			  AND r.id NOT IN (
			    SELECT pr.repo_id
			    FROM project_repos pr
			    JOIN projects px ON px.id = pr.project_id
			    WHERE px.public_id = ?
			  )
			ORDER BY r.name
			LIMIT ? OFFSET ?
		`, projectID, projectID, limit, offset)
	} else {
		rows, err = s.DB.Query(`
			SELECT r.public_id, p.public_id, r.name, r.type, r.path, u.public_id, r.created_at
			FROM repos r
			JOIN projects p ON p.id = r.project_id
			JOIN users u ON u.id = r.created_by
			WHERE p.public_id <> ?
			  AND r.id NOT IN (
			    SELECT pr.repo_id
			    FROM project_repos pr
			    JOIN projects px ON px.id = pr.project_id
			    WHERE px.public_id = ?
			  )
			  AND r.name LIKE ?
			ORDER BY r.name
			LIMIT ? OFFSET ?
		`, projectID, projectID, "%"+query+"%", limit, offset)
	}
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&r.ID, &r.ProjectID, &r.Name, &r.Type, &r.Path, &r.CreatedBy, &created)
		if err != nil {
			return nil, err
		}
		r.CreatedAt = created.Unix()
		r.IsForeign = false
		repos = append(repos, r)
	}
	return repos, rows.Err()
}

func (s *Store) CreateIssue(issue models.Issue) (models.Issue, error) {
	var id string
	var err error
	var now time.Time
	var nowUnix int64
	if issue.ID == "" {
		id, err = util.NewID()
		if err != nil {
			return issue, err
		}
		issue.ID = id
	}
	now = time.Now().UTC()
	nowUnix = now.Unix()
	issue.CreatedAt = nowUnix
	issue.UpdatedAt = nowUnix
	if issue.Status == "" {
		issue.Status = "open"
	}
	_, err = s.DB.Exec(`
		INSERT INTO issues (public_id, project_id, title, body, status, created_by, assignee_id, created_at, updated_at)
		VALUES (?, (SELECT id FROM projects WHERE public_id = ?), ?, ?, ?, (SELECT id FROM users WHERE public_id = ?), (SELECT id FROM users WHERE public_id = ?), ?, ?)
	`, issue.ID, issue.ProjectID, issue.Title, issue.Body, issue.Status, issue.CreatedBy, issue.AssigneeID, now, now)
	return issue, err
}

func (s *Store) UpdateIssue(issue models.Issue) error {
	var err error
	var now time.Time
	var nowUnix int64
	now = time.Now().UTC()
	nowUnix = now.Unix()
	issue.UpdatedAt = nowUnix
	_, err = s.DB.Exec(`UPDATE issues SET title = ?, body = ?, status = ?, assignee_id = (SELECT id FROM users WHERE public_id = ?), updated_at = ? WHERE public_id = ?`,
		issue.Title, issue.Body, issue.Status, issue.AssigneeID, now, issue.ID)
	return err
}

func (s *Store) GetIssue(id string) (models.Issue, error) {
	var issue models.Issue
	var row *sql.Row
	var created time.Time
	var updated time.Time
	var err error
	row = s.DB.QueryRow(`SELECT i.public_id, p.public_id, i.title, i.body, i.status, cu.public_id, COALESCE(au.public_id, ''), i.created_at, i.updated_at
		FROM issues i
		JOIN projects p ON p.id = i.project_id
		JOIN users cu ON cu.id = i.created_by
		LEFT JOIN users au ON au.id = i.assignee_id
		WHERE i.public_id = ?`, id)
	err = row.Scan(&issue.ID, &issue.ProjectID, &issue.Title, &issue.Body, &issue.Status, &issue.CreatedBy, &issue.AssigneeID, &created, &updated)
	if err != nil {
		return issue, err
	}
	issue.CreatedAt = created.Unix()
	issue.UpdatedAt = updated.Unix()
	return issue, nil
}

func (s *Store) ListIssues(projectID string) ([]models.Issue, error) {
	var rows *sql.Rows
	var err error
	var issues []models.Issue
	var issue models.Issue
	var created time.Time
	var updated time.Time
	rows, err = s.DB.Query(`SELECT i.public_id, p.public_id, i.title, i.body, i.status, cu.public_id, COALESCE(au.public_id, ''), i.created_at, i.updated_at
		FROM issues i
		JOIN projects p ON p.id = i.project_id
		JOIN users cu ON cu.id = i.created_by
		LEFT JOIN users au ON au.id = i.assignee_id
		WHERE i.project_id = (SELECT id FROM projects WHERE public_id = ?)
		ORDER BY i.created_at DESC`, projectID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&issue.ID, &issue.ProjectID, &issue.Title, &issue.Body, &issue.Status, &issue.CreatedBy, &issue.AssigneeID, &created, &updated)
		if err != nil {
			return nil, err
		}
		issue.CreatedAt = created.Unix()
		issue.UpdatedAt = updated.Unix()
		issues = append(issues, issue)
	}
	return issues, rows.Err()
}

func (s *Store) AddIssueComment(comment models.IssueComment) (models.IssueComment, error) {
	var id string
	var err error
	var now time.Time
	var nowUnix int64
	if comment.ID == "" {
		id, err = util.NewID()
		if err != nil {
			return comment, err
		}
		comment.ID = id
	}
	now = time.Now().UTC()
	nowUnix = now.Unix()
	comment.CreatedAt = nowUnix
	_, err = s.DB.Exec(`INSERT INTO issue_comments (public_id, issue_id, body, created_by, created_at)
		VALUES (?, (SELECT id FROM issues WHERE public_id = ?), ?, (SELECT id FROM users WHERE public_id = ?), ?)`,
		comment.ID, comment.IssueID, comment.Body, comment.CreatedBy, now)
	return comment, err
}

func (s *Store) CreateWikiPage(page models.WikiPage) (models.WikiPage, error) {
	var id string
	var err error
	var now time.Time
	var nowUnix int64
	if page.ID == "" {
		id, err = util.NewID()
		if err != nil {
			return page, err
		}
		page.ID = id
	}
	now = time.Now().UTC()
	nowUnix = now.Unix()
	page.UpdatedAt = nowUnix
	_, err = s.DB.Exec(`INSERT INTO wiki_pages (public_id, project_id, title, slug, body, created_by, updated_at)
		VALUES (?, (SELECT id FROM projects WHERE public_id = ?), ?, ?, ?, (SELECT id FROM users WHERE public_id = ?), ?)`,
		page.ID, page.ProjectID, page.Title, page.Slug, page.Body, page.CreatedBy, now)
	return page, err
}

func (s *Store) UpdateWikiPage(page models.WikiPage) error {
	var err error
	var now time.Time
	var nowUnix int64
	now = time.Now().UTC()
	nowUnix = now.Unix()
	page.UpdatedAt = nowUnix
	_, err = s.DB.Exec(`UPDATE wiki_pages SET title = ?, body = ?, updated_at = ? WHERE public_id = ?`, page.Title, page.Body, now, page.ID)
	return err
}

func (s *Store) ListWikiPages(projectID string) ([]models.WikiPage, error) {
	var rows *sql.Rows
	var err error
	var pages []models.WikiPage
	var page models.WikiPage
	var updated time.Time
	rows, err = s.DB.Query(`SELECT w.public_id, p.public_id, w.title, w.slug, w.body, u.public_id, w.updated_at
		FROM wiki_pages w
		JOIN projects p ON p.id = w.project_id
		JOIN users u ON u.id = w.created_by
		WHERE w.project_id = (SELECT id FROM projects WHERE public_id = ?)
		ORDER BY w.title`, projectID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&page.ID, &page.ProjectID, &page.Title, &page.Slug, &page.Body, &page.CreatedBy, &updated)
		if err != nil {
			return nil, err
		}
		page.UpdatedAt = updated.Unix()
		pages = append(pages, page)
	}
	return pages, rows.Err()
}

func (s *Store) GetWikiPage(id string) (models.WikiPage, error) {
	var page models.WikiPage
	var row *sql.Row
	var updated time.Time
	var err error
	row = s.DB.QueryRow(`SELECT w.public_id, p.public_id, w.title, w.slug, w.body, u.public_id, w.updated_at
		FROM wiki_pages w
		JOIN projects p ON p.id = w.project_id
		JOIN users u ON u.id = w.created_by
		WHERE w.public_id = ?`, id)
	err = row.Scan(&page.ID, &page.ProjectID, &page.Title, &page.Slug, &page.Body, &page.CreatedBy, &updated)
	if err != nil {
		return page, err
	}
	page.UpdatedAt = updated.Unix()
	return page, nil
}

func (s *Store) CreateUpload(upload models.Upload) (models.Upload, error) {
	var id string
	var err error
	var now time.Time
	var nowUnix int64
	if upload.ID == "" {
		id, err = util.NewID()
		if err != nil {
			return upload, err
		}
		upload.ID = id
	}
	now = time.Now().UTC()
	nowUnix = now.Unix()
	upload.CreatedAt = nowUnix
	_, err = s.DB.Exec(`INSERT INTO uploads (public_id, project_id, filename, content_type, size, storage_path, created_by, created_at)
		VALUES (?, (SELECT id FROM projects WHERE public_id = ?), ?, ?, ?, ?, (SELECT id FROM users WHERE public_id = ?), ?)`,
		upload.ID, upload.ProjectID, upload.Filename, upload.ContentType, upload.Size, upload.StoragePath, upload.CreatedBy, now)
	return upload, err
}

func (s *Store) GetUpload(id string) (models.Upload, error) {
	var upload models.Upload
	var row *sql.Row
	var created time.Time
	var err error
	row = s.DB.QueryRow(`SELECT up.public_id, p.public_id, up.filename, up.content_type, up.size, up.storage_path, u.public_id, up.created_at
		FROM uploads up
		JOIN projects p ON p.id = up.project_id
		JOIN users u ON u.id = up.created_by
		WHERE up.public_id = ?`, id)
	err = row.Scan(&upload.ID, &upload.ProjectID, &upload.Filename, &upload.ContentType, &upload.Size, &upload.StoragePath, &upload.CreatedBy, &created)
	if err != nil {
		return upload, err
	}
	upload.CreatedAt = created.Unix()
	return upload, nil
}

func (s *Store) ListUploads(projectID string) ([]models.Upload, error) {
	var rows *sql.Rows
	var err error
	var uploads []models.Upload
	var upload models.Upload
	var created time.Time
	rows, err = s.DB.Query(`SELECT up.public_id, p.public_id, up.filename, up.content_type, up.size, up.storage_path, u.public_id, up.created_at
		FROM uploads up
		JOIN projects p ON p.id = up.project_id
		JOIN users u ON u.id = up.created_by
		WHERE up.project_id = (SELECT id FROM projects WHERE public_id = ?)
		ORDER BY up.created_at DESC`, projectID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		err = rows.Scan(&upload.ID, &upload.ProjectID, &upload.Filename, &upload.ContentType, &upload.Size, &upload.StoragePath, &upload.CreatedBy, &created)
		if err != nil {
			return nil, err
		}
		upload.CreatedAt = created.Unix()
		uploads = append(uploads, upload)
	}
	return uploads, rows.Err()
}

func (s *Store) RequireAdmin(user models.User) error {
	if !user.IsAdmin {
		return errors.New("admin required")
	}
	return nil
}

func mustID() string {
	var id string
	var err error
	id, err = util.NewID()
	if err != nil {
		panic(err)
	}
	return id
}

func nullIfEmpty(value string) interface{} {
	if value == "" {
		return nil
	}
	return value
}