Resident Management

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE residents (
    id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id         UUID NOT NULL,
    full_name         TEXT NOT NULL,
    email             TEXT NOT NULL,
    phone             TEXT NOT NULL,
    -- Never store a government ID in the clear. Keep the last four
    -- characters for support-desk lookups and a salted hash of the full
    -- value for uniqueness checks; the plaintext never reaches this table.
    government_id_last4 TEXT NOT NULL,
    government_id_hash   TEXT NOT NULL,
    deactivated_at    TIMESTAMPTZ,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Email is unique per tenant only among active residents — a
-- deactivated resident's email must be reusable by a genuinely new
-- profile without a database-level collision.
CREATE UNIQUE INDEX residents_tenant_email_active_key
    ON residents (tenant_id, email)
    WHERE deactivated_at IS NULL;

CREATE UNIQUE INDEX residents_tenant_govid_hash_active_key
    ON residents (tenant_id, government_id_hash)
    WHERE deactivated_at IS NULL;

CREATE INDEX residents_tenant_id_idx ON residents (tenant_id);

DROP TABLE IF EXISTS residents;

package domain

import (
	"crypto/sha256"
	"encoding/hex"
	"errors"
	"time"

	"github.com/google/uuid"
)

type Resident struct {
	ID                 uuid.UUID
	TenantID           uuid.UUID
	FullName           string
	Email              string
	Phone              string
	GovernmentIDLast4  string
	GovernmentIDHash   string
	DeactivatedAt      *time.Time
	CreatedAt          time.Time
	UpdatedAt          time.Time
}

func (r *Resident) IsActive() bool {
	return r.DeactivatedAt == nil
}

// HashGovernmentID converts a raw government ID into the two columns
// residents actually stores. tenantSalt is per-tenant so the same
// government ID hashes differently across HOAs, preventing cross-tenant
// correlation of the same person even if both hashes ever leaked.
func HashGovernmentID(rawID, tenantSalt string) (last4, hash string) {
	if len(rawID) >= 4 {
		last4 = rawID[len(rawID)-4:]
	}
	sum := sha256.Sum256([]byte(tenantSalt + rawID))
	return last4, hex.EncodeToString(sum[:])
}

var (
	ErrResidentNotFound     = errors.New("resident: not found")
	ErrDuplicateEmail       = errors.New("resident: email already registered for this tenant")
	ErrDuplicateGovernmentID = errors.New("resident: government id already registered for this tenant")
	ErrResidentDeactivated  = errors.New("resident: is deactivated")
)

package postgres

import (
	"context"
	"errors"

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"

	"github.com/hoa-platform/backend/services/resident-service/internal/domain"
)

type ResidentRepository struct {
	pool *pgxpool.Pool
}

func NewResidentRepository(pool *pgxpool.Pool) *ResidentRepository {
	return &ResidentRepository{pool: pool}
}

func (r *ResidentRepository) Create(ctx context.Context, res domain.Resident) (domain.Resident, error) {
	const q = `
		INSERT INTO residents (tenant_id, full_name, email, phone, government_id_last4, government_id_hash)
		VALUES ($1, $2, $3, $4, $5, $6)
		RETURNING id, created_at, updated_at`

	row := r.pool.QueryRow(ctx, q, res.TenantID, res.FullName, res.Email, res.Phone, res.GovernmentIDLast4, res.GovernmentIDHash)
	if err := row.Scan(&res.ID, &res.CreatedAt, &res.UpdatedAt); err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == "23505" {
			if pgErr.ConstraintName == "residents_tenant_govid_hash_active_key" {
				return domain.Resident{}, domain.ErrDuplicateGovernmentID
			}
			return domain.Resident{}, domain.ErrDuplicateEmail
		}
		return domain.Resident{}, err
	}
	return res, nil
}

func (r *ResidentRepository) Get(ctx context.Context, tenantID, id uuid.UUID) (domain.Resident, error) {
	const q = `
		SELECT id, tenant_id, full_name, email, phone, government_id_last4, government_id_hash, deactivated_at, created_at, updated_at
		FROM residents
		WHERE tenant_id = $1 AND id = $2 AND deactivated_at IS NULL`

	var res domain.Resident
	row := r.pool.QueryRow(ctx, q, tenantID, id)
	err := row.Scan(&res.ID, &res.TenantID, &res.FullName, &res.Email, &res.Phone, &res.GovernmentIDLast4, &res.GovernmentIDHash, &res.DeactivatedAt, &res.CreatedAt, &res.UpdatedAt)
	if errors.Is(err, pgx.ErrNoRows) {
		return domain.Resident{}, domain.ErrResidentNotFound
	}
	return res, err
}

func (r *ResidentRepository) Deactivate(ctx context.Context, tenantID, id uuid.UUID) error {
	const q = `
		UPDATE residents SET deactivated_at = now(), updated_at = now()
		WHERE tenant_id = $1 AND id = $2 AND deactivated_at IS NULL`

	tag, err := r.pool.Exec(ctx, q, tenantID, id)
	if err != nil {
		return err
	}
	if tag.RowsAffected() == 0 {
		return domain.ErrResidentNotFound
	}
	return nil
}