-- Active Views for Soft Delete Implementation
-- These views filter out soft-deleted records and their deleted ancestors
-- Use these views for reporting, analytics, and read-only operations

-- Active Buildings: Only buildings that are not soft-deleted
CREATE OR REPLACE VIEW active_buildings AS
SELECT *
FROM buildings
WHERE deleted_at IS NULL;

-- Active Apartments: Only apartments that are not soft-deleted AND their building is not soft-deleted
CREATE OR REPLACE VIEW active_apartments AS
SELECT a.*
FROM apartments a
JOIN buildings b ON a.building_id = b.id
WHERE a.deleted_at IS NULL
  AND b.deleted_at IS NULL;

-- Active Rooms: Only rooms that are not soft-deleted AND their apartment/building are not soft-deleted
CREATE OR REPLACE VIEW active_rooms AS
SELECT r.*
FROM rooms r
JOIN apartments a ON r.apartment_id = a.id
JOIN buildings b ON a.building_id = b.id
WHERE r.deleted_at IS NULL
  AND a.deleted_at IS NULL
  AND b.deleted_at IS NULL;

-- Active Beds: Only beds that are not soft-deleted AND their room/apartment/building are not soft-deleted
CREATE OR REPLACE VIEW active_beds AS
SELECT b.*
FROM beds b
JOIN rooms r ON b.room_id = r.id
JOIN apartments a ON r.apartment_id = a.id
JOIN buildings bu ON a.building_id = bu.id
WHERE b.deleted_at IS NULL
  AND r.deleted_at IS NULL
  AND a.deleted_at IS NULL
  AND bu.deleted_at IS NULL;

-- Active Residents: Only residents that are not soft-deleted
CREATE OR REPLACE VIEW active_residents AS
SELECT *
FROM residents
WHERE deleted_at IS NULL;

-- Note: These views are for READ-ONLY operations (reporting, analytics, dashboard)
-- For CRUD operations, use the actual tables with soft delete logic in the application layer
