Relationships

CREATE TABLE buildings (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id  UUID NOT NULL,
    name       TEXT NOT NULL,
    address    TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX buildings_tenant_id_idx ON buildings (tenant_id);

DROP TABLE IF EXISTS buildings;

-- Step 1: add nullable so the migration does not lock on a NOT NULL
-- rewrite of an already-populated table.
ALTER TABLE flats ADD COLUMN building_id UUID REFERENCES buildings(id);

-- Step 2: in a fresh environment there is nothing to backfill; in a
-- populated one, this is where an operator-run backfill script would
-- assign every existing flat to a real building before step 3 runs.

-- Step 3: tighten once every row has a value. Ship this as its own
-- migration in a follow-up deploy for a populated database — bundled
-- here because this lesson's local environment starts empty.
ALTER TABLE flats ALTER COLUMN building_id SET NOT NULL;

-- Unit numbers are unique per building now, not per tenant — two
-- buildings in the same HOA can each have an "A-101".
DROP INDEX IF EXISTS flats_tenant_unit_number_key;
CREATE UNIQUE INDEX flats_building_unit_number_key
    ON flats (building_id, unit_number);

CREATE INDEX flats_building_id_idx ON flats (building_id);

DROP INDEX IF EXISTS flats_building_id_idx;
DROP INDEX IF EXISTS flats_building_unit_number_key;
CREATE UNIQUE INDEX flats_tenant_unit_number_key ON flats (tenant_id, unit_number);
ALTER TABLE flats DROP COLUMN building_id;

package domain

import (
	"errors"
	"time"

	"github.com/google/uuid"
)

type Building struct {
	ID        uuid.UUID
	TenantID  uuid.UUID
	Name      string
	Address   string
	CreatedAt time.Time
}

var ErrBuildingNotFound = errors.New("building: not found")

type Flat struct {
	ID          uuid.UUID
	TenantID    uuid.UUID
	BuildingID  uuid.UUID // new: every flat now belongs to exactly one building
	UnitNumber  string
	FloorNumber int
	AreaSqft    float64
	Status      FlatStatus
	CreatedAt   time.Time
	UpdatedAt   time.Time
}

package postgres

import (
	"context"
	"errors"

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

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

type BuildingRepository struct {
	pool *pgxpool.Pool
}

func NewBuildingRepository(pool *pgxpool.Pool) *BuildingRepository {
	return &BuildingRepository{pool: pool}
}

func (r *BuildingRepository) Create(ctx context.Context, b domain.Building) (domain.Building, error) {
	const q = `
		INSERT INTO buildings (tenant_id, name, address)
		VALUES ($1, $2, $3)
		RETURNING id, created_at`
	row := r.pool.QueryRow(ctx, q, b.TenantID, b.Name, b.Address)
	if err := row.Scan(&b.ID, &b.CreatedAt); err != nil {
		return domain.Building{}, err
	}
	return b, nil
}

// Exists is used by FlatRepository.Create to validate a building_id before
// attempting the insert, so the FK violation path below is a defensive
// backstop rather than the primary error path.
func (r *BuildingRepository) Exists(ctx context.Context, tenantID, id uuid.UUID) (bool, error) {
	const q = `SELECT 1 FROM buildings WHERE tenant_id = $1 AND id = $2`
	var exists int
	err := r.pool.QueryRow(ctx, q, tenantID, id).Scan(&exists)
	if errors.Is(err, pgx.ErrNoRows) {
		return false, nil
	}
	return err == nil, err
}

type BuildingChecker interface {
	Exists(ctx context.Context, tenantID, id uuid.UUID) (bool, error)
}

type FlatService struct {
	repo      FlatRepository
	buildings BuildingChecker
}

func NewFlatService(repo FlatRepository, buildings BuildingChecker) *FlatService {
	return &FlatService{repo: repo, buildings: buildings}
}

func (s *FlatService) Create(ctx context.Context, f domain.Flat) (domain.Flat, error) {
	ok, err := s.buildings.Exists(ctx, f.TenantID, f.BuildingID)
	if err != nil {
		return domain.Flat{}, err
	}
	if !ok {
		return domain.Flat{}, domain.ErrBuildingNotFound
	}

	f.Status = domain.FlatStatusVacant
	return s.repo.Create(ctx, f)
}