Relationships

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TYPE occupancy_type AS ENUM ('owner_occupied', 'tenant');

CREATE TABLE occupancies (
    id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id      UUID NOT NULL,
    flat_id        UUID NOT NULL, -- owned by flat-service; no FK across services
    resident_id    UUID NOT NULL REFERENCES residents(id),
    occupancy_type occupancy_type NOT NULL,
    move_in_date   DATE NOT NULL,
    move_out_date  DATE, -- NULL means still resident today

    CHECK (move_out_date IS NULL OR move_out_date > move_in_date)
);

CREATE INDEX occupancies_tenant_flat_idx ON occupancies (tenant_id, flat_id);
CREATE INDEX occupancies_resident_id_idx ON occupancies (resident_id);

-- A resident cannot have two simultaneously-open occupancy rows for the
-- same flat (that would just be the same fact recorded twice).
CREATE UNIQUE INDEX occupancies_open_resident_flat_key
    ON occupancies (flat_id, resident_id)
    WHERE move_out_date IS NULL;

-- The real HOA rule: no two *tenant* occupancies for the same flat may
-- have overlapping date ranges. Family members occupying the same flat
-- concurrently is fine (they are separate rows, none of them
-- necessarily 'tenant'); renting the same unit to two different
-- households at once is not.
ALTER TABLE occupancies ADD CONSTRAINT occupancies_no_overlapping_tenancy
    EXCLUDE USING gist (
        flat_id WITH =,
        daterange(move_in_date, COALESCE(move_out_date, 'infinity'::date), '[)') WITH &&
    )
    WHERE (occupancy_type = 'tenant');

ALTER TABLE occupancies DROP CONSTRAINT IF EXISTS occupancies_no_overlapping_tenancy;
DROP TABLE IF EXISTS occupancies;
DROP TYPE IF EXISTS occupancy_type;

package postgres

import (
	"context"
	"errors"

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

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

type OccupancyRepository struct {
	pool *pgxpool.Pool
}

func NewOccupancyRepository(pool *pgxpool.Pool) *OccupancyRepository {
	return &OccupancyRepository{pool: pool}
}

func (r *OccupancyRepository) MoveIn(ctx context.Context, o domain.Occupancy) (domain.Occupancy, error) {
	const q = `
		INSERT INTO occupancies (tenant_id, flat_id, resident_id, occupancy_type, move_in_date)
		VALUES ($1, $2, $3, $4, $5)
		RETURNING id`

	row := r.pool.QueryRow(ctx, q, o.TenantID, o.FlatID, o.ResidentID, o.OccupancyType, o.MoveInDate)
	if err := row.Scan(&o.ID); err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == "23P01" { // exclusion_violation
			return domain.Occupancy{}, domain.ErrOverlappingTenancy
		}
		if errors.As(err, &pgErr) && pgErr.Code == "23505" {
			return domain.Occupancy{}, domain.ErrResidentAlreadyOccupies
		}
		return domain.Occupancy{}, err
	}
	return o, nil
}

// MoveOut never deletes the row — it stamps move_out_date so
// "who has ever lived in this flat" remains a complete, queryable
// history. CurrentOccupants below is the only place that filters it away.
func (r *OccupancyRepository) MoveOut(ctx context.Context, tenantID, occupancyID uuid.UUID, moveOutDate string) error {
	const q = `
		UPDATE occupancies SET move_out_date = $3
		WHERE tenant_id = $1 AND id = $2 AND move_out_date IS NULL`

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

func (r *OccupancyRepository) CurrentOccupants(ctx context.Context, tenantID, flatID uuid.UUID) ([]domain.Occupancy, error) {
	const q = `
		SELECT id, tenant_id, flat_id, resident_id, occupancy_type, move_in_date, move_out_date
		FROM occupancies
		WHERE tenant_id = $1 AND flat_id = $2 AND move_out_date IS NULL`

	rows, err := r.pool.Query(ctx, q, tenantID, flatID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var result []domain.Occupancy
	for rows.Next() {
		var o domain.Occupancy
		if err := rows.Scan(&o.ID, &o.TenantID, &o.FlatID, &o.ResidentID, &o.OccupancyType, &o.MoveInDate, &o.MoveOutDate); err != nil {
			return nil, err
		}
		result = append(result, o)
	}
	return result, rows.Err()
}

// History returns every occupancy this flat has ever had, open or
// closed, ordered oldest-first — this is what "resident history" means
// in this platform: never an UPDATE that erases the past, always a new
// row alongside every prior one.
func (r *OccupancyRepository) History(ctx context.Context, tenantID, flatID uuid.UUID) ([]domain.Occupancy, error) {
	const q = `
		SELECT id, tenant_id, flat_id, resident_id, occupancy_type, move_in_date, move_out_date
		FROM occupancies
		WHERE tenant_id = $1 AND flat_id = $2
		ORDER BY move_in_date ASC`

	rows, err := r.pool.Query(ctx, q, tenantID, flatID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var result []domain.Occupancy
	for rows.Next() {
		var o domain.Occupancy
		if err := rows.Scan(&o.ID, &o.TenantID, &o.FlatID, &o.ResidentID, &o.OccupancyType, &o.MoveInDate, &o.MoveOutDate); err != nil {
			return nil, err
		}
		result = append(result, o)
	}
	return result, rows.Err()
}

package domain

import (
	"errors"
	"time"

	"github.com/google/uuid"
)

type OccupancyType string

const (
	OccupancyOwnerOccupied OccupancyType = "owner_occupied"
	OccupancyTenant        OccupancyType = "tenant"
)

type Occupancy struct {
	ID            uuid.UUID
	TenantID      uuid.UUID
	FlatID        uuid.UUID
	ResidentID    uuid.UUID
	OccupancyType OccupancyType
	MoveInDate    time.Time
	MoveOutDate   *time.Time
}

var (
	ErrOverlappingTenancy               = errors.New("occupancy: flat already has an overlapping tenant occupancy for these dates")
	ErrResidentAlreadyOccupies          = errors.New("occupancy: resident already has an open occupancy for this flat")
	ErrOccupancyNotFoundOrAlreadyClosed = errors.New("occupancy: not found, or already moved out")
)