-- =============================================================================
-- BeKind Backend — Resident and Facility Schema
-- Tables: residents, resident_stays, resident_events, resident_documents, buildings, apartments, rooms, beds
-- Depends on: 001-auth-schema.sql (users, update_updated_at_column)
--             002-file-schema.sql (files)
--
-- Architecture:
-- - Single source of truth for occupancy: resident_stays (NO bed_id in residents)
-- - Residents only store personal info (no residence info)
-- - Hierarchy traversal via joins: resident_stays -> bed -> room -> apartment -> building
-- - is_active derived from move_out_at IS NULL
--
-- APPLICATION LAYER VALIDATION (NOT enforced by SQL):
-- - Entity hierarchy consistency: Do not activate child if parent is inactive
--   Example: Cannot activate bed if room is inactive
--   Reason: SQL triggers for cascade validation are complex, prone to deadlock, and hard to maintain
--   Solution: Enforce at service/application layer before database operations
--
-- AUDIT FIELD CONSISTENCY (NOT enforced by SQL):
-- - updated_by field can become stale if not properly set during updates
--   Example: UPDATE resident_stays SET move_out_at = now() (forgetting to set updated_by)
--   Solution: Use one of the following approaches:
--     1. Session variable: SET LOCAL current_user_id = ?; (trigger auto-injects)
--     2. Mandatory service layer abstraction: All updates go through repository that sets updated_by
--     3. Trigger: Auto-inject actor id from session context
-- =============================================================================

-- =============================================================================
-- EXTENSIONS (must be created before tables that use them)
-- =============================================================================

-- Enable UUID extension if not already enabled (required for uuid_generate_v4())
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Enable btree_gist for exclusion constraints (required for EXCLUDE USING gist)
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- Enable pg_trgm for efficient text search (required for gin_trgm_ops)
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- =============================================================================
-- ENUM TYPES
-- =============================================================================

-- Resident status (personal/business status, NOT occupancy state)
-- Occupancy state is derived from resident_stays.move_out_at IS NULL
-- Semantics:
-- - active: Đang hoạt động, có thể ở
-- - inactive: Ngừng hoạt động (bao gồm: deceased, banned, suspended, moved out permanently)
--   Use notes field to store reason/context when needed
DO $$ BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'resident_status') THEN
        CREATE TYPE resident_status AS ENUM ('active', 'inactive');
    END IF;
END $$;

-- Document type
DO $$ BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'document_type') THEN
        CREATE TYPE document_type AS ENUM (
            'id_card',
            'passport',
            'household_registration',
            'temporary_residence',
            'contract',
            'other'
        );
    END IF;
END $$;

-- Common entity status (DRY - used by buildings, apartments, rooms, beds)
DO $$ BEGIN
    CREATE TYPE entity_status AS ENUM ('active', 'inactive');
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- Resident event types
DO $$ BEGIN
    CREATE TYPE resident_event_type AS ENUM (
        'created',
        'updated',
        'moved_in',
        'moved_out',
        'room_transfer',
        'status_changed',
        'deleted'
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- Resident stay types (for future business needs)
DO $$ BEGIN
    CREATE TYPE resident_stay_type AS ENUM (
        'permanent',
        'temporary',
        'maintenance',
        'quarantine',
        'guest'
    );
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- =============================================================================
-- TABLE: buildings (Tòa nhà)
-- =============================================================================

CREATE TABLE IF NOT EXISTS buildings (
    -- Primary
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Building Information
    name VARCHAR(200) NOT NULL,
    code VARCHAR(50) NOT NULL,
    address TEXT,
    description TEXT,

    -- Status
    status entity_status NOT NULL DEFAULT 'active',

    -- Soft delete
    deleted_at TIMESTAMP WITH TIME ZONE,
    deleted_by UUID REFERENCES users(id) ON DELETE SET NULL,

    -- Audit fields
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Constraints
    CONSTRAINT buildings_name_not_empty CHECK (length(trim(name)) > 0),
    CONSTRAINT buildings_code_not_empty CHECK (length(trim(code)) > 0),
    CONSTRAINT buildings_soft_delete_consistency CHECK (
        (deleted_at IS NULL AND deleted_by IS NULL)
        OR
        (deleted_at IS NOT NULL)
    )
);

-- =============================================================================
-- TABLE: apartments (Căn hộ)
-- =============================================================================

CREATE TABLE IF NOT EXISTS apartments (
    -- Primary
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Building Reference
    building_id UUID NOT NULL REFERENCES buildings(id) ON DELETE RESTRICT,

    -- Apartment Information
    name VARCHAR(200) NOT NULL,
    code VARCHAR(50) NOT NULL,
    floor INTEGER,

    -- Status
    status entity_status NOT NULL DEFAULT 'active',

    -- Soft delete
    deleted_at TIMESTAMP WITH TIME ZONE,
    deleted_by UUID REFERENCES users(id) ON DELETE SET NULL,

    -- Audit fields
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Constraints
    CONSTRAINT apartments_name_not_empty CHECK (length(trim(name)) > 0),
    CONSTRAINT apartments_code_not_empty CHECK (length(trim(code)) > 0),
    CONSTRAINT apartments_floor_range CHECK (floor IS NULL OR floor BETWEEN -10 AND 300),
    CONSTRAINT apartments_soft_delete_consistency CHECK (
        (deleted_at IS NULL AND deleted_by IS NULL)
        OR
        (deleted_at IS NOT NULL)
    )
);

-- =============================================================================
-- TABLE: rooms (Phòng)
-- =============================================================================

CREATE TABLE IF NOT EXISTS rooms (
    -- Primary
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Apartment Reference
    apartment_id UUID NOT NULL REFERENCES apartments(id) ON DELETE RESTRICT,

    -- Room Information
    name VARCHAR(200) NOT NULL,
    code VARCHAR(50) NOT NULL,
    floor INTEGER,

    -- Capacity (derived from beds count, not stored)
    -- max_capacity removed - derive from: SELECT COUNT(*) FROM beds WHERE room_id = ? AND deleted_at IS NULL

    -- Status
    status entity_status NOT NULL DEFAULT 'active',

    -- Soft delete
    deleted_at TIMESTAMP WITH TIME ZONE,
    deleted_by UUID REFERENCES users(id) ON DELETE SET NULL,

    -- Audit fields
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Constraints
    CONSTRAINT rooms_name_not_empty CHECK (length(trim(name)) > 0),
    CONSTRAINT rooms_code_not_empty CHECK (length(trim(code)) > 0),
    CONSTRAINT rooms_floor_range CHECK (floor IS NULL OR floor BETWEEN -10 AND 300),
    CONSTRAINT rooms_soft_delete_consistency CHECK (
        (deleted_at IS NULL AND deleted_by IS NULL)
        OR
        (deleted_at IS NOT NULL)
    )
);

-- =============================================================================
-- TABLE: beds (Giường)
-- =============================================================================

CREATE TABLE IF NOT EXISTS beds (
    -- Primary
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Room Reference
    room_id UUID NOT NULL REFERENCES rooms(id) ON DELETE RESTRICT,

    -- Bed Information
    name VARCHAR(200) NOT NULL,
    code VARCHAR(50) NOT NULL,

    -- Status
    status entity_status NOT NULL DEFAULT 'active',

    -- Soft delete
    deleted_at TIMESTAMP WITH TIME ZONE,
    deleted_by UUID REFERENCES users(id) ON DELETE SET NULL,

    -- Audit fields
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Constraints
    CONSTRAINT beds_name_not_empty CHECK (length(trim(name)) > 0),
    CONSTRAINT beds_code_not_empty CHECK (length(trim(code)) > 0),
    CONSTRAINT beds_soft_delete_consistency CHECK (
        (deleted_at IS NULL AND deleted_by IS NULL)
        OR
        (deleted_at IS NOT NULL)
    )
);

-- =============================================================================
-- TABLE: residents
-- =============================================================================

CREATE TABLE IF NOT EXISTS residents (
    -- Primary
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Personal Information
    full_name VARCHAR(200) NOT NULL,
    email VARCHAR(255),
    phone VARCHAR(25),
    id_card VARCHAR(50),
    date_of_birth DATE,

    -- Searchable normalized fields (for case-insensitive search and deduplication)
    full_name_normalized VARCHAR(200) GENERATED ALWAYS AS (lower(trim(full_name))) STORED,
    email_normalized VARCHAR(255) GENERATED ALWAYS AS (lower(trim(email))) STORED,
    phone_normalized VARCHAR(25) GENERATED ALWAYS AS (regexp_replace(trim(phone), '[^0-9]', '', 'g')) STORED,
    id_card_normalized VARCHAR(50) GENERATED ALWAYS AS (upper(trim(id_card))) STORED,

    -- Emergency Contact
    emergency_contact_name VARCHAR(200),
    emergency_contact_phone VARCHAR(25),
    emergency_contact_relationship VARCHAR(100),

    -- Status (personal status: active, inactive. Occupancy derived from resident_stays)
    status resident_status NOT NULL DEFAULT 'active',

    -- Optimistic locking
    version INTEGER NOT NULL DEFAULT 1,

    -- Soft delete
    deleted_at TIMESTAMP WITH TIME ZONE,
    deleted_by UUID REFERENCES users(id) ON DELETE SET NULL,

    -- Audit
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by UUID REFERENCES users(id) ON DELETE SET NULL,

    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Constraints
    CONSTRAINT residents_full_name_not_empty CHECK (length(trim(full_name)) > 0),
    CONSTRAINT residents_version_positive CHECK (version > 0),
    CONSTRAINT residents_email_format CHECK (email IS NULL OR email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    CONSTRAINT residents_phone_format CHECK (phone IS NULL OR phone ~ '^\+?[0-9\s\-]{8,20}$'),
    CONSTRAINT residents_soft_delete_consistency CHECK (
        (deleted_at IS NULL AND deleted_by IS NULL)
        OR
        (deleted_at IS NOT NULL)
    )
);

-- =============================================================================
-- TABLE: resident_documents
-- =============================================================================

CREATE TABLE IF NOT EXISTS resident_documents (
    -- Primary
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Links
    resident_id UUID NOT NULL REFERENCES residents(id) ON DELETE RESTRICT,
    file_id UUID NOT NULL REFERENCES files(id) ON DELETE CASCADE,

    -- Document Info
    document_type document_type NOT NULL,
    document_name VARCHAR(255),
    description TEXT,
    is_primary BOOLEAN DEFAULT FALSE,

    -- Document Metadata
    issued_date DATE,
    expiry_date DATE,
    verified_at TIMESTAMP WITH TIME ZONE,
    verified_by UUID REFERENCES users(id) ON DELETE SET NULL,

    -- Audit
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by UUID REFERENCES users(id) ON DELETE SET NULL,

    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Soft delete
    deleted_at TIMESTAMP WITH TIME ZONE,
    deleted_by UUID REFERENCES users(id) ON DELETE SET NULL,

    -- Constraints
    CONSTRAINT resident_documents_expiry_valid CHECK (
        expiry_date IS NULL
        OR issued_date IS NULL
        OR expiry_date >= issued_date
    ),
    CONSTRAINT resident_documents_verification_consistency CHECK (
        (verified_at IS NULL AND verified_by IS NULL)
        OR
        (verified_at IS NOT NULL AND verified_by IS NOT NULL)
    ),
    CONSTRAINT resident_documents_soft_delete_consistency CHECK (
        (deleted_at IS NULL AND deleted_by IS NULL)
        OR
        (deleted_at IS NOT NULL)
    )
);

-- =============================================================================
-- TABLE: resident_stays (Occupancy Management - Single Source of Truth)
-- History is immutable - no soft delete (occupancy ledger must be preserved)
--
-- IMMUTABILITY PATTERN (append-mostly immutable):
-- - Immutable fields: resident_id, bed_id, move_in_at (enforced by trigger)
-- - Mutable fields: move_out_at, move_out_reason, move_out_notes (checkout only)
-- - When closing a stay, move_out_at = now() technically mutates history - this is acceptable
--
-- CRITICAL TRANSACTION PATTERNS:
--
-- 1. Move resident to new location:
--    BEGIN TRANSACTION;
--      -- Lock active stay to prevent race conditions
--      SELECT id FROM resident_stays WHERE resident_id = ? AND move_out_at IS NULL FOR UPDATE;
--
--      -- Close current stay
--      UPDATE resident_stays SET move_out_at = now(), move_out_reason = ?, updated_by = ?
--      WHERE resident_id = ? AND move_out_at IS NULL;
--
--      -- Insert new stay
--      INSERT INTO resident_stays (resident_id, bed_id, move_in_at, created_by, updated_by)
--      VALUES (?, ?, now(), ?, ?);
--
--      -- Log events
--      INSERT INTO resident_events (resident_id, event_type, payload, correlation_id, created_by)
--      VALUES (?, 'room_transfer', ?, ?, ?);
--    COMMIT;
--
-- 2. Check out resident:
--    UPDATE resident_stays SET move_out_at = now(), move_out_reason = ?, move_out_notes = ?
--    WHERE resident_id = ? AND move_out_at IS NULL;
--
-- TRANSACTION ISOLATION RECOMMENDATIONS:
-- - Use READ COMMITTED isolation level (default in PostgreSQL)
-- - DO NOT use SERIALIZABLE - exclusion constraints and unique indexes already protect against overlaps
-- - DEFERRABLE constraints allow complex transfer operations to succeed within a transaction
-- - SELECT FOR UPDATE prevents race conditions during resident transfers
-- =============================================================================

CREATE TABLE IF NOT EXISTS resident_stays (
    -- Primary
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Links
    resident_id UUID NOT NULL REFERENCES residents(id) ON DELETE RESTRICT,
    bed_id UUID NOT NULL REFERENCES beds(id) ON DELETE RESTRICT,

    -- Stay Information
    move_in_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    move_out_at TIMESTAMP WITH TIME ZONE,

    -- Generated occupancy period (inclusive start, exclusive end)
    occupancy_period tstzrange GENERATED ALWAYS AS (
        tstzrange(move_in_at, COALESCE(move_out_at, 'infinity'), '[)')
    ) STORED,

    -- Reason for move out
    move_out_reason VARCHAR(255),
    move_out_notes TEXT,

    -- Stay type (for future business needs: permanent, temporary, maintenance, quarantine, guest)
    stay_type resident_stay_type NOT NULL DEFAULT 'permanent',

    -- Audit
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by UUID REFERENCES users(id) ON DELETE SET NULL,

    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Constraints
    CONSTRAINT resident_stays_move_out_after_move_in CHECK (
        move_out_at IS NULL OR move_out_at >= move_in_at
    ),
    CONSTRAINT resident_stays_move_out_consistency CHECK (
        move_out_at IS NOT NULL
        OR (
            move_out_reason IS NULL
            AND move_out_notes IS NULL
        )
    ),
    CONSTRAINT resident_stays_move_out_reason_required CHECK (
        move_out_at IS NULL
        OR length(trim(move_out_reason)) > 0
    )
);

-- =============================================================================
-- TABLE: resident_events (Audit/Lifecycle Tracking)
-- =============================================================================

CREATE TABLE IF NOT EXISTS resident_events (
    -- Primary
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    -- Links
    -- Note: CASCADE used because resident_events is audit log. Since residents are soft deleted,
    -- hard delete should cascade to preserve referential integrity if ever needed.
    resident_id UUID NOT NULL REFERENCES residents(id) ON DELETE CASCADE,

    -- Event Information
    event_type resident_event_type NOT NULL,
    -- Payload structure convention (ENFORCE at application layer):
    -- {
    --   "before": {},           // Previous state (for update events)
    --   "after": {},            // New state
    --   "metadata": {},         // Additional context (timestamps, IDs, etc.)
    --   "reason": ""            // Business reason for the change
    -- }
    -- Application MUST validate payload schema to prevent drift
    -- Use jsonb_build_object() to ensure structure consistency
    payload JSONB,

    -- Correlation for tracking related events
    correlation_id UUID,

    -- Audit
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,

    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Indexes for event queries
    CONSTRAINT resident_events_correlation_optional CHECK (
        correlation_id IS NULL OR event_type IN ('moved_in', 'moved_out', 'room_transfer')
    ),
    -- Partial payload validation (minimum contract)
    -- Application layer enforces full schema, but DB validates structure exists
    CONSTRAINT resident_events_payload_structure CHECK (
        jsonb_typeof(payload) = 'object'
        AND (payload ? 'before' OR payload ? 'after' OR payload ? 'metadata' OR payload ? 'reason')
    )
);

-- =============================================================================
-- INDEXES
-- =============================================================================

-- Buildings indexes
CREATE INDEX IF NOT EXISTS idx_buildings_code ON buildings(code) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_buildings_status ON buildings(status) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_buildings_deleted_at ON buildings(deleted_at);
CREATE UNIQUE INDEX IF NOT EXISTS uq_buildings_code ON buildings(code) WHERE deleted_at IS NULL;

-- Apartments indexes
CREATE INDEX IF NOT EXISTS idx_apartments_building_id ON apartments(building_id) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_apartments_code ON apartments(code) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_apartments_status ON apartments(status) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_apartments_deleted_at ON apartments(deleted_at);
CREATE UNIQUE INDEX IF NOT EXISTS uq_apartments_code ON apartments(building_id, code) WHERE deleted_at IS NULL;

-- Rooms indexes
CREATE INDEX IF NOT EXISTS idx_rooms_apartment_id ON rooms(apartment_id) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_rooms_code ON rooms(code) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_rooms_status ON rooms(status) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_rooms_deleted_at ON rooms(deleted_at);
CREATE UNIQUE INDEX IF NOT EXISTS uq_rooms_code ON rooms(apartment_id, code) WHERE deleted_at IS NULL;

-- Beds indexes
CREATE INDEX IF NOT EXISTS idx_beds_room_id ON beds(room_id) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_beds_code ON beds(code) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_beds_status ON beds(status) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_beds_deleted_at ON beds(deleted_at);
CREATE UNIQUE INDEX IF NOT EXISTS uq_beds_code ON beds(room_id, code) WHERE deleted_at IS NULL;

-- Residents indexes
CREATE INDEX IF NOT EXISTS idx_residents_full_name ON residents(full_name);
CREATE INDEX IF NOT EXISTS idx_residents_full_name_trgm ON residents USING gin(full_name gin_trgm_ops);
CREATE INDEX IF NOT EXISTS idx_residents_id_card ON residents(id_card) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_residents_status ON residents(status) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_residents_deleted_at ON residents(deleted_at);
CREATE INDEX IF NOT EXISTS idx_residents_created_at ON residents(created_at);

-- Unique constraints for email, id_card, and phone (partial unique index for soft delete, using normalized columns)
CREATE UNIQUE INDEX IF NOT EXISTS uq_residents_email ON residents(email_normalized) WHERE deleted_at IS NULL AND email_normalized IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS uq_residents_id_card ON residents(id_card_normalized) WHERE deleted_at IS NULL AND id_card_normalized IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS uq_residents_phone ON residents(phone_normalized) WHERE deleted_at IS NULL AND phone_normalized IS NOT NULL AND phone_normalized != '';

-- Resident documents indexes
CREATE INDEX IF NOT EXISTS idx_resident_documents_resident_id ON resident_documents(resident_id) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_resident_documents_file_id ON resident_documents(file_id);
CREATE INDEX IF NOT EXISTS idx_resident_documents_type ON resident_documents(document_type) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_resident_documents_deleted_at ON resident_documents(deleted_at);

-- Unique constraint for primary documents (one primary per resident per document type)
CREATE UNIQUE INDEX IF NOT EXISTS uq_primary_document ON resident_documents(resident_id, document_type) WHERE is_primary = TRUE AND deleted_at IS NULL;

-- Unique constraint for resident-document pairs (partial unique index for soft delete)
CREATE UNIQUE INDEX IF NOT EXISTS uq_resident_document_active ON resident_documents(resident_id, file_id) WHERE deleted_at IS NULL;

-- Resident stays indexes
CREATE INDEX IF NOT EXISTS idx_resident_stays_resident_id ON resident_stays(resident_id);
CREATE INDEX IF NOT EXISTS idx_resident_stays_bed_id ON resident_stays(bed_id);
CREATE INDEX IF NOT EXISTS idx_resident_stays_move_in_at ON resident_stays(move_in_at);
CREATE INDEX IF NOT EXISTS idx_resident_stays_move_out_at ON resident_stays(move_out_at);

-- GiST index for occupancy_period (time-range queries)
CREATE INDEX IF NOT EXISTS idx_resident_stays_occupancy_period ON resident_stays USING gist (occupancy_period);

-- Partial indexes for active occupancy queries (most common pattern)
CREATE INDEX IF NOT EXISTS idx_resident_stays_active_resident_partial ON resident_stays(resident_id) WHERE move_out_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_resident_stays_active_bed_partial ON resident_stays(bed_id) WHERE move_out_at IS NULL;

-- Unique constraint: one active stay per resident (move_out_at IS NULL = active)
CREATE UNIQUE INDEX IF NOT EXISTS uq_resident_stays_active ON resident_stays(resident_id) WHERE move_out_at IS NULL;

-- Commented out: Allow multiple residents per bed
-- Unique constraint: one active stay per bed (occupancy enforcement)
-- CREATE UNIQUE INDEX IF NOT EXISTS uq_bed_occupied ON resident_stays(bed_id) WHERE move_out_at IS NULL;

-- Exclusion constraint: prevent overlapping stays for same resident
-- DEFERRABLE INITIALLY IMMEDIATE allows constraint check to be deferred within transaction
-- Useful for complex transfer operations where timing of UPDATE/INSERT matters
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'exc_resident_stays_no_overlap'
    ) THEN
        ALTER TABLE resident_stays
        ADD CONSTRAINT exc_resident_stays_no_overlap
        EXCLUDE USING gist (
            resident_id WITH =,
            occupancy_period WITH &&
        ) DEFERRABLE INITIALLY IMMEDIATE;
    END IF;
END $$;

-- Commented out: Allow overlapping stays for same bed
-- Exclusion constraint: prevent overlapping stays for same resident
-- DEFERRABLE INITIALLY IMMEDIATE allows constraint check to be deferred within transaction
-- Useful for complex transfer operations where timing of UPDATE/INSERT matters
-- DO $$
-- BEGIN
--     IF NOT EXISTS (
--         SELECT 1
--         FROM pg_constraint
--         WHERE conname = 'exc_bed_stays_no_overlap'
--     ) THEN
--         ALTER TABLE resident_stays
--         ADD CONSTRAINT exc_bed_stays_no_overlap
--         EXCLUDE USING gist (
--             bed_id WITH =,
--             occupancy_period WITH &&
--         ) DEFERRABLE INITIALLY IMMEDIATE;
--     END IF;
-- END $$;

-- Resident events indexes
CREATE INDEX IF NOT EXISTS idx_resident_events_resident_id ON resident_events(resident_id);
CREATE INDEX IF NOT EXISTS idx_resident_events_type ON resident_events(event_type);
CREATE INDEX IF NOT EXISTS idx_resident_events_correlation_id ON resident_events(correlation_id) WHERE correlation_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_resident_events_created_at ON resident_events(created_at);

-- =============================================================================
-- VIEWS
-- =============================================================================

-- View: Active resident stays (current occupancy)
CREATE OR REPLACE VIEW active_resident_stays AS
SELECT *
FROM resident_stays
WHERE move_out_at IS NULL;

-- View: Resident current locations with full hierarchy
CREATE OR REPLACE VIEW resident_current_locations AS
SELECT
    rs.id AS stay_id,
    rs.resident_id,
    r.full_name AS resident_name,
    rs.bed_id,
    b.name AS bed_name,
    b.code AS bed_code,
    b.status AS bed_status,
    rm.id AS room_id,
    rm.name AS room_name,
    rm.code AS room_code,
    rm.status AS room_status,
    a.id AS apartment_id,
    a.name AS apartment_name,
    a.code AS apartment_code,
    a.floor AS apartment_floor,
    a.status AS apartment_status,
    bu.id AS building_id,
    bu.name AS building_name,
    bu.code AS building_code,
    bu.status AS building_status,
    rs.move_in_at,
    rs.occupancy_period
FROM resident_stays rs
JOIN residents r ON r.id = rs.resident_id
JOIN beds b ON b.id = rs.bed_id
JOIN rooms rm ON rm.id = b.room_id
JOIN apartments a ON a.id = rm.apartment_id
JOIN buildings bu ON bu.id = a.building_id
WHERE rs.move_out_at IS NULL
  AND r.deleted_at IS NULL
  AND b.deleted_at IS NULL
  AND rm.deleted_at IS NULL
  AND a.deleted_at IS NULL
  AND bu.deleted_at IS NULL;

-- =============================================================================
-- ACTIVE VIEWS (Soft Delete Filtering Strategy)
-- These views automatically filter out soft-deleted records
-- Use these views instead of base tables to avoid bugs from forgetting WHERE deleted_at IS NULL
--
-- TEMPORARILY DISABLED: Only resident_current_locations is implemented for now
-- Enable other views when needed:
-- - active_buildings, active_apartments, active_rooms, active_beds
-- - active_residents, active_resident_documents
-- - bed_occupancy_stats, room_occupancy_stats
-- =============================================================================

-- CREATE OR REPLACE VIEW active_buildings AS
-- SELECT *
-- FROM buildings
-- WHERE deleted_at IS NULL;
--
-- CREATE OR REPLACE VIEW active_apartments AS
-- SELECT *
-- FROM apartments
-- WHERE deleted_at IS NULL;
--
-- CREATE OR REPLACE VIEW active_rooms AS
-- SELECT *
-- FROM rooms
-- WHERE deleted_at IS NULL;
--
-- CREATE OR REPLACE VIEW active_beds AS
-- SELECT *
-- FROM beds
-- WHERE deleted_at IS NULL;
--
-- CREATE OR REPLACE VIEW active_residents AS
-- SELECT *
-- FROM residents
-- WHERE deleted_at IS NULL;
--
-- CREATE OR REPLACE VIEW active_resident_documents AS
-- SELECT *
-- FROM resident_documents
-- WHERE deleted_at IS NULL;
--
-- =============================================================================
-- OCCUPANCY ANALYTICS VIEWS (TEMPORARILY DISABLED)
-- =============================================================================
--
-- -- View: Bed occupancy stats (shows which beds are currently occupied)
-- CREATE OR REPLACE VIEW bed_occupancy_stats AS
-- SELECT
--     b.id,
--     b.name,
--     b.code,
--     b.status,
--     EXISTS (
--         SELECT 1
--         FROM resident_stays rs
--         WHERE rs.bed_id = b.id
--           AND rs.move_out_at IS NULL
--     ) AS is_occupied,
--     (
--         SELECT rs.resident_id
--         FROM resident_stays rs
--         WHERE rs.bed_id = b.id
--           AND rs.move_out_at IS NULL
--         LIMIT 1
--     ) AS current_resident_id
-- FROM beds b
-- WHERE b.deleted_at IS NULL;
--
-- -- View: Room occupancy stats (shows bed count and occupancy per room)
-- CREATE OR REPLACE VIEW room_occupancy_stats AS
-- SELECT
--     r.id,
--     r.name,
--     r.code,
--     r.status,
--     COUNT(DISTINCT b.id) AS total_beds,
--     COUNT(DISTINCT rs.bed_id) FILTER (
--         WHERE rs.move_out_at IS NULL
--     ) AS occupied_beds,
--     COUNT(DISTINCT b.id) - COUNT(DISTINCT rs.bed_id) FILTER (
--         WHERE rs.move_out_at IS NULL
--     ) AS available_beds,
--     ROUND(
--         (
--             COUNT(DISTINCT rs.bed_id) FILTER (
--                 WHERE rs.move_out_at IS NULL
--             )::numeric / NULLIF(COUNT(DISTINCT b.id), 0)
--         ) * 100,
--         2
--     ) AS occupancy_percentage
-- FROM rooms r
-- LEFT JOIN beds b ON b.room_id = r.id AND b.deleted_at IS NULL
-- LEFT JOIN resident_stays rs ON rs.bed_id = b.id
-- WHERE r.deleted_at IS NULL
-- GROUP BY r.id, r.name, r.code, r.status;

-- =============================================================================
-- TRIGGERS
-- =============================================================================

DROP TRIGGER IF EXISTS trg_40_update_buildings_updated_at ON buildings;
CREATE TRIGGER trg_40_update_buildings_updated_at
    BEFORE UPDATE ON buildings
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS trg_40_update_apartments_updated_at ON apartments;
CREATE TRIGGER trg_40_update_apartments_updated_at
    BEFORE UPDATE ON apartments
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS trg_40_update_rooms_updated_at ON rooms;
CREATE TRIGGER trg_40_update_rooms_updated_at
    BEFORE UPDATE ON rooms
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS trg_40_update_beds_updated_at ON beds;
CREATE TRIGGER trg_40_update_beds_updated_at
    BEFORE UPDATE ON beds
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS trg_40_update_residents_updated_at ON residents;
CREATE TRIGGER trg_40_update_residents_updated_at
    BEFORE UPDATE ON residents
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS trg_40_update_resident_documents_updated_at ON resident_documents;
CREATE TRIGGER trg_40_update_resident_documents_updated_at
    BEFORE UPDATE ON resident_documents
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Function: Prevent mutation of critical fields in resident_stays
CREATE OR REPLACE FUNCTION prevent_resident_stay_mutation()
RETURNS TRIGGER AS $$
BEGIN
    -- Prevent changes to critical immutable fields after insert
    IF (TG_OP = 'UPDATE') THEN
        IF (
            OLD.resident_id IS DISTINCT FROM NEW.resident_id OR
            OLD.bed_id IS DISTINCT FROM NEW.bed_id OR
            OLD.move_in_at IS DISTINCT FROM NEW.move_in_at
        ) THEN
            RAISE EXCEPTION 'Cannot modify resident_id, bed_id, or move_in_at in resident_stays (immutable fields)';
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Function: Validate bed assignment (DB-level critical invariant)
-- Ensures bed is not deleted and is active before assignment
-- This prevents background jobs, direct SQL, admin scripts from bypassing app layer validation
CREATE OR REPLACE FUNCTION validate_bed_assignment()
RETURNS TRIGGER AS $$
DECLARE
    v_bed_deleted_at TIMESTAMP WITH TIME ZONE;
    v_bed_status entity_status;
BEGIN
    -- On INSERT and UPDATE of bed_id, validate bed is assignable
    IF TG_OP = 'INSERT' OR (TG_OP = 'UPDATE' AND OLD.bed_id IS DISTINCT FROM NEW.bed_id) THEN
        SELECT deleted_at, status INTO v_bed_deleted_at, v_bed_status
        FROM beds
        WHERE id = NEW.bed_id;

        IF NOT FOUND THEN
            RAISE EXCEPTION 'Bed % does not exist', NEW.bed_id;
        END IF;

        IF v_bed_deleted_at IS NOT NULL THEN
            RAISE EXCEPTION 'Cannot assign to deleted bed %', NEW.bed_id;
        END IF;

        IF v_bed_status != 'active' THEN
            RAISE EXCEPTION 'Cannot assign to inactive bed % (status: %)', NEW.bed_id, v_bed_status;
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_05_prevent_resident_stay_mutation ON resident_stays;
CREATE TRIGGER trg_05_prevent_resident_stay_mutation
    BEFORE UPDATE ON resident_stays
    FOR EACH ROW EXECUTE FUNCTION prevent_resident_stay_mutation();

DROP TRIGGER IF EXISTS trg_04_validate_bed_assignment ON resident_stays;
CREATE TRIGGER trg_04_validate_bed_assignment
    BEFORE INSERT OR UPDATE ON resident_stays
    FOR EACH ROW EXECUTE FUNCTION validate_bed_assignment();

DROP TRIGGER IF EXISTS trg_40_update_resident_stays_updated_at ON resident_stays;
CREATE TRIGGER trg_40_update_resident_stays_updated_at
    BEFORE UPDATE ON resident_stays
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- =============================================================================
-- STORED PROCEDURES AND FUNCTIONS (Production-Grade Improvements)
-- =============================================================================

-- =============================================================================
-- Function: increment_version (Optimistic Locking)
-- Automatically increments version field on UPDATE
-- Application usage: UPDATE residents SET ... WHERE id = ? AND version = ?;
-- If affected rows = 0 => conflict detected
-- =============================================================================
CREATE OR REPLACE FUNCTION increment_version()
RETURNS TRIGGER AS $$
BEGIN
    NEW.version = OLD.version + 1;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_30_increment_resident_version ON residents;
CREATE TRIGGER trg_30_increment_resident_version
    BEFORE UPDATE ON residents
    FOR EACH ROW EXECUTE FUNCTION increment_version();

-- =============================================================================
-- Function: inject_created_by (Audit Integrity)
-- Automatically injects created_by from session variable on INSERT
-- Application usage: SET LOCAL app.current_user_id = 'uuid';
-- =============================================================================
CREATE OR REPLACE FUNCTION inject_created_by()
RETURNS TRIGGER AS $$
BEGIN
    NEW.created_by := current_setting('app.current_user_id', true)::uuid;
    RETURN NEW;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- =============================================================================
-- Function: inject_updated_by (Audit Integrity)
-- Automatically injects updated_by from session variable
-- Application usage: SET LOCAL app.current_user_id = 'uuid';
-- =============================================================================
CREATE OR REPLACE FUNCTION inject_updated_by()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_by := current_setting('app.current_user_id', true)::uuid;
    RETURN NEW;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply audit integrity triggers to all tables with created_by and updated_by
DROP TRIGGER IF EXISTS trg_10_inject_buildings_created_by ON buildings;
CREATE TRIGGER trg_10_inject_buildings_created_by
    BEFORE INSERT ON buildings
    FOR EACH ROW EXECUTE FUNCTION inject_created_by();

DROP TRIGGER IF EXISTS trg_20_inject_buildings_updated_by ON buildings;
CREATE TRIGGER trg_20_inject_buildings_updated_by
    BEFORE UPDATE ON buildings
    FOR EACH ROW EXECUTE FUNCTION inject_updated_by();

DROP TRIGGER IF EXISTS trg_10_inject_apartments_created_by ON apartments;
CREATE TRIGGER trg_10_inject_apartments_created_by
    BEFORE INSERT ON apartments
    FOR EACH ROW EXECUTE FUNCTION inject_created_by();

DROP TRIGGER IF EXISTS trg_20_inject_apartments_updated_by ON apartments;
CREATE TRIGGER trg_20_inject_apartments_updated_by
    BEFORE UPDATE ON apartments
    FOR EACH ROW EXECUTE FUNCTION inject_updated_by();

DROP TRIGGER IF EXISTS trg_10_inject_rooms_created_by ON rooms;
CREATE TRIGGER trg_10_inject_rooms_created_by
    BEFORE INSERT ON rooms
    FOR EACH ROW EXECUTE FUNCTION inject_created_by();

DROP TRIGGER IF EXISTS trg_20_inject_rooms_updated_by ON rooms;
CREATE TRIGGER trg_20_inject_rooms_updated_by
    BEFORE UPDATE ON rooms
    FOR EACH ROW EXECUTE FUNCTION inject_updated_by();

DROP TRIGGER IF EXISTS trg_10_inject_beds_created_by ON beds;
CREATE TRIGGER trg_10_inject_beds_created_by
    BEFORE INSERT ON beds
    FOR EACH ROW EXECUTE FUNCTION inject_created_by();

DROP TRIGGER IF EXISTS trg_20_inject_beds_updated_by ON beds;
CREATE TRIGGER trg_20_inject_beds_updated_by
    BEFORE UPDATE ON beds
    FOR EACH ROW EXECUTE FUNCTION inject_updated_by();

DROP TRIGGER IF EXISTS trg_10_inject_residents_created_by ON residents;
CREATE TRIGGER trg_10_inject_residents_created_by
    BEFORE INSERT ON residents
    FOR EACH ROW EXECUTE FUNCTION inject_created_by();

DROP TRIGGER IF EXISTS trg_20_inject_residents_updated_by ON residents;
CREATE TRIGGER trg_20_inject_residents_updated_by
    BEFORE UPDATE ON residents
    FOR EACH ROW EXECUTE FUNCTION inject_updated_by();

DROP TRIGGER IF EXISTS trg_10_inject_resident_stays_created_by ON resident_stays;
CREATE TRIGGER trg_10_inject_resident_stays_created_by
    BEFORE INSERT ON resident_stays
    FOR EACH ROW EXECUTE FUNCTION inject_created_by();

DROP TRIGGER IF EXISTS trg_20_inject_resident_stays_updated_by ON resident_stays;
CREATE TRIGGER trg_20_inject_resident_stays_updated_by
    BEFORE UPDATE ON resident_stays
    FOR EACH ROW EXECUTE FUNCTION inject_updated_by();

-- =============================================================================
-- Procedure: transfer_resident (Transaction Wrapper)
-- Centralized logic for resident room transfer with audit trail
-- Usage: CALL transfer_resident(resident_id, new_bed_id, reason, actor_id);
-- =============================================================================
CREATE OR REPLACE PROCEDURE transfer_resident(
    p_resident_id UUID,
    p_new_bed_id UUID,
    p_reason VARCHAR,
    p_actor_id UUID
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_correlation_id UUID := uuid_generate_v4();
    v_old_bed_id UUID;
BEGIN
    -- Lock current active stay to prevent race conditions and capture old bed_id
    SELECT bed_id INTO v_old_bed_id
    FROM resident_stays
    WHERE resident_id = p_resident_id
      AND move_out_at IS NULL
    FOR UPDATE;

    IF v_old_bed_id IS NULL THEN
        RAISE EXCEPTION 'No active stay found for resident %', p_resident_id;
    END IF;

    -- Prevent self-transfer (same bed)
    IF v_old_bed_id = p_new_bed_id THEN
        RAISE EXCEPTION 'Cannot transfer resident to the same bed %', p_new_bed_id;
    END IF;

    -- Close old stay
    UPDATE resident_stays
    SET
        move_out_at = CURRENT_TIMESTAMP,
        move_out_reason = p_reason,
        updated_by = p_actor_id
    WHERE resident_id = p_resident_id
      AND move_out_at IS NULL;

    -- Create new stay
    INSERT INTO resident_stays (
        resident_id,
        bed_id,
        move_in_at,
        created_by,
        updated_by
    ) VALUES (
        p_resident_id,
        p_new_bed_id,
        CURRENT_TIMESTAMP,
        p_actor_id,
        p_actor_id
    );

    -- Event log with standardized payload (using captured v_old_bed_id)
    INSERT INTO resident_events (
        resident_id,
        event_type,
        payload,
        correlation_id,
        created_by
    ) VALUES (
        p_resident_id,
        'room_transfer',
        jsonb_build_object(
            'before', jsonb_build_object('bed_id', v_old_bed_id),
            'after', jsonb_build_object('bed_id', p_new_bed_id),
            'metadata', jsonb_build_object('old_bed_closed_at', CURRENT_TIMESTAMP),
            'reason', p_reason
        ),
        v_correlation_id,
        p_actor_id
    );
END;
$$;

-- =============================================================================
-- Procedure: check_out_resident (Transaction Wrapper)
-- Centralized logic for resident checkout with audit trail
-- Usage: CALL check_out_resident(resident_id, reason, notes, actor_id);
-- =============================================================================
CREATE OR REPLACE PROCEDURE check_out_resident(
    p_resident_id UUID,
    p_reason VARCHAR,
    p_notes TEXT,
    p_actor_id UUID
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_correlation_id UUID := uuid_generate_v4();
    v_bed_id UUID;
BEGIN
    -- Lock current active stay
    SELECT bed_id INTO v_bed_id
    FROM resident_stays
    WHERE resident_id = p_resident_id
      AND move_out_at IS NULL
    FOR UPDATE;

    IF v_bed_id IS NULL THEN
        RAISE EXCEPTION 'No active stay found for resident %', p_resident_id;
    END IF;

    -- Close stay
    UPDATE resident_stays
    SET
        move_out_at = CURRENT_TIMESTAMP,
        move_out_reason = p_reason,
        move_out_notes = p_notes,
        updated_by = p_actor_id
    WHERE resident_id = p_resident_id
      AND move_out_at IS NULL;

    -- Event log
    INSERT INTO resident_events (
        resident_id,
        event_type,
        payload,
        correlation_id,
        created_by
    ) VALUES (
        p_resident_id,
        'moved_out',
        jsonb_build_object(
            'before', jsonb_build_object('bed_id', v_bed_id),
            'after', jsonb_build_object('bed_id', NULL),
            'metadata', jsonb_build_object('checkout_at', CURRENT_TIMESTAMP),
            'reason', p_reason
        ),
        v_correlation_id,
        p_actor_id
    );
END;
$$;

-- =============================================================================
-- PRODUCTION-GRADE DOCUMENTATION AND OPERATIONAL GUIDELINES
-- =============================================================================

-- =============================================================================
-- PARTITION STRATEGY FOR resident_events
-- =============================================================================
-- resident_events will grow very large over time. Implement partitioning by month:
--
-- CREATE TABLE resident_events (
--     -- existing columns
-- ) PARTITION BY RANGE (created_at);
--
-- CREATE TABLE resident_events_2026_01 PARTITION OF resident_events
--     FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
--
-- CREATE TABLE resident_events_2026_02 PARTITION OF resident_events
--     FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
--
-- Benefits:
-- - Faster queries on recent data (partition pruning)
-- - Easier maintenance (DROP old partitions instead of DELETE)
-- - Reduced index bloat
-- - Better vacuum performance
--
-- Implementation: Add partitioning in migration 010+ when table size > 10M rows
-- =============================================================================

-- =============================================================================
-- ARCHIVAL STRATEGY FOR resident_stays
-- =============================================================================
-- resident_stays is an immutable ledger. After several years, it will be large.
--
-- Options:
-- 1. Partition by year:
--    CREATE TABLE resident_stays (...) PARTITION BY RANGE (move_in_at);
--    CREATE TABLE resident_stays_2026 PARTITION OF resident_stays
--        FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
--
-- 2. Archive table for historical data:
--    CREATE TABLE resident_stays_archive (LIKE resident_stays INCLUDING ALL);
--    INSERT INTO resident_stays_archive SELECT * FROM resident_stays WHERE move_out_at < CURRENT_DATE - INTERVAL '5 years';
--    DELETE FROM resident_stays WHERE move_out_at < CURRENT_DATE - INTERVAL '5 years';
--
-- Recommendation: Partition by year after 3 years of production data
-- =============================================================================

-- =============================================================================
-- CONCURRENCY RETRY POLICY FOR BED TRANSFER
-- =============================================================================
-- Race condition: Two transactions attempt to assign to the same bed simultaneously.
-- The unique constraint (uq_bed_occupied) protects against this, but application needs retry logic.
--
-- Application handling:
-- try {
--     CALL transfer_resident(resident_id, new_bed_id, reason, actor_id);
-- } catch (PostgresException e) {
--     if (e.getSQLState().equals("23505")) { // unique_violation
--         // duplicate key value violates unique constraint uq_bed_occupied
--         return business_error("Bed is already occupied");
--     }
--     if (e.getSQLState().equals("40001")) { // serialization_failure
--         // retry with exponential backoff
--         retry_with_backoff();
--     }
--     throw e;
-- }
--
-- Do NOT suppress these errors - they indicate genuine concurrency conflicts
-- =============================================================================

-- =============================================================================
-- PERMISSION BOUNDARY DESIGN
-- =============================================================================
-- Define clear role-based access control (RBAC) at application layer:
--
-- | Role     | Permissions                                                                 |
-- | -------- | --------------------------------------------------------------------------- |
-- | admin    | Full access: CRUD on all entities, move residents, delete, view documents   |
-- | staff    | Move residents, view all data, create/update residents, view documents       |
-- | viewer   | Read-only access to all entities and documents                               |
-- | auditor  | Read-only access to resident_events, resident_stays, audit trail             |
--
-- Critical operations requiring explicit permission:
-- - move resident (room_transfer): staff, admin
-- - delete resident (soft delete): admin only
-- - view sensitive documents: staff, admin, auditor
-- - modify resident_stays: admin only (use stored procedures)
-- =============================================================================

-- =============================================================================
-- RETENTION POLICY
-- =============================================================================
-- Define data retention periods for compliance and storage optimization:
--
-- | Data Type             | Retention Period | Action                     |
-- | --------------------- | ---------------- | -------------------------- |
-- | resident_events       | 7 years          | Archive after 7 years       |
-- | resident_documents    | Permanent        | Never delete                |
-- | resident_stays        | Permanent        | Archive after 5 years       |
-- | deleted residents     | 5 years          | Hard delete after 5 years   |
-- | audit logs            | 7 years          | Archive after 7 years       |
--
-- Implementation: Create scheduled jobs (cron/pg_cron) to handle archival
-- =============================================================================

-- =============================================================================
-- FILE INTEGRITY VALIDATION
-- =============================================================================
-- resident_documents.file_id references files table. Validate at application layer:
--
-- Required validations before upload:
-- - MIME type whitelist (application/pdf, image/jpeg, image/png)
-- - Max file size (e.g., 10MB)
-- - Antivirus scan (if available)
-- - Checksum validation (SHA-256)
--
-- Files table should include:
-- - mime_type VARCHAR(100) NOT NULL
-- - file_size BIGINT NOT NULL
-- - checksum VARCHAR(64)
-- - is_scanned BOOLEAN DEFAULT FALSE
-- - scan_result VARCHAR(50)
--
-- Application MUST validate these before inserting into resident_documents
-- =============================================================================

-- =============================================================================
-- TIMEZONE CONVENTION
-- =============================================================================
-- All timestamps MUST be stored in UTC in the database.
--
-- Database layer:
-- - All TIMESTAMP WITH TIME ZONE columns store UTC internally
-- - Use CURRENT_TIMESTAMP (which is UTC-aware)
-- - Never use LOCALTIMESTAMP (session-local time)
--
-- Application layer:
-- - Convert to/from user's local timezone at presentation layer
-- - Store UTC timestamps
-- - Use timezone-aware datetime libraries (moment-timezone, date-fns-tz)
--
-- Reporting:
-- - Always specify timezone in report headers
-- - Use UTC for cross-timezone reporting
-- - Convert to local timezone for user-facing reports
-- =============================================================================

-- =============================================================================
-- NAMING CONVENTION CONSISTENCY
-- =============================================================================
-- Standardize naming across all database objects:
--
-- Tables: plural, snake_case (residents, resident_stays, buildings)
-- Columns: snake_case (full_name, move_in_at, created_by)
--
-- Indexes:
-- - idx_<table>_<columns> (regular indexes)
-- - uq_<table>_<columns> (unique constraints)
-- - exc_<table>_<rule> (exclusion constraints)
-- - fk_<table>_<reference> (foreign keys - if named)
-- - chk_<table>_<rule> (check constraints - if named)
--
-- Examples:
-- - idx_residents_full_name
-- - uq_residents_email
-- - exc_resident_stays_no_overlap
-- - chk_residents_email_format
--
-- Views: snake_case, descriptive (active_residents, bed_occupancy_stats)
-- Functions: snake_case (increment_version, inject_updated_by)
-- Procedures: snake_case (transfer_resident, check_out_resident)
-- Triggers: snake_case (increment_resident_version)
-- =============================================================================

-- =============================================================================
-- TABLE AND COLUMN COMMENTS (Metadata for Documentation)
-- =============================================================================
-- Add comments to improve discoverability and auto-documentation:
--
COMMENT ON TABLE residents IS 'Resident master data - personal information only. Occupancy state derived from resident_stays.';
COMMENT ON TABLE resident_stays IS 'Occupancy ledger - single source of truth for resident locations. Immutable history pattern.';
COMMENT ON TABLE resident_events IS 'Audit trail for resident lifecycle events. Standardized payload structure enforced at application layer.';
COMMENT ON TABLE buildings IS 'Building entity - top level of facility hierarchy.';
COMMENT ON TABLE apartments IS 'Apartment entity - belongs to building, contains rooms.';
COMMENT ON TABLE rooms IS 'Room entity - belongs to apartment, contains beds.';
COMMENT ON TABLE beds IS 'Bed entity - belongs to room, assigned to one resident at a time.';
COMMENT ON TABLE resident_documents IS 'Document storage for residents - links to files table with metadata.';
--
COMMENT ON COLUMN resident_stays.occupancy_period IS 'Generated tstzrange column for time-range queries. Inclusive start, exclusive end.';
COMMENT ON COLUMN resident_stays.move_out_reason IS 'Required when move_out_at is set. Enforced by CHECK constraint.';
COMMENT ON COLUMN residents.version IS 'Optimistic locking version. Auto-incremented by trigger on UPDATE.';
COMMENT ON COLUMN residents.full_name_normalized IS 'Generated column for case-insensitive search and deduplication.';
COMMENT ON COLUMN resident_events.payload IS 'Standardized JSONB structure: {before, after, metadata, reason}. Enforce at application layer.';
COMMENT ON COLUMN resident_events.correlation_id IS 'UUID to group related events (e.g., room_transfer sequence).';
-- =============================================================================
