CREATE TABLE properties ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE, name VARCHAR(255) NOT NULL, property_type VARCHAR(100) CHECK (property_type IN ( 'residential', 'commercial', 'industrial', 'mixed_use', 'land' )), description TEXT, address_line1 VARCHAR(255) NOT NULL, address_line2 VARCHAR(255), city VARCHAR(100), state VARCHAR(100), postal_code VARCHAR(20), country VARCHAR(100) DEFAULT 'Kenya', latitude DECIMAL(10, 8), longitude DECIMAL(11, 8), year_built INTEGER, total_units INTEGER DEFAULT 0, total_square_footage DECIMAL(10, 2), number_of_floors INTEGER, parking_spaces INTEGER DEFAULT 0, amenities JSONB DEFAULT '[]', manager_id UUID REFERENCES users(id) ON DELETE SET NULL, agent_id UUID REFERENCES users(id) ON DELETE SET NULL, management_company VARCHAR(255), currency VARCHAR(3) DEFAULT 'KES', tax_rate DECIMAL(5, 2) DEFAULT 16.00, late_fee_percentage DECIMAL(5, 2) DEFAULT 5.00, late_fee_grace_period INTEGER DEFAULT 5, status VARCHAR(50) DEFAULT 'active' CHECK (status IN ( 'active', 'inactive', 'under_renovation', 'sold', 'archived' )), primary_image_url VARCHAR(500), images JSONB DEFAULT '[]', documents JSONB DEFAULT '[]', created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), deleted_at TIMESTAMP WITH TIME ZONE, metadata JSONB DEFAULT '{}', created_by UUID REFERENCES users(id), updated_by UUID REFERENCES users(id) ); CREATE TABLE units ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), property_id UUID NOT NULL REFERENCES properties(id) ON DELETE CASCADE, tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE, unit_number VARCHAR(50) NOT NULL, unit_type VARCHAR(100) CHECK (unit_type IN ( 'studio', 'one_bedroom', 'two_bedroom', 'three_bedroom', 'penthouse', 'office', 'retail', 'warehouse', 'parking', 'other' )), floor_number INTEGER, square_footage DECIMAL(10, 2), bedrooms INTEGER DEFAULT 0, bathrooms DECIMAL(3, 1) DEFAULT 0, has_balcony BOOLEAN DEFAULT FALSE, has_parking BOOLEAN DEFAULT FALSE, features JSONB DEFAULT '{}', base_rent DECIMAL(10, 2), security_deposit DECIMAL(10, 2), includes_utilities BOOLEAN DEFAULT FALSE, utility_allowance DECIMAL(10, 2) DEFAULT 0, status VARCHAR(50) DEFAULT 'vacant' CHECK (status IN ( 'vacant', 'occupied', 'reserved', 'under_maintenance', 'not_rentable', 'occupied_renewal' )), available_date DATE, current_tenant_id UUID, current_lease_id UUID, lease_start_date DATE, lease_end_date DATE, last_inspection_date DATE, next_inspection_date DATE, condition_rating INTEGER CHECK (condition_rating BETWEEN 1 AND 5), maintenance_notes TEXT, primary_image_url VARCHAR(500), floor_plan_url VARCHAR(500), images JSONB DEFAULT '[]', created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), deleted_at TIMESTAMP WITH TIME ZONE, metadata JSONB DEFAULT '{}', created_by UUID REFERENCES users(id), updated_by UUID REFERENCES users(id), UNIQUE(property_id, unit_number) ); CREATE TABLE property_documents ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), property_id UUID REFERENCES properties(id) ON DELETE CASCADE, unit_id UUID REFERENCES units(id) ON DELETE CASCADE, tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE, document_type VARCHAR(100) CHECK (document_type IN ( 'title_deed', 'insurance', 'inspection_report', 'property_tax', 'utility_bill', 'contract', 'photo', 'other' )), title VARCHAR(255) NOT NULL, description TEXT, file_name VARCHAR(255) NOT NULL, file_url VARCHAR(500) NOT NULL, file_size INTEGER, mime_type VARCHAR(100), is_verified BOOLEAN DEFAULT FALSE, expiry_date DATE, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), deleted_at TIMESTAMP WITH TIME ZONE, metadata JSONB DEFAULT '{}', uploaded_by UUID REFERENCES users(id) ); CREATE TABLE property_expenses ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), property_id UUID NOT NULL REFERENCES properties(id) ON DELETE CASCADE, tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE, expense_type VARCHAR(100) CHECK (expense_type IN ( 'maintenance', 'repair', 'utility', 'tax', 'insurance', 'management_fee', 'cleaning', 'security', 'landscaping', 'other' )), description TEXT NOT NULL, amount DECIMAL(10, 2) NOT NULL, vendor_name VARCHAR(255), vendor_contact VARCHAR(255), invoice_number VARCHAR(100), expense_date DATE NOT NULL, due_date DATE, paid_date DATE, status VARCHAR(50) DEFAULT 'pending' CHECK (status IN ( 'pending', 'approved', 'paid', 'cancelled', 'disputed' )), receipt_url VARCHAR(500), attachments JSONB DEFAULT '[]', approved_by UUID REFERENCES users(id), approved_at TIMESTAMP WITH TIME ZONE, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), deleted_at TIMESTAMP WITH TIME ZONE, metadata JSONB DEFAULT '{}', created_by UUID REFERENCES users(id) ); CREATE INDEX idx_properties_tenant ON properties(tenant_id) WHERE deleted_at IS NULL; CREATE INDEX idx_properties_type ON properties(property_type) WHERE deleted_at IS NULL; CREATE INDEX idx_properties_status ON properties(status) WHERE deleted_at IS NULL; CREATE INDEX idx_properties_manager ON properties(manager_id) WHERE deleted_at IS NULL; CREATE INDEX idx_properties_agent ON properties(agent_id) WHERE deleted_at IS NULL; CREATE INDEX idx_units_property ON units(property_id) WHERE deleted_at IS NULL; CREATE INDEX idx_units_tenant ON units(tenant_id) WHERE deleted_at IS NULL; CREATE INDEX idx_units_status ON units(status) WHERE deleted_at IS NULL; CREATE INDEX idx_units_type ON units(unit_type) WHERE deleted_at IS NULL; CREATE INDEX idx_units_number ON units(unit_number); CREATE INDEX idx_units_tenant_current ON units(current_tenant_id); CREATE INDEX idx_property_docs_property ON property_documents(property_id) WHERE deleted_at IS NULL; CREATE INDEX idx_property_docs_unit ON property_documents(unit_id) WHERE deleted_at IS NULL; CREATE INDEX idx_property_docs_type ON property_documents(document_type) WHERE deleted_at IS NULL; CREATE INDEX idx_property_expenses_property ON property_expenses(property_id) WHERE deleted_at IS NULL; CREATE INDEX idx_property_expenses_type ON property_expenses(expense_type); CREATE INDEX idx_property_expenses_date ON property_expenses(expense_date); CREATE INDEX idx_property_expenses_status ON property_expenses(status); CREATE TRIGGER update_properties_timestamp BEFORE UPDATE ON properties FOR EACH ROW EXECUTE FUNCTION update_timestamp(); CREATE TRIGGER update_units_timestamp BEFORE UPDATE ON units FOR EACH ROW EXECUTE FUNCTION update_timestamp(); CREATE TRIGGER update_property_documents_timestamp BEFORE UPDATE ON property_documents FOR EACH ROW EXECUTE FUNCTION update_timestamp(); CREATE TRIGGER update_property_expenses_timestamp BEFORE UPDATE ON property_expenses FOR EACH ROW EXECUTE FUNCTION update_timestamp();