package system

import (
	"context"
	"encoding/json"
	"errors"
	"fmt"
	"strings"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgtype"
	"github.com/jackc/pgx/v5/pgxpool"

	"github.com/TaruLab/keluargamart-backend/internal/auth"
	"github.com/TaruLab/keluargamart-backend/internal/db"
	"github.com/TaruLab/keluargamart-backend/pkg/logger"
)

// ErrInUse is returned when a row cannot be deleted because other rows
// reference it (foreign-key constraint).
var ErrInUse = errors.New("in use")

// ErrConflict is returned when a unique constraint is violated (duplicate
// store code).
var ErrConflict = errors.New("conflict")

// ErrApplicationAccess is returned when the application axis derived from a
// user's role could not be persisted, so the caller must not be told the new
// shell applies. The driver error is logged, never returned: respondErr echoes
// err.Error() to the client, so a raw pgx error would leak SQL detail.
var ErrApplicationAccess = errors.New("application access could not be persisted")

// Repository persists users, roles and activity logs in PostgreSQL (sqlc).
type Repository struct {
	pool *pgxpool.Pool
	q    *db.Queries
}

func NewRepository(pool *pgxpool.Pool) *Repository {
	return &Repository{pool: pool, q: db.New(pool)}
}

func (r *Repository) Begin(ctx context.Context) (Tx, error) {
	return r.pool.Begin(ctx)
}

// queries binds the sqlc queries to the caller's transaction, falling back to
// the pool only when the caller is not inside one. Reading through the pool
// while holding a transaction is how an aborted write becomes visible as a
// success.
func (r *Repository) queries(q db.DBTX) *db.Queries {
	if q == nil {
		return r.q
	}
	return db.New(q)
}

// --- users -----------------------------------------------------------------

func (r *Repository) countUsersByModule(ctx context.Context, storeIDs []int64, roles []string, selfID int64, search string) (ModuleCounts, error) {
	var sb strings.Builder
	var args []any
	sb.WriteString("SELECT count(*) AS all_count, ")
	sb.WriteString("count(*) FILTER (WHERE role NOT IN ('cashier', 'buyer')) AS erp_count, ")
	sb.WriteString("count(*) FILTER (WHERE role = 'cashier') AS pos_count, ")
	sb.WriteString("count(*) FILTER (WHERE role = 'buyer') AS b2b_count ")
	sb.WriteString("FROM users u WHERE 1=1 ")

	if len(storeIDs) > 0 {
		args = append(args, storeIDs)
		sb.WriteString(fmt.Sprintf(" AND EXISTS (SELECT 1 FROM store_users su WHERE su.user_id = u.id AND su.store_id = ANY($%d))", len(args)))
	}
	if len(roles) > 0 || selfID > 0 {
		args = append(args, roles, selfID)
		sb.WriteString(fmt.Sprintf(" AND (cardinality($%d::text[]) = 0 OR u.role::text = ANY($%d::text[]) OR u.id = $%d)", len(args)-1, len(args)-1, len(args)))
	}
	if s := strings.TrimSpace(search); s != "" {
		args = append(args, s)
		sb.WriteString(fmt.Sprintf(" AND (u.name ILIKE '%%' || $%d || '%%' OR u.email ILIKE '%%' || $%d || '%%')", len(args), len(args)))
	}

	var counts ModuleCounts
	err := r.pool.QueryRow(ctx, sb.String(), args...).Scan(&counts.All, &counts.ERP, &counts.POS, &counts.B2B)
	if err != nil {
		return ModuleCounts{}, err
	}
	return counts, nil
}

func (r *Repository) queryUsers(ctx context.Context, storeIDs []int64, roles []string, selfID int64, page, perPage int, filter UserFilter) ([]auth.User, int64, error) {
	var whereClauses []string
	var args []any

	if len(storeIDs) > 0 {
		args = append(args, storeIDs)
		whereClauses = append(whereClauses, fmt.Sprintf("EXISTS (SELECT 1 FROM store_users su WHERE su.user_id = u.id AND su.store_id = ANY($%d))", len(args)))
	}
	if len(roles) > 0 || selfID > 0 {
		args = append(args, roles, selfID)
		whereClauses = append(whereClauses, fmt.Sprintf("(cardinality($%d::text[]) = 0 OR u.role::text = ANY($%d::text[]) OR u.id = $%d)", len(args)-1, len(args)-1, len(args)))
	}
	if search := strings.TrimSpace(filter.Search); search != "" {
		args = append(args, search)
		whereClauses = append(whereClauses, fmt.Sprintf("(u.name ILIKE '%%' || $%d || '%%' OR u.email ILIKE '%%' || $%d || '%%')", len(args), len(args)))
	}
	if role := strings.TrimSpace(filter.Role); role != "" {
		args = append(args, role)
		whereClauses = append(whereClauses, fmt.Sprintf("u.role::text = $%d", len(args)))
	}
	if status := strings.TrimSpace(filter.Status); status != "" {
		if status == "active" {
			whereClauses = append(whereClauses, "u.is_active = true")
		} else if status == "blocked" || status == "inactive" {
			whereClauses = append(whereClauses, "u.is_active = false")
		}
	}
	if mod := strings.ToLower(strings.TrimSpace(filter.Module)); mod != "" && mod != "all" {
		switch mod {
		case "pos":
			whereClauses = append(whereClauses, "u.role = 'cashier'")
		case "b2b", "ecommerce":
			whereClauses = append(whereClauses, "u.role = 'buyer'")
		case "erp", "backoffice":
			whereClauses = append(whereClauses, "u.role NOT IN ('cashier', 'buyer')")
		}
	}

	whereSQL := ""
	if len(whereClauses) > 0 {
		whereSQL = " WHERE " + strings.Join(whereClauses, " AND ")
	}

	countSQL := "SELECT count(*) FROM users u" + whereSQL
	var total int64
	if err := r.pool.QueryRow(ctx, countSQL, args...).Scan(&total); err != nil {
		return nil, 0, err
	}

	limit := perPage
	if limit <= 0 {
		limit = 15
	}
	offset := (page - 1) * limit
	if offset < 0 {
		offset = 0
	}

	args = append(args, limit, offset)
	querySQL := fmt.Sprintf(`
		SELECT u.id, u.name, u.email, u.email_verified_at, u.password, u.role, u.permissions,
		       u.is_active, u.last_login_at, u.avatar, u.created_at, u.updated_at
		FROM users u
		%s
		ORDER BY u.created_at DESC, u.id DESC
		LIMIT $%d OFFSET $%d`, whereSQL, len(args)-1, len(args))

	rows, err := r.pool.Query(ctx, querySQL, args...)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	out := make([]auth.User, 0, limit)
	for rows.Next() {
		var rf userRowFields
		if err := rows.Scan(
			&rf.ID,
			&rf.Name,
			&rf.Email,
			&rf.EmailVerifiedAt,
			&rf.Password,
			&rf.Role,
			&rf.Permissions,
			&rf.IsActive,
			&rf.LastLoginAt,
			&rf.Avatar,
			&rf.CreatedAt,
			&rf.UpdatedAt,
		); err != nil {
			return nil, 0, err
		}
		u := userFromRow(rf)
		u.ApplicationAccess = auth.DeriveApplicationAccess(u.Role, false)
		out = append(out, *u)
	}
	if err := rows.Err(); err != nil {
		return nil, 0, err
	}
	return out, total, nil
}

func (r *Repository) ListUsers(ctx context.Context, page, perPage int, filter UserFilter) ([]auth.User, int64, ModuleCounts, error) {
	counts, err := r.countUsersByModule(ctx, nil, nil, 0, filter.Search)
	if err != nil {
		return nil, 0, ModuleCounts{}, err
	}
	users, total, err := r.queryUsers(ctx, nil, nil, 0, page, perPage, filter)
	if err != nil {
		return nil, 0, ModuleCounts{}, err
	}
	return users, total, counts, nil
}

func (r *Repository) ListUsersInStores(ctx context.Context, storeIDs []int64, roles []string, selfID int64, page, perPage int, filter UserFilter) ([]auth.User, int64, ModuleCounts, error) {
	counts, err := r.countUsersByModule(ctx, storeIDs, roles, selfID, filter.Search)
	if err != nil {
		return nil, 0, ModuleCounts{}, err
	}
	users, total, err := r.queryUsers(ctx, storeIDs, roles, selfID, page, perPage, filter)
	if err != nil {
		return nil, 0, ModuleCounts{}, err
	}
	return users, total, counts, nil
}

func (r *Repository) UserInStores(ctx context.Context, userID int64, storeIDs []int64) (bool, error) {
	return r.q.UserInStores(ctx, db.UserInStoresParams{UserID: userID, Column2: storeIDs})
}

func (r *Repository) UserByID(ctx context.Context, id int64) (*auth.User, error) {
	row, err := r.q.GetUserByID(ctx, id)
	if err != nil {
		return nil, mapErr(err)
	}
	return userFromRow(userRowFields(row)), nil
}

func (r *Repository) UserByEmail(ctx context.Context, email string) (*auth.User, error) {
	row, err := r.q.GetUserByEmail(ctx, email)
	if err != nil {
		return nil, mapErr(err)
	}
	return userFromRow(userRowFields(row)), nil
}

// CountActiveAdmins counts admins with is_active=true (lockout guard).
func (r *Repository) CountActiveAdmins(ctx context.Context) (int64, error) {
	return r.q.CountActiveAdmins(ctx)
}

// CreateUser inserts the user row and pins its application axis (the shell the
// derived role is allowed to enter) in ONE transaction.
//
// WRITE-RESULT AUDIT 2026-09-27: the application_access UPDATE used to discard
// both its error and its row count with `_, _ =`, and the returned payload then
// carried the derived value anyway — a privilege/shell assignment that never
// persisted was reported to the operator as applied. The driver error is now
// propagated (never as a success), the row count is checked, and a failed write
// rolls the INSERT back so no half-provisioned user survives. Same defect on
// the admin role-change path: see UpdateUser.
func (r *Repository) CreateUser(ctx context.Context, u *auth.User, passwordHash string) (*auth.User, error) {
	tx, err := r.pool.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)
	row, err := r.q.WithTx(tx).CreateUser(ctx, db.CreateUserParams{
		Name:        u.Name,
		Email:       u.Email,
		Password:    passwordHash,
		Role:        string(u.Role),
		Permissions: userPermissionsJSON(u),
		IsActive:    u.IsActive,
	})
	if err != nil {
		return nil, err
	}
	appAccess := auth.DeriveApplicationAccess(u.Role, false)
	if err := setApplicationAccess(ctx, tx, row.ID, appAccess); err != nil {
		return nil, err
	}
	if err := tx.Commit(ctx); err != nil {
		return nil, err
	}
	out := userFromRow(userRowFields(row))
	out.ApplicationAccess = appAccess
	return out, nil
}

// UpdateUser applies the role/name/email/password change and the application
// axis it implies in ONE transaction; see CreateUser for why the axis write is
// no longer allowed to fail silently. A failed axis write rolls the role change
// back rather than leaving a user whose stored role and stored shell disagree —
// internal/auth/repository.go:52-53 documents that disagreement locking an
// account out of the wrong shell, with no second line of defence here.
func (r *Repository) UpdateUser(ctx context.Context, u *auth.User, passwordHash *string) (*auth.User, error) {
	tx, err := r.pool.Begin(ctx)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback(ctx)
	row, err := r.q.WithTx(tx).UpdateUser(ctx, db.UpdateUserParams{
		ID:          u.ID,
		Name:        u.Name,
		Email:       u.Email,
		Role:        string(u.Role),
		Permissions: userPermissionsJSON(u),
		IsActive:    u.IsActive,
		Password:    toText(passwordHash),
	})
	if err != nil {
		return nil, err
	}
	appAccess := auth.DeriveApplicationAccess(u.Role, false)
	if err := setApplicationAccess(ctx, tx, row.ID, appAccess); err != nil {
		return nil, err
	}
	if err := tx.Commit(ctx); err != nil {
		return nil, err
	}
	out := userFromRow(userRowFields(row))
	out.ApplicationAccess = appAccess
	return out, nil
}

// setApplicationAccess pins users.application_access and refuses to report
// success unless the row count proves it. Split out of CreateUser/UpdateUser so
// the decision is unit-testable without a live database, exactly as
// observeTokenRevocation is for the token-revocation fix.
//
// It runs on the caller's db.DBTX (the caller's transaction, never the pool) via
// the generated SetUserApplicationAccess query — the :execrows annotation is what
// makes the count reach this function; a raw Exec had to read it off
// pgconn.CommandTag by hand.
func setApplicationAccess(ctx context.Context, q db.DBTX, userID int64, app auth.ApplicationAccess) error {
	rows, err := db.New(q).SetUserApplicationAccess(ctx, db.SetUserApplicationAccessParams{
		ApplicationAccess: string(app),
		ID:                userID,
	})
	return observeApplicationAccess(ctx, userID, rows, err)
}

// observeApplicationAccess maps the application_access write result to the
// caller's verdict: the driver error is logged and reported as
// ErrApplicationAccess (never as a success), and a zero-row write is
// ErrNotFound — the row the caller just wrote is not there. It deliberately
// takes ids and the derived value only, never credentials.
func observeApplicationAccess(ctx context.Context, userID, rows int64, err error) error {
	log := logger.FromContext(ctx)
	if err != nil {
		log.Error("application_access write failed", "user_id", userID, "err", err)
		return ErrApplicationAccess
	}
	if rows == 0 {
		log.Error("application_access write matched zero rows", "user_id", userID)
		return auth.ErrNotFound
	}
	return nil
}

// SetUserActive flips users.is_active and refuses to report a write that
// matched no row.
//
// WRITE-RESULT AUDIT 2026-09-27 (A16): the query was annotated :exec, so the row
// count never reached Go and this wrapper could only hand back the driver error
// — a deactivation that matched zero users was reported to the operator as
// applied. The annotation is now :execrows and the count is propagated.
//
// A zero is a hard error here, not a warning, because it is not a legitimate
// no-op: the predicate is a bare `WHERE id = $1` with no business filter, and
// ToggleUserActive has just read this very row through UserByID
// (internal/system/service.go), so a zero means the row vanished underneath the
// caller. Same verdict as DeleteUser below. It is logged because it is an
// anomaly rather than the expected "no such id" 404 — DeleteUser's zero comes
// straight off the URL and needs no signal, this one never should happen.
func (r *Repository) SetUserActive(ctx context.Context, id int64, active bool) error {
	rows, err := r.q.SetUserActive(ctx, db.SetUserActiveParams{ID: id, IsActive: active})
	return observeUserActive(ctx, id, rows, err)
}

// observeUserActive maps the is_active write result to the caller's verdict. It
// takes ids and the count only, never credentials, and is split out of
// SetUserActive so the branch is unit-testable without a live database, exactly
// as observeTokenRevocation is for the token-revocation fix.
func observeUserActive(ctx context.Context, userID, rows int64, err error) error {
	if err != nil {
		return err
	}
	if rows == 0 {
		logger.FromContext(ctx).Error("is_active write matched zero rows; the user was not deactivated",
			"user_id", userID, "rows", rows)
		return auth.ErrNotFound
	}
	return nil
}

func (r *Repository) DeleteUser(ctx context.Context, id int64) error {
	n, err := r.q.DeleteUser(ctx, id)
	if err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == "23503" {
			return ErrInUse
		}
		return err
	}
	if n == 0 {
		return auth.ErrNotFound
	}
	return nil
}

// --- roles -----------------------------------------------------------------

func (r *Repository) ListRoles(ctx context.Context) ([]db.Role, error) {
	return r.q.ListRoles(ctx)
}

func (r *Repository) RoleByID(ctx context.Context, id int64) (db.Role, error) {
	row, err := r.q.GetRoleByID(ctx, id)
	if err != nil {
		return db.Role{}, mapErr(err)
	}
	return row, nil
}

func (r *Repository) RoleByName(ctx context.Context, name string) (db.Role, error) {
	row, err := r.q.GetRoleByName(ctx, name)
	if err != nil {
		return db.Role{}, mapErr(err)
	}
	return row, nil
}

func (r *Repository) CountUsersByRole(ctx context.Context, role string) (int64, error) {
	return r.q.CountUsersByRole(ctx, role)
}

// RenameRole cascades roles.name + users.role + role_permissions.role in ONE
// transaction so a rename never strands users or matrix rows on the old name.
func (r *Repository) RenameRole(ctx context.Context, id int64, oldName, newName string) (db.Role, error) {
	tx, err := r.pool.Begin(ctx)
	if err != nil {
		return db.Role{}, err
	}
	defer tx.Rollback(ctx)
	q := r.q.WithTx(tx)
	n, err := q.UpdateRole(ctx, db.UpdateRoleParams{ID: id, Name: newName})
	if err != nil {
		return db.Role{}, err
	}
	if n == 0 {
		return db.Role{}, auth.ErrNotFound
	}
	if _, err := q.UpdateUsersRole(ctx, db.UpdateUsersRoleParams{Role: oldName, Role_2: newName}); err != nil {
		return db.Role{}, err
	}
	if _, err := q.UpdateRolePermissionsRole(ctx, db.UpdateRolePermissionsRoleParams{Role: oldName, Role_2: newName}); err != nil {
		return db.Role{}, err
	}
	if err := tx.Commit(ctx); err != nil {
		return db.Role{}, err
	}
	return r.RoleByID(ctx, id)
}

// DeleteRoleTx refuses with ErrInUse when any user still holds the role, else
// deletes its role_permissions rows + role row in ONE transaction.
func (r *Repository) DeleteRoleTx(ctx context.Context, id int64, name string) error {
	tx, err := r.pool.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx)
	q := r.q.WithTx(tx)
	n, err := q.CountUsersByRole(ctx, name)
	if err != nil {
		return err
	}
	if n > 0 {
		return fmt.Errorf("role %q is still assigned to %d user(s): %w", name, n, ErrInUse)
	}
	if err := q.DeleteRolePermissionsByRole(ctx, name); err != nil {
		return err
	}
	d, err := q.DeleteRole(ctx, id)
	if err != nil {
		return err
	}
	if d == 0 {
		return auth.ErrNotFound
	}
	return tx.Commit(ctx)
}

func (r *Repository) CreateRole(ctx context.Context, name string) (db.Role, error) {
	return r.q.CreateRole(ctx, name)
}

func (r *Repository) UpdateRole(ctx context.Context, id int64, name string) (db.Role, error) {
	n, err := r.q.UpdateRole(ctx, db.UpdateRoleParams{ID: id, Name: name})
	if err != nil {
		return db.Role{}, err
	}
	if n == 0 {
		return db.Role{}, auth.ErrNotFound
	}
	return r.RoleByID(ctx, id)
}

func (r *Repository) DeleteRole(ctx context.Context, id int64) error {
	n, err := r.q.DeleteRole(ctx, id)
	if err != nil {
		return err
	}
	if n == 0 {
		return auth.ErrNotFound
	}
	return nil
}

// --- activity logs ---------------------------------------------------------

func (r *Repository) ListLogs(ctx context.Context, q LogQuery, page, perPage int) ([]db.ListActivityLogsRow, int64, error) {
	total, err := r.q.CountActivityLogs(ctx, db.CountActivityLogsParams{
		Column1: q.Module,
		Column2: q.UserID,
		Column3: q.DateFrom,
		Column4: q.DateTo,
	})
	if err != nil {
		return nil, 0, err
	}
	rows, err := r.q.ListActivityLogs(ctx, db.ListActivityLogsParams{
		Column1: q.Module,
		Column2: q.UserID,
		Column3: q.DateFrom,
		Column4: q.DateTo,
		Limit:   int32(perPage),
		Offset:  int32((page - 1) * perPage),
	})
	if err != nil {
		return nil, 0, err
	}
	return rows, total, nil
}

func (r *Repository) InsertLog(ctx context.Context, p db.CreateActivityLogParams) error {
	return r.q.CreateActivityLog(ctx, p)
}

// objectLabelSources maps an audit model_type to the table and column that
// names it. Closed allowlist, not string interpolation: model_type arrives
// from middleware classify(), and a table name must never be built from it.
var objectLabelSources = map[string]struct{ table, label string }{
	"users":                {"users", "name"},
	"products":             {"products", "name"},
	"customers":            {"customers", "name"},
	"suppliers":            {"suppliers", "name"},
	"stores":               {"stores", "name"},
	"warehouses":           {"warehouses", "name"},
	"employees":            {"employees", "name"},
	"sales-orders":         {"sales_orders", "so_number"},
	"purchase-orders":      {"purchase_orders", "po_number"},
	"delivery-orders":      {"delivery_orders", "do_number"},
	"shipments":            {"shipments", "shipment_number"},
	"invoices":             {"invoices", "invoice_number"},
	"galon":                {"products", "name"},
	"memberships":          {"memberships", "member_number"},
	"cashier/shifts":       {"cashier_shifts", "shift_number"},
	"pos":                  {"sales_orders", "so_number"},
	"sales-returns":        {"sales_returns", "return_number"},
	"sales-vouchers":       {"sales_vouchers", "voucher_number"},
	"vouchers":             {"vouchers", "voucher_number"},
	"cashflows":            {"cashflows", "transaction_number"},
	"fleets":               {"fleet", "plate_number"},
	"distribution/drivers": {"employees", "name"},
}

// ObjectLabels resolves display names for one model type in a single query,
// so a page of mixed rows costs one query per type rather than one per row.
// Unknown types and missing rows yield no entry: the caller falls back to the
// bare id, which is still more useful than a failed list.
func (r *Repository) ObjectLabels(ctx context.Context, modelType string, ids []int64) (map[int64]string, error) {
	out := map[int64]string{}
	src, ok := objectLabelSources[modelType]
	if !ok || len(ids) == 0 {
		return out, nil
	}
	// The table and column come from the allowlist above, never from input;
	// only the ids are bound parameters.
	// A plain Go slice: pgx encodes it as an array natively. (pgtype.Array
	// without explicit Dims encoded as empty, silently resolving nothing.)
	rows, err := r.pool.Query(ctx,
		"SELECT id, "+src.label+" FROM "+src.table+" WHERE id = ANY($1)",
		ids)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		var id int64
		var label *string
		if err := rows.Scan(&id, &label); err != nil {
			return nil, err
		}
		if label != nil && *label != "" {
			out[id] = *label
		}
	}
	return out, rows.Err()
}

func (r *Repository) ClearLogs(ctx context.Context) error {
	return r.q.ClearActivityLogs(ctx)
}

// --- converters ------------------------------------------------------------

// userRowFields mirrors the identical sqlc user row structs so any of them can
// be converted (Go allows conversion between structurally identical types).
type userRowFields struct {
	ID              int64
	Name            string
	Email           string
	EmailVerifiedAt pgtype.Timestamptz
	Password        string
	Role            string
	Permissions     []byte
	IsActive        bool
	LastLoginAt     pgtype.Timestamptz
	Avatar          pgtype.Text
	CreatedAt       pgtype.Timestamptz
	UpdatedAt       pgtype.Timestamptz
}

func userFromRow(r userRowFields) *auth.User {
	u := &auth.User{
		ID:              r.ID,
		Name:            r.Name,
		Email:           r.Email,
		Password:        r.Password,
		Role:            auth.Role(r.Role),
		IsActive:        r.IsActive,
		CreatedAt:       r.CreatedAt.Time,
		UpdatedAt:       r.UpdatedAt.Time,
		EmailVerifiedAt: tsPtr(r.EmailVerifiedAt),
		LastLoginAt:     tsPtr(r.LastLoginAt),
		Avatar:          textPtr(r.Avatar),
	}
	if len(r.Permissions) > 0 && string(r.Permissions) != "null" {
		var pm map[string]bool
		if err := json.Unmarshal(r.Permissions, &pm); err == nil {
			m := auth.Permissions{}
			for k, v := range pm {
				m[k] = v
			}
			u.Permissions = m
		}
	}
	return u
}

func toText(s *string) pgtype.Text {
	if s == nil {
		return pgtype.Text{}
	}
	return pgtype.Text{String: *s, Valid: true}
}

func tsPtr(t pgtype.Timestamptz) *time.Time {
	if !t.Valid {
		return nil
	}
	v := t.Time
	return &v
}

func textPtr(t pgtype.Text) *string {
	if !t.Valid {
		return nil
	}
	v := t.String
	return &v
}

func userPermissionsJSON(u *auth.User) []byte {
	if u.Permissions == nil {
		return nil
	}
	b, err := json.Marshal(u.Permissions)
	if err != nil {
		return nil
	}
	return b
}

func mapErr(err error) error {
	if errors.Is(err, pgx.ErrNoRows) {
		return auth.ErrNotFound
	}
	return err
}

// --- tenant stores (multi-tenant §6) ---------------------------------------

func (r *Repository) ListStores(ctx context.Context) ([]db.Store, error) {
	rows, err := r.q.ListStores(ctx)
	if err != nil {
		return nil, err
	}
	out := make([]db.Store, 0, len(rows))
	for _, rw := range rows {
		out = append(out, storeFromListRow(rw))
	}
	return out, nil
}

func (r *Repository) StoreByID(ctx context.Context, id int64) (db.Store, error) {
	s, err := r.q.GetStoreByID(ctx, id)
	return storeFromGetRow(s), mapErr(err)
}

func (r *Repository) CreateStoreWithWarehouse(ctx context.Context, sp db.CreateStoreParams, wh *db.CreateWarehouseParams) (db.Store, error) {
	tx, err := r.pool.Begin(ctx)
	if err != nil {
		return db.Store{}, err
	}
	defer tx.Rollback(ctx)
	qtx := r.q.WithTx(tx)

	if wh != nil {
		wid, err := qtx.CreateWarehouse(ctx, *wh)
		if err != nil {
			var pgErr *pgconn.PgError
			if errors.As(err, &pgErr) && pgErr.Code == "23505" {
				return db.Store{}, ErrConflict
			}
			return db.Store{}, err
		}
		sp.WarehouseID = wid
	}

	s, err := qtx.CreateStore(ctx, sp)
	if err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == "23505" {
			return db.Store{}, ErrConflict
		}
		return db.Store{}, mapErr(err)
	}
	if err := tx.Commit(ctx); err != nil {
		return db.Store{}, err
	}
	return storeFromCreateRow(s), nil
}

func (r *Repository) UpdateStore(ctx context.Context, p db.UpdateStoreParams) (db.Store, error) {
	s, err := r.q.UpdateStore(ctx, p)
	if err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == "23505" {
			return db.Store{}, ErrConflict
		}
		return db.Store{}, mapErr(err)
	}
	return storeFromUpdateRow(s), nil
}

func (r *Repository) DeleteStore(ctx context.Context, id int64) error {
	n, err := r.q.DeleteStore(ctx, id)
	if err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == "23503" {
			return ErrInUse
		}
		return err
	}
	if n == 0 {
		return auth.ErrNotFound
	}
	return nil
}

func (r *Repository) CountStoresByWarehouse(ctx context.Context, warehouseID int64) (int64, error) {
	return r.q.CountStoresByWarehouse(ctx, warehouseID)
}

func (r *Repository) WarehouseHasInventory(ctx context.Context, warehouseID int64) (bool, error) {
	return r.q.WarehouseHasInventory(ctx, warehouseID)
}

func (r *Repository) DeactivateWarehouse(ctx context.Context, id int64) error {
	n, err := r.q.DeactivateWarehouse(ctx, id)
	if err != nil {
		return err
	}
	if n == 0 {
		return auth.ErrNotFound
	}
	return nil
}

func (r *Repository) DeleteWarehouse(ctx context.Context, id int64) error {
	n, err := r.q.DeleteWarehouse(ctx, id)
	if err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == "23503" {
			return ErrInUse
		}
		return err
	}
	if n == 0 {
		return auth.ErrNotFound
	}
	return nil
}

func (r *Repository) ListStoreUsers(ctx context.Context, storeID int64) ([]db.ListStoreUsersRow, error) {
	rows, err := r.q.ListStoreUsers(ctx, storeID)
	return rows, err
}

func (r *Repository) AddStoreUser(ctx context.Context, storeID, userID int64) error {
	err := r.q.AddStoreUser(ctx, db.AddStoreUserParams{StoreID: storeID, UserID: userID})
	if err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == "23505" {
			return ErrConflict
		}
	}
	return err
}

func (r *Repository) RemoveStoreUser(ctx context.Context, storeID, userID int64) (int64, error) {
	return r.q.RemoveStoreUser(ctx, db.RemoveStoreUserParams{StoreID: storeID, UserID: userID})
}

// --- role permission matrix ------------------------------------------------

func (r *Repository) RolePermissionMatrix(ctx context.Context, q db.DBTX) ([]db.ListRolePermissionsRow, error) {
	rows, err := r.queries(q).ListRolePermissions(ctx)
	return rows, err
}

func (r *Repository) UpsertRolePermission(ctx context.Context, q db.DBTX, role, permission string) error {
	return r.queries(q).UpsertRolePermission(ctx, db.UpsertRolePermissionParams{Role: role, Permission: permission})
}

func (r *Repository) DeleteRolePermission(ctx context.Context, q db.DBTX, role, permission string) (int64, error) {
	return r.queries(q).DeleteRolePermission(ctx, db.DeleteRolePermissionParams{Role: role, Permission: permission})
}

// --- store row adapters (sqlc emits per-query structs) -----------------------

func storeFromListRow(r db.ListStoresRow) db.Store {
	return db.Store{
		ID: r.ID, Code: r.Code, Name: r.Name,
		Address: r.Address, Phone: r.Phone, IsActive: r.IsActive,
		WarehouseID: r.WarehouseID,
		Latitude:    r.Latitude, Longitude: r.Longitude, LocationNote: r.LocationNote,
		CreatedAt: r.CreatedAt, UpdatedAt: r.UpdatedAt,
	}
}

func storeFromGetRow(r db.GetStoreByIDRow) db.Store {
	return db.Store{
		ID: r.ID, Code: r.Code, Name: r.Name,
		Address: r.Address, Phone: r.Phone, IsActive: r.IsActive,
		WarehouseID: r.WarehouseID,
		Latitude:    r.Latitude, Longitude: r.Longitude,
		CreatedAt: r.CreatedAt, UpdatedAt: r.UpdatedAt,
	}
}

func storeFromCreateRow(r db.CreateStoreRow) db.Store {
	return storeFromGetRow(db.GetStoreByIDRow(r))
}

func storeFromUpdateRow(r db.UpdateStoreRow) db.Store {
	return storeFromCreateRow(db.CreateStoreRow(r))
}